On Mon, 18 May 2009 12:17:25 -0700 (PDT), Allen Fowler <allen.fow...@yahoo.com> wrote:
> >> >I have several CGI and cron scripts and that I would like coordinate via a > >> "First In >> >/ First Out" style buffer. That is, some processes are adding work >> >units, and some take the oldest and start work on them. >> > >> >Could SQLite be used for this? >> > >> >> For what it's worth, here you go. >> Perhaps you can borrow a few ideas from it. >> > > >Thank you for posting the code. >I'll try to look through it. > (Like I said, I've never used complex SQL before... and for me this is > complex.) >Can you point me to the part that takes care of making >an atomic removal of a task from the queue, such that >one and only one worker process can get access to a task? >That's what's got me stumped. It's not guaranteed here, I think. The code is used on a site with very low concurrency. My 'solution' has only one worker, the dispatcher. I use schtask.exe to schedule dispatchers, it was the only way I could find to run something on windows outside the context of Apache/PHP. (the at utility would have been better, but it wasn't available to my account profile). Every time a new job is submitted, any previously scheduled dispatchers (which don't run yet) are removed from the scheduler queue. Then the new dispatcher is scheduled to run. Once it starts, the dispatcher runs all waiting jobs it can find, one by one, and exits when all jobs are done. In hindsight I don't like my code that much ;) So I guess this doesn't solve your problem. On Linux/Unix, you could implement a similar dispatcher, which would be the only process which removes tasks from the sqlite queue and starts each task as a background job. >The simple solution would just create a race condition... i think: > >1) INSERT INTO status_table FROM SELECT oldest task in queue >2) DELETE task in queue > >Right? It might work fine if you wrap it in an exclusive transaction. >Thank you, >AF > > > >P.S. > >Am I correct to assume your code is a more flashed out version of what I was >trying to do before.... > >Table: task_log => (id, task_data, time_stamp) >Table: task_fifo = > (id, fk_task_log) >Table: task_status_log => (id, fk_task_log, status_code, time_stamp) > >And in psudo SQL: > >TRIGGER ON INSERT INTO task_log: >BEGIN > INSERT INTO task_fifo (fk_task_log) VALUES (NEW.id) >END; > >TRIGGER ON DELETE FROM task_fifo: >BEGIN > INSERT INTO task_status_log VALUES (OLD.fk_task_log, "CLAIMED") >END; > > >And then, again in psudo SQL, the worker does something like: > >DELETE 1 OLDEST FROM task_fifo; I don't think it is exactly the same. -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
