class WIKK::SQL

WIKK_SQL wrapper for ruby mysql gem. @attr_reader [Numeric] affected_rows the number of rows changed, deleted, or added. @attr_reader [Mysql::Result] result the last query's result @attr_reader [Mysql] my the DB connection descriptor

Constants

VERSION

Attributes

affected_rows[R]
my[R]
result[R]

Public Class Methods

connect(db_config) { |sql| ... } click to toggle source

Create WIKK::SQL instance and set up the mySQL connection. @param db_config [Configuration] Configuration class, Hash, or any class with appropriate attr_readers. @yieldparam sql [WIKK_SQL] if a block is given. @return [NilClass] if block is given, and closes the mySQL connection. @return [WIKK_SQL] if no block is given, and caller must call sql.close

# 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) { |res| ... } click to toggle source

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 @param db_config [Configuration] Configuration class, Hash, or any class with appropriate attr_readers. @param the_query [String] Sql query to send to DB server. @param with_table_names [Boolean] if TrueClass, then table names are included in the hash keys. @raise [Mysql] passes on Mysql errors, freeing the result. @yieldparam [Hash] each result row @note @result and @affected_rows are also set via call to query().

# 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) { |y| ... } click to toggle source

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 @param db_config [Configuration] Configuration class, Hash, or any class with appropriate attr_readers. @param the_query [String] Sql query to send to DB server. @raise [Mysql] passes on Mysql errors, freeing the result. @yieldparam [Array] each result row @note @result and @affected_rows are also set via call to query().

# 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) { |**res| ... } click to toggle source

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. @param db_config [Configuration] Configuration class, Hash, or any class with appropriate attr_readers. This can be used with keyword arguments. eg. each_sym { |key1:, key2:, …, **rest_of_args| do something } @param the_query [String] Sql query to send to DB server. @raise [Mysql] passes on Mysql errors, freeing the result. @yieldparam [Hash] each result row @note @result and @affected_rows are also set via call to query().

# 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| ... } click to toggle source

Create WIKK::SQL instance and set up the mySQL connection, and Run a query on the DB server. @param db_config [Configuration] Configuration class, Hash, or any class with appropriate attr_readers. @param the_query [String] Sql query to send to DB server. @raise [Mysql] passes on Mysql errors, freeing the result. @yieldparam [Mysql::Result] @result and @affected_rows are also set. @return [Mysql::Result] @result and @affected_rows are also set.

# 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

Public Instance Methods

close() click to toggle source

close the mySQL connection. Call only if connect was not given a block. @return [NilClass]

# File lib/wikk_sql.rb, line 61
def close
  @my.close if @my != nil
  return (@my = nil)
end
connect(db_config) { || ... } click to toggle source

Set up the mySQL connection. @param db_config [Configuration] Configuration class, Hash, or any class with appropriate attr_readers. @yieldparam [] if a block is given. @return [NilClass] if block is given, and closes the mySQL connection. @return [WIKK_SQL] if no block is given, and caller must call sql.close

# 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
Also aliased as: open
each_hash(the_query, with_table_names=false) { |row| ... } click to toggle source

Yields query result row by row, as Hash, using String keys @param the_query [String] Sql query to send to DB server. @param with_table_names [Boolean] if TrueClass, then table names are included in the hash keys. @raise [Mysql] passes on Mysql errors, freeing the result. @yieldparam [Hash] each result row @note @result and @affected_rows are also set via call to query().

# 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) { |row| ... } click to toggle source

Yields query query results row by row, as Array @param the_query [String] Sql query to send to DB server. @raise [Mysql] passes on Mysql errors, freeing the result. @yieldparam [Array] each result row @note @result and @affected_rows are also set via call to query().

# 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_with_object({}) { |(k,v),h| h = v }| ... } click to toggle source

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 } @param the_query [String] Sql query to send to DB server. @raise [Mysql] passes on Mysql errors, freeing the result. @yieldparam [Hash] each result row @note @result and @affected_rows are also set via call to query().

# 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() click to toggle source

Get the database field attributes from a query result. @yieldparam [Array] Array of field records @note fields are name (of field), table (name), def, type, length, max_length, flags,decimals

# File lib/wikk_sql.rb, line 177
def fetch_fields
  @result.fetch_fields
end
open(db_config)
Alias for: connect
query(the_query) { |result| ... } click to toggle source

Run a query on the DB server. @param the_query [String] Sql query to send to DB server. @raise [Mysql] passes on Mysql errors, freeing the result. @yieldparam [Mysql::Result] @result and @affected_rows are also set. @return [Mysql::Result] @result and @affected_rows are also set.

# 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() { || ... } click to toggle source

Perform a transaction in the passed block. RollBACK on error, otherwise COMMIT @yieldparam [] yields to block, where the queries are performed. @raise [Mysql] passes on Mysql errors, freeing the result.

# 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