CREATE SEQUENCE rec_id_GEN;
CREATE SEQUENCE qtrack_GEN;

CREATE TABLE url (
  rec_id          INT                  NOT NULL PRIMARY KEY,
  url             VARCHAR(255) DEFAULT '' NOT NULL,
  status          INT       DEFAULT  0 NOT NULL,
  hops            INT       DEFAULT  0 NOT NULL,
  crc32           INT       DEFAULT -1 NOT NULL,
  docsize         INT       DEFAULT  0 NOT NULL,
  next_index_time INT       DEFAULT  0 NOT NULL,
  last_mod_time   INT       DEFAULT  0 NOT NULL,
  referrer        INT       DEFAULT  0 NOT NULL,
  seed            INT       DEFAULT  0 NOT NULL,
  bad_since_time  INT       DEFAULT  0 NOT NULL,
  site_id	  INT       DEFAULT  0 NOT NULL,
  server_id	  INT       DEFAULT  0 NOT NULL,
  shows           INT       DEFAULT  0 NOT NULL,
  pop_rank	  FLOAT     DEFAULT  0 NOT NULL
);

CREATE INDEX         url_crc             ON url (crc32);
CREATE INDEX         url_seed            ON url (seed);
CREATE UNIQUE  INDEX url_url             ON url (url);
CREATE INDEX         url_referrer        ON url (referrer);
CREATE INDEX         url_bad_since_time  ON url (bad_since_time);
CREATE INDEX         url_next_index_time ON url (next_index_time);
CREATE INDEX         url_server_id       ON url (server_id);



CREATE TABLE urlinfo (
  url_id  INT            NOT NULL,
  sname   VARCHAR(64)    NOT NULL,
  sval    CLOB(128M) NOT NULL
);

CREATE INDEX urlinfo_id ON urlinfo (url_id);




CREATE TABLE dict (
  word     VARCHAR(32) NOT NULL,
  url_id   INT      NOT NULL,
  intag    INT      NOT NULL
);

CREATE   INDEX dict_url_id  ON dict (url_id);
CREATE   INDEX dict_word    ON dict (word);

CREATE TABLE crossdict (
  url_id INT NOT NULL,
  ref_id INT NOT NULL,
  word  varchar(32) NOT NULL,
  intag  INT NOT NULL
);
CREATE INDEX crossdict_url_id ON crossdict (url_id);
CREATE INDEX crossdict_ref_id ON crossdict (ref_id);
CREATE INDEX crossdict_word   ON crossdict (word);


CREATE TABLE bdict (
	word VARCHAR(255) NOT NULL,
	secno INT NOT NULL,
	intag BLOB(128M) NOT NULL
);

CREATE INDEX bdict_word ON bdict (word);

CREATE TABLE qcache (
  id       INT NOT NULL,
  tm       INT NOT NULL,
  doclist  BLOB(128M) NOT NULL,
  wordinfo CLOB(128M) NOT NULL,
  PRIMARY KEY (id,tm)
);
CREATE INDEX key_qcache_tmstamp ON qcache(tm);

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


CREATE TABLE qtrack (
        rec_id INT            NOT NULL  PRIMARY KEY,
        ip     VARCHAR(16)    NOT NULL,
        qwords VARCHAR(15000) NOT NULL,
        qtime  INT            NOT NULL,
        wtime  INT            NOT NULL,
        found  INT            NOT NULL
);
CREATE INDEX qtrack_ipt ON qtrack(ip,qtime);

CREATE TABLE qinfo (
     q_id    INT NOT NULL,
     name    VARCHAR(64),
     sval    VARCHAR(15000)
);
CREATE INDEX qinfo_id ON qinfo (q_id);


create table server (
	rec_id		int not null primary key,
	enabled		int		default 0	not null,
	url		VARCHAR(127)	default ''	not null,
	tag		VARCHAR(63)	default ''	not null,
	category	int		default 0	not null,
	command		char(1)		default 'S'	not null,
	ordre		int		default 0	not null,
	parent		int		default 0	not null,
	weight		float		default 1	not null,
	pop_weight	float		default 0	not null
);

CREATE UNIQUE INDEX srv_url ON server (url);
CREATE INDEX srv_ordre ON server (ordre);
CREATE INDEX srv_parent ON server (parent);
CREATE INDEX srv_command ON server (command);
CREATE INDEX srv_tag      ON SERVER (tag);
CREATE INDEX srv_category ON SERVER (category);


CREATE TABLE srvinfo (
       srv_id INT  NOT NULL,
       sname  VARCHAR(127) NOT NULL,
       sval   VARCHAR(15000) NOT NULL
);

CREATE INDEX srvinfo_id ON srvinfo (srv_id);

create table links (
       ot	   int,
       k	   int,
       weight	   float     	default 0
);

CREATE UNIQUE INDEX links_links ON links (ot, k);
CREATE        INDEX links_ot    ON links (ot);
CREATE        INDEX links_k     ON links (k);

CREATE TABLE wrdstat (
  word varchar(64) NOT NULL,
  snd varchar(64) NOT NULL,
  cnt int NOT NULL
);
CREATE INDEX wrdstat_word ON wrdstat (word);
CREATE INDEX wrdstat_snd  ON wrdstat (snd);
CREATE TABLE bdicti (
  url_id INT NOT NULL,
  state INT NOT NULL,
  intag00 BLOB(1M) NOT NULL, intag01 BLOB(1M) NOT NULL,
  intag02 BLOB(1M) NOT NULL, intag03 BLOB(1M) NOT NULL,
  intag04 BLOB(1M) NOT NULL, intag05 BLOB(1M) NOT NULL,
  intag06 BLOB(1M) NOT NULL, intag07 BLOB(1M) NOT NULL,
  intag08 BLOB(1M) NOT NULL, intag09 BLOB(1M) NOT NULL,
  intag0A BLOB(1M) NOT NULL, intag0B BLOB(1M) NOT NULL,
  intag0C BLOB(1M) NOT NULL, intag0D BLOB(1M) NOT NULL,
  intag0E BLOB(1M) NOT NULL, intag0F BLOB(1M) NOT NULL,
  intag10 BLOB(1M) NOT NULL, intag11 BLOB(1M) NOT NULL,
  intag12 BLOB(1M) NOT NULL, intag13 BLOB(1M) NOT NULL,
  intag14 BLOB(1M) NOT NULL, intag15 BLOB(1M) NOT NULL,
  intag16 BLOB(1M) NOT NULL, intag17 BLOB(1M) NOT NULL,
  intag18 BLOB(1M) NOT NULL, intag19 BLOB(1M) NOT NULL,
  intag1A BLOB(1M) NOT NULL, intag1B BLOB(1M) NOT NULL,
  intag1C BLOB(1M) NOT NULL, intag1D BLOB(1M) NOT NULL,
  intag1E BLOB(1M) NOT NULL, intag1F BLOB(1M) NOT NULL
);
CREATE INDEX bdicti_url_id ON bdicti (url_id);
CREATE INDEX bdicti_state ON bdicti (state);
