Changeset 507

Timestamp:

05/08/13 18:53:23 (11 years ago)

Author:

Kris Deugau

Message:

/trunk

Re-re-re-review and tweak the new-database initialization dns.sql script
and the dns-1.0-1.2.sql upgrade script to ensure the resulting structure
is as close as possible to identical to minimize any possible breakage.

Creating a database with the 1.0 SQL init, then applying the 1.0-1.2
upgrade script, will result in pg_dump output with only one difference;
the setval for the sequence on user_id in the users table. Sematically,
there is no difference in the database behaviour (the first new user
created will have ID 2), but the changes make it easier to copy-paste
pg_dump schema fragments if further updates to the default schema are
required.

Location:

trunk

Files:

• dns-1.0-1.2.sql (modified) (2 diffs)
• dns.sql (modified) (8 diffs)

trunk/dns-1.0-1.2.sql

@@ -40 +40 @@
 \.
  
-SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
+SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);
 
 ALTER TABLE domains ADD COLUMN changed boolean DEFAULT true NOT NULL;
@@ -171 +171 @@
 \.
 
+-- and readd the primary key
+ALTER TABLE ONLY rectypes
+     ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
+
 -- Update dbversion
 UPDATE misc SET value='1.2' WHERE key='dbversion';

trunk/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,
+    rdns_id integer NOT NULL DEFAULT 0,
     location character varying (4) DEFAULT '' 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 +316 @@
     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);