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

\connect - postgres

\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 "kdeugau";
GRANT ALL on "customers" to "ipdb";

CREATE TABLE "masterblocks" (
        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY
);

REVOKE ALL on "masterblocks" from PUBLIC;
GRANT ALL on "masterblocks" to "kdeugau";
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 ''
);

REVOKE ALL on "routed" from PUBLIC;
GRANT ALL on "routed" to "kdeugau";
GRANT ALL on "routed" to "ipdb";

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

REVOKE ALL on "temp" from PUBLIC;
GRANT ALL on "temp" to "kdeugau";
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 "kdeugau";
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,
        "newcustid" integer,
        CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
);

REVOKE ALL on "poolips" from PUBLIC;
GRANT ALL on "poolips" to "kdeugau";
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 '',
        "newcustid" integer
);

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

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

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

REVOKE ALL on "alloctypes" from PUBLIC;
GRANT ALL on "alloctypes" to "kdeugau";
GRANT ALL on "alloctypes" to "ipdb";

CREATE TABLE "cities" (
        "city" character varying(30) DEFAULT '' NOT NULL PRIMARY KEY,
        "routing" character(1) DEFAULT 'n' NOT NULL
);

REVOKE ALL on "cities" from PUBLIC;
GRANT ALL on "cities" to "kdeugau";
GRANT ALL on "cities" to "ipdb";