1<?php
2
3/**
4 * Abstract DB clas.
5 *
6 * @abstract
7 */
8abstract class DB {
9
10
11	/**
12	 * Default db username
13	 *
14	 * (default value: null)
15	 *
16	 * @var mixed
17	 * @access protected
18	 */
19	protected $username = null;
20
21	/**
22	 * Default db password
23	 *
24	 * (default value: null)
25	 *
26	 * @var mixed
27	 * @access protected
28	 */
29	protected $password = null;
30
31	/**
32	 * charset
33	 *
34	 * (default value: 'utf8')
35	 *
36	 * @var string
37	 * @access protected
38	 */
39	protected $charset = 'utf8';
40
41	/**
42	 * pdo
43	 *
44	 * (default value: null)
45	 *
46	 * @var mixed
47	 * @access protected
48	 */
49	protected $pdo = null;
50
51	/**
52	 * Database name - needed for check
53	 *
54	 * (default value: '')
55	 *
56	 * @var string
57	 * @access public
58	 */
59	public $dbname 	= '';		// needed for DB check
60
61	/**
62	 * hosnamr
63	 *
64	 * (default value: 'localhost')
65	 *
66	 * @var string
67	 * @access protected
68	 */
69	protected $host 	= 'localhost';
70
71	/**
72	 * Default port number
73	 *
74	 * (default value: '3306')
75	 *
76	 * @var string
77	 * @access protected
78	 */
79	protected $port 	= '3306';
80
81	/**
82	 * Cache file to store all results from queries to
83	 *
84	 *  structure:
85	 *
86	 *      [table][index] = (object) $content
87	 *
88	 *
89	 * (default value: array())
90	 *
91	 * @var array
92	 * @access public
93	 */
94	public $cache = array();
95
96
97
98
99	/**
100	 * __construct function.
101	 *
102	 * @access public
103	 * @param mixed $username (default: null)
104	 * @param mixed $password (default: null)
105	 * @param mixed $charset (default: null)
106	 * @param mixed $ssl (default: null)
107	 * @return void
108	 */
109	public function __construct($username = null, $password = null, $charset = null, $ssl = null) {
110		if (isset($username)) $this->username = $username;
111		if (isset($password)) $this->password = $password;
112		if (isset($charset))  $this->charset = $charset;
113		# ssl
114		if ($ssl) {
115			$this->ssl = $ssl;
116		}
117	}
118
119	/**
120	 * convert a date object/string ready for use in sql
121	 *
122	 * @access public
123	 * @static
124	 * @param mixed $date (default: null)
125	 * @return void
126	 */
127	public static function toDate($date = null) {
128		if (is_int($date)) {
129			return date('Y-m-d H:i:s', $date);
130		} else if (is_string($date)) {
131			return date('Y-m-d H:i:s', strtotime($date));
132		} else {
133			return date('Y-m-d H:i:s');
134		}
135	}
136
137	/**
138	 * Connect to the database
139	 * Call whenever a connection is needed to be made
140	 *
141	 * @access public
142	 * @return void
143	 */
144	public function connect() {
145		$dsn = $this->makeDsn();
146
147		try {
148			# ssl?
149			if ($this->ssl) {
150				$this->pdo = new \PDO($dsn, $this->username, $this->password, $this->ssl);
151			}
152			else {
153				$this->pdo = new \PDO($dsn, $this->username, $this->password);
154			}
155
156			$this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
157
158		} catch (\PDOException $e) {
159			throw new Exception ("Could not connect to database! ".$e->getMessage());
160		}
161
162		@$this->pdo->query('SET NAMES \'' . $this->charset . '\';');
163	}
164
165	/**
166	 * makeDsn function.
167	 *
168	 * @access protected
169	 * @return void
170	 */
171	protected function makeDsn() {
172		return ':charset=' . $this->charset;
173	}
174
175	/**
176	 * resets conection.
177	 *
178	 * @access public
179	 * @return void
180	 */
181	public function resetConn() {
182		unset($this->pdo);
183		$this->install = false;
184	}
185
186	/**
187	 * logs queries to file
188	 *
189	 * @access private
190	 * @param mixed $query
191	 * @param array $values
192	 * @return void
193	 */
194	private function log_query ($query, $values = false) {
195		if($this->debug) {
196
197			$myFile = "/tmp/queries.txt";
198			$fh = fopen($myFile, 'a') or die("can't open file");
199			// query
200			fwrite($fh, $query->queryString);
201			// values
202			if(is_array($values)) {
203            fwrite($fh, " Params: ".implode(", ", $values));
204			}
205			// break
206            fwrite($fh, "\n");
207			fclose($fh);
208		}
209	}
210
211	/**
212	 * Remove outer quotes from a string
213	 *
214	 * @access public
215	 * @static
216	 * @param mixed $str
217	 * @return void
218	 */
219	public static function unquote_outer($str) {
220		$len = strlen($str);
221
222		if ($len>1) {
223			if ($str[0] == "'" && $str[$len-1] == "'") {
224				return substr($str, 1, -1);
225			} else if ($str[0] == "'") {
226				return substr($str, 1);
227			} else if ($str[$len-1] == "'") {
228				return substr($str, 0, -1);
229			}
230		} else if ($len>0) {
231			if ($str[0] == "'") {
232				return '';
233			}
234		}
235
236		return $str;
237	}
238
239	/**
240	 * Are we currently connected to the database
241	 *
242	 * @access public
243	 * @return void
244	 */
245	public function isConnected() {
246		return ($this->pdo !== null);
247	}
248
249	/**
250	 * Returns last insert ID
251	 *
252	 * @access public
253	 * @return void
254	 */
255	public function lastInsertId() {
256		return $this->pdo->lastInsertId();
257	}
258
259	/**
260	 * Run a statement on the database
261	 * Note: no objects are fetched
262	 *
263	 * @access public
264	 * @param mixed $query
265	 * @param array $values (default: array())
266	 * @param integer|null &$rowCount (default: null)
267	 * @return void
268	 */
269	public function runQuery($query, $values = array(), &$rowCount = null) {
270		if (!$this->isConnected()) $this->connect();
271
272		$result = null;
273
274		$statement = $this->pdo->prepare($query);
275
276		//debuq
277		$this->log_query($statement, $values);
278
279		if (is_object($statement)) {
280			$result = $statement->execute((array)$values); //this array cast allows single values to be used as the parameter
281			$rowCount = $statement->rowCount();
282		}
283		return $result;
284	}
285
286	/**
287	 * Allow a value to be escaped, ready for insertion as a mysql parameter
288	 * Note: for usage as a value (rather than prepared statements), you MUST manually quote around.
289	 *
290	 * @access public
291	 * @param mixed $str
292	 * @return void
293	 */
294	public function escape($str) {
295		$str = (string) $str;
296		if (strlen($str) == 0) return "";
297
298		if (!$this->isConnected()) $this->connect();
299
300		// SQL Injection - strip backquote character
301		$str = str_replace('`', '', $str);
302		return $this->unquote_outer($this->pdo->quote($str));
303	}
304
305	/**
306	 * Get a quick number of objects in a table
307	 *
308	 * @access public
309	 * @param mixed $tableName
310	 * @return void
311	 */
312	public function numObjects($tableName) {
313		if (!$this->isConnected()) $this->connect();
314
315		$tableName = $this->escape($tableName);
316		$statement = $this->pdo->prepare('SELECT COUNT(*) as `num` FROM `'.$tableName.'`;');
317
318		//debuq
319		$this->log_query ($statement);
320		$statement->execute();
321
322		return $statement->fetchColumn();
323    }
324
325	/**
326	 * Get a quick number of objects in a table for filtered field
327	 *
328	 * @access public
329	 * @param mixed $tableName
330	 * @param mixed $method
331	 * @param boolean $like (default: false)
332	 * @param mixed $value
333	 * @return void
334	 */
335	public function numObjectsFilter($tableName, $method, $value, $like = false) {
336		if (!$this->isConnected()) $this->connect();
337
338		$like === true ? $operator = "LIKE" : $operator = "=";
339
340		$tableName = $this->escape($tableName);
341		$statement = $this->pdo->prepare('SELECT COUNT(*) as `num` FROM `'.$tableName.'` where `'.$method.'` '.$operator.' ?;');
342
343		//debuq
344		$this->log_query ($statement, (array) $value);
345		$statement->execute(array($value));
346
347		return $statement->fetchColumn();
348	}
349
350	/**
351	 * Update an object in a table with values given
352	 *
353	 * Note: the id of the object is assumed to be in.
354	 *
355	 * @access public
356	 * @param mixed $tableName
357	 * @param mixed $obj
358	 * @param string $primarykey (default: 'id')
359	 * @param mixed $primarykey2 (default: null)
360	 * @return void
361	 */
362	public function updateObject($tableName, $obj, $primarykey = 'id', $primarykey2 = null) {
363		if (!$this->isConnected()) $this->connect();
364
365		$obj = (array)$obj;
366
367		//we cannot update an object without an id specified so quit
368		if (!isset($obj[$primarykey])) {
369			throw new Exception('Missing primary key');
370			return false;
371		}
372
373		$tableName = $this->escape($tableName);
374
375		//get the objects id from the provided object and knock it off from the object so we dont try to update it
376		$objId[] = $obj[$primarykey];
377		unset($obj[$primarykey]);
378
379		//secondary primary key?
380		if(!is_null($primarykey2)) {
381		$objId[] = $obj[$primarykey2];
382		unset($obj[$primarykey2]);
383		}
384
385		//TODO: validate given object parameters with that of the table (this validates parameters names)
386
387		//formulate an update statement based on the object parameters
388		$objParams = array_keys($obj);
389
390		$preparedParamArr = array();
391		foreach ($objParams as $objParam) {
392			$preparedParamArr[] = '`' . $this->escape($objParam) . '`=?';
393		}
394
395		// exit on no parameters
396		if(sizeof($preparedParamArr)==0) {
397			throw new Exception('No values to update');
398			return false;
399		}
400
401		$preparedParamStr = implode(',', $preparedParamArr);
402
403		//primary key 2?
404		if(!is_null($primarykey2))
405		$statement = $this->pdo->prepare('UPDATE `' . $tableName . '` SET ' . $preparedParamStr . ' WHERE `' . $primarykey . '`=? AND `' . $primarykey2 . '`=?;');
406		else
407		$statement = $this->pdo->prepare('UPDATE `' . $tableName . '` SET ' . $preparedParamStr . ' WHERE `' . $primarykey . '`=?;');
408
409		//merge the parameters and values
410		$paramValues = array_merge(array_values($obj), $objId);
411
412		//debuq
413		$this->log_query ($statement, $paramValues);
414		//run the update on the object
415		return $statement->execute($paramValues);
416	}
417
418	/**
419	 * Update multiple objects at once.
420	 *
421	 * @access public
422	 * @param string $tableName
423	 * @param array $ids
424	 * @param array $values
425	 * @return void
426	 */
427	public function updateMultipleObjects($tableName, $ids, $values) {
428		$tableName = $this->escape($tableName);
429		//set ids
430		$num = count($ids);
431		$idParts = array_fill(0, $num, '`id`=?');
432		//set values
433		$objParams = array_keys($values);
434		$preparedParamArr = array();
435		foreach ($objParams as $objParam) {
436			$preparedParamArr[] = '`' . $this->escape($objParam) . '`=?';
437		}
438		//set values
439		$all_values = array_merge(array_values($values),$ids);
440		//execute
441		return $this->runQuery('UPDATE `'.$tableName.'` SET '.implode(',', $preparedParamArr).'  WHERE '.implode(' OR ', $idParts), $all_values);
442	}
443
444	/**
445	 * Insert an object into a table
446	 * Note: an id field is ignored if specified.
447	 *
448	 * @access public
449	 * @param string $tableName
450	 * @param object|array $obj
451	 * @param bool $raw (default: false)
452	 * @param bool $replace (default: false)
453	 * @param bool $ignoreId (default: true)
454	 * @return void
455	 */
456	public function insertObject($tableName, $obj, $raw = false, $replace = false, $ignoreId = true) {
457		if (!$this->isConnected()) $this->connect();
458
459		$obj = (array)$obj;
460
461		$tableName = $this->escape($tableName);
462
463		if (!$raw && array_key_exists('id', $obj) && $ignoreId) {
464			unset($obj['id']);
465		}
466
467		if (count($obj)<1) {
468			return true;
469		}
470
471		//formulate an update statement based on the object parameters
472		$objValues = array_values($obj);
473
474		$preparedParamsArr = array();
475		foreach ($obj as $key => $value) {
476			$preparedParamsArr[] = '`' . $this->escape($key) . '`';
477		}
478
479		$preparedParamsStr = implode(', ', $preparedParamsArr);
480		$preparedValuesStr = implode(', ', array_fill(0, count($objValues), '?'));
481
482		if ($replace) {
483			$statement = $this->pdo->prepare('REPLACE INTO `' . $tableName . '` (' . $preparedParamsStr . ') VALUES (' . $preparedValuesStr . ');');
484		} else {
485			$statement = $this->pdo->prepare('INSERT INTO `' . $tableName . '` (' . $preparedParamsStr . ') VALUES (' . $preparedValuesStr . ');');
486		}
487
488		//run the update on the object
489		if (!$statement->execute($objValues)) {
490			$errObj = $statement->errorInfo();
491
492			//return false;
493			throw new Exception($errObj[2]);
494		}
495
496		return $this->pdo->lastInsertId();
497	}
498
499
500	/**
501	 * Check if an object exists.
502	 *
503	 * @access public
504	 * @param string $tableName
505	 * @param string $query (default: null)
506	 * @param array $values (default: array())
507	 * @param mixed $id (default: null)
508	 * @return void
509	 */
510	public function objectExists($tableName, $query = null, $values = array(), $id = null) {
511		return is_object($this->getObject($tableName, $id));
512	}
513
514	/**
515	 * Get a filtered list of objects from the database.
516	 *
517	 * @access public
518	 * @param string $tableName
519	 * @param string $sortField (default: 'id')
520	 * @param bool $sortAsc (default: true)
521	 * @param mixed $numRecords (default: null)
522	 * @param int $offset (default: 0)
523	 * @param string $class (default: 'stdClass')
524	 * @return void
525	 */
526	public function getObjects($tableName, $sortField = 'id', $sortAsc = true, $numRecords = null, $offset = 0, $class = 'stdClass') {
527		if (!$this->isConnected()) $this->connect();
528
529		$sortStr = '';
530		if (!$sortAsc) {
531			$sortStr = 'DESC';
532		}
533
534		// change sort fields for vlans and vrfs. ugly :/
535	    if ($tableName=='vlans' && $sortField=='id') { $sortField = "vlanId"; }
536	    if ($tableName=='vrf' && $sortField=='id') { $sortField = "vrfId"; }
537
538		//we should escape all of the params that we need to
539		$tableName = $this->escape($tableName);
540		$sortField = $this->escape($sortField);
541
542		if ($numRecords === null) {
543			//get all (no limit)
544			$statement = $this->pdo->query('SELECT * FROM `'.$tableName.'` ORDER BY `'.$sortField.'` '.$sortStr.';');
545		} else {
546			//get a limited range of objects
547			$statement = $this->pdo->query('SELECT * FROM `'.$tableName.'` ORDER BY `'.$sortField.'` '.$sortStr.' LIMIT '.$numRecords.' OFFSET '.$offset.';');
548		}
549
550		$results = array();
551
552		if (is_object($statement)) {
553			$results = $statement->fetchAll($class == 'stdClass' ? PDO::FETCH_CLASS : PDO::FETCH_NUM);
554		}
555
556		return $results;
557	}
558
559
560	/**
561	 * use this function to conserve memory and read rows one by one rather than reading all of them
562	 *
563	 * @access public
564	 * @param mixed $query (default: null)
565	 * @param array $values (default: array())
566	 * @param mixed $callback (default: null)
567	 * @return void
568	 */
569	public function getObjectsQueryIncremental($query = null, $values = array(), $callback = null) {
570		if (!$this->isConnected()) $this->connect();
571
572		$statement = $this->pdo->prepare($query);
573
574		//debuq
575		$this->log_query ($statement, $values);
576		$statement->execute((array)$values);
577
578		if (is_object($statement)) {
579			if ($callback) {
580				while ($newObj = $statement->fetchObject('stdClass')) {
581					if ($callback($newObj)===false) {
582						return false;
583					}
584				}
585			}
586		}
587
588		return true;
589	}
590
591
592	/**
593	 * Get all objects matching values
594	 *
595	 * @access public
596	 * @param mixed $query (default: null)
597	 * @param array $values (default: array())
598	 * @param string $class (default: 'stdClass')
599	 * @return void
600	 */
601	public function getObjectsQuery($query = null, $values = array(), $class = 'stdClass') {
602		if (!$this->isConnected()) $this->connect();
603
604		$statement = $this->pdo->prepare($query);
605
606		//debug
607		$this->log_query ($statement, $values);
608		$statement->execute((array)$values);
609
610		$results = array();
611
612		if (is_object($statement)) {
613			$results = $statement->fetchAll($class == 'stdClass' ? PDO::FETCH_CLASS : PDO::FETCH_NUM);
614		}
615
616		return $results;
617	}
618
619	/**
620	 * Get all objects groped by $groupField, array of (id,count(*)) pairs
621	 *
622	 * @param  string $tableName
623	 * @param  string $groupField
624	 * @return array
625	 */
626	public function getGroupBy($tableName, $groupField = 'id') {
627		if (!$this->isConnected()) $this->connect();
628
629		$statement = $this->pdo->prepare("SELECT `$groupField`,COUNT(*) FROM `$tableName` GROUP BY `$groupField`");
630
631		//debug
632		$this->log_query ($statement, array());
633		$statement->execute();
634
635		$results = array();
636
637		if (is_object($statement)) {
638			$results = $statement->fetchAll(PDO::FETCH_KEY_PAIR);
639		}
640
641		return $results;
642	}
643
644	/**
645	 * Get a single object from the database
646	 *
647	 * @access public
648	 * @param mixed $tableName
649	 * @param mixed $id (default: null)
650	 * @param string $class (default: 'stdClass')
651	 * @return void
652	 */
653	public function getObject($tableName, $id = null, $class = 'stdClass') {
654		if (!$this->isConnected()) $this->connect();
655		$id = intval($id);
656
657		//has a custom query been provided?
658		$tableName = $this->escape($tableName);
659
660		//prepare a statement to get a single object from the database
661		if ($id !== null) {
662			$statement = $this->pdo->prepare('SELECT * FROM `'.$tableName.'` WHERE `id`=? LIMIT 1;');
663			$statement->bindParam(1, $id, \PDO::PARAM_INT);
664		} else {
665			$statement = $this->pdo->prepare('SELECT * FROM `'.$tableName.'` LIMIT 1;');
666		}
667
668		//debuq
669		$this->log_query ($statement, array($id));
670		$statement->execute();
671
672		//we can then extract the single object (if we have a result)
673		$resultObj = $statement->fetchObject($class);
674
675		if ($resultObj === false) {
676			return null;
677		} else {
678			return $resultObj;
679		}
680	}
681
682	/**
683	 * Fetches single object from provided query
684	 *
685	 * @access public
686	 * @param mixed $query (default: null)
687	 * @param array $values (default: array())
688	 * @param string $class (default: 'stdClass')
689	 * @return void
690	 */
691	public function getObjectQuery($query = null, $values = array(), $class = 'stdClass') {
692		if (!$this->isConnected()) $this->connect();
693
694		$statement = $this->pdo->prepare($query);
695		//debuq
696		$this->log_query ($statement, $values);
697		$statement->execute((array)$values);
698
699		$resultObj = $statement->fetchObject($class);
700
701		if ($resultObj === false) {
702			return null;
703		} else {
704			return $resultObj;
705		}
706	}
707
708	/**
709	 * Get single value
710	 *
711	 * @access public
712	 * @param mixed $query (default: null)
713	 * @param array $values (default: array())
714	 * @param string $class (default: 'stdClass')
715	 * @return void
716	 */
717	public function getValueQuery($query = null, $values = array(), $class = 'stdClass') {
718		$obj = $this->getObjectQuery($query, $values, $class);
719
720		if (is_object($obj)) {
721			$obj = (array)$obj;
722			return reset($obj);
723		} else {
724			return null;
725		}
726	}
727
728	/**
729	 * Escape $result_fields parameter
730	 *
731	 * @access public
732	 * @param string|array $result_fields
733	 * @return string
734	 */
735	public function escape_result_fields($result_fields) {
736		if (empty($result_fields)) return "*";
737
738		if (is_array($result_fields)) {
739			foreach ($result_fields as $i => $f) $result_fields[$i] = "`$f`";
740			$result_fields = implode(',', $result_fields);
741		}
742		return $result_fields;
743	}
744
745	/**
746	 * Searches for object in database
747	 *
748	 * @access public
749	 * @param mixed $table
750	 * @param mixed $field
751	 * @param mixed $value
752	 * @param string $sortField (default: 'id')
753	 * @param bool $sortAsc (default: true)
754	 * @param bool $like (default: false)
755	 * @param bool $negate (default: false)
756	 * @param string|array $result_fields (default: "*")
757	 * @return void
758	 */
759	public function findObjects($table, $field, $value, $sortField = 'id', $sortAsc = true, $like = false, $negate = false, $result_fields = "*") {
760		$table = $this->escape($table);
761		$field = $this->escape($field);
762		$sortField = $this->escape($sortField);
763		$like === true ? $operator = "LIKE" : $operator = "=";
764		$negate === true ? $negate_operator = "NOT " : $negate_operator = "";
765
766		$result_fields = $this->escape_result_fields($result_fields);
767
768		// change sort fields for vlans and vrfs. ugly :/
769	    if ($table=='vlans' && $sortField=='id') { $sortField = "vlanId"; }
770	    if ($table=='vrf' && $sortField=='id') { $sortField = "vrfId"; }
771
772	    // subnets
773	    if ($table=='subnets' && $sortField=='subnet') {
774	        return $this->getObjectsQuery('SELECT '.$result_fields.' FROM `' . $table . '` WHERE `'. $field .'`'.$negate_operator. $operator .'? ORDER BY LPAD(`subnet`,39,0) ' . ($sortAsc ? '' : 'DESC') . ';', array($value));
775	    } else {
776	        return $this->getObjectsQuery('SELECT '.$result_fields.' FROM `' . $table . '` WHERE `'. $field .'`'.$negate_operator. $operator .'? ORDER BY `'.$sortField.'` ' . ($sortAsc ? '' : 'DESC') . ';', array($value));
777	    }
778	}
779
780	/**
781	 * Searches for single object.
782	 *
783	 * @access public
784	 * @param mixed $table
785	 * @param mixed $field
786	 * @param mixed $value
787	 * @return void
788	 */
789	public function findObject($table, $field, $value) {
790		$table = $this->escape($table);
791		$field = $this->escape($field);
792
793		return $this->getObjectQuery('SELECT * FROM `' . $table . '` WHERE `' . $field . '` = ? LIMIT 1;', array($value));
794	}
795
796	/**
797	 * Get list of items.
798	 *
799	 * @access public
800	 * @param mixed $query (default: null)
801	 * @param array $values (default: array())
802	 * @param string $class (default: 'stdClass')
803	 * @return void
804	 */
805	public function getList($query = null, $values = array(), $class = 'stdClass') {
806		$objs = $this->getObjectsQuery($query, $values, $class);
807
808		$list = array();
809
810		if (!is_array($objs))
811			return $list;
812
813		foreach ($objs as $obj) {
814			$columns = array_values((array)$obj);
815			$list[] = $columns[0];
816		}
817
818		return $list;
819	}
820
821	/**
822	* Delete an object from the database
823	*
824	* @param {string} table name
825	* @param {int} object id
826	* @return {boolean} success
827	*/
828	public function deleteObject($tableName, $id) {
829		$tableName = $this->escape($tableName);
830
831		return $this->runQuery('DELETE FROM `'.$tableName.'` WHERE `id`=?;', array($id));
832	}
833
834	/**
835	* Delete a list of objects from the database
836	*
837	* @param {string} table name
838	* @param {array} list of ids
839	* @return {boolean} success
840	*/
841	public function deleteObjects($tableName, $ids) {
842		$tableName = $this->escape($tableName);
843		$num = count($ids);
844		$idParts = array_fill(0, $num, '`id`=?');
845
846		return $this->runQuery('DELETE FROM `'.$tableName.'` WHERE ' . implode(' OR ', $idParts), $ids);
847	}
848
849	/**
850	 * Delete a list of objects from the database based on identifier
851	 *
852	 * @method deleteObjects
853	 * @param  string $tableName
854	 * @param  string $identifier
855	 * @param  mixed $ids
856	 * @return bool
857	 */
858	public function deleteObjectsByIdentifier($tableName, $identifier = "id", $id = 0) {
859		$tableName = $this->escape($tableName);
860		$identifier = $this->escape($identifier);
861
862		return $this->runQuery('DELETE FROM `'.$tableName.'` WHERE `'.$identifier.'` = ?', $id);
863	}
864
865	/**
866	 * Delete specified row
867	 *
868	 * @access public
869	 * @param {string} $tableName
870	 * @param {string $field
871	 * @param {string $value
872	 * @return void
873	 */
874	public function deleteRow($tableName, $field, $value, $field2=null, $value2 = null) {
875		$tableName = $this->escape($tableName);
876		$field = $this->escape($field);
877		$field2 = $this->escape($field2);
878
879		//multiple
880		if(!empty($field2))
881		return $this->runQuery('DELETE FROM `'.$tableName.'` WHERE `'.$field.'`=? and `'.$field2.'`=?;', array($value, $value2));
882		else
883		return $this->runQuery('DELETE FROM `'.$tableName.'` WHERE `'.$field.'`=?;', array($value));
884	}
885
886	/**
887	 * truncate specified table
888	 *
889	 * @access public
890	 * @param {string} $tableName
891	 * @return void
892	 */
893	public function emptyTable($tableName) {
894		//escape talbe name
895		$tableName = $this->escape($tableName);
896		//execute
897		return $this->runQuery('TRUNCATE TABLE `'.$tableName.'`;');
898	}
899
900	/**
901	 * Begin SQL Transaction
902	 *
903	 * @access public
904	 * @return bool
905	 */
906	public function beginTransaction() {
907		return $this->pdo->beginTransaction();
908	}
909
910	/**
911	 * Commit SQL Transaction
912	 *
913	 * @access public
914	 * @return bool
915	 */
916	public function commit() {
917		return $this->pdo->commit();
918	}
919
920	/**
921	 * Commit SQL Transaction
922	 *
923	 * @access public
924	 * @return bool
925	 */
926	public function rollBack() {
927		return $this->pdo->rollBack();
928	}
929}
930
931
932/**
933*
934*	PDO class wrapper
935*		Database class
936*
937*/
938class Database_PDO extends DB {
939
940
941	/**
942	 * SSL options for db connection
943	 *
944	 * (default value: array ())
945	 *
946	 * @var array
947	 * @access protected
948	 */
949	protected $pdo_ssl_opts = array ();
950
951	/**
952	 * flag if installation is happenig!
953	 *
954	 * (default value: false)
955	 *
956	 * @var bool
957	 * @access public
958	 */
959	public $install = false;
960
961	/**
962	 * Debugging flag
963	 *
964	 * (default value: false)
965	 *
966	 * @var bool
967	 * @access protected
968	 */
969	protected $debug = false;
970
971
972
973
974
975
976	/**
977	 * __construct function.
978	 *
979	 * @access public
980	 * @param mixed $host (default: null)
981	 * @param mixed $port (default: null)
982	 * @param mixed $dbname (default: null)
983	 * @param mixed $username (default: null)
984	 * @param mixed $password (default: null)
985	 * @param mixed $charset (default: null)
986	 */
987	public function __construct($username=null, $password=null, $host=null, $port=null, $dbname=null, $charset=null) {
988		# set parameters
989		$this->set_db_params ();
990		# rewrite user/pass if requested - for installation
991		$username==null ? : $this->username = $username;
992		$password==null ? : $this->password = $password;
993		$host==null 	? : $this->host = $host;
994		$port==null 	? : $this->port = $port;
995		$dbname==null 	? : $this->dbname = $dbname;
996
997		# construct
998		parent::__construct($this->username, $this->password, $this->charset, $this->ssl);
999	}
1000
1001
1002	/**
1003	 * get database parameters from config.php
1004	 *
1005	 * @access private
1006	 * @return void
1007	 */
1008	private function set_db_params () {
1009		# use config file
1010		$db = Config::get('db');
1011
1012		# set
1013		$this->host 	= $db['host'];
1014		$this->port 	= $db['port'];
1015		$this->username = $db['user'];
1016		$this->password = $db['pass'];
1017		$this->dbname 	= $db['name'];
1018
1019		$this->ssl = false;
1020		if (@$db['ssl']===true) {
1021
1022			$this->pdo_ssl_opts = array (
1023				'ssl_key'    => PDO::MYSQL_ATTR_SSL_KEY,
1024				'ssl_cert'   => PDO::MYSQL_ATTR_SSL_CERT,
1025				'ssl_ca'     => PDO::MYSQL_ATTR_SSL_CA,
1026				'ssl_cipher' => PDO::MYSQL_ATTR_SSL_CIPHER,
1027				'ssl_capath' => PDO::MYSQL_ATTR_SSL_CAPATH
1028			);
1029
1030			$this->ssl = array();
1031
1032			if ($db['ssl_verify']===false) {
1033				$this->ssl[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] = false;
1034			}
1035
1036			foreach ($this->pdo_ssl_opts as $key => $pdoopt) {
1037				if ($db[$key]) {
1038					$this->ssl[$pdoopt] = $db[$key];
1039				}
1040			}
1041
1042		}
1043
1044	}
1045
1046	/**
1047	 * connect function.
1048	 *
1049	 * @access public
1050	 * @return void
1051	 */
1052	public function connect() {
1053		parent::connect();
1054		//@$this->pdo->query('SET NAMES \'' . $this->charset . '\';');
1055	}
1056
1057	/**
1058	 * makeDsn function
1059	 *
1060	 * @access protected
1061	 * @return void
1062	 */
1063	protected function makeDsn() {
1064		# for installation
1065		if($this->install)	{ return 'mysql:host=' . $this->host . ';port=' . $this->port . ';charset=' . $this->charset; }
1066		else				{ return 'mysql:host=' . $this->host . ';port=' . $this->port . ';dbname=' . $this->dbname . ';charset=' . $this->charset; }
1067	}
1068
1069	/**
1070	 * more generic static useful methods
1071	 *
1072	 * @access public
1073	 * @return void
1074	 */
1075	public function getColumnInfo() {
1076		$columns = $this->getObjectsQuery("
1077			SELECT `table_name`, `column_name`, `column_default`, `is_nullable`, `data_type`,`column_key`, `extra`
1078			FROM `columns`
1079			WHERE `table_schema`='" . $this->dbname . "';
1080		");
1081
1082		$columnsByTable = array();
1083
1084		if (!is_array($columns))
1085			return $columnsByTable;
1086
1087		foreach ($columns as $column) {
1088			if (!isset($columnsByTable[$column->table_name])) {
1089				$columnsByTable[$column->table_name] = array();
1090			}
1091
1092			$columnsByTable[$column->table_name][$column->column_name] = $column;
1093		}
1094
1095		return $columnsByTable;
1096	}
1097
1098	/**
1099	 * Returns field info.
1100	 *
1101	 * @access public
1102	 * @param bool $tableName (default: false)
1103	 * @param bool $field (default: false)
1104	 * @return void|object
1105	 */
1106	public function getFieldInfo ($tableName = false, $field = false) {
1107    	//escape
1108    	$tableName = $this->escape($tableName);
1109    	$field = $this->escape($field);
1110    	// fetch and return
1111    	return $this->getObjectQuery("SHOW FIELDS FROM `$tableName` where Field = ?", array($field));
1112
1113	}
1114
1115	/**
1116	 * getForeignKeyInfo function.
1117	 *
1118	 * @access public
1119	 * @return void
1120	 */
1121	public function getForeignKeyInfo() {
1122		$foreignLinks = $this->getObjectsQuery("
1123			SELECT i.`table_name`, k.`column_name`, i.`constraint_type`, i.`constraint_name`, k.`referenced_table_name`, k.`referenced_column_name`
1124			FROM `table_constraints` i
1125			LEFT JOIN `key_column_usage` k ON i.`constraint_name` = k.`constraint_name`
1126			WHERE i.`constraint_type` = 'FOREIGN KEY' AND i.`table_schema`='" . $this->dbname . "';
1127		");
1128
1129		$foreignLinksByTable = array();
1130		$foreignLinksByRefTable = array();
1131
1132		if (!is_array($foreignLinks))
1133			return array($foreignLinksByTable, $foreignLinksByRefTable);
1134
1135		foreach ($foreignLinks as $foreignLink) {
1136			if (!isset($foreignLinksByTable[$foreignLink->table_name])) {
1137				$foreignLinksByTable[$foreignLink->table_name] = array();
1138			}
1139
1140			if (!isset($foreignLinksByRefTable[$foreignLink->referenced_table_name])) {
1141				$foreignLinksByRefTable[$foreignLink->referenced_table_name] = array();
1142			}
1143
1144			$foreignLinksByTable[$foreignLink->table_name][$foreignLink->column_name] = $foreignLink;
1145			$foreignLinksByRefTable[$foreignLink->referenced_table_name][$foreignLink->table_name] = $foreignLink;
1146		}
1147
1148		return array($foreignLinksByTable, $foreignLinksByRefTable);
1149	}
1150}
1151