Class: WIKK::SQL

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

Overview

WIKK_SQL wrapper for ruby mysql gem.

Constant Summary collapse

VERSION = 
'0.1.2'

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_sql.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_sql.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_sql.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



20
21
22
23
24
25
26
27
28
29
 
# File 'lib/wikk_sql.rb', line 20

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.



222
223
224
225
226
227
228
229
230
231
232
233
234
235
 
# File 'lib/wikk_sql.rb', line 222

def self.each_hash(db_config, query, with_table_names=false)
  sql = self.new
  sql.open db_config
  begin
    if block_given?
      sql.each_hash(query, with_table_names) do |res|
        yield res
      end
    end
  ensure
    sql.close
  end
  return sql
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.



201
202
203
204
205
206
207
208
209
210
211
212
 
# File 'lib/wikk_sql.rb', line 201

def self.each_row(db_config, query)
  sql = self.new
  sql.open db_config
  begin
    if block_given?
      sql.each_row(query) { |y| yield y }
    end
  ensure
    sql.close
  end
  return sql
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.



245
246
247
248
249
250
251
252
253
254
255
256
257
258
 
# File 'lib/wikk_sql.rb', line 245

def self.each_sym(db_config, query)
  sql = self.new
  sql.open db_config
  begin
    if block_given?
      sql.each_sym(query) do |**res|
        yield **res
      end
    end
  ensure
    sql.close
  end
  return sql
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.



180
181
182
183
184
185
186
187
188
189
190
191
192
 
# File 'lib/wikk_sql.rb', line 180

def self.query(db_config, the_query)
  sql = self.new
  sql.open db_config
  begin
    result = sql.query(the_query)
    if block_given?
      yield result
    end
  ensure
    sql.close
  end
  return result
end

Instance Method Details

#close ⇒ NilClass

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

Returns:

  • (NilClass) 


55
56
57
58
 
# File 'lib/wikk_sql.rb', line 55

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



36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
# File 'lib/wikk_sql.rb', line 36

def connect(db_config)
  if db_config.class == Hash
    sym = db_config.each_with_object({}) { |(k,v),h| h[k.to_sym] = v }
    db_config = Struct.new(*(k = sym.keys)).new(*sym.values_at(*k))
  end

  @my = Mysql::new(db_config.host, db_config.dbuser, db_config.key, db_config.db ) 
  #@@my.reconnect = true
  if block_given?
    yield
    return close
  end
  return @my
end

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

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

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/wikk_sql.rb', line 136

def each_hash(the_query, with_table_names=false)
  begin
    query(the_query)
    if @result != nil && block_given?
      @affected_rows = @result.num_rows() #This is non-zero is we do a select, and get results.
      @result.each_hash(with_table_names) do |row|
        yield row
      end
    end
  rescue Mysql::Error => e
    #puts "#{e.errno}: #{e.error}"
    raise e
  ensure
    if block_given? && @result != nil
      @result.free
    end
  end
end

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

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

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
 
# File 'lib/wikk_sql.rb', line 111

def each_row(the_query)
  begin
    query(the_query)
    if @result != nil && block_given?
      @affected_rows = @result.num_rows() #This is non-zero is we do a select, and get results.
      @result.each do |row|
        yield row #return one row at a time to the block
      end
    end
  rescue Mysql::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| ... } ⇒ Object

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

Raises:

  • (Mysql) —

    passes on Mysql errors, freeing the result.



161
162
163
164
165
 
# File 'lib/wikk_sql.rb', line 161

def each_sym(the_query)
  each_hash(the_query) do |row_hash|
    yield row_hash.each_with_object({}) { |(k,v),h| h[k.to_sym] = v }
  end
end

#fetch_fields {|[Mysql::Field]| ... } ⇒ Object

Note:

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

Get the database field attributes from a query result.

Yield Parameters:

  • [Mysql::Field] (Array) —

    Array of field records



170
171
172
 
# File 'lib/wikk_sql.rb', line 170

def fetch_fields
  @result.fetch_fields
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.



65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/wikk_sql.rb', line 65

def query(the_query)
  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)
    @affected_rows = @my.affected_rows #This is non-zero for insert/delete/update of rows
    if block_given?
      yield @result
    else
      return @result
    end
  rescue Mysql::Error => e
    if result != nil 
      @result.free #Free any result we had left over from previous use.
      @result = nil
    end
    raise e
  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.



92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/wikk_sql.rb', line 92

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