docs/poweradmin-pgsql-db-structure.sql
author peter
Fri, 28 Mar 2008 21:40:42 +0000
changeset 106 41d05ff53708
child 113 e7a448dba024
permissions -rw-r--r--
[feladat @ 206] Added database structure for PostgreSQL and fixed some queries to be compatible with PostgreSQL. Bugfix in delete_supermaster.php
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
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    12
INSERT INTO users VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3','Administrator','admin@example.net','Administrator with full rights.',1,1);
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
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    20
INSERT INTO perm_items VALUES (41,'zone_master_add','User is allowed to add new master zones.'),(42,'zone_slave_add','User is allowed to add new slave zones.'),(43,'zone_content_view_own','User is allowed to see the content and meta data of zones he owns.'),(44,'zone_content_edit_own','User is allowed to edit the content of zones he owns.'),(45,'zone_meta_edit_own','User is allowed to edit the meta data of zones he owns.'),(46,'zone_content_view_others','User is allowed to see the content and meta data of zones he does not own.'),(47,'zone_content_edit_others','User is allowed to edit the content of zones he does not own.'),(48,'zone_meta_edit_others','User is allowed to edit the meta data of zones he does not own.'),(49,'search','User is allowed to perform searches.'),(50,'supermaster_view','User is allowed to view supermasters.'),(51,'supermaster_add','User is allowed to add new supermasters.'),(52,'supermaster_edit','User is allowed to edit supermasters.'),(53,'user_is_ueberuser','User has full access. God-like. Redeemer.'),(54,'user_view_others','User is allowed to see other users and their details.'),(55,'user_add_new','User is allowed to add new users.'),(56,'user_edit_own','User is allowed to edit their own details.'),(57,'user_edit_others','User is allowed to edit other users.'),(58,'user_passwd_edit_others','User is allowed to edit the password of other users.'),(59,'user_edit_templ_perm','User is allowed to change the permission template that is assigned to a user.'),(60,'templ_perm_add','User is allowed to add new permission templates.'),(61,'templ_perm_edit','User is allowed to edit existing permission templates.');
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    21
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    22
CREATE TABLE perm_templ (
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    23
  id SERIAL PRIMARY KEY,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    24
  name varchar(128) NOT NULL,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    25
  descr text NOT NULL
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    26
);
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    27
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    28
INSERT INTO perm_templ VALUES (1,'Administrator','Administrator template with full rights.');
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    29
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    30
CREATE TABLE perm_templ_items (
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    31
  id SERIAL PRIMARY KEY,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    32
  templ_id integer NOT NULL,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    33
  perm_id integer NOT NULL
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    34
);
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    35
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    36
INSERT INTO perm_templ_items VALUES (249,1,53);
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    37
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    38
CREATE TABLE zones (
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    39
  id SERIAL PRIMARY KEY,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    40
  domain_id integer default 0,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    41
  owner integer default 0,
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    42
  comment text
41d05ff53708 [feladat @ 206]
peter
parents:
diff changeset
    43
);