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