[geeklog-cvs] geeklog: revised DB_save function

[geeklog-cvs at lists.geeklog.net]

changeset 7647:d61bef61c0f5
url:  http://project.geeklog.net/cgi-bin/hgwebdir.cgi/geeklog/rev/d61bef61c0f5
user: stan <yankees26an at gmail.com>
date: Sun Aug 16 19:45:20 2009 -0400
description:
revised DB_save function

diffstat:

 system/databases/pgsql.class.php |  76 ++++++++++++++++++++++++++-----------
 1 files changed, 53 insertions(+), 23 deletions(-)

diffs (107 lines):

diff -r 632cf4a1c40a -r d61bef61c0f5 system/databases/pgsql.class.php
--- a/system/databases/pgsql.class.php	Thu Aug 13 17:24:26 2009 -0400
+++ b/system/databases/pgsql.class.php	Sun Aug 16 19:45:20 2009 -0400
@@ -388,42 +388,71 @@
         if ($this->isVerbose()) {
             $this->_errorlog("\n*** Inside database->dbSave ***");
         }
-        $sql ='SELECT COUNT(*) FROM '.$table;
+        $sql = "SELECT COUNT(*) FROM $table";
         $result = $this->dbQuery($sql);
         $row = pg_fetch_row($result);
-        if($row[0]==0)
+        if($row[0]==0) //nothing in the table yet
         {
-           $sql='INSERT INTO '.$table.'('.$fields.') VALUES('.$values.')'; 
+            $sql="INSERT INTO $table($fields) VALUES($values)";  
         }
         else
         {
-            $fields = explode(',',$fields);              
-            $values = explode(',',$values);
-            if(count($fields)==count($values) && count($fields)>0)
+            unset($row); unset($result);
+            $fields_array = explode(',',$fields);
+            $values_array = explode(',',$values);
+            $row = array();              
+            $sql = 'SELECT pg_attribute.attname FROM pg_index, pg_class, pg_attribute 
+                    WHERE pg_class.oid = \''.$table.'\'::regclass AND 
+                    indrelid = pg_class.oid AND
+                    pg_attribute.attrelid = pg_class.oid AND 
+                    pg_attribute.attnum = any(pg_index.indkey)
+                    GROUP BY pg_attribute.attname, pg_attribute.attnum;';
+      
+            $result = $this->dbQuery($sql);
+            while($fetched = pg_fetch_row($result))
             {
-                $things='';
-                $counter = count($fields);
-                for($i=0;$i<$counter;$i++)
+             $row[] = $fetched;   
+            }
+            $counter=count($row);
+            if(!empty($row[0]))
+            {
+                $key = array_search($row[0][0],$fields_array);
+                if($key!==FALSE) //$fields contains the primary key already
                 {
-                    if(($i+1)==$counter)
+                 $sql = "DELETE FROM $table WHERE {$row[0][0]}='{$values_array[$key]}'";
+                 $result = $this->dbQuery($sql);
+                }
+                elseif($counter>1) //we will search for unique fields and see if they are getting duplicates
+                {
+                    $where_clause='';
+                    for($x=1;$x<$counter;$x++)
                     {
-                        $things .= $fields[$i].'='.$values[$i];
+                        $key = array_search($row[$x][0],$fields_array);
+                        if($key!==FALSE)
+                        {
+                            $values_array[$key] = str_replace('\'','',$values_array[$key]);
+                            $values_array[$key] = str_replace('"','',$values_array[$key]);
+                            if($x==$counter-1){$where_clause .="{$row[$x][0]} ='{$values_array[$key]}'"; }
+                            else{$where_clause .="{$row[$x][0]} ='{$values_array[$key]}' AND ";}
+                        }
                     }
-                    else
-                    {
-                        $things .= $fields[$i].'='.$values[$i].', ';   
-                    }  
+                    $sql="SELECT COUNT(*) FROM $table WHERE $where_clause";
+                    $result = $this->dbQuery($sql);
+                    $row2 = pg_fetch_row($result);
+                    if($row2[0]!=0){$sql = "DELETE FROM $table WHERE $where_clause'";}
+                    
+                    $sql="INSERT INTO $table ($fields) VALUES ($values)";  
                 }
-                $sql='UPDATE '.$table.' SET '.$things;
+                else
+                {
+                    COM_errorLog("There was a problem saving this DB_save call: $fields,$values"); 
+                }
             }
-            else
+            else //no keys to worry about
             {
-                if ($this->isVerbose()) {
-                $this->_errorlog("\n*** Field count doesnt match value count or empty ***");
-                }   
-            }  
+                $sql="INSERT INTO $table ($fields) VALUES ($values)";  
+            }
         }
-        //$sql = "REPLACE INTO $table ($fields) VALUES ($values)";
 
         $this->dbQuery($sql);
 
@@ -660,7 +689,8 @@
     {
         if(!empty($sequence))
         {
-            $result = pg_query('SELECT CURRVAL(\''.$sequence.'\'); ');    
+            $result = @pg_query('SELECT CURRVAL(\''.$sequence.'\'); ');
+            if($result==FALSE) {$result = @pg_query('SELECT NEXTVAL(\''.$sequence.'\'); ');}    
         }
         else
         {