source: trunk/cgi-bin/ipdb.psql@ 321

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 "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 "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 "ipdb";
GRANT SELECT on "routed" to "ipdb";

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

REVOKE ALL on "temp" from PUBLIC;
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 "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" character varying(16) DEFAULT '',
        "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 "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" character varying(16) DEFAULT '',
        swip character(1) DEFAULT 'n'
);

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

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

REVOKE ALL on "searchme" from PUBLIC;
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 ''
);

--
-- Name: alloctypes; Type: TABLE DATA; Schema: public; Owner: ipdb
--

COPY alloctypes ("type", listname, dispname, listorder, def_custid) FROM stdin;
cn      Customer netblock       Customer netblock       0       
si      Static IP - Server pool Server pool IP  20      
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      
sd      Static Pool - Servers   Server pool     40      6750400
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
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
ve      Dynamic VoIP block      Dynamic VoIP block      105     DYN-VOIP
li      Static IP - LAN/POP     Static LAN/POP IP       190     6750400
ai      Static IP - Managment   Static management IP    192     6750400
ad      Static Pool - Managment Management pool 196     6750400
ld      Static Pool - LAN/POP   LAN pool        195     6750400
wc      Reserve for CORE/WAN blocks     CORE/WAN blocks 200     6750400
pc      Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201     6750400
ac      Reserve for ATM ATM blocks      202     6750400
wr      CORE/WAN block  CORE/WAN block  220     6750400
pr      Dynamic-route DSL netblock      Dynamic-route DSL       221     
ar      ATM block       ATM block       222     
rm      Routing Routed netblock 500     6750400
in      Internal netblock       Internal netblock       990     6750400
mm      Master block    Master block    999     6750400
\.

REVOKE ALL on "alloctypes" from PUBLIC;
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 "ipdb";

--
-- 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'
);