source: branches/privdata/cgi-bin/ipdb.psql@ 337

CREATE DATABASE ipdb;

\connect ipdb ipdb

CREATE TABLE "customers" (
        "custid" character varying(16) DEFAULT '' NOT NULL,
        "name" character varying(64),
        "street" character varying(25),
        "street2" character varying(25),
        "city" character varying(30),
        "province" character(2),
        "pocode" character varying(7),
        "phone" character varying(15),
        "abuse" character varying(50),
        "def_rdns" character varying(40),
        "description" text,
        Constraint "customers_pkey" Primary Key ("custid")
);

REVOKE ALL on "customers" from PUBLIC;
GRANT ALL on "customers" to "kdeugau";
GRANT ALL on "customers" to "ipdb";

CREATE TABLE "masterblocks" (
        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
        "ctime" timestamp DEFAULT now()
);

REVOKE ALL on "masterblocks" from PUBLIC;
GRANT ALL on "masterblocks" to "kdeugau";
GRANT ALL on "masterblocks" to "ipdb";

CREATE TABLE "routed" (
        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
        "maskbits" integer DEFAULT 128,
        "city" character varying(30) DEFAULT '',
        "ctime" timestamp DEFAULT now()
);

REVOKE ALL on "routed" from PUBLIC;
GRANT ALL on "routed" to "kdeugau";
GRANT ALL on "routed" to "ipdb";

CREATE TABLE "temp" (
        "ofs" integer
);

REVOKE ALL on "temp" from PUBLIC;
GRANT ALL on "temp" to "kdeugau";
GRANT ALL on "temp" to "ipdb";

CREATE TABLE "freeblocks" (
        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
        "maskbits" integer DEFAULT 128,
        "city" character varying(30) DEFAULT '',
        "routed" character(1) DEFAULT 'n'
);

REVOKE ALL on "freeblocks" from PUBLIC;
GRANT ALL on "freeblocks" to "kdeugau";
GRANT ALL on "freeblocks" to "ipdb";

CREATE TABLE "poolips" (
        "pool" cidr DEFAULT '255.255.255.255/32' NOT NULL,
        "ip" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
        "custid" character varying(16) DEFAULT '' NOT NULL,
        "city" character varying(30) DEFAULT '' NOT NULL,
        "type" character(2) DEFAULT '' NOT NULL,
        "available" character(1) DEFAULT 'y' NOT NULL,
        "notes" text DEFAULT '' NOT NULL,
        "description" character varying(64) DEFAULT '' NOT NULL,
        "circuitid" character varying(128) DEFAULT '' NOT NULL,
        "privdata" text DEFAULT '' NOT NULL,
        "newcustid" integer,
        "createstamp" timestamp DEFAULT now(),
        "modifystamp" timestamp DEFAULT now(),
        CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
);

REVOKE ALL on "poolips" from PUBLIC;
GRANT ALL on "poolips" to "kdeugau";
GRANT ALL on "poolips" to "ipdb";

CREATE TABLE "allocations" (
        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
        "custid" character varying(16) DEFAULT '',
        "type" character(2) DEFAULT '',
        "city" character varying(30) DEFAULT '',
        "description" character varying(64) DEFAULT '',
        "notes" text DEFAULT '',
        "maskbits" integer DEFAULT 128,
        "circuitid" character varying(128) DEFAULT '',
        "createstamp" timestamp DEFAULT now(),
        "modifystamp" timestamp DEFAULT now(),
        "privdata" text DEFAULT '' NOT NULL,
        "newcustid" integer
);

REVOKE ALL on "allocations" from PUBLIC;
GRANT ALL on "allocations" to "kdeugau";
GRANT ALL on "allocations" to "ipdb";

CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description FROM poolips;

REVOKE ALL on "searchme" from PUBLIC;
GRANT ALL on "searchme" to "kdeugau";
GRANT ALL on "searchme" to "ipdb";

CREATE TABLE "alloctypes" (
        "type" character(2) DEFAULT '' NOT NULL PRIMARY KEY,
        "listname" character varying(40) DEFAULT '',
        "dispname" character varying(40) DEFAULT '',
        "listorder" integer DEFAULT 0,
        "def_custid" character varying(16) DEFAULT ''
);

REVOKE ALL on "alloctypes" from PUBLIC;
GRANT ALL on "alloctypes" to "kdeugau";
GRANT ALL on "alloctypes" to "ipdb";

CREATE TABLE "cities" (
        "id" serial NOT NULL PRIMARY KEY,
        "city" character varying(30) DEFAULT '' NOT NULL,
        "routing" character(1) DEFAULT 'n' NOT NULL
);

REVOKE ALL on "cities" from PUBLIC;
GRANT ALL on "cities" to "kdeugau";
GRANT ALL on "cities" to "ipdb";

CREATE TABLE "alloctypes" (
        "type" character(2) DEFAULT '' NOT NULL,
        "listname" character varying(40) DEFAULT '',
        "dispname" character varying(40) DEFAULT '',
        "listorder" integer DEFAULT 0,
        "def_custid" character varying(16) DEFAULT '',
        Constraint "alloctypes_pkey" Primary Key ("type")
);

REVOKE ALL on "alloctypes" from PUBLIC;
GRANT ALL on "alloctypes" to "kdeugau";
GRANT ALL on "alloctypes" to "ipdb";

COPY "alloctypes" FROM stdin;
cd      Static Pool - Cable     Cable pool      41      CBL-BUS
dp      Static Pool - DSL       DSL pool        42      DSL-BUS
mp      Static Pool - Dialup    Static dialup pool      43      DIAL-BUS
wp      Static Pool - Wireless  Static wireless pool    44      WL-BUS
mm      Master block    Master block    999     6750400
in      Internal netblock       Internal netblock       990     6750400
sd      Static Pool - Servers   Server pool     40      6750400
cn      Customer netblock       Customer netblock       0       
ci      Static IP - Cable       Static cable IP 21      
di      Static IP - DSL Static DSL IP   22      
mi      Static IP - Dialup      Static dialup IP        23      
wi      Static IP - Wireless    Static wireless IP      24      
si      Static IP - Server pool Server pool IP  20      6750400
wc      Reserve for WAN blocks  WAN IP blocks   200     6750400
en      End-use netblock        End-use netblock        100     6750400
me      Dialup netblock Dialup netblock 101     DIAL-RES
de      Dynamic DSL block       Dynamic DSL block       102     DSL-RES
ce      Dynamic cable block     Dynamic cable block     103     CBL-RES
we      Dynamic WiFi block      Dynamic WiFi block      104     WL-RES
rm      Routing Routed netblock 500     6750400
pc      Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201     6750400
pr      Dynamic-route DSL netblock      Dynamic-route DSL       221     
wr      WAN block       WAN block       220     6750400
\.

--
-- Trigger and matching function to update modifystamp on allocations, poolips
--
CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
    BEGIN
        NEW.modifystamp := ''now'';
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
    FOR EACH ROW EXECUTE PROCEDURE up_modtime();

CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
    FOR EACH ROW EXECUTE PROCEDURE up_modtime();

--
-- User data table - required for proper ACLs
--

CREATE TABLE "users" (
        "username" varchar(16) NOT NULL PRIMARY KEY,
        "password" varchar(16) DEFAULT '',
        "acl" varchar(16) DEFAULT 'b'
);

CREATE TABLE "dns" (
        "ip" inet NOT NULL PRIMARY KEY,
        "hostname" character varying(128),
        "auto" character(1) DEFAULT 'y'
);