72 lines
2.5 KiB
SQL
72 lines
2.5 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS sources (
|
|
id INTEGER PRIMARY KEY,
|
|
url TEXT UNIQUE,
|
|
title TEXT,
|
|
publisher TEXT,
|
|
date_published TEXT,
|
|
content TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS summaries (
|
|
id INTEGER PRIMARY KEY,
|
|
source_id INTEGER REFERENCES sources(id) ON DELETE SET NULL,
|
|
title TEXT NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
newsletter_date TEXT,
|
|
tone_version TEXT,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT UNIQUE
|
|
);
|
|
CREATE TABLE IF NOT EXISTS source_tags (
|
|
source_id INTEGER REFERENCES sources(id) ON DELETE CASCADE,
|
|
tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY(source_id, tag_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS embeddings (
|
|
id INTEGER PRIMARY KEY,
|
|
ref_table TEXT NOT NULL CHECK(ref_table IN ('sources','summaries')),
|
|
ref_id INTEGER NOT NULL,
|
|
model TEXT NOT NULL,
|
|
dim INTEGER NOT NULL,
|
|
vec BLOB NOT NULL,
|
|
UNIQUE(ref_table, ref_id, model)
|
|
);
|
|
|
|
CREATE VIRTUAL TABLE IF NOT EXISTS sources_fts USING fts5(
|
|
title, content, content='sources', content_rowid='id'
|
|
);
|
|
CREATE VIRTUAL TABLE IF NOT EXISTS summaries_fts USING fts5(
|
|
title, summary, content='summaries', content_rowid='id'
|
|
);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS sources_ai AFTER INSERT ON sources BEGIN
|
|
INSERT INTO sources_fts(rowid, title, content) VALUES (new.id, new.title, new.content);
|
|
END;
|
|
CREATE TRIGGER IF NOT EXISTS sources_au AFTER UPDATE ON sources BEGIN
|
|
INSERT INTO sources_fts(sources_fts, rowid, title, content)
|
|
VALUES('delete', old.id, old.title, old.content);
|
|
INSERT INTO sources_fts(rowid, title, content) VALUES (new.id, new.title, new.content);
|
|
END;
|
|
CREATE TRIGGER IF NOT EXISTS sources_ad AFTER DELETE ON sources BEGIN
|
|
INSERT INTO sources_fts(sources_fts, rowid, title, content) VALUES('delete', old.id, old.title, old.content);
|
|
END;
|
|
|
|
CREATE TRIGGER IF NOT EXISTS summaries_ai AFTER INSERT ON summaries BEGIN
|
|
INSERT INTO summaries_fts(rowid, title, summary) VALUES (new.id, new.title, new.summary);
|
|
END;
|
|
CREATE TRIGGER IF NOT EXISTS summaries_au AFTER UPDATE ON summaries BEGIN
|
|
INSERT INTO summaries_fts(summaries_fts, rowid, title, summary)
|
|
VALUES('delete', old.id, old.title, old.summary);
|
|
INSERT INTO summaries_fts(rowid, title, summary) VALUES (new.id, new.title, new.summary);
|
|
END;
|
|
CREATE TRIGGER IF NOT EXISTS summaries_ad AFTER DELETE ON summaries BEGIN
|
|
INSERT INTO summaries_fts(summaries_fts, rowid, title, summary) VALUES('delete', old.id, old.title, old.summary);
|
|
END;
|