Во время миграции из Oracle в PostgreSQL с помощью ora2pg встал вопрос с несоответствием типов данных между разными базами. По умолчанию не все колонки конвертируется правильно, а отсутствие в Oracle Boolean и вовсе порождает неоднозначность – часть колонок нужно перенести как числа, часть как логические значения. В тоже время hibernate знает все о типах данных и может создать эталонную схему.
Итоговый процесс переноса выглядел следующим образом: создание структуры таблиц через ora2pg, исправление структуры по эталонной схеме, перенос данных, конвертация blob и Boolean, добавление отсутствующих в PostgreSQL функций (nvl, nvl2, regexp_substr), создания оставшейся структуры — индексов, view и прочего.
Под катом накопившиеся за время конвертации sql скрипты для полуавтоматической миграции.
$./export_schema.sh
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;
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;
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
'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 '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;
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)
$$;
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;
$$;
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;
$$;
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;
$$;
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;
$$;
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;
$$;
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;
$$;
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;
$$;
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;
$$;
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;
$$;
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;
$$;
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;
$$;
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;
$$;
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;
$$;
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;
$$;
create function regexp_substr(str text, pattern text) returns text
LANGUAGE SQL
AS $$
SELECT (regexp_matches(str, pattern))[1]
$$;
create function regexp_substr(str character varying, pattern character varying) returns text
LANGUAGE SQL
AS $$
SELECT (regexp_matches(str, pattern))[1]
$$;
create function trunc(dt date, formatstr character varying) returns date
LANGUAGE plpgsql
AS $$
begin
return date_trunc(formatstr,dt);
end;
$$;
create function trunc(dt timestamp with time zone, formatstr character varying) returns date
LANGUAGE plpgsql
AS $$
begin
return date_trunc(formatstr,dt);
end;
$$;
create function trunc(dt timestamp without time zone, formatstr character varying) returns date
LANGUAGE plpgsql
AS $$
begin
return date_trunc(formatstr,dt);
end;
$$;
@Column(name = "script", nullable = true)
@Lob
@Type(type = "org.hibernate.type.TextType")
public String scriptText;
К сожалению, не доступен сервер mySQL