/[phpgroupware]/etemplate/inc/class.so_sql.inc.php
ViewVC logotype

Contents of /etemplate/inc/class.so_sql.inc.php

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.5 - (show annotations) (download)
Wed Apr 16 14:32:15 2003 UTC (21 years, 1 month ago) by ralfbecker
Branch: MAIN
CVS Tags: merged2head
Branch point for: Version-0_9_16-branch, old_0_9_17_HEAD
Changes since 1.4: +21 -13 lines
added possibility to use a sql query-string to so_sql::search

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 };

savannah-hackers-public@gnu.org
ViewVC Help
Powered by ViewVC 1.1.26