Class: Mysql2wrapper::Client

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

Defined Under Namespace

Classes: UpdateAllClass

Constant Summary collapse

UPDATE_ALL = 
UpdateAllClass
QUERY_BASE_COLOR = 
35
QUERY_SPECIAL_COLOR = 
31
MULTIPLE_INSERT_DEFAULT = 
100

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(config, _logger = Logger.new(STDOUT)) ⇒ Client

Returns a new instance of Client.



19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
# File 'lib/mysql2wrapper/client.rb', line 19

def initialize(config,_logger=Logger.new(STDOUT))
  self.logger = _logger
  if self.logger
    # TODO パスワードの隠し
    config_c = config.clone
    if config_c[:password].present?
      config_c[:password] = '****'
    end
    self.logger.info "mysql2 client created with #{config_c.inspect}"
  end
  self.client = Mysql2::Client.new(config)
  self.config = config
  # サーバが古いので一応問題あるけど以下の方向で
  # http://kennyqi.com/archives/61.html
  self.class.query(self.client,"SET NAMES 'utf8'",self.logger)
  self.class.query(self.client,"SET SQL_AUTO_IS_NULL=0",self.logger)
  self.class.query(self.client,"SET SQL_MODE=STRICT_ALL_TABLES",self.logger)
end

Instance Attribute Details

#affected_rows ⇒ Object (readonly)

Returns the value of attribute affected_rows.



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

def affected_rows
  @affected_rows
end

#client ⇒ Object

Returns the value of attribute client.



7
8
9
 
# File 'lib/mysql2wrapper/client.rb', line 7

def client
  @client
end

#config ⇒ Object

Returns the value of attribute config.



7
8
9
 
# File 'lib/mysql2wrapper/client.rb', line 7

def config
  @config
end

#last_query ⇒ Object

Returns the value of attribute last_query.



7
8
9
 
# File 'lib/mysql2wrapper/client.rb', line 7

def last_query
  @last_query
end

#logger ⇒ Object

Returns the value of attribute logger.



7
8
9
 
# File 'lib/mysql2wrapper/client.rb', line 7

def logger
  @logger
end

Class Method Details

.config_from_yml(yml_path, environment, db_server_name = nil) ⇒ Object

db_server_nameはDB名そのもの（複数DB対応）



269
270
271
272
273
274
275
276
277
278
279
 
# File 'lib/mysql2wrapper/client.rb', line 269

def self.config_from_yml(yml_path,environment,db_server_name=nil)
  raise "yaml not found(#{yml_path})" unless File.exists?(yml_path)
  db_config = YAML.load_file(yml_path)[environment]
  if db_server_name
    db_config = db_config[db_server_name]
  end
  unless db_config
    raise "can not get db_config with env:#{environment}#{db_server_name ? "/db_server:#{db_server_name}":''}"
  end
  self.make_config_key_symbol(db_config)
end

.make_config_key_symbol(config) ⇒ Object 



102
103
104
105
106
107
108
 
# File 'lib/mysql2wrapper/client.rb', line 102

def self.make_config_key_symbol(config)
  new_config = {}
  config.each do |key,value|
    new_config[key.to_sym] = value
  end
  config = new_config
end

.query(client, sql_query, logger = nil, color = QUERY_BASE_COLOR) ⇒ Object 



38
39
40
41
42
43
44
45
46
47
48
49
50
 
# File 'lib/mysql2wrapper/client.rb', line 38

def self.query(client,sql_query,logger=nil,color=QUERY_BASE_COLOR)
  s = Time.now
  ret = client.query(sql_query)
  e = Time.now
  if logger
    # TODO too long sql shorten
    if sql_query.size > 1400
      sql_query = sql_query[0..600] + "\n<< trimed >>\n" + sql_query[(sql_query.size - 600)..(sql_query.size - 1)]
    end
    logger.info "[QUERY] "" \e[#{color}m (#{((e-s)*1000).round(2)}ms/#{client.affected_rows}rows) #{sql_query}\e[0m"
  end
  ret
end

Instance Method Details

#close ⇒ Object 



118
119
120
 
# File 'lib/mysql2wrapper/client.rb', line 118

def close
  self.client.close if self.client
end

#count(table_name, where = nil, key_name = '*') ⇒ Object 



110
111
112
113
114
115
116
 
# File 'lib/mysql2wrapper/client.rb', line 110

def count(table_name,where=nil,key_name='*')
  query = "SELECT COUNT(#{escape(key_name)}) AS cnt FROM #{escape(table_name)}"
  if where
    query = "#{query} #{parse_where where}"
  end
  query(query).first['cnt']
end

#databases ⇒ Object 



289
290
291
292
 
# File 'lib/mysql2wrapper/client.rb', line 289

def databases
  query = 'show databases'
  self.client.query(query).map{|ar|ar['Database']}
end

#dump ⇒ Object 



56
57
58
 
# File 'lib/mysql2wrapper/client.rb', line 56

def dump
  "#{self.config.inspect}\n#{self.client.pretty_inspect}"
end

#escape(str) ⇒ Object 



122
123
124
 
# File 'lib/mysql2wrapper/client.rb', line 122

def escape(str)
  self.client.escape(str)
end

#insert(table_name, data, multiple_insert_by = MULTIPLE_INSERT_DEFAULT) ⇒ Object 



228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
 
# File 'lib/mysql2wrapper/client.rb', line 228

def insert(table_name,data,multiple_insert_by=MULTIPLE_INSERT_DEFAULT)
  @affected_rows = 0 # 一応リセット
  affected_rows_total = 0
  query = "INSERT INTO `#{escape(table_name)}`"
  _datas = data.clone
  case _datas
  when Array
    ;
  when Hash
    _datas = [_datas]
  else
    raise ArgumentError, "data must be Array or Hash"
  end

  return nil if _datas.size == 0

  _datas.each_slice(multiple_insert_by).each do |rows|
    query = <<"EOS"
INSERT INTO `#{escape(table_name)}`
(#{rows.first.keys.map{|o|"`#{escape(o.to_s)}`"}.join(',')})
VALUES
#{
rows.map do |row|
"(#{
  row.map do |key,value|
    proceed_value(value)
  end.join(',')
})"
end.join(',')
}
EOS

    self.query(query.chomp)
    affected_rows_total += self.client.affected_rows
  end
  @affected_rows = affected_rows_total
end

#parse_where(v) ⇒ Object 



166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
 
# File 'lib/mysql2wrapper/client.rb', line 166

def parse_where(v)
  case v
  when String
    if v.size > 0
      "WHERE #{v}"
    else
      ''
    end
  when Hash
    "WHERE #{
      v.map do |key,value|
        case value
        when nil
          "`#{escape(key.to_s)}` IS NULL"
        when Array
          # ここ、、、自動で条件を抜くってのもできるけど、、、、まぁそれで意図しない結果になるより
          # エラーを上げるほうが妥当だろうて
          raise "at least one value needs for #{key.to_s} (can not call in statement with no value)" if value.size == 0
          "`#{escape(key.to_s)}` in (#{value.map{|o|proceed_value(o)}.join(',')})"
        else
          "`#{escape(key.to_s)}` = #{proceed_value(value)}"
        end
      end.join(' AND ')
    }"
  else
    raise 'can set String or Hash on where'
  end
end

#proceed_value(value) ⇒ Object 



199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
 
# File 'lib/mysql2wrapper/client.rb', line 199

def proceed_value(value)
  case value
  when Proc
    "'#{escape(value.call.to_s)}'"
  when nil
    "NULL"
  when TrueClass,FalseClass
    if value
      "'1'"
    else
      "'0'"
    end
  when Time,DateTime
    "'#{value.strftime("%Y-%m-%d %H:%M:%S")}'"
  when Date
    "'#{value.strftime("%Y-%m-%d")}'"
  when String
    if value.respond_to?(:function_sql?) && value.function_sql?
      "#{value.to_s}"
    else
      value = escape(value)
      #value = value.encode('utf-8', {:invalid => :replace, :undef => :replace})
      "'#{value}'"
    end
  else
    "'#{escape(value.to_s)}'"
  end
end

#query(str_query, color = QUERY_BASE_COLOR) ⇒ Object 



60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'lib/mysql2wrapper/client.rb', line 60

def query(str_query,color=QUERY_BASE_COLOR)
  begin
    case str_query
    when /^SET /,'COMMIT','ROLLBACK'
    else
      self.last_query = str_query
    end
  rescue ArgumentError => e
    # バイナリが絡むとstr_queryに正規表現とかかけられない
    # invalid sequence エラーになる
    self.last_query = str_query
  end
  res = self.class.query(self.client,str_query,self.logger,color)
  @affected_rows = self.client.affected_rows
  res
end

#sanitize(str) ⇒ Object 



126
127
128
 
# File 'lib/mysql2wrapper/client.rb', line 126

def sanitize(str)
  escape(str)
end

#select(table_name, select, where = nil) ⇒ Object 



158
159
160
161
162
163
164
 
# File 'lib/mysql2wrapper/client.rb', line 158

def select(table_name,select,where=nil)
  query = "SELECT #{select} FROM `#{escape table_name}`"
  if where
    query = "#{query} #{parse_where(where)}"
  end
  query query
end

#stop_logging ⇒ Object 



52
53
54
 
# File 'lib/mysql2wrapper/client.rb', line 52

def stop_logging
  self.logger = nil
end

#table_information_schema(type, table_name) ⇒ Object 



306
307
308
309
310
311
312
313
314
315
316
317
 
# File 'lib/mysql2wrapper/client.rb', line 306

def table_information_schema(type,table_name)
  query = "
SELECT
*
FROM
INFORMATION_SCHEMA.#{type}
WHERE
table_name = '#{escape(table_name)}' AND
table_schema = '#{escape(self.config[:database])}'
"
    query(query).to_a
end

#table_informations ⇒ Object 



294
295
296
297
298
299
300
301
302
303
304
 
# File 'lib/mysql2wrapper/client.rb', line 294

def table_informations
  query = "select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '#{escape(self.config[:database])}' AND TABLE_TYPE = 'BASE TABLE'"
  tables = self.client.query(query).to_a
  tables.each do |table|
    table['COLUMNS'] = table_information_schema('COLUMNS',table['TABLE_NAME'])
    table['INDEXES'] = table_information_schema('STATISTICS',table['TABLE_NAME'])
    query = "SHOW CREATE TABLE `#{escape(table['TABLE_NAME'])}`"
    table['CREATE TABLE'] = self.client.query(query).first['Create Table']
  end
  tables
end

#table_names ⇒ Object 



285
286
287
 
# File 'lib/mysql2wrapper/client.rb', line 285

def table_names
  table_informations.map{|o|o['TABLE_NAME']}
end

#tables ⇒ Object 



281
282
283
 
# File 'lib/mysql2wrapper/client.rb', line 281

def tables
  table_informations.map{|o|o['TABLE_NAME']}
end

#transaction(&proc) ⇒ Object

HACKME トランザクションのネストをどう考えるか このライブラリを使うシチュエーションってのは トランザクションのネストを許さない場合ってな気がするので 一応エラーを上げるようにしてますが、、、、

Raises:

  • (ArgumentError) 


82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/mysql2wrapper/client.rb', line 82

def transaction(&proc)
  raise ArgumentError, "No block was given" unless block_given?
  #query "SET AUTOCOMMIT=0;",QUERY_SPECIAL_COLOR
  if @__inside_transaction
    # HACHME エラーの種別の検討
    raise StandardError, 'can not nest transaction!!!!'
  end
  @__inside_transaction = true
  query "BEGIN",QUERY_SPECIAL_COLOR
  begin
    yield
    query "COMMIT",QUERY_SPECIAL_COLOR
  rescue => e
    query "ROLLBACK",QUERY_SPECIAL_COLOR
    raise e
  ensure
    @__inside_transaction = false
  end
end

#update(table_name, hash, where) ⇒ Object

where は ‘WHERE’を付けずに指定 全行updateを使用するシチュエーションはほぼ0に 近いと思われるので、簡単には実行できなくしてます 実際に実行する際はwhereにUpdateAllを引数とします client.update ‘test01’,:v_int1=>3,Mysql2wrapper::Client::UPDATE_ALL



137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
 
# File 'lib/mysql2wrapper/client.rb', line 137

def update(table_name,hash,where)
  case where
  when '',nil
    raise ArgumentError, 'can not set blank or nil on where with update(you shoule use UpdateAllClass with where)'
  when UpdateAllClass.class
    where = nil
  when String,Hash
  else
    raise ArgumentError, "where must be String or UpdateAll"
  end
  query = "UPDATE `#{escape(table_name)}` SET #{
    hash.map do |key,value|
      "`#{escape(key.to_s)}` = #{proceed_value(value)}"
    end.join(',')
  }"
  if where
    query = "#{query} #{parse_where where}"
  end
  self.query(query)
end

#update_all_flag ⇒ Object 



195
196
197
 
# File 'lib/mysql2wrapper/client.rb', line 195

def update_all_flag
  self.class::UPDATE_ALL
end