Changeset 85 for trunk


Ignore:
Timestamp:
03/04/11 17:49:38 (14 years ago)
Author:
Kris Deugau
Message:

/trunk

Update SQL tabledef file

File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/dns.sql

    r65 r85  
    55-- CREATE DATABASE dnsdb OWNED BY dnsdb;
    66-- SET SESSION AUTHORIZATION 'dnsdb';
    7 
    8 -- tabledefs and preloaded data bits
    9 CREATE TABLE permissions (
    10     permission_id SERIAL NOT NULL,
    11     admin boolean DEFAULT 'n' NOT NULL,
    12     self_edit boolean DEFAULT 'n' NOT NULL,
    13     group_create boolean DEFAULT 'n' NOT NULL,
    14     group_edit boolean DEFAULT 'n' NOT NULL,
    15     group_delete boolean DEFAULT 'n' NOT NULL,
    16     user_create boolean DEFAULT 'n' NOT NULL,
    17     user_edit boolean DEFAULT 'n' NOT NULL,
    18     user_delete boolean DEFAULT 'n' NOT NULL,
    19     domain_create boolean DEFAULT 'n' NOT NULL,
    20     domain_edit boolean DEFAULT 'n' NOT NULL,
    21     domain_delete boolean DEFAULT 'n' NOT NULL,
    22     record_create boolean DEFAULT 'n' NOT NULL,
    23     record_edit boolean DEFAULT 'n' NOT NULL,
    24     record_delete boolean DEFAULT 'n' NOT NULL
    25 );
    26 
    27 -- Need *two* basic permissions;  one for the initial group, one for the default admin user
    28 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;
    29 1       n       n       n       n       n       n       n       n       n       n       n       n       n       n
    30 2       y       n       n       n       n       n       n       n       n       n       n       n       n       n
    31 \.
    32 
    33 CREATE TABLE groups (
    34     group_id serial NOT NULL,
    35     parent_group_id integer DEFAULT 1 NOT NULL,
    36     permission_id integer DEFAULT 1 NOT NULL,
    37     group_name character varying(255) DEFAULT ''::character varying NOT NULL
    38 );
    39 
    40 -- Provide a basic default group
    41 COPY groups (group_id, parent_group_id, group_name) FROM stdin;
    42 1       1       1       default
    43 \.
    44 
    45 CREATE TABLE domains (
    46     domain_id serial NOT NULL,
    47     "domain" character varying(80) NOT NULL,
    48     group_id integer DEFAULT 1 NOT NULL,
    49     description character varying(255) DEFAULT ''::character varying NOT NULL,
    50     status integer DEFAULT 1 NOT NULL,
    51     zserial integer,
    52     sertype character(1) DEFAULT 'D'::bpchar
    53 );
    547
    558CREATE TABLE default_records (
    … …  
    7730\.
    7831
     32CREATE TABLE domains (
     33    domain_id serial NOT NULL,
     34    "domain" character varying(80) NOT NULL,
     35    group_id integer DEFAULT 1 NOT NULL,
     36    description character varying(255) DEFAULT ''::character varying NOT NULL,
     37    status integer DEFAULT 1 NOT NULL,
     38    zserial integer,
     39    sertype character(1) DEFAULT 'D'::bpchar
     40);
     41
     42CREATE TABLE groups (
     43    group_id serial NOT NULL,
     44    parent_group_id integer DEFAULT 1 NOT NULL,
     45    group_name character varying(255) DEFAULT ''::character varying NOT NULL,
     46    permission_id integer DEFAULT 1 NOT NULL,
     47    inherit_perm boolean DEFAULT true NOT NULL
     48);
     49
     50-- Provide a basic default group
     51COPY groups (group_id, parent_group_id, group_name) FROM stdin;
     521       1       1       default
     53\.
     54
     55CREATE TABLE log (
     56    domain_id integer,
     57    user_id integer,
     58    group_id integer,
     59    email character varying(60),
     60    name character varying(60),
     61    entry character varying(200),
     62    stamp timestamp with time zone DEFAULT now()
     63);
     64
     65CREATE TABLE permissions (
     66    permission_id serial NOT NULL,
     67    "admin" boolean DEFAULT false NOT NULL,
     68    self_edit boolean DEFAULT false NOT NULL,
     69    group_create boolean DEFAULT false NOT NULL,
     70    group_edit boolean DEFAULT false NOT NULL,
     71    group_delete boolean DEFAULT false NOT NULL,
     72    user_create boolean DEFAULT false NOT NULL,
     73    user_edit boolean DEFAULT false NOT NULL,
     74    user_delete boolean DEFAULT false NOT NULL,
     75    domain_create boolean DEFAULT false NOT NULL,
     76    domain_edit boolean DEFAULT false NOT NULL,
     77    domain_delete boolean DEFAULT false NOT NULL,
     78    record_create boolean DEFAULT false NOT NULL,
     79    record_edit boolean DEFAULT false NOT NULL,
     80    record_delete boolean DEFAULT false NOT NULL,
     81    user_id integer DEFAULT 0 NOT NULL,
     82    group_id integer DEFAULT 0 NOT NULL
     83);
     84
     85-- Need *two* basic permissions;  one for the initial group, one for the default admin user
     86COPY 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;
     871       n       n       n       n       n       n       n       n       n       n       n       n       n       n
     882       y       n       n       n       n       n       n       n       n       n       n       n       n       n
     89\.
     90
     91-- fixeme:  need to handle looooong TXT records (eg, SPF)
    7992CREATE TABLE records (
    8093    domain_id integer NOT NULL,
    … …  
    8699    weight integer DEFAULT 0 NOT NULL,
    87100    port integer DEFAULT 0 NOT NULL,
    88     ttl integer DEFAULT 86400 NOT NULL,
     101    ttl integer DEFAULT 7200 NOT NULL,
    89102    description character varying(255)
    90103);
    … …  
    98111
    99112-- Types are required.  NB:  these are vaguely read-only too
     113-- data from http://www.iana.org/assignments/dns-parameters
    100114COPY rectypes (val, name, stdflag, listorder) FROM stdin;
    1011151       A       1       1
    … …  
    171185    username character varying(60) NOT NULL,
    172186    "password" character varying(34) NOT NULL,
    173     firstname character varying(30),
    174     lastname character varying(30),
     187    firstname character varying(60),
     188    lastname character varying(60),
    175189    phone character varying(15),
    176190    "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
    177191    status integer DEFAULT 1 NOT NULL,
    178     acl character varying(40) DEFAULT 'b'::character varying NOT NULL,
    179     permission_id DEFAULT 1 NOT NULL,
     192    permission_id integer DEFAULT 1 NOT NULL,
     193    inherit_perm boolean DEFAULT true NOT NULL
    180194);
    181195
    … …  
    1841981       1       test@test       $1$BByge8u2$48AaGX3YeHplfErX5Tlqa1      \N      \N      \N      S       1       A       2
    185199\.
    186 
    187 CREATE TABLE log (
    188     domain_id integer,
    189     user_id integer,
    190     group_id integer,
    191     email character varying(60),
    192     name character varying(60),
    193     entry character varying(200),
    194     stamp timestamp with time zone
    195 );
    196200
    197201--
    … …  
    243247ALTER TABLE ONLY groups
    244248    ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
    245 
    246 -- set sequence start values - make sure we don't screw up adding
    247 -- records to tables that already have a few entries
    248 SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
    249 
    250 SELECT pg_catalog.setval('groups_group_id_seq', 52, true);
    251 
    252 SELECT pg_catalog.setval('domains_domain_id_seq', 953, true);
    253 
    254 SELECT pg_catalog.setval('default_records_record_id_seq', 320, true);
    255 
    256 SELECT pg_catalog.setval('records_record_id_seq', 660, true);
    257 
    258 SELECT pg_catalog.setval('users_user_id_seq', 37, true);
Note: See TracChangeset for help on using the changeset viewer.