pgtoolkit/pgbrowser.py
changeset 104 d8ff52a0390f
parent 103 24e94a3da209
child 105 10551741f61f
equal deleted inserted replaced
103:24e94a3da209 104:d8ff52a0390f
     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, fname, fschema, definition):
       
    46         self.browser = browser
       
    47         self.table = table
       
    48         self.name = name
       
    49         self.type = type
       
    50         self.fname = fname  # foreign table name
       
    51         self.fschema = fschema  # foreign table schema
       
    52         self.definition = definition
       
    53 
       
    54 
       
    55 class Index:
       
    56     def __init__(self, browser, table,
       
    57             name, primary, unique, clustered, valid, definition,
       
    58             columns, size):
       
    59         self.browser = browser
       
    60         self.table = table
       
    61         self.name = name
       
    62         self.primary = primary
       
    63         self.unique = unique
       
    64         self.clustered = clustered
       
    65         self.valid = valid
       
    66         self.definition = definition
       
    67         self.columns = columns
       
    68         self.size = size
       
    69 
       
    70 
       
    71 class Table:
       
    72     def __init__(self, browser, schema, name, owner, size, description, options):
       
    73         self._columns = None
       
    74         self._constraints = None
       
    75         self._indexes = None
       
    76         self.browser = browser  # Browser instance
       
    77         self.schema = schema  # Schema instance
       
    78         self.name = name  # table name, str
       
    79         self.owner = owner
       
    80         self.size = size
       
    81         self.description = description
       
    82         self.options = options or []
       
    83 
       
    84     def refresh(self):
       
    85         self.refresh_columns()
       
    86         self.refresh_constraints()
       
    87         self.refresh_indexes()
       
    88 
       
    89     def refresh_columns(self):
       
    90         rows = self.browser.list_columns(self.name, self.schema.name)
       
    91         self._columns = OrderedDict([(x['name'], Column(self.browser, self, **x)) for x in rows])
       
    92 
       
    93     def refresh_constraints(self):
       
    94         rows = self.browser.list_constraints(self.name, self.schema.name)
       
    95         self._constraints = OrderedDict([(x['name'], Constraint(self.browser, self, **x)) for x in rows])
       
    96 
       
    97     def refresh_indexes(self):
       
    98         rows = self.browser.list_indexes(self.name, self.schema.name)
       
    99         self._indexes = OrderedDict([(x['name'], Index(self.browser, self, **x)) for x in rows])
       
   100 
       
   101     def getcolumns(self):
       
   102         if self._columns is None:
       
   103             self.refresh_columns()
       
   104         return self._columns
       
   105     columns = property(getcolumns)
       
   106 
       
   107     def getconstraints(self):
       
   108         if self._constraints is None:
       
   109             self.refresh_constraints()
       
   110         return self._constraints
       
   111     constraints = property(getconstraints)
       
   112 
       
   113     def getindexes(self):
       
   114         if self._indexes is None:
       
   115             self.refresh_indexes()
       
   116         return self._indexes
       
   117     indexes = property(getindexes)
       
   118 
       
   119 
       
   120 class Argument:
       
   121     def __init__(self, browser, function, name, type, mode, default):
       
   122         # PgBrowser instance
       
   123         self.browser = browser
       
   124         # Function instance
       
   125         self.function = function
       
   126         self.name = name
       
   127         self.type = type
       
   128         self.mode = mode
       
   129         self.default = default
       
   130 
       
   131 
       
   132 class Function:
       
   133     def __init__(self, browser, schema, oid, name, function_name, type, result, source):
       
   134         self.browser = browser
       
   135         self.schema = schema
       
   136         self.oid = oid
       
   137         #: unique name - function name + arg types
       
   138         self.name = name
       
   139         #: pure function name without args
       
   140         self.function_name = function_name
       
   141         self.type = type
       
   142         self.result = result
       
   143         self.source = source
       
   144         self._arguments = None
       
   145         self._definition = None
       
   146 
       
   147     def refresh(self):
       
   148         self.refresh_args()
       
   149 
       
   150     def refresh_args(self):
       
   151         rows = self.browser.list_function_args(self.oid)
       
   152         self._arguments = OrderedDict([(x['name'], Argument(self.browser, self, **x)) for x in rows])
       
   153 
       
   154     @property
       
   155     def arguments(self):
       
   156         if self._arguments is None:
       
   157             self.refresh_args()
       
   158         return self._arguments
       
   159 
       
   160     @property
       
   161     def definition(self):
       
   162         """Get full function definition including CREATE command."""
       
   163         if not self._definition:
       
   164             self._definition = self.browser.get_function_definition(self.oid)
       
   165         return self._definition
       
   166 
       
   167 
       
   168 class Type:
       
   169     def __init__(self, browser, schema, name, type, elements, description):
       
   170         self.browser = browser
       
   171         self.schema = schema
       
   172         self.name = name
       
   173         self.type = type
       
   174         self.elements = elements
       
   175         self.description = description
       
   176 
       
   177 
       
   178 class Schema:
       
   179     def __init__(self, browser, name, owner, acl, description, system):
       
   180         self._tables = None
       
   181         self._functions = None
       
   182         self._types = None
       
   183         self.browser = browser
       
   184         self.name = name
       
   185         self.owner = owner
       
   186         self.acl = acl
       
   187         self.description = description
       
   188         self.system = system
       
   189 
       
   190     def refresh(self):
       
   191         self.refresh_tables()
       
   192         self.refresh_functions()
       
   193 
       
   194     def refresh_tables(self):
       
   195         rows = self.browser.list_tables(self.name)
       
   196         self._tables = OrderedDict([(x['name'], Table(self.browser, self, **x)) for x in rows])
       
   197 
       
   198     def refresh_functions(self):
       
   199         rows = self.browser.list_functions(self.name)
       
   200         self._functions = OrderedDict([(x['name'], Function(self.browser, self, **x)) for x in rows])
       
   201 
       
   202     def refresh_types(self):
       
   203         rows = self.browser.list_types(self.name)
       
   204         self._types = OrderedDict([(x['name'], Type(self.browser, self, **x)) for x in rows])
       
   205 
       
   206     @property
       
   207     def tables(self):
       
   208         if self._tables is None:
       
   209             self.refresh_tables()
       
   210         return self._tables
       
   211 
       
   212     @property
       
   213     def functions(self):
       
   214         if self._functions is None:
       
   215             self.refresh_functions()
       
   216         return self._functions
       
   217 
       
   218     @property
       
   219     def types(self):
       
   220         if self._types is None:
       
   221             self.refresh_types()
       
   222         return self._types
       
   223 
       
   224 
       
   225 class PgBrowser:
       
   226     def __init__(self, conn=None):
       
   227         self._schemas = None
       
   228         self.conn = conn
       
   229 
       
   230     def setconn(self, conn=None):
       
   231         self.conn = conn
       
   232 
       
   233     def refresh(self):
       
   234         self.refresh_schemas()
       
   235 
       
   236     def refresh_schemas(self):
       
   237         rows = self.list_schemas()
       
   238         self._schemas = OrderedDict([(x['name'], Schema(self, **x)) for x in rows])
       
   239 
       
   240     @property
       
   241     def schemas(self):
       
   242         if self._schemas is None:
       
   243             self.refresh_schemas()
       
   244         return self._schemas
       
   245 
       
   246     def _query(self, query, args):
       
   247         try:
       
   248             curs = self.conn.cursor()
       
   249             curs.execute(query, args)
       
   250             curs.connection.commit()
       
   251             rows = curs.fetchall()
       
   252             return [dict(zip([desc[0] for desc in curs.description], row)) for row in rows]
       
   253         finally:
       
   254             curs.close()
       
   255 
       
   256     def list_databases(self):
       
   257         return self._query('''
       
   258             SELECT
       
   259                 d.datname as "name",
       
   260                 pg_catalog.pg_get_userbyid(d.datdba) as "owner",
       
   261                 pg_catalog.pg_encoding_to_char(d.encoding) as "encoding",
       
   262                 d.datcollate as "collation",
       
   263                 d.datctype as "ctype",
       
   264                 d.datacl AS "acl",
       
   265                 CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
       
   266                     THEN pg_catalog.pg_database_size(d.datname)
       
   267                     ELSE -1 -- No access
       
   268                 END as "size",
       
   269                 t.spcname as "tablespace",
       
   270                 pg_catalog.shobj_description(d.oid, 'pg_database') as "description"
       
   271             FROM pg_catalog.pg_database d
       
   272             JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
       
   273             ORDER BY 1;
       
   274             ''', [])
       
   275 
       
   276     def list_schemas(self):
       
   277         return self._query('''
       
   278             SELECT
       
   279                 n.nspname AS "name",
       
   280                 pg_catalog.pg_get_userbyid(n.nspowner) AS "owner",
       
   281                 n.nspacl AS "acl",
       
   282                 pg_catalog.obj_description(n.oid, 'pg_namespace') AS "description",
       
   283                 CASE WHEN n.nspname IN ('information_schema', 'pg_catalog', 'pg_toast')
       
   284                     OR n.nspname ~ '^pg_temp_' OR n.nspname ~ '^pg_toast_temp_'
       
   285                     THEN TRUE
       
   286                     ELSE FALSE
       
   287                 END AS "system"
       
   288             FROM pg_catalog.pg_namespace n
       
   289             ORDER BY 1;
       
   290             ''', [])
       
   291 
       
   292     def list_tables(self, schema='public'):
       
   293         return self._query('''
       
   294             SELECT
       
   295                 c.relname as "name",
       
   296                 pg_catalog.pg_get_userbyid(c.relowner) as "owner",
       
   297                 pg_catalog.pg_relation_size(c.oid) as "size",
       
   298                 pg_catalog.obj_description(c.oid, 'pg_class') as "description",
       
   299                 c.reloptions as "options"
       
   300             FROM pg_catalog.pg_class c
       
   301             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
       
   302             WHERE n.nspname = %s AND c.relkind IN ('r','s','')
       
   303             ORDER BY 1;
       
   304             ''', [schema])
       
   305 
       
   306     def list_columns(self, table, schema='public', order=2):
       
   307         return self._query('''
       
   308             SELECT
       
   309                 --a.attrelid,
       
   310                 a.attname as "name",
       
   311                 format_type(a.atttypid, a.atttypmod) AS "type",
       
   312                 a.attnotnull as "notnull",
       
   313                 a.atthasdef as "hasdefault",
       
   314                 pg_catalog.pg_get_expr(d.adbin, d.adrelid) as "default",
       
   315                 pg_catalog.col_description(a.attrelid, a.attnum) AS "description"
       
   316             FROM pg_catalog.pg_attribute a
       
   317             LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
       
   318             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
       
   319             LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
       
   320             WHERE n.nspname = %s AND c.relname = %s AND a.attnum > 0 AND NOT a.attisdropped
       
   321             ORDER BY ''' + str(order), [schema, table])
       
   322 
       
   323     def list_constraints(self, table, schema='public'):
       
   324         return self._query('''
       
   325             SELECT
       
   326                 r.conname AS "name",
       
   327                 r.contype AS "type",
       
   328                 cf.relname AS "fname",
       
   329                 nf.nspname AS "fschema",
       
   330                 pg_catalog.pg_get_constraintdef(r.oid, true) as "definition"
       
   331             FROM pg_catalog.pg_constraint r
       
   332             JOIN pg_catalog.pg_class c ON r.conrelid = c.oid
       
   333             JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
       
   334             LEFT JOIN pg_catalog.pg_class cf ON r.confrelid = cf.oid
       
   335             LEFT JOIN pg_catalog.pg_namespace nf ON nf.oid = cf.relnamespace
       
   336             WHERE n.nspname = %s AND c.relname = %s
       
   337             ORDER BY 1
       
   338             ''', [schema, table])
       
   339 
       
   340     def list_indexes(self, table, schema='public'):
       
   341         return self._query('''
       
   342             SELECT
       
   343                 c2.relname as "name",
       
   344                 i.indisprimary as "primary",
       
   345                 i.indisunique as "unique",
       
   346                 i.indisclustered as "clustered",
       
   347                 i.indisvalid as "valid",
       
   348                 pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as "definition",
       
   349                 ARRAY(SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c2.oid ORDER BY attnum) AS "columns",
       
   350                 pg_catalog.pg_relation_size(c2.oid) as "size"
       
   351                 --c2.reltablespace as "tablespace_oid"
       
   352             FROM pg_catalog.pg_class c
       
   353             JOIN pg_catalog.pg_index i ON c.oid = i.indrelid
       
   354             JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid
       
   355             JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
       
   356             WHERE n.nspname = %(schema)s AND c.relname = %(table)s
       
   357             ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
       
   358             ''', {'schema': schema, 'table': table})
       
   359 
       
   360     def list_functions(self, schema='public'):
       
   361         '''List functions in schema.'''
       
   362         return self._query('''
       
   363             SELECT
       
   364                 p.oid as "oid",
       
   365                 p.proname || '(' || array_to_string(
       
   366                     array(SELECT pg_catalog.format_type(unnest(p.proargtypes), NULL)),
       
   367                       ', '
       
   368                 ) || ')' as "name",
       
   369                 p.proname as "function_name",
       
   370                 pg_catalog.pg_get_function_result(p.oid) as "result",
       
   371                 p.prosrc as "source",
       
   372                 CASE
       
   373                     WHEN p.proisagg THEN 'agg'
       
   374                     WHEN p.proiswindow THEN 'window'
       
   375                     WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
       
   376                     ELSE 'normal'
       
   377                 END as "type"
       
   378             FROM pg_catalog.pg_proc p
       
   379             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
       
   380             WHERE n.nspname = %s
       
   381             ORDER BY 1, 2, 4;
       
   382             ''', [schema])
       
   383 
       
   384     def list_function_args(self, oid):
       
   385         """List function arguments.
       
   386 
       
   387         Notes about query:
       
   388             type: Use allargtypes if present, argtypes otherwise.
       
   389                 The trick with [0:999] moves lower bound from 0 to default 1
       
   390                 by slicing all elements (slices has always lower bound 1).
       
   391             mode: This trick makes array of NULLs of same length as argnames,
       
   392                 in case argmodes is NULL.
       
   393             default: Use pg_get_expr, split output by ', '
       
   394                 FIXME: will fail if ', ' is present in default value string.
       
   395         """
       
   396         return self._query('''
       
   397             SELECT
       
   398               unnest(p.proargnames) AS "name",
       
   399               pg_catalog.format_type(unnest(
       
   400                 COALESCE(p.proallargtypes, (p.proargtypes::oid[])[0:999])
       
   401               ), NULL) AS "type",
       
   402               unnest(
       
   403                 COALESCE(
       
   404                   p.proargmodes::text[],
       
   405                   array(SELECT NULL::text FROM generate_series(1, array_upper(p.proargnames, 1)))
       
   406                 )
       
   407               ) AS "mode",
       
   408               unnest(array_cat(
       
   409                 array_fill(NULL::text, array[COALESCE(array_upper(p.proargnames,1),0) - p.pronargdefaults]),
       
   410                 string_to_array(pg_get_expr(p.proargdefaults, 'pg_proc'::regclass, true), ', ')
       
   411               )) AS "default"
       
   412             FROM pg_proc p
       
   413             WHERE p.oid = %s''', [oid])
       
   414 
       
   415     def get_function_definition(self, oid):
       
   416         """Get full function definition, including CREATE command etc.
       
   417 
       
   418         Args:
       
   419             oid: function oid from pg_catalog.pg_proc (returned by list_functions)
       
   420 
       
   421         """
       
   422         return self._query('''SELECT pg_get_functiondef(%s) AS definition;''', [oid])[0]['definition']
       
   423 
       
   424     def list_types(self, schema='public'):
       
   425         """List types in schema.
       
   426 
       
   427         http://www.postgresql.org/docs/8.4/static/catalog-pg-type.html
       
   428 
       
   429         """
       
   430         return self._query('''
       
   431             SELECT
       
   432                 t.typname AS "name",
       
   433                 CASE
       
   434                     WHEN t.typtype = 'b' THEN 'base'::text
       
   435                     WHEN t.typtype = 'c' THEN 'composite'::text
       
   436                     WHEN t.typtype = 'd' THEN 'domain'::text
       
   437                     WHEN t.typtype = 'e' THEN 'enum'::text
       
   438                     WHEN t.typtype = 'p' THEN 'pseudo'::text
       
   439                 END AS "type",
       
   440                 ARRAY(
       
   441                       SELECT e.enumlabel
       
   442                       FROM pg_catalog.pg_enum e
       
   443                       WHERE e.enumtypid = t.oid
       
   444                       ORDER BY e.oid
       
   445                 ) AS "elements",
       
   446                 pg_catalog.obj_description(t.oid, 'pg_type') AS "description"
       
   447             FROM pg_catalog.pg_type t
       
   448             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
       
   449             WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
       
   450               AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
       
   451                   AND n.nspname <> 'pg_catalog'
       
   452                   AND n.nspname <> 'information_schema'
       
   453               AND n.nspname = %(schema)s
       
   454             ORDER BY 1, 2;
       
   455         ''', {'schema': schema})
       
   456 
       
   457     def list_sequences(self, schema=None):
       
   458         '''List sequences in schema.'''
       
   459         return self._query('''
       
   460             SELECT
       
   461                 nc.nspname AS "sequence_schema",
       
   462                 c.relname AS "sequence_name",
       
   463                 t.relname AS "related_table",
       
   464                 a.attname AS "related_column",
       
   465                 format_type(a.atttypid, a.atttypmod) AS "related_column_type"
       
   466             FROM pg_class c
       
   467             JOIN pg_namespace nc ON nc.oid = c.relnamespace
       
   468             JOIN pg_depend d ON d.objid = c.oid
       
   469             JOIN pg_class t ON d.refobjid = t.oid
       
   470             JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
       
   471             WHERE c.relkind = 'S' AND NOT pg_is_other_temp_schema(nc.oid)
       
   472             ''' + (schema and ' AND nc.nspname = %(schema)s' or '') + '''
       
   473         ''', {'schema': schema})
       
   474 
       
   475     def list_column_usage(self, table, column, schema='public'):
       
   476         '''List objects using the column.
       
   477 
       
   478         Currently shows views and constraints which use the column.
       
   479 
       
   480         This is useful to find which views block alteration of column type etc.
       
   481 
       
   482         '''
       
   483         return self._query('''
       
   484             SELECT
       
   485                 'view' AS type, view_schema AS schema, view_name AS name
       
   486             FROM information_schema.view_column_usage
       
   487             WHERE table_schema=%(schema)s AND table_name=%(table)s AND column_name=%(column)s
       
   488 
       
   489             UNION
       
   490 
       
   491             SELECT
       
   492                 'constraint' AS type, constraint_schema AS schema, constraint_name AS name
       
   493             FROM information_schema.constraint_column_usage
       
   494             WHERE table_schema=%(schema)s AND table_name=%(table)s AND column_name=%(column)s
       
   495             ''', {'schema':schema, 'table':table, 'column':column})
       
   496