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

.install_mysql_pool_on_object ⇒ Object

Set these methods into the Object namespace



15
16
17
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 15

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

Instance Method Details

#close_mysql(iMySQLConnection) ⇒ Object

Close a MySQL connection created with connect_to_mysql

Parameters

  • iMySQLConnection (MySQL): The MySQL connection to close



82
83
84
85
86
87
88
89
90
91
92
93
94
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 82

def close_mysql(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

#close_prepared_statement(iMySQLConnection, iPreparedStatement) ⇒ Object

Close a previously created prepared statement using get_prepared_statement.

Parameters

  • iMySQLConnection (MySQL): The MySQL connection

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



160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 160

def close_prepared_statement(iMySQLConnection, iPreparedStatement)
  lPreparedStatements = $RUtilAnts_MySQLPool_Pool[findMySQLConnectionKey(iMySQLConnection)][2]
  lFound = false
  lDelete = nil
  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[0].close
          lDelete = iStrSQL
        end
      end
      lFound = true
    end
  end
  lPreparedStatements.delete(lDelete) if (lDelete != nil)
  if (!lFound)
    raise MissingPreparedStatementFromPoolError.new("Prepared statement #{iPreparedStatement.inspect} can't be found among the pool of MySQL connection #{iMySQLConnection.inspect}")
  end
end

#connect_to_mysql(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



32
33
34
35
36
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
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 32

def connect_to_mysql(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
        log_err "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

#get_prepared_statement(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 = {}]

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

Return

  • MySQL::Statement: The MySQL statement



132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 132

def get_prepared_statement(iMySQLConnection, iStrSQL, iAdditionalOptions = {})
  # Parse options
  lLeaveOpen = iAdditionalOptions[:leave_open] || 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

#setup_mysql_connection(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



107
108
109
110
111
112
113
114
115
116
117
118
119
120
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 107

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

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

  return rError
end

#setup_prepared_statement(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 = {}]

    • :leave_open (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



193
194
195
196
197
198
199
200
 
# File 'lib/rUtilAnts/MySQLPool.rb', line 193

def setup_prepared_statement(iMySQLConnection, iStrSQL, iAdditionalOptions = {})
  lStatement = get_prepared_statement(iMySQLConnection, iStrSQL, iAdditionalOptions)
  begin
    yield(lStatement)
  ensure
    close_prepared_statement(iMySQLConnection, lStatement)
  end
end