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