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
949	dev	31	-- login tries. "usr" is set if the user is found only
		32	create table userlogins
		33	(
		34	id integer not null,
		35	usr integer,
		36	login varchar(255) not null,
		37	logintime timestamp not null,
		38	success char(1) default '0' check (success = '1' or success = '0'),
		39	ip inet not null,
		40
		41	constraint userlogins_prim primary key (id),
		42	constraint userlogins_user foreign key (usr) references users(id)
		43	);
		44
899	dev	45	-- default user admin:admin
913	dev	46	insert into users (id, login, password, superuser) values (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', '1');
899	dev	47	select nextval('hibernate_sequence'); -- skip id of the default user
		48
		49	-- an user is allowed to see and use a system user if he is the 'owner' or the system user has no owner (null)
		50	create table systemusers
		51	(
		52	id integer not null,
		53	uid integer not null,
		54	name varchar(255) not null,
		55	owner integer,
913	dev	56	enabled char(1) default '1' check (enabled = '1' or enabled = ' '),
		57	comment text,
899	dev	58	mod_stamp timestamp,
		59	mod_user integer,
		60
		61	constraint systemusers_prim primary key (id),
		62	constraint systemusers_uid unique(uid),
		63	constraint systemusers_name unique(name),
		64	constraint systemusers_owner foreign key (owner) references users(id)
		65	);
		66
		67	create table domains
		68	(
		69	id integer not null,
		70	name varchar(255) not null,
		71	owner integer not null,
913	dev	72	enabled char(1) default '1' check (enabled = '1' or enabled = ' '),
		73	comment text,
899	dev	74	mod_stamp timestamp,
		75	mod_user integer,
		76
		77	constraint domains_prim primary key (id),
		78	constraint domains_name unique(name),
		79	constraint domains_owner foreign key (owner) references users(id)
		80	);
		81
		82	-- name of mailbox = login + '@' + domain, e.g. user@example.com
		83	-- FIXME: make a possibility to use global unique mailboxes
		84	create table mailboxes
		85	(
		86	id integer not null,
		87	login varchar(255) not null,
		88	password varchar(255), -- if null, then owner's password is used
		89	domain integer not null,
		90	owner integer not null,
913	dev	91	virusCheck char(1) default '1' check (virusCheck = '1' or virusCheck = ' '),
		92	spamCheck char(1) default '1' check (spamCheck = '1' or spamCheck = ' '),
899	dev	93	systemuser integer,
913	dev	94	enabled char(1) default '1' check (enabled = '1' or enabled = ' '),
		95	comment text,
899	dev	96	mod_stamp timestamp,
		97	mod_user integer,
		98
		99	constraint mailboxes_prim primary key (id),
		100	constraint mailboxes_name unique(login, domain),
		101	constraint mailboxes_domain foreign key (domain) references domains(id),
		102	constraint mailboxes_owner foreign key (owner) references users(id),
		103	constraint mailboxes_systemuser foreign key (systemuser) references systemusers(id)
		104	);
		105
		106	-- email address of alias = address + '@' + domain, e.g. user@example.com
		107	create table mailaliases
		108	(
		109	id integer not null,
		110	address varchar(255) not null,
		111	domain integer not null,
		112	owner integer not null,
913	dev	113	enabled char(1) default '1' check (enabled = '1' or enabled = ' '),
		114	comment text,
899	dev	115	mod_stamp timestamp,
		116	mod_user integer,
		117
		118	constraint mailaliases_prim primary key (id),
		119	constraint mailaliases_name unique(address, domain),
		120	constraint mailaliases_domain foreign key (domain) references domains(id),
		121	constraint mailaliases_owner foreign key (owner) references users(id)
		122	);
		123
		124	create table mailaliasdests
		125	(
		126	id integer not null,
		127	alias integer not null,
		128	mailbox integer,
		129	email varchar(255),
913	dev	130	enabled char(1) default '1' check (enabled = '1' or enabled = ' '),
		131	comment text,
899	dev	132	mod_stamp timestamp,
		133	mod_user integer,
		134
		135	constraint mailaliasdests_prim primary key (id),
		136	constraint mailaliasdests_dest check ((mailbox notnull) or (email notnull)),
		137	constraint mailaliasdests_mailbox foreign key (mailbox) references mailboxes(id),
		138	constraint mailaliasdests_email check ((email isnull) or (char_length(email) > 0))
		139	);

Subversion Repositories general

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