pgtools/pgbrowser.py
author Radek Brich <radek.brich@devl.cz>
Mon, 18 Jul 2011 17:39:37 +0200
changeset 2 efee419b7a2d
parent 0 eaae9539e910
permissions -rw-r--r--
Fix pgmanager exceptions. Add CLI tools: bigtables, longqueries, schemadiff.
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,
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    33
        name, type, notnull, hasdefault, default, description):
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
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    42
        
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:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    45
    def __init__(self, browser, table, name, type, definition):
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
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    50
        self.definition = definition
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    51
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    52
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    53
class Index:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    54
    def __init__(self, browser, table,
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    55
        name, primary, unique, clustered, valid, definition):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    56
        self.browser = browser
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    57
        self.table = table
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    58
        self.name = name
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    59
        self.primary = primary
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    60
        self.unique = unique
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    61
        self.clustered = clustered
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    62
        self.valid = valid
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    63
        self.definition = definition
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    64
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    65
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    66
class Table:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    67
    def __init__(self, browser, schema, name, owner, size, description):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    68
        self._columns = None
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    69
        self._constraints = None
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    70
        self._indexes = None
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    71
        self.browser = browser  # Browser instance
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    72
        self.schema = schema  # Schema instance
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    73
        self.name = name  # table name, str
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    74
        self.owner = owner
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    75
        self.size = size
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    76
        self.description = description
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    77
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    78
    def refresh(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    79
        self.refresh_columns()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    80
        self.refresh_constraints()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    81
        self.refresh_indexes()
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_columns(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    84
        rows = self.browser.list_columns(self.name, self.schema.name)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    85
        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
    86
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    87
    def refresh_constraints(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    88
        rows = self.browser.list_constraints(self.name, self.schema.name)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    89
        self._constraints = OrderedDict([(x['name'], Constraint(self.browser, self, **x)) for x in rows])
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    90
        
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    91
    def refresh_indexes(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    92
        rows = self.browser.list_indexes(self.name, self.schema.name)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    93
        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
    94
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    95
    def getcolumns(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    96
        if self._columns is None:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    97
            self.refresh_columns()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    98
        return self._columns
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    99
    columns = property(getcolumns)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   100
    
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   101
    def getconstraints(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   102
        if self._constraints is None:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   103
            self.refresh_constraints()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   104
        return self._constraints
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   105
    constraints = property(getconstraints)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   106
        
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   107
    def getindexes(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   108
        if self._indexes is None:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   109
            self.refresh_indexes()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   110
        return self._indexes
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   111
    indexes = property(getindexes)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   112
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   113
class Schema:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   114
    def __init__(self, browser, name, owner, acl, description, system):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   115
        self._tables = None
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   116
        self.browser = browser
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   117
        self.name = name
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   118
        self.owner = owner
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   119
        self.acl = acl
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   120
        self.description = description
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   121
        self.system = system
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   122
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   123
    def refresh(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   124
        rows = self.browser.list_tables(self.name)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   125
        self._tables = OrderedDict([(x['name'], Table(self.browser, self, **x)) for x in rows]) 
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   126
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   127
    def gettables(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   128
        if self._tables is None:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   129
            self.refresh()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   130
        return self._tables
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   131
    tables = property(gettables)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   132
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   133
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   134
class PgBrowser:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   135
    def __init__(self, conn=None):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   136
        self._schemas = None
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   137
        self.conn = conn
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   138
        
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   139
    def setconn(self, conn=None):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   140
        self.conn = conn
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   141
    
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   142
    def refresh(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   143
        rows = self.list_schemas()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   144
        self._schemas = OrderedDict([(x['name'], Schema(self, **x)) for x in rows])
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   145
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   146
    def getschemas(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   147
        if self._schemas is None:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   148
            self.refresh()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   149
        return self._schemas
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   150
    schemas = property(getschemas)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   151
                    
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   152
    def _query(self, query, *args):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   153
        try:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   154
            curs = self.conn.cursor()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   155
            curs.execute(query, args)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   156
            curs.connection.commit()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   157
            rows = curs.fetchall()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   158
            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
   159
        finally:
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   160
            curs.close()
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   161
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   162
    def list_databases(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   163
        return self._query('''
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   164
            SELECT
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   165
                d.datname as "name",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   166
                pg_catalog.pg_get_userbyid(d.datdba) as "owner",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   167
                pg_catalog.pg_encoding_to_char(d.encoding) as "encoding",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   168
                d.datcollate as "collation",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   169
                d.datctype as "ctype",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   170
                d.datacl AS "acl",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   171
                CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   172
                    THEN pg_catalog.pg_database_size(d.datname)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   173
                    ELSE -1 -- No access
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   174
                END as "size",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   175
                t.spcname as "tablespace",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   176
                pg_catalog.shobj_description(d.oid, 'pg_database') as "description"
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   177
            FROM pg_catalog.pg_database d
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   178
            JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   179
            ORDER BY 1;
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   180
            ''')
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   181
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   182
    def list_schemas(self):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   183
        return self._query('''
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   184
            SELECT
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   185
                n.nspname AS "name",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   186
                pg_catalog.pg_get_userbyid(n.nspowner) AS "owner",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   187
                n.nspacl AS "acl",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   188
                pg_catalog.obj_description(n.oid, 'pg_namespace') AS "description",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   189
                CASE WHEN n.nspname IN ('information_schema', 'pg_catalog', 'pg_toast')
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   190
                    OR n.nspname ~ '^pg_temp_' OR n.nspname ~ '^pg_toast_temp_'
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   191
                    THEN TRUE
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   192
                    ELSE FALSE
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   193
                END AS "system"
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   194
            FROM pg_catalog.pg_namespace n
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   195
            ORDER BY 1;
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   196
            ''')
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   197
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   198
    def list_tables(self, schema='public'):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   199
        return self._query('''
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   200
            SELECT
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   201
                c.relname as "name",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   202
                pg_catalog.pg_get_userbyid(c.relowner) as "owner",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   203
                pg_catalog.pg_relation_size(c.oid) as "size",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   204
                pg_catalog.obj_description(c.oid, 'pg_class') as "description"
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   205
            FROM pg_catalog.pg_class c
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   206
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   207
            WHERE n.nspname = %s AND c.relkind IN ('r','s','')
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   208
            ORDER BY 1;
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   209
            ''', schema)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   210
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   211
    def list_columns(self, table, schema='public'):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   212
        return self._query('''
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   213
            SELECT
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   214
                a.attname as "name",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   215
                format_type(a.atttypid, a.atttypmod) AS "type",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   216
                a.attnotnull as "notnull",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   217
                a.atthasdef as "hasdefault",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   218
                d.adsrc as "default",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   219
                pg_catalog.col_description(a.attrelid, a.attnum) AS "description"
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   220
            FROM pg_catalog.pg_attribute a
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   221
            LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   222
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   223
            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
   224
            WHERE n.nspname = %s AND c.relname = %s AND a.attnum > 0 AND NOT a.attisdropped
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   225
            ORDER BY 1
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   226
            ''', schema, table)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   227
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   228
    def list_constraints(self, table, schema='public'):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   229
        return self._query('''
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   230
            SELECT
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   231
                conname as "name",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   232
                r.contype as "type",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   233
                pg_catalog.pg_get_constraintdef(r.oid, true) as "definition"
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   234
            FROM pg_catalog.pg_constraint r
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   235
            JOIN pg_catalog.pg_class c ON r.conrelid = c.oid
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   236
            JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   237
            WHERE n.nspname = %s AND c.relname = %s
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   238
            ORDER BY 1
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   239
            ''', schema, table)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   240
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   241
    def list_indexes(self, table, schema='public'):
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   242
        return self._query('''
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   243
            SELECT
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   244
                c2.relname as "name",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   245
                i.indisprimary as "primary",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   246
                i.indisunique as "unique",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   247
                i.indisclustered as "clustered",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   248
                i.indisvalid as "valid",
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   249
                pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as "definition"
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   250
                --c2.reltablespace as "tablespace_oid"
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   251
            FROM pg_catalog.pg_class c
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   252
            JOIN pg_catalog.pg_index i ON c.oid = i.indrelid
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   253
            JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   254
            JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   255
            WHERE n.nspname = %s AND c.relname = %s
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   256
            ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   257
            ''', schema, table)
eaae9539e910 Postgres tools.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   258