Job scheduling with PHP - 4

In my previous post about Job scheduling with PHP , I described two entities the context and the schedule. The context is where all configurations and descriptions of source systems go. The schedule is the entity we schedule for execution. The entity where we describe the job we want to do is called job. Jobs are chained together in schedules and can be included in a schedule from a job library or explicitly declared directly in the schedule. Before we look at the job I need to explain return codes.

Return Codes

This is a bit  complicated, if you are not for details go directly to Summary .

Previous in my  posts on job scheduling with PHP I have explained why I use Boolean return codes (with a few exceptions). Normally you declare a schedule with mustcomplete=’yes’ , which means execution stops if any return code is FALSE. This is what you normally want, stop at point of failure, correct and rerun the  schedule.  But sometimes you need to cleanup or automatically fix the problem by executing error correcting jobs or you like to build schedules with logic like ‘if month end run job allocateNewMonth’. You do this by prereqs, a prereq is basically a Boolean gate that is either open or closed, a job prereq determines if a job should execute or not, if the prereq is FALSE the job is bypassed. This means that the result of a job is not strictly Boolean, it can successfully execute, fail or be bypassed. The ‘bypassed’ condition defaults to TRUE/success, you can change this by bypassed=’false’  in the schedule.

To allow for failures you turn off normal error checking by stating ‘ mustcomplete=no ’ in the schedule, then all error checking must be done explicitly in the schedule.    

Summary:  Job return codes are Boolean. If a FALSE return code is detected execution of the schedule is intercepted. This default behavior can be changed.

The Job Iterator

How many times must a job execute  to become a success? In most job control systems I have seen the answer is ONCE only. In my job scheduler the answer is - it defaults to zero or more times . By default a job is executed ONCE.  The job iterator determines how many times a job executes. The job iterator is a table and the job is executed once for each row, the job iterator is also a placeholder for symbolic variables a.k.a. @tags. Job iterators are immensely powerful, but for now the job iterator determines how many times a job executes and can contain @tags.  

The job iterator is declared by the xml tag <forevery> within a job.

The Job

The job is declared by the xml <job> tag.

A job is a unit of work consisting of five optional execution elements:

1.             init actions; (operation system commands) executed prior to the job type action.

2.             the job type action; which is an SQL script or a PHP script or function.

3.             nested jobs.

4.             exit actions; (operation system commands) executed after the job type action.

5.             guard action; a php script that executes in case of unsuccessful excecution of 1,2,3 or 4.

Job prereqs can be used to fine tune execution logic.

The example

Now this may seem awfully complicated but it is not. You only add what you need and that is almost always only an SQL script or a PHP script. Does this mean I always have to create SQL and PHP scripts? Yes and No. You write SQL scripts, but very seldom PHP scripts those you need are already written, e.g:

I want to create an HTML table report and mail it to Kalle Kula. The data is in Mysql table mytable.


This schedule consists of 2 jobs.

The first job creates the report formatted as HTML. The second job mails the job with the help of the prewritten PHP script sendmail.  

There are two tags in there THETABLE points to the result HTML table produced by job 1, and the second tag THECSS point to a prewritten CSS template file.

Now suppose Kalle tells us, ‘ Please send the data for sales area ‘uppsala’ to my colleague Peggy Piggelin and please send the reports as Excel sheets ’. We have to do some changes to our schedule, these changes can be done in several ways. I show you one way to do it:


As you can see we have added a dummy  job with a <forevery> job iterator which consists of two rows with the columns NAME,EMAIL and SALES areas. In this new dummy job we execute the two original jobs, first for row one in the iterator then for the second and last row. To change the output from an HTML table to an MS Excel sheet we only changed SQL converter.

In real life you store the recipients in a database table and create the job iterator with an SQL query. The report sql queries are probably not hard coded in the job but stored in files in a suitable directory.

The post PHP, MySQL, Iterators, Jobs ,Templates and Relation sets , explains templates and shows alternate iterators.

The post PHP parallel job scheduling - 1  explains how you can parallel execute jobs.

Other Examples can be found here .

PHP code

I end my post about my job scheduler by displaying sqlconverter_GoogleDocs01.php

This code uploads the SQL result table to Google docs. I think this is very cool : )

No comments:

Post a Comment