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