Changeset 548 for branches/stable/dns.sql

Timestamp:

12/11/13 16:01:18 (10 years ago)

Author:

Kris Deugau

Message:

/branches/stable

Merge reverse DNS work and object conversion from /trunk, part 5

Includes changes through r543 with a few more minor conflicts.

Location:

branches/stable

Files:

• . (modified) (1 prop)
• dns.sql (modified) (8 diffs)

branches/stable/dns.sql

@@ -78 +78 @@
     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 (
@@ -90 +93 @@
     default_location character varying (4) DEFAULT '' NOT NULL
 );
+CREATE INDEX rev_status_index ON revzones USING btree (status);
 
 CREATE TABLE groups (
@@ -109 +113 @@
     log_id serial NOT NULL,
     domain_id integer,
-    rdns_id integer,
     user_id integer,
     group_id integer,
@@ -115 +118 @@
     name character varying(60),
     entry text,
-    stamp timestamp with time zone DEFAULT now()
+    stamp timestamp with time zone DEFAULT now(),
+    rdns_id integer
 );
 
@@ -134 +138 @@
     record_edit boolean DEFAULT false NOT NULL,
     record_delete boolean DEFAULT false NOT NULL,
+    user_id integer UNIQUE,
+    group_id integer UNIQUE,
     record_locchg boolean DEFAULT false NOT NULL,
     location_create boolean DEFAULT false NOT NULL,
     location_edit boolean DEFAULT false NOT NULL,
     location_delete boolean DEFAULT false NOT NULL,
-    location_view boolean DEFAULT false NOT NULL,
-    user_id integer UNIQUE,
-    group_id integer UNIQUE
+    location_view boolean DEFAULT false NOT NULL
 );
 
 -- Need *two* basic permissions;  one for the initial group, one for the default admin user
-COPY permissions (permission_id, "admin", self_edit, group_create, group_edit, group_delete, user_create, user_edit, user_delete, domain_create, domain_edit, domain_delete, record_create, record_edit, record_delete, record_locchg, location_create, location_edit, location_delete, location_view, user_id, group_id) FROM stdin;
-1       f       f       f       f       f       f       f       f       t       t       t       t       t       t       f       f       f       f       f       \N      1
-2       t       f       f       f       f       f       f       f       f       f       f       f       f       f       f       f       f       f       f       1       \N
+COPY permissions (permission_id, "admin", self_edit, group_create, group_edit, group_delete, user_create, user_edit, user_delete, domain_create, domain_edit, domain_delete, record_create, record_edit, record_delete, user_id, group_id, record_locchg, location_create, location_edit, location_delete, location_view) FROM stdin;
+1       f       f       f       f       f       f       f       f       t       t       t       t       t       t       \N      1       f       f       f       f       f
+2       t       f       f       f       f       f       f       f       f       f       f       f       f       f       1       \N      f       f       f       f       f
 \.
 
@@ -152 +156 @@
 CREATE TABLE records (
     domain_id integer NOT NULL DEFAULT 0,
-    rdns_id integer NOT NULL DEFAULT 0,
     record_id serial NOT NULL,
     host text DEFAULT '' NOT NULL,
@@ -162 +165 @@
     ttl integer DEFAULT 7200 NOT NULL,
     description text,
-    location character varying (4) DEFAULT '' NOT NULL
-);
+    rdns_id integer NOT NULL DEFAULT 0,
+    location character varying (4) DEFAULT '' NOT NULL,
+    stamp TIMESTAMP WITH TIME ZONE DEFAULT 'epoch' NOT NULL,
+    expires boolean DEFAULT 'n' NOT NULL,
+    stampactive boolean DEFAULT 'n' NOT NULL
+);
+CREATE INDEX rec_domain_index ON records USING btree (domain_id);
+CREATE INDEX rec_revzone_index ON records USING btree (rdns_id);
+CREATE INDEX rec_types_index ON records USING btree ("type");
 
 CREATE TABLE rectypes (
@@ -309 +319 @@
     ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
 
+ALTER TABLE ONLY default_records
+    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
+
+ALTER TABLE ONLY users
+    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
+
 ALTER TABLE ONLY revzones
     ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
 
-ALTER TABLE ONLY default_records
-    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
-
-ALTER TABLE ONLY users
-    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
-
 ALTER TABLE ONLY groups
     ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
 
 -- set starting sequence numbers, since we've inserted data before they're active
-SELECT pg_catalog.setval('misc_misc_id_seq', 2, false);
-SELECT pg_catalog.setval('default_records_record_id_seq', 8, false);
-SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
-SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
-SELECT pg_catalog.setval('groups_group_id_seq', 2, false);
-SELECT pg_catalog.setval('permissions_permission_id_seq', 3, false);
-SELECT pg_catalog.setval('records_record_id_seq', 1, false);
-SELECT pg_catalog.setval('users_user_id_seq', 2, false);
+-- only set the ones that have data loaded with \copy, and obey the convention
+-- that comes out of pg_dump
+SELECT pg_catalog.setval('misc_misc_id_seq', 1, true);
+SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
+SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);
+SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
+SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
+SELECT pg_catalog.setval('users_user_id_seq', 1, true);