[Sommaire] [Imprimer]

   1  <?php
   2      /**************************************************************************\
   3      * eGroupWare - generalized SQL Storage Object                              *
   4      * http://www.egroupware.org                                                *
   5      * Written by Ralf Becker <RalfBecker@outdoor-training.de>                  *
   6      * --------------------------------------------                             *
   7      *  This program is free software; you can redistribute it and/or modify it *
   8      *  under the terms of the GNU General Public License as published by the   *
   9      *  Free Software Foundation; either version 2 of the License, or (at your  *
  10      *  option) any later version.                                              *
  11      \**************************************************************************/
  12  
  13      /* $Id: class.so_sql.inc.php 21495 2006-04-30 16:45:38Z ralfbecker $ */
  14  
  15  /**
  16   * generalized SQL Storage Object
  17   *
  18   * the class can be used in following ways:
  19   * 1) by calling the constructor with an app and table-name or
  20   * 2) by setting the following documented class-vars in a class derifed from this one
  21   * Of cause can you derife the class and call the constructor with params.
  22   *
  23   * @package etemplate
  24   * @subpackage api
  25   * @author RalfBecker-AT-outdoor-training.de
  26   * @license http://opensource.org/licenses/gpl-license.php GPL - GNU General Public License
  27   */
  28  class so_sql
  29  {
  30       /** 
  31        * @var string $table_name need to be set in the derived class to the db-table-name 
  32        */
  33      var $table_name;
  34       /**
  35        * @var string $autoinc_id db-col-name of autoincrement id or ''
  36        */
  37      var $autoinc_id = '';
  38      /**
  39       * @var array $non_db_cols all cols in data which are not (direct)in the db, for data_merge
  40       */
  41      var $non_db_cols = array();
  42      /**
  43       * @var int $debug=0 4 turns on the so_sql debug-messages
  44       */
  45      var $debug = 0;
  46      /**
  47       * @var string $empty_on_write string to be written to db if a col-value is '', eg. "''" or 'NULL' (default)
  48       */
  49      var $empty_on_write = 'NULL';
  50      /**
  51       * @var int/boolean $total total number of entries of last search with start != false
  52       */
  53      var $total = false;
  54      /**
  55       * @var db-object $db privat instance of the db-object
  56       */
  57      var $db;
  58      /**
  59       * @var array $db_uni_cols unique keys/index, set by derived class or via so_sql($app,$table)
  60       */
  61      var $db_uni_cols = array();
  62      /**
  63       * @var array $db_key_cols db-col-name / internal-name pairs, set by derived calls or via so_sql($app,$table) 
  64       */
  65      var $db_key_cols = array();
  66      /**
  67       * @var array $db_data_cols db-col-name / internal-name pairs, set by derived calls or via so_sql($app,$table) 
  68       */
  69      var $db_data_cols = array();
  70      /**
  71       * @var array $db_cols all columns = $db_key_cols + $db_data_cols, set in the constructor
  72       */
  73      var $db_cols = array();
  74      /**
  75       * @var array $this->table_def eGW table definition
  76       */
  77      var $table_def = array();
  78      /**
  79       * @var array $data holds the content of all columns
  80       */
  81      var $data = array();
  82      /**
  83       * @deprecated  a SO class dont need to and should NOT export functions (make them callable via menuaction)
  84       * @var array $public_functions
  85       */
  86      var $public_functions = array();
  87  
  88      /**
  89       * constructor of the class
  90       *
  91       * NEED to be called from the constructor of the derived class !!!
  92       *
  93       * @param string $app should be set if table-defs to be read from <app>/setup/tables_current.inc.php
  94       * @param string $table should be set if table-defs to be read from <app>/setup/tables_current.inc.php
  95       * @param object/db $db database object, if not the one in $GLOBALS['egw']->db should be used, eg. for an other database
  96       * @param string $colum_prefix='' column prefix to automatic remove from the column-name, if the column name starts with it
  97       */
  98  	function so_sql($app='',$table='',$db=null,$column_prefix='')
  99      {
 100          $this->db = is_object($db) ? clone($db) : clone($GLOBALS['egw']->db);
 101          $this->db_cols = $this->db_key_cols + $this->db_data_cols;
 102  
 103          if ($app)
 104          {
 105              $this->db->set_app($app);
 106  
 107              if ($table) $this->setup_table($app,$table,$column_prefix);
 108          }
 109          $this->init();
 110  
 111          if ((int) $this->debug >= 4)
 112          {
 113              echo "<p>so_sql('$app','$table')</p>\n";
 114              _debug_array($this);
 115          }
 116      }
 117  
 118      /**
 119       * sets up the class for an app and table (by using the table-definition of $app/setup/tables_current.inc.php
 120       *
 121       * If you need a more complex conversation then just removing the column_prefix, you have to do so in a derifed class !!!
 122       *
 123       * @param string $app app-name $table belongs too
 124       * @param string $table table-name
 125       * @param string $colum_prefix='' column prefix to automatic remove from the column-name, if the column name starts with it
 126       */
 127  	function setup_table($app,$table,$colum_prefix='')
 128      {
 129          $this->table_name = $table;
 130          $this->table_def = $this->db->get_table_definitions($app,$table);
 131          if (!$this->table_def || !is_array($this->table_def['fd']))
 132          {
 133              echo "<p>so_sql::setup_table('$app','$table'): No table definitions found !!!<br>\n".function_backtrace()."</p>\n";
 134          }
 135          $this->db_key_cols = $this->db_data_cols = $this->db_cols = array();
 136          $this->autoinc_id = '';
 137          $len_prefix = strlen($colum_prefix);
 138          foreach($this->table_def['fd'] as $col => $def)
 139          {
 140              $name = $col;
 141              if ($len_prefix && substr($name,0,$len_prefix) == $colum_prefix)
 142              {
 143                  $name = substr($col,$len_prefix);
 144              }
 145              if (in_array($col,$this->table_def['pk']))
 146              {
 147                  $this->db_key_cols[$col] = $name;
 148              }
 149              else
 150              {
 151                  $this->db_data_cols[$col] = $name;
 152              }
 153              $this->db_cols[$col] = $name;
 154  
 155              if ($def['type'] == 'auto')
 156              {
 157                  $this->autoinc_id = $col;
 158              }
 159              if (in_array($name,$this->table_def['uc']))
 160              {
 161                  $this->db_uni_cols[$col] = $name;
 162              }
 163          }
 164      }
 165  
 166      /**
 167       * merges in new values from the given new data-array
 168       *
 169       * @param $new array in form col => new_value with values to set
 170       */
 171  	function data_merge($new)
 172      {
 173          if ((int) $this->debug >= 4) echo "<p>so_sql::data_merge(".print_r($new,true).")</p>\n";
 174  
 175          if (!is_array($new) || !count($new))
 176          {
 177              return;
 178          }
 179          foreach($this->db_cols as $db_col => $col)
 180          {
 181              if (isset($new[$col]))
 182              {
 183                  $this->data[$col] = $new[$col];
 184              }
 185          }
 186          foreach($this->non_db_cols as $db_col => $col)
 187          {
 188              if (isset($new[$col]))
 189              {
 190                  $this->data[$col] = $new[$col];
 191              }
 192          }
 193          if ((int) $this->debug >= 4) _debug_array($this->data);
 194      }
 195  
 196      /**
 197       * changes the data from the db-format to your work-format
 198       *
 199       * it gets called everytime when data is read from the db
 200       * This function needs to be reimplemented in the derived class
 201       *
 202       * @param array $data if given works on that array and returns result, else works on internal data-array
 203       */
 204  	function db2data($data=null)
 205      {
 206          if (!is_array($data))
 207          {
 208              $data = &$this->data;
 209          }
 210          // do the necessare changes here
 211  
 212          return $data;
 213      }
 214  
 215      /**
 216       * changes the data from your work-format to the db-format
 217       *
 218       * It gets called everytime when data gets writen into db or on keys for db-searches
 219       * this needs to be reimplemented in the derived class
 220       *
 221       * @param array $data if given works on that array and returns result, else works on internal data-array
 222       */
 223  	function data2db($data=null)
 224      {
 225          if ($intern = !is_array($data))
 226          {
 227              $data = &$this->data;
 228          }
 229          // do the necessary changes here
 230  
 231          return $data;
 232      }
 233  
 234      /**
 235       * initializes data with the content of key
 236       *
 237       * @param array $keys array with keys in form internalName => value
 238       */
 239  	function init($keys=array())
 240      {
 241          $this->data = array();
 242  
 243          $this->db2data();
 244  
 245          $this->data_merge($keys);
 246      }
 247  
 248      /**
 249       * reads row matched by key and puts all cols in the data array
 250       *
 251       * @param array $keys array with keys in form internalName => value, may be a scalar value if only one key
 252       * @param string/array $extra_cols string or array of strings to be added to the SELECT, eg. "count(*) as num"
 253       * @param string $join='' sql to do a join, added as is after the table-name, eg. ", table2 WHERE x=y" or 
 254       * @return array/boolean data if row could be retrived else False
 255      */
 256  	function read($keys,$extra_cols='',$join='')
 257      {
 258          if (!is_array($keys))
 259          {
 260              $pk = array_values($this->db_key_cols);
 261              if ($pk) $keys = array($pk[0] => $keys);
 262          }    
 263          
 264          $this->init($keys);
 265          $this->data2db();
 266  
 267          $query = false;
 268          foreach ($this->db_key_cols as $db_col => $col)
 269          {
 270              if ($this->data[$col] != '')
 271              {
 272                  $query[$db_col] = $this->data[$col];
 273              }
 274          }
 275          if (!$query)    // no primary key in keys, lets try the data_cols for a unique key
 276          {
 277              foreach($this->db_uni_cols as $db_col => $col)
 278              {
 279                  if ($this->data[$col] != '')
 280                  {
 281                      $query[$db_col] = $this->data[$col];
 282                  }
 283              }
 284          }
 285          if (!$query)    // keys has no cols
 286          {
 287              $this->db2data();
 288  
 289              return False;
 290          }
 291          $this->db->select($this->table_name,'*'.($extra_cols?','.(is_array($extra_cols)?implode(',',$extra_cols):$extra_cols):''),
 292              $query,__LINE__,__FILE__,False,'',False,0,$join);
 293  
 294          if (!($row = $this->db->row(true)))
 295          {
 296              if ($this->autoinc_id)
 297              {
 298                  unset($this->data[$this->db_key_cols[$this->autoinc_id]]);
 299              }
 300              if ((int) $this->debug >= 4) echo "nothing found !!!</p>\n";
 301  
 302              $this->db2data();
 303  
 304              return False;
 305          }
 306          $cols = $this->db_cols;
 307          if ($extra_cols)    // extra columns to report
 308          {
 309              foreach(is_array($extra_cols) ? $extra_cols : array($extra_cols) as $col)
 310              {
 311                  if (stristr($col,'as')) $col = preg_replace('/^.*as *([a-z0-9_]+) *$/i','\\1',$col);
 312                  $cols[$col] = $col;
 313              }
 314          }
 315          foreach ($cols as $db_col => $col)
 316          {
 317              $this->data[$col] = $row[$db_col];
 318          }
 319          $this->db2data();
 320  
 321          if ((int) $this->debug >= 4)
 322          {
 323              echo "data =\n"; _debug_array($this->data);
 324          }
 325          return $this->data;
 326      }
 327  
 328      /**
 329       * saves the content of data to the db
 330       *
 331       * @param array $keys if given $keys are copied to data before saveing => allows a save as
 332       * @return int 0 on success and errno != 0 else
 333       */
 334  	function save($keys=null)
 335      {
 336          if (is_array($keys) && count($keys)) $this->data_merge($keys);
 337  
 338          $this->data2db();
 339  
 340          if ((int) $this->debug >= 4) { echo "so_sql::save(".print_r($keys,true).") autoinc_id='$this->autoinc_id', data="; _debug_array($this->data); }
 341  
 342          if ($this->autoinc_id && !$this->data[$this->db_key_cols[$this->autoinc_id]])    // insert with auto id
 343          {
 344              foreach($this->db_cols as $db_col => $col)
 345              {
 346                  if (!$this->autoinc_id || $db_col != $this->autoinc_id)    // not write auto-inc-id
 347                  {
 348                      if (!isset($this->data[$col]) &&     // handling of unset columns in $this->data
 349                          (isset($this->table_def['fd'][$db_col]['default']) ||    // we have a default value
 350                           !isset($this->table_def['fd'][$db_col]['nullable']) || $this->table_def['fd'][$db_col]['nullable']))    // column is nullable
 351                      {
 352                          continue;    // no need to write that (unset) column
 353                      }
 354                      $data[$db_col] = (string) $this->data[$col] === '' && $this->empty_on_write == 'NULL' ? null : $this->data[$col];
 355                  }
 356              }
 357              $this->db->insert($this->table_name,$data,false,__LINE__,__FILE__);
 358  
 359              if ($this->autoinc_id)
 360              {
 361                  $this->data[$this->db_key_cols[$this->autoinc_id]] = $this->db->get_last_insert_id($this->table_name,$this->autoinc_id);
 362              }
 363          }
 364          else // insert in table without auto id or update of existing row, dont write colums unset in $this->data
 365          {
 366              foreach($this->db_data_cols as $db_col => $col)
 367              {
 368                  if (!isset($this->data[$col]) &&    // handling of unset columns in $this->data
 369                      ($this->autoinc_id ||            // update of table with auto id or
 370                       isset($this->table_def['fd'][$db_col]['default']) ||    // we have a default value or
 371                       !isset($this->table_def['fd'][$db_col]['nullable']) || $this->table_def['fd'][$db_col]['nullable']))    // column is nullable
 372                  {
 373                      continue;    // no need to write that (unset) column
 374                  }
 375                  $data[$db_col] = (string) $this->data[$col] === '' && $this->empty_on_write == 'NULL' ? null : $this->data[$col];
 376              }
 377              $keys = '';
 378              foreach($this->db_key_cols as $db_col => $col)
 379              {
 380                  $keys[$db_col] = $this->data[$col];
 381              }
 382              if (!$data && !$this->autoinc_id)    // happens if all columns are in the primary key
 383              {
 384                  $data = $keys;
 385                  $keys = False;
 386              }
 387              if (!$this->autoinc_id)    // always try an insert if we have no autoinc_id, as we dont know if the data exists
 388              {
 389                  $this->db->insert($this->table_name,$data,$keys,__LINE__,__FILE__);
 390              }
 391              else
 392              {
 393                  $this->db->update($this->table_name,$data,$keys,__LINE__,__FILE__);
 394              }
 395          }
 396          $this->db2data();
 397  
 398          return $this->db->Errno;
 399      }
 400  
 401      /**
 402       * deletes row representing keys in internal data or the supplied $keys if != null
 403       *
 404       * @param array $keys if given array with col => value pairs to characterise the rows to delete
 405       * @return int affected rows, should be 1 if ok, 0 if an error
 406       */
 407  	function delete($keys=null)
 408      {
 409          if (!is_array($keys) || !count($keys))    // use internal data
 410          {
 411              $data = $this->data;
 412              $keys = $this->db_key_cols;
 413          }
 414          else    // data and keys are supplied in $keys
 415          {
 416              $data = $keys; $keys = array();
 417              foreach($this->db_cols as $db_col => $col)
 418              {
 419                  if (isset($data[$col]))
 420                  {
 421                      $keys[$db_col] = $col;
 422                  }
 423              }
 424          }
 425          $data = $this->data2db($data);
 426  
 427          foreach($keys as $db_col => $col)
 428          {
 429              $query[$db_col] = $data[$col];
 430          }
 431          $this->db->delete($this->table_name,$query,__LINE__,__FILE__);
 432  
 433          return $this->db->affected_rows();
 434      }
 435  
 436      /**
 437       * searches db for rows matching searchcriteria
 438       *
 439       * '*' and '?' are replaced with sql-wildcards '%' and '_'
 440       *
 441       * For a union-query you call search for each query with $start=='UNION' and one more with only $order_by and $start set to run the union-query.
 442       *
 443       * @param array/string $criteria array of key and data cols, OR a SQL query (content for WHERE), fully quoted (!)
 444       * @param boolean/string $only_keys=true True returns only keys, False returns all cols. comma seperated list of keys to return
 445       * @param string $order_by='' fieldnames + {ASC|DESC} separated by colons ',', can also contain a GROUP BY (if it contains ORDER BY)
 446       * @param string/array $extra_cols='' string or array of strings to be added to the SELECT, eg. "count(*) as num"
 447       * @param string $wildcard='' appended befor and after each criteria
 448       * @param boolean $empty=false False=empty criteria are ignored in query, True=empty have to be empty in row
 449       * @param string $op='AND' defaults to 'AND', can be set to 'OR' too, then criteria's are OR'ed together
 450       * @param mixed $start=false if != false, return only maxmatch rows begining with start, or array($start,$num), or 'UNION' for a part of a union query
 451       * @param array $filter=null if set (!=null) col-data pairs, to be and-ed (!) into the query without wildcards
 452       * @param string $join='' sql to do a join, added as is after the table-name, eg. ", table2 WHERE x=y" or 
 453       *    "LEFT JOIN table2 ON (x=y)", Note: there's no quoting done on $join!
 454       * @param boolean $need_full_no_count=false If true an unlimited query is run to determine the total number of rows, default false
 455       * @return boolean/array of matching rows (the row is an array of the cols) or False
 456       */
 457      function &search($criteria,$only_keys=True,$order_by='',$extra_cols='',$wildcard='',$empty=False,$op='AND',$start=false,$filter=null,$join='',$need_full_no_count=false)
 458      {
 459          if ((int) $this->debug >= 4) echo "<p>so_sql::search(".print_r($criteria,true).",'$only_keys','$order_by','$extra_cols','$wildcard','$empty','$op','$start',".print_r($filter,true).",'$join')</p>\n";
 460  
 461          if (!is_array($criteria))
 462          {
 463              $query = $criteria;
 464          }
 465          else
 466          {
 467              $criteria = $this->data2db($criteria);
 468              foreach($criteria as $col => $val)
 469              {
 470                  if (is_int($col))
 471                  {
 472                      $query[] = $val;
 473                  }
 474                  elseif ($empty || $val != '')
 475                  {
 476                      if (!($db_col = array_search($col,$this->db_cols)))
 477                      {
 478                          $db_col = $col;
 479                      }
 480                      if ($wildcard || strstr($criteria[$col],'*') || strstr($criteria[$col],'?') || $criteria[$col]{0} == '!')
 481                      {
 482                          $cmp_op = ' LIKE ';
 483                          if ($criteria[$col]{0} == '!')
 484                          {
 485                              $cmp_op = ' NOT LIKE ';
 486                              $criteria[$col] = substr($criteria[$col],1);
 487                          }
 488                          $query[] = $db_col.$cmp_op.$this->db->quote($wildcard.str_replace(array('%','_','*','?'),array('\\%','\\_','%','_'),$criteria[$col]).$wildcard);
 489                      }
 490                      elseif (strstr($db_col,'.'))    // we have a table-name specified
 491                      {
 492                          list($table,$only_col) = explode('.',$db_col);
 493                          
 494                          $table_def = $this->db->get_table_definitions(false,$table);
 495                          
 496                          $query[] = $db_col.(is_array($val) ? ' IN ' : '=').$this->db->quote($val,$table_def['fd'][$only_col]);
 497                      }
 498                      else
 499                      {
 500                          $query[$db_col] = $criteria[$col];
 501                      }
 502                  }
 503              }
 504              if (is_array($query) && $op != 'AND') $query = $this->db->column_data_implode(' '.$op.' ',$query);
 505          }
 506          if (is_array($filter))
 507          {
 508              $db_filter = array();
 509              $data2db_filter = $this->data2db($filter);
 510              if (!is_array($data2db_filter)) {
 511                  echo function_backtrace()."<br/>\n";
 512                  echo "filter=";_debug_array($filter);
 513                  echo "data2db(filter)=";_debug_array($data2db_filter);
 514              }    
 515              foreach($data2db_filter as $col => $val)
 516              {
 517                  if ($val !== '')
 518                  {
 519                      // check if a db-internal name conversation necessary
 520                      if (!is_numeric($col) && ($c = array_search($col,$this->db_cols))) 
 521                      {
 522                          $col = $c;
 523                      }
 524                      $db_filter[$col] = $val;
 525                  }
 526              }
 527              if ($query) 
 528              {
 529                  if ($op != 'AND')
 530                  {
 531                      $db_filter[] = '('.$this->db->column_data_implode(' '.$op.' ',$query).')';
 532                  }
 533                  else 
 534                  {
 535                      $db_filter = array_merge($db_filter,$query);
 536                  }
 537              }
 538              $query = $db_filter;
 539          }
 540          if ((int) $this->debug >= 4)
 541          {
 542              echo "<p>so_sql::search(,only_keys=$only_keys,order_by='$order_by',wildcard='$wildcard',empty=$empty,$op,start='$start',".print_r($filter,true).") query=".print_r($query,true).", total='$this->total'</p>\n";
 543              echo "<br>criteria = "; _debug_array($criteria);
 544          }
 545          $colums = ($only_keys === true ? implode(',',$this->db_key_cols) : (!$only_keys ? '*' : $only_keys)).
 546              ($extra_cols ? ','.(is_array($extra_cols) ? implode(',',$extra_cols) : $extra_cols) : '');
 547  
 548          $num_rows = 0;    // as spec. in max_matches in the user-prefs
 549          if (is_array($start)) list($start,$num_rows) = $start;
 550          
 551          if ($order_by && !stristr($order_by,'ORDER BY') && !stristr($order_by,'GROUP BY'))
 552          {
 553              $order_by = 'ORDER BY '.$order_by;
 554          }
 555          static $union = array();
 556          static $union_cols = array();
 557          if ($start === 'UNION' || $union)
 558          {
 559              if ($start === 'UNION')
 560              {
 561                  $union[] = array(
 562                      'table'  => $this->table_name,
 563                      'cols'   => $colums,
 564                      'where'  => $query,
 565                      'append' => $order_by,
 566                      'join'   => $join,
 567                  );
 568                  if (!$union_cols)    // union used the colum-names of the first query
 569                  {
 570                      $union_cols = $this->_get_columns($only_keys,$extra_cols);
 571                  }
 572                  return true;    // waiting for further calls, before running the union-query
 573              }
 574              // running the union query now
 575              if ($start !== false && $num_rows != 1)    // need to get the total too, saved in $this->total
 576              {
 577                  if ($this->db->Type == 'mysql' && $this->db->ServerInfo['version'] >= 4.0)
 578                  {
 579                      $union[0]['cols'] = ($mysql_calc_rows = 'SQL_CALC_FOUND_ROWS ').$union[0]['cols'];
 580                  }
 581                  else    // cant do a count, have to run the query without limit
 582                  {
 583                      $this->db->union($union,__LINE__,__FILE__);
 584                      $this->total = $this->db->num_rows();
 585                  }
 586              }
 587              $this->db->union($union,__LINE__,__FILE__,$order_by,$start,$num_rows);
 588  
 589              $cols = $union_cols;
 590              $union = $union_cols = array();
 591          }
 592          else    // no UNION
 593          {
 594              if ($start !== false && $num_rows != 1)    // need to get the total too, saved in $this->total
 595              {
 596                  if ($this->db->Type == 'mysql' && $this->db->ServerInfo['version'] >= 4.0)
 597                  {
 598                      $mysql_calc_rows = 'SQL_CALC_FOUND_ROWS ';
 599                  }
 600                  elseif (!$need_full_no_count && (!$join || stristr($join,'LEFT JOIN')))
 601                  {
 602                      $this->db->select($this->table_name,'COUNT(*)',$query,__LINE__,__FILE__);
 603                      $this->total = $this->db->next_record() ? (int) $this->db->f(0) : false;
 604                  }
 605                  else    // cant do a count, have to run the query without limit
 606                  {
 607                      $this->db->select($this->table_name,$colums,$query,__LINE__,__FILE__,false,$order_by,false,0,$join);
 608                      $this->total = $this->db->num_rows();
 609                  }
 610              }
 611              $this->db->select($this->table_name,$mysql_calc_rows.$colums,$query,__LINE__,__FILE__,
 612                  $start,$order_by,false,$num_rows,$join);
 613  
 614              $cols = $this->_get_columns($only_keys,$extra_cols);
 615          }
 616          if ($mysql_calc_rows)
 617          {
 618              $this->total = $this->db->Link_ID->GetOne('SELECT FOUND_ROWS()');
 619          }
 620          $arr = array();
 621          for ($n = 0; ($row = $this->db->row(true)); ++$n)
 622          {
 623              $data = array();
 624              foreach($cols as $db_col => $col)
 625              {
 626                  $data[$col] = $row[$db_col];
 627              }
 628              $arr[] = $this->db2data($data);
 629          }
 630          return $n ? $arr : False;
 631      }
 632  
 633      /**
 634       * extract the requested columns from $only_keys and $extra_cols param of a search
 635       *
 636       * @internal 
 637       * @param boolean/string $only_keys=true True returns only keys, False returns all cols. comma seperated list of keys to return
 638       * @param string/array $extra_cols='' string or array of strings to be added to the SELECT, eg. "count(*) as num"
 639       * @return array with columns as db-name => internal-name pairs
 640       */     
 641  	function _get_columns($only_keys,$extra_cols)
 642      {
 643          if ($only_keys === true)    // only primary key
 644          {
 645              $cols = $this->db_key_cols;
 646          }
 647          else
 648          {
 649              $cols = array();
 650              foreach(explode(',',str_replace(array('DISTINCT ','distinct '),'',$only_keys)) as $col)
 651              {
 652                  if (!$col || $col == '*' || $col == $this->table_name.'.*')    // all columns
 653                  {
 654                      $cols = array_merge($cols,$this->db_cols);
 655                  }
 656                  else    // only the specified columns
 657                  {
 658                      if (stristr($col,'as')) $col = preg_replace('/^.*as +([a-z0-9_]+) *$/i','\\1',$col);
 659                      $cols[$col] = $col;
 660                  }
 661              }
 662          }
 663          if ($extra_cols)    // extra columns to report
 664          {
 665              foreach(is_array($extra_cols) ? $extra_cols : explode(',',$extra_cols) as $col)
 666              {
 667                  if (stristr($col,'as ')) $col = preg_replace('/^.*as +([a-z0-9_]+) *$/i','\\1',$col);
 668                  $cols[$col] = $col;
 669              }
 670          }
 671          return $cols;
 672      }
 673  
 674      /**
 675       * query rows for the nextmatch widget
 676       *
 677       * @param array $query with keys 'start', 'search', 'order', 'sort', 'col_filter'
 678       *    For other keys like 'filter', 'cat_id' you have to reimplement this method in a derived class.
 679       * @param array &$rows returned rows/competitions
 680       * @param array &$readonlys eg. to disable buttons based on acl, not use here, maybe in a derived class
 681       * @param string $join='' sql to do a join, added as is after the table-name, eg. ", table2 WHERE x=y" or 
 682       *    "LEFT JOIN table2 ON (x=y)", Note: there's no quoting done on $join!
 683       * @param boolean $need_full_no_count=false If true an unlimited query is run to determine the total number of rows, default false
 684       * @return int total number of rows
 685       */
 686  	function get_rows($query,&$rows,&$readonlys,$join='',$need_full_no_count=false)
 687      {
 688          if ((int) $this->debug >= 4)
 689          {
 690              echo "<p>so_sql::get_rows(".print_r($query,true).",,)</p>\n";
 691          }
 692          $criteria = array();
 693          if ($query['search'])
 694          {
 695              foreach($this->db_cols as $col)    // we search all cols
 696              {
 697                  $criteria[$col] = $query['search'];
 698              }
 699          }
 700          $rows = (array) $this->search($criteria,false,$query['order']?$query['order'].' '.$query['sort']:'',
 701              '','%',false,'OR',(int)$query['start'],$query['col_filter'],$join,$need_full_no_count);
 702  
 703          return $this->total;
 704      }
 705          
 706      /**
 707       * Check if values for unique keys are unique
 708       *
 709       * @param array $data data-set to check, defaults to $this->data
 710       * @return int 0: all keys are unique, 1: first key not unique, 2: ...
 711       */
 712  	function not_unique($data=null)
 713      {
 714          if (!is_array($data))
 715          {
 716              $data = $this->data;
 717          }
 718          $n = 1;
 719          foreach($this->db_uni_cols as $db_col => $col)
 720          {
 721              if (list($other) = $this->search(array($db_col => $data[$col]),false,'','','',false,'AND',false,null,''))
 722              {
 723                  foreach($this->db_key_cols as $db_key_col => $key_col)
 724                  {
 725                      if ($data[$key_col] != $other[$key_col])
 726                      {
 727                          if ((int) $this->debug >= 4)
 728                          {
 729                              echo "<p>not_unique in '$col' as for '$key_col': '$data[$key_col]}' != '$other[$key_col]}'</p>\n";
 730                          }
 731                          return $n;    // different entry => $n not unique
 732                      }
 733                  }
 734              }
 735              ++$n;
 736          }
 737          return 0;
 738      }
 739      
 740      /**
 741       * Query DB for a list / array with one colum as key and an other one(s) as value, eg. id => title pairs
 742       *
 743       * We do some caching as these kind of function is usualy called multiple times, eg. for option-lists.
 744       *
 745       * @param string $value_col array of column-names for the values of the array, can also be an expression aliased with AS,
 746       *    if more then one column given, an array with keys identical to the given ones is returned and not just the value of the column
 747       * @param string $key_col='' column-name for the keys, default '' = same as (first) $value_col: returns a distinct list
 748       * @param array $filter=array() to filter the entries
 749       * @param string $order='' order, default '' = same as (first) $value_col
 750       * @return array with key_col => value_col pairs or array if more then one value_col given (keys as in value_col)
 751       */
 752  	function query_list($value_col,$key_col='',$filter=array(),$order='')
 753      {
 754          static $cache = array();
 755          
 756          $cache_key = serialize($value_col).'-'.$key_col.'-'.serialize($filter).'-'.$order;
 757          
 758          if (isset($cache[$cache_key]))
 759          {
 760              return $cache[$cache_key];
 761          }
 762          if (!is_array($value_col)) $value_col = array($value_col);
 763          
 764          $cols = array();
 765          foreach(is_array($value_col) ? $value_col : array($value_col) as $key => $col)
 766          {
 767              $cols[$key] = preg_match('/AS ([a-z_0-9]+)$/i',$col,$matches) ? $matches[1] : $col;
 768          }            
 769          if (!$order) $order = current($cols);
 770  
 771          if (($search =& $this->search(array(),($key_col ? $key_col.',' : 'DISTINCT ').implode(',',$value_col),$order,'','',false,'AND',false,$filter)))
 772          {
 773              if (preg_match('/AS ([a-z_0-9]+)$/i',$key_col,$matches))
 774              {
 775                  $key_col = $matches[1];
 776              }
 777              elseif (!$key_col)
 778              {
 779                  $key_col = current($cols);
 780              }
 781              foreach($search as $row)
 782              {
 783                  if (count($cols) > 1)
 784                  {
 785                      $data = array();
 786                      foreach($cols as $key => $col)
 787                      {
 788                          $data[$key] = $row[$col];
 789                      }
 790                  }
 791                  else
 792                  {
 793                      $data = $row[current($cols)];
 794                  }
 795                  $ret[$row[$key_col]] = $data;
 796              }
 797          }
 798          return $cache[$cache_key] =& $ret;
 799      }
 800  }