147 if self._schemas is None: |
147 if self._schemas is None: |
148 self.refresh() |
148 self.refresh() |
149 return self._schemas |
149 return self._schemas |
150 schemas = property(getschemas) |
150 schemas = property(getschemas) |
151 |
151 |
152 def _query(self, query, *args): |
152 def _query(self, query, args): |
153 try: |
153 try: |
154 curs = self.conn.cursor() |
154 curs = self.conn.cursor() |
155 curs.execute(query, args) |
155 curs.execute(query, args) |
156 curs.connection.commit() |
156 curs.connection.commit() |
157 rows = curs.fetchall() |
157 rows = curs.fetchall() |
175 t.spcname as "tablespace", |
175 t.spcname as "tablespace", |
176 pg_catalog.shobj_description(d.oid, 'pg_database') as "description" |
176 pg_catalog.shobj_description(d.oid, 'pg_database') as "description" |
177 FROM pg_catalog.pg_database d |
177 FROM pg_catalog.pg_database d |
178 JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid |
178 JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid |
179 ORDER BY 1; |
179 ORDER BY 1; |
180 ''') |
180 ''', []) |
181 |
181 |
182 def list_schemas(self): |
182 def list_schemas(self): |
183 return self._query(''' |
183 return self._query(''' |
184 SELECT |
184 SELECT |
185 n.nspname AS "name", |
185 n.nspname AS "name", |
204 pg_catalog.obj_description(c.oid, 'pg_class') as "description" |
204 pg_catalog.obj_description(c.oid, 'pg_class') as "description" |
205 FROM pg_catalog.pg_class c |
205 FROM pg_catalog.pg_class c |
206 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
206 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
207 WHERE n.nspname = %s AND c.relkind IN ('r','s','') |
207 WHERE n.nspname = %s AND c.relkind IN ('r','s','') |
208 ORDER BY 1; |
208 ORDER BY 1; |
209 ''', schema) |
209 ''', [schema]) |
210 |
210 |
211 def list_columns(self, table, schema='public', order=2): |
211 def list_columns(self, table, schema='public', order=2): |
212 return self._query(''' |
212 return self._query(''' |
213 SELECT |
213 SELECT |
214 a.attrelid, |
214 a.attrelid, |
221 FROM pg_catalog.pg_attribute a |
221 FROM pg_catalog.pg_attribute a |
222 LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid |
222 LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid |
223 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
223 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
224 LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum |
224 LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum |
225 WHERE n.nspname = %s AND c.relname = %s AND a.attnum > 0 AND NOT a.attisdropped |
225 WHERE n.nspname = %s AND c.relname = %s AND a.attnum > 0 AND NOT a.attisdropped |
226 ORDER BY ''' + str(order), schema, table) |
226 ORDER BY ''' + str(order), [schema, table]) |
227 |
227 |
228 def list_constraints(self, table, schema='public'): |
228 def list_constraints(self, table, schema='public'): |
229 return self._query(''' |
229 return self._query(''' |
230 SELECT |
230 SELECT |
231 conname as "name", |
231 conname as "name", |
234 FROM pg_catalog.pg_constraint r |
234 FROM pg_catalog.pg_constraint r |
235 JOIN pg_catalog.pg_class c ON r.conrelid = c.oid |
235 JOIN pg_catalog.pg_class c ON r.conrelid = c.oid |
236 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
236 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
237 WHERE n.nspname = %s AND c.relname = %s |
237 WHERE n.nspname = %s AND c.relname = %s |
238 ORDER BY 1 |
238 ORDER BY 1 |
239 ''', schema, table) |
239 ''', [schema, table]) |
240 |
240 |
241 def list_indexes(self, table, schema='public'): |
241 def list_indexes(self, table, schema='public'): |
242 return self._query(''' |
242 return self._query(''' |
243 SELECT |
243 SELECT |
244 c2.relname as "name", |
244 c2.relname as "name", |
245 i.indisprimary as "primary", |
245 i.indisprimary as "primary", |
246 i.indisunique as "unique", |
246 i.indisunique as "unique", |
247 i.indisclustered as "clustered", |
247 i.indisclustered as "clustered", |
248 i.indisvalid as "valid", |
248 i.indisvalid as "valid", |
249 pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as "definition" |
249 pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as "definition", |
|
250 ARRAY(SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c2.oid ORDER BY attnum) AS "columns" |
250 --c2.reltablespace as "tablespace_oid" |
251 --c2.reltablespace as "tablespace_oid" |
251 FROM pg_catalog.pg_class c |
252 FROM pg_catalog.pg_class c |
252 JOIN pg_catalog.pg_index i ON c.oid = i.indrelid |
253 JOIN pg_catalog.pg_index i ON c.oid = i.indrelid |
253 JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid |
254 JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid |
254 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
255 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
255 WHERE n.nspname = %s AND c.relname = %s |
256 WHERE n.nspname = %(schema)s AND c.relname = %(table)s |
256 ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname |
257 ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname |
257 ''', schema, table) |
258 ''', {'schema': schema, 'table': table}) |
258 |
259 |
|
260 def list_column_usage(self, table, column, schema='public'): |
|
261 '''List objects using the column. |
|
262 |
|
263 These objects may block alteration of column. Currently only views are listed. |
|
264 |
|
265 ''' |
|
266 return self._query(''' |
|
267 SELECT |
|
268 'view' AS type, view_schema AS schema, view_name AS name |
|
269 FROM information_schema.view_column_usage |
|
270 WHERE table_schema=%(schema)s AND table_name=%(table)s AND column_name=%(column)s |
|
271 ''', {'schema':schema, 'table':table, 'column':column}) |
|
272 |