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

-- 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";

-- Table for VRF list
CREATE TABLE vrfs (
    vrf varchar(32) DEFAULT '' NOT NULL,
    comment text DEFAULT '' NOT NULL,
    location varchar(4) DEFAULT '' NOT NULL
);

COPY vrfs (vrf, comment, location) FROM stdin;
DEFAULT Initial default VRF     
\.

ALTER TABLE ONLY vrfs
    ADD CONSTRAINT vrfs_pkey PRIMARY KEY (vrf);

-- Table for tracking active netblock assignments and containers.
-- Note that maskbits is obsolete but left in for a release cycle or two
-- so that legacy dumps can still be loaded, in the unlikely event.
CREATE TABLE allocations (
    cidr cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
    "type" character(2) DEFAULT ''::bpchar,
    city character varying(30) DEFAULT ''::character varying,
    description character varying(128) DEFAULT ''::character varying,
    notes text DEFAULT ''::text,
    maskbits integer DEFAULT 128,
    circuitid character varying(128) DEFAULT ''::character varying,
    createstamp timestamp with time zone DEFAULT now(),
    modifystamp timestamp with time zone DEFAULT now(),
    privdata text DEFAULT ''::text NOT NULL,
    custid character varying(16) DEFAULT ''::character varying,
    swip character(1) DEFAULT 'n'::bpchar,
    vrf text DEFAULT ''::text NOT NULL,
    vlan text DEFAULT ''::text NOT NULL,
    rdns text DEFAULT ''::text NOT NULL,
    parent_id integer DEFAULT 0 NOT NULL,
    master_id integer DEFAULT 0 NOT NULL,
    backup_id integer DEFAULT 0 NOT NULL,
    id serial NOT NULL
);

ALTER TABLE ONLY allocations
    ADD CONSTRAINT allocations_pkey PRIMARY KEY (id);
CREATE UNIQUE INDEX allocations_skey ON allocations (cidr,vrf,type);

-- Customer or POP site cities or locations.
CREATE TABLE cities (
    id serial NOT NULL,
    city character varying(30) DEFAULT ''::character varying NOT NULL,
    routing character(1) DEFAULT 'n'::bpchar NOT NULL
);

ALTER TABLE ONLY cities
    ADD CONSTRAINT cities_pkey PRIMARY KEY (id);

-- Local table for rWHOIS customer data
CREATE TABLE customers (
    custid character varying(16) DEFAULT ''::character varying 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
);

ALTER TABLE ONLY customers
    ADD CONSTRAINT customers_pkey PRIMARY KEY (custid);

-- Flag table for deciding if we can usefully do rDNS RPC calls.
CREATE TABLE dnsavail (
    "zone" cidr,
    "location" varchar(4) DEFAULT '',
    parent_alloc integer NOT NULL
);

ALTER TABLE ONLY dnsavail
    ADD CONSTRAINT dnsavail_pkey PRIMARY KEY ("zone",parent_alloc);

-- Store backup fields in a separate table
CREATE TABLE backuplist (
    backup_id serial NOT NULL,
    bkbrand text,
    bkmodel text,
    bktype text,
    bkport integer,
    bksrc text,
    bkuser text,
    bkvpass text,
    bkepass text,
    bkip inet
);

-- Table for tracking netblocks available for assignment
CREATE TABLE freeblocks (
    cidr cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
    maskbits integer DEFAULT 128,
    city character varying(30) DEFAULT ''::character varying,
    routed character(1) DEFAULT 'n'::bpchar,
    vrf text DEFAULT ''::text NOT NULL,
    parent_id integer DEFAULT 0 NOT NULL,
    master_id integer DEFAULT 0 NOT NULL,
    reserve_for integer DEFAULT 0 NOT NULL,
    id serial NOT NULL
);

ALTER TABLE ONLY freeblocks
    ADD CONSTRAINT freeblocks_pkey PRIMARY KEY (cidr, parent_id);

-- Table for arbitrary infonotices tagged to an allocation
CREATE TABLE blocknotices (
    alloc_id integer NOT NULL,
    ipflag boolean DEFAULT false NOT NULL,
    notice text DEFAULT ''::text NOT NULL
);

ALTER TABLE ONLY blocknotices
    ADD CONSTRAINT blocknotices_pkey PRIMARY KEY (alloc_id, ipflag);

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


-- Table for tracking single IP assignments.  Single static IP assignments
-- need somewhat stronger clustering than provided by other types.
CREATE TABLE poolips (
    pool cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
    ip cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
    city character varying(30) DEFAULT ''::character varying NOT NULL,
    "type" character(2) DEFAULT ''::bpchar NOT NULL,
    available character(1) DEFAULT 'y'::bpchar NOT NULL,
    notes text DEFAULT ''::text NOT NULL,
    description character varying(128) DEFAULT ''::character varying NOT NULL,
    circuitid character varying(128) DEFAULT ''::character varying NOT NULL,
    privdata text DEFAULT ''::text NOT NULL,
    custid character varying(16) DEFAULT ''::character varying,
    createstamp timestamp with time zone DEFAULT now(),
    modifystamp timestamp with time zone DEFAULT now(),
    vrf text DEFAULT ''::text NOT NULL,
    vlan text DEFAULT ''::text NOT NULL,
    rdns text DEFAULT ''::text NOT NULL,
    parent_id integer DEFAULT 0 NOT NULL,
    master_id integer DEFAULT 0 NOT NULL,
    backup_id integer DEFAULT 0 NOT NULL,
    id serial NOT NULL,
    CONSTRAINT poolips_available_check CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
);

ALTER TABLE ONLY poolips
    ADD CONSTRAINT poolips_pkey PRIMARY KEY (ip, parent_id);


-- Combined netblock+IP view for searches
CREATE VIEW searchme AS
    SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city,
      allocations.description, allocations.notes, allocations.circuitid, allocations.vrf,
      allocations.vlan, allocations.id, allocations.parent_id, allocations.master_id, 'n' AS available
    FROM allocations
  UNION
    SELECT poolips.ip AS cidr, poolips.custid, poolips."type", poolips.city,
      poolips.description, poolips.notes, poolips.circuitid, poolips.vrf,
      poolips.vlan, poolips.id, poolips.parent_id, poolips.master_id, poolips.available
    FROM poolips;


-- Type list for assignments
CREATE TABLE alloctypes (
    "type" character(2) DEFAULT ''::bpchar NOT NULL,
    listname character varying(40) DEFAULT ''::character varying,
    dispname character varying(40) DEFAULT ''::character varying,
    listorder integer DEFAULT 0,
    def_custid character varying(16) DEFAULT ''::character varying,
    arin_netname character varying(20) DEFAULT 'ISP'::character varying
);

ALTER TABLE ONLY alloctypes
    ADD CONSTRAINT alloctypes_pkey PRIMARY KEY ("type");


-- Initial/standard allocation types.  Update def_custid and arin_netname as appropriate.
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 aggregation     Routing aggregation     500     5554242 ISP
mm      Master block    Master block    999     5554242 ISP
\.

-- User data table - required for proper ACLs
CREATE TABLE users (
    username character varying(256) NOT NULL,
    "password" character varying(256) DEFAULT ''::character varying,
    acl character varying(256) DEFAULT 'b'::character varying
);

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (username);

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


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