Hi, El 16/11/10 17:17, Thomas Mueller escribió: > I like the idea, however I would prefer not to add new syntax. Let's discuss > about what features are useful / needed first > I understand creating a backup of a database (file backup and / or the > script) automatically is useful. One feature that's > currently not possible is defragment a database (currently it's only > supported using 'shutdown defrag' but not yet while the database is > open). What else would be useful? I would like to have concrete use > cases (not abstract ones such as "call a method"). I have some concrete use cases of my own projects: Most of my project using H2 are Web apps with H2 in embedded mode. In this case used housekeeping and nightly tasks over embedded dbs in an application server environment become almost impossible. To mention a few:
1) Database related: Shutdown (or close all connections) to do Defrag and offline full Backup and Script, transfers to backup servers ; then start db. 2) Cleanup temporary and working files like csv used to export information, etc. 3) Nightly ETL tasks as data generation, importing, exporting and reporting. 4) Repetitive ETL and data synchronization tasks with other data sources at specified rate or times (or based on database triggers). 5) Monitoring external events to trigger some database activity 6) End of Month, Quarter, Year, etc. singular process at specific times. 7) Ability to schedule one-time-only user defined tasks Many of this task can be done with an external Scheduler without H2 integration, but if they are integrated benefit to persist the definitions of tasks, their schedule of shooting, logging and status. >> CREATE TASK [ SQL | SCRIPT | MODULE ] taskId AS [sqlText | scriptFilename >> | moduleName ] WHEN timeTriggerExpresion ; >> 3) As a new trigger type (TRIGGER ON TIMER) implementation. > This is new syntax I would like to avoid (unless there is a standard for it). AFAIK here aren't any std for this ; but google the term "SQL TIMER TRIGGER" or "SQL TRIGGER ON TIMER" , seems there are a lot of hungry people for something like that. Anyway it's perfectly avoidable. >> 1) Completely decoupled server that handle H2 at jdbc connection level. > I think it could be within a database. That could be an in-memory > database in theory, but you probably need to persist the configuration > (when to run what), so a regular database makes sense. Yes, and this is a facing problem when a desirable task is start H2 itself. Maybe some external startup point would be need but I'm not sure. >> 2) As an extension of actual DatabaseListener with a new TimerEvent every n >> millis. > As for "auto-backup", the "every n millis" is not user friendly. I > guess it would be more user friendly to support crontab syntax. I have > some code somewhere to parse that, including a simple scheduler. I > will check. I have almost done a Cron-Like expression handler for that cases. In my draft the Timer will have only one TimerTask to awakening the ScheduleProcessor on their on thread to do the real work. TimerTasks have restrictions about how long their are running without blocking the timer. SchedulerProcessor check scheduler repository on a regular basis searching for changes and tasks to be fired and act on them. At some point may be preferable replace TImer with concurrent.ScheduledThreadPoolExecutor to take advantage of Callable, Future and Executor interfaces; but right now I'm trying to do simplest and lighter possible implementation. I'm playing with implementation based on UDFs on a class with static initializer similar to FullTextSearch use of init( Connection ). Don't know yet if this level of integration will be enough to cover the desirable use cases. Regards, Dario -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
