CREATE SEQUENCE rec_id_GEN;
CREATE SEQUENCE qtrack_GEN;

CREATE TABLE url (
  rec_id          INT          NOT NULL PRIMARY KEY,
  status          INT          DEFAULT  0 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,
  hops            INT          DEFAULT  0 NOT NULL,
  crc32           INT          DEFAULT -1 NOT NULL,
  seed            INT          DEFAULT  0 NOT NULL,
  bad_since_time  INT          DEFAULT  0 NOT NULL,
  server_id	  INT          DEFAULT  0 NOT NULL,
  shows           INT          DEFAULT  0 NOT NULL,
  url             VARCHAR(255) DEFAULT '' NOT NULL
);
CREATE UNIQUE INDEX url_url      ON url (url);
CREATE INDEX url_crc             ON url (crc32);
CREATE INDEX url_seed            ON url (seed);
CREATE INDEX url_referrer        ON url (referrer);
CREATE INDEX url_next_index_time ON url (next_index_time);
CREATE INDEX url_bad_since_time  ON url (bad_since_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 cachedcopy (
  url_id  INT        NOT NULL,
  ts      INT        NOT NULL,
  content BLOB(128M) NOT NULL
);
CREATE INDEX cachedcopy_id ON cachedcopy (url_id);
CREATE INDEX cachedcopy_ts ON cachedcopy (ts);


CREATE TABLE dict (
  word     VARCHAR(32) NOT NULL,
  url_id   INT         NOT NULL,
  coord    INT         NOT NULL
);
CREATE INDEX dict_word    ON dict (word);
CREATE INDEX dict_url_id  ON dict (url_id);


CREATE TABLE bdict (
  word   VARCHAR(255) NOT NULL,
  secno  INT          NOT NULL,
  coords BLOB(128M)   NOT NULL
);
CREATE INDEX bdict_word ON bdict (word);


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,
  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 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 (
  url_id   int           not null default 0,
  seed     int           not null default 0,
  url      varchar(1024) not null default '',
  src      varchar(10)   not null default '',
  rel      varchar(32)   not null default '',
  linktext varchar(1024) not null default ''
);
CREATE INDEX key_links_url_id ON links (url_id);
CREATE INDEX key_links_seed ON links (seed);

CREATE TABLE redirect (
  url_id int           not null default 0,
  seed   int           not null default 0,
  url    varchar(1024) not null default ''
);
CREATE INDEX key_redirect_url_id ON redirect (url_id);
CREATE INDEX key_redirect_seed   ON redirect (seed);


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,
  nfound INT            NOT NULL
);
CREATE INDEX qtrack_ipt ON qtrack(ip,qtime);


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

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