From 2f2a82831e3015a901997a327da209e4011ea33e Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Mon, 6 Oct 2025 16:04:52 +0200 Subject: [PATCH 01/17] ora: Optimize keys.sql --- internal/oracle/sql/keys.sql | 25 ++++++++++--------------- 1 file changed, 10 insertions(+), 15 deletions(-) diff --git a/internal/oracle/sql/keys.sql b/internal/oracle/sql/keys.sql index f3f4e9b01..f89619df2 100644 --- a/internal/oracle/sql/keys.sql +++ b/internal/oracle/sql/keys.sql @@ -1,26 +1,22 @@ -WITH VIEWS AS ( - SELECT DISTINCT OWNER, MVIEW_NAME AS TABLE_NAME - FROM ALL_MVIEWS - - UNION - - SELECT DISTINCT LOG_OWNER AS OWNER, LOG_TABLE AS TABLE_NAME - FROM ALL_MVIEW_LOGS -), -TABLES AS ( +WITH TABLES AS ( SELECT TAB.OWNER, TAB.TABLE_NAME FROM ALL_TABLES TAB - LEFT JOIN VIEWS ON VIEWS.OWNER = TAB.OWNER AND VIEWS.TABLE_NAME = TAB.TABLE_NAME - WHERE VIEWS.OWNER IS NULL + LEFT OUTER JOIN ALL_MVIEWS MV + ON MV.OWNER = TAB.OWNER AND MV.MVIEW_NAME = TAB.TABLE_NAME + LEFT OUTER JOIN ALL_MVIEW_LOGS MVL + ON MVL.LOG_OWNER = TAB.OWNER AND MVL.LOG_TABLE = TAB.TABLE_NAME + WHERE TAB.OWNER IN ({{ template "stringarray.sql" .Schemas }}) AND TAB.SECONDARY = 'N' AND TAB.NESTED = 'NO' AND TAB.DROPPED = 'NO' + AND MV.OWNER IS NULL + AND MVL.LOG_OWNER IS NULL ) SELECT CON.OWNER, CON.TABLE_NAME, CON.CONSTRAINT_NAME, '' AS "COMMENT", - LISTAGG(COL.COLUMN_NAME, ',') AS "COLUMNS", + LISTAGG(COL.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COL.POSITION) AS "COLUMNS", CASE WHEN CON.CONSTRAINT_TYPE = 'P' THEN 1 ELSE 0 END AS IS_PRIMARY, CASE WHEN CON.CONSTRAINT_TYPE = 'U' THEN 1 ELSE 0 END AS IS_UNIQUE FROM TABLES TAB @@ -28,8 +24,7 @@ SELECT CON.OWNER, ON TAB.OWNER = CON.OWNER AND TAB.TABLE_NAME = CON.TABLE_NAME JOIN ALL_CONS_COLUMNS COL ON CON.OWNER = COL.OWNER AND CON.TABLE_NAME = COL.TABLE_NAME AND CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME - WHERE CON.OWNER IN ({{ template "stringarray.sql" .Schemas }}) - AND CON.CONSTRAINT_TYPE IN ('P', 'U') + WHERE CON.CONSTRAINT_TYPE IN ('P', 'U') AND CON.STATUS = 'ENABLED' AND CON.VALIDATED = 'VALIDATED' GROUP BY CON.OWNER, CON.TABLE_NAME, CON.CONSTRAINT_NAME, CON.CONSTRAINT_TYPE -- GitLab From 1bd7384c85c6cbca5822c7e69ea8d19c7ffed8fa Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Wed, 22 Oct 2025 09:25:48 +0200 Subject: [PATCH 02/17] ora: Optimize index query --- internal/oracle/sql/tables-indexes.sql | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) diff --git a/internal/oracle/sql/tables-indexes.sql b/internal/oracle/sql/tables-indexes.sql index 756b05e4e..26a6c87d7 100644 --- a/internal/oracle/sql/tables-indexes.sql +++ b/internal/oracle/sql/tables-indexes.sql @@ -4,6 +4,10 @@ SELECT DISTINCT I.TABLE_OWNER, I.TABLE_NAME, I.INDEX_NAME, '' AS "COMMENT", CASE WHEN I.VISIBILITY = 'INVISIBLE' THEN 1 ELSE 0 END AS "INVISIBLE" FROM ALL_INDEXES I + LEFT OUTER JOIN ALL_MVIEWS MV + ON MV.OWNER = I.TABLE_OWNER AND MV.MVIEW_NAME = I.TABLE_NAME + LEFT OUTER JOIN ALL_MVIEW_LOGS MVL + ON MVL.LOG_OWNER = I.TABLE_OWNER AND MVL.LOG_TABLE = I.TABLE_NAME LEFT JOIN ALL_CONSTRAINTS CONS -- search constraint using this index ON CONS.OWNER = I.OWNER AND CONS.TABLE_NAME = I.TABLE_NAME @@ -18,8 +22,8 @@ SELECT DISTINCT I.TABLE_OWNER, I.TABLE_NAME, I.INDEX_NAME, AND ICOL.TABLE_NAME = CCOL.TABLE_NAME AND ICOL.COLUMN_NAME = CCOL.COLUMN_NAME WHERE I.OWNER IN ({{ template "stringarray.sql" .Schemas }}) - AND (I.OWNER, I.TABLE_NAME) NOT IN (SELECT OWNER, MVIEW_NAME FROM ALL_MVIEWS) - AND (I.OWNER, I.TABLE_NAME) NOT IN (SELECT LOG_OWNER, LOG_TABLE FROM ALL_MVIEW_LOGS) + AND MV.OWNER IS NULL + AND MVL.LOG_OWNER IS NULL AND I.TABLE_NAME NOT LIKE 'RUPD$_%' AND I.TABLE_NAME NOT LIKE 'DR$_%' AND NVL(CONS.CONSTRAINT_TYPE, 'X') NOT IN ('P', 'U') -- GitLab From 52f2c9bd968df7458fc4ca14e6462b9f54e3ada7 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Thu, 18 Sep 2025 09:48:18 +0200 Subject: [PATCH 03/17] =?UTF-8?q?d=C3=A9v:=20Add=20oracle11=20service?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit - Adapt service name customization - Skip XTRA fixtures. --- docker-compose.yml | 17 +++++ test/cli/oracle.bats | 6 +- test/docker/oracle-entrypoint.sh | 2 +- test/fixtures/oracle/00-add-service-name.sh | 13 +++- test/fixtures/oracle/02-sakila-load-data.sh | 2 +- test/fixtures/oracle/90-roles.sql | 4 +- test/fixtures/oracle/91-extra-relations.sql | 69 +---------------- .../oracle/92-extra-relations-ora18.sql | 75 +++++++++++++++++++ test/fixtures/oracle/exit11.sql | 13 ++++ test/fixtures/oracle/exitsqlplus.sqllib | 1 + 10 files changed, 126 insertions(+), 76 deletions(-) mode change 100755 => 100644 test/fixtures/oracle/02-sakila-load-data.sh create mode 100644 test/fixtures/oracle/92-extra-relations-ora18.sql create mode 100644 test/fixtures/oracle/exit11.sql create mode 100644 test/fixtures/oracle/exitsqlplus.sqllib diff --git a/docker-compose.yml b/docker-compose.yml index faff0b09e..96c938997 100644 --- a/docker-compose.yml +++ b/docker-compose.yml @@ -130,6 +130,7 @@ services: ORACLE_PASSWORD: N0tSecret APP_USER: sakila APP_USER_PASSWORD: N0tSecret + DALIBO_SERVICE_NAME: FREEPDB1 ORACLE_SERVICE: SAKILA ORACLE_PFILE: "${ORACLE_PFILE-/pg_migrate/test/docker/oracle-init23.ora}" entrypoint: /pg_migrate/test/docker/oracle-entrypoint.sh @@ -146,10 +147,26 @@ services: ORACLE_PASSWORD: N0tSecret APP_USER: sakila APP_USER_PASSWORD: N0tSecret + DALIBO_SERVICE_NAME: XEPDB1 ORACLE_SERVICE: SAKILA ORACLE_PFILE: "${ORACLE_PFILE-/pg_migrate/test/docker/oracle-init18.ora}" entrypoint: /pg_migrate/test/docker/oracle-entrypoint.sh + oracle11: + image: gvenzl/oracle-xe:11-slim + ports: ["1521:1521"] + working_dir: /pg_migrate/ + volumes: + - .:/pg_migrate/ + - ./test/fixtures/oracle/:/container-entrypoint-initdb.d/ + environment: + ORACLE_PASSWORD: N0tSecret + APP_USER: sakila + APP_USER_PASSWORD: N0tSecret + DALIBO_SERVICE_NAME: XE + ORACLE_SERVICE: SAKILA + entrypoint: /pg_migrate/test/docker/oracle-entrypoint.sh + postgres: image: postgres:17-alpine ports: ["5432:5432"] diff --git a/test/cli/oracle.bats b/test/cli/oracle.bats index b98b5e56e..baf222e67 100644 --- a/test/cli/oracle.bats +++ b/test/cli/oracle.bats @@ -179,7 +179,7 @@ setup_file() { @test "convert" { run jq -er ".Tables | length" "$target" - assert_output "27" + assert_output "28" run jq -er '.["sakila"]' "$PGMDIRECTORY/.pg_migrate/Map.json" assert_output "SAKILA" @@ -197,8 +197,8 @@ setup_file() { # Checking boolean conversion configured in init test. run psql -tA -c "SELECT first_name FROM sakila.customer WHERE is_active ORDER BY customer_id LIMIT 1;" assert_output "MARY" - run psql -tA -c "SELECT last_value FROM xtra.tab_id_seq" - assert_output "1358" + run psql -tA -c "SELECT last_value FROM xtra.tab_id_id_seq" + assert_output "1338" run psql -tA -c "SELECT last_value FROM sakila.film_sequence" assert_output "22" } diff --git a/test/docker/oracle-entrypoint.sh b/test/docker/oracle-entrypoint.sh index 972c716d3..4548328fc 100755 --- a/test/docker/oracle-entrypoint.sh +++ b/test/docker/oracle-entrypoint.sh @@ -6,5 +6,5 @@ if [ -n "${ORACLE_PFILE-}" ] ; then sed -i "s/startup;/startup pfile=\${ORACLE_PFILE};/" /opt/oracle/container-entrypoint.sh fi -cd /opt/oracle +cd "${ORACLE_BASE}" exec ./container-entrypoint.sh diff --git a/test/fixtures/oracle/00-add-service-name.sh b/test/fixtures/oracle/00-add-service-name.sh index c70459230..ef7f97cf6 100644 --- a/test/fixtures/oracle/00-add-service-name.sh +++ b/test/fixtures/oracle/00-add-service-name.sh @@ -8,13 +8,18 @@ fi echo -e "\nCONTAINER: Creating ${ORACLE_SERVICE} service." sqlplus -s /nolog << EOF - -- Exit on any errors WHENEVER SQLERROR EXIT SQL.SQLCODE - CONNECT sys/${ORACLE_PASSWORD}@localhost:1521/FREEPDB1 AS SYSDBA + CONNECT sys/${ORACLE_PASSWORD}@localhost:1521/${DALIBO_SERVICE_NAME} AS SYSDBA EXEC dbms_service.create_service(service_name => '${ORACLE_SERVICE}', network_name => '${ORACLE_SERVICE}'); EXEC dbms_service.start_service(service_name => '${ORACLE_SERVICE}'); - ALTER PLUGGABLE DATABASE SAVE STATE; - exit; + + CREATE OR REPLACE TRIGGER START_SERVICE_${ORACLE_SERVICE} + AFTER STARTUP ON DATABASE + BEGIN + DBMS_SERVICE.START_SERVICE(SERVICE_NAME => '${ORACLE_SERVICE}'); + END; + / + EXIT EOF echo "CONTAINER: DONE: Creating ${ORACLE_SERVICE} service." diff --git a/test/fixtures/oracle/02-sakila-load-data.sh b/test/fixtures/oracle/02-sakila-load-data.sh old mode 100755 new mode 100644 index c4ee501f7..52d0a9229 --- a/test/fixtures/oracle/02-sakila-load-data.sh +++ b/test/fixtures/oracle/02-sakila-load-data.sh @@ -7,7 +7,7 @@ ORACLE_HOST="localhost" ORACLE_PORT="1521" INIT_DIR="/container-entrypoint-initdb.d/files" -ORA_DIR="/opt/oracle" +ORA_DIR="${ORACLE_BASE}" export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss ' diff --git a/test/fixtures/oracle/90-roles.sql b/test/fixtures/oracle/90-roles.sql index 0d363ac60..5d64919a4 100644 --- a/test/fixtures/oracle/90-roles.sql +++ b/test/fixtures/oracle/90-roles.sql @@ -1,9 +1,11 @@ WHENEVER SQLERROR EXIT SQL.SQLCODE CONNECT sys/N0tSecret@localhost:1521/sakila AS SYSDBA +START '/container-entrypoint-initdb.d/exit11.sql' + CREATE ROLE "ALICE" IDENTIFIED BY "N0tSecret" / -CREATE USER SEBASTIEN +CREATE USER SEBASTIEN IDENTIFIED BY "N0tSecret" / GRANT CREATE SESSION TO SEBASTIEN / diff --git a/test/fixtures/oracle/91-extra-relations.sql b/test/fixtures/oracle/91-extra-relations.sql index a8c43f691..2f7f2e863 100644 --- a/test/fixtures/oracle/91-extra-relations.sql +++ b/test/fixtures/oracle/91-extra-relations.sql @@ -8,7 +8,7 @@ CREATE PUBLIC SYNONYM SYN_SAKILA_ACTOR_PUB FOR SAKILA.ACTOR / CREATE TABLE TAB ( - ID NUMBER(8, 2) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1337) NOT NULL PRIMARY KEY, + ID NUMBER(8, 2) NOT NULL PRIMARY KEY, CREATED DATE NOT NULL, FMT VARCHAR2(10) DEFAULT 'YYYY', YEAR_GENERATED AS (EXTRACT(YEAR FROM CREATED)) VIRTUAL, @@ -17,7 +17,7 @@ CREATE TABLE TAB ( ) / -INSERT INTO TAB(CREATED, TXT) VALUES (SYSDATE, 'Nร”T-UTF-8') +INSERT INTO TAB(ID, CREATED, TXT) VALUES (1, SYSDATE, 'Nร”T-UTF-8') / CREATE TABLE TAB_PRECISION_MISMATCH ( @@ -46,7 +46,6 @@ CREATE TABLE TAB_NUMBER ( / CREATE TABLE TAB_DATES ( - ID NUMBER GENERATED AS IDENTITY NOT NULL PRIMARY KEY, T TIMESTAMP(6), TZ TIMESTAMP WITH TIME ZONE, TZ_LOCAL TIMESTAMP WITH LOCAL TIME ZONE, @@ -76,7 +75,6 @@ END; / CREATE TABLE TAB_RAW ( - ID NUMBER GENERATED AS IDENTITY NOT NULL PRIMARY KEY, RAW16 RAW(16), RAW32 RAW(32), VLONGR LONG RAW, @@ -90,51 +88,6 @@ INSERT INTO TAB_RAW(VLONGR) VALUES (NULL) -- NULL row INSERT INTO TAB_RAW(VLONGR) VALUES (UTL_RAW.CAST_TO_RAW('raw data')) / -CREATE TABLE TAB_PART_RANGE ( - ID NUMBER(10) NOT NULL PRIMARY KEY, - YEAR NUMBER, - MONTH NUMBER -) -PARTITION BY RANGE (YEAR, MONTH) -( - PARTITION P_PAST VALUES LESS THAN (2019, 1), - PARTITION P_2019 VALUES LESS THAN (2020, 1), - PARTITION P_2020 VALUES LESS THAN (2021, 1), - PARTITION P_CURRENT VALUES LESS THAN (MAXVALUE, MAXVALUE) -) -/ - -INSERT INTO TAB_PART_RANGE (ID, YEAR, MONTH) VALUES (1, 2019, 1) -/ - -CREATE TABLE TAB_PART_REF ( - ID NUMBER NOT NULL PRIMARY KEY, - CONSTRAINT ID_FK FOREIGN KEY (ID) REFERENCES TAB_PART_RANGE(ID) -) -PARTITION BY REFERENCE (ID_FK) -/ - -CREATE TABLE TAB_PART_SYSTEM (ID NUMBER) -PARTITION BY SYSTEM -PARTITIONS 4 -/ - -CREATE TABLE TAB_PART_LIST_SUB ( - ID NUMBER(10) NOT NULL, - REGION VARCHAR2(20) NOT NULL -) -PARTITION BY LIST (REGION) -SUBPARTITION BY HASH (ID) ( - PARTITION P_EAST VALUES ('EAST', 'WEST') SUBPARTITIONS 4, - PARTITION P_NORTH VALUES ('NORTH') SUBPARTITIONS 2, - PARTITION P_SOUTH VALUES ('SOUTH') SUBPARTITIONS 2, - PARTITION P_DEFAULT VALUES (DEFAULT) SUBPARTITIONS 1 -) -/ - -INSERT INTO TAB_PART_LIST_SUB (ID, REGION) VALUES (1, 'EAST') -/ - CREATE GLOBAL TEMPORARY TABLE TAB_TMP ( ID NUMBER(10) NOT NULL ) @@ -153,19 +106,12 @@ ALTER TABLE "TAB" / WHENEVER SQLERROR EXIT SQL.SQLCODE -CREATE INDEX IDX_INVISIBLE ON TAB(ID) INVISIBLE +CREATE INDEX IDX_INVISIBLE ON TAB(TXT) INVISIBLE / CREATE INDEX IDX_FUNCTIONNAL ON TAB( TRUNC(CREATED, FMT) ) / --- Create an unsupported bitmap index. -CREATE BITMAP INDEX IDX_TAB_BITMAP_YESNO ON TAB("YESNO") -/ - -CREATE MATERIALIZED VIEW LOG ON TAB -/ - CREATE VIEW "VMixedCase" AS SELECT 1 AS "PRIMARY" FROM DUAL / @@ -185,11 +131,6 @@ CREATE MATERIALIZED VIEW MV_NEVER AS SELECT TRUNC(SYSDATE, 'YY'||'YY') AS Y FROM DUAL / -CREATE MATERIALIZED VIEW MV_FAST - REFRESH FAST ON COMMIT - AS SELECT ID FROM TAB -/ - CREATE MATERIALIZED VIEW MV_NEXT REFRESH COMPLETE WITH ROWID @@ -206,7 +147,3 @@ CONNECT sys/N0tSecret@localhost:1521/sakila AS SYSDBA EXEC DBMS_STATS.GATHER_TABLE_STATS('XTRA', 'TAB', CASCADE => TRUE); EXEC DBMS_STATS.GATHER_TABLE_STATS('XTRA', 'TAB_PRECISION_MISMATCH'); EXEC DBMS_STATS.GATHER_TABLE_STATS('XTRA', 'TAB_NUMBER'); -EXEC DBMS_STATS.GATHER_TABLE_STATS('XTRA', 'TAB_PART_RANGE'); -EXEC DBMS_STATS.GATHER_TABLE_STATS('XTRA', 'TAB_PART_REF'); -EXEC DBMS_STATS.GATHER_TABLE_STATS('XTRA', 'TAB_PART_LIST_SUB'); -EXEC DBMS_STATS.GATHER_TABLE_STATS('XTRA', 'TAB_PART_SYSTEM'); diff --git a/test/fixtures/oracle/92-extra-relations-ora18.sql b/test/fixtures/oracle/92-extra-relations-ora18.sql new file mode 100644 index 000000000..3919109c9 --- /dev/null +++ b/test/fixtures/oracle/92-extra-relations-ora18.sql @@ -0,0 +1,75 @@ +WHENEVER SQLERROR EXIT SQL.SQLCODE +CONNECT xtra/N0tSecret@localhost:1521/sakila + +START '/container-entrypoint-initdb.d/exit11.sql' + +CREATE TABLE TAB_ID ( + ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1337) PRIMARY KEY +) +/ + +-- Oracle 11g XE docker image has partionning disabled +CREATE TABLE TAB_PART_RANGE ( + ID NUMBER(10) NOT NULL PRIMARY KEY, + YEAR NUMBER, + MONTH NUMBER +) +PARTITION BY RANGE (YEAR, MONTH) +( + PARTITION P_PAST VALUES LESS THAN (2019, 1), + PARTITION P_2019 VALUES LESS THAN (2020, 1), + PARTITION P_2020 VALUES LESS THAN (2021, 1), + PARTITION P_CURRENT VALUES LESS THAN (MAXVALUE, MAXVALUE) +) +/ + +INSERT INTO TAB_PART_RANGE (ID, YEAR, MONTH) VALUES (1, 2019, 1) +/ + +CREATE TABLE TAB_PART_REF ( + ID NUMBER NOT NULL PRIMARY KEY, + CONSTRAINT ID_FK FOREIGN KEY (ID) REFERENCES TAB_PART_RANGE(ID) +) +PARTITION BY REFERENCE (ID_FK) +/ + +CREATE TABLE TAB_PART_SYSTEM (ID NUMBER) +PARTITION BY SYSTEM +PARTITIONS 4 +/ + +CREATE TABLE TAB_PART_LIST_SUB ( + ID NUMBER(10) NOT NULL, + REGION VARCHAR2(20) NOT NULL +) +PARTITION BY LIST (REGION) +SUBPARTITION BY HASH (ID) ( + PARTITION P_EAST VALUES ('EAST', 'WEST') SUBPARTITIONS 4, + PARTITION P_NORTH VALUES ('NORTH') SUBPARTITIONS 2, + PARTITION P_SOUTH VALUES ('SOUTH') SUBPARTITIONS 2, + PARTITION P_DEFAULT VALUES (DEFAULT) SUBPARTITIONS 1 +) +/ + +INSERT INTO TAB_PART_LIST_SUB (ID, REGION) VALUES (1, 'EAST') +/ + +-- Create an unsupported bitmap index. +CREATE BITMAP INDEX IDX_BITMAP ON TAB_TEXT(V2) +/ + +CREATE MATERIALIZED VIEW LOG ON TAB +/ + +CREATE MATERIALIZED VIEW MV_FAST + REFRESH FAST ON COMMIT + AS SELECT ID FROM TAB +/ + +CONNECT sys/N0tSecret@localhost:1521/sakila AS SYSDBA + +EXEC DBMS_STATS.GATHER_TABLE_STATS('XTRA', 'TAB_ID'); +EXEC DBMS_STATS.GATHER_TABLE_STATS('XTRA', 'TAB_PART_RANGE'); +EXEC DBMS_STATS.GATHER_TABLE_STATS('XTRA', 'TAB_PART_REF'); +EXEC DBMS_STATS.GATHER_TABLE_STATS('XTRA', 'TAB_PART_LIST_SUB'); +EXEC DBMS_STATS.GATHER_TABLE_STATS('XTRA', 'TAB_PART_SYSTEM'); diff --git a/test/fixtures/oracle/exit11.sql b/test/fixtures/oracle/exit11.sql new file mode 100644 index 000000000..a6d531b06 --- /dev/null +++ b/test/fixtures/oracle/exit11.sql @@ -0,0 +1,13 @@ +-- EXIT SQLplus for Oracle 11 +SET TERMOUT OFF + +-- Use a SELECT CASE to set a file to execute. +COLUMN exit_file NEW_VALUE exit_file NOPRINT +SELECT CASE WHEN &_O_RELEASE LIKE '11%' THEN '/container-entrypoint-initdb.d/exitsqlplus.sqllib' + ELSE '/dev/null' + END AS exit_file FROM DUAL; +/ + +-- Unconditionnaly execute the file. +START &exit_file +SET TERMOUT ON diff --git a/test/fixtures/oracle/exitsqlplus.sqllib b/test/fixtures/oracle/exitsqlplus.sqllib new file mode 100644 index 000000000..961e4659e --- /dev/null +++ b/test/fixtures/oracle/exitsqlplus.sqllib @@ -0,0 +1 @@ +EXIT 0 -- GitLab From 7b2da0baa313c6d8ba4a8020fcd5886800858959 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Thu, 18 Sep 2025 09:48:34 +0200 Subject: [PATCH 04/17] ora: Port version query to Oracle 11 --- internal/oracle/metadata.go | 3 ++- internal/oracle/sql/version.sql | 3 +++ 2 files changed, 5 insertions(+), 1 deletion(-) create mode 100644 internal/oracle/sql/version.sql diff --git a/internal/oracle/metadata.go b/internal/oracle/metadata.go index abe91545f..fbc8d5be5 100644 --- a/internal/oracle/metadata.go +++ b/internal/oracle/metadata.go @@ -24,7 +24,8 @@ func (mdl *model) Version(ctx context.Context) (software, version string, err er return } conn := database.Connection(ctx, database.Source) - err = conn.QueryRowContext(ctx, `SELECT PRODUCT, VERSION_FULL FROM PRODUCT_COMPONENT_VERSION`).Scan(&software, &version) + sql := fetch.Render("version", nil) + err = conn.QueryRowContext(ctx, sql).Scan(&software, &version) if err != nil { err = fmt.Errorf("oracle: %w", err) } diff --git a/internal/oracle/sql/version.sql b/internal/oracle/sql/version.sql new file mode 100644 index 000000000..41219801b --- /dev/null +++ b/internal/oracle/sql/version.sql @@ -0,0 +1,3 @@ +SELECT PRODUCT, VERSION + FROM PRODUCT_COMPONENT_VERSION + WHERE PRODUCT LIKE 'Oracle%' -- GitLab From 141089d66ff98457f94682578ffea84625a66272 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Fri, 3 Oct 2025 16:41:13 +0200 Subject: [PATCH 05/17] ora: Adapt mview query for Oracle 11g --- internal/catalog/model.go | 8 ++++++++ internal/oracle/sql/materialized-views.sql | 12 +++++++++++- 2 files changed, 19 insertions(+), 1 deletion(-) diff --git a/internal/catalog/model.go b/internal/catalog/model.go index d6be9493d..c5368d4e0 100644 --- a/internal/catalog/model.go +++ b/internal/catalog/model.go @@ -26,6 +26,14 @@ type Model struct { Annotable } +func (mdl Model) After(v string) bool { + return mdl.Metadata.Version > v +} + +func (mdl Model) Before(v string) bool { + return mdl.Metadata.Version < v +} + // CreateSchemas generates CREATE SCHEMA tasks func (mdl Model) CreateSchemas(p dispatch.TaskAdder) { for _, schema := range mdl.Schemas { diff --git a/internal/oracle/sql/materialized-views.sql b/internal/oracle/sql/materialized-views.sql index 82f69bc7f..9015e5b69 100644 --- a/internal/oracle/sql/materialized-views.sql +++ b/internal/oracle/sql/materialized-views.sql @@ -12,7 +12,12 @@ WITH MVIEWS AS ( ) SELECT V.OWNER, V.MVIEW_NAME, - REFRESH_MODE, REFRESH_METHOD, J.REPEAT_INTERVAL, + REFRESH_MODE, REFRESH_METHOD, +{{- if .Before "18" }} + J.INTERVAL, +{{- else }} + J.REPEAT_INTERVAL, +{{- end }} MVLONG.QUERY, V.COLUMNS, COM.COMMENTS @@ -23,11 +28,16 @@ SELECT V.OWNER, LEFT OUTER JOIN DBA_RGROUP R ON R.OWNER = V.OWNER AND R.NAME = V.MVIEW_NAME +{{- if .Before "18" }} + LEFT OUTER JOIN ALL_JOBS J + ON J.JOB = R.JOB +{{- else }} LEFT OUTER JOIN ALL_SCHEDULER_JOBS J ON J.OWNER = R.OWNER AND J.JOB_NAME = R.JOB_NAME AND J.ENABLED = 'TRUE' AND J.SYSTEM = 'FALSE' +{{- end }} LEFT OUTER JOIN ALL_MVIEW_COMMENTS COM ON V.OWNER = COM.OWNER AND V.MVIEW_NAME = COM.MVIEW_NAME -- GitLab From 056061ce29c203a98a4df1bd48f7bd9ddaa1c7cb Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Mon, 6 Oct 2025 15:26:05 +0200 Subject: [PATCH 06/17] ora: Port mview index columns to 12c --- internal/oracle/sql/materialized-views-indexes-columns.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/internal/oracle/sql/materialized-views-indexes-columns.sql b/internal/oracle/sql/materialized-views-indexes-columns.sql index d267033f9..2c27c1914 100644 --- a/internal/oracle/sql/materialized-views-indexes-columns.sql +++ b/internal/oracle/sql/materialized-views-indexes-columns.sql @@ -2,7 +2,7 @@ SELECT I.OWNER, I.TABLE_NAME, I.INDEX_NAME, C.COLUMN_NAME, E.COLUMN_EXPRESSION AS EXPRESSION, CASE WHEN C.DESCEND = 'DESC' THEN 1 ELSE 0 END AS DESCEND, - COL.COLLATION AS COLLATION + {{ if .After "18" }}COL.COLLATION{{ else }}''{{ end }} AS COLLATION FROM ALL_MVIEWS MV JOIN ALL_INDEXES I ON I.OWNER = MV.OWNER -- GitLab From 163a712f0c13899b506e631cb825f29b13d2b9b2 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Wed, 15 Oct 2025 11:52:18 +0200 Subject: [PATCH 07/17] ora: Adapt scheduler inspection for Oracle 12 --- internal/oracle/sql/scheduler.sql | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/internal/oracle/sql/scheduler.sql b/internal/oracle/sql/scheduler.sql index db5ade28c..1f37737da 100644 --- a/internal/oracle/sql/scheduler.sql +++ b/internal/oracle/sql/scheduler.sql @@ -3,11 +3,15 @@ SELECT OWNER, J.JOB_NAME, JOB_SUBNAME, LAST_START_DATE, REPEAT_INTERVAL, END_DATE FROM ALL_SCHEDULER_JOBS J +{{- if .After "18" }} LEFT OUTER JOIN ALL_REFRESH R ON J.OWNER = R.ROWNER AND J.JOB_NAME = R.JOB_NAME +{{- end }} WHERE OWNER IN ({{ template "stringarray.sql" .Schemas }}) AND SYSTEM = 'FALSE' +{{- if .After "18" }} AND R.RNAME IS NULL +{{- end }} AND J.JOB_NAME NOT LIKE 'DBMS_JOB$_%%' ORDER BY 1, 2 -- GitLab From 11463708db1d16f971858d829b346491b8b9693b Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Fri, 3 Oct 2025 17:01:30 +0200 Subject: [PATCH 08/17] ora: Port roles query to Oracle 11g --- internal/oracle/sql/roles.sql | 114 ++++++++++++++++++++++++++++++++-- 1 file changed, 109 insertions(+), 5 deletions(-) diff --git a/internal/oracle/sql/roles.sql b/internal/oracle/sql/roles.sql index f9ed9e894..438b4223e 100644 --- a/internal/oracle/sql/roles.sql +++ b/internal/oracle/sql/roles.sql @@ -1,21 +1,37 @@ WITH ROLES AS ( - SELECT ROLE, R.COMMON, AUTHENTICATION_TYPE + SELECT ROLE, +{{- if .After "12" }} + R.COMMON, +{{- end }} + AUTHENTICATION_TYPE FROM DBA_ROLES R +{{- if .After "12" }} WHERE R.ORACLE_MAINTAINED = 'N' +{{- end }} UNION - SELECT USERNAME, U.COMMON, AUTHENTICATION_TYPE + SELECT USERNAME, +{{- if .After "12" }} + U.COMMON, +{{- end }} + AUTHENTICATION_TYPE FROM DBA_USERS U - WHERE U.ORACLE_MAINTAINED = 'N' - AND U.ACCOUNT_STATUS = 'OPEN' + WHERE U.ACCOUNT_STATUS = 'OPEN' +{{- if .After "12" }} + AND U.ORACLE_MAINTAINED = 'N' +{{- end }} ORDER BY 1 ) SELECT R.ROLE AS "ROLE", CASE WHEN LOGINP.GRANTEE IS NOT NULL THEN 1 ELSE 0 END AS "LOGIN", CASE WHEN SUPER.GRANTEE IS NOT NULL THEN 1 ELSE 0 END AS "SUPER", - CASE R."COMMON" WHEN 'YES' THEN 1 ELSE 0 END AS "COMMON", +{{- if .After "12" }} + CASE R."COMMON" WHEN 'YES' THEN 1 ELSE 0 END +{{- else }} + 0 +{{- end }} AS "COMMON", AUTHENTICATION_TYPE FROM ROLES R LEFT OUTER JOIN DBA_SYS_PRIVS LOGINP @@ -24,4 +40,92 @@ SELECT R.ROLE AS "ROLE", LEFT OUTER JOIN DBA_ROLE_PRIVS SUPER ON R.ROLE = SUPER.GRANTEE AND SUPER.GRANTED_ROLE = 'DBA' +{{- if .Before "18" }} + -- poor ORACLE_MAINTAINED substitution. + WHERE ROLE NOT LIKE 'APEX_%' + AND ROLE NOT LIKE 'AQ_%_ROLE' + AND ROLE NOT LIKE 'FLOWS_%' + AND ROLE NOT LIKE 'HS_ADMIN_%' + AND ROLE NOT LIKE 'OEM_%' + AND ROLE NOT LIKE 'XDB_%' + AND ROLE NOT LIKE '%P_FULL_DATABASE' + AND ROLE NOT LIKE '%_CATALOG_ROLE' + AND ROLE NOT IN ( + 'ADM_PARALLEL_EXECUTE_TASK', + 'ANONYMOUS', + 'APPQOSSYS', + 'AUDSYS', + 'AURORA$JIS$UTILITY$', + 'AURORA$ORB$UNAUTHENTICATED', + 'AUTHENTICATEDUSER', + 'CONNECT', + 'CSMIG', + 'CTXSYS', + 'DBA', + 'DBFS_ROLE', + 'DBMS_PRIVILEGE_CAPTURE', + 'DBSFWUSER', + 'DBSNMP', + 'DGPDB_INT', + 'DIP', + 'DMSYS', + 'DVSYS', + 'DVF', + 'EXFSYS', + 'GATHER_SYSTEM_STATISTICS', + 'GSMADMIN_INTERNAL', + 'GSMCATUSER', + 'GSMUSER', + 'GGSHAREDCAP', + 'GGSYS', + 'LBACSYS', + 'LOGSTDBY_ADMINISTRATOR', + 'MDDATA', + 'MDSYS', + 'MGMT_VIEW', + 'MGDSYS', + 'ODM', + 'ODM_MTR', + 'OLAPSYS', + 'ORDDATA', + 'ORDPLUGINS', + 'ORDSYS', + 'ORACLE_OCM', + 'OSE$HTTP$ADMIN', + 'OUTLN', + 'OWBSYS', + 'OWBSYS_AUDIT', + 'OJVMSYS', + 'PERFSTAT', + 'PDBADMIN', + 'PLUSTRACE', + 'RECOVERY_CATALOG_OWNER', + 'REMOTE_SCHEDULER_AGENT', + 'RESOURCE', + 'SCHEDULER_ADMIN', + 'SI_INFORMTN_SCHEMA', + 'SDE', + 'SPATIAL_CSW_ADMIN_USR', + 'SPATIAL_WFS_ADMIN_USR', + 'SQLTXPLAIN', + 'SYSTEM', + 'SYSMAN', + 'SYSRAC', + 'SYS', + 'SYSBACKUP', + 'SYSDG', + 'SYSKM', + 'SYS$UMF', + 'TSMSYS', + 'TRACESRV', + 'VECSYS', + 'WKSYS', + 'WK_PROXY', + 'WK_TEST', + 'WKPROXY', + 'WMSYS', + 'XDB', + 'XS$NULL' + ) +{{- end }} ORDER BY 1 -- GitLab From 4412db45f35a064bf6bd6ba47feb2daa0f29021e Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Mon, 6 Oct 2025 15:20:15 +0200 Subject: [PATCH 09/17] ora: Port sequences inspection to Oracle 11 --- internal/oracle/sql/sequences.sql | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) diff --git a/internal/oracle/sql/sequences.sql b/internal/oracle/sql/sequences.sql index 87fd1c8d0..ef536abcd 100644 --- a/internal/oracle/sql/sequences.sql +++ b/internal/oracle/sql/sequences.sql @@ -7,9 +7,13 @@ SELECT S.SEQUENCE_OWNER, CASE WHEN CYCLE_FLAG = 'Y' THEN 1 ELSE 0 END AS CYCLE_FLAG, CACHE_SIZE FROM ALL_SEQUENCES S +{{- if .After "12" }} LEFT OUTER JOIN ALL_TAB_IDENTITY_COLS IC ON S.SEQUENCE_OWNER = IC.OWNER AND S.SEQUENCE_NAME = IC.SEQUENCE_NAME - WHERE IC.COLUMN_NAME IS NULL - AND S.SEQUENCE_OWNER IN ({{ template "stringarray.sql" .Schemas }}) +{{- end }} + WHERE S.SEQUENCE_OWNER IN ({{ template "stringarray.sql" .Schemas }}) + {{- if .After "12" }} + AND IC.COLUMN_NAME IS NULL + {{- end }} ORDER BY 1, 2 -- GitLab From 9c1340aa5d8405b467c8478fc3625f14db70520a Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Mon, 6 Oct 2025 16:25:47 +0200 Subject: [PATCH 10/17] ora: Port table column inspection to Oracle 11 --- internal/oracle/sql/columns.sql | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) diff --git a/internal/oracle/sql/columns.sql b/internal/oracle/sql/columns.sql index 354edcc53..773abed38 100644 --- a/internal/oracle/sql/columns.sql +++ b/internal/oracle/sql/columns.sql @@ -30,8 +30,10 @@ SELECT COL.OWNER, COL.DATA_SCALE AS SCALE, CASE WHEN COL.NULLABLE = 'Y' THEN 1 ELSE 0 END AS NULLABLE, COL.DATA_DEFAULT, - CASE WHEN COL.IDENTITY_COLUMN = 'YES' THEN IC.GENERATION_TYPE || ' AS IDENTITY' - WHEN COL.VIRTUAL_COLUMN = 'YES' THEN 'VIRTUAL' + CASE WHEN COL.VIRTUAL_COLUMN = 'YES' THEN 'VIRTUAL' +{{- if .After "12" }} + WHEN COL.IDENTITY_COLUMN = 'YES' THEN IC.GENERATION_TYPE || ' AS IDENTITY' +{{- end }} ELSE 'NEVER' END AS "GENERATED" FROM ALL_TAB_COLS COL @@ -42,10 +44,12 @@ SELECT COL.OWNER, ON COL.OWNER = CC.OWNER AND COL.TABLE_NAME = CC.TABLE_NAME AND COL.COLUMN_NAME = CC.COLUMN_NAME +{{- if .After "12" }} LEFT OUTER JOIN ALL_TAB_IDENTITY_COLS IC ON IC.OWNER = COL.OWNER AND IC.TABLE_NAME = COL.TABLE_NAME AND IC.COLUMN_NAME = COL.COLUMN_NAME +{{- end }} WHERE COL.HIDDEN_COLUMN = 'NO' -- skip system temporary table for materialized views log AND COL.TABLE_NAME NOT LIKE 'RUPD$_%' -- GitLab From e2d0535ad5fc41bdbc479562b7ed4446ebc03224 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Mon, 13 Oct 2025 09:05:19 +0200 Subject: [PATCH 11/17] ora: Skip inspecting identities on Oracle 11 --- internal/oracle/tables.go | 16 +++++++++------- 1 file changed, 9 insertions(+), 7 deletions(-) diff --git a/internal/oracle/tables.go b/internal/oracle/tables.go index 2e447cea3..fc53176fd 100644 --- a/internal/oracle/tables.go +++ b/internal/oracle/tables.go @@ -79,13 +79,15 @@ func (mdl *model) inspectTables(ctx context.Context) error { errs.Appendf("columns: %w", err) progress.Update(ctx, progress.Increment(1)) - mdl.Tables, _, err = fetch.CollectAndAssociateContext( - tctx, - "identities", mdl, - mdl.Tables, - catalog.RowToIdentity, - ) - errs.Appendf("identities: %w", err) + if mdl.After("12") { + mdl.Tables, _, err = fetch.CollectAndAssociateContext( + tctx, + "identities", mdl, + mdl.Tables, + catalog.RowToIdentity, + ) + errs.Appendf("identities: %w", err) + } progress.Update(ctx, progress.Increment(1)) }, func(ctx context.Context, _ chan error) { -- GitLab From 6248d42b85315ed28d896b5ff574fe6cee09186d Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Fri, 10 Oct 2025 14:18:55 +0200 Subject: [PATCH 12/17] ora: Port checks to Oracle 11 Include NOT NULL checks in checks because 11g only have LONG. Needs more investigation on whether to filter client side or something else. --- internal/oracle/sql/checks.sql | 9 +++++++-- 1 file changed, 7 insertions(+), 2 deletions(-) diff --git a/internal/oracle/sql/checks.sql b/internal/oracle/sql/checks.sql index d8bc91627..d72f1bcd9 100644 --- a/internal/oracle/sql/checks.sql +++ b/internal/oracle/sql/checks.sql @@ -5,7 +5,10 @@ SELECT CON.OWNER, FROM ALL_CONSTRAINTS CON JOIN ALL_TABLES TAB ON TAB.OWNER = CON.OWNER AND TAB.TABLE_NAME = CON.TABLE_NAME - WHERE CON.SEARCH_CONDITION_VC NOT LIKE '"%" IS NOT NULL' + LEFT OUTER JOIN ALL_MVIEWS MV + ON MV.OWNER = TAB.OWNER AND MV.MVIEW_NAME = TAB.TABLE_NAME + WHERE CON.OWNER IN ({{ template "stringarray.sql" .Schemas }}) + AND MV.OWNER IS NULL AND TAB.TEMPORARY = 'N' AND TAB.SECONDARY = 'N' AND TAB.NESTED = 'NO' @@ -14,5 +17,7 @@ SELECT CON.OWNER, AND CON.STATUS = 'ENABLED' AND CON.VALIDATED = 'VALIDATED' AND CON.INVALID IS NULL - AND CON.OWNER IN ({{ template "stringarray.sql" .Schemas }}) +{{- if .After "12" }} + AND CON.SEARCH_CONDITION_VC NOT LIKE '"%" IS NOT NULL' +{{- end }} ORDER BY 1, 2, 3 -- GitLab From 536bab46607e3d506565a5e02a96179c31b774da Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Mon, 13 Oct 2025 09:01:44 +0200 Subject: [PATCH 13/17] ora: Port index column inspection to Oracle 12 --- internal/oracle/sql/tables-indexes-columns.sql | 10 +++++++--- 1 file changed, 7 insertions(+), 3 deletions(-) diff --git a/internal/oracle/sql/tables-indexes-columns.sql b/internal/oracle/sql/tables-indexes-columns.sql index 866523e96..d2e82c7af 100644 --- a/internal/oracle/sql/tables-indexes-columns.sql +++ b/internal/oracle/sql/tables-indexes-columns.sql @@ -1,8 +1,12 @@ SELECT I.OWNER, I.TABLE_NAME, I.INDEX_NAME, NVL2(E.COLUMN_EXPRESSION, '', C.COLUMN_NAME), E.COLUMN_EXPRESSION AS EXPRESSION, CASE WHEN C.DESCEND = 'DESC' THEN 1 ELSE 0 END AS DESCEND, - COL.COLLATION AS COLLATION + {{ if .After "18" }}COL.COLLATION{{ else }}''{{ end }} AS COLLATION FROM ALL_INDEXES I + LEFT OUTER JOIN ALL_MVIEWS MV + ON MV.OWNER = I.TABLE_OWNER AND MV.MVIEW_NAME = I.TABLE_NAME + LEFT OUTER JOIN ALL_MVIEW_LOGS MVL + ON MVL.LOG_OWNER = I.TABLE_OWNER AND MVL.LOG_TABLE = I.TABLE_NAME JOIN ALL_IND_COLUMNS C ON I.OWNER = C.INDEX_OWNER AND I.INDEX_NAME = C.INDEX_NAME @@ -23,8 +27,8 @@ SELECT I.OWNER, I.TABLE_NAME, I.INDEX_NAME, NVL2(E.COLUMN_EXPRESSION, '', C.COLU ON E.INDEX_OWNER = I.OWNER AND E.INDEX_NAME = I.INDEX_NAME WHERE I.OWNER IN ({{ template "stringarray.sql" .Schemas }}) - AND (I.OWNER, I.TABLE_NAME) NOT IN (SELECT OWNER, MVIEW_NAME FROM ALL_MVIEWS) - AND (I.OWNER, I.TABLE_NAME) NOT IN (SELECT LOG_OWNER, LOG_TABLE FROM ALL_MVIEW_LOGS) + AND MV.OWNER IS NULL + AND MVL.LOG_OWNER IS NULL AND I.TABLE_NAME NOT LIKE 'RUPD$_%' AND I.TABLE_NAME NOT LIKE 'DR$_%' AND NVL(CONS.CONSTRAINT_TYPE, 'X') NOT IN ('P', 'U') -- GitLab From 2cef2f42ceb8618be30420270bfd6711853a475d Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Mon, 13 Oct 2025 09:09:29 +0200 Subject: [PATCH 14/17] ora: Port db link inspection to Oracle 12 --- internal/oracle/sql/dblinks.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/internal/oracle/sql/dblinks.sql b/internal/oracle/sql/dblinks.sql index b4a52b9d5..a21143ab8 100644 --- a/internal/oracle/sql/dblinks.sql +++ b/internal/oracle/sql/dblinks.sql @@ -1,5 +1,5 @@ SELECT OWNER, DB_LINK, USERNAME, HOST, - CASE VALID WHEN 'YES' THEN 1 ELSE 0 END AS VALID + {{ if .After "18" }}CASE VALID WHEN 'YES' THEN 1 ELSE 0 END{{ else }}1{{ end }} AS VALID FROM ALL_DB_LINKS WHERE OWNER IN ( 'PUBLIC', -- GitLab From 7de0a93290654007f48e9cd1404e21b33a5f651f Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Tue, 14 Oct 2025 17:37:56 +0200 Subject: [PATCH 15/17] docs: Document supported and unsupported versions --- docs/references/features.md | 48 +++++++++++++++++++++++++++---------- 1 file changed, 35 insertions(+), 13 deletions(-) diff --git a/docs/references/features.md b/docs/references/features.md index 95ff0f3ef..213f31e80 100644 --- a/docs/references/features.md +++ b/docs/references/features.md @@ -46,6 +46,28 @@ Here are implemented and planified features. [transqlate]: https://gitlab.com/dalibo/transqlate +## Versions + +Here are the implementattion status of the different systems and versions. + +| System | Support | +|----------------------------|------------| +| PostgreSQL 13 to 18 | ๐ŸŸข `FI` | +| Oracle 18c to 23ai | ๐ŸŸข `FI` | +| Oracle 11g and Oracle 12c | ๐ŸŸ  `PI` | +| Oracle 10g | โšช `NP` | +| Oracle 9i and below | `-` `WONT` | +| MySQL 8.4 | ๐ŸŸข `FI` | +| MariaDB | โšช `NP` | +| Microsoft SQL Server | โšช `NP` | +| Sybaseโ„ข SQL Anywhere | โšช `NP` | +| IBM Db2 | โšช `NP` | + +PostgreSQL is supported as a target system. +PostgreSQL is the only target system supported. +Other system are only supported as source of migration. + + ## Compatibility System-specific features will be implemented one system at a time. @@ -61,19 +83,19 @@ PostgreSQL Migrator fully implements tables conversion for limited systems, as it **inspects** the source catalog, **converts** columns data types, and **copies** data to target tables. -| | Oracleโ„ข Database | MySQL | Microsoft SQLโ€ฏServerยฎ | Sybaseโ„ข SQLโ€ฏAnywhere | IBMโ€ฏDb2 | -|-------------|:----------------:|:-----:|:---------------------:|:--------------------:|:-------:| -| Roles | ๐ŸŸ  `PI` | ๐Ÿ”ด `NI` | โšช `NP` | โšช `NP` | โšช `NP` | -| Privileges | ๐Ÿ”ด `NI` | ๐Ÿ”ด `NI` | โšช `NP` | โšช `NP` | โšช `NP` | -| Schemas | ๐ŸŸข `FI` | ๐ŸŸข `FI` | โšช `NP` | โšช `NP` | โšช `NP` | -| Sequences | ๐ŸŸข `FI` | - | โšช `NP` | โšช `NP` | โšช `NP` | -| Tables | ๐ŸŸข `FI` | ๐ŸŸข `FI` | โšช `NP` | โšช `NP` | โšช `NP` | -| Partitions | ๐ŸŸ  `PI` | ๐ŸŸข `FI` | โšช `NP` | โšช `NP` | โšช `NP` | -| Constraints | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | โšช `NP` | โšช `NP` | โšช `NP` | -| Indexes | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | โšช `NP` | โšช `NP` | โšช `NP` | -| Views | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | โšช `NP` | โšช `NP` | โšช `NP` | -| Routines | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | โšช `NP` | โšช `NP` | โšช `NP` | -| Triggers | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | โšช `NP` | โšช `NP` | โšช `NP` | +| | Oracleโ„ข Database | MySQL | +|-------------|:----------------:|:-----:| +| Roles | ๐ŸŸ  `PI` | ๐Ÿ”ด `NI` | +| Privileges | ๐Ÿ”ด `NI` | ๐Ÿ”ด `NI` | +| Schemas | ๐ŸŸข `FI` | ๐ŸŸข `FI` | +| Sequences | ๐ŸŸข `FI` | - | +| Tables | ๐ŸŸข `FI` | ๐ŸŸข `FI` | +| Partitions | ๐ŸŸ  `PI` | ๐ŸŸข `FI` | +| Constraints | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | +| Indexes | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | +| Views | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | +| Routines | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | +| Triggers | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | The following table describes specific SQL extension support. -- GitLab From fbd64512fcb7211cae1ae7d28601bf4c1d8af8ce Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Tue, 14 Oct 2025 17:39:09 +0200 Subject: [PATCH 16/17] docs: Document partial support for 11g and 12c --- docs/references/requirements.md | 18 +----------------- 1 file changed, 1 insertion(+), 17 deletions(-) diff --git a/docs/references/requirements.md b/docs/references/requirements.md index f26f0495c..8deb0130c 100644 --- a/docs/references/requirements.md +++ b/docs/references/requirements.md @@ -17,23 +17,7 @@ PostgreSQL Migrator refuses to allocate more than 1GB of RAM. ## Versions -=== "Oracle" - - From version 18c to 23ai. - - We plan to support Oracle 12R2. - - Oracle Database 8i is not supported and won't be. - -=== "MySQL" - - Version 8.4. - - MariaDB is not supported yet. - -=== "PostgreSQL" - - From version 13 ot 18. +See [Features](features.md#versions) page for supported versions of source system and PostgreSQL target. ## Privileges -- GitLab From daf64364136240c3832b9587058f539aa78858de Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=89tienne=20BERSAC?= Date: Wed, 22 Oct 2025 11:21:29 +0200 Subject: [PATCH 17/17] docs: Use tab to present features per system --- docs/references/features.md | 116 +++++++++++++++++++++++------------- 1 file changed, 73 insertions(+), 43 deletions(-) diff --git a/docs/references/features.md b/docs/references/features.md index 213f31e80..61462eec3 100644 --- a/docs/references/features.md +++ b/docs/references/features.md @@ -23,7 +23,7 @@ The following tables and matrices provide information about the progress of each | - | Not applicable | -## Features +## Migration Here are implemented and planified features. @@ -50,22 +50,40 @@ Here are implemented and planified features. Here are the implementattion status of the different systems and versions. -| System | Support | -|----------------------------|------------| -| PostgreSQL 13 to 18 | ๐ŸŸข `FI` | -| Oracle 18c to 23ai | ๐ŸŸข `FI` | -| Oracle 11g and Oracle 12c | ๐ŸŸ  `PI` | -| Oracle 10g | โšช `NP` | -| Oracle 9i and below | `-` `WONT` | -| MySQL 8.4 | ๐ŸŸข `FI` | -| MariaDB | โšช `NP` | -| Microsoft SQL Server | โšช `NP` | -| Sybaseโ„ข SQL Anywhere | โšช `NP` | -| IBM Db2 | โšช `NP` | -PostgreSQL is supported as a target system. -PostgreSQL is the only target system supported. -Other system are only supported as source of migration. +=== "Oracle" + + | System | Support | + |----------------------------|------------| + | Oracle 18c to 23ai | ๐ŸŸข `FI` | + | Oracle 11g and Oracle 12c | ๐ŸŸ  `PI` | + | Oracle 10g | โšช `NP` | + | Oracle 9i and below | `-` `WONT` | + +=== "MySQL" + + | System | Support | + |----------------------------|------------| + | MySQL 8.4 | ๐ŸŸข `FI` | + | MariaDB | โšช `NP` | + +=== "Other" + + | System | Support | + |----------------------------|------------| + | Microsoft SQL Server | โšช `NP` | + | Sybaseโ„ข SQL Anywhere | โšช `NP` | + | IBM Db2 | โšช `NP` | + +=== "PostgreSQL" + + | System | Support | + |----------------------------|------------| + | PostgreSQL 13 to 18 | ๐ŸŸข `FI` | + + PostgreSQL is supported as a target system. + PostgreSQL is the only target system supported. + Other system are only supported as source of migration. ## Compatibility @@ -83,33 +101,45 @@ PostgreSQL Migrator fully implements tables conversion for limited systems, as it **inspects** the source catalog, **converts** columns data types, and **copies** data to target tables. -| | Oracleโ„ข Database | MySQL | -|-------------|:----------------:|:-----:| -| Roles | ๐ŸŸ  `PI` | ๐Ÿ”ด `NI` | -| Privileges | ๐Ÿ”ด `NI` | ๐Ÿ”ด `NI` | -| Schemas | ๐ŸŸข `FI` | ๐ŸŸข `FI` | -| Sequences | ๐ŸŸข `FI` | - | -| Tables | ๐ŸŸข `FI` | ๐ŸŸข `FI` | -| Partitions | ๐ŸŸ  `PI` | ๐ŸŸข `FI` | -| Constraints | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | -| Indexes | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | -| Views | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | -| Routines | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | -| Triggers | ๐ŸŸ  `PI` | ๐ŸŸ  `PI` | - -The following table describes specific SQL extension support. - -| System | Features | Status | -|------------------|--------------------|--------| -| Oracleโ„ข Database | Synonyms | ๐ŸŸ  `PI` | -| | Packages | ๐ŸŸ  `PI` | -| | Types | ๐ŸŸ  `PI` | -| | Database Links | ๐ŸŸ  `PI` | -| | Materialized Views | ๐ŸŸ  `PI` | -| | Schedules | ๐ŸŸ  `PI` | -| MySQL | Autoincrement | ๐ŸŸ  `PI` | -| | Federated Tables | ๐ŸŸ  `PI` | -| | Events | ๐ŸŸ  `PI` | +=== "Oracle" + + | Features | Status | + |--------------------|--------| + | Roles | ๐ŸŸ  `PI` | + | Privileges | ๐Ÿ”ด `NI` | + | Schemas | ๐ŸŸข `FI` | + | Sequences | ๐ŸŸข `FI` | + | Tables | ๐ŸŸข `FI` | + | Partitions | ๐ŸŸ  `PI` | + | Constraints | ๐ŸŸ  `PI` | + | Indexes | ๐ŸŸ  `PI` | + | Views | ๐ŸŸ  `PI` | + | Routines | ๐ŸŸ  `PI` | + | Triggers | ๐ŸŸ  `PI` | + | Synonyms | ๐ŸŸ  `PI` | + | Packages | ๐ŸŸ  `PI` | + | Types | ๐ŸŸ  `PI` | + | Database Links | ๐ŸŸ  `PI` | + | Materialized Views | ๐ŸŸ  `PI` | + | Schedules | ๐ŸŸ  `PI` | + +=== "MySQL" + + | Features | Status | + |--------------------|--------| + | Roles | ๐Ÿ”ด `NI` | + | Privileges | ๐Ÿ”ด `NI` | + | Schemas | ๐ŸŸข `FI` | + | Tables | ๐ŸŸข `FI` | + | Partitions | ๐ŸŸข `FI` | + | Constraints | ๐ŸŸ  `PI` | + | Indexes | ๐ŸŸ  `PI` | + | Views | ๐ŸŸ  `PI` | + | Routines | ๐ŸŸ  `PI` | + | Triggers | ๐ŸŸ  `PI` | + | Autoincrement | ๐ŸŸ  `PI` | + | Federated Tables | ๐ŸŸ  `PI` | + | Events | ๐ŸŸ  `PI` | ## Contribute -- GitLab