Greetings
I am new to Sequel and one feature that brings me to it is batch
imports - they are simply absent in other ruby ORM, so I ended up hand
crafting very long SQL strings and executing them, and that made me
grouchy over time :)
I really like Sequel batch importing feature, namely multi_insert
method (in addition to it being great simple ruby DSL for SQL).
Nevertheless, I find that its return value (nil) is not right.
It would be much more helpful if it would return array of primary keys
of rows just inserted.
Let me explain myself.
Lets assume that there are 3 tables in a system: users, addresses, and
users_to_addresses (many-to-many relation). This is just hypothetical
example, so please bear with me, despite weirdness of this design.
Script would be trying to insert 1M of users, 1M of addresses, and
then link them together pairwise. If you feel like there should be
more justification for many-to-many relation, make in 3M of addresses,
and assign 3 addresses for each user, one for home address, one for
work address, and one ... for summer vacation house.
It would be most convenient to create first 1M of users, getting back
their ids as a result.
Then do same with addresses. Then use the ids returned in the previous
steps to create third batch import query that links users and
addresses.
As it is now, after 1M of users are created, a query must me concocted
that will return id's of those users. That is inconvenient at least,
and in many cases is very difficult/not possible.
I am not sure about all databases, but PostgreSQL allows for batch
imports like "insert into foo (bar, baz) values (1,2), (3,4) returning
id". That query does exactly what is needed, it returns id's of
records created.
Not sure if similar functionality exists in other databases. If not,
it could behave this way for databases supporting such functionality,
and return nil for databases that do not.
It would not be the first or last method like that (here is excerpt
from DataSet.insert rubydoc: "The returned value is generally the
value of the primary key for the inserted row, but that is adapter
dependent").
What do you think, guys, would that be useful addition to Sequel?
By the way...
I "prototyped" the change in form of monkey-patch, that is working for
PostgreSQL database (it is not going to behave nicely when other
database is used), and does exactly what I wanted, but it would be
nice to have this functionality integrated in a way that could benefit
everybody. I just do not understand Sequel architecture enough to get
this code work right for all databases.
Here is the code:
module Sequel
class Dataset
def import(columns, values, opts={})
return @db.transaction{insert(columns, values)} if values.is_a?
(Dataset)
return if values.empty?
raise(Error, IMPORT_ERROR_MSG) if columns.empty?
rows = []
if slice_size = opts[:commit_every] || opts[:slice]
offset = 0
loop do
@db.transaction(opts) do
multi_insert_sql(columns, values[offset, slice_size]).map
do |st|
returning_fetch_rows(st + ' ' + RETURNING + ' ' +
insert_pk.to_s(self)) do |r|
rows << (r.size == 1 ? r.values.first : r)
end
end
end
offset += slice_size
break if offset >= values.length
end
else
statements = multi_insert_sql(columns, values)
@db.transaction do
statements.map do |st|
returning_fetch_rows(st + ' ' + RETURNING + ' ' +
insert_pk.to_s(self)) do |r|
rows << (r.size == 1 ? r.values.first : r)
end
end
end
end
rows
end
end
end
--
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.
