# This is script for MySQL to 
# create database structure for UdmSearch 3.2.x
# use: "mysql database <create.txt"
#

SET storage_engine=MyISAM;

CREATE TABLE url (
  rec_id	  int(11) NOT NULL auto_increment,
  status	  smallint(6) DEFAULT '0' NOT NULL,
  docsize	  int(11) DEFAULT '0' NOT NULL,
  next_index_time INT NOT NULL,
  last_mod_time	  INT DEFAULT '0' NOT NULL,
  referrer	  int(11) DEFAULT '0' NOT NULL,
  hops		  smallint(6) DEFAULT '0' NOT NULL,
  crc32		  int(11)	DEFAULT '-1' NOT NULL,
  seed		  smallint(6) DEFAULT '0' NOT NULL,
  bad_since_time  INT NOT NULL,
  site_id	  int(11),
  server_id	  int(11),
  shows           int(11) DEFAULT '0' NOT NULL,
  pop_rank	  float DEFAULT 0 NOT NULL,
  url		  BLOB NOT NULL,
  PRIMARY KEY (rec_id),
  UNIQUE url (url(255)),
  KEY key_crc (crc32),
  KEY key_seed (seed),
  KEY key_referrer (referrer),
  KEY key_bad_since_time (bad_since_time),
  KEY key_next_index_time (next_index_time, seed, rec_id),
  KEY key_site_id (site_id),
  KEY ley_server_id (server_id),
  KEY key_status (status),
  KEY key_hops (hops)
) MAX_ROWS=300000000 AVG_ROW_LENGTH=512;


CREATE TABLE urlinfo (
  url_id INT NOT NULL,
  sname  VARCHAR(32)  NOT NULL,
  sval   MEDIUMTEXT         NOT NULL,
  KEY    urlinfo_id  (url_id)
) MAX_ROWS=300000000 AVG_ROW_LENGTH=512;


CREATE TABLE dict (
  url_id int(11) DEFAULT '0' NOT NULL,
  word varchar(32) DEFAULT '' NOT NULL,
  intag int(11) DEFAULT '0' NOT NULL,
  KEY url_id (url_id),
  KEY word_url (word)
);

CREATE TABLE bdict (
  word VARCHAR(255) NOT NULL,
  secno TINYINT UNSIGNED NOT NULL,
  intag LONGBLOB NOT NULL,
  KEY(word)
) MAX_ROWS=300000000 AVG_ROW_LENGTH=512;


CREATE TABLE categories (
  rec_id int(11) NOT NULL auto_increment,
  path char(10) DEFAULT '' NOT NULL,
  link char(10) DEFAULT '' NOT NULL,
  name char(64) DEFAULT '' NOT NULL,
  PRIMARY KEY (rec_id)
);


CREATE TABLE qtrack (
  rec_id  int(11) NOT NULL auto_increment,
  ip      varchar(16) NOT NULL,
  qwords  TEXT NOT NULL,
  qtime   int(11) DEFAULT '0' NOT NULL,
  wtime   int(11) DEFAULT '0' NOT NULL,
  found   int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (rec_id),
  KEY qtrack_ipt (ip, qtime)
);

#
# keep "qinfo_nv" index name less than 1000 bytes,
# i.e. less than 333 characters, for 4.1+ with UTF-8.
#
CREATE TABLE qinfo (
       q_id  int(11),
       name  varchar(128),
       value varchar(255),
       KEY qinfo_id (q_id),
       KEY qinfo_nv (name, value(128))
);

CREATE TABLE crossdict (
  url_id int(11) DEFAULT '0' NOT NULL,
  ref_id int(11) DEFAULT '0' NOT NULL,
  word  varchar (32) DEFAULT '0' NOT NULL,
  intag  int(11) DEFAULT '0' NOT NULL,
  KEY url_id (url_id),
  KEY ref_id (ref_id),
  KEY word (word)
);

create table server (
	rec_id		int null primary key,
	enabled		int		not null	default 0,
	url		BLOB		not null,
	tag		TEXT		not null,
	category	int		not null	default 0,
	command		char(1)		not null	default 'S',
	ordre		int		not null	default 0,
	parent		int		not null	default 0,
	weight		float		not null	default 1,
	pop_weight	float		not null	default 0,
	KEY srv_ordre (ordre),
	KEY srv_parent (parent),
	KEY srv_command (command),
	KEY srv_tag(tag(16)),
	KEY srv_category(category),
	UNIQUE srv_url(url(255))
);

CREATE TABLE srvinfo (
       srv_id int  NOT NULL,
       sname  text NOT NULL,
       sval   text NOT NULL,
       KEY srvinfo_id (srv_id)
);

create table links (
       ot	   int(11) not null,
       k	   int(11) not null,
       weight	   float   not null	default 0,
       KEY links_ot (ot),
       KEY links_k (k),
       UNIQUE links_links (ot, k)
);

CREATE TABLE wrdstat (
  word varchar(64) NOT NULL,
  snd varchar(64) NOT NULL,
  cnt int(11) NOT NULL,
  KEY word (word),
  KEY snd (snd)
);

CREATE TABLE qcache (
  id      int not null,
  tm      int not null,
  doclist longblob not null,
  wordinfo text not null,
  primary key qcache_id (id,tm),
  key qcache_tmstamp (tm)
);

-- TODO for qcache
-- id      - CRC sum of qstring variables
-- tmstamp - query date
-- qstring - values affecting query results
-- qextprm - values not affecting query results, like ps and np
-- doclist - result: a list off (url_id, coord) pairs, if
--             actual search was done. NULL if query was taken
--	     from cache.
-- qcache=yes,full,short,no
CREATE TABLE bdicti (
  url_id INT NOT NULL,
  state TINYINT NOT NULL,
  intag00 LONGBLOB NOT NULL, intag01 LONGBLOB NOT NULL,
  intag02 LONGBLOB NOT NULL, intag03 LONGBLOB NOT NULL,
  intag04 LONGBLOB NOT NULL, intag05 LONGBLOB NOT NULL,
  intag06 LONGBLOB NOT NULL, intag07 LONGBLOB NOT NULL,
  intag08 LONGBLOB NOT NULL, intag09 LONGBLOB NOT NULL,
  intag0A LONGBLOB NOT NULL, intag0B LONGBLOB NOT NULL,
  intag0C LONGBLOB NOT NULL, intag0D LONGBLOB NOT NULL,
  intag0E LONGBLOB NOT NULL, intag0F LONGBLOB NOT NULL,
  intag10 LONGBLOB NOT NULL, intag11 LONGBLOB NOT NULL,
  intag12 LONGBLOB NOT NULL, intag13 LONGBLOB NOT NULL,
  intag14 LONGBLOB NOT NULL, intag15 LONGBLOB NOT NULL,
  intag16 LONGBLOB NOT NULL, intag17 LONGBLOB NOT NULL,
  intag18 LONGBLOB NOT NULL, intag19 LONGBLOB NOT NULL,
  intag1A LONGBLOB NOT NULL, intag1B LONGBLOB NOT NULL,
  intag1C LONGBLOB NOT NULL, intag1D LONGBLOB NOT NULL,
  intag1E LONGBLOB NOT NULL, intag1F LONGBLOB NOT NULL,
  KEY(url_id), KEY(state)
) MAX_ROWS=300000000 AVG_ROW_LENGTH=512;
