CREATE TABLE area (
areaaddr                 INT UNSIGNED NOT NULL DEFAULT 0,
descrip                  VARCHAR(80) NOT NULL,
areaindex                BIGINT NOT NULL AUTO_INCREMENT,
customer                 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                 PRIMARY KEY (areaindex)
)TYPE=INNODB;


ALTER TABLE area ADD  UNIQUE INDEX area_customer  (customer, descrip);


ALTER TABLE area ADD  UNIQUE INDEX area_areaaddr  (areaaddr, customer);


CREATE TABLE auditlog (
userid                   VARCHAR(40),
action                   VARCHAR(254) NOT NULL,
dt                       TIMESTAMP NOT NULL
)TYPE=INNODB;


ALTER TABLE auditlog ADD  INDEX auditlog_dt  (dt);


CREATE TABLE base (
baseaddr                 INT UNSIGNED NOT NULL DEFAULT 0,
subnetsize               INT UNSIGNED NOT NULL DEFAULT 0,
descrip                  VARCHAR(80) NOT NULL,
baseindex                BIGINT NOT NULL AUTO_INCREMENT,
admingrp                 VARCHAR(40) NOT NULL,
customer                 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
lastmod                  TIMESTAMP NOT NULL,
userid                   VARCHAR(40),
baseopt                  INT UNSIGNED DEFAULT 0,
swipmod                  DATETIME,
                 PRIMARY KEY (baseindex)
)TYPE=INNODB;


ALTER TABLE base ADD  UNIQUE INDEX base_baseaddr  (baseaddr, customer);


ALTER TABLE base ADD  INDEX base_customer  (customer);


ALTER TABLE base ADD  INDEX base_admingrp  (admingrp);


CREATE TABLE baseadd (
baseindex                BIGINT NOT NULL DEFAULT 0,
info                     LONGTEXT,
infobin                  LONGBLOB,
infobinfn                VARCHAR(255),
                 PRIMARY KEY (baseindex)
)TYPE=INNODB;


CREATE TABLE custinfo (
customer                 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
org                      VARCHAR(100),
street                   VARCHAR(255),
city                     VARCHAR(80),
state                    VARCHAR(2),
zipcode                  VARCHAR(10),
cntry                    VARCHAR(2),
maint                    VARCHAR(80),
nichandl                 VARCHAR(80),
lname                    VARCHAR(80),
fname                    VARCHAR(80),
mname                    VARCHAR(80),
torg                     VARCHAR(100),
tstreet                  VARCHAR(255),
tcity                    VARCHAR(80),
tstate                   VARCHAR(2),
tzipcode                 VARCHAR(10),
tcntry                   VARCHAR(2),
phne                     VARCHAR(20),
mbox                     VARCHAR(100),
                 PRIMARY KEY (customer)
)TYPE=INNODB;


CREATE TABLE custadd (
customer                 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
info                     LONGTEXT,
infobinfn                VARCHAR(255),
                 PRIMARY KEY (customer)
)TYPE=INNODB;


CREATE TABLE customer (
custdescrip              VARCHAR(80) NOT NULL,
customer                 SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
crm                      VARCHAR(20),
admingrp                 VARCHAR(40) NOT NULL,
                 PRIMARY KEY (customer)
)TYPE=INNODB;


ALTER TABLE customer ADD  UNIQUE INDEX customer_custdescrip  (custdescrip);


ALTER TABLE customer ADD  INDEX customer_crm  (crm);


ALTER TABLE customer ADD  INDEX customer_admingrp  (admingrp);


CREATE TABLE grp (
grpdescrip               VARCHAR(80) NOT NULL,
grp                      VARCHAR(40) NOT NULL,
createcust               VARCHAR(1) NOT NULL DEFAULT 'N',
resaddr                  SMALLINT UNSIGNED DEFAULT 0,
grpopt                   INT UNSIGNED NOT NULL DEFAULT 0,
                 PRIMARY KEY (grp)
)TYPE=INNODB;


ALTER TABLE grp ADD  UNIQUE INDEX grp_grpdescrip  (grpdescrip);


CREATE TABLE ipaddr (
ipaddr                   INT UNSIGNED NOT NULL DEFAULT 0,
userinf                  VARCHAR(80),
location                 VARCHAR(80),
telno                    VARCHAR(15),
descrip                  VARCHAR(80),
hname                    VARCHAR(100),
macaddr                  VARCHAR(12),
baseindex                BIGINT NOT NULL DEFAULT 0,
lastmod                  TIMESTAMP NOT NULL,
lastpol                  DATETIME,
userid                   VARCHAR(40),
                 PRIMARY KEY (ipaddr, baseindex)
)TYPE=INNODB;


ALTER TABLE ipaddr ADD  INDEX ipaddr_baseindex  (baseindex);


CREATE TABLE ipaddradd (
ipaddr                   INT UNSIGNED NOT NULL DEFAULT 0,
baseindex                BIGINT NOT NULL DEFAULT 0,
info                     LONGTEXT,
infobin                  LONGBLOB,
infobinfn                VARCHAR(255),
                 PRIMARY KEY (ipaddr, baseindex)
)TYPE=INNODB;


ALTER TABLE ipaddradd ADD  INDEX ipaddradd_baseindex  (baseindex);


CREATE TABLE ipaddrlnk (
ipaddr                   INT UNSIGNED NOT NULL DEFAULT 0,
baseindex                BIGINT NOT NULL DEFAULT 0,
customer                 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
dstipaddr                INT UNSIGNED NOT NULL DEFAULT 0,
dstcustomer              SMALLINT UNSIGNED NOT NULL DEFAULT 0
)TYPE=INNODB;


ALTER TABLE ipaddrlnk ADD  UNIQUE INDEX ipaddrlnk_uniq  (baseindex, ipaddr, customer, dstipaddr, dstcustomer);


ALTER TABLE ipaddrlnk ADD  INDEX ipaddrlnk_dst  (dstipaddr, dstcustomer);


ALTER TABLE ipaddrlnk ADD  INDEX ipaddrlnk_src  (ipaddr, customer);


CREATE TABLE requestip (
requestindex             INTEGER NOT NULL AUTO_INCREMENT,
customer                 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
requestdesc              VARCHAR(80),
userinf                  VARCHAR(80),
location                 VARCHAR(80),
telno                    VARCHAR(15),
descrip                  VARCHAR(80),
hname                    VARCHAR(100),
macaddr                  VARCHAR(12),
lastmod                  TIMESTAMP NOT NULL,
info                     LONGTEXT,
                 PRIMARY KEY (requestindex)
)TYPE=INNODB;


ALTER TABLE requestip ADD  UNIQUE INDEX requestip_desc  (customer, requestdesc);


CREATE TABLE netrange (
rangeaddr                INT UNSIGNED NOT NULL DEFAULT 0,
rangesize                INT UNSIGNED NOT NULL DEFAULT 0,
descrip                  VARCHAR(80) NOT NULL,
rangeindex               BIGINT NOT NULL AUTO_INCREMENT,
areaindex                BIGINT NOT NULL DEFAULT 0,
customer                 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                 PRIMARY KEY (rangeindex)
)TYPE=INNODB;


ALTER TABLE netrange ADD  INDEX range_rangeaddr  (rangeaddr, customer);


ALTER TABLE netrange ADD  UNIQUE INDEX range_customer  (customer, descrip);


ALTER TABLE netrange ADD  INDEX range_areaindex  (areaindex);


CREATE TABLE revdns (
customer                 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
hname                    VARCHAR(100) NOT NULL,
ipaddr                   VARCHAR(15) NOT NULL,
horder                   TINYINT UNSIGNED NOT NULL DEFAULT 0
)TYPE=INNODB;


ALTER TABLE revdns ADD  INDEX revdns_customer  (customer);


CREATE TABLE version (
version                  INTEGER NOT NULL DEFAULT 0
)TYPE=INNODB;


CREATE TABLE users (
userid                   VARCHAR(40) NOT NULL,
userdescrip              VARCHAR(80) NOT NULL,
useremail                VARCHAR(64),
password                 VARCHAR(40) NOT NULL,
                 PRIMARY KEY (userid)
)TYPE=INNODB;


CREATE TABLE usergrp (
userid                   VARCHAR(40) NOT NULL,
grp                      VARCHAR(40) NOT NULL
)TYPE=INNODB;


ALTER TABLE usergrp ADD  UNIQUE INDEX usergrp_userid  (userid, grp);


ALTER TABLE usergrp ADD  INDEX usergrp_grp  (grp);


CREATE TABLE bounds (
boundsaddr               INT UNSIGNED NOT NULL DEFAULT 0,
boundssize               INT UNSIGNED NOT NULL DEFAULT 0,
grp                      VARCHAR(40) NOT NULL
)TYPE=INNODB;


ALTER TABLE bounds ADD  INDEX bounds_grp  (grp);


ALTER TABLE bounds ADD  INDEX bounds_boundsaddr  (boundsaddr);


CREATE TABLE fwdzone (
data_id                  BIGINT NOT NULL AUTO_INCREMENT,
domain                   VARCHAR(254) NOT NULL,
createmod                DATE,
lastmod                  DATETIME,
regmod                   DATE,
expiremod                DATETIME,
engineer                 VARCHAR(254),
error_message            VARCHAR(254),
responsiblemail          VARCHAR(64),
serialdate               VARCHAR(8) NOT NULL,
serialnum                INTEGER DEFAULT 0,
ttl                      INTEGER DEFAULT 0,
refresh                  INTEGER DEFAULT 0,
retry                    INTEGER DEFAULT 0,
expire                   INTEGER DEFAULT 0,
minimum                  INTEGER DEFAULT 0,
userid                   VARCHAR(40),
lastexp                  DATETIME,
slaveonly                VARCHAR(1) NOT NULL DEFAULT 'N',
zonefilepath1            VARCHAR(254),
zonefilepath2            VARCHAR(254),
customer                 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
admingrp                 VARCHAR(40),
                 PRIMARY KEY (data_id)
)TYPE=INNODB;


ALTER TABLE fwdzone ADD  INDEX fwdzone_customer  (customer);


ALTER TABLE fwdzone ADD  INDEX fwdzone_domain  (domain);


CREATE TABLE fwdzoneadd (
customer                 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
data_id                  BIGINT NOT NULL DEFAULT 0,
info                     LONGTEXT,
infobinfn                VARCHAR(255)
)TYPE=INNODB;


ALTER TABLE fwdzoneadd ADD  UNIQUE INDEX fwdzoneadd_customer  (customer, data_id);


CREATE TABLE fwdzonerec (
recidx                   BIGINT NOT NULL AUTO_INCREMENT,
data_id                  BIGINT NOT NULL DEFAULT 0,
lastmod                  DATETIME,
host                     VARCHAR(254) NOT NULL,
recordtype               VARCHAR(5) NOT NULL,
ip_hostname              VARCHAR(254) NOT NULL,
error_message            VARCHAR(254),
sortorder                INTEGER NOT NULL DEFAULT 0,
userid                   VARCHAR(40) NOT NULL,
customer                 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                 PRIMARY KEY (recidx)
)TYPE=INNODB;


ALTER TABLE fwdzonerec ADD  INDEX fwdzonerec_data_id  (data_id);


ALTER TABLE fwdzonerec ADD  INDEX fwdzonerec_sortorder  (sortorder);


ALTER TABLE fwdzonerec ADD  INDEX fwdzonerec_customer  (customer);


CREATE TABLE fwddns (
id                       BIGINT NOT NULL DEFAULT 0,
hname                    VARCHAR(100) NOT NULL,
horder                   TINYINT UNSIGNED NOT NULL DEFAULT 0
)TYPE=INNODB;


ALTER TABLE fwddns ADD  INDEX fwddns_id  (id);


CREATE TABLE zones (
id                       BIGINT NOT NULL AUTO_INCREMENT,
zoneip                   INT UNSIGNED NOT NULL DEFAULT 0,
zonesize                 INT UNSIGNED NOT NULL DEFAULT 0,
zone                     VARCHAR(254) NOT NULL,
error_message            VARCHAR(254),
lastmod                  DATETIME,
serialdate               VARCHAR(8) NOT NULL,
serialnum                INTEGER DEFAULT 0,
ttl                      INTEGER DEFAULT 0,
refresh                  INTEGER DEFAULT 0,
retry                    INTEGER DEFAULT 0,
expire                   INTEGER DEFAULT 0,
minimum                  INTEGER DEFAULT 0,
userid                   VARCHAR(40),
lastexp                  DATETIME,
slaveonly                VARCHAR(1) NOT NULL DEFAULT 'N',
zonefilepath1            VARCHAR(254),
zonefilepath2            VARCHAR(254),
responsiblemail          VARCHAR(64),
customer                 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                 PRIMARY KEY (id)
)TYPE=INNODB;


ALTER TABLE zones ADD  INDEX zones_customer  (customer);


ALTER TABLE zones ADD  INDEX zones_zoneip  (zoneip);


CREATE TABLE zonedns (
id                       BIGINT NOT NULL DEFAULT 0,
hname                    VARCHAR(100) NOT NULL,
horder                   TINYINT UNSIGNED NOT NULL DEFAULT 0
)TYPE=INNODB;


ALTER TABLE zonedns ADD  INDEX zonedns_id  (id);


BEGIN;


INSERT INTO version (version) VALUES (22);


COMMIT;