text: /*
  // pgAgent - PostgreSQL Tools
  //
  // Copyright (C) 2002 - 2020 The pgAdmin Development Team
  // This software is released under the PostgreSQL Licence
  //
  // pgagent--unpackaged--4.1.sql - Convert pgAgent existing tables and functions to an extension
  //
  */
  
  \echo Use "CREATE EXTENSION pgagent FROM unpackaged" to load this file. \quit
  
  ALTER EXTENSION pgagent ADD TABLE pgagent.pga_jobagent;
  ALTER EXTENSION pgagent ADD TABLE pgagent.pga_jobclass;
  ALTER EXTENSION pgagent ADD TABLE pgagent.pga_job;
  ALTER EXTENSION pgagent ADD TABLE pgagent.pga_jobstep;
  ALTER EXTENSION pgagent ADD TABLE pgagent.pga_schedule;
  ALTER EXTENSION pgagent ADD TABLE pgagent.pga_exception;
  ALTER EXTENSION pgagent ADD TABLE pgagent.pga_joblog;
  ALTER EXTENSION pgagent ADD TABLE pgagent.pga_jobsteplog;
  
  ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_exception_jexid_seq;
  ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_job_jobid_seq;
  ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_jobclass_jclid_seq;
  ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_joblog_jlgid_seq;
  ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_jobstep_jstid_seq;
  ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_jobsteplog_jslid_seq;
  ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_schedule_jscid_seq;
  
  ALTER EXTENSION pgagent ADD FUNCTION pgagent.pgagent_schema_version();
  ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool);
  ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_is_leap_year(int2);
  ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_job_trigger();
  ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_schedule_trigger();
  ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_exception_trigger();
  
  SELECT pg_catalog.pg_extension_config_dump('pga_jobagent', '');
  SELECT pg_catalog.pg_extension_config_dump('pga_jobclass', $$WHERE jclname NOT IN ('Routine Maintenance', 'Data Import', 'Data Export', 'Data Summarisation', 'Miscellaneous')$$);
  SELECT pg_catalog.pg_extension_config_dump('pga_job', '');
  SELECT pg_catalog.pg_extension_config_dump('pga_jobstep', '');
  SELECT pg_catalog.pg_extension_config_dump('pga_schedule', '');
  SELECT pg_catalog.pg_extension_config_dump('pga_exception', '');
  SELECT pg_catalog.pg_extension_config_dump('pga_joblog', '');
  SELECT pg_catalog.pg_extension_config_dump('pga_jobsteplog', '');
  
  CREATE OR REPLACE FUNCTION pgagent.pgagent_schema_version() RETURNS int2 AS '
  BEGIN
      -- RETURNS PGAGENT MAJOR VERSION
      -- WE WILL CHANGE THE MAJOR VERSION, ONLY IF THERE IS A SCHEMA CHANGE
      RETURN 4;
  END;
  ' LANGUAGE 'plpgsql' VOLATILE;
  
  CREATE OR REPLACE FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool) RETURNS timestamptz AS '
  DECLARE
      jscid           ALIAS FOR $1;
      jscstart        ALIAS FOR $2;
      jscend          ALIAS FOR $3;
      jscminutes      ALIAS FOR $4;
      jschours        ALIAS FOR $5;
      jscweekdays     ALIAS FOR $6;
      jscmonthdays    ALIAS FOR $7;
      jscmonths       ALIAS FOR $8;
  
      nextrun         timestamp := ''1970-01-01 00:00:00-00'';
      runafter        timestamp := ''1970-01-01 00:00:00-00'';
  
      bingo            bool := FALSE;
      gotit            bool := FALSE;
      foundval        bool := FALSE;
      daytweak        bool := FALSE;
      minutetweak        bool := FALSE;
  
      i                int2 := 0;
      d                int2 := 0;
  
      nextminute        int2 := 0;
      nexthour        int2 := 0;
      nextday            int2 := 0;
      nextmonth       int2 := 0;
      nextyear        int2 := 0;
  
  
  BEGIN
      -- No valid start date has been specified
      IF jscstart IS NULL THEN RETURN NULL; END IF;
  
      -- The schedule is past its end date
      IF jscend IS NOT NULL AND jscend < now() THEN RETURN NULL; END IF;
  
      -- Get the time to find the next run after. It will just be the later of
      -- now() + 1m and the start date for the time being, however, we might want to
      -- do more complex things using this value in the future.
      IF date_trunc(''MINUTE'', jscstart) > date_trunc(''MINUTE'', (now() + ''1 Minute''::interval)) THEN
          runafter := date_trunc(''MINUTE'', jscstart);
      ELSE
          runafter := date_trunc(''MINUTE'', (now() + ''1 Minute''::interval));
      END IF;
  
      --
      -- Enter a loop, generating next run timestamps until we find one
      -- that falls on the required weekday, and is not matched by an exception
      --
  
      WHILE bingo = FALSE LOOP
  
          --
          -- Get the next run year
          --
          nextyear := date_part(''YEAR'', runafter);
  
          --
          -- Get the next run month
          --
          nextmonth := date_part(''MONTH'', runafter);
          gotit := FALSE;
          FOR i IN (nextmonth) .. 12 LOOP
              IF jscmonths[i] = TRUE THEN
                  nextmonth := i;
                  gotit := TRUE;
                  foundval := TRUE;
                  EXIT;
              END IF;
          END LOOP;
          IF gotit = FALSE THEN
              FOR i IN 1 .. (nextmonth - 1) LOOP
                  IF jscmonths[i] = TRUE THEN
                      nextmonth := i;
  
                      -- Wrap into next year
                      nextyear := nextyear + 1;
                      gotit := TRUE;
                      foundval := TRUE;
                      EXIT;
                  END IF;
             END LOOP;
          END IF;
  
          --
          -- Get the next run day
          --
          -- If the year, or month have incremented, get the lowest day,
          -- otherwise look for the next day matching or after today.
          IF (nextyear > date_part(''YEAR'', runafter) OR nextmonth > date_part(''MONTH'', runafter)) THEN
              nextday := 1;
              FOR i IN 1 .. 32 LOOP
                  IF jscmonthdays[i] = TRUE THEN
                      nextday := i;
                      foundval := TRUE;
                      EXIT;
                  END IF;
              END LOOP;
          ELSE
              nextday := date_part(''DAY'', runafter);
              gotit := FALSE;
              FOR i IN nextday .. 32 LOOP
                  IF jscmonthdays[i] = TRUE THEN
                      nextday := i;
                      gotit := TRUE;
                      foundval := TRUE;
                      EXIT;
                  END IF;
              END LOOP;
              IF gotit = FALSE THEN
                  FOR i IN 1 .. (nextday - 1) LOOP
                      IF jscmonthdays[i] = TRUE THEN
                          nextday := i;
  
                          -- Wrap into next month
                          IF nextmonth = 12 THEN
                              nextyear := nextyear + 1;
                              nextmonth := 1;
                          ELSE
                              nextmonth := nextmonth + 1;
                          END IF;
                          gotit := TRUE;
                          foundval := TRUE;
                          EXIT;
                      END IF;
                  END LOOP;
              END IF;
          END IF;
  
          -- Was the last day flag selected?
          IF nextday = 32 THEN
              IF nextmonth = 1 THEN
                  nextday := 31;
              ELSIF nextmonth = 2 THEN
                  IF pgagent.pga_is_leap_year(nextyear) = TRUE THEN
                      nextday := 29;
                  ELSE
                      nextday := 28;
                  END IF;
              ELSIF nextmonth = 3 THEN
                  nextday := 31;
              ELSIF nextmonth = 4 THEN
                  nextday := 30;
              ELSIF nextmonth = 5 THEN
                  nextday := 31;
              ELSIF nextmonth = 6 THEN
                  nextday := 30;
              ELSIF nextmonth = 7 THEN
                  nextday := 31;
              ELSIF nextmonth = 8 THEN
                  nextday := 31;
              ELSIF nextmonth = 9 THEN
                  nextday := 30;
              ELSIF nextmonth = 10 THEN
                  nextday := 31;
              ELSIF nextmonth = 11 THEN
                  nextday := 30;
              ELSIF nextmonth = 12 THEN
                  nextday := 31;
              END IF;
          END IF;
  
          --
          -- Get the next run hour
          --
          -- If the year, month or day have incremented, get the lowest hour,
          -- otherwise look for the next hour matching or after the current one.
          IF (nextyear > date_part(''YEAR'', runafter) OR nextmonth > date_part(''MONTH'', runafter) OR nextday > date_part(''DAY'', runafter) OR daytweak = TRUE) THEN
              nexthour := 0;
              FOR i IN 1 .. 24 LOOP
                  IF jschours[i] = TRUE THEN
                      nexthour := i - 1;
                      foundval := TRUE;
                      EXIT;
                  END IF;
              END LOOP;
          ELSE
              nexthour := date_part(''HOUR'', runafter);
              gotit := FALSE;
              FOR i IN (nexthour + 1) .. 24 LOOP
                  IF jschours[i] = TRUE THEN
                      nexthour := i - 1;
                      gotit := TRUE;
                      foundval := TRUE;
                      EXIT;
                  END IF;
              END LOOP;
              IF gotit = FALSE THEN
                  FOR i IN 1 .. nexthour LOOP
                      IF jschours[i] = TRUE THEN
                          nexthour := i - 1;
  
                          -- Wrap into next month
                          IF (nextmonth = 1 OR nextmonth = 3 OR nextmonth = 5 OR nextmonth = 7 OR nextmonth = 8 OR nextmonth = 10 OR nextmonth = 12) THEN
                              d = 31;
                          ELSIF (nextmonth = 4 OR nextmonth = 6 OR nextmonth = 9 OR nextmonth = 11) THEN
                              d = 30;
                          ELSE
                              IF pgagent.pga_is_leap_year(nextyear) = TRUE THEN
                                  d := 29;
                              ELSE
                                  d := 28;
                              END IF;
                          END IF;
  
                          IF nextday = d THEN
                              nextday := 1;
                              IF nextmonth = 12 THEN
                                  nextyear := nextyear + 1;
                                  nextmonth := 1;
                              ELSE
                                  nextmonth := nextmonth + 1;
                              END IF;
                          ELSE
                              nextday := nextday + 1;
                          END IF;
  
                          gotit := TRUE;
                          foundval := TRUE;
                          EXIT;
                      END IF;
                  END LOOP;
              END IF;
          END IF;
  
          --
          -- Get the next run minute
          --
          -- If the year, month day or hour have incremented, get the lowest minute,
          -- otherwise look for the next minute matching or after the current one.
          IF (nextyear > date_part(''YEAR'', runafter) OR nextmonth > date_part(''MONTH'', runafter) OR nextday > date_part(''DAY'', runafter) OR nexthour > date_part(''HOUR'', runafter) OR daytweak = TRUE) THEN
              nextminute := 0;
              IF minutetweak = TRUE THEN
          d := 1;
              ELSE
          d := date_part(''YEAR'', runafter)::int2;
              END IF;
              FOR i IN d .. 60 LOOP
                  IF jscminutes[i] = TRUE THEN
                      nextminute := i - 1;
                      foundval := TRUE;
                      EXIT;
                  END IF;
              END LOOP;
          ELSE
              nextminute := date_part(''MINUTE'', runafter);
              gotit := FALSE;
              FOR i IN (nextminute + 1) .. 60 LOOP
                  IF jscminutes[i] = TRUE THEN
                      nextminute := i - 1;
                      gotit := TRUE;
                      foundval := TRUE;
                      EXIT;
                  END IF;
              END LOOP;
              IF gotit = FALSE THEN
                  FOR i IN 1 .. nextminute LOOP
                      IF jscminutes[i] = TRUE THEN
                          nextminute := i - 1;
  
                          -- Wrap into next hour
                          IF (nextmonth = 1 OR nextmonth = 3 OR nextmonth = 5 OR nextmonth = 7 OR nextmonth = 8 OR nextmonth = 10 OR nextmonth = 12) THEN
                              d = 31;
                          ELSIF (nextmonth = 4 OR nextmonth = 6 OR nextmonth = 9 OR nextmonth = 11) THEN
                              d = 30;
                          ELSE
                              IF pgagent.pga_is_leap_year(nextyear) = TRUE THEN
                                  d := 29;
                              ELSE
                                  d := 28;
                              END IF;
                          END IF;
  
                          IF nexthour = 23 THEN
                              nexthour = 0;
                              IF nextday = d THEN
                                  nextday := 1;
                                  IF nextmonth = 12 THEN
                                      nextyear := nextyear + 1;
                                      nextmonth := 1;
                                  ELSE
                                      nextmonth := nextmonth + 1;
                                  END IF;
                              ELSE
                                  nextday := nextday + 1;
                              END IF;
                          ELSE
                              nexthour := nexthour + 1;
                          END IF;
  
                          gotit := TRUE;
                          foundval := TRUE;
                          EXIT;
                      END IF;
                  END LOOP;
              END IF;
          END IF;
  
          -- Build the result, and check it is not the same as runafter - this may
          -- happen if all array entries are set to false. In this case, add a minute.
  
          nextrun := (nextyear::varchar || ''-''::varchar || nextmonth::varchar || ''-'' || nextday::varchar || '' '' || nexthour::varchar || '':'' || nextminute::varchar)::timestamptz;
  
          IF nextrun = runafter AND foundval = FALSE THEN
                  nextrun := nextrun + INTERVAL ''1 Minute'';
          END IF;
  
          -- If the result is past the end date, exit.
          IF nextrun > jscend THEN
              RETURN NULL;
          END IF;
  
          -- Check to ensure that the nextrun time is actually still valid. Its
          -- possible that wrapped values may have carried the nextrun onto an
          -- invalid time or date.
          IF ((jscminutes = ''{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'' OR jscminutes[date_part(''MINUTE'', nextrun) + 1] = TRUE) AND
              (jschours = ''{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'' OR jschours[date_part(''HOUR'', nextrun) + 1] = TRUE) AND
              (jscmonthdays = ''{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'' OR jscmonthdays[date_part(''DAY'', nextrun)] = TRUE OR
              (jscmonthdays = ''{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,t}'' AND
               ((date_part(''MONTH'', nextrun) IN (1,3,5,7,8,10,12) AND date_part(''DAY'', nextrun) = 31) OR
                (date_part(''MONTH'', nextrun) IN (4,6,9,11) AND date_part(''DAY'', nextrun) = 30) OR
                (date_part(''MONTH'', nextrun) = 2 AND ((pgagent.pga_is_leap_year(date_part(''YEAR'', nextrun)::int2) AND date_part(''DAY'', nextrun) = 29) OR date_part(''DAY'', nextrun) = 28))))) AND
              (jscmonths = ''{f,f,f,f,f,f,f,f,f,f,f,f}'' OR jscmonths[date_part(''MONTH'', nextrun)] = TRUE)) THEN
  
  
              -- Now, check to see if the nextrun time found is a) on an acceptable
              -- weekday, and b) not matched by an exception. If not, set
              -- runafter = nextrun and try again.
  
              -- Check for a wildcard weekday
              gotit := FALSE;
              FOR i IN 1 .. 7 LOOP
                  IF jscweekdays[i] = TRUE THEN
                      gotit := TRUE;
                      EXIT;
                  END IF;
              END LOOP;
  
              -- OK, is the correct weekday selected, or a wildcard?
              IF (jscweekdays[date_part(''DOW'', nextrun) + 1] = TRUE OR gotit = FALSE) THEN
  
                  -- Check for exceptions
                  SELECT INTO d jexid FROM pgagent.pga_exception WHERE jexscid = jscid AND ((jexdate = nextrun::date AND jextime = nextrun::time) OR (jexdate = nextrun::date AND jextime IS NULL) OR (jexdate IS NULL AND jextime = nextrun::time));
                  IF FOUND THEN
                      -- Nuts - found an exception. Increment the time and try again
                      runafter := nextrun + INTERVAL ''1 Minute'';
                      bingo := FALSE;
                      minutetweak := TRUE;
              daytweak := FALSE;
                  ELSE
                      bingo := TRUE;
                  END IF;
              ELSE
                  -- We''re on the wrong week day - increment a day and try again.
                  runafter := nextrun + INTERVAL ''1 Day'';
                  bingo := FALSE;
                  minutetweak := FALSE;
                  daytweak := TRUE;
              END IF;
  
          ELSE
              runafter := nextrun + INTERVAL ''1 Minute'';
              bingo := FALSE;
              minutetweak := TRUE;
          daytweak := FALSE;
          END IF;
  
      END LOOP;
  
      RETURN nextrun;
  END;
  ' LANGUAGE 'plpgsql' VOLATILE;
  
  COMMENT ON FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool) IS 'Calculates the next runtime for a given schedule';
  