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

[Raoul Bonnal]

Hi Rob, 
Thanks, I'll try to replicate the issue later this week.
Having this dependencies perhaps is better to extract biosql from bioruby.


On 14/11/11 15.37, "Rob Syme" <rob.syme at gmail.com> wrote:

> The script is very simple, just try and connect to the db. Replication
> is easiest when your postgres install includes a database $USER with
> permissions such that you can create databases with `createdb` without
> sudo. Instructions up at https://help.ubuntu.com/community/PostgreSQL
> under "Alternative Setup". If you have a more secure postgres setup,
> just supply the username password in the final line:
> 
> # Setup the postgres db
> git clone https://github.com/biosql/biosql.git
> createdb biosql
> psql biosql < biosql/sql/biosqldb-pg.sql
> 
> # Grab all the gems
> gem install activerecord bio composite_primary_keys pg
> activerecord-postgres-adapter
> 
> # Try and establish the db connection
> # The connection hash is minimal, username defaults to the $USER, and
> hostname defaults to localhost.
> ruby -r bio -e "Bio::SQL.establish_connection({'development'=>{
> 'database'=>'biosql','adapter'=>'postgresql' }},'development')"
> 
> 
> 
> 
> On Mon, Nov 14, 2011 at 9:34 PM, Raoul Bonnal <bonnal at ingm.org> wrote:
>> 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_rec
>>>>> or
>>>>> 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
>>> 
>>> 
>> 
>> 
>> _______________________________________________
>> BioRuby Project - http://www.bioruby.org/
>> BioRuby mailing list
>> BioRuby at lists.open-bio.org
>> http://lists.open-bio.org/mailman/listinfo/bioruby
>>