recommendations for working with PostgreSQL

C

Chad Perrin

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.
 
R

Reid Thompson

Chad said:
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
 
R

Reid Thompson

Reid said:
Chad said:
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
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
 
B

Ben Bleything

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
 
S

Sharon Rosner

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
 
C

Chad Perrin

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.
 
T

Thomas Adam

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

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 Adam

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

Chad Perrin

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.)

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.
 
C

Chad Perrin

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.
 
B

Bill Kelly

From: "Chad Perrin said:
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.

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
 
C

Chad Perrin

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,900
Latest member
Nell636132

Latest Threads

Top