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