[3733] | 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 ), |
---|
| 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 ) |
---|
| 53 | ); |
---|
| 54 | |
---|
| 55 | |
---|
| 56 | CREATE TABLE session ( |
---|
| 57 | session_id SERIAL PRIMARY KEY, |
---|
| 58 | user_no INT4 REFERENCES usr ( user_no ), |
---|
| 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; |
---|
| 72 | |
---|
| 73 | BEGIN; |
---|
| 74 | GRANT SELECT,INSERT,UPDATE ON |
---|
| 75 | usr |
---|
| 76 | , usr_setting |
---|
| 77 | , roles |
---|
| 78 | , role_member |
---|
| 79 | , session |
---|
| 80 | , tmp_password |
---|
| 81 | TO general; |
---|
| 82 | GRANT SELECT,UPDATE ON |
---|
| 83 | usr_user_no_seq |
---|
| 84 | , session_session_id_seq |
---|
| 85 | TO general; |
---|
| 86 | |
---|
| 87 | GRANT SELECT ON |
---|
| 88 | supported_locales |
---|
| 89 | TO general; |
---|
| 90 | |
---|
| 91 | GRANT DELETE ON |
---|
| 92 | tmp_password |
---|
| 93 | , role_member |
---|
| 94 | TO general; |
---|
| 95 | |
---|
| 96 | COMMIT; |
---|