0,0 → 1,140 |
-- |
-- hostadmiral sql tables |
-- |
-- FIXME: which values has boolean in hibernate - '1':' ' or '1':'0'? |
|
create sequence hibernate_sequence; |
|
-- 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, |
password varchar(255) not null, |
boss integer, |
superuser char(1) default ' ' check (superuser = '1' or superuser = ' '), |
locale varchar(5) default 'en', -- language_country |
enabled char(1) default '1' check (enabled = '1' or enabled = ' '), |
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) |
); |
|
-- 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) |
); |
|
-- default user admin:admin |
insert into users (id, login, password, superuser) values (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', '1'); |
select nextval('hibernate_sequence'); -- skip id of the default user |
|
-- 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 = ' '), |
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 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 = ' '), |
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) |
); |
|
-- name of mailbox = login + '@' + domain, e.g. user@example.com |
-- 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 = ' '), |
spamCheck char(1) default '1' check (spamCheck = '1' or spamCheck = ' '), |
systemuser integer, |
enabled char(1) default '1' check (enabled = '1' or enabled = ' '), |
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) |
); |
|
-- 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 = ' '), |
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 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 = ' '), |
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)) |
); |