gistfile1.txt

> .schema
CREATE TABLE Files(    file_id INTEGER PRIMARY KEY,    filename VARCHAR(4096) NOT NULL UNIQUE,    last_modified INTEGER,    size INTEGER,    num_tags INTEGER DEFAULT 0);
CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE TABLE FileTags(    file_id INTEGER NOT NULL,    tag_id INTEGER NOT NULL,    probability DOUBLE NOT NULL,    PRIMARY KEY (file_id, tag_id),    FOREIGN KEY(file_id) REFERENCES Files(file_id),    FOREIGN KEY(tag_id)  REFERENCES Tags(tag_id));
CREATE TABLE Tags(tag_id INTEGER PRIMARY KEY, tag_name VARCHAR[50] NOT NULL UNIQUE, num_files INTEGER DEFAULT 0);
CREATE TABLE TagCardinalities(
    a INTEGER NOT NULL,
    b INTEGER NOT NULL,
    cardinality INTEGER,
    PRIMARY KEY (a,b),
    FOREIGN KEY(a) REFERENCES Tags(tag_id),
    FOREIGN KEY(b) REFERENCES Tags(tag_id));
CREATE INDEX filename_idx ON Files(filename);
CREATE INDEX filetags_idx ON FileTags(file_id,tag_id);
CREATE INDEX tag_name_idx ON Tags(tag_name);
CREATE INDEX tagcardinalities_a_idx ON TagCardinalities(a);
CREATE INDEX tagcardinalities_b_idx ON TagCardinalities(b);
CREATE INDEX tagcardinalities_ab_idx ON TagCardinalities(a,b);