Recommendations for working with PostgreSQL

I have need to work with PostgreSQL via Ruby. What gems/modules do you
recommend? Are there any quickstart-type howtos online to which you
might direct me?

Thanks in advance.

Reid T. wrote:

og_psql = {

}
cyberhome: /home/rthompso>
$ psql -U rthompso -h 127.0.0.1 -c “select count(*) from ogcomment” test
count

10000
(1 row)

I just updated og…
updated to reflect a few changes…
I’m sure there are others that can give better examples…

require ‘og’

class Comment
attr_accessor :title, String
attr_accessor :body, String
attr_accessor :author, String
attr_accessor :create_time, Time
end

og_psql = {
:destroy => false,
:store => :postgresql,
:user => ‘rthompso’,
:address => ‘127.0.0.1’,
:password => ‘rthompso’,
:name => ‘test’
}

db =Og.setup(og_psql)

t1= Time.now

save the object in the database

db.store.start
1.upto(10000) { |i|
c = Comment.new
c.title = ‘Hello’
c.body = ‘World’
c.create_time = Time.now
c.author = ‘tml’
c.save
}
db.store.commit
puts Time.now - t1

Chad P. wrote:

I have need to work with PostgreSQL via Ruby. What gems/modules do you
recommend? Are there any quickstart-type howtos online to which you
might direct me?

Thanks in advance.

og www.nitroproject.org

$ cat testog.rb
require ‘og’

class Comment
property :title, String
property :body, String
property :author, String
property :create_time, Time
end

og_psql = {
:destroy => false,
:store => :psql,
:user => ‘rthompso’,
:address => ‘127.0.0.1’,
:password => ‘rthompso’,
:name => ‘test’
}

db =Og.setup(og_psql)

t1= Time.now

save the object in the database

db.store.start
1.upto(10000) { |i|
c = Comment.new
c.title = ‘Hello’
c.body = ‘World’
c.create_time = Time.now
c.author = ‘tml’
c.save
}
db.store.commit
puts Time.now - t1

-----------------this is on cygwin windows ----------------------

$ ruby -v ruby 1.8.6 (2007-03-13 patchlevel 0) [i386-cygwin]


cyberhome: /home/rthompso>
$ export RUBYOPT=rubygems
cyberhome: /home/rthompso>
$ ruby testog.rb
INFO: Og uses the Psql store.
NOTICE: CREATE TABLE will create implicit sequence “ogcomment_oid_seq”
for serial column “ogcomment.oid”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
“ogcomment_pkey” for table “ogcomment”
INFO: Created table ‘ogcomment’.
10.697

cyberhome: /home/rthompso>
$ psql -U rthompso -h 127.0.0.1 -c “select count(*) from ogcomment” test
count

On Wed, Oct 17, 2007, Chad P. wrote:

I have need to work with PostgreSQL via Ruby. What gems/modules do you
recommend? Are there any quickstart-type howtos online to which you
might direct me?

If you’re looking for an ORM, there is of course ActiveRecord, and
someone else already offered up Og.

As far as the driver goes, the ruby-postgres gem seems to be the way to
go.

Ben

I have need to work with PostgreSQL via Ruby. What gems/modules do you
recommend? Are there any quickstart-type howtos online to which you
might direct me?

First you need to install the postgres driver:

sudo gem install postgres

As far as ORM’s go, may I suggest Sequel:

require ‘sequel/postgres’

DB = Sequel(‘postgres://rthompso:[email protected]/test’)

DB.create_table :items do # Create a new table
column :name, :text
column :price, :float
end

items = DB[:items] # Create a dataset

Populate the table

items << {:name => ‘abc’, :price => rand * 100}
items << {:name => ‘def’, :price => rand * 100}
items << {:name => ‘ghi’, :price => rand * 100}

puts “Count of items: #{items.count}”

puts “All items:”
items.reverse_order(:price).print

puts “The average price is: #{items.avg(:price)}”

puts “All items with price under 50”
items.filter {:price < 50}.print

You can find more information here:

http://code.google.com/p/ruby-sequel/

Or on sequel-talk:

http://groups.google.com/group/sequel-talk

best
Sharon

On Wed, Oct 17, 2007 at 01:44:05PM +0900, Sharon R. wrote:

I have need to work with PostgreSQL via Ruby. What gems/modules do you
recommend? Are there any quickstart-type howtos online to which you
might direct me?

First you need to install the postgres driver:

sudo gem install postgres

As far as ORM’s go, may I suggest Sequel:

Thanks. I’ll look into it.

On Wed, Oct 17, 2007 at 08:22:27AM +0900, Reid T. wrote:

require ‘og’

Thanks. I’ll look into it.

On Wed, Oct 17, 2007 at 11:21:15AM +0900, Ben B. wrote:

On Wed, Oct 17, 2007, Chad P. wrote:

I have need to work with PostgreSQL via Ruby. What gems/modules do you
recommend? Are there any quickstart-type howtos online to which you
might direct me?

If you’re looking for an ORM, there is of course ActiveRecord, and
someone else already offered up Og.

I’m not necessarily looking for ORM, though now that I think about it I
can’t help but wonder if every single programmatic interface between
Ruby
and a relational database might qualify in some manner as ORM (since
“everything is an object” in Ruby). In the Perl world, I’d tend to use
DBI for all things database-y, and was wondering what the preferred
approach would be in the Ruby world especially in the case of
PostgreSQL.

As far as the driver goes, the ruby-postgres gem seems to be the way to
go.

Thanks for confirming that.

On 17/10/2007, Chad P. [email protected] wrote:

can’t help but wonder if every single programmatic interface between Ruby
and a relational database might qualify in some manner as ORM (since
“everything is an object” in Ruby). In the Perl world, I’d tend to use

But then, just because the language you’re using means everything is
an object, does not mean to say that when you connect to a database
and start pulling stuff out of it that what you get is ORM. ORM is an
interface your application would define for itself, mapping to the
underlying tables/views/whatever in the database.

Ruby makes this easier in that you could just (at a crude level) map
an object you define to have properties, behaviour and scope of the
underlying tables, etc., in the database. Sometimes there is merit to
that – and indeed, coming into this are things like patterns (the
factory pattern in particular is useful in ORM scenarios.)

DBI for all things database-y, and was wondering what the preferred
approach would be in the Ruby world especially in the case of PostgreSQL.

Having written numerous libraries (well, OK, two) in both perl and
ruby, I can honestly say that whilst it’s somewhat “easier” in perl
(you’re not forced to go down the abstracted route of ORM design) you
lose a lot of functionality – the scope of what you can write in Perl
diminishes since the interface subroutines rarely map in an ORM way
[1]. Look into it heavily with Ruby – it will make life easier.

– Thomas A.

[1] Anyone who things writing OO Perl is a good thing for this; think
again. OO perl is just broken.

-----Original Message-----
From: Chad P. [mailto:[email protected]]
Sent: Wednesday, October 17, 2007 8:37 AM
To: ruby-talk ML
Subject: Re: recommendations for working with PostgreSQL
[…]
I’m not necessarily looking for ORM, though now that I think
about it I
can’t help but wonder if every single programmatic interface
between Ruby
and a relational database might qualify in some manner as ORM (since
“everything is an object” in Ruby).
[…]

I’d argue against that - an ORM would map the entity described by the
database to an equivalent object in Ruby (say, an entry in an
addressbook).
DBI like interfaces do map to objects in Ruby, but those objects
represent
merely the database row with no knowledge about the entity that row
describes.


CCD CopyWrite Chad P. [ http://ccd.apotheon.org ]
W. Somerset Maugham: “The ability to quote is a serviceable
substitute for
wit.”

Felix

On Thu, Oct 18, 2007 at 02:37:39AM +0900, Thomas A. wrote:

interface your application would define for itself, mapping to the
underlying tables/views/whatever in the database.

Ruby makes this easier in that you could just (at a crude level) map
an object you define to have properties, behaviour and scope of the
underlying tables, etc., in the database. Sometimes there is merit to
that – and indeed, coming into this are things like patterns (the
factory pattern in particular is useful in ORM scenarios.)

Thus, my “in some manner” qualification. The comment about all
programmatic DB interfaces in Ruby being ORMs was really a throw-away,
not exactly meant to be taken in strictly literal fashion. Sorry if
that
wasn’t clear.

DBI for all things database-y, and was wondering what the preferred
approach would be in the Ruby world especially in the case of PostgreSQL.

Having written numerous libraries (well, OK, two) in both perl and
ruby, I can honestly say that whilst it’s somewhat “easier” in perl
(you’re not forced to go down the abstracted route of ORM design) you
lose a lot of functionality – the scope of what you can write in Perl
diminishes since the interface subroutines rarely map in an ORM way
[1]. Look into it heavily with Ruby – it will make life easier.

Thanks for the advice. I shall (look into it heavily).

[1] Anyone who things writing OO Perl is a good thing for this; think
again. OO perl is just broken.

I certainly don’t disagree much. It’s not any more broken, really, than
OOP in languages like C++, but they’re pretty broken in that regard too.
Just my opinion, of course. In general, I love Perl – but in terms of
its OO constructs, I do not love it.

On Thu, Oct 18, 2007 at 12:39:33AM +0900, Chad P. wrote:

On Wed, Oct 17, 2007 at 08:22:27AM +0900, Reid T. wrote:

require ‘og’

Thanks. I’ll look into it.

I’m having a difficult time finding anything about Og itself. I find a
lot of stuff in Google that seems to be related to it, but only leads me
to Nitro with little or no mention of Og. What am I missing?

It should be noted that I’m not writing a web app at this time, so if Og
is somehow specific to Nitro, it’s probably not what I need.

On Thu, Oct 18, 2007 at 04:39:31AM +0900, Bill K. wrote:

The tutorial.txt provided with the Og installation covers the
basics. (Although out-of-date now on a couple points.) There are
also a couple more detailed tutorials on: http://oxywtf.de/

Thanks. That’ll probably help me in my decision about what to use.

From: “Chad P.” [email protected]

It should be noted that I’m not writing a web app at this time, so if Og
is somehow specific to Nitro, it’s probably not what I need.

Og isn’t tied to Nitro. Although I do use Og with Nitro on some
projects, I also use Og independently on others.

I believe gem install og should be sufficient.

In my own exprience Og is a nice enough piece of software that
it’s worth looking into despite the scarcity of documentation.

The tutorial.txt provided with the Og installation covers the
basics. (Although out-of-date now on a couple points.) There are
also a couple more detailed tutorials on: http://oxywtf.de/

Note: If you’re running an older version of Postgres, e.g.
7.4.x, the og-0.41.0 gem is broken for 7.4 versions of Postgres,
but can be patched easily:

--- og-0.41.0/lib/og/store/sql.rb.orig  2006-12-27 04:59:23.000000000 
-0800
+++ og-0.41.0/lib/og/store/sql.rb       2007-03-19 00:15:42.000000000 
-0700
@@ -350,7 +350,7 @@
     if table_type = @options[:table_type]
       sql << ") TYPE = #{table_type};"
     else
-      sql << ")"
+      sql << ") WITHOUT OIDS;"  # %%BWK HACK for postgres 7.4 (added 
WITHOUT OIDS)
     end

     # Create indices.

Alternately you could use darcs to pull the latest sources, which
contain the real patch, which isn’t as hacky as my one-liner above.

Note: The Nitro/Og team is on the verge of releasing a 0.50 gem,
Real Soon Now.

Regards,

Bill

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs