set quoted_identifier on;

--
-- In Sybase we can't use TEXT type like in PgSQL or MySQL:
--
-- The text, ntext, and image data types cannot be compared or sorted, 
-- except when using IS NULL or LIKE operator.
--
-- We use VARCHAR() instead.
--

CREATE TABLE "dict" (
	"url_id" "int" NOT NULL ,
	"word"    varchar (32) NOT NULL ,
	"intag" "int" NOT NULL 
);
CREATE  INDEX "dict_url_id" ON "dict" ("url_id");
CREATE  INDEX "dict_word_url_id" ON "dict" ("word", "url_id");

CREATE TABLE bdict (
  word  VARCHAR(255) NOT NULL,
  secno INT NOT NULL,
  intag IMAGE NOT NULL
);
CREATE INDEX bdict_word ON bdict (word);


CREATE TABLE "url" (
        "rec_id"          "int" IDENTITY 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" NULL,
        "site_id"         "int" NULL,
        "server_id"       "int" NULL,
        "shows"           "int" DEFAULT 0 NOT NULL,
        "pop_rank"        float DEFAULT 0 NOT NULL,
        "url"             varchar(600)  NOT NULL,
        CONSTRAINT "UQ_url_url" UNIQUE ("url")
);

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 "urlinfo" (
       "url_id" "int"         NOT NULL,
       "sname"  VARCHAR(96)   NOT NULL,
       "sval"   TEXT          NOT NULL
);
CREATE INDEX "urlinfo_id" ON "urlinfo" (url_id);


CREATE TABLE "server" (
        "rec_id"     int           not null primary key,
        "enabled"    int           default 0 not null ,
        "url"        varchar(4096) default '' not null ,
        "tag"        varchar(96)   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 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(96)   NOT NULL,
       sval   varchar(4096) NOT NULL
);
CREATE INDEX srvinfo_id ON srvinfo (srv_id);


CREATE TABLE "links" (
        "ot"     int   not null,
        "k"      int   not null,
        "weight" float default 0 not null
);
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" "int" IDENTITY NOT NULL 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" "int" IDENTITY NOT NULL PRIMARY KEY,
        "ip"     varchar(16)         NOT NULL,
        "qwords" varchar(4096)       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,
       "name"  text,
       "value" text
);
CREATE INDEX qinfo_id ON qinfo (q_id);


CREATE TABLE crossdict (
  url_id int DEFAULT '0' NOT NULL,
  ref_id int DEFAULT '0' NOT NULL,
  intag  int 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);
