-- $Id$

DROP TABLE word;
DROP TABLE file;
DROP TABLE package;
DROP TABLE provider_record;
DROP TABLE crawler;

CREATE TABLE word (
    text VARCHAR(40) NOT NULL,
    type ENUM ('word', 'subword') DEFAULT 'word' NOT NULL,
    crawler_id TINYINT UNSIGNED NOT NULL,
    file_id INTEGER UNSIGNED NOT NULL,

    cnt INTEGER UNSIGNED DEFAULT 0 NOT NULL,

    PRIMARY KEY (text, type, crawler_id, file_id)
) ENGINE=InnoDB;

CREATE TABLE file (
    crawler_id TINYINT UNSIGNED NOT NULL,
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

    package_id INTEGER UNSIGNED NOT NULL,
    path VARBINARY(255) NOT NULL,
    language_id TINYINT UNSIGNED NOT NULL,

    PRIMARY KEY (id, crawler_id),
    UNIQUE KEY (crawler_id, package_id, path)
) ENGINE=InnoDB;

CREATE TABLE package (
    crawler_id TINYINT UNSIGNED NOT NULL,
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

    path VARBINARY(255) NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    md CHAR(32) NOT NULL DEFAULT '',

    PRIMARY KEY(id, crawler_id),
    UNIQUE KEY (md)
) ENGINE=InnoDB;

CREATE TABLE provider_record (
    id VARBINARY(64) NOT NULL,    

    crawler_id TINYINT UNSIGNED NOT NULL,
    package_id INTEGER UNSIGNED NOT NULL,

    provider_id TINYINT UNSIGNED NOT NULL,
    name VARBINARY(255) NOT NULL DEFAULT 'noname',
    url VARBINARY(255) NOT NULL DEFAULT '',
    download_url VARBINARY(255) NOT NULL DEFAULT '',
    license VARCHAR(64) NOT NULL DEFAULT '',

    PRIMARY KEY(id),
    UNIQUE KEY (crawler_id, package_id, provider_id)
) ENGINE=InnoDB;

CREATE TABLE crawler (
    id TINYINT UNSIGNED NOT NULL,

    name VARCHAR(255) NOT NULL,
    url VARBINARY(255) NOT NULL,

    PRIMARY KEY(id)
) ENGINE=InnoDB;

INSERT INTO crawler (id, name, url) VALUES 
    (1, "Tango", "http://www.poehlsen.org/sse/"),
    (2, "Soleil", "http://soleil.ethium.org:8000/sse/");