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