pgtools/pgbrowser.py
changeset 7 685b20d2d3ab
parent 6 4ab077c93b2d
child 8 2911935c524d
--- a/pgtools/pgbrowser.py	Wed Aug 10 18:34:54 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 <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)
-