1<?php 2 /**********************************************************************\ 3 * phpGroupWare - eTemplate * 4 * http://www.phpgroupware.org * 5 * This program is part of the GNU project, see http://www.gnu.org/ * 6 * * 7 * Copyright 2002, 2003 Free Software Foundation, Inc. * 8 * * 9 * Originally Written by Ralf Becker - <RalfBecker@outdoor-training.de> * 10 * -------------------------------------------- * 11 * This program is Free Software; you can redistribute it and/or modify * 12 * it under the terms of the GNU General Public License as published by * 13 * the Free Software Foundation; either version 2 of the License, or * 14 * at your option) any later version. * 15 \**********************************************************************/ 16 /* $Id: class.so_sql.inc.php 13846 2003-11-03 07:05:05Z skwashd $ */ 17 18/*! 19@class so_sql 20@author ralfbecker 21@abstract generalized SQL Storage Object 22@discussion the class can be used in following ways: 23@discussion 1) by calling the constructor with an app and table-name or 24@discussion 2) by setting the following class-vars in a class derifed from this one 25@discussion Of cause can you derife the class and call the constructor with params. 26@param $table_name need to be set in the derived class to the db-table-name 27@param $autoinc_id db-col-name of autoincrement id or '' 28@param $db_key_cols array of all primary-key-columns in form dbName => internalName 29@discussion the save function does NOT touch any other cols in the table!!! 30@param $db_data_cols array of all data-cols 31@param $debug turns on debug-messages 32@param $empty_on_write string to be written to db if a col-value is '', eg. "''" or 'NULL' (default) 33@param $non_db_cols all cols in data which are not (direct)in the db, for data_merge 34*/ 35class so_sql 36{ 37 var $public_functions = array( 38 'init' => True, 39 'data_merge' => True, 40 'read' => True, 41 'save' => True, 42 'delete' => True, 43 'search' => True, 44 ); 45 var $db,$table_name; 46 var $autoinc_id = ''; 47 var $db_key_cols = array(),$db_data_cols = array(); // key_cols mean primary keys 48 var $db_uni_cols = array(); 49 var $db_cols; // = $db_key_cols + $db_data_cols 50 var $non_db_cols = array(); 51 var $data; // holds the content of all db_cols 52 var $debug = 0; 53 var $empty_on_write = 'NULL'; 54 var $non_db_cols = array(); 55 56 /*! 57 @function so_sql 58 @syntax so_sql( $app='',$table='' ) 59 @author ralfbecker 60 @abstract constructor of the class 61 @discussion NEED to be called from the constructor of the derived class 62 @param $app, $table should be set if table-defs to be read from <app>/setup/tables_current.inc.php 63 */ 64 function so_sql($app='',$table='') 65 { 66 $this->db = $GLOBALS['phpgw']->db; 67 $this->db_cols = $this->db_key_cols + $this->db_data_cols; 68 69 if ($app && $table) 70 { 71 $this->setup_table($app,$table); 72 } 73 $this->init(); 74 75 if ($this->debug) 76 { 77 echo "<p>so_sql('$app','$table')</p>\n"; 78 _debug_array($this); 79 } 80 } 81 82 /*! 83 @function setup_table 84 @syntax setup_table( $app,$table ) 85 @author ralfbecker 86 @abstract reads table-definition from <app>/setup/tables_current.inc.php 87 @discussion Does NOT set a different internal-data-name. If you want this, you have to do so 88 @discussion in a derifed class !!! 89 */ 90 function setup_table($app,$table) 91 { 92 include(PHPGW_SERVER_ROOT . "/$app/setup/tables_current.inc.php"); 93 94 if (!isset($phpgw_baseline[$table])) 95 { 96 echo "<p>Can't find table-definitions for App. '$app', Table '$table' !!!</p>\n"; 97 exit(); 98 } 99 $this->table_name = $table; 100 101 $table_def = $phpgw_baseline[$table]; 102 $this->db_key_cols = $this->db_data_cols = $this->db_cols = array(); 103 $this->autoinc_id = ''; 104 foreach($table_def['fd'] as $name => $def) 105 { 106 if (in_array($name,$table_def['pk'])) 107 { 108 $this->db_key_cols[$name] = $name; 109 } 110 else 111 { 112 $this->db_data_cols[$name] = $name; 113 } 114 $this->db_cols[$name] = $name; 115 116 if ($def['type'] == 'auto') 117 { 118 $this->autoinc_id = $name; 119 } 120 if (in_array($name,$table_def['uc'])) 121 { 122 $this->db_uni_cols[$name] = $name; 123 } 124 } 125 } 126 127 /*! 128 @function so_data_merge 129 @syntax so_data_merge( $new ) 130 @author ralfbecker 131 @abstract merges in new values from the given new data-array 132 @param $new array in form col => new_value with values to set 133 */ 134 function data_merge($new) 135 { 136 if (!is_array($new) || !count($new)) 137 { 138 return; 139 } 140 foreach($this->db_cols as $db_col => $col) 141 { 142 if (isset($new[$col])) 143 { 144 $this->data[$col] = $new[$col]; 145 } 146 } 147 foreach($this->non_db_cols as $db_col => $col) 148 { 149 if (isset($new[$col])) 150 { 151 $this->data[$col] = $new[$col]; 152 } 153 } 154 } 155 156 /*! 157 @function db2data 158 @abstract changes the data from the db-format to your work-format 159 @discussion it gets called everytime when data is read from the db 160 @discussion This function needs to be reimplemented in the derived class 161 @param $data if given works on that array and returns result, else works on internal data-array 162 */ 163 function db2data($data=0) 164 { 165 if ($intern = !is_array($data)) 166 { 167 $data = $this->data; 168 } 169 // do the necessare changes here 170 171 if ($intern) 172 { 173 $this->data = $data; 174 } 175 return $data; 176 } 177 178 /*! 179 @function data2db 180 @abstract changes the data from your work-format to the db-format 181 @discussion It gets called everytime when data gets writen into db or on keys for db-searches 182 @discussion this needs to be reimplemented in the derived class 183 @param $data if given works on that array and returns result, else works on internal data-array 184 */ 185 function data2db($data=0) 186 { 187 if ($intern = !is_array($data)) 188 { 189 $data = $this->data; 190 } 191 // do the necessary changes here 192 193 if ($intern) 194 { 195 $this->data = $data; 196 } 197 return $data; 198 } 199 200 /*! 201 @function init 202 @abstract initializes data with the content of key 203 @param $keys array with keys in form internalName => value 204 @result void 205 */ 206 function init($keys=array()) 207 { 208 $this->data = array(); 209 210 $this->db2data(); 211 212 $this->data_merge($keys); 213 } 214 215 /*! 216 @function read 217 @abstract reads row matched by key and puts all cols in the data array 218 @param $keys array with keys in form internalName => value, may be a scalar value if only one key 219 @result data array if row could be retrived else False and data = array() 220 */ 221 function read($keys) 222 { 223 $this->init($keys); 224 225 $this->data2db(); 226 foreach ($this->db_key_cols as $db_col => $col) 227 { 228 if ($this->data[$col] != '') 229 { 230 $query .= ($query ? ' AND ':'')."$db_col='".addslashes($this->data[$col])."'"; 231 } 232 } 233 if (!$query) // no primary key in keys, lets try the data_cols for a unique key 234 { 235 foreach($this->db_data_cols as $db_col => $col) 236 { 237 if ($this->data[$col] != '') 238 { 239 $query .= ($query ? ' AND ':'')."$db_col='".addslashes($this->data[$col])."'"; 240 } 241 } 242 } 243 if (!$query) // keys has no cols 244 { 245 $this->db2data(); 246 247 return False; 248 } 249 $this->db->query($sql = "SELECT * FROM $this->table_name WHERE $query",__LINE__,__FILE__); 250 251 if ($this->debug) 252 { 253 echo "<p>read(): sql = '$sql': "; 254 } 255 if (!$this->db->next_record()) 256 { 257 if ($this->autoinc_id) 258 { 259 unset($this->data[$this->db_key_cols[$this->autoinc_id]]); 260 } 261 if ($this->debug) echo "nothing found !!!</p>\n"; 262 263 $this->db2data(); 264 265 return False; 266 } 267 foreach ($this->db_cols as $db_col => $col) 268 { 269 $this->data[$col] = $this->db->f($db_col); 270 } 271 $this->db2data(); 272 273 if ($this->debug) 274 { 275 echo "data =\n"; _debug_array($this->data); 276 } 277 return $this->data; 278 } 279 280 /*! 281 @function save 282 @abstracts saves the content of data to the db 283 @param $keys if given $keys are copied to data before saveing => allows a save as 284 @result 0 on success and errno != 0 else 285 */ 286 function save($keys='') 287 { 288 $this->data_merge($keys); 289 290 if (!$this->autoinc_id) // no autoincrement id, so we need to find out with read if key already in db 291 { 292 $data = $this->data; 293 $new = !$this->read($data); 294 $this->data = $data; 295 } 296 else 297 { 298 $new = !$this->data[$this->db_key_cols[$this->autoinc_id]]; // autoincrement idx is 0 => new 299 } 300 $this->data2db(); 301 302 if ($new) // prepare an insert 303 { 304 foreach($this->db_cols as $db_col => $col) 305 { 306 if (!$this->autoinc_id || $db_col != $this->autoinc_id) // not write auto-inc-id 307 { 308 $cols .= ($cols ? ',' : '') . $db_col; 309 $vals .= ($vals ? ',' : '') . ($this->data[$col] == '' ? 310 $this->empty_on_write : "'".addslashes($this->data[$col])."'"); 311 } 312 } 313 $this->db->query($sql = "INSERT INTO $this->table_name ($cols) VALUES ($vals)",__LINE__,__FILE__); 314 315 if ($this->autoinc_id) 316 { 317 $this->data[$this->db_key_cols[$this->autoinc_id]] = $this->db->get_last_insert_id($this->table_name,$this->autoinc_id); 318 } 319 } 320 else //update existing row, preserv other cols not used here 321 { 322 foreach($this->db_data_cols as $db_col => $col) 323 { 324 $vals .= ($vals ? ',':'') . "$db_col=".($this->data[$col] == '' ? 325 $this->empty_on_write : "'".addslashes($this->data[$col])."'"); 326 } 327 $keys = ''; 328 foreach($this->db_key_cols as $db_col => $col) 329 { 330 $keys .= ($keys ? ' AND ':'') . "$db_col='".addslashes($this->data[$col])."'"; 331 } 332 $this->db->query($sql = "UPDATE $this->table_name SET $vals WHERE $keys",__LINE__,__FILE__); 333 } 334 if ($this->debug) 335 { 336 echo "<p>save(): sql = '$sql'</p>\n"; 337 } 338 $this->db2data(); 339 340 return $this->db->errno; 341 } 342 343 /*! 344 @function delete 345 @abstract deletes row representing keys in internal data or the supplied $keys if != '' 346 @param $keys if not '', array with col => value pairs to characterise the rows to delete 347 @result affected rows, should be 1 if ok, 0 if an error 348 */ 349 function delete($keys='') 350 { 351 if (!is_array($keys) || !count($keys)) // use internal data 352 { 353 $data = $this->data; 354 $keys = $this->db_key_cols; 355 } 356 else // data and keys are supplied in $keys 357 { 358 $data = $keys; $keys = array(); 359 foreach($this->db_cols as $db_col => $col) 360 { 361 if (isset($data[$col])) 362 { 363 $keys[$db_col] = $col; 364 } 365 } 366 } 367 $data = $this->data2db($data); 368 369 foreach($keys as $db_col => $col) 370 { 371 $query .= ($query ? ' AND ' : '') . $db_col . "='" . addslashes($data[$col]) . "'"; 372 } 373 $this->db->query($sql = "DELETE FROM $this->table_name WHERE $query",__LINE__,__FILE__); 374 375 if ($this->debug) 376 { 377 echo "<p>delete(): sql = '$sql'</p>\n"; 378 } 379 return $this->db->affected_rows(); 380 } 381 382 /*! 383 @function search 384 @abstract searches db for rows matching searchcriteria 385 @discussion '*' and '?' are replaced with sql-wildcards '%' and '_' 386 @param $criteria array of key and data cols, OR a SQL query (content for WHERE), fully quoted (!) 387 @param $only_keys True returns only keys, False returns all cols 388 @param $order_by fieldnames + {ASC|DESC} separated by colons ',' 389 @param $extra_cols string to be added to the SELECT, eg. (count(*) as num) 390 @param $wildcard string appended befor and after each criteria 391 @param $empty False=empty criteria are ignored in query, True=empty have to be empty in row 392 @param $op defaults to 'AND', can be set to 'OR' too, then criteria's are OR'ed together 393 @result array of matching rows (the row is an array of the cols) or False 394 */ 395 function search($criteria,$only_keys=True,$order_by='',$extra_cols='',$wildcard='',$empty=False,$op='AND') 396 { 397 if (!is_array($criteria)) 398 { 399 $query = ' WHERE '.$criteria; 400 } 401 else 402 { 403 $criteria = $this->data2db($criteria); 404 foreach($this->db_cols as $db_col => $col) 405 { //echo "testing col='$col', criteria[$col]='".$criteria[$col]."'<br>"; 406 if (isset($criteria[$col]) && ($empty || $criteria[$col] != '')) 407 { 408 $query .= ($query ? " $op " : ' WHERE ') . $db_col . 409 ($wildcard || strstr($criteria[$col],'*') || strstr($criteria[$col],'?') ? 410 " LIKE '$wildcard".strtr(str_replace('_','\\_',addslashes($criteria[$col])),'*?','%_')."$wildcard'" : 411 "='".addslashes($criteria[$col])."'"); 412 } 413 } 414 } 415 $this->db->query($sql = 'SELECT '.($only_keys ? implode(',',$this->db_key_cols) : '*'). 416 ($extra_cols != '' ? ",$extra_cols" : '')." FROM $this->table_name $query" . 417 ($order_by != '' ? " ORDER BY $order_by" : ''),__LINE__,__FILE__); 418 419 if ($this->debug) 420 { 421 echo "<p>search(only_keys=$only_keys,order_by='$order_by',wildcard='$wildcard',empty=$empty)<br>sql = '$sql'</p>\n"; 422 echo "<br>criteria = "; _debug_array($criteria); 423 } 424 $arr = array(); 425 $cols = $only_keys ? $this->db_key_cols : $this->db_cols; 426 for ($n = 0; $this->db->next_record(); ++$n) 427 { 428 $row = array(); 429 foreach($cols as $db_col => $col) 430 { 431 $row[$col] = $this->db->f($db_col); 432 } 433 $arr[] = $this->db2data($row); 434 } 435 return $n ? $arr : False; 436 } 437 438 /*! 439 @function not_unique 440 @syntax not_unique( $data='' ) 441 @author ralfbecker 442 @abstract Check if values for unique keys are unique 443 @param $data data-set to check, defaults to $this->data 444 @result 0: all keys are unique, 1: first key not unique, 2: ... 445 */ 446 function not_unique($data='') 447 { 448 if (!is_array($data)) 449 { 450 $data = $this->data; 451 } 452 $n = 1; 453 foreach($this->db_uni_cols as $db_col => $col) 454 { 455 if (list($other) = $this->search(array($db_col => $data[$col]))) 456 { 457 foreach($this->db_key_cols as $db_key_col => $key_col) 458 { 459 if ($data[$key_col] != $other[$key_col]) 460 { 461 if ($this->debug) 462 { 463 echo "<p>not_unique in '$col' as for '$key_col': '${data[$key_col]}' != '${other[$key_col]}'</p>\n"; 464 } 465 return $n; // different entry => $n not unique 466 } 467 } 468 } 469 ++$n; 470 } 471 return 0; 472 } 473}; 474