3 * Zend Framework (http://framework.zend.com/)
5 * @link http://github.com/zendframework/zf2 for the canonical source repository
6 * @copyright Copyright (c) 2005-2013 Zend Technologies USA Inc. (http://www.zend.com)
7 * @license http://framework.zend.com/license/new-bsd New BSD License
10 namespace Zend\Db\Metadata\Source
;
12 use Zend\Db\Adapter\Adapter
;
14 class PostgresqlMetadata
extends AbstractSource
17 protected function loadSchemaData()
19 if (isset($this->data
['schemas'])) {
22 $this->prepareDataHierarchy('schemas');
24 $p = $this->adapter
->getPlatform();
26 $sql = 'SELECT ' . $p->quoteIdentifier('schema_name')
27 . ' FROM ' . $p->quoteIdentifierChain(array('information_schema', 'schemata'))
28 . ' WHERE ' . $p->quoteIdentifier('schema_name')
29 . ' != \'information_schema\''
30 . ' AND ' . $p->quoteIdentifier('schema_name') . " NOT LIKE 'pg_%'";
32 $results = $this->adapter
->query($sql, Adapter
::QUERY_MODE_EXECUTE
);
35 foreach ($results->toArray() as $row) {
36 $schemas[] = $row['schema_name'];
39 $this->data
['schemas'] = $schemas;
42 protected function loadTableNameData($schema)
44 if (isset($this->data
['table_names'][$schema])) {
47 $this->prepareDataHierarchy('table_names', $schema);
49 $p = $this->adapter
->getPlatform();
52 array('t', 'table_name'),
53 array('t', 'table_type'),
54 array('v', 'view_definition'),
55 array('v', 'check_option'),
56 array('v', 'is_updatable'),
59 array_walk($isColumns, function (&$c) use ($p) { $c = $p->quoteIdentifierChain($c); });
61 $sql = 'SELECT ' . implode(', ', $isColumns)
62 . ' FROM ' . $p->quoteIdentifierChain(array('information_schema', 'tables')) . ' t'
64 . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'views')) . ' v'
65 . ' ON ' . $p->quoteIdentifierChain(array('t', 'table_schema'))
66 . ' = ' . $p->quoteIdentifierChain(array('v', 'table_schema'))
67 . ' AND ' . $p->quoteIdentifierChain(array('t', 'table_name'))
68 . ' = ' . $p->quoteIdentifierChain(array('v', 'table_name'))
70 . ' WHERE ' . $p->quoteIdentifierChain(array('t', 'table_type'))
71 . ' IN (\'BASE TABLE\', \'VIEW\')';
73 if ($schema != self
::DEFAULT_SCHEMA
) {
74 $sql .= ' AND ' . $p->quoteIdentifierChain(array('t', 'table_schema'))
75 . ' = ' . $p->quoteTrustedValue($schema);
77 $sql .= ' AND ' . $p->quoteIdentifierChain(array('t', 'table_schema'))
78 . ' != \'information_schema\'';
81 $results = $this->adapter
->query($sql, Adapter
::QUERY_MODE_EXECUTE
);
84 foreach ($results->toArray() as $row) {
85 $tables[$row['table_name']] = array(
86 'table_type' => $row['table_type'],
87 'view_definition' => $row['view_definition'],
88 'check_option' => $row['check_option'],
89 'is_updatable' => ('YES' == $row['is_updatable']),
93 $this->data
['table_names'][$schema] = $tables;
96 protected function loadColumnData($table, $schema)
98 if (isset($this->data
['columns'][$schema][$table])) {
102 $this->prepareDataHierarchy('columns', $schema, $table);
104 $platform = $this->adapter
->getPlatform();
113 'character_maximum_length',
114 'character_octet_length',
119 array_walk($isColumns, function (&$c) use ($platform) { $c = $platform->quoteIdentifier($c); });
121 $sql = 'SELECT ' . implode(', ', $isColumns)
122 . ' FROM ' . $platform->quoteIdentifier('information_schema')
123 . $platform->getIdentifierSeparator() . $platform->quoteIdentifier('columns')
124 . ' WHERE ' . $platform->quoteIdentifier('table_schema')
125 . ' != \'information\''
126 . ' AND ' . $platform->quoteIdentifier('table_name')
127 . ' = ' . $platform->quoteTrustedValue($table);
129 if ($schema != '__DEFAULT_SCHEMA__') {
130 $sql .= ' AND ' . $platform->quoteIdentifier('table_schema')
131 . ' = ' . $platform->quoteTrustedValue($schema);
134 $results = $this->adapter
->query($sql, Adapter
::QUERY_MODE_EXECUTE
);
136 foreach ($results->toArray() as $row) {
137 $columns[$row['column_name']] = array(
138 'ordinal_position' => $row['ordinal_position'],
139 'column_default' => $row['column_default'],
140 'is_nullable' => ('YES' == $row['is_nullable']),
141 'data_type' => $row['data_type'],
142 'character_maximum_length' => $row['character_maximum_length'],
143 'character_octet_length' => $row['character_octet_length'],
144 'numeric_precision' => $row['numeric_precision'],
145 'numeric_scale' => $row['numeric_scale'],
146 'numeric_unsigned' => null,
147 'erratas' => array(),
151 $this->data
['columns'][$schema][$table] = $columns;
154 protected function loadConstraintData($table, $schema)
156 if (isset($this->data
['constraints'][$schema][$table])) {
160 $this->prepareDataHierarchy('constraints', $schema, $table);
163 array('t', 'table_name'),
164 array('tc', 'constraint_name'),
165 array('tc', 'constraint_type'),
166 array('kcu', 'column_name'),
167 array('cc', 'check_clause'),
168 array('rc', 'match_option'),
169 array('rc', 'update_rule'),
170 array('rc', 'delete_rule'),
171 array('referenced_table_schema' => 'kcu2', 'table_schema'),
172 array('referenced_table_name' => 'kcu2', 'table_name'),
173 array('referenced_column_name' => 'kcu2', 'column_name'),
176 $p = $this->adapter
->getPlatform();
178 array_walk($isColumns, function (&$c) use ($p) {
180 $c = $p->quoteIdentifierChain($c);
181 if (is_string($alias)) {
182 $c .= ' ' . $p->quoteIdentifier($alias);
186 $sql = 'SELECT ' . implode(', ', $isColumns)
187 . ' FROM ' . $p->quoteIdentifierChain(array('information_schema', 'tables')) . ' t'
189 . ' INNER JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'table_constraints')) . ' tc'
190 . ' ON ' . $p->quoteIdentifierChain(array('t', 'table_schema'))
191 . ' = ' . $p->quoteIdentifierChain(array('tc', 'table_schema'))
192 . ' AND ' . $p->quoteIdentifierChain(array('t', 'table_name'))
193 . ' = ' . $p->quoteIdentifierChain(array('tc', 'table_name'))
195 . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'key_column_usage')) . ' kcu'
196 . ' ON ' . $p->quoteIdentifierChain(array('tc', 'table_schema'))
197 . ' = ' . $p->quoteIdentifierChain(array('kcu', 'table_schema'))
198 . ' AND ' . $p->quoteIdentifierChain(array('tc', 'table_name'))
199 . ' = ' . $p->quoteIdentifierChain(array('kcu', 'table_name'))
200 . ' AND ' . $p->quoteIdentifierChain(array('tc', 'constraint_name'))
201 . ' = ' . $p->quoteIdentifierChain(array('kcu', 'constraint_name'))
203 . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'check_constraints')) . ' cc'
204 . ' ON ' . $p->quoteIdentifierChain(array('tc', 'constraint_schema'))
205 . ' = ' . $p->quoteIdentifierChain(array('cc', 'constraint_schema'))
206 . ' AND ' . $p->quoteIdentifierChain(array('tc', 'constraint_name'))
207 . ' = ' . $p->quoteIdentifierChain(array('cc', 'constraint_name'))
209 . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'referential_constraints')) . ' rc'
210 . ' ON ' . $p->quoteIdentifierChain(array('tc', 'constraint_schema'))
211 . ' = ' . $p->quoteIdentifierChain(array('rc', 'constraint_schema'))
212 . ' AND ' . $p->quoteIdentifierChain(array('tc', 'constraint_name'))
213 . ' = ' . $p->quoteIdentifierChain(array('rc', 'constraint_name'))
215 . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'key_column_usage')) . ' kcu2'
216 . ' ON ' . $p->quoteIdentifierChain(array('rc', 'unique_constraint_schema'))
217 . ' = ' . $p->quoteIdentifierChain(array('kcu2', 'constraint_schema'))
218 . ' AND ' . $p->quoteIdentifierChain(array('rc', 'unique_constraint_name'))
219 . ' = ' . $p->quoteIdentifierChain(array('kcu2', 'constraint_name'))
220 . ' AND ' . $p->quoteIdentifierChain(array('kcu', 'position_in_unique_constraint'))
221 . ' = ' . $p->quoteIdentifierChain(array('kcu2', 'ordinal_position'))
223 . ' WHERE ' . $p->quoteIdentifierChain(array('t', 'table_name'))
224 . ' = ' . $p->quoteTrustedValue($table)
225 . ' AND ' . $p->quoteIdentifierChain(array('t', 'table_type'))
226 . ' IN (\'BASE TABLE\', \'VIEW\')';
228 if ($schema != self
::DEFAULT_SCHEMA
) {
229 $sql .= ' AND ' . $p->quoteIdentifierChain(array('t', 'table_schema'))
230 . ' = ' . $p->quoteTrustedValue($schema);
232 $sql .= ' AND ' . $p->quoteIdentifierChain(array('t', 'table_schema'))
233 . ' != \'information_schema\'';
236 $sql .= ' ORDER BY CASE ' . $p->quoteIdentifierChain(array('tc', 'constraint_type'))
237 . " WHEN 'PRIMARY KEY' THEN 1"
238 . " WHEN 'UNIQUE' THEN 2"
239 . " WHEN 'FOREIGN KEY' THEN 3"
240 . " WHEN 'CHECK' THEN 4"
242 . ', ' . $p->quoteIdentifierChain(array('tc', 'constraint_name'))
243 . ', ' . $p->quoteIdentifierChain(array('kcu', 'ordinal_position'));
245 $results = $this->adapter
->query($sql, Adapter
::QUERY_MODE_EXECUTE
);
248 $constraints = array();
249 foreach ($results->toArray() as $row) {
250 if ($row['constraint_name'] !== $name) {
251 $name = $row['constraint_name'];
252 $constraints[$name] = array(
253 'constraint_name' => $name,
254 'constraint_type' => $row['constraint_type'],
255 'table_name' => $row['table_name'],
257 if ('CHECK' == $row['constraint_type']) {
258 $constraints[$name]['check_clause'] = $row['check_clause'];
261 $constraints[$name]['columns'] = array();
262 $isFK = ('FOREIGN KEY' == $row['constraint_type']);
264 $constraints[$name]['referenced_table_schema'] = $row['referenced_table_schema'];
265 $constraints[$name]['referenced_table_name'] = $row['referenced_table_name'];
266 $constraints[$name]['referenced_columns'] = array();
267 $constraints[$name]['match_option'] = $row['match_option'];
268 $constraints[$name]['update_rule'] = $row['update_rule'];
269 $constraints[$name]['delete_rule'] = $row['delete_rule'];
272 $constraints[$name]['columns'][] = $row['column_name'];
274 $constraints[$name]['referenced_columns'][] = $row['referenced_column_name'];
278 $this->data
['constraints'][$schema][$table] = $constraints;
281 protected function loadTriggerData($schema)
283 if (isset($this->data
['triggers'][$schema])) {
287 $this->prepareDataHierarchy('triggers', $schema);
289 $p = $this->adapter
->getPlatform();
293 'event_manipulation',
294 'event_object_catalog',
295 'event_object_schema',
296 'event_object_table',
300 'action_orientation',
301 array('action_timing' => 'condition_timing'),
302 array('action_reference_old_table' => 'condition_reference_old_table'),
303 array('action_reference_new_table' => 'condition_reference_new_table'),
307 array_walk($isColumns, function (&$c) use ($p) {
310 $c = $p->quoteIdentifierChain($c);
311 if (is_string($alias)) {
312 $c .= ' ' . $p->quoteIdentifier($alias);
315 $c = $p->quoteIdentifier($c);
319 $sql = 'SELECT ' . implode(', ', $isColumns)
320 . ' FROM ' . $p->quoteIdentifierChain(array('information_schema', 'triggers'))
323 if ($schema != self
::DEFAULT_SCHEMA
) {
324 $sql .= $p->quoteIdentifier('trigger_schema')
325 . ' = ' . $p->quoteTrustedValue($schema);
327 $sql .= $p->quoteIdentifier('trigger_schema')
328 . ' != \'information_schema\'';
331 $results = $this->adapter
->query($sql, Adapter
::QUERY_MODE_EXECUTE
);
334 foreach ($results->toArray() as $row) {
335 $row = array_change_key_case($row, CASE_LOWER
);
336 $row['action_reference_old_row'] = 'OLD';
337 $row['action_reference_new_row'] = 'NEW';
338 if (null !== $row['created']) {
339 $row['created'] = new \
DateTime($row['created']);
341 $data[$row['trigger_name']] = $row;
344 $this->data
['triggers'][$schema] = $data;