DigitalHuman-service/script/sql/DM8/powerjob.sql

CREATE TABLE "km"."pj_app_info"
(
    "id"             BIGINT       NOT NULL,
    "app_name"       VARCHAR(255) DEFAULT NULL
                                  NULL,
    "current_server" VARCHAR(255) DEFAULT NULL
                                  NULL,
    "gmt_create"     TIMESTAMP(6) NULL,
    "gmt_modified"   TIMESTAMP(6) NULL,
    "password"       VARCHAR(255) DEFAULT NULL
                                  NULL
);
CREATE TABLE "km"."pj_container_info"
(
    "id"               BIGINT       NOT NULL,
    "app_id"           BIGINT       NULL,
    "container_name"   VARCHAR(255) DEFAULT NULL
                                    NULL,
    "gmt_create"       TIMESTAMP(6) NULL,
    "gmt_modified"     TIMESTAMP(6) NULL,
    "last_deploy_time" TIMESTAMP(6) NULL,
    "source_info"      VARCHAR(255) DEFAULT NULL
                                    NULL,
    "source_type"      INT          NULL,
    "status"           INT          NULL,
    "version"          VARCHAR(255) DEFAULT NULL
                                    NULL
);
CREATE TABLE "km"."pj_instance_info"
(
    "id"                    BIGINT       NOT NULL,
    "actual_trigger_time"   BIGINT       NULL,
    "app_id"                BIGINT       NULL,
    "expected_trigger_time" BIGINT       NULL,
    "finished_time"         BIGINT       NULL,
    "gmt_create"            TIMESTAMP(6) NULL,
    "gmt_modified"          TIMESTAMP(6) NULL,
    "instance_id"           BIGINT       NULL,
    "instance_params"       TEXT         NULL,
    "job_id"                BIGINT       NULL,
    "job_params"            TEXT         NULL,
    "last_report_time"      BIGINT       NULL,
    "result"                TEXT         NULL,
    "running_times"         BIGINT       NULL,
    "status"                INT          NULL,
    "task_tracker_address"  VARCHAR(255) DEFAULT NULL
                                         NULL,
    "type"                  INT          NULL,
    "wf_instance_id"        BIGINT       NULL
);
CREATE TABLE "km"."pj_job_info"
(
    "id"                   BIGINT       NOT NULL,
    "alarm_config"         VARCHAR(255) DEFAULT NULL
                                        NULL,
    "app_id"               BIGINT       NULL,
    "concurrency"          INT          NULL,
    "designated_workers"   VARCHAR(255) DEFAULT NULL
                                        NULL,
    "dispatch_strategy"    INT          NULL,
    "execute_type"         INT          NULL,
    "extra"                VARCHAR(255) DEFAULT NULL
                                        NULL,
    "gmt_create"           TIMESTAMP(6) NULL,
    "gmt_modified"         TIMESTAMP(6) NULL,
    "instance_retry_num"   INT          NULL,
    "instance_time_limit"  BIGINT       NULL,
    "job_description"      VARCHAR(255) DEFAULT NULL
                                        NULL,
    "job_name"             VARCHAR(255) DEFAULT NULL
                                        NULL,
    "job_params"           TEXT         NULL,
    "lifecycle"            VARCHAR(255) DEFAULT NULL
                                        NULL,
    "log_config"           VARCHAR(255) DEFAULT NULL
                                        NULL,
    "max_instance_num"     INT          NULL,
    "max_worker_count"     INT          NULL,
    "min_cpu_cores"        REAL         NOT NULL,
    "min_disk_space"       REAL         NOT NULL,
    "min_memory_space"     REAL         NOT NULL,
    "next_trigger_time"    BIGINT       NULL,
    "notify_user_ids"      VARCHAR(255) DEFAULT NULL
                                        NULL,
    "processor_info"       VARCHAR(255) DEFAULT NULL
                                        NULL,
    "processor_type"       INT          NULL,
    "status"               INT          NULL,
    "tag"                  VARCHAR(255) DEFAULT NULL
                                        NULL,
    "task_retry_num"       INT          NULL,
    "time_expression"      VARCHAR(255) DEFAULT NULL
                                        NULL,
    "time_expression_type" INT          NULL
);
CREATE TABLE "km"."pj_oms_lock"
(
    "id"            BIGINT       NOT NULL,
    "gmt_create"    TIMESTAMP(6) NULL,
    "gmt_modified"  TIMESTAMP(6) NULL,
    "lock_name"     VARCHAR(255) DEFAULT NULL
                                 NULL,
    "max_lock_time" BIGINT       NULL,
    "ownerip"       VARCHAR(255) DEFAULT NULL
                                 NULL
);
CREATE TABLE "km"."pj_server_info"
(
    "id"           BIGINT       NOT NULL,
    "gmt_create"   TIMESTAMP(6) NULL,
    "gmt_modified" TIMESTAMP(6) NULL,
    "ip"           VARCHAR(255) DEFAULT NULL
                                NULL
);
CREATE TABLE "km"."pj_user_info"
(
    "id"           BIGINT       NOT NULL,
    "email"        VARCHAR(255) DEFAULT NULL
                                NULL,
    "extra"        VARCHAR(255) DEFAULT NULL
                                NULL,
    "gmt_create"   TIMESTAMP(6) NULL,
    "gmt_modified" TIMESTAMP(6) NULL,
    "password"     VARCHAR(255) DEFAULT NULL
                                NULL,
    "phone"        VARCHAR(255) DEFAULT NULL
                                NULL,
    "username"     VARCHAR(255) DEFAULT NULL
                                NULL,
    "web_hook"     VARCHAR(255) DEFAULT NULL
                                NULL
);
CREATE TABLE "km"."pj_workflow_info"
(
    "id"                   BIGINT       NOT NULL,
    "app_id"               BIGINT       NULL,
    "extra"                VARCHAR(255) DEFAULT NULL
                                        NULL,
    "gmt_create"           TIMESTAMP(6) NULL,
    "gmt_modified"         TIMESTAMP(6) NULL,
    "lifecycle"            VARCHAR(255) DEFAULT NULL
                                        NULL,
    "max_wf_instance_num"  INT          NULL,
    "next_trigger_time"    BIGINT       NULL,
    "notify_user_ids"      VARCHAR(255) DEFAULT NULL
                                        NULL,
    "pedag"                TEXT         NULL,
    "status"               INT          NULL,
    "time_expression"      VARCHAR(255) DEFAULT NULL
                                        NULL,
    "time_expression_type" INT          NULL,
    "wf_description"       VARCHAR(255) DEFAULT NULL
                                        NULL,
    "wf_name"              VARCHAR(255) DEFAULT NULL
                                        NULL
);
CREATE TABLE "km"."pj_workflow_instance_info"
(
    "id"                    BIGINT       NOT NULL,
    "actual_trigger_time"   BIGINT       NULL,
    "app_id"                BIGINT       NULL,
    "dag"                   TEXT         NULL,
    "expected_trigger_time" BIGINT       NULL,
    "finished_time"         BIGINT       NULL,
    "gmt_create"            TIMESTAMP(6) NULL,
    "gmt_modified"          TIMESTAMP(6) NULL,
    "parent_wf_instance_id" BIGINT       NULL,
    "result"                TEXT         NULL,
    "status"                INT          NULL,
    "wf_context"            TEXT         NULL,
    "wf_init_params"        TEXT         NULL,
    "wf_instance_id"        BIGINT       NULL,
    "workflow_id"           BIGINT       NULL
);
CREATE TABLE "km"."pj_workflow_node_info"
(
    "id"               BIGINT       NOT NULL,
    "app_id"           BIGINT       NOT NULL,
    "enable"           BIT          NOT NULL,
    "extra"            TEXT         NULL,
    "gmt_create"       TIMESTAMP(6) NOT NULL,
    "gmt_modified"     TIMESTAMP(6) NOT NULL,
    "job_id"           BIGINT       NULL,
    "node_name"        VARCHAR(255) DEFAULT NULL
                                    NULL,
    "node_params"      TEXT         NULL,
    "skip_when_failed" BIT          NOT NULL,
    "type"             INT          NULL,
    "workflow_id"      BIGINT       NULL
);


ALTER TABLE "km"."pj_app_info"
    ADD CONSTRAINT "pj_app_info_pkey" PRIMARY KEY ("id");

ALTER TABLE "km"."pj_container_info"
    ADD CONSTRAINT "pj_container_info_pkey" PRIMARY KEY ("id");

ALTER TABLE "km"."pj_instance_info"
    ADD CONSTRAINT "pj_instance_info_pkey" PRIMARY KEY ("id");

ALTER TABLE "km"."pj_job_info"
    ADD CONSTRAINT "pj_job_info_pkey" PRIMARY KEY ("id");

ALTER TABLE "km"."pj_oms_lock"
    ADD CONSTRAINT "pj_oms_lock_pkey" PRIMARY KEY ("id");

ALTER TABLE "km"."pj_server_info"
    ADD CONSTRAINT "pj_server_info_pkey" PRIMARY KEY ("id");

ALTER TABLE "km"."pj_user_info"
    ADD CONSTRAINT "pj_user_info_pkey" PRIMARY KEY ("id");

ALTER TABLE "km"."pj_workflow_info"
    ADD CONSTRAINT "pj_workflow_info_pkey" PRIMARY KEY ("id");

ALTER TABLE "km"."pj_workflow_instance_info"
    ADD CONSTRAINT "pj_workflow_instance_info_pkey" PRIMARY KEY ("id");

ALTER TABLE "km"."pj_workflow_node_info"
    ADD CONSTRAINT "pj_workflow_node_info_pkey" PRIMARY KEY ("id");


CREATE UNIQUE INDEX "uidx01_app_info"
    ON "km"."pj_app_info" ("app_name");

CREATE INDEX "idx01_container_info"
    ON "km"."pj_container_info" ("app_id");

CREATE INDEX "idx01_instance_info"
    ON "km"."pj_instance_info" ("job_id", "status");

CREATE INDEX "idx02_instance_info"
    ON "km"."pj_instance_info" ("app_id", "status");

CREATE INDEX "idx03_instance_info"
    ON "km"."pj_instance_info" ("instance_id", "status");

CREATE INDEX "idx01_job_info"
    ON "km"."pj_job_info" ("app_id", "status", "time_expression_type", "next_trigger_time");

CREATE UNIQUE INDEX "uidx01_oms_lock"
    ON "km"."pj_oms_lock" ("lock_name");

CREATE INDEX "idx01_server_info"
    ON "km"."pj_server_info" ("gmt_modified");

CREATE UNIQUE INDEX "uidx01_server_info"
    ON "km"."pj_server_info" ("ip");

CREATE INDEX "uidx01_user_info"
    ON "km"."pj_user_info" ("username");

CREATE INDEX "uidx02_user_info"
    ON "km"."pj_user_info" ("email");

CREATE INDEX "idx01_workflow_info"
    ON "km"."pj_workflow_info" ("app_id", "status", "time_expression_type", "next_trigger_time");

CREATE INDEX "idx01_wf_instance"
    ON "km"."pj_workflow_instance_info" ("workflow_id", "status", "app_id", "expected_trigger_time");

CREATE UNIQUE INDEX "uidx01_wf_instance"
    ON "km"."pj_workflow_instance_info" ("wf_instance_id");

CREATE INDEX "idx01_workflow_node_info"
    ON "km"."pj_workflow_node_info" ("workflow_id", "gmt_create");