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) |
114 |
114 |
|
115 |
|
116 class Function: |
|
117 def __init__(self, browser, schema, oid, name, type, arguments, result, source): |
|
118 self.browser = browser |
|
119 self.oid = oid |
|
120 self.name = name |
|
121 self.type = type |
|
122 self.arguments = arguments |
|
123 self.result = result |
|
124 self.source = source |
|
125 self._definition = None |
|
126 |
|
127 @property |
|
128 def definition(self): |
|
129 """Get full function definition including CREATE command.""" |
|
130 if not self._definition: |
|
131 self._definition = self.browser.get_function_definition(self.oid) |
|
132 return self._definition |
|
133 |
|
134 |
115 class Schema: |
135 class Schema: |
116 def __init__(self, browser, name, owner, acl, description, system): |
136 def __init__(self, browser, name, owner, acl, description, system): |
117 self._tables = None |
137 self._tables = None |
|
138 self._functions = None |
118 self.browser = browser |
139 self.browser = browser |
119 self.name = name |
140 self.name = name |
120 self.owner = owner |
141 self.owner = owner |
121 self.acl = acl |
142 self.acl = acl |
122 self.description = description |
143 self.description = description |
123 self.system = system |
144 self.system = system |
124 |
145 |
125 def refresh(self): |
146 def refresh(self): |
|
147 self.refresh_tables() |
|
148 self.refresh_functions() |
|
149 |
|
150 def refresh_tables(self): |
126 rows = self.browser.list_tables(self.name) |
151 rows = self.browser.list_tables(self.name) |
127 self._tables = OrderedDict([(x['name'], Table(self.browser, self, **x)) for x in rows]) |
152 self._tables = OrderedDict([(x['name'], Table(self.browser, self, **x)) for x in rows]) |
128 |
153 |
129 def gettables(self): |
154 def refresh_functions(self): |
|
155 rows = self.browser.list_functions(self.name) |
|
156 self._functions = OrderedDict([(x['name'], Function(self.browser, self, **x)) for x in rows]) |
|
157 |
|
158 @property |
|
159 def tables(self): |
130 if self._tables is None: |
160 if self._tables is None: |
131 self.refresh() |
161 self.refresh_tables() |
132 return self._tables |
162 return self._tables |
133 tables = property(gettables) |
163 |
|
164 @property |
|
165 def functions(self): |
|
166 if self._functions is None: |
|
167 self.refresh_functions() |
|
168 return self._functions |
134 |
169 |
135 |
170 |
136 class PgBrowser: |
171 class PgBrowser: |
137 def __init__(self, conn=None): |
172 def __init__(self, conn=None): |
138 self._schemas = None |
173 self._schemas = None |
140 |
175 |
141 def setconn(self, conn=None): |
176 def setconn(self, conn=None): |
142 self.conn = conn |
177 self.conn = conn |
143 |
178 |
144 def refresh(self): |
179 def refresh(self): |
|
180 self.refresh_schemas() |
|
181 |
|
182 def refresh_schemas(self): |
145 rows = self.list_schemas() |
183 rows = self.list_schemas() |
146 self._schemas = OrderedDict([(x['name'], Schema(self, **x)) for x in rows]) |
184 self._schemas = OrderedDict([(x['name'], Schema(self, **x)) for x in rows]) |
147 |
185 |
148 def getschemas(self): |
186 @property |
|
187 def schemas(self): |
149 if self._schemas is None: |
188 if self._schemas is None: |
150 self.refresh() |
189 self.refresh_schemas() |
151 return self._schemas |
190 return self._schemas |
152 schemas = property(getschemas) |
|
153 |
191 |
154 def _query(self, query, args): |
192 def _query(self, query, args): |
155 try: |
193 try: |
156 curs = self.conn.cursor() |
194 curs = self.conn.cursor() |
157 curs.execute(query, args) |
195 curs.execute(query, args) |
261 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
299 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
262 WHERE n.nspname = %(schema)s AND c.relname = %(table)s |
300 WHERE n.nspname = %(schema)s AND c.relname = %(table)s |
263 ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname |
301 ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname |
264 ''', {'schema': schema, 'table': table}) |
302 ''', {'schema': schema, 'table': table}) |
265 |
303 |
|
304 def list_functions(self, schema='public'): |
|
305 '''List functions in schema.''' |
|
306 return self._query(''' |
|
307 SELECT |
|
308 p.oid as "oid", |
|
309 p.proname as "name", |
|
310 pg_catalog.pg_get_function_result(p.oid) as "result", |
|
311 pg_catalog.pg_get_function_arguments(p.oid) as "arguments", |
|
312 p.prosrc as "source", |
|
313 CASE |
|
314 WHEN p.proisagg THEN 'agg' |
|
315 WHEN p.proiswindow THEN 'window' |
|
316 WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger' |
|
317 ELSE 'normal' |
|
318 END as "type" |
|
319 FROM pg_catalog.pg_proc p |
|
320 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace |
|
321 WHERE pg_catalog.pg_function_is_visible(p.oid) |
|
322 AND n.nspname = %s |
|
323 ORDER BY 1, 2, 4; |
|
324 ''', [schema]) |
|
325 |
|
326 def get_function_definition(self, oid): |
|
327 """Get full function definition, including CREATE command etc. |
|
328 |
|
329 Args: |
|
330 oid: function oid from pg_catalog.pg_proc (returned by list_functions) |
|
331 |
|
332 """ |
|
333 return self._query('''select pg_get_functiondef(%s);''', [oid]) |
|
334 |
266 def list_sequences(self, schema=None): |
335 def list_sequences(self, schema=None): |
267 '''List sequences in schema.''' |
336 '''List sequences in schema.''' |
268 return self._query(''' |
337 return self._query(''' |
269 SELECT |
338 SELECT |
270 nc.nspname AS "sequence_schema", |
339 nc.nspname AS "sequence_schema", |