JobRunr 6.0.0 migration(postgres)

jobrunr6.0.0.sql

CREATE TABLE "jobrunr_migrations"
(
    "id"          NCHAR(36) PRIMARY KEY,
    "script"      VARCHAR(64) NOT NULL,
    "installedon" VARCHAR(29) NOT NULL
);

CREATE TABLE "jobrunr_jobs"
(
    "id"             NCHAR(36) PRIMARY KEY,
    "version"        INT          NOT NULL,
    "jobasjson"      TEXT         NOT NULL,
    "jobsignature"   VARCHAR(512) NOT NULL,
    "state"          VARCHAR(36)  NOT NULL,
    "createdat"      TIMESTAMP    NOT NULL,
    "updatedat"      TIMESTAMP    NOT NULL,
    "scheduledat"    TIMESTAMP,
    "recurringjobid" VARCHAR(128)
);

CREATE INDEX "jobrunr_state_idx" ON "jobrunr_jobs" ("state");
CREATE INDEX "jobrunr_job_signature_idx" ON "jobrunr_jobs" ("jobsignature");
CREATE INDEX "jobrunr_job_created_at_idx" ON "jobrunr_jobs" ("createdat");
CREATE INDEX "jobrunr_jobs_state_updated_idx" ON "jobrunr_jobs" ("state" ASC, "updatedat" ASC);
CREATE INDEX "jobrunr_job_scheduled_at_idx" ON "jobrunr_jobs" ("scheduledat");
CREATE INDEX "jobrunr_job_rci_idx" ON "jobrunr_jobs" ("recurringjobid");

CREATE TABLE "jobrunr_recurring_jobs"
(
    "id"        NCHAR(128) PRIMARY KEY,
    "version"   INT    NOT NULL,
    "jobasjson" TEXT   NOT NULL,
    "createdat" BIGINT NOT NULL DEFAULT '0'
);

CREATE INDEX "jobrunr_recurring_job_created_at_idx" ON "jobrunr_recurring_jobs" ("createdat");

CREATE TABLE "jobrunr_backgroundjobservers"
(
    "id"                         NCHAR(36) PRIMARY KEY,
    "workerpoolsize"             INT           NOT NULL,
    "pollintervalinseconds"      INT           NOT NULL,
    "firstheartbeat"             TIMESTAMP(6)  NOT NULL,
    "lastheartbeat"              TIMESTAMP(6)  NOT NULL,
    "running"                    INT           NOT NULL,
    "systemtotalmemory"          BIGINT        NOT NULL,
    "systemfreememory"           BIGINT        NOT NULL,
    "systemcpuload"              NUMERIC(3, 2) NOT NULL,
    "processmaxmemory"           BIGINT        NOT NULL,
    "processfreememory"          BIGINT        NOT NULL,
    "processallocatedmemory"     BIGINT        NOT NULL,
    "processcpuload"             NUMERIC(3, 2) NOT NULL,
    "deletesucceededjobsafter"   VARCHAR(32),
    "permanentlydeletejobsafter" VARCHAR(32),
    "name"                       VARCHAR(128)
);

CREATE INDEX "jobrunr_bgjobsrvrs_fsthb_idx" ON "jobrunr_backgroundjobservers" ("firstheartbeat");
CREATE INDEX "jobrunr_bgjobsrvrs_lsthb_idx" ON "jobrunr_backgroundjobservers" ("lastheartbeat");

CREATE TABLE "jobrunr_job_counters"
(
    "name"   NCHAR(36) PRIMARY KEY,
    "amount" INT NOT NULL
);

INSERT INTO "jobrunr_job_counters" ("name", "amount")
VALUES ('AWAITING', 0);
INSERT INTO "jobrunr_job_counters" ("name", "amount")
VALUES ('SCHEDULED', 0);
INSERT INTO "jobrunr_job_counters" ("name", "amount")
VALUES ('ENQUEUED', 0);
INSERT INTO "jobrunr_job_counters" ("name", "amount")
VALUES ('PROCESSING', 0);
INSERT INTO "jobrunr_job_counters" ("name", "amount")
VALUES ('FAILED', 0);
INSERT INTO "jobrunr_job_counters" ("name", "amount")
VALUES ('SUCCEEDED', 0);

CREATE TABLE "jobrunr_metadata"
(
    "id"        VARCHAR(156) PRIMARY KEY,
    "name"      VARCHAR(92) NOT NULL,
    "owner"     VARCHAR(64) NOT NULL,
    "value"     TEXT        NOT NULL,
    "createdat" TIMESTAMP   NOT NULL,
    "updatedat" TIMESTAMP   NOT NULL
);

INSERT INTO "jobrunr_metadata" ("id", "name", "owner", "value", "createdat", "updatedat")
VALUES ('succeeded-jobs-counter-cluster', 'succeeded-jobs-counter', 'cluster',
        CAST((SELECT "amount" FROM "jobrunr_job_counters" WHERE "name" = 'SUCCEEDED') AS CHAR(10)), CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP);

DROP TABLE "jobrunr_job_counters";

CREATE VIEW "jobrunr_jobs_stats"
AS
WITH "job_stat_results" AS (SELECT "state", COUNT(*) AS "count"
                            FROM "jobrunr_jobs"
                            GROUP BY ROLLUP ("state"))
SELECT COALESCE((SELECT "count" FROM "job_stat_results" WHERE "state" IS NULL), 0)        AS "total",
       COALESCE((SELECT "count" FROM "job_stat_results" WHERE "state" = 'SCHEDULED'), 0)  AS "scheduled",
       COALESCE((SELECT "count" FROM "job_stat_results" WHERE "state" = 'ENQUEUED'), 0)   AS "enqueued",
       COALESCE((SELECT "count" FROM "job_stat_results" WHERE "state" = 'PROCESSING'), 0) AS "processing",
       COALESCE((SELECT "count" FROM "job_stat_results" WHERE "state" = 'FAILED'), 0)     AS "failed",
       COALESCE((SELECT "count" FROM "job_stat_results" WHERE "state" = 'SUCCEEDED'), 0)  AS "succeeded",
       COALESCE((SELECT CAST(CAST("value" AS CHAR(10)) AS DECIMAL(10, 0))
                 FROM "jobrunr_metadata" "jm"
                 WHERE "jm"."id" = 'succeeded-jobs-counter-cluster'), 0)                  AS "alltimesucceeded",
       COALESCE((SELECT "count" FROM "job_stat_results" WHERE "state" = 'DELETED'), 0)    AS "deleted",
       (SELECT COUNT(*) FROM "jobrunr_backgroundjobservers")                              AS "nbrofbackgroundjobservers",
       (SELECT COUNT(*) FROM "jobrunr_recurring_jobs")                                    AS "nbrofrecurringjobs";