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