mlbstats-mastodon-scripts/schema.sql

create table if not exists plays (
	"json" text unique on conflict ignore,
	"posted" integer check ("posted" in (0, 1))
);

create index if not exists nonposted_plays
on plays(posted)
where posted = 0;

create table if not exists highlights (
	"json" text unique on conflict ignore,
	"posted" integer check ("posted" in (0, 1))
);

create index if not exists nonposted_highlights
on highlights(posted)
where posted = 0;

create table if not exists games (
	"json" text unique on conflict ignore,
	"posted" integer check ("posted" in (0, 1))
);

create index if not exists nonposted_games
on games(posted)
where posted = 0;

create trigger if not exists delete_old_states
before insert on games
begin
       delete from games
       where json ->> 'gamePk' = new.json ->> 'gamePk' and
       json ->> 'status' ->> 'detailedState' != new.json ->> 'status' ->> 'detailedState';
end;