set quoted_identifier on;

--
-- In MS SQL  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 qcache (
  id       INT NOT NULL,
  tm       INT NOT NULL,
  doclist  IMAGE NOT NULL,
  wordinfo TEXT  NOT NULL,
  PRIMARY KEY (id,tm)
);
CREATE INDEX key_qcache_tmstamp ON qcache(tm);

CREATE TABLE "url" (
        "rec_id"          "int" IDENTITY(1,1) NOT NULL PRIMARY KEY,
        "status"          "int" NOT NULL DEFAULT 0,
        "docsize"         "int" NOT NULL DEFAULT 0,
        "next_index_time" "int" NOT NULL,
        "last_mod_time"   "int" NOT NULL DEFAULT 0,
        "referrer"        "int" NOT NULL DEFAULT 0,
        "hops"            "int" NOT NULL DEFAULT 0,
        "crc32"           "int" NOT NULL DEFAULT -1,
        "seed"            "int" NOT NULL DEFAULT 0,
        "bad_since_time"  "int"     NULL,
        "site_id"         "int"     NULL,
        "server_id"       "int"     NULL,
        "shows"           "int" NOT NULL DEFAULT 0,
        "pop_rank"        float NOT NULL DEFAULT 0,
        "url"             varchar(4096)  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           not null default 0,
        "url"        varchar(4096) not null default '',
        "tag"        varchar(96)   not null default '',
        "category"   int           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 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 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" "int" IDENTITY(1,1) 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(1,1) 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);
CREATE TABLE bdicti (
  url_id INT NOT NULL,
  state TINYINT NOT NULL,
  intag00 IMAGE NOT NULL, intag01 IMAGE NOT NULL,
  intag02 IMAGE NOT NULL, intag03 IMAGE NOT NULL,
  intag04 IMAGE NOT NULL, intag05 IMAGE NOT NULL,
  intag06 IMAGE NOT NULL, intag07 IMAGE NOT NULL,
  intag08 IMAGE NOT NULL, intag09 IMAGE NOT NULL,
  intag0A IMAGE NOT NULL, intag0B IMAGE NOT NULL,
  intag0C IMAGE NOT NULL, intag0D IMAGE NOT NULL,
  intag0E IMAGE NOT NULL, intag0F IMAGE NOT NULL,
  intag10 IMAGE NOT NULL, intag11 IMAGE NOT NULL,
  intag12 IMAGE NOT NULL, intag13 IMAGE NOT NULL,
  intag14 IMAGE NOT NULL, intag15 IMAGE NOT NULL,
  intag16 IMAGE NOT NULL, intag17 IMAGE NOT NULL,
  intag18 IMAGE NOT NULL, intag19 IMAGE NOT NULL,
  intag1A IMAGE NOT NULL, intag1B IMAGE NOT NULL,
  intag1C IMAGE NOT NULL, intag1D IMAGE NOT NULL,
  intag1E IMAGE NOT NULL, intag1F IMAGE NOT NULL);
CREATE INDEX bdicti_url_id ON bdicti (url_id);
CREATE INDEX bdicti_state ON bdicti (state);
