docs/poweradmin-pgsql-db-structure.sql
author rejo
Mon, 31 Mar 2008 06:43:38 +0000
changeset 119 f74e4f88b680
parent 118 839a18931017
permissions -rw-r--r--
[feladat @ 222] Changed case of Poweradmin's name (a is now lowercase).
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
106
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
     1
CREATE TABLE users (
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
     2
  id SERIAL PRIMARY KEY,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
     3
  username varchar(16) NOT NULL,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
     4
  password varchar(34) NOT NULL,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
     5
  fullname varchar(255) NOT NULL,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
     6
  email varchar(255) NOT NULL,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
     7
  description text NOT NULL,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
     8
  perm_templ integer default 0,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
     9
  active smallint default 0
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    10
);
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    11
113
e7a448dba024 [feladat @ 213]
peter
parents: 106
diff changeset
    12
INSERT INTO users (username, password, fullname, email, description, perm_templ, active) VALUES ('admin','21232f297a57a5a743894a0e4a801fc3','Administrator','admin@example.net','Administrator with full rights.',1,1);
106
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    13
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    14
CREATE TABLE perm_items (
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    15
  id SERIAL PRIMARY KEY,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    16
  name varchar(64) NOT NULL,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    17
  descr text NOT NULL
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    18
);
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    19
118
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    20
INSERT INTO perm_items (name, descr) VALUES ('user_is_ueberuser','User has full access. God-like. Redeemer.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    21
INSERT INTO perm_items (name, descr) VALUES ('zone_master_add','User is allowed to add new master zones.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    22
INSERT INTO perm_items (name, descr) VALUES ('zone_slave_add','User is allowed to add new slave zones.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    23
INSERT INTO perm_items (name, descr) VALUES ('zone_content_view_own','User is allowed to see the content and meta data of zones he owns.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    24
INSERT INTO perm_items (name, descr) VALUES ('zone_content_edit_own','User is allowed to edit the content of zones he owns.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    25
INSERT INTO perm_items (name, descr) VALUES ('zone_meta_edit_own','User is allowed to edit the meta data of zones he owns.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    26
INSERT INTO perm_items (name, descr) VALUES ('zone_content_view_others','User is allowed to see the content and meta data of zones he does not own.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    27
INSERT INTO perm_items (name, descr) VALUES ('zone_content_edit_others','User is allowed to edit the content of zones he does not own.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    28
INSERT INTO perm_items (name, descr) VALUES ('zone_meta_edit_others','User is allowed to edit the meta data of zones he does not own.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    29
INSERT INTO perm_items (name, descr) VALUES ('search','User is allowed to perform searches.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    30
INSERT INTO perm_items (name, descr) VALUES ('supermaster_view','User is allowed to view supermasters.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    31
INSERT INTO perm_items (name, descr) VALUES ('supermaster_add','User is allowed to add new supermasters.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    32
INSERT INTO perm_items (name, descr) VALUES ('supermaster_edit','User is allowed to edit supermasters.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    33
INSERT INTO perm_items (name, descr) VALUES ('user_view_others','User is allowed to see other users and their details.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    34
INSERT INTO perm_items (name, descr) VALUES ('user_add_new','User is allowed to add new users.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    35
INSERT INTO perm_items (name, descr) VALUES ('user_edit_own','User is allowed to edit their own details.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    36
INSERT INTO perm_items (name, descr) VALUES ('user_edit_others','User is allowed to edit other users.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    37
INSERT INTO perm_items (name, descr) VALUES ('user_passwd_edit_others','User is allowed to edit the password of other users.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    38
INSERT INTO perm_items (name, descr) VALUES ('user_edit_templ_perm','User is allowed to change the permission template that is assigned to a user.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    39
INSERT INTO perm_items (name, descr) VALUES ('templ_perm_add','User is allowed to add new permission templates.');
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    40
INSERT INTO perm_items (name, descr) VALUES ('templ_perm_edit','User is allowed to edit existing permission templates.');
106
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    41
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    42
CREATE TABLE perm_templ (
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    43
  id SERIAL PRIMARY KEY,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    44
  name varchar(128) NOT NULL,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    45
  descr text NOT NULL
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    46
);
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    47
113
e7a448dba024 [feladat @ 213]
peter
parents: 106
diff changeset
    48
INSERT INTO perm_templ (name, descr) VALUES ('Administrator','Administrator template with full rights.');
106
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    49
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    50
CREATE TABLE perm_templ_items (
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    51
  id SERIAL PRIMARY KEY,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    52
  templ_id integer NOT NULL,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    53
  perm_id integer NOT NULL
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    54
);
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    55
113
e7a448dba024 [feladat @ 213]
peter
parents: 106
diff changeset
    56
INSERT INTO perm_templ_items (templ_id, perm_id) VALUES (1,1);
106
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    57
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    58
CREATE TABLE zones (
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    59
  id SERIAL PRIMARY KEY,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    60
  domain_id integer default 0,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    61
  owner integer default 0,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    62
  comment text
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    63
);
118
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    64
839a18931017 [feladat @ 221]
peter
parents: 113
diff changeset
    65
CREATE INDEX zone_domain_owner ON zones(domain_id, owner);