1: <?php
  2: 
  3: /**
  4:  * This file is part of the Nette Framework (https://nette.org)
  5:  * Copyright (c) 2004 David Grudl (https://davidgrudl.com)
  6:  */
  7: 
  8: namespace Nette\Database\Drivers;
  9: 
 10: use Nette;
 11: 
 12: 
 13: /**
 14:  * Supplemental PostgreSQL database driver.
 15:  */
 16: class PgSqlDriver extends Nette\Object implements Nette\Database\ISupplementalDriver
 17: {
 18:     /** @var Nette\Database\Connection */
 19:     private $connection;
 20: 
 21: 
 22:     public function __construct(Nette\Database\Connection $connection, array $options)
 23:     {
 24:         $this->connection = $connection;
 25:     }
 26: 
 27: 
 28:     public function convertException(\PDOException $e)
 29:     {
 30:         $code = isset($e->errorInfo[0]) ? $e->errorInfo[0] : NULL;
 31:         if ($code === '0A000' && strpos($e->getMessage(), 'truncate') !== FALSE) {
 32:             return Nette\Database\ForeignKeyConstraintViolationException::from($e);
 33: 
 34:         } elseif ($code === '23502') {
 35:             return Nette\Database\NotNullConstraintViolationException::from($e);
 36: 
 37:         } elseif ($code === '23503') {
 38:             return Nette\Database\ForeignKeyConstraintViolationException::from($e);
 39: 
 40:         } elseif ($code === '23505') {
 41:             return Nette\Database\UniqueConstraintViolationException::from($e);
 42: 
 43:         } elseif ($code === '08006') {
 44:             return Nette\Database\ConnectionException::from($e);
 45: 
 46:         } else {
 47:             return Nette\Database\DriverException::from($e);
 48:         }
 49:     }
 50: 
 51: 
 52:     /********************* SQL ****************d*g**/
 53: 
 54: 
 55:     /**
 56:      * Delimites identifier for use in a SQL statement.
 57:      */
 58:     public function delimite($name)
 59:     {
 60:         // @see http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
 61:         return '"' . str_replace('"', '""', $name) . '"';
 62:     }
 63: 
 64: 
 65:     /**
 66:      * Formats boolean for use in a SQL statement.
 67:      */
 68:     public function formatBool($value)
 69:     {
 70:         return $value ? 'TRUE' : 'FALSE';
 71:     }
 72: 
 73: 
 74:     /**
 75:      * Formats date-time for use in a SQL statement.
 76:      */
 77:     public function formatDateTime(/*\DateTimeInterface*/ $value)
 78:     {
 79:         return $value->format("'Y-m-d H:i:s'");
 80:     }
 81: 
 82: 
 83:     /**
 84:      * Formats date-time interval for use in a SQL statement.
 85:      */
 86:     public function formatDateInterval(\DateInterval $value)
 87:     {
 88:         throw new Nette\NotSupportedException;
 89:     }
 90: 
 91: 
 92:     /**
 93:      * Encodes string for use in a LIKE statement.
 94:      */
 95:     public function formatLike($value, $pos)
 96:     {
 97:         $bs = substr($this->connection->quote('\\', \PDO::PARAM_STR), 1, -1); // standard_conforming_strings = on/off
 98:         $value = substr($this->connection->quote($value, \PDO::PARAM_STR), 1, -1);
 99:         $value = strtr($value, array('%' => $bs . '%', '_' => $bs . '_', '\\' => '\\\\'));
100:         return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'");
101:     }
102: 
103: 
104:     /**
105:      * Injects LIMIT/OFFSET to the SQL query.
106:      */
107:     public function applyLimit(& $sql, $limit, $offset)
108:     {
109:         if ($limit < 0 || $offset < 0) {
110:             throw new Nette\InvalidArgumentException('Negative offset or limit.');
111:         }
112:         if ($limit !== NULL) {
113:             $sql .= ' LIMIT ' . (int) $limit;
114:         }
115:         if ($offset) {
116:             $sql .= ' OFFSET ' . (int) $offset;
117:         }
118:     }
119: 
120: 
121:     /**
122:      * Normalizes result row.
123:      */
124:     public function normalizeRow($row)
125:     {
126:         return $row;
127:     }
128: 
129: 
130:     /********************* reflection ****************d*g**/
131: 
132: 
133:     /**
134:      * Returns list of tables.
135:      */
136:     public function getTables()
137:     {
138:         $tables = array();
139:         foreach ($this->connection->query("
140:             SELECT DISTINCT ON (c.relname)
141:                 c.relname::varchar AS name,
142:                 c.relkind = 'v' AS view,
143:                 n.nspname::varchar || '.' || c.relname::varchar AS \"fullName\"
144:             FROM
145:                 pg_catalog.pg_class AS c
146:                 JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
147:             WHERE
148:                 c.relkind IN ('r', 'v')
149:                 AND n.nspname = ANY (pg_catalog.current_schemas(FALSE))
150:             ORDER BY
151:                 c.relname
152:         ") as $row) {
153:             $tables[] = (array) $row;
154:         }
155: 
156:         return $tables;
157:     }
158: 
159: 
160:     /**
161:      * Returns metadata for all columns in a table.
162:      */
163:     public function getColumns($table)
164:     {
165:         $columns = array();
166:         foreach ($this->connection->query("
167:             SELECT
168:                 a.attname::varchar AS name,
169:                 c.relname::varchar AS table,
170:                 upper(t.typname) AS nativetype,
171:                 CASE WHEN a.atttypmod = -1 THEN NULL ELSE a.atttypmod -4 END AS size,
172:                 FALSE AS unsigned,
173:                 NOT (a.attnotnull OR t.typtype = 'd' AND t.typnotnull) AS nullable,
174:                 pg_catalog.pg_get_expr(ad.adbin, 'pg_catalog.pg_attrdef'::regclass)::varchar AS default,
175:                 coalesce(co.contype = 'p' AND strpos(ad.adsrc, 'nextval') = 1, FALSE) AS autoincrement,
176:                 coalesce(co.contype = 'p', FALSE) AS primary,
177:                 substring(pg_catalog.pg_get_expr(ad.adbin, 'pg_catalog.pg_attrdef'::regclass) from 'nextval[(]''\"?([^''\"]+)') AS sequence
178:             FROM
179:                 pg_catalog.pg_attribute AS a
180:                 JOIN pg_catalog.pg_class AS c ON a.attrelid = c.oid
181:                 JOIN pg_catalog.pg_type AS t ON a.atttypid = t.oid
182:                 LEFT JOIN pg_catalog.pg_attrdef AS ad ON ad.adrelid = c.oid AND ad.adnum = a.attnum
183:                 LEFT JOIN pg_catalog.pg_constraint AS co ON co.connamespace = c.relnamespace AND contype = 'p' AND co.conrelid = c.oid AND a.attnum = ANY(co.conkey)
184:             WHERE
185:                 c.relkind IN ('r', 'v')
186:                 AND c.oid = {$this->connection->quote($this->delimiteFQN($table))}::regclass
187:                 AND a.attnum > 0
188:                 AND NOT a.attisdropped
189:             ORDER BY
190:                 a.attnum
191:         ") as $row) {
192:             $column = (array) $row;
193:             $column['vendor'] = $column;
194:             unset($column['sequence']);
195: 
196:             $columns[] = $column;
197:         }
198: 
199:         return $columns;
200:     }
201: 
202: 
203:     /**
204:      * Returns metadata for all indexes in a table.
205:      */
206:     public function getIndexes($table)
207:     {
208:         $indexes = array();
209:         foreach ($this->connection->query("
210:             SELECT
211:                 c2.relname::varchar AS name,
212:                 i.indisunique AS unique,
213:                 i.indisprimary AS primary,
214:                 a.attname::varchar AS column
215:             FROM
216:                 pg_catalog.pg_class AS c1
217:                 JOIN pg_catalog.pg_index AS i ON c1.oid = i.indrelid
218:                 JOIN pg_catalog.pg_class AS c2 ON i.indexrelid = c2.oid
219:                 LEFT JOIN pg_catalog.pg_attribute AS a ON c1.oid = a.attrelid AND a.attnum = ANY(i.indkey)
220:             WHERE
221:                 c1.relkind = 'r'
222:                 AND c1.oid = {$this->connection->quote($this->delimiteFQN($table))}::regclass
223:         ") as $row) {
224:             $indexes[$row['name']]['name'] = $row['name'];
225:             $indexes[$row['name']]['unique'] = $row['unique'];
226:             $indexes[$row['name']]['primary'] = $row['primary'];
227:             $indexes[$row['name']]['columns'][] = $row['column'];
228:         }
229: 
230:         return array_values($indexes);
231:     }
232: 
233: 
234:     /**
235:      * Returns metadata for all foreign keys in a table.
236:      */
237:     public function getForeignKeys($table)
238:     {
239:         /* Does't work with multicolumn foreign keys */
240:         return $this->connection->query("
241:             SELECT
242:                 co.conname::varchar AS name,
243:                 al.attname::varchar AS local,
244:                 nf.nspname || '.' || cf.relname::varchar AS table,
245:                 af.attname::varchar AS foreign
246:             FROM
247:                 pg_catalog.pg_constraint AS co
248:                 JOIN pg_catalog.pg_class AS cl ON co.conrelid = cl.oid
249:                 JOIN pg_catalog.pg_class AS cf ON co.confrelid = cf.oid
250:                 JOIN pg_catalog.pg_namespace AS nf ON nf.oid = cf.relnamespace
251:                 JOIN pg_catalog.pg_attribute AS al ON al.attrelid = cl.oid AND al.attnum = co.conkey[1]
252:                 JOIN pg_catalog.pg_attribute AS af ON af.attrelid = cf.oid AND af.attnum = co.confkey[1]
253:             WHERE
254:                 co.contype = 'f'
255:                 AND cl.oid = {$this->connection->quote($this->delimiteFQN($table))}::regclass
256:                 AND nf.nspname = ANY (pg_catalog.current_schemas(FALSE))
257:         ")->fetchAll();
258:     }
259: 
260: 
261:     /**
262:      * Returns associative array of detected types (IReflection::FIELD_*) in result set.
263:      */
264:     public function getColumnTypes(\PDOStatement $statement)
265:     {
266:         return Nette\Database\Helpers::detectTypes($statement);
267:     }
268: 
269: 
270:     /**
271:      * @param  string
272:      * @return bool
273:      */
274:     public function isSupported($item)
275:     {
276:         return $item === self::SUPPORT_SEQUENCE || $item === self::SUPPORT_SUBSELECT || $item === self::SUPPORT_SCHEMA;
277:     }
278: 
279: 
280:     /**
281:      * Converts: schema.name => "schema"."name"
282:      * @param  string
283:      * @return string
284:      */
285:     private function delimiteFQN($name)
286:     {
287:         return implode('.', array_map(array($this, 'delimite'), explode('.', $name)));
288:     }
289: 
290: }
291: