Rev 1015 | 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 | ); |
||
1016 | dev | 31 | create index passwords_obj on passwords (obj); |
1015 | dev | 32 | |
899 | dev | 33 | -- an user is allowed to: |
34 | -- see/use an user if he is the 'boss' or is the same user |
||
35 | -- modify password - as above |
||
36 | -- modify login - only if he is the 'boss', not of his own, and only inside some name rules (e.g. name@domain) |
||
913 | dev | 37 | -- 'superuser' is allowed to do anything with all objects, but cannot delete himself or modify own |
899 | dev | 38 | -- 'superuser' flag (to guarantee at least one superuser exists in the system) |
39 | create table users |
||
40 | ( |
||
41 | id integer not null, |
||
42 | login varchar(255) not null, |
||
43 | boss integer, |
||
1010 | dev | 44 | superuser char(1) default '0' check (superuser = '1' or superuser = '0'), |
950 | dev | 45 | locale varchar(5) default 'en', -- language_country |
1010 | dev | 46 | enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
913 | dev | 47 | comment text, |
899 | dev | 48 | mod_stamp timestamp, |
49 | mod_user integer, |
||
50 | |||
51 | constraint users_prim primary key (id), |
||
52 | constraint users_login unique(login), |
||
53 | constraint users_boss foreign key (boss) references users(id) |
||
54 | ); |
||
1016 | dev | 55 | create index users_boss on users (boss); |
899 | dev | 56 | |
949 | dev | 57 | -- login tries. "usr" is set if the user is found only |
58 | create table userlogins |
||
59 | ( |
||
60 | id integer not null, |
||
61 | usr integer, |
||
62 | login varchar(255) not null, |
||
63 | logintime timestamp not null, |
||
64 | success char(1) default '0' check (success = '1' or success = '0'), |
||
951 | dev | 65 | ip inet not null, |
949 | dev | 66 | |
67 | constraint userlogins_prim primary key (id), |
||
68 | constraint userlogins_user foreign key (usr) references users(id) |
||
69 | ); |
||
1016 | dev | 70 | create index userlogins_usrs on userlogins (usr); |
949 | dev | 71 | |
899 | dev | 72 | -- default user admin:admin |
1015 | dev | 73 | insert into users (id, login, superuser) values (1, 'admin', '1'); |
74 | insert into passwords (id, obj, digest, password) values (2, 1, 'MD5', '21232f297a57a5a743894a0e4a801fc3'); |
||
899 | dev | 75 | select nextval('hibernate_sequence'); -- skip id of the default user |
1015 | dev | 76 | select nextval('hibernate_sequence'); -- skip id of the default password |
899 | dev | 77 | |
78 | -- an user is allowed to see and use a system user if he is the 'owner' or the system user has no owner (null) |
||
79 | create table systemusers |
||
80 | ( |
||
81 | id integer not null, |
||
82 | uid integer not null, |
||
83 | name varchar(255) not null, |
||
84 | owner integer, |
||
1010 | dev | 85 | enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
913 | dev | 86 | comment text, |
899 | dev | 87 | mod_stamp timestamp, |
88 | mod_user integer, |
||
89 | |||
90 | constraint systemusers_prim primary key (id), |
||
91 | constraint systemusers_uid unique(uid), |
||
92 | constraint systemusers_name unique(name), |
||
93 | constraint systemusers_owner foreign key (owner) references users(id) |
||
94 | ); |
||
1016 | dev | 95 | create index systemusers_owner on systemusers (owner); |
899 | dev | 96 | |
97 | create table domains |
||
98 | ( |
||
99 | id integer not null, |
||
100 | name varchar(255) not null, |
||
101 | owner integer not null, |
||
1010 | dev | 102 | enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
913 | dev | 103 | comment text, |
899 | dev | 104 | mod_stamp timestamp, |
105 | mod_user integer, |
||
106 | |||
107 | constraint domains_prim primary key (id), |
||
108 | constraint domains_name unique(name), |
||
109 | constraint domains_owner foreign key (owner) references users(id) |
||
110 | ); |
||
1016 | dev | 111 | create index domains_owner on domains (owner); |
899 | dev | 112 | |
113 | -- name of mailbox = login + '@' + domain, e.g. user@example.com |
||
1015 | dev | 114 | -- if mailbox has no corresponding password entries, then owner's password is used |
899 | dev | 115 | -- FIXME: make a possibility to use global unique mailboxes |
116 | create table mailboxes |
||
117 | ( |
||
118 | id integer not null, |
||
119 | login varchar(255) not null, |
||
120 | domain integer not null, |
||
121 | owner integer not null, |
||
1010 | dev | 122 | virusCheck char(1) default '1' check (virusCheck = '1' or virusCheck = '0'), |
123 | spamCheck char(1) default '1' check (spamCheck = '1' or spamCheck = '0'), |
||
899 | dev | 124 | systemuser integer, |
1010 | dev | 125 | enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
913 | dev | 126 | comment text, |
899 | dev | 127 | mod_stamp timestamp, |
128 | mod_user integer, |
||
129 | |||
130 | constraint mailboxes_prim primary key (id), |
||
131 | constraint mailboxes_name unique(login, domain), |
||
132 | constraint mailboxes_domain foreign key (domain) references domains(id), |
||
133 | constraint mailboxes_owner foreign key (owner) references users(id), |
||
134 | constraint mailboxes_systemuser foreign key (systemuser) references systemusers(id) |
||
135 | ); |
||
1016 | dev | 136 | create index mailboxes_domain on mailboxes (domain); |
137 | create index mailboxes_owner on mailboxes (owner); |
||
899 | dev | 138 | |
139 | -- email address of alias = address + '@' + domain, e.g. user@example.com |
||
140 | create table mailaliases |
||
141 | ( |
||
142 | id integer not null, |
||
143 | address varchar(255) not null, |
||
144 | domain integer not null, |
||
145 | owner integer not null, |
||
1010 | dev | 146 | enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
913 | dev | 147 | comment text, |
899 | dev | 148 | mod_stamp timestamp, |
149 | mod_user integer, |
||
150 | |||
151 | constraint mailaliases_prim primary key (id), |
||
152 | constraint mailaliases_name unique(address, domain), |
||
153 | constraint mailaliases_domain foreign key (domain) references domains(id), |
||
154 | constraint mailaliases_owner foreign key (owner) references users(id) |
||
155 | ); |
||
1016 | dev | 156 | create index mailaliases_domain on mailaliases (domain); |
157 | create index mailaliases_owner on mailaliases (owner); |
||
899 | dev | 158 | |
159 | create table mailaliasdests |
||
160 | ( |
||
161 | id integer not null, |
||
162 | alias integer not null, |
||
163 | mailbox integer, |
||
164 | email varchar(255), |
||
1010 | dev | 165 | enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
913 | dev | 166 | comment text, |
899 | dev | 167 | mod_stamp timestamp, |
168 | mod_user integer, |
||
169 | |||
170 | constraint mailaliasdests_prim primary key (id), |
||
171 | constraint mailaliasdests_dest check ((mailbox notnull) or (email notnull)), |
||
172 | constraint mailaliasdests_mailbox foreign key (mailbox) references mailboxes(id), |
||
173 | constraint mailaliasdests_email check ((email isnull) or (char_length(email) > 0)) |
||
174 | ); |
||
1016 | dev | 175 | create index mailaliasdests_alias on mailaliasdests (alias); |
176 | create index mailaliasdests_mailbox on mailaliasdests (mailbox); |