- Timestamp:
- 05/08/13 18:53:23 (12 years ago)
- Location:
- trunk
- Files:
-
- 2 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/dns-1.0-1.2.sql
r456 r507 40 40 \. 41 41 42 SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);42 SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true); 43 43 44 44 ALTER TABLE domains ADD COLUMN changed boolean DEFAULT true NOT NULL; … … 171 171 \. 172 172 173 -- and readd the primary key 174 ALTER TABLE ONLY rectypes 175 ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name); 176 173 177 -- Update dbversion 174 178 UPDATE misc SET value='1.2' WHERE key='dbversion'; -
trunk/dns.sql
r456 r507 78 78 default_location character varying (4) DEFAULT '' NOT NULL 79 79 ); 80 -- ~2x performance boost iff most zones are fed to output from the cache 81 CREATE INDEX dom_status_index ON domains (status); 82 80 83 81 84 CREATE TABLE revzones ( … … 90 93 default_location character varying (4) DEFAULT '' NOT NULL 91 94 ); 95 CREATE INDEX rev_status_index ON revzones USING btree (status); 92 96 93 97 CREATE TABLE groups ( … … 109 113 log_id serial NOT NULL, 110 114 domain_id integer, 111 rdns_id integer,112 115 user_id integer, 113 116 group_id integer, … … 115 118 name character varying(60), 116 119 entry text, 117 stamp timestamp with time zone DEFAULT now() 120 stamp timestamp with time zone DEFAULT now(), 121 rdns_id integer 118 122 ); 119 123 … … 134 138 record_edit boolean DEFAULT false NOT NULL, 135 139 record_delete boolean DEFAULT false NOT NULL, 140 user_id integer UNIQUE, 141 group_id integer UNIQUE, 136 142 record_locchg boolean DEFAULT false NOT NULL, 137 143 location_create boolean DEFAULT false NOT NULL, 138 144 location_edit boolean DEFAULT false NOT NULL, 139 145 location_delete boolean DEFAULT false NOT NULL, 140 location_view boolean DEFAULT false NOT NULL, 141 user_id integer UNIQUE, 142 group_id integer UNIQUE 146 location_view boolean DEFAULT false NOT NULL 143 147 ); 144 148 145 149 -- Need *two* basic permissions; one for the initial group, one for the default admin user 146 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;147 1 f f f f f f f f t t t t t t f f f f f \N 1148 2 t f f f f f f f f f f f f f f f f f f 1 \N150 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; 151 1 f f f f f f f f t t t t t t \N 1 f f f f f 152 2 t f f f f f f f f f f f f f 1 \N f f f f f 149 153 \. 150 154 … … 152 156 CREATE TABLE records ( 153 157 domain_id integer NOT NULL DEFAULT 0, 154 rdns_id integer NOT NULL DEFAULT 0,155 158 record_id serial NOT NULL, 156 159 host text DEFAULT '' NOT NULL, … … 162 165 ttl integer DEFAULT 7200 NOT NULL, 163 166 description text, 167 rdns_id integer NOT NULL DEFAULT 0, 164 168 location character varying (4) DEFAULT '' NOT NULL 165 169 ); 170 CREATE INDEX rec_domain_index ON records USING btree (domain_id); 171 CREATE INDEX rec_revzone_index ON records USING btree (rdns_id); 172 CREATE INDEX rec_types_index ON records USING btree ("type"); 166 173 167 174 CREATE TABLE rectypes ( … … 309 316 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id); 310 317 318 ALTER TABLE ONLY default_records 319 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id); 320 321 ALTER TABLE ONLY users 322 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id); 323 311 324 ALTER TABLE ONLY revzones 312 325 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id); 313 326 314 ALTER TABLE ONLY default_records315 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);316 317 ALTER TABLE ONLY users318 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);319 320 327 ALTER TABLE ONLY groups 321 328 ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id); 322 329 323 330 -- set starting sequence numbers, since we've inserted data before they're active 324 SELECT pg_catalog.setval('misc_misc_id_seq', 2, false); 325 SELECT pg_catalog.setval('default_records_record_id_seq', 8, false); 326 SELECT pg_catalog.setval(' default_rev_records_record_id_seq', 5, false);327 SELECT pg_catalog.setval('d omains_domain_id_seq', 1, false);328 SELECT pg_catalog.setval(' groups_group_id_seq', 2, false);329 SELECT pg_catalog.setval(' permissions_permission_id_seq', 3, false);330 SELECT pg_catalog.setval(' records_record_id_seq', 1, false);331 SELECT pg_catalog.setval('users_user_id_seq', 2, false);331 -- only set the ones that have data loaded with \copy, and obey the convention 332 -- that comes out of pg_dump 333 SELECT pg_catalog.setval('misc_misc_id_seq', 1, true); 334 SELECT pg_catalog.setval('default_records_record_id_seq', 8, true); 335 SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true); 336 SELECT pg_catalog.setval('groups_group_id_seq', 1, true); 337 SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true); 338 SELECT pg_catalog.setval('users_user_id_seq', 1, true);
Note:
See TracChangeset
for help on using the changeset viewer.