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