dba/awl-tables.sql

   1 -- Tables needed for AWL Libraries
   2
   3 BEGIN;
   4
   5 CREATE TABLE supported_locales (
   6   locale TEXT PRIMARY KEY,
   7   locale_name_en TEXT,
   8   locale_name_locale TEXT
   9 );
  10
  11 -- This is the table of users for the system
  12 CREATE TABLE usr (
  13   user_no SERIAL PRIMARY KEY,
  14   active BOOLEAN DEFAULT TRUE,
  15   email_ok TIMESTAMPTZ,
  16   joined TIMESTAMPTZ DEFAULT current_timestamp,
  17   updated TIMESTAMPTZ,
  18   last_used TIMESTAMPTZ,
  19   username TEXT NOT NULL,  -- Note UNIQUE INDEX below constains case-insensitive uniqueness
  20   password TEXT,
  21   fullname TEXT,
  22   email TEXT,
  23   config_data TEXT,
  24   date_format_type TEXT DEFAULT 'E', -- default to english date format dd/mm/yyyy
  25   locale TEXT
  26 );
  27 CREATE FUNCTION max_usr() RETURNS INT4 AS 'SELECT max(user_no) FROM usr' LANGUAGE 'sql';
  28 CREATE UNIQUE INDEX usr_sk1_unique_username ON usr ( lower(username) );
  29
  30 CREATE TABLE usr_setting (
  31   user_no INT4 REFERENCES usr ( user_no ) ON DELETE CASCADE,
  32   setting_name TEXT,
  33   setting_value TEXT,
  34   PRIMARY KEY ( user_no, setting_name )
  35 );
  36
  37 CREATE FUNCTION get_usr_setting(INT4,TEXT)
  38     RETURNS TEXT
  39     AS 'SELECT setting_value FROM usr_setting
  40             WHERE usr_setting.user_no = $1
  41             AND usr_setting.setting_name = $2 ' LANGUAGE 'sql';
  42
  43 CREATE TABLE roles (
  44     role_no SERIAL PRIMARY KEY,
  45     role_name TEXT
  46 );
  47 CREATE FUNCTION max_roles() RETURNS INT4 AS 'SELECT max(role_no) FROM roles' LANGUAGE 'sql';
  48
  49
  50 CREATE TABLE role_member (
  51     role_no INT4 REFERENCES roles ( role_no ),
  52     user_no INT4 REFERENCES usr ( user_no ) ON DELETE CASCADE
  53 );
  54
  55
  56 CREATE TABLE session (
  57     session_id SERIAL PRIMARY KEY,
  58     user_no INT4 REFERENCES usr ( user_no ) ON DELETE CASCADE,
  59     session_start TIMESTAMPTZ DEFAULT current_timestamp,
  60     session_end TIMESTAMPTZ DEFAULT current_timestamp,
  61     session_key TEXT,
  62     session_config TEXT
  63 );
  64 CREATE FUNCTION max_session() RETURNS INT4 AS 'SELECT max(session_id) FROM session' LANGUAGE 'sql';
  65
  66 CREATE TABLE tmp_password (
  67   user_no INT4 REFERENCES usr ( user_no ),
  68   password TEXT,
  69   valid_until TIMESTAMPTZ DEFAULT (current_timestamp + '1 day'::interval)
  70 );
  71 COMMIT;