NTP_V2/db/grains-jobs.sql

54 lines
2.2 KiB
SQL

-- Scheduled jobs (screen "المهام المجدولة", SU only). Run once per database in the PRIMARY schema (NAFITHTCS). Safe to re-run; not run by the app.
-- Until this script has run the scheduler stays idle (one warning in the API log) and the screen shows a message.
--
-- GRAINS_JOBS one row per job: stopped / started, schedule, last run. Rows are created by the screen, nothing to seed.
-- GRAINS_JOB_RUNS history of every run (kept 90 days by the application).
--
-- Jobs (ports of the legacy jobs\*.cfm files): activate-company-destinations, close-old-permits, fill-company-shares.
-- Schedule: SCHEDULETYPE = 'INTERVAL' (INTERVALMINUTES) or 'DAILY' (DAILYTIMES = 'HH:mm,HH:mm', database server time).
DECLARE
PROCEDURE ddl(p_sql VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_sql;
EXCEPTION
WHEN OTHERS THEN
-- already there: name in use (955), column list already indexed (1408)
IF SQLCODE NOT IN (-955, -1408) THEN RAISE; END IF;
END;
BEGIN
ddl('CREATE TABLE GRAINS_JOBS (
JOBKEY VARCHAR2(50) NOT NULL,
ENABLED NUMBER(1) DEFAULT 0 NOT NULL,
SCHEDULETYPE VARCHAR2(10),
INTERVALMINUTES NUMBER(8),
DAILYTIMES VARCHAR2(200),
LASTSTARTDATE DATE,
LASTENDDATE DATE,
LASTSTATUS VARCHAR2(20),
LASTMESSAGE VARCHAR2(500),
LASTCOUNT NUMBER(10),
UPDATEDATE DATE,
UPDATEDBY VARCHAR2(50),
CONSTRAINT PK_GRAINS_JOBS PRIMARY KEY (JOBKEY)
)');
ddl('CREATE TABLE GRAINS_JOB_RUNS (
ID NUMBER(12) NOT NULL,
JOBKEY VARCHAR2(50) NOT NULL,
STARTDATE DATE NOT NULL,
ENDDATE DATE,
STATUS VARCHAR2(20),
ROWSAFFECTED NUMBER(10),
MESSAGE VARCHAR2(500),
TRIGGEREDBY VARCHAR2(50),
CONSTRAINT PK_GRAINS_JOB_RUNS PRIMARY KEY (ID)
)');
ddl('CREATE SEQUENCE SEQ_GRAINS_JOB_RUNS START WITH 1 INCREMENT BY 1 NOCACHE');
ddl('CREATE INDEX IX_GRAINS_JOB_RUNS_KEY ON GRAINS_JOB_RUNS (JOBKEY, ID)');
END;
/
-- Stop a job from SQL if the screen is not reachable: UPDATE GRAINS_JOBS SET ENABLED = 0 WHERE JOBKEY = 'close-old-permits'; COMMIT;