--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgtoolkit/pgbrowser.py Tue Aug 16 16:03:46 2011 +0200
@@ -0,0 +1,258 @@
+# -*- coding: utf-8 -*-
+#
+# PgBrowser - browse database schema and metadata
+#
+# Some of the queries came from psql.
+#
+# Copyright (c) 2011 Radek Brich <radek.brich@devl.cz>
+#
+# 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)
+