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:
23: class PgSqlDriver extends Nette\Object implements Nette\Database\ISupplementalDriver
24: {
25:
26: private $connection;
27:
28:
29:
30: public function __construct(Nette\Database\Connection $connection, array $options)
31: {
32: $this->connection = $connection;
33: }
34:
35:
36:
37:
38:
39:
40:
41: 42: 43:
44: public function delimite($name)
45: {
46:
47: return '"' . str_replace('"', '""', $name) . '"';
48: }
49:
50:
51:
52: 53: 54:
55: public function formatBool($value)
56: {
57: return $value ? 'TRUE' : 'FALSE';
58: }
59:
60:
61:
62: 63: 64:
65: public function formatDateTime(\DateTime $value)
66: {
67: return $value->format("'Y-m-d H:i:s'");
68: }
69:
70:
71:
72: 73: 74:
75: public function formatLike($value, $pos)
76: {
77: $value = strtr($value, array("'" => "''", '\\' => '\\\\', '%' => '\\\\%', '_' => '\\\\_'));
78: return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'");
79: }
80:
81:
82:
83: 84: 85:
86: public function applyLimit(&$sql, $limit, $offset)
87: {
88: if ($limit >= 0)
89: $sql .= ' LIMIT ' . (int) $limit;
90:
91: if ($offset > 0)
92: $sql .= ' OFFSET ' . (int) $offset;
93: }
94:
95:
96:
97: 98: 99:
100: public function normalizeRow($row, $statement)
101: {
102: return $row;
103: }
104:
105:
106:
107:
108:
109:
110:
111: 112: 113:
114: public function getTables()
115: {
116: $tables = array();
117: foreach ($this->connection->query("
118: SELECT
119: table_name AS name,
120: table_type = 'VIEW' AS view
121: FROM
122: information_schema.tables
123: WHERE
124: table_schema = current_schema()
125: ") as $row) {
126: $tables[] = (array) $row;
127: }
128:
129: return $tables;
130: }
131:
132:
133:
134: 135: 136:
137: public function getColumns($table)
138: {
139: $columns = array();
140: foreach ($this->connection->query("
141: SELECT
142: c.column_name AS name,
143: c.table_name AS table,
144: upper(c.udt_name) AS nativetype,
145: greatest(c.character_maximum_length, c.numeric_precision) AS size,
146: FALSE AS unsigned,
147: c.is_nullable = 'YES' AS nullable,
148: c.column_default AS default,
149: coalesce(tc.constraint_type = 'PRIMARY KEY', FALSE) AND strpos(c.column_default, 'nextval') = 1 AS autoincrement,
150: coalesce(tc.constraint_type = 'PRIMARY KEY', FALSE) AS primary,
151: substring(c.column_default from 'nextval[(]''\"?([^''\"]+)') AS sequence
152: FROM
153: information_schema.columns AS c
154: LEFT JOIN information_schema.constraint_column_usage AS ccu USING(table_catalog, table_schema, table_name, column_name)
155: LEFT JOIN information_schema.table_constraints AS tc USING(constraint_catalog, constraint_schema, constraint_name)
156: WHERE
157: c.table_name = {$this->connection->quote($table)}
158: AND
159: c.table_schema = current_schema()
160: AND
161: (tc.constraint_type IS NULL OR tc.constraint_type = 'PRIMARY KEY')
162: ORDER BY
163: c.ordinal_position
164: ") as $row) {
165: $column = (array) $row;
166: $column['vendor'] = $column;
167: unset($column['sequence']);
168:
169: $columns[] = $column;
170: }
171:
172: return $columns;
173: }
174:
175:
176:
177: 178: 179:
180: public function getIndexes($table)
181: {
182:
183: $indexes = array();
184: foreach ($this->connection->query("
185: SELECT
186: c2.relname AS name,
187: indisunique AS unique,
188: indisprimary AS primary,
189: attname AS column
190: FROM
191: pg_class AS c1
192: JOIN pg_namespace ON c1.relnamespace = pg_namespace.oid
193: JOIN pg_index ON c1.oid = indrelid
194: JOIN pg_class AS c2 ON indexrelid = c2.oid
195: LEFT JOIN pg_attribute ON c1.oid = attrelid AND attnum = ANY(indkey)
196: WHERE
197: nspname = current_schema()
198: AND
199: c1.relkind = 'r'
200: AND
201: c1.relname = {$this->connection->quote($table)}
202: ") as $row) {
203: $indexes[$row['name']]['name'] = $row['name'];
204: $indexes[$row['name']]['unique'] = $row['unique'];
205: $indexes[$row['name']]['primary'] = $row['primary'];
206: $indexes[$row['name']]['columns'][] = $row['column'];
207: }
208:
209: return array_values($indexes);
210: }
211:
212:
213:
214: 215: 216:
217: public function getForeignKeys($table)
218: {
219:
220: $keys = array();
221: foreach ($this->connection->query("
222: SELECT
223: tc.constraint_name AS name,
224: kcu.column_name AS local,
225: ccu.table_name AS table,
226: ccu.column_name AS foreign
227: FROM
228: information_schema.table_constraints AS tc
229: JOIN information_schema.key_column_usage AS kcu USING(constraint_catalog, constraint_schema, constraint_name)
230: JOIN information_schema.constraint_column_usage AS ccu USING(constraint_catalog, constraint_schema, constraint_name)
231: WHERE
232: constraint_type = 'FOREIGN KEY'
233: AND
234: tc.table_name = {$this->connection->quote($table)}
235: ORDER BY
236: kcu.ordinal_position
237: ") as $row) {
238: $keys[] = (array) $row;
239: }
240:
241: return $keys;
242: }
243:
244:
245:
246: 247: 248:
249: public function isSupported($item)
250: {
251: return $item === self::SUPPORT_COLUMNS_META || $item === self::SUPPORT_SEQUENCE;
252: }
253:
254: }
255: