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

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.



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

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.



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

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.



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

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.



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

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) 


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

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) 


36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
# 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
  raise Mysql::Error, 'Not Connected' 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| ... } ⇒ 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.



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

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.



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

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.



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

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



177
178
179
 
# File 'lib/wikk_sql.rb', line 177

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.



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

def query(the_query)
  raise Mysql::Error, 'Not Connected' if @my == nil
  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.



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

def transaction
  raise Mysql::Error, 'Not Connected' if @my == nil
  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