pgtoolkit/pgbrowser.py
author Radek Brich <radek.brich@devl.cz>
Tue, 04 Feb 2014 16:10:04 +0100
changeset 94 a10f553e6f6a
parent 87 3ef717ee9253
child 95 6adcb7ee4517
permissions -rw-r--r--
PgDiff: Add patch support for SQL functions.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
# -*- coding: utf-8 -*-
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     2
#
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     3
# PgBrowser - browse database schema and metadata
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     4
#
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     5
# Some of the queries came from psql.
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     6
#
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     7
# Copyright (c) 2011  Radek Brich <radek.brich@devl.cz>
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     8
#
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     9
# Permission is hereby granted, free of charge, to any person obtaining a copy
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    10
# of this software and associated documentation files (the "Software"), to deal
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    11
# in the Software without restriction, including without limitation the rights
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    13
# copies of the Software, and to permit persons to whom the Software is
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    14
# furnished to do so, subject to the following conditions:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
#
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
# The above copyright notice and this permission notice shall be included in
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
# all copies or substantial portions of the Software.
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    18
#
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    19
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    20
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    21
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    22
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    23
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    24
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    25
# THE SOFTWARE.
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    26
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    27
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    28
from collections import OrderedDict
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    29
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    30
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    31
class Column:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    32
    def __init__(self, browser, table,
87
3ef717ee9253 PgDiff: Add support for indexes.
Radek Brich <radek.brich@devl.cz>
parents: 86
diff changeset
    33
            name, type, notnull, hasdefault, default, description):
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    34
        self.browser = browser  # Browser instance
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    35
        self.table = table  # Table instance
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    36
        self.name = name
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    37
        self.type = type
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    38
        self.notnull = notnull
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    39
        self.hasdefault = hasdefault
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    40
        self.default = default
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    41
        self.description = description
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
    42
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    43
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    44
class Constraint:
40
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    45
    def __init__(self, browser, table, name, type, fname, fschema, definition):
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    46
        self.browser = browser
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    47
        self.table = table
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    48
        self.name = name
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    49
        self.type = type
40
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    50
        self.fname = fname  # foreign table name
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
    51
        self.fschema = fschema  # foreign table schema
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    52
        self.definition = definition
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    53
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    54
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    55
class Index:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    56
    def __init__(self, browser, table,
87
3ef717ee9253 PgDiff: Add support for indexes.
Radek Brich <radek.brich@devl.cz>
parents: 86
diff changeset
    57
            name, primary, unique, clustered, valid, definition,
3ef717ee9253 PgDiff: Add support for indexes.
Radek Brich <radek.brich@devl.cz>
parents: 86
diff changeset
    58
            columns):
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    59
        self.browser = browser
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    60
        self.table = table
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    61
        self.name = name
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    62
        self.primary = primary
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    63
        self.unique = unique
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    64
        self.clustered = clustered
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    65
        self.valid = valid
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    66
        self.definition = definition
87
3ef717ee9253 PgDiff: Add support for indexes.
Radek Brich <radek.brich@devl.cz>
parents: 86
diff changeset
    67
        self.columns = columns
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    68
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    69
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    70
class Table:
68
b0d972be2631 Add listtables tool.
Radek Brich <radek.brich@devl.cz>
parents: 59
diff changeset
    71
    def __init__(self, browser, schema, name, owner, size, description, options):
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    72
        self._columns = None
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    73
        self._constraints = None
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    74
        self._indexes = None
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    75
        self.browser = browser  # Browser instance
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    76
        self.schema = schema  # Schema instance
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    77
        self.name = name  # table name, str
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    78
        self.owner = owner
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    79
        self.size = size
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    80
        self.description = description
68
b0d972be2631 Add listtables tool.
Radek Brich <radek.brich@devl.cz>
parents: 59
diff changeset
    81
        self.options = options or []
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    82
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    83
    def refresh(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    84
        self.refresh_columns()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    85
        self.refresh_constraints()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    86
        self.refresh_indexes()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    87
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    88
    def refresh_columns(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    89
        rows = self.browser.list_columns(self.name, self.schema.name)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    90
        self._columns = OrderedDict([(x['name'], Column(self.browser, self, **x)) for x in rows])
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    91
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    92
    def refresh_constraints(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    93
        rows = self.browser.list_constraints(self.name, self.schema.name)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    94
        self._constraints = OrderedDict([(x['name'], Constraint(self.browser, self, **x)) for x in rows])
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
    95
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    96
    def refresh_indexes(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    97
        rows = self.browser.list_indexes(self.name, self.schema.name)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    98
        self._indexes = OrderedDict([(x['name'], Index(self.browser, self, **x)) for x in rows])
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    99
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   100
    def getcolumns(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   101
        if self._columns is None:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   102
            self.refresh_columns()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   103
        return self._columns
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   104
    columns = property(getcolumns)
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
   105
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   106
    def getconstraints(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   107
        if self._constraints is None:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   108
            self.refresh_constraints()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   109
        return self._constraints
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   110
    constraints = property(getconstraints)
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
   111
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   112
    def getindexes(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   113
        if self._indexes is None:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   114
            self.refresh_indexes()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   115
        return self._indexes
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   116
    indexes = property(getindexes)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   117
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   118
59
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   119
class Argument:
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   120
    def __init__(self, browser, function, name, type, mode, default):
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   121
        # PgBrowser instance
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   122
        self.browser = browser
59
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   123
        # Function instance
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   124
        self.function = function
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   125
        self.name = name
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   126
        self.type = type
59
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   127
        self.mode = mode
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   128
        self.default = default
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   129
94
a10f553e6f6a PgDiff: Add patch support for SQL functions.
Radek Brich <radek.brich@devl.cz>
parents: 87
diff changeset
   130
59
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   131
class Function:
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   132
    def __init__(self, browser, schema, oid, name, function_name, type, result, source):
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   133
        self.browser = browser
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   134
        self.schema = schema
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   135
        self.oid = oid
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   136
        #: unique name - function name + arg types
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   137
        self.name = name
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   138
        #: pure function name without args
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   139
        self.function_name = function_name
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   140
        self.type = type
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   141
        self.result = result
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   142
        self.source = source
59
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   143
        self._arguments = None
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   144
        self._definition = None
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   145
59
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   146
    def refresh(self):
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   147
        self.refresh_args()
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   148
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   149
    def refresh_args(self):
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   150
        rows = self.browser.list_function_args(self.oid)
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   151
        self._arguments = OrderedDict([(x['name'], Argument(self.browser, self, **x)) for x in rows])
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   152
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   153
    @property
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   154
    def arguments(self):
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   155
        if self._arguments is None:
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   156
            self.refresh_args()
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   157
        return self._arguments
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   158
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   159
    @property
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   160
    def definition(self):
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   161
        """Get full function definition including CREATE command."""
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   162
        if not self._definition:
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   163
            self._definition = self.browser.get_function_definition(self.oid)
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   164
        return self._definition
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   165
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   166
85
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   167
class Type:
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   168
    def __init__(self, browser, schema, name, type, elements, description):
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   169
        self.browser = browser
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   170
        self.schema = schema
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   171
        self.name = name
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   172
        self.type = type
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   173
        self.elements = elements
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   174
        self.description = description
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   175
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   176
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   177
class Schema:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   178
    def __init__(self, browser, name, owner, acl, description, system):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   179
        self._tables = None
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   180
        self._functions = None
85
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   181
        self._types = None
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   182
        self.browser = browser
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   183
        self.name = name
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   184
        self.owner = owner
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   185
        self.acl = acl
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   186
        self.description = description
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   187
        self.system = system
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   188
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   189
    def refresh(self):
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   190
        self.refresh_tables()
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   191
        self.refresh_functions()
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   192
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   193
    def refresh_tables(self):
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   194
        rows = self.browser.list_tables(self.name)
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
   195
        self._tables = OrderedDict([(x['name'], Table(self.browser, self, **x)) for x in rows])
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   196
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   197
    def refresh_functions(self):
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   198
        rows = self.browser.list_functions(self.name)
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   199
        self._functions = OrderedDict([(x['name'], Function(self.browser, self, **x)) for x in rows])
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   200
85
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   201
    def refresh_types(self):
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   202
        rows = self.browser.list_types(self.name)
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   203
        self._types = OrderedDict([(x['name'], Type(self.browser, self, **x)) for x in rows])
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   204
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   205
    @property
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   206
    def tables(self):
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   207
        if self._tables is None:
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   208
            self.refresh_tables()
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   209
        return self._tables
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   210
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   211
    @property
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   212
    def functions(self):
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   213
        if self._functions is None:
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   214
            self.refresh_functions()
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   215
        return self._functions
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   216
85
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   217
    @property
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   218
    def types(self):
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   219
        if self._types is None:
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   220
            self.refresh_types()
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   221
        return self._types
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   222
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   223
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   224
class PgBrowser:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   225
    def __init__(self, conn=None):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   226
        self._schemas = None
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   227
        self.conn = conn
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
   228
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   229
    def setconn(self, conn=None):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   230
        self.conn = conn
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
   231
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   232
    def refresh(self):
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   233
        self.refresh_schemas()
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   234
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   235
    def refresh_schemas(self):
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   236
        rows = self.list_schemas()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   237
        self._schemas = OrderedDict([(x['name'], Schema(self, **x)) for x in rows])
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   238
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   239
    @property
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   240
    def schemas(self):
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   241
        if self._schemas is None:
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   242
            self.refresh_schemas()
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   243
        return self._schemas
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
   244
31
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   245
    def _query(self, query, args):
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   246
        try:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   247
            curs = self.conn.cursor()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   248
            curs.execute(query, args)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   249
            curs.connection.commit()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   250
            rows = curs.fetchall()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   251
            return [dict(zip([desc[0] for desc in curs.description], row)) for row in rows]
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   252
        finally:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   253
            curs.close()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   254
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   255
    def list_databases(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   256
        return self._query('''
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   257
            SELECT
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   258
                d.datname as "name",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   259
                pg_catalog.pg_get_userbyid(d.datdba) as "owner",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   260
                pg_catalog.pg_encoding_to_char(d.encoding) as "encoding",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   261
                d.datcollate as "collation",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   262
                d.datctype as "ctype",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   263
                d.datacl AS "acl",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   264
                CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   265
                    THEN pg_catalog.pg_database_size(d.datname)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   266
                    ELSE -1 -- No access
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   267
                END as "size",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   268
                t.spcname as "tablespace",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   269
                pg_catalog.shobj_description(d.oid, 'pg_database') as "description"
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   270
            FROM pg_catalog.pg_database d
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   271
            JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   272
            ORDER BY 1;
31
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   273
            ''', [])
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   274
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   275
    def list_schemas(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   276
        return self._query('''
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   277
            SELECT
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   278
                n.nspname AS "name",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   279
                pg_catalog.pg_get_userbyid(n.nspowner) AS "owner",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   280
                n.nspacl AS "acl",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   281
                pg_catalog.obj_description(n.oid, 'pg_namespace') AS "description",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   282
                CASE WHEN n.nspname IN ('information_schema', 'pg_catalog', 'pg_toast')
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   283
                    OR n.nspname ~ '^pg_temp_' OR n.nspname ~ '^pg_toast_temp_'
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   284
                    THEN TRUE
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   285
                    ELSE FALSE
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   286
                END AS "system"
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   287
            FROM pg_catalog.pg_namespace n
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   288
            ORDER BY 1;
31
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   289
            ''', [])
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   290
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   291
    def list_tables(self, schema='public'):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   292
        return self._query('''
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   293
            SELECT
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   294
                c.relname as "name",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   295
                pg_catalog.pg_get_userbyid(c.relowner) as "owner",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   296
                pg_catalog.pg_relation_size(c.oid) as "size",
68
b0d972be2631 Add listtables tool.
Radek Brich <radek.brich@devl.cz>
parents: 59
diff changeset
   297
                pg_catalog.obj_description(c.oid, 'pg_class') as "description",
b0d972be2631 Add listtables tool.
Radek Brich <radek.brich@devl.cz>
parents: 59
diff changeset
   298
                c.reloptions as "options"
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   299
            FROM pg_catalog.pg_class c
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   300
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   301
            WHERE n.nspname = %s AND c.relkind IN ('r','s','')
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   302
            ORDER BY 1;
31
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   303
            ''', [schema])
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   304
12
203be9022b46 Fix pgdatadiff.
Radek Brich <radek.brich@devl.cz>
parents: 9
diff changeset
   305
    def list_columns(self, table, schema='public', order=2):
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   306
        return self._query('''
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   307
            SELECT
40
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   308
                --a.attrelid,
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   309
                a.attname as "name",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   310
                format_type(a.atttypid, a.atttypmod) AS "type",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   311
                a.attnotnull as "notnull",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   312
                a.atthasdef as "hasdefault",
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
   313
                pg_catalog.pg_get_expr(d.adbin, d.adrelid) as "default",
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   314
                pg_catalog.col_description(a.attrelid, a.attnum) AS "description"
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   315
            FROM pg_catalog.pg_attribute a
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   316
            LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   317
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   318
            LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   319
            WHERE n.nspname = %s AND c.relname = %s AND a.attnum > 0 AND NOT a.attisdropped
31
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   320
            ORDER BY ''' + str(order), [schema, table])
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   321
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   322
    def list_constraints(self, table, schema='public'):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   323
        return self._query('''
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   324
            SELECT
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
   325
                r.conname AS "name",
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
   326
                r.contype AS "type",
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
   327
                cf.relname AS "fname",
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
   328
                nf.nspname AS "fschema",
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   329
                pg_catalog.pg_get_constraintdef(r.oid, true) as "definition"
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   330
            FROM pg_catalog.pg_constraint r
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   331
            JOIN pg_catalog.pg_class c ON r.conrelid = c.oid
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   332
            JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
35
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
   333
            LEFT JOIN pg_catalog.pg_class cf ON r.confrelid = cf.oid
e7f79c4a27ce TableCopyTool: order tables with respect to references.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
   334
            LEFT JOIN pg_catalog.pg_namespace nf ON nf.oid = cf.relnamespace
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   335
            WHERE n.nspname = %s AND c.relname = %s
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   336
            ORDER BY 1
31
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   337
            ''', [schema, table])
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   338
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   339
    def list_indexes(self, table, schema='public'):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   340
        return self._query('''
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   341
            SELECT
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   342
                c2.relname as "name",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   343
                i.indisprimary as "primary",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   344
                i.indisunique as "unique",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   345
                i.indisclustered as "clustered",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   346
                i.indisvalid as "valid",
31
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   347
                pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as "definition",
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   348
                ARRAY(SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c2.oid ORDER BY attnum) AS "columns"
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   349
                --c2.reltablespace as "tablespace_oid"
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   350
            FROM pg_catalog.pg_class c
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   351
            JOIN pg_catalog.pg_index i ON c.oid = i.indrelid
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   352
            JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   353
            JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
31
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   354
            WHERE n.nspname = %(schema)s AND c.relname = %(table)s
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   355
            ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
31
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   356
            ''', {'schema': schema, 'table': table})
0
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   357
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   358
    def list_functions(self, schema='public'):
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   359
        '''List functions in schema.'''
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   360
        return self._query('''
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   361
            SELECT
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   362
                p.oid as "oid",
59
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   363
                p.proname || '(' || array_to_string(
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   364
                    array(SELECT pg_catalog.format_type(unnest(p.proargtypes), NULL)),
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   365
                      ', '
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   366
                ) || ')' as "name",
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   367
                p.proname as "function_name",
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   368
                pg_catalog.pg_get_function_result(p.oid) as "result",
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   369
                p.prosrc as "source",
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   370
                CASE
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   371
                    WHEN p.proisagg THEN 'agg'
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   372
                    WHEN p.proiswindow THEN 'window'
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   373
                    WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   374
                    ELSE 'normal'
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   375
                END as "type"
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   376
            FROM pg_catalog.pg_proc p
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   377
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
59
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   378
            WHERE n.nspname = %s
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   379
            ORDER BY 1, 2, 4;
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   380
            ''', [schema])
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   381
59
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   382
    def list_function_args(self, oid):
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   383
        """List function arguments.
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   384
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   385
        Notes about query:
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   386
            type: Use allargtypes if present, argtypes otherwise.
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   387
                The trick with [0:999] moves lower bound from 0 to default 1
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   388
                by slicing all elements (slices has always lower bound 1).
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   389
            mode: This trick makes array of NULLs of same length as argnames,
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   390
                in case argmodes is NULL.
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   391
            default: Use pg_get_expr, split output by ', '
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   392
                FIXME: will fail if ', ' is present in default value string.
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   393
        """
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   394
        return self._query('''
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   395
            SELECT
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   396
              unnest(p.proargnames) AS "name",
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   397
              pg_catalog.format_type(unnest(
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   398
                COALESCE(p.proallargtypes, (p.proargtypes::oid[])[0:999])
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   399
              ), NULL) AS "type",
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   400
              unnest(
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   401
                COALESCE(
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   402
                  p.proargmodes::text[],
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   403
                  array(SELECT NULL::text FROM generate_series(1, array_upper(p.proargnames, 1)))
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   404
                )
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   405
              ) AS "mode",
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   406
              unnest(array_cat(
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   407
                array_fill(NULL::text, array[COALESCE(array_upper(p.proargnames,1),0) - p.pronargdefaults]),
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   408
                string_to_array(pg_get_expr(p.proargdefaults, 'pg_proc'::regclass, true), ', ')
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   409
              )) AS "default"
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   410
            FROM pg_proc p
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   411
            WHERE p.oid = %s''', [oid])
65efd0c6919f PgBrowser: add function arguments as another level in hierarchy. PgDiff: compare function arguments one by one.
Radek Brich <radek.brich@devl.cz>
parents: 58
diff changeset
   412
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   413
    def get_function_definition(self, oid):
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   414
        """Get full function definition, including CREATE command etc.
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   415
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   416
        Args:
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   417
            oid: function oid from pg_catalog.pg_proc (returned by list_functions)
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   418
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   419
        """
94
a10f553e6f6a PgDiff: Add patch support for SQL functions.
Radek Brich <radek.brich@devl.cz>
parents: 87
diff changeset
   420
        return self._query('''SELECT pg_get_functiondef(%s) AS definition;''', [oid])[0]['definition']
58
0bcc13460dae PgBrowser: Add functions. PgDiff: Compare functions.
Radek Brich <radek.brich@devl.cz>
parents: 52
diff changeset
   421
85
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   422
    def list_types(self, schema='public'):
86
b61b54aa9f96 Fix: ConfigParser add_argument -> add_option.
Radek Brich <brich.radek@ifortuna.cz>
parents: 85
diff changeset
   423
        """List types in schema.
b61b54aa9f96 Fix: ConfigParser add_argument -> add_option.
Radek Brich <brich.radek@ifortuna.cz>
parents: 85
diff changeset
   424
b61b54aa9f96 Fix: ConfigParser add_argument -> add_option.
Radek Brich <brich.radek@ifortuna.cz>
parents: 85
diff changeset
   425
        http://www.postgresql.org/docs/8.4/static/catalog-pg-type.html
b61b54aa9f96 Fix: ConfigParser add_argument -> add_option.
Radek Brich <brich.radek@ifortuna.cz>
parents: 85
diff changeset
   426
b61b54aa9f96 Fix: ConfigParser add_argument -> add_option.
Radek Brich <brich.radek@ifortuna.cz>
parents: 85
diff changeset
   427
        """
85
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   428
        return self._query('''
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   429
            SELECT
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   430
                t.typname AS "name",
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   431
                CASE
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   432
                    WHEN t.typtype = 'b' THEN 'base'::text
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   433
                    WHEN t.typtype = 'c' THEN 'composite'::text
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   434
                    WHEN t.typtype = 'd' THEN 'domain'::text
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   435
                    WHEN t.typtype = 'e' THEN 'enum'::text
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   436
                    WHEN t.typtype = 'p' THEN 'pseudo'::text
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   437
                END AS "type",
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   438
                ARRAY(
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   439
                      SELECT e.enumlabel
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   440
                      FROM pg_catalog.pg_enum e
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   441
                      WHERE e.enumtypid = t.oid
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   442
                      ORDER BY e.oid
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   443
                ) AS "elements",
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   444
                pg_catalog.obj_description(t.oid, 'pg_type') AS "description"
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   445
            FROM pg_catalog.pg_type t
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   446
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   447
            WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   448
              AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   449
                  AND n.nspname <> 'pg_catalog'
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   450
                  AND n.nspname <> 'information_schema'
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   451
              AND n.nspname = %(schema)s
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   452
            ORDER BY 1, 2;
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   453
        ''', {'schema': schema})
11a282e23e0d Add basic support for types in browser and schema diff.
Radek Brich <brich.radek@ifortuna.cz>
parents: 68
diff changeset
   454
40
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   455
    def list_sequences(self, schema=None):
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   456
        '''List sequences in schema.'''
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   457
        return self._query('''
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   458
            SELECT
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   459
                nc.nspname AS "sequence_schema",
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   460
                c.relname AS "sequence_name",
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   461
                t.relname AS "related_table",
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   462
                a.attname AS "related_column",
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   463
                format_type(a.atttypid, a.atttypmod) AS "related_column_type"
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   464
            FROM pg_class c
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   465
            JOIN pg_namespace nc ON nc.oid = c.relnamespace
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   466
            JOIN pg_depend d ON d.objid = c.oid
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   467
            JOIN pg_class t ON d.refobjid = t.oid
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   468
            JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   469
            WHERE c.relkind = 'S' AND NOT pg_is_other_temp_schema(nc.oid)
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   470
            ''' + (schema and ' AND nc.nspname = %(schema)s' or '') + '''
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   471
        ''', {'schema': schema})
922d7fb63384 Add listserial tool. PgBrowser: Add list_sequences.
Radek Brich <radek.brich@devl.cz>
parents: 35
diff changeset
   472
31
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   473
    def list_column_usage(self, table, column, schema='public'):
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   474
        '''List objects using the column.
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
   475
32
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents: 31
diff changeset
   476
        Currently shows views and constraints which use the column.
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
   477
32
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents: 31
diff changeset
   478
        This is useful to find which views block alteration of column type etc.
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
   479
31
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   480
        '''
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   481
        return self._query('''
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   482
            SELECT
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   483
                'view' AS type, view_schema AS schema, view_name AS name
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   484
            FROM information_schema.view_column_usage
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   485
            WHERE table_schema=%(schema)s AND table_name=%(table)s AND column_name=%(column)s
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
   486
32
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents: 31
diff changeset
   487
            UNION
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 40
diff changeset
   488
32
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents: 31
diff changeset
   489
            SELECT
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents: 31
diff changeset
   490
                'constraint' AS type, constraint_schema AS schema, constraint_name AS name
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents: 31
diff changeset
   491
            FROM information_schema.constraint_column_usage
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents: 31
diff changeset
   492
            WHERE table_schema=%(schema)s AND table_name=%(table)s AND column_name=%(column)s
31
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   493
            ''', {'schema':schema, 'table':table, 'column':column})
c2e6e24b83d9 Add browser - database schema browser using tuikit (curses UI). Add listdepends - tool which shows depending views for column. Update pgdatadiff - allow composite primary key. Update pgmanager - RowDict is now OrderedDict. Drop support for Python2.x.
Radek Brich <radek.brich@devl.cz>
parents: 12
diff changeset
   494