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'), |