CREATE TRUSTED LANGUAGE plpgsql HANDLER plpgsql_call_handler;

CREATE SEQUENCE next_url_id;

CREATE TABLE "url" (
	"rec_id"          int4 DEFAULT nextval('next_url_id') PRIMARY KEY,
	"status"          int4 NOT NULL DEFAULT 0,
	"docsize"         int4 NOT NULL DEFAULT 0,
	"next_index_time" int4 NOT NULL,
	"last_mod_time"   int4,
	"referrer"        int4 NOT NULL DEFAULT 0,
	"hops"            int4 NOT NULL DEFAULT 0,
	"crc32"           int4 NOT NULL DEFAULT 0,
	"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 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 SEQUENCE next_server_id;

CREATE TABLE "server" (
        "rec_id" int4 DEFAULT nextval('next_server_id') PRIMARY KEY,
        "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"		int		not null	default 0,
	"weight"		float		not null	default 1,
	"pop_weight"		float		not null	default 0
);

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 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;
	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 SEQUENCE next_cat_id;

CREATE TABLE "categories" (
	"rec_id" int4 DEFAULT nextval('next_cat_id') 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" serial,
	"ip"     text NOT NULL,
	"qwords" text NOT NULL,
	"qtime"  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);
