# -----------------------------------------------------------------
# PHPWebLib v3 tables
#
# The tables must be present in all PHPWebLib v3 applications.
#
# Last modified: 		11 July 2008
# -----------------------------------------------------------------
# Authentication and sessions
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_sessions (
	session_id		char(32)			not null,
	name			varchar(100)		not null,
	vars			longtext			not null,
	last_used		int	unsigned		not null,
	
	primary key							(session_id),
	index 			i_name 				(name),
	index 			i_last_used 		(last_used)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_users (
	user_id			int unsigned		not null auto_increment,
	user_name		varchar(255)		not null,
	userclass       tinyint unsigned    not null,
	password		varchar(100)		not null,
	fname           varchar(255)    	not null,
	lname           varchar(255)    	not null,
	email			varchar(255)		not null,
	vars			longtext			not null,
	active			tinyint unsigned	not null,
	confirmed_on	int	unsigned		null,
	confirmation_key	char(32)		null,
	
	primary key							(user_id),
	unique index	u_user_name			(user_name),
	index 			i_userclass			(userclass),
	unique index 	u_confirmation_key 	(confirmation_key)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_user_roles (
	role_id			smallint unsigned	not null auto_increment,
	name			varchar(255)		not null,
	code			varchar(10)			not null,
	description		text				not null,
	rank			smallint unsigned	not null,
	permissions		text				not null,
	is_system_role	tinyint unsigned	not null,
	
	primary key							(role_id)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_users_x_roles (
	link_id			int unsigned		not null auto_increment,
	user_id			int unsigned		not null references weblib_users(user_id),
	role_id			smallint unsigned	not null references weblib_user_roles(role_id),
	
	primary key							(link_id),
	index			i_user_id			(user_id),
	index			i_role_id			(role_id),
	unique index	u_all				(user_id, role_id)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_user_groups (
	group_id		int unsigned		not null auto_increment,
	name			varchar(255)		not null,
	name_short		varchar(20)			not null,
	description		text				not null,
	
	admin_id		int unsigned		not null references weblib_users(user_id),
	created_on		int unsigned		not null,
	
	rank			smallint unsigned	not null,
	
	primary key							(group_id)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_groups_x_roles (
	link_id			int unsigned		not null auto_increment,
	group_id		int unsigned		not null references weblib_user_groups(group_id),
	role_id			smallint unsigned	not null references weblib_user_roles(role_id),
	
	primary key							(link_id),
	index			i_group_id			(group_id),
	index			i_role_id			(role_id),
	unique index	u_all				(group_id, role_id)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_users_x_groups (
	link_id			int unsigned		not null auto_increment,
	user_id			int unsigned		not null references weblib_users(user_id),
	group_id		int unsigned		not null references weblib_user_groups(group_id),
	
	primary key							(link_id),
	index			i_user_id			(user_id),
	index			i_group_id			(group_id),
	unique index	u_all				(user_id, group_id)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_ips (
	ip_id			int unsigned		not null auto_increment,
	user_id			int unsigned		not null references weblib_users(user_id),
	ip1				tinyint unsigned	not null,
	ip2				tinyint	unsigned	null,
	ip3				tinyint	unsigned	null,
	ip4s			tinyint	unsigned	null,
	ip4e			tinyint	unsigned	null,
	notes			text				not null,

	primary key							(ip_id),
	index			i_user_id			(user_id),
	unique index 	u_ip 				(ip1, ip2, ip3, ip4s, ip4e)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
# Event logging
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_eventlog (
	date_time		int 				not null,
	code			char(4) 			not null,
	message			text				not null,
	source			varchar(255) 		not null,
	
	index 			i_when 				(date_time),
	index 			i_source 			(source)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
# Application variables
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_application_vars (
	vars			longtext			not null
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
# Task queuing
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_task_queue (
	task_id			int unsigned		not null auto_increment,
	user_id			int unsigned		null,
	row_key			char(32)			null,
	entity_key		char(32)			null,
	created_on		int unsigned		not null,
	completed_on	int unsigned		null,
	priority		smallint unsigned	not null,
	
	handler			varchar(20)			not null,
	
	parameters		text				not null,
	notes			text				not null,
	
	primary key							(task_id),
	index			i_handler			(handler),
	index			i_entity_key		(entity_key),
	index			i_user_id			(user_id)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
# Row locking
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_row_locks (
	lock_id			int unsigned		not null auto_increment,
	table_num		tinyint unsigned	not null,
	row_id			int unsigned		not null,
	
	user_id			int unsigned		not null references weblib_users(user_id),
	
	created_on		int unsigned		not null,
	last_ping		int unsigned		not null,
	
	primary key							(lock_id),
	index			i_row_id			(row_id),
	index			i_table_row			(table_num, row_id),
	index			I_created_on		(created_on),
	index			i_last_ping			(last_ping),
	index			i_user_id			(user_id),
	unique index	u_lock				(row_id, table_num, user_id)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
# User file workspace
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_user_workspace_items (
	item_id			int unsigned		not null auto_increment,
	user_id			int unsigned		not null references weblib_users(user_id),
	mimetype		varchar(255)		not null,
	typecode		char(1)				not null,
	media_info		longblob			not null,
	file_info		text				not null,
	dir_path		text				not null,
	uploaded_on		int unsigned		not null,
	md5_checksum	char(32)			not null,
	
	primary key							(item_id),
	index			i_user_id			(user_id),
	index			i_md5_checksum		(md5_checksum)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
# Row-level Access Control Lists (ACL)
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_acl (
	acl_id			int unsigned		not null auto_increment,
	table_num		tinyint unsigned	not null,
	row_id			int unsigned		not null,
	
	entity_type		char(1)				not null,
	entity_id		int unsigned		not null,
	
	access			tinyint unsigned	not null,
	
	primary key							(acl_id),
	index			i_row_id			(row_id),
	index			i_table_row			(table_num, row_id, access),
	index			i_entity_id			(entity_id),
	index			i_entity_row		(entity_type, entity_id, access)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
# Change history
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_change_log (
	log_id				bigint				not null auto_increment,
	log_datetime		int unsigned		not null,
	user_id				int unsigned		null,
	changetype			char(1)				not null,
	logged_table_num	int unsigned		not null,
	logged_row_id		int unsigned		not null,
	snapshot			longtext			not null,
	remarks				text				not null,
	unit_type			tinyint unsigned	null,
	unit_id				int unsigned		null,
	
	primary key 							(log_id),
	index				i_datetime			(log_datetime),
	index				i_user_id			(user_id),
	index				i_logged			(logged_row_id, logged_table_num),
	index				i_unit				(unit_id, unit_type)
) type=myisam character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS weblib_change_log_subjects (
	log_id				bigint				not null,
	subject_table_num	tinyint unsigned	not null,
	subject_row_id		int unsigned		not null,
	
	index				i_log_id			(log_id),
	index				i_subject			(subject_row_id, subject_table_num)
	
) type=myisam character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------
# Locales/i18n
# -----------------------------------------------------------------
CREATE TABLE weblib_locales (
	locale_id		smallint not null primary key auto_increment,
	name			varchar(255) not null,
	language		varchar(3) not null,
	country			char(2) not null,
	dialect			varchar(8) null,
	description		text not null,
	formats			text not null,
	
	UNIQUE INDEX u_all (name, language, country, dialect),
	INDEX i_language (language),
	INDEX i_country (country)
) type=innodb character set utf8 collate utf8_general_ci;
# -----------------------------------------------------------------