CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS '$libdir/plpgsql' LANGUAGE C;
CREATE TRUSTED LANGUAGE plpgsql HANDLER plpgsql_call_handler;

CREATE TABLE "url" (
	"rec_id"          serial primary key,
	"status"          int4 NOT NULL DEFAULT 0,
	"docsize"         int4 NOT NULL DEFAULT 0,
	"next_index_time" int4 NOT NULL,
	"last_mod_time"   int4 NOT NULL DEFAULT 0,
	"referrer"        int4 NOT NULL DEFAULT 0,
	"hops"            int4 NOT NULL DEFAULT 0,
	"crc32"           int4 NOT NULL DEFAULT -1,
	"seed"            int NOT NULL DEFAULT 0,
	"bad_since_time"  int4,
	"site_id"	  int4,
	"server_id"	  int4,
	"shows"           int4 NOT NULL DEFAULT 0,
	"pop_rank"	  float NOT NULL DEFAULT 0,
	"url"             text NOT NULL
);

CREATE TABLE "urlinfo" (
       url_id int4 NOT NULL,
       sname  text NOT NULL,
       sval   text NOT NULL
);

CREATE INDEX urlinfo_id ON urlinfo (url_id);

CREATE TABLE "dict" (
	"url_id" int4 NOT NULL,
	"word"   text NOT NULL,
	"intag"  int4 NOT NULL
);

CREATE INDEX "dict_word" ON "dict" ( "word"   );
CREATE INDEX "dict_url"  ON "dict" ( "url_id" );
CREATE INDEX "dict_word_url_id" ON "dict" ( "word", "url_id" );

CREATE TABLE "bdict" (
	word varchar(255) NOT NULL,
	secno int2 NOT NULL,
	intag bytea NOT NULL
);

CREATE INDEX "bdict_word" ON "bdict" ("word");

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_status" ON "url" ( "status" );
CREATE INDEX "url_bad_since_time" ON "url" ( "bad_since_time" );
CREATE INDEX "url_hops" ON "url" ( "hops" );
CREATE INDEX "url_siteid" ON "url" ( "site_id" );
CREATE INDEX "url_server_id" ON url ( "server_id" );


CREATE TABLE "server" (
        "rec_id"		int4		not null,
        "enabled"               int             not null        default 0,
        "url"                   text            not null        default '',
        "tag"                   text            not null        default '',
        "category"              int4            not null        default 0,
	"command"		char(1)		not null	default 'S',
	"ordre"			int		not null	default 0,
	"parent"		int4		not null	default 0,
	"weight"		float		not null	default 1,
	"pop_weight"		float		not null	default 0
);

ALTER TABLE ONLY server ADD CONSTRAINT server_pkey PRIMARY KEY (rec_id); 
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 int4 NOT NULL,
       sname  text NOT NULL,
       sval   text NOT NULL
);

CREATE INDEX srvinfo_id ON srvinfo (srv_id);


CREATE FUNCTION clean_srvinfo() RETURNS trigger 
AS 'begin DELETE FROM srvinfo WHERE srv_id=old.rec_id; UPDATE url SET next_index_time=0 WHERE server_id=old.rec_id OR site_id=old.rec_id; return NULL; end;'
LANGUAGE plpgsql;


CREATE TRIGGER srvdel AFTER DELETE ON server FOR EACH ROW EXECUTE PROCEDURE clean_srvinfo();


CREATE TABLE "links" (
	"ot"	 int4 not null,
	"k"	 int4 not null,
	"weight" float not null 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 "categories" (
	"rec_id" serial primary key,
	"path" varchar(10) DEFAULT '' NOT NULL,
	"link" varchar(10) DEFAULT '' NOT NULL,
	"name" varchar(64) DEFAULT '' NOT NULL
);


CREATE TABLE "qtrack" (
        "rec_id" serial4 primary key,
	"ip"     text NOT NULL,
	"qwords" text NOT NULL,
	"qtime"  int4 NOT NULL,
	"wtime"  int4 NOT NULL,
	"found"  int4 NOT NULL
);
CREATE INDEX qtrack_ipt ON qtrack(ip,qtime);

CREATE TABLE "qinfo" (
       "q_id"  int4,
       "name"  text,
       "value" text
);
CREATE INDEX qinfo_id ON qinfo (q_id);
CREATE INDEX qinfo_nv ON qinfo (name, value);

CREATE TABLE crossdict (
  url_id int4 DEFAULT '0' NOT NULL,
  ref_id int4 DEFAULT '0' NOT NULL,
  intag  int4 DEFAULT '0' NOT NULL,
  word   varchar(32) DEFAULT '0' 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 INDEX crossdict_word_url_id ON crossdict (word, url_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 bdicti (
  url_id INT NOT NULL,
  state INT NOT NULL,
  intag00 bytea NOT NULL, intag01 bytea NOT NULL,
  intag02 bytea NOT NULL, intag03 bytea NOT NULL,
  intag04 bytea NOT NULL, intag05 bytea NOT NULL,
  intag06 bytea NOT NULL, intag07 bytea NOT NULL,
  intag08 bytea NOT NULL, intag09 bytea NOT NULL,
  intag0A bytea NOT NULL, intag0B bytea NOT NULL,
  intag0C bytea NOT NULL, intag0D bytea NOT NULL,
  intag0E bytea NOT NULL, intag0F bytea NOT NULL,
  intag10 bytea NOT NULL, intag11 bytea NOT NULL,
  intag12 bytea NOT NULL, intag13 bytea NOT NULL,
  intag14 bytea NOT NULL, intag15 bytea NOT NULL,
  intag16 bytea NOT NULL, intag17 bytea NOT NULL,
  intag18 bytea NOT NULL, intag19 bytea NOT NULL,
  intag1A bytea NOT NULL, intag1B bytea NOT NULL,
  intag1C bytea NOT NULL, intag1D bytea NOT NULL,
  intag1E bytea NOT NULL, intag1F bytea NOT NULL
);
CREATE INDEX bdicti_url_id ON bdicti (url_id);
CREATE INDEX bdicti_state ON bdicti (state);
