Subversion Repositories general

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
906 dev 1 
--
2 
-- hostcaptain sql tables
3 
--
923 dev 4 
-- FIXME: which values has boolean in hibernate - '1':' ' or '1':'0'?
906 dev 5 
 
899 dev 6 
create sequence hibernate_sequence;
7 
 
8 
-- an user is allowed to:
9 
--   see/use an user if he is the 'boss' or is the same user
10 
--   modify password - as above
11 
--   modify login - only if he is the 'boss', not of his own, and only inside some name rules (e.g. name@domain)
913 dev 12 
-- 'superuser' is allowed to do anything with all objects, but cannot delete himself or modify own
899 dev 13 
--   'superuser' flag (to guarantee at least one superuser exists in the system)
14 
create table users
15 
(
16 
	id          integer                         not null,
17 
	login       varchar(255)                    not null,
18 
	password    varchar(255)                    not null,
19 
	boss        integer,
913 dev 20 
	superuser   char(1)                         default ' ' check (superuser = '1' or superuser = ' '),
21 
	enabled     char(1)                         default '1' check (enabled   = '1' or enabled   = ' '),
22 
	comment     text,
899 dev 23 
	mod_stamp   timestamp,
24 
	mod_user    integer,
25 
 
26 
	constraint users_prim  primary key (id),
27 
	constraint users_login unique(login),
28 
	constraint users_boss  foreign key (boss) references users(id)
29 
);
30 
 
31 
-- default user admin:admin
913 dev 32 
insert into users (id, login, password, superuser) values (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', '1');
899 dev 33 
select nextval('hibernate_sequence'); -- skip id of the default user
34 
 
35 
-- an user is allowed to see and use a system user if he is the 'owner' or the system user has no owner (null)
36 
create table systemusers
37 
(
38 
	id          integer                         not null,
39 
	uid         integer                         not null,
40 
	name        varchar(255)                    not null,
41 
	owner       integer,
913 dev 42 
	enabled     char(1)                         default '1' check (enabled   = '1' or enabled   = ' '),
43 
	comment     text,
899 dev 44 
	mod_stamp   timestamp,
45 
	mod_user    integer,
46 
 
47 
	constraint systemusers_prim  primary key (id),
48 
	constraint systemusers_uid   unique(uid),
49 
	constraint systemusers_name  unique(name),
50 
	constraint systemusers_owner foreign key (owner) references users(id)
51 
);
52 
 
53 
create table domains
54 
(
55 
	id          integer                         not null,
56 
	name        varchar(255)                    not null,
57 
	owner       integer                         not null,
913 dev 58 
	enabled     char(1)                         default '1' check (enabled   = '1' or enabled   = ' '),
59 
	comment     text,
899 dev 60 
	mod_stamp   timestamp,
61 
	mod_user    integer,
62 
 
63 
	constraint domains_prim  primary key (id),
64 
	constraint domains_name  unique(name),
65 
	constraint domains_owner foreign key (owner) references users(id)
66 
);
67 
 
68 
-- name of mailbox = login + '@' + domain, e.g. user@example.com
69 
-- FIXME: make a possibility to use global unique mailboxes
70 
create table mailboxes
71 
(
72 
	id          integer                         not null,
73 
	login       varchar(255)                    not null,
74 
	password    varchar(255),                             -- if null, then owner's password is used
75 
	domain      integer                         not null,
76 
	owner       integer                         not null,
913 dev 77 
	virusCheck  char(1)                         default '1' check (virusCheck = '1' or virusCheck = ' '),
78 
	spamCheck   char(1)                         default '1' check (spamCheck  = '1' or spamCheck  = ' '),
899 dev 79 
	systemuser  integer,
913 dev 80 
	enabled     char(1)                         default '1' check (enabled    = '1' or enabled    = ' '),
81 
	comment     text,
899 dev 82 
	mod_stamp   timestamp,
83 
	mod_user    integer,
84 
 
85 
	constraint mailboxes_prim       primary key (id),
86 
	constraint mailboxes_name       unique(login, domain),
87 
	constraint mailboxes_domain     foreign key (domain)     references domains(id),
88 
	constraint mailboxes_owner      foreign key (owner)      references users(id),
89 
	constraint mailboxes_systemuser foreign key (systemuser) references systemusers(id)
90 
);
91 
 
92 
-- email address of alias = address + '@' + domain, e.g. user@example.com
93 
create table mailaliases
94 
(
95 
	id          integer                         not null,
96 
	address     varchar(255)                    not null,
97 
	domain      integer                         not null,
98 
	owner       integer                         not null,
913 dev 99 
	enabled     char(1)                         default '1' check (enabled   = '1' or enabled   = ' '),
100 
	comment     text,
899 dev 101 
	mod_stamp   timestamp,
102 
	mod_user    integer,
103 
 
104 
	constraint mailaliases_prim   primary key (id),
105 
	constraint mailaliases_name   unique(address, domain),
106 
	constraint mailaliases_domain foreign key (domain) references domains(id),
107 
	constraint mailaliases_owner  foreign key (owner)  references users(id)
108 
);
109 
 
110 
create table mailaliasdests
111 
(
112 
	id          integer                         not null,
113 
	alias       integer                         not null,
114 
	mailbox     integer,
115 
	email       varchar(255),
913 dev 116 
	enabled     char(1)                         default '1' check (enabled   = '1' or enabled   = ' '),
117 
	comment     text,
899 dev 118 
	mod_stamp   timestamp,
119 
	mod_user    integer,
120 
 
121 
	constraint mailaliasdests_prim    primary key (id),
122 
	constraint mailaliasdests_dest    check ((mailbox notnull) or (email notnull)),
123 
	constraint mailaliasdests_mailbox foreign key (mailbox) references mailboxes(id),
124 
	constraint mailaliasdests_email   check ((email isnull) or (char_length(email) > 0))
125 
);