File classes\Database.php | PHPLucidFrame 3.3 API

1:	<?php
2:
3:	/**
4:	* This file is part of the PHPLucidFrame library.
5:	* Core utility and class required for file processing system
6:	*
7:	* @package PHPLucidFrame\File
8:	* @since PHPLucidFrame v 2.2.0
9:	* @copyright Copyright (c), PHPLucidFrame.
10:	* @link http://phplucidframe.com
11:	* @license http://www.opensource.org/licenses/mit-license.php MIT License
12:	*
13:	* This source file is subject to the MIT license that is bundled
14:	* with this source code in the file LICENSE
15:	*/
16:
17:	namespace LucidFrame\Core;
18:
19:	/**
20:	* This class is part of the PHPLucidFrame library.
21:	* Helper for file processing system
22:	*/
23:	class Database
24:	{
25:	private $namespace = 'default';
26:
27:	private $config = array();
28:
29:	private $driver = 'mysql';
30:	private $host;
31:	private $port;
32:	private $username;
33:	private $password;
34:	private $name;
35:	private $charset = 'utf8';
36:	private $collation = 'utf8_unicode_ci';
37:	private $prefix = '';
38:
39:	private $connection;
40:
41:	public $schemaManager;
42:
43:	private static $queries = array();
44:	private static $bindParams = array();
45:
46:	private $errorCode;
47:	private $error;
48:
49:	private static $FETCH_MODE_MAP = array(
50:	LC_FETCH_OBJECT => \PDO::FETCH_OBJ,
51:	LC_FETCH_ASSOC => \PDO::FETCH_ASSOC,
52:	LC_FETCH_ARRAY => \PDO::FETCH_NUM
53:	);
54:
55:	public function __construct($namespace = null)
56:	{
57:	$this->config = _cfg('databases');
58:	if ($namespace === null) {
59:	$this->namespace = _cfg('defaultDbSource');
60:	}
61:
62:	_app('db', $this);
63:
64:	$this->connect();
65:	}
66:
67:	/**
68:	* @return mixed
69:	*/
70:	public function getConnection()
71:	{
72:	return $this->connection;
73:	}
74:
75:	/**
76:	* @return mixed
77:	*/
78:	public function getSchemaManager()
79:	{
80:	return $this->schemaManager;
81:	}
82:
83:	/**
84:	* @param mixed $schemaManager
85:	*/
86:	public function setSchemaManager($schemaManager)
87:	{
88:	$this->schemaManager = $schemaManager;
89:	}
90:
91:	/**
92:	* Start database connection
93:	* @param string $namespace
94:	* @return \PDO or PDOException
95:	*/
96:	public function connect($namespace = null)
97:	{
98:	if ($namespace) {
99:	$this->namespace = $namespace;
100:	}
101:
102:	$this->driver = $this->getDriver();
103:	$this->host = $this->getHost();
104:	$this->port = $this->getPort();
105:	$this->username = $this->getUser();
106:	$this->password = $this->getPassword();
107:	$this->name = $this->getName();
108:	$this->prefix = $this->getPrefix();
109:	$this->charset = $this->getCharset();
110:	$this->collation = $this->getCollation();
111:
112:	if ($this->driver) {
113:	if ($file = _i('helpers' . _DS_ . 'db_helper.php', false)) {
114:	include $file;
115:	}
116:
117:	if ($this->driver === 'mysql') {
118:	require HELPER . 'db_helper.mysqli.php';
119:	} else {
120:	require HELPER . 'db_helper.' . $this->driver . '.php';
121:	}
122:
123:	if ($this->getHost() && $this->getUser() && $this->getName()) {
124:	# Start DB connection
125:	$dsn = sprintf('%s:host=%s;dbname=%s;charset=%s', $this->driver, $this->host, $this->name, $this->charset);
126:	$options = array(
127:	\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
128:	\PDO::MYSQL_ATTR_INIT_COMMAND => sprintf('SET NAMES %s COLLATE %s', $this->charset, $this->collation)
129:	);
130:
131:	$this->connection = new \PDO($dsn, $this->username, $this->password, $options);
132:
133:	# Load the schema of the currently connected database
134:	$schema = _schema($this->namespace, true);
135:	$this->schemaManager = new SchemaManager($schema);
136:	if (!$this->schemaManager->isLoaded()) {
137:	$this->schemaManager->build($namespace);
138:	}
139:	}
140:	}
141:
142:	return $this->connection;
143:	}
144:
145:	/**
146:	* Return the current database namespace
147:	* if $namespace is not provided, $lc_defaultDbSource will be returned
148:	* if $lc_defaultDbSource is empty, `default` will be returned
149:	*
150:	* @param string $namespace The given namespace
151:	* @return string The database namespace
152:	*/
153:	public function getNamespace($namespace = null)
154:	{
155:	if (!empty($namespace)) {
156:	return $namespace;
157:	}
158:
159:	return $this->namespace;
160:	}
161:
162:	/**
163:	* Return the database configuration of the given namespace
164:	* @param string $namespace Namespace of the configuration to read from
165:	* @return array The array of database configuration
166:	*/
167:	public function getConfig($namespace = null)
168:	{
169:	$namespace = $this->getNamespace($namespace);
170:
171:	if (!isset($this->config[$namespace])) {
172:	die('Database configuration error for ' . $namespace . '!');
173:	}
174:
175:	return $this->config[$namespace];
176:	}
177:
178:	/**
179:	* Return the database driver of the given namespace
180:	* @param string $namespace Namespace of the configuration to read from
181:	* @return string Database driver name
182:	*/
183:	public function getDriver($namespace = null)
184:	{
185:	$conf = $this->getConfig($this->getNamespace($namespace));
186:
187:	return $conf['driver'];
188:	}
189:
190:	/**
191:	* Return the database host name of the given namespace
192:	* @param string $namespace Namespace of the configuration to read from
193:	* @return string Database host name
194:	*/
195:	public function getHost($namespace = null)
196:	{
197:	$conf = $this->getConfig($this->getNamespace($namespace));
198:
199:	return $conf['host'];
200:	}
201:
202:	/**
203:	* Return the database port of the given namespace
204:	* @param string $namespace Namespace of the configuration to read from
205:	* @return string Database port
206:	*/
207:	public function getPort($namespace = null)
208:	{
209:	$conf = $this->getConfig($this->getNamespace($namespace));
210:
211:	return $conf['port'];
212:	}
213:
214:	/**
215:	* Return the database name of the given namespace
216:	* @param string $namespace Namespace of the configuration to read from
217:	* @return string Database name
218:	*/
219:	public function getName($namespace = null)
220:	{
221:	$conf = $this->getConfig($this->getNamespace($namespace));
222:	if (!isset($conf['database'])) {
223:	die('Database name is not set.');
224:	}
225:
226:	return $conf['database'];
227:	}
228:
229:	/**
230:	* Return the database user name of the given namespace
231:	* @param string $namespace Namespace of the configuration to read from
232:	* @return string Database username
233:	*/
234:	public function getUser($namespace = null)
235:	{
236:	$conf = $this->getConfig($this->getNamespace($namespace));
237:	if (!isset($conf['username'])) {
238:	die('Database username is not set.');
239:	}
240:
241:	return $conf['username'];
242:	}
243:
244:	/**
245:	* Return the database password of the given namespace
246:	* @param string $namespace Namespace of the configuration to read from
247:	* @return string Database password
248:	*/
249:	private function getPassword($namespace = null)
250:	{
251:	$conf = $this->getConfig($this->getNamespace($namespace));
252:	if (!isset($conf['password'])) {
253:	die('Database password is not set.');
254:	}
255:
256:	return $conf['password'];
257:	}
258:
259:	/**
260:	* Return the database table prefix of the given namespace
261:	* @param string $namespace Namespace of the configuration to read from
262:	* @return string The table prefix
263:	*/
264:	public function getPrefix($namespace = null)
265:	{
266:	$conf = $this->getConfig($this->getNamespace($namespace));
267:
268:	return isset($conf['prefix']) ? $conf['prefix'] : $this->prefix;
269:	}
270:
271:	/**
272:	* Return the database charset of the given namespace
273:	* @param string $namespace Namespace of the configuration to read from
274:	* @return string Database charset
275:	*/
276:	public function getCharset($namespace = null)
277:	{
278:	$conf = $this->getConfig($this->getNamespace($namespace));
279:
280:	return isset($conf['charset']) ? $conf['charset'] : $this->charset;
281:	}
282:
283:	/**
284:	* Return the database collation of the given namespace
285:	* @param string $namespace Namespace of the configuration to read from
286:	* @return string Database collation
287:	*/
288:	public function getCollation($namespace = null)
289:	{
290:	$conf = $this->getConfig($this->getNamespace($namespace));
291:
292:	return isset($conf['collation']) ? $conf['collation'] : $this->collation;
293:	}
294:
295:	/**
296:	* Perform a query on the database
297:	*
298:	* @param string $sql SQL query string
299:	* @param array $args Array of placeholders and their values
300:	* array(
301:	* ':placeholder1' => $value1,
302:	* ':placeholder2' => $value2
303:	* )
304:	* The prefix colon ":" for placeholder is optional
305:	*
306:	* @return mixed PDOStatement\|boolean\|string
307:	*/
308:	public function query($sql, $args = array())
309:	{
310:	if (!is_array($args)) {
311:	$args = array();
312:	}
313:
314:	$params = array();
315:	foreach ($args as $key => $value) {
316:	if (is_numeric($key)) {
317:	$params[$key] = $value;
318:	continue;
319:	}
320:
321:	if (strpos($key, ':') === false) {
322:	$key = ':' . $key;
323:	}
324:
325:	$params[$key] = $value;
326:	}
327:
328:	try {
329:	if (empty($params)) {
330:	$stmt = $this->connection->query($sql);
331:	self::$queries[] = $sql;
332:	} else {
333:	$stmt = $this->connection->prepare($sql);
334:	$stmt->execute($params);
335:	self::$queries[] = $sql;
336:	self::$bindParams = $params;
337:	}
338:
339:	if (_g('db_printQuery')) {
340:	return $this->getQueryStr();
341:	}
342:	} catch (\PDOException $e) {
343:	$this->errorCode = $e->getCode();
344:	$this->error = $e->getMessage();
345:
346:	throw $e;
347:	}
348:
349:	return $stmt;
350:	}
351:
352:	/**
353:	* Get the last executed SQL string or one of the executed SQL strings by providing the index
354:	*
355:	* @param int The index number of the query returned; if not given, the last query is returned
356:	* @return string Return the built and executed SQL string
357:	*/
358:	public function getQueryStr()
359:	{
360:	$arg = func_get_args();
361:	$index = count($arg) == 0 ? count(self::$queries) - 1 : 0;
362:
363:	$sql = isset(self::$queries[$index]) ? self::$queries[$index] : '';
364:
365:	if ($sql && count(self::$bindParams)) {
366:	foreach (self::$bindParams as $key => $value) {
367:	if (strpos($key, ':') === false) {
368:	$key = ':'.$key;
369:	}
370:
371:	if (is_array($value)) {
372:	$value = implode(',', $value);
373:	$regex = '/'.$key.'\b/i';
374:	$sql = preg_replace($regex, $value, $sql);
375:	} else {
376:	$regex = '/'.$key.'\b/i';
377:	$sql = preg_replace($regex, $value, $sql);
378:	}
379:	}
380:	}
381:
382:	return $sql;
383:	}
384:
385:	/**
386:	* Fetch a result row as an associative array
387:	* @param \PDOStatement $stmt
388:	* @return array\|false An associative array that corresponds to the fetched row or NULL if there are no more rows.
389:	*/
390:	public function fetchAssoc($stmt)
391:	{
392:	return $stmt ? $stmt->fetch(\PDO::FETCH_ASSOC) : false;
393:	}
394:
395:	/**
396:	* Fetch a result row as an associative, a numeric array, or both
397:	* @param \PDOStatement $stmt
398:	* @return array\|false An array that corresponds to the fetched row or
399:	* NULL if there are no more rows for the result set represented by the result parameter.
400:	*/
401:	public function fetchArray($stmt)
402:	{
403:	return $stmt ? $stmt->fetch(\PDO::FETCH_NUM) : false;
404:	}
405:
406:	/**
407:	* Returns the current row of a result set as an object
408:	* @param \PDOStatement $stmt
409:	* @return object\|false An object that corresponds to the fetched row or NULL if there are no more rows in result set.
410:	*/
411:	public function fetchObject($stmt)
412:	{
413:	return $stmt ? $stmt->fetch(\PDO::FETCH_OBJ) : false;
414:	}
415:
416:	/**
417:	* Perform a query on the database and return the array of all results
418:	*
419:	* @param string $sql The SQL query string
420:	* @param array $args The array of placeholders and their values
421:	* @param int $resultType The optional constant indicating what type of array should be produced.
422:	* The possible values for this parameter are the constants
423:	* LC_FETCH_OBJECT, LC_FETCH_ASSOC, or LC_FETCH_ARRAY.
424:	* Default to LC_FETCH_OBJECT.
425:	*
426:	* @return array\|boolean The result array of objects or associated arrays or index arrays.
427:	* If the result not found, return false.
428:	*/
429:	public function fetchAll($sql, $args = array(), $resultType = LC_FETCH_OBJECT)
430:	{
431:	if (is_numeric($args)) {
432:	if (in_array($args, array(LC_FETCH_OBJECT, LC_FETCH_ASSOC, LC_FETCH_ARRAY))) {
433:	$resultType = $args;
434:	}
435:	$args = array();
436:	}
437:
438:	$stmt = $this->query($sql, $args);
439:	$data = $stmt->fetchAll(self::$FETCH_MODE_MAP[$resultType]);
440:
441:	return count($data) ? $data : false;
442:	}
443:
444:	/**
445:	* Perform a query on the database and return the first result row as object
446:	*
447:	* It adds the `LIMIT 1` clause if the query has no record limit
448:	* This is useful for one-row fetching. No need explicit `db_query()` call as this invokes it internally.
449:	*
450:	* @param string $sql The SQL query string
451:	* @param array $args The array of placeholders and their values
452:	*
453:	* array(
454:	* ':placeholder1' => $value1,
455:	* ':placeholder2' => $value2
456:	* )
457:	*
458:	* @return object\|boolean The result object
459:	*/
460:	function fetchResult($sql, $args = array())
461:	{
462:	$sql = $this->appendLimit($sql);
463:
464:	if ($result = $this->query($sql, $args)) {
465:	if ($row = $this->fetchObject($result)) {
466:	return $row;
467:	}
468:	}
469:
470:	return false;
471:	}
472:
473:	/**
474:	* Perform a query on the database and return the first field value only.
475:	*
476:	* It adds the `LIMIT 1` clause if the query has no record limit
477:	* This will be useful for `COUNT()`, `MAX()`, `MIN()` queries
478:	*
479:	* @param string $sql The SQL query string
480:	* @param array $args The array of placeholders and their values
481:	*
482:	* array(
483:	* ':placeholder1' => $value1,
484:	* ':placeholder2' => $value2
485:	* )
486:	*
487:	* @return mixed The value of the first field
488:	*/
489:	public function fetchColumn($sql, $args = array())
490:	{
491:	$sql = $this->appendLimit($sql);
492:
493:	if ($result = $this->query($sql, $args)) {
494:	return $result->fetchColumn();
495:	}
496:
497:	return false;
498:	}
499:
500:	/**
501:	* Gets the number of rows in a result
502:	* @param \PDOStatement $stmt
503:	* @return int Returns the number of rows in the result set.
504:	*/
505:	public function getNumRows($stmt)
506:	{
507:	return $stmt->rowCount();
508:	}
509:
510:	/**
511:	* Perform a count query on the database and return the count
512:	*
513:	* @param string $arg1 The SQL query string or table name
514:	* @param string\|array $arg2 The field name to count on
515:	* or the array of placeholders and their values if the first argument is SQL
516:	*
517:	* array(
518:	* ':placeholder1' => $value1,
519:	* ':placeholder2' => $value2
520:	* )
521:	*
522:	* @param string\|null $arg3 The field alias if the first argument is table name
523:	* or the second argument is field name
524:	*
525:	* @return int\|QueryBuilder The result count
526:	*/
527:	public function getCount($arg1, $arg2 = null, $arg3 = null)
528:	{
529:	QueryBuilder::clearBindValues();
530:
531:	if ($arg1 && QueryBuilder::validateName($arg1)) {
532:	$table = $arg1;
533:	$alias = 'count';
534:
535:	$qb = new QueryBuilder($table);
536:
537:	if ($arg3 && QueryBuilder::validateName($arg3)) {
538:	$alias = $arg3;
539:	}
540:
541:	if ($arg2 && QueryBuilder::validateName($arg2)) {
542:	$field = $arg2;
543:	$qb->count($field, $alias);
544:	} else {
545:	$qb->count('*', 'count');
546:	}
547:
548:	return $qb;
549:	} else {
550:	$sql = $arg1;
551:	$args = $arg2;
552:
553:	if ($result = $this->fetchColumn($sql, $args)) {
554:	return $result;
555:	}
556:	}
557:
558:	return 0;
559:	}
560:
561:	/**
562:	* Returns the auto generated id used in the last query
563:	* @return int The value of the `AUTO_INCREMENT` field that was updated by the previous query;
564:	* `0` if there was no previous query on the connection or if the query did not update an `AUTO_INCREMENT` value.
565:	*/
566:	public function getInsertId()
567:	{
568:	return $this->connection ? $this->connection->lastInsertId() : 0;
569:	}
570:
571:	/**
572:	* Returns a string description of the last error
573:	* @return string
574:	*/
575:	public function getError()
576:	{
577:	return $this->error;
578:	}
579:
580:	/**
581:	* Returns the error code for the most recent query function call
582:	* @return int
583:	*/
584:	public function getErrorCode()
585:	{
586:	return $this->errorCode;
587:	}
588:
589:	/**
590:	* Closes a previously opened database connection
591:	* @return void
592:	*/
593:	public function close()
594:	{
595:	$this->connection = null;
596:	}
597:
598:	/**
599:	* Get the full table name with prefix
600:	* @param string $table The table name with or without prefix
601:	* @return string The table name with prefix
602:	*/
603:	public function getTable($table)
604:	{
605:	$prefix = $this->getPrefix();
606:
607:	if (empty($prefix)) {
608:	return $table;
609:	}
610:
611:	if ($prefix == substr($table, 0, strlen($prefix))) {
612:	return $table;
613:	}
614:
615:	return $prefix . $table;
616:	}
617:
618:	/**
619:	* Check the table has slug field
620:	*
621:	* @param string $table The table name without prefix
622:	* @param boolean $useSlug True to include the slug field or False to not exclude it
623:	* @return boolean true or false
624:	*/
625:	public function hasSlug($table, $useSlug = true)
626:	{
627:	if ($useSlug == false) {
628:	return false;
629:	}
630:
631:	return $this->schemaManager->hasSlug($table);
632:	}
633:
634:	/**
635:	* Check the table has timestamp fields
636:	*
637:	* @param string $table The table name without prefix
638:	* @return boolean true or false
639:	*/
640:	public function hasTimestamps($table)
641:	{
642:	return $this->schemaManager->hasTimestamps($table);
643:	}
644:
645:	/**
646:	* Build the SQL expression like SUM, MAX, AVG, etc
647:	*
648:	* @param string $field The field name
649:	* @param mixed $value The value for the field
650:	* @param string $exp The SQL expression
651:	* @return array The condition array, for example
652:	*
653:	* array(
654:	* 'value' => $value,
655:	* 'exp >=' => $exp,
656:	* 'field => $field
657:	* )
658:	*
659:	*/
660:	public function exp($field, $value, $exp = '')
661:	{
662:	if ($exp) {
663:	$field = strtoupper($field) . '(' . $value . ')';
664:	} else {
665:	$field = '';
666:	}
667:
668:	return array(
669:	'value' => $value,
670:	'exp' => $exp,
671:	'field' => $field
672:	);
673:	}
674:
675:	/**
676:	* Append LIMIT clause to the SQL statement
677:	* @param string $sql The SQL statement
678:	* @return string
679:	*/
680:	private function appendLimit($sql)
681:	{
682:	if (! preg_match('/LIMIT\s+[0-9]{1,}\b/i', $sql)) {
683:	$sql .= ' LIMIT 1';
684:	}
685:
686:	return $sql;
687:	}
688:	}
689:

Namespaces

Classes