Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
- description: Create tables with foreign key constraints
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "POSTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "USER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "TITLE",
"position": 3,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CONTENT",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "PUBLISHED_AT",
"position": 5,
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_USER_ID",
"expressions": [
"\"USER_ID\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"foreignKeys": [
{
"name": "FK_USER",
"columns": [
"USER_ID"
],
"referencedTable": "USERS",
"referencedColumns": [
"ID"
],
"onDelete": "CASCADE"
}
]
},
{
"name": "USERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "USERNAME",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMAIL",
"position": 3,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CREATED_AT",
"position": 4,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_USERNAME",
"expressions": [
"\"USERNAME\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "UK_EMAIL",
"expressions": [
"\"EMAIL\""
],
"descending": [
false
],
"type": "NORMAL",
"unique": true,
"visible": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
CREATE TABLE "USERS" (
"ID" NUMBER DEFAULT "TESTSCHEMA"."ISEQ$$_0000002".nextval NOT NULL,
"USERNAME" VARCHAR2(50 BYTE) NOT NULL,
"EMAIL" VARCHAR2(100 BYTE) NOT NULL,
"CREATED_AT" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE "USERS" ADD CONSTRAINT "SYS_C0000002" PRIMARY KEY (ID);
CREATE INDEX "IDX_USERNAME" ON "USERS" ("USERNAME" ASC);
CREATE UNIQUE INDEX "UK_EMAIL" ON "USERS" ("EMAIL" ASC);
CREATE TABLE "POSTS" (
"ID" NUMBER DEFAULT "TESTSCHEMA"."ISEQ$$_0000001".nextval NOT NULL,
"USER_ID" NUMBER NOT NULL,
"TITLE" VARCHAR2(200 BYTE) NOT NULL,
"CONTENT" CLOB,
"PUBLISHED_AT" TIMESTAMP(6)
);
ALTER TABLE "POSTS" ADD CONSTRAINT "SYS_C0000001" PRIMARY KEY (ID);
CREATE INDEX "IDX_USER_ID" ON "POSTS" ("USER_ID" ASC);
ALTER TABLE "POSTS" ADD CONSTRAINT "FK_USER" FOREIGN KEY ("USER_ID") REFERENCES "USERS" ("ID") ON DELETE CASCADE;
- description: Basic table operations with columns, constraints, and indexes
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "COMMENTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000003\".nextval",
"type": "NUMBER"
},
{
"name": "POST_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "USER_ID",
"position": 3,
"type": "NUMBER"
},
{
"name": "CONTENT",
"position": 4,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "CREATED_AT",
"position": 5,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_COMMENT_USER",
"columns": [
"USER_ID"
],
"referencedTable": "USERS",
"referencedColumns": [
"ID"
]
},
{
"name": "FK_POST",
"columns": [
"POST_ID"
],
"referencedTable": "POSTS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "POSTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "USER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "TITLE",
"position": 3,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CONTENT",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_POSTS_USER_ID",
"expressions": [
"\"USER_ID\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"foreignKeys": [
{
"name": "FK_USER",
"columns": [
"USER_ID"
],
"referencedTable": "USERS",
"referencedColumns": [
"ID"
]
}
],
"checkConstraints": [
{
"name": "CHECK_TITLE_LENGTH",
"expression": "LENGTH(TITLE) > 0"
}
]
},
{
"name": "USERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "USERNAME",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMAIL",
"position": 3,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CREATED_AT",
"position": 4,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "IS_ACTIVE",
"position": 5,
"default": "1",
"nullable": true,
"type": "NUMBER(1)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_USERS_EMAIL",
"expressions": [
"\"EMAIL\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "POSTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "USER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "TITLE",
"position": 3,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CONTENT",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_POSTS_USER_ID",
"expressions": [
"\"USER_ID\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"foreignKeys": [
{
"name": "FK_USER",
"columns": [
"USER_ID"
],
"referencedTable": "USERS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "USERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "USERNAME",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMAIL",
"position": 3,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CREATED_AT",
"position": 4,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |+
ALTER TABLE "COMMENTS" DROP CONSTRAINT "FK_COMMENT_USER";
ALTER TABLE "COMMENTS" DROP CONSTRAINT "FK_POST";
DROP TABLE "COMMENTS";
ALTER TABLE "POSTS" DROP CONSTRAINT "CHECK_TITLE_LENGTH";
DROP INDEX "IDX_USERS_EMAIL";
ALTER TABLE "USERS" DROP COLUMN "IS_ACTIVE";
- description: Views, functions, and materialized views
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "ORDERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "PRODUCT_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "QUANTITY",
"position": 3,
"type": "NUMBER"
},
{
"name": "TOTAL",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_PRODUCT",
"columns": [
"PRODUCT_ID"
],
"referencedTable": "PRODUCTS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "STOCK",
"position": 4,
"default": "0",
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
],
"views": [
{
"name": "PRODUCT_INVENTORY",
"definition": "SELECT\n P.ID,\n P.NAME,\n P.PRICE,\n P.STOCK,\n NVL(SUM(O.QUANTITY), 0) AS TOTAL_ORDERED\nFROM PRODUCTS P\nLEFT JOIN ORDERS O ON P.ID = O.PRODUCT_ID\nGROUP BY P.ID, P.NAME, P.PRICE, P.STOCK",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "ORDERS",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "PRODUCTS",
"column": "*"
}
],
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "STOCK",
"position": 4,
"nullable": true,
"type": "NUMBER"
},
{
"name": "TOTAL_ORDERED",
"position": 5,
"nullable": true,
"type": "NUMBER"
}
]
}
],
"functions": [
{
"name": "CALCULATE_ORDER_TOTAL",
"definition": "FUNCTION CALCULATE_ORDER_TOTAL(P_PRODUCT_ID NUMBER, P_QUANTITY NUMBER)\nRETURN NUMBER\nIS\n V_PRICE NUMBER(10, 2);\nBEGIN\n SELECT PRICE INTO V_PRICE FROM PRODUCTS WHERE ID = P_PRODUCT_ID;\n RETURN P_QUANTITY * V_PRICE;\nEXCEPTION\n WHEN NO_DATA_FOUND THEN\n RETURN 0;\nEND;"
}
],
"materializedViews": [
{
"name": "PRODUCT_STATS",
"definition": "SELECT\n PRODUCT_ID,\n COUNT(*) AS ORDER_COUNT,\n SUM(QUANTITY) AS TOTAL_QUANTITY,\n SUM(TOTAL) AS TOTAL_REVENUE\nFROM ORDERS\nGROUP BY PRODUCT_ID\n",
"comment": "snapshot table for snapshot TESTSCHEMA.PRODUCT_STATS",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "ORDERS",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "PRODUCT_STATS",
"column": "*"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "ORDERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "PRODUCT_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "QUANTITY",
"position": 3,
"type": "NUMBER"
},
{
"name": "TOTAL",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_PRODUCT",
"columns": [
"PRODUCT_ID"
],
"referencedTable": "PRODUCTS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "STOCK",
"position": 4,
"default": "0",
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
DROP FUNCTION "CALCULATE_ORDER_TOTAL";
DROP VIEW "PRODUCT_INVENTORY";
DROP MATERIALIZED VIEW "PRODUCT_STATS";
- description: Sequences and their usage in tables
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "ITEMS",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
},
{
"name": "SALES_ORDERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"ORDER_SEQ\".NEXTVAL",
"type": "NUMBER"
},
{
"name": "ITEM_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "QUANTITY",
"position": 3,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_ITEM",
"columns": [
"ITEM_ID"
],
"referencedTable": "ITEMS",
"referencedColumns": [
"ID"
]
}
]
}
],
"sequences": [
{
"name": "ORDER_SEQ"
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "ITEMS",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
DROP SEQUENCE "ORDER_SEQ";
ALTER TABLE "SALES_ORDERS" DROP CONSTRAINT "FK_ITEM";
DROP TABLE "SALES_ORDERS";
- description: Drop indexes and constraints from tables
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CATEGORY",
"position": 3,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
},
{
"name": "SUPPLIER_ID",
"position": 5,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CATEGORY",
"position": 3,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
},
{
"name": "SUPPLIER_ID",
"position": 5,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_PRODUCTS_CATEGORY",
"expressions": [
"\"CATEGORY\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_PRODUCTS_NAME",
"expressions": [
"\"NAME\""
],
"descending": [
false
],
"type": "NORMAL",
"unique": true,
"visible": true
},
{
"name": "IDX_PRODUCTS_PRICE",
"expressions": [
"\"PRICE\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"checkConstraints": [
{
"name": "CHECK_NAME_LENGTH",
"expression": "LENGTH(NAME) >= 3"
},
{
"name": "CHECK_PRICE_POSITIVE",
"expression": "PRICE > 0"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
CREATE INDEX "IDX_PRODUCTS_CATEGORY" ON "PRODUCTS" ("CATEGORY" ASC);
CREATE UNIQUE INDEX "IDX_PRODUCTS_NAME" ON "PRODUCTS" ("NAME" ASC);
CREATE INDEX "IDX_PRODUCTS_PRICE" ON "PRODUCTS" ("PRICE" ASC);
ALTER TABLE "PRODUCTS" ADD CONSTRAINT "CHECK_NAME_LENGTH" CHECK (LENGTH(NAME) >= 3);
ALTER TABLE "PRODUCTS" ADD CONSTRAINT "CHECK_PRICE_POSITIVE" CHECK (PRICE > 0);
- description: Drop views and functions that depend on tables
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "SALES",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "PRODUCT_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "SALE_AMOUNT",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "SALE_DATE",
"position": 4,
"type": "DATE"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "SALES",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "PRODUCT_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "SALE_AMOUNT",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "SALE_DATE",
"position": 4,
"type": "DATE"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
],
"views": [
{
"name": "MONTHLY_SALES",
"definition": "SELECT\n EXTRACT(YEAR FROM SALE_DATE) AS YEAR_VAL,\n EXTRACT(MONTH FROM SALE_DATE) AS MONTH_VAL,\n SUM(SALE_AMOUNT) AS TOTAL_SALES\nFROM SALES\nGROUP BY EXTRACT(YEAR FROM SALE_DATE), EXTRACT(MONTH FROM SALE_DATE)",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "SALES",
"column": "*"
}
],
"columns": [
{
"name": "YEAR_VAL",
"position": 1,
"nullable": true,
"type": "NUMBER"
},
{
"name": "MONTH_VAL",
"position": 2,
"nullable": true,
"type": "NUMBER"
},
{
"name": "TOTAL_SALES",
"position": 3,
"nullable": true,
"type": "NUMBER"
}
]
}
],
"functions": [
{
"name": "CALCULATE_DISCOUNT",
"definition": "FUNCTION CALCULATE_DISCOUNT(AMOUNT NUMBER)\nRETURN NUMBER\nIS\nBEGIN\n RETURN AMOUNT * 0.1;\nEND;"
},
{
"name": "GET_MONTHLY_TOTAL",
"definition": "FUNCTION GET_MONTHLY_TOTAL(YEAR_PARAM NUMBER, MONTH_PARAM NUMBER)\nRETURN NUMBER\nIS\n V_TOTAL NUMBER;\nBEGIN\n SELECT NVL(SUM(SALE_AMOUNT), 0) INTO V_TOTAL\n FROM SALES\n WHERE EXTRACT(YEAR FROM SALE_DATE) = YEAR_PARAM\n AND EXTRACT(MONTH FROM SALE_DATE) = MONTH_PARAM;\n\n RETURN V_TOTAL;\nEND;"
}
],
"materializedViews": [
{
"name": "TOP_PRODUCTS",
"definition": "SELECT\n PRODUCT_NAME,\n COUNT(*) AS SALE_COUNT,\n SUM(SALE_AMOUNT) AS TOTAL_REVENUE\nFROM SALES\nGROUP BY PRODUCT_NAME\n",
"comment": "snapshot table for snapshot TESTSCHEMA.TOP_PRODUCTS",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "SALES",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "TOP_PRODUCTS",
"column": "*"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
CREATE OR REPLACE FUNCTION CALCULATE_DISCOUNT(AMOUNT NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN AMOUNT * 0.1;
END;
CREATE OR REPLACE FUNCTION GET_MONTHLY_TOTAL(YEAR_PARAM NUMBER, MONTH_PARAM NUMBER)
RETURN NUMBER
IS
V_TOTAL NUMBER;
BEGIN
SELECT NVL(SUM(SALE_AMOUNT), 0) INTO V_TOTAL
FROM SALES
WHERE EXTRACT(YEAR FROM SALE_DATE) = YEAR_PARAM
AND EXTRACT(MONTH FROM SALE_DATE) = MONTH_PARAM;
RETURN V_TOTAL;
END;
CREATE VIEW "MONTHLY_SALES" AS SELECT
EXTRACT(YEAR FROM SALE_DATE) AS YEAR_VAL,
EXTRACT(MONTH FROM SALE_DATE) AS MONTH_VAL,
SUM(SALE_AMOUNT) AS TOTAL_SALES
FROM SALES
GROUP BY EXTRACT(YEAR FROM SALE_DATE), EXTRACT(MONTH FROM SALE_DATE);
CREATE MATERIALIZED VIEW "TOP_PRODUCTS" AS SELECT
PRODUCT_NAME,
COUNT(*) AS SALE_COUNT,
SUM(SALE_AMOUNT) AS TOTAL_REVENUE
FROM SALES
GROUP BY PRODUCT_NAME
;
COMMENT ON MATERIALIZED VIEW "TOP_PRODUCTS" IS 'snapshot table for snapshot TESTSCHEMA.TOP_PRODUCTS';
COMMENT ON MATERIALIZED VIEW "TOP_PRODUCTS" IS 'snapshot table for snapshot TESTSCHEMA.TOP_PRODUCTS';
- description: Alter table with column additions, type changes, and constraints
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "DESCRIPTION",
"position": 4,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "CATEGORY",
"position": 5,
"nullable": true,
"type": "VARCHAR2(30 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "IS_ACTIVE",
"position": 6,
"default": "1",
"nullable": true,
"type": "NUMBER(1)"
},
{
"name": "CREATED_AT",
"position": 7,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "STOCK_QUANTITY",
"position": 8,
"default": "0",
"nullable": true,
"type": "NUMBER"
},
{
"name": "WEIGHT",
"position": 9,
"nullable": true,
"type": "NUMBER(5,2)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_PRODUCTS_CATEGORY",
"expressions": [
"\"CATEGORY\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_PRODUCTS_CREATED_AT",
"expressions": [
"\"CREATED_AT\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_PRODUCTS_NAME_CATEGORY",
"expressions": [
"\"NAME\"",
"\"CATEGORY\""
],
"descending": [
false,
false
],
"type": "NORMAL",
"unique": true,
"visible": true
},
{
"name": "IDX_PRODUCTS_PRICE",
"expressions": [
"\"PRICE\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"checkConstraints": [
{
"name": "CHECK_PRICE_POSITIVE",
"expression": "PRICE > 0"
},
{
"name": "CHECK_STOCK_NON_NEGATIVE",
"expression": "STOCK_QUANTITY >= 0"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(8,2)"
},
{
"name": "DESCRIPTION",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "CATEGORY",
"position": 5,
"nullable": true,
"type": "VARCHAR2(30 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "IS_ACTIVE",
"position": 6,
"default": "1",
"nullable": true,
"type": "NUMBER(1)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_PRODUCTS_CATEGORY",
"expressions": [
"\"CATEGORY\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_PRODUCTS_PRICE",
"expressions": [
"\"PRICE\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
ALTER TABLE "PRODUCTS" DROP CONSTRAINT "CHECK_PRICE_POSITIVE";
ALTER TABLE "PRODUCTS" DROP CONSTRAINT "CHECK_STOCK_NON_NEGATIVE";
DROP INDEX "IDX_PRODUCTS_CREATED_AT";
DROP INDEX "IDX_PRODUCTS_NAME_CATEGORY";
ALTER TABLE "PRODUCTS" DROP COLUMN "CREATED_AT";
ALTER TABLE "PRODUCTS" DROP COLUMN "STOCK_QUANTITY";
ALTER TABLE "PRODUCTS" DROP COLUMN "WEIGHT";
ALTER TABLE "PRODUCTS" MODIFY "NAME" VARCHAR2(50 BYTE);
ALTER TABLE "PRODUCTS" MODIFY "PRICE" NUMBER(8,2);
ALTER TABLE "PRODUCTS" MODIFY "DESCRIPTION" NULL;
- description: Drop and recreate constraints with different definitions
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "AUTHORS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "SYS_C0000002",
"expressions": [
"EMAIL"
],
"unique": true,
"isConstraint": true
}
]
},
{
"name": "BOOKS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "TITLE",
"position": 2,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "AUTHOR_ID",
"position": 3,
"type": "NUMBER"
},
{
"name": "ISBN",
"position": 4,
"nullable": true,
"type": "VARCHAR2(20 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PUBLISHED_YEAR",
"position": 5,
"nullable": true,
"type": "NUMBER"
},
{
"name": "PRICE",
"position": 6,
"nullable": true,
"type": "NUMBER(8,2)"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "SYS_C0000004",
"expressions": [
"ISBN"
],
"unique": true,
"isConstraint": true
},
{
"name": "IDX_BOOKS_AUTHOR",
"expressions": [
"\"AUTHOR_ID\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_BOOKS_YEAR",
"expressions": [
"\"PUBLISHED_YEAR\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"foreignKeys": [
{
"name": "FK_AUTHOR_NEW",
"columns": [
"AUTHOR_ID"
],
"referencedTable": "AUTHORS",
"referencedColumns": [
"ID"
],
"onDelete": "CASCADE"
}
],
"checkConstraints": [
{
"name": "CHECK_PRICE_POSITIVE",
"expression": "PRICE > 0"
},
{
"name": "CHECK_TITLE_LENGTH",
"expression": "LENGTH(TITLE) >= 3"
},
{
"name": "CHECK_YEAR_EXTENDED",
"expression": "PUBLISHED_YEAR >= 1000 AND PUBLISHED_YEAR <= 2030"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "AUTHORS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "SYS_C0000002",
"expressions": [
"EMAIL"
],
"unique": true,
"isConstraint": true
}
]
},
{
"name": "BOOKS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "TITLE",
"position": 2,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "AUTHOR_ID",
"position": 3,
"type": "NUMBER"
},
{
"name": "ISBN",
"position": 4,
"nullable": true,
"type": "VARCHAR2(20 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PUBLISHED_YEAR",
"position": 5,
"nullable": true,
"type": "NUMBER"
},
{
"name": "PRICE",
"position": 6,
"nullable": true,
"type": "NUMBER(8,2)"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "SYS_C0000004",
"expressions": [
"ISBN"
],
"unique": true,
"isConstraint": true
},
{
"name": "IDX_BOOKS_AUTHOR",
"expressions": [
"\"AUTHOR_ID\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_BOOKS_YEAR",
"expressions": [
"\"PUBLISHED_YEAR\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"foreignKeys": [
{
"name": "FK_AUTHOR",
"columns": [
"AUTHOR_ID"
],
"referencedTable": "AUTHORS",
"referencedColumns": [
"ID"
]
}
],
"checkConstraints": [
{
"name": "CHECK_PRICE_POSITIVE",
"expression": "PRICE > 0"
},
{
"name": "CHECK_YEAR_VALID",
"expression": "PUBLISHED_YEAR >= 1000 AND PUBLISHED_YEAR <= 2100"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
ALTER TABLE "BOOKS" DROP CONSTRAINT "FK_AUTHOR_NEW";
ALTER TABLE "BOOKS" DROP CONSTRAINT "CHECK_TITLE_LENGTH";
ALTER TABLE "BOOKS" DROP CONSTRAINT "CHECK_YEAR_EXTENDED";
ALTER TABLE "BOOKS" ADD CONSTRAINT "CHECK_YEAR_VALID" CHECK (PUBLISHED_YEAR >= 1000 AND PUBLISHED_YEAR <= 2100);
ALTER TABLE "BOOKS" ADD CONSTRAINT "FK_AUTHOR" FOREIGN KEY ("AUTHOR_ID") REFERENCES "AUTHORS" ("ID");
- description: Drop sequence and dependent table
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "SIMPLE_LOG",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TRANSACTION_SEQ\".NEXTVAL",
"type": "NUMBER"
},
{
"name": "MESSAGE",
"position": 2,
"type": "CLOB",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
],
"sequences": [
{
"name": "TRANSACTION_SEQ"
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |+
CREATE SEQUENCE "TRANSACTION_SEQ";
CREATE TABLE "SIMPLE_LOG" (
"ID" NUMBER DEFAULT "TRANSACTION_SEQ".NEXTVAL NOT NULL,
"MESSAGE" CLOB NOT NULL
);
ALTER TABLE "SIMPLE_LOG" ADD CONSTRAINT "SYS_C0000001" PRIMARY KEY (ID);
- description: Complex view dependencies with multiple levels and materialized views
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "DEPARTMENTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "MANAGER_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_MANAGER",
"columns": [
"MANAGER_ID"
],
"referencedTable": "EMPLOYEES",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "EMPLOYEES",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "DEPARTMENT_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
},
{
"name": "SALARY",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_DEPT",
"columns": [
"DEPARTMENT_ID"
],
"referencedTable": "DEPARTMENTS",
"referencedColumns": [
"ID"
]
}
]
}
],
"views": [
{
"name": "DEPT_EMPLOYEE_COUNT",
"definition": "SELECT D.ID AS DEPT_ID, D.NAME AS DEPT_NAME, COUNT(E.ID) AS EMP_COUNT\nFROM DEPARTMENTS D\nLEFT JOIN EMPLOYEES E ON D.ID = E.DEPARTMENT_ID\nGROUP BY D.ID, D.NAME",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "DEPARTMENTS",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "EMPLOYEES",
"column": "*"
}
],
"columns": [
{
"name": "DEPT_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "DEPT_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMP_COUNT",
"position": 3,
"nullable": true,
"type": "NUMBER"
}
]
},
{
"name": "DEPT_MANAGER_SUMMARY",
"definition": "SELECT\n DS.DEPT_ID,\n DS.DEPT_NAME,\n DS.EMP_COUNT,\n DS.AVG_SALARY,\n M.NAME AS MANAGER_NAME\nFROM DEPT_SUMMARY DS\nJOIN DEPARTMENTS D ON DS.DEPT_ID = D.ID\nLEFT JOIN EMPLOYEES M ON D.MANAGER_ID = M.ID",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "DEPARTMENTS",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "DEPT_SUMMARY",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "EMPLOYEES",
"column": "*"
}
],
"columns": [
{
"name": "DEPT_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "DEPT_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMP_COUNT",
"position": 3,
"nullable": true,
"type": "NUMBER"
},
{
"name": "AVG_SALARY",
"position": 4,
"nullable": true,
"type": "NUMBER"
},
{
"name": "MANAGER_NAME",
"position": 5,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
]
},
{
"name": "DEPT_SUMMARY",
"definition": "SELECT\n DEC.DEPT_ID,\n DEC.DEPT_NAME,\n DEC.EMP_COUNT,\n NVL(AVG(E.SALARY), 0) AS AVG_SALARY\nFROM DEPT_EMPLOYEE_COUNT DEC\nLEFT JOIN EMPLOYEES E ON DEC.DEPT_ID = E.DEPARTMENT_ID\nGROUP BY DEC.DEPT_ID, DEC.DEPT_NAME, DEC.EMP_COUNT",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "DEPT_EMPLOYEE_COUNT",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "EMPLOYEES",
"column": "*"
}
],
"columns": [
{
"name": "DEPT_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "DEPT_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMP_COUNT",
"position": 3,
"nullable": true,
"type": "NUMBER"
},
{
"name": "AVG_SALARY",
"position": 4,
"nullable": true,
"type": "NUMBER"
}
]
}
],
"materializedViews": [
{
"name": "MV_DEPT_STATS",
"definition": "SELECT\n D.ID AS DEPT_ID,\n D.NAME AS DEPT_NAME,\n COUNT(E.ID) AS TOTAL_EMPLOYEES,\n NVL(SUM(E.SALARY), 0) AS TOTAL_PAYROLL,\n NVL(AVG(E.SALARY), 0) AS AVG_SALARY,\n NVL(MAX(E.SALARY), 0) AS MAX_SALARY,\n NVL(MIN(E.SALARY), 0) AS MIN_SALARY\nFROM DEPARTMENTS D\nLEFT JOIN EMPLOYEES E ON D.ID = E.DEPARTMENT_ID\nGROUP BY D.ID, D.NAME\n",
"comment": "snapshot table for snapshot TESTSCHEMA.MV_DEPT_STATS",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "DEPARTMENTS",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "EMPLOYEES",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "MV_DEPT_STATS",
"column": "*"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "DEPARTMENTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "MANAGER_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_MANAGER",
"columns": [
"MANAGER_ID"
],
"referencedTable": "EMPLOYEES",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "EMPLOYEES",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "DEPARTMENT_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
},
{
"name": "SALARY",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_DEPT",
"columns": [
"DEPARTMENT_ID"
],
"referencedTable": "DEPARTMENTS",
"referencedColumns": [
"ID"
]
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
DROP VIEW "DEPT_EMPLOYEE_COUNT";
DROP VIEW "DEPT_MANAGER_SUMMARY";
DROP VIEW "DEPT_SUMMARY";
DROP MATERIALIZED VIEW "MV_DEPT_STATS";
- description: Circular foreign key dependencies and triggers
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "CUSTOMERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PREFERRED_ORDER_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_PREFERRED_ORDER",
"columns": [
"PREFERRED_ORDER_ID"
],
"referencedTable": "ORDERS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "ORDERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "CUSTOMER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "ORDER_DATE",
"position": 3,
"default": "SYSDATE",
"nullable": true,
"type": "DATE"
},
{
"name": "TOTAL_AMOUNT",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_CUSTOMER",
"columns": [
"CUSTOMER_ID"
],
"referencedTable": "CUSTOMERS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "ORDER_ITEMS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000003\".nextval",
"type": "NUMBER"
},
{
"name": "ORDER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "PRODUCT_NAME",
"position": 3,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "QUANTITY",
"position": 4,
"type": "NUMBER"
},
{
"name": "UNIT_PRICE",
"position": 5,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_ORDER_ITEM",
"columns": [
"ORDER_ID"
],
"referencedTable": "ORDERS",
"referencedColumns": [
"ID"
],
"onDelete": "CASCADE"
}
],
"triggers": [
{
"name": "TRG_UPDATE_ORDER_TOTAL",
"event": "INSERT OR UPDATE OR DELETE",
"timing": "AFTER EACH ROW",
"body": "CREATE OR REPLACE TRIGGER TRG_UPDATE_ORDER_TOTAL\nAFTER INSERT OR UPDATE OR DELETE ON ORDER_ITEMS\nFOR EACH ROW\nDECLARE\n V_ORDER_ID NUMBER;\n V_TOTAL NUMBER(10, 2);\nBEGIN\n -- Get order ID\n IF INSERTING OR UPDATING THEN\n V_ORDER_ID := :NEW.ORDER_ID;\n ELSE\n V_ORDER_ID := :OLD.ORDER_ID;\n END IF;\n\n -- Calculate new total\n SELECT NVL(SUM(QUANTITY * UNIT_PRICE), 0) INTO V_TOTAL\n FROM ORDER_ITEMS\n WHERE ORDER_ID = V_ORDER_ID;\n\n -- Update order total\n UPDATE ORDERS SET TOTAL_AMOUNT = V_TOTAL WHERE ID = V_ORDER_ID;\nEND;"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "CUSTOMERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PREFERRED_ORDER_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
},
{
"name": "ORDERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "CUSTOMER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "ORDER_DATE",
"position": 3,
"default": "SYSDATE",
"nullable": true,
"type": "DATE"
},
{
"name": "TOTAL_AMOUNT",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |+
ALTER TABLE "CUSTOMERS" DROP CONSTRAINT "FK_PREFERRED_ORDER";
ALTER TABLE "ORDERS" DROP CONSTRAINT "FK_CUSTOMER";
ALTER TABLE "ORDER_ITEMS" DROP CONSTRAINT "FK_ORDER_ITEM";
DROP TABLE "ORDER_ITEMS";
- description: Advanced Oracle-specific indexes and complex constraints
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "SALES_DATA",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "SALE_DATE",
"position": 2,
"type": "DATE"
},
{
"name": "CUSTOMER_ID",
"position": 3,
"type": "NUMBER"
},
{
"name": "PRODUCT_ID",
"position": 4,
"type": "NUMBER"
},
{
"name": "AMOUNT",
"position": 5,
"type": "NUMBER(10,2)"
},
{
"name": "REGION",
"position": 6,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "UK_SALES_DATE_CUSTOMER",
"expressions": [
"SALE_DATE",
"CUSTOMER_ID",
"PRODUCT_ID"
],
"unique": true,
"isConstraint": true
},
{
"name": "IDX_SALES_MONTH_YEAR",
"expressions": [
"EXTRACT(YEAR FROM \"SALE_DATE\")",
"EXTRACT(MONTH FROM \"SALE_DATE\")"
],
"descending": [
false,
false
],
"type": "FUNCTION-BASED NORMAL",
"visible": true
},
{
"name": "IDX_SALES_REGION_AMOUNT",
"expressions": [
"\"REGION\"",
"\"AMOUNT\""
],
"descending": [
false,
true
],
"type": "FUNCTION-BASED NORMAL",
"visible": true
}
],
"checkConstraints": [
{
"name": "CHK_AMOUNT_RANGE",
"expression": "AMOUNT > 0 AND AMOUNT <= 999999.99"
},
{
"name": "CHK_REGION_VALID",
"expression": "REGION IN ('NORTH', 'SOUTH', 'EAST', 'WEST', 'CENTRAL')"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "SALES_DATA",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "SALE_DATE",
"position": 2,
"type": "DATE"
},
{
"name": "CUSTOMER_ID",
"position": 3,
"type": "NUMBER"
},
{
"name": "PRODUCT_ID",
"position": 4,
"type": "NUMBER"
},
{
"name": "AMOUNT",
"position": 5,
"type": "NUMBER(10,2)"
},
{
"name": "REGION",
"position": 6,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |+
ALTER TABLE "SALES_DATA" DROP CONSTRAINT "CHK_AMOUNT_RANGE";
ALTER TABLE "SALES_DATA" DROP CONSTRAINT "CHK_REGION_VALID";
DROP INDEX "IDX_SALES_MONTH_YEAR";
DROP INDEX "IDX_SALES_REGION_AMOUNT";
ALTER TABLE "SALES_DATA" DROP CONSTRAINT "UK_SALES_DATE_CUSTOMER";
- description: Stored procedures and functions with dependencies
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "AUDIT_LOG",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "TABLE_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "OPERATION",
"position": 3,
"type": "VARCHAR2(10 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "OLD_VALUES",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "NEW_VALUES",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "TIMESTAMP",
"position": 6,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "USER_NAME",
"position": 7,
"default": "USER",
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
],
"functions": [
{
"name": "CALCULATE_BUSINESS_DAYS",
"definition": "FUNCTION CALCULATE_BUSINESS_DAYS(\n P_START_DATE DATE,\n P_END_DATE DATE\n) RETURN NUMBER\nIS\n V_DAYS NUMBER := 0;\n V_CURRENT_DATE DATE := P_START_DATE;\nBEGIN\n WHILE V_CURRENT_DATE <= P_END_DATE LOOP\n -- Skip weekends (Saturday = 7, Sunday = 1)\n IF TO_CHAR(V_CURRENT_DATE, 'D') NOT IN ('1', '7') THEN\n V_DAYS := V_DAYS + 1;\n END IF;\n V_CURRENT_DATE := V_CURRENT_DATE + 1;\n END LOOP;\n\n RETURN V_DAYS;\nEND;"
},
{
"name": "GET_AUDIT_COUNT",
"definition": "FUNCTION GET_AUDIT_COUNT(P_TABLE_NAME VARCHAR2) RETURN NUMBER\nIS\n V_COUNT NUMBER;\nBEGIN\n SELECT COUNT(*) INTO V_COUNT\n FROM AUDIT_LOG\n WHERE TABLE_NAME = P_TABLE_NAME;\n\n RETURN V_COUNT;\nEND;"
}
],
"procedures": [
{
"name": "LOG_AUDIT",
"definition": "PROCEDURE LOG_AUDIT(\n P_TABLE_NAME VARCHAR2,\n P_OPERATION VARCHAR2,\n P_OLD_VALUES CLOB DEFAULT NULL,\n P_NEW_VALUES CLOB DEFAULT NULL\n)\nIS\nBEGIN\n INSERT INTO AUDIT_LOG (TABLE_NAME, OPERATION, OLD_VALUES, NEW_VALUES)\n VALUES (P_TABLE_NAME, P_OPERATION, P_OLD_VALUES, P_NEW_VALUES);\n COMMIT;\nEXCEPTION\n WHEN OTHERS THEN\n ROLLBACK;\n RAISE;\nEND;"
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "AUDIT_LOG",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "TABLE_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "OPERATION",
"position": 3,
"type": "VARCHAR2(10 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "OLD_VALUES",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "NEW_VALUES",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "TIMESTAMP",
"position": 6,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "USER_NAME",
"position": 7,
"default": "USER",
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
DROP FUNCTION "CALCULATE_BUSINESS_DAYS";
DROP FUNCTION "GET_AUDIT_COUNT";
DROP PROCEDURE "LOG_AUDIT";
- description: Edge cases for column operations and data type modifications
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "TEST_COLUMNS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "COL_VARCHAR",
"position": 2,
"nullable": true,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "COL_NUMBER",
"position": 3,
"nullable": true,
"type": "NUMBER(15,3)"
},
{
"name": "COL_DATE",
"position": 4,
"nullable": true,
"type": "DATE"
},
{
"name": "COL_NULLABLE",
"position": 5,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "COL_WITH_DEFAULT",
"position": 6,
"default": "'NEW_DEFAULT'",
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "COL_TIMESTAMP",
"position": 7,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "COL_BOOLEAN",
"position": 8,
"nullable": true,
"type": "NUMBER(1)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"checkConstraints": [
{
"name": "CHK_NUMBER_POSITIVE",
"expression": "COL_NUMBER > 0"
},
{
"name": "CHK_VARCHAR_LENGTH",
"expression": "LENGTH(COL_VARCHAR) >= 5"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "TEST_COLUMNS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "COL_VARCHAR",
"position": 2,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "COL_NUMBER",
"position": 3,
"nullable": true,
"type": "NUMBER(10,2)"
},
{
"name": "COL_DATE",
"position": 4,
"nullable": true,
"type": "DATE"
},
{
"name": "COL_CLOB",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "COL_NULLABLE",
"position": 6,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "COL_WITH_DEFAULT",
"position": 7,
"default": "'DEFAULT_VALUE'",
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
ALTER TABLE "TEST_COLUMNS" DROP CONSTRAINT "CHK_NUMBER_POSITIVE";
ALTER TABLE "TEST_COLUMNS" DROP CONSTRAINT "CHK_VARCHAR_LENGTH";
ALTER TABLE "TEST_COLUMNS" DROP COLUMN "COL_TIMESTAMP";
ALTER TABLE "TEST_COLUMNS" DROP COLUMN "COL_BOOLEAN";
ALTER TABLE "TEST_COLUMNS" ADD "COL_CLOB" CLOB;
ALTER TABLE "TEST_COLUMNS" MODIFY "COL_VARCHAR" VARCHAR2(50 BYTE);
ALTER TABLE "TEST_COLUMNS" MODIFY "COL_NUMBER" NUMBER(10,2);
ALTER TABLE "TEST_COLUMNS" MODIFY "COL_NULLABLE" NULL;
ALTER TABLE "TEST_COLUMNS" MODIFY "COL_WITH_DEFAULT" DEFAULT NULL;
ALTER TABLE "TEST_COLUMNS" MODIFY "COL_WITH_DEFAULT" DEFAULT 'DEFAULT_VALUE';
- description: 'Reverse: Drop tables with foreign key constraints'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "POSTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "USER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "TITLE",
"position": 3,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CONTENT",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "PUBLISHED_AT",
"position": 5,
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_USER_ID",
"expressions": [
"\"USER_ID\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"foreignKeys": [
{
"name": "FK_USER",
"columns": [
"USER_ID"
],
"referencedTable": "USERS",
"referencedColumns": [
"ID"
],
"onDelete": "CASCADE"
}
]
},
{
"name": "USERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "USERNAME",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMAIL",
"position": 3,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CREATED_AT",
"position": 4,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_USERNAME",
"expressions": [
"\"USERNAME\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "UK_EMAIL",
"expressions": [
"\"EMAIL\""
],
"descending": [
false
],
"type": "NORMAL",
"unique": true,
"visible": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{}
],
"serviceName": "FREEPDB1"
}
expected: |
ALTER TABLE "POSTS" DROP CONSTRAINT "FK_USER";
DROP TABLE "POSTS";
DROP TABLE "USERS";
- description: 'Reverse: Drop tables, columns, constraints, and indexes'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "POSTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "USER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "TITLE",
"position": 3,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CONTENT",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_POSTS_USER_ID",
"expressions": [
"\"USER_ID\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"foreignKeys": [
{
"name": "FK_USER",
"columns": [
"USER_ID"
],
"referencedTable": "USERS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "USERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000003\".nextval",
"type": "NUMBER"
},
{
"name": "USERNAME",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMAIL",
"position": 3,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CREATED_AT",
"position": 4,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "COMMENTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "POST_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "USER_ID",
"position": 3,
"type": "NUMBER"
},
{
"name": "CONTENT",
"position": 4,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "CREATED_AT",
"position": 5,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_COMMENT_USER",
"columns": [
"USER_ID"
],
"referencedTable": "USERS",
"referencedColumns": [
"ID"
]
},
{
"name": "FK_POST",
"columns": [
"POST_ID"
],
"referencedTable": "POSTS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "POSTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "USER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "TITLE",
"position": 3,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CONTENT",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_POSTS_USER_ID",
"expressions": [
"\"USER_ID\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"foreignKeys": [
{
"name": "FK_USER",
"columns": [
"USER_ID"
],
"referencedTable": "USERS",
"referencedColumns": [
"ID"
]
}
],
"checkConstraints": [
{
"name": "CHECK_TITLE_LENGTH",
"expression": "LENGTH(TITLE) > 0"
}
]
},
{
"name": "USERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000003\".nextval",
"type": "NUMBER"
},
{
"name": "USERNAME",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMAIL",
"position": 3,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CREATED_AT",
"position": 4,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "IS_ACTIVE",
"position": 5,
"default": "1",
"nullable": true,
"type": "NUMBER(1)"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_USERS_EMAIL",
"expressions": [
"\"EMAIL\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
ALTER TABLE "USERS" ADD "IS_ACTIVE" NUMBER(1) DEFAULT 1;
CREATE TABLE "COMMENTS" (
"ID" NUMBER DEFAULT "TESTSCHEMA"."ISEQ$$_0000001".nextval NOT NULL,
"POST_ID" NUMBER NOT NULL,
"USER_ID" NUMBER NOT NULL,
"CONTENT" CLOB NOT NULL,
"CREATED_AT" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE "COMMENTS" ADD CONSTRAINT "SYS_C0000001" PRIMARY KEY (ID);
ALTER TABLE "COMMENTS" ADD CONSTRAINT "FK_COMMENT_USER" FOREIGN KEY ("USER_ID") REFERENCES "USERS" ("ID");
ALTER TABLE "COMMENTS" ADD CONSTRAINT "FK_POST" FOREIGN KEY ("POST_ID") REFERENCES "POSTS" ("ID");
ALTER TABLE "POSTS" ADD CONSTRAINT "CHECK_TITLE_LENGTH" CHECK (LENGTH(TITLE) > 0);
CREATE INDEX "IDX_USERS_EMAIL" ON "USERS" ("EMAIL" ASC);
- description: 'Reverse: Drop views, functions, and materialized views'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "ORDERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "PRODUCT_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "QUANTITY",
"position": 3,
"type": "NUMBER"
},
{
"name": "TOTAL",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_PRODUCT",
"columns": [
"PRODUCT_ID"
],
"referencedTable": "PRODUCTS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "STOCK",
"position": 4,
"default": "0",
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "ORDERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "PRODUCT_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "QUANTITY",
"position": 3,
"type": "NUMBER"
},
{
"name": "TOTAL",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_PRODUCT",
"columns": [
"PRODUCT_ID"
],
"referencedTable": "PRODUCTS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "STOCK",
"position": 4,
"default": "0",
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
],
"views": [
{
"name": "PRODUCT_INVENTORY",
"definition": "SELECT\n P.ID,\n P.NAME,\n P.PRICE,\n P.STOCK,\n NVL(SUM(O.QUANTITY), 0) AS TOTAL_ORDERED\nFROM PRODUCTS P\nLEFT JOIN ORDERS O ON P.ID = O.PRODUCT_ID\nGROUP BY P.ID, P.NAME, P.PRICE, P.STOCK",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "ORDERS",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "PRODUCTS",
"column": "*"
}
],
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "STOCK",
"position": 4,
"nullable": true,
"type": "NUMBER"
},
{
"name": "TOTAL_ORDERED",
"position": 5,
"nullable": true,
"type": "NUMBER"
}
]
}
],
"functions": [
{
"name": "CALCULATE_ORDER_TOTAL",
"definition": "FUNCTION CALCULATE_ORDER_TOTAL(P_PRODUCT_ID NUMBER, P_QUANTITY NUMBER)\nRETURN NUMBER\nIS\n V_PRICE NUMBER(10, 2);\nBEGIN\n SELECT PRICE INTO V_PRICE FROM PRODUCTS WHERE ID = P_PRODUCT_ID;\n RETURN P_QUANTITY * V_PRICE;\nEXCEPTION\n WHEN NO_DATA_FOUND THEN\n RETURN 0;\nEND;"
}
],
"materializedViews": [
{
"name": "PRODUCT_STATS",
"definition": "SELECT\n PRODUCT_ID,\n COUNT(*) AS ORDER_COUNT,\n SUM(QUANTITY) AS TOTAL_QUANTITY,\n SUM(TOTAL) AS TOTAL_REVENUE\nFROM ORDERS\nGROUP BY PRODUCT_ID\n",
"comment": "snapshot table for snapshot TESTSCHEMA.PRODUCT_STATS",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "ORDERS",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "PRODUCT_STATS",
"column": "*"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
CREATE OR REPLACE FUNCTION CALCULATE_ORDER_TOTAL(P_PRODUCT_ID NUMBER, P_QUANTITY NUMBER)
RETURN NUMBER
IS
V_PRICE NUMBER(10, 2);
BEGIN
SELECT PRICE INTO V_PRICE FROM PRODUCTS WHERE ID = P_PRODUCT_ID;
RETURN P_QUANTITY * V_PRICE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;
CREATE VIEW "PRODUCT_INVENTORY" AS SELECT
P.ID,
P.NAME,
P.PRICE,
P.STOCK,
NVL(SUM(O.QUANTITY), 0) AS TOTAL_ORDERED
FROM PRODUCTS P
LEFT JOIN ORDERS O ON P.ID = O.PRODUCT_ID
GROUP BY P.ID, P.NAME, P.PRICE, P.STOCK;
CREATE MATERIALIZED VIEW "PRODUCT_STATS" AS SELECT
PRODUCT_ID,
COUNT(*) AS ORDER_COUNT,
SUM(QUANTITY) AS TOTAL_QUANTITY,
SUM(TOTAL) AS TOTAL_REVENUE
FROM ORDERS
GROUP BY PRODUCT_ID
;
COMMENT ON MATERIALIZED VIEW "PRODUCT_STATS" IS 'snapshot table for snapshot TESTSCHEMA.PRODUCT_STATS';
COMMENT ON MATERIALIZED VIEW "PRODUCT_STATS" IS 'snapshot table for snapshot TESTSCHEMA.PRODUCT_STATS';
- description: 'Reverse: Drop sequences and tables using them'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "ITEMS",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "ITEMS",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
},
{
"name": "SALES_ORDERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"ORDER_SEQ\".NEXTVAL",
"type": "NUMBER"
},
{
"name": "ITEM_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "QUANTITY",
"position": 3,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_ITEM",
"columns": [
"ITEM_ID"
],
"referencedTable": "ITEMS",
"referencedColumns": [
"ID"
]
}
]
}
],
"sequences": [
{
"name": "ORDER_SEQ"
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
CREATE SEQUENCE "ORDER_SEQ";
CREATE TABLE "SALES_ORDERS" (
"ID" NUMBER DEFAULT "ORDER_SEQ".NEXTVAL NOT NULL,
"ITEM_ID" NUMBER NOT NULL,
"QUANTITY" NUMBER NOT NULL
);
ALTER TABLE "SALES_ORDERS" ADD CONSTRAINT "SYS_C0000002" PRIMARY KEY (ID);
ALTER TABLE "SALES_ORDERS" ADD CONSTRAINT "FK_ITEM" FOREIGN KEY ("ITEM_ID") REFERENCES "ITEMS" ("ID");
- description: 'Reverse: Create indexes and constraints on tables'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CATEGORY",
"position": 3,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
},
{
"name": "SUPPLIER_ID",
"position": 5,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_PRODUCTS_CATEGORY",
"expressions": [
"\"CATEGORY\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_PRODUCTS_NAME",
"expressions": [
"\"NAME\""
],
"descending": [
false
],
"type": "NORMAL",
"unique": true,
"visible": true
},
{
"name": "IDX_PRODUCTS_PRICE",
"expressions": [
"\"PRICE\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"checkConstraints": [
{
"name": "CHECK_NAME_LENGTH",
"expression": "LENGTH(NAME) >= 3"
},
{
"name": "CHECK_PRICE_POSITIVE",
"expression": "PRICE > 0"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CATEGORY",
"position": 3,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
},
{
"name": "SUPPLIER_ID",
"position": 5,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |+
ALTER TABLE "PRODUCTS" DROP CONSTRAINT "CHECK_NAME_LENGTH";
ALTER TABLE "PRODUCTS" DROP CONSTRAINT "CHECK_PRICE_POSITIVE";
DROP INDEX "IDX_PRODUCTS_CATEGORY";
DROP INDEX "IDX_PRODUCTS_NAME";
DROP INDEX "IDX_PRODUCTS_PRICE";
- description: 'Reverse: Create views and functions that depend on tables'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "SALES",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "PRODUCT_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "SALE_AMOUNT",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "SALE_DATE",
"position": 4,
"type": "DATE"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
],
"views": [
{
"name": "MONTHLY_SALES",
"definition": "SELECT\n EXTRACT(YEAR FROM SALE_DATE) AS YEAR_VAL,\n EXTRACT(MONTH FROM SALE_DATE) AS MONTH_VAL,\n SUM(SALE_AMOUNT) AS TOTAL_SALES\nFROM SALES\nGROUP BY EXTRACT(YEAR FROM SALE_DATE), EXTRACT(MONTH FROM SALE_DATE)",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "SALES",
"column": "*"
}
],
"columns": [
{
"name": "YEAR_VAL",
"position": 1,
"nullable": true,
"type": "NUMBER"
},
{
"name": "MONTH_VAL",
"position": 2,
"nullable": true,
"type": "NUMBER"
},
{
"name": "TOTAL_SALES",
"position": 3,
"nullable": true,
"type": "NUMBER"
}
]
}
],
"functions": [
{
"name": "CALCULATE_DISCOUNT",
"definition": "FUNCTION CALCULATE_DISCOUNT(AMOUNT NUMBER)\nRETURN NUMBER\nIS\nBEGIN\n RETURN AMOUNT * 0.1;\nEND;"
},
{
"name": "GET_MONTHLY_TOTAL",
"definition": "FUNCTION GET_MONTHLY_TOTAL(YEAR_PARAM NUMBER, MONTH_PARAM NUMBER)\nRETURN NUMBER\nIS\n V_TOTAL NUMBER;\nBEGIN\n SELECT NVL(SUM(SALE_AMOUNT), 0) INTO V_TOTAL\n FROM SALES\n WHERE EXTRACT(YEAR FROM SALE_DATE) = YEAR_PARAM\n AND EXTRACT(MONTH FROM SALE_DATE) = MONTH_PARAM;\n\n RETURN V_TOTAL;\nEND;"
}
],
"materializedViews": [
{
"name": "TOP_PRODUCTS",
"definition": "SELECT\n PRODUCT_NAME,\n COUNT(*) AS SALE_COUNT,\n SUM(SALE_AMOUNT) AS TOTAL_REVENUE\nFROM SALES\nGROUP BY PRODUCT_NAME\n",
"comment": "snapshot table for snapshot TESTSCHEMA.TOP_PRODUCTS",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "SALES",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "TOP_PRODUCTS",
"column": "*"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "SALES",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "PRODUCT_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "SALE_AMOUNT",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "SALE_DATE",
"position": 4,
"type": "DATE"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
DROP FUNCTION "CALCULATE_DISCOUNT";
DROP FUNCTION "GET_MONTHLY_TOTAL";
DROP VIEW "MONTHLY_SALES";
DROP MATERIALIZED VIEW "TOP_PRODUCTS";
- description: 'Reverse: Remove columns, constraints and revert type changes'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(8,2)"
},
{
"name": "DESCRIPTION",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "CATEGORY",
"position": 5,
"nullable": true,
"type": "VARCHAR2(30 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "IS_ACTIVE",
"position": 6,
"default": "1",
"nullable": true,
"type": "NUMBER(1)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_PRODUCTS_CATEGORY",
"expressions": [
"\"CATEGORY\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_PRODUCTS_PRICE",
"expressions": [
"\"PRICE\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "DESCRIPTION",
"position": 4,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "CATEGORY",
"position": 5,
"nullable": true,
"type": "VARCHAR2(30 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "IS_ACTIVE",
"position": 6,
"default": "1",
"nullable": true,
"type": "NUMBER(1)"
},
{
"name": "CREATED_AT",
"position": 7,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "STOCK_QUANTITY",
"position": 8,
"default": "0",
"nullable": true,
"type": "NUMBER"
},
{
"name": "WEIGHT",
"position": 9,
"nullable": true,
"type": "NUMBER(5,2)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_PRODUCTS_CATEGORY",
"expressions": [
"\"CATEGORY\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_PRODUCTS_CREATED_AT",
"expressions": [
"\"CREATED_AT\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_PRODUCTS_NAME_CATEGORY",
"expressions": [
"\"NAME\"",
"\"CATEGORY\""
],
"descending": [
false,
false
],
"type": "NORMAL",
"unique": true,
"visible": true
},
{
"name": "IDX_PRODUCTS_PRICE",
"expressions": [
"\"PRICE\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"checkConstraints": [
{
"name": "CHECK_PRICE_POSITIVE",
"expression": "PRICE > 0"
},
{
"name": "CHECK_STOCK_NON_NEGATIVE",
"expression": "STOCK_QUANTITY >= 0"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
ALTER TABLE "PRODUCTS" ADD "CREATED_AT" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE "PRODUCTS" ADD "STOCK_QUANTITY" NUMBER DEFAULT 0;
ALTER TABLE "PRODUCTS" ADD "WEIGHT" NUMBER(5,2);
ALTER TABLE "PRODUCTS" MODIFY "NAME" VARCHAR2(100 BYTE);
ALTER TABLE "PRODUCTS" MODIFY "PRICE" NUMBER(10,2);
ALTER TABLE "PRODUCTS" MODIFY "DESCRIPTION" NOT NULL;
CREATE INDEX "IDX_PRODUCTS_CREATED_AT" ON "PRODUCTS" ("CREATED_AT" ASC);
CREATE UNIQUE INDEX "IDX_PRODUCTS_NAME_CATEGORY" ON "PRODUCTS" ("NAME" ASC, "CATEGORY" ASC);
ALTER TABLE "PRODUCTS" ADD CONSTRAINT "CHECK_PRICE_POSITIVE" CHECK (PRICE > 0);
ALTER TABLE "PRODUCTS" ADD CONSTRAINT "CHECK_STOCK_NON_NEGATIVE" CHECK (STOCK_QUANTITY >= 0);
- description: 'Reverse: Revert constraint modifications'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "AUTHORS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "SYS_C0000002",
"expressions": [
"EMAIL"
],
"unique": true,
"isConstraint": true
}
]
},
{
"name": "BOOKS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "TITLE",
"position": 2,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "AUTHOR_ID",
"position": 3,
"type": "NUMBER"
},
{
"name": "ISBN",
"position": 4,
"nullable": true,
"type": "VARCHAR2(20 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PUBLISHED_YEAR",
"position": 5,
"nullable": true,
"type": "NUMBER"
},
{
"name": "PRICE",
"position": 6,
"nullable": true,
"type": "NUMBER(8,2)"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "SYS_C0000004",
"expressions": [
"ISBN"
],
"unique": true,
"isConstraint": true
},
{
"name": "IDX_BOOKS_AUTHOR",
"expressions": [
"\"AUTHOR_ID\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_BOOKS_YEAR",
"expressions": [
"\"PUBLISHED_YEAR\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"foreignKeys": [
{
"name": "FK_AUTHOR",
"columns": [
"AUTHOR_ID"
],
"referencedTable": "AUTHORS",
"referencedColumns": [
"ID"
]
}
],
"checkConstraints": [
{
"name": "CHECK_PRICE_POSITIVE",
"expression": "PRICE > 0"
},
{
"name": "CHECK_YEAR_VALID",
"expression": "PUBLISHED_YEAR >= 1000 AND PUBLISHED_YEAR <= 2100"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "AUTHORS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "SYS_C0000002",
"expressions": [
"EMAIL"
],
"unique": true,
"isConstraint": true
}
]
},
{
"name": "BOOKS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "TITLE",
"position": 2,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "AUTHOR_ID",
"position": 3,
"type": "NUMBER"
},
{
"name": "ISBN",
"position": 4,
"nullable": true,
"type": "VARCHAR2(20 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PUBLISHED_YEAR",
"position": 5,
"nullable": true,
"type": "NUMBER"
},
{
"name": "PRICE",
"position": 6,
"nullable": true,
"type": "NUMBER(8,2)"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "SYS_C0000004",
"expressions": [
"ISBN"
],
"unique": true,
"isConstraint": true
},
{
"name": "IDX_BOOKS_AUTHOR",
"expressions": [
"\"AUTHOR_ID\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_BOOKS_YEAR",
"expressions": [
"\"PUBLISHED_YEAR\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"foreignKeys": [
{
"name": "FK_AUTHOR_NEW",
"columns": [
"AUTHOR_ID"
],
"referencedTable": "AUTHORS",
"referencedColumns": [
"ID"
],
"onDelete": "CASCADE"
}
],
"checkConstraints": [
{
"name": "CHECK_TITLE_LENGTH",
"expression": "LENGTH(TITLE) >= 3"
},
{
"name": "CHECK_YEAR_EXTENDED",
"expression": "PUBLISHED_YEAR >= 1000 AND PUBLISHED_YEAR <= 2030"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
ALTER TABLE "BOOKS" DROP CONSTRAINT "FK_AUTHOR";
ALTER TABLE "BOOKS" DROP CONSTRAINT "CHECK_PRICE_POSITIVE";
ALTER TABLE "BOOKS" DROP CONSTRAINT "CHECK_YEAR_VALID";
ALTER TABLE "BOOKS" ADD CONSTRAINT "CHECK_TITLE_LENGTH" CHECK (LENGTH(TITLE) >= 3);
ALTER TABLE "BOOKS" ADD CONSTRAINT "CHECK_YEAR_EXTENDED" CHECK (PUBLISHED_YEAR >= 1000 AND PUBLISHED_YEAR <= 2030);
ALTER TABLE "BOOKS" ADD CONSTRAINT "FK_AUTHOR_NEW" FOREIGN KEY ("AUTHOR_ID") REFERENCES "AUTHORS" ("ID") ON DELETE CASCADE;
- description: 'Reverse: Create sequence and dependent table'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "SIMPLE_LOG",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TRANSACTION_SEQ\".NEXTVAL",
"type": "NUMBER"
},
{
"name": "MESSAGE",
"position": 2,
"type": "CLOB",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
],
"sequences": [
{
"name": "TRANSACTION_SEQ"
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{}
],
"serviceName": "FREEPDB1"
}
expected: |
DROP TABLE "SIMPLE_LOG";
DROP SEQUENCE "TRANSACTION_SEQ";
- description: 'Reverse: Drop complex view dependencies'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "DEPARTMENTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "MANAGER_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_MANAGER",
"columns": [
"MANAGER_ID"
],
"referencedTable": "EMPLOYEES",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "EMPLOYEES",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "DEPARTMENT_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
},
{
"name": "SALARY",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_DEPT",
"columns": [
"DEPARTMENT_ID"
],
"referencedTable": "DEPARTMENTS",
"referencedColumns": [
"ID"
]
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "DEPARTMENTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "MANAGER_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_MANAGER",
"columns": [
"MANAGER_ID"
],
"referencedTable": "EMPLOYEES",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "EMPLOYEES",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "DEPARTMENT_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
},
{
"name": "SALARY",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_DEPT",
"columns": [
"DEPARTMENT_ID"
],
"referencedTable": "DEPARTMENTS",
"referencedColumns": [
"ID"
]
}
]
}
],
"views": [
{
"name": "DEPT_EMPLOYEE_COUNT",
"definition": "SELECT D.ID AS DEPT_ID, D.NAME AS DEPT_NAME, COUNT(E.ID) AS EMP_COUNT\nFROM DEPARTMENTS D\nLEFT JOIN EMPLOYEES E ON D.ID = E.DEPARTMENT_ID\nGROUP BY D.ID, D.NAME",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "DEPARTMENTS",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "EMPLOYEES",
"column": "*"
}
],
"columns": [
{
"name": "DEPT_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "DEPT_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMP_COUNT",
"position": 3,
"nullable": true,
"type": "NUMBER"
}
]
},
{
"name": "DEPT_MANAGER_SUMMARY",
"definition": "SELECT\n DS.DEPT_ID,\n DS.DEPT_NAME,\n DS.EMP_COUNT,\n DS.AVG_SALARY,\n M.NAME AS MANAGER_NAME\nFROM DEPT_SUMMARY DS\nJOIN DEPARTMENTS D ON DS.DEPT_ID = D.ID\nLEFT JOIN EMPLOYEES M ON D.MANAGER_ID = M.ID",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "DEPARTMENTS",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "DEPT_SUMMARY",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "EMPLOYEES",
"column": "*"
}
],
"columns": [
{
"name": "DEPT_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "DEPT_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMP_COUNT",
"position": 3,
"nullable": true,
"type": "NUMBER"
},
{
"name": "AVG_SALARY",
"position": 4,
"nullable": true,
"type": "NUMBER"
},
{
"name": "MANAGER_NAME",
"position": 5,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
]
},
{
"name": "DEPT_SUMMARY",
"definition": "SELECT\n DEC.DEPT_ID,\n DEC.DEPT_NAME,\n DEC.EMP_COUNT,\n NVL(AVG(E.SALARY), 0) AS AVG_SALARY\nFROM DEPT_EMPLOYEE_COUNT DEC\nLEFT JOIN EMPLOYEES E ON DEC.DEPT_ID = E.DEPARTMENT_ID\nGROUP BY DEC.DEPT_ID, DEC.DEPT_NAME, DEC.EMP_COUNT",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "DEPT_EMPLOYEE_COUNT",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "EMPLOYEES",
"column": "*"
}
],
"columns": [
{
"name": "DEPT_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "DEPT_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMP_COUNT",
"position": 3,
"nullable": true,
"type": "NUMBER"
},
{
"name": "AVG_SALARY",
"position": 4,
"nullable": true,
"type": "NUMBER"
}
]
}
],
"materializedViews": [
{
"name": "MV_DEPT_STATS",
"definition": "SELECT\n D.ID AS DEPT_ID,\n D.NAME AS DEPT_NAME,\n COUNT(E.ID) AS TOTAL_EMPLOYEES,\n NVL(SUM(E.SALARY), 0) AS TOTAL_PAYROLL,\n NVL(AVG(E.SALARY), 0) AS AVG_SALARY,\n NVL(MAX(E.SALARY), 0) AS MAX_SALARY,\n NVL(MIN(E.SALARY), 0) AS MIN_SALARY\nFROM DEPARTMENTS D\nLEFT JOIN EMPLOYEES E ON D.ID = E.DEPARTMENT_ID\nGROUP BY D.ID, D.NAME\n",
"comment": "snapshot table for snapshot TESTSCHEMA.MV_DEPT_STATS",
"dependencyColumns": [
{
"schema": "TESTSCHEMA",
"table": "DEPARTMENTS",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "EMPLOYEES",
"column": "*"
},
{
"schema": "TESTSCHEMA",
"table": "MV_DEPT_STATS",
"column": "*"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
CREATE VIEW "DEPT_EMPLOYEE_COUNT" AS SELECT D.ID AS DEPT_ID, D.NAME AS DEPT_NAME, COUNT(E.ID) AS EMP_COUNT
FROM DEPARTMENTS D
LEFT JOIN EMPLOYEES E ON D.ID = E.DEPARTMENT_ID
GROUP BY D.ID, D.NAME;
CREATE MATERIALIZED VIEW "MV_DEPT_STATS" AS SELECT
D.ID AS DEPT_ID,
D.NAME AS DEPT_NAME,
COUNT(E.ID) AS TOTAL_EMPLOYEES,
NVL(SUM(E.SALARY), 0) AS TOTAL_PAYROLL,
NVL(AVG(E.SALARY), 0) AS AVG_SALARY,
NVL(MAX(E.SALARY), 0) AS MAX_SALARY,
NVL(MIN(E.SALARY), 0) AS MIN_SALARY
FROM DEPARTMENTS D
LEFT JOIN EMPLOYEES E ON D.ID = E.DEPARTMENT_ID
GROUP BY D.ID, D.NAME
;
COMMENT ON MATERIALIZED VIEW "MV_DEPT_STATS" IS 'snapshot table for snapshot TESTSCHEMA.MV_DEPT_STATS';
CREATE VIEW "DEPT_SUMMARY" AS SELECT
DEC.DEPT_ID,
DEC.DEPT_NAME,
DEC.EMP_COUNT,
NVL(AVG(E.SALARY), 0) AS AVG_SALARY
FROM DEPT_EMPLOYEE_COUNT DEC
LEFT JOIN EMPLOYEES E ON DEC.DEPT_ID = E.DEPARTMENT_ID
GROUP BY DEC.DEPT_ID, DEC.DEPT_NAME, DEC.EMP_COUNT;
CREATE VIEW "DEPT_MANAGER_SUMMARY" AS SELECT
DS.DEPT_ID,
DS.DEPT_NAME,
DS.EMP_COUNT,
DS.AVG_SALARY,
M.NAME AS MANAGER_NAME
FROM DEPT_SUMMARY DS
JOIN DEPARTMENTS D ON DS.DEPT_ID = D.ID
LEFT JOIN EMPLOYEES M ON D.MANAGER_ID = M.ID;
COMMENT ON MATERIALIZED VIEW "MV_DEPT_STATS" IS 'snapshot table for snapshot TESTSCHEMA.MV_DEPT_STATS';
- description: 'Reverse: Remove circular foreign key dependencies and triggers'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "CUSTOMERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PREFERRED_ORDER_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
},
{
"name": "ORDERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "CUSTOMER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "ORDER_DATE",
"position": 3,
"default": "SYSDATE",
"nullable": true,
"type": "DATE"
},
{
"name": "TOTAL_AMOUNT",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "CUSTOMERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PREFERRED_ORDER_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_PREFERRED_ORDER",
"columns": [
"PREFERRED_ORDER_ID"
],
"referencedTable": "ORDERS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "ORDERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "CUSTOMER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "ORDER_DATE",
"position": 3,
"default": "SYSDATE",
"nullable": true,
"type": "DATE"
},
{
"name": "TOTAL_AMOUNT",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_CUSTOMER",
"columns": [
"CUSTOMER_ID"
],
"referencedTable": "CUSTOMERS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "ORDER_ITEMS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000003\".nextval",
"type": "NUMBER"
},
{
"name": "ORDER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "PRODUCT_NAME",
"position": 3,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "QUANTITY",
"position": 4,
"type": "NUMBER"
},
{
"name": "UNIT_PRICE",
"position": 5,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"foreignKeys": [
{
"name": "FK_ORDER_ITEM",
"columns": [
"ORDER_ID"
],
"referencedTable": "ORDERS",
"referencedColumns": [
"ID"
],
"onDelete": "CASCADE"
}
],
"triggers": [
{
"name": "TRG_UPDATE_ORDER_TOTAL",
"event": "INSERT OR UPDATE OR DELETE",
"timing": "AFTER EACH ROW",
"body": "CREATE OR REPLACE TRIGGER TRG_UPDATE_ORDER_TOTAL\nAFTER INSERT OR UPDATE OR DELETE ON ORDER_ITEMS\nFOR EACH ROW\nDECLARE\n V_ORDER_ID NUMBER;\n V_TOTAL NUMBER(10, 2);\nBEGIN\n -- Get order ID\n IF INSERTING OR UPDATING THEN\n V_ORDER_ID := :NEW.ORDER_ID;\n ELSE\n V_ORDER_ID := :OLD.ORDER_ID;\n END IF;\n\n -- Calculate new total\n SELECT NVL(SUM(QUANTITY * UNIT_PRICE), 0) INTO V_TOTAL\n FROM ORDER_ITEMS\n WHERE ORDER_ID = V_ORDER_ID;\n\n -- Update order total\n UPDATE ORDERS SET TOTAL_AMOUNT = V_TOTAL WHERE ID = V_ORDER_ID;\nEND;"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
CREATE TABLE "ORDER_ITEMS" (
"ID" NUMBER DEFAULT "TESTSCHEMA"."ISEQ$$_0000003".nextval NOT NULL,
"ORDER_ID" NUMBER NOT NULL,
"PRODUCT_NAME" VARCHAR2(100 BYTE) NOT NULL,
"QUANTITY" NUMBER NOT NULL,
"UNIT_PRICE" NUMBER(10,2) NOT NULL
);
ALTER TABLE "ORDER_ITEMS" ADD CONSTRAINT "SYS_C0000003" PRIMARY KEY (ID);
CREATE OR REPLACE TRIGGER TRG_UPDATE_ORDER_TOTAL
AFTER INSERT OR UPDATE OR DELETE ON ORDER_ITEMS
FOR EACH ROW
DECLARE
V_ORDER_ID NUMBER;
V_TOTAL NUMBER(10, 2);
BEGIN
-- Get order ID
IF INSERTING OR UPDATING THEN
V_ORDER_ID := :NEW.ORDER_ID;
ELSE
V_ORDER_ID := :OLD.ORDER_ID;
END IF;
-- Calculate new total
SELECT NVL(SUM(QUANTITY * UNIT_PRICE), 0) INTO V_TOTAL
FROM ORDER_ITEMS
WHERE ORDER_ID = V_ORDER_ID;
-- Update order total
UPDATE ORDERS SET TOTAL_AMOUNT = V_TOTAL WHERE ID = V_ORDER_ID;
END;
ALTER TABLE "ORDER_ITEMS" ADD CONSTRAINT "FK_ORDER_ITEM" FOREIGN KEY ("ORDER_ID") REFERENCES "ORDERS" ("ID") ON DELETE CASCADE;
ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "FK_PREFERRED_ORDER" FOREIGN KEY ("PREFERRED_ORDER_ID") REFERENCES "ORDERS" ("ID");
ALTER TABLE "ORDERS" ADD CONSTRAINT "FK_CUSTOMER" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "CUSTOMERS" ("ID");
- description: 'Reverse: Remove advanced indexes and complex constraints'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "SALES_DATA",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "SALE_DATE",
"position": 2,
"type": "DATE"
},
{
"name": "CUSTOMER_ID",
"position": 3,
"type": "NUMBER"
},
{
"name": "PRODUCT_ID",
"position": 4,
"type": "NUMBER"
},
{
"name": "AMOUNT",
"position": 5,
"type": "NUMBER(10,2)"
},
{
"name": "REGION",
"position": 6,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "SALES_DATA",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "SALE_DATE",
"position": 2,
"type": "DATE"
},
{
"name": "CUSTOMER_ID",
"position": 3,
"type": "NUMBER"
},
{
"name": "PRODUCT_ID",
"position": 4,
"type": "NUMBER"
},
{
"name": "AMOUNT",
"position": 5,
"type": "NUMBER(10,2)"
},
{
"name": "REGION",
"position": 6,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "UK_SALES_DATE_CUSTOMER",
"expressions": [
"SALE_DATE",
"CUSTOMER_ID",
"PRODUCT_ID"
],
"unique": true,
"isConstraint": true
},
{
"name": "IDX_SALES_MONTH_YEAR",
"expressions": [
"EXTRACT(YEAR FROM \"SALE_DATE\")",
"EXTRACT(MONTH FROM \"SALE_DATE\")"
],
"descending": [
false,
false
],
"type": "FUNCTION-BASED NORMAL",
"visible": true
},
{
"name": "IDX_SALES_REGION_AMOUNT",
"expressions": [
"\"REGION\"",
"\"AMOUNT\""
],
"descending": [
false,
true
],
"type": "FUNCTION-BASED NORMAL",
"visible": true
}
],
"checkConstraints": [
{
"name": "CHK_AMOUNT_RANGE",
"expression": "AMOUNT > 0 AND AMOUNT <= 999999.99"
},
{
"name": "CHK_REGION_VALID",
"expression": "REGION IN ('NORTH', 'SOUTH', 'EAST', 'WEST', 'CENTRAL')"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
CREATE INDEX "IDX_SALES_MONTH_YEAR" ON "SALES_DATA" (EXTRACT(YEAR FROM "SALE_DATE") ASC, EXTRACT(MONTH FROM "SALE_DATE") ASC);
CREATE INDEX "IDX_SALES_REGION_AMOUNT" ON "SALES_DATA" ("REGION" ASC, "AMOUNT" DESC);
ALTER TABLE "SALES_DATA" ADD CONSTRAINT "UK_SALES_DATE_CUSTOMER" UNIQUE (SALE_DATE, CUSTOMER_ID, PRODUCT_ID);
ALTER TABLE "SALES_DATA" ADD CONSTRAINT "CHK_AMOUNT_RANGE" CHECK (AMOUNT > 0 AND AMOUNT <= 999999.99);
ALTER TABLE "SALES_DATA" ADD CONSTRAINT "CHK_REGION_VALID" CHECK (REGION IN ('NORTH', 'SOUTH', 'EAST', 'WEST', 'CENTRAL'));
- description: 'Reverse: Remove stored procedures and functions'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "AUDIT_LOG",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "TABLE_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "OPERATION",
"position": 3,
"type": "VARCHAR2(10 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "OLD_VALUES",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "NEW_VALUES",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "TIMESTAMP",
"position": 6,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "USER_NAME",
"position": 7,
"default": "USER",
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "AUDIT_LOG",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "TABLE_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "OPERATION",
"position": 3,
"type": "VARCHAR2(10 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "OLD_VALUES",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "NEW_VALUES",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "TIMESTAMP",
"position": 6,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "USER_NAME",
"position": 7,
"default": "USER",
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
],
"functions": [
{
"name": "CALCULATE_BUSINESS_DAYS",
"definition": "FUNCTION CALCULATE_BUSINESS_DAYS(\n P_START_DATE DATE,\n P_END_DATE DATE\n) RETURN NUMBER\nIS\n V_DAYS NUMBER := 0;\n V_CURRENT_DATE DATE := P_START_DATE;\nBEGIN\n WHILE V_CURRENT_DATE <= P_END_DATE LOOP\n -- Skip weekends (Saturday = 7, Sunday = 1)\n IF TO_CHAR(V_CURRENT_DATE, 'D') NOT IN ('1', '7') THEN\n V_DAYS := V_DAYS + 1;\n END IF;\n V_CURRENT_DATE := V_CURRENT_DATE + 1;\n END LOOP;\n\n RETURN V_DAYS;\nEND;"
},
{
"name": "GET_AUDIT_COUNT",
"definition": "FUNCTION GET_AUDIT_COUNT(P_TABLE_NAME VARCHAR2) RETURN NUMBER\nIS\n V_COUNT NUMBER;\nBEGIN\n SELECT COUNT(*) INTO V_COUNT\n FROM AUDIT_LOG\n WHERE TABLE_NAME = P_TABLE_NAME;\n\n RETURN V_COUNT;\nEND;"
}
],
"procedures": [
{
"name": "LOG_AUDIT",
"definition": "PROCEDURE LOG_AUDIT(\n P_TABLE_NAME VARCHAR2,\n P_OPERATION VARCHAR2,\n P_OLD_VALUES CLOB DEFAULT NULL,\n P_NEW_VALUES CLOB DEFAULT NULL\n)\nIS\nBEGIN\n INSERT INTO AUDIT_LOG (TABLE_NAME, OPERATION, OLD_VALUES, NEW_VALUES)\n VALUES (P_TABLE_NAME, P_OPERATION, P_OLD_VALUES, P_NEW_VALUES);\n COMMIT;\nEXCEPTION\n WHEN OTHERS THEN\n ROLLBACK;\n RAISE;\nEND;"
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |+
CREATE OR REPLACE FUNCTION CALCULATE_BUSINESS_DAYS(
P_START_DATE DATE,
P_END_DATE DATE
) RETURN NUMBER
IS
V_DAYS NUMBER := 0;
V_CURRENT_DATE DATE := P_START_DATE;
BEGIN
WHILE V_CURRENT_DATE <= P_END_DATE LOOP
-- Skip weekends (Saturday = 7, Sunday = 1)
IF TO_CHAR(V_CURRENT_DATE, 'D') NOT IN ('1', '7') THEN
V_DAYS := V_DAYS + 1;
END IF;
V_CURRENT_DATE := V_CURRENT_DATE + 1;
END LOOP;
RETURN V_DAYS;
END;
CREATE OR REPLACE FUNCTION GET_AUDIT_COUNT(P_TABLE_NAME VARCHAR2) RETURN NUMBER
IS
V_COUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO V_COUNT
FROM AUDIT_LOG
WHERE TABLE_NAME = P_TABLE_NAME;
RETURN V_COUNT;
END;
CREATE OR REPLACE PROCEDURE LOG_AUDIT(
P_TABLE_NAME VARCHAR2,
P_OPERATION VARCHAR2,
P_OLD_VALUES CLOB DEFAULT NULL,
P_NEW_VALUES CLOB DEFAULT NULL
)
IS
BEGIN
INSERT INTO AUDIT_LOG (TABLE_NAME, OPERATION, OLD_VALUES, NEW_VALUES)
VALUES (P_TABLE_NAME, P_OPERATION, P_OLD_VALUES, P_NEW_VALUES);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
- description: 'Reverse: Revert edge case column operations'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "TEST_COLUMNS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "COL_VARCHAR",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "COL_NUMBER",
"position": 3,
"nullable": true,
"type": "NUMBER(10,2)"
},
{
"name": "COL_DATE",
"position": 4,
"nullable": true,
"type": "DATE"
},
{
"name": "COL_NULLABLE",
"position": 5,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "COL_WITH_DEFAULT",
"position": 6,
"default": "'DEFAULT_VALUE'",
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "COL_CLOB",
"position": 7,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "TEST_COLUMNS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "COL_VARCHAR",
"position": 2,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "COL_NUMBER",
"position": 3,
"nullable": true,
"type": "NUMBER(15,3)"
},
{
"name": "COL_DATE",
"position": 4,
"nullable": true,
"type": "DATE"
},
{
"name": "COL_NULLABLE",
"position": 5,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "COL_WITH_DEFAULT",
"position": 6,
"default": "'NEW_DEFAULT'",
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "COL_TIMESTAMP",
"position": 7,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "COL_BOOLEAN",
"position": 8,
"nullable": true,
"type": "NUMBER(1)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"checkConstraints": [
{
"name": "CHK_NUMBER_POSITIVE",
"expression": "COL_NUMBER > 0"
},
{
"name": "CHK_VARCHAR_LENGTH",
"expression": "LENGTH(COL_VARCHAR) >= 5"
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
ALTER TABLE "TEST_COLUMNS" DROP COLUMN "COL_CLOB";
ALTER TABLE "TEST_COLUMNS" ADD "COL_TIMESTAMP" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE "TEST_COLUMNS" ADD "COL_BOOLEAN" NUMBER(1);
ALTER TABLE "TEST_COLUMNS" MODIFY "COL_VARCHAR" VARCHAR2(200 BYTE);
ALTER TABLE "TEST_COLUMNS" MODIFY "COL_NUMBER" NUMBER(15,3);
ALTER TABLE "TEST_COLUMNS" MODIFY "COL_NULLABLE" NOT NULL;
ALTER TABLE "TEST_COLUMNS" MODIFY "COL_WITH_DEFAULT" DEFAULT NULL;
ALTER TABLE "TEST_COLUMNS" MODIFY "COL_WITH_DEFAULT" DEFAULT 'NEW_DEFAULT';
ALTER TABLE "TEST_COLUMNS" ADD CONSTRAINT "CHK_NUMBER_POSITIVE" CHECK (COL_NUMBER > 0);
ALTER TABLE "TEST_COLUMNS" ADD CONSTRAINT "CHK_VARCHAR_LENGTH" CHECK (LENGTH(COL_VARCHAR) >= 5);
- description: Add comprehensive comments to tables and columns using Oracle COMMENT syntax
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "CUSTOMERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER",
"comment": "Primary key for customer records"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Full name of the customer"
},
{
"name": "EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Customer email address (unique)"
},
{
"name": "PHONE",
"position": 4,
"nullable": true,
"type": "VARCHAR2(20 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Contact phone number"
},
{
"name": "ADDRESS",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP",
"comment": "Full mailing address with multiple lines"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_CUSTOMERS_EMAIL",
"expressions": [
"\"EMAIL\""
],
"descending": [
false
],
"type": "NORMAL",
"unique": true,
"visible": true
}
],
"comment": "Customer information and contact details"
},
{
"name": "ORDERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000003\".nextval",
"type": "NUMBER",
"comment": "Unique order identifier"
},
{
"name": "CUSTOMER_ID",
"position": 2,
"type": "NUMBER",
"comment": "Reference to customer who placed the order"
},
{
"name": "PRODUCT_ID",
"position": 3,
"type": "NUMBER",
"comment": "Reference to ordered product"
},
{
"name": "ORDER_DATE",
"position": 4,
"default": "SYSDATE",
"nullable": true,
"type": "DATE",
"comment": "Date when the order was placed"
},
{
"name": "QUANTITY",
"position": 5,
"type": "NUMBER",
"comment": "Number of items ordered"
},
{
"name": "TOTAL_AMOUNT",
"position": 6,
"nullable": true,
"type": "NUMBER(12,2)",
"comment": "Total order amount including taxes"
},
{
"name": "STATUS",
"position": 7,
"default": "'PENDING'",
"nullable": true,
"type": "VARCHAR2(20 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Current order status (PENDING, SHIPPED, DELIVERED, CANCELLED)"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Customer orders linking products and customers",
"foreignKeys": [
{
"name": "FK_ORDER_CUSTOMER",
"columns": [
"CUSTOMER_ID"
],
"referencedTable": "CUSTOMERS",
"referencedColumns": [
"ID"
]
},
{
"name": "FK_ORDER_PRODUCT",
"columns": [
"PRODUCT_ID"
],
"referencedTable": "PRODUCTS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER",
"comment": "Unique product identifier (auto-generated)"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Product name - must be unique and descriptive"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(10,2)",
"comment": "Product price in USD with 2 decimal places"
},
{
"name": "DESCRIPTION",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP",
"comment": "Detailed product description with HTML formatting support"
},
{
"name": "CATEGORY",
"position": 5,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Product category for filtering and organization"
},
{
"name": "CREATED_AT",
"position": 6,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)",
"comment": "Timestamp when the product was added to catalog"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_PRODUCTS_CATEGORY",
"expressions": [
"\"CATEGORY\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"comment": "Product catalog table containing all product information"
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "CUSTOMERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PHONE",
"position": 4,
"nullable": true,
"type": "VARCHAR2(20 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "ADDRESS",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_CUSTOMERS_EMAIL",
"expressions": [
"\"EMAIL\""
],
"descending": [
false
],
"type": "NORMAL",
"unique": true,
"visible": true
}
]
},
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "DESCRIPTION",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "CATEGORY",
"position": 5,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CREATED_AT",
"position": 6,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_PRODUCTS_CATEGORY",
"expressions": [
"\"CATEGORY\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
ALTER TABLE "ORDERS" DROP CONSTRAINT "FK_ORDER_CUSTOMER";
ALTER TABLE "ORDERS" DROP CONSTRAINT "FK_ORDER_PRODUCT";
DROP TABLE "ORDERS";
COMMENT ON TABLE "CUSTOMERS" IS '';
COMMENT ON COLUMN "CUSTOMERS"."ID" IS '';
COMMENT ON COLUMN "CUSTOMERS"."NAME" IS '';
COMMENT ON COLUMN "CUSTOMERS"."EMAIL" IS '';
COMMENT ON COLUMN "CUSTOMERS"."PHONE" IS '';
COMMENT ON COLUMN "CUSTOMERS"."ADDRESS" IS '';
COMMENT ON COLUMN "CUSTOMERS"."ID" IS '';
COMMENT ON COLUMN "CUSTOMERS"."NAME" IS '';
COMMENT ON COLUMN "CUSTOMERS"."EMAIL" IS '';
COMMENT ON COLUMN "CUSTOMERS"."PHONE" IS '';
COMMENT ON COLUMN "CUSTOMERS"."ADDRESS" IS '';
COMMENT ON TABLE "PRODUCTS" IS '';
COMMENT ON COLUMN "PRODUCTS"."ID" IS '';
COMMENT ON COLUMN "PRODUCTS"."NAME" IS '';
COMMENT ON COLUMN "PRODUCTS"."PRICE" IS '';
COMMENT ON COLUMN "PRODUCTS"."DESCRIPTION" IS '';
COMMENT ON COLUMN "PRODUCTS"."CATEGORY" IS '';
COMMENT ON COLUMN "PRODUCTS"."CREATED_AT" IS '';
COMMENT ON COLUMN "PRODUCTS"."ID" IS '';
COMMENT ON COLUMN "PRODUCTS"."NAME" IS '';
COMMENT ON COLUMN "PRODUCTS"."PRICE" IS '';
COMMENT ON COLUMN "PRODUCTS"."DESCRIPTION" IS '';
COMMENT ON COLUMN "PRODUCTS"."CATEGORY" IS '';
COMMENT ON COLUMN "PRODUCTS"."CREATED_AT" IS '';
- description: Modify existing comments and remove comments using empty string
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "INVENTORY",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER",
"comment": "Primary key for inventory records"
},
{
"name": "PRODUCT_CODE",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Updated: Standardized product SKU following new naming convention"
},
{
"name": "STOCK_LEVEL",
"position": 3,
"type": "NUMBER",
"comment": "Updated: Real-time stock quantity with automated updates"
},
{
"name": "MIN_STOCK",
"position": 4,
"default": "10",
"nullable": true,
"type": "NUMBER",
"comment": "Updated: Dynamic minimum threshold based on sales velocity"
},
{
"name": "MAX_STOCK",
"position": 5,
"default": "1000",
"nullable": true,
"type": "NUMBER"
},
{
"name": "WAREHOUSE_ID",
"position": 6,
"nullable": true,
"type": "NUMBER"
},
{
"name": "LAST_UPDATED",
"position": 7,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)",
"comment": "Timestamp of last inventory update or modification"
},
{
"name": "REORDER_POINT",
"position": 8,
"default": "0",
"nullable": true,
"type": "NUMBER",
"comment": "Calculated reorder point based on lead time and demand"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Updated inventory management system with enhanced tracking capabilities"
},
{
"name": "SUPPLIERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Supplier company name"
},
{
"name": "CONTACT_EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "RATING",
"position": 4,
"nullable": true,
"type": "NUMBER(2,1)",
"comment": "Supplier performance rating (1.0 to 5.0 scale)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "INVENTORY",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER",
"comment": "Primary key for inventory records"
},
{
"name": "PRODUCT_CODE",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Product identification code"
},
{
"name": "STOCK_LEVEL",
"position": 3,
"type": "NUMBER",
"comment": "Current stock quantity"
},
{
"name": "MIN_STOCK",
"position": 4,
"default": "10",
"nullable": true,
"type": "NUMBER",
"comment": "Minimum stock threshold"
},
{
"name": "MAX_STOCK",
"position": 5,
"default": "1000",
"nullable": true,
"type": "NUMBER",
"comment": "Maximum stock capacity"
},
{
"name": "WAREHOUSE_ID",
"position": 6,
"nullable": true,
"type": "NUMBER",
"comment": "Warehouse location identifier"
},
{
"name": "LAST_UPDATED",
"position": 7,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Initial inventory tracking table"
},
{
"name": "SUPPLIERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Supplier company name"
},
{
"name": "CONTACT_EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Primary contact email"
},
{
"name": "RATING",
"position": 4,
"nullable": true,
"type": "NUMBER(2,1)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Supplier information table"
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
ALTER TABLE "INVENTORY" DROP COLUMN "REORDER_POINT";
COMMENT ON TABLE "INVENTORY" IS 'Initial inventory tracking table';
COMMENT ON COLUMN "INVENTORY"."PRODUCT_CODE" IS 'Product identification code';
COMMENT ON COLUMN "INVENTORY"."STOCK_LEVEL" IS 'Current stock quantity';
COMMENT ON COLUMN "INVENTORY"."MIN_STOCK" IS 'Minimum stock threshold';
COMMENT ON COLUMN "INVENTORY"."MAX_STOCK" IS 'Maximum stock capacity';
COMMENT ON COLUMN "INVENTORY"."WAREHOUSE_ID" IS 'Warehouse location identifier';
COMMENT ON COLUMN "INVENTORY"."LAST_UPDATED" IS '';
COMMENT ON COLUMN "INVENTORY"."PRODUCT_CODE" IS 'Product identification code';
COMMENT ON COLUMN "INVENTORY"."STOCK_LEVEL" IS 'Current stock quantity';
COMMENT ON COLUMN "INVENTORY"."MIN_STOCK" IS 'Minimum stock threshold';
COMMENT ON COLUMN "INVENTORY"."MAX_STOCK" IS 'Maximum stock capacity';
COMMENT ON COLUMN "INVENTORY"."WAREHOUSE_ID" IS 'Warehouse location identifier';
COMMENT ON COLUMN "INVENTORY"."LAST_UPDATED" IS '';
COMMENT ON TABLE "SUPPLIERS" IS 'Supplier information table';
COMMENT ON COLUMN "SUPPLIERS"."CONTACT_EMAIL" IS 'Primary contact email';
COMMENT ON COLUMN "SUPPLIERS"."RATING" IS '';
COMMENT ON COLUMN "SUPPLIERS"."CONTACT_EMAIL" IS 'Primary contact email';
COMMENT ON COLUMN "SUPPLIERS"."RATING" IS '';
- description: Test comments with special characters, quotes, Unicode, multi-line text, and complex content
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "COMPLEX_TYPES",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "XML_DATA",
"position": 2,
"nullable": true,
"type": "XMLTYPE",
"comment": "XMLType column for structured XML storage\nExample: <?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<root><item id=\"1\" name=\"test\">Content</item></root>"
},
{
"name": "TIMESTAMP_TZ",
"position": 3,
"default": "SYSTIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6) WITH TIME ZONE",
"comment": "Timestamp with timezone: YYYY-MM-DD HH24:MI:SS.FF TZR\nDefault: SYSTIMESTAMP (system timestamp with session timezone)"
},
{
"name": "INTERVAL_DS",
"position": 4,
"nullable": true,
"type": "INTERVAL DAY(2) TO SECOND(6)",
"comment": "Interval Day to Second: +/-DD HH:MI:SS.FFFFFF\nRange: +/-999999999 days, precision up to nanoseconds"
},
{
"name": "RAW_DATA",
"position": 5,
"nullable": true,
"type": "RAW(100)"
},
{
"name": "LONG_RAW_DATA",
"position": 6,
"nullable": true,
"type": "LONG RAW"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Advanced data types testing table\nSupports: XMLType, Timestamps with timezone, Intervals, RAW data\nPerformance: Optimized for OLTP workloads @ 10K+ TPS\nSecurity: Row-level security enabled, audit trails maintained"
},
{
"name": "MULTILANG_CONTENT",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "TITLE_EN",
"position": 2,
"nullable": true,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "English title field"
},
{
"name": "TITLE_ES",
"position": 3,
"nullable": true,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "T\u00edtulo en espa\u00f1ol"
},
{
"name": "TITLE_ZH",
"position": 4,
"nullable": true,
"type": "NVARCHAR2(400)",
"collation": "USING_NLS_COMP",
"comment": "\u4e2d\u6587\u6807\u9898\u5b57\u6bb5 - \u652f\u6301\u7b80\u4f53\u548c\u7e41\u4f53\u4e2d\u6587"
},
{
"name": "CONTENT_TEXT",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP",
"comment": "Content field supporting:\n1. HTML tags: <b>bold</b>, <i>italic</i>, <a href=\"http://example.com\">links</a>\n2. Markdown: **bold**, *italic*, [link](url), backtick-code-backtick\n3. Special symbols: \u00a9 \u00ae \u2122 \u00a7 \u00b6 \u2020 \u2021 \u2022 \u2026 \u2030 \u2032 \u2033\n4. Emojis: \ud83d\ude00 \ud83d\ude03 \ud83d\ude04 \ud83d\ude01 \ud83d\ude80 \u2b50 \ud83c\udf89 \ud83d\udcaf\n5. Mathematical: \u2200x\u2208\u211d: x\u00b2 \u2265 0, \u2211(i=1 to n) i = n(n+1)/2"
},
{
"name": "TAGS",
"position": 6,
"nullable": true,
"type": "VARCHAR2(1000 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "XML tags: <tag type=\"category\" value=\"news\"/><tag type=\"priority\" value=\"high\"/>End of XML"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Multilingual content: English, Espa\u00f1ol, \u4e2d\u6587, \u0627\u0644\u0639\u0631\u0628\u064a\u0629, \u0420\u0443\u0441\u0441\u043a\u0438\u0439, \u65e5\u672c\u8a9e"
},
{
"name": "SPECIAL_DATA",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "DATA_VALUE",
"position": 2,
"nullable": true,
"type": "VARCHAR2(500 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Data value field - supports NULL, empty strings, and 'quoted' content"
},
{
"name": "METADATA_JSON",
"position": 3,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP",
"comment": "JSON metadata: {\"key\": \"value\", \"array\": [1,2,3]}"
},
{
"name": "UNICODE_TEXT",
"position": 4,
"nullable": true,
"type": "NVARCHAR2(400)",
"collation": "USING_NLS_COMP",
"comment": "Unicode text support:\nLine 1: Basic ASCII text\nLine 2: Special chars !@#$%^&*()_+-={}[]|;:,.<>?\nLine 3: Math symbols \u2264 \u2265 \u00b1 \u00d7 \u00f7 \u2211 \u220f \u2206 \u03c0\nLine 4: Currencies $ \u20ac \u00a3 \u00a5 \u20b9"
},
{
"name": "BINARY_DATA",
"position": 5,
"nullable": true,
"type": "BLOB",
"comment": "Binary data storage\\nSupports: images, documents, archives\\tTab-separated info\\r\\nWindows line endings"
},
{
"name": "CATEGORY",
"position": 6,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Category field: use % for wildcard searches, _ for single char matching"
},
{
"name": "STATUS",
"position": 7,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Status field - values: 'ACTIVE', 'INACTIVE', 'PENDING'; DROP TABLE users; --"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Table containing \"special\" data with various formats & types"
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "MULTILANG_CONTENT",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "TITLE_EN",
"position": 2,
"nullable": true,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "TITLE_ES",
"position": 3,
"nullable": true,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "TITLE_ZH",
"position": 4,
"nullable": true,
"type": "NVARCHAR2(400)",
"collation": "USING_NLS_COMP"
},
{
"name": "CONTENT_TEXT",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "TAGS",
"position": 6,
"nullable": true,
"type": "VARCHAR2(1000 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
},
{
"name": "SPECIAL_DATA",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "DATA_VALUE",
"position": 2,
"nullable": true,
"type": "VARCHAR2(500 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "METADATA_JSON",
"position": 3,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "UNICODE_TEXT",
"position": 4,
"nullable": true,
"type": "NVARCHAR2(400)",
"collation": "USING_NLS_COMP"
},
{
"name": "BINARY_DATA",
"position": 5,
"nullable": true,
"type": "BLOB"
},
{
"name": "CATEGORY",
"position": 6,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "STATUS",
"position": 7,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
DROP TABLE "COMPLEX_TYPES";
COMMENT ON TABLE "MULTILANG_CONTENT" IS '';
COMMENT ON COLUMN "MULTILANG_CONTENT"."TITLE_EN" IS '';
COMMENT ON COLUMN "MULTILANG_CONTENT"."TITLE_ES" IS '';
COMMENT ON COLUMN "MULTILANG_CONTENT"."TITLE_ZH" IS '';
COMMENT ON COLUMN "MULTILANG_CONTENT"."CONTENT_TEXT" IS '';
COMMENT ON COLUMN "MULTILANG_CONTENT"."TAGS" IS '';
COMMENT ON COLUMN "MULTILANG_CONTENT"."TITLE_EN" IS '';
COMMENT ON COLUMN "MULTILANG_CONTENT"."TITLE_ES" IS '';
COMMENT ON COLUMN "MULTILANG_CONTENT"."TITLE_ZH" IS '';
COMMENT ON COLUMN "MULTILANG_CONTENT"."CONTENT_TEXT" IS '';
COMMENT ON COLUMN "MULTILANG_CONTENT"."TAGS" IS '';
COMMENT ON TABLE "SPECIAL_DATA" IS '';
COMMENT ON COLUMN "SPECIAL_DATA"."DATA_VALUE" IS '';
COMMENT ON COLUMN "SPECIAL_DATA"."METADATA_JSON" IS '';
COMMENT ON COLUMN "SPECIAL_DATA"."UNICODE_TEXT" IS '';
COMMENT ON COLUMN "SPECIAL_DATA"."BINARY_DATA" IS '';
COMMENT ON COLUMN "SPECIAL_DATA"."CATEGORY" IS '';
COMMENT ON COLUMN "SPECIAL_DATA"."STATUS" IS '';
COMMENT ON COLUMN "SPECIAL_DATA"."DATA_VALUE" IS '';
COMMENT ON COLUMN "SPECIAL_DATA"."METADATA_JSON" IS '';
COMMENT ON COLUMN "SPECIAL_DATA"."UNICODE_TEXT" IS '';
COMMENT ON COLUMN "SPECIAL_DATA"."BINARY_DATA" IS '';
COMMENT ON COLUMN "SPECIAL_DATA"."CATEGORY" IS '';
COMMENT ON COLUMN "SPECIAL_DATA"."STATUS" IS '';
- description: 'Reverse: Remove all comments and drop a table'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "CUSTOMERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PHONE",
"position": 4,
"nullable": true,
"type": "VARCHAR2(20 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "ADDRESS",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_CUSTOMERS_EMAIL",
"expressions": [
"\"EMAIL\""
],
"descending": [
false
],
"type": "NORMAL",
"unique": true,
"visible": true
}
]
},
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000003\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(10,2)"
},
{
"name": "DESCRIPTION",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "CATEGORY",
"position": 5,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CREATED_AT",
"position": 6,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_PRODUCTS_CATEGORY",
"expressions": [
"\"CATEGORY\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "CUSTOMERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER",
"comment": "Primary key for customer records"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Full name of the customer"
},
{
"name": "EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Customer email address (unique)"
},
{
"name": "PHONE",
"position": 4,
"nullable": true,
"type": "VARCHAR2(20 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Contact phone number"
},
{
"name": "ADDRESS",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP",
"comment": "Full mailing address with multiple lines"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_CUSTOMERS_EMAIL",
"expressions": [
"\"EMAIL\""
],
"descending": [
false
],
"type": "NORMAL",
"unique": true,
"visible": true
}
],
"comment": "Customer information and contact details"
},
{
"name": "ORDERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER",
"comment": "Unique order identifier"
},
{
"name": "CUSTOMER_ID",
"position": 2,
"type": "NUMBER",
"comment": "Reference to customer who placed the order"
},
{
"name": "PRODUCT_ID",
"position": 3,
"type": "NUMBER",
"comment": "Reference to ordered product"
},
{
"name": "ORDER_DATE",
"position": 4,
"default": "SYSDATE",
"nullable": true,
"type": "DATE",
"comment": "Date when the order was placed"
},
{
"name": "QUANTITY",
"position": 5,
"type": "NUMBER",
"comment": "Number of items ordered"
},
{
"name": "TOTAL_AMOUNT",
"position": 6,
"nullable": true,
"type": "NUMBER(12,2)",
"comment": "Total order amount including taxes"
},
{
"name": "STATUS",
"position": 7,
"default": "'PENDING'",
"nullable": true,
"type": "VARCHAR2(20 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Current order status (PENDING, SHIPPED, DELIVERED, CANCELLED)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Customer orders linking products and customers",
"foreignKeys": [
{
"name": "FK_ORDER_CUSTOMER",
"columns": [
"CUSTOMER_ID"
],
"referencedTable": "CUSTOMERS",
"referencedColumns": [
"ID"
]
},
{
"name": "FK_ORDER_PRODUCT",
"columns": [
"PRODUCT_ID"
],
"referencedTable": "PRODUCTS",
"referencedColumns": [
"ID"
]
}
]
},
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000003\".nextval",
"type": "NUMBER",
"comment": "Unique product identifier (auto-generated)"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Product name - must be unique and descriptive"
},
{
"name": "PRICE",
"position": 3,
"type": "NUMBER(10,2)",
"comment": "Product price in USD with 2 decimal places"
},
{
"name": "DESCRIPTION",
"position": 4,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP",
"comment": "Detailed product description with HTML formatting support"
},
{
"name": "CATEGORY",
"position": 5,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Product category for filtering and organization"
},
{
"name": "CREATED_AT",
"position": 6,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)",
"comment": "Timestamp when the product was added to catalog"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
},
{
"name": "IDX_PRODUCTS_CATEGORY",
"expressions": [
"\"CATEGORY\""
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"comment": "Product catalog table containing all product information"
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
CREATE TABLE "ORDERS" (
"ID" NUMBER DEFAULT "TESTSCHEMA"."ISEQ$$_0000002".nextval NOT NULL,
"CUSTOMER_ID" NUMBER NOT NULL,
"PRODUCT_ID" NUMBER NOT NULL,
"ORDER_DATE" DATE DEFAULT SYSDATE,
"QUANTITY" NUMBER NOT NULL,
"TOTAL_AMOUNT" NUMBER(12,2),
"STATUS" VARCHAR2(20 BYTE) DEFAULT 'PENDING'
);
ALTER TABLE "ORDERS" ADD CONSTRAINT "SYS_C0000002" PRIMARY KEY (ID);
COMMENT ON TABLE "ORDERS" IS 'Customer orders linking products and customers';
COMMENT ON COLUMN "ORDERS"."ID" IS 'Unique order identifier';
COMMENT ON COLUMN "ORDERS"."CUSTOMER_ID" IS 'Reference to customer who placed the order';
COMMENT ON COLUMN "ORDERS"."PRODUCT_ID" IS 'Reference to ordered product';
COMMENT ON COLUMN "ORDERS"."ORDER_DATE" IS 'Date when the order was placed';
COMMENT ON COLUMN "ORDERS"."QUANTITY" IS 'Number of items ordered';
COMMENT ON COLUMN "ORDERS"."TOTAL_AMOUNT" IS 'Total order amount including taxes';
COMMENT ON COLUMN "ORDERS"."STATUS" IS 'Current order status (PENDING, SHIPPED, DELIVERED, CANCELLED)';
ALTER TABLE "ORDERS" ADD CONSTRAINT "FK_ORDER_CUSTOMER" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "CUSTOMERS" ("ID");
ALTER TABLE "ORDERS" ADD CONSTRAINT "FK_ORDER_PRODUCT" FOREIGN KEY ("PRODUCT_ID") REFERENCES "PRODUCTS" ("ID");
COMMENT ON TABLE "CUSTOMERS" IS 'Customer information and contact details';
COMMENT ON COLUMN "CUSTOMERS"."ID" IS 'Primary key for customer records';
COMMENT ON COLUMN "CUSTOMERS"."NAME" IS 'Full name of the customer';
COMMENT ON COLUMN "CUSTOMERS"."EMAIL" IS 'Customer email address (unique)';
COMMENT ON COLUMN "CUSTOMERS"."PHONE" IS 'Contact phone number';
COMMENT ON COLUMN "CUSTOMERS"."ADDRESS" IS 'Full mailing address with multiple lines';
COMMENT ON COLUMN "CUSTOMERS"."ID" IS 'Primary key for customer records';
COMMENT ON COLUMN "CUSTOMERS"."NAME" IS 'Full name of the customer';
COMMENT ON COLUMN "CUSTOMERS"."EMAIL" IS 'Customer email address (unique)';
COMMENT ON COLUMN "CUSTOMERS"."PHONE" IS 'Contact phone number';
COMMENT ON COLUMN "CUSTOMERS"."ADDRESS" IS 'Full mailing address with multiple lines';
COMMENT ON TABLE "PRODUCTS" IS 'Product catalog table containing all product information';
COMMENT ON COLUMN "PRODUCTS"."ID" IS 'Unique product identifier (auto-generated)';
COMMENT ON COLUMN "PRODUCTS"."NAME" IS 'Product name - must be unique and descriptive';
COMMENT ON COLUMN "PRODUCTS"."PRICE" IS 'Product price in USD with 2 decimal places';
COMMENT ON COLUMN "PRODUCTS"."DESCRIPTION" IS 'Detailed product description with HTML formatting support';
COMMENT ON COLUMN "PRODUCTS"."CATEGORY" IS 'Product category for filtering and organization';
COMMENT ON COLUMN "PRODUCTS"."CREATED_AT" IS 'Timestamp when the product was added to catalog';
COMMENT ON COLUMN "PRODUCTS"."ID" IS 'Unique product identifier (auto-generated)';
COMMENT ON COLUMN "PRODUCTS"."NAME" IS 'Product name - must be unique and descriptive';
COMMENT ON COLUMN "PRODUCTS"."PRICE" IS 'Product price in USD with 2 decimal places';
COMMENT ON COLUMN "PRODUCTS"."DESCRIPTION" IS 'Detailed product description with HTML formatting support';
COMMENT ON COLUMN "PRODUCTS"."CATEGORY" IS 'Product category for filtering and organization';
COMMENT ON COLUMN "PRODUCTS"."CREATED_AT" IS 'Timestamp when the product was added to catalog';
- description: 'Reverse: Restore original comments and modify state'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "INVENTORY",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "PRODUCT_CODE",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Product identification code"
},
{
"name": "STOCK_LEVEL",
"position": 3,
"type": "NUMBER",
"comment": "Current stock quantity"
},
{
"name": "MIN_STOCK",
"position": 4,
"default": "10",
"nullable": true,
"type": "NUMBER",
"comment": "Minimum stock threshold"
},
{
"name": "MAX_STOCK",
"position": 5,
"default": "1000",
"nullable": true,
"type": "NUMBER",
"comment": "Maximum stock capacity"
},
{
"name": "WAREHOUSE_ID",
"position": 6,
"nullable": true,
"type": "NUMBER",
"comment": "Warehouse location identifier"
},
{
"name": "LAST_UPDATED",
"position": 7,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Initial inventory tracking table"
},
{
"name": "SUPPLIERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CONTACT_EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Primary contact email"
},
{
"name": "RATING",
"position": 4,
"nullable": true,
"type": "NUMBER(2,1)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Supplier information table"
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "INVENTORY",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "PRODUCT_CODE",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Updated: Standardized product SKU following new naming convention"
},
{
"name": "STOCK_LEVEL",
"position": 3,
"type": "NUMBER",
"comment": "Updated: Real-time stock quantity with automated updates"
},
{
"name": "MIN_STOCK",
"position": 4,
"default": "10",
"nullable": true,
"type": "NUMBER",
"comment": "Updated: Dynamic minimum threshold based on sales velocity"
},
{
"name": "MAX_STOCK",
"position": 5,
"default": "1000",
"nullable": true,
"type": "NUMBER"
},
{
"name": "WAREHOUSE_ID",
"position": 6,
"nullable": true,
"type": "NUMBER"
},
{
"name": "LAST_UPDATED",
"position": 7,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)",
"comment": "Timestamp of last inventory update or modification"
},
{
"name": "REORDER_POINT",
"position": 8,
"default": "0",
"nullable": true,
"type": "NUMBER",
"comment": "Calculated reorder point based on lead time and demand"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Updated inventory management system with enhanced tracking capabilities"
},
{
"name": "SUPPLIERS",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "CONTACT_EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "RATING",
"position": 4,
"nullable": true,
"type": "NUMBER(2,1)",
"comment": "Supplier performance rating (1.0 to 5.0 scale)"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: |
ALTER TABLE "INVENTORY" ADD "REORDER_POINT" NUMBER DEFAULT 0;
COMMENT ON TABLE "INVENTORY" IS 'Updated inventory management system with enhanced tracking capabilities';
COMMENT ON COLUMN "INVENTORY"."PRODUCT_CODE" IS 'Updated: Standardized product SKU following new naming convention';
COMMENT ON COLUMN "INVENTORY"."STOCK_LEVEL" IS 'Updated: Real-time stock quantity with automated updates';
COMMENT ON COLUMN "INVENTORY"."MIN_STOCK" IS 'Updated: Dynamic minimum threshold based on sales velocity';
COMMENT ON COLUMN "INVENTORY"."MAX_STOCK" IS '';
COMMENT ON COLUMN "INVENTORY"."WAREHOUSE_ID" IS '';
COMMENT ON COLUMN "INVENTORY"."LAST_UPDATED" IS 'Timestamp of last inventory update or modification';
COMMENT ON COLUMN "INVENTORY"."REORDER_POINT" IS 'Calculated reorder point based on lead time and demand';
COMMENT ON COLUMN "INVENTORY"."PRODUCT_CODE" IS 'Updated: Standardized product SKU following new naming convention';
COMMENT ON COLUMN "INVENTORY"."STOCK_LEVEL" IS 'Updated: Real-time stock quantity with automated updates';
COMMENT ON COLUMN "INVENTORY"."MIN_STOCK" IS 'Updated: Dynamic minimum threshold based on sales velocity';
COMMENT ON COLUMN "INVENTORY"."MAX_STOCK" IS '';
COMMENT ON COLUMN "INVENTORY"."WAREHOUSE_ID" IS '';
COMMENT ON COLUMN "INVENTORY"."LAST_UPDATED" IS 'Timestamp of last inventory update or modification';
COMMENT ON COLUMN "INVENTORY"."REORDER_POINT" IS 'Calculated reorder point based on lead time and demand';
COMMENT ON TABLE "SUPPLIERS" IS '';
COMMENT ON COLUMN "SUPPLIERS"."CONTACT_EMAIL" IS '';
COMMENT ON COLUMN "SUPPLIERS"."RATING" IS 'Supplier performance rating (1.0 to 5.0 scale)';
COMMENT ON COLUMN "SUPPLIERS"."CONTACT_EMAIL" IS '';
COMMENT ON COLUMN "SUPPLIERS"."RATING" IS 'Supplier performance rating (1.0 to 5.0 scale)';
- description: 'Reverse: Remove all comments with special characters'
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "MULTILANG_CONTENT",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "TITLE_EN",
"position": 2,
"nullable": true,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "TITLE_ES",
"position": 3,
"nullable": true,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "TITLE_ZH",
"position": 4,
"nullable": true,
"type": "NVARCHAR2(400)",
"collation": "USING_NLS_COMP"
},
{
"name": "CONTENT_TEXT",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "TAGS",
"position": 6,
"nullable": true,
"type": "VARCHAR2(1000 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
},
{
"name": "SPECIAL_DATA",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "DATA_VALUE",
"position": 2,
"nullable": true,
"type": "VARCHAR2(500 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "METADATA_JSON",
"position": 3,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP"
},
{
"name": "UNICODE_TEXT",
"position": 4,
"nullable": true,
"type": "NVARCHAR2(400)",
"collation": "USING_NLS_COMP"
},
{
"name": "BINARY_DATA",
"position": 5,
"nullable": true,
"type": "BLOB"
},
{
"name": "CATEGORY",
"position": 6,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP"
},
{
"name": "STATUS",
"position": 7,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
]
}
]
}
],
"serviceName": "FREEPDB1"
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "COMPLEX_TYPES",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "XML_DATA",
"position": 2,
"nullable": true,
"type": "XMLTYPE",
"comment": "XMLType column for structured XML storage\nExample: <?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<root><item id=\"1\" name=\"test\">Content</item></root>"
},
{
"name": "TIMESTAMP_TZ",
"position": 3,
"default": "SYSTIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6) WITH TIME ZONE",
"comment": "Timestamp with timezone: YYYY-MM-DD HH24:MI:SS.FF TZR\nDefault: SYSTIMESTAMP (system timestamp with session timezone)"
},
{
"name": "INTERVAL_DS",
"position": 4,
"nullable": true,
"type": "INTERVAL DAY(2) TO SECOND(6)",
"comment": "Interval Day to Second: +/-DD HH:MI:SS.FFFFFF\nRange: +/-999999999 days, precision up to nanoseconds"
},
{
"name": "RAW_DATA",
"position": 5,
"nullable": true,
"type": "RAW(100)"
},
{
"name": "LONG_RAW_DATA",
"position": 6,
"nullable": true,
"type": "LONG RAW"
}
],
"indexes": [
{
"name": "SYS_C0000001",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Advanced data types testing table\nSupports: XMLType, Timestamps with timezone, Intervals, RAW data\nPerformance: Optimized for OLTP workloads @ 10K+ TPS\nSecurity: Row-level security enabled, audit trails maintained"
},
{
"name": "MULTILANG_CONTENT",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000001\".nextval",
"type": "NUMBER"
},
{
"name": "TITLE_EN",
"position": 2,
"nullable": true,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "English title field"
},
{
"name": "TITLE_ES",
"position": 3,
"nullable": true,
"type": "VARCHAR2(200 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "T\u00edtulo en espa\u00f1ol"
},
{
"name": "TITLE_ZH",
"position": 4,
"nullable": true,
"type": "NVARCHAR2(400)",
"collation": "USING_NLS_COMP",
"comment": "\u4e2d\u6587\u6807\u9898\u5b57\u6bb5 - \u652f\u6301\u7b80\u4f53\u548c\u7e41\u4f53\u4e2d\u6587"
},
{
"name": "CONTENT_TEXT",
"position": 5,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP",
"comment": "Content field supporting:\n1. HTML tags: <b>bold</b>, <i>italic</i>, <a href=\"http://example.com\">links</a>\n2. Markdown: **bold**, *italic*, [link](url), backtick-code-backtick\n3. Special symbols: \u00a9 \u00ae \u2122 \u00a7 \u00b6 \u2020 \u2021 \u2022 \u2026 \u2030 \u2032 \u2033\n4. Emojis: \ud83d\ude00 \ud83d\ude03 \ud83d\ude04 \ud83d\ude01 \ud83d\ude80 \u2b50 \ud83c\udf89 \ud83d\udcaf\n5. Mathematical: \u2200x\u2208\u211d: x\u00b2 \u2265 0, \u2211(i=1 to n) i = n(n+1)/2"
},
{
"name": "TAGS",
"position": 6,
"nullable": true,
"type": "VARCHAR2(1000 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "XML tags: <tag type=\"category\" value=\"news\"/><tag type=\"priority\" value=\"high\"/>End of XML"
}
],
"indexes": [
{
"name": "SYS_C0000002",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Multilingual content: English, Espa\u00f1ol, \u4e2d\u6587, \u0627\u0644\u0639\u0631\u0628\u064a\u0629, \u0420\u0443\u0441\u0441\u043a\u0438\u0439, \u65e5\u672c\u8a9e"
},
{
"name": "SPECIAL_DATA",
"columns": [
{
"name": "ID",
"position": 1,
"default": "\"TESTSCHEMA\".\"ISEQ$$_0000002\".nextval",
"type": "NUMBER"
},
{
"name": "DATA_VALUE",
"position": 2,
"nullable": true,
"type": "VARCHAR2(500 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Data value field - supports NULL, empty strings, and 'quoted' content"
},
{
"name": "METADATA_JSON",
"position": 3,
"nullable": true,
"type": "CLOB",
"collation": "USING_NLS_COMP",
"comment": "JSON metadata: {\"key\": \"value\", \"array\": [1,2,3]}"
},
{
"name": "UNICODE_TEXT",
"position": 4,
"nullable": true,
"type": "NVARCHAR2(400)",
"collation": "USING_NLS_COMP",
"comment": "Unicode text support:\nLine 1: Basic ASCII text\nLine 2: Special chars !@#$%^&*()_+-={}[]|;:,.<>?\nLine 3: Math symbols \u2264 \u2265 \u00b1 \u00d7 \u00f7 \u2211 \u220f \u2206 \u03c0\nLine 4: Currencies $ \u20ac \u00a3 \u00a5 \u20b9"
},
{
"name": "BINARY_DATA",
"position": 5,
"nullable": true,
"type": "BLOB",
"comment": "Binary data storage\\nSupports: images, documents, archives\\tTab-separated info\\r\\nWindows line endings"
},
{
"name": "CATEGORY",
"position": 6,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Category field: use % for wildcard searches, _ for single char matching"
},
{
"name": "STATUS",
"position": 7,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"collation": "USING_NLS_COMP",
"comment": "Status field - values: 'ACTIVE', 'INACTIVE', 'PENDING'; DROP TABLE users; --"
}
],
"indexes": [
{
"name": "SYS_C0000003",
"expressions": [
"ID"
],
"unique": true,
"primary": true,
"isConstraint": true
}
],
"comment": "Table containing \"special\" data with various formats & types"
}
]
}
],
"serviceName": "FREEPDB1"
}
expected: "CREATE TABLE \"COMPLEX_TYPES\" (\n \"ID\" NUMBER NOT NULL,\n \"XML_DATA\" XMLTYPE,\n \"TIMESTAMP_TZ\" TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP,\n \"INTERVAL_DS\" INTERVAL DAY(2) TO SECOND(6),\n \"RAW_DATA\" RAW(100),\n \"LONG_RAW_DATA\" LONG RAW\n);\nALTER TABLE \"COMPLEX_TYPES\" ADD CONSTRAINT \"SYS_C0000001\" PRIMARY KEY (ID);\nCOMMENT ON TABLE \"COMPLEX_TYPES\" IS 'Advanced data types testing table\nSupports: XMLType, Timestamps with timezone, Intervals, RAW data\nPerformance: Optimized for OLTP workloads @ 10K+ TPS\nSecurity: Row-level security enabled, audit trails maintained';\nCOMMENT ON COLUMN \"COMPLEX_TYPES\".\"XML_DATA\" IS 'XMLType column for structured XML storage\nExample: <?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<root><item id=\"1\" name=\"test\">Content</item></root>';\nCOMMENT ON COLUMN \"COMPLEX_TYPES\".\"TIMESTAMP_TZ\" IS 'Timestamp with timezone: YYYY-MM-DD HH24:MI:SS.FF TZR\nDefault: SYSTIMESTAMP (system timestamp with session timezone)';\nCOMMENT ON COLUMN \"COMPLEX_TYPES\".\"INTERVAL_DS\" IS 'Interval Day to Second: +/-DD HH:MI:SS.FFFFFF\nRange: +/-999999999 days, precision up to nanoseconds';\n\nCOMMENT ON TABLE \"MULTILANG_CONTENT\" IS 'Multilingual content: English, Español, 中文, العربية, Русский, 日本語';\nCOMMENT ON COLUMN \"MULTILANG_CONTENT\".\"TITLE_EN\" IS 'English title field';\nCOMMENT ON COLUMN \"MULTILANG_CONTENT\".\"TITLE_ES\" IS 'Título en español';\nCOMMENT ON COLUMN \"MULTILANG_CONTENT\".\"TITLE_ZH\" IS '中文标题字段 - 支持简体和繁体中文';\nCOMMENT ON COLUMN \"MULTILANG_CONTENT\".\"CONTENT_TEXT\" IS 'Content field supporting:\n1. HTML tags: <b>bold</b>, <i>italic</i>, <a href=\"http://example.com\">links</a>\n2. Markdown: **bold**, *italic*, [link](url), backtick-code-backtick\n3. Special symbols: © ® ™ § ¶ † ‡ • … ‰ ′ ″\n4. Emojis: \U0001F600 \U0001F603 \U0001F604 \U0001F601 \U0001F680 ⭐ \U0001F389 \U0001F4AF\n5. Mathematical: ∀x∈ℝ: x² ≥ 0, ∑(i=1 to n) i = n(n+1)/2';\nCOMMENT ON COLUMN \"MULTILANG_CONTENT\".\"TAGS\" IS 'XML tags: <tag type=\"category\" value=\"news\"/><tag type=\"priority\" value=\"high\"/>End of XML';\nCOMMENT ON COLUMN \"MULTILANG_CONTENT\".\"TITLE_EN\" IS 'English title field';\nCOMMENT ON COLUMN \"MULTILANG_CONTENT\".\"TITLE_ES\" IS 'Título en español';\nCOMMENT ON COLUMN \"MULTILANG_CONTENT\".\"TITLE_ZH\" IS '中文标题字段 - 支持简体和繁体中文';\nCOMMENT ON COLUMN \"MULTILANG_CONTENT\".\"CONTENT_TEXT\" IS 'Content field supporting:\n1. HTML tags: <b>bold</b>, <i>italic</i>, <a href=\"http://example.com\">links</a>\n2. Markdown: **bold**, *italic*, [link](url), backtick-code-backtick\n3. Special symbols: © ® ™ § ¶ † ‡ • … ‰ ′ ″\n4. Emojis: \U0001F600 \U0001F603 \U0001F604 \U0001F601 \U0001F680 ⭐ \U0001F389 \U0001F4AF\n5. Mathematical: ∀x∈ℝ: x² ≥ 0, ∑(i=1 to n) i = n(n+1)/2';\nCOMMENT ON COLUMN \"MULTILANG_CONTENT\".\"TAGS\" IS 'XML tags: <tag type=\"category\" value=\"news\"/><tag type=\"priority\" value=\"high\"/>End of XML';\nCOMMENT ON TABLE \"SPECIAL_DATA\" IS 'Table containing \"special\" data with various formats & types';\nCOMMENT ON COLUMN \"SPECIAL_DATA\".\"DATA_VALUE\" IS 'Data value field - supports NULL, empty strings, and ''quoted'' content';\nCOMMENT ON COLUMN \"SPECIAL_DATA\".\"METADATA_JSON\" IS 'JSON metadata: {\"key\": \"value\", \"array\": [1,2,3]}';\nCOMMENT ON COLUMN \"SPECIAL_DATA\".\"UNICODE_TEXT\" IS 'Unicode text support:\nLine 1: Basic ASCII text\nLine 2: Special chars !@#$%^&*()_+-={}[]|;:,.<>?\nLine 3: Math symbols ≤ ≥ ± × ÷ ∑ ∏ ∆ π\nLine 4: Currencies $ € £ ¥ ₹';\nCOMMENT ON COLUMN \"SPECIAL_DATA\".\"BINARY_DATA\" IS 'Binary data storage\\nSupports: images, documents, archives\\tTab-separated info\\r\\nWindows line endings';\nCOMMENT ON COLUMN \"SPECIAL_DATA\".\"CATEGORY\" IS 'Category field: use % for wildcard searches, _ for single char matching';\nCOMMENT ON COLUMN \"SPECIAL_DATA\".\"STATUS\" IS 'Status field - values: ''ACTIVE'', ''INACTIVE'', ''PENDING''; DROP TABLE users; --';\nCOMMENT ON COLUMN \"SPECIAL_DATA\".\"DATA_VALUE\" IS 'Data value field - supports NULL, empty strings, and ''quoted'' content';\nCOMMENT ON COLUMN \"SPECIAL_DATA\".\"METADATA_JSON\" IS 'JSON metadata: {\"key\": \"value\", \"array\": [1,2,3]}';\nCOMMENT ON COLUMN \"SPECIAL_DATA\".\"UNICODE_TEXT\" IS 'Unicode text support:\nLine 1: Basic ASCII text\nLine 2: Special chars !@#$%^&*()_+-={}[]|;:,.<>?\nLine 3: Math symbols ≤ ≥ ± × ÷ ∑ ∏ ∆ π\nLine 4: Currencies $ € £ ¥ ₹';\nCOMMENT ON COLUMN \"SPECIAL_DATA\".\"BINARY_DATA\" IS 'Binary data storage\\nSupports: images, documents, archives\\tTab-separated info\\r\\nWindows line endings';\nCOMMENT ON COLUMN \"SPECIAL_DATA\".\"CATEGORY\" IS 'Category field: use % for wildcard searches, _ for single char matching';\nCOMMENT ON COLUMN \"SPECIAL_DATA\".\"STATUS\" IS 'Status field - values: ''ACTIVE'', ''INACTIVE'', ''PENDING''; DROP TABLE users; --';\n"
- description: Invisible and virtual columns, and object tables the migration leaves alone
oldMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "CHILD",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "S_REF",
"position": 2,
"type": "NUMBER",
"nullable": true
},
{
"name": "A_REF",
"position": 3,
"type": "NUMBER",
"nullable": true
}
],
"foreign_keys": [
{
"name": "CHILD_FK",
"columns": [
"S_REF",
"A_REF"
],
"referenced_table": "T",
"referenced_columns": [
"S",
"A"
]
}
]
},
{
"name": "OT",
"skip_dump": true,
"columns": [
{
"name": "X",
"position": 1,
"type": "NUMBER",
"nullable": true
}
]
},
{
"name": "T",
"columns": [
{
"name": "A",
"position": 1,
"type": "NUMBER"
},
{
"name": "VC",
"position": 2,
"type": "NUMBER",
"generation": {
"type": "TYPE_VIRTUAL",
"expression": "\"A\"+1"
}
},
{
"name": "H",
"position": 3,
"type": "NUMBER",
"nullable": true,
"is_invisible": true
},
{
"name": "S",
"position": 4,
"type": "NUMBER",
"nullable": true,
"comment": "S comment"
},
{
"name": "VT",
"position": 5,
"type": "NUMBER",
"nullable": true,
"generation": {
"type": "TYPE_VIRTUAL",
"expression": "\"A\"+5"
}
},
{
"name": "R",
"position": 6,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "T_A",
"expressions": [
"\"A\""
],
"type": "NORMAL",
"visible": true
},
{
"name": "T_S",
"expressions": [
"\"S\""
],
"type": "NORMAL",
"visible": true
},
{
"name": "T_VC",
"expressions": [
"\"VC\""
],
"type": "NORMAL",
"visible": true
},
{
"name": "T_SA_UK",
"expressions": [
"S",
"A"
],
"type": "NORMAL",
"unique": true,
"visible": true,
"is_constraint": true
}
],
"check_constraints": [
{
"name": "T_S_CK",
"expression": "\"S\" > 0"
},
{
"name": "T_VC_CK",
"expression": "\"VC\" > 0"
},
{
"name": "T_SA_CK",
"expression": "\"S\" >= \"A\""
},
{
"name": "T_VCS_CK",
"expression": "\"VC\" <= \"S\" + 100"
},
{
"name": "VC_NN",
"expression": "\"VC\" IS NOT NULL"
},
{
"name": "R_NN",
"expression": "\"R\" IS NOT NULL"
}
]
}
]
}
]
}
newMetadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"name": "CHILD",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "S_REF",
"position": 2,
"type": "NUMBER",
"nullable": true
},
{
"name": "A_REF",
"position": 3,
"type": "NUMBER",
"nullable": true
}
],
"foreign_keys": [
{
"name": "CHILD_FK",
"columns": [
"S_REF",
"A_REF"
],
"referenced_table": "T",
"referenced_columns": [
"S",
"A"
]
}
]
},
{
"name": "OT",
"skip_dump": true,
"columns": [
{
"name": "X",
"position": 1,
"type": "NUMBER",
"nullable": true
},
{
"name": "Y",
"position": 2,
"type": "NUMBER",
"nullable": true
}
]
},
{
"name": "OT2",
"skip_dump": true,
"columns": [
{
"name": "X",
"position": 1,
"type": "NUMBER",
"nullable": true
}
]
},
{
"name": "T",
"columns": [
{
"name": "A",
"position": 1,
"type": "NUMBER"
},
{
"name": "VC",
"position": 2,
"type": "NUMBER",
"generation": {
"type": "TYPE_VIRTUAL",
"expression": "\"A\"+2"
}
},
{
"name": "H",
"position": 3,
"type": "NUMBER",
"nullable": true
},
{
"name": "S",
"position": 4,
"type": "NUMBER",
"generation": {
"type": "TYPE_VIRTUAL",
"expression": "\"A\"*3"
},
"comment": "S comment"
},
{
"name": "N",
"position": 5,
"type": "NUMBER",
"is_invisible": true,
"default": "5"
},
{
"name": "NV",
"position": 6,
"type": "NUMBER",
"nullable": true,
"is_invisible": true,
"generation": {
"type": "TYPE_VIRTUAL",
"expression": "\"A\"-1"
}
},
{
"name": "VT",
"position": 7,
"type": "VARCHAR2(40 BYTE)",
"nullable": true,
"generation": {
"type": "TYPE_VIRTUAL",
"expression": "TO_CHAR(\"A\")"
}
},
{
"name": "R",
"position": 8,
"type": "NUMBER",
"generation": {
"type": "TYPE_VIRTUAL",
"expression": "\"A\"*7"
}
}
],
"indexes": [
{
"name": "T_A",
"expressions": [
"\"A\""
],
"type": "NORMAL",
"visible": true
},
{
"name": "T_S",
"expressions": [
"\"S\""
],
"type": "NORMAL",
"visible": true
},
{
"name": "T_VC",
"expressions": [
"\"VC\""
],
"type": "NORMAL",
"visible": true
},
{
"name": "T_SA_UK",
"expressions": [
"S",
"A"
],
"type": "NORMAL",
"unique": true,
"visible": true,
"is_constraint": true
}
],
"check_constraints": [
{
"name": "T_S_CK",
"expression": "\"S\" > 0"
},
{
"name": "T_VC_CK",
"expression": "\"VC\" > 0"
},
{
"name": "T_SA_CK",
"expression": "\"S\" >= \"A\""
},
{
"name": "T_VCS_CK",
"expression": "\"VC\" <= \"S\" + 100"
},
{
"name": "VC_NN",
"expression": "\"VC\" IS NOT NULL"
},
{
"name": "R_NN",
"expression": "\"R\" IS NOT NULL"
}
]
},
{
"name": "T2",
"columns": [
{
"name": "A",
"position": 1,
"type": "NUMBER"
},
{
"name": "H",
"position": 2,
"type": "VARCHAR2(10 BYTE)",
"nullable": true,
"is_invisible": true
},
{
"name": "VC",
"position": 3,
"type": "NUMBER",
"nullable": true,
"generation": {
"type": "TYPE_VIRTUAL",
"expression": "\"A\"+1"
}
}
]
},
{
"name": "NEWCHILD",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "S_REF",
"position": 2,
"type": "NUMBER",
"nullable": true
},
{
"name": "A_REF",
"position": 3,
"type": "NUMBER",
"nullable": true
}
],
"foreign_keys": [
{
"name": "NEWCHILD_FK",
"columns": [
"S_REF",
"A_REF"
],
"referenced_table": "T",
"referenced_columns": [
"S",
"A"
]
}
]
}
]
}
]
}
expected: |
ALTER TABLE "T" ADD "N" NUMBER INVISIBLE DEFAULT 5 NOT NULL;
ALTER TABLE "T" ADD "NV" NUMBER INVISIBLE GENERATED ALWAYS AS ("A"-1) VIRTUAL;
CREATE TABLE "T2" (
"A" NUMBER NOT NULL,
"H" VARCHAR2(10 BYTE) INVISIBLE,
"VC" NUMBER GENERATED ALWAYS AS ("A"+1) VIRTUAL
);
CREATE TABLE "NEWCHILD" (
"ID" NUMBER NOT NULL,
"S_REF" NUMBER,
"A_REF" NUMBER
);
ALTER TABLE "CHILD" DROP CONSTRAINT "CHILD_FK";
ALTER TABLE "T" DROP CONSTRAINT "T_S_CK";
ALTER TABLE "T" DROP CONSTRAINT "T_VC_CK";
ALTER TABLE "T" DROP CONSTRAINT "T_SA_CK";
ALTER TABLE "T" DROP CONSTRAINT "T_VCS_CK";
ALTER TABLE "T" DROP CONSTRAINT "VC_NN";
ALTER TABLE "T" DROP CONSTRAINT "R_NN";
DROP INDEX "T_S";
DROP INDEX "T_VC";
ALTER TABLE "T" DROP CONSTRAINT "T_SA_UK";
ALTER TABLE "T" MODIFY ("VC" NUMBER AS ("A"+2));
ALTER TABLE "T" MODIFY ("VC" CONSTRAINT "VC_NN" NOT NULL);
ALTER TABLE "T" MODIFY ("H" VISIBLE);
ALTER TABLE "T" DROP COLUMN "S";
ALTER TABLE "T" ADD "S" NUMBER GENERATED ALWAYS AS ("A"*3) VIRTUAL NOT NULL;
COMMENT ON COLUMN "T"."S" IS 'S comment';
ALTER TABLE "T" MODIFY ("VT" VARCHAR2(40 BYTE) AS (TO_CHAR("A")));
ALTER TABLE "T" DROP COLUMN "R";
ALTER TABLE "T" ADD "R" NUMBER GENERATED ALWAYS AS ("A"*7) VIRTUAL;
ALTER TABLE "T" MODIFY ("R" CONSTRAINT "R_NN" NOT NULL);
CREATE INDEX "T_S" ON "T" ("S" ASC);
CREATE INDEX "T_VC" ON "T" ("VC" ASC);
ALTER TABLE "T" ADD CONSTRAINT "T_SA_UK" UNIQUE (S, A);
ALTER TABLE "T" ADD CONSTRAINT "T_S_CK" CHECK ("S" > 0);
ALTER TABLE "T" ADD CONSTRAINT "T_VC_CK" CHECK ("VC" > 0);
ALTER TABLE "T" ADD CONSTRAINT "T_SA_CK" CHECK ("S" >= "A");
ALTER TABLE "T" ADD CONSTRAINT "T_VCS_CK" CHECK ("VC" <= "S" + 100);
ALTER TABLE "CHILD" ADD CONSTRAINT "CHILD_FK" FOREIGN KEY ("S_REF", "A_REF") REFERENCES "T" ("S", "A");
ALTER TABLE "NEWCHILD" ADD CONSTRAINT "NEWCHILD_FK" FOREIGN KEY ("S_REF", "A_REF") REFERENCES "T" ("S", "A");