# IlohaMail 0.7.3
# All required and optional tables for MySQL backend

CREATE TABLE users (
   id mediumint(9) NOT NULL auto_increment,
   login varchar(50) NOT NULL,
   host varchar(50) NOT NULL,
   dateCreated int(12),
   lastLogin int(12),
   userLevel int(3),
   PRIMARY KEY (id),
   KEY id (id),
   UNIQUE id_2 (id),
   KEY login (login),
   KEY host (host)
);

CREATE TABLE contacts (
	id mediumint(9) NOT NULL auto_increment,
	owner mediumint(9) NOT NULL,
	name text,
	email text,
	email2 text,
	grp text,
	aim text,
	icq text,
	msn text,
	yahoo text,
	jabber text,
	phone text,
	work text,
	cell text,
	address text,
	url text,
	cal_url text,
	rss_url text,
	comments text,
	shared char,
	firstname text,
	lastname text,
	street text,
	extended text,
	city text,
	region text,
	postalcode text,
	country text,
	PRIMARY KEY (id),
	KEY id (id),
	KEY owner (owner),
	KEY shared (shared)
);

CREATE TABLE bookmarks(
	id int NOT NULL auto_increment,
	owner int NOT NULL,
	name text,
	url text,
	rss text,
	grp text,
	is_private char,
	comments text,
	PRIMARY KEY (id),
	KEY id (id),
	KEY owner (owner)
);

CREATE TABLE sessions (
	sid varchar(64) NOT NULL,
	login text,
	password text,
	host text,
	path text,
	dataID int,
	port int,
	userLevel int(3),
	inTime int(12) NOT NULL,
	lastSend int,
	numSent int,
	check_data varchar(64),
	check_sum int(4),
	js_mode char(1),
	PRIMARY KEY sid (sid),
	KEY inTime (inTime)
);

CREATE TABLE user_log(
	logTime datetime,
	logTimeStamp int(12),
	userID int,
	account text,
	action text,
	comment text,
	ip varchar(15)
);

CREATE TABLE calendar(
	id int NOT NULL auto_increment,
	userID int,
	title text,
	place text,
	description text,
	participants text,
	beginTime int,
	endTime int,
	beginDate int(11),
	endDate int (11),
	pattern text,
	isPrivate char,
	color varchar(20),
	shared char,
	allday char,
	publish char,
	PRIMARY KEY (id),
	KEY id (id),
	KEY userID (userID),
	KEY beginDate (beginDate),
	KEY endDate (endDate),
	KEY shared (shared),
	KEY publish (shared)
);

CREATE TABLE identities(
	id int NOT NULL auto_increment,
    owner mediumint(9) NOT NULL,
	name varchar(128),
	email varchar(128),
	replyto varchar(128),
	sig text,
	PRIMARY KEY (id),
	KEY id (id),
	KEY owner (owner)
);

CREATE TABLE cache(
	id int NOT NULL auto_increment,
	owner int NOT NULL,
	cache_key varchar(64),
	cache_data text,
	cache_ts int(11),
	volatile bool,
	PRIMARY KEY(id),
	KEY id (id),
	KEY owner (owner),
	KEY cache_key (cache_key),
	KEY cache_ts (cache_ts),
	KEY volatile (volatile)
);

CREATE TABLE datastore(
	id int NOT NULL auto_increment,
	owner int NOT NULL,
	ds_key varchar(16) NOT NULL,
	ds_data text,
	format char,
	PRIMARY KEY id (id),
	KEY id (id),
	KEY owner (owner),
	KEY ds_key (ds_key)
);

CREATE TABLE filters(
	id int NOT NULL auto_increment,
	owner int NOT NULL,
	name text,
	rule text,
	flags text,
	sort_order int,
	PRIMARY KEY id (id),
	KEY id (id),
	KEY sort_order (sort_order)
);