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 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);