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 are parsing SQL in
multi_insert_sql, and Sequel has a strict "no parsing 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
-~----------~----~----~----~------~----~------~--~---
