Index: trunk/psLib/src/db/psDB.c =================================================================== --- trunk/psLib/src/db/psDB.c (revision 7626) +++ trunk/psLib/src/db/psDB.c (revision 7644) @@ -12,6 +12,6 @@ * @author Joshua Hoblitt * - * @version $Revision: 1.60 $ $Name: not supported by cvs2svn $ - * @date $Date: 2006-06-22 00:36:22 $ + * @version $Revision: 1.61 $ $Name: not supported by cvs2svn $ + * @date $Date: 2006-06-23 01:51:48 $ * * Copyright 2005 Joshua Hoblitt, University of Hawaii @@ -53,4 +53,11 @@ mysqlType; +typedef struct +{ + MYSQL_BIND *bind; + psU32 n; +} +psDBMysqlRow; + static pthread_mutex_t lookupTableMutex = PTHREAD_MUTEX_INITIALIZER; static psHash *pTypeToSQLlookupTable = NULL; @@ -60,5 +67,7 @@ // database utility functions -static inline bool psDBPackRow(MYSQL_BIND *bind, const psMetadata *values, psU32 paramCount); +static inline bool psDBPackMySQLRow(psDBMysqlRow *mysqlRow, const psMetadata *values); +static psDBMysqlRow *psDBMysqlRowAlloc(psU32 paramCount); +static void psDBMysqlRowFree(psDBMysqlRow *row); // SQL generation functions @@ -564,8 +573,6 @@ const psArray *rowSet) { - psMetadata *row; // row of data char *query; // SQL query MYSQL_STMT *stmt; // prepared db statement - MYSQL_BIND *bind; // field values to insert unsigned long paramCount; // number of placeholders in query psU64 j; // row index @@ -585,5 +592,5 @@ // we are assuming that all rows in the set have an identical with reguard // to field count and type - row = rowSet->data[0]; + psMetadata *row = rowSet->data[0]; // Generate SQL query string @@ -611,29 +618,28 @@ paramCount = mysql_stmt_param_count(stmt); - // structure larger enough to hold one field of data per place holder - bind = psAlloc(sizeof(MYSQL_BIND) * paramCount); + // structure large enough to hold one field of data per place holder + psDBMysqlRow *mysqlRow = psDBMysqlRowAlloc(paramCount); // loop over rows for (j = 0; j < rowSet->n; j++) { - row = rowSet->data[j]; - - // reset bind for each row - memset(bind, 0, sizeof(MYSQL_BIND) * paramCount); - - if (!psDBPackRow(bind, row, paramCount)) { + psMetadata *rowData = rowSet->data[j]; + + if (!psDBPackMySQLRow(mysqlRow, rowData)) { psError(PS_ERR_UNKNOWN, false, "Failed to pack params into bind structure."); mysql_rollback(dbh->mysql); - psFree(bind); + psFree(mysqlRow); mysql_stmt_close(stmt); - } - - if (mysql_stmt_bind_param(stmt, bind)) { + + return false; + } + + if (mysql_stmt_bind_param(stmt, mysqlRow->bind)) { psError(PS_ERR_UNKNOWN, true, "Failed to bind params. Error: %s", mysql_stmt_error(stmt)); mysql_rollback(dbh->mysql); - psFree(bind); + psFree(mysqlRow); mysql_stmt_close(stmt); @@ -647,5 +653,5 @@ mysql_rollback(dbh->mysql); - psFree(bind); + psFree(mysqlRow); mysql_stmt_close(stmt); @@ -654,5 +660,5 @@ } // end loop over rows - psFree(bind); + psFree(mysqlRow); mysql_stmt_close(stmt); @@ -768,11 +774,8 @@ // structure large enough to hold one field of data per place holder - bind = psAlloc(sizeof(MYSQL_BIND) * paramCount); - - // init bind - memset(bind, 0, sizeof(MYSQL_BIND) * paramCount); - - if (!psDBPackRow(bind, values, paramCount)) { - psFree(bind); + psDBMysqlRow *mysqlRow = psDBMysqlRowAlloc(paramCount); + + if (!psDBPackMySQLRow(mysqlRow, values)) { + psFree(mysqlRow); mysql_stmt_close(stmt); return -1; @@ -782,5 +785,5 @@ psError(PS_ERR_UNKNOWN, true, "Failed to bind params. Error: %s", mysql_stmt_error(stmt)); mysql_rollback(dbh->mysql); - psFree(bind); + psFree(mysqlRow); mysql_stmt_close(stmt); return -1; @@ -791,10 +794,10 @@ mysql_stmt_error(stmt)); mysql_rollback(dbh->mysql); - psFree(bind); + psFree(mysqlRow); mysql_stmt_close(stmt); return -1; } - psFree(bind); + psFree(mysqlRow); // mysql_stmt_affected_rows() must be called before a commit @@ -948,4 +951,89 @@ psFree(dest); return true; +} + +long p_psDBRunQueryPrepared(psDB *dbh, + const psArray *rowSet, + const char *format, + ...) +{ + va_list argPtr; + char *query = NULL; + + PS_ASSERT_PTR_NON_NULL(dbh, -1); + PS_ASSERT_PTR_NON_NULL(rowSet, -1); + PS_ASSERT_PTR_NON_NULL(format, -1); + + // generate query string + va_start(argPtr, format); + if(argPtr == NULL) { + query = psStringCopy(format); + } else { + int destSize = vsnprintf(query, 0, format, argPtr); + query = psAlloc(destSize + 1); + vsnprintf(query, destSize + 1, format, argPtr); + } + va_end(argPtr); + + // Prepare SQL statement + MYSQL_STMT *stmt = mysql_stmt_init(dbh->mysql); + if (!stmt) { + psAbort(__func__, "mysql_stmt_init(), out of memory."); + } + if (mysql_stmt_prepare(stmt, query, (unsigned long)strlen(query))) { + psError(PS_ERR_UNKNOWN, true, "Failed to prepare query. Error: %s", mysql_stmt_error(stmt)); + mysql_stmt_close(stmt); + psFree(query); + return -1; + } + + psFree(query); + + // how many place holders are in our query + psS32 paramCount = mysql_stmt_param_count(stmt); + + // structure large enough to hold one field of data per place holder + psDBMysqlRow *mysqlRow = psDBMysqlRowAlloc(paramCount); + + // loop over rows + for (long j = 0; j < rowSet->n; j++) { + psMetadata *rowData = rowSet->data[j]; + + if (!psDBPackMySQLRow(mysqlRow, rowData)) { + psError(PS_ERR_UNKNOWN, false, "Failed to pack params into bind structure."); + + psFree(mysqlRow); + mysql_stmt_close(stmt); + + return -1; + } + + if (mysql_stmt_bind_param(stmt, mysqlRow->bind)) { + psError(PS_ERR_UNKNOWN, true, "Failed to bind params. Error: %s", mysql_stmt_error(stmt)); + + psFree(mysqlRow); + mysql_stmt_close(stmt); + + return -1; + } + + if (mysql_stmt_execute(stmt)) { + psError(PS_ERR_UNKNOWN, true, "Failed to execute prepared statement. Error: %s", mysql_stmt_error(stmt)); + + psFree(mysqlRow); + mysql_stmt_close(stmt); + + return -1; + } + } // end loop over rows + + psFree(mysqlRow); + + // FYI mysql_stmt_affected_rows() must be called before a commit + long rowsAffected = mysql_stmt_affected_rows(stmt); + + mysql_stmt_close(stmt); + + return rowsAffected; } @@ -1026,4 +1114,7 @@ } else if (pType == PS_DATA_BOOL) { psMetadataAdd(md, PS_LIST_TAIL, field[i].name, pType, "", atoi(data)); + } else if (pType == PS_DATA_TIME) { + psMetadataAddTime(md, PS_LIST_TAIL, field[i].name, 0, "", + psTimeFromISO(data, PS_TIME_UTC)); } else { // XXX: assume binary string ... @@ -1044,25 +1135,22 @@ } -static inline bool psDBPackRow(MYSQL_BIND *bind, - const psMetadata *values, - psU32 paramCount) -{ - psListIterator *cursor; // row iterator - psMetadataItem *item; // field in row +static inline bool psDBPackMySQLRow(psDBMysqlRow *mysqlRow, + const psMetadata *values) +{ mysqlType *mType; // type tmp variable static bool isNull = true; // used in a MYSQL_BIND to indicate NULL, - // this will be used outside of this func - psU32 i; // field index - - // Verify values or bind are not null - if((values == NULL) || (bind == NULL)) { - return false; - } - + + PS_ASSERT_PTR_NON_NULL(mysqlRow, false); + PS_ASSERT_PTR_NON_NULL(values, false); + + MYSQL_BIND *bind = mysqlRow->bind; + + // row iterator // check size of values == paramCount ? - cursor = psListIteratorAlloc(values->list, 0, false); + psListIterator *cursor = psListIteratorAlloc(values->list, 0, false); // loop over fields - i = 0; + psU32 i = 0; + psMetadataItem *item = NULL; // field in row while ((item = psListGetAndIncrement(cursor))) { // lookup pType -> mysql type @@ -1112,8 +1200,29 @@ bind[i].buffer_length = (unsigned long)strlen((char *)item->data.V); bind[i].length = &bind[i].buffer_length; - bind[i].buffer = item->data.V; + bind[i].buffer = psStringCopy(item->data.V); bind[i].is_null = *(char *)item->data.V == '\0' ? (my_bool *)&isNull : NULL; + } else { + // handles the case of NULL as a NULL database value + bind[i].buffer_length = 0; + bind[i].length = &bind[i].buffer_length; + bind[i].buffer = NULL; + bind[i].is_null = (my_bool *)&isNull; + } + } else if (item->type == PS_DATA_TIME) { + // XXX we're abusing the comment field of the metadata item here as + // we need to have a buffer that exists outside this functions + // scope without leaking memory + // make a copy of the psTime so we don't modify user data when we + // try to do the conversion + if ((char *)item->data.V) { + psTime *time = p_psTimeCopy((psTime *)item->data.V); + psTimeConvert(time, PS_TIME_UTC); + bind[i].buffer = psTimeToISO(time); + psFree(time); + bind[i].buffer_length = (unsigned long)strlen((char *)bind[i].buffer); + bind[i].length = &bind[i].buffer_length; + bind[i].is_null = NULL; } else { // handles the case of NULL as a NULL database value @@ -1127,6 +1236,6 @@ "FIXME: Only type of PS_TYPE_S32 (PS_DATA_S32), " "PS_TYPE_F32 (PS_DATA_F32), PS_TYPE_F64 (PS_DATA_F64), " - "PS_TYPE_BOOL (PS_DATA_BOOL), " - "and PS_DATA_STRING are supported."); + "PS_TYPE_BOOL (PS_DATA_BOOL), PS_DATA_STRING" + "and PS_DATA_TIME are supported."); psFree(cursor); @@ -1142,4 +1251,31 @@ return true; +} + +static psDBMysqlRow *psDBMysqlRowAlloc(psU32 paramCount) +{ + psDBMysqlRow *row = psAlloc(sizeof(psDBMysqlRow)); + psMemSetDeallocator(row, (psFreeFunc)psDBMysqlRowFree); + + row->bind = psAlloc(sizeof(MYSQL_BIND) * paramCount); + memset(row->bind, 0, sizeof(MYSQL_BIND) * paramCount); + row->n = paramCount; + + return row; +} + +static void psDBMysqlRowFree(psDBMysqlRow *mysqlRow) +{ + MYSQL_BIND *bind = mysqlRow->bind; + + for (psU32 i = 0; i < mysqlRow->n; i++) { + // buffer_length is only defined for pointers to character buffers + // primitive types will have this value set to zero. + if (bind[i].buffer_length) { + psFree(bind[i].buffer); + } + } + + psFree(bind); } @@ -1695,4 +1831,5 @@ psDBAddToLookupTable(pTypeToSQLlookupTable, PS_TYPE_BOOL,"TINYINT"); psDBAddToLookupTable(pTypeToSQLlookupTable, PS_DATA_STRING, "VARCHAR"); + psDBAddToLookupTable(pTypeToSQLlookupTable, PS_DATA_TIME, "DATETIME"); } else { // increment the ref count by one for every psDB @@ -1843,4 +1980,5 @@ psDBAddVoidToLookupTable(pTypeToMysqlLookupTable, PS_DATA_STRING, psDBMySQLTypeAlloc(MYSQL_TYPE_VAR_STRING, false)); + psDBAddVoidToLookupTable(pTypeToMysqlLookupTable, PS_DATA_TIME, psDBMySQLTypeAlloc(MYSQL_TYPE_DATETIME, false)); psDBAddVoidToLookupTable(pTypeToMysqlLookupTable, PS_DATA_VECTOR, psDBMySQLTypeAlloc(MYSQL_TYPE_VAR_STRING, false)); psDBAddVoidToLookupTable(pTypeToMysqlLookupTable, PS_DATA_IMAGE, psDBMySQLTypeAlloc(MYSQL_TYPE_VAR_STRING, false)); Index: trunk/psLib/src/db/psDB.h =================================================================== --- trunk/psLib/src/db/psDB.h (revision 7626) +++ trunk/psLib/src/db/psDB.h (revision 7644) @@ -10,6 +10,6 @@ * @author Joshua Hoblitt * - * @version $Revision: 1.18 $ $Name: not supported by cvs2svn $ - * @date $Date: 2006-06-07 03:40:43 $ + * @version $Revision: 1.19 $ $Name: not supported by cvs2svn $ + * @date $Date: 2006-06-23 01:51:48 $ * * Copyright 2005 Joshua Hoblitt, University of Hawaii @@ -94,4 +94,19 @@ const char *format, ///< SQL string to execute ... ///< Arguments for name formatting and metadata item data. +); + +/** Executes a SQL query as a prepared statement + * + * This function will execute a string as a raw SQL query. No additional + * processing of the string or abstraction of the underlying database's SQL + * dialect is provided. Caveat emptor. + * + * @return long: the number of database rows affected + */ +long p_psDBRunQueryPrepared( + psDB *dbh, ///< Database handle + const psArray *rowSet, ///< row data as psArray of psMetadata + const char *format, ///< SQL string to execute + ... );