Changeset 262 for branches/dns/cgi-bin/ipdb.psql

Timestamp:

06/17/05 16:42:45 (19 years ago)

Author:

Kris Deugau

Message:

/branches/dns

Update branch base with trunk changes from r216:261

File:

• branches/dns/cgi-bin/ipdb.psql (modified) (9 diffs)

branches/dns/cgi-bin/ipdb.psql

@@ -23 +23 @@
 
 CREATE TABLE "masterblocks" (
-        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY
+        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
+        "ctime" timestamp DEFAULT now()
 );
 
@@ -33 +34 @@
         "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
         "maskbits" integer DEFAULT 128,
-        "city" character varying(30) DEFAULT ''
+        "city" character varying(30) DEFAULT '',
+        "ctime" timestamp DEFAULT now()
 );
 
@@ -70 +72 @@
         "circuitid" character varying(128) DEFAULT '' NOT NULL,
         "newcustid" integer,
+        "createstamp" timestamp DEFAULT now(),
+        "modifystamp" timestamp DEFAULT now(),
         CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
 );
@@ -86 +90 @@
         "maskbits" integer DEFAULT 128,
         "circuitid" character varying(128) DEFAULT '',
+        "createstamp" timestamp DEFAULT now(),
+        "modifystamp" timestamp DEFAULT now(),
         "newcustid" integer
 );
@@ -112 +118 @@
 
 CREATE TABLE "cities" (
-        "city" character varying(30) DEFAULT '' NOT NULL PRIMARY KEY,
+        "id" serial NOT NULL PRIMARY KEY,
+        "city" character varying(30) DEFAULT '' NOT NULL,
         "routing" character(1) DEFAULT 'n' NOT NULL
 );
@@ -119 +126 @@
 GRANT ALL on "cities" to "kdeugau";
 GRANT ALL on "cities" to "ipdb";
-
---
--- Selected TOC Entries:
---
-\connect - ipdb
-
---
--- TOC Entry ID 2 (OID 92809)
---
--- Name: alloctypes Type: TABLE Owner: ipdb
---
 
 CREATE TABLE "alloctypes" (
@@ -140 +136 @@
 );
 
---
--- TOC Entry ID 3 (OID 92809)
---
--- Name: alloctypes Type: ACL Owner: 
---
-
 REVOKE ALL on "alloctypes" from PUBLIC;
 GRANT ALL on "alloctypes" to "kdeugau";
 GRANT ALL on "alloctypes" to "ipdb";
-
---
--- Data for TOC Entry ID 4 (OID 92809)
---
--- Name: alloctypes Type: TABLE DATA Owner: ipdb
---
-
 
 COPY "alloctypes" FROM stdin;
@@ -172 +155 @@
 si      Static IP - Server pool Server pool IP  20      6750400
 wc      Reserve for WAN blocks  WAN IP blocks   200     6750400
-wr      Internal WAN block      Internal WAN block      201     6750400
-pc      Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 202     6750400
 en      End-use netblock        End-use netblock        100     6750400
 me      Dialup netblock Dialup netblock 101     DIAL-RES
@@ -180 +161 @@
 we      Dynamic WiFi block      Dynamic WiFi block      104     WL-RES
 rm      Routing Routed netblock 500     6750400
-pr      Dynamic-route DSL netblock      Dynamic-route DSL       203     
+pc      Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201     6750400
+pr      Dynamic-route DSL netblock      Dynamic-route DSL       221     
+wr      WAN block       WAN block       220     6750400
 \.
+
+--
+-- 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'
+);