On Thu, Mar 24, 2011 at 2:18 PM, Lars Olsson <[email protected]> wrote:
> Hi,
>
> Recently I've been playing around with destroy hooks in Sequel::Model.
> Everything works as expected, but the qenerated SQL seems a bit
> inefficient. Lets say I have this:
>
> ### Start code
> require 'logger'
> require 'sequel'
>
> DB_NAME = 'foobar.db'
>
> # Remove old db (if it exists)
> File.delete(DB_NAME) if File.exists?(DB_NAME)
>
> # Create new (sqlite) db
> DB = Sequel.sqlite(DB_NAME, :loggers => Logger.new($stdout))
>
> # Allow table creation from within the model
> Sequel::Model.plugin(:schema)
>
> class Foo < Sequel::Model
> self.db = DB
> self.set_schema do
> primary_key :id
> text :s1
> text :s2
> end
> self.create_table unless self.table_exists?
>
> def before_destroy
> self.bars.each do |bar|
> bar.destroy
> end
> end
> end
>
> class Bar < Sequel::Model
> self.db = DB
> self.set_schema do
> primary_key :id
> text :s1
> integer :i1
> foreign_key :foo_id
> end
> self.create_table unless self.table_exists?
> end
>
> # Associations
> Foo.associate :one_to_many, :bars, :class => Bar, :key => :foo_id
> Bar.associate :many_to_one, :foo, :class => Foo, :key => :foo_id
>
> # Create some Foos and Bars
> foo_1 = Foo.new
> foo_1.s1 = 'foo_1'
> foo_1.s2 = 'foo_1'
> foo_1.save
>
> bar_1 = Bar.new
> bar_1.s1 = 'bar_1'
> bar_1.i1 = 1
> bar_1.foo = foo_1
> bar_1.save
>
> bar_2 = Bar.new
> bar_2.s1 = 'bar_2'
> bar_2.i1 = 2
> bar_2.foo = foo_1
> bar_2.save
>
> bar_3 = Bar.new
> bar_3.s1 = 'bar_3'
> bar_3.i1 = 3
> bar_3.foo = foo_1
> bar_3.save
>
> bar_4 = Bar.new
> bar_4.s1 = 'bar_4'
> bar_4.i1 = 4
> bar_4.foo = foo_1
> bar_4.save
>
> # Print all Bars
> puts Bar.all.inspect
>
> # Destroy the Foo
> foo_1.destroy
>
> # Print all Bars
> puts Bar.all.inspect
>
> ### End code
>
> Whenever I remove a Foo I also want to remove all associated Bars. The
> generated SQL code (for the before_destroy hook) looks like this:
>
> I, [2011-03-24T14:11:20.784272 #7766] INFO -- : (0.000056s) BEGIN
> I, [2011-03-24T14:11:20.784996 #7766] INFO -- : (0.000303s) SELECT *
> FROM `bars` WHERE (`bars`.`foo_id` = 1)
> I, [2011-03-24T14:11:20.785629 #7766] INFO -- : (0.000181s) DELETE
> FROM `bars` WHERE (`id` = 1)
> I, [2011-03-24T14:11:20.786088 #7766] INFO -- : (0.000089s) DELETE
> FROM `bars` WHERE (`id` = 2)
> I, [2011-03-24T14:11:20.786528 #7766] INFO -- : (0.000088s) DELETE
> FROM `bars` WHERE (`id` = 3)
> I, [2011-03-24T14:11:20.786966 #7766] INFO -- : (0.000083s) DELETE
> FROM `bars` WHERE (`id` = 4)
> I, [2011-03-24T14:11:20.787305 #7766] INFO -- : (0.000142s) DELETE
> FROM `foos` WHERE (`id` = 1)
> I, [2011-03-24T14:11:20.802552 #7766] INFO -- : (0.015104s) COMMIT
>
> As seen in the trace, the before_destroy hook generates four SQL
> calls, but ideally it should need just one:
> DELETE FROM `bars` WHERE (`foo_id` = 1)
>
> Is there a way to accomplish this within Sequel::Model? A
> Foo.destroy_all_bars method or something?
>
Hi Lars,
yes there is. I believe I mentioned in an earlier email.
In your Foo Sequel::Model, add the following line:
plugin :association_dependencies, :bars => :delete
Important: it's really :delete, not :destroy. By doing so, when destroying
I would strongly suggest to refactor your code so that the
Sequel::Model contains all the above mentioned business logic.
I would do it like this:
class Foo < Sequel::Model
one_to_many :bars, :class => Bar, :key => :foo_i
plugin :association_dependencies, :bars => :delete
plugin :schema
set_schema do
primary_key :id
text :s1
text :s2
end
create_table unless table_exists?
end
class Bar < Sequel::Model
:many_to_one, :foo, :class => Foo, :key => :foo_id
plugin :schema
set_schema do
primary_key :id
text :s1
integer :i1
foreign_key :foo_id
end
create_table unless table_exists?
end
My 2 cents.
--
Christian
--
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.
