/* Table: DEFAULTS, Owner: SYSDBA */
CREATE TABLE DEFAULTS (PRIMARYSERVER VARCHAR(200),
        RESPONSIBLEPERSON VARCHAR(200),
        REFRESHTIME INTEGER,
        RETRYTIME INTEGER,
        EXPIRETIME INTEGER,
        MINIMUMTTL INTEGER);

/* Table: HOSTS, Owner: SYSDBA */
CREATE TABLE HOSTS (HOSTID INTEGER NOT NULL,
        ADDRESSH INTEGER,
        ADDRESSL INTEGER,
        TTL INTEGER,
        ZONEID INTEGER,
        HOSTNAME VARCHAR(200),
UNIQUE (HOSTID));

/* Table: MX, Owner: SYSDBA */
CREATE TABLE MX (MXID INTEGER NOT NULL,
        ZONEID INTEGER,
        ZONENAME VARCHAR(200),
        SERVERNAME VARCHAR(200),
        PREFERENCE INTEGER,
        TTL INTEGER,
UNIQUE (MXID));

/* Table: NS, Owner: SYSDBA */
CREATE TABLE NS (NSID INTEGER NOT NULL,
        ZONEID INTEGER,
        ZONENAME VARCHAR(200),
        SERVERNAME VARCHAR(200),
        TTL INTEGER,
UNIQUE (NSID));

/* Table: PTR, Owner: SYSDBA */
CREATE TABLE PTR (PTRID INTEGER NOT NULL,
        ZONEID INTEGER,
        INPUTNAME VARCHAR(200),
        OUTPUTNAME VARCHAR(200),
        TTL INTEGER,
UNIQUE (PTRID));

/* Table: SOA, Owner: SYSDBA */
CREATE TABLE SOA (SOAID INTEGER NOT NULL,
        PRIMARYSERVER VARCHAR(200),
        RESPONSIBLEPERSON VARCHAR(200),
        SERIALNUMBER INTEGER,
        REFRESHTIME INTEGER,
        RETRYTIME INTEGER,
        EXPIRETIME INTEGER,
        MINIMUMTTL INTEGER,
        TTL INTEGER,
        ZONEID INTEGER,
        ZONENAME VARCHAR(200),
UNIQUE (SOAID));

/* Table: ZONES, Owner: SYSDBA */
CREATE TABLE ZONES (ZONEID INTEGER NOT NULL,
        PARENTID INTEGER,
        ZONENAME VARCHAR(192),
UNIQUE (ZONEID));

/*  Index definitions for all user tables */
CREATE INDEX HOST_FIND ON HOSTS(HOSTNAME);
CREATE INDEX HOST_MAIN ON HOSTS(ZONEID);
CREATE INDEX MX_FIND ON MX(ZONENAME);
CREATE INDEX MX_MAIN ON MX(ZONEID);
CREATE INDEX NS_FIND ON NS(ZONENAME);
CREATE INDEX NS_MAIN ON NS(ZONEID);
CREATE INDEX PTR_FIND ON PTR(INPUTNAME);
CREATE INDEX PTR_MAIN ON PTR(ZONEID);
CREATE INDEX SOA_FIND ON SOA(ZONENAME);
CREATE INDEX SOA_MAIN ON SOA(ZONEID);
CREATE INDEX ZONES_MAIN ON ZONES(PARENTID, ZONENAME);

CREATE GENERATOR KEY_GEN;

SET TERM ^ ;

/* Triggers only will work for SQL triggers */
CREATE TRIGGER SET_HOST_ID FOR HOSTS                           
ACTIVE BEFORE INSERT POSITION 0 
AS BEGIN
NEW.HostID = GEN_ID(KEY_GEN, 1);END
 ^
CREATE TRIGGER SET_SOA_ID FOR SOA                             
ACTIVE BEFORE INSERT POSITION 0 
AS BEGIN
NEW.SOAID = GEN_ID(KEY_GEN, 1);END
 ^
CREATE TRIGGER SET_ZONE_ID FOR ZONES                           
ACTIVE BEFORE INSERT POSITION 0 
AS BEGIN
NEW.ZoneID = GEN_ID(KEY_GEN, 1);END
 ^
CREATE TRIGGER SET_NS_ID FOR NS                              
ACTIVE BEFORE INSERT POSITION 0 
AS BEGIN
NEW.NSID = GEN_ID(KEY_GEN, 1);END
 ^
CREATE TRIGGER SET_PTR_ID FOR PTR                             
ACTIVE BEFORE INSERT POSITION 0 
AS BEGIN
NEW.PTRID = GEN_ID(KEY_GEN, 1);END
 ^
CREATE TRIGGER SET_MX_ID FOR MX                              
ACTIVE BEFORE INSERT POSITION 0 
AS BEGIN
NEW.MXID = GEN_ID(KEY_GEN, 1);END
 ^
COMMIT WORK ^
SET TERM ; ^