DROP TABLE dbo.categories;
CREATE TABLE dbo.categories (
    rec_id    int  IDENTITY (1, 1) NOT NULL,
    path      CHAR(10) NOT NULL DEFAULT (''),
    link      CHAR(10) NOT NULL DEFAULT (''),
    name      CHAR(64) NOT NULL DEFAULT (''),
    CONSTRAINT categories_rec_id PRIMARY KEY  CLUSTERED 
    (
        rec_id
    )
);

DROP TABLE dbo.ndict;
CREATE TABLE dbo.ndict (
    url_id      int NOT NULL DEFAULT (0),
    word_id     int NOT NULL DEFAULT (0),
    intag       int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict_url_ind ON dbo.ndict (url_id);
CREATE  INDEX ndict_word_ind ON dbo.ndict (word_id);
 
DROP TABLE dbo.ndict2;
DROP TABLE dbo.ndict3;
DROP TABLE dbo.ndict4;
DROP TABLE dbo.ndict5;
DROP TABLE dbo.ndict6;
DROP TABLE dbo.ndict7;
DROP TABLE dbo.ndict8;
DROP TABLE dbo.ndict9;
DROP TABLE dbo.ndict10;
DROP TABLE dbo.ndict11;
DROP TABLE dbo.ndict12;
DROP TABLE dbo.ndict16;
DROP TABLE dbo.ndict32;

CREATE TABLE dbo.ndict2 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict2_url_ind ON dbo.ndict2 (url_id);
CREATE  INDEX ndict2_word_ind ON dbo.ndict2 (word_id);

CREATE TABLE dbo.ndict3 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict3_url_ind ON dbo.ndict3 (url_id);
CREATE  INDEX ndict3_word_ind ON dbo.ndict3 (word_id);

CREATE TABLE dbo.ndict4 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict4_url_ind ON dbo.ndict4 (url_id);
CREATE  INDEX ndict4_word_ind ON dbo.ndict4 (word_id);

CREATE TABLE dbo.ndict5 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict5_url_ind ON dbo.ndict5 (url_id);
CREATE  INDEX ndict5_word_ind ON dbo.ndict5 (word_id);

CREATE TABLE dbo.ndict6 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict6_url_ind ON dbo.ndict6 (url_id);
CREATE  INDEX ndict6_word_ind ON dbo.ndict6 (word_id);

CREATE TABLE dbo.ndict7 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict7_url_ind ON dbo.ndict7 (url_id);
CREATE  INDEX ndict7_word_ind ON dbo.ndict7 (word_id);

CREATE TABLE dbo.ndict8 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict8_url_ind ON dbo.ndict8 (url_id);
CREATE  INDEX ndict8_word_ind ON dbo.ndict8 (word_id);

CREATE TABLE dbo.ndict9 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict9_url_ind ON dbo.ndict9 (url_id);
CREATE  INDEX ndict9_word_ind ON dbo.ndict9 (word_id);

CREATE TABLE dbo.ndict10 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict10_url_ind ON dbo.ndict10 (url_id);
CREATE  INDEX ndict10_word_ind ON dbo.ndict10 (word_id);

CREATE TABLE dbo.ndict11 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict11_url_ind ON dbo.ndict11 (url_id);
CREATE  INDEX ndict11_word_ind ON dbo.ndict11 (word_id);

CREATE TABLE dbo.ndict12 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict12_url_ind ON dbo.ndict12 (url_id);
CREATE  INDEX ndict12_word_ind ON dbo.ndict12 (word_id);

CREATE TABLE dbo.ndict16 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict16_url_ind ON dbo.ndict16 (url_id);
CREATE  INDEX ndict16_word_ind ON dbo.ndict16 (word_id);

CREATE TABLE dbo.ndict32 (
    url_id    int NOT NULL DEFAULT (0),
    word_id   int NOT NULL DEFAULT (0),
    intag     int NOT NULL DEFAULT (0)
);

CREATE  INDEX ndict32_url_ind ON dbo.ndict32 (url_id);
CREATE  INDEX ndict32_word_ind ON dbo.ndict32 (word_id);

DROP TABLE dbo.url;
DROP TABLE dbo.dict;
DROP TABLE dbo.robots;
DROP TABLE dbo.stopword;

CREATE TABLE dbo.dict (
    word    CHAR (32) NOT NULL,
    url_id  int NOT NULL,
    intag  int NOT NULL
    CONSTRAINT PK_dict PRIMARY KEY  CLUSTERED 
    (
        word, url_id
    ),
);

CREATE TABLE dbo.robots (
    hostinfo char (127) NOT NULL,
    path char (127) NOT NULL
);


CREATE TABLE dbo.stopword (
    word char (32) NOT NULL, 
    lang char (2) NOT NULL 
    CONSTRAINT PK_stopword PRIMARY KEY  CLUSTERED 
    (
        word, lang
    ),
);

CREATE TABLE dbo.url (
    rec_id        int IDENTITY (1, 1) NOT NULL,
    status        int NOT NULL,
    url           char (128) NOT NULL,
    content_type  char (48) NULL,
    title         char (128) NULL,
    txt           char (255) NULL,
    docsize       int,
    last_index_time  int NOT NULL,
    next_index_time  int NOT NULL,
    last_mod_time int,
    referrer      int,
    tag           char (10) NULL,
    hops          int,
    category      char (10) NULL,
    keywords      char (255) NULL,
    description   char (100) NULL,
    crc32         int NOT NULL,
    lang          char (2) NULL,
    CONSTRAINT PK_url PRIMARY KEY  CLUSTERED 
    (
        rec_id
    ),
    CONSTRAINT UQ_url UNIQUE  NONCLUSTERED 
    (
        url
    )
);

CREATE  INDEX url_crc ON dbo.url (crc32);

DROP TABLE dbo.crossdict;
CREATE TABLE dbo.crossdict (
    url_id int NOT NULL DEFAULT (0),
    ref_id int NOT NULL DEFAULT (0),
    word   char (32) NOT NULL DEFAULT (''),
    intag  int NOT NULL DEFAULT (0)
);

CREATE  INDEX cd_url_ind ON dbo.crossdict (url_id);
CREATE  INDEX cd_ref_ind ON dbo.crossdict (ref_id);
CREATE  INDEX cd_word_ind ON dbo.crossdict (word);

DROP TABLE dbo.ncrossdict;
CREATE TABLE dbo.ncrossdict (
    url_id   int NOT NULL DEFAULT (0),
    ref_id   int NOT NULL DEFAULT (0),
    word_id  int NOT NULL DEFAULT (0),
    intag    int NOT NULL DEFAULT (0)
);

CREATE  INDEX ncd_url_ind ON dbo.ncrossdict (url_id);
CREATE  INDEX ncd_ref_ind ON dbo.ncrossdict (ref_id);
CREATE  INDEX ncd_word_ind ON dbo.ncrossdict (word_id);

DROP TABLE dbo.affix;
CREATE TABLE dbo.affix (
    flag char (1) NOT NULL DEFAULT (''),
    type char (1) NOT NULL DEFAULT (''),
    lang char (3) NOT NULL DEFAULT (''),
    mask char (32) NOT NULL DEFAULT (''),
    find char (32) NOT NULL DEFAULT (''),
    repl char (32) NOT NULL DEFAULT ('')
);

CREATE INDEX affix_flag_ind ON dbo.affix (flag);

DROP TABLE dbo.spell;
CREATE TABLE dbo.spell (
  word char (64) NOT NULL DEFAULT '',
  flag char (32) NOT NULL DEFAULT '',
  lang char (3) NOT NULL DEFAULT ''
);

CREATE INDEX spell_word_ind ON dbo.spell (word);

DROP TABLE dbo.dict2;
DROP TABLE dbo.dict3;
DROP TABLE dbo.dict4;
DROP TABLE dbo.dict5;
DROP TABLE dbo.dict6;
DROP TABLE dbo.dict7;
DROP TABLE dbo.dict8;
DROP TABLE dbo.dict9;
DROP TABLE dbo.dict10;
DROP TABLE dbo.dict11;
DROP TABLE dbo.dict12;
DROP TABLE dbo.dict16;
DROP TABLE dbo.dict32;

CREATE TABLE dbo.dict2 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict2_url_ind ON dbo.dict2 (url_id);
CREATE INDEX dict2_word_ind ON dbo.dict2 (word);

CREATE TABLE dbo.dict3 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict3_url_ind ON dbo.dict3 (url_id);
CREATE INDEX dict3_word_ind ON dbo.dict3 (word);

CREATE TABLE dbo.dict4 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict4_url_ind ON dbo.dict4 (url_id);
CREATE INDEX dict4_word_ind ON dbo.dict4 (word);

CREATE TABLE dbo.dict5 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict5_url_ind ON dbo.dict5 (url_id);
CREATE INDEX dict5_word_ind ON dbo.dict5 (word);

CREATE TABLE dbo.dict6 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict6_url_ind ON dbo.dict6 (url_id);
CREATE INDEX dict6_word_ind ON dbo.dict6 (word);

CREATE TABLE dbo.dict7 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict7_url_ind ON dbo.dict7 (url_id);
CREATE INDEX dict7_word_ind ON dbo.dict7 (word);

CREATE TABLE dbo.dict8 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict8_url_ind ON dbo.dict8 (url_id);
CREATE INDEX dict8_word_ind ON dbo.dict8 (word);

CREATE TABLE dbo.dict9 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict9_url_ind ON dbo.dict9 (url_id);
CREATE INDEX dict9_word_ind ON dbo.dict9 (word);

CREATE TABLE dbo.dict10 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict10_url_ind ON dbo.dict10 (url_id);
CREATE INDEX dict10_word_ind ON dbo.dict10 (word);

CREATE TABLE dbo.dict11 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict11_url_ind ON dbo.dict11 (url_id);
CREATE INDEX dict11_word_ind ON dbo.dict11 (word);

CREATE TABLE dbo.dict12 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict12_url_ind ON dbo.dict12 (url_id);
CREATE INDEX dict12_word_ind ON dbo.dict12 (word);

CREATE TABLE dbo.dict16 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict16_url_ind ON dbo.dict16 (url_id);
CREATE INDEX dict16_word_ind ON dbo.dict16 (word);

CREATE TABLE dbo.dict32 (
    word     char (2) NOT NULL,
    url_id   int NOT NULL,
    intag    int NOT NULL
);

CREATE INDEX dict32_url_ind ON dbo.dict32 (url_id);
CREATE INDEX dict32_word_ind ON dbo.dict32 (word);

DROP TABLE dbo.qtrack;
CREATE TABLE dbo.qtrack (
    qwords  char (63) NULL DEFAULT '',
    qtime   int NOT NULL DEFAULT (0),
    found   int NOT NULL DEFAULT (0)
);
