some fixes

2 files changed, 46 insertions, 36 deletions

diff --git a/MainController.php b/MainController.php
index a7542d4..17e7167 100644
--- a/MainController.php
+++ b/MainController.php
@@ -33,6 +33,8 @@ class MainController
   private $table;                		// corresponding Db table
   private $primary_key;           	// name of primary key column
   
+  private $sql = '';                // the current SQL query
+  
   private $sql_select = 'SELECT %s FROM %s %s %s';
   
   private $sql_insert = 'INSERT INTO %s (%s) VALUES (%s)';
@@ -102,7 +104,7 @@ class MainController
 	      $schema = MainDb::fetch($sql);
 	      
 	      foreach( $schema as $s ) {
-	        $this->data[$s['column_name']] = '';
+	        $this->data[$s['column_name']] = NULL;
 	      }
 	      
 	      break;
@@ -138,10 +140,10 @@ class MainController
 	  
 	  list($key, $value) = each($where);
 	  
-	  $sql = 'SELECT * FROM `%s` WHERE `%s` = ? LIMIT 1';
-	  $sql = sprintf($sql, $this->table, $key);
+	  $this->sql = 'SELECT * FROM %s WHERE %s = ? LIMIT 1';
+	  $this->sql = sprintf($this->sql, $this->table, $key);
 
-	  return MainDb::one($sql, array($value));
+	  return MainDb::one($this->sql, array($value));
 	  
 	}
 	
@@ -251,6 +253,7 @@ class MainController
 			$group_by = '';
 		  $order_by = '';
   		
+  		// try to determine table name of child
   		$table = ($child::TableName) ? $child::TableName : $child;
   		
   		if ($ob = $child::OrderBy) {
@@ -277,13 +280,13 @@ class MainController
       
 			if (in_array($this->table, $many_to_many)) // m/n
 			{
-				$sql = "SELECT `{$table}`.*, `{$table}_{$this->table}`.*,
-				`{$table}_{$this->table}`.id AS {$table}_{$this->table}_id,
-				`{$table}`.*
-				FROM `{$table}_{$this->table}`, `{$this->table}`, `$table`
-				WHERE `{$table}_{$this->table}`.`{$this->table}_id` = `$this->table`.id AND
-      	`{$table}_{$this->table}`.`{$table}_id` = `$table`.id AND
-      	`{$this->table}`.id = ?";
+				$sql = "SELECT {$table}.*, {$table}_{$this->table}.*,
+				{$table}_{$this->table}.id AS {$table}_{$this->table}_id,
+				{$table}.*
+				FROM {$table}_{$this->table}, {$this->table}, {$table}
+				WHERE {$table}_{$this->table}.{$pk} = {$this->table}.id AND
+      	{$table}_{$this->table}.{$pk} = {$table}.id AND
+      	{$this->table}.id = ?";
 			}
 			else if (@in_array($table, $ref_schema['belongsTo'])) { // 1/m
 			  $sql = "SELECT * FROM `$ref` WHERE `$ref`.`{$table}_id` = ?";
@@ -295,7 +298,7 @@ class MainController
         return MainApp::error('Error executing query ' . $sql);
       }
       
-			$this->data[$i][$child] = $stmt->fetchAll(PDO::FETCH_ASSOC);
+			$this->data[$i][$table] = $stmt->fetchAll(PDO::FETCH_ASSOC);
 			
 		}
 	}
@@ -308,7 +311,7 @@ class MainController
 		$fk = explode(',', static::ForeignKey);
     
     // No parents defined
-    if (empty($fk[0])) {
+    if (empty($fk)) {
       return false;
     }
 		
@@ -316,6 +319,9 @@ class MainController
 		{
 			$fkey = 'id';
 			$lkey = "{$parent}_id";
+			
+			// try to determine parent table's table name
+			$parent = ($parent::TableName) ? $parent::TableName : $parent;
       
 			@$parent_id = $row[$lkey];
       @$ref_schema = $fk[''];
@@ -324,11 +330,11 @@ class MainController
 			{
 				foreach ($ref_schema['select'] as $a => $b)
 				{
-					$select .= ", $b AS `{$a}`";
+					$select .= ", $b AS {$a}";
 				}
 			}
 			
-			$sql = "SELECT $select FROM `{$parent}` WHERE `{$fkey}` = ?";
+			$sql = "SELECT $select FROM {$parent} WHERE {$fkey} = ?";
 			
       $stmt = MainDb::query($sql, array($parent_id));
 			$this->data[$i][$parent] = $stmt->fetchAll(PDO::FETCH_ASSOC);
@@ -353,15 +359,16 @@ class MainController
 		}
 		
 		foreach($data as $k => $v) {
-			$insert .= "`{$k}`, ";
+			$insert .= "{$k}, ";
 			$values[":{$k}"] = $v;
 		}
 		
 		$insert = rtrim($insert, ', ');
 		$val = implode(', ', array_keys($values));
-		$sql = "INSERT INTO `{$this->table}` ({$insert}) VALUES ({$val})";
 		
-		return MainDb::query($sql, $values);
+		$this->sql_insert = sprintf($this->sql_insert, $this->table, $insert, $val);
+		
+		return MainDb::query($this->sql_insert, $values);
 		
 	}
 	
@@ -385,9 +392,9 @@ class MainController
 		}
 		
     $data = array(":{$key}" => $value);
-		$sql = "DELETE FROM `{$this->table}` WHERE `{$key}` = :{$key} $limit";
+		$this->sql = "DELETE FROM {$this->table} WHERE {$key} = :{$key} $limit";
     
-		return MainDb::query($sql, $data);
+		return MainDb::query($this->sql, $data);
 		
 	}
 	
@@ -415,14 +422,14 @@ class MainController
     }
     
     foreach ($data as $k => $v) {
-      $query .= "`$k` = :$k, ";
+      $query .= "$k = :$k, ";
       $values[':'.$k] = $v;
     }
     
     $query = rtrim($query, ', ');
-    $sql = "UPDATE `{$this->table}` SET $query WHERE `$col` = :$col";
+    $this->sql = sprintf('UPDATE %s SET %s WHERE %s = :%s', $this->table, $query, $col, $col);
     
-    return MainDb::query($sql, $values);
+    return MainDb::query($this->sql, $values);
     
   }
 	
diff --git a/MainDb.php b/MainDb.php
index e1e76eb..dba4e18 100644
--- a/MainDb.php
+++ b/MainDb.php
@@ -59,7 +59,8 @@ class MainDb
 		  }
 		
     }
-  
+    
+    self::$instance->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_EMPTY_STRING);
     return self::$instance;
   
   }
@@ -108,25 +109,27 @@ class MainDb
       $pdo = self::getInstance();
       $stmt = $pdo->prepare($sql);
       
-      if (!$stmt) {
+      if( !$stmt ) {
         list($ec, $dec, $emsg) = $pdo->errorInfo();
         $error = $emsg ."\n" . print_r(debug_backtrace(), TRUE);
         return MainApp::error($error);
       }
       
-      $result = $stmt->execute($data);
+      $result = $stmt->execute( $data );
       
-      if (!$result) {
+      if( !$result ) {
         list($ec, $dec, $emsg) = $pdo->errorInfo();
         $error = $emsg ."\n" . print_r(debug_backtrace(), TRUE);
         return MainApp::error($error);
       }
     
     }
-    catch (PDOException $e) {
-        $error = $e->getMessage() . $sql;
-        $error .= "\n" . print_r(debug_backtrace(), TRUE);
-        return MainApp::error($error);
+    catch( PDOException $e ) {
+      
+      $error = $e->getMessage() . $sql;
+      $error .= "\n" . print_r(debug_backtrace(), TRUE);
+      return MainApp::error( $error );
+    
     }
     
     // DELETE statements should report number of rows deleted
@@ -149,15 +152,15 @@ class MainDb
       return $stmt;
     }
     
-    if (empty($data[':id'])) {
-      $data[':id'] = $pdo->lastInsertId();
+    if( empty( $data[':id'] )) {
+      $data[':id'] = $pdo->lastInsertId('order_id_seq');
     }
     
     $out = array();
     
     // Always strip ":" prefixes from input array keys
-    foreach ($data as $k => $v) {
-      $key = ltrim($k, ':');
+    foreach( $data as $k => $v ) {
+      $key = ltrim( $k, ':' );
       $out[$key] = $v;
     }
     
@@ -185,7 +188,7 @@ class MainDb
   // count something
   public static function total($table)
   {
-    $sql = 'SELECT COUNT(*) AS the_count FROM `%s`';
+    $sql = 'SELECT COUNT(*) AS the_count FROM %s';
     $res = self::fetch(sprintf($sql, $table));
     $res = current($res);
     return $res['the_count'];