I had to develop a solution for a similar need myself. The most robust approach I found was to insert the new filename into a table of files and get a unique id from that table and keep track of status of loading the data (new, loaded), etc. The file_id was then the foreign key in the data table. With that, a load becomes:
current_file_id = DB[:csv_files].insert(:filename => filename, :created_timestamp => Time.now, :load_status => "NEW") DB[:data].filter(:file_id => current_file_id).delete begin # loop through CSV and add the rows to the data table. DB[:csv_files].filter(:id => current_file_id).update(:load_status => "COMPLETE") rescue DB[:data].filter(:file_id => current_file_id).delete raise end I record other things such as number of rows in the csv file and check that I have same number of rows in the data table as noted when the csv file was parsed. I also timestamp it so that I can expire data from the data table over time (to keep it from growing indefinitely). Basically, when you don't have control of your database, you have to take control of the logic and the problem space rather than bank on something every database should support, but might not support in quite the same way. Either that or abstract the differing functionalities away with a library (like Sequel). ;-) Regards, Michael On Wed, Oct 19, 2011 at 4:51 AM, Stefan Rohlfing <[email protected]> wrote: > Hi Gary, > You are right, implementing some kind of transaction behavior can only bring > you so far. > After adding >> >> Sequel::MySQL.default_engine = 'InnoDB' > > to my code, transactions are working now with MySQL (of course, this only > works if the table had not been created yet). > > Stefan > > -- > You received this message because you are subscribed to the Google Groups > "sequel-talk" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sequel-talk/-/a4Gm4cubw5oJ. > 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/sequel-talk?hl=en. > -- http://codeconnoisseur.org -- You received this message because you are subscribed to the Google Groups "sequel-talk" 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/sequel-talk?hl=en.
