SCRIPT
BEGIN

   EXECUTE IMMEDIATE 'CREATE TABLE "zonedef"
(
"zid" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"name" VARCHAR(512) COLLATE "UNI" NOT NULL,
"desc" CLOB COLLATE "UNI",
"kind" INTEGER,
"umode" CLOB COLLATE "UNI",
"lmode" CLOB COLLATE "UNI",
"utopid" INTEGER,
"ltopid" INTEGER,
"udepth" FLOAT,
"ldepth" FLOAT,
"uoffset" FLOAT,
"loffset" FLOAT,
"remarks" CLOB COLLATE "UNI",
"adddate" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"chgdate" TIMESTAMP,
CONSTRAINT "primary" PRIMARY KEY ("zid"),
CONSTRAINT "namekey" UNIQUE ("name")
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768';
   
   EXECUTE IMMEDIATE 'CREATE TRIGGER "zonedef_update" BEFORE UPDATE ON "zonedef"
BEGIN
    set NEWROW.chgdate = CURRENT_TIMESTAMP;
END
';
   
   EXECUTE IMMEDIATE 'CREATE TABLE "zonefielddef"
(
"fid" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"zid" INTEGER NOT NULL,
"name" VARCHAR(512) COLLATE "UNI" NOT NULL,
"source" VARCHAR(512) COLLATE "UNI" NOT NULL,
"desc" CLOB COLLATE "UNI",
"units" CLOB COLLATE "UNI",
"kind" CLOB COLLATE "UNI",
"ndec" INTEGER,
"flags" INTEGER,
"unitstype" INTEGER,
"remarks" CLOB COLLATE "UNI",
"adddate" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"chgdate" TIMESTAMP,
CONSTRAINT "primary" PRIMARY KEY ("fid"),
CONSTRAINT "zidkey" FOREIGN KEY ("zid") REFERENCES "zonedef" ("zid"),
CONSTRAINT "zidfidkey" UNIQUE ("zid", "fid"),
CONSTRAINT "zidnamesourcekey" UNIQUE ("zid", "name", "source")
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 8288
BLOB BLOCK SIZE 1024
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768';
   
   EXECUTE IMMEDIATE 'CREATE TRIGGER "zonedeffield_update" BEFORE UPDATE ON "zonefielddef"
BEGIN
    set NEWROW.chgdate = CURRENT_TIMESTAMP;
END
';
END