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.
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.
# 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
Closes all resources used by this object instance.
# 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`
# 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.
db.sql "UPDATE sometable SET somevalue=somevalue+1"
value = db.sql "SELECT value FROM sometable LIMIT 1"
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.)
db.sql("SELECT first, last FROM sometable WHERE last=?", "Smith") do |row| puts "Name: #{row.first} #{row.last} end
db.sql "SELECT first FROM sometable" do |firstname| puts "Hello #{firstname}!" end
# 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.
# File lib/DB.rb, line 108 def sql_count (stmt, *args, &block) _sql_internal(stmt,true,*args,&block) end
# 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.
# File lib/DB.rb, line 147 def get_db return @db if @db && @db.connected? @db = DBI.connect(@driver, @usr, @pwd, @params) end
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.
# 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