database wrapper for mysql2 and sqlite3 and postgresql

database.rb

require 'yaml'
require 'logger'
RAILS_ENV = ENV["RAILS_ENV"] || "development"
ROOT = File.dirname(File.expand_path("../",__FILE__))
DB_SETTING = YAML.load_file(ROOT + "/config/database.yml")[RAILS_ENV]
class DataBaseWrapper
  attr_reader :client
  def initialize(log = Logger.new(STDOUT))
    @log = log 
    case DB_SETTING["adapter"] 
    when "sqlite3"
      require 'sqlite3'
      @adapter = "sqlite3"
      @client = SQLite3::Database.new(DB_SETTING["path"])
      @client.execute("PRAGMA count_changes=ON;")
    when "mysql2"
      require 'mysql2-cs-bind'
      @adapter = "mysql2"
      db_params = {:username => DB_SETTING["username"], :password => DB_SETTING["password"], :database => DB_SETTING["database"]}
      if DB_SETTING["host"]
        db_params[:host] = DB_SETTING["host"]
      end
      @client = Mysql2::Client.new(db_params)
    when "postgresql"
      require 'pg'
      @adapter = "postgre"
      db_params = {:user => DB_SETTING["username"], :password => DB_SETTING["password"], :dbname => DB_SETTING["database"], :host => DB_SETTING["host"]}
      @client = PG::Connection.open(db_params)
    end
  end

  def insert_multiple(sql,values)
    case DB_SETTING["adapter"] 
    when "sqlite3"
      values.each do |val|
        @client.execute(sql.sub("?",val))
      end
    when "mysql2"
      res = @client.exec(sql.sub("?",values.join(",")))
      res
    when "postgresql"
      res = @client.exec(sql.sub("?",values.join(",")))
      res
    end
  end

  def xinsert(table,args)
    query("insert into #{table} (#{args.keys.join(",")}) values(#{("?" * args.keys.length).split("").join(",")})",*args.values)
  end

  def query(*args)
    case DB_SETTING["adapter"] 
    when "sqlite3"
      m = args[0].match(/[sS][eE][lL][eE][cC][tT]\s+(.*)\s+[fF][rR][oO][mM]\s+(\S*)\s/)
      if m
        rows = []
        if m[1] =~ /\*/
          fields = []
          @client.table_info(m[2]) do |row|
            fields << row["name"]
          end
        else
          fields = m[1].split(/,/).map{|d| d.gsub(/\s/,"")}
        end
        @client.execute(*args.map{|f| f.kind_of?(Date) || f.kind_of?(Time)  ? f.strftime("%Y-%m-%d %H:%M:%S.%N")[0..-4] : f}) do |rec|
          t = {}
          fields.each_with_index do |f,i|
            t[f] = rec[i]
          end
          rows.push(t)
        end
        rows
      else
        @ret = @client.execute(*args.map{|f| f.kind_of?(Date) || f.kind_of?(Time)  ? f.strftime("%Y-%m-%d %H:%M:%S.%N")[0..-4] : f})
      end
    when "mysql2"
      @client.xquery(*args)
    when "postgresql"
      cnt=0
      sql = args[0].gsub("?"){|w| "$" + (cnt+=1).to_s }
      m = args[0].match(/^[iI][nN][sS][eE][rR][tT]\s+/)
      if m
        sql += " returning *"
      end
      @res = @client.exec(sql,args[1 .. -1].map{|f| f.kind_of?(Date) || f.kind_of?(Time)  ? f.strftime("%Y-%m-%d %H:%M:%S.%N")[0..-4] : f}.flatten)
      if m
        @last_id = 0
        if @res.map{|r| r}.length > 0
          @last_id = @res[0]["id"].to_i
        end
      end
      @res
    end
  end

  def affected_rows
    case DB_SETTING["adapter"] 
    when "sqlite3"
      @ret[0][0]
    when "mysql2"
      @client.affected_rows()
    when "postgresql"
      @res.result_status
    end
  end

  def last_id
    case DB_SETTING["adapter"] 
    when "sqlite3"
      @client.last_insert_row_id
    when "mysql2"
      @client.last_id
    when "postgresql"
      @last_id
    end
  end

  def close
    @client.close
  end
end