=begin

= Pg2


((<Ruby Postgres|URL:http://www.postgresql.jp/interfaces/ruby/index-ja.html>))が必要です。

Ruby Ver.1.8.1 Cygwin版で動作確認しました。それ以下だと駄目かも。
Ruby Postgres 0.7.1で動作確認しました。

以下の「使用例1」と「使用例2」がやってる事は同じです。見比べてみましょう。

詳しい説明は、ソース中のコメントに有ります。


== 使用例1

 db= Pg2::DB.new("localhost", 5432, "", "", "database1")
 db.query("insert into todo (contents, priority) values('Sleep', 1)")
 db.query("insert into todo (contents, priority) values('Work', 2)")
 rows= db.query("select * from todo")
 p(rows)
   # => [{"contents"=>"Sleep", "priority"=>1}, {"contents"=>"Work", "priority"=>2}]
 db.query("update todo set contents='Play' where priority=2")
 db.query("delete from todo where priority=1")
 rows= db.query("select * from todo")
 p(rows)
   # => [{"contents"=>"Play", "priority"=>2}]
 db.close()


== 使用例2

 db= Pg2::DB.new("localhost", 5432, "", "", "database1")
 db.insert("todo", {"contents"=>"Sleep", "priority"=>1})
 db.insert("todo", {"contents"=>"Work", "priority"=>2})
 rows= db.select_all(nil, "todo")
 p(rows)
   # => [{"contents"=>"Sleep", "priority"=>1}, {"contents"=>"Work", "priority"=>2}]
 db.update("todo", {"contents"=>"Play"}, {"priority"=>2})
 db.delete("todo", {"priority"=>1})
 rows= db.select_all(nil, "todo")
 p(rows)
   # => [{"contents"=>"Play", "priority"=>2}]
 db.close()


== 既知の問題

time, timestampのタイムゾーンに対応していません。


=end


require "postgres"
require "date"


=begin
== module Pg2
=end
module Pg2
  
  
  BOOLOID= 16
  BYTEAOID= 17
  CHAROID= 18
  NAMEOID= 19
  INT8OID= 20
  INT2OID= 21
  INT2VECTOROID= 22
  INT4OID= 23
  REGPROCOID= 24
  TEXTOID= 25
  OIDOID= 26
  TIDOID= 27
  XIDOID= 28
  CIDOID= 29
  OIDVECTOROID= 30
  POINTOID= 600
  LSEGOID= 601
  PATHOID= 602
  BOXOID= 603
  POLYGONOID= 604
  LINEOID= 628
  FLOAT4OID= 700
  FLOAT8OID= 701
  ABSTIMEOID= 702
  RELTIMEOID= 703
  TINTERVALOID= 704
  UNKNOWNOID= 705
  CIRCLEOID= 718
  CASHOID= 790
  MACADDROID= 829
  INETOID= 869
  CIDROID= 650
  ACLITEMOID= 1033
  BPCHAROID= 1042
  VARCHAROID= 1043
  DATEOID= 1082
  TIMEOID= 1083
  TIMESTAMPOID= 1114
  TIMESTAMPTZOID= 1184
  INTERVALOID= 1186
  TIMETZOID= 1266
  BITOID= 1560
  VARBITOID= 1562
  NUMERICOID= 1700
  REFCURSOROID= 1790
  REGPROCEDUREOID= 2202
  REGOPEROID= 2203
  REGOPERATOROID= 2204
  REGCLASSOID= 2205
  REGTYPEOID= 2206
  RECORDOID= 2249
  CSTRINGOID= 2275
  ANYOID= 2276
  ANYARRAYOID= 2277
  VOIDOID= 2278
  TRIGGEROID= 2279
  LANGUAGE_HANDLEROID= 2280
  INTERNALOID= 2281
  OPAQUEOID= 2282
  ANYELEMENTOID= 2283
  
=begin
=== モジュール関数
=end

=begin
--- str_to_obj(str, type_oid)
    
    DBからの文字列を、Rubyのオブジェクトに変換する。
    
    直接使う事は、あまり無いかも。
=end
  def str_to_obj(str, type_oid)
    return nil if !str
    case type_oid
      when BOOLOID
        return str=="t"
      when INT8OID, INT2OID, INT4OID
        return str.to_i()
      when FLOAT4OID, FLOAT8OID
          return str.to_f()
      when DATEOID
        if !(str=~/^(\d+)-(\d+)-(\d+)$/)
          raise("Unknown date format: "+str)
        end
        return Date.new($1.to_i(), $2.to_i(), $3.to_i())
      when TIMEOID, ABSTIMEOID, DATEOID, TIMESTAMPOID, TIMESTAMPTZOID, TIMETZOID
        #タイムゾーンには未対応。
        if !(str=~/^((\d+)-(\d+)-(\d+) )?(\d+):(\d+):([\d.]+)([-+]\d+)?$/)
          raise("Unknown time format: "+str)
        end
        return Time.mktime($2 ? $2.to_i() : 0, $3 ? $3.to_i() : 1, $4 ? $4.to_i() : 1, \
          $5.to_i(), $6.to_i(), $7.to_i(), ($7.to_f()*1000000).to_i()%1000000)
      else
        return str
    end
  end
  
=begin
--- obj_to_str(obj)
    
    Rubyのオブジェクトを、SQL用の文字列に変換する。
    
    直接使う事は、あまり無いかも。
=end
  def obj_to_str(obj)
    if obj==nil
      return "null"
    elsif obj==true
      return "'t'"
    elsif obj==false
      return "'f'"
    elsif obj.is_a?(Integer) || obj.is_a?(Float)
      return obj.to_s()
    elsif obj.is_a?(Date)
      return Kernel.format("'%d-%d-%d'", obj.year, obj.month, obj.day)
    elsif obj.is_a?(Time)
      return Kernel.format("'%d-%d-%d %d:%d:%f'", obj.year, obj.month, obj.day, obj.hour, obj.min, \
        obj.sec+obj.usec*0.000001)
    elsif obj.is_a?(RawValue)
      return obj.str
    else
      return "'"+obj.to_s().gsub(/'/){ "\\'" }+"'"
    end
  end
  
=begin
--- db_type_to_class(db_type)
    
    DBの型名から、対応するRubyのクラスを求める。
    
    直接使う事は、あまり無いかも。
    
    漏れが有ったらごめんなさい。
=end
  def db_type_to_class(db_type)
    db_type= db_type.strip().downcase().gsub(/\s+/, " ")+" "
    case db_type
      when /^(bigint|int8|bigserial|serial8|integer|int|int4|smallint|int2|serial|serial4) /
        return Integer
      when /^(double precision|float8|numeric|real|float4) /
        return Float
      when /^(boolean|bool) /
        return TrueClass #Booleanの代用。
      when /^(date) /
        return Date
      when /^(time|timestamp) /
        return Time
      else
        return String
    end
  end
  
  module_function(:str_to_obj, :obj_to_str, :db_type_to_class)
  
  
  module Delegatable
    
    #メソッドの処理を、特定のインスタンス変数に委譲する。
    def delegate(target_name, method_name)
      define_method(method_name) do |*args|
        return eval("@"+target_name.to_s()).method(method_name).call(*args)
      end
    end
    
  end
  
  
=begin
== class Pg2::SimpleDB

PostgreSQLにアクセスするためのクラス。
((<PGconn|URL:http://www.postgresql.jp/interfaces/ruby/reference-ja.html>)) のラッパ。

以下のメソッド以外に、 PGconn のメソッドも使える。
=end
  class SimpleDB
    
    include(Pg2)
    extend(Delegatable)
    
=begin
=== クラスメソッド
=end

=begin
--- SimpleDB.new(pghost, pgport, pgoptions, pgtty, dbname, login, passwd)
    
    DBに接続する。引数は PGconn.new と同じ。
=end
    def initialize(*args)
      @logger= nil
      @conn= PGconn.new(*args)
    end
    
=begin
=== インスタンスメソッド
=end
    
=begin
--- exec(sql)
--- exec(sql){ |res| ... }
    
    指定のSQLクエリをPostgreSQLに送る。
    
    成功すれば、 Pg2::Result クラスの結果オブジェクトが返る。
    失敗すれば、例外 PGError が発生。
    
    ブロックを指定した場合、結果オブジェクトを引数としてブロックが実行される。
    ブロックの実行が終了すると、自動的に結果オブジェクトが (({clear()})) される。
=end
    def exec(sql, &block)
      @logger.print(sql+";\n") if @logger
      begin
        res= Result.new(wrapped().exec(sql))
      rescue PGError=>ex
        raise(PGError.new(ex.message+"\nQuery: "+sql))
      end
      if block
        begin
          return block.call(res)
        ensure
          res.clear()
        end
      else
        return res
      end
    end
    
=begin
--- query(sql[, as])
    
    指定のSQLクエリをPostgreSQLに送る。
    
    成功すれば、結果の全ての行が配列で返る。
    
    失敗すれば、例外PGErrorが発生。この点がPGconn#queryとは違うので注意。
    
    ((|as|)) の指定については、 Pg2::Result#row を参照。
=end
    def query(sql, as= :hash)
      exec(sql){ |r| return r.rows(as) }
    end
    
=begin
--- get_row(sql[, as])
    
    クエリの結果の最初の行が返る。
    行が無ければ、 (({nil}))が返る。
    
    失敗すれば、例外PGErrorが発生。
    
    ((|as|)) の指定については、 Pg2::Result#row を参照。
=end
    def get_row(sql, as= :hash)
      exec(sql){ |r| return r.num_rows()>0 ? r.row(0, as) : nil }
    end
    
=begin
--- get_column(sql[, column])
    
    クエリの結果の、columnで指定された列の要素が配列で返る。
    
    columnには、列番号かフィールド名を指定する。省略した場合は最初の列。
    
    失敗すれば、例外PGErrorが発生。
=end
    def get_column(sql, column= 0)
      as= column.is_a?(Integer) ? :array : :hash
      rows= query(sql, as)
      return rows.map(){ |r| r[column] }
    end
    
=begin
--- get_one(sql)
    
    クエリの結果の、最初の行の最初の列の要素が返る。
    
    要素が無ければ、 (({nil})) が返る。
    
    失敗すれば、例外 PGError が発生。
=end
    def get_one(sql)
      row= get_row(sql, :array)
      return row ? row[0] : nil
    end
    
=begin
--- async_exec(sql)
--- async_exec(sql){ |res| ... }
    
    非同期でクエリを送る以外は、 (({exec})) と同じ。
=end
    def async_exec(sql, &block)
      begin
        res= Result.new(wrapped().async_exec(sql))
      rescue PGError=>ex
        raise(PGError.new(ex.message+"\nQuery: "+sql))
      end
      if block
        begin
          return block.call(res)
        ensure
          res.clear()
        end
      else
        return res
      end
    end
    
=begin
--- async_query(sql)
    
    非同期でクエリを送る以外は、 (({query})) と同じ。
=end
    def async_query(sql)
      begin
        async_exec(sql){ |r| return r.rows(as) }
      rescue PGError
        return nil
      end
    end
    
=begin
--- logger
--- logger = io
    
    IO オブジェクトを代入すると、発行した全てのSQLがそこに出力される。
=end
    attr_accessor(:logger)
    
=begin
--- format(fmt[, arg[, ...]])
    
    SQL用の (({sprintf})) もどき。以下の指定子が使える。
    
    * (({%v}))  シングルクオートでくくったりして、SQL中で値として使える形式にする。
      String, Integer, Float, Date, Timeに対応。
    * (({%s}))  文字列をそのまま埋め込む。
    * (({%%}))  パーセント記号。
    
     p db.format("select * from users where name=%v and birth=%v", \
                 "Gimite", Date.new(1983, 3, 2))
     
     # => "select * from users where name='Gimite' and birth='1983-3-2'"
=end
    def format(fmt, *args)
      args= args.dup()
      return fmt.gsub(/\%([vs%])/) do
        if $1=="v"
          obj_to_str(args.shift())
        elsif $1=="s"
          args.shift().to_s()
        else
          "%"
        end
      end
    end
    
    #PGconnからの委譲。
    delegate(:conn, :db)
    delegate(:conn, :host)
    delegate(:conn, :user)
    delegate(:conn, :options)
    delegate(:conn, :port)
    delegate(:conn, :tty)
    delegate(:conn, :error)
    delegate(:conn, :finish)
    delegate(:conn, :close)
    delegate(:conn, :reset)
    delegate(:conn, :trace)
    delegate(:conn, :untrace)
    delegate(:conn, :get_notify)
    delegate(:conn, :insert_table)
    delegate(:conn, :getline)
    delegate(:conn, :putline)
    delegate(:conn, :endcopy)
    delegate(:conn, :lo_import)
    delegate(:conn, :lo_export)
    delegate(:conn, :lo_create)
    delegate(:conn, :lo_open)
    delegate(:conn, :lo_unlink)
    
    #ラップされたPGconnオブジェクト。
    def wrapped()
      return @conn
    end
    
    def inspect()
      return Kernel.format("\#<%s:0x%x>", self.class().name(), id)
    end
    
  end
  
  
  #Pg2::SimpleDBに、動的なSQL作りに便利なメソッドを加えたもの。
  class DB < SimpleDB
    
    #各引数を繋いでWHERE句を作る。
    #引数にはHashかStringを指定する。
    #例えば
    #  where({"first"=>"Bob", "age"=>20}, "and id>=0")
    #は、
    #  WHERE first='Bob' and age=20 and id>=0
    #に変換される。
    def where(*args)
      args.collect!() do |a|
        if a.is_a?(Hash)
          a.to_a().map(){ |p| p[0]+"="+obj_to_str(p[1]) }.join(" and ")
        else
          a.to_s()
        end
      end
      return "WHERE "+args.join(" ")
    end
    
    #"LIMIT #{limit} OFFSET #{offset}"を返す。
    #select系メソッドの追加引数として使う。
    def limit(limit, offset= 0)
      return Kernel.format("LIMIT %s OFFSET %s", limit, offset)
    end
    
    #"OFFSET #{offset}"を返す。
    #select系メソッドの追加引数として使う。
    def offset(offset)
      return Kernel.format("OFFSET %s", offset)
    end
    
    #ORDER BY句を作る。
    #フィールド(列)名を1個以上指定する。
    #最後の引数に:asc(順方向)または:desc(逆方向)を指定できる。
    def order_by(*fields)
      dir= ""
      dir= " DESC" if fields.delete(:desc)
      dir= " ASC" if fields.delete(:asc)
      raise(ArgumentError.new("one or more fields must be specified")) if fields.empty?()
      return Kernel.format("ORDER BY %s%s", fields.join(", "), dir)
    end
    
    #SELECT文を作る。
    #
    #expは、取り出すフィールド(列)名or式。取れる値は以下の4種類。
    #・String。そのままSQLに埋め込まれる。(例: "field1, field2")
    #・Array。フィールド名の配列。(例: ["field1", "field2"])
    #・Hash。「フィールド名=>別名」の形。(例: {"field1"=>"a", "field2"=>"b"})
    #・nil。全ての列を取り出す。"*"と同じ。
    #
    #tableは、テーブル名。取れる値は以下の3種類。
    #・String。そのままSQLに埋め込まれる。
    #  (例: "table1", "table1, table2", "table1 a, table2 b")
    #・Array。カンマで繋いでSQLに埋め込まれる。
    #  (例: ["table1", "table2"], ["table1 a", "table2 b"])
    #・Hash。「テーブル名=>別名」の形。
    #  (例: {"table1"=>"a", "table2"=>"b"})
    #
    #conditionは、条件(WHERE句)。取れる値は以下の3種類。
    #・String。SQLのWHERE句をそのまま書く。(例: "WHERE id=3")
    #・Hash。「フィールド名=>値」の形。(例: {"id"=>3})
    #・nil。無条件。
    #
    #追加の引数として任意個のStringを指定でき、それらはWHERE句の後ろに、
    #そのままの順で(スペースで区切って)追加される。主に、limit、offset、
    #order_byメソッドの戻り値を指定する。
    #
    #つまり、大雑把に言うと、
    #  SELECT #{exp} FROM #{table} #{condition} #{args.join(" ")}
    #の形のSQLが作られる。
    def select_sql(exp, table, condition= nil, *args)
      exp= "*" if !exp
      exp= exp.to_a().map(){ |p| p[0]+" AS "+p[1] } if exp.is_a?(Hash)
      exp= exp.join(", ") if exp.is_a?(Array)
      table= table.to_a().map(){ |p| p[0]+" "+p[1] } if table.is_a?(Hash)
      table= table.join(", ") if table.is_a?(Array)
      condition= "" if !condition
      condition= where(condition) if condition.is_a?(Hash)
      if args.size==1 && args[0].is_a?(Integer)
        args= [limit(args[0])]
      elsif args.size==2 && args[0].is_a?(Integer) && args[1].is_a?(Integer)
        args= [limit(*args)]
      elsif args.size==2 && args[0]==nil && args[1].is_a?(Integer)
        args= [offset(args[1])]
      end
      return Kernel.format("SELECT %s FROM %s %s %s", \
        exp, table, condition, args.join(" "))
    end
    
    #SELECT文を実行し、Pg2::Resultオブジェクトを返す。
    #パラメータはselect_sqlメソッドを参照。
    def select(*args, &block)
      return exec(select_sql(*args), &block)
    end
    
    #SELECT文を実行し、結果の全ての行を配列で返す。
    #パラメータはselect_sqlメソッドを参照。
    def select_all(*args)
      return query(select_sql(*args))
    end
    
    #SELECT文を実行し、結果の最初の行を返す。
    #パラメータはselect_sqlメソッドを参照。
    def select_row(*args)
      return get_row(select_sql(*args))
    end
    
    #SELECT文を実行し、結果の最初の列を返す。
    #パラメータはselect_sqlメソッドを参照。
    def select_column(*args)
      return get_column(select_sql(*args))
    end
    
    #SELECT文を実行し、結果の最初の行の最初の列の要素を返す。
    #パラメータはselect_sqlメソッドを参照。
    def select_one(*args)
      return get_one(select_sql(*args))
    end
    
    #tableにrowを挿入するINSERT文を作る。
    #rowは フィールド名=>値 のHash。
    def insert_sql(table, row)
      k= row.keys().join(", ")
      v= row.values().map(){ |v| obj_to_str(v) }.join(", ")
      return Kernel.format("INSERT INTO %s (%s) VALUES(%s)", \
        table, k, v)
    end
    
    #INSERT文を実行する。
    #パラメータはinsert_sqlメソッド参照。
    def insert(*args)
      exec(insert_sql(*args)){ |r| return r.cmdstatus() }
    end
    
    #tableのconditionを満たす行をvaluesに変更するUPDATE文を作る。
    #valuesは フィールド名=>値 のHash。
    #conditionについては、select_sqlメソッド参照。
    def update_sql(table, values, condition)
      exp= values.to_a().map(){ |p| p[0]+"="+obj_to_str(p[1]) }.join(", ")
      condition= "" if !condition
      condition= where(condition) if condition.is_a?(Hash)
      return Kernel.format("UPDATE %s SET %s %s", table, exp, condition)
    end
    
    #UPDATE文を実行する。
    #パラメータはupdate_sqlメソッド参照。
    def update(*args)
      exec(update_sql(*args)){ |r| return r.cmdstatus() }
    end
    
    #tableのconditionを満たす行を削除するDELETE文を作る。
    #conditionについては、select_sqlメソッド参照。
    def delete_sql(table, condition)
      condition= "" if !condition
      condition= where(condition) if condition.is_a?(Hash)
      return Kernel.format("DELETE FROM %s %s", table, condition)
    end
    
    #DELETE文を実行する。
    #パラメータはdelete_sqlメソッド参照。
    def delete(*args)
      exec(delete_sql(*args)){ |r| return r.cmdstatus() }
    end
    
    #最後にtableに挿入された行のfieldの値を返す。
    #fieldはserial型でなければならない。
    def last_serial(table, field)
      return select_one("last_value", table+"_"+field+"_seq")
    end
    
    #CREATE TABLE文を作る。詳細はその内書く。
    def create_table_sql(table, columns, rest1= "", rest2= "")
      col_strs= []
      p_keys= []
      for c in columns
        s= c[:name]+" "+c[:db_type]
        if c.has_key?(:default)
          s+= Kernel.format(" DEFAULT %s", obj_to_str(c[:default]))
        end
        col_strs.push(s)
        p_keys.push(c[:name]) if c[:primary?]
      end
      if !p_keys.empty?()
        p_str= Kernel.format(",\n  PRIMARY KEY(%s)", p_keys.join(", "))
      else
        p_str= ""
      end
      return Kernel.format("CREATE TABLE %s (\n  %s%s%s\n) %s", \
        table, col_strs.join(",\n  "), p_str, rest1, rest2)
    end
    
    #CREATE TABLE文を実行する。詳細はその内書く。
    def create_table(*args)
      return exec(create_table_sql(*args)){ |r| return r.cmdstatus() }
    end
    
    #DROP TABLE文を作る。
    def drop_table_sql(table)
      return Kernel.format("DROP TABLE %s", table)
    end
    
    #DROP TABLE文を実行する。
    def drop_table(table)
      return exec(drop_table_sql(table)){ |r| return r.cmdstatus() }
    end
    
  end
  
  
  #SQLクエリの結果。PGresultのラッパ。
  #使い終わったら、clear()する事。
  class Result
    
    include(Pg2)
    
    def initialize(res)
      @res= res
    end
    
    #結果のn行目を返す。
    #asに:hashを指定すると、フィールド名をキーとするHashを返す。
    #asに:arrayを指定すると、Arrayを返す。
    def row(n, as= :hash)
      raise(":hash or :array expected.") if as!=:hash && as!=:array
      r= as==:hash ? {} : []
      for i in 0...@res.num_fields()
        str= @res.getvalue(n, i)
        val= str_to_obj(str, @res.type(i))
        if as==:hash
          r[@res.fieldname(i)]= val
        else
          r[i]= val
        end
      end
      return r
    end
    
    #結果の各行を引数としてブロックを実行する。
    #asの指定については、rowを参照。
    def each_row(as= :hash, &block)
      for i in 0...num_rows()
        block.call(row(i, as))
      end
    end
    
    alias each each_row
    
    #結果の全ての行を配列で返す。
    #asの指定については、rowを参照。
    def rows(as= :hash)
      return (0...num_rows()).map(){ |i| row(i, as) }
    end
    
    #結果の行数。
    def num_rows()
      return @res.num_tuples()
    end
    
    #結果の列数。
    def num_columns()
      return @res.num_fields()
    end
    
    #フィールド(列)名を配列で返す。
    def fields()
      return @res.fields()
    end
    
    #index列目の型を表すOIDを返す。
    def type(index)
      return @res.type(index)
    end
    
    #最後のクエリのコマンドステータスを文字列で返す。
    def cmdstatus()
      return @res.cmdstatus()
    end
    
    #結果を解放する。オブジェクトを使い終わったら呼ぶ事。
    def clear()
      return @res.clear()
    end
    
    #ラップされたPGresultオブジェクト。
    def wrapped()
      return @res
    end
    
    def inspect()
      return Kernel.format("\#<%s:0x%x>", self.class().name(), id)
    end
    
  end
  
  
  #文字列をクオートでくくらずに、そのままSQLに埋め込みたい時に使う。
  #例: db.insert("table1", {"t"=>Pg2::RawValue.new("now()")})
  class RawValue
    
    def initialize(str)
      @str= str
    end
    
    def to_s()
      return @str
    end
    
    attr_accessor(:str)
    
  end
  
  
end #Pg2