35 lines
896 B
SQL
35 lines
896 B
SQL
CREATE TABLE websites (
|
|
id INTEGER PRIMARY KEY,
|
|
domain TEXT UNIQUE,
|
|
last_collected TIMESTAMP WITHOUT TIME ZONE
|
|
);
|
|
|
|
CREATE TABLE posts (
|
|
id INTEGER PRIMARY KEY,
|
|
url TEXT UNIQUE,
|
|
last_updated_by_version TEXT,
|
|
content TEXT,
|
|
content_tsv TSVECTOR
|
|
);
|
|
|
|
CREATE INDEX "posts_fti_idx" ON posts USING GIN(content_tsv);
|
|
CREATE TRIGGER "posts_fti_trigger" AFTER UPDATE OR INSERT OR DELETE
|
|
ON posts FOR EACH ROW
|
|
EXECUTE PROCEDURE tsvector_update_trigger(posts_fti, content_tsv, 'pg_catalog.english', content);
|
|
|
|
CREATE TABLE authors (
|
|
id INTEGER PRIMARY KEY,
|
|
url TEXT UNIQUE
|
|
);
|
|
|
|
CREATE TABLE posts_keywords (
|
|
post_id INTEGER,
|
|
keyword TEXT,
|
|
|
|
PRIMARY KEY(post_id, keyword),
|
|
CONSTRAINT fk_post FOREIGN KEY(post_id) REFERENCES posts(id)
|
|
-- CONSTRAINT fk_keyword FOREIGN KEY(keyword) REFERENCES keywords(keyword)
|
|
);
|
|
|
|
CREATE INDEX "posts_keywords_idx" ON posts_keywords(keyword);
|