/framework/core/db/DbConnection.php

  1<?php
  2abstract class DbConnection
  3{
  4	protected $params;
  5	private $queryParams;
  6	private $types;
  7	/**
  8	 * Actual connection to the database
  9	 *
 10	 * @var unknown_type
 11	 */
 12	private $conn;
 13	private $echo;
 14
 15	function __construct($params, $connectionName)
 16	{
 17		$this->params = $params;
 18		$this->validateParams($connectionName);
 19		$echo = false;
 20		$this->init();
 21	}
 22
 23	//
 24	//	Begin configuration funtions
 25	//
 26
 27	private function validateParams($connectionName)
 28	{
 29		//	handle the required fields
 30		$missing = array();
 31		foreach($this->getRequireds() as $thisRequired)
 32		{
 33			if(!isset($this->params[$thisRequired]))
 34				$missing[] = $thisRequired;
 35		}
 36
 37		if(!empty($missing))
 38			throw new ConfigException('db', $missing, "for connection $connectionName");
 39
 40		//	handle the defaults
 41		foreach($this->getDefaults() as $name => $value)
 42		{
 43			if(!isset($this->params[$name]))
 44				$this->params[$name] = $value;
 45		}
 46	}
 47
 48	protected function init()
 49	{
 50	}
 51
 52	protected function getRequireds()
 53	{
 54		return array();
 55	}
 56
 57	protected function getDefaults()
 58	{
 59		return array();
 60	}
 61
 62	//
 63	//	End configuration funtions
 64	//
 65
 66	//
 67	//	Begin misc funtions
 68	//
 69
 70	public function echoOn()
 71	{
 72		$this->echo = true;
 73	}
 74
 75	public function echoOff()
 76	{
 77		$this->echo = false;
 78	}
 79
 80	function escapeString($string)
 81	{
 82		trigger_error("escapeString must be defined in each individual database driver");
 83	}
 84
 85	function escapeIdentifier($string)
 86	{
 87		return '"' . $string . '"';
 88	}
 89
 90	//
 91	//	End misc funtions
 92	//
 93
 94	//
 95	//	Begin Schema functions
 96	//
 97
 98	public function alterSchema($sql)
 99	{
100		return $this->_query($sql);
101	}
102
103	public function getSchema()
104	{
105		return new DbSchema($this);
106	}
107
108	abstract public function tableExists($name);
109	abstract public function getTableNames();
110	abstract public function getTableFieldInfo($tableName);
111
112	/**
113	 * Executes the passed in SQL statement on the database and returns a result set
114	 *
115	 * @param string $sql SQL statement to execute
116	 * @return DbResultSet object
117	 */
118	abstract public function _query($sql);
119
120	//
121	//	End Schema functions
122	//
123
124	//
125	//	Begin transaction funtions
126	//
127
128	public function beginTransaction()
129	{
130		$this->_query('begin');
131	}
132
133	public function commitTransaction()
134	{
135		$this->_query('commit');
136	}
137
138	public function rollbackTransaction()
139	{
140		$this->_query('rollback');
141	}
142
143	//
144	//	End transaction funtions
145	//
146
147	//
148	//	Begin query funtions
149	//
150
151	/**
152	 * Escapes and inserts parameters into the the SQL statement and executes the statement.
153	 *
154	 * Syntax for parameters:
155	 * - :<varname>:string 	-- the variable is escaped as a string and replaces this entry
156	 * - :<varname>:int 		-- the variable is escaped as a number and replaces this entry
157	 * - :<varname>:keyword	-- the variable replaces this entry with no change
158	 * - :<varname>:identifier	-- the variable is escaped as a SQL identifier (table, field name, etc) and replaces this entry
159	 * - :<varname>			-- if no type is specified, variable is treated as a string.
160	 *
161	 * @param string $sql SQL statement to execute
162	 * @param assoc_array $params $variable => $value array of parameters to substitute into the SQL statement
163	 * @return DbResultSet Resultset object
164	 */
165	public function query($sql, $params)
166	{
167		//	do all of the variable replacements
168		$this->queryParams = array();
169		foreach($params as $key => $value)
170		{
171			$parts = explode(':', $key);
172			$this->queryParams[$parts[0]] = $value;
173			if(isset($parts[1]))
174				$this->types[$parts[0]] = $parts[1];
175		}
176		// TODO: indication here how to escape options in here
177		// $sql = preg_replace_callback("/:([[:alpha:]_\d]+):([[:alpha:]_]+)|[^:]:([[:alpha:]_\d]+)/", array($this, 'queryCallback'), $sql);
178		$sql = preg_replace_callback("/:([[:alpha:]_\d]+):([[:alpha:]_]+)|:([[:alpha:]_\d]+)/", array($this, 'queryCallback'), $sql);
179		
180		if($this->echo)
181		{
182			if(php_sapi_name() == "cli")
183				echo $sql . "\n";
184			else
185				echo $sql . '<br>';
186		}
187			
188
189		//	actually do the query
190		return $this->_query($sql);
191	}
192
193	/**
194	 * Private callback function passed to preg_replace_callback for doing the variable substitutions
195	 * This method handles all escaping of strings and identifiers based on the matched list.
196	 * Syntax:
197	 * - :<varname>:string 	-- the variable is escaped as a string and replaces this entry
198	 * - :<varname>:int 		-- the variable is escaped as a number and replaces this entry
199	 * - :<varname>:keyword	-- the variable replaces this entry with no change
200	 * - :<varname>:identifier	-- the variable is escaped as a SQL identifier (table, field name, etc) and replaces this entry
201	 * - :<varname>			-- if no type is specified, variable is treated as a string.
202	 *
203	 * @param array $matches array of matches provided by preg_replace_callback
204	 * @return string String to replace the matched string with
205	 */
206	private function queryCallback($matches)
207	{
208		if(isset($matches[3]))
209			$matches[1] = $matches[3];
210
211		$name = $matches[1];		
212		if(is_null($this->queryParams[$name]))
213			return 'NULL';
214		if($matches[2])
215			$type = $matches[2];
216
217		if(isset($type) && isset($this->types[$name]))
218			assert($type == $this->types[$name]);
219
220		$type = isset($type) ? $type : (isset($this->types[$name]) ? $this->types[$name] : 'string');
221
222		switch($type)
223		{
224			case 'string':
225				$replaceString = $this->escapeString($this->queryParams[$name]);
226				break;
227			case 'int':
228				$replaceString = (int)$this->queryParams[$name];
229				break;
230			case 'keyword':
231				$replaceString = $this->queryParams[$name];
232				break;
233			case 'identifier':
234				$replaceString = $this->escapeIdentifier($this->queryParams[$name]);
235				break;
236			case 'inInts':
237                                assert(is_array($this->queryParams[$name]));
238                                foreach($this->queryParams[$name] as $key => $int)
239                                {
240                                        $this->queryParams[$name][$key] = (int)$int;
241                                }
242                                $replaceString = implode(', ', $this->queryParams[$name]);
243                                break;
244                        case 'inStrings':
245                                assert(is_array($this->queryParams[$name]));
246                                foreach($this->queryParams[$name] as $key => $string)
247                                {
248                                        $this->queryParams[$name][$key] = $this->escapeString($string);
249                                }
250                                $replaceString = implode(', ', $this->queryParams[$name]);
251                                break;
252			default:
253				trigger_error("unknown param type: " . $type);
254				break;
255		}
256
257		return $replaceString;
258	}
259
260	//
261	//	end query funtions
262	//
263
264
265	//
266	//	Begin fetch funtions
267	//
268
269	public function fetchCell($sql, $params)
270	{
271		$row = $this->fetchRow($sql, $params);
272		if(!$row)
273			return NULL;
274		return current($row);
275	}
276
277	public function fetchRow($sql, $params)
278	{
279		$res = $this->query($sql, $params);
280		$num = $res->numRows();
281		if($num > 1)
282			trigger_error("1 row expected: $num returned");
283
284		if($num == 0)
285			return false;
286
287		return $res->current();
288	}
289
290	public function fetchRows($sql, $params)
291	{
292		$rows = array();
293		$res = $this->query($sql, $params);
294
295		if(!$res->valid())
296			return array();
297
298		for($row = $res->current(); $res->valid(); $row = $res->next())
299		{
300			$rows[] = $row;
301		}
302
303		return $rows;
304	}
305
306	public function fetchColumn($sql, $params)
307	{
308		$rows = array();
309		$res = $this->query($sql, $params);
310
311		if(!$res->valid())
312			return array();
313
314		for($row = $res->current(); $res->valid(); $row = $res->next())
315		{
316			$rows[] = current($row);
317		}
318
319		return $rows;
320	}
321
322
323	/**
324	 * Creates a simple nested array structure grouping the values of the $valueField column by the values of the columns specified in the $keyFields array.
325	 *
326	 * For example, if your query returns a list of books and you'd like to group the titles by subject and isbn number, let $keyFields = array("subject", "isbn") and $valueField = "title".
327	 * The format thus created will be $var[$subject][$isbn] = $title;
328	 *
329	 * @param string $sql SQL query with parameters in the format ":variablename" or ":variablename:datatype"
330	 * @param array $keyFields array of fields to group the results by
331	 * @param array $valueField name of the field containing the value to be grouped
332	 * @param array($key=>$value) $params ($key => value) array of parameters to substitute into the SQL query. If you are not passing parameters in, params should be an empty array()
333	 * @return associative array structure grouped by the values in $mapFields
334	 */
335	public function fetchSimpleMap($sql, $keyFields, $valueField, $params)
336	{
337		$map = array();
338		$res = $this->query($sql, $params);
339		for($row = $res->current(); $res->valid(); $row = $res->next())
340		{
341			$cur = &$map;
342			if(is_array($keyFields))
343			{
344				foreach($keyFields as $key)
345				{
346					$cur = &$cur[$row[$key]];
347					$lastKey = $row[$key];
348				}
349			}
350			else
351			{
352				$cur = &$cur[$row[$keyFields]];
353				$lastKey = $row[$keyFields];
354			}
355
356			if(isset($cur) && !empty($lastKey))
357				trigger_error("db::fetchSimpleMap : duplicate key in query: \n $sql \n");
358
359			$cur = $row[ $valueField ];
360		}
361
362		return $map;
363	}
364
365	/**
366	 * Returns a nested array, grouped by the fields (or field) listed in $mapFields
367	 *
368	 * For example, if mapFields = array("person_id", "book_id"), and the resultset returns
369	 * a list of all the chapters of all the books of all the people, this will group the
370	 * records by person and by book, keeping each row in an array under
371	 * $var[$person_id][$book_id]
372	 *
373	 * @param string $sql SQL query with parameters in the format ":variablename" or ":variablename:datatype"
374	 * @param array $mapFields array of fields to group the results by
375	 * @param array($key=>$value) $params ($key => value) array of parameters to substitute into the SQL query. If you are not passing parameters in, params should be an empty array()
376	 * @return associative array structure grouped by the values in $mapFields
377	 */
378	public function fetchMap($sql, $mapFields, $params)
379	{
380		$map = array();
381		$res = $this->query($sql, $params);
382		for($row = $res->current(); $res->valid(); $row = $res->next())
383		{
384			if(is_array($mapFields))
385			{
386				$cur = &$map;
387
388				foreach($mapFields as $val)
389				{
390					$curKey = $row[$val];
391
392					if(!isset($cur[$curKey]))
393						$cur[$curKey] = array();
394
395					$cur = &$cur[$curKey];
396				}
397
398				if(count($cur))
399					trigger_error("db::fetchSimpleMap : duplicate key $curKey (would silently destroy data) in query: \n $inQuery \n");
400
401				$cur = $row;
402			}
403			else
404			{
405				$mapKey = $row[$mapFields];
406				$map[$mapKey] = $row;
407			}
408		}
409
410		return $map;
411	}
412
413	public function fetchComplexMap($sql, $mapFields, $params)
414	{
415		$map = array();
416		$res = $this->query($sql, $params);
417		for($row = $res->current(); $res->valid(); $row = $res->next())
418		{
419			if(is_array($mapFields))
420			{
421				$cur = &$map;
422
423				foreach($mapFields as $val)
424				{
425					$curKey = $row[$val];
426
427					if(!isset($cur[$curKey]))
428						$cur[$curKey] = array();
429
430					$cur = &$cur[$curKey];
431				}
432
433				$cur[] = $row;
434			}
435			else
436			{
437				$mapKey = $row[$mapFields];
438				$map[$mapKey][] = $row;
439			}
440		}
441
442		return $map;
443	}
444
445	//
446	//	End fetch functions
447	//
448
449	//
450	//	Begin insert functions
451	//
452
453	public function insertRow($sql, $params, $serial = true)
454	{
455		$this->query($sql, $params);
456		if($serial)
457			return $this->getLastInsertId();
458		else
459			return false;
460	}
461
462	public function insertRows($sql, $params, $serial = false)
463	{
464		$ids = array();
465		foreach($params as $thisRow)
466		{
467			$id = $this->insertRow($sql, $thisRow, $serial);
468			if($serial)
469				$ids[] = $id;
470		}
471	}
472
473	/**
474	 * Automatically insert the values of an ($key=>$value) array into a database using the $key as the field name
475	 *
476	 * @param string $tableName Table into which the insert should be performed
477	 * @param array $values ($key => $value) array in the format ($fieldName => $fieldValue)
478	 * @param boolean $serial True if the last inserted ID should be returned
479	 * @return mixed ID of the inserted row or false if $serial == false
480	 */
481	public function insertArray($tableName, $values, $serial = true)
482	{
483		$insertInfo = DbConnection::generateInsertInfo($tableName, $values);
484		return $this->insertRow($insertInfo['sql'], $insertInfo['params'], $serial);
485	}
486
487	//
488	//	End insert functions
489	//
490
491	//
492	//	Begin update functions
493	//
494
495	/**
496	 * Executes the given update SQL statement. This method throws an error if multiple rows are updated.
497	 *
498	 * @param string $sql SQL UPDATE statement
499	 * @param assoc_array $params $param => $value array of parameters to escape and substitute into the query
500	 */
501	public function updateRow($sql, $params)
502	{
503		$affected = $this->updateRows($sql, $params);
504		if($affected == 0 || $affected > 1)
505			trigger_error("attempting to update one row, $affected altered");
506	}
507
508	/**
509	 * Executes the given update SQL statement
510	 *
511	 * @param string $sql SQL UPDATE statement
512	 * @param assoc_array $params $param => $value array of parameters to escape and substitute into the query
513	 */
514	public function updateRows($sql, $params)
515	{
516		$res = $this->query($sql, $params);
517		return $res->affectedRows();
518	}
519
520	//
521	//	End update functions
522	//
523
524	//
525	//	Begin delete functions
526	//
527
528	public function deleteRow($sql, $params)
529	{
530		//	it just so happens that they actually need to do the exact same thing
531		$this->updateRow($sql, $params);
532	}
533
534	public function deleteRows($sql, $params)
535	{
536		//	it just so happens that they actually need to do the exact same thing
537		return $this->updateRows($sql, $params);
538	}
539
540	//
541	//	End delete functions
542	//
543
544	//
545	//	Begin combo functions
546	//
547
548	/**
549	 * Automatically generates a select statement using the given information
550	 *
551	 * @param string $tableName name of the table to select from
552	 * @param array $fieldsNames array of fields to retreive
553	 * @param assoc_array $conditions $field => $value array of what to check (at this time only the "=" operator is supported)
554	 * @param assoc_array $params $var => $value array of optional special parameters.  Currently "lock" and "orderby" (set to an array of field names) are supported.
555	 * @return string SQL select statement
556	 */
557	static function generateSelectInfo($tableName, $fieldsNames, $conditions = NULL, $params = NULL)
558	{
559		// echo_r($params);
560		$selectParams = array();
561
562		//	create the field clause
563		//	This first option will allow a "*" or a comma separated list of fields in case an array was not passed in
564		if(is_string($fieldsNames))
565			$fieldClause = $fieldsNames;
566		else
567		{
568			$fieldList = array();
569			foreach ($fieldsNames as $fieldName)
570			{
571				$fieldList[] = ":fld_$fieldName:identifier";
572				$selectParams["fld_$fieldName"] = $fieldName;
573			}
574			$fieldClause = implode(', ', $fieldList);
575		}
576
577		//	create the condition clause
578		if($conditions)
579		{
580			$conditionParts = array();
581			foreach($conditions as $fieldName => $value)
582			{
583				if(is_array($value))
584				{
585					$valueParts = array();
586					foreach($value as $thisKey => $thisValue)
587					{
588						$partName = "{$fieldName}_{$thisKey}";
589						$valueParts[] = ":$partName";
590						$selectParams[$partName] = $thisValue;
591					}
592					
593					$valueString = implode(', ', $valueParts);
594					$conditionParts[] = ":fld_$fieldName:identifier in ($valueString)";
595				}
596				else
597					$conditionParts[] = ":fld_$fieldName:identifier = :$fieldName";
598				$selectParams[$fieldName] = $value;
599				$selectParams["fld_$fieldName"] = $fieldName;
600			}
601			$conditionClause = 'WHERE ' . implode(' AND ', $conditionParts);
602		}
603		else
604			$conditionClause = '';
605
606		//	create the lock clause
607		if(isset($params['lock']) && $params['lock'])
608			$lockClause = 'for update';
609		else
610			$lockClause = '';
611
612		//	create the order by clause
613		if(isset($params['orderby']) && $params['orderby'])
614		{
615			$orderByClause = 'order by ';
616			if(is_array($params['orderby']))
617				$orderByClause .= implode(', ', $params['orderby']);
618			else
619				$orderByClause .= $params['orderby'];
620		}
621		else
622			$orderByClause = '';
623
624		//	create the limit
625		if(isset($params['limit']) && $params['limit'])
626		{
627			$limitClause = 'limit ' . $params['limit'];
628		}
629		else
630			$limitClause = '';
631		
632		//	now put it all together
633		$selectSql = "SELECT $fieldClause FROM :tableName:identifier $conditionClause $orderByClause $limitClause $lockClause";
634		$selectParams['tableName'] = $tableName;
635
636		return array('sql' => $selectSql, 'params' => $selectParams);
637	}
638
639	/**
640	 * Automatically generate an UPDATE SQL statement for the given table based on the passed in conditions and values.  Currently only supports direct equalities for conditions.
641	 *
642	 * @param string $tableName Name of the table to update
643	 * @param assoc_array $conditions $field => $value array of conditions.  Currently only supports the "=" operator.
644	 * @param assoc_array $values $field => $value array of fields to update.  The update statement will be generated to update the fields supplied to the supplied values.
645	 * @return string SQL statement to update the table
646	 */
647	static function generateUpdateInfo($tableName, $conditions, $values)
648	{
649		$updateParams = array();
650
651		//	create the condition part
652		$conditionParts = array();
653		foreach($conditions as $fieldName => $value)
654		{
655			$conditionParts[] = ":fld_$fieldName:identifier = :$fieldName";
656			$updateParams[$fieldName] = $value;
657			$updateParams["fld_$fieldName"] = $fieldName;
658		}
659		$conditionClause = implode(' AND ', $conditionParts);
660
661		//	create the set part
662		$setParts = array();
663		foreach($values as $fieldName => $value)
664		{
665			$fieldNameParts = explode(':', $fieldName);
666			$realFieldName = $fieldNameParts[0];
667
668			$setParts[] = ":fld_$realFieldName:identifier = :$fieldName";
669			$updateParams[$realFieldName] = $value;
670			$updateParams["fld_$realFieldName"] = $realFieldName;
671		}
672
673		$setClause = implode(', ', $setParts);
674
675		//	now put it all together
676		$updateSql = "UPDATE :tableName:keyword SET $setClause WHERE $conditionClause";
677		$updateParams['tableName'] = $tableName;
678
679		return array('sql' => $updateSql, 'params' => $updateParams);
680	}
681
682	/**
683	 * Automatically generate a statement to delete records from a table based on certain conditions.  Currently only the "=" operator is supported for conditions.
684	 *
685	 * @param string $tableName Name of the table to delete from
686	 * @param assoc_array $conditions $field => $value array of conditions to match for the delete statement
687	 * @return string SQL statement to delete records from the table
688	 */
689	static function generateDeleteInfo($tableName, $conditions)
690	{
691		$deleteParams = array();
692
693		//	create the condition part
694		$conditionParts = array();
695		foreach($conditions as $fieldName => $value)
696		{
697			$conditionParts[] = ":fld_$fieldName:identifier = :$fieldName";
698			$deleteParams[$fieldName] = $value;
699			$deleteParams["fld_$fieldName"] = $fieldName;
700		}
701		$conditionClause = implode(' AND ', $conditionParts);
702
703		$updateSql = "DELETE FROM :tableName:keyword WHERE $conditionClause";
704		$deleteParams['tableName'] = $tableName;
705
706		return array('sql' => $updateSql, 'params' => $deleteParams);
707	}
708
709	/**
710	 * Generate an insert statement from an associative ($key => $value) array
711	 *
712	 * @param string $tableName Table into which the insert should be performed
713	 * @param array $values ($key => $value) array in the format ($fieldName => $fieldValue)
714	 * @return string SQL statement to perform the insert
715	 */
716	static function generateInsertInfo($tableName, $values)
717	{
718		$insertParams = array();
719
720		if($values)
721		{
722			//	create the set part
723			$fieldParts = array();
724			$valuesParts = array();
725			foreach($values as $fieldName => $value)
726			{
727				$fieldNameParts = explode(':', $fieldName);
728				$realFieldName = $fieldNameParts[0];
729				$fieldParts[] = ':fld_' . $realFieldName . ":identifier" ;
730				$valuesParts[] = ':' . $fieldName;
731				$insertParams[$realFieldName] = $value;
732				$insertParams["fld_" . $realFieldName] = $realFieldName;
733			}
734			$fieldClause = implode(', ', $fieldParts);
735			$valuesClause = implode(', ', $valuesParts);
736
737			//	now put it all together
738			$insertSql = "INSERT INTO :tableName:identifier ($fieldClause) VALUES ($valuesClause)";
739			$insertParams['tableName'] = $tableName;
740		}
741		else
742		{
743			$insertSql = "INSERT INTO :tableName:identifier DEFAULT VALUES";
744			$insertParams['tableName'] = $tableName;
745		}
746
747		return array('sql' => $insertSql, 'params' => $insertParams);
748	}
749
750	public function upsertRow($tableName, $conditions, $values)
751	{
752		//	generate the update query info
753		$updateInfo = self::generateUpdateInfo($tableName, $conditions, $values);
754
755		//	update the row if it's there
756		$num = $this->updateRows($updateInfo['sql'], $updateInfo['params']);
757
758		if($num > 1)
759			trigger_error("one row expected, $num rows updated");
760
761		if(!$num)
762		{
763			//	generate the insert query info
764			$insertInfo = self::generateInsertInfo($tableName, array_merge($conditions, $values));
765
766			//	if it wasn't there insert it
767			$this->insertRow($insertInfo['sql'], $insertInfo['params'], false);
768
769			//
770			//	I think the only thing that can go wrong here is we get a race condition where another process inserts the row
771			//	after we do the update but before we do the insert.  In that case an error will be thrown here that needs to be handled.
772			//	Also after catching the error we should still try to execute the update statement.  Of course if the thread causing the insert
773			//	error then rolls back we might need to do an insert.  Hmmm... this could get tricky.
774			//
775		}
776	}
777
778	public function selsertRow($tableName, $fieldNames, $conditions, $defaults = NULL, $lock = 0)
779	{
780		//	generate the sqlect query info
781		$selectInfo = self::generateSelectInfo($tableName, $fieldNames, $conditions, $lock);
782
783		//	select the row if it's there
784		$row = $this->fetchRow($selectInfo['sql'], $selectInfo['params']);
785		if(!$row)
786		{
787			//	generate the insert query info
788			$allInsertFields = $defaults ? array_merge($conditions, $defaults) : $conditions;
789			$insertInfo = self::generateInsertInfo($tableName, array_merge($conditions, $allInsertFields));
790
791			//	if it wasn't there insert it
792			$this->insertRow($insertInfo['sql'], $insertInfo['params'], false);
793			//	you may have a race condition here
794			//	if another thread inserted it first then we need to supress the error in PHP4
795			//	we should probably try to use exceptions in PHP5
796
797			$row = $this->fetchRow($selectInfo['sql'], $selectInfo['params']);
798			if(!$row)
799			{
800				//	I'm pretty sure that this should actually never happen
801				trigger_error("error upsert: this shouldn't ever happen.  If it does figure out why and fix this function");
802			}
803		}
804
805		return $row;
806	}
807
808	//
809	//	End combo functions
810	//
811}