1: <?php
2:
3: 4: 5: 6: 7: 8: 9: 10: 11:
12:
13:
14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28:
29: class DibiPostgreDriver extends DibiObject implements IDibiDriver, IDibiResultDriver, IDibiReflector
30: {
31:
32: private $connection;
33:
34:
35: private $resultSet;
36:
37:
38: private $escMethod = FALSE;
39:
40:
41:
42: 43: 44:
45: public function __construct()
46: {
47: if (!extension_loaded('pgsql')) {
48: throw new DibiDriverException("PHP extension 'pgsql' is not loaded.");
49: }
50: }
51:
52:
53:
54: 55: 56: 57: 58:
59: public function connect(array &$config)
60: {
61: if (isset($config['resource'])) {
62: $this->connection = $config['resource'];
63:
64: } else {
65: if (!isset($config['charset'])) $config['charset'] = 'utf8';
66: if (isset($config['string'])) {
67: $string = $config['string'];
68: } else {
69: $string = '';
70: DibiConnection::alias($config, 'user', 'username');
71: DibiConnection::alias($config, 'dbname', 'database');
72: foreach (array('host','hostaddr','port','dbname','user','password','connect_timeout','options','sslmode','service') as $key) {
73: if (isset($config[$key])) $string .= $key . '=' . $config[$key] . ' ';
74: }
75: }
76:
77: DibiDriverException::tryError();
78: if (empty($config['persistent'])) {
79: $this->connection = pg_connect($string, PGSQL_CONNECT_FORCE_NEW);
80: } else {
81: $this->connection = pg_pconnect($string, PGSQL_CONNECT_FORCE_NEW);
82: }
83: if (DibiDriverException::catchError($msg)) {
84: throw new DibiDriverException($msg, 0);
85: }
86: }
87:
88: if (!is_resource($this->connection)) {
89: throw new DibiDriverException('Connecting error.');
90: }
91:
92: if (isset($config['charset'])) {
93: DibiDriverException::tryError();
94: pg_set_client_encoding($this->connection, $config['charset']);
95: if (DibiDriverException::catchError($msg)) {
96: throw new DibiDriverException($msg, 0);
97: }
98: }
99:
100: if (isset($config['schema'])) {
101: $this->query('SET search_path TO "' . $config['schema'] . '"');
102: }
103:
104: $this->escMethod = version_compare(PHP_VERSION , '5.2.0', '>=');
105: }
106:
107:
108:
109: 110: 111: 112:
113: public function disconnect()
114: {
115: pg_close($this->connection);
116: }
117:
118:
119:
120: 121: 122: 123: 124: 125:
126: public function query($sql)
127: {
128: $this->resultSet = @pg_query($this->connection, $sql); 129:
130: if ($this->resultSet === FALSE) {
131: throw new DibiDriverException(pg_last_error($this->connection), 0, $sql);
132: }
133:
134: return is_resource($this->resultSet) && pg_num_fields($this->resultSet) ? clone $this : NULL;
135: }
136:
137:
138:
139: 140: 141: 142:
143: public function getAffectedRows()
144: {
145: return pg_affected_rows($this->resultSet);
146: }
147:
148:
149:
150: 151: 152: 153:
154: public function getInsertId($sequence)
155: {
156: if ($sequence === NULL) {
157: 158: $res = $this->query("SELECT LASTVAL()");
159: } else {
160: $res = $this->query("SELECT CURRVAL('$sequence')");
161: }
162:
163: if (!$res) return FALSE;
164:
165: $row = $res->fetch(FALSE);
166: $res->free();
167: return is_array($row) ? $row[0] : FALSE;
168: }
169:
170:
171:
172: 173: 174: 175: 176: 177:
178: public function begin($savepoint = NULL)
179: {
180: $this->query($savepoint ? "SAVEPOINT $savepoint" : 'START TRANSACTION');
181: }
182:
183:
184:
185: 186: 187: 188: 189: 190:
191: public function commit($savepoint = NULL)
192: {
193: $this->query($savepoint ? "RELEASE SAVEPOINT $savepoint" : 'COMMIT');
194: }
195:
196:
197:
198: 199: 200: 201: 202: 203:
204: public function rollback($savepoint = NULL)
205: {
206: $this->query($savepoint ? "ROLLBACK TO SAVEPOINT $savepoint" : 'ROLLBACK');
207: }
208:
209:
210:
211: 212: 213: 214:
215: public function inTransaction()
216: {
217: return !in_array(pg_transaction_status($this->connection), array(PGSQL_TRANSACTION_UNKNOWN, PGSQL_TRANSACTION_IDLE), TRUE);
218: }
219:
220:
221:
222: 223: 224: 225:
226: public function getResource()
227: {
228: return $this->connection;
229: }
230:
231:
232:
233: 234: 235: 236:
237: public function getReflector()
238: {
239: return $this;
240: }
241:
242:
243:
244:
245:
246:
247:
248: 249: 250: 251: 252: 253: 254:
255: public function escape($value, $type)
256: {
257: switch ($type) {
258: case dibi::TEXT:
259: if ($this->escMethod) {
260: return "'" . pg_escape_string($this->connection, $value) . "'";
261: } else {
262: return "'" . pg_escape_string($value) . "'";
263: }
264:
265: case dibi::BINARY:
266: if ($this->escMethod) {
267: return "'" . pg_escape_bytea($this->connection, $value) . "'";
268: } else {
269: return "'" . pg_escape_bytea($value) . "'";
270: }
271:
272: case dibi::IDENTIFIER:
273: 274: return '"' . str_replace('"', '""', $value) . '"';
275:
276: case dibi::BOOL:
277: return $value ? 'TRUE' : 'FALSE';
278:
279: case dibi::DATE:
280: return $value instanceof DateTime ? $value->format("'Y-m-d'") : date("'Y-m-d'", $value);
281:
282: case dibi::DATETIME:
283: return $value instanceof DateTime ? $value->format("'Y-m-d H:i:s'") : date("'Y-m-d H:i:s'", $value);
284:
285: default:
286: throw new InvalidArgumentException('Unsupported type.');
287: }
288: }
289:
290:
291:
292: 293: 294: 295: 296: 297:
298: public function escapeLike($value, $pos)
299: {
300: throw new NotImplementedException;
301: }
302:
303:
304:
305: 306: 307: 308: 309: 310: 311:
312: public function unescape($value, $type)
313: {
314: if ($type === dibi::BINARY) {
315: return pg_unescape_bytea($value);
316: }
317: throw new InvalidArgumentException('Unsupported type.');
318: }
319:
320:
321:
322: 323: 324: 325: 326: 327: 328:
329: public function applyLimit(&$sql, $limit, $offset)
330: {
331: if ($limit >= 0)
332: $sql .= ' LIMIT ' . (int) $limit;
333:
334: if ($offset > 0)
335: $sql .= ' OFFSET ' . (int) $offset;
336: }
337:
338:
339:
340:
341:
342:
343:
344: 345: 346: 347:
348: public function getRowCount()
349: {
350: return pg_num_rows($this->resultSet);
351: }
352:
353:
354:
355: 356: 357: 358: 359:
360: public function fetch($assoc)
361: {
362: return pg_fetch_array($this->resultSet, NULL, $assoc ? PGSQL_ASSOC : PGSQL_NUM);
363: }
364:
365:
366:
367: 368: 369: 370: 371:
372: public function seek($row)
373: {
374: return pg_result_seek($this->resultSet, $row);
375: }
376:
377:
378:
379: 380: 381: 382:
383: public function free()
384: {
385: pg_free_result($this->resultSet);
386: $this->resultSet = NULL;
387: }
388:
389:
390:
391: 392: 393: 394:
395: public function getResultColumns()
396: {
397: $hasTable = version_compare(PHP_VERSION , '5.2.0', '>=');
398: $count = pg_num_fields($this->resultSet);
399: $columns = array();
400: for ($i = 0; $i < $count; $i++) {
401: $row = array(
402: 'name' => pg_field_name($this->resultSet, $i),
403: 'table' => $hasTable ? pg_field_table($this->resultSet, $i) : NULL,
404: 'nativetype'=> pg_field_type($this->resultSet, $i),
405: );
406: $row['fullname'] = $row['table'] ? $row['table'] . '.' . $row['name'] : $row['name'];
407: $columns[] = $row;
408: }
409: return $columns;
410: }
411:
412:
413:
414: 415: 416: 417:
418: public function getResultResource()
419: {
420: return $this->resultSet;
421: }
422:
423:
424:
425:
426:
427:
428:
429: 430: 431: 432:
433: public function getTables()
434: {
435: $version = pg_version($this->connection);
436: if ($version['server'] < 8) {
437: throw new DibiDriverException('Reflection requires PostgreSQL 8.');
438: }
439:
440: $res = $this->query("
441: SELECT table_name as name, CAST(table_type = 'VIEW' AS INTEGER) as view
442: FROM information_schema.tables
443: WHERE table_schema = current_schema()
444: ");
445: $tables = pg_fetch_all($res->resultSet);
446: $res->free();
447: return $tables ? $tables : array();
448: }
449:
450:
451:
452: 453: 454: 455: 456:
457: public function getColumns($table)
458: {
459: $_table = $this->escape($table, dibi::TEXT);
460: $res = $this->query("
461: SELECT indkey
462: FROM pg_class
463: LEFT JOIN pg_index on pg_class.oid = pg_index.indrelid AND pg_index.indisprimary
464: WHERE pg_class.relname = $_table
465: ");
466: $primary = (int) pg_fetch_object($res->resultSet)->indkey;
467:
468: $res = $this->query("
469: SELECT *
470: FROM information_schema.columns
471: WHERE table_name = $_table AND table_schema = current_schema()
472: ORDER BY ordinal_position
473: ");
474: $columns = array();
475: while ($row = $res->fetch(TRUE)) {
476: $size = (int) max($row['character_maximum_length'], $row['numeric_precision']);
477: $columns[] = array(
478: 'name' => $row['column_name'],
479: 'table' => $table,
480: 'nativetype' => strtoupper($row['udt_name']),
481: 'size' => $size ? $size : NULL,
482: 'nullable' => $row['is_nullable'] === 'YES',
483: 'default' => $row['column_default'],
484: 'autoincrement' => (int) $row['ordinal_position'] === $primary && substr($row['column_default'], 0, 7) === 'nextval',
485: 'vendor' => $row,
486: );
487: }
488: $res->free();
489: return $columns;
490: }
491:
492:
493:
494: 495: 496: 497: 498:
499: public function getIndexes($table)
500: {
501: $_table = $this->escape($table, dibi::TEXT);
502: $res = $this->query("
503: SELECT ordinal_position, column_name
504: FROM information_schema.columns
505: WHERE table_name = $_table AND table_schema = current_schema()
506: ORDER BY ordinal_position
507: ");
508:
509: $columns = array();
510: while ($row = $res->fetch(TRUE)) {
511: $columns[$row['ordinal_position']] = $row['column_name'];
512: }
513:
514: $res = $this->query("
515: SELECT pg_class2.relname, indisunique, indisprimary, indkey
516: FROM pg_class
517: LEFT JOIN pg_index on pg_class.oid = pg_index.indrelid
518: INNER JOIN pg_class as pg_class2 on pg_class2.oid = pg_index.indexrelid
519: WHERE pg_class.relname = $_table
520: ");
521:
522: $indexes = array();
523: while ($row = $res->fetch(TRUE)) {
524: $indexes[$row['relname']]['name'] = $row['relname'];
525: $indexes[$row['relname']]['unique'] = $row['indisunique'] === 't';
526: $indexes[$row['relname']]['primary'] = $row['indisprimary'] === 't';
527: foreach (explode(' ', $row['indkey']) as $index) {
528: $indexes[$row['relname']]['columns'][] = $columns[$index];
529: }
530: }
531: $res->free();
532: return array_values($indexes);
533: }
534:
535:
536:
537: 538: 539: 540: 541:
542: public function getForeignKeys($table)
543: {
544: throw new NotImplementedException;
545: }
546:
547: }
548: