From: Ollivier Robert <roberto@...>
Date: 2003-01-27T08:56:18+09:00
Subject: Re: mod_ruby: what's persistent and what's shared?

In article <Pine.LNX.4.33.0301260252110.10983-100000@eli.fsl.noaa.gov>,
ahoward  <ahoward@fsl.noaa.gov> wrote:
> 3) 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
-=-=-
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <!-- $Id: //depot/sidhe/www/dbpool.rsp#4 $ -->
  <head>
    <title>Test DB pool - $Id: //depot/sidhe/www/dbpool.rsp#4 $</title>
    <style type="text/css">
th.domain {
    width=60%;
    text-align:left;
    background-color:lightblue;
}

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;
}
    </style>
  </head>
  <body>
<% 
require 'cgi'

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 "<pre>"
  File.open(MYNAME).each_line do |l|
    print CGI.escapeHTML(l)
  end
  puts "</pre>"
else
dbh = $dbhs.get
%>
    <div id="heading">
      Current DBH handle:
      <div class="highlight">
        <%= CGI::escapeHTML(dbh.to_s)%>
      </div>
    </div>
    <div class="left" id="left">
      <table summary="List of domains">
        <caption>List of domains</caption>
        <tr>
          <th class="domain">Domain</th>
          <th class="id">Domain ID</th>
          <th class="id">Client ID</th>
        </tr>
<%
req = "select domain,domain_id,client_id from domain"
dbh.select_all(req) do |row|
  puts "      <tr>"
  row.each  do |field|
    puts "        <td class=\"id\">#{field}</td>"
  end 
  puts "      </tr>"
end
$dbhs.release(dbh)
%>
      </table>
    </div>
    <p>&nbsp;</p> 
    <div class="right" id="right">
      <table summary="All Handles">
      <caption>All defined handles in <code>$dbhs</code></caption>
      <tr>
        <th class="id">
          ID
        </th>
        <th class="domain">
          DBH Handle
        </th>
      </tr>
<%
i = 0
$dbhs.each do |h|
%>
      <tr>
<% if h.id == dbh.id then %>
        <td class="highlight">
          <%= i%>
        </td>
        <td class="highlight">
          <%= CGI::escapeHTML(h.to_s)%>
        </td>
<% else %>
        <td>
          <%= i%>
        </td>
        <td>
          <%= CGI::escapeHTML(h.to_s)%>
        </td>
<% end %>
      </tr>
<%
  i = i + 1
end
%>
      </table>
    </div>
<% end %>
  </body>
</html>
-=-=-
-- 
Ollivier ROBERT   -=- Eurocontrol EEC/ITM -=-   roberto@eurocontrol.fr
Usenet Canal Historique                   FreeBSD: The Power to Serve!