Changeset 545 for branches/stable/dns-1.0-1.2.sql

Timestamp:

12/10/13 17:47:44 (10 years ago)

Author:

Kris Deugau

Message:

/branches/stable

Merge reverse DNS and location work; 2 of mumble

• from r263 through r416

Numerous conflicts due to hand-copy or partial merges

Location:

branches/stable

Files:

• . (modified) (1 prop)
• dns-1.0-1.2.sql (copied) (copied from trunk/dns-1.0-1.2.sql ) (4 diffs)

branches/stable/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 (
+    location character varying (4) PRIMARY KEY,
+    loc_id serial UNIQUE,
+    group_id integer NOT NULL DEFAULT 1,
+    iplist text NOT NULL DEFAULT '',
+    description character varying(40) NOT NULL DEFAULT '',
+    comments 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 record_locchg boolean DEFAULT false NOT NULL;
+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 +42 @@
 SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
 
+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);
+
 CREATE TABLE revzones (
     rdns_id serial NOT NULL,
@@ -30 +54 @@
     status integer DEFAULT 1 NOT NULL,
     zserial integer,
-    sertype character(1) DEFAULT 'D'::bpchar
+    sertype character(1) DEFAULT 'D'::bpchar,
+    changed boolean DEFAULT true NOT NULL,
+    default_location character varying (4) DEFAULT '' NOT NULL
 );
+CREATE INDEX rev_status_index ON revzones (status);
+
+ALTER TABLE ONLY revzones
+    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
 
 ALTER TABLE log ADD COLUMN rdns_id INTEGER;
@@ -40 +70 @@
 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);
+-- Further ~1/3 performance gain, same dataset
+CREATE INDEX rec_domain_index ON records (domain_id);
+CREATE INDEX rec_revzone_index ON records (rdns_id);
 
 -- May as well drop and recreate;  this is nominally static and loaded from the