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; |
---|