Changeset 763


Ignore:
Timestamp:
07/29/15 16:21:52 (9 years ago)
Author:
Kris Deugau
Message:

/trunk

Update initial SQL definition file with current structure

File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/cgi-bin/ipdb.psql

    r657 r763  
    1 DROP DATABASE ipdb;
    2 
    3 CREATE USER ipdb WITH PASSWORD 'ipdbpwd';
    4 
    5 CREATE DATABASE ipdb;
    6 
    71-- Need to do this or our triggers don't work.  Why do we need to do this?
    8 CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;
    9 CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";
    10 
    11 UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE usename='ipdb')
    12         WHERE datname='ipdb';
    13 
    14 \connect ipdb ipdb
    15 
    16 CREATE TABLE "customers" (
    17         "custid" character varying(16) DEFAULT '' NOT NULL,
    18         "name" character varying(64),
    19         "street" character varying(25),
    20         "street2" character varying(25),
    21         "city" character varying(30),
    22         "province" character(2),
    23         "country" character(2),
    24         "pocode" character varying(7),
    25         "phone" character varying(15),
    26         "tech_handle" character varying(50),
    27         "abuse_handle" character varying(50),
    28         "admin_handle" character varying(50),
    29         "def_rdns" character varying(40),
    30         "special" text,
    31         Constraint "customers_pkey" Primary Key ("custid")
    32 );
    33 
    34 REVOKE ALL on "customers" from PUBLIC;
    35 GRANT ALL on "customers" to "ipdb";
    36 
    37 CREATE TABLE "masterblocks" (
    38         "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
    39         "ctime" timestamp with time zone DEFAULT now(),
    40         "mtime" timestamp with time zone DEFAULT now(),
    41         "rwhois" character(1) DEFAULT 'n' NOT NULL
    42 );
    43 
    44 REVOKE ALL on "masterblocks" from PUBLIC;
    45 GRANT ALL on "masterblocks" to "ipdb";
    46 
    47 CREATE TABLE "routed" (
    48         "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
    49         "maskbits" integer DEFAULT 128,
    50         "city" character varying(30) DEFAULT '',
    51         "ctime" timestamp with time zone DEFAULT now()
    52 );
    53 
    54 REVOKE ALL on "routed" from PUBLIC;
    55 GRANT ALL on "routed" to "ipdb";
    56 GRANT SELECT on "routed" to "ipdb";
    57 
    58 CREATE TABLE "freeblocks" (
    59         "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
    60         "maskbits" integer DEFAULT 128,
    61         "city" character varying(30) DEFAULT '',
    62         "routed" character(1) DEFAULT 'n'
    63 );
    64 
    65 REVOKE ALL on "freeblocks" from PUBLIC;
    66 GRANT ALL on "freeblocks" to "ipdb";
    67 
    68 CREATE TABLE "poolips" (
    69         "pool" cidr DEFAULT '255.255.255.255/32' NOT NULL,
    70         "ip" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
    71         "city" character varying(30) DEFAULT '' NOT NULL,
    72         "type" character(2) DEFAULT '' NOT NULL,
    73         "available" character(1) DEFAULT 'y' NOT NULL,
    74         "notes" text DEFAULT '' NOT NULL,
    75         "description" character varying(64) DEFAULT '' NOT NULL,
    76         "circuitid" character varying(128) DEFAULT '' NOT NULL,
    77         "privdata" text DEFAULT '' NOT NULL,
    78         "custid" character varying(16) DEFAULT '',
    79         "createstamp" timestamp with time zone DEFAULT now(),
    80         "modifystamp" timestamp with time zone DEFAULT now(),
    81         CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
    82 );
    83 
    84 REVOKE ALL on "poolips" from PUBLIC;
    85 GRANT ALL on "poolips" to "ipdb";
    86 
    87 CREATE TABLE "allocations" (
    88         "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
    89         "type" character(2) DEFAULT '',
    90         "city" character varying(30) DEFAULT '',
    91         "description" character varying(64) DEFAULT '',
    92         "notes" text DEFAULT '',
    93         "maskbits" integer DEFAULT 128,
    94         "circuitid" character varying(128) DEFAULT '',
    95         "createstamp" timestamp with time zone DEFAULT now(),
    96         "modifystamp" timestamp with time zone DEFAULT now(),
    97         "privdata" text DEFAULT '' NOT NULL,
    98         "custid" character varying(16) DEFAULT '',
    99         swip character(1) DEFAULT 'n'
    100 );
    101 
    102 REVOKE ALL on "allocations" from PUBLIC;
    103 GRANT ALL on "allocations" to "ipdb";
    104 
    105 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;
    106 
    107 REVOKE ALL on "searchme" from PUBLIC;
    108 GRANT ALL on "searchme" to "ipdb";
    109 
    110 CREATE TABLE "alloctypes" (
    111         "type" character(2) DEFAULT '' NOT NULL PRIMARY KEY,
    112         "listname" character varying(40) DEFAULT '',
    113         "dispname" character varying(40) DEFAULT '',
    114         "listorder" integer DEFAULT 0,
    115         "def_custid" character varying(16) DEFAULT '',
    116         "arin_netname" character varying(20) DEFAULT 'ISP'
    117 );
    118 
    119 --
    120 -- Name: alloctypes; Type: TABLE DATA; Schema: public; Owner: ipdb
    121 --
    122 
     2-- CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;
     3-- CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";
     4
     5-- Table for tracking active netblock assignments and containers.
     6-- Note that maskbits is obsolete but left in for a release cycle or two
     7-- so that legacy dumps can still be loaded, in the unlikely event.
     8CREATE TABLE allocations (
     9    cidr cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
     10    "type" character(2) DEFAULT ''::bpchar,
     11    city character varying(30) DEFAULT ''::character varying,
     12    description character varying(64) DEFAULT ''::character varying,
     13    notes text DEFAULT ''::text,
     14    maskbits integer DEFAULT 128,
     15    circuitid character varying(128) DEFAULT ''::character varying,
     16    createstamp timestamp with time zone DEFAULT now(),
     17    modifystamp timestamp with time zone DEFAULT now(),
     18    privdata text DEFAULT ''::text NOT NULL,
     19    custid character varying(16) DEFAULT ''::character varying,
     20    swip character(1) DEFAULT 'n'::bpchar,
     21    vrf text DEFAULT ''::text NOT NULL,
     22    vlan text DEFAULT ''::text NOT NULL,
     23    rdns text DEFAULT ''::text NOT NULL,
     24    parent_id integer DEFAULT 0 NOT NULL,
     25    master_id integer DEFAULT 0 NOT NULL,
     26    id serial NOT NULL
     27);
     28
     29ALTER TABLE ONLY allocations
     30    ADD CONSTRAINT allocations_pkey PRIMARY KEY (id);
     31CREATE UNIQUE INDEX allocations_skey ON allocations (cidr,vrf,type);
     32
     33-- Customer or POP site cities or locations.
     34CREATE TABLE cities (
     35    id serial NOT NULL,
     36    city character varying(30) DEFAULT ''::character varying NOT NULL,
     37    routing character(1) DEFAULT 'n'::bpchar NOT NULL
     38);
     39
     40ALTER TABLE ONLY cities
     41    ADD CONSTRAINT cities_pkey PRIMARY KEY (id);
     42
     43-- Local table for rWHOIS customer data
     44CREATE TABLE customers (
     45    custid character varying(16) DEFAULT ''::character varying NOT NULL,
     46    name character varying(64),
     47    street character varying(25),
     48    street2 character varying(25),
     49    city character varying(30),
     50    province character(2),
     51    country character(2),
     52    pocode character varying(7),
     53    phone character varying(15),
     54    tech_handle character varying(50),
     55    abuse_handle character varying(50),
     56    admin_handle character varying(50),
     57    def_rdns character varying(40),
     58    special text
     59);
     60
     61ALTER TABLE ONLY customers
     62    ADD CONSTRAINT customers_pkey PRIMARY KEY (custid);
     63
     64-- Flag table for deciding if we can usefully do rDNS RPC calls.
     65CREATE TABLE dnsavail (
     66    "zone" cidr PRIMARY KEY,
     67    "location" varchar(2) DEFAULT '',
     68    parent_alloc integer NOT NULL
     69);
     70
     71-- Table for tracking netblocks available for assignment
     72CREATE TABLE freeblocks (
     73    cidr cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
     74    maskbits integer DEFAULT 128,
     75    city character varying(30) DEFAULT ''::character varying,
     76    routed character(1) DEFAULT 'n'::bpchar,
     77    vrf text DEFAULT ''::text NOT NULL,
     78    parent_id integer DEFAULT 0 NOT NULL,
     79    master_id integer DEFAULT 0 NOT NULL,
     80    reserve_for integer DEFAULT 0 NOT NULL,
     81    id serial NOT NULL
     82);
     83
     84ALTER TABLE ONLY freeblocks
     85    ADD CONSTRAINT freeblocks_pkey PRIMARY KEY (cidr, parent_id);
     86
     87-- Network nodes - allows finding customers affected by a broken <x> quickly
     88CREATE TABLE noderef (
     89    block inet NOT NULL PRIMARY KEY,
     90    node_id integer
     91);
     92
     93CREATE TABLE nodes (
     94    node_id serial NOT NULL PRIMARY KEY,
     95    node_type character varying(2),
     96    node_name character varying(40),
     97    node_ip inet
     98);
     99
     100
     101-- Email notifications on <action>
     102CREATE TABLE notify (
     103    action varchar(5) NOT NULL PRIMARY KEY,
     104    reciplist varchar(500)
     105);
     106
     107
     108-- Table for tracking single IP assignments.  Single static IP assignments
     109-- need somewhat stronger clustering than provided by other types.
     110CREATE TABLE poolips (
     111    pool cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
     112    ip cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
     113    city character varying(30) DEFAULT ''::character varying NOT NULL,
     114    "type" character(2) DEFAULT ''::bpchar NOT NULL,
     115    available character(1) DEFAULT 'y'::bpchar NOT NULL,
     116    notes text DEFAULT ''::text NOT NULL,
     117    description character varying(64) DEFAULT ''::character varying NOT NULL,
     118    circuitid character varying(128) DEFAULT ''::character varying NOT NULL,
     119    privdata text DEFAULT ''::text NOT NULL,
     120    custid character varying(16) DEFAULT ''::character varying,
     121    createstamp timestamp with time zone DEFAULT now(),
     122    modifystamp timestamp with time zone DEFAULT now(),
     123    vrf text DEFAULT ''::text NOT NULL,
     124    vlan text DEFAULT ''::text NOT NULL,
     125    rdns text DEFAULT ''::text NOT NULL,
     126    parent_id integer DEFAULT 0 NOT NULL,
     127    master_id integer DEFAULT 0 NOT NULL,
     128    id serial NOT NULL,
     129    CONSTRAINT poolips_available_check CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
     130);
     131
     132ALTER TABLE ONLY poolips
     133    ADD CONSTRAINT poolips_pkey PRIMARY KEY (ip, parent_id);
     134
     135
     136-- Combined netblock+IP view for searches
     137CREATE VIEW searchme AS
     138    SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city,
     139      allocations.description, allocations.notes, allocations.circuitid, allocations.vrf,
     140      allocations.vlan, allocations.id, allocations.parent_id, 'n' AS available
     141    FROM allocations
     142  UNION
     143    SELECT poolips.ip AS cidr, poolips.custid, poolips."type", poolips.city,
     144      poolips.description, poolips.notes, poolips.circuitid, poolips.vrf,
     145      poolips.vlan, poolips.id, poolips.parent_id, poolips.available
     146    FROM poolips;
     147
     148
     149-- Type list for assignments
     150CREATE TABLE alloctypes (
     151    "type" character(2) DEFAULT ''::bpchar NOT NULL,
     152    listname character varying(40) DEFAULT ''::character varying,
     153    dispname character varying(40) DEFAULT ''::character varying,
     154    listorder integer DEFAULT 0,
     155    def_custid character varying(16) DEFAULT ''::character varying,
     156    arin_netname character varying(20) DEFAULT 'ISP'::character varying
     157);
     158
     159ALTER TABLE ONLY alloctypes
     160    ADD CONSTRAINT alloctypes_pkey PRIMARY KEY ("type");
     161
     162
     163-- Initial/standard allocation types.  Update def_custid and arin_netname as appropriate.
    123164COPY alloctypes ("type", listname, dispname, listorder, def_custid, arin_netname) FROM stdin;
    124165cn      Customer netblock       Customer netblock       0               ISPCUST
    … …  
    154195ar      ATM block       ATM block       222             ISP
    155196fr      Fibre   Fibre   223             ISP
    156 rm      Routing Routed netblock 500     5554242 ISP
     197rm      Routing aggregation     Routing aggregation     500     5554242 ISP
    157198mm      Master block    Master block    999     5554242 ISP
    158199\.
    159200
    160 REVOKE ALL on "alloctypes" from PUBLIC;
    161 GRANT ALL on "alloctypes" to "ipdb";
    162 
    163 CREATE TABLE "cities" (
    164         "id" serial NOT NULL PRIMARY KEY,
    165         "city" character varying(30) DEFAULT '' NOT NULL,
    166         "routing" character(1) DEFAULT 'n' NOT NULL
    167 );
    168 
    169 REVOKE ALL on "cities" from PUBLIC;
    170 GRANT ALL on "cities" to "ipdb";
    171 
    172 --
     201-- User data table - required for proper ACLs
     202CREATE TABLE users (
     203    username character varying(256) NOT NULL,
     204    "password" character varying(256) DEFAULT ''::character varying,
     205    acl character varying(256) DEFAULT 'b'::character varying
     206);
     207
     208ALTER TABLE ONLY users
     209    ADD CONSTRAINT users_pkey PRIMARY KEY (username);
     210
     211-- Default password is admin
     212INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsA');
     213
     214
    173215-- Trigger and matching function to update modifystamp on allocations, poolips
    174 --
    175216CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
    176217    BEGIN
    … …  
    185226CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
    186227    FOR EACH ROW EXECUTE PROCEDURE up_modtime();
    187 
    188 --
    189 -- User data table - required for proper ACLs
    190 --
    191 
    192 CREATE TABLE "users" (
    193         "username" varchar(16) NOT NULL PRIMARY KEY,
    194         "password" varchar(16) DEFAULT '',
    195         "acl" varchar(16) DEFAULT 'b'
    196 );
    197 
    198 -- Default password is admin
    199 INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsA');
    200 
    201 -- Network nodes - allows finding customers affected by a broken <x> quickly
    202 CREATE TABLE noderef (
    203     block inet NOT NULL PRIMARY KEY,
    204     node_id integer
    205 );
    206 
    207 CREATE TABLE nodes (
    208     node_id serial NOT NULL PRIMARY KEY,
    209     node_type character varying(2),
    210     node_name character varying(40),
    211     node_ip inet
    212 );
    213 
    214 -- Email notifications on <action>
    215 CREATE TABLE notify (
    216     action varchar(5) NOT NULL PRIMARY KEY,
    217     reciplist varchar(500)
    218 );
Note: See TracChangeset for help on using the changeset viewer.