class Dbwrapper::DB
Attributes
adapter[R]
client[R]
result[R]
Public Class Methods
new(option)
click to toggle source
# File lib/dbwrapper.rb, line 9 def initialize(option) setting = {}.tap{|h| option.each{|k,v| h[k.to_sym] = v}} raise ArgumentError,"database is not specified! for sqlite3" unless setting[:database] @adapter = setting[:adapter] case @adapter when "sqlite3" require 'sqlite3' @client = SQLite3::Database.new(setting[:database]) @client.execute("PRAGMA count_changes=ON;") when "mysql2" require 'mysql2-cs-bind' db_params = {:database => setting[:database]} if setting[:username] db_params[:username] = setting[:username] end if setting[:password] db_params[:password] = setting[:password] end if setting[:host] db_params[:host] = setting[:host] end if setting[:port] db_params[:port] = setting[:port] end @client = Mysql2::Client.new(db_params) when "postgresql" require 'pg' require 'pg_typecast' db_params = {:dbname => setting[:database]} if setting[:host] db_params[:host] = setting[:host] end if setting[:port] db_params[:port] = setting[:port] end if setting[:username] db_params[:user] = setting[:username] end if setting[:password] db_params[:password] = setting[:password] end @client = PG::Connection.open(db_params) else raise ArgumentError,"adapter is not specified. sqlite3 or mysql2 or postgresql" end if setting[:log] @log = Logger.new(setting[:log]) else @log = Logger.new(STDOUT) end if setting[:log_level] case setting[:log_level] when "fatal" @log.level = Logger::FATAL when "error" @log.level = Logger::ERROR when "warn" @log.level = Logger::WARN when "info" @log.level = Logger::INFO when "debug" @log.level = Logger::DEBUG end else @log.level = Logger::WARN end end
Public Instance Methods
affected_rows()
click to toggle source
# File lib/dbwrapper.rb, line 153 def affected_rows case @adapter when "sqlite3" if @result.count > 0 @result[0][0] else 0 end when "mysql2" @client.affected_rows() when "postgresql" @result.result_status end end
backup_table(t,path)
click to toggle source
# File lib/dbwrapper.rb, line 179 def backup_table(t,path) FileUtils.mkdir_p(path) results = query("SELECT * FROM #{t}") CSV.open("#{path}/#{t}.csv", "wb") do |csv| csv << results.first.keys if results.first results.each do |result| csv << result.values.map{|r| if r == nil then 'null' elsif r.is_a?(Numeric) then r elsif r.is_a?(Time) then "'#{r.strftime("%Y-%m-%d %H:%M:%S.%N")[0..-4]}'" else "'#{r}'" end } end end end
close()
click to toggle source
# File lib/dbwrapper.rb, line 212 def close case @adapter when "postgresql" @client.finish else @client.close end end
last_id()
click to toggle source
# File lib/dbwrapper.rb, line 168 def last_id case @adapter when "sqlite3" @client.last_insert_row_id when "mysql2" @client.last_id when "postgresql" @last_id end end
query(*args)
click to toggle source
# File lib/dbwrapper.rb, line 101 def query(*args) @log.debug(args) case @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 @result = @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" @result = @client.xquery(*args) @result.to_a 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 @result = @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 @result.count > 0 @last_id = @result[@result.count - 1]["id"].to_i end res = @result elsif args[0].match(/[sS][eE][lL][eE][cC][tT]\s+(.*)\s+[fF][rR][oO][mM]\s+(\S*)\s*/) res = @result.map{|r| {}.tap{|h| r.each{|k,v| h[k.to_s] = v}}} else res = @result end res.to_a end end
restore_table(t,path)
click to toggle source
# File lib/dbwrapper.rb, line 190 def restore_table(t,path) cnt = 0 fields = nil CSV.foreach("#{path}/#{t}.csv") do |row| if cnt == 0 fields = row.join(",") else query("insert into #{t} (#{fields}) values (#{row.join(",")})") end cnt+=1 end end
truncate(t)
click to toggle source
# File lib/dbwrapper.rb, line 203 def truncate(t) case @adapter when "sqlite3" query("delete from #{t}") when "mysql2","postgresql" query("TRUNCATE TABLE #{t}") end end
xinsert(table,args)
click to toggle source
# File lib/dbwrapper.rb, line 78 def xinsert(table,args) raise ArgumentError,"args should not be empty" if args.size == 0 unless args.is_a?(Array) args = [args] end key_set = args.map{|a| a.keys.join(",")}.uniq if key_set.length != 1 raise ArgumentError,"keys of records shuoud be same." end keys = args[0].keys raise ArgumentError,"key shuoud be at least one." if keys.length == 0 case @adapter when "sqlite3" args.each do |r| query("insert into #{table} (#{keys.join(",")}) values (#{r.values.map{|v| "'#{v}'"}.join(",")})") end when "postgresql","mysql2" vals = args.map(&:values).map{|r| "(" + r.map{|v| "'#{v}'"}.join(",") + ")"} sql = "insert into #{table} (#{keys.join(",")}) values #{vals.join(",")}" query(sql) end end