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