| 1 | package BoardStreams::DBMigrations; | ||
| 2 | |||
| 3 | use Mojo::Base -strict, -signatures; | ||
| 4 | |||
| 5 | our $VERSION = "v0.0.36"; | ||
| 6 | |||
| 7 | my $string = <<~'SQL'; | ||
| 8 | -- 1 up | ||
| 9 | CREATE TABLE "bs_streams" ( | ||
| 10 | "id" BIGINT GENERATED BY DEFAULT AS IDENTITY, | ||
| 11 | "name" TEXT NOT NULL, | ||
| 12 | "state" JSONB NOT NULL CHECK("state" != 'null'), | ||
| 13 | "event_id" BIGINT NOT NULL DEFAULT 0, | ||
| 14 | "last_dt" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, | ||
| 15 | "type" TEXT NULL CHECK(LENGTH("type") <= 50), | ||
| 16 | "keep_events" BOOLEAN NOT NULL DEFAULT true, | ||
| 17 | |||
| 18 | PRIMARY KEY ("id") | ||
| 19 | ); | ||
| 20 | CREATE UNIQUE INDEX "bs_streams_uidx_name" ON "bs_streams" ("name"); | ||
| 21 | CREATE INDEX ON "bs_streams" ("type"); | ||
| 22 | |||
| 23 | CREATE SEQUENCE "bs_events_id_seq"; | ||
| 24 | CREATE TABLE "bs_events" ( | ||
| 25 | "id" BIGINT NOT NULL DEFAULT nextval('bs_events_id_seq'), | ||
| 26 | "stream_id" BIGINT NOT NULL REFERENCES "bs_streams" ON DELETE CASCADE, | ||
| 27 | "datetime" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, | ||
| 28 | "data" JSONB NOT NULL CHECK("data" != 'null'), | ||
| 29 | |||
| 30 | PRIMARY KEY ("id") | ||
| 31 | ); | ||
| 32 | CREATE INDEX ON "bs_events" ("stream_id", "id"); | ||
| 33 | CREATE INDEX ON "bs_events" ("stream_id", "datetime"); | ||
| 34 | |||
| 35 | CREATE TABLE "bs_workers" ( | ||
| 36 | "id" TEXT NOT NULL, | ||
| 37 | "dt_heartbeat" TIMESTAMPTZ NOT NULL, | ||
| 38 | "is_repairer" BOOLEAN NOT NULL DEFAULT false, | ||
| 39 | |||
| 40 | PRIMARY KEY ("id") | ||
| 41 | ); | ||
| 42 | CREATE INDEX ON "bs_workers" ("dt_heartbeat"); | ||
| 43 | CREATE UNIQUE INDEX ON "bs_workers" ("is_repairer") WHERE "is_repairer" = true; | ||
| 44 | |||
| 45 | CREATE TABLE "bs_guards" ( | ||
| 46 | "worker_id" TEXT NOT NULL, | ||
| 47 | "stream_id" BIGINT NOT NULL REFERENCES "bs_streams" ON DELETE CASCADE, | ||
| 48 | "count" BIGINT NOT NULL CHECK("count" >= 0), | ||
| 49 | |||
| 50 | PRIMARY KEY ("worker_id", "stream_id") | ||
| 51 | ); | ||
| 52 | CREATE INDEX ON "bs_guards" ("stream_id"); | ||
| 53 | |||
| 54 | -- 1 down | ||
| 55 | DROP TABLE "bs_guards"; | ||
| 56 | DROP TABLE "bs_workers"; | ||
| 57 | DROP TABLE "bs_events"; | ||
| 58 | DROP TABLE "bs_streams"; | ||
| 59 | SQL | ||
| 60 | |||
| 61 | sub apply_migrations ($class, $pg) { | ||
| 62 | $pg->migrations | ||
| 63 | ->name('boardstreams_library') | ||
| 64 | ->from_string($string) | ||
| 65 | ->migrate; | ||
| 66 | } | ||
| 67 | |||
| 68 | 1; |