source: trunk/dns.sql@ 63

Last change on this file since 63 was 50, checked in by Kris Deugau, 15 years ago

/trunk

Add a few more TODOs
Add the tabledef SQL file

File size: 6.2 KB
Line 
1-- these lines could be run as a superuser. alter database name, username, password, group as appropriate.
2-- make sure to alter dnsdb.conf to match
3-- CREATE GROUP dnsdb;
4-- CREATE USER dnsdb WITH UNENCRYPTED PASSWORD 'secret' IN GROUP dnsdb;
5-- CREATE DATABASE dnsdb OWNED BY dnsdb;
6-- SET SESSION AUTHORIZATION 'dnsdb';
7
8-- tabledefs and preloaded data bits
9CREATE TABLE groups (
10 group_id serial NOT NULL,
11 parent_group_id integer DEFAULT 1 NOT NULL,
12 group_name character varying(255) DEFAULT ''::character varying NOT NULL
13);
14
15-- Provide a basic default group
16COPY groups (group_id, parent_group_id, group_name) FROM stdin;
171 1 default
18\.
19
20CREATE TABLE domains (
21 domain_id serial NOT NULL,
22 "domain" character varying(80) NOT NULL,
23 group_id integer DEFAULT 1 NOT NULL,
24 description character varying(255) DEFAULT ''::character varying NOT NULL,
25 status integer DEFAULT 1 NOT NULL,
26 zserial integer,
27 sertype character(1) DEFAULT 'D'::bpchar
28);
29
30CREATE TABLE default_records (
31 record_id serial NOT NULL,
32 group_id integer DEFAULT 1 NOT NULL,
33 host character varying(100) DEFAULT ''::character varying NOT NULL,
34 "type" integer DEFAULT 1 NOT NULL,
35 val character varying(100) DEFAULT ''::character varying NOT NULL,
36 distance integer DEFAULT 0 NOT NULL,
37 weight integer DEFAULT 0 NOT NULL,
38 port integer DEFAULT 0 NOT NULL,
39 ttl integer DEFAULT 86400 NOT NULL,
40 description character varying(255)
41);
42
43-- default records for the default group
44COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
451 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N
462 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N
473 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N
484 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N
495 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N
506 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N
517 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N
52\.
53
54CREATE TABLE records (
55 domain_id integer NOT NULL,
56 record_id serial NOT NULL,
57 host character varying(100) DEFAULT ''::character varying NOT NULL,
58 "type" integer DEFAULT 1 NOT NULL,
59 val character varying(100) DEFAULT ''::character varying NOT NULL,
60 distance integer DEFAULT 0 NOT NULL,
61 weight integer DEFAULT 0 NOT NULL,
62 port integer DEFAULT 0 NOT NULL,
63 ttl integer DEFAULT 86400 NOT NULL,
64 description character varying(255)
65);
66
67CREATE TABLE rectypes (
68 val integer NOT NULL,
69 name character varying(12) NOT NULL,
70 stdflag integer DEFAULT 1 NOT NULL,
71 listorder integer DEFAULT 255 NOT NULL
72);
73
74-- Types are required. NB: these are vaguely read-only too
75COPY rectypes (val, name, stdflag, listorder) FROM stdin;
761 A 1 1
772 NS 1 2
783 MD 2 255
794 MF 2 255
805 CNAME 1 6
816 SOA 0 8
827 MB 3 255
838 MG 3 255
849 MR 3 255
8510 NULL 3 255
8611 WKS 3 255
8712 PTR 2 4
8813 HINFO 3 255
8914 MINFO 3 255
9015 MX 1 3
9116 TXT 1 5
9217 RP 2 255
9318 AFSDB 3 255
9419 X25 3 255
9520 ISDN 3 255
9621 RT 3 255
9722 NSAP 3 255
9823 NSAP-PTR 3 255
9924 SIG 3 255
10025 KEY 3 255
10126 PX 3 255
10227 GPOS 3 255
10328 AAAA 2 2
10429 LOC 3 255
10530 NXT 3 255
10631 EID 3 255
10732 NIMLOC 3 255
10833 SRV 1 7
10934 ATMA 3 255
11035 NAPTR 3 255
11136 KX 3 255
11237 CERT 3 255
11338 A6 3 3
11439 DNAME 3 255
11540 SINK 3 255
11641 OPT 3 255
11742 APL 3 255
11843 DS 3 255
11944 SSHFP 3 255
12045 IPSECKEY 3 255
12146 RRSIG 3 255
12247 NSEC 3 255
12348 DNSKEY 3 255
12449 DHCID 3 255
12550 NSEC3 3 255
12651 NSEC3PARAM 3 255
12755 HIP 3 255
12899 SPF 3 255
129100 UINFO 3 255
130101 UID 3 255
131102 GID 3 255
132103 UNSPEC 3 255
133249 TKEY 3 255
134250 TSIG 3 255
135251 IXFR 3 255
136252 AXFR 3 255
137253 MAILB 3 255
138254 MAILA 3 255
13932768 TA 3 255
14032769 DLV 3 255
141\.
142
143CREATE TABLE users (
144 user_id serial NOT NULL,
145 group_id integer DEFAULT 1 NOT NULL,
146 username character varying(60) NOT NULL,
147 "password" character varying(34) NOT NULL,
148 firstname character varying(30),
149 lastname character varying(30),
150 phone character varying(15),
151 "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
152 status integer DEFAULT 1 NOT NULL,
153 acl character varying(40) DEFAULT 'b'::character varying NOT NULL
154);
155
156-- create initial default user? may be better to create an "initialize" script or something
157COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, acl) FROM stdin;
1581 1 test@test $1$BByge8u2$48AaGX3YeHplfErX5Tlqa1 \N \N \N S 1 A
159\.
160
161CREATE TABLE log (
162 domain_id integer,
163 user_id integer,
164 group_id integer,
165 email character varying(60),
166 name character varying(60),
167 entry character varying(200),
168 stamp timestamp with time zone
169);
170
171--
172-- contraints. add these here so initial data doesn't get added strangely.
173--
174
175-- primary keys
176ALTER TABLE ONLY groups
177 ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
178
179ALTER TABLE ONLY domains
180 ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain");
181
182ALTER TABLE ONLY domains
183 ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
184
185ALTER TABLE ONLY default_records
186 ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
187
188ALTER TABLE ONLY records
189 ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
190
191ALTER TABLE ONLY rectypes
192 ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
193
194ALTER TABLE ONLY users
195 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
196
197ALTER TABLE ONLY users
198 ADD CONSTRAINT uidu UNIQUE (user_id);
199
200-- foreign keys
201ALTER TABLE ONLY domains
202 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
203
204ALTER TABLE ONLY default_records
205 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
206
207ALTER TABLE ONLY records
208 ADD CONSTRAINT "$1" FOREIGN KEY (domain_id) REFERENCES domains(domain_id);
209
210ALTER TABLE ONLY users
211 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
212
213ALTER TABLE ONLY groups
214 ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
215
216-- set sequence start values - make sure we don't screw up adding
217-- records to tables that already have a few entries
218
219SELECT pg_catalog.setval('groups_group_id_seq', 52, true);
220
221SELECT pg_catalog.setval('domains_domain_id_seq', 953, true);
222
223SELECT pg_catalog.setval('default_records_record_id_seq', 320, true);
224
225SELECT pg_catalog.setval('records_record_id_seq', 660, true);
226
227SELECT pg_catalog.setval('users_user_id_seq', 37, true);
Note: See TracBrowser for help on using the repository browser.