Changeset 7644

Timestamp:

Jun 22, 2006, 3:51:48 PM (20 years ago)

Author:

jhoblitt

Message:

• rework the way MYSQL_BIND arrays are handled so that they are wrapped in a structure that keeps track of how many elements are in the array. This allows a custom free function to iterate through the array and free allocated buffers.
• preliminary psTime support
• add p_psDBRunQueryPrepared() (not yet used)

Location:

trunk/psLib/src/db

Files:

• psDB.c (modified) (19 diffs)
• psDB.h (modified) (2 diffs)

trunk/psLib/src/db/psDB.c

@@ -12 +12 @@
  *  @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 +53 @@
 mysqlType;
 
+typedef struct
+{
+    MYSQL_BIND      *bind;
+    psU32           n;
+}
+psDBMysqlRow;
+
 static pthread_mutex_t lookupTableMutex = PTHREAD_MUTEX_INITIALIZER;
 static psHash   *pTypeToSQLlookupTable = NULL;
@@ -60 +67 @@
 
 // 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 +573 @@
                     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 +592 @@
     // 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 +618 @@
     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 +653 @@
             mysql_rollback(dbh->mysql);
 
-            psFree(bind);
+            psFree(mysqlRow);
             mysql_stmt_close(stmt);
 
@@ -654 +660 @@
     } // end loop over rows
 
-    psFree(bind);
+    psFree(mysqlRow);
     mysql_stmt_close(stmt);
 
@@ -768 +774 @@
 
     // 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 +785 @@
         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 +794 @@
                 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 +951 @@
     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 +1114 @@
             } 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 +1135 @@
 }
 
-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 +1200 @@
                 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 +1236 @@
                     "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 +1251 @@
 
     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 +1831 @@
         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 +1980 @@
 
         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));

trunk/psLib/src/db/psDB.h

@@ -10 +10 @@
  *  @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 +94 @@
     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
+    ...
 );