# -----------------------------------------------------------------
# PHPWebLib SearchEngine2 tables
#
# The tables must be present in all PHPWebLib v2 applications requiring 
# search engine functionality.
#
# Last modified: 		22 July 2007
# -----------------------------------------------------------------

DROP TABLE IF EXISTS weblib_search_word_index;
DROP TABLE IF EXISTS weblib_search_text;
DROP TABLE IF EXISTS weblib_search_word_synonyms;
DROP TABLE IF EXISTS weblib_search_phrase_synonyms;
DROP TABLE IF EXISTS weblib_search_date_index;
DROP TABLE IF EXISTS weblib_search_words;
DROP TABLE IF EXISTS weblib_search_ngrams;


# -----------------------------------------------------------------

CREATE TABLE IF NOT EXISTS weblib_search_word_index (
	index_id			int unsigned		not null auto_increment,
	
	table_num			tinyint unsigned 	not null,
	row_id				int unsigned 		not null,
	
	field_table_num		tinyint unsigned	not null,
	field_num			tinyint unsigned	not null,
	field_row_id		int unsigned		not null,
	
	word_id				int unsigned		not null references weblib_search_words(word_id),
	seq					int unsigned		not null,
	
	PRIMARY KEY								(index_id),
	
	INDEX				i_table_num			(table_num),
	INDEX				i_row_id			(row_id),
	INDEX				i_field_table_num	(field_table_num),
	INDEX				i_field_num			(field_num),
	INDEX				i_field_row_id		(field_row_id),
	INDEX				i_word_id			(word_id)
) TYPE=innodb character set utf8 collate utf8_general_ci;

# -----------------------------------------------------------------


CREATE TABLE IF NOT EXISTS weblib_search_text (
	index_id			int unsigned		not null auto_increment,
	
	table_num			tinyint unsigned 	not null,
	row_id				int unsigned 		not null,
	
	field_table_num		tinyint unsigned	not null,
	field_num			tinyint unsigned	not null,
	field_row_id		int unsigned		not null,
	
	fieldtext			longtext 			not null,
	
	PRIMARY KEY								(index_id),
	FULLTEXT INDEX		f_fulltext			(fieldtext),
	INDEX				i_table_num			(table_num),
	INDEX				i_row_id			(row_id),
	INDEX				i_field_table_num	(field_table_num),
	INDEX				i_field_num			(field_num),
	INDEX				i_field_row_id		(field_row_id)
	
) TYPE=myisam character set utf8 collate utf8_general_ci;

# -----------------------------------------------------------------

CREATE TABLE IF NOT EXISTS weblib_search_word_synonyms (
	word_id				int unsigned		not null references weblib_search_words(word_id),
	synonym_id			int unsigned		not null references weblib_search_words(word_id),
	
	UNIQUE INDEX		u_all				(word_id, synonym_id),
	INDEX				i_synonym_id		(synonym_id)
) TYPE=innodb;

# -----------------------------------------------------------------

CREATE TABLE IF NOT EXISTS weblib_search_phrase_synonyms (
	phrase				varchar(255)		not null,
	synonym				varchar(255)		not null,
	
	INDEX				i_phrase			(phrase),
	INDEX				i_synonym			(synonym)
) TYPE=innodb;

# -----------------------------------------------------------------

CREATE TABLE IF NOT EXISTS weblib_search_date_index (	
	table_num			tinyint unsigned 	not null,
	row_id				int unsigned 		not null,
	
	field_table_num		tinyint unsigned	not null,
	field_num			tinyint unsigned	not null,
	field_row_id		int unsigned		not null,
	
	sdatetime			decimal(20,10)		not null,
	edatetime			decimal(20,10)		not null,
	seq					int unsigned		not null,
	
	PRIMARY KEY								(table_num, row_id, field_table_num, field_num, field_row_id, sdatetime, edatetime, seq),
	INDEX				i_table_num			(table_num),
	INDEX				i_row_id			(row_id),
	INDEX				i_field_table_num	(field_table_num),
	INDEX				i_field_num			(field_num),
	INDEX				i_field_row_id		(field_row_id),
	INDEX				i_date				(sdatetime, edatetime)
) TYPE=innodb;

# -----------------------------------------------------------------

CREATE TABLE IF NOT EXISTS weblib_search_words (
	word_id				int unsigned 		not null auto_increment,
	word				varchar(255)		not null,
	soundex				varchar(4)			not null,
	metaphone			varchar(255)		not null,
	stem				varchar(255)		not null,
	
	PRIMARY KEY								(word_id),
	UNIQUE INDEX		u_word				(word),
	INDEX				i_soundex			(soundex),
	INDEX				i_metaphone			(metaphone),
	INDEX				i_stem				(stem)
) TYPE=innodb;

# -----------------------------------------------------------------

CREATE TABLE IF NOT EXISTS weblib_search_ngrams (
	word_id				int unsigned		not null references weblib_search_words(word_id),
	ngram				char(4)				not null,
	seq					tinyint unsigned	not null,
	
	PRIMARY KEY								(word_id, seq),
	INDEX				i_ngram				(ngram)
) TYPE=innodb;
