Subversion Repositories general

Rev

Details | Last modification | View Log | RSS feed

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