pgsqlquery.cpp

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

/*
 * PostgresqQuery object defines the needed query functions for the dbConnect PostgreSQL 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 "pgsqlQuery.h"
#include "pgsqlOID.h"


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

//------------------------------------------------------------------------------
// PostgresqlQuery::_pgsqGetFieldsInformation
//------------------------------------------------------------------------------
void 
PostgresqlQuery::_pgsqlGetFieldsInformation()
{
   // Clear any previouse field information just incase.
   _freeCollection(FIELD_INFORMATION);

   // Internal method used to build the field information.

   
   // Allocate the information pointers
   _numFieldInformation = _fieldCount;
   _fieldInformation = (BaseFieldDescription**)malloc(_numFieldInformation * sizeof(BaseFieldDescription*));
   
   // Assign the data to the corresponding fields.
   for (int i=0; i<_numFieldInformation; i++)
   {
      // Field Name.
      string fName = PQfname(__pgsql_res__, i);
      // Work out the field type.
      FieldType _type = _pgsqlResolveFieldType(PQftype(__pgsql_res__, i));

      // Field Properties.
      long int  _precision = 0;
      long int  _scale = 0;

      bool     _isIncrement = false;    // Not supported, PostgreSQL uses sequences
      bool     _isPriKey    = false;    // Not supported 
      bool     _isUnique    = false;    // Not supported 
      bool     _isNotNull   = false;    // Not supported 

      int modData = PQfmod(__pgsql_res__, i);
      // TODO: Use modData for prescision etc settings

      // Add the field properties to the vector.
      _fieldInformation[i] = new BaseFieldDescription(fName, i, _type, _isIncrement, _isPriKey, _isUnique, _isNotNull);
   }
}  // PostgresqlQuery::_pgsqGetFieldsInformation


//------------------------------------------------------------------------------
// PostgresqlQuery::_pgsqlGetResultSetRow
//------------------------------------------------------------------------------
void
PostgresqlQuery::_pgsqlGetResultSetRow()
{
   // Free the current field values just incase
   _freeCollection(FIELD_VALUES);
   
   // Retrieve the data from the current row.
   char* value = NULL;
   
   try
   {
      // Allocate the value pointers
      _numRecordValues = _fieldCount;
      _recordValues = (PostgresqlValue**)malloc(_numRecordValues * sizeof(PostgresqlValue*));

      // Assign the data to the corresponding fields.
      for (int i=0; i<_numRecordValues; i++)
      {
         Oid type = PQftype(__pgsql_res__, i);
         _recordValues[i] = new PostgresqlValue(_fieldInformation[i]->name().c_str(), type);
         
			// Check if the field is NULL
         if (PQgetisnull(__pgsql_res__, _pgsqlCurrentRow, i))
         {
            _recordValues[i]->setNULL();
         }
         else
         {
            // Get the actual PostgreSQL value. The value is already a NULL terminated string and is managed by the result
            value = PQgetvalue(__pgsql_res__, _pgsqlCurrentRow, i);
            
            // Check if this is a BYTEA type field and convert if necessary
            if (type ==  PG_TYPE_BYTEA)
            {
               // Convert the value to the proper binary representation
               // **NB: The memory allocated is required to be freed by the PostgresqlValue object
               size_t binValSize = 0;
               unsigned char *binVal = PQunescapeBytea((unsigned char*)value, &binValSize);

               // The destructor of PostgresqlValue will free binVal
               _recordValues[i]->setBinary((void*)binVal, binValSize);    
            }
            else
            {
               _recordValues[i]->setString((const char*)value);
            }

            value = NULL;
         }
      }         
   }
   catch(...)
   { }
}  // PostgresqlQuery::_pgsqlGetResultSetRow


//------------------------------------------------------------------------------
// PostgresqlQuery::_pgsqlResolveFieldType
//------------------------------------------------------------------------------
FieldType
PostgresqlQuery::_pgsqlResolveFieldType(
   Oid type)
{
   FieldType res;
   
   switch(type)
   {      
      case PG_TYPE_BOOL:
         res = FT_BOOLEAN;
         break;
      case PG_TYPE_CHAR:
         res = FT_STRING;
         break;
      case PG_TYPE_NAME:
         res = FT_STRING;
         break;
      case PG_TYPE_INT8:
         res = FT_LONG;
         break;
      case PG_TYPE_INT2:
         res = FT_SHORT;
         break;
      case PG_TYPE_INT4:
         res = FT_LONG;
         break;
      case PG_TYPE_TEXT:
         res = FT_STRING;
         break;
      case PG_TYPE_OID:
         res = FT_LONG;
         break;
      case PG_TYPE_FLOAT4:
         res = FT_DOUBLE;
         break;
      case PG_TYPE_FLOAT8:
         res = FT_DOUBLE;
         break;
      case PG_TYPE_ABSTIME:
         res = FT_DATETIME;
         break;
      case PG_TYPE_RELTIME:
         res = FT_DATETIME;
         break;
      case PG_TYPE_UNKNOWN:
         res = FT_UNKNOWN;
         break;
      case PG_TYPE_MONEY:
         res = FT_DOUBLE;
         break;
      case PG_TYPE_MACADDR:
         res = FT_STRING;
         break;      
      case PG_TYPE_INET:
         res = FT_STRING;
         break;
      case PG_TYPE_CIDR:
         res = FT_STRING;
         break;      
      case PG_TYPE_BPCHAR:
         res = FT_STRING;
         break;
      case PG_TYPE_VARCHAR:
         res = FT_STRING;
         break;
      case PG_TYPE_DATE:
         res = FT_DATETIME;
         break;
      case PG_TYPE_TIME:
         res = FT_DATETIME;
         break;
      case PG_TYPE_TIMESTAMP:
         res = FT_DATETIME;
         break;
      case PG_TYPE_TIMESTAMPTZ:
         res = FT_DATETIME;
         break;
      case PG_TYPE_TIMETZ:
         res = FT_DATETIME;
         break;
      case PG_TYPE_NUMERIC:
         res = FT_DOUBLE;
         break;
      case PG_TYPE_PATH:
         res = FT_STRING;
         break;      
      case PG_TYPE_BYTEA:
         res = FT_BLOB;
         break;
      case PG_TYPE_BIT:
         res = FT_STRING;
         break;
      case PG_TYPE_VARBIT:
         res = FT_STRING;
         break;      
      default:
         res = FT_UNKNOWN;
         break;      
   }
   
   return res;
}  // PostgresqlQuery::_pgsqlResolveFieldType


//------------------------------------------------------------------------------
// PostgresqlQuery::_pgsqlParseBindParameters
//------------------------------------------------------------------------------
string
PostgresqlQuery::_pgsqlParseBindParameters(
      const string& originalSqlStatement)

{
   // Try and substitute the parameters checking to make sure they exist.
   string res = originalSqlStatement; 
   int pos;
   
   for (int i=0; i<_numParameters; i++)
   {      
      string paramName = ":" + _parameters[i]->name();
      if ((pos = res.find(paramName)) == string::npos)
      {
         // We have a parameter that is not present in our sql statement
         string err = "_pgsqlParseBindParameters(): The specified bind parameter, ";
         err += paramName;
         err += ", is not present in the SQL statement: ";
         err += originalSqlStatement;
         throw BindParameterNotPresent(err);
      }
      
      // Substitute the parameter with the correct value according to the parameter type.
      res.replace(pos, paramName.length(), _parameters[i]->paramToPostgreSQLString());
   }
   
   
   // Make sure there are no parameters left
   if (((pos = res.find(" :")) != string::npos) || 
       ((pos = res.find("(:")) != string::npos) || 
       ((pos = res.find(",:")) != string::npos) )
   {
      // We have a parameter that has not been set.
      pos += 1;
      int pos2 = res.find(" ", pos);
      int pos3 = res.find(")", pos);
      int pos4 = res.find(",", pos);
      if (pos2 == string::npos) pos2 = 65535;
      if (pos3 == string::npos) pos3 = 65535;
      if (pos4 == string::npos) pos4 = 65535;         
      
      int endPos;      
      if (pos2 < pos3 && pos2 < pos4)
         endPos = pos2;
      if (pos3 < pos2 && pos3 < pos4)
         endPos = pos3;
      if (pos4 < pos2 && pos4 < pos3)
         endPos = pos4;     
      
      string unknownParam = res.substr(pos, endPos-pos);
      
      string err = "_pgsqlParseBindParameters(): The bind parameter, ";
      err += unknownParam;
      err += " in the SQL statement: ";
      err += originalSqlStatement;
      err += " has not been set.";
      throw BindParameterNotSet(err);
   }      
   
   return res;
}  // PostgresqlQuery::_pgsqlParseBindParameters


//------------------------------------------------------------------------------
// PostgresqlQuery::_pgsqlParseFunctionCall
//------------------------------------------------------------------------------
string
PostgresqlQuery::_pgsqlParseFunctionCall(
      const string& originalSqlStatement)
{
   // Check if this is a function call. Only a single token is present in the SQL if it is
   // Check for only one token by looking for any spaces
   if (originalSqlStatement.find(" ") == string::npos)
   {
      // Is a function, build the correct SQL with any bind parameters
      // Lowercase the function as all Posgresql creates functions lower cased
      string lFunc = originalSqlStatement;
      for (int i=0; i<lFunc.length(); i++)
         lFunc[i] = tolower(lFunc[i]);
            
            
      // Ping the connection to make sure it is still valid.
      _parentConnection->_pgsqlPing(_index);

      // Determine the number of function parameters using the Postgresql catalogue
      string sqlQuery =
         "SELECT pg_proc.pronargs FROM pg_proc WHERE pg_proc.proname = '" + lFunc + "'";
      PGresult *r = PQexec(_parentConnection->_handles[_index]->__conn, sqlQuery.c_str());
      if (!r || PQresultStatus(r) == PGRES_BAD_RESPONSE || PQresultStatus(r) == PGRES_FATAL_ERROR)
      { 
         string err = "_pgsqlParseFunctionCall(): Unable to determine number of function parameters";
         if (r)
         {
            err += ", ";
            err += PQresultErrorMessage(r);
            PQclear(r);
         }
         throw CommandError(err);
      }
   
      // Make sure the function exists   
      if (PQresultStatus(r) == PGRES_COMMAND_OK || PQntuples(r) == 0)
      {
         string err = "_pgsqlParseFunctionCall(): The function '";
         err += originalSqlStatement;
         err += "' does not exist in the server catalogue.";                  
         if (r)
            PQclear(r);         
         throw CommandError(err);
      }         
      
      // Get the result and convert it to an integer
      long numFuncParameters = atol(PQgetvalue(r, 0, 0));
      PQclear(r);      

      
      // Build in form "SELECT function(:param1, :paramX) AS _fnc_reslt_"
      string functionCallSQL = "SELECT ";
      functionCallSQL += lFunc;
      functionCallSQL += "(";
      
      // Add any parameters given if required
      // NOTE: The first param, ie param 0 is reserved for any results
      if (numFuncParameters > 1)
      {
         // Always add the first param.
         functionCallSQL += ":param1";
         
         // Iterate throught the other parameters and add with a leading ,
         char buf[6];
         for (int j=2; j<=numFuncParameters; j++)
         {
            functionCallSQL += ",:param";
            
            memset(buf, 0, sizeof(buf));
            sprintf(buf, "%d", j);
            functionCallSQL += buf;
         }  
      }
     
      functionCallSQL += ") AS result";  // The result will always be in the field _fnc_reslt_;
      
      _isFunctionQuery = true;
      return functionCallSQL;
   }
   else
   {
      // Nope, not a function, return the original query
      _isFunctionQuery = false;
      return originalSqlStatement;
   }
}  // PostgresqlQuery::_pgsqlParseFunctionCall


//------------------------------------------------------------------------------
// PostgresqlQuery::_freeCollection
//------------------------------------------------------------------------------
void 
PostgresqlQuery::_freeCollection(
   CollectionType type)
{
   int i;
   switch (type)
   {
      case FIELD_INFORMATION:
         if (_fieldInformation)
         {
            for (i=0; i<_numFieldInformation; i++)
            {
               if (_fieldInformation[i])
               {
                  delete _fieldInformation[i];
                  _fieldInformation[i] = NULL;         
               }
            }
            free(_fieldInformation);
            _fieldInformation = NULL;
            _numFieldInformation = 0;
            _fieldCount = 0;
         }
      
         break;
            
      case FIELD_VALUES:
         if (_recordValues)
         {
            for (i=0; i<_numRecordValues; i++)
            {
               if (_recordValues[i])
               {
                  delete _recordValues[i];
                  _recordValues[i] = NULL;         
               }
            }