1: <?php
  2: 
  3: /**
  4:  * This file is part of the "dibi" - smart database abstraction layer.
  5:  *
  6:  * Copyright (c) 2005 David Grudl (http://davidgrudl.com)
  7:  *
  8:  * For the full copyright and license information, please view
  9:  * the file license.txt that was distributed with this source code.
 10:  */
 11: 
 12: 
 13: /**
 14:  * The dibi driver for PostgreSQL database.
 15:  *
 16:  * Driver options:
 17:  *   - host, hostaddr, port, dbname, user, password, connect_timeout, options, sslmode, service => see PostgreSQL API
 18:  *   - string => or use connection string
 19:  *   - schema => the schema search path
 20:  *   - charset => character encoding to set (default is utf8)
 21:  *   - persistent (bool) => try to find a persistent link?
 22:  *   - resource (resource) => existing connection resource
 23:  *   - lazy, profiler, result, substitutes, ... => see DibiConnection options
 24:  *
 25:  * @author     David Grudl
 26:  * @package    dibi\drivers
 27:  */
 28: class DibiPostgreDriver extends DibiObject implements IDibiDriver, IDibiResultDriver, IDibiReflector
 29: {
 30:     /** @var resource  Connection resource */
 31:     private $connection;
 32: 
 33:     /** @var resource  Resultset resource */
 34:     private $resultSet;
 35: 
 36:     /** @var int|FALSE  Affected rows */
 37:     private $affectedRows = FALSE;
 38: 
 39:     /** @var bool  Escape method */
 40:     private $escMethod = FALSE;
 41: 
 42: 
 43: 
 44:     /**
 45:      * @throws DibiNotSupportedException
 46:      */
 47:     public function __construct()
 48:     {
 49:         if (!extension_loaded('pgsql')) {
 50:             throw new DibiNotSupportedException("PHP extension 'pgsql' is not loaded.");
 51:         }
 52:     }
 53: 
 54: 
 55: 
 56:     /**
 57:      * Connects to a database.
 58:      * @return void
 59:      * @throws DibiException
 60:      */
 61:     public function connect(array &$config)
 62:     {
 63:         if (isset($config['resource'])) {
 64:             $this->connection = $config['resource'];
 65: 
 66:         } else {
 67:             if (!isset($config['charset'])) $config['charset'] = 'utf8';
 68:             if (isset($config['string'])) {
 69:                 $string = $config['string'];
 70:             } else {
 71:                 $string = '';
 72:                 DibiConnection::alias($config, 'user', 'username');
 73:                 DibiConnection::alias($config, 'dbname', 'database');
 74:                 foreach (array('host','hostaddr','port','dbname','user','password','connect_timeout','options','sslmode','service') as $key) {
 75:                     if (isset($config[$key])) $string .= $key . '=' . $config[$key] . ' ';
 76:                 }
 77:             }
 78: 
 79:             DibiDriverException::tryError();
 80:             if (empty($config['persistent'])) {
 81:                 $this->connection = pg_connect($string, PGSQL_CONNECT_FORCE_NEW);
 82:             } else {
 83:                 $this->connection = pg_pconnect($string, PGSQL_CONNECT_FORCE_NEW);
 84:             }
 85:             if (DibiDriverException::catchError($msg)) {
 86:                 throw new DibiDriverException($msg, 0);
 87:             }
 88:         }
 89: 
 90:         if (!is_resource($this->connection)) {
 91:             throw new DibiDriverException('Connecting error.');
 92:         }
 93: 
 94:         if (isset($config['charset'])) {
 95:             DibiDriverException::tryError();
 96:             pg_set_client_encoding($this->connection, $config['charset']);
 97:             if (DibiDriverException::catchError($msg)) {
 98:                 throw new DibiDriverException($msg, 0);
 99:             }
100:         }
101: 
102:         if (isset($config['schema'])) {
103:             $this->query('SET search_path TO "' . $config['schema'] . '"');
104:         }
105: 
106:         $this->escMethod = version_compare(PHP_VERSION , '5.2.0', '>=');
107:     }
108: 
109: 
110: 
111:     /**
112:      * Disconnects from a database.
113:      * @return void
114:      */
115:     public function disconnect()
116:     {
117:         pg_close($this->connection);
118:     }
119: 
120: 
121: 
122:     /**
123:      * Executes the SQL query.
124:      * @param  string      SQL statement.
125:      * @return IDibiResultDriver|NULL
126:      * @throws DibiDriverException
127:      */
128:     public function query($sql)
129:     {
130:         $this->affectedRows = FALSE;
131:         $res = @pg_query($this->connection, $sql); // intentionally @
132: 
133:         if ($res === FALSE) {
134:             throw new DibiDriverException(pg_last_error($this->connection), 0, $sql);
135: 
136:         } elseif (is_resource($res)) {
137:             $this->affectedRows = pg_affected_rows($res);
138:             if (pg_num_fields($res)) {
139:                 return $this->createResultDriver($res);
140:             }
141:         }
142:     }
143: 
144: 
145: 
146:     /**
147:      * Gets the number of affected rows by the last INSERT, UPDATE or DELETE query.
148:      * @return int|FALSE  number of rows or FALSE on error
149:      */
150:     public function getAffectedRows()
151:     {
152:         return $this->affectedRows;
153:     }
154: 
155: 
156: 
157:     /**
158:      * Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.
159:      * @return int|FALSE  int on success or FALSE on failure
160:      */
161:     public function getInsertId($sequence)
162:     {
163:         if ($sequence === NULL) {
164:             // PostgreSQL 8.1 is needed
165:             $res = $this->query("SELECT LASTVAL()");
166:         } else {
167:             $res = $this->query("SELECT CURRVAL('$sequence')");
168:         }
169: 
170:         if (!$res) return FALSE;
171: 
172:         $row = $res->fetch(FALSE);
173:         return is_array($row) ? $row[0] : FALSE;
174:     }
175: 
176: 
177: 
178:     /**
179:      * Begins a transaction (if supported).
180:      * @param  string  optional savepoint name
181:      * @return void
182:      * @throws DibiDriverException
183:      */
184:     public function begin($savepoint = NULL)
185:     {
186:         $this->query($savepoint ? "SAVEPOINT $savepoint" : 'START TRANSACTION');
187:     }
188: 
189: 
190: 
191:     /**
192:      * Commits statements in a transaction.
193:      * @param  string  optional savepoint name
194:      * @return void
195:      * @throws DibiDriverException
196:      */
197:     public function commit($savepoint = NULL)
198:     {
199:         $this->query($savepoint ? "RELEASE SAVEPOINT $savepoint" : 'COMMIT');
200:     }
201: 
202: 
203: 
204:     /**
205:      * Rollback changes in a transaction.
206:      * @param  string  optional savepoint name
207:      * @return void
208:      * @throws DibiDriverException
209:      */
210:     public function rollback($savepoint = NULL)
211:     {
212:         $this->query($savepoint ? "ROLLBACK TO SAVEPOINT $savepoint" : 'ROLLBACK');
213:     }
214: 
215: 
216: 
217:     /**
218:      * Is in transaction?
219:      * @return bool
220:      */
221:     public function inTransaction()
222:     {
223:         return !in_array(pg_transaction_status($this->connection), array(PGSQL_TRANSACTION_UNKNOWN, PGSQL_TRANSACTION_IDLE), TRUE);
224:     }
225: 
226: 
227: 
228:     /**
229:      * Returns the connection resource.
230:      * @return mixed
231:      */
232:     public function getResource()
233:     {
234:         return $this->connection;
235:     }
236: 
237: 
238: 
239:     /**
240:      * Returns the connection reflector.
241:      * @return IDibiReflector
242:      */
243:     public function getReflector()
244:     {
245:         return $this;
246:     }
247: 
248: 
249: 
250:     /**
251:      * Result set driver factory.
252:      * @param  resource
253:      * @return IDibiResultDriver
254:      */
255:     public function createResultDriver($resource)
256:     {
257:         $res = clone $this;
258:         $res->resultSet = $resource;
259:         return $res;
260:     }
261: 
262: 
263: 
264:     /********************* SQL ****************d*g**/
265: 
266: 
267: 
268:     /**
269:      * Encodes data for use in a SQL statement.
270:      * @param  mixed     value
271:      * @param  string    type (dibi::TEXT, dibi::BOOL, ...)
272:      * @return string    encoded value
273:      * @throws InvalidArgumentException
274:      */
275:     public function escape($value, $type)
276:     {
277:         switch ($type) {
278:         case dibi::TEXT:
279:             if ($this->escMethod) {
280:                 return "'" . pg_escape_string($this->connection, $value) . "'";
281:             } else {
282:                 return "'" . pg_escape_string($value) . "'";
283:             }
284: 
285:         case dibi::BINARY:
286:             if ($this->escMethod) {
287:                 return "'" . pg_escape_bytea($this->connection, $value) . "'";
288:             } else {
289:                 return "'" . pg_escape_bytea($value) . "'";
290:             }
291: 
292:         case dibi::IDENTIFIER:
293:             // @see http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
294:             return '"' . str_replace('"', '""', $value) . '"';
295: 
296:         case dibi::BOOL:
297:             return $value ? 'TRUE' : 'FALSE';
298: 
299:         case dibi::DATE:
300:             return $value instanceof DateTime ? $value->format("'Y-m-d'") : date("'Y-m-d'", $value);
301: 
302:         case dibi::DATETIME:
303:             return $value instanceof DateTime ? $value->format("'Y-m-d H:i:s'") : date("'Y-m-d H:i:s'", $value);
304: 
305:         default:
306:             throw new InvalidArgumentException('Unsupported type.');
307:         }
308:     }
309: 
310: 
311: 
312:     /**
313:      * Encodes string for use in a LIKE statement.
314:      * @param  string
315:      * @param  int
316:      * @return string
317:      */
318:     public function escapeLike($value, $pos)
319:     {
320:         if ($this->escMethod) {
321:             $value = pg_escape_string($this->connection, $value);
322:         } else {
323:             $value = pg_escape_string($value);
324:     }
325: 
326:         $value = strtr($value, array( '%' => '\\\\%', '_' => '\\\\_'));
327:         return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'");
328:     }
329: 
330: 
331: 
332:     /**
333:      * Decodes data from result set.
334:      * @param  string    value
335:      * @param  string    type (dibi::BINARY)
336:      * @return string    decoded value
337:      * @throws InvalidArgumentException
338:      */
339:     public function unescape($value, $type)
340:     {
341:         if ($type === dibi::BINARY) {
342:             return pg_unescape_bytea($value);
343:         }
344:         throw new InvalidArgumentException('Unsupported type.');
345:     }
346: 
347: 
348: 
349:     /**
350:      * Injects LIMIT/OFFSET to the SQL query.
351:      * @param  string &$sql  The SQL query that will be modified.
352:      * @param  int $limit
353:      * @param  int $offset
354:      * @return void
355:      */
356:     public function applyLimit(&$sql, $limit, $offset)
357:     {
358:         if ($limit >= 0)
359:             $sql .= ' LIMIT ' . (int) $limit;
360: 
361:         if ($offset > 0)
362:             $sql .= ' OFFSET ' . (int) $offset;
363:     }
364: 
365: 
366: 
367:     /********************* result set ****************d*g**/
368: 
369: 
370: 
371:     /**
372:      * Automatically frees the resources allocated for this result set.
373:      * @return void
374:      */
375:     public function __destruct()
376:     {
377:         $this->resultSet && @$this->free();
378:     }
379: 
380: 
381: 
382:     /**
383:      * Returns the number of rows in a result set.
384:      * @return int
385:      */
386:     public function getRowCount()
387:     {
388:         return pg_num_rows($this->resultSet);
389:     }
390: 
391: 
392: 
393:     /**
394:      * Fetches the row at current position and moves the internal cursor to the next position.
395:      * @param  bool     TRUE for associative array, FALSE for numeric
396:      * @return array    array on success, nonarray if no next record
397:      */
398:     public function fetch($assoc)
399:     {
400:         return pg_fetch_array($this->resultSet, NULL, $assoc ? PGSQL_ASSOC : PGSQL_NUM);
401:     }
402: 
403: 
404: 
405:     /**
406:      * Moves cursor position without fetching row.
407:      * @param  int      the 0-based cursor pos to seek to
408:      * @return boolean  TRUE on success, FALSE if unable to seek to specified record
409:      */
410:     public function seek($row)
411:     {
412:         return pg_result_seek($this->resultSet, $row);
413:     }
414: 
415: 
416: 
417:     /**
418:      * Frees the resources allocated for this result set.
419:      * @return void
420:      */
421:     public function free()
422:     {
423:         pg_free_result($this->resultSet);
424:         $this->resultSet = NULL;
425:     }
426: 
427: 
428: 
429:     /**
430:      * Returns metadata for all columns in a result set.
431:      * @return array
432:      */
433:     public function getResultColumns()
434:     {
435:         $hasTable = version_compare(PHP_VERSION , '5.2.0', '>=');
436:         $count = pg_num_fields($this->resultSet);
437:         $columns = array();
438:         for ($i = 0; $i < $count; $i++) {
439:             $row = array(
440:                 'name'      => pg_field_name($this->resultSet, $i),
441:                 'table'     => $hasTable ? pg_field_table($this->resultSet, $i) : NULL,
442:                 'nativetype'=> pg_field_type($this->resultSet, $i),
443:             );
444:             $row['fullname'] = $row['table'] ? $row['table'] . '.' . $row['name'] : $row['name'];
445:             $columns[] = $row;
446:         }
447:         return $columns;
448:     }
449: 
450: 
451: 
452:     /**
453:      * Returns the result set resource.
454:      * @return mixed
455:      */
456:     public function getResultResource()
457:     {
458:         return $this->resultSet;
459:     }
460: 
461: 
462: 
463:     /********************* IDibiReflector ****************d*g**/
464: 
465: 
466: 
467:     /**
468:      * Returns list of tables.
469:      * @return array
470:      */
471:     public function getTables()
472:     {
473:         $version = pg_parameter_status($this->resource, 'server_version');
474:         if ($version < 7.4) {
475:             throw new DibiDriverException('Reflection requires PostgreSQL 7.4 and newer.');
476:         }
477: 
478:         $res = $this->query("
479:             SELECT
480:                 table_name AS name,
481:                 CASE table_type
482:                     WHEN 'VIEW' THEN 1
483:                     ELSE 0
484:                 END AS view
485:             FROM
486:                 information_schema.tables
487:             WHERE
488:                 table_schema = current_schema()
489:         ");
490:         $tables = pg_fetch_all($res->resultSet);
491:         return $tables ? $tables : array();
492:     }
493: 
494: 
495: 
496:     /**
497:      * Returns metadata for all columns in a table.
498:      * @param  string
499:      * @return array
500:      */
501:     public function getColumns($table)
502:     {
503:         $_table = $this->escape($table, dibi::TEXT);
504:         $res = $this->query("
505:             SELECT indkey
506:             FROM pg_class
507:             LEFT JOIN pg_index on pg_class.oid = pg_index.indrelid AND pg_index.indisprimary
508:             WHERE pg_class.relname = $_table
509:         ");
510:         $primary = (int) pg_fetch_object($res->resultSet)->indkey;
511: 
512:         $res = $this->query("
513:             SELECT *
514:             FROM information_schema.columns
515:             WHERE table_name = $_table AND table_schema = current_schema()
516:             ORDER BY ordinal_position
517:         ");
518:         $columns = array();
519:         while ($row = $res->fetch(TRUE)) {
520:             $size = (int) max($row['character_maximum_length'], $row['numeric_precision']);
521:             $columns[] = array(
522:                 'name' => $row['column_name'],
523:                 'table' => $table,
524:                 'nativetype' => strtoupper($row['udt_name']),
525:                 'size' => $size ? $size : NULL,
526:                 'nullable' => $row['is_nullable'] === 'YES',
527:                 'default' => $row['column_default'],
528:                 'autoincrement' => (int) $row['ordinal_position'] === $primary && substr($row['column_default'], 0, 7) === 'nextval',
529:                 'vendor' => $row,
530:             );
531:         }
532:         return $columns;
533:     }
534: 
535: 
536: 
537:     /**
538:      * Returns metadata for all indexes in a table.
539:      * @param  string
540:      * @return array
541:      */
542:     public function getIndexes($table)
543:     {
544:         $_table = $this->escape($table, dibi::TEXT);
545:         $res = $this->query("
546:             SELECT ordinal_position, column_name
547:             FROM information_schema.columns
548:             WHERE table_name = $_table AND table_schema = current_schema()
549:             ORDER BY ordinal_position
550:         ");
551: 
552:         $columns = array();
553:         while ($row = $res->fetch(TRUE)) {
554:             $columns[$row['ordinal_position']] = $row['column_name'];
555:         }
556: 
557:         $res = $this->query("
558:             SELECT pg_class2.relname, indisunique, indisprimary, indkey
559:             FROM pg_class
560:             LEFT JOIN pg_index on pg_class.oid = pg_index.indrelid
561:             INNER JOIN pg_class as pg_class2 on pg_class2.oid = pg_index.indexrelid
562:             WHERE pg_class.relname = $_table
563:         ");
564: 
565:         $indexes = array();
566:         while ($row = $res->fetch(TRUE)) {
567:             $indexes[$row['relname']]['name'] = $row['relname'];
568:             $indexes[$row['relname']]['unique'] = $row['indisunique'] === 't';
569:             $indexes[$row['relname']]['primary'] = $row['indisprimary'] === 't';
570:             foreach (explode(' ', $row['indkey']) as $index) {
571:                 $indexes[$row['relname']]['columns'][] = $columns[$index];
572:             }
573:         }
574:         return array_values($indexes);
575:     }
576: 
577: 
578: 
579:     /**
580:      * Returns metadata for all foreign keys in a table.
581:      * @param  string
582:      * @return array
583:      */
584:     public function getForeignKeys($table)
585:     {
586:         $_table = $this->escape($table, dibi::TEXT);
587: 
588:         $res = $this->query("
589:             SELECT
590:                 c.conname AS name,
591:                 lt.attname AS local,
592:                 c.confrelid::regclass AS table,
593:                 ft.attname AS foreign,
594: 
595:                 CASE c.confupdtype
596:                     WHEN 'a' THEN 'NO ACTION'
597:                     WHEN 'r' THEN 'RESTRICT'
598:                     WHEN 'c' THEN 'CASCADE'
599:                     WHEN 'n' THEN 'SET NULL'
600:                     WHEN 'd' THEN 'SET DEFAULT'
601:                     ELSE 'UNKNOWN'
602:                 END AS \"onUpdate\",
603: 
604:                 CASE c.confdeltype
605:                     WHEN 'a' THEN 'NO ACTION'
606:                     WHEN 'r' THEN 'RESTRICT'
607:                     WHEN 'c' THEN 'CASCADE'
608:                     WHEN 'n' THEN 'SET NULL'
609:                     WHEN 'd' THEN 'SET DEFAULT'
610:                     ELSE 'UNKNOWN'
611:                 END AS \"onDelete\",
612: 
613:                 c.conkey,
614:                 lt.attnum AS lnum,
615:                 c.confkey,
616:                 ft.attnum AS fnum
617:             FROM
618:                 pg_constraint c
619:                 JOIN pg_attribute lt ON c.conrelid = lt.attrelid AND lt.attnum = ANY (c.conkey)
620:                 JOIN pg_attribute ft ON c.confrelid = ft.attrelid AND ft.attnum = ANY (c.confkey)
621:             WHERE
622:                 c.contype = 'f'
623:                 AND
624:                 c.conrelid = $_table::regclass
625:         ");
626: 
627:         $fKeys = $references = array();
628:         while ($row = $res->fetch(TRUE)) {
629:             if (!isset($fKeys[$row['name']])) {
630:                 $fKeys[$row['name']] = array(
631:                     'name' => $row['name'],
632:                     'table' => $row['table'],
633:                     'local' => array(),
634:                     'foreign' => array(),
635:                     'onUpdate' => $row['onUpdate'],
636:                     'onDelete' => $row['onDelete'],
637:                 );
638: 
639:                 $l = explode(',', trim($row['conkey'], '{}'));
640:                 $f = explode(',', trim($row['confkey'], '{}'));
641: 
642:                 $references[$row['name']] = array_combine($l, $f);
643:             }
644: 
645:             if (isset($references[$row['name']][$row['lnum']]) && $references[$row['name']][$row['lnum']] === $row['fnum']) {
646:                 $fKeys[$row['name']]['local'][] = $row['local'];
647:                 $fKeys[$row['name']]['foreign'][] = $row['foreign'];
648:             }
649:         }
650: 
651:         return $fKeys;
652:     }
653: 
654: }
655: