Rev 1010 | Rev 1016 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed
Rev | Author | Line No. | Line |
---|---|---|---|
906 | dev | 1 | -- |
924 | dev | 2 | -- hostadmiral sql tables |
906 | dev | 3 | -- |
1010 | dev | 4 | -- FIXME: which values has boolean in hibernate - '1':'0' or '1':'0'? |
1015 | dev | 5 | -- |
906 | dev | 6 | |
899 | dev | 7 | create sequence hibernate_sequence; |
8 | |||
1015 | dev | 9 | -- version of this database structure, |
10 | -- one and only one entry is allowed |
||
11 | create table dbversion |
||
12 | ( |
||
13 | version integer not null |
||
14 | ); |
||
15 | insert into dbversion (version) values (1); |
||
16 | |||
17 | -- passwords for different objects and crypted by different digests |
||
18 | create table passwords |
||
19 | ( |
||
20 | id integer not null, |
||
21 | obj integer, -- to which object belongs FIXME: schould be NOT NULL |
||
22 | -- but hibernate makes insert first then update for obj |
||
23 | digest varchar(255) not null, -- 'MD5', 'ENCRYPT' etc |
||
24 | password varchar(255) not null, -- the crypted password |
||
25 | mod_stamp timestamp, |
||
26 | mod_user integer, |
||
27 | |||
28 | constraint passwords_prim primary key (id), |
||
29 | constraint passwords_unique unique(obj, digest) |
||
30 | ); |
||
31 | |||
899 | dev | 32 | -- an user is allowed to: |
33 | -- see/use an user if he is the 'boss' or is the same user |
||
34 | -- modify password - as above |
||
35 | -- modify login - only if he is the 'boss', not of his own, and only inside some name rules (e.g. name@domain) |
||
913 | dev | 36 | -- 'superuser' is allowed to do anything with all objects, but cannot delete himself or modify own |
899 | dev | 37 | -- 'superuser' flag (to guarantee at least one superuser exists in the system) |
38 | create table users |
||
39 | ( |
||
40 | id integer not null, |
||
41 | login varchar(255) not null, |
||
42 | boss integer, |
||
1010 | dev | 43 | superuser char(1) default '0' check (superuser = '1' or superuser = '0'), |
950 | dev | 44 | locale varchar(5) default 'en', -- language_country |
1010 | dev | 45 | enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
913 | dev | 46 | comment text, |
899 | dev | 47 | mod_stamp timestamp, |
48 | mod_user integer, |
||
49 | |||
50 | constraint users_prim primary key (id), |
||
51 | constraint users_login unique(login), |
||
52 | constraint users_boss foreign key (boss) references users(id) |
||
53 | ); |
||
54 | |||
949 | dev | 55 | -- login tries. "usr" is set if the user is found only |
56 | create table userlogins |
||
57 | ( |
||
58 | id integer not null, |
||
59 | usr integer, |
||
60 | login varchar(255) not null, |
||
61 | logintime timestamp not null, |
||
62 | success char(1) default '0' check (success = '1' or success = '0'), |
||
951 | dev | 63 | ip inet not null, |
949 | dev | 64 | |
65 | constraint userlogins_prim primary key (id), |
||
66 | constraint userlogins_user foreign key (usr) references users(id) |
||
67 | ); |
||
68 | |||
899 | dev | 69 | -- default user admin:admin |
1015 | dev | 70 | insert into users (id, login, superuser) values (1, 'admin', '1'); |
71 | insert into passwords (id, obj, digest, password) values (2, 1, 'MD5', '21232f297a57a5a743894a0e4a801fc3'); |
||
899 | dev | 72 | select nextval('hibernate_sequence'); -- skip id of the default user |
1015 | dev | 73 | select nextval('hibernate_sequence'); -- skip id of the default password |
899 | dev | 74 | |
75 | -- an user is allowed to see and use a system user if he is the 'owner' or the system user has no owner (null) |
||
76 | create table systemusers |
||
77 | ( |
||
78 | id integer not null, |
||
79 | uid integer not null, |
||
80 | name varchar(255) not null, |
||
81 | owner integer, |
||
1010 | dev | 82 | enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
913 | dev | 83 | comment text, |
899 | dev | 84 | mod_stamp timestamp, |
85 | mod_user integer, |
||
86 | |||
87 | constraint systemusers_prim primary key (id), |
||
88 | constraint systemusers_uid unique(uid), |
||
89 | constraint systemusers_name unique(name), |
||
90 | constraint systemusers_owner foreign key (owner) references users(id) |
||
91 | ); |
||
92 | |||
93 | create table domains |
||
94 | ( |
||
95 | id integer not null, |
||
96 | name varchar(255) not null, |
||
97 | owner integer not null, |
||
1010 | dev | 98 | enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
913 | dev | 99 | comment text, |
899 | dev | 100 | mod_stamp timestamp, |
101 | mod_user integer, |
||
102 | |||
103 | constraint domains_prim primary key (id), |
||
104 | constraint domains_name unique(name), |
||
105 | constraint domains_owner foreign key (owner) references users(id) |
||
106 | ); |
||
107 | |||
108 | -- name of mailbox = login + '@' + domain, e.g. user@example.com |
||
1015 | dev | 109 | -- if mailbox has no corresponding password entries, then owner's password is used |
899 | dev | 110 | -- FIXME: make a possibility to use global unique mailboxes |
111 | create table mailboxes |
||
112 | ( |
||
113 | id integer not null, |
||
114 | login varchar(255) not null, |
||
115 | domain integer not null, |
||
116 | owner integer not null, |
||
1010 | dev | 117 | virusCheck char(1) default '1' check (virusCheck = '1' or virusCheck = '0'), |
118 | spamCheck char(1) default '1' check (spamCheck = '1' or spamCheck = '0'), |
||
899 | dev | 119 | systemuser integer, |
1010 | dev | 120 | enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
913 | dev | 121 | comment text, |
899 | dev | 122 | mod_stamp timestamp, |
123 | mod_user integer, |
||
124 | |||
125 | constraint mailboxes_prim primary key (id), |
||
126 | constraint mailboxes_name unique(login, domain), |
||
127 | constraint mailboxes_domain foreign key (domain) references domains(id), |
||
128 | constraint mailboxes_owner foreign key (owner) references users(id), |
||
129 | constraint mailboxes_systemuser foreign key (systemuser) references systemusers(id) |
||
130 | ); |
||
131 | |||
132 | -- email address of alias = address + '@' + domain, e.g. user@example.com |
||
133 | create table mailaliases |
||
134 | ( |
||
135 | id integer not null, |
||
136 | address varchar(255) not null, |
||
137 | domain integer not null, |
||
138 | owner integer not null, |
||
1010 | dev | 139 | enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
913 | dev | 140 | comment text, |
899 | dev | 141 | mod_stamp timestamp, |
142 | mod_user integer, |
||
143 | |||
144 | constraint mailaliases_prim primary key (id), |
||
145 | constraint mailaliases_name unique(address, domain), |
||
146 | constraint mailaliases_domain foreign key (domain) references domains(id), |
||
147 | constraint mailaliases_owner foreign key (owner) references users(id) |
||
148 | ); |
||
149 | |||
150 | create table mailaliasdests |
||
151 | ( |
||
152 | id integer not null, |
||
153 | alias integer not null, |
||
154 | mailbox integer, |
||
155 | email varchar(255), |
||
1010 | dev | 156 | enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
913 | dev | 157 | comment text, |
899 | dev | 158 | mod_stamp timestamp, |
159 | mod_user integer, |
||
160 | |||
161 | constraint mailaliasdests_prim primary key (id), |
||
162 | constraint mailaliasdests_dest check ((mailbox notnull) or (email notnull)), |
||
163 | constraint mailaliasdests_mailbox foreign key (mailbox) references mailboxes(id), |
||
164 | constraint mailaliasdests_email check ((email isnull) or (char_length(email) > 0)) |
||
165 | ); |