Changeset 7294 for trunk/psLib/src/db/psDB.c

Timestamp:

Jun 2, 2006, 9:21:49 AM (20 years ago)

Author:

Paul Price

Message:

Applying patch from Josh: adds 'or' conditional support to psDB where clauses

File:

• trunk/psLib/src/db/psDB.c (modified) (5 diffs)

trunk/psLib/src/db/psDB.c

@@ -12 +12 @@
  *  @author Joshua Hoblitt
  *
- *  @version $Revision: 1.52 $ $Name: not supported by cvs2svn $
- *  @date $Date: 2006-05-18 20:08:33 $
+ *  @version $Revision: 1.53 $ $Name: not supported by cvs2svn $
+ *  @date $Date: 2006-06-02 19:21:49 $
  *
  *  Copyright 2005 Joshua Hoblitt, University of Hawaii
@@ -64 +64 @@
 static char    *psDBGenerateSetSQL(const psMetadata *set
                                   );
+static char    *psDBGenerateConditionalSQL(const psMetadataItem *item);
 // lookup table functions
 static psElemType psDBMySQLToPType(enum enum_field_types type, unsigned int flags);
@@ -1350 +1351 @@
 {
     char            *query = NULL;
-    psListIterator  *cursor;
-    psMetadataItem  *item;
 
     if (!where) {
@@ -1360 +1359 @@
     query = psStringCopy("WHERE ");
 
-    cursor = psListIteratorAlloc(where->list, 0, false);
+    // we need to know if an item is 'MULTI' so we have to march through the
+    // list by key name and not by items
+    psList *keys = psHashKeyList(where->hash);
+    psListIterator *cursor = psListIteratorAlloc(keys, 0, false);
 
     // find column name and match pattern
-    while ((item = psListGetAndIncrement(cursor))) {
-        // item->data must be a string
-        if ((item->type == PS_DATA_S32) || (item->type == PS_TYPE_S32)) {
-            psStringAppend(&query, "%s=%d", item->name, (int)(item->data.S32));
-        } else if ((item->type == PS_DATA_F32) || (item->type == PS_TYPE_F32)) {
-            psStringAppend(&query, "%s=%g", item->name, (double)(item->data.F32));
-        } else if ((item->type == PS_DATA_F64) || (item->type == PS_TYPE_F64)) {
-            psStringAppend(&query, "%s=%g", item->name, (double)(item->data.F64));
-        } else if ((item->type == PS_DATA_BOOL) || (item->type == PS_TYPE_BOOL)) {
-            psStringAppend(&query, "%s=%d", item->name, (int)(item->data.B));
-        } else if (item->type == PS_DATA_STRING) {
-            // + column name + _ + like + _ + ' + value + '
-            // check for NULL and empty ("") strings
-            if (item->data.V == NULL || *(char *)item->data.V == '\0') {
-                psStringAppend(&query, "%s IS NULL", item->name);
-            } else {
-                // XXX ASC NOTE: we should have a better match for
-                // char & varchar columns than this.  LIKE is OK for
-                // very large TEXT columns that really shouldn't be
-                // used in a where clause...
-                psStringAppend(&query, "%s LIKE '%s'", item->name, item->data.V);
+    psString itemName = NULL;
+    while ((itemName = psListGetAndIncrement(cursor))) {
+        psMetadataItem *item = psMetadataLookup(where, itemName);
+
+        if (item->type == PS_DATA_METADATA_MULTI) {
+            char *logicalOp = "AND";
+
+            // scan through the list and change the logicalOp joining
+            // conditionals together to "OR" if a comment of "==" is found
+            psListIterator *mCursor = psListIteratorAlloc(item->data.list, 0,
+                                      false);
+            psMetadataItem *mItem = NULL;
+            while ((mItem = psListGetAndIncrement(mCursor))) {
+                if (mItem->comment && strstr(mItem->comment, "==")) {
+                    logicalOp = "OR";
+                    break;
+                }
             }
+
+            // "(" + conditional [ + " AND/OR " + conditional ] + ")"
+            // reset the iterator
+            psListIteratorSet(mCursor, 0);
+            psStringAppend(&query, "(");
+            while ((mItem = psListGetAndIncrement(mCursor))) {
+                char *conditional = psDBGenerateConditionalSQL(mItem);
+                if (!conditional) {
+                    psError(PS_ERR_UNKNOWN, false,
+                            "SQL conditional generation failed.");
+                    psFree(mCursor);
+                    psFree(cursor);
+                    psFree(keys);
+                    psFree(query);
+                    return NULL;
+                }
+
+                psStringAppend(&query, "%s", conditional);
+                psFree(conditional);
+
+                // + " AND/OR "
+                if (!mCursor->offEnd) {
+                    psStringAppend(&query, " %s ", logicalOp);
+                }
+
+            }
+            psFree(mCursor);
+
+            psStringAppend(&query, ")");
         } else {
-            psError(PS_ERR_BAD_PARAMETER_TYPE, true,
-                    "Only types PS_DATA_S32, PS_DATA_F32, PS_DATA_F64, PS_DATA_BOOL, PS_DATA_STRING are supported");
-
-            psFree(cursor);
-            psFree(query);
-
-            return NULL;
-        }
-
-        // + " and " after every column declaration except the last one
-        if (!cursor->offEnd) {
-            psStringAppend(&query, " AND ");
-        }
-    }
-
-    psFree(cursor);
+            char *conditional = psDBGenerateConditionalSQL(item);
+            if (!conditional) {
+                psError(PS_ERR_UNKNOWN, false,
+                        "SQL conditional generation failed.");
+                psFree(cursor);
+                psFree(keys);
+                psFree(query);
+                return NULL;
+            }
+
+            psStringAppend(&query, "%s", conditional);
+            psFree(conditional);
+
+            // + " and " after every column declaration except the last one
+            if (!cursor->offEnd) {
+                psStringAppend(&query, " AND ");
+            }
+        }
+    }
+
+    //    psFree(cursor);
+    psFree(keys);
 
     return query;
@@ -1437 +1470 @@
 
     psFree(cursor);
+
+    return query;
+}
+
+static char *psDBGenerateConditionalSQL(const psMetadataItem *item)
+{
+    char            *query = NULL;
+
+    PS_ASSERT_PTR_NON_NULL(item, NULL);
+
+    // stringify the psMetadataItem into a SQL search specification
+    if ((item->type == PS_DATA_S32) || (item->type == PS_TYPE_S32)) {
+        psStringAppend(&query, "%s=%d", item->name, (int)(item->data.S32));
+    } else if ((item->type == PS_DATA_F32) || (item->type == PS_TYPE_F32)) {
+        psStringAppend(&query, "%s=%g", item->name, (double)(item->data.F32));
+    } else if ((item->type == PS_DATA_F64) || (item->type == PS_TYPE_F64)) {
+        psStringAppend(&query, "%s=%g", item->name, (double)(item->data.F64));
+    } else if ((item->type == PS_DATA_BOOL) || (item->type == PS_TYPE_BOOL)) {
+        psStringAppend(&query, "%s=%d", item->name, (int)(item->data.B));
+    } else if (item->type == PS_DATA_STRING) {
+        // + column name + _ + like + _ + ' + value + '
+        // check for NULL and empty ("") strings
+        if (item->data.V == NULL || *(char *)item->data.V == '\0') {
+            psStringAppend(&query, "%s IS NULL", item->name);
+        } else {
+            // XXX ASC NOTE: we should have a better match for
+            // char & varchar columns than this.  LIKE is OK for
+            // very large TEXT columns that really shouldn't be
+            // used in a where clause...
+            psStringAppend(&query, "%s LIKE '%s'", item->name, item->data.V);
+        }
+    } else {
+        psError(PS_ERR_BAD_PARAMETER_TYPE, true,
+                "Only types PS_DATA_S32, PS_DATA_F32, PS_DATA_F64, PS_DATA_BOOL, PS_DATA_STRING are supported");
+
+        return NULL;
+    }
 
     return query;