On Monday, September 3, 2012 8:21:55 PM UTC-7, Chris Hanks wrote:
>
> I'm working on expanding the association_pks plugin to work with composite
> primary keys. It's going well, but I don't know sqlite very well, and I'm
> stymied by some syntax errors I'm getting while trying to make the
> integration tests pass.
>
> In a nutshell, the problem is (taken from a debugging session in the
> plugin):
>
> irb(#<Vocalist:0x000000027d09b8>):044:0> ds =
> _join_table_dataset(opts).filter(opts[:left_key]=>[Array(opts[:left_primary_key]).map{|k|send(k)}])
> => #<Sequel::SQLite::Dataset: "SELECT * FROM (`vocalists_instruments`)
> WHERE (((`first` = 'F1') AND (`last` = 'L1')))">
> irb(#<Vocalist:0x000000027d09b8>):045:0> ds.all
> => [{:first=>"F1", :last=>"L1", :instrument_id=>1}, {:first=>"F1",
> :last=>"L1", :instrument_id=>2}, {:first=>"F1", :last=>"L1",
> :instrument_id=>3}]
> irb(#<Vocalist:0x000000027d09b8>):046:0> ds.delete
> Sequel::DatabaseError: SQLite3::SQLException: near "(": syntax error
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/sqlite3-1.3.6/lib/sqlite3/database.rb:91:in
>
> `initialize'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/sqlite3-1.3.6/lib/sqlite3/database.rb:91:in
>
> `new'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/sqlite3-1.3.6/lib/sqlite3/database.rb:91:in
>
> `prepare'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/sqlite3-1.3.6/lib/sqlite3/database.rb:223:in
>
> `execute_batch'
> from /home/chris/sequel/lib/sequel/adapters/sqlite.rb:179:in `block (2
> levels) in _execute'
> from /home/chris/sequel/lib/sequel/database/logging.rb:33:in `log_yield'
> from /home/chris/sequel/lib/sequel/adapters/sqlite.rb:179:in `block in
> _execute'
> from /home/chris/sequel/lib/sequel/database/connecting.rb:229:in `block in
> synchronize'
> from /home/chris/sequel/lib/sequel/connection_pool/threaded.rb:92:in `hold'
> from /home/chris/sequel/lib/sequel/database/connecting.rb:229:in
> `synchronize'
> from /home/chris/sequel/lib/sequel/adapters/sqlite.rb:167:in `_execute'
> from /home/chris/sequel/lib/sequel/adapters/sqlite.rb:128:in `execute_dui'
> from /home/chris/sequel/lib/sequel/dataset/actions.rb:773:in `execute_dui'
> from /home/chris/sequel/lib/sequel/dataset/actions.rb:132:in `delete'
> from /home/chris/sequel/lib/sequel/adapters/shared/sqlite.rb:488:in
> `delete'
> from (irb):46:in `block (2 levels) in def_many_to_many'
> ... 22 levels...
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/example.rb:80:in
>
> `instance_eval'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/example.rb:80:in
>
> `block in run'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/example.rb:173:in
>
> `with_around_hooks'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/example.rb:77:in
>
> `run'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/example_group.rb:355:in
>
> `block in run_examples'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/example_group.rb:351:in
>
> `map'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/example_group.rb:351:in
>
> `run_examples'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/example_group.rb:337:in
>
> `run'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/command_line.rb:28:in
>
> `block (2 levels) in run'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/command_line.rb:28:in
>
> `map'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/command_line.rb:28:in
>
> `block in run'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/reporter.rb:34:in
>
> `report'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/command_line.rb:25:in
>
> `run'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/runner.rb:80:in
>
> `run_in_process'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/runner.rb:69:in
>
> `run'
> from
> /home/chris/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/rspec-core-2.8.0/lib/rspec/core/runner.rb:10:in
>
> `block in autorun'irb(#<Vocalist:0x000000027d09b8>):047:0> ds.delete_sql
> => "DELETE FROM (`vocalists_instruments`) WHERE (((`first` = 'F1') AND
> (`last` = 'L1')))"
> irb(#<Vocalist:0x000000027d09b8>):048:0>
>
> So it's a simple dataset, and it works fine as a select statement, but
> when I try to delete with it, sqlite can't handle it. I usually work with
> Postgres, where the syntax is slightly different, but this seems like a
> pretty simple query that Sequel + sqlite ought to be able to figure out, so
> I must be missing something.
>
> Thanks!
>
For the archives, this is because ds.opts[:from] was
[[:vocalists_instruments]] instead of [:vocalists_instruments]. SQLite
allows parens around tables in SELECT but not in DELETE, probably because
SELECT can use joins but DELETE cannot. So the fact that it worked in
SELECT was just an accident.
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/JKSyPGJQIqEJ.
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.
