Class DBrb
In: lib/DB.rb
Parent: Object

DBrb is a wrapper to facilitate working with Ruby DBI. The intention is to make the most commonly used DB functionality (as determined by me) easy and intuitive to use.

Examples

      db = DBrb.new("my_dbi_driver_string", "usr", "credentials")

      # return single value or array if a single row is selected
      i = db.sql("select max(id) from some_table")
      name, last_name =  db.sql("SELECT name, last_name FROM some_name WHERE id = ?", 1000)

should the two examples above return more than one row, the first one is used.

      db.sql("SELECT name, last_name FROM some_name WHERE id < ?", 1000) do |row|
              puts "#{row.name} #{row.last_name}"
              # in case of conflicts with existing methods, you can use:
              row["last_name"]
      end

The row that the sql iterates over is the standard DBI::Row, which has been supplemented with accessor methods named after the columns in case these don’t conflict with existing methods.

Methods

_sql_internal   close   dumpStmtStats   get_db   get_pstmt   new   sql   sql_count   to_s  

Public Class methods

[Source]

# File lib/DB.rb, line 46
        def initialize driver, usr=nil, pwd=nil, params=nil
                @driver=driver
                @usr=usr
                @pwd=pwd
                @params=params
                

                @pstatements = {}

                if block_given?
                        yield self
                        self.close
                end

        end

Public Instance methods

Closes all resources used by this object instance.

[Source]

# File lib/DB.rb, line 113
        def close 
                @pstatements.each_value {|pstmt|
                        pstmt[0].finish
                }
                @pstatements = {}
                @db.disconnect
                @db = nil
        end

Debug method. This method dumps statistics about how often each sql statement was executed by this instance. Statistics are reset after calls to `close`

[Source]

# File lib/DB.rb, line 125
        def dumpStmtStats 
                @pstatements.each_pair { |stmt, arr|
                        puts "#{stmt} : #{arr[1]}"
                } if @pstatements
        end

sql is the method that does all the work. The only required parameter is stmt, which passes a string with the SQL statement to be executed. The optional parameters in args get bound to ’?’-placeholders in the statement.

This method can be called in several ways.

  • Statements not returning results:
       db.sql "UPDATE sometable SET somevalue=somevalue+1"
    
  • If the statement returns a single value, that value is returned by the method call:
       value = db.sql "SELECT value FROM sometable LIMIT 1"
    
  • Statements returning several values in a single row:
       first_name, last_name =
               db.sql "SELECT first, last
                       FROM sometable
                       LIMIT 1"
    

(It should go without saying: if a single row result is expected like in the examples above but the statement returns several rows, the first one is used.)

  • If a block is passed to the method, it can be used to iterate over the results.
       db.sql("SELECT first, last FROM sometable WHERE last=?", "Smith") do |row|
               puts "Name: #{row.first} #{row.last}
       end
    
  • or, in case the resulting rows only contain a single column :
       db.sql "SELECT first FROM sometable" do |firstname|
               puts "Hello #{firstname}!"
       end
    

[Source]

# File lib/DB.rb, line 98
        def sql(stmt, *args, &block) #:yields: row
                                #optional args and an optional block
                _sql_internal(stmt,false,*args, &block)
        end

Similar to the sql method. This method is meant for `INSERT` and `UPDATE` statements and returns the RPC (row processed count), i.e. the number of rows affected by the statement.

[Source]

# File lib/DB.rb, line 108
        def sql_count (stmt, *args, &block)
                _sql_internal(stmt,true,*args,&block) 
        end

[Source]

# File lib/DB.rb, line 131
        def to_s
                "DBrb: #{@driver}"
        end

Protected Instance methods

[Source]

# File lib/DB.rb, line 152
        def _sql_internal (stmt, count, *args) #:yields: row
                #optional args and an optional block
                pstmt = get_pstmt stmt
                pstmt.execute *args
                
                
                ret_val = nil 
                begin
                        if block_given?
                                pstmt.each { |row|
                                        if row.length==1
                                                yield row[0]
                                        else
                                                yield row
                                        end
                                } 
                        else 
                                        unless count
                                                ret_val = pstmt.fetch if pstmt.fetchable?
                                                ret_val = ret_val[0] if ret_val && ret_val.length==1
                                        end
                
                        end  
                rescue NoMethodError 
                        # calling fetch for mysql drivers on stmts returning
                        # no rows (INSERT...) is fucked.

#                       Message: <"undefined method `fetch_row' for nil:NilClass">
#                       ---Backtrace---
#                       /usr/local/lib/ruby/site_ruby/1.8/DBD/Mysql/Mysql.rb:424:in `fetch'
#                       /usr/local/lib/ruby/site_ruby/1.8/dbi/dbi.rb:811:in `fetch'
#                       /usr/local/lib/ruby/site_ruby/1.8/dbi/dbi.rb:836:in `each'
                end
        
                ret_val = pstmt.rows if count
                pstmt.cancel # clean up
                return ret_val

        end

Returns a (cached) reference to the DBI driver. Default behaviour is to create a single connection, keeping it cached and reusing it for all DB statements executed by this object instance.

This method is used internally by `sql` and could be overridden in case you require different behaviour, e.g. creating a new connection for each executed statement or maintaining several connections.

[Source]

# File lib/DB.rb, line 147
        def get_db 
                return @db if @db && @db.connected?  
                @db = DBI.connect(@driver, @usr, @pwd, @params) 
        end

Private Instance methods

Maintains a cache of all prepared statments. Each prepared statement executed by `sql` is prepared only once and reused in case the same sql statement is executed a second time.

Prepared statements are disposed when `close` is called.

[Source]

# File lib/DB.rb, line 202
        def get_pstmt stmt #:doc:
                #@pstatements[stmt] ||= get_db.prepare stmt
                @pstatements[stmt] ||= [nil,0]
                @pstatements[stmt][1] += 1
                @pstatements[stmt][0] ||= get_db.prepare stmt
        end

[Validate]