Subversion Repositories general

Rev

Details | Last modification | View Log | RSS feed

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