1: <?php
  2:     /* QCubed Development Framework for PHP
  3:      * http://www.qcu.be/
  4:      *
  5:      * Copyright (C) 2006
  6:      * Justin Sinclair - The Sinclair Group, LLC - http://www.sinclairgroup.net/
  7:      *
  8:      * This program is free software; you can redistribute it and/or
  9:      * modify it under the terms of the GNU General Public License
 10:      * as published by the Free Software Foundation; either version 2
 11:      * of the License, or (at your option) any later version.
 12:      *
 13:      * This program is distributed in the hope that it will be useful,
 14:      * but WITHOUT ANY WARRANTY; without even the implied warranty of
 15:      * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 16:      * GNU General Public License for more details.
 17:      *
 18:      * You should have received a copy of the GNU General Public License
 19:      * along with this program; if not, write to the Free Software
 20:      * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 21:      */
 22: 
 23:     /**
 24:      * PostgreSQL database adapter.
 25:      *
 26:      * To allow QCubed to determine the identity column in a PostgreSQL table (and because a 
 27:      * table may have more than one column generated by the SERIAL "data type"), this adapter
 28:      * assumes that if the first column (ordinal position 1) was created as SERIAL, it is the 
 29:      * identity column.  Otherwise, no identity column will be set for that table.
 30:      *
 31:      * @package DatabaseAdapters
 32:      */     
 33:     class QPostgreSqlDatabase extends QDatabaseBase {
 34:         /** Adapter name */
 35:         const Adapter = 'PostgreSQL Database Adapter';
 36: 
 37:         protected $objPgSql;
 38:         protected $objMostRecentResult;
 39:         protected $blnOnlyFullGroupBy = true;
 40: 
 41:         public function SqlVariable($mixData, $blnIncludeEquality = false, $blnReverseEquality = false) {
 42:             // Are we SqlVariabling a BOOLEAN value?
 43:             if (is_bool($mixData)) {
 44:                 // Yes
 45:                 if ($blnIncludeEquality) {
 46:                     // We must include the inequality
 47: 
 48:                     if ($blnReverseEquality) {
 49:                         // Do a "Reverse Equality"
 50: 
 51:                         // Check against NULL, True then False
 52:                         if (is_null($mixData))
 53:                             return 'IS NOT NULL';
 54:                         else if ($mixData)
 55:                             return "= '0'";
 56:                         else
 57:                             return "!= '0'";
 58:                     } else {
 59:                         // Check against NULL, True then False
 60:                         if (is_null($mixData))
 61:                             return 'IS NULL';
 62:                         else if ($mixData)
 63:                             return "!= '0'";
 64:                         else
 65:                             return "= '0'";
 66:                     }
 67:                 } else {
 68:                     // Check against NULL, True then False
 69:                     if (is_null($mixData))
 70:                         return 'NULL';
 71:                     else if ($mixData)
 72:                         return "'1'";
 73:                     else
 74:                         return "'0'";
 75:                 }
 76:             }
 77: 
 78:             // Check for Equality Inclusion
 79:             if ($blnIncludeEquality) {
 80:                 if ($blnReverseEquality) {
 81:                     if (is_null($mixData))
 82:                         $strToReturn = 'IS NOT ';
 83:                     else
 84:                         $strToReturn = '!= ';
 85:                 } else {
 86:                     if (is_null($mixData))
 87:                         $strToReturn = 'IS ';
 88:                     else
 89:                         $strToReturn = '= ';
 90:                 }
 91:             } else
 92:                 $strToReturn = '';
 93: 
 94:             // Check for NULL Value
 95:             if (is_null($mixData))
 96:                 return $strToReturn . 'NULL';
 97: 
 98:             // Check for NUMERIC Value
 99:             if (is_integer($mixData) || is_float($mixData))
100:                 return $strToReturn . sprintf('%s', $mixData);
101: 
102:             // Check for DATE Value
103:             if ($mixData instanceof QDateTime) {
104:                 if ($mixData->IsTimeNull()) {
105:                     if ($mixData->IsDateNull()) {
106:                         return $strToReturn . 'NULL'; // null date and time is a null value
107:                     }
108:                     return  $strToReturn . sprintf("'%s'", $mixData->qFormat('YYYY-MM-DD'));
109:                 } elseif ($mixData->IsDateNull()) {
110:                     return  $strToReturn . sprintf("'%s'", $mixData->qFormat('hhhh:mm:ss'));
111:                 } else {
112:                     return  $strToReturn . sprintf("'%s'", $mixData->qFormat(QDateTime::FormatIso));
113:                 }
114:                 return $strToReturn . $s;
115:             }
116: 
117:             // Assume it's some kind of string value
118:             return $strToReturn . sprintf("'%s'", pg_escape_string($mixData));
119:         }
120:         
121:         public function SqlLimitVariablePrefix($strLimitInfo) {
122:             // PostgreSQL uses Limit by Suffixes (via a LIMIT clause)
123:             // Prefix is not used, therefore, return null
124:             return null;
125:         }
126: 
127:         public function SqlLimitVariableSuffix($strLimitInfo) {
128:             // Setup limit suffix (if applicable) via a LIMIT clause 
129:             if (strlen($strLimitInfo)) {
130:                 if (strpos($strLimitInfo, ';') !== false)
131:                     throw new Exception('Invalid Semicolon in LIMIT Info');
132:                 if (strpos($strLimitInfo, '`') !== false)
133:                     throw new Exception('Invalid Backtick in LIMIT Info');
134:                 
135:                 // First figure out if we HAVE an offset
136:                 $strArray = explode(',', $strLimitInfo);
137:                 
138:                 if (count($strArray) == 2) {
139:                     // Yep -- there's an offset
140:                     return sprintf('LIMIT %s OFFSET %s', $strArray[1], $strArray[0]);
141:                 } else if (count($strArray) == 1) {
142:                     return sprintf('LIMIT %s', $strArray[0]);
143:                 } else {
144:                     throw new QPostgreSqlDatabaseException('Invalid Limit Info: ' . $strLimitInfo, 0, null);
145:                 }
146:             }
147: 
148:             return null;
149:         }
150: 
151:         public function SqlSortByVariable($strSortByInfo) {
152:             // Setup sorting information (if applicable) via a ORDER BY clause
153:             if (strlen($strSortByInfo)) {
154:                 if (strpos($strSortByInfo, ';') !== false)
155:                     throw new Exception('Invalid Semicolon in ORDER BY Info');
156:                 if (strpos($strSortByInfo, '`') !== false)
157:                     throw new Exception('Invalid Backtick in ORDER BY Info');
158: 
159:                 return "ORDER BY $strSortByInfo";
160:             }
161:             
162:             return null;
163:         }
164: 
165:         public function InsertOrUpdate($strTable, $mixColumnsAndValuesArray, $strPKNames = null) {
166:             $strEscapedArray = $this->EscapeIdentifiersAndValues($mixColumnsAndValuesArray);
167:             $strColumns = array_keys($strEscapedArray);
168:             $strUpdateStatement = '';
169:             foreach ($strEscapedArray as $strColumn => $strValue) {
170:                 if ($strUpdateStatement) $strUpdateStatement .= ', ';
171:                 $strUpdateStatement .= $strColumn . ' = ' . $strValue;
172:             }
173:             if (is_null($strPKNames)) {
174:                 $strPKNames = array($strColumns[0]);
175:             } else if (is_array($strPKNames)) {
176:                 $strPKNames = $this->EscapeIdentifiers($strPKNames);
177:             } else {
178:                 $strPKNames = array($this->EscapeIdentifier($strPKNames));
179:             }
180:             $strMatchCondition = '';
181:             foreach ($strPKNames as $strPKName) {
182:                 if ($strMatchCondition) $strMatchCondition .= ' AND ';
183:                 $strMatchCondition .= $strPKName.' = '.$strEscapedArray[$strPKName];
184:             }
185:             $strTable = $this->EscapeIdentifierBegin . $strTable . $this->EscapeIdentifierEnd;
186:             $strUpdateSql = sprintf('UPDATE %s SET %s WHERE %s',
187:                 $strTable, $strUpdateStatement, $strMatchCondition);
188:             $strInsertSql = sprintf('INSERT INTO %s (%s) SELECT %s WHERE NOT EXISTS (SELECT 1 FROM %s WHERE %s)',
189:                 $strTable,
190:                 implode(', ', $strColumns),
191:                 implode(', ', array_values($strEscapedArray)),
192:                 $strTable, $strMatchCondition);
193:             $this->TransactionBegin();
194:             try {
195:                 $this->ExecuteNonQuery($strUpdateSql);
196:                 $this->ExecuteNonQuery($strInsertSql);
197:                 $this->TransactionCommit();
198:             } catch (Exception $ex) {
199:                 $this->TransactionRollback();
200:                 throw $ex;
201:             }
202:         }
203: 
204:         /**
205:          * Connects to the database
206:          *
207:          * @throws QPostgreSqlDatabaseException
208:          */
209:         public function Connect() {
210:             // Lookup Adapter-Specific Connection Properties
211:             $strServer = $this->Server;
212:             $strName = $this->Database;
213:             $strUsername = $this->Username;
214:             $strPassword = $this->Password;
215:             $strPort = $this->Port;
216: 
217:             // Connect to the Database Server
218:             $this->objPgSql = pg_connect(sprintf('host=%s dbname=%s user=%s password=%s port=%s',$strServer, $strName, $strUsername, $strPassword, $strPort));
219: 
220:             if (!$this->objPgSql)
221:                 throw new QPostgreSqlDatabaseException("Unable to connect to Database", -1, null);
222: 
223:             // Update Connected Flag
224:             $this->blnConnectedFlag = true;
225:         }
226: 
227:         public function __get($strName) {
228:             switch ($strName) {
229:                 case 'AffectedRows':
230:                     return pg_affected_rows($this->objMostRecentResult);
231:                 default:
232:                     try {
233:                         return parent::__get($strName);
234:                     } catch (QCallerException $objExc) {
235:                         $objExc->IncrementOffset();
236:                         throw $objExc;
237:                     }
238:             }
239:         }
240: 
241:         protected function ExecuteQuery($strQuery) {
242:             // Perform the Query
243:             $objResult = pg_query($this->objPgSql, $strQuery);
244:             if (!$objResult)
245:                 throw new QPostgreSqlDatabaseException(pg_last_error(), 0, $strQuery);
246:                 
247:             // Return the Result
248:             $this->objMostRecentResult = $objResult;
249:             $objPgSqlDatabaseResult = new QPostgreSqlDatabaseResult($objResult, $this);
250:             return $objPgSqlDatabaseResult;
251:         }
252: 
253:         protected function ExecuteNonQuery($strNonQuery) {
254:             // Perform the Query
255:             $objResult = pg_query($this->objPgSql, $strNonQuery);
256:             if (!$objResult)
257:                 throw new QPostgreSqlDatabaseException(pg_last_error(), 0, $strNonQuery);
258:             $this->objMostRecentResult = $objResult;
259:         }
260: 
261:         /**
262:          * Returns the list of tables in the database as string
263:          *
264:          * @return array List of tables in the database as string
265:          */
266:         public function GetTables() {
267:             $objResult = $this->Query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = current_schema() AND TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME ASC");
268:             $strToReturn = array();
269:             while ($strRowArray = $objResult->FetchRow())
270:                 array_push($strToReturn, $strRowArray[0]);
271:             return $strToReturn;
272:         }
273:         
274:         public function GetFieldsForTable($strTableName) {
275:             $strTableName = $this->SqlVariable($strTableName);
276:             $strQuery = sprintf('
277:                 SELECT 
278:                     columns.table_name,
279:                     columns.column_name,
280:                     columns.ordinal_position,
281:                     columns.column_default,
282:                     columns.is_nullable,
283:                     columns.data_type,
284:                     columns.character_maximum_length,
285:                     descr.description AS comment,
286:                     (pg_get_serial_sequence(columns.table_name,columns.column_name) IS NOT NULL) AS is_serial
287:                 FROM 
288:                     INFORMATION_SCHEMA.COLUMNS columns
289:                     JOIN pg_catalog.pg_class klass ON (columns.table_name = klass.relname AND klass.relkind = \'r\')
290:                     LEFT JOIN pg_catalog.pg_description descr ON (descr.objoid = klass.oid AND descr.objsubid = columns.ordinal_position)
291:                 WHERE 
292:                     columns.table_schema = current_schema()
293:                 AND
294:                     columns.table_name = %s
295:                 ORDER BY
296:                     ordinal_position
297:             ', $strTableName);
298:     
299:             $objResult = $this->Query($strQuery);
300: 
301:             $objFields = array();
302: 
303:             while ($objRow = $objResult->GetNextRow()) {
304:                 array_push($objFields, new QPostgreSqlDatabaseField($objRow, $this));
305:             }
306: 
307:             return $objFields;
308:         }
309: 
310:         public function InsertId($strTableName = null, $strColumnName = null) {
311:             $strQuery = sprintf('
312:                 SELECT currval(pg_get_serial_sequence(%s, %s))
313:             ', $this->SqlVariable($strTableName), $this->SqlVariable($strColumnName));
314:             
315:             $objResult = $this->Query($strQuery);
316:             $objRow = $objResult->FetchRow();
317:             return $objRow[0];
318:         }
319:         
320:         public function Close() {
321:             pg_close($this->objPgSql);
322: 
323:             // Update Connected Flag
324:             $this->blnConnectedFlag = false;
325:         }
326: 
327:         /**
328:          * Sends the 'BEGIN' command to the PostgreSQL server to start a transaction
329:          */
330:         protected function ExecuteTransactionBegin() {
331:             $this->NonQuery('BEGIN;');
332:         }
333: 
334:         /**
335:          * Sends the 'COMMIT' command to the PostgreSQL server to commit/end a transaction
336:          */
337:         protected function ExecuteTransactionCommit() {
338:             $this->NonQuery('COMMIT;');
339:         }
340: 
341:         /**
342:          * Sends the 'ROLLBACK' command to the PostgreSQL server to revert a transaction
343:          */
344:         protected function ExecuteTransactionRollBack() {
345:             $this->NonQuery('ROLLBACK;');
346:         }
347: 
348:         private function ParseColumnNameArrayFromKeyDefinition($strKeyDefinition) {
349:             $strKeyDefinition = trim($strKeyDefinition);
350:             
351:             // Get rid of the opening "(" and the closing ")"
352:             $intPosition = strpos($strKeyDefinition, '(');
353:             if ($intPosition === false)
354:                 throw new Exception("Invalid Key Definition: $strKeyDefinition");
355:             $strKeyDefinition = trim(substr($strKeyDefinition, $intPosition + 1));
356: 
357:             $intPosition = strpos($strKeyDefinition, ')');
358:             if ($intPosition === false)
359:                 throw new Exception("Invalid Key Definition: $strKeyDefinition");
360:             $strKeyDefinition = trim(substr($strKeyDefinition, 0, $intPosition));
361:             $strKeyDefinition = str_replace(" ","",$strKeyDefinition);
362:             
363:             // Create the Array
364:             // TODO: Current method doesn't support key names with commas or parenthesis in them!
365:             $strToReturn = explode(',', $strKeyDefinition);
366: 
367:             // Take out trailing and leading '"' character in each name (if applicable)
368:             for ($intIndex = 0; $intIndex < count($strToReturn); $intIndex++) {
369:                 $strColumn = $strToReturn[$intIndex];
370: 
371:                 if (substr($strColumn, 0, 1) == '"')
372:                     $strColumn = substr($strColumn, 1, strpos($strColumn, '"', 1) - 1);
373: 
374:                 $strToReturn[$intIndex] = $strColumn;
375:             }
376:             
377:             return $strToReturn;
378:         }
379:         
380:         public function GetIndexesForTable($strTableName) {
381:             $objIndexArray = array();
382:             
383:             $objResult = $this->Query(sprintf('
384:                 SELECT 
385:                     c2.relname AS indname, 
386:                     i.indisprimary, 
387:                     i.indisunique, 
388:                     pg_catalog.pg_get_indexdef(i.indexrelid) AS inddef 
389:                 FROM 
390:                     pg_catalog.pg_class c, 
391:                     pg_catalog.pg_class c2, 
392:                     pg_catalog.pg_index i
393:                 WHERE 
394:                     c.relname = %s 
395:                 AND 
396:                     pg_catalog.pg_table_is_visible(c.oid)
397:                 AND 
398:                     c.oid = i.indrelid 
399:                 AND 
400:                     i.indexrelid = c2.oid
401:                 ORDER BY 
402:                     c2.relname
403:             ', $this->SqlVariable($strTableName)));
404:             
405:             while ($objRow = $objResult->GetNextRow()) {
406:                 $strIndexDefinition = $objRow->GetColumn('inddef');
407:                 $strKeyName = $objRow->GetColumn('indname');
408:                 $blnPrimaryKey = ($objRow->GetColumn('indisprimary') === "t");
409:                 $blnUnique = ($objRow->GetColumn('indisunique') === "t");
410:                 $strColumnNameArray = $this->ParseColumnNameArrayFromKeyDefinition($strIndexDefinition);
411:                 
412:                 $objIndex = new QDatabaseIndex($strKeyName, $blnPrimaryKey, $blnUnique, $strColumnNameArray);
413:                 array_push($objIndexArray, $objIndex);
414:             }
415:             
416:             return $objIndexArray;
417:         }
418:         
419:         public function GetForeignKeysForTable($strTableName) {
420:             $objForeignKeyArray = array();
421:             
422:             // Use Query to pull the FKs
423:             $strQuery = sprintf('
424:                 SELECT
425:                     pc.conname,
426:                     pg_catalog.pg_get_constraintdef(pc.oid, true) AS consrc
427:                 FROM
428:                     pg_catalog.pg_constraint pc
429:                 WHERE
430:                     pc.conrelid = 
431:                     (
432:                         SELECT
433:                             oid 
434:                         FROM 
435:                             pg_catalog.pg_class 
436:                         WHERE
437:                             relname=%s
438:                         AND 
439:                             relnamespace = 
440:                             (
441:                                 SELECT 
442:                                     oid 
443:                                 FROM 
444:                                     pg_catalog.pg_namespace
445:                                 WHERE 
446:                                     nspname=current_schema()
447:                             )
448:                     )
449:                 AND 
450:                     pc.contype = \'f\'
451:             ', $this->SqlVariable($strTableName));
452:             
453:             $objResult = $this->Query($strQuery);
454:             
455:             while ($objRow = $objResult->GetNextRow()) {
456:                 $strKeyName = $objRow->GetColumn('conname');
457:                 
458:                 // Remove leading and trailing '"' characters (if applicable)
459:                 if (substr($strKeyName, 0, 1) == '"')
460:                     $strKeyName = substr($strKeyName, 1, strlen($strKeyName) - 2);
461: 
462:                 // By the end of the following lines, we will end up with a strTokenArray
463:                 // Index 1: the list of columns that are the foreign key
464:                 // Index 2: the table which this FK references
465:                 // Index 3: the list of columns which this FK references
466:                 $strTokenArray = explode('FOREIGN KEY ', $objRow->GetColumn('consrc'));
467:                 $strTokenArray[1] = explode(' REFERENCES ', $strTokenArray[1]);
468:                 $strTokenArray[2] = $strTokenArray[1][1];
469:                 $strTokenArray[1] = $strTokenArray[1][0];
470:                 $strTokenArray[2] = explode("(", $strTokenArray[2]);
471:                 $strTokenArray[3] = "(".$strTokenArray[2][1];
472:                 $strTokenArray[2] = $strTokenArray[2][0];
473:                 
474:                 // Remove leading and trailing '"' characters (if applicable)
475:                 if (substr($strTokenArray[2], 0, 1) == '"')
476:                     $strTokenArray[2] = substr($strTokenArray[2], 1, strlen($strTokenArray[2]) - 2);
477:                     
478:                 $strColumnNameArray = $this->ParseColumnNameArrayFromKeyDefinition($strTokenArray[1]);
479:                 $strReferenceTableName = $strTokenArray[2];
480:                 $strReferenceColumnNameArray = $this->ParseColumnNameArrayFromKeyDefinition($strTokenArray[3]);
481:                 
482:                 $objForeignKey = new QDatabaseForeignKey(
483:                     $strKeyName,
484:                     $strColumnNameArray,
485:                     $strReferenceTableName,
486:                     $strReferenceColumnNameArray);
487:                 array_push($objForeignKeyArray, $objForeignKey);
488:             }
489: 
490:             // Return the Array of Foreign Keys
491:             return $objForeignKeyArray;
492:         }
493: 
494:         public function ExplainStatement($sql) {
495:             return $this->Query("EXPLAIN " . $sql);
496:         }
497:     }
498: 
499:     /**
500:      * Database exception class
501:      *
502:      * @package DatabaseAdapters
503:      */
504:     class QPostgreSqlDatabaseException extends QDatabaseExceptionBase {
505:         /**
506:          * QPostgreSqlDatabaseException constructor.
507:          *
508:          * @param string $strMessage
509:          * @param int    $intNumber
510:          * @param string $strQuery
511:          */
512:         public function __construct($strMessage, $intNumber, $strQuery) {
513:             parent::__construct(sprintf("PostgreSql Error: %s", $strMessage), 2);
514:             $this->intErrorNumber = $intNumber;
515:             $this->strQuery = $strQuery;
516:         }
517:     }
518: 
519:     /**
520:      * Class to handle results sent by database upon querying
521:      * @package DatabaseAdapters
522:      */
523:     class QPostgreSqlDatabaseResult extends QDatabaseResultBase {
524:         protected $objPgSqlResult;
525:         protected $objDb;
526: 
527:         /**
528:          * QPostgreSqlDatabaseResult constructor.
529:          *
530:          * @param                     $objResult
531:          * @param QPostgreSqlDatabase $objDb
532:          */
533:         public function __construct($objResult, QPostgreSqlDatabase $objDb) {
534:             $this->objPgSqlResult = $objResult;
535:             $this->objDb = $objDb;
536:         }
537: 
538:         /**
539:          * Fetch result (single result) as array
540:          *
541:          * @return array
542:          */
543:         public function FetchArray() {
544:             return pg_fetch_array($this->objPgSqlResult);
545:         }
546: 
547:         /**
548:          * Fetch fields (currently just null)
549:          *
550:          * @return null
551:          */
552:         public function FetchFields() {
553:             return null;  // Not implemented
554:         }
555: 
556:         /**
557:          * Fetch field (currently just null)
558:          *
559:          * @return null
560:          */
561:         public function FetchField() {
562:             return null;  // Not implemented
563:         }
564: 
565:         /**
566:          * Fetch row
567:          *
568:          * @return array
569:          */
570:         public function FetchRow() {
571:             return pg_fetch_row($this->objPgSqlResult);
572:         }
573:         
574:         /**
575:          * Return number of rows in result
576:          * 
577:          * @return int
578:          */
579:         public function CountRows() {
580:             return pg_num_rows($this->objPgSqlResult);
581:         }
582:         
583:         /**
584:          * Return number of fields in a result
585:          * 
586:          * @return int
587:          */
588:         public function CountFields() {
589:             return pg_num_fields($this->objPgSqlResult);
590:         }
591: 
592:         /**
593:          * Free the memory. Connection closes when script ends
594:          */
595:         public function Close() {
596:             pg_free_result($this->objPgSqlResult);
597:         }
598: 
599:         /**
600:          * @return null|QPostgreSqlDatabaseRow
601:          */
602:         public function GetNextRow() {
603:             $strColumnArray = $this->FetchArray();
604:             
605:             if ($strColumnArray)
606:                 return new QPostgreSqlDatabaseRow($strColumnArray);
607:             else
608:                 return null;
609:         }
610: 
611:         /**
612:          * Returns all results in the result set as array
613:          * 
614:          * @return array
615:          */
616:         public function GetRows() {
617:             $objDbRowArray = array();
618:             while ($objDbRow = $this->GetNextRow())
619:                 array_push($objDbRowArray, $objDbRow);
620:             return $objDbRowArray;
621:         }
622:     }
623: 
624:     /**
625:      * Class for handling a single row from PostgreSQL database result set
626:      * 
627:      * @package DatabaseAdapters
628:      */
629:     class QPostgreSqlDatabaseRow extends QDatabaseRowBase {
630:         /** @var string[] Column name value pairs for current result set */
631:         protected $strColumnArray;
632:         
633:         /**
634:          * QPostgreSqlDatabaseRow constructor.
635:          *
636:          * @param string $strColumnArray
637:          */
638:         public function __construct($strColumnArray) {
639:             $this->strColumnArray = $strColumnArray;
640:         }
641: 
642:         /**
643:          * Gets the value of a column from a result row returned by the database
644:          *
645:          * @param string        $strColumnName Name of the column
646:          * @param null|string   $strColumnType Data type
647:          *
648:          * @return mixed
649:          */
650:         public function GetColumn($strColumnName, $strColumnType = null) {
651:             if (!isset($this->strColumnArray[$strColumnName])) {
652:                 return null;
653:             }
654:             $strColumnValue = $this->strColumnArray[$strColumnName];
655:             switch ($strColumnType) {
656:                 case QDatabaseFieldType::Bit:
657:                     // PostgreSQL returns 't' or 'f' for boolean fields
658:                     if ($strColumnValue == 'f') {
659:                         return false;
660:                     } else {
661:                         return ($strColumnValue) ? true : false;
662:                     }
663:                     
664:                 case QDatabaseFieldType::Blob:
665:                 case QDatabaseFieldType::Char:
666:                 case QDatabaseFieldType::VarChar:
667:                 case QDatabaseFieldType::Json: // JSON is basically String
668:                     return QType::Cast($strColumnValue, QType::String);
669:                 case QDatabaseFieldType::Date:
670:                 case QDatabaseFieldType::DateTime:
671:                 case QDatabaseFieldType::Time:
672:                     return new QDateTime($strColumnValue);
673: 
674:                 case QDatabaseFieldType::Float:
675:                     return QType::Cast($strColumnValue, QType::Float);
676: 
677:                 case QDatabaseFieldType::Integer:
678:                     return QType::Cast($strColumnValue, QType::Integer);
679: 
680:                 default:
681:                     return $strColumnValue;
682:             }
683:         }
684: 
685:         /**
686:          * Tells whether a particular column exists in a returned database row
687:          *
688:          * @param string $strColumnName Name of te column
689:          *
690:          * @return bool
691:          */
692:         public function ColumnExists($strColumnName) {
693:             return array_key_exists($strColumnName, $this->strColumnArray);
694:         }
695:         
696:         /**
697:          * @return string|string[]
698:          */
699:         public function GetColumnNameArray() {
700:             return $this->strColumnArray;
701:         }
702: 
703:         /**
704:          * Returns the boolean value corresponding to whatever a bit column returns. Postgres
705:          * returns a 't' or 'f' (or null).
706:          * @param bool|null $mixValue Value of the BIT column
707:          * @return bool
708:          */
709:         public function ResolveBooleanValue ($mixValue) {
710:             if ($mixValue == 'f') {
711:                 return false;
712:             } elseif ($mixValue == 't') {
713:                 return true;
714:             }
715:             else
716:                 return null;
717:         }
718:     }
719: 
720:     /**
721:      *
722:      * @package DatabaseAdapters
723:      */
724:     class QPostgreSqlDatabaseField extends QDatabaseFieldBase {
725:         /**
726:          * QPostgreSqlDatabaseField constructor.
727:          *
728:          * @param      $mixFieldData
729:          * @param null $objDb
730:          */
731:         public function __construct($mixFieldData, $objDb = null) {
732:             $this->strName = $mixFieldData->GetColumn('column_name');
733:             $this->strOriginalName = $this->strName;
734:             $this->strTable = $mixFieldData->GetColumn('table_name');
735:             $this->strOriginalTable = $this->strTable;
736:             $this->strDefault = $mixFieldData->GetColumn('column_default');
737:             $this->intMaxLength = $mixFieldData->GetColumn('character_maximum_length', QDatabaseFieldType::Integer);
738:             $this->blnNotNull = ($mixFieldData->GetColumn('is_nullable') == "NO") ? true : false;
739:             
740:             // If this column was created as SERIAL and is a simple (non-composite) primary key
741:             // then we assume it's the identity field.
742:             // Otherwise, no identity field will be set for this table.
743:             $this->blnIdentity = false;
744:             if ($mixFieldData->GetColumn('is_serial') == 't') {
745:                 $objIndexes = $objDb->GetIndexesForTable($this->strTable);
746:                 foreach ($objIndexes as $objIndex) {
747:                     if ($objIndex->PrimaryKey) {
748:                         $columns = $objIndex->ColumnNameArray;
749:                         $this->blnIdentity = (count($columns) == 1 && $columns[0] == $this->strName);
750:                         break;
751:                     }
752:                 }
753:             }
754: 
755:             // Determine Primary Key
756:             $objResult = $objDb->Query(sprintf('
757:                 SELECT 
758:                     kcu.column_name 
759:                 FROM 
760:                     information_schema.table_constraints tc, 
761:                     information_schema.key_column_usage kcu 
762:                 WHERE 
763:                     tc.table_name = %s 
764:                 AND 
765:                     tc.table_schema = current_schema() 
766:                 AND 
767:                     tc.constraint_type = \'PRIMARY KEY\' 
768:                 AND 
769:                     kcu.table_name = tc.table_name 
770:                 AND 
771:                     kcu.table_schema = tc.table_schema 
772:                 AND 
773:                     kcu.constraint_name = tc.constraint_name
774:             ', $objDb->SqlVariable($this->strTable)));
775:             
776:             while ($objRow = $objResult->GetNextRow()) {
777:                 if ($objRow->GetColumn('column_name') == $this->strName)
778:                     $this->blnPrimaryKey = true;
779:             }
780:             
781:             if (!$this->blnPrimaryKey)
782:                 $this->blnPrimaryKey = false;
783: 
784:             // UNIQUE
785:             $objResult = $objDb->Query(sprintf('
786:                 SELECT 
787:                     kcu.column_name, (SELECT COUNT(*) FROM information_schema.key_column_usage kcu2 WHERE kcu2.constraint_name=kcu.constraint_name ) as unique_fields 
788:                 FROM 
789:                     information_schema.table_constraints tc, 
790:                     information_schema.key_column_usage kcu 
791:                 WHERE 
792:                     tc.table_name = %s 
793:                 AND 
794:                     tc.table_schema = current_schema() 
795:                 AND 
796:                     tc.constraint_type = \'UNIQUE\' 
797:                 AND 
798:                     kcu.table_name = tc.table_name 
799:                 AND 
800:                     kcu.table_schema = tc.table_schema 
801:                 AND 
802:                     kcu.constraint_name = tc.constraint_name
803:                 GROUP BY 
804:                     kcu.constraint_name, kcu.column_name
805:             ', $objDb->SqlVariable($this->strTable)));
806:             while ($objRow = $objResult->GetNextRow()) {
807:                 if ($objRow->GetColumn('column_name') == $this->strName && $objRow->GetColumn('unique_fields') == '1' )
808:                     $this->blnUnique = true;
809:             }
810:             if (!$this->blnUnique)
811:             $this->blnUnique = false;   
812:             
813:             // Determine Type
814:             $this->strType = $mixFieldData->GetColumn('data_type');
815:             
816:             switch ($this->strType) {
817:                 case 'integer':
818:                 case 'smallint':
819:                 case 'bigint': // 8-byte. PHP int sizes are platform dependent. On 64-bit machines,
820:                                // this is fine. On 32-bit, PHP will convert to float for numbers too big.
821:                                // However, we do NOT want to return a float, as we lose the ability to 
822:                                // compare against real integers. (float(0) != int(0))! Assume the developer knows what he
823:                                // is doing if he uses these.
824:                                // http://php.net/manual/en/language.types.integer.php
825:                     $this->strType = QDatabaseFieldType::Integer;
826:                     
827:                     break;
828:                 case 'money':
829:                     // NOTE: The money type is deprecated in PostgreSQL.
830:                     throw new QPostgreSqlDatabaseException('Unsupported Field Type: money.  Use numeric or decimal instead.', 0,null);
831:                     break;
832:                 case 'decimal':
833:                 case 'numeric':
834:                     // NOTE: PHP's best response to fixed point exact precision numbers is to use the bcmath library.
835:                     // bcmath requires string inputs. If you try to do math directly on these, PHP will convert to float,
836:                     // so for those who care, they will need to be careful. For those who do not care, then PHP will do
837:                     // the conversion automatically.
838:                     $this->strType = QDatabaseFieldType::VarChar;
839:                     break;
840: 
841:                 case 'real':
842:                     $this->strType = QDatabaseFieldType::Float;
843:                     break;                  
844:                 case 'bit':
845:                     if ($this->intMaxLength == 1)           
846:                         $this->strType = QDatabaseFieldType::Bit;
847:                     else 
848:                         throw new QPostgreSqlDatabaseException('Unsupported Field Type: bit with MaxLength > 1', 0, null);
849:                     break;
850:                 case 'boolean':
851:                     $this->strType = QDatabaseFieldType::Bit;
852:                     break;
853:                 case 'character':
854:                     $this->strType = QDatabaseFieldType::Char;
855:                     break;              
856:                 case 'character varying':
857:                 case 'double precision': 
858:                     // NOTE: PHP does not offer full support of double-precision floats.
859:                     // Value will be set as a VarChar which will guarantee that the precision will be maintained.
860:                     //    However, you will not be able to support full typing control (e.g. you would
861:                     //    not be able to use a QFloatTextBox -- only a regular QTextBox)
862:                     $this->strType = QDatabaseFieldType::VarChar;
863:                     break;
864:                 case 'json':
865:                 case 'jsonb':
866:                     $this->strType = QDatabaseFieldType::Json;
867:                     break;
868:                 case 'tsvector':
869:                     // this is the TSVector data type in PostgreSQL used for full text search systems.
870:                     // It can safely be used as a text type for displaying the data.
871:                     // NOTE: It must be handled via custom queries.
872:                     // NOTE: It is added here to avoid code generator halting after error because of unrecognized type
873:                     $this->strType = QDatabaseFieldType::VarChar;
874:                     break;
875:                 case 'text':
876:                     $this->strType = QDatabaseFieldType::Blob;
877:                     break;
878:                 case 'timestamp':
879:                 case 'timestamp with time zone':
880:                     // this data type is not heavily used but is important to be included to avoid errors when code generating.
881:                 case 'timestamp without time zone':
882:                     // System-generated Timestamp values need to be treated as plain text
883:                     $this->strType = QDatabaseFieldType::DateTime; // PostgreSql treats timestamp as a datetime
884:                     //$this->blnTimestamp = true;
885:                     break;
886:                 case 'date':
887:                     $this->strType = QDatabaseFieldType::Date;
888:                     break;
889:                 case 'time':
890:                 case 'time without time zone':      
891:                     $this->strType = QDatabaseFieldType::Time;
892:                     break;
893:                 default:
894:                     throw new QPostgreSqlDatabaseException('Unsupported Field Type: ' . $this->strType, 0, null);
895:             }
896: 
897:             // Retrieve comment
898:             $this->strComment = $mixFieldData->GetColumn('comment');
899:         }
900:     }
901: