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.3'

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.



227
228
229
230
231
232
233
234
235
236
237
238
239
240
 
# File 'lib/wikk_sql.rb', line 227

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.



206
207
208
209
210
211
212
213
214
215
216
217
 
# File 'lib/wikk_sql.rb', line 206

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.



250
251
252
253
254
255
256
257
258
259
260
261
262
263
 
# File 'lib/wikk_sql.rb', line 250

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.



185
186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/wikk_sql.rb', line 185

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) 


60
61
62
63
 
# File 'lib/wikk_sql.rb', line 60

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
50
51
52
53
54
 
# 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

  begin
    @my = Mysql::new(db_config.host, db_config.dbuser, db_config.key, db_config.db ) 
  rescue Exception => e
    @my = nil
    raise e
  end
  #@@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.



141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
 
# File 'lib/wikk_sql.rb', line 141

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.



116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
 
# File 'lib/wikk_sql.rb', line 116

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.



166
167
168
169
170
 
# File 'lib/wikk_sql.rb', line 166

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



175
176
177
 
# File 'lib/wikk_sql.rb', line 175

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.



70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
 
# File 'lib/wikk_sql.rb', line 70

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.



97
98
99
100
101
102
103
104
105
106
107
108
109
 
# File 'lib/wikk_sql.rb', line 97

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