[BioRuby] BioSQL - postgres refusing SQL generated by Bio::SQL::establish_connection

[Raoul Bonnal]

It should work with versions prior to Rails 3.x.
But if you are not in a rush after paper submission I'll look into it,
let'say 2 weeks  ? There was another BioSQL issue around, i didn't answer
because I was busy.

@Rob: could you give me the script to replicate the error ?

We could extract BioSQL from BioRuby and create a plugin, is something I'm
considering.

On 14/11/11 14.14, "Naohisa GOTO" <ngoto at gen-info.osaka-u.ac.jp> wrote:

> Hi Raoul,
> 
> Which version of ActiveRecord is suitable with the current
> BioRuby's biosql implementation?
> 
> With RubyGems and (optionally) with Bundler, we can specify
> specific version number to be used.
> 
> Naohisa Goto
> ngoto at gen-info.osaka-u.ac.jp / ng at bioruby.org
> 
> On Mon, 14 Nov 2011 11:43:51 +0100
> Raoul Bonnal <bonnal at ingm.org> wrote:
> 
>> Hi Rob,
>> may be a problem of my biosql implementation, I suppose.
>> I didn't update the library and I didn't check compatibility with new active
>> record stuff and composite primary keys.
>> Now, I can not take care of this, if there is someone wants to take care of
>> I'll be happy to support him/her.
>> 
>> 
>> On 14/11/11 07.07, "Rob Syme" <rob.syme at gmail.com> wrote:
>> 
>>> Hi all,
>>> Full details are up at: http://gist.github.com/1363302
>>> 
>>> This might just be the result of a schema change in BioSQL, but my SQL
>>> knowledge doesn't extend quite far enough. Has anyone come across this
>>> before?
>>> 
>>> When connecting to an empty postgres BioSQL db, I get an
>>> ActiveRecord::StatementInvalid error:
>>> 
>>> PGError: ERROR:  cannot perform INSERT RETURNING on relation "ontology"
>>> HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a
>>> RETURNING clause.
>>> : INSERT INTO "ontology" ("definition", "name") VALUES ($1, $2)
>>> RETURNING "ontology_id"
>>> ActiveRecord::StatementInvalid: PGError: ERROR:  cannot perform INSERT
>>> RETURNING on relation "ontology"
>>> HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a
>>> RETURNING clause.
>>> : INSERT INTO "ontology" ("definition", "name") VALUES ($1, $2)
>>> RETURNING "ontology_id"
>>> from 
>>> /home/rob/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_recor
>>> d/
>>> connection_adapters/postgresql_adapter.rb:1009:in
>>> `prepare'
>>> <snip>
>>> from 
>>> /home/rob/.rvm/gems/ruby-1.9.2-p290/gems/bio-1.4.2.5000/lib/bio/io/biosql/bi
>>> os
>>> ql.rb:31:in
>>> `establish_connection'
>>> 
>>> 
>>> The offending SQL was:
>>> 
>>> SELECT "ontology".* FROM "ontology" WHERE (name = 'Annotation Tags')
>>> LIMIT 1 # This works fine
>>> INSERT INTO "ontology" ("definition", "name") VALUES ($1, $2)
>>> RETURNING "ontology_id"  [["definition", nil], ["name", "Annotation
>>> Tags"]] # This fails
>>> 
>>> Relevant gems installed:
>>>   activerecord (3.1.1)
>>>   composite_primary_keys (4.1.1)
>>>   pg (0.11.0)
>>>   bio (1.4.2.5000)
>>> 
>>> Using:
>>>   psql (PostgreSQL) 9.1.1
>>>   BioSQL schema version 1.0.1 and
>>>   ruby 1.9.2p290 (2011-07-09 revision 32553) [x86_64-linux]
>>> 
>>> -r
>>> _______________________________________________
>>> BioRuby Project - http://www.bioruby.org/
>>> BioRuby mailing list
>>> BioRuby at lists.open-bio.org
>>> http://lists.open-bio.org/mailman/listinfo/bioruby
>> 
>> 
>> _______________________________________________
>> BioRuby Project - http://www.bioruby.org/
>> BioRuby mailing list
>> BioRuby at lists.open-bio.org
>> http://lists.open-bio.org/mailman/listinfo/bioruby
> 
>