Changeset 3175

Timestamp:

Feb 9, 2005, 2:02:16 PM (21 years ago)

Author:

jhoblitt

Message:

add psDBInsertRows()
change mysqlType typdef to use enum_field_types
test tweaks

Location:

trunk/archive/psdb

Files:

• psDB.c (modified) (3 diffs)
• psDB.h (modified) (2 diffs)
• test.c (modified) (2 diffs)

trunk/archive/psdb/psDB.c

@@ -8 +8 @@
  *  @author Joshua Hoblitt
  *
- *  @version $Revision: 1.8 $ $Name: not supported by cvs2svn $
- *  @date $Date: 2005-02-09 01:15:39 $
+ *  @version $Revision: 1.9 $ $Name: not supported by cvs2svn $
+ *  @date $Date: 2005-02-10 00:02:16 $
  *
  *  Copyright 2005 Joshua Hoblitt, University of Hawaii
@@ -25 +25 @@
 typedef struct
 {
-    psU32   type;
-    bool    isUnsigned;
+    enum enum_field_types type;
+    bool            isUnsigned;
 } mysqlType;
 
@@ -564 +564 @@
         return false;
     }
+
+    // point of no return
+    mysql_commit(dbh->mysql);
+
+    psFree(bind);
+    mysql_stmt_close(stmt);
+
+    return true;
+}
+
+bool psDBInsertRows(psDB *dbh, const char *tableName, psArray *rowSet)
+{
+    psMetadata      *row;               // row of data
+    char            *query;             // SQL query
+    MYSQL_STMT      *stmt;              // prepared db statement
+    MYSQL_BIND      *bind;              // field values to insert
+    psU32           paramCount;         // number of placeholders in query
+    psHash          *mysqlFieldType;    // type lookup table
+    mysqlType       *mType;             // type tmp variable
+    char            *key;               // hash tmp variable
+    psMetadataItem  *item;              // field in row
+    psListIterator  *cursor;            // row iterator
+    psU32           i;                  // field index
+    psU64           j;                  // row index
+    bool            isNull = true;      // used in a MYSQL_BIND to indicate NULL
+
+    // we are assuming that all rows in the set have an identical with reguard
+    // to field count and type
+    row = rowSet->data[0];
+
+    query = psDBGenerateInsertRowSQL(tableName, row);
+    if (!query) {
+        return NULL;
+    }
+
+    stmt = mysql_stmt_init(dbh->mysql);
+    if (!stmt) {
+        // call abort?
+        fprintf(stderr, " mysql_stmt_init(), out of memory\n");
+
+        return false;
+    }
+
+    if (mysql_stmt_prepare(stmt, query, (unsigned long)strlen(query))) {
+            fprintf(stderr, "Failed to prepare query.  Error: %s\n", mysql_stmt_error(stmt));
+
+            mysql_stmt_close(stmt);
+            psFree(query);
+
+            return false;
+    }
+
+    psFree(query);
+
+    // how many place holders are in our query
+    paramCount = (psU32)mysql_stmt_param_count(stmt);
+
+    // structure larger enough to hold one field of data per place holder
+    bind = psAlloc(sizeof(MYSQL_BIND) * paramCount);
+
+    mysqlFieldType = psDBGetPSToMySQLTable();
+
+    // loop over rows
+    for (j = 0; j < rowSet->n; j++) {
+        cursor = psListIteratorAlloc(row->list, 0);
+
+        // reset bind for each row
+        memset(bind, 0, sizeof(MYSQL_BIND) * paramCount);
+
+        // loop over fields
+        i = 0;
+        while ((item = psListGetNext(cursor))) {
+            key = psDBIntToString((psU64)item->pType);
+            mType = psHashLookup(mysqlFieldType, key);
+            psFree(key);
+
+            // input data length is determined by the MYSQL_TYPE_* unless it's a string
+            bind[i].buffer  = (char *)item->data.V;
+            bind[i].buffer_type = mType->type;
+            bind[i].is_unsigned = mType->isUnsigned;
+
+            // convert NaNs to NULL
+            bind[i].is_null = psDBIsPTypeNaN(item->pType, item->data.V) 
+                            ? (my_bool *)&isNull
+                            : NULL;
+
+            if (item->pType == PS_TYPE_PTR) {
+                if (item->type == PS_META_STR) {
+                    if (*(char *)item->data.V == '\0') {
+                        bind[i].is_null = (my_bool *)&isNull;
+                    } else {
+                        bind[i].buffer_length = (unsigned long)strlen((char *)item->data.V);
+                        bind[i].is_null = NULL;
+                    }
+                } else {
+                    // error, not a pointer to a string
+                }
+            }
+
+            // increment field index
+            i++;
+        }
+
+        psFree(cursor);
+
+        if (mysql_stmt_bind_param(stmt, bind)) {
+            fprintf(stderr, "Failed to bind params.  Error: %s\n", mysql_stmt_error(stmt));
+
+            psFree(bind);
+            mysql_stmt_close(stmt);
+
+            return false;
+        }
+
+        if (mysql_stmt_execute(stmt)) {
+            fprintf(stderr, "Failed to execute prepared statement.  Error: %s\n", mysql_stmt_error(stmt));
+
+            psFree(bind);
+            mysql_stmt_close(stmt);
+
+            return false;
+        }
+    } // end loop over rows
+
+    psFree(mysqlFieldType);
 
     // point of no return

trunk/archive/psdb/psDB.h

@@ -10 +10 @@
  *  @author Joshua Hoblitt
  *
- *  @version $Revision: 1.3 $ $Name: not supported by cvs2svn $
- *  @date $Date: 2005-02-09 01:15:39 $
+ *  @version $Revision: 1.4 $ $Name: not supported by cvs2svn $
+ *  @date $Date: 2005-02-10 00:02:16 $
  *
  *  Copyright 2005 Joshua Hoblitt, University of Hawaii
@@ -51 +51 @@
 bool psDBInsertOneRow(psDB *dbh, const char *tableName, psMetadata *row);
 
+bool psDBInsertRows(psDB *dbh, const char *tableName, psArray *rowSet);
+
 psArray *psDBDumpRows(psDB *dbh, const char *tableName);
 

trunk/archive/psdb/test.c

@@ -48 +48 @@
 
     psFree(md);
+    
+    // setup horses table for later tests
+    {
+        psMetadata      *md;
+        psMetadataItem  *item;
+
+        md = psMetadataAlloc();
+        item = psMetadataItemAlloc("color", PS_TYPE_PTR, PS_META_STR, NULL, "100");
+        psMetadataAddItem(md, item, 3);
+        psFree(item);
+        psDBCreateTable(dbh, "horses", md);
+        psFree(md);
+    }
 
     // psDBSelectColumn()
@@ -103 +116 @@
     psFree(md);
 
+    //psDBInsertRows();
+
+    {
+        psArray         *rowSet;
+        psMetadata      *row;
+        psMetadataItem  *field;
+
+        rowSet = psArrayAlloc(3);
+        rowSet->n = 0;
+
+        row = psMetadataAlloc();
+        field = psMetadataItemAlloc("color", PS_TYPE_PTR, PS_META_STR, NULL, "brown"); 
+        psMetadataAddItem(row, field, 0);
+        psFree(field);
+        psArrayAdd(rowSet, 0, row);
+        psFree(row);
+
+        row = psMetadataAlloc();
+        field = psMetadataItemAlloc("color", PS_TYPE_PTR, PS_META_STR, NULL, ""); 
+        psMetadataAddItem(row, field, 0);
+        psFree(field);
+        psArrayAdd(rowSet, 0, row);
+        psFree(row);
+
+        row = psMetadataAlloc();
+        field = psMetadataItemAlloc("color", PS_TYPE_PTR, PS_META_STR, NULL, "pink"); 
+        psMetadataAddItem(row, field, 0);
+        psFree(field);
+        psArrayAdd(rowSet, 0, row);
+        psFree(row);
+
+        if (psDBInsertRows(dbh, "horses", rowSet)) {
+            printf("psDBInsertRows() succeeded\n");
+        }
+
+        psFree(rowSet);
+    }
+
     //psDBDumpRows();
     resultSet = psDBDumpRows(dbh, "horses");
     psFree(resultSet);
+
+    // cleanup other tests
+    psDBDropTable(dbh, "horses");
 
     // close db