-- $Id$

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

CREATE TABLE word (
    text VARCHAR(40) NOT NULL,
    type ENUM ('word', 'subword') DEFAULT 'word' NOT NULL,
    file_id INTEGER UNSIGNED NOT NULL,
    cnt INTEGER UNSIGNED DEFAULT 0 NOT NULL,
    PRIMARY KEY (text, type, file_id)
) ENGINE=InnoDB;

CREATE TABLE file (
    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),
    UNIQUE KEY (package_id, path)
) ENGINE=InnoDB;

CREATE TABLE package (
    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),
    UNIQUE KEY (md)
) ENGINE=InnoDB;

CREATE TABLE provider_record (
    id VARBINARY(64) 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 (package_id, provider_id)
) ENGINE=InnoDB;