37 self.type = type |
37 self.type = type |
38 self.notnull = notnull |
38 self.notnull = notnull |
39 self.hasdefault = hasdefault |
39 self.hasdefault = hasdefault |
40 self.default = default |
40 self.default = default |
41 self.description = description |
41 self.description = description |
42 |
42 |
43 |
43 |
44 class Constraint: |
44 class Constraint: |
45 def __init__(self, browser, table, name, type, fname, fschema, definition): |
45 def __init__(self, browser, table, name, type, fname, fschema, definition): |
46 self.browser = browser |
46 self.browser = browser |
47 self.table = table |
47 self.table = table |
87 self._columns = OrderedDict([(x['name'], Column(self.browser, self, **x)) for x in rows]) |
87 self._columns = OrderedDict([(x['name'], Column(self.browser, self, **x)) for x in rows]) |
88 |
88 |
89 def refresh_constraints(self): |
89 def refresh_constraints(self): |
90 rows = self.browser.list_constraints(self.name, self.schema.name) |
90 rows = self.browser.list_constraints(self.name, self.schema.name) |
91 self._constraints = OrderedDict([(x['name'], Constraint(self.browser, self, **x)) for x in rows]) |
91 self._constraints = OrderedDict([(x['name'], Constraint(self.browser, self, **x)) for x in rows]) |
92 |
92 |
93 def refresh_indexes(self): |
93 def refresh_indexes(self): |
94 rows = self.browser.list_indexes(self.name, self.schema.name) |
94 rows = self.browser.list_indexes(self.name, self.schema.name) |
95 self._indexes = OrderedDict([(x['name'], Index(self.browser, self, **x)) for x in rows]) |
95 self._indexes = OrderedDict([(x['name'], Index(self.browser, self, **x)) for x in rows]) |
96 |
96 |
97 def getcolumns(self): |
97 def getcolumns(self): |
98 if self._columns is None: |
98 if self._columns is None: |
99 self.refresh_columns() |
99 self.refresh_columns() |
100 return self._columns |
100 return self._columns |
101 columns = property(getcolumns) |
101 columns = property(getcolumns) |
102 |
102 |
103 def getconstraints(self): |
103 def getconstraints(self): |
104 if self._constraints is None: |
104 if self._constraints is None: |
105 self.refresh_constraints() |
105 self.refresh_constraints() |
106 return self._constraints |
106 return self._constraints |
107 constraints = property(getconstraints) |
107 constraints = property(getconstraints) |
108 |
108 |
109 def getindexes(self): |
109 def getindexes(self): |
110 if self._indexes is None: |
110 if self._indexes is None: |
111 self.refresh_indexes() |
111 self.refresh_indexes() |
112 return self._indexes |
112 return self._indexes |
113 indexes = property(getindexes) |
113 indexes = property(getindexes) |
122 self.description = description |
122 self.description = description |
123 self.system = system |
123 self.system = system |
124 |
124 |
125 def refresh(self): |
125 def refresh(self): |
126 rows = self.browser.list_tables(self.name) |
126 rows = self.browser.list_tables(self.name) |
127 self._tables = OrderedDict([(x['name'], Table(self.browser, self, **x)) for x in rows]) |
127 self._tables = OrderedDict([(x['name'], Table(self.browser, self, **x)) for x in rows]) |
128 |
128 |
129 def gettables(self): |
129 def gettables(self): |
130 if self._tables is None: |
130 if self._tables is None: |
131 self.refresh() |
131 self.refresh() |
132 return self._tables |
132 return self._tables |
135 |
135 |
136 class PgBrowser: |
136 class PgBrowser: |
137 def __init__(self, conn=None): |
137 def __init__(self, conn=None): |
138 self._schemas = None |
138 self._schemas = None |
139 self.conn = conn |
139 self.conn = conn |
140 |
140 |
141 def setconn(self, conn=None): |
141 def setconn(self, conn=None): |
142 self.conn = conn |
142 self.conn = conn |
143 |
143 |
144 def refresh(self): |
144 def refresh(self): |
145 rows = self.list_schemas() |
145 rows = self.list_schemas() |
146 self._schemas = OrderedDict([(x['name'], Schema(self, **x)) for x in rows]) |
146 self._schemas = OrderedDict([(x['name'], Schema(self, **x)) for x in rows]) |
147 |
147 |
148 def getschemas(self): |
148 def getschemas(self): |
149 if self._schemas is None: |
149 if self._schemas is None: |
150 self.refresh() |
150 self.refresh() |
151 return self._schemas |
151 return self._schemas |
152 schemas = property(getschemas) |
152 schemas = property(getschemas) |
153 |
153 |
154 def _query(self, query, args): |
154 def _query(self, query, args): |
155 try: |
155 try: |
156 curs = self.conn.cursor() |
156 curs = self.conn.cursor() |
157 curs.execute(query, args) |
157 curs.execute(query, args) |
158 curs.connection.commit() |
158 curs.connection.commit() |
216 --a.attrelid, |
216 --a.attrelid, |
217 a.attname as "name", |
217 a.attname as "name", |
218 format_type(a.atttypid, a.atttypmod) AS "type", |
218 format_type(a.atttypid, a.atttypmod) AS "type", |
219 a.attnotnull as "notnull", |
219 a.attnotnull as "notnull", |
220 a.atthasdef as "hasdefault", |
220 a.atthasdef as "hasdefault", |
221 d.adsrc as "default", |
221 pg_catalog.pg_get_expr(d.adbin, d.adrelid) as "default", |
222 pg_catalog.col_description(a.attrelid, a.attnum) AS "description" |
222 pg_catalog.col_description(a.attrelid, a.attnum) AS "description" |
223 FROM pg_catalog.pg_attribute a |
223 FROM pg_catalog.pg_attribute a |
224 LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid |
224 LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid |
225 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
225 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
226 LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum |
226 LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum |
281 ''' + (schema and ' AND nc.nspname = %(schema)s' or '') + ''' |
281 ''' + (schema and ' AND nc.nspname = %(schema)s' or '') + ''' |
282 ''', {'schema': schema}) |
282 ''', {'schema': schema}) |
283 |
283 |
284 def list_column_usage(self, table, column, schema='public'): |
284 def list_column_usage(self, table, column, schema='public'): |
285 '''List objects using the column. |
285 '''List objects using the column. |
286 |
286 |
287 Currently shows views and constraints which use the column. |
287 Currently shows views and constraints which use the column. |
288 |
288 |
289 This is useful to find which views block alteration of column type etc. |
289 This is useful to find which views block alteration of column type etc. |
290 |
290 |
291 ''' |
291 ''' |
292 return self._query(''' |
292 return self._query(''' |
293 SELECT |
293 SELECT |
294 'view' AS type, view_schema AS schema, view_name AS name |
294 'view' AS type, view_schema AS schema, view_name AS name |
295 FROM information_schema.view_column_usage |
295 FROM information_schema.view_column_usage |
296 WHERE table_schema=%(schema)s AND table_name=%(table)s AND column_name=%(column)s |
296 WHERE table_schema=%(schema)s AND table_name=%(table)s AND column_name=%(column)s |
297 |
297 |
298 UNION |
298 UNION |
299 |
299 |
300 SELECT |
300 SELECT |
301 'constraint' AS type, constraint_schema AS schema, constraint_name AS name |
301 'constraint' AS type, constraint_schema AS schema, constraint_name AS name |
302 FROM information_schema.constraint_column_usage |
302 FROM information_schema.constraint_column_usage |
303 WHERE table_schema=%(schema)s AND table_name=%(table)s AND column_name=%(column)s |
303 WHERE table_schema=%(schema)s AND table_name=%(table)s AND column_name=%(column)s |
304 ''', {'schema':schema, 'table':table, 'column':column}) |
304 ''', {'schema':schema, 'table':table, 'column':column}) |