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 :-)

Rgds

Si

FIrebird Scheduled Tasks (FIST)

1. Introduction

I believe that one element missing from Firebird is the ability to
nativly run scheduled tasks against any database. My definition of a
scheduled task, for the purpose of this document is:

~ The ability to run a stored procedure or execute a block of code
within a database at a predetermined time/date.

Being able to schedule tasks directly within a database would, imo,
enhance the Firebird brand by empowering DBAs and software Developers
to interact and organize their data in a reliable and efficient manner.

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.


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.


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.


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.


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).

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.

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.


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.

Each step, if multiple steps are specified, should be dependant on the
previous step succesfully completing. For instance, if you have a 2
step FIST, and the first step fails to execute then the second, and
each subsequent step should not be executed.


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.



7. Auto Delete.

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


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.


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;

-- delete a task

DROP TASK TASKNAME;

-- alter a task, add a step

ALTER TASK TASKNAME
ADD STEP [STEPNUMBER] AS
BEGIN
DELETE FROM MY_TABLE WHERE SOME_DATE < CURENT_DATE - 10;
END;

-- alter a task, remove step

ALTER TASK TASKNAME
DROP STEP [STEPNUMBER];