tCMS/schema/flatfile.schema

CREATE TABLE IF NOT EXISTS tag (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS posts_index (
    post_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL REFERENCES tag(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS tag_idx ON tag(name);

CREATE VIEW IF NOT EXISTS posts AS SELECT p.post_id as id, t.name AS tag FROM posts_index AS p JOIN tag AS t ON t.id=p.tag_id;