Subversion Repositories general

--

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