diff -r 2911935c524d -r 2fcc8ef0b97d tools/pgbrowser.py --- a/tools/pgbrowser.py Tue Aug 16 15:12:53 2011 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,258 +0,0 @@ -# -*- coding: utf-8 -*- -# -# PgBrowser - browse database schema and metadata -# -# Some of the queries came from psql. -# -# Copyright (c) 2011 Radek Brich -# -# Permission is hereby granted, free of charge, to any person obtaining a copy -# of this software and associated documentation files (the "Software"), to deal -# in the Software without restriction, including without limitation the rights -# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell -# copies of the Software, and to permit persons to whom the Software is -# furnished to do so, subject to the following conditions: -# -# The above copyright notice and this permission notice shall be included in -# all copies or substantial portions of the Software. -# -# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR -# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, -# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE -# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER -# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, -# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN -# THE SOFTWARE. - - -from collections import OrderedDict - - -class Column: - def __init__(self, browser, table, - name, type, notnull, hasdefault, default, description): - self.browser = browser # Browser instance - self.table = table # Table instance - self.name = name - self.type = type - self.notnull = notnull - self.hasdefault = hasdefault - self.default = default - self.description = description - - -class Constraint: - def __init__(self, browser, table, name, type, definition): - self.browser = browser - self.table = table - self.name = name - self.type = type - self.definition = definition - - -class Index: - def __init__(self, browser, table, - name, primary, unique, clustered, valid, definition): - self.browser = browser - self.table = table - self.name = name - self.primary = primary - self.unique = unique - self.clustered = clustered - self.valid = valid - self.definition = definition - - -class Table: - def __init__(self, browser, schema, name, owner, size, description): - self._columns = None - self._constraints = None - self._indexes = None - self.browser = browser # Browser instance - self.schema = schema # Schema instance - self.name = name # table name, str - self.owner = owner - self.size = size - self.description = description - - def refresh(self): - self.refresh_columns() - self.refresh_constraints() - self.refresh_indexes() - - def refresh_columns(self): - rows = self.browser.list_columns(self.name, self.schema.name) - self._columns = OrderedDict([(x['name'], Column(self.browser, self, **x)) for x in rows]) - - def refresh_constraints(self): - rows = self.browser.list_constraints(self.name, self.schema.name) - self._constraints = OrderedDict([(x['name'], Constraint(self.browser, self, **x)) for x in rows]) - - def refresh_indexes(self): - rows = self.browser.list_indexes(self.name, self.schema.name) - self._indexes = OrderedDict([(x['name'], Index(self.browser, self, **x)) for x in rows]) - - def getcolumns(self): - if self._columns is None: - self.refresh_columns() - return self._columns - columns = property(getcolumns) - - def getconstraints(self): - if self._constraints is None: - self.refresh_constraints() - return self._constraints - constraints = property(getconstraints) - - def getindexes(self): - if self._indexes is None: - self.refresh_indexes() - return self._indexes - indexes = property(getindexes) - -class Schema: - def __init__(self, browser, name, owner, acl, description, system): - self._tables = None - self.browser = browser - self.name = name - self.owner = owner - self.acl = acl - self.description = description - self.system = system - - def refresh(self): - rows = self.browser.list_tables(self.name) - self._tables = OrderedDict([(x['name'], Table(self.browser, self, **x)) for x in rows]) - - def gettables(self): - if self._tables is None: - self.refresh() - return self._tables - tables = property(gettables) - - -class PgBrowser: - def __init__(self, conn=None): - self._schemas = None - self.conn = conn - - def setconn(self, conn=None): - self.conn = conn - - def refresh(self): - rows = self.list_schemas() - self._schemas = OrderedDict([(x['name'], Schema(self, **x)) for x in rows]) - - def getschemas(self): - if self._schemas is None: - self.refresh() - return self._schemas - schemas = property(getschemas) - - def _query(self, query, *args): - try: - curs = self.conn.cursor() - curs.execute(query, args) - curs.connection.commit() - rows = curs.fetchall() - return [dict(zip([desc[0] for desc in curs.description], row)) for row in rows] - finally: - curs.close() - - def list_databases(self): - return self._query(''' - SELECT - d.datname as "name", - pg_catalog.pg_get_userbyid(d.datdba) as "owner", - pg_catalog.pg_encoding_to_char(d.encoding) as "encoding", - d.datcollate as "collation", - d.datctype as "ctype", - d.datacl AS "acl", - CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') - THEN pg_catalog.pg_database_size(d.datname) - ELSE -1 -- No access - END as "size", - t.spcname as "tablespace", - pg_catalog.shobj_description(d.oid, 'pg_database') as "description" - FROM pg_catalog.pg_database d - JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid - ORDER BY 1; - ''') - - def list_schemas(self): - return self._query(''' - SELECT - n.nspname AS "name", - pg_catalog.pg_get_userbyid(n.nspowner) AS "owner", - n.nspacl AS "acl", - pg_catalog.obj_description(n.oid, 'pg_namespace') AS "description", - CASE WHEN n.nspname IN ('information_schema', 'pg_catalog', 'pg_toast') - OR n.nspname ~ '^pg_temp_' OR n.nspname ~ '^pg_toast_temp_' - THEN TRUE - ELSE FALSE - END AS "system" - FROM pg_catalog.pg_namespace n - ORDER BY 1; - ''') - - def list_tables(self, schema='public'): - return self._query(''' - SELECT - c.relname as "name", - pg_catalog.pg_get_userbyid(c.relowner) as "owner", - pg_catalog.pg_relation_size(c.oid) as "size", - pg_catalog.obj_description(c.oid, 'pg_class') as "description" - FROM pg_catalog.pg_class c - LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace - WHERE n.nspname = %s AND c.relkind IN ('r','s','') - ORDER BY 1; - ''', schema) - - def list_columns(self, table, schema='public'): - return self._query(''' - SELECT - a.attname as "name", - format_type(a.atttypid, a.atttypmod) AS "type", - a.attnotnull as "notnull", - a.atthasdef as "hasdefault", - d.adsrc as "default", - pg_catalog.col_description(a.attrelid, a.attnum) AS "description" - FROM pg_catalog.pg_attribute a - LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid - LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace - LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum - WHERE n.nspname = %s AND c.relname = %s AND a.attnum > 0 AND NOT a.attisdropped - ORDER BY 1 - ''', schema, table) - - def list_constraints(self, table, schema='public'): - return self._query(''' - SELECT - conname as "name", - r.contype as "type", - pg_catalog.pg_get_constraintdef(r.oid, true) as "definition" - FROM pg_catalog.pg_constraint r - JOIN pg_catalog.pg_class c ON r.conrelid = c.oid - JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace - WHERE n.nspname = %s AND c.relname = %s - ORDER BY 1 - ''', schema, table) - - def list_indexes(self, table, schema='public'): - return self._query(''' - SELECT - c2.relname as "name", - i.indisprimary as "primary", - i.indisunique as "unique", - i.indisclustered as "clustered", - i.indisvalid as "valid", - pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as "definition" - --c2.reltablespace as "tablespace_oid" - FROM pg_catalog.pg_class c - JOIN pg_catalog.pg_index i ON c.oid = i.indrelid - JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid - JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace - WHERE n.nspname = %s AND c.relname = %s - ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname - ''', schema, table) -