If you are open to non RDBMS solution, sounds like what you need is message queue system.
At work, we use RabbitMQ (memory-only) and have been quite happy with it. SecondLife posted their discovery about MQ here: http://wiki.secondlife.com/wiki/Message_Queue_Evaluation_Notes - Didip - On Mon, Jun 29, 2009 at 8:51 PM, allen.fowler <[email protected]>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. > > > > > Since I need the queue to both survive system crashes and provide an > > > audit record of all work-units in/out, people have suggested using an > > > ACID compliant databse for the task. (SQLite or MySQL depending on > > > volume). > > > > > Can SQLAlchemy be used for the task? > > > > > Any help would be appreciated, as I a new to SQL and SQLAlchemy. > > > > Sounds reasonable and fairly simple to do. > > > > I think you would need two tables. > > One for the tasks to be done an another for tasks completed (the > > history you wanted) > > > > It sounds like you would need the following functionality > > * Insert record - Adding records to the que > > * Qurey record - Finding the record that needs doing. > > * Deleting record - Remove the task from the current_tasks table when > > the task is complete. > > > > I'd use the current date+time as a key for the current_tasks table so > > you can query and return the first (session.query....first(), not all > > ()) record with the lowest date (as you may occasionally have more > > than one entry with the exact same date) > > > > SQLAlchemy is defiantly capable of doing all of these things and > > provides awesome ORM functionality so you don't have to construct any > > SQL - Look at declarative base > > rough example here : > http://www.sqlalchemy.org/docs/05/ormtutorial.html#creating-table-cla... > > more details here : > http://www.sqlalchemy.org/docs/05/reference/ext/declarative.html > > > > Good luck. > > > > > OK... I am going to try implement something like this using SQA & > sqlite > > (Has anyone done this FIFO buffer / message channel system in SQL > Alchemy before? I hate to re-invent the wheel.) > > The first problem will am grappling with is a way to atomically > retrive an item from the queue. > > After some asking around on the sqlite mailing list: > http://www.nabble.com/forum/ViewPost.jtp?post=23590920&framed=y > > It seems the solution is to use a "BEGIN EXCLUSIVE TRANSACTION; " to > warp the parts of the code that do the FIFO stuff. > > How would I do this in SQLAlchemy? (With out affecting the rest of my > code that uses the regular session/transaction system.) > > Thank you > :) > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" 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/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
