| 2,9 → 2,33 |
|---|
| -- hostadmiral sql tables |
| -- |
| -- FIXME: which values has boolean in hibernate - '1':'0' or '1':'0'? |
| -- |
| |
| create sequence hibernate_sequence; |
| |
| -- version of this database structure, |
| -- one and only one entry is allowed |
| create table dbversion |
| ( |
| version integer not null |
| ); |
| insert into dbversion (version) values (1); |
| |
| -- passwords for different objects and crypted by different digests |
| create table passwords |
| ( |
| id integer not null, |
| obj integer, -- to which object belongs FIXME: schould be NOT NULL |
| -- but hibernate makes insert first then update for obj |
| digest varchar(255) not null, -- 'MD5', 'ENCRYPT' etc |
| password varchar(255) not null, -- the crypted password |
| mod_stamp timestamp, |
| mod_user integer, |
| |
| constraint passwords_prim primary key (id), |
| constraint passwords_unique unique(obj, digest) |
| ); |
| |
| -- an user is allowed to: |
| -- see/use an user if he is the 'boss' or is the same user |
| -- modify password - as above |
| 15,7 → 39,6 |
|---|
| ( |
| id integer not null, |
| login varchar(255) not null, |
| password varchar(255) not null, |
| boss integer, |
| superuser char(1) default '0' check (superuser = '1' or superuser = '0'), |
| locale varchar(5) default 'en', -- language_country |
| 44,8 → 67,10 |
|---|
| ); |
| |
| -- default user admin:admin |
| insert into users (id, login, password, superuser) values (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', '1'); |
| insert into users (id, login, superuser) values (1, 'admin', '1'); |
| insert into passwords (id, obj, digest, password) values (2, 1, 'MD5', '21232f297a57a5a743894a0e4a801fc3'); |
| select nextval('hibernate_sequence'); -- skip id of the default user |
| select nextval('hibernate_sequence'); -- skip id of the default password |
| |
| -- an user is allowed to see and use a system user if he is the 'owner' or the system user has no owner (null) |
| create table systemusers |
| 81,12 → 106,12 |
|---|
| ); |
| |
| -- name of mailbox = login + '@' + domain, e.g. user@example.com |
| -- if mailbox has no corresponding password entries, then owner's password is used |
| -- FIXME: make a possibility to use global unique mailboxes |
| create table mailboxes |
| ( |
| id integer not null, |
| login varchar(255) not null, |
| password varchar(255), -- if null, then owner's password is used |
| domain integer not null, |
| owner integer not null, |
| virusCheck char(1) default '1' check (virusCheck = '1' or virusCheck = '0'), |