Changeset 370 for trunk/dns-1.0-1.2.sql

Timestamp:

07/29/12 22:02:48 (12 years ago)

Author:

Kris Deugau

Message:

/trunk

Checkpoint; adding location/view support. See #10.

• add location to menu
• add table and link fields to initial tabledef and upgrade SQL
• add listing subs and list page
• update permissions list and subpage template with new permissions

File:

• trunk/dns-1.0-1.2.sql (modified) (4 diffs)

trunk/dns-1.0-1.2.sql

@@ -1 +1 @@
 -- SQL table/record type upgrade file for dnsadmin 1.0 to 1.2 migration
+
+-- need this before we add any other bits
+CREATE TABLE locations (
+    loc character varying (4) PRIMARY KEY,
+    group_id integer NOT NULL DEFAULT 1,
+    iplist text NOT NULL DEFAULT '',
+    description text NOT NULL DEFAULT ''
+);
+
+ALTER TABLE ONLY locations
+    ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
+
+ALTER TABLE permissions ADD COLUMN location_create boolean DEFAULT false NOT NULL;
+ALTER TABLE permissions ADD COLUMN location_edit boolean DEFAULT false NOT NULL;
+ALTER TABLE permissions ADD COLUMN location_delete boolean DEFAULT false NOT NULL;
+ALTER TABLE permissions ADD COLUMN location_view boolean DEFAULT false NOT NULL;
 
 -- Minor buglet;  domains must be unique
@@ -23 +39 @@
 SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
 
-ALTER TABLE domains ADD COLUMN changed boolean;
-UPDATE domains SET changed=false;
-ALTER TABLE domains ALTER COLUMN changed SET DEFAULT true;
-ALTER TABLE domains ALTER COLUMN changed SET NOT NULL;
+ALTER TABLE domains ADD COLUMN changed boolean DEFAULT true NOT NULL;
+ALTER TABLE domains ADD COLUMN default_location character varying (4) DEFAULT '' NOT NULL;
 -- ~2x performance boost iff most zones are fed to output from the cache
 CREATE INDEX dom_status_index ON domains (status);
@@ -38 +52 @@
     zserial integer,
     sertype character(1) DEFAULT 'D'::bpchar,
-    changed boolean DEFAULT true NOT NULL
+    changed boolean DEFAULT true NOT NULL,
+    default_location character varying (4) DEFAULT '' NOT NULL
 );
 CREATE INDEX rev_status_index ON revzones (status);
@@ -52 +67 @@
 ALTER TABLE records DROP CONSTRAINT "$1";
 ALTER TABLE records ALTER COLUMN domain_id SET DEFAULT 0;
-ALTER TABLE records ADD COLUMN rdns_id INTEGER DEFAULT 0;
-UPDATE records SET rdns_id=0;
-ALTER TABLE records ALTER COLUMN rdns_id SET NOT NULL;
+ALTER TABLE records ADD COLUMN rdns_id INTEGER DEFAULT 0 NOT NULL;
+ALTER TABLE records ADD COLUMN location character varying (4) DEFAULT '' NOT NULL;
+
 -- ~120s -> 75s performance boost on 100K records when always exporting all records
 CREATE INDEX rec_types_index ON records (type);