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

DROP DATABASE ipdb;

CREATE USER ipdb WITH PASSWORD 'ipdbpwd';

CREATE DATABASE ipdb;

-- Need to do this or our triggers don't work.  Why do we need to do this?
CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;
CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";

UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE usename='ipdb')
        WHERE datname='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),
        "country" character(2),
        "pocode" character varying(7),
        "phone" character varying(15),
        "tech_handle" character varying(50),
        "abuse_handle" character varying(50),
        "admin_handle" character varying(50),
        "def_rdns" character varying(40),
        "special" 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 with time zone DEFAULT now(),
        "mtime" timestamp with time zone DEFAULT now(),
        "rwhois" character(1) DEFAULT 'n' NOT NULL
);

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 with time zone DEFAULT now()
);

REVOKE ALL on "routed" from PUBLIC;
GRANT ALL on "routed" to "ipdb";
GRANT SELECT on "routed" 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,
        "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,
        "custid" character varying(16) DEFAULT '',
        "createstamp" timestamp with time zone DEFAULT now(),
        "modifystamp" timestamp with time zone 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,
        "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 with time zone DEFAULT now(),
        "modifystamp" timestamp with time zone DEFAULT now(),
        "privdata" text DEFAULT '' NOT NULL,
        "custid" 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, allocations.circuitid FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes, poolips.circuitid 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 '',
        "arin_netname" character varying(20) DEFAULT 'ISP'
);

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

COPY alloctypes ("type", listname, dispname, listorder, def_custid, arin_netname) FROM stdin;
cn      Customer netblock       Customer netblock       0               ISPCUST
si      Static IP - Server pool Server pool IP  20              ISP
ci      Static IP - Cable       Static cable IP 21              ISP
di      Static IP - DSL Static DSL IP   22              ISP
mi      Static IP - Dialup      Static dialup IP        23              ISP
wi      Static IP - Wireless    Static wireless IP      24              ISP
sd      Static Pool - Servers   Server pool     40      5554242 ISP
cd      Static Pool - Cable     Cable pool      41      CBL-BUS ISP-STATIC-CABLE
dp      Static Pool - DSL       DSL pool        42      DSL-BUS ISP-STATIC-DSL
mp      Static Pool - Dialup    Static dialup pool      43      DIAL-BUS        ISP-STATIC-DIAL
wp      Static Pool - Wireless  Static wireless pool    44      WL-BUS  ISP-STATIC-WIFI
en      End-use netblock        End-use netblock        100     5554242 ISP
me      Dialup netblock Dialup netblock 101     DIAL-RES        ISP-DIAL
de      Dynamic DSL block       Dynamic DSL block       102     DSL-RES ISP-DSL
ce      Dynamic cable block     Dynamic cable block     103     CBL-RES ISP-CABLE
we      Dynamic WiFi block      Dynamic WiFi block      104     WL-RES  ISP-WIFI
ve      Dynamic VoIP block      Dynamic VoIP block      105     DYN-VOIP        ISP
li      Static IP - LAN/POP     Static LAN/POP IP       190     NOC-VPN ISP
ai      Static IP - Management  Static management IP    192     NOC-VPN ISP
bi      Static IP - Wifi CPE    Wifi CPE IP     193             ISP
ld      Static Pool - LAN/POP   LAN pool        195     NOC-VPN ISP
ad      Static Pool - Management        Management pool 196     NOC-VPN ISP
bd      Static pool - Wifi CPE  Wifi CPE pool   197             ISP
in      Internal netblock       Internal netblock       199     5554242 ISP
wc      Reserve for CORE/WAN blocks     CORE/WAN blocks 200     5554242 ISP
pc      Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201     5554242 ISP-STATIC-DSL
ac      Reserve for ATM ATM blocks      202     5554242 ISP
fc      Reserve for fibre       Fibre blocks    203     5554242 ISP
wr      CORE/WAN block  CORE/WAN block  220     5554242 ISP
pr      Dynamic-route DSL netblock (cust)       Dynamic-route DSL (cust)        221             ISPCUST
ar      ATM block       ATM block       222             ISP
fr      Fibre   Fibre   223             ISP
rm      Routing Routed netblock 500     5554242 ISP
mm      Master block    Master block    999     5554242 ISP
\.

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

-- Default password is admin
INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsA');

-- Network nodes - allows finding customers affected by a broken <x> quickly
CREATE TABLE noderef (
    block inet NOT NULL PRIMARY KEY,
    node_id integer
);

CREATE TABLE nodes (
    node_id serial NOT NULL PRIMARY KEY,
    node_type character varying(2),
    node_name character varying(40),
    node_ip inet
);

-- Email notifications on <action>
CREATE TABLE notify (
    action varchar(5) NOT NULL PRIMARY KEY,
    reciplist varchar(500)
);