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)
913	dev	11	-- 'superuser' is allowed to do anything with all objects, but cannot delete himself or modify own
899	dev	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,
913	dev	19	superuser char(1) default ' ' check (superuser = '1' or superuser = ' '),
		20	enabled char(1) default '1' check (enabled = '1' or enabled = ' '),
		21	comment text,
899	dev	22	mod_stamp timestamp,
		23	mod_user integer,
		24
		25	constraint users_prim primary key (id),
		26	constraint users_login unique(login),
		27	constraint users_boss foreign key (boss) references users(id)
		28	);
		29
		30	-- default user admin:admin
913	dev	31	insert into users (id, login, password, superuser) values (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', '1');
899	dev	32	select nextval('hibernate_sequence'); -- skip id of the default user
		33
		34	-- an user is allowed to see and use a system user if he is the 'owner' or the system user has no owner (null)
		35	create table systemusers
		36	(
		37	id integer not null,
		38	uid integer not null,
		39	name varchar(255) not null,
		40	owner integer,
913	dev	41	enabled char(1) default '1' check (enabled = '1' or enabled = ' '),
		42	comment text,
899	dev	43	mod_stamp timestamp,
		44	mod_user integer,
		45
		46	constraint systemusers_prim primary key (id),
		47	constraint systemusers_uid unique(uid),
		48	constraint systemusers_name unique(name),
		49	constraint systemusers_owner foreign key (owner) references users(id)
		50	);
		51
		52	create table domains
		53	(
		54	id integer not null,
		55	name varchar(255) not null,
		56	owner integer not null,
913	dev	57	enabled char(1) default '1' check (enabled = '1' or enabled = ' '),
		58	comment text,
899	dev	59	mod_stamp timestamp,
		60	mod_user integer,
		61
		62	constraint domains_prim primary key (id),
		63	constraint domains_name unique(name),
		64	constraint domains_owner foreign key (owner) references users(id)
		65	);
		66
		67	-- name of mailbox = login + '@' + domain, e.g. user@example.com
		68	-- FIXME: make a possibility to use global unique mailboxes
		69	create table mailboxes
		70	(
		71	id integer not null,
		72	login varchar(255) not null,
		73	password varchar(255), -- if null, then owner's password is used
		74	domain integer not null,
		75	owner integer not null,
913	dev	76	virusCheck char(1) default '1' check (virusCheck = '1' or virusCheck = ' '),
		77	spamCheck char(1) default '1' check (spamCheck = '1' or spamCheck = ' '),
899	dev	78	systemuser integer,
913	dev	79	enabled char(1) default '1' check (enabled = '1' or enabled = ' '),
		80	comment text,
899	dev	81	mod_stamp timestamp,
		82	mod_user integer,
		83
		84	constraint mailboxes_prim primary key (id),
		85	constraint mailboxes_name unique(login, domain),
		86	constraint mailboxes_domain foreign key (domain) references domains(id),
		87	constraint mailboxes_owner foreign key (owner) references users(id),
		88	constraint mailboxes_systemuser foreign key (systemuser) references systemusers(id)
		89	);
		90
		91	-- email address of alias = address + '@' + domain, e.g. user@example.com
		92	create table mailaliases
		93	(
		94	id integer not null,
		95	address varchar(255) not null,
		96	domain integer not null,
		97	owner integer not null,
913	dev	98	enabled char(1) default '1' check (enabled = '1' or enabled = ' '),
		99	comment text,
899	dev	100	mod_stamp timestamp,
		101	mod_user integer,
		102
		103	constraint mailaliases_prim primary key (id),
		104	constraint mailaliases_name unique(address, domain),
		105	constraint mailaliases_domain foreign key (domain) references domains(id),
		106	constraint mailaliases_owner foreign key (owner) references users(id)
		107	);
		108
		109	create table mailaliasdests
		110	(
		111	id integer not null,
		112	alias integer not null,
		113	mailbox integer,
		114	email varchar(255),
913	dev	115	enabled char(1) default '1' check (enabled = '1' or enabled = ' '),
		116	comment text,
899	dev	117	mod_stamp timestamp,
		118	mod_user integer,
		119
		120	constraint mailaliasdests_prim primary key (id),
		121	constraint mailaliasdests_dest check ((mailbox notnull) or (email notnull)),
		122	constraint mailaliasdests_mailbox foreign key (mailbox) references mailboxes(id),
		123	constraint mailaliasdests_email check ((email isnull) or (char_length(email) > 0))
		124	);

Subversion Repositories general

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