Subversion Repositories general

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 'N' check (superuser = 'Y' or superuser = 'N'),
        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)
);

-- default user admin:admin
insert into users (id, login, password, superuser) values (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 'Y');
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,
        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,
        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),
        spamCheck   char(1),
        systemuser  integer,
        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,
        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),
        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))
);