Namespaces

Classes

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