| 0,0 → 1,179 |
|---|
| -- |
| -- 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 |
| ( |
| major integer not null, -- incremented if old structure is now useable with new code and upgrade needed |
| -- e.g. new tables, fields, new field type etc. minor is reset to 0 |
| minor integer not null -- incremented if some additional things changes and upgrade recomended but not |
| -- not needed, e.g. indexes |
| ); |
| insert into dbversion (major, minor) values (2, 0); |
| |
| -- 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) |
| ); |
| create index passwords_obj on passwords (obj); |
| |
| -- an user is allowed to: |
| -- see/use an user if he is the 'boss' or is the same user |
| -- modify password - as above |
| -- modify login - only if he is the 'boss', not of his own, and only inside some name rules (e.g. name@domain) |
| -- 'superuser' is allowed to do anything with all objects, but cannot delete himself or modify own |
| -- 'superuser' flag (to guarantee at least one superuser exists in the system) |
| create table users |
| ( |
| id integer not null, |
| login varchar(255) not null, |
| boss integer, |
| superuser char(1) default '0' check (superuser = '1' or superuser = '0'), |
| locale varchar(5) default 'en', -- language_country |
| enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
| comment text, |
| mod_stamp timestamp, |
| mod_user integer, |
| |
| constraint users_prim primary key (id), |
| constraint users_login unique(login), |
| constraint users_boss foreign key (boss) references users(id) |
| ); |
| create index users_boss on users (boss); |
| |
| -- login tries. "usr" is set if the user is found only |
| create table userlogins |
| ( |
| id integer not null, |
| usr integer, |
| login varchar(255) not null, |
| logintime timestamp not null, |
| success char(1) default '0' check (success = '1' or success = '0'), |
| ip inet not null, |
| |
| constraint userlogins_prim primary key (id), |
| constraint userlogins_user foreign key (usr) references users(id) |
| ); |
| create index userlogins_usrs on userlogins (usr); |
| |
| -- default user admin:admin |
| 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 |
| ( |
| id integer not null, |
| uid integer not null, |
| name varchar(255) not null, |
| owner integer, |
| enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
| comment text, |
| mod_stamp timestamp, |
| mod_user integer, |
| |
| constraint systemusers_prim primary key (id), |
| constraint systemusers_uid unique(uid), |
| constraint systemusers_name unique(name), |
| constraint systemusers_owner foreign key (owner) references users(id) |
| ); |
| create index systemusers_owner on systemusers (owner); |
| |
| create table domains |
| ( |
| id integer not null, |
| name varchar(255) not null, |
| owner integer not null, |
| enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
| comment text, |
| mod_stamp timestamp, |
| mod_user integer, |
| |
| constraint domains_prim primary key (id), |
| constraint domains_name unique(name), |
| constraint domains_owner foreign key (owner) references users(id) |
| ); |
| create index domains_owner on domains (owner); |
| |
| -- 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, |
| domain integer not null, |
| owner integer not null, |
| virusCheck char(1) default '1' check (virusCheck = '1' or virusCheck = '0'), |
| spamCheck char(1) default '1' check (spamCheck = '1' or spamCheck = '0'), |
| systemuser integer, |
| enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
| comment text, |
| mod_stamp timestamp, |
| mod_user integer, |
| |
| constraint mailboxes_prim primary key (id), |
| constraint mailboxes_name unique(login, domain), |
| constraint mailboxes_domain foreign key (domain) references domains(id), |
| constraint mailboxes_owner foreign key (owner) references users(id), |
| constraint mailboxes_systemuser foreign key (systemuser) references systemusers(id) |
| ); |
| create index mailboxes_domain on mailboxes (domain); |
| create index mailboxes_owner on mailboxes (owner); |
| |
| -- email address of alias = address + '@' + domain, e.g. user@example.com |
| create table mailaliases |
| ( |
| id integer not null, |
| address varchar(255) not null, |
| domain integer not null, |
| owner integer not null, |
| enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
| comment text, |
| mod_stamp timestamp, |
| mod_user integer, |
| |
| constraint mailaliases_prim primary key (id), |
| constraint mailaliases_name unique(address, domain), |
| constraint mailaliases_domain foreign key (domain) references domains(id), |
| constraint mailaliases_owner foreign key (owner) references users(id) |
| ); |
| create index mailaliases_domain on mailaliases (domain); |
| create index mailaliases_owner on mailaliases (owner); |
| |
| create table mailaliasdests |
| ( |
| id integer not null, |
| alias integer not null, |
| mailbox integer, |
| email varchar(255), |
| enabled char(1) default '1' check (enabled = '1' or enabled = '0'), |
| comment text, |
| mod_stamp timestamp, |
| mod_user integer, |
| |
| constraint mailaliasdests_prim primary key (id), |
| constraint mailaliasdests_dest check ((mailbox notnull) or (email notnull)), |
| constraint mailaliasdests_mailbox foreign key (mailbox) references mailboxes(id), |
| constraint mailaliasdests_email check ((email isnull) or (char_length(email) > 0)) |
| ); |
| create index mailaliasdests_alias on mailaliasdests (alias); |
| create index mailaliasdests_mailbox on mailaliasdests (mailbox); |