Forum: Ruby on Rails connecting to postgres

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
D9e14f1be0972ff1f393cc87dbd072e1?d=identicon&s=25 George George (tanzanite)
on 2007-02-28 02:24
Hello everyone!

Please forgive my ignorance, am pretty new to rails.

Am creating an application on rails framework that will use a legacy
postgres
database with about 119 tables and 121 sequences that define the primary
key fields.
The database is ready now and i need to hook it to active:record:Base
i have installed these two gems:
postgres-pr
ruby-postgres

My YML file reads like this:

development:
  adapter: postgresql
  database: DB_NAME
  username: george
  password: tanzanite
  host: localhost
  schema_order: public

# Warning: The database defined as 'test' will be erased and
# re-generated from your development database when you run 'rake'.
# Do not set this db to the same as development or production.
test:
  adapter: postgresql
  database: DB_NAME_test
  username: tanzanite
  password: password
  host: localhost
  schema_order: public

production:
  adapter: postgresql
  database: DB_NAME_production
  username: tanzanite
  password: password
  host: localhost
  schema_order: public


i then wrote a configuration file that stops pluralization of the
tables.
i generated a model scaffold to "test the waters" and lol and behold! it
could not connect to through active:record.

Please can someone point me to a useful resource to guide me on how to
accomplish this. All resources seem to use mysql and am only finding no
information on connecting to postgres legacy databases.

How do i map  the recquired id field to my user-defined primary key
generically named Tablename_id?

What configuration details am i missing?

Thank you.

rgds
tanzanite
4b87f676cb6c4d648d71000681823693?d=identicon&s=25 Jean-sébastien Jney (jney)
on 2007-02-28 03:24
(Received via mailing list)
Hello,

  database.yml seems to be ok (maybe schema_order... i don't know i
don't use this parameter).
are you sure about your postgresql database config?
can you connect from application server to your postgresql database?
if not check :
  - pg_hba.conf should be configure to allow application server to
connect.
  - and listen_addresses in postgresql.conf should be correct to.

bye

On 28 fév, 02:24, Tanzanite Tanzanite <rails-mailing-l...@andreas-
5c19f2d52879a1e10670c7334ba4c7e3?d=identicon&s=25 Lugovoi Nikolai (Guest)
on 2007-02-28 03:42
(Received via mailing list)
1. What exceptions are in logs?

2. For legacy databases you might find useful class methods
set_primary_key, set_table_name, set_sequence_name, see
http://api.rubyonrails.org/classes/ActiveRecord/Ba...
and primary_key_prefix_type
http://dev.rubyonrails.org/browser/trunk/activerec...


2007/2/28, Tanzanite Tanzanite <rails-mailing-list@andreas-s.net>:
D9e14f1be0972ff1f393cc87dbd072e1?d=identicon&s=25 George George (tanzanite)
on 2007-02-28 03:53
Lugovoi Nikolai wrote:
> 1. What exceptions are in logs?
>
> 2. For legacy databases you might find useful class methods
> set_primary_key, set_table_name, set_sequence_name, see
> http://api.rubyonrails.org/classes/ActiveRecord/Ba...
> and primary_key_prefix_type
> 
http://dev.rubyonrails.org/browser/trunk/activerec...
>
>
> 2007/2/28, Tanzanite Tanzanite <rails-mailing-list@andreas-s.net>:

Comments appreciated

This is what is in the logs

ActiveRecord::StatementInvalid (RuntimeError: ERROR  C42501  Mpermission
denied for relation feature  Faclchk.c  L1395  Raclcheck_error: SELECT
count(*) AS count_all FROM feature ):
    C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/abstract_adapter.rb:128:in
`log'
    C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/postgresql_adapter.rb:152:in
`execute'
    C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/postgresql_adapter.rb:433:in
`select'
    C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/abstract/database_statements.rb:13:in
`select_one'
    C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/abstract/database_statements.rb:19:in
`select_value'
    C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:212:in
`execute_simple_calculation'
    C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:121:in
`calculate'
    C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:117:in
`catch'
    C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:117:in
`calculate'
    C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:45:in
`count'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/pagination.rb:173:in
`count_collection_for_pagination'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/pagination.rb:197:in
`paginator_and_collection_for'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/pagination.rb:130:in
`paginate'
    /app/controllers/admin_controller.rb:12:in `list'
    /app/controllers/admin_controller.rb:3:in `index'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/base.rb:1095:in
`send'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/base.rb:1095:in
`perform_action_without_filters'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/filters.rb:632:in
`call_filter'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/filters.rb:634:in
`call_filter'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/filters.rb:619:in
`perform_action_without_benchmark'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/benchmarking.rb:66:in
`perform_action_without_rescue'
    C:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/benchmarking.rb:66:in
`perform_action_without_rescue'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/rescue.rb:83:in
`perform_action'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/base.rb:430:in
`send'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/base.rb:430:in
`process_without_filters'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/filters.rb:624:in
`process_without_session_management_support'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/session_management.rb:114:in
`process'
    C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/base.rb:330:in
`process'
    C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/dispatcher.rb:41:in
`dispatch'
    C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/webrick_server.rb:113:in
`handle_dispatch'
    C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/webrick_server.rb:79:in
`service'
    C:/ruby/lib/ruby/1.8/webrick/httpserver.rb:104:in `service'
    C:/ruby/lib/ruby/1.8/webrick/httpserver.rb:65:in `run'
    C:/ruby/lib/ruby/1.8/webrick/server.rb:173:in `start_thread'
    C:/ruby/lib/ruby/1.8/webrick/server.rb:162:in `start'
    C:/ruby/lib/ruby/1.8/webrick/server.rb:162:in `start_thread'
    C:/ruby/lib/ruby/1.8/webrick/server.rb:95:in `start'
    C:/ruby/lib/ruby/1.8/webrick/server.rb:92:in `each'
    C:/ruby/lib/ruby/1.8/webrick/server.rb:92:in `start'
    C:/ruby/lib/ruby/1.8/webrick/server.rb:23:in `start'
    C:/ruby/lib/ruby/1.8/webrick/server.rb:82:in `start'
    C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/webrick_server.rb:63:in
`dispatch'
    C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/commands/servers/webrick.rb:59
    C:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in
`gem_original_require'
    C:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in
`require'
    C:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.0/lib/active_support/dependencies.rb:496:in
`require'
    C:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.0/lib/active_support/dependencies.rb:343:in
`new_constants_in'
    C:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.0/lib/active_support/dependencies.rb:496:in
`require'
    C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/commands/server.rb:39
    C:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in
`gem_original_require'
    C:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in
`require'
    ./script/server:3
    -e:4:in `load'
    -e:4
D9e14f1be0972ff1f393cc87dbd072e1?d=identicon&s=25 George George (tanzanite)
on 2007-02-28 03:56
Jean-sébastien Jney wrote:
> Hello,
>
>   database.yml seems to be ok (maybe schema_order... i don't know i
> don't use this parameter).
> are you sure about your postgresql database config?
> can you connect from application server to your postgresql database?
> if not check :
>   - pg_hba.conf should be configure to allow application server to
> connect.
>   - and listen_addresses in postgresql.conf should be correct to.
>
> bye
>
> On 28 f�v, 02:24, Tanzanite Tanzanite <rails-mailing-l...@andreas-

Thanks i have configured connections to accept localhost
am now getting this

RuntimeError: ERROR  C42501  Mpermission denied for relation feature
Faclchk.c  L1395  Raclcheck_error: SELECT count(*) AS count_all FROM
feature
5c19f2d52879a1e10670c7334ba4c7e3?d=identicon&s=25 Lugovoi Nikolai (Guest)
on 2007-02-28 04:01
(Received via mailing list)
2007/2/28, Tanzanite Tanzanite <rails-mailing-list@andreas-s.net>:
> This is what is in the logs
>
> ActiveRecord::StatementInvalid (RuntimeError: ERROR  C42501  Mpermission
> denied for relation feature  Faclchk.c  L1395  Raclcheck_error: SELECT
> count(*) AS count_all FROM feature ):
>

So, the problem shows out clear: you connect to database with postgres
role that does not have enough privilieges.
Make sure you did GRANT'ed necessary access  to database objects.
http://www.postgresql.org/docs/current/static/sql-grant.html
D9e14f1be0972ff1f393cc87dbd072e1?d=identicon&s=25 George George (tanzanite)
on 2007-02-28 04:15
Lugovoi Nikolai wrote:
> 2007/2/28, Tanzanite Tanzanite <rails-mailing-list@andreas-s.net>:
>> This is what is in the logs
>>
>> ActiveRecord::StatementInvalid (RuntimeError: ERROR  C42501  Mpermission
>> denied for relation feature  Faclchk.c  L1395  Raclcheck_error: SELECT
>> count(*) AS count_all FROM feature ):
>>
>
> So, the problem shows out clear: you connect to database with postgres
> role that does not have enough privilieges.
> Make sure you did GRANT'ed necessary access  to database objects.
> http://www.postgresql.org/docs/current/static/sql-grant.html

Hey!

Thank you!
Thank you!

it worked

These are the additional configurations i have  added:
i have manually set the primary key to the actual name of the primary
key in my table. This is not a DRY compliant method and i have to change
that so that i dont have to repeat that for each of the 119 tables!

but otherwise it means it can work and for that am greatful for your
comments!

rgds
tanzanite
5c19f2d52879a1e10670c7334ba4c7e3?d=identicon&s=25 Lugovoi Nikolai (Guest)
on 2007-02-28 04:55
(Received via mailing list)
2007/2/28, Tanzanite Tanzanite <rails-mailing-list@andreas-s.net>:
> These are the additional configurations i have  added:
> i have manually set the primary key to the actual name of the primary
> key in my table. This is not a DRY compliant method and i have to change
> that so that i dont have to repeat that for each of the 119 tables!
>

Well,  using legacy database schema that does not conform to Rails
conventions and cannot be changed, is indeed not very DRY.

e.g. when defining associations one may need to declare foreign key
names and class names explicitly, but from my own experience it's not
that bad even with dozens of tables :)

But, you can dig postgres system tables (I mean with some ruby/sql
script) and build first scratch version of ActiveRecord models
automatically.
420aefb1d5de8150ec86fb240196e1be?d=identicon&s=25 Andrew Madu (Guest)
on 2007-02-28 11:15
(Received via mailing list)
Hi,
for all those who may be wishing to integrate postgresql into their
rails
framework, I received this from the postgresql list:

=============================================================
Given the recent discussions of applications stacks, PHP & Ruby etc. it
seems an ideal time for me to introduce a project I've been working on.

StackBuilder is an extension of the Windows installer for PostgreSQL
that will allow the user to quickly and easily download and install
additional software to build the application stack they desire around
PostgreSQL. The project includes the StackBuilder wizard as well as a
toolkit for building application installers.

Whilst the StackBuilder itself is still in development, the first
application installer, pgRails, is available for manual download and
testing from http://pgfoundry.org/projects/stackbuilder/

pgRails is a distribution of Ruby, Rails, and the Ruby PostgreSQL
connector all preconfigured for use with PostgreSQL on Windows 2000 and
above.

I'd like to invite anyone who is interested to download and try it out,
and report any issues or problems using the project's trackers on
pgFoundry.

Regards, Dave.
=======================================================

Hope that helps someone.. ;-)

--
Regards

Andrew
This topic is locked and can not be replied to.