From 5e5026c1fc4c0bd9b448a08cb81fdc1a9b34e9c1 Mon Sep 17 00:00:00 2001
From: chriskl <chriskl>
Date: Sun, 25 May 2003 09:41:57 +0000
Subject: [PATCH] try to fix insert value analyzing, esp. for booleans

---
 classes/database/BaseDB.php   | 17 +++++-----
 classes/database/Postgres.php | 59 ++++++++++++++++++++++++++++++++---
 2 files changed, 63 insertions(+), 13 deletions(-)

diff --git a/classes/database/BaseDB.php b/classes/database/BaseDB.php
index d1342200..7994ca4c 100644
--- a/classes/database/BaseDB.php
+++ b/classes/database/BaseDB.php
@@ -4,7 +4,7 @@
  * A class that implements the DB interface for Postgres
  * Note: This class uses ADODB and returns RecordSets.
  *
- * $Id: BaseDB.php,v 1.17 2003/05/17 15:51:37 chriskl Exp $
+ * $Id: BaseDB.php,v 1.18 2003/05/25 09:41:57 chriskl Exp $
  */
 
 include_once('classes/database/ADODB_base.php');
@@ -63,8 +63,6 @@ class BaseDB extends ADODB_base {
 	function insertRow($table, $vars, $nulls, $format, $types) {
 		if (!is_array($vars) || !is_array($nulls) || !is_array($format)
 			|| !is_array($types)) return -1;
-		// @@ WE CANNOT USE insert AS WE NEED TO NOT QUOTE SOME THINGS
-		// @@ WHAT ABOUT BOOLEANS??
 		else {
 			$this->fieldClean($table);
 
@@ -74,17 +72,18 @@ class BaseDB extends ADODB_base {
 				$values = '';
 				foreach($vars as $key => $value) {
 					$doEscape = $format[$key] == 'VALUE';
-					$this->clean($key);
+					$this->fieldClean($key);
 					if ($doEscape) $this->clean($value);
 	
-					if ($fields) $fields .= ", \"{$key}\"";
-					else $fields = "INSERT INTO \"{$table}\" (\"{$key}\"";
-	
 					// Handle NULL values
 					if (isset($nulls[$key])) $tmp = 'NULL';
-					elseif ($doEscape) $tmp = "'{$value}'";
-					else $tmp = $value;
+					else $tmp = $this->formatValue($types[$key], $format[$key], $value);
 					
+					// If format Value retuns a null value, then don't bother
+					// inserting a value for that column.
+					if ($fields) $fields .= ", \"{$key}\"";
+					else $fields = "INSERT INTO \"{$table}\" (\"{$key}\"";
+
 					if ($values) $values .= ", {$tmp}";
 					else $values = ") VALUES ({$tmp}";
 				}
diff --git a/classes/database/Postgres.php b/classes/database/Postgres.php
index 46a8fe70..35cd6dc5 100755
--- a/classes/database/Postgres.php
+++ b/classes/database/Postgres.php
@@ -4,7 +4,7 @@
  * A class that implements the DB interface for Postgres
  * Note: This class uses ADODB and returns RecordSets.
  *
- * $Id: Postgres.php,v 1.114 2003/05/21 09:06:23 chriskl Exp $
+ * $Id: Postgres.php,v 1.115 2003/05/25 09:41:57 chriskl Exp $
  */
 
 // @@@ THOUGHT: What about inherits? ie. use of ONLY???
@@ -353,8 +353,8 @@ class Postgres extends BaseDB {
 				if ($value !== null && $value == '') $value = null;
 				echo "<select name=\"", htmlspecialchars($name), "\">\n";
 				echo "<option value=\"\"", ($value === null) ? ' selected' : '', "></option>\n";
-				echo "<option value=\"Y\"", ($value !== null && $value) ? ' selected' : '', ">{$lang['stryes']}</option>\n";
-				echo "<option value=\"N\"", ($value !== null && !$value) ? ' selected' : '', ">{$lang['strno']}</option>\n";
+				echo "<option value=\"TRUE\"", ($value !== null && $value) ? ' selected' : '', ">{$lang['strtrue']}</option>\n";
+				echo "<option value=\"FALSE\"", ($value !== null && !$value) ? ' selected' : '', ">{$lang['strfalse']}</option>\n";
 				echo "</select>\n";
 				break;
 			case 'text':
@@ -367,7 +367,58 @@ class Postgres extends BaseDB {
 				echo "<input name=\"", htmlspecialchars($name), "\" value=\"", htmlspecialchars($value), "\" size=\"35\" />\n";
 				break;
 		}		
-	}	
+	}
+	
+	/**
+	 * Formats a value or expression for sql purposes
+	 * @param $type The type of the field
+	 * @param $mode VALUE or EXPRESSION
+	 * @param $value The actual value entered in the field.  Can be NULL
+	 * @return The suitably quoted and escaped value.
+	 */
+	function formatValue($type, $format, $value) {
+		switch ($type) {
+			case 'bool':
+			case 'boolean':
+				if ($format == 'VALUE') {
+					if ($value == 'TRUE')
+						return 'TRUE';
+					elseif ($value == 'FALSE')
+						return 'FALSE';
+					else
+						return "''";
+				}
+				else return $value;
+				break;		
+			default:
+				// Checking variable fields is difficult as there might be a size
+				// attribute...			
+				if (strpos($type, 'time') === 0) {
+					// Assume it's one of the time types...
+					if ($value == '') return "''";
+					elseif (strcasecmp($value, 'CURRENT_TIMESTAMP') == 0 
+							|| strcasecmp($value, 'CURRENT_TIME') == 0
+							|| strcasecmp($value, 'CURRENT_DATE') == 0
+							|| strcasecmp($value, 'LOCALTIME') == 0
+							|| strcasecmp($value, 'LOCALTIMESTAMP') == 0) {
+						return $value;
+					}
+					elseif ($format == 'EXPRESSION')
+						return $value;
+					else {
+						$this->clean($value);
+						return "'{$value}'";
+					}
+				}
+				else {
+					if ($format == 'VALUE') {
+						$this->clean($value);
+						return "'{$value}'";					
+					}
+					return $value;
+				}
+		}
+	}
 
 	/**
 	 * Return all database available on the server
-- 
2.39.5