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" char (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 "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,
        "since"           "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 TABLE "urlinfo" (
       "url_id" "int"         NOT NULL,
       "sname"  VARCHAR(96)   NOT NULL,
       "sval"   VARCHAR(4096) 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 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,
	"valid"  char(1) not null default 't'
);
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 INDEX cat_path ON categories (path);
CREATE INDEX cat_link ON categories (link);


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 dict2 (
  url_id int        NOT NULL,
  word char(2)      NOT NULL,
  intag int         NOT NULL
);

CREATE TABLE dict3 (
  url_id int        NOT NULL,
  word char(3)      NOT NULL,
  intag int         NOT NULL
);


CREATE TABLE dict4 (
  url_id int        NOT NULL,
  word char(4)      NOT NULL,
  intag int         NOT NULL
);

CREATE TABLE dict5 (
  url_id int        NOT NULL,
  word char(5)      NOT NULL,
  intag int         NOT NULL
);


CREATE TABLE dict6 (
  url_id int        NOT NULL,
  word char(6)      NOT NULL,
  intag int         NOT NULL
);


CREATE TABLE dict7 (
  url_id int        NOT NULL,
  word char(7)      NOT NULL,
  intag int         NOT NULL
);


CREATE TABLE dict8 (
  url_id int        NOT NULL,
  word char(8)      NOT NULL,
  intag int         NOT NULL
);


CREATE TABLE dict9 (
  url_id int        NOT NULL,
  word char(9)      NOT NULL,
  intag int         NOT NULL
);


CREATE TABLE dict10 (
  url_id int        NOT NULL,
  word char(10)     NOT NULL,
  intag int         NOT NULL
);


CREATE TABLE dict11 (
  url_id int        NOT NULL,
  word char(11)     NOT NULL,
  intag int         NOT NULL
);



CREATE TABLE dict12 (
  url_id int        NOT NULL,
  word char(12)     NOT NULL,
  intag int         NOT NULL
);



CREATE TABLE dict16 (
  url_id int        NOT NULL,
  word char(16)     NOT NULL,
  intag int         NOT NULL
);


CREATE TABLE dict32 (
  url_id int        NOT NULL,
  word varchar(32)  NOT NULL,
  intag int         NOT NULL
);



CREATE  INDEX url_id2 ON dict2 (url_id);
CREATE  INDEX word2   ON dict2 (word);

CREATE  INDEX url_id3 ON dict3 (url_id);
CREATE  INDEX word3   ON dict3 (word);

CREATE  INDEX url_id4 ON dict4 (url_id);
CREATE  INDEX word4   ON dict4 (word);

CREATE  INDEX url_id5 ON dict5 (url_id);
CREATE  INDEX word5   ON dict5 (word);

CREATE  INDEX url_id6 ON dict6 (url_id);
CREATE  INDEX word6   ON dict6 (word);

CREATE  INDEX url_id7 ON dict7 (url_id);
CREATE  INDEX word7   ON dict7 (word);

CREATE  INDEX url_id8 ON dict8 (url_id);
CREATE  INDEX word8   ON dict8 (word);

CREATE  INDEX url_id9 ON dict9 (url_id);
CREATE  INDEX word9   ON dict9 (word);

CREATE  INDEX url_id10 ON dict10 (url_id);
CREATE  INDEX word10   ON dict10 (word);

CREATE  INDEX url_id11 ON dict11 (url_id);
CREATE  INDEX word11   ON dict11 (word);

CREATE  INDEX url_id12 ON dict12 (url_id);
CREATE  INDEX word12   ON dict12 (word);

CREATE  INDEX url_id16 ON dict16 (url_id);
CREATE  INDEX word16   ON dict16 (word);

CREATE  INDEX url_id32 ON dict32 (url_id);
CREATE  INDEX word32   ON dict32 (word);

CREATE INDEX dict2_word_url_id ON dict2 (word, url_id);
CREATE INDEX dict3_word_url_id ON dict3 (word, url_id);
CREATE INDEX dict4_word_url_id ON dict4 (word, url_id);
CREATE INDEX dict5_word_url_id ON dict5 (word, url_id);
CREATE INDEX dict6_word_url_id ON dict6 (word, url_id);
CREATE INDEX dict7_word_url_id ON dict7 (word, url_id);
CREATE INDEX dict8_word_url_id ON dict8 (word, url_id);
CREATE INDEX dict9_word_url_id ON dict9 (word, url_id);
CREATE INDEX dict10_word_url_id ON dict10 (word, url_id);
CREATE INDEX dict11_word_url_id ON dict11 (word, url_id);
CREATE INDEX dict12_word_url_id ON dict12 (word, url_id);
CREATE INDEX dict16_word_url_id ON dict16 (word, url_id);
CREATE INDEX dict32_word_url_id ON dict32 (word, url_id);

