CREATE TABLE IF NOT EXISTS url (
    url_id INTEGER PRIMARY KEY,
    path TEXT NOT NULL
) STRICT;

CREATE UNIQUE INDEX IF NOT EXISTS url_path
ON url (path);

CREATE TABLE IF NOT EXISTS file (
    file_id INTEGER PRIMARY KEY,
    url_id INTEGER NOT NULL,
    content_type TEXT NOT NULL,
    last_modified INTEGER NOT NULL,
    title TEXT NOT NULL,
    etag TEXT NOT NULL,
    headers BLOB NOT NULL,
    FOREIGN KEY (url_id) REFERENCES url (url_id)
) STRICT;

CREATE UNIQUE INDEX IF NOT EXISTS file_url_content_type
ON file (url_id, content_type);

CREATE TABLE IF NOT EXISTS content (
    content_id INTEGER PRIMARY KEY,
    file_id INTEGER NOT NULL,
    encoding TEXT NOT NULL,
    body BLOB NOT NULL,
    FOREIGN KEY (file_id) REFERENCES file (file_id)
) STRICT;

CREATE UNIQUE INDEX IF NOT EXISTS file_content
ON content (file_id, encoding);