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

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

Subversion Repositories general

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