Changeset 3188

Timestamp:

Feb 10, 2005, 10:56:41 AM (21 years ago)

Author:

jhoblitt

Message:

add psDBUpdateRows() (incomplete)
add psDBGenerateSetSQL()
add psDBGenerateUpdateRowSQL()

Location:

trunk/archive/psdb

Files:

• psDB.c (modified) (8 diffs)
• psDB.h (modified) (2 diffs)
• test.c (modified) (4 diffs)

trunk/archive/psdb/psDB.c

@@ -8 +8 @@
  *  @author Joshua Hoblitt
  *
- *  @version $Revision: 1.9 $ $Name: not supported by cvs2svn $
- *  @date $Date: 2005-02-10 00:02:16 $
+ *  @version $Revision: 1.10 $ $Name: not supported by cvs2svn $
+ *  @date $Date: 2005-02-10 20:56:41 $
  *
  *  Copyright 2005 Joshua Hoblitt, University of Hawaii
@@ -61 +61 @@
 static char    *psDBGenerateSelectColumnSQL(const char *tableName, const char *col, const psU64 limit);
 static char    *psDBGenerateSelectRowsSQL(const char *tableName, psMetadata *where);
+static char    *psDBGenerateUpdateRowSQL(const char *tableName, psMetadata *where, psMetadata *values);
+static char    *psDBGenerateSetSQL(psMetadata *set);
 
 psDB *psDBInit(const char *host, const char *user, const char *passwd, const char *dbname)
@@ -628 +630 @@
     // loop over rows
     for (j = 0; j < rowSet->n; j++) {
-        cursor = psListIteratorAlloc(row->list, 0);
+        // extract the psList, from the psMetadata, in the current psArray element
+        cursor = psListIteratorAlloc(((psMetadata *)rowSet->data[j])->list, 0);
 
         // reset bind for each row
@@ -660 +663 @@
                 } else {
                     // error, not a pointer to a string
+                    mysql_rollback(dbh->mysql);
+
+                    psFree(cursor);
+                    psFree(bind);
+                    mysql_stmt_close(stmt);
+
+                    return false;
                 }
             }
@@ -672 +682 @@
             fprintf(stderr, "Failed to bind params.  Error: %s\n", mysql_stmt_error(stmt));
 
+            mysql_rollback(dbh->mysql);
+
             psFree(bind);
             mysql_stmt_close(stmt);
@@ -681 +693 @@
             fprintf(stderr, "Failed to execute prepared statement.  Error: %s\n", mysql_stmt_error(stmt));
 
+            mysql_rollback(dbh->mysql);
+
             psFree(bind);
             mysql_stmt_close(stmt);
@@ -702 +716 @@
 {
     return psDBSelectRows(dbh, tableName, NULL);
+}
+
+psS64 psDBUpdateRows(psDB *dbh, const char *tableName, psMetadata *where, psMetadata *values)
+{
+    char            *query;
+    MYSQL_STMT      *stmt;              // prepared db statement
+    psU32           paramCount;         // number of placeholders in query
+    MYSQL_BIND      *bind;              // field values to insert
+    psU64           rowsAffected;
+
+    query = psDBGenerateUpdateRowSQL(tableName, where, values);
+    if (!query) {
+        return -1;
+    }
+
+    printf("complete query is %s\n", query);
+
+    stmt = mysql_stmt_init(dbh->mysql);
+    if (!stmt) {
+        // call abort?
+        fprintf(stderr, " mysql_stmt_init(), out of memory\n");
+
+        return -1;
+    }
+
+    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 -1;
+    }
+
+    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);
+
+    if (mysql_stmt_bind_param(stmt, bind)) {
+        fprintf(stderr, "Failed to bind params.  Error: %s\n", mysql_stmt_error(stmt));
+
+        mysql_rollback(dbh->mysql);
+
+        psFree(bind);
+        mysql_stmt_close(stmt);
+
+        return -1;
+    }
+
+    if (mysql_stmt_execute(stmt)) {
+        fprintf(stderr, "Failed to execute prepared statement.  Error: %s\n", mysql_stmt_error(stmt));
+
+        mysql_rollback(dbh->mysql);
+
+        psFree(bind);
+        mysql_stmt_close(stmt);
+
+        return -1;
+    }
+
+    psFree(bind);
+
+    // point of no return
+    mysql_commit(dbh->mysql);
+
+    rowsAffected = (psU64)mysql_stmt_affected_rows(stmt);
+
+    mysql_stmt_close(stmt);
+
+    return rowsAffected;
+}
+
+static char *psDBGenerateUpdateRowSQL(const char *tableName, psMetadata *where, psMetadata *values)
+{
+    char            *query;
+    char            *string;
+
+    if ((!values) || (!where)) {
+        return NULL;
+    }
+
+    // init query with a psMemBlock
+    query = psStringCopy("UPDATE ");
+    psStringAppend(&query, tableName);
+
+    string = psDBGenerateSetSQL(values);
+    if (!string) {
+        return NULL;
+    }
+    psStringAppend(&query, " ");
+    psStringAppend(&query, string);
+    psFree(string);
+
+    string = psDBGenerateWhereSQL(where);
+    if (!string) {
+        return NULL;
+    }
+    psStringAppend(&query, " ");
+    psStringAppend(&query, string);
+    psFree(string);
+
+    return query;
 }
 
@@ -785 +905 @@
         if (!cursor->offEnd) {
             psStringAppend(&query, " AND ");
+        }
+    }
+
+    psFree(cursor);
+
+    return query;
+}
+
+static char *psDBGenerateSetSQL(psMetadata *set)
+{
+    char            *query;
+    psListIterator  *cursor;
+    psMetadataItem  *item;
+
+    // init query with a psMemBlock
+    query = psStringCopy("SET ");
+
+    cursor = psListIteratorAlloc(set->list, 0);
+
+    // find column name 
+    while ((item = psListGetNext(cursor))) {
+        // + column name + _ + = + _ + ?
+        psStringAppend(&query, item->name);
+        psStringAppend(&query, " = ?");
+
+        // + ", " after every column declaration except the last one
+        if (!cursor->offEnd) {
+            psStringAppend(&query, ",  ");
         }
     }

trunk/archive/psdb/psDB.h

@@ -10 +10 @@
  *  @author Joshua Hoblitt
  *
- *  @version $Revision: 1.4 $ $Name: not supported by cvs2svn $
- *  @date $Date: 2005-02-10 00:02:16 $
+ *  @version $Revision: 1.5 $ $Name: not supported by cvs2svn $
+ *  @date $Date: 2005-02-10 20:56:41 $
  *
  *  Copyright 2005 Joshua Hoblitt, University of Hawaii
@@ -55 +55 @@
 psArray *psDBDumpRows(psDB *dbh, const char *tableName);
 
+psS64 psDBUpdateRows(psDB *dbh, const char *tableName, psMetadata *where, psMetadata *values);
+
 /// @}
 

trunk/archive/psdb/test.c

@@ -147 +147 @@
     }
 
-    //psDBInsertOneRow();
+    // psDBInsertOneRow();
     {
         psMetadata      *md;
@@ -162 +162 @@
     }
 
-    //psDBInsertRows();
+    // psDBInsertRows();
     {
         psArray         *rowSet;
@@ -197 +197 @@
     }
 
-    //psDBDumpRows();
+    // psDBDumpRows();
     {
         psArray         *resultSet;
@@ -206 +206 @@
     }
 
+    // psDBUpdateRows();
+    {
+        psMetadata      *where;
+        psMetadata      *value;
+        psMetadataItem  *item;
+        psU64           rowsAffected;
+
+        where = psMetadataAlloc();
+        item = psMetadataItemAlloc("color", PS_TYPE_PTR, PS_META_STR, NULL, "pink"); 
+        psMetadataAddItem(where, item, 0);
+        psFree(item);
+
+        value = psMetadataAlloc();
+        item = psMetadataItemAlloc("color", PS_TYPE_PTR, PS_META_STR, NULL, "HOT pink"); 
+        psMetadataAddItem(value, item, 0);
+        psFree(item);
+
+        rowsAffected = psDBUpdateRows(dbh, "horse", where, value);
+        psFree(where);
+        psFree(value);
+
+        OK(rowsAffected, "psDBUpdateRows()");
+    }
+    
     // cleanup other tests
-    psDBDropTable(dbh, "horse");
+    // psDBDropTable(dbh, "horse");
     psDBDropTable(dbh, "yak");