1<?php
2// (c) Copyright by authors of the Tiki Wiki CMS Groupware Project
3//
4// All Rights Reserved. See copyright.txt for details and a complete list of authors.
5// Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details.
6// $Id$
7
8/**
9 * Class TikiDb
10 * Implemented by TikiDb_Pdo and TikiDb_Adodb
11 */
12abstract class TikiDb
13{
14	const ERR_DIRECT = true;
15	const ERR_NONE = false;
16	const ERR_EXCEPTION = 'exception';
17
18	private static $instance;
19
20	private $errorHandler;
21	private $errorMessage;
22	private $serverType;
23
24	protected $savedQuery;
25
26	private $tablePrefix;
27	private $usersTablePrefix;
28
29	/**
30	 * @return TikiDb
31	 */
32	public static function get() // {{{
33	{
34		return self::$instance;
35	} // }}}
36
37	public static function set(TikiDb $instance) // {{{
38	{
39		return self::$instance = $instance;
40	} // }}}
41
42	function startTimer() // {{{
43	{
44		list($micro, $sec) = explode(' ', microtime());
45		return $micro + $sec;
46	} // }}}
47
48	function stopTimer($starttime) // {{{
49	{
50		global $elapsed_in_db;
51		list($micro, $sec) = explode(' ', microtime());
52		$now = $micro + $sec;
53		$elapsed_in_db += $now - $starttime;
54	} // }}}
55
56	abstract function qstr($str);
57
58	/**
59	 * @param null $query
60	 * @param null $values
61	 * @param int $numrows
62	 * @param int $offset
63	 * @param bool $reporterrors
64	 * @return TikiDb_Pdo_Result
65	 */
66	abstract function query($query = null, $values = null, $numrows = -1, $offset = -1, $reporterrors = self::ERR_DIRECT);
67
68	function lastInsertId() // {{{
69	{
70		return $this->getOne('SELECT LAST_INSERT_ID()');
71	} // }}}
72
73	function queryError($query, &$error, $values = null, $numrows = -1, $offset = -1) // {{{
74	{
75		$this->errorMessage = '';
76		$result = $this->query($query, $values, $numrows, $offset, self::ERR_NONE);
77		$error = $this->errorMessage;
78
79		return $result;
80	} // }}}
81
82	function queryException($query, $values = null, $numrows = -1, $offset = -1) // {{{
83	{
84		return $this->query($query, $values, $numrows, $offset, self::ERR_EXCEPTION);
85	} // }}}
86
87	function getOne($query, $values = null, $reporterrors = self::ERR_DIRECT, $offset = 0) // {{{
88	{
89		$result = $this->query($query, $values, 1, $offset, $reporterrors);
90
91		if ($result) {
92			$res = $result->fetchRow();
93
94			if (empty($res)) {
95				return $res;
96			}
97
98			return reset($res);
99		}
100
101		return false;
102	} // }}}
103
104	function fetchAll($query = null, $values = null, $numrows = -1, $offset = -1, $reporterrors = self::ERR_DIRECT) // {{{
105	{
106		$result = $this->query($query, $values, $numrows, $offset, $reporterrors);
107
108		$rows = [];
109
110		if ($result) {
111			while ($row = $result->fetchRow()) {
112				$rows[] = $row;
113			}
114		}
115		return $rows;
116	} // }}}
117
118	function fetchMap($query = null, $values = null, $numrows = -1, $offset = -1, $reporterrors = self::ERR_DIRECT) // {{{
119	{
120		$result = $this->fetchAll($query, $values, $numrows, $offset, $reporterrors);
121
122		$map = [];
123
124		foreach ($result as $row) {
125			$key = array_shift($row);
126			$value = array_shift($row);
127
128			$map[$key] = $value;
129		}
130
131		return $map;
132	} // }}}
133
134	function setErrorHandler(TikiDb_ErrorHandler $handler) // {{{
135	{
136		$this->errorHandler = $handler;
137	} // }}}
138
139	function setTablePrefix($prefix) // {{{
140	{
141		$this->tablePrefix = $prefix;
142	} // }}}
143
144	function setUsersTablePrefix($prefix) // {{{
145	{
146		$this->usersTablePrefix = $prefix;
147	} // }}}
148
149	function getServerType() // {{{
150	{
151		return $this->serverType;
152	} // }}}
153
154	function setServerType($type) // {{{
155	{
156		$this->serverType = $type;
157	} // }}}
158
159	function getErrorMessage() // {{{
160	{
161		return $this->errorMessage;
162	} // }}}
163
164	protected function setErrorMessage($message) // {{{
165	{
166		$this->errorMessage = $message;
167	} // }}}
168
169	protected function handleQueryError($query, $values, $result, $mode) // {{{
170	{
171		if ($mode === self::ERR_NONE) {
172			return null;
173		} elseif ($mode === self::ERR_DIRECT && $this->errorHandler) {
174			$this->errorHandler->handle($this, $query, $values, $result);
175		} elseif ($mode === self::ERR_EXCEPTION || ! $this->errorHandler) {
176			TikiDb_Exception::classify($this->errorMessage);
177		}
178	} // }}}
179
180	protected function convertQueryTablePrefixes(&$query) // {{{
181	{
182		$db_table_prefix = $this->tablePrefix;
183		$common_users_table_prefix = $this->usersTablePrefix;
184
185		if (! is_null($db_table_prefix) && ! empty($db_table_prefix)) {
186			if (! is_null($common_users_table_prefix) && ! empty($common_users_table_prefix)) {
187				$query = str_replace("`users_", "`" . $common_users_table_prefix . "users_", $query);
188			} else {
189				$query = str_replace("`users_", "`" . $db_table_prefix . "users_", $query);
190			}
191
192			$query = str_replace("`tiki_", "`" . $db_table_prefix . "tiki_", $query);
193			$query = str_replace("`messu_", "`" . $db_table_prefix . "messu_", $query);
194			$query = str_replace("`sessions", "`" . $db_table_prefix . "sessions", $query);
195		}
196	} // }}}
197
198	function convertSortMode($sort_mode, $fields = null) // {{{
199	{
200		if (! $sort_mode) {
201			return '1';
202		}
203		// parse $sort_mode for evil stuff
204		$sort_mode = str_replace('pref:', '', $sort_mode);
205		$sort_mode = preg_replace('/[^A-Za-z_,.]/', '', $sort_mode);
206
207		// Do not process empty sort modes
208		if (empty($sort_mode)) {
209			return '1';
210		}
211
212		if ($sort_mode == 'random') {
213			return "RAND()";
214		}
215
216		$sorts = [];
217		foreach (explode(',', $sort_mode) as $sort) {
218			// force ending to either _asc or _desc unless it's "random"
219			$sep = strrpos($sort, '_');
220			$dir = substr($sort, $sep);
221			if (($dir !== '_asc') && ($dir !== '_desc')) {
222				if ($sep != (strlen($sort) - 1)) {
223					$sort .= '_';
224				}
225				$sort .= 'asc';
226			}
227
228			// When valid fields are specified, skip those not available
229			if (is_array($fields) && preg_match('/^(.*)_(asc|desc)$/', $sort, $parts)) {
230				if (! in_array($parts[1], $fields)) {
231					continue;
232				}
233			}
234
235			$sort = preg_replace('/_asc$/', '` asc', $sort);
236			$sort = preg_replace('/_desc$/', '` desc', $sort);
237			$sort = '`' . $sort;
238			$sort = str_replace('.', '`.`', $sort);
239			$sorts[] = $sort;
240		}
241
242		if (empty($sorts)) {
243			return '1';
244		}
245
246		$sort_mode = implode(',', $sorts);
247		return $sort_mode;
248	} // }}}
249
250	function getQuery() // {{{
251	{
252		return $this->savedQuery;
253	} // }}}
254
255	function setQuery($sql) // {{{
256	{
257		$this->savedQuery = $sql;
258	} // }}}
259
260	function ifNull($field, $ifNull) // {{{
261	{
262		return " COALESCE($field, $ifNull) ";
263	} // }}}
264
265	function in($field, $values, &$bindvars) // {{{
266	{
267		$parts = explode('.', $field);
268		foreach ($parts as &$part) {
269			$part = '`' . $part . '`';
270		}
271		$field = implode('.', $parts);
272		$bindvars = array_merge($bindvars, $values);
273
274		if (count($values) > 0) {
275			$values = rtrim(str_repeat('?,', count($values)), ',');
276			return " $field IN( $values ) ";
277		} else {
278			return " 0 ";
279		}
280	} // }}}
281
282	function parentObjects(&$objects, $table, $childKey, $parentKey) // {{{
283	{
284		$query = "select `$childKey`, `$parentKey` from `$table` where `$childKey` in (" . implode(',', array_fill(0, count($objects), '?')) . ')';
285		foreach ($objects as $object) {
286			$bindvars[] = $object['itemId'];
287		}
288		$result = $this->query($query, $bindvars);
289		while ($res = $result->fetchRow()) {
290			$ret[$res[$childKey]] = $res[$parentKey];
291		}
292		foreach ($objects as $i => $object) {
293			$objects[$i][$parentKey] = $ret[$object['itemId']];
294		}
295	} // }}}
296
297	function concat() // {{{
298	{
299		$arr = func_get_args();
300
301		// suggestion by andrew005@mnogo.ru
302		$s = implode(',', $arr);
303		if (strlen($s) > 0) {
304			return "CONCAT($s)";
305		} else {
306			return '';
307		}
308	} // }}}
309
310	function table($tableName, $autoIncrement = true) // {{{
311	{
312		return new TikiDb_Table($this, $tableName, $autoIncrement);
313	} // }}}
314
315	function begin() // {{{
316	{
317		return new TikiDb_Transaction;
318	} // }}}
319
320	/**
321	* Get a list of installed engines in the MySQL instance
322	* $return array of engine names
323	*/
324	function getEngines()
325	{
326		static $engines = [];
327		if (empty($engines)) {
328			$result = $this->query('show engines');
329			if ($result) {
330				while ($res = $result->fetchRow()) {
331					$engines[] = $res['Engine'];
332				}
333			}
334		}
335		return $engines;
336	}
337
338	/**
339	 * Check if InnoDB is an avaible engine
340	 * @return true if the InnoDB engine is available
341	 */
342	function hasInnoDB()
343	{
344		$engines = $this->getEngines();
345		foreach ($engines as $engine) {
346			if (strcmp(strtoupper($engine), 'INNODB') == 0) {
347				return true;
348			}
349		}
350		return false;
351	}
352
353	/**
354	 * Detect the engine used in the current schema.
355	 * Assumes that all tables use the same table engine
356	 * @return string identifying the current engine, or an empty string if not installed
357	 */
358	function getCurrentEngine()
359	{
360		static $engine = '';
361		if (empty($engine)) {
362			$result = $this->query('SHOW TABLE STATUS LIKE ?', 'tiki_schema');
363			if ($result) {
364				$res = $result->fetchRow();
365				$engine  = $res['Engine'];
366			}
367		}
368		return $engine;
369	}
370
371	/**
372	 * Determine if MySQL fulltext search is supported by the current DB engine
373	 * Assumes that all tables use the same table engine.
374	 * Fulltext search is assumed supported if
375	 * 1) engine = MyISAM
376	 * 2) engine = InnoDB and MySQL version >= 5.6
377	 * @return true if it is supported, otherwise false
378	 */
379	function isMySQLFulltextSearchSupported()
380	{
381		$currentEngine = $this->getCurrentEngine();
382		if (strcasecmp($currentEngine, "MyISAM") == 0) {
383			return true;
384		} elseif (strcasecmp($currentEngine, "INNODB") == 0) {
385			$versionNr = $this->getMySQLVersionNr();
386			if ($versionNr >= 5.6) {
387				return true;
388			} else {
389				return false;
390			}
391		}
392		return false;
393	}
394
395
396	/**
397	 * Read the MySQL version string.
398	 * @return version string
399	 */
400	function getMySQLVersion()
401	{
402		static $version = '';
403		if (empty($version)) {
404			$result = $this->query('select version() as Version');
405			if ($result) {
406				$res = $result->fetchRow();
407				$version  = $res['Version'];
408			}
409		}
410		return $version;
411	}
412	/**
413	 * Read the MySQL version number, e.g. 5.5
414	 * @return version float
415	 */
416	function getMySQLVersionNr()
417	{
418		$versionNr = 0.0;
419		$version = $this->getMySQLVersion();
420		$versionNr = (float)$version;
421		return $versionNr;
422	}
423
424	function listTables()
425	{
426		$result = $this->fetchAll("show tables");
427		$list = [];
428
429		if ($result) {
430			foreach ($result as $row) {
431				$list[] = reset($row);
432			}
433		}
434
435		return $list;
436	}
437
438	/*
439	*	isMySQLConnSSL
440	*	Check if MySQL is using an SSL connection
441	*	@return true if MySQL uses SSL. Otherwise false;
442	*/
443	function isMySQLConnSSL()
444	{
445		if (! $this->haveMySQLSSL()) {
446			return false;
447		}
448		$result = $this->query('show status like "Ssl_cipher"');
449		$ret = $result->fetchRow();
450		$cypher = $ret['Value'];
451		return ! empty($cypher);
452	}
453
454	/*
455	*	Check if the MySQL installation has SSL activated
456	*	@return true is SSL is supported and activated on the current MySQL server
457	*/
458	function haveMySQLSSL()
459	{
460		static $haveMySQLSSL = null;
461
462		if (! isset($haveMySQLSSL)) {
463			$result = $this->query('show variables like "have_ssl"');
464			$ret = $result->fetchRow();
465			if (empty($ret)) {
466				$result = $this->query('show variables like "have_openssl"');
467				$ret = $result->fetchRow();
468			}
469			if (! isset($ret)) {
470				$haveMySQLSSL = false;
471			}
472			$ssl = $ret['Value'];
473			if (empty($ssl)) {
474				$haveMySQLSSL = false;
475			} else {
476				$haveMySQLSSL = $ssl == 'YES';
477			}
478		}
479		return $haveMySQLSSL;
480	}
481
482
483	/**
484	 * Obtain a lock with a name given by the string $str, using a $timeout of timeout seconds
485	 * @param $str
486	 * @param int $timeout
487	 * @return bool if lock was created
488	 */
489	function getLock($str, $timeout = 1)
490	{
491		if ($this->isLocked($str)) {
492			return false;
493		}
494		$result = $this->getOne("SELECT GET_LOCK(?, ?) as isLocked", [$str, $timeout]);
495		return (bool)((int)$result);
496	}
497
498	/**
499	 * Releases the lock named by the string $str
500	 * @param $str
501	 * @return bool
502	 */
503	function releaseLock($str)
504	{
505		$result = $this->getOne("SELECT RELEASE_LOCK(?) as isReleased", [$str]);
506		return (bool)((int)$result);
507	}
508
509	/**
510	 * Checks whether the lock named $str is in use (that is, locked)
511	 * @param $str
512	 * @return bool
513	 */
514	function isLocked($str)
515	{
516		$result = $this->getOne("SELECT IS_USED_LOCK(?) as isLocked", [$str]);
517		return (bool)((int)$result);
518	}
519}
520