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

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)
) ENGINE=InnoDB;

CREATE TABLE package (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    path VARBINARY(255) NOT NULL,
    name VARBINARY(255) NOT NULL DEFAULT 'noname',
    url VARBINARY(255) NOT NULL DEFAULT '',
    timestamp TIMESTAMP NOT NULL,
    md CHAR(32) NOT NULL DEFAULT '',
    PRIMARY KEY(id),
    UNIQUE KEY (md)
) ENGINE=InnoDB;
