[Phpgroupware-cvs] CVS: etemplate/inc class.so_sql.inc.php,1.3,1.4

[Ralf Becker <address@hidden>]

Update of /cvsroot/phpgroupware/etemplate/inc
In directory subversions:/tmp/cvs-serv14018

Modified Files:
        class.so_sql.inc.php 
Log Message:
change behavior on auto-increment columns, they are not written for new entries
some formatting and droped the aliases

Index: class.so_sql.inc.php
===================================================================
RCS file: /cvsroot/phpgroupware/etemplate/inc/class.so_sql.inc.php,v
retrieving revision 1.3
retrieving revision 1.4
diff -C2 -r1.3 -r1.4
*** class.so_sql.inc.php        2 Sep 2002 11:11:09 -0000       1.3
--- class.so_sql.inc.php        27 Mar 2003 17:21:58 -0000      1.4
***************
*** 65,70 ****
  
                if ($app && $table)
                        $this->setup_table($app,$table);
! 
                $this->init();
  
--- 65,71 ----
  
                if ($app && $table)
+               {
                        $this->setup_table($app,$table);
!               }
                $this->init();
  
***************
*** 98,115 ****
                $this->db_key_cols = $this->db_data_cols = $this->db_cols = 
array();
                $this->autoinc_id = '';
!               reset($table_def['fd']);
!               while (list($name,$def) = each($table_def['fd']))
                {
                        if (in_array($name,$table_def['pk']))
                                $this->db_key_cols[$name] = $name;
                        else
                                $this->db_data_cols[$name] = $name;
                        $this->db_cols[$name] = $name;
  
                        if ($def['type'] == 'auto')
                                $this->autoinc_id = $name;
! 
                        if (in_array($name,$table_def['uc']))
                                $this->db_uni_cols[$name] = $name;
                }
        }
--- 99,122 ----
                $this->db_key_cols = $this->db_data_cols = $this->db_cols = 
array();
                $this->autoinc_id = '';
!               foreach($table_def['fd'] as $name => $def)
                {
                        if (in_array($name,$table_def['pk']))
+                       {
                                $this->db_key_cols[$name] = $name;
+                       }
                        else
+                       {
                                $this->db_data_cols[$name] = $name;
+                       }
                        $this->db_cols[$name] = $name;
  
                        if ($def['type'] == 'auto')
+                       {
                                $this->autoinc_id = $name;
!                       }
                        if (in_array($name,$table_def['uc']))
+                       {
                                $this->db_uni_cols[$name] = $name;
+                       }
                }
        }
***************
*** 122,147 ****
        @param $new array in form col => new_value with values to set
        */
!       function so_sql_data_merge($new)
        {
                if (!is_array($new) || !count($new))
                        return;
! 
!               for (reset($this->db_cols); list($db_col,$col) = 
each($this->db_cols); )
                        if (isset($new[$col]))
                                $this->data[$col] = $new[$col];
! 
!               for (reset($this->non_db_cols); list($db_col,$col) = 
each($this->non_db_cols); )
                        if (isset($new[$col]))
                                $this->data[$col] = $new[$col];
!       }
! 
!       /*!
!       @function data_merge
!       @abstract just an convinient alias for so_sql_data_merge, might be 
reimplemented in derived class
!       @parms as for so_sql_data_merge
!       */
!       function data_merge($new)
!       {
!               $this->so_sql_data_merge($new);
        }
  
--- 129,152 ----
        @param $new array in form col => new_value with values to set
        */
!       function data_merge($new)
        {
                if (!is_array($new) || !count($new))
+               {
                        return;
!               }
!               foreach($this->db_cols as $db_col => $col)
!               {
                        if (isset($new[$col]))
+                       {
                                $this->data[$col] = $new[$col];
!                       }
!               }
!               foreach($this->non_db_cols as $db_col => $col)
!               {
                        if (isset($new[$col]))
+                       {
                                $this->data[$col] = $new[$col];
!                       }
!               }
        }
  
***************
*** 156,166 ****
        {
                if ($intern = !is_array($data))
                        $data = $this->data;
! 
                // do the necessare changes here
  
                if ($intern)
                        $this->data = $data;
! 
                return $data;
        }
--- 161,173 ----
        {
                if ($intern = !is_array($data))
+               {
                        $data = $this->data;
!               }
                // do the necessare changes here
  
                if ($intern)
+               {
                        $this->data = $data;
!               }
                return $data;
        }
***************
*** 176,196 ****
        {
                if ($intern = !is_array($data))
                        $data = $this->data;
! 
                // do the necessary changes here
  
                if ($intern)
                        $this->data = $data;
! 
                return $data;
        }
  
        /*!
!       @function so_sql_init
        @abstract initializes data with the content of key
        @param $keys array with keys in form internalName => value
        @result void
        */
!       function so_sql_init($keys=array())
        {
                $this->data = array();
--- 183,205 ----
        {
                if ($intern = !is_array($data))
+               {
                        $data = $this->data;
!               }
                // do the necessary changes here
  
                if ($intern)
+               {
                        $this->data = $data;
!               }
                return $data;
        }
  
        /*!
!       @function init
        @abstract initializes data with the content of key
        @param $keys array with keys in form internalName => value
        @result void
        */
!       function init($keys=array())
        {
                $this->data = array();
***************
*** 202,237 ****
  
        /*!
!       @function init
!       @abstract just an convinient alias for so_sql_init, might be 
reimplemented in derived class
!       @parms as for so_sql_init
!       */
!       function init($keys=array())
!       {
!               $this->so_sql_init($keys);
!       }
! 
!       /*!
!       @function so_sql_read
        @abstract reads row matched by key and puts all cols in the data array
        @param $keys array with keys in form internalName => value, may be a 
scalar value if only one key
        @result data array if row could be retrived else False and data = 
array()
        */
!       function so_sql_read($keys)
        {
                $this->init($keys);
  
                $this->data2db();
!               for(reset($this->db_key_cols); list($db_col,$col) = 
each($this->db_key_cols); )
                {
                        if ($this->data[$col] != '')
                                $query .= ($query ? ' AND 
':'')."$db_col='".addslashes($this->data[$col])."'";
                }
                if (!$query)    // no primary key in keys, lets try the 
data_cols for a unique key
!                       for(reset($this->db_data_cols); list($db_col,$col) = 
each($this->db_data_cols); )
                        {
                                if ($this->data[$col] != '')
                                        $query .= ($query ? ' AND 
':'')."$db_col='".addslashes($this->data[$col])."'";
                        }
! 
                if (!$query)    // keys has no cols
                {
--- 211,241 ----
  
        /*!
!       @function read
        @abstract reads row matched by key and puts all cols in the data array
        @param $keys array with keys in form internalName => value, may be a 
scalar value if only one key
        @result data array if row could be retrived else False and data = 
array()
        */
!       function read($keys)
        {
                $this->init($keys);
  
                $this->data2db();
!               foreach ($this->db_key_cols as $db_col => $col)
                {
                        if ($this->data[$col] != '')
+                       {
                                $query .= ($query ? ' AND 
':'')."$db_col='".addslashes($this->data[$col])."'";
+                       }
                }
                if (!$query)    // no primary key in keys, lets try the 
data_cols for a unique key
!               {
!                       foreach($this->db_data_cols as $db_col => $col)
                        {
                                if ($this->data[$col] != '')
+                               {
                                        $query .= ($query ? ' AND 
':'')."$db_col='".addslashes($this->data[$col])."'";
+                               }
                        }
!               }
                if (!$query)    // keys has no cols
                {
***************
*** 243,253 ****
  
                if ($this->debug)
!                       echo "<p>so_sql_read(): sql = '$sql': ";
! 
                if (!$this->db->next_record())
                {
                        if ($this->autoinc_id)
                                
unset($this->data[$this->db_key_cols[$this->autoinc_id]]);
! 
                        if ($this->debug) echo "nothing found !!!</p>\n";
  
--- 247,259 ----
  
                if ($this->debug)
!               {
!                       echo "<p>read(): sql = '$sql': ";
!               }
                if (!$this->db->next_record())
                {
                        if ($this->autoinc_id)
+                       {
                                
unset($this->data[$this->db_key_cols[$this->autoinc_id]]);
!                       }
                        if ($this->debug) echo "nothing found !!!</p>\n";
  
***************
*** 256,262 ****
                        return False;
                }
!               for (reset($this->db_cols); list($db_col,$col) = 
each($this->db_cols); )
                        $this->data[$col] = $this->db->f($db_col);
! 
                $this->db2data();
  
--- 262,269 ----
                        return False;
                }
!               foreach ($this->db_cols as $db_col => $col)
!               {
                        $this->data[$col] = $this->db->f($db_col);
!               }
                $this->db2data();
  
***************
*** 269,288 ****
  
        /*!
!       @function read
!       @abstract just an convinient alias for so_sql_read, might be 
reimplemented in derived class
!       @parms as for so_sql_read
!       */
!       function read($keys=array())
!       {
!               return $this->so_sql_read($keys);
!       }
! 
!       /*!
!       @function so_sql_save
        @abstracts saves the content of data to the db
        @param $keys if given $keys are copied to data before saveing => allows 
a save as
        @result 0 on success and errno != 0 else
        */
!       function so_sql_save($keys='')
        {
                $this->data_merge($keys);
--- 276,285 ----
  
        /*!
!       @function save
        @abstracts saves the content of data to the db
        @param $keys if given $keys are copied to data before saveing => allows 
a save as
        @result 0 on success and errno != 0 else
        */
!       function save($keys='')
        {
                $this->data_merge($keys);
***************
*** 295,330 ****
                }
                else
                        $new = 
!$this->data[$this->db_key_cols[$this->autoinc_id]];     // autoincrement idx 
is 0 => new
! 
                $this->data2db();
  
                if ($new)       // prepare an insert
                {
!                       for(reset($this->db_cols); list($db_col,$col) = 
each($this->db_cols); )
                        {
!                               $cols .= ($cols ? ',' : '') . $db_col;
!                               $vals .= ($vals ? ',' : '') . 
($this->data[$col] == '' ?
!                                       $this->empty_on_write : 
"'".addslashes($this->data[$col])."'");
                        }
                        $this->db->query($sql = "INSERT INTO $this->table_name 
($cols) VALUES ($vals)",__LINE__,__FILE__);
  
                        if ($this->autoinc_id)
                                
$this->data[$this->db_key_cols[$this->autoinc_id]] = 
$this->db->get_last_insert_id($this->table_name,$this->autoinc_id);
                }
                else //update existing row, preserv other cols not used here
                {
!                       for(reset($this->db_data_cols); list($db_col,$col) = 
each($this->db_data_cols); )
                                $vals .= ($vals ? ',':'') . 
"$db_col=".($this->data[$col] == '' ?
                                                $this->empty_on_write : 
"'".addslashes($this->data[$col])."'");
! 
                        $keys = '';
!                       for(reset($this->db_key_cols); list($db_col,$col) = 
each($this->db_key_cols); )
                                $keys .= ($keys ? ',':'') . 
"$db_col='".addslashes($this->data[$col])."'";
! 
                        $this->db->query($sql = "UPDATE $this->table_name SET 
$vals WHERE $keys",__LINE__,__FILE__);
                }
                if ($this->debug)
!                       echo "<p>so_sql_save(): sql = '$sql'</p>\n";
! 
                $this->db2data();
  
--- 292,336 ----
                }
                else
+               {
                        $new = 
!$this->data[$this->db_key_cols[$this->autoinc_id]];     // autoincrement idx 
is 0 => new
!               }
                $this->data2db();
  
                if ($new)       // prepare an insert
                {
!                       foreach($this->db_cols as $db_col => $col)
                        {
!                               if (!$this->autoinc_id || $db_col != 
$this->autoinc_id) // not write auto-inc-id
!                               {
!                                       $cols .= ($cols ? ',' : '') . $db_col;
!                                       $vals .= ($vals ? ',' : '') . 
($this->data[$col] == '' ?
!                                               $this->empty_on_write : 
"'".addslashes($this->data[$col])."'");
!                               }
                        }
                        $this->db->query($sql = "INSERT INTO $this->table_name 
($cols) VALUES ($vals)",__LINE__,__FILE__);
  
                        if ($this->autoinc_id)
+                       {
                                
$this->data[$this->db_key_cols[$this->autoinc_id]] = 
$this->db->get_last_insert_id($this->table_name,$this->autoinc_id);
+                       }
                }
                else //update existing row, preserv other cols not used here
                {
!                       foreach($this->db_data_cols as $db_col => $col)
!                       {
                                $vals .= ($vals ? ',':'') . 
"$db_col=".($this->data[$col] == '' ?
                                                $this->empty_on_write : 
"'".addslashes($this->data[$col])."'");
!                       }
                        $keys = '';
!                       foreach($this->db_key_cols as $db_col => $col)
!                       {
                                $keys .= ($keys ? ',':'') . 
"$db_col='".addslashes($this->data[$col])."'";
!                       }
                        $this->db->query($sql = "UPDATE $this->table_name SET 
$vals WHERE $keys",__LINE__,__FILE__);
                }
                if ($this->debug)
!               {
!                       echo "<p>save(): sql = '$sql'</p>\n";
!               }
                $this->db2data();
  
***************
*** 333,352 ****
  
        /*!
!       @function save
!       @abstract just an convinient alias for so_sql_save, might be 
reimplemented in derived class
!       @parms as for so_sql_save
!       */
!       function save($keys='')
!       {
!               return $this->so_sql_save($keys);
!       }
! 
!       /*!
!       @function so_sql_delete
        @abstract deletes row representing keys in internal data or the 
supplied $keys if != ''
        @param $keys if not '', array with col => value pairs to characterise 
the rows to delete
        @result affected rows, should be 1 if ok, 0 if an error
        */
!       function so_sql_delete($keys='')
        {
                if (!is_array($keys) || !count($keys))  // use internal data
--- 339,348 ----
  
        /*!
!       @function delete
        @abstract deletes row representing keys in internal data or the 
supplied $keys if != ''
        @param $keys if not '', array with col => value pairs to characterise 
the rows to delete
        @result affected rows, should be 1 if ok, 0 if an error
        */
!       function delete($keys='')
        {
                if (!is_array($keys) || !count($keys))  // use internal data
***************
*** 358,390 ****
                {
                        $data = $keys; $keys = array();
!                       for(reset($this->db_cols); list($db_col,$col) = 
each($this->db_cols); )
                                if (isset($data[$col]))
                                        $keys[$db_col] = $col;
                }
                $data = $this->data2db($data);
  
!               for (reset($keys); list($db_col,$col) = each($keys); )
                        $query .= ($query ? ' AND ' : '') . $db_col . "='" . 
addslashes($data[$col]) . "'";
! 
                $this->db->query($sql = "DELETE FROM $this->table_name WHERE 
$query",__LINE__,__FILE__);
  
                if ($this->debug)
!                       echo "<p>so_sql_delete(): sql = '$sql'</p>\n";
! 
                return $this->db->affected_rows();
        }
  
        /*!
!       @function delete
!       @abstract just an convinient alias for so_sql_delete, might be 
reimplemented in derived class
!       @parms as for so_sql_delete
!       */
!       function delete($keys='')
!       {
!               return $this->so_sql_delete($keys);
!       }
! 
!       /*!
!       @function so_sql_search
        @abstract searches db for rows matching searchcriteria
        @discussion '*' and '?' are replaced with sql-wildcards '%' and '_'
--- 354,382 ----
                {
                        $data = $keys; $keys = array();
!                       foreach($this->db_cols as $db_col => $col)
!                       {
                                if (isset($data[$col]))
+                               {
                                        $keys[$db_col] = $col;
+                               }
+                       }
                }
                $data = $this->data2db($data);
  
!               foreach($keys as $db_col => $col)
!               {
                        $query .= ($query ? ' AND ' : '') . $db_col . "='" . 
addslashes($data[$col]) . "'";
!               }
                $this->db->query($sql = "DELETE FROM $this->table_name WHERE 
$query",__LINE__,__FILE__);
  
                if ($this->debug)
!               {
!                       echo "<p>delete(): sql = '$sql'</p>\n";
!               }
                return $this->db->affected_rows();
        }
  
        /*!
!       @function search
        @abstract searches db for rows matching searchcriteria
        @discussion '*' and '?' are replaced with sql-wildcards '%' and '_'
***************
*** 396,410 ****
        @result array of matching rows (the row is an array of the cols) or 
False
        */
!       function 
so_sql_search($criteria,$only_keys=True,$order_by='',$extra_cols='',$wildcard='',$empty=False)
        {
                $criteria = $this->data2db($criteria);
  
!               for (reset($this->db_cols); list($db_col,$col) = 
each($this->db_cols); )
                {       //echo "testing col='$col', 
criteria[$col]='".$criteria[$col]."'<br>";
                        if (isset($criteria[$col]) && ($empty || 
$criteria[$col] != ''))
!                                       $query .= ($query ? ' AND ' : ' WHERE 
') . $db_col .
                                        ($wildcard || 
strstr($criteria[$col],'*') || strstr($criteria[$col],'?') ?
                                        " LIKE 
'$wildcard".strtr(str_replace('_','\\_',addslashes($criteria[$col])),'*?','%_')."$wildcard'"
 :
                                        "='".addslashes($criteria[$col])."'");
                }
                $this->db->query($sql = 'SELECT '.($only_keys ? 
implode(',',$this->db_key_cols) : '*').
--- 388,404 ----
        @result array of matching rows (the row is an array of the cols) or 
False
        */
!       function 
search($criteria,$only_keys=True,$order_by='',$extra_cols='',$wildcard='',$empty=False)
        {
                $criteria = $this->data2db($criteria);
  
!               foreach($this->db_cols as $db_col => $col)
                {       //echo "testing col='$col', 
criteria[$col]='".$criteria[$col]."'<br>";
                        if (isset($criteria[$col]) && ($empty || 
$criteria[$col] != ''))
!                       {
!                               $query .= ($query ? ' AND ' : ' WHERE ') . 
$db_col .
                                        ($wildcard || 
strstr($criteria[$col],'*') || strstr($criteria[$col],'?') ?
                                        " LIKE 
'$wildcard".strtr(str_replace('_','\\_',addslashes($criteria[$col])),'*?','%_')."$wildcard'"
 :
                                        "='".addslashes($criteria[$col])."'");
+                       }
                }
                $this->db->query($sql = 'SELECT '.($only_keys ? 
implode(',',$this->db_key_cols) : '*').
***************
*** 414,418 ****
                if ($this->debug)
                {
!                       echo 
"<p>so_sql_search(only_keys=$only_keys,order_by='$order_by',wildcard='$wildcard',empty=$empty)<br>sql
 = '$sql'</p>\n";
                        echo "<br>criteria = "; _debug_array($criteria);
                }
--- 408,412 ----
                if ($this->debug)
                {
!                       echo 
"<p>search(only_keys=$only_keys,order_by='$order_by',wildcard='$wildcard',empty=$empty)<br>sql
 = '$sql'</p>\n";
                        echo "<br>criteria = "; _debug_array($criteria);
                }
***************
*** 422,428 ****
                {
                        $row = array();
!                       for (reset($cols); list($db_col,$col) = each($cols); )
                                $row[$col] = $this->db->f($db_col);
! 
                        $arr[] = $this->db2data($row);
                }
--- 416,423 ----
                {
                        $row = array();
!                       foreach($cols as $db_col => $col)
!                       {
                                $row[$col] = $this->db->f($db_col);
!                       }
                        $arr[] = $this->db2data($row);
                }
***************
*** 431,446 ****
  
        /*!
!       @function search
!       @abstract just an convinient alias for so_sql_search, might be 
reimplemented in derived class
!       @parms as for so_sql_search
!       */
!       function 
search($criteria,$only_keys=True,$order_by='',$extra_cols='',$wildcard='',$empty=False)
!       {
!               return 
$this->so_sql_search($criteria,$only_keys,$order_by,$extra_cols,$wildcard,$empty);
!       }
! 
!       /*!
!       @function so_sql_not_unique
!       @syntax so_sql_not_unique( $data='' )
        @author ralfbecker
        @abstract Check if values for unique keys are unique
--- 426,431 ----
  
        /*!
!       @function not_unique
!       @syntax not_unique( $data='' )
        @author ralfbecker
        @abstract Check if values for unique keys are unique
***************
*** 448,465 ****
        @result 0: all keys are unique, 1: first key not unique, 2: ...
        */
!       function so_sql_not_unique($data='')
        {
                if (!is_array($data))
                        $data = $this->data;
! 
!               reset($this->db_uni_cols);
!               for ($n=1; list($db_col,$col) = each($this->db_uni_cols); ++$n)
                {
                        if (list($other) = $this->search(array($db_col => 
$data[$col])))
                        {
!                               reset($this->db_key_cols);
!                               while (list($db_key_col,$key_col) = 
each($this->db_key_cols))
                                {
!                                       if ($data[$key_col] != 
$other[$key_col]) {
                                                if ($this->debug)
                                                {
--- 433,451 ----
        @result 0: all keys are unique, 1: first key not unique, 2: ...
        */
!       function not_unique($data='')
        {
                if (!is_array($data))
+               {
                        $data = $this->data;
!               }
!               $n = 1;
!               foreach($this->db_uni_cols as $db_col => $col)
                {
                        if (list($other) = $this->search(array($db_col => 
$data[$col])))
                        {
!                               foreach($this->db_key_cols as $db_key_col => 
$key_col)
                                {
!                                       if ($data[$key_col] != 
$other[$key_col]) 
!                                       {
                                                if ($this->debug)
                                                {
***************
*** 470,487 ****
                                }
                        }
                }
                return 0;
        }
! 
!       /*!
!       @function not_unique
!       @syntax not_unique( $data='' )
!       @author ralfbecker
!       @abstract just an convinient alias for so_sql_search, might be 
reimplemented in derived class
!       @parms as for so_sql_not_unique
!       */
!       function not_unique($data='')
!       {
!               return $this->so_sql_not_unique($data);
!       }
! };
\ No newline at end of file
--- 456,462 ----
                                }
                        }
+                       ++$n;
                }
                return 0;
        }
! };