|
1
|
|
|
<?php |
|
2
|
|
|
namespace ntentan\atiaa\descriptors; |
|
3
|
|
|
|
|
4
|
|
|
class PostgresqlDescriptor extends InformationSchemaDescriptor |
|
5
|
|
|
{ |
|
|
|
|
|
|
6
|
1 |
|
protected function cleanDefaultValue($defaultValue) |
|
|
|
|
|
|
7
|
|
|
{ |
|
8
|
|
|
// Deal with typecasts |
|
9
|
1 |
|
if(preg_match("/(?<value>.*)(::)(?<type>[a-zA-Z0-9\s]*)$/", $defaultValue, $matches)) { |
|
10
|
|
|
|
|
11
|
1 |
|
$value = $matches['value']; |
|
12
|
|
|
|
|
13
|
|
|
// If numeric |
|
14
|
1 |
|
if(is_numeric($value)) { |
|
15
|
|
|
return $value; |
|
16
|
|
|
|
|
17
|
|
|
// If its a string |
|
18
|
1 |
|
} else if (preg_match("/'(?<string>.*)'/", $value, $matches)) { |
|
19
|
1 |
|
return $matches['string']; |
|
20
|
|
|
|
|
21
|
|
|
// null for anything else |
|
|
|
|
|
|
22
|
|
|
} else { |
|
23
|
1 |
|
return null; |
|
24
|
|
|
} |
|
25
|
|
|
|
|
26
|
|
|
// Return the nextval as atiaa uses them to detect auto keys |
|
27
|
1 |
|
} else if(preg_match("/nextval\(.*/", $defaultValue)) { |
|
28
|
1 |
|
return $defaultValue; |
|
29
|
|
|
|
|
30
|
|
|
// Return numeric default values |
|
31
|
1 |
|
} else if(is_numeric($defaultValue)) { |
|
32
|
1 |
|
return $defaultValue; |
|
33
|
|
|
|
|
34
|
|
|
// Return null for anything else |
|
|
|
|
|
|
35
|
|
|
} else { |
|
36
|
1 |
|
return null; |
|
37
|
|
|
} |
|
38
|
|
|
} |
|
39
|
|
|
|
|
40
|
|
|
/** |
|
41
|
|
|
* |
|
42
|
|
|
* @note Query sourced from http://stackoverflow.com/questions/2204058/show-which-columns-an-index-is-on-in-postgresql |
|
43
|
|
|
* @param type $table |
|
44
|
|
|
* @return type |
|
45
|
|
|
*/ |
|
46
|
4 |
|
protected function getIndices(&$table) |
|
47
|
|
|
{ |
|
48
|
4 |
|
return $this->driver->query( |
|
49
|
4 |
|
sprintf("select |
|
50
|
|
|
t.relname as table_name, |
|
51
|
|
|
i.relname as name, |
|
52
|
|
|
a.attname as column |
|
53
|
|
|
from |
|
54
|
|
|
pg_class t, |
|
55
|
|
|
pg_class i, |
|
56
|
|
|
pg_index ix, |
|
57
|
|
|
pg_attribute a, |
|
58
|
|
|
pg_namespace n |
|
59
|
|
|
where |
|
60
|
|
|
t.oid = ix.indrelid |
|
61
|
|
|
and i.oid = ix.indexrelid |
|
62
|
|
|
and a.attrelid = t.oid |
|
63
|
|
|
and a.attnum = ANY(ix.indkey) |
|
64
|
|
|
and t.relkind = 'r' |
|
65
|
|
|
and t.relname = '%s' |
|
66
|
|
|
and n.nspname = '%s' |
|
67
|
|
|
and i.relnamespace = n.oid |
|
68
|
|
|
AND indisunique != 't' |
|
69
|
|
|
AND indisprimary != 't' |
|
70
|
|
|
order by i.relname, a.attname", |
|
71
|
4 |
|
$table['name'], $table['schema']) |
|
72
|
|
|
); |
|
73
|
|
|
} |
|
74
|
|
|
|
|
75
|
|
|
/** |
|
76
|
|
|
* @note Query sourced from http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys |
|
77
|
|
|
* @param type $table |
|
78
|
|
|
*/ |
|
79
|
4 |
|
protected function getForeignKeys(&$table) |
|
80
|
|
|
{ |
|
81
|
4 |
|
return $this->driver->query( |
|
82
|
4 |
|
"SELECT distinct |
|
83
|
|
|
kcu.constraint_name as name, |
|
84
|
|
|
kcu.table_schema as schema, |
|
85
|
|
|
kcu.table_name as table, |
|
86
|
|
|
kcu.column_name as column, |
|
87
|
|
|
ccu.table_name AS foreign_table, |
|
88
|
|
|
ccu.table_schema AS foreign_schema, |
|
89
|
|
|
ccu.column_name AS foreign_column, |
|
90
|
|
|
rc.update_rule as on_update, |
|
91
|
|
|
rc.delete_rule as on_delete |
|
92
|
|
|
|
|
93
|
|
|
FROM |
|
94
|
|
|
information_schema.table_constraints AS tc |
|
95
|
|
|
JOIN information_schema.key_column_usage AS kcu |
|
96
|
|
|
ON tc.constraint_name = kcu.constraint_name and tc.table_schema = kcu.table_schema and tc.table_name = kcu.table_name |
|
97
|
|
|
JOIN information_schema.constraint_column_usage AS ccu |
|
98
|
|
|
ON ccu.constraint_name = tc.constraint_name and ccu.constraint_schema = tc.table_schema |
|
99
|
|
|
JOIN information_schema.referential_constraints AS rc |
|
100
|
|
|
ON rc.constraint_name = tc.constraint_name and rc.constraint_schema = tc.table_schema |
|
101
|
|
|
WHERE constraint_type = 'FOREIGN KEY' |
|
102
|
|
|
AND tc.table_name=:name AND tc.table_schema=:schema |
|
103
|
|
|
AND kcu.table_name=:name AND kcu.table_schema=:schema |
|
104
|
|
|
order by kcu.constraint_name, kcu.column_name", |
|
105
|
|
|
//$table['name'], $table['schema'] |
|
|
|
|
|
|
106
|
4 |
|
array('name'=>$table['name'], 'schema'=>$table['schema']) |
|
107
|
|
|
); |
|
108
|
|
|
} |
|
109
|
|
|
|
|
110
|
2 |
|
public function getSchemata() |
|
111
|
|
|
{ |
|
112
|
2 |
|
return $this->driver->query( |
|
113
|
2 |
|
"select schema_name as name from information_schema.schemata |
|
114
|
|
|
where schema_name not like 'pg_temp%' and |
|
115
|
|
|
schema_name not like 'pg_toast%' and |
|
116
|
|
|
schema_name not in ('pg_catalog', 'information_schema') |
|
117
|
|
|
order by schema_name" |
|
118
|
|
|
); |
|
119
|
|
|
} |
|
120
|
|
|
|
|
121
|
4 |
|
protected function hasAutoIncrementingKey(&$table) |
|
122
|
|
|
{ |
|
123
|
4 |
|
$auto = false; |
|
124
|
4 |
|
$primaryKey = reset($table['primary_key']); |
|
125
|
4 |
|
if(is_array($primaryKey)) |
|
126
|
|
|
{ |
|
127
|
3 |
|
if(count($primaryKey) == 1 && substr_count($table['columns'][$primaryKey['columns'][0]]['default'], 'nextval')) |
|
128
|
|
|
{ |
|
129
|
3 |
|
$table['columns'][$primaryKey['columns'][0]]['default'] = null; |
|
130
|
3 |
|
$auto = true; |
|
131
|
|
|
} |
|
132
|
|
|
} |
|
133
|
4 |
|
return $auto; |
|
134
|
|
|
} |
|
135
|
|
|
} |
|
136
|
|
|
|
ntentan /
atiaa
Loading history...