CREATE TABLE dict (
       word                 VARCHAR2(32) NOT NULL,
       url_id               NUMBER(12) NOT NULL,
       coord                NUMBER(12) NOT NULL
) PCTFREE 5 PCTUSED 95;

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


CREATE TABLE bdict (
       word                 VARCHAR2(255) NOT NULL,
       secno                NUMBER(12) NOT NULL,
       coords                BLOB NOT NULL
) PCTFREE 5 PCTUSED 95;

CREATE INDEX bdict_word ON bdict (word);

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


CREATE TABLE url (
       rec_id               NUMBER(12) NOT NULL,
       status               NUMBER(6) DEFAULT 0 NOT NULL,
       docsize              NUMBER(12) DEFAULT 0 NOT NULL,
       next_index_time      NUMBER(12) NULL,
       last_mod_time        NUMBER(12) DEFAULT 0 NOT NULL,
       referrer             NUMBER(12) DEFAULT 0 NOT NULL,
       hops                 NUMBER(6) DEFAULT 0 NOT NULL,
       crc32                NUMBER(12) DEFAULT -1 NOT NULL,
       seed                 NUMBER(6) DEFAULT 0 NOT NULL,
       bad_since_time       NUMBER(12) NULL,
       server_id	    NUMBER(12),
       shows		    NUMBER(12) DEFAULT 0 NOT NULL,
       url                  VARCHAR2(2048) NOT NULL
)
       PCTFREE 5
       PCTUSED 95;

CREATE UNIQUE INDEX url_url ON url (url ASC);
CREATE INDEX url_rec_id ON url (rec_id);
CREATE INDEX url_crc32 ON url (crc32 ASC);
CREATE INDEX url_next_index_time_ind ON url (next_index_time);
CREATE INDEX url_referrer ON url (referrer);
CREATE INDEX url_server_id  ON url (server_id);

CREATE SEQUENCE next_url_id INCREMENT BY 1 START WITH 1;

CREATE TABLE urlinfo (
       url_id NUMBER(38) NOT NULL,
       sname  VARCHAR2(64) NOT NULL,
       sval   CLOB
);
CREATE INDEX urlinfo_id_ind ON urlinfo (url_id);


CREATE TABLE cachedcopy (
       url_id  NUMBER(38) NOT NULL,
       ts      NUMBER(38) NOT NULL,
       content BLOB
);
CREATE INDEX cachedcopy_id ON cachedcopy (url_id);
CREATE INDEX cachedcopy_ts ON cachedcopy (ts);


CREATE TABLE server (
	rec_id          NUMBER(12)      DEFAULT 0   NOT NULL, 
	enabled		NUMBER(6)	DEFAULT 0   NOT NULL,
	url		VARCHAR2(2048)	DEFAULT ''  NOT NULL,
	period		NUMBER(12)	DEFAULT 604800 NOT NULL,
	tag		VARCHAR2(16)	DEFAULT '',
	command		CHAR(1)		DEFAULT 'S' NOT NULL,
	ordre		NUMBER(6)	DEFAULT 0   NOT NULL,
	parent		NUMBER(12)	DEFAULT 0   not null,
	weight		float		DEFAULT 1   not null,
	pop_weight	float		DEFAULT 0   not null
);

CREATE SEQUENCE server_rec_id_seq INCREMENT BY 1 START WITH 1;

ALTER TABLE server ADD CONSTRAINT server_pk PRIMARY KEY (rec_id);
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(96)   NOT NULL,
     sval   varchar(1024) NOT NULL
);
CREATE INDEX srvinfo_id ON srvinfo (srv_id);

CREATE TABLE links (
  url_id NUMBER(12) not null,
  seed NUMBER(6) not null,
  url VARCHAR(1024) NOT NULL,
  src varchar(10) not null,
  rel varchar(32),
  linktext VARCHAR(1024)
);
CREATE INDEX links_url_id ON links (url_id);
CREATE INDEX links_seed ON links (seed);

CREATE TABLE redirect (
  url_id NUMBER(12) not null,
  seed NUMBER(6) not null,
  url VARCHAR(1024) NOT NULL
);
CREATE INDEX redirect_url_id ON redirect (url_id);
CREATE INDEX redirect_seed   ON redirect (seed);

CREATE SEQUENCE qtrack_seq INCREMENT BY 1 START WITH 1;

CREATE TABLE qtrack (
  rec_id INT            NOT NULL  PRIMARY KEY,
  ip     VARCHAR(16),
  qwords VARCHAR(2048)  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(2048)
);
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);