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