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