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

Rev	Author	Line No.	Line
906	dev	1	--
924	dev	2	-- hostadmiral sql tables
906	dev	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	);

Subversion Repositories general

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