Class: Dbwrapper::DB

Inherits:
Object
  • Object
show all
Defined in:
lib/dbwrapper.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(option) ⇒ DB

Returns a new instance of DB.

Raises:

  • (ArgumentError) 


9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
# 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

Instance Attribute Details

#adapter ⇒ Object (readonly)

Returns the value of attribute adapter.



8
9
10
 
# File 'lib/dbwrapper.rb', line 8

def adapter
  @adapter
end

#client ⇒ Object (readonly)

Returns the value of attribute client.



8
9
10
 
# File 'lib/dbwrapper.rb', line 8

def client
  @client
end

#result ⇒ Object (readonly)

Returns the value of attribute result.



8
9
10
 
# File 'lib/dbwrapper.rb', line 8

def result
  @result
end

Instance Method Details

#affected_rows ⇒ Object 



153
154
155
156
157
158
159
160
161
162
163
164
165
166
 
# 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) ⇒ Object 



179
180
181
182
183
184
185
186
187
188
 
# 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 ⇒ Object 



212
213
214
215
216
217
218
219
 
# File 'lib/dbwrapper.rb', line 212

def close
  case @adapter
  when "postgresql"
    @client.finish
  else
    @client.close
  end
end

#last_id ⇒ Object 



168
169
170
171
172
173
174
175
176
177
 
# 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) ⇒ Object 



101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
 
# 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) ⇒ Object 



190
191
192
193
194
195
196
197
198
199
200
201
 
# 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) ⇒ Object 



203
204
205
206
207
208
209
210
 
# 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) ⇒ Object

Raises:

  • (ArgumentError) 


78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# 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