In article Pine.LNX.4.33.0301260252110.10983-100000@eli.fsl.noaa.gov,
···
ahoward ahoward@fsl.noaa.gov wrote:
- process a :
$db.exec # something which has something to do with foobar_database, but
# it’s connected to barfoo_database!!
My class implement per-database pools so this can’t happen. It also
implement mutex-like functions Get/Release so at each point in time a DB
handle is tied to a specific process.
The code is below. Any comment is welcome…
dbpool.rb
-=-=-
Package to manage pools of database connections
Copyright © 2002 by Ollivier Robert
Distribute under the same terms as the Ruby license
$Id: //depot/src/ruby/dbpool.rb#3 $
require “dbi”
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Can be overridden by the call to DBpool.new
MAXCONN = 5
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
class DBconn
Represents a given connection to the DB
Private member:
busy indicates whether a given connection has been allocated
Public members:
dbh database handle
class DBconn #{{{
public
attr_accessor :dbh
attr_accessor :busy
def initialize(dbh)
@busy = false
@dbh = dbh
end
def cid
return @dbh.id
end
def busy?
return @busy
end
end
}}}
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
class DBpool
Pool of connections to a given DB
Private members:
last last recently allocated connection
pool for for allocating connections
poolh for releasing connections (hashed by dbh.id]
connect connect string/user/password for identifying pools
Public members:
size number of dbh handled by this pool
Includes: Enumerable
class DBpool #{{{
include Enumerable
private
pool is an array with DBconn objects
last is the last one allocated
attr_accessor :last, :pool, :poolh
attr_accessor :connect
public
attr_reader :size
def initialize(connect, user, pass, nb = MAXCONN)
@pool = Array.new
@poolh = Hash.new
@last = -1
@size = nb
#
# Fill up the pool with connections
#
0.upto(nb - 1) do |c|
dbh = DBI.connect (connect, user, pass)
dbc = DBconn.new(dbh)
@pool[c] = dbc
@poolh[dbc.cid] = dbc
end
end
Get next available DBconn
Loop with a small sleep till one is available
def get
while true
#
# Look at the next to @last in the pool
#
nextdbh = (@last + 1) % @size
#
# If not busy, take it and mark it busy
#
if not @pool[nextdbh].busy? then
@pool[nextdbh].busy = true
@last = nextdbh
return @pool[nextdbh].dbh
else
#
# Was busy, try next one
#
@last = @last + 1
end
#
# Don’t try the next one so soon
#
sleep 1
end
end
Used during initialisation to add DBconn
to the pool
def <<(dbo)
@pool << dbo
end
Release a DBconn object
def release(dbh)
@poolh[dbh.id].busy = false
end
Enumerate the pool
def each
@pool.each do |dbc|
yield dbc.dbh
end
end
Final removal of all connections
def destroy
@pool.each do |dbc|
dbc.dbh.disconnect
dbc.busy = nil
end
@pool = nil
@poolh = nil
@last = nil
end
end
}}}
-=-=-
Use it in httpd.conf:
-=-=-
RubyAddPath “/home/staff/roberto/src/ruby”
RubyRequire dbpool
RubyRequire foobar/config
-=-=-
foobar/config
-=-=-
module Foobar
module Config
require “dbi”
DB = "foobar"
USER = "anyuser"
PASS = "guess"
MAXCONN = 10
$dbhs = DBpool.new("DBI:Mysql:#{DB}", USER, PASS, MAXCONN)
end
end
-=-=-
Example of usage:
dbpool.rhtml
-=-=-
th.id {
width=20%;
text-align:left;
background-color:lightblue;
}
td.highlight {
background-color:lightgreen;
}
div.highlight {
color:lightgreen;
}
td.domain {
width=60%;
text-align:left;
}
div.left {
position:absolute;
top:3em;
margin-left:3em;
margin-right:3em;
}
div.right {
position:absolute;
top:3em;
margin-left:3em;
margin-right:3em;
}
#left {
left:0;
}
#right {
right:0;
}
#heading {
text-align:center;
}
MYNAME = ENV[‘SCRIPT_FILENAME’].untaint
cgi = CGI.new
h = cgi.params
Allow source of the “page/script” to be retrieved
if h.has_key?(“source”) then
puts “
””
File.open(MYNAME).each_line do |l|
print CGI.escapeHTML(l)
end
puts “
else
dbh = $dbhs.get
%>
Current DBH handle:
<%= CGI::escapeHTML(dbh.to_s)%>
List of domains
<%
req = “select domain,domain_id,client_id from domain”
dbh.select_all(req) do |row|
puts " "
row.each do |field|
puts " <td class="id">#{field}"
end
puts " "
end
$dbhs.release(dbh)
%>
| Domain | Domain ID | Client ID |
|---|---|---|
All defined handles in
$dbhs<%
i = 0
$dbhs.each do |h|
%>
<% if h.id == dbh.id then %>
<% else %>
<% end %>
<%
i = i + 1
end
%>
ID |
DBH Handle |
||
|---|---|---|---|
<%= i%> |
<%= CGI::escapeHTML(h.to_s)%> |
<%= i%> |
<%= CGI::escapeHTML(h.to_s)%> |
<% end %> -=-=- -- Ollivier ROBERT -=- Eurocontrol EEC/ITM -=- roberto@eurocontrol.fr Usenet Canal Historique FreeBSD: The Power to Serve!