Module: RUtilAnts::MySQLPool

Defined in:
lib/rUtilAnts/MySQLPool.rb

Defined Under Namespace

Classes: MissingConnectionFromPoolError, MissingExistingConnectionError, MissingPreparedStatementFromPoolError

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.initializeMySQLPool ⇒ Object

Set these methods into the Object namespace



20
21
22
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 20

def self.initializeMySQLPool
  Object.module_eval('include RUtilAnts::MySQLPool')
end

Instance Method Details

#closeMySQL(iMySQLConnection) ⇒ Object

Close a MySQL connection created with connectToMySQL

Parameters:

  • iMySQLConnection (MySQL): The MySQL connection to close



87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 87

def closeMySQL(iMySQLConnection)
  # Find the connection
  if (defined?($RUtilAnts_MySQLPool_Pool) == nil)
    $RUtilAnts_MySQLPool_Pool = {}
  end
  lDBKey = findMySQLConnectionKey(iMySQLConnection)
  $RUtilAnts_MySQLPool_Pool[lDBKey][1] -= 1
  if ($RUtilAnts_MySQLPool_Pool[lDBKey][1] == 0)
    # Close for real
    $RUtilAnts_MySQLPool_Pool[lDBKey][0].close
    $RUtilAnts_MySQLPool_Pool[lDBKey] = nil
  end
end

#closePreparedStatement(iMySQLConnection, iPreparedStatement) ⇒ Object

Close a previously created prepared statement using getPreparedStatement.

Parameters:

  • iMySQLConnection (MySQL): The MySQL connection

  • iPreparedStatement (MySQL::Statement): The MySQL prepared statement



165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 165

def closePreparedStatement(iMySQLConnection, iPreparedStatement)
  lPreparedStatements = $RUtilAnts_MySQLPool_Pool[findMySQLConnectionKey(iMySQLConnection)][2]
  lFound = false
  lPreparedStatements.each do |iStrSQL, ioPreparedStatementInfo|
    if (ioPreparedStatementInfo[0] == iPreparedStatement)
      # Found it
      if (ioPreparedStatementInfo[1] != -1)
        ioPreparedStatementInfo[1] -= 1
        if (ioPreparedStatementInfo[1] == 0)
          # Close it for real
          ioPreparedStatementInfo[1].close
          lPreparedStatements[iStrSQL] = nil
        end
      end
      lFound = true
    end
  end
  if (!lFound)
    raise MissingPreparedStatementFromPoolError.new("Prepared statement #{iPreparedStatement.inspect} can't be found among the pool of MySQL connection #{iMySQLConnection.inspect}")
  end
end

#connectToMySQL(iHost, iDBName, iUser, iPassword = nil) ⇒ Object

Create a MySQL connection to a MySQL database. Keep connections in a pool with counters. Reuse existing connections. If the given password is nil, we force the reuse of an existing connection.

Parameters:

  • iHost (String): The host to connect to

  • iDBName (String): The MySQL database name to connect to

  • iUser (String): The user

  • iPassword (String): The password. If nil, this will only try to reuse an existing connection [optional = nil]

Return:

  • Exception: An error, or nil in case of success

  • MySQL: The MySQL connection, or nil in case of failure



37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 37

def connectToMySQL(iHost, iDBName, iUser, iPassword = nil)
  rError = nil
  rMySQL = nil

  if (defined?($RUtilAnts_MySQLPool_Pool) == nil)
    # The pool: connection, counters and prepared statements per host/dbname/user
    # map< [ HostName, DBName, UserName ], [ MySQLConnection, Counter, map< SQLString, [ MySQLPreparedStatement, Counter ] > ] >
    $RUtilAnts_MySQLPool_Pool = {}
  end
  lDBKey = [ iHost, iDBName, iUser ]
  if ($RUtilAnts_MySQLPool_Pool[lDBKey] == nil)
    if (iPassword == nil)
      # This is a problem: we want an existing connection, but none exists.
      rError = MissingExistingConnectionError.new("An existing connection should already exist for #{lDBKey.inspect}")
    else
      # Create the connection
      require 'mysql'
      begin
        lMySQL = Mysql.new(iHost, iUser, iPassword, iDBName)
#            lMySQL = Mysql.init
#            lMySQL.options(Mysql::OPT_CONNECT_TIMEOUT, 28800)
#            lMySQL.options(Mysql::OPT_READ_TIMEOUT, 28800)
#            lMySQL.options(Mysql::OPT_WRITE_TIMEOUT, 28800)
#            lMySQL.real_connect(iHost, iUser, iPassword, iDBName)
      rescue Exception
        logErr "Error while creating MySQL connection to #{lDBKey.inspect}: #{$!}.\n#{$!.backtrace.join("\n")}"
        rError = $!
        lMySQL = nil
      end
      if (rError == nil)
        $RUtilAnts_MySQLPool_Pool[lDBKey] = [ lMySQL, 0, {} ]
      else
        $RUtilAnts_MySQLPool_Pool[lDBKey] = nil
      end
    end
  end
  if ((rError == nil) and
      ($RUtilAnts_MySQLPool_Pool[lDBKey] != nil))
    # Increase the count of clients to this connection
    $RUtilAnts_MySQLPool_Pool[lDBKey][1] += 1
    rMySQL = $RUtilAnts_MySQLPool_Pool[lDBKey][0]
  end

  return rError, rMySQL
end

#getPreparedStatement(iMySQLConnection, iStrSQL, iAdditionalOptions = {}) ⇒ Object

Get a prepared statement for a given SQL string of a given MySQL connection. Use the cache of prepared statements.

Parameters:

  • iMySQLConnection (MySQL): The MySQL connection

  • iStrSQL (String): The SQL statement to prepare

  • iAdditionalOptions (map<Symbol,Object>): Additional options [optional = {}]

** :LeaveOpen (Boolean): Do we NOT close the opened statement once it is not used anymore ? [optional = false] Return:

  • MySQL::Statement: The MySQL statement



137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 137

def getPreparedStatement(iMySQLConnection, iStrSQL, iAdditionalOptions = {})
  # Parse options
  lLeaveOpen = iAdditionalOptions[:LeaveOpen] || false
  # Find the prepared statements set
  lPreparedStatements = $RUtilAnts_MySQLPool_Pool[findMySQLConnectionKey(iMySQLConnection)][2]
  if (lPreparedStatements[iStrSQL] == nil)
    # Create a new one
    lPreparedStatements[iStrSQL] = [
      iMySQLConnection.prepare(iStrSQL),
      0
    ]
  end
  if (lLeaveOpen)
    # We set its counter to -1
    lPreparedStatements[iStrSQL][1] = -1
  elsif (lPreparedStatements[iStrSQL][1] != -1)
    # We increment its usage counter
    lPreparedStatements[iStrSQL][1] += 1
  end

  return lPreparedStatements[iStrSQL][0]
end

#setupMySQLConnection(iHost, iDBName, iUser, iPassword = nil) ⇒ Object

Setup a MySQL connection, and ensure it is closed once the client code ends.

Parameters:

  • iHost (String): The host to connect to

  • iDBName (String): The MySQL database name to connect to

  • iUser (String): The user

  • iPassword (String): The password. If nil, this will only try to reuse an existing connection [optional = nil]

  • CodeBlock: The code executed with the MySQL connection

** iMySQL (MySQL): The MySQL connection Return:

  • Exception: An error, or nil in case of success



112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 112

def setupMySQLConnection(iHost, iDBName, iUser, iPassword = nil)
  rError = nil

  rError, lMySQL = connectToMySQL(iHost, iDBName, iUser, iPassword)
  if (rError == nil)
    begin
      yield(lMySQL)
    ensure
      closeMySQL(lMySQL)
    end
  end

  return rError
end

#setupPreparedStatement(iMySQLConnection, iStrSQL, iAdditionalOptions = {}) ⇒ Object

Setup a prepared statement, and ensure it will be closed

Parameters:

  • iMySQLConnection (MySQL): The MySQL connection

  • iStrSQL (String): The SQL statement to prepare

  • iAdditionalOptions (map<Symbol,Object>): Additional options [optional = {}]

** :LeaveOpen (Boolean): Do we NOT close the opened statement once it is not used anymore ? [optional = false]

  • CodeBlock: The code executed once the statement is prepared

** iPreparedStatement (MySQL::Statement): The prepared statement



196
197
198
199
200
201
202
203
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 196

def setupPreparedStatement(iMySQLConnection, iStrSQL, iAdditionalOptions = {})
  lStatement = getPreparedStatement(iMySQLConnection, iStrSQL, iAdditionalOptions)
  begin
    yield(lStatement)
  ensure
    closePreparedStatement(iMySQLConnection, lStatement)
  end
end