CREATE TABLE domains (
        id                INTEGER PRIMARY KEY,
        name              VARCHAR(255) NOT NULL,
        master            VARCHAR(20) DEFAULT NULL,
        last_check        INTEGER DEFAULT NULL,
        type              VARCHAR(6) NOT NULL,
        notified_serial   INTEGER DEFAULT NULL,
        account           VARCHAR(40) DEFAULT NULL
        );

CREATE UNIQUE INDEX name_index ON domains(name);

CREATE TABLE records (
        id              INTEGER PRIMARY KEY,
        domain_id       INTEGER DEFAULT NULL,
        name            VARCHAR(255) DEFAULT NULL,
        type            VARCHAR(6) DEFAULT NULL,
        content         VARCHAR(255) DEFAULT NULL,
        ttl             INTEGER DEFAULT NULL,
        prio            INTEGER DEFAULT NULL,
        change_date     INTEGER DEFAULT NULL
        );

CREATE INDEX rec_name_index ON records(name);
CREATE INDEX nametype_index ON records(name,type);
CREATE INDEX domain_id ON records(domain_id);

CREATE TABLE supermasters (
        ip          VARCHAR(25) NOT NULL,
        nameserver  VARCHAR(255) NOT NULL,
        account     VARCHAR(40) DEFAULT NULL
        );

--
-- rbl domain example
--
-- it's important to only have one master for the domain which has an SOA ttl expiry of a very low value (60 seconds)
--
INSERT INTO domains (name,type,account) values ('REPLACEME.', 'MASTER', "MASTER");
INSERT INTO records (domain_id,name,type,content,ttl) VALUES (1, 'REPLACEME', 'SOA','ns hostmaster 20080221 10800 5400 10800 10800', 60);
INSERT INTO records (domain_id,name,type,content,ttl) VALUES (1, 'REPLACEME', 'NS', 'ns', 10800);

-- INSERT INTO domains (name,type,account) values ('rbl.kewl.org', 'MASTER', "MASTER");
-- INSERT INTO records VALUES(1127,1,'rbl.kewl.org','SOA','ns0.kewl.org. hostmaster.kewl.org. 0 10800 5400 10800 10800',60,NULL,NULL);
-- INSERT INTO records VALUES(1128,1,'rbl.kewl.org','NS','ns0.kewl.org.',10800,NULL,NULL);

-- SOA ()
-- TTL (EXPIRY TIME OF RECORD NOT IN ZONE)
-- SERIAL NUMBER
-- REFRESH PERIOD (AXFR INTERVAL)
-- RETRY TIME (IF ABOVE FAILED)
-- EXPIRY TIME (LIFETIME OF NON REFRESHED ZONE)
-- MIN/DEFAULT TTL (DEFAULT TTL FOR A RECORD)