1: <?php
  2: 
  3: /**
  4:  * This file is part of the Nette Framework (https://nette.org)
  5:  * Copyright (c) 2004 David Grudl (http://davidgrudl.com)
  6:  * @package Nette\Database\Drivers
  7:  */
  8: 
  9: 
 10: 
 11: /**
 12:  * Supplemental PostgreSQL database driver.
 13:  *
 14:  * @author     David Grudl
 15:  * @package Nette\Database\Drivers
 16:  */
 17: class PgSqlDriver extends Object implements ISupplementalDriver
 18: {
 19:     /** @var Connection */
 20:     private $connection;
 21: 
 22: 
 23:     public function __construct(Connection $connection, array $options)
 24:     {
 25:         $this->connection = $connection;
 26:     }
 27: 
 28: 
 29:     /********************* SQL ****************d*g**/
 30: 
 31: 
 32:     /**
 33:      * Delimites identifier for use in a SQL statement.
 34:      */
 35:     public function delimite($name)
 36:     {
 37:         // @see http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
 38:         return '"' . str_replace('"', '""', $name) . '"';
 39:     }
 40: 
 41: 
 42:     /**
 43:      * Formats boolean for use in a SQL statement.
 44:      */
 45:     public function formatBool($value)
 46:     {
 47:         return $value ? 'TRUE' : 'FALSE';
 48:     }
 49: 
 50: 
 51:     /**
 52:      * Formats date-time for use in a SQL statement.
 53:      */
 54:     public function formatDateTime(DateTime $value)
 55:     {
 56:         return $value->format("'Y-m-d H:i:s'");
 57:     }
 58: 
 59: 
 60:     /**
 61:      * Encodes string for use in a LIKE statement.
 62:      */
 63:     public function formatLike($value, $pos)
 64:     {
 65:         $value = strtr($value, array("'" => "''", '\\' => '\\\\', '%' => '\\\\%', '_' => '\\\\_'));
 66:         return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'");
 67:     }
 68: 
 69: 
 70:     /**
 71:      * Injects LIMIT/OFFSET to the SQL query.
 72:      */
 73:     public function applyLimit(& $sql, $limit, $offset)
 74:     {
 75:         if ($limit >= 0) {
 76:             $sql .= ' LIMIT ' . (int) $limit;
 77:         }
 78:         if ($offset > 0) {
 79:             $sql .= ' OFFSET ' . (int) $offset;
 80:         }
 81:     }
 82: 
 83: 
 84:     /**
 85:      * Normalizes result row.
 86:      */
 87:     public function normalizeRow($row, $statement)
 88:     {
 89:         return $row;
 90:     }
 91: 
 92: 
 93:     /********************* reflection ****************d*g**/
 94: 
 95: 
 96:     /**
 97:      * Returns list of tables.
 98:      */
 99:     public function getTables()
100:     {
101:         $tables = array();
102:         foreach ($this->connection->query("
103:             SELECT
104:                 c.relname::varchar AS name,
105:                 c.relkind = 'v' AS view
106:             FROM
107:                 pg_catalog.pg_class AS c
108:                 JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
109:             WHERE
110:                 c.relkind IN ('r', 'v')
111:                 AND ARRAY[n.nspname] <@ pg_catalog.current_schemas(FALSE)
112:             ORDER BY
113:                 c.relname
114:         ") as $row) {
115:             $tables[] = (array) $row;
116:         }
117: 
118:         return $tables;
119:     }
120: 
121: 
122:     /**
123:      * Returns metadata for all columns in a table.
124:      */
125:     public function getColumns($table)
126:     {
127:         $columns = array();
128:         foreach ($this->connection->query("
129:             SELECT
130:                 a.attname::varchar AS name,
131:                 c.relname::varchar AS table,
132:                 upper(t.typname) AS nativetype,
133:                 NULL AS size,
134:                 FALSE AS unsigned,
135:                 NOT (a.attnotnull OR t.typtype = 'd' AND t.typnotnull) AS nullable,
136:                 pg_catalog.pg_get_expr(ad.adbin, 'pg_catalog.pg_attrdef'::regclass)::varchar AS default,
137:                 coalesce(co.contype = 'p' AND strpos(ad.adsrc, 'nextval') = 1, FALSE) AS autoincrement,
138:                 coalesce(co.contype = 'p', FALSE) AS primary,
139:                 substring(pg_catalog.pg_get_expr(ad.adbin, 'pg_catalog.pg_attrdef'::regclass) from 'nextval[(]''\"?([^''\"]+)') AS sequence
140:             FROM
141:                 pg_catalog.pg_attribute AS a
142:                 JOIN pg_catalog.pg_class AS c ON a.attrelid = c.oid
143:                 JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
144:                 JOIN pg_catalog.pg_type AS t ON a.atttypid = t.oid
145:                 LEFT JOIN pg_catalog.pg_attrdef AS ad ON ad.adrelid = c.oid AND ad.adnum = a.attnum
146:                 LEFT JOIN pg_catalog.pg_constraint AS co ON co.connamespace = n.oid AND contype = 'p' AND co.conrelid = c.oid AND a.attnum = ANY(co.conkey)
147:             WHERE
148:                 c.relkind IN ('r', 'v')
149:                 AND c.relname::varchar = {$this->connection->quote($table)}
150:                 AND ARRAY[n.nspname] <@ pg_catalog.current_schemas(FALSE)
151:                 AND a.attnum > 0
152:                 AND NOT a.attisdropped
153:             ORDER BY
154:                 a.attnum
155:         ") as $row) {
156:             $column = (array) $row;
157:             $column['vendor'] = $column;
158:             unset($column['sequence']);
159: 
160:             $columns[] = $column;
161:         }
162: 
163:         return $columns;
164:     }
165: 
166: 
167:     /**
168:      * Returns metadata for all indexes in a table.
169:      */
170:     public function getIndexes($table)
171:     {
172:         $indexes = array();
173:         foreach ($this->connection->query("
174:             SELECT
175:                 c2.relname::varchar AS name,
176:                 i.indisunique AS unique,
177:                 i.indisprimary AS primary,
178:                 a.attname::varchar AS column
179:             FROM
180:                 pg_catalog.pg_class AS c1
181:                 JOIN pg_catalog.pg_namespace AS n ON c1.relnamespace = n.oid
182:                 JOIN pg_catalog.pg_index AS i ON c1.oid = i.indrelid
183:                 JOIN pg_catalog.pg_class AS c2 ON i.indexrelid = c2.oid
184:                 LEFT JOIN pg_catalog.pg_attribute AS a ON c1.oid = a.attrelid AND a.attnum = ANY(i.indkey)
185:             WHERE
186:                 ARRAY[n.nspname] <@ pg_catalog.current_schemas(FALSE)
187:                 AND c1.relkind = 'r'
188:                 AND c1.relname = {$this->connection->quote($table)}
189:         ") as $row) {
190:             $indexes[$row['name']]['name'] = $row['name'];
191:             $indexes[$row['name']]['unique'] = $row['unique'];
192:             $indexes[$row['name']]['primary'] = $row['primary'];
193:             $indexes[$row['name']]['columns'][] = $row['column'];
194:         }
195: 
196:         return array_values($indexes);
197:     }
198: 
199: 
200:     /**
201:      * Returns metadata for all foreign keys in a table.
202:      */
203:     public function getForeignKeys($table)
204:     {
205:         /* Does't work with multicolumn foreign keys */
206:         return $this->connection->query("
207:             SELECT
208:                 co.conname::varchar AS name,
209:                 al.attname::varchar AS local,
210:                 cf.relname::varchar AS table,
211:                 af.attname::varchar AS foreign
212:             FROM
213:                 pg_catalog.pg_constraint AS co
214:                 JOIN pg_catalog.pg_namespace AS n ON co.connamespace = n.oid
215:                 JOIN pg_catalog.pg_class AS cl ON co.conrelid = cl.oid
216:                 JOIN pg_catalog.pg_class AS cf ON co.confrelid = cf.oid
217:                 JOIN pg_catalog.pg_attribute AS al ON al.attrelid = cl.oid AND al.attnum = co.conkey[1]
218:                 JOIN pg_catalog.pg_attribute AS af ON af.attrelid = cf.oid AND af.attnum = co.confkey[1]
219:             WHERE
220:                 ARRAY[n.nspname] <@ pg_catalog.current_schemas(FALSE)
221:                 AND co.contype = 'f'
222:                 AND cl.relname = {$this->connection->quote($table)}
223:         ")->fetchAll();
224:     }
225: 
226: 
227:     /**
228:      * @return bool
229:      */
230:     public function isSupported($item)
231:     {
232:         return $item === self::SUPPORT_COLUMNS_META || $item === self::SUPPORT_SEQUENCE;
233:     }
234: 
235: }
236: