Wednesday, September 1, 2010

Testing pg (postgresql-ruby)

# pgsql01.rb

require 'pg'

class PgSQLTest

  def initialize
    @db = PGconn.connect( nil, 5432, nil, nil, 'mydb', nil, nil ) 
    puts "dbname: " + @db.db + ", user: "+@db.user
    @db.exec("create table test (id INTEGER PRIMARY KEY, title TEXT)") \
       unless(table_exists("test"))
    @db.exec("insert into test (id, title) values(1, 'test1')") \
       unless(record_exists("test", "test1"))
    @db.exec("insert into test (id, title) values(2, 'test2')") \
       unless(record_exists("test", "test2"))
  end
 
  def exit
    @db.close if(@db)
  end
 
  def table_exists(name)
    rows = @db.exec("SELECT 1 FROM pg_catalog.pg_class \
       WHERE relkind = 'r' AND relname = '"+name+"' \
       AND pg_catalog.pg_table_is_visible(oid) LIMIT 1")
    return (rows.count>0)
  end
 
  def record_exists(table_name, title)
    rows = @db.exec("select id from "+table_name+" where title='"+title+"'")
    return (rows.count>0)
  end
 
  def show_all
    rows = @db.exec('select * from test')
    for col in rows.fields
      printf("%-15s", col)
    end 
    printf("\n---------------------\n")
    r = 0
    rows.each do |row|
      c = 0
      row.each do |col|
        printf("%-15s", rows.getvalue(r,c))
        c = c + 1
      end
      r = r + 1
      printf("\n")
    end
  end 

end

app = PgSQLTest.new
app.show_all
app.exit

Run it.
ruby pgsql01.rb
dbname: mydb, user: socrateos
id             title          
---------------------
1              test1          
2              test2  

No comments:

Post a Comment