dwelling-radio/internal/statistics/db/sqlite/queries/schema.sql

CREATE TABLE IF NOT EXISTS `song` (
    `song_id` INTEGER NOT NULL,
    `artist`  TEXT    NOT NULL,
    `title`   TEXT    NOT NULL,
    PRIMARY KEY (`song_id` AUTOINCREMENT),
    UNIQUE (`artist`, `title`) );

CREATE TABLE IF NOT EXISTS `history` (
    `start_at`       TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
    `song_id`        INTEGER NOT NULL,
    `listeners`      INTEGER NOT NULL,
    `peak_listeners` INTEGER NOT NULL,
    PRIMARY KEY (`start_at`),
    FOREIGN KEY (`song_id`) REFERENCES `song` (`song_id`)
             ON UPDATE CASCADE ON DELETE CASCADE );