Changeset 85 for trunk/dns.sql

Timestamp:

03/04/11 17:49:38 (13 years ago)

Author:

Kris Deugau

Message:

/trunk

Update SQL tabledef file

File:

• trunk/dns.sql (modified) (7 diffs)

trunk/dns.sql

@@ -5 +5 @@
 -- CREATE DATABASE dnsdb OWNED BY dnsdb;
 -- SET SESSION AUTHORIZATION 'dnsdb';
-
--- tabledefs and preloaded data bits
-CREATE TABLE permissions (
-    permission_id SERIAL NOT NULL,
-    admin boolean DEFAULT 'n' NOT NULL,
-    self_edit boolean DEFAULT 'n' NOT NULL,
-    group_create boolean DEFAULT 'n' NOT NULL,
-    group_edit boolean DEFAULT 'n' NOT NULL,
-    group_delete boolean DEFAULT 'n' NOT NULL,
-    user_create boolean DEFAULT 'n' NOT NULL,
-    user_edit boolean DEFAULT 'n' NOT NULL,
-    user_delete boolean DEFAULT 'n' NOT NULL,
-    domain_create boolean DEFAULT 'n' NOT NULL,
-    domain_edit boolean DEFAULT 'n' NOT NULL,
-    domain_delete boolean DEFAULT 'n' NOT NULL,
-    record_create boolean DEFAULT 'n' NOT NULL,
-    record_edit boolean DEFAULT 'n' NOT NULL,
-    record_delete boolean DEFAULT 'n' 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) FROM stdin;
-1       n       n       n       n       n       n       n       n       n       n       n       n       n       n
-2       y       n       n       n       n       n       n       n       n       n       n       n       n       n
-\.
-
-CREATE TABLE groups (
-    group_id serial NOT NULL,
-    parent_group_id integer DEFAULT 1 NOT NULL,
-    permission_id integer DEFAULT 1 NOT NULL,
-    group_name character varying(255) DEFAULT ''::character varying NOT NULL
-);
-
--- Provide a basic default group
-COPY groups (group_id, parent_group_id, group_name) FROM stdin;
-1       1       1       default
-\.
-
-CREATE TABLE domains (
-    domain_id serial NOT NULL,
-    "domain" character varying(80) NOT NULL,
-    group_id integer DEFAULT 1 NOT NULL,
-    description character varying(255) DEFAULT ''::character varying NOT NULL,
-    status integer DEFAULT 1 NOT NULL,
-    zserial integer,
-    sertype character(1) DEFAULT 'D'::bpchar
-);
 
 CREATE TABLE default_records (
@@ -77 +30 @@
 \.
 
+CREATE TABLE domains (
+    domain_id serial NOT NULL,
+    "domain" character varying(80) NOT NULL,
+    group_id integer DEFAULT 1 NOT NULL,
+    description character varying(255) DEFAULT ''::character varying NOT NULL,
+    status integer DEFAULT 1 NOT NULL,
+    zserial integer,
+    sertype character(1) DEFAULT 'D'::bpchar
+);
+
+CREATE TABLE groups (
+    group_id serial NOT NULL,
+    parent_group_id integer DEFAULT 1 NOT NULL,
+    group_name character varying(255) DEFAULT ''::character varying NOT NULL,
+    permission_id integer DEFAULT 1 NOT NULL,
+    inherit_perm boolean DEFAULT true NOT NULL
+);
+
+-- Provide a basic default group
+COPY groups (group_id, parent_group_id, group_name) FROM stdin;
+1       1       1       default
+\.
+
+CREATE TABLE log (
+    domain_id integer,
+    user_id integer,
+    group_id integer,
+    email character varying(60),
+    name character varying(60),
+    entry character varying(200),
+    stamp timestamp with time zone DEFAULT now()
+);
+
+CREATE TABLE permissions (
+    permission_id serial NOT NULL,
+    "admin" boolean DEFAULT false NOT NULL,
+    self_edit boolean DEFAULT false NOT NULL,
+    group_create boolean DEFAULT false NOT NULL,
+    group_edit boolean DEFAULT false NOT NULL,
+    group_delete boolean DEFAULT false NOT NULL,
+    user_create boolean DEFAULT false NOT NULL,
+    user_edit boolean DEFAULT false NOT NULL,
+    user_delete boolean DEFAULT false NOT NULL,
+    domain_create boolean DEFAULT false NOT NULL,
+    domain_edit boolean DEFAULT false NOT NULL,
+    domain_delete boolean DEFAULT false NOT NULL,
+    record_create boolean DEFAULT false NOT NULL,
+    record_edit boolean DEFAULT false NOT NULL,
+    record_delete boolean DEFAULT false NOT NULL,
+    user_id integer DEFAULT 0 NOT NULL,
+    group_id integer DEFAULT 0 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) FROM stdin;
+1       n       n       n       n       n       n       n       n       n       n       n       n       n       n
+2       y       n       n       n       n       n       n       n       n       n       n       n       n       n
+\.
+
+-- fixeme:  need to handle looooong TXT records (eg, SPF)
 CREATE TABLE records (
     domain_id integer NOT NULL,
@@ -86 +99 @@
     weight integer DEFAULT 0 NOT NULL,
     port integer DEFAULT 0 NOT NULL,
-    ttl integer DEFAULT 86400 NOT NULL,
+    ttl integer DEFAULT 7200 NOT NULL,
     description character varying(255)
 );
@@ -98 +111 @@
 
 -- Types are required.  NB:  these are vaguely read-only too
+-- data from http://www.iana.org/assignments/dns-parameters
 COPY rectypes (val, name, stdflag, listorder) FROM stdin;
 1       A       1       1
@@ -171 +185 @@
     username character varying(60) NOT NULL,
     "password" character varying(34) NOT NULL,
-    firstname character varying(30),
-    lastname character varying(30),
+    firstname character varying(60),
+    lastname character varying(60),
     phone character varying(15),
     "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
     status integer DEFAULT 1 NOT NULL,
-    acl character varying(40) DEFAULT 'b'::character varying NOT NULL,
-    permission_id DEFAULT 1 NOT NULL,
+    permission_id integer DEFAULT 1 NOT NULL,
+    inherit_perm boolean DEFAULT true NOT NULL
 );
 
@@ -184 +198 @@
 1       1       test@test       $1$BByge8u2$48AaGX3YeHplfErX5Tlqa1      \N      \N      \N      S       1       A       2
 \.
-
-CREATE TABLE log (
-    domain_id integer,
-    user_id integer,
-    group_id integer,
-    email character varying(60),
-    name character varying(60),
-    entry character varying(200),
-    stamp timestamp with time zone
-);
 
 --
@@ -243 +247 @@
 ALTER TABLE ONLY groups
     ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
-
--- set sequence start values - make sure we don't screw up adding
--- records to tables that already have a few entries
-SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
-
-SELECT pg_catalog.setval('groups_group_id_seq', 52, true);
-
-SELECT pg_catalog.setval('domains_domain_id_seq', 953, true);
-
-SELECT pg_catalog.setval('default_records_record_id_seq', 320, true);
-
-SELECT pg_catalog.setval('records_record_id_seq', 660, true);
-
-SELECT pg_catalog.setval('users_user_id_seq', 37, true);