CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pet_id TEXT NOT NULL,
platform TEXT NOT NULL,
post_id TEXT NOT NULL UNIQUE,
post_url TEXT NOT NULL,
posted_at TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE post_metrics (
id INTEGER PRIMARY KEY AUTOINCREMENT,
post_id TEXT NOT NULL,
collected_at TEXT NOT NULL,
likes INTEGER,
reposts INTEGER,
comments INTEGER,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
FOREIGN KEY (post_id) REFERENCES posts (post_id)
);
Action Items:
We should branch off of https://github.com/codeforboston/CutePetsBoston/pull/155/changes because this change builds on it.
Right now
collect_metricson that branch updates database.json, but we want to change it to update the sqlite db instead