Subversion Repositories general

--

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

);