1: <?php
2:
3: 4: 5: 6:
7:
8: namespace Nette\Database\Drivers;
9:
10: use Nette;
11:
12:
13: 14: 15:
16: class PgSqlDriver implements Nette\Database\ISupplementalDriver
17: {
18: use Nette\SmartObject;
19:
20:
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:
55:
56:
57: 58: 59:
60: public function delimite($name)
61: {
62:
63: return '"' . str_replace('"', '""', $name) . '"';
64: }
65:
66:
67: 68: 69:
70: public function formatBool($value)
71: {
72: return $value ? 'TRUE' : 'FALSE';
73: }
74:
75:
76: 77: 78:
79: public function formatDateTime( $value)
80: {
81: return $value->format("'Y-m-d H:i:s'");
82: }
83:
84:
85: 86: 87:
88: public function formatDateInterval(\DateInterval $value)
89: {
90: throw new Nette\NotSupportedException;
91: }
92:
93:
94: 95: 96:
97: public function formatLike($value, $pos)
98: {
99: $bs = substr($this->connection->quote('\\'), 1, -1);
100: $value = substr($this->connection->quote($value), 1, -1);
101: $value = strtr($value, ['%' => $bs . '%', '_' => $bs . '_', '\\' => '\\\\']);
102: return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'");
103: }
104:
105:
106: 107: 108:
109: public function applyLimit(&$sql, $limit, $offset)
110: {
111: if ($limit < 0 || $offset < 0) {
112: throw new Nette\InvalidArgumentException('Negative offset or limit.');
113: }
114: if ($limit !== null) {
115: $sql .= ' LIMIT ' . (int) $limit;
116: }
117: if ($offset) {
118: $sql .= ' OFFSET ' . (int) $offset;
119: }
120: }
121:
122:
123: 124: 125:
126: public function normalizeRow($row)
127: {
128: return $row;
129: }
130:
131:
132:
133:
134:
135: 136: 137:
138: public function getTables()
139: {
140: $tables = [];
141: foreach ($this->connection->query("
142: SELECT DISTINCT ON (c.relname)
143: c.relname::varchar AS name,
144: c.relkind IN ('v', 'm') AS view,
145: n.nspname::varchar || '.' || c.relname::varchar AS \"fullName\"
146: FROM
147: pg_catalog.pg_class AS c
148: JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
149: WHERE
150: c.relkind IN ('r', 'v', 'm')
151: AND n.nspname = ANY (pg_catalog.current_schemas(FALSE))
152: ORDER BY
153: c.relname
154: ") as $row) {
155: $tables[] = (array) $row;
156: }
157:
158: return $tables;
159: }
160:
161:
162: 163: 164:
165: public function getColumns($table)
166: {
167: $columns = [];
168: foreach ($this->connection->query("
169: SELECT
170: a.attname::varchar AS name,
171: c.relname::varchar AS table,
172: upper(t.typname) AS nativetype,
173: CASE WHEN a.atttypmod = -1 THEN NULL ELSE a.atttypmod -4 END AS size,
174: FALSE AS unsigned,
175: NOT (a.attnotnull OR t.typtype = 'd' AND t.typnotnull) AS nullable,
176: pg_catalog.pg_get_expr(ad.adbin, 'pg_catalog.pg_attrdef'::regclass)::varchar AS default,
177: coalesce(co.contype = 'p' AND strpos(ad.adsrc, 'nextval') = 1, FALSE) AS autoincrement,
178: coalesce(co.contype = 'p', FALSE) AS primary,
179: substring(pg_catalog.pg_get_expr(ad.adbin, 'pg_catalog.pg_attrdef'::regclass) from 'nextval[(]''\"?([^''\"]+)') AS sequence
180: FROM
181: pg_catalog.pg_attribute AS a
182: JOIN pg_catalog.pg_class AS c ON a.attrelid = c.oid
183: JOIN pg_catalog.pg_type AS t ON a.atttypid = t.oid
184: LEFT JOIN pg_catalog.pg_attrdef AS ad ON ad.adrelid = c.oid AND ad.adnum = a.attnum
185: 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)
186: WHERE
187: c.relkind IN ('r', 'v')
188: AND c.oid = {$this->connection->quote($this->delimiteFQN($table))}::regclass
189: AND a.attnum > 0
190: AND NOT a.attisdropped
191: ORDER BY
192: a.attnum
193: ") as $row) {
194: $column = (array) $row;
195: $column['vendor'] = $column;
196: unset($column['sequence']);
197:
198: $columns[] = $column;
199: }
200:
201: return $columns;
202: }
203:
204:
205: 206: 207:
208: public function getIndexes($table)
209: {
210: $indexes = [];
211: foreach ($this->connection->query("
212: SELECT
213: c2.relname::varchar AS name,
214: i.indisunique AS unique,
215: i.indisprimary AS primary,
216: a.attname::varchar AS column
217: FROM
218: pg_catalog.pg_class AS c1
219: JOIN pg_catalog.pg_index AS i ON c1.oid = i.indrelid
220: JOIN pg_catalog.pg_class AS c2 ON i.indexrelid = c2.oid
221: LEFT JOIN pg_catalog.pg_attribute AS a ON c1.oid = a.attrelid AND a.attnum = ANY(i.indkey)
222: WHERE
223: c1.relkind = 'r'
224: AND c1.oid = {$this->connection->quote($this->delimiteFQN($table))}::regclass
225: ") as $row) {
226: $indexes[$row['name']]['name'] = $row['name'];
227: $indexes[$row['name']]['unique'] = $row['unique'];
228: $indexes[$row['name']]['primary'] = $row['primary'];
229: $indexes[$row['name']]['columns'][] = $row['column'];
230: }
231:
232: return array_values($indexes);
233: }
234:
235:
236: 237: 238:
239: public function getForeignKeys($table)
240: {
241:
242: return $this->connection->query("
243: SELECT
244: co.conname::varchar AS name,
245: al.attname::varchar AS local,
246: nf.nspname || '.' || cf.relname::varchar AS table,
247: af.attname::varchar AS foreign
248: FROM
249: pg_catalog.pg_constraint AS co
250: JOIN pg_catalog.pg_class AS cl ON co.conrelid = cl.oid
251: JOIN pg_catalog.pg_class AS cf ON co.confrelid = cf.oid
252: JOIN pg_catalog.pg_namespace AS nf ON nf.oid = cf.relnamespace
253: JOIN pg_catalog.pg_attribute AS al ON al.attrelid = cl.oid AND al.attnum = co.conkey[1]
254: JOIN pg_catalog.pg_attribute AS af ON af.attrelid = cf.oid AND af.attnum = co.confkey[1]
255: WHERE
256: co.contype = 'f'
257: AND cl.oid = {$this->connection->quote($this->delimiteFQN($table))}::regclass
258: AND nf.nspname = ANY (pg_catalog.current_schemas(FALSE))
259: ")->fetchAll();
260: }
261:
262:
263: 264: 265:
266: public function getColumnTypes(\PDOStatement $statement)
267: {
268: return Nette\Database\Helpers::detectTypes($statement);
269: }
270:
271:
272: 273: 274: 275:
276: public function isSupported($item)
277: {
278: return $item === self::SUPPORT_SEQUENCE || $item === self::SUPPORT_SUBSELECT || $item === self::SUPPORT_SCHEMA;
279: }
280:
281:
282: 283: 284: 285: 286:
287: private function delimiteFQN($name)
288: {
289: return implode('.', array_map([$this, 'delimite'], explode('.', $name)));
290: }
291: }
292: