
-- list of search words
CREATE TABLE IF NOT EXISTS SearchWords (
    WordId          INT NOT NULL AUTO_INCREMENT,
    WordText        TEXT,
    INDEX           (WordText(16)),
    INDEX           (WordId)
);

-- list of search word stems
CREATE TABLE IF NOT EXISTS SearchStems (
    WordId          INT NOT NULL AUTO_INCREMENT,
    WordText        TEXT,
    INDEX           (WordText(16)),
    INDEX           (WordId)
);

-- lookup table for counts of word occurences
CREATE TABLE IF NOT EXISTS SearchWordCounts (
    WordId          INT NOT NULL,
    ItemId          INT NOT NULL,
    FieldId         SMALLINT NOT NULL,
    Count           SMALLINT,
    INDEX           (WordId, FieldId)
);

-- list of search fields
CREATE TABLE IF NOT EXISTS SearchFields (
    FieldId             SMALLINT NOT NULL AUTO_INCREMENT,
    FieldName           TEXT,
    FieldLocation       INT,
    INDEX               (FieldId),
    INDEX               (FieldName(20))
);

-- list of link between synonyms
CREATE TABLE IF NOT EXISTS SearchWordSynonyms (
    WordIdA             INT NOT NULL,
    WordIdB             INT NOT NULL,
    INDEX               (WordIdA, WordIdB)
);


