File system/Db/Pdo/MySql.php | Afterlogic WebMail Pro API

1:	<?php
2:	/**
3:	* This code is licensed under AGPLv3 license or Afterlogic Software License
4:	* if commercial version of the product was purchased.
5:	* For full statements of the licenses see LICENSE-AFTERLOGIC and LICENSE-AGPL3 files.
6:	*/
7:
8:	namespace Aurora\System\Db\Pdo;
9:
10:	use Aurora\System\Exceptions\DbException;
11:
12:	/**
13:	* @license https://www.gnu.org/licenses/agpl-3.0.html AGPL-3.0
14:	* @license https://afterlogic.com/products/common-licensing Afterlogic Software License
15:	* @copyright Copyright (c) 2019, Afterlogic Corp.
16:	*
17:	* @package Api
18:	* @subpackage Db
19:	*/
20:	class MySql extends \Aurora\System\Db\Sql
21:	{
22:	/**
23:	* @var bool
24:	*/
25:	protected $bUseExplain;
26:
27:	/**
28:	* @var bool
29:	*/
30:	protected $bUseExplainExtended;
31:
32:	/**
33:	* @var PDO database object
34:	*/
35:	protected $oPDO;
36:
37:	/**
38:	* @var PDO resource
39:	*/
40:	protected $rResultId;
41:
42:	/**
43:	* @var bool
44:	*/
45:	public static $bUseReconnect = false;
46:
47:	/**
48:	* @param string $sHost
49:	* @param string $sUser
50:	* @param string $sPassword
51:	* @param string $sDbName
52:	* @param string $sDbTablePrefix = ''
53:	*/
54:	public function __construct($sHost, $sUser, $sPassword, $sDbName, $sDbTablePrefix = '')
55:	{
56:	$this->sHost = trim($sHost);
57:	$this->sUser = trim($sUser);
58:	$this->sPassword = trim($sPassword);
59:	$this->sDbName = trim($sDbName);
60:	$this->sDbTablePrefix = trim($sDbTablePrefix);
61:
62:	$this->oPDO = null;
63:	$this->rResultId = null;
64:
65:	$this->iExecuteCount = 0;
66:	$oSettings =& \Aurora\System\Api::GetSettings();
67:	$this->bUseExplain = $oSettings->GetValue('DBUseExplain', false);
68:	$this->bUseExplainExtended = $oSettings->GetValue('DBUseExplainExtended', false);
69:	}
70:
71:	/**
72:	* @param string $sHost
73:	* @param string $sUser
74:	* @param string $sPassword
75:	* @param string $sDbName
76:	*/
77:	public function ReInitIfNotConnected($sHost, $sUser, $sPassword, $sDbName)
78:	{
79:	if (!$this->IsConnected()) {
80:	$this->sHost = trim($sHost);
81:	$this->sUser = trim($sUser);
82:	$this->sPassword = trim($sPassword);
83:	$this->sDbName = trim($sDbName);
84:	}
85:	}
86:
87:	/**
88:	* @param bool $bWithSelect = true
89:	* @return bool
90:	*/
91:	public function Connect($bWithSelect = true, $bNewLink = false)
92:	{
93:	if (!class_exists('PDO')) {
94:	throw new DbException('Can\'t load PDO extension.', 0);
95:	}
96:
97:	$mPdoDrivers = \PDO::getAvailableDrivers();
98:	if (!is_array($mPdoDrivers) \|\| !in_array('mysql', $mPdoDrivers)) {
99:	throw new DbException('Can\'t load PDO mysql driver.', 0);
100:	}
101:
102:	if (strlen($this->sHost) == 0 \|\| strlen($this->sUser) == 0 \|\| strlen($this->sDbName) == 0) {
103:	throw new DbException('Not enough details required to establish connection.', 0);
104:	}
105:
106:	if (\Aurora\System\Api::$bUseDbLog) {
107:	\Aurora\System\Logger::LogSql('DB(PDO/mysql) : start connect to '.$this->sUser.'@'.$this->sHost);
108:	}
109:
110:	$aPDOAttr = array(\PDO::ATTR_TIMEOUT => 5, \PDO::ATTR_EMULATE_PREPARES => false, \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION);
111:	if (defined('PDO::MYSQL_ATTR_MAX_BUFFER_SIZE')) {
112:	$aPDOAttr[\PDO::MYSQL_ATTR_MAX_BUFFER_SIZE] = 1024102450;
113:	}
114:
115:	if (defined('PDO::MYSQL_ATTR_USE_BUFFERED_QUERY')) {
116:	$aPDOAttr[\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = true;
117:	}
118:
119:	$sDbPort = '';
120:	$sUnixSocket = '';
121:
122:	$sDbHost = $this->sHost;
123:	$sDbName = $this->sDbName;
124:	$sDbLogin = $this->sUser;
125:	$sDbPassword = $this->sPassword;
126:
127:	$iPos = strpos($sDbHost, ':');
128:	if (false !== $iPos && 0 < $iPos) {
129:	$sAfter = substr($sDbHost, $iPos + 1);
130:	$sDbHost = substr($sDbHost, 0, $iPos);
131:
132:	if (is_numeric($sAfter)) {
133:	$sDbPort = $sAfter;
134:	} else {
135:	$sUnixSocket = $sAfter;
136:	}
137:	}
138:
139:	$this->oPDO = false;
140:	if (class_exists('PDO')) {
141:	try {
142:	$aParts = array();
143:	if ($bWithSelect && 0 < strlen($sDbName)) {
144:	$aParts[] = 'dbname='.$sDbName;
145:	}
146:	if (0 < strlen($sDbHost)) {
147:	$aParts[] = 'host='.$sDbHost;
148:	}
149:	if (0 < strlen($sDbPort)) {
150:	$aParts[] = 'port='.$sDbPort;
151:	}
152:	if (0 < strlen($sUnixSocket)) {
153:	$aParts[] = 'unix_socket='.$sUnixSocket;
154:	}
155:	$aParts[] = 'charset=utf8';
156:
157:	$sPdoString = 'mysql:'.implode(';', $aParts);
158:	if (\Aurora\System\Api::$bUseDbLog) {
159:	\Aurora\System\Logger::LogSql('DB : PDO('.$sPdoString.')');
160:	}
161:
162:	$this->oPDO = @new \PDO($sPdoString, $sDbLogin, $sDbPassword, $aPDOAttr);
163:	if (\Aurora\System\Api::$bUseDbLog) {
164:	\Aurora\System\Logger::LogSql('DB : connected to '.$this->sUser.'@'.$this->sHost);
165:	}
166:
167:	if ($this->oPDO) {
168:	@register_shutdown_function(array(&$this, 'Disconnect'));
169:	}
170:	} catch (\Exception $oException) {
171:	\Aurora\System\Logger::LogSql($oException->getMessage(), \Aurora\System\Enums\LogLevel::Error);
172:	\Aurora\System\Logger::LogSql($oException->getTraceAsString(), \Aurora\System\Enums\LogLevel::Error);
173:	$this->oPDO = false;
174:
175:	throw new DbException($oException->getMessage(), $oException->getCode(), $oException);
176:	}
177:	} else {
178:	\Aurora\System\Logger::LogSql('Class PDO dosn\'t exist', \Aurora\System\Enums\LogLevel::Error);
179:	}
180:
181:	return !!$this->oPDO;
182:	}
183:
184:	/**
185:	* @return bool
186:	*/
187:	public function ConnectNoSelect()
188:	{
189:	return $this->Connect(false);
190:	}
191:
192:	/**
193:	* @return bool
194:	*/
195:	public function Select()
196:	{
197:	return $this->oPDO != null;
198:	}
199:
200:	/**
201:	* @return bool
202:	*/
203:	public function Disconnect()
204:	{
205:	$result = false;
206:	if ($this->oPDO) {
207:	if (is_resource($this->rResultId)) {
208:	$this->rResultId->closeCursor();
209:	}
210:
211:	$this->rResultId = null;
212:
213:	if (\Aurora\System\Api::$bUseDbLog) {
214:	\Aurora\System\Logger::LogSql('DB : disconnect from '.$this->sUser.'@'.$this->sHost);
215:	}
216:
217:	unset($this->oPDO);
218:	$this->oPDO = null;
219:	$result = true;
220:	}
221:
222:	return $result;
223:	}
224:
225:	/**
226:	* @return bool
227:	*/
228:	public function IsConnected()
229:	{
230:	return !!$this->oPDO;
231:	}
232:
233:	/** @param $sQuery
234:	@return false or PDOStatement
235:	*/
236:	private function SilentQuery($sQuery)
237:	{
238:	if (!$this->oPDO) {
239:	return false;
240:	}
241:
242:	try {
243:	$res = $this->oPDO->query($sQuery);
244:	return $res;
245:	} catch (\Exception $e) {
246:	\Aurora\System\Logger::LogSql($sQuery);
247:	}
248:
249:	return false;
250:	}
251:
252:	/**
253:	* @param string $sQuery
254:	* @param string $bIsSlaveExecute = false
255:	* @return bool
256:	*/
257:	public function Execute($sQuery, $bIsSlaveExecute = false)
258:	{
259:	$sExplainLog = '';
260:	$sQuery = trim($sQuery);
261:	if (self::$bUseReconnect) {
262:	$this->Reconnect();
263:	}
264:	if (($this->bUseExplain \|\| $this->bUseExplainExtended) && 0 === strpos($sQuery, 'SELECT')) {
265:	$sExplainQuery = 'EXPLAIN ';
266:	$sExplainQuery .= ($this->bUseExplainExtended) ? 'extended '.$sQuery : $sQuery;
267:
268:	$rExplainResult = $this->SilentQuery($sExplainQuery);
269:	if ($rExplainResult != false) {
270:	while (false != ($mResult = $rExplainResult->fetch(\PDO::FETCH_ASSOC))) {
271:	$sExplainLog .= AU_API_CRLF.print_r($mResult, true);
272:	}
273:
274:	$rExplainResult->closeCursor();
275:	}
276:
277:	if ($this->bUseExplainExtended) {
278:	$rExplainResult = $this->SilentQuery('SHOW warnings');
279:	if ($rExplainResult != false) {
280:	while (false != ($mResult = $rExplainResult->fetch(\PDO::FETCH_ASSOC))) {
281:	$sExplainLog .= AU_API_CRLF.print_r($mResult, true);
282:	}
283:
284:	$rExplainResult->closeCursor();
285:	}
286:	}
287:	}
288:
289:	$this->iExecuteCount++;
290:	$this->log($sQuery, $bIsSlaveExecute);
291:	if (!empty($sExplainLog)) {
292:	$this->log('EXPLAIN:'.AU_API_CRLF.trim($sExplainLog), $bIsSlaveExecute);
293:	}
294:
295:	$this->rResultId = $this->SilentQuery($sQuery);
296:	if ($this->rResultId === false) {
297:	$this->_setSqlError();
298:	}
299:
300:	return $this->rResultId !== false;
301:	}
302:
303:	/**
304:	* @param bool $bAutoFree = true
305:	* @return &object
306:	*/
307:	public function &GetNextRecord($bAutoFree = true)
308:	{
309:	if ($this->rResultId) {
310:	$mResult = $this->rResultId->fetch(\PDO::FETCH_OBJ);
311:	if (!$mResult && $bAutoFree) {
312:	$this->FreeResult();
313:	}
314:
315:	return $mResult;
316:	} else {
317:	$nNull = false;
318:	$this->_setSqlError();
319:	return $nNull;
320:	}
321:	}
322:
323:	/**
324:	* @param bool $bAutoFree = true
325:	* @return &array
326:	*/
327:	public function &GetNextArrayRecord($bAutoFree = true)
328:	{
329:	if ($this->rResultId) {
330:	$mResult = $this->rResultId->fetch(\PDO::FETCH_ASSOC);
331:	if (!$mResult && $bAutoFree) {
332:	$this->FreeResult();
333:	}
334:	return $mResult;
335:	} else {
336:	$nNull = false;
337:	$this->_setSqlError();
338:	return $nNull;
339:	}
340:	}
341:
342:	/**
343:	* @param string $sTableName = null
344:	* @param string $sFieldName = null
345:	* @return int
346:	*/
347:	public function GetLastInsertId($sTableName = null, $sFieldName = null)
348:	{
349:	try {
350:	return (int) $this->oPDO->lastInsertId();
351:	} catch(\Exception $e) {
352:	\Aurora\System\Api::LogException($e);
353:	}
354:
355:	return 0;
356:	}
357:
358:	/**
359:	* @return array
360:	*/
361:	public function GetTableNames()
362:	{
363:	if (!$this->Execute('SHOW TABLES')) {
364:	return false;
365:	}
366:
367:	$aResult = array();
368:	while (false !== ($aValue = $this->GetNextArrayRecord())) {
369:	foreach ($aValue as $sValue) {
370:	$aResult[] = $sValue;
371:	break;
372:	}
373:	}
374:
375:	return $aResult;
376:	}
377:
378:	/**
379:	* @param string $sTableName
380:	* @return array
381:	*/
382:	public function GetTableFields($sTableName)
383:	{
384:	if (!$this->Execute('SHOW COLUMNS FROM `'.$sTableName.'`')) {
385:	return false;
386:	}
387:
388:	$aResult = array();
389:	while (false !== ($oValue = $this->GetNextRecord())) {
390:	if ($oValue && isset($oValue->Field) && 0 < strlen($oValue->Field)) {
391:	$aResult[] = $oValue->Field;
392:	}
393:	}
394:
395:	return $aResult;
396:	}
397:
398:	/**
399:	* @param string $sTableName
400:	* @return array
401:	*/
402:	public function GetTableIndexes($sTableName)
403:	{
404:	if (!$this->Execute('SHOW INDEX FROM `'.$sTableName.'`')) {
405:	return false;
406:	}
407:
408:	$aResult = array();
409:	while (false !== ($oValue = $this->GetNextRecord())) {
410:	if ($oValue && isset($oValue->Key_name, $oValue->Column_name)) {
411:	if (!isset($aResult[$oValue->Key_name])) {
412:	$aResult[$oValue->Key_name] = array();
413:	}
414:	$aResult[$oValue->Key_name][] = $oValue->Column_name;
415:	}
416:	}
417:
418:	return $aResult;
419:	}
420:
421:	/**
422:	* @return bool
423:	*/
424:	public function FreeResult()
425:	{
426:	if ($this->rResultId) {
427:	if (!$this->rResultId->closeCursor()) {
428:	$this->_setSqlError();
429:	return false;
430:	} else {
431:	$this->rResultId = null;
432:	}
433:	}
434:	return true;
435:	}
436:
437:	/**
438:	* @return int
439:	*/
440:	public function ResultCount()
441:	{
442:	if ($this->rResultId) {
443:	return $this->rResultId->rowCount(); // Only works for MySQL
444:	// return $this->SilentQuery("SELECT FOUND_ROWS()")->fetchColumn();
445:	}
446:
447:	return 0;
448:	}
449:
450:	/**
451:	* @return void
452:	*/
453:	private function _setSqlError()
454:	{
455:	if ($this->IsConnected()) {
456:	$aEr = $this->oPDO->errorInfo();
457:	$this->ErrorDesc = (string) implode("\r\n", is_array($aEr) ? $aEr : array()).' ['.$this->oPDO->errorCode().']';
458:	$this->ErrorCode = 0;
459:	} else {
460:	$this->ErrorDesc = 'No connection';
461:	$this->ErrorCode = -23456789;
462:	}
463:
464:	if (0 < strlen($this->ErrorDesc)) {
465:	$this->errorLog($this->ErrorDesc);
466:	throw new \Aurora\System\Exceptions\DbException($this->ErrorDesc, $this->ErrorCode);
467:	}
468:	}
469:
470:	private function Reconnect()
471:	{
472:	try {
473:	$this->oPDO->query('SELECT 1');
474:	} catch (\PDOException $e) {
475:	$this->Connect();
476:	}
477:	}
478:	}
479:

Namespaces

Classes