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

[Rob Syme]

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_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
>>
>>
>
>
> _______________________________________________
> BioRuby Project - http://www.bioruby.org/
> BioRuby mailing list
> BioRuby at lists.open-bio.org
> http://lists.open-bio.org/mailman/listinfo/bioruby
>