Module: RUtilAnts::MySQLPool
- Defined in:
- lib/rUtilAnts/MySQLPool.rb
Defined Under Namespace
Classes: MissingConnectionFromPoolError, MissingExistingConnectionError, MissingPreparedStatementFromPoolError
Class Method Summary collapse
-
.initializeMySQLPool ⇒ Object
Set these methods into the Object namespace.
Instance Method Summary collapse
-
#closeMySQL(iMySQLConnection) ⇒ Object
Close a MySQL connection created with connectToMySQL.
-
#closePreparedStatement(iMySQLConnection, iPreparedStatement) ⇒ Object
Close a previously created prepared statement using getPreparedStatement.
-
#connectToMySQL(iHost, iDBName, iUser, iPassword = nil) ⇒ Object
Create a MySQL connection to a MySQL database.
-
#getPreparedStatement(iMySQLConnection, iStrSQL, iAdditionalOptions = {}) ⇒ Object
Get a prepared statement for a given SQL string of a given MySQL connection.
-
#setupMySQLConnection(iHost, iDBName, iUser, iPassword = nil) ⇒ Object
Setup a MySQL connection, and ensure it is closed once the client code ends.
-
#setupPreparedStatement(iMySQLConnection, iStrSQL, iAdditionalOptions = {}) ⇒ Object
Setup a prepared statement, and ensure it will be closed.
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 |