WebSVN – general – Blame – /hostadmiral/trunk/sql/00.tables.sql/

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

Subversion Repositories general

(root)/hostadmiral/trunk/sql/00.tables.sql/ – Rev 899