Class: WIKK::SQL

Inherits:
Object
  • Object
show all
Defined in:
lib/wikk_mysql2.rb,
lib/wikk_ruby_mysql.rb,
lib/wikk_sql.rb

Overview

WIKK_SQL wrapper for ruby mysql gem.

Constant Summary collapse

VERSION = 
'0.2.1'

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Instance Attribute Details

#affected_rows ⇒ Numeric (readonly)

the number of rows changed, deleted, or added.

Returns:

  • (Numeric) —

    the current value of affected_rows



10
11
12
 
# File 'lib/wikk_mysql2.rb', line 10

def affected_rows
  @affected_rows
end

#my ⇒ Mysql (readonly)

the DB connection descriptor

Returns:

  • (Mysql) —

    the current value of my



10
11
12
 
# File 'lib/wikk_mysql2.rb', line 10

def my
  @my
end

#result ⇒ Mysql::Result (readonly)

the last query’s result

Returns:

  • (Mysql::Result) —

    the current value of result



10
11
12
 
# File 'lib/wikk_mysql2.rb', line 10

def result
  @result
end

Class Method Details

.connect(db_config) {|sql| ... } ⇒ NilClass, WIKK_SQL

Create WIKK::SQL instance and set up the mySQL connection.

Parameters:

  • db_config (Configuration) —

    Configuration class, Hash, or any class with appropriate attr_readers.

Yield Parameters:

  • sql (WIKK_SQL) —

    if a block is given.

Returns:

  • (NilClass) —

    if block is given, and closes the mySQL connection.

  • (WIKK_SQL) —

    if no block is given, and caller must call sql.close



18
19
20
21
22
23
24
25
26
27
 
# File 'lib/wikk_mysql2.rb', line 18

def self.connect(db_config)
  sql = self.new
  sql.connect(db_config)
  if block_given?
    yield sql
    return sql.close
  else
    return sql
  end
end

.each_hash(db_config, query, with_table_names = false) {|each| ... } ⇒ Object

Note:

@result and @affected_rows are also set via call to query().

Create WIKK::SQL instance and set up the mySQL connection, and Run a query on the DB server. Yields query result row by row, as Hash, using String keys

Parameters:

  • db_config (Configuration) —

    Configuration class, Hash, or any class with appropriate attr_readers.

  • the_query (String) —

    Sql query to send to DB server.

  • with_table_names (Boolean) (defaults to: false) —

    if TrueClass, then table names are included in the hash keys.

Yield Parameters:

  • each (Hash) —

    result row

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



333
334
335
336
337
338
339
340
341
342
 
# File 'lib/wikk_mysql2.rb', line 333

def self.each_hash(db_config, query, with_table_names = false, &block)
  self.connect( db_config ) do |sql|
    if block_given?
      sql.each_hash(query, with_table_names, &block)
      return sql.affected_rows
    else
      return sql.each_hash(query, with_table_names)
    end
  end
end

.each_param(db_config, query) {|for| ... } ⇒ Object

Note:

@result and @affected_rows are also set via call to query().

Create WIKK::SQL instance and set up the mySQL connection, and Run a query on the DB server. Yields query result row by row, as Hash using Symbol keys, so can’t have table names included.

This can be used with keyword arguments. eg. each_sym { key1:, key2:, …, **rest_of_args do something }

Parameters:

  • db_config (Configuration) —

    Configuration class, Hash, or any class with appropriate attr_readers.

  • the_query (String) —

    Sql query to send to DB server.

Yield Parameters:

  • for (**Hash) —

    each result row, which can be passed to named args in a block

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



373
374
375
376
377
378
379
380
381
382
 
# File 'lib/wikk_mysql2.rb', line 373

def self.each_param(db_config, query, &block)
  self.connect( db_config ) do |sql|
    if block_given?
      sql.each_param(query, &block)
      return sql  # May be useful to access the affected rows
    else
      return sql.each_sym(query)
    end
  end
end

.each_row(db_config, query) {|each| ... } ⇒ Object

Note:

@result and @affected_rows are also set via call to query().

Create WIKK::SQL instance and set up the mySQL connection, and Run a query on the DB server. Yields query query results row by row, as Array

Parameters:

  • db_config (Configuration) —

    Configuration class, Hash, or any class with appropriate attr_readers.

  • the_query (String) —

    Sql query to send to DB server.

Yield Parameters:

  • each (Array) —

    result row

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



313
314
315
316
317
318
319
320
321
322
 
# File 'lib/wikk_mysql2.rb', line 313

def self.each_row(db_config, query, &block)
  self.connect db_config do |sql|
    if block_given?
      sql.each_row(query, &block)
      return sql.affected_rows
    else
      return sql.each_row(query)
    end
  end
end

.each_sym(db_config, query) {|each| ... } ⇒ Object

Note:

@result and @affected_rows are also set via call to query().

Create WIKK::SQL instance and set up the mySQL connection, and Run a query on the DB server. Yields query result row by row, as Hash using Symbol keys, so can’t have table names included.

This can be used with keyword arguments. eg. each_sym { key1:, key2:, …, **rest_of_args do something }

Parameters:

  • db_config (Configuration) —

    Configuration class, Hash, or any class with appropriate attr_readers.

  • the_query (String) —

    Sql query to send to DB server.

Yield Parameters:

  • each (Hash) —

    result row

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



353
354
355
356
357
358
359
360
361
362
 
# File 'lib/wikk_mysql2.rb', line 353

def self.each_sym(db_config, query, &block)
  self.connect( db_config ) do |sql|
    if block_given?
      sql.each_sym(query, &block)
      return sql  # May be useful to access the affected rows
    else
      return sql.each_sym(query)
    end
  end
end

.escape(query) ⇒ String

Call underlying connector’s escape of the query string

Parameters:

  • query (String) —

    The SQL query

Returns:

  • (String) —

    The SQL query, escaped to remove vulnerabilities



78
79
80
 
# File 'lib/wikk_mysql2.rb', line 78

def self.escape(query)
  Mysql2::Client.escape(query)
end

.query(db_config, the_query) {|@result| ... } ⇒ Mysql::Result

Create WIKK::SQL instance and set up the mySQL connection, and Run a query on the DB server.

Parameters:

  • db_config (Configuration) —

    Configuration class, Hash, or any class with appropriate attr_readers.

  • the_query (String) —

    Sql query to send to DB server.

Yield Parameters:

  • @result (Mysql::Result) —

    and @affected_rows are also set.

Returns:

  • (Mysql::Result) —

    @result and @affected_rows are also set.

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



293
294
295
296
297
298
299
300
301
302
303
 
# File 'lib/wikk_mysql2.rb', line 293

def self.query(db_config, the_query)
  self.connect db_config do |sql|
    result = sql.query(the_query)
    if block_given?
      yield result
      return sql.affected_rows
    else
      return result
    end
  end
end

Instance Method Details

#close ⇒ NilClass

close the mySQL connection. Call only if connect was not given a block.

Returns:

  • (NilClass) 


70
71
72
73
 
# File 'lib/wikk_mysql2.rb', line 70

def close
  @my.close if @my != nil
  return (@my = nil)
end

#connect(db_config) {|[]| ... } ⇒ NilClass, WIKK_SQL Also known as: open

Set up the mySQL connection.

Parameters:

  • db_config (Configuration) —

    Configuration class, Hash, or any class with appropriate attr_readers.

Yield Parameters:

  • [] —

    if a block is given.

Returns:

  • (NilClass) —

    if block is given, and closes the mySQL connection.

  • (WIKK_SQL) —

    if no block is given, and caller must call sql.close

Raises:

  • (Mysql::Error) 


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
 
# File 'lib/wikk_mysql2.rb', line 35

def connect(db_config)
  if db_config.instance_of?(Hash)
    sym = db_config.transform_keys(& :to_sym )
    db_config = Struct.new(*(k = sym.keys)).new(*sym.values_at(*k))
  end

  begin
    @my = Mysql2::Client.new( host: db_config.host,
                              database: db_config.db,
                              username: db_config.dbuser,
                              password: db_config.key,
                              socket: db_config.respond_to?(:socket) ? db_config.socket : nil,
                              port: db_config.respond_to?(:port) ? db_config.port : nil,
                              flags: 'SESSION_TRACK',
                              init_command: 'SET @@SESSION.session_track_schema=ON'
                            )
  rescue StandardError => e
    @my = nil
    raise e
  end
  raise Mysql2::Error, 2002 if @my.nil?

  # @@my.reconnect = true
  if block_given?
    yield
    return close
  end
  return @my
end

#each_hash(the_query, with_table_names = false) {|each| ... } ⇒ Array

Note:

@result and @affected_rows are also set via call to query().

Yields query result row by row, as Hash, using String keys

Parameters:

  • the_query (String) —

    Sql query to send to DB server.

  • with_table_names (Boolean) (defaults to: false) —

    if TrueClass, then table names are included in the hash keys.

Yield Parameters:

  • each (Hash) —

    result row

Returns:

  • (Array) —

    all rows, if no block is given

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
 
# File 'lib/wikk_mysql2.rb', line 183

def each_hash(the_query, with_table_names = false, &block)
  begin
    if with_table_names
      # We have to build the hash ourselves, if we want table names included
      query(the_query, { as: :array, cache_rows: false })
      if @result != nil
        fields = @result.fields
        tables = @result.respond_to?(:tables) ? @result.tables : [] # My addition to mysql2 results.c

        result = []
        @result.each do |row|
          hrow = {}
          (0...row.length).each do |i|
            field_name = tables[i].nil? ? fields[i] : "#{tables[i]}.#{fields[i]}"
            hrow[field_name] = row[i]
          end
          yield hrow
          result << hrow
        end
        return result
      end
    else
      query(the_query, { as: :hash, cache_rows: false })
      if @result != nil
        if block_given?
          @result.each(&block)
        else
          return @result.to_a
        end
      end
    end
  rescue Mysql2::Error => e
    # puts "#{e.errno}: #{e.error}"
    raise e
  ensure
    if block_given? && @result != nil
      @result.free
    end
  end
end

#each_param(the_query) {|each| ... } ⇒ Array

Note:

@result and @affected_rows are also set via call to query().

Yields query result row by row, as **Hash using Symbol keys, so can’t have table names included. This can be used with keyword arguments. eg. each_sym { |key1:, key2:, …, **rest_of_args| do something }

Parameters:

  • the_query (String) —

    Sql query to send to DB server.

Yield Parameters:

  • each (**Hash) —

    result row

Returns:

  • (Array) —

    if no block is given, returns an Array of Hash’d rows, with symbol as the key

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



255
256
257
258
259
260
261
262
263
264
 
# File 'lib/wikk_mysql2.rb', line 255

def each_param(the_query)
  query(the_query, { symbolize_keys: true, as: :hash, cache_rows: false })
  if @result != nil
    if block_given?
      @result.each { |row| yield(**row) }
    else
      return @result.to_a
    end
  end
end

#each_row(the_query) {|each| ... } ⇒ Array

Note:

@result and @affected_rows are also set via call to query().

Yields query query results row by row, as Array

Parameters:

  • the_query (String) —

    Sql query to send to DB server.

Yield Parameters:

  • each (Array) —

    result row

Returns:

  • (Array) —

    Array of rows

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
 
# File 'lib/wikk_mysql2.rb', line 150

def each_row(the_query, &block)
  begin
    query(the_query, { as: :array, cache_rows: false })
    unless @result.nil?
      if block_given?
        @affected_rows = @result.count # This is non-zero is we do a select, and get results.
        @result.each(&block)
      else
        result = []
        @result.each { |row| result << row }
        return result
      end
    end
  rescue Mysql2::Error => e
    # puts "#{e.errno}: #{e.error}"
    raise e
  ensure
    if block_given? && @result != nil
      @result.free
    end
  end
end

#each_sym(the_query) {|each| ... } ⇒ Array

Note:

@result and @affected_rows are also set via call to query().

Yields query result row by row, as Hash using Symbol keys, so can’t have table names included. This can be used with keyword arguments. eg. each_sym { |key1:, key2:, …, **rest_of_args| do something }

Parameters:

  • the_query (String) —

    Sql query to send to DB server.

Yield Parameters:

  • each (Hash) —

    result row

Returns:

  • (Array) —

    if no block is given, returns an Array of Hash’d rows, with symbol as the key

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



234
235
236
237
238
239
240
241
242
243
 
# File 'lib/wikk_mysql2.rb', line 234

def each_sym(the_query, &block)
  query(the_query, { symbolize_keys: true, as: :hash, cache_rows: false })
  if @result != nil
    if block_given?
      @result.each(&block)
    else
      return @result.to_a
    end
  end
end

#escape(query) ⇒ String

Call underlying connector’s escape of the query string

Parameters:

  • query (String) —

    The SQL query

Returns:

  • (String) —

    The SQL query, escaped to remove vulnerabilities



85
86
87
 
# File 'lib/wikk_mysql2.rb', line 85

def escape(query)
  @my.escape(query)
end

#fetch_fields ⇒ Array

Note:

fields are name (of field), table (name), def, type, length, max_length, flags,decimals

Get the database field attributes from a query result.

Returns:

  • (Array) —

    [Mysql::Field] Array of field records



270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
 
# File 'lib/wikk_mysql2.rb', line 270

def fetch_fields
  fields = @result.fields
  field_types = @result.field_types
  tables = @result.respond_to?(:tables) ? @result.tables : [] # My addition to mysql2 results.c
  fields_arr = []
  (0...@result.fields.length).each do |i|
    fields_arr[i] = MySQL_FIELD.new(
      name: fields[i],
      table: tables[i],
      db: databases[i],
      type: field_types[i]
    )
  end
  return fields_arr
end

#query(the_query) {|@result| ... } ⇒ Mysql::Result

Run a query on the DB server.

Parameters:

  • the_query (String) —

    Sql query to send to DB server.

Yield Parameters:

  • @result (Mysql::Result) —

    and @affected_rows are also set.

Returns:

  • (Mysql::Result) —

    @result and @affected_rows are also set.

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
 
# File 'lib/wikk_mysql2.rb', line 95

def query(the_query, options = nil )
  raise Mysql2::Error, 2002 if @my.nil?

  # Default to returning values as an array, with no caching of previous results
  # This is consistent with ruby-mysql
  options ||= { as: :array, cache_rows: false }
  begin
    if @result != nil
      @result.free # Free any result we had left over from previous use.
      @result = nil
    end
    @affected_rows = 0 # incase this query crashes and burns, this will have a value.
    @result = @my.query(the_query, options)
    @affected_rows = @my.affected_rows # This is non-zero for select/insert/delete/update of rows
    if block_given?
      yield @result
    else
      return @result
    end
  rescue Mysql2::Error => e
    if @result != nil
      @result.free # Free any result we had left over from previous use.
      @result = nil
    end
    raise e
  end
end

#query_array {|each| ... } ⇒ Array

Note:

@result and @affected_rows are also set via call to query().

Yields query query results row by row, as Array

Parameters:

  • the_query (String) —

    Sql query to send to DB server.

Yield Parameters:

  • each (Array) —

    result row

Returns:

  • (Array) —

    Array of rows

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
 
# File 'lib/wikk_mysql2.rb', line 173

def each_row(the_query, &block)
  begin
    query(the_query, { as: :array, cache_rows: false })
    unless @result.nil?
      if block_given?
        @affected_rows = @result.count # This is non-zero is we do a select, and get results.
        @result.each(&block)
      else
        result = []
        @result.each { |row| result << row }
        return result
      end
    end
  rescue Mysql2::Error => e
    # puts "#{e.errno}: #{e.error}"
    raise e
  ensure
    if block_given? && @result != nil
      @result.free
    end
  end
end

#query_hash {|each| ... } ⇒ Array

Note:

@result and @affected_rows are also set via call to query().

Yields query result row by row, as Hash, using String keys

Parameters:

  • the_query (String) —

    Sql query to send to DB server.

  • with_table_names (Boolean) —

    if TrueClass, then table names are included in the hash keys.

Yield Parameters:

  • each (Hash) —

    result row

Returns:

  • (Array) —

    all rows, if no block is given

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



224
225
226
227
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
 
# File 'lib/wikk_mysql2.rb', line 224

def each_hash(the_query, with_table_names = false, &block)
  begin
    if with_table_names
      # We have to build the hash ourselves, if we want table names included
      query(the_query, { as: :array, cache_rows: false })
      if @result != nil
        fields = @result.fields
        tables = @result.respond_to?(:tables) ? @result.tables : [] # My addition to mysql2 results.c

        result = []
        @result.each do |row|
          hrow = {}
          (0...row.length).each do |i|
            field_name = tables[i].nil? ? fields[i] : "#{tables[i]}.#{fields[i]}"
            hrow[field_name] = row[i]
          end
          yield hrow
          result << hrow
        end
        return result
      end
    else
      query(the_query, { as: :hash, cache_rows: false })
      if @result != nil
        if block_given?
          @result.each(&block)
        else
          return @result.to_a
        end
      end
    end
  rescue Mysql2::Error => e
    # puts "#{e.errno}: #{e.error}"
    raise e
  ensure
    if block_given? && @result != nil
      @result.free
    end
  end
end

#query_sym {|each| ... } ⇒ Array

Note:

@result and @affected_rows are also set via call to query().

Yields query result row by row, as Hash using Symbol keys, so can’t have table names included. This can be used with keyword arguments. eg. each_sym { |key1:, key2:, …, **rest_of_args| do something }

Parameters:

  • the_query (String) —

    Sql query to send to DB server.

Yield Parameters:

  • each (Hash) —

    result row

Returns:

  • (Array) —

    if no block is given, returns an Array of Hash’d rows, with symbol as the key

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



245
246
247
248
249
250
251
252
253
254
 
# File 'lib/wikk_mysql2.rb', line 245

def each_sym(the_query, &block)
  query(the_query, { symbolize_keys: true, as: :hash, cache_rows: false })
  if @result != nil
    if block_given?
      @result.each(&block)
    else
      return @result.to_a
    end
  end
end

#transaction {|[]| ... } ⇒ Object

Perform a transaction in the passed block. RollBACK on error, otherwise COMMIT

Yield Parameters:

  • [] —

    yields to block, where the queries are performed.

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



128
129
130
131
132
133
134
135
136
137
138
139
140
141
 
# File 'lib/wikk_mysql2.rb', line 128

def transaction
  raise Mysql2::Error, 2002 if @my.nil?

  if block_given?
    begin
      @my.query('START TRANSACTION WITH CONSISTENT SNAPSHOT')
      yield # Start executing the query black.
      @my.query('COMMIT')
    rescue Mysql2::Error => e
      @my.query('ROLLBACK')
      raise e
    end
  end
end