最終的な転送プロセスは次のとおりでした：ora2pgによるテーブル構造の作成、参照スキームに従って構造の修正、データの転送、blobおよびBooleanの変換、PostgreSQLにない関数（nvl、nvl2、regexp_substr）の追加、インデックス、ビューなどの残りの構造の作成。
catの下では、sqlの変換中に蓄積された半自動移行用のsqlスクリプト。
準備する
データ変換のユーティリティとして、ora2pgが使用されました。 使用プロセスは、 記事で非常によく説明されています 。
ora2pgでプロジェクトを作成し、プロジェクトを設定して、スキームを生成します。
$./export_schema.sh
PostgreSQLの「./schema/tables/table.sql」ファイルに「ora_schema」スキーマとテーブルを作成します。
Hibernateを切り替えて作成モードにし、別の「hb_schema」参照回路を作成します。 プロジェクトでビューを使用する場合、異なるスキームのテーブルの数は収束しません。 Hibernateは、ビューの代わりに完全なテーブルを生成します。これを考慮する必要があります。
列タイプの修正
変更する列を調べます
select hb.table_name as table , hb.column_name as column, hb.data_type as hb_type, ora.data_type as ora_type from information_schema.columns hb join information_schema.columns ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema' and ora.data_type != hb.data_type ORDER BY hb.table_name, hb.column_name;
単純なケースの代替品
select 'ALTER TABLE ora_schema.' || ora.table_name || ' ALTER COLUMN ' || ora.column_name || ' TYPE ' || hb.data_type || case when hb.data_type = 'numeric' then '('|| hb.numeric_precision ||','|| hb.numeric_scale||')' else '' end || '; ' from information_schema.columns as hb INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema' and ora.data_type != hb.data_type and (hb.data_type = 'integer' or hb.data_type = 'numeric' or hb.data_type = 'bigint' or hb.data_type = 'date') ORDER BY hb.table_name, hb.column_name;
データを転送する
データを転送する前に、booleanおよびbytea（oid）列にのみ型の不一致が残っていること、および数値を持つすべての列が正しい精度を持ち、偶発的な丸めを避けるための「倍精度」ではないことを確認します。
デフォルトでは、コピープロセスは1つのストリームに移動します。 構成を目的の番号に変更します
JOBS 4 ORACLE_COPIES 4
データをコピーするプロセスを開始します
ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf
処理ブール
デフォルト値がある場合は削除する必要があり、タイプを変更してデフォルト値を返す
--ALTER TABLE mytabe ALTER COLUMN mycolumn DROP DEFAULT; --ALTER TABLE mytabe ALTER mycolumn TYPE bool USING CASE WHEN mycolumn is null then null when mycolumn=0 THEN FALSE ELSE TRUE END; --ALTER TABLE mytabe ALTER COLUMN mycolumn SET DEFAULT FALSE; select case when ora.column_default is not null then 'ALTER TABLE ora_schema.'|| ora.table_name || ' ALTER COLUMN ' || ora.column_name || ' DROP DEFAULT; ' else '' end || 'ALTER TABLE ora_schema.'|| ora.table_name || ' ALTER ' || ora.column_name || ' TYPE bool USING CASE WHEN ' || ora.column_name ||' is null then null WHEN ' || ora.column_name ||'=0 THEN FALSE ELSE TRUE END;' || case when cast(ora.column_default as NUMERIC) = 0 then 'ALTER TABLE ora_schema.'|| ora.table_name || ' ALTER COLUMN ' || ora.column_name || ' SET DEFAULT FALSE' when cast(ora.column_default as NUMERIC) = 1 then 'ALTER TABLE ora_schema.'|| ora.table_name || ' ALTER COLUMN ' || ora.column_name || ' SET DEFAULT TRUE' else '' end from information_schema.columns as hb INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema' and hb.column_name = ora.column_name and hb.udt_name != ora.udt_name and hb.data_type = 'boolean' ORDER BY hb.table_name, hb.column_name;
処理OID（bytea）
byteaをoidに変換する手順を作成します
CREATE OR REPLACE FUNCTION ora_schema.bytea_to_oid(p_blob bytea) RETURNS oid AS $BODY$ declare v_oid oid; v_int integer; begin if octet_length(p_blob)=0 then v_oid:=null; else select lo_create(0) into v_oid; select lo_open(v_oid, x'20000'::int | x'40000'::int) into v_int; select lowrite (0, p_blob) into v_int; end if; return v_oid; END;$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION ora_schema.bytea_to_oid(bytea) OWNER TO postgres;
一時的な列を作成する
select 'ALTER TABLE ora_schema.' || ora.table_name || ' ADD COLUMN ' || hb.column_name || '_oid oid; ' from information_schema.columns as hb JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema' and hb.udt_name != ora.udt_name and hb.data_type = 'oid' ORDER BY hb.table_name, hb.column_name;
データを転送する
select 'update ' || ora.table_name || ' set ' || hb.column_name || '_oid = bytea_to_oid(' || hb.column_name || ');' from information_schema.columns as hb JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema' and hb.udt_name != ora.udt_name and hb.data_type = 'oid' ORDER BY hb.table_name, hb.column_name;
古い列を削除する
select 'ALTER TABLE ora_schema.' || ora.table_name || ' DROP COLUMN ' || hb.column_name || '; ' from information_schema.columns as hb INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema' and hb.udt_name != ora.udt_name and hb.data_type = 'oid' ORDER BY hb.table_name, hb.column_name;
時間列の名前を変更する
select 'ALTER TABLE ora_schema.' || ora.table_name || ' RENAME COLUMN ' || hb.column_name ||'_oid to '|| hb.column_name||'; ' from information_schema.columns as hb INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name ||'_oid' = ora.column_name where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema' and hb.data_type = 'oid' ORDER BY hb.table_name, hb.column_name;
PostgreSQLにない機能の追加
コードを書き換える必要がないように、PostgreSQLには存在しないが、Oracleにあり、プロジェクトで使用されるコードを作成するだけです。
last_day（日付）
create function last_day(dt date) returns date LANGUAGE SQL AS $$ select cast (date_trunc('month',dt)+interval '1 month' - interval '1 day' as date) $$;
nvl（日付、日付）
create function nvl(var1 date, var2 date) returns date LANGUAGE plpgsql AS $$ begin return case when var1 is null then var2 else var1 end; end; $$;
nvl（整数、整数）
create function nvl(var1 integer, var2 integer) returns integer LANGUAGE plpgsql AS $$ begin return case when var1 is null then var2 else var1 end; end; $$;
nvl（数値、数値）
create function nvl(var1 numeric, var2 numeric) returns numeric LANGUAGE plpgsql AS $$ begin return case when var1 is null then var2 else var1 end; end; $$;
nvl（テキスト、テキスト）
create function nvl(var1 text, var2 text) returns text LANGUAGE plpgsql AS $$ begin return case when var1 is null then var2 else var1 end; end; $$;
nvl（タイムゾーン付きタイムスタンプ、タイムゾーン付きタイムスタンプ）
create function nvl(var1 timestamp with time zone, var2 timestamp with time zone) returns timestamp with time zone LANGUAGE plpgsql AS $$ begin return case when var1 is null then var2 else var1 end; end; $$;
nvl（タイムスタンプ、タイムスタンプ）
create function nvl(var1 timestamp without time zone, var2 timestamp without time zone) returns timestamp without time zone LANGUAGE plpgsql AS $$ begin return case when var1 is null then var2 else var1 end; end; $$;
nvl（varchar、varchar）
create function nvl(var1 character varying, var2 character varying) returns character varying LANGUAGE plpgsql AS $$ begin return case when var1 is null then var2 else var1 end; end; $$;
nvl2（日付、日付、日付）
create function nvl2(var1 date, var2 date, var3 date) returns date LANGUAGE plpgsql AS $$ begin return case when var1 is not null then var2 else var3 end; end; $$;
nvl2（整数、整数、整数）
create function nvl2(var1 integer, var2 integer, var3 integer) returns integer LANGUAGE plpgsql AS $$ begin return case when var1 is not null then var2 else var3 end; end; $$;
nvl2（数値、数値、数値）
create function nvl2(var1 numeric, var2 numeric, var3 numeric) returns numeric LANGUAGE plpgsql AS $$ begin return case when var1 is not null then var2 else var3 end; end; $$;
nvl2（テキスト、テキスト、テキスト）
create function nvl2(var1 text, var2 text, var3 text) returns text LANGUAGE plpgsql AS $$ begin return case when var1 is not null then var2 else var3 end; end; $$;
nvl（タイムゾーン付きタイムスタンプ、タイムゾーン付きタイムスタンプ）
create function nvl2(var1 timestamp with time zone, var2 timestamp with time zone, var3 timestamp with time zone) returns timestamp with time zone LANGUAGE plpgsql AS $$ begin return case when var1 is not null then var2 else var3 end; end; $$;
nvl2（タイムスタンプ、タイムスタンプ、タイムスタンプ）
create function nvl2(var1 timestamp without time zone, var2 timestamp without time zone, var3 timestamp without time zone) returns timestamp without time zone LANGUAGE plpgsql AS $$ begin return case when var1 is not null then var2 else var3 end; end; $$;
nvl2（varchar、varchar、varchar）
create function nvl2(var1 character varying, var2 character varying, var3 character varying) returns character varying LANGUAGE plpgsql AS $$ begin return case when var1 is not null then var2 else var3 end; end; $$;
regexp_substr（テキスト、テキスト）
create function regexp_substr(str text, pattern text) returns text LANGUAGE SQL AS $$ SELECT (regexp_matches(str, pattern))[1] $$;
regexp_substr（varchar、varchar）
create function regexp_substr(str character varying, pattern character varying) returns text LANGUAGE SQL AS $$ SELECT (regexp_matches(str, pattern))[1] $$;
trunc（日付、varchar）
create function trunc(dt date, formatstr character varying) returns date LANGUAGE plpgsql AS $$ begin return date_trunc(formatstr,dt); end; $$;
trunc（タイムゾーン付きのタイムスタンプ、varchar）
create function trunc(dt timestamp with time zone, formatstr character varying) returns date LANGUAGE plpgsql AS $$ begin return date_trunc(formatstr,dt); end; $$;
trunc（タイムスタンプ、varchar）
create function trunc(dt timestamp without time zone, formatstr character varying) returns date LANGUAGE plpgsql AS $$ begin return date_trunc(formatstr,dt); end; $$;
テキストフィールド
Oracleには無制限のテキストフィールドはありませんが、代わりにLobが使用されます。 PostgreSQLには特別なタイプ-テキストがあります。 両方のベースを使用できるようにするには、テキストフィールドの注釈を次のようにする必要があります
@Column(name = "script", nullable = true) @Type(type = "org.hibernate.type.MaterializedClobType") public String scriptText;
Postgresでは、追加された機能を知っている自作の方言も使用します。
public class PostgresDialect extends PostgreSQL9Dialect { public PostgresDialect() { super(); this.registerFunction("nvl", new StandardSQLFunction("nvl")); } public SqlTypeDescriptor getSqlTypeDescriptorOverride(int sqlCode) { Object descriptor; switch (sqlCode) { case 2005: descriptor = LongVarcharTypeDescriptor.INSTANCE; break; default: descriptor = super.getSqlTypeDescriptorOverride(sqlCode); } return (SqlTypeDescriptor) descriptor; } }
シーケンス
OracleとPostgressのシーケンスとは異なるnextval構文があります。
オラクル
SELECT my_seq.nextval;
ポストグレス
select nextval('my_seq');
一般的な見方をします。 これを行うには、PostgresとOracleで関数を作成し、この関数を使用するようにどこでも関数を書き換えます。
オラクル
create or replace function seq_nextval(p_sequence_name varchar) return integer as l_statement varchar(4000); l_value integer; begin l_statement := 'select '||upper(p_sequence_name)||'.nextval from dual'; execute immediate l_statement into l_value; return l_value; end;
ポストグレス
create function seq_nextval(p_sequence_name text) returns bigint as $$ select nextval(p_sequence_name); $$ language sql;
使用する
select seq_nextval('my_seq')
Oracleではこの関数を作成できますが、値を選択することはできないため、Postgres nextval（ 'my_seq'）関数を直接使用することはできません。
Transformers.ALIAS_TO_ENTITY_MAP
Transformers.ALIAS_TO_ENTITY_MAPを使用する場合、戻り値の型を指定する必要があります。 Oracleはデフォルトで大文字のキー、PotgresSqlは小文字、キーの不一致は手動でのみ修正されます。
シスデート
SysdateはPotgresSqlにはなく、current_timestampは両方のデータベースで機能します。 それと交換
部分文字列
Javaとは異なり、データベースでは、インデックスは1で始まり、サブストリング（ 'str'、0、2）ではなく最初の2文字を取得するためにサブストリング（ 'str'、1、2）を正しく指定します。 しかし、Oracleは2番目のオプションを認めています。 修正する必要があり、0インデックスを使用しません
一時テーブル
PostgreSQLでは、Oracleとは異なり、一時テーブルは毎回作成され、セッション内で有効になります。 Oracleでは、一時テーブルの内容のみが何らかのフレームワークに存在し、テーブル自体は常に作成されます。
これにより、次の問題が発生します。
1）各セッションを作成するとき、一時テーブルを作成する必要があります。 （この瞬間は、新しいセッションを作成するときに初期化sql-blockを実行するアプリケーションサーバーの機能を使用して簡略化できます（接続プール→詳細設定→初期化SQL）
2）休止状態のエンティティの検証は、構成されたスキームのフレームワーク内で実行されます（スキームを指定しない場合、使用可能なすべてのスキームで検証が実行されます）。 なぜなら Postgreの一時テーブルは別のスキームで作成されるため、検証は失敗します。
（同じ構造の実際のテーブルを作成することで回避できます。ネイティブクエリの場合、作業は通常のテーブルではなく一時テーブルで行われます）。
3）ネイティブクエリを介してのみ、このような一時テーブルを操作することができます。 Hibernateは、生成されたすべてのクエリにスキーマ名を追加します（ネイティブクエリを介して一時テーブルですべての作業を行うか、一時テーブルを破棄して、一意のキーでデータを区別する通常のテーブルを使用する必要があります。どちらの方法でも機能を書き換える必要があります）。
Oracleのように、Postgressで一時テーブルを引き続き使用するには、テーブル関数を使用し、テーブル関数に基づいてビューを作成します。 詳細はこちら
一時テーブルを作成する機能
create_permanent_temp_table
create or replace function create_permanent_temp_table( p_table_name varchar, p_schema varchar default null) returns void as $$ declare -- https://github.com/yallie/pg_global_temp_tables v_table_name varchar := p_table_name || '$tmp'; v_trigger_name varchar := p_table_name || '$iud'; v_final_statement text; v_table_statement text; -- create temporary table... v_all_column_list text; -- id, name, ... v_new_column_list text; -- new.id, new.name, ... v_assignment_list text; -- id = new.id, name = new.name, ... v_cols_types_list text; -- id bigint, name varchar, ... v_old_column_list text; -- id = old.id, name = old.name, ... v_old_pkey_column text; -- id = old.id begin -- check if the temporary table exists if not exists(select 1 from pg_class where relname = p_table_name and relpersistence = 't') then raise exception 'Temporary table % does not exist. %', p_table_name, 'Create an ordinary temp ' || 'table first, then use create_permanent_temp_table function to convert it to a permanent one.' using errcode = 'UTMP1'; end if; -- make sure that the schema is defined if p_schema is null or p_schema = '' then p_schema := current_schema; end if; -- generate the temporary table statement with pkey as ( select cc.conrelid, format(E', constraint %I primary key(%s)', cc.conname, string_agg(a.attname, ', ' order by array_position(cc.conkey, a.attnum))) pkey from pg_catalog.pg_constraint cc join pg_catalog.pg_class c on c.oid = cc.conrelid join pg_catalog.pg_attribute a on a.attrelid = cc.conrelid and a.attnum = any(cc.conkey) where cc.contype = 'p' group by cc.conrelid, cc.conname ) select format(E'\tcreate temporary table if not exists %I\n\t(\n%s%s\n\t)\n\ton commit drop;', v_table_name, string_agg( format(E'\t\t%I %s%s', a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), case when a.attnotnull then ' not null' else '' end ), E',\n' order by a.attnum ), (select pkey from pkey where pkey.conrelid = c.oid)) as sql into v_table_statement from pg_catalog.pg_class c join pg_catalog.pg_attribute a on a.attrelid = c.oid and a.attnum > 0 join pg_catalog.pg_type t on a.atttypid = t.oid where c.relname = p_table_name and c.relpersistence = 't' group by c.oid, c.relname; -- generate the lists of columns select string_agg(a.attname, ', '), string_agg(format('new.%I', a.attname), ', '), string_agg(format('%I = new.%I', a.attname, a.attname), ', '), string_agg(format('%I %s', a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod)), ', '), string_agg(format('%I = old.%I', a.attname, a.attname), ' and ') into v_all_column_list, v_new_column_list, v_assignment_list, v_cols_types_list, v_old_column_list from pg_catalog.pg_class c join pg_catalog.pg_attribute a on a.attrelid = c.oid and a.attnum > 0 join pg_catalog.pg_type t on a.atttypid = t.oid where c.relname = p_table_name and c.relpersistence = 't'; -- generate the list of primary key columns select string_agg(format('%I = old.%I', a.attname, a.attname), ' and ' order by array_position(cc.conkey, a.attnum)) into v_old_pkey_column from pg_catalog.pg_constraint cc join pg_catalog.pg_class c on c.oid = cc.conrelid join pg_catalog.pg_attribute a on a.attrelid = cc.conrelid and a.attnum = any(cc.conkey) where cc.contype = 'p' and c.relname = p_table_name and c.relpersistence = 't' group by cc.conrelid, cc.conname; -- if primary key is defined, use the primary key columns if length(v_old_pkey_column) > 0 then v_old_column_list := v_old_pkey_column; end if; -- generate the view function v_final_statement := format(E'-- rename the original table to avoid the conflict alter table %I rename to %I; -- the function to select from the temporary table create or replace function %I.%I() returns table(%s) as $x$ begin -- generated by pg_global_temp_tables -- create table statement %s return query select * from %I; end; $x$ language plpgsql set client_min_messages to error;\n', p_table_name, v_table_name, p_schema, p_table_name, v_cols_types_list, v_table_statement, v_table_name); -- generate the view v_final_statement := v_final_statement || format(E' create or replace view %I.%I as select * from %I.%I();\n', p_schema, p_table_name, p_schema, p_table_name); -- generate the trigger function v_final_statement := v_final_statement || format(E' create or replace function %I.%I() returns trigger as $x$ begin -- generated by pg_global_temp_tables -- create temporary table %s -- handle the trigger operation if lower(tg_op) = \'insert\' then insert into %I(%s) values (%s); return new; elsif lower(tg_op) = \'update\' then update %I set %s where %s; return new; elsif lower(tg_op) = \'delete\' then delete from %I where %s; return old; end if; end; $x$ language plpgsql set client_min_messages to error;\n', p_schema, v_trigger_name, v_table_statement, -- function header v_table_name, v_all_column_list, v_new_column_list, -- insert v_table_name, v_assignment_list, v_old_column_list, -- update v_table_name, v_old_column_list); -- delete -- generate the view trigger v_final_statement := v_final_statement || format(E' drop trigger if exists %I on %I.%I; create trigger %I instead of insert or update or delete on %I.%I for each row execute procedure %I.%I();', v_trigger_name, p_schema, p_table_name, v_trigger_name, p_schema, p_table_name, p_schema, v_trigger_name); -- create all objects at once execute v_final_statement; end; $$ language plpgsql set client_min_messages to error;
一時テーブルを削除する機能
drop_permanent_temp_table
create or replace function drop_permanent_temp_table( p_table_name varchar, p_schema varchar default null) returns void as $$ declare -- https://github.com/yallie/pg_global_temp_tables v_table_name varchar := p_table_name || '$tmp'; v_trigger_name varchar := p_table_name || '$iud'; v_count int; v_drop_statements text; begin -- make sure that the schema is defined if p_schema is null or p_schema = '' then p_schema := current_schema; end if; -- check if the supporting functions exist select count(*) into v_count from pg_catalog.pg_proc p join pg_catalog.pg_namespace n on n.oid = p.pronamespace where p.proname in (p_table_name, v_trigger_name) and p.pronargs = 0 and n.nspname = p_schema and p.prosrc like '%pg_global_temp_tables%'; if v_count <> 2 then raise exception 'The table %.% does not seem to be persistent temporary table. %', p_schema, p_table_name, 'The function only supports tables created by pg_global_temp_tables library.' using errcode = 'UTMP2'; end if; -- generate the drop function statements v_drop_statements := format(E'-- drop the functions and cascade the view drop function %I.%I() cascade; drop function %I.%I() cascade;', p_schema, p_table_name, p_schema, v_trigger_name); -- drop the functions execute v_drop_statements; end; $$ language plpgsql set client_min_messages to error;
使用する
通常の一時テーブルを作成する
CREATE temp TABLE filter_table ( id BIGINT NOT NULL, id_str VARCHAR(255), key VARCHAR(255) NOT NULL, fd DATE, id_long BIGINT, sd DATE, CONSTRAINT filter_table_pkey PRIMARY KEY (key, id) ) ON COMMIT PRESERVE ROWS;
生成関数を実行します：
select create_permanent_temp_table('filter_table', 'schema_name');