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: Basic tables with primary keys, foreign keys, unique constraints, check constraints, and indexes
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "DEPARTMENTS",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER",
"is_identity": true
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)"
},
{
"name": "BUDGET",
"position": 3,
"default": "0",
"nullable": true,
"type": "NUMBER(12, 2)"
}
],
"indexes": [
{
"name": "PK_DEPARTMENTS",
"expressions": [
"ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
}
]
},
{
"name": "EMPLOYEES",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER",
"is_identity": true
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)"
},
{
"name": "EMAIL",
"position": 3,
"nullable": true,
"type": "VARCHAR2(100 BYTE)"
},
{
"name": "DEPARTMENT_ID",
"position": 4,
"nullable": true,
"type": "NUMBER"
},
{
"name": "SALARY",
"position": 5,
"nullable": true,
"type": "NUMBER(10, 2)"
},
{
"name": "HIRE_DATE",
"position": 6,
"default": "SYSDATE",
"nullable": true,
"type": "DATE"
},
{
"name": "IS_ACTIVE",
"position": 7,
"default": "1",
"nullable": true,
"type": "NUMBER(1)"
}
],
"indexes": [
{
"name": "PK_EMPLOYEES",
"expressions": [
"ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
},
{
"name": "UK_EMPLOYEES_EMAIL",
"expressions": [
"EMAIL"
],
"type": "NORMAL",
"unique": true,
"visible": true,
"is_constraint": true
},
{
"name": "IDX_EMP_DEPT",
"expressions": [
"DEPARTMENT_ID"
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_EMP_NAME",
"expressions": [
"NAME"
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
],
"foreign_keys": [
{
"name": "FK_DEPT",
"columns": [
"DEPARTMENT_ID"
],
"referenced_table": "DEPARTMENTS",
"referenced_columns": [
"ID"
]
}
],
"check_constraints": [
{
"name": "CHK_EMPLOYEES_IS_ACTIVE",
"expression": "IS_ACTIVE IN (0, 1)"
}
]
}
]
}
]
}
expected: |+
CREATE TABLE "DEPARTMENTS" (
"ID" NUMBER NOT NULL,
"NAME" VARCHAR2(100 BYTE) NOT NULL,
"BUDGET" NUMBER(12, 2) DEFAULT 0,
CONSTRAINT "PK_DEPARTMENTS" PRIMARY KEY ("ID")
);
CREATE TABLE "EMPLOYEES" (
"ID" NUMBER NOT NULL,
"NAME" VARCHAR2(100 BYTE) NOT NULL,
"EMAIL" VARCHAR2(100 BYTE),
"DEPARTMENT_ID" NUMBER,
"SALARY" NUMBER(10, 2),
"HIRE_DATE" DATE DEFAULT SYSDATE,
"IS_ACTIVE" NUMBER(1) DEFAULT 1,
CONSTRAINT "PK_EMPLOYEES" PRIMARY KEY ("ID"),
CONSTRAINT "UK_EMPLOYEES_EMAIL" UNIQUE ("EMAIL"),
CONSTRAINT "CHK_EMPLOYEES_IS_ACTIVE" CHECK (IS_ACTIVE IN (0, 1)),
CONSTRAINT "FK_DEPT" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "DEPARTMENTS" ("ID")
);
CREATE INDEX "IDX_EMP_DEPT" ON "EMPLOYEES" ("DEPARTMENT_ID" ASC);
CREATE INDEX "IDX_EMP_NAME" ON "EMPLOYEES" ("NAME" ASC);
- description: A view whose INSTEAD OF trigger must survive the definition round trip
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "EMPLOYEES",
"columns": [
{
"name": "EMP_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "EMAIL",
"position": 2,
"nullable": true,
"type": "VARCHAR2(100 BYTE)"
}
],
"indexes": [
{
"name": "PK_EMPLOYEES",
"expressions": [
"EMP_ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
}
]
}
],
"views": [
{
"name": "EMPLOYEE_VIEW",
"definition": "SELECT EMP_ID, EMAIL\nFROM EMPLOYEES",
"triggers": [
{
"name": "EMPLOYEE_VIEW_INSERT_TRG",
"body": "CREATE OR REPLACE TRIGGER EMPLOYEE_VIEW_INSERT_TRG\nINSTEAD OF INSERT ON EMPLOYEE_VIEW\nFOR EACH ROW\nBEGIN\n INSERT INTO EMPLOYEES (EMP_ID, EMAIL) VALUES (:NEW.EMP_ID, :NEW.EMAIL);\nEND;"
}
]
}
]
}
]
}
expected: |+
CREATE TABLE "EMPLOYEES" (
"EMP_ID" NUMBER NOT NULL,
"EMAIL" VARCHAR2(100 BYTE),
CONSTRAINT "PK_EMPLOYEES" PRIMARY KEY ("EMP_ID")
);
CREATE VIEW "EMPLOYEE_VIEW" AS SELECT EMP_ID, EMAIL
FROM EMPLOYEES;
CREATE OR REPLACE TRIGGER EMPLOYEE_VIEW_INSERT_TRG
INSTEAD OF INSERT ON EMPLOYEE_VIEW
FOR EACH ROW
BEGIN
INSERT INTO EMPLOYEES (EMP_ID, EMAIL) VALUES (:NEW.EMP_ID, :NEW.EMAIL);
END;
- description: An object table is left out, and invisible and virtual columns keep their clauses
metadata: |
{
"name": "TESTSCHEMA",
"schemas": [
{
"tables": [
{
"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",
"nullable": true,
"generation": {
"type": "TYPE_VIRTUAL",
"expression": "\"A\"+1"
}
},
{
"name": "H",
"position": 3,
"type": "NUMBER",
"nullable": true,
"is_invisible": true
},
{
"name": "HV",
"position": 4,
"type": "NUMBER",
"is_invisible": true,
"default": "7"
},
{
"name": "IHV",
"position": 5,
"type": "NUMBER",
"nullable": true,
"is_invisible": true,
"generation": {
"type": "TYPE_VIRTUAL",
"expression": "\"A\"*2"
}
}
]
}
]
}
]
}
expected: |+
CREATE TABLE "T" (
"A" NUMBER NOT NULL,
"VC" NUMBER GENERATED ALWAYS AS ("A"+1) VIRTUAL,
"H" NUMBER INVISIBLE,
"HV" NUMBER INVISIBLE DEFAULT 7 NOT NULL,
"IHV" NUMBER INVISIBLE GENERATED ALWAYS AS ("A"*2) VIRTUAL
);