msqlconnection.cpp

C++连接一写常用数据库的接口

/*
 * MsqlConnection object defines the needed connection functions for the dbConnect mSQL driver
 * Copyright (C) 2003 Johnathan Ingram, [e-mail]
 *
 * This library is free software; you can redistribute it and/or
 *   modify it under the terms of the GNU Lesser General Public
 *   License as published by the Free Software Foundation; either
 *   version 2.1 of the License, or (at your option) any later version.
 *
 *   This library is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *   Lesser General Public License for more details.
 *
 *   You should have received a copy of the GNU Lesser General Public
 *   License along with this library; if not, write to the Free Software
 *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  US
 *
 */


#include "msqlConnection.h"
#include "msqlBindParam.h"
#include "msqlQuery.h"


// -----------------------------------------------------------------------------
// PRIVATE:
// -----------------------------------------------------------------------------

// -----------------------------------------------------------------------------
// MsqlConnection::_msqlConnect
// -----------------------------------------------------------------------------
void 
MsqlConnection::_msqlConnect(
         int index)
{  
   // All calls to the msql API will be wrapped by a mutex.
   // It is questionable how thread safe the API is with global vars like msqlErrMsg defined in msql.h :(
   SimpleThread_Synchronize sync(msqlAPIMutex);
   
   // Make sure the handle is valid and we are not already connected.
   if (index > _numHandles || index < 0)
      throw Error("_msqlConnect(): Invalid index to database handle.");

   if (_handles[index]->status != BaseHandle::DISCONNECTED)
      throw AlreadyConnected("_msqlConnect(): "
         "The database connection is already connected to the database.");


   // Connect to the mSQL database. mSQL does not need user name and password.
   if ((_handles[index]->msqlsock = msqlConnect((char*)host.c_str())) == -1)
   {
      string err = "_msqlConnect(): ";
      err += msqlErrMsg;
      throw ErrorConnecting(err);
   }
      
   // Select the database that is required
   if (msqlSelectDB(_handles[index]->msqlsock, (char*)databaseName.c_str()) == -1)
   {
      // Set the error
      string err = "_msqlConnect(): ";
      err += msqlErrMsg;

      // Disconnect from the database.			
      msqlClose(_handles[index]->msqlsock);

      throw ErrorConnecting(err);
   }
     
   _handles[index]->status = BaseHandle::CONNECTED;
   _handles[index]->lastUsed = time(NULL);
} // MsqlConnection::_msqlConnect


// -----------------------------------------------------------------------------
// MsqlConnection::_msqlDisconnect
// -----------------------------------------------------------------------------
void 
MsqlConnection::_msqlDisconnect(
         int index)
{
   // All calls to the msql API will be wrapped by a mutex.
   // It is questionable how thread safe the API is with global vars like msqlErrMsg defined in msql.h :(
   SimpleThread_Synchronize sync(msqlAPIMutex);

   // Make sure the handle is valid and we are not already connected.
   if (index > _numHandles || index < 0)
      throw Error("_msqlDisconnect(): Invalid index to database handle.");   

   if (_handles[index]->status == BaseHandle::DISCONNECTED || 
       _handles[index]->status == BaseHandle::UNKNOWN)
      throw NotConnected("_msqlDisconnect(): "
         "The database handle does not have a valid connection to the database.");

   // Disconnect from the server.
   msqlClose(_handles[index]->msqlsock);
   _handles[index]->msqlsock = 0;

   _handles[index]->status = BaseHandle::DISCONNECTED;
   _handles[index]->lastUsed = time(NULL);         
} // MsqlConnection::_msqlDisconnect




// -----------------------------------------------------------------------------
// MsqlConnection::_msqlPing
// -----------------------------------------------------------------------------
void 
MsqlConnection::_msqlPing(
      int index)
{
   // Make sure the handle is valid.
   if (index > _numHandles || index < 0)
      throw Error("_msqlPing(): Invalid index to database handle.");

   // Only ping the connection if the ping interval has expired since the
   //   connection was last used.
	time_t currentTime = time(NULL);
	if (currentTime - _handles[index]->lastUsed >= pingInterval)
	{
      // Generate an error by selecting from a table that does not exist.
      // If the error is correct, then we still have a connection.
      try
      {
         SimpleThread_Synchronize sync(msqlAPIMutex);
         _msqlQuery(index, "SELECT nofield FROM msqlpingtableDbConnect");
      }
      catch(ErrorQuerying ex)
      {
         // Make sure we have the correct error
         if (strcasecmp(ex.description.c_str(), "Unknown table \"msqlpingtableDbConnect\"") == 0)
            return;  // :) We are still connected
         

         // If no connection, then force disconnect and connect
         // Synchronize the function as we dont want this connection taken from us
         SimpleThread_Synchronize sync(classMutex);
         
         //1. Disconnect ingnoring errors
         try
         {
            _msqlDisconnect(index);
         }
         catch(...)
         { }  // Ignore any errors from the disconnect.
         
         //2. Try get a new connection
         try
         {
            _msqlConnect(index);
            
            // Force the connection to be in use again
            _handles[index]->status = BaseHandle::CONNECTED_USED;
         }
         catch(...)
         {
            string err = "_msqlPing(): Unable to establish new connection, ";
            throw ErrorPingingConnection(err);            
         }         
      }
	}
} // MsqlConnection::_msqlPing



// -----------------------------------------------------------------------------
// MsqlConnection::_msqlQuery
// -----------------------------------------------------------------------------
void 
MsqlConnection::_msqlQuery(
      int           index,
      const string& sqlStatement)
{
   // Make sure the handle is in the connected used state.
   if (_handles[index]->status != BaseHandle::CONNECTED_USED)
      throw ErrorQuerying("_msqlQuery(): The database connection is not valid. May have been terminated by the connection object?");

   // All calls to the msql API will be wrapped by a mutex.
   // It is questionable how thread safe the API is with global vars like msqlErrMsg defined in msql.h :(
   SimpleThread_Synchronize sync(msqlAPIMutex);

   //Execute the query
   if (msqlQuery(_handles[index]->msqlsock,  (char*)sqlStatement.c_str())== -1)
   {
      // Something went wrong while trying to execute the sql statement, so throw an exception.
      string err = "_msqlQuery(): ";
      err += "SQL statement: ";
      err += sqlStatement;
      err += ", ";
      err += msqlErrMsg;
      throw ErrorQuerying(err);
   }

   // Set the last used
   _handles[index]->lastUsed = time(NULL);
} // MsqlConnection::_msqlQuery
      

//------------------------------------------------------------------------------
// MsqlConnection::_freeCollection
//------------------------------------------------------------------------------
void MsqlConnection::_freeCollection(
      CollectionType type)
{
   int i;
   switch (type)
   {
      case CONNECTION_HANDLES:
         if (_handles)
         {
            for (i=0; i<_numHandles; i++)
            {
               if (_handles[i])
               {
                  delete _handles[i];
                  _handles[i] = NULL;         
               }
            }
            free(_handles);
            _handles = NULL;
            _numHandles = 0;
         }
      
         break;      
    }
} // MsqlConnection::_freeCollection


// -----------------------------------------------------------------------------
// PUBLIC:
// -----------------------------------------------------------------------------

// -----------------------------------------------------------------------------
// MsqlConnection::MsqlConnection
// -----------------------------------------------------------------------------
MsqlConnection::MsqlConnection(
      int argc, 
      const char** argv)
:  
   BaseConnection(MSQL_DRIVERNAME),
   _numHandles(0),
   _handles(NULL)
{
   // Store any arguments in a easy to use structure. (Must be even number)
   // All argument names will be in lowercase.
   if (argc % 2 == 0)
      for (int i=0; i<argc; i+=2)
      {
         // AVAILABLE: Add any mSQL parameters here         
      }
} // MsqlConnection::MsqlConnection



// -----------------------------------------------------------------------------
// MsqlConnection::~MsqlConnection
// -----------------------------------------------------------------------------
MsqlConnection::~MsqlConnection()
{
	// Make sure we disconnect from the database.
	if (isConnected)
	{
		disconnect(120);  // 2 Minute timeout.
	}

   // Free any handles.   
   _freeCollection(CONNECTION_HANDLES);   
} // MsqlConnection::~MsqlConnection



// -----------------------------------------------------------------------------
// MsqlConnection::connect
// -----------------------------------------------------------------------------
void 
MsqlConnection::connect(
      const string &username, 
      const string &password, 
      const string &databaseName, 
      const string &host, 
      int          maxConnections,
      int          minConnections,
      const string &optParam1,
      const string &optParam2)
{
   // Synchronize the function.
   SimpleThread_Synchronize sync(classMutex);

   // Make sure we are not already connected.
   if (isConnected)
      throw ErrorConnecting("connect(): Already connected to the database.");

   // Check and set the parameters. (Use the base class for this)
   BaseConnection::connect(username, password, databaseName, host,
      maxConnections, minConnections, optParam1, optParam2);

   // Free any handles used before.
   _freeCollection(CONNECTION_HANDLES);   

   // Create the collection to hold the needed handle information.
   _numHandles = maxConnections;
   _handles = (MsqlHandle**)malloc(_numHandles * sizeof(MsqlHandle*));
   for (int i=0; i<_numHandles; i++)
   {
      _handles[i] = new MsqlHandle();
      _handles[i]->status = BaseHandle::DISCONNECTED;
      _handles[i]->queryObject = NULL;
   }
      
   // Connect the required specified minimum connections. The rest will be on demand.
   for (int i=0; i<minConnections; i++)
      _msqlConnect(i);   

   isConnected = true;
}  // MsqlConnection::connect
                           


// -----------------------------------------------------------------------------
// MsqlConnection::disconnect
// -----------------------------------------------------------------------------
void
MsqlConnection::disconnect(
      time_t timeout)
{
   // Synchronize the function.
   SimpleThread_Synchronize sync(classMutex);

   bool doneTimeout = false;

   // Only disconnect if we are connected.
   if (!isConnected)
      throw NotConnected("disconnect(): Not connected to the database.");
     
   // Disconnect all the open connections.     
   for (int i=0; i<_numHandles; i++)
   {
      if (_handles[i]->status == BaseHandle::CONNECTED || 
          _handles[i]->status == BaseHandle::CONNECTED_USED)
      {
         // If the handle is USED wait for the timeout.
         // Only wait once for the timeout for all connections.
         if (_handles[i]->status == BaseHandle::CONNECTED_USED && !doneTimeout)
         {
            SimpleThread::sleep(timeout * 1000);
            doneTimeout = true;
         }
            
         _msqlDisconnect(i);
      }
   }
           
   isConnected = false;
} // MsqlConnection::disconnect



// -----------------------------------------------------------------------------
// MsqlConnection::requestQueryConnection
// -----------------------------------------------------------------------------
void*
MsqlConnection::requestQueryConnection()
{
   // This function must act as a fifo stack. The first thread in must get the first 
   //   available connection.
   // Synchronize the function.
   SimpleThread_Synchronize sync(classMutex);
   
   // Make sure we are connected.
   if (!isConnected)
      throw NotConnected("requestQueryConnection(): Not connected to the database.");
 
   // Flag that a request is occuring.
   isRequestQueryConnectionOccuring = true;

   
   // Loop until we have a valid connection or an error.
   // Built in timeout of 1 min per thread to retrieve a connection.
   int i;
   time_t now = time(NULL);
   while (time(NULL) <= now + 60)
   {
      // Try and obtain a connection
      for (i=0; i<_numHandles; i++)
      {
         // We have an available handle thats already connected.
         if (_handles[i]->status == BaseHandle::CONNECTED)
         {
            _handles[i]->queryObject = new MsqlQuery(this, i);
            _handles[i]->status = BaseHandle::CONNECTED_USED;

            isRequestQueryConnectionOccuring = false;
            return _handles[i]->queryObject;
         }
         
         // We have an available handle that needs to be connected.
         if (_handles[i]->status == BaseHandle::DISCONNECTED)
         {
            _msqlConnect(i);
            _handles[i]->queryObject = new MsqlQuery(this, i);
            _handles[i]->status = BaseHandle::CONNECTED_USED;

            isRequestQueryConnectionOccuring = false;
            return _handles[i]->queryObject;
         }
      }

      // Sleep for a second to conserve resources
      SimpleThread::sleep(1000);
   }


   // If we got this far a timeout occured.
   isRequestQueryConnectionOccuring = false;
   throw QueryConnectionTimeout("requestQueryConnection(): A timout occured "
         "while trying to obtain a query connection.");

} // MsqlConnection::requestQueryConnection



// -----------------------------------------------------------------------------
// MsqlConnection::releaseQueryConnection
// -----------------------------------------------------------------------------
void
MsqlConnection::releaseQueryConnection(
   void* queryObject)
{
   // Don't synchronize as we may need to release a connection to allow
   //  requestQueryConnection to get an available one
   
   // Find the handle that has the query connection instance
   int i;
   for (i=0; i<_numHandles; i++)
   {
      if (_handles[i]->queryObject == queryObject)
      {
         _handles[i]->queryObject = NULL;
         _handles[i]->status = BaseHandle::CONNECTED;
         
         // Check if we need to release the connection.
         // We release the connection if the connection is outside of the minimum
         // connections and there is no current request for a connection waiting.
         if (i >= minConnections && !isRequestQueryConnectionOccuring)
         {
            _msqlDisconnect(i);
         }
        
         break;
      }
   }
} // MsqlConnection::releaseQueryConnection