tools/pgbrowser.py
changeset 7 685b20d2d3ab
parent 0 eaae9539e910
equal deleted inserted replaced
6:4ab077c93b2d 7:685b20d2d3ab
       
     1 # -*- coding: utf-8 -*-
       
     2 #
       
     3 # PgBrowser - browse database schema and metadata
       
     4 #
       
     5 # Some of the queries came from psql.
       
     6 #
       
     7 # Copyright (c) 2011  Radek Brich <radek.brich@devl.cz>
       
     8 #
       
     9 # Permission is hereby granted, free of charge, to any person obtaining a copy
       
    10 # of this software and associated documentation files (the "Software"), to deal
       
    11 # in the Software without restriction, including without limitation the rights
       
    12 # to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
       
    13 # copies of the Software, and to permit persons to whom the Software is
       
    14 # furnished to do so, subject to the following conditions:
       
    15 #
       
    16 # The above copyright notice and this permission notice shall be included in
       
    17 # all copies or substantial portions of the Software.
       
    18 #
       
    19 # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
       
    20 # IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
       
    21 # FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
       
    22 # AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
       
    23 # LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
       
    24 # OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
       
    25 # THE SOFTWARE.
       
    26 
       
    27 
       
    28 from collections import OrderedDict
       
    29 
       
    30 
       
    31 class Column:
       
    32     def __init__(self, browser, table,
       
    33         name, type, notnull, hasdefault, default, description):
       
    34         self.browser = browser  # Browser instance
       
    35         self.table = table  # Table instance
       
    36         self.name = name
       
    37         self.type = type
       
    38         self.notnull = notnull
       
    39         self.hasdefault = hasdefault
       
    40         self.default = default
       
    41         self.description = description
       
    42         
       
    43 
       
    44 class Constraint:
       
    45     def __init__(self, browser, table, name, type, definition):
       
    46         self.browser = browser
       
    47         self.table = table
       
    48         self.name = name
       
    49         self.type = type
       
    50         self.definition = definition
       
    51 
       
    52 
       
    53 class Index:
       
    54     def __init__(self, browser, table,
       
    55         name, primary, unique, clustered, valid, definition):
       
    56         self.browser = browser
       
    57         self.table = table
       
    58         self.name = name
       
    59         self.primary = primary
       
    60         self.unique = unique
       
    61         self.clustered = clustered
       
    62         self.valid = valid
       
    63         self.definition = definition
       
    64 
       
    65 
       
    66 class Table:
       
    67     def __init__(self, browser, schema, name, owner, size, description):
       
    68         self._columns = None
       
    69         self._constraints = None
       
    70         self._indexes = None
       
    71         self.browser = browser  # Browser instance
       
    72         self.schema = schema  # Schema instance
       
    73         self.name = name  # table name, str
       
    74         self.owner = owner
       
    75         self.size = size
       
    76         self.description = description
       
    77 
       
    78     def refresh(self):
       
    79         self.refresh_columns()
       
    80         self.refresh_constraints()
       
    81         self.refresh_indexes()
       
    82 
       
    83     def refresh_columns(self):
       
    84         rows = self.browser.list_columns(self.name, self.schema.name)
       
    85         self._columns = OrderedDict([(x['name'], Column(self.browser, self, **x)) for x in rows])
       
    86 
       
    87     def refresh_constraints(self):
       
    88         rows = self.browser.list_constraints(self.name, self.schema.name)
       
    89         self._constraints = OrderedDict([(x['name'], Constraint(self.browser, self, **x)) for x in rows])
       
    90         
       
    91     def refresh_indexes(self):
       
    92         rows = self.browser.list_indexes(self.name, self.schema.name)
       
    93         self._indexes = OrderedDict([(x['name'], Index(self.browser, self, **x)) for x in rows])
       
    94 
       
    95     def getcolumns(self):
       
    96         if self._columns is None:
       
    97             self.refresh_columns()
       
    98         return self._columns
       
    99     columns = property(getcolumns)
       
   100     
       
   101     def getconstraints(self):
       
   102         if self._constraints is None:
       
   103             self.refresh_constraints()
       
   104         return self._constraints
       
   105     constraints = property(getconstraints)
       
   106         
       
   107     def getindexes(self):
       
   108         if self._indexes is None:
       
   109             self.refresh_indexes()
       
   110         return self._indexes
       
   111     indexes = property(getindexes)
       
   112 
       
   113 class Schema:
       
   114     def __init__(self, browser, name, owner, acl, description, system):
       
   115         self._tables = None
       
   116         self.browser = browser
       
   117         self.name = name
       
   118         self.owner = owner
       
   119         self.acl = acl
       
   120         self.description = description
       
   121         self.system = system
       
   122 
       
   123     def refresh(self):
       
   124         rows = self.browser.list_tables(self.name)
       
   125         self._tables = OrderedDict([(x['name'], Table(self.browser, self, **x)) for x in rows]) 
       
   126 
       
   127     def gettables(self):
       
   128         if self._tables is None:
       
   129             self.refresh()
       
   130         return self._tables
       
   131     tables = property(gettables)
       
   132 
       
   133 
       
   134 class PgBrowser:
       
   135     def __init__(self, conn=None):
       
   136         self._schemas = None
       
   137         self.conn = conn
       
   138         
       
   139     def setconn(self, conn=None):
       
   140         self.conn = conn
       
   141     
       
   142     def refresh(self):
       
   143         rows = self.list_schemas()
       
   144         self._schemas = OrderedDict([(x['name'], Schema(self, **x)) for x in rows])
       
   145 
       
   146     def getschemas(self):
       
   147         if self._schemas is None:
       
   148             self.refresh()
       
   149         return self._schemas
       
   150     schemas = property(getschemas)
       
   151                     
       
   152     def _query(self, query, *args):
       
   153         try:
       
   154             curs = self.conn.cursor()
       
   155             curs.execute(query, args)
       
   156             curs.connection.commit()
       
   157             rows = curs.fetchall()
       
   158             return [dict(zip([desc[0] for desc in curs.description], row)) for row in rows]
       
   159         finally:
       
   160             curs.close()
       
   161 
       
   162     def list_databases(self):
       
   163         return self._query('''
       
   164             SELECT
       
   165                 d.datname as "name",
       
   166                 pg_catalog.pg_get_userbyid(d.datdba) as "owner",
       
   167                 pg_catalog.pg_encoding_to_char(d.encoding) as "encoding",
       
   168                 d.datcollate as "collation",
       
   169                 d.datctype as "ctype",
       
   170                 d.datacl AS "acl",
       
   171                 CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
       
   172                     THEN pg_catalog.pg_database_size(d.datname)
       
   173                     ELSE -1 -- No access
       
   174                 END as "size",
       
   175                 t.spcname as "tablespace",
       
   176                 pg_catalog.shobj_description(d.oid, 'pg_database') as "description"
       
   177             FROM pg_catalog.pg_database d
       
   178             JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
       
   179             ORDER BY 1;
       
   180             ''')
       
   181 
       
   182     def list_schemas(self):
       
   183         return self._query('''
       
   184             SELECT
       
   185                 n.nspname AS "name",
       
   186                 pg_catalog.pg_get_userbyid(n.nspowner) AS "owner",
       
   187                 n.nspacl AS "acl",
       
   188                 pg_catalog.obj_description(n.oid, 'pg_namespace') AS "description",
       
   189                 CASE WHEN n.nspname IN ('information_schema', 'pg_catalog', 'pg_toast')
       
   190                     OR n.nspname ~ '^pg_temp_' OR n.nspname ~ '^pg_toast_temp_'
       
   191                     THEN TRUE
       
   192                     ELSE FALSE
       
   193                 END AS "system"
       
   194             FROM pg_catalog.pg_namespace n
       
   195             ORDER BY 1;
       
   196             ''')
       
   197 
       
   198     def list_tables(self, schema='public'):
       
   199         return self._query('''
       
   200             SELECT
       
   201                 c.relname as "name",
       
   202                 pg_catalog.pg_get_userbyid(c.relowner) as "owner",
       
   203                 pg_catalog.pg_relation_size(c.oid) as "size",
       
   204                 pg_catalog.obj_description(c.oid, 'pg_class') as "description"
       
   205             FROM pg_catalog.pg_class c
       
   206             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
       
   207             WHERE n.nspname = %s AND c.relkind IN ('r','s','')
       
   208             ORDER BY 1;
       
   209             ''', schema)
       
   210 
       
   211     def list_columns(self, table, schema='public'):
       
   212         return self._query('''
       
   213             SELECT
       
   214                 a.attname as "name",
       
   215                 format_type(a.atttypid, a.atttypmod) AS "type",
       
   216                 a.attnotnull as "notnull",
       
   217                 a.atthasdef as "hasdefault",
       
   218                 d.adsrc as "default",
       
   219                 pg_catalog.col_description(a.attrelid, a.attnum) AS "description"
       
   220             FROM pg_catalog.pg_attribute a
       
   221             LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
       
   222             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
       
   223             LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
       
   224             WHERE n.nspname = %s AND c.relname = %s AND a.attnum > 0 AND NOT a.attisdropped
       
   225             ORDER BY 1
       
   226             ''', schema, table)
       
   227 
       
   228     def list_constraints(self, table, schema='public'):
       
   229         return self._query('''
       
   230             SELECT
       
   231                 conname as "name",
       
   232                 r.contype as "type",
       
   233                 pg_catalog.pg_get_constraintdef(r.oid, true) as "definition"
       
   234             FROM pg_catalog.pg_constraint r
       
   235             JOIN pg_catalog.pg_class c ON r.conrelid = c.oid
       
   236             JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
       
   237             WHERE n.nspname = %s AND c.relname = %s
       
   238             ORDER BY 1
       
   239             ''', schema, table)
       
   240 
       
   241     def list_indexes(self, table, schema='public'):
       
   242         return self._query('''
       
   243             SELECT
       
   244                 c2.relname as "name",
       
   245                 i.indisprimary as "primary",
       
   246                 i.indisunique as "unique",
       
   247                 i.indisclustered as "clustered",
       
   248                 i.indisvalid as "valid",
       
   249                 pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as "definition"
       
   250                 --c2.reltablespace as "tablespace_oid"
       
   251             FROM pg_catalog.pg_class c
       
   252             JOIN pg_catalog.pg_index i ON c.oid = i.indrelid
       
   253             JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid
       
   254             JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
       
   255             WHERE n.nspname = %s AND c.relname = %s
       
   256             ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
       
   257             ''', schema, table)
       
   258