--- In Firebird-Architect@yahoogroups.com, Jim Starkey <jas@n...> wrote:

> 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.
>
>
> The design can (and should) follow agreement on the requirements

(which,

> incidentally, are a terrific first pass).

Thankyou, I fully agree that the design should follow an agreement on
requirements. And the goodwill of a developer to pick this up and run
with it, as it were.

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

Fair enough.

> How does the engine know if the scheduled task completed successfully?

Procedure could raise an exception or return a valid result code (int
0 or whatever)

> 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?

If PSQL had support for start/commit/rollback this would not be an
issue, in the interests of simplicity I would say 1 tran per task.
Once again though, its a good point for discussion.

> >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?

a, b, e. Not sure at the moment, TBH. Maybe/maybe not a good
example, but some one in the future might ask for it.

c. If I was asked to produce a one off report, scheduled for a
specific date time I could create the task and ignore it until run.

d. Nightly maintenance, data pruning/validation etc etc.

f. I was thinking of this in similar terms to a trigger, where it
could be ACTIVE/INACTIVE.

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

In the original doc:

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.

So you would definately need to include an execution date/time.

> >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?


For simplicity maybe, but you might want to update or manipulate
records daily between 0800hrs and 1700hrs whilst your employees are on
site, rather than continuously. Just a thought.

> Even Paul Beach eventually mastered the tortured syntax, and he's a
> marketing guy.

LOL

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

Agreed.

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

Maybe, would need to add UDF though so you could send an email or
write a log entry etc. Personally though, I think notification is
important.

> >9. Management.
> How about:
>
> CREATE TASK <name> PROCEDURE <procname> SCHEDULE <schedule>
>

Even better, much simpler and easier to use :-)

Perhaps adding another one

EXECUTE TASK <name>;

> I also think you have to make a case for non-times tasks.

I don't fully understand this, do you mean manually execute a
procedure as and when need arises?

rgds

Si