# -----------------------------------------------------------------
# PHPWebLib SearchEngine v3 tables
#
# The tables must be present in all PHPWebLib v2 applications  
# requiring search engine (v3) functionality.
#
# Last modified: 		25 August 2007
# -----------------------------------------------------------------




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

CREATE TABLE IF NOT EXISTS weblib_search_terms (
	term_id				int unsigned 		not null auto_increment,
	term				varchar(100)		not null,
	stem				varchar(100)		not null,
	metaphone			varchar(100)		not null,
	
	inverse_doc_freq	decimal(6,6)		not null,		/* inverse of the number of rows in which the term appear */
	
	PRIMARY KEY								(term_id),
	UNIQUE INDEX		u_term				(term),
	INDEX				u_stem				(stem),
	INDEX				i_metaphone			(metaphone)
) TYPE=innodb character set utf8 collate utf8_general_ci;

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

CREATE TABLE IF NOT EXISTS weblib_search_term_ngrams (
	term_id				int unsigned		not null references weblib_search_terms(term_id),
	ngram				char(4)				not null,
	seq					tinyint unsigned	not null,
	
	PRIMARY KEY								(term_id, seq),
	INDEX				i_ngram				(ngram)
) TYPE=innodb character set utf8 collate utf8_general_ci;

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

CREATE TABLE IF NOT EXISTS weblib_search_bigrams (
	table_num			tinyint unsigned 	not null,			/* indexed table num & row_id */
	row_id				int unsigned 		not null,
	
	bigram				varchar(201)		not null,
	bigram_stem			varchar(201)		not null,
	
	INDEX				i_row				(table_num, row_id),
	
	INDEX				i_table_num			(table_num),
	INDEX				i_row_id			(row_id)
) TYPE=innodb character set utf8 collate utf8_general_ci;

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

CREATE TABLE IF NOT EXISTS weblib_search_term_index (
	table_num			tinyint unsigned 	not null,			/* indexed table num & row_id */
	row_id				int unsigned 		not null,
	
	field_table_num		tinyint unsigned	not null,			/* field, row and table housing content - if content is applied to a related row then these values will differ from table_num and row_id */
	field_num			tinyint unsigned	not null,
	field_row_id		int unsigned		not null,
	
	term_id				int unsigned		not null references weblib_search_terms(term_id),
	seq					int unsigned		not null,			/* sequence of term in field */
																/* index-time statistics used for relevance ranking */
	boost				decimal(4,2)		not null default 1,	/* field boost factor - set in search_indices config file */
	term_field_freq		decimal(6,3)		not null,			/* square root of the number of times the term appears in the currently scored field */
	field_len_norm		decimal(6,3)		not null,			/* field length norm value; inversely related to length of field */
	
	PRIMARY KEY								(table_num, row_id, term_id, field_table_num, field_num, field_row_id, 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_term_id			(term_id)
) TYPE=innodb character set utf8 collate utf8_general_ci;

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

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(30,10)		not null,
	edatetime			decimal(30,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 character set utf8 collate utf8_general_ci;

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

CREATE TABLE IF NOT EXISTS weblib_search_term_synonyms (
	term_id				int unsigned		not null references weblib_search_terms(term_id),
	synonym_id			int unsigned		not null references weblib_search_terms(term_id),
	
	UNIQUE INDEX		u_all				(term_id, synonym_id),
	INDEX				i_synonym_id		(synonym_id)
) TYPE=innodb character set utf8 collate utf8_general_ci;

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

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 character set utf8 collate utf8_general_ci;

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