Em 04-10-2013 19:13, Jeremy Evans escreveu:
On Friday, October 4, 2013 2:36:33 PM UTC-7, Rodrigo Rosenfeld Rosas
wrote:
It would be great if this would work the way most would expect it
to behave:
ids = MyModel.dataset.returning(:id).import [:a, :b], [[1, 2], [3,
4]] # => [id1, id2]
Could you please consider this for some future version?
Looks like it isn't currently documented (I'll fix that), but you can
use the :return=>:primary_key option to do so starting in Sequel 3.31.0.
Great, good to know. I'll change my manual import statement back to
using #import with those options on Monday.
Most people using #import/#multi_insert probably don't care about the
inserted ids, and using the option can make things slower, which is
why it doesn't operate that way by default.
I totally understand and I was even wondering yesterday if the returning
clause (even "returning null") could slow down the import statement a
little, even if you are not fetching the returned values...
Let me try to illustrate my confusing database design and application
goals to something simpler (although I can't see anyone actually mapping
a music sheet to a RDMS this way :P ), so that I could explain why the
returning clause is useful for my situation.
Suppose you want to import a song score from some JSON, where the score
contains measures, which contains notes:
tables:
- song_scores(id, title)
- measures(id, score_id, position, cleff)
- notes(id, measure_id, pitch, duration)
You could even scale the problem if you want to import a songbook by
adding a songbook_id to song_score.
I realize this model is rather incomplete but it doesn't matter as you
wouldn't want to represent a music sheet this way anyway :) Also, the
notes should have a position column too, but let's ignore that...
So, suppose your import procedure accepts a JSON like this (mine is
actually more compact, but it shouldn't matter):
{
"title": "Naquele Tempo",
"measures": [
{
"cleff": "g",
"notes": [
{"pitch": "C", duration: 2},
...
]
},
...
]
}
In my application I actually have to merge this JSON, not only insert in
the database. So how would you design such a batch merge strategy on
PostgreSQL for something like the above case?
Here's my strategy (I actually got it working yesterday, so I know it
works):
In our application, there's something like a unique constraint over
measures(score_id, position) which complicates it even more, so I
decided to simplify it.
To keep it simpler I decided to delete all measures and notes for the
song if it exists (take the title as unique, for instance, but in my
case it's actually an unique foreign key) and start the procedure from
there.
So basically, this is how it works in a conceptual level:
1 - start a transaction
2 - lock tables song_scores, measures and notes for exclusive access
3 - delete all records in all those tables for the imported score (in my
application I don't have to delete the first level and will apply a
merge strategy instead, but what I'm trying to do here is to explain how
I'm using the "returning id" values to simplify my import procedure)
4 - generate an array with the values for measures, including the
position based on some each_with_index block
5 - import the measures and store their ids. They will be in the same
order of the previous array
6 - use the returned ids to be able to import the notes
7 - commit the transaction
Let me detail a bit how 4 works:
score_id = find_or_create_score.id
measures = []
all_notes = []
json['measures'].each_with_index do |measure, position|
measures << [score_id, position, measure['cleff']]
all_notes << measure['notes'] # this is required in my cases because
I'll skip some values earlier in the loop for some conditions
end
measure_ids = DB[:measures].import [:score_id, :position, :cleff],
measures, return: :primary_key
notes_values = []
all_notes.each_with_index do |notes, i|
measure_id = measure_ids[i]
notes.each {|n| notes_values << [measure_id, n['pitch], n['duration']] }
end
DB[:notes].import [:measure_id, :pitch, :duration], notes_values
Wow, I wrote way more I had originally planned to :P
Hopefully you understand how returning the ids simplified my import
procedure a lot :)
Have a nice weekend!
Cheers,
Rodrigo.
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/groups/opt_out.
