autorunner-schema.sql

schema

    CREATE TABLE autorun_agent (
        id                      INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        agent_id                VARCHAR(64) NOT NULL,
        on_pre_run_exec         VARCHAR(255) NOT NULL DEFAULT '',
        on_post_run_exec        VARCHAR(255) NOT NULL DEFAULT '',
        on_fail_run_exec        VARCHAR(255) NOT NULL DEFAULT '',
        last_modified           TIMESTAMP,
        UNIQUE KEY (agent_id)
    ) ENGINE=InnoDB;
        
        
    CREATE TABLE autorun_exec (
        id                      INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        agent_id                VARCHAR(64) NOT NULL,
        exec_name               VARCHAR(64) NOT NULL,
        bin                     VARCHAR(255) NOT NULL,
        arg_template            VARCHAR(255) NOT NULL,            
        last_modified           TIMESTAMP,
        KEY (agent_id),
        KEY (exec_name),
        UNIQUE KEY (agent_id, exec_name),
        FOREIGN KEY (agent_id) REFERENCES autorun_agent (agent_id) ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB;
        

    CREATE TABLE autorun_scheduled_item (
        id                      INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        agent_id                VARCHAR(64) NOT NULL,
        item_priority           INTEGER NOT NULL DEFAULT 0,
        item_name               VARCHAR(64) NOT NULL,
        exec_name               VARCHAR(64) NOT NULL,
        arg1                    VARCHAR(255) NOT NULL DEFAULT '',
        arg2                    VARCHAR(255) NOT NULL DEFAULT '',
        arg3                    VARCHAR(255) NOT NULL DEFAULT '',
        arg4                    VARCHAR(255) NOT NULL DEFAULT '',
        arg5                    VARCHAR(255) NOT NULL DEFAULT '',
        min_runs                INTEGER UNSIGNED NOT NULL,
        max_runs                INTEGER UNSIGNED NOT NULL,
        date_start              DATE NOT NULL,
        date_end                DATE NOT NULL,
        time_start              TIME NOT NULL,
        time_end                TIME NOT NULL,
        active                  BOOLEAN NOT NULL,
        last_modified           TIMESTAMP,
        UNIQUE KEY (agent_id, item_name),
        FOREIGN KEY (agent_id) REFERENCES autorun_agent (agent_id) ON DELETE RESTRICT ON UPDATE CASCADE,
        FOREIGN KEY (exec_name) REFERENCES autorun_exec (exec_name) ON DELETE RESTRICT ON UPDATE CASCADE,
        KEY (agent_id),
        KEY (exec_name),
        KEY (min_runs),
        KEY (max_runs),
        KEY (date_start),
        KEY (date_end),
        KEY (time_start),
        KEY (time_end),
        KEY (active)
    ) ENGINE=InnoDB; 


    CREATE TABLE autorun_runcount (
        id                      INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        scheduled_item_id       INTEGER UNSIGNED NOT NULL,
        run_date                DATE NOT NULL,
        run_count               INTEGER UNSIGNED NOT NULL,
        last_modified           TIMESTAMP,
        UNIQUE KEY (scheduled_item_id, run_date),
        FOREIGN KEY (scheduled_item_id) REFERENCES autorun_scheduled_item (id) ON DELETE CASCADE ON UPDATE CASCADE,
        KEY (run_date),
        KEY (run_count)
    ) ENGINE=InnoDB;
        

    autorun_agent_startedjob
        id
        agent_id
         





------------
-- get next job
------------
-- mysql specific

select *
from
    autorun_scheduled_item si left join autorun_runcount rc 
        on  ( (rc.scheduled_item_id=si.id) and (rc.run_date='20100219') )

where 
    (ifnull(rc.run_count,0)<si.max_runs) 
    and (si.date_start<='20100219') and (si.date_end>='20100219')
    and (si.time_start<=time(now())) and (si.time_end>=time(now()))

order by ifnull(rc.run_count,0) asc, si.item_priority ASC, si.id asc