Skip to content

Create sqlite db for dashboard #154

Description

@binamkayastha

Action Items:

  • Save the sqlite DB as a artifact workflow similar to database.json, call it metrics.sqlite
  • Make sure the workflow "retrieves" the metrics.sqlite from the previous run
  • Have main.py create the sqlite file if it doesn't exist with the following create table statements
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)
);

We should branch off of https://github.com/codeforboston/CutePetsBoston/pull/155/changes because this change builds on it.

Right now collect_metrics on that branch updates database.json, but we want to change it to update the sqlite db instead

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Projects

Status
In Progress

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions