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 NOT NULL,
    PRIMARY KEY (text, type, file_id)
);

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)
);

CREATE TABLE package (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    filename VARBINARY(255) NOT NULL,
    url VARBINARY(255) NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    md CHAR(32) NOT NULL,
    PRIMARY KEY(id),
    KEY (md)
);