Simon Carter wrote:

>Recently someone said that if you have a proposal, throw it on this
>list. So, my first proposal is FIST (FIrebird Scheduled Tasks). I
>have a brief description of it below, which outlines how I would like
>to see it. I appreciate it may be technically impossible/improbable,
>and has more holes than your average tea bag, but you never know until
>you ask :-)
>
>
>

I've got a similar feature in Netfrastructure that gets very heavy use
for backups, replication, calendar event notification, report
generation, etc. Netfrastructure is a different environment, but let me
sketch the interface for reference.

A scheduled task is registered with the Connection method updateSchedule:

public void updateSchedule (String appName, String eventName,
String schedule);

The string "schedule" is the cron syntax extended to include seconds
(yes, it's confusing, but I can blame someone else for it). If the
schedule string is null, the schedule is cancelled. The actual task is
executing by calling the method

public void scheduled (Connection cnct, String event) throws
Exception

in the <appName> application class. The connection passed to the
scheduled task handler is the same of the account that originally
scheduled the task.

>Due to my lack of internal engine knowledge I am not going to try and
>describe how to implement FIST, instead I will concentrate on the
>features that I think are important. I hope that other users can help
>fill in the gaps and provide extra requirements so as FIST can be a
>useful enhancement to Firebird.
>
>

The design can (and should) follow agreement on the requirements (which,
incidentally, are a terrific first pass).

>
>2. Scope.
>
>FIST should have the ability to be system wide, or Database wide.
>Where a task is system wide then any element of the FIST should
>specify the database name.
>
>

We don't have a concept of system wide, nor, since the engine doesn't
know about individual database until one is attached, could it.
Scheduled tasks should be database specific. The security problems
involved in scheduling tasks in other databases are almost certainly
unsolveable. And, I think, unnecessary.

>
>3. Transactions.
>
>Each FIST should be contained within its own transaction, if for any
>reason a FIST can not be succesfully executed then the complete
>transaction should be rolled back. Otherwise the transaction should
>be commited.
>
>

How does the engine know if the scheduled task completed successfully?
What if the task needs to start several transactions? Wouldn't it make
sense to require the task to commit and rollback if it failed to do so?

>
>4. Schedules.
>
>FIST should empower DBAs, Developers and users with the ability to:
>
> a. Initiate a task as soon as the engine is started.
> b. Initiate a task during idle CPU time.
> c. Create a one off task for execution on a specified date/time.
> d. Create Recurring tasks.
> e. Initiate a task when a user connects/disconnects from a database.
> f. Ability to disable/enable tasks.
>
>

I don't see the need or the desirability for these. Can you give some
scenarios?

>
>4a. Recurring Tasks
>
>Recurring tasks need to have fine grained functionality to ensure
>their usefulness, this can include:
>
> a. Daily.
> b. Weekly, with the ability to select the days of the week to
>execute.
> c. Monthly, with the ability to select the Nth Day (1 - 31 or
>Mon, Tue etc).
>
>

You need more than this. Daily isn't good enough -- you need to specify
when. Backups, for example, are best done when everyone reasonable is
asleep.

>Each recurring task should have an optional begin/end date, if no
>begin/end date is specified then the assumption should be made that
>the task will always be executed.
>
>

Wouldn't it be simpler to run a task regularly until explicitly cancelled?

>Recurring tasks should also have the ability to be scheduled once per
>period at a specified time, or scheduled to run at different, user
>specified intervals. For example:
>
>A daily task could be sheduled to execute every n minutes or n hours
>within an optional user specifed time period. i.e. Every 15 minutes
>between 0800 hrs and 1700 hrs.
>
>

The cron syntax handles this well -- ugly, ok, but perfectly useable.
Even Paul Beach eventually mastered the tortured syntax, and he's a
marketing guy.

>
>5. Steps.
>
>Each task should be able to execute 1..n steps (procedures or code
>blocks) in a user specified order, the ability to re-order steps
>should be available.
>
>Each step, should at a minimum have the abiltiy to specify:
>
> a. The name of the step.
> b. The database, if system wide.
> c. The name of the user who's profile is used to execute the step.
> d. The SQL block to execute. Can specify a Stored Proc and its params.
> e. Optionally specify the number of attempts to execute a step,
>incase of failure. This limit should be capped to something like 3
>for performance reasons.
>
>

Again, this is more complexity than is needed. It would be simpler to
call a procedure and let it do the step management, send email, error
handling, etc. With native Java procdures, this would be a snap.

>6. Notifications.
>
>When a FIST completes an optional method of notification should be
>issued. This should include:
>
> a. Ability to email a user.
> b. Ability to add an entry to a log file.
>
>
>
>

Let the task procedure handle this. There's no reason to build it into
the infrastructure.

>7. Auto Delete.
>
>Each FIST should be able to delete its self, if its optional end
>date/time has been exceeded.
>
>

How about if it justs deletes itself if it feels like it.

>
>8. Inclusion.
>
>Each database should have the ability to enable/disable the use of
>FIST for its self. This could enable DBAs to take the database
>off-line, or save resources by indicating to FIST that it should not
>touch the database.
>
>

This is important. From experience, it's almost impossible to debug a
database engine with tasks popping off at regular intervals.

>
>9. Management.
>
>For ease of use, management of FISTs (adding/updating/deleting) should
>be done using SQL, for instance, you could use something like:
>
>-- create a task
>
>CREATE OR ALTER TASK TASKNAME FOR [DATABASENAME/SYSTEM [RUNBY USERNAME]]
> ADD DAILY 08:00 [RECURRING EVERY 15 MINUTES [BETWEEN DATETIME AND
>DATETIME]]
> STEP [STEPNUMBER] AS
> BEGIN
> EXECUTE PROCEDURE MY_PROCEDURE (PARAM, PARAM);
> END;
>
>

How about:

CREATE TASK <name> PROCEDURE <procname> SCHEDULE <schedule>

The only can want to handle security is for the task to run as the
account that scheduled it. Leave everything else to the task. I
suggest you pass the task its name, however.


This an excellent start. I agree with your functionality, but disagree
on the division of work between the scheduler and the task handler. I'd
rather see the scheduler schedule tasks, period, leaving task semantics
strictly up to the task/procedure. I also think you have to make a
case for non-times tasks.