I'd like to give it a go with the approach you suggested. Could you explain in a little detail (or refer me to any source) that explains what you mean by "no parsing SQL". I'm not quite sure what you mean by that. Also, do you have any other policies I should be aware of? I couldn't find a developer page or similar describing what they might be.
Thanks On Mar 24, 4:57 pm, Jeremy Evans <[email protected]> wrote: > On Mar 24, 11:56 am, dusty <[email protected]> wrote: > > > > > I am doing some multi_inserts of large sets of data using mysql and > > I'm going to need to support insert ignore and on duplicate key > > update. > > > So far here is what I came up with. These are some methods I added to > > a Sequel::Model class > > > def self.multi_insert_ignore(*args) > > def dataset.multi_insert_sql(columns,values) > > sql = super(columns,values) > > sql[0].gsub!(/^INSERT/,'INSERT IGNORE') > > end > > multi_insert(*args) > > end > > > def self.multi_insert_update(*args) > > def dataset.multi_insert_sql(columns,values) > > sql = super(columns,values) > > dup = columns.map {|column| "#{column}=VALUES(#{column})"}.join > > (', ') > > sql[0] << " ON DUPLICATE KEY UPDATE #{dup}" > > end > > multi_insert(*args) > > end > > > Examples > > > INSERT IGNORE INTO `animals` (`name`, `kind`) VALUES ('sally', > > 'monkey'), ('bob', 'bird') > > INSERT INTO `animals` (`name`, `kind`) VALUES ('sally', 'monkey2'), > > ('bob', 'bird2') ON DUPLICATE KEY UPDATE name=VALUES(name), kind=VALUES > > (kind) > > > You can see my quick usage of it and the logs with it here > > >http://gist.github.com/84287 > > > Does that approach seem reasonable? If I were to fork sequel to add > > those methods, what class/module does that belong in? I'm assuming it > > should go somewhere in a mysql specific class. > > Your code looks OK, but it can't go into Sequel in it's current > status. One reason for this is you areparsing SQLin > multi_insert_sql, and Sequel has a strict "noparsing SQL" policy. > Also, in both multi_insert_sql overrides, you should be cloning the > dataset and then both overriding the method and calling multi_insert > on the clone. > > Because the API for multi_insert sucks, the best way to add support > for this properly is to add dataset methods to set flags in a clone: > > module Sequel::MySQL::DatasetMethods > def multi_insert_ignore(*args) > clone(:multi_insert_ignore=>true).multi_insert(*args) > end > > def multi_insert_update(*args) > clone(:multi_insert_update=>true).multi_insert(*args) > end > > def multi_insert_sql(columns, values) > # Deal with :multi_insert_update > # and :multi_insert_ignore flags > end > end > > I wouldn't have a problem considering such a patch for inclusion in > Sequel, assuming it is generic. It looks like the INSERT IGNORE is > generic, but I'm not sure if the update is, as different users may > want different behavior. Maybe there should be a way to set the value > of the ON DUPLICATE KEY UPDATE clause? I wouldn't mind the current > behavior as a default. > > I encourage you to convert your patch to the format above and submit > it for inclusion in Sequel. It's much better than managing a fork or > extension. If you choose to go the fork/extension route, you just > want to add the methods to the Sequel::MySQL::DatasetMethods module. > > Thanks, > Jeremy --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
