--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/browser.py	Mon Mar 05 18:36:46 2012 +0100
@@ -0,0 +1,50 @@
+#!/usr/bin/env python3
+# -*- coding: utf-8 -*-
+
+import locale
+locale.setlocale(locale.LC_ALL, '')
+
+from tuikit import *
+from pgtoolkit import pgbrowser
+from pgtoolkit.toolbase import SimpleTool
+
+
+class MyApplication(Application, SimpleTool):
+    def __init__(self):
+        Application.__init__(self)
+        SimpleTool.__init__(self, name='browser', desc='PostgreSQL database browser.')
+        self.init()
+        
+        self.top.connect('keypress', self.globalkeypress)
+
+        browser = pgbrowser.PgBrowser(self.pgm.get_conn('target'))
+        model = TreeModel()
+        view = TreeView(model)
+        self.top.add(view)
+        
+        # populate schemas
+        schemas = browser.list_schemas()
+        model.add('/',  [schema['name'] for schema in schemas if not schema['system']])
+        # populate tables
+        for schema in schemas:
+            if schema['system']:
+                continue
+            tables = browser.list_tables(schema=schema['name'])
+            schemanode = '/'+schema['name']
+            model.add(schemanode,  [table['name'] for table in tables])
+            view.collapse(schemanode)        
+        
+        vert = VerticalLayout()
+        self.top.layout(vert)
+        
+        view.setfocus()
+
+    def globalkeypress(self, keyname, char):
+        if keyname == 'escape':
+            self.terminate()
+
+
+if __name__ == '__main__':
+    app = MyApplication()
+    app.start()
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/listdepends.py	Mon Mar 05 18:36:46 2012 +0100
@@ -0,0 +1,25 @@
+#!/usr/bin/env python3.2
+
+from pgtoolkit import pgbrowser, toolbase, prettysize
+
+
+class ListDependsTool(toolbase.SimpleTool):
+    def __init__(self):
+        toolbase.SimpleTool.__init__(self, name='listdepends', desc='List column dependencies.')
+        self.parser.add_argument('table', metavar='table', type=str, help='Table name.')
+        self.parser.add_argument('column', metavar='column', type=str, help='Column name.')
+        self.parser.add_argument('-s', '--schema', dest='schema', metavar='schema',
+            type=str, default='public', help='Schema name (default=public).')
+        self.init()
+
+    def main(self):
+        browser = pgbrowser.PgBrowser(self.pgm.get_conn('target'))
+
+        objects = browser.list_column_usage(self.args.table, self.args.column, schema=self.args.schema)
+        for obj in sorted(objects, key=lambda x: (x['type'], x['schema'], x['name'])):
+            print(obj['type'], ' ', obj['schema'], '.', obj['name'], sep='')
+
+
+tool = ListDependsTool()
+tool.main()
+

--- a/pgtoolkit/pgbrowser.py	Mon Feb 27 15:12:40 2012 +0100
+++ b/pgtoolkit/pgbrowser.py	Mon Mar 05 18:36:46 2012 +0100
@@ -149,7 +149,7 @@
         return self._schemas
     schemas = property(getschemas)
                     
-    def _query(self, query, *args):
+    def _query(self, query, args):
         try:
             curs = self.conn.cursor()
             curs.execute(query, args)
@@ -177,7 +177,7 @@
             FROM pg_catalog.pg_database d
             JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
             ORDER BY 1;
-            ''')
+            ''', [])
 
     def list_schemas(self):
         return self._query('''
@@ -193,7 +193,7 @@
                 END AS "system"
             FROM pg_catalog.pg_namespace n
             ORDER BY 1;
-            ''')
+            ''', [])
 
     def list_tables(self, schema='public'):
         return self._query('''
@@ -206,7 +206,7 @@
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             WHERE n.nspname = %s AND c.relkind IN ('r','s','')
             ORDER BY 1;
-            ''', schema)
+            ''', [schema])
 
     def list_columns(self, table, schema='public', order=2):
         return self._query('''
@@ -223,7 +223,7 @@
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
             WHERE n.nspname = %s AND c.relname = %s AND a.attnum > 0 AND NOT a.attisdropped
-            ORDER BY ''' + str(order), schema, table)
+            ORDER BY ''' + str(order), [schema, table])
 
     def list_constraints(self, table, schema='public'):
         return self._query('''
@@ -236,7 +236,7 @@
             JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             WHERE n.nspname = %s AND c.relname = %s
             ORDER BY 1
-            ''', schema, table)
+            ''', [schema, table])
 
     def list_indexes(self, table, schema='public'):
         return self._query('''
@@ -246,13 +246,27 @@
                 i.indisunique as "unique",
                 i.indisclustered as "clustered",
                 i.indisvalid as "valid",
-                pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as "definition"
+                pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as "definition",
+                ARRAY(SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c2.oid ORDER BY attnum) AS "columns"
                 --c2.reltablespace as "tablespace_oid"
             FROM pg_catalog.pg_class c
             JOIN pg_catalog.pg_index i ON c.oid = i.indrelid
             JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid
             JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
-            WHERE n.nspname = %s AND c.relname = %s
+            WHERE n.nspname = %(schema)s AND c.relname = %(table)s
             ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
-            ''', schema, table)
+            ''', {'schema': schema, 'table': table})
 
+    def list_column_usage(self, table, column, schema='public'):
+        '''List objects using the column.
+        
+        These objects may block alteration of column. Currently only views are listed.
+        
+        '''
+        return self._query('''
+            SELECT
+                'view' AS type, view_schema AS schema, view_name AS name
+            FROM information_schema.view_column_usage
+            WHERE table_schema=%(schema)s AND table_name=%(table)s AND column_name=%(column)s
+            ''', {'schema':schema, 'table':table, 'column':column})
+

--- a/pgtoolkit/pgdatadiff.py	Mon Feb 27 15:12:40 2012 +0100
+++ b/pgtoolkit/pgdatadiff.py	Mon Mar 05 18:36:46 2012 +0100
@@ -37,11 +37,19 @@
         'V' : BOLD | WHITE,
         'K' : BOLD | BLUE}
     
-    def __init__(self, change, cols1, cols2, id=None):
+    def __init__(self, change, cols1, cols2, key=None):
+        '''
+        
+        change - one of '+', '-', '*' (add, remove, update)
+        cols1 - original column values (OrderedDict)
+        cols2 - new column values (OrderedDict)
+        key - primary key columns (OrderedDict)
+        
+        '''
         self.change = change
         self.cols1 = cols1
         self.cols2 = cols2
-        self.id = id
+        self.key = key
     
     def format(self):
         out = []
@@ -70,8 +78,9 @@
             return [', '.join([self._format_value_add(*x) for x in self.cols2.items()])]
         
         out = []        
-        if self.id:
-            out.extend([highlight(1, self.COLORS['*']), self.id[0], ': ', highlight(0), self.id[1], ', '])
+        if self.key:
+            for colname in self.key:
+                out.extend([highlight(1, self.COLORS['*']), colname, ': ', highlight(0), self.key[colname], ', '])
 
         items = []
         for i in range(len(self.cols1)):
@@ -122,9 +131,9 @@
 
     def _format_where(self):
         out = [' WHERE ']
-        out.extend([self.id[0], ' = '])
-        out.append(self.id[1])
-        out.append(';')
+        for colname in self.key:
+            out.extend([colname, ' = ', self.key[colname], ' AND '])
+        out[-1] = ';'
         return out
 
 class PgDataDiff:
@@ -196,14 +205,22 @@
 
     def _select(self):
         browser = pgbrowser.PgBrowser(self.conn1)
+        
         columns = browser.list_columns(schema=self.schema1, table=self.table1, order=1)
         if not columns:
             raise Exception('Table %s.%s not found.' % (self.schema1, self.table1))
         columns_sel = ', '.join(['"' + x['name'] + '"' for x in columns])
         self.colnames = [x['name'] for x in columns]
         
-        query1 = 'SELECT ' + columns_sel + ' FROM ' + self.fulltable1 + ' ORDER BY 1;'
-        query2 = 'SELECT ' + columns_sel + ' FROM ' + self.fulltable2 + ' ORDER BY 1;'
+        pkey = [ind for ind in browser.list_indexes(schema=self.schema1, table=self.table1) if ind['primary']]
+        if not pkey:
+            raise Exception('Table %s.%s has no primary key.' % (self.schema1, self.table1))
+        pkey = pkey[0]
+        pkey_sel = ', '.join(['"' + x + '"' for x in pkey['columns']])
+        self.pkeycolnames = pkey['columns']
+        
+        query1 = 'SELECT ' + columns_sel + ' FROM ' + self.fulltable1 + ' ORDER BY ' + pkey_sel
+        query2 = 'SELECT ' + columns_sel + ' FROM ' + self.fulltable2 + ' ORDER BY ' + pkey_sel
         
         curs1 = self.conn1.cursor()
         curs2 = self.conn2.cursor()
@@ -216,32 +233,31 @@
     def _compare_data(self, row1, row2):
         cols1 = OrderedDict()
         cols2 = OrderedDict()
-        for i in range(len(row1)):
-            if row1[i] != row2[i]:
-                cols1[self.colnames[i]] = row1[i]
-                cols2[self.colnames[i]] = row2[i]
+        for name in row1:
+            if row1[name] != row2[name]:
+                cols1[name] = row1[name]
+                cols2[name] = row2[name]
         if cols1:
-            id = (self.colnames[0], row1[0])
-            return DiffData('*', cols1, cols2, id=id)
+            key = OrderedDict(zip(self.pkeycolnames, [row1[colname] for colname in self.pkeycolnames]))
+            return DiffData('*', cols1, cols2, key=key)
         
         return None
     
     def _compare_row(self, row1, row2):
         if row2 is None:
-            cols1 = OrderedDict(zip(self.colnames, row1))
-            id = (self.colnames[0], row1[0])
-            return DiffData('-', cols1, None, id=id)
+            key = OrderedDict(zip(self.pkeycolnames, [row1[colname] for colname in self.pkeycolnames]))
+            return DiffData('-', row1, None, key=key)
         if row1 is None:
-            cols2 = OrderedDict(zip(self.colnames, row2))
-            return DiffData('+', None, cols2)
+            return DiffData('+', None, row2)
+        
         
-        if row1[0] < row2[0]:
-            cols1 = OrderedDict(zip(self.colnames, row1))
-            id = (self.colnames[0], row1[0])
-            return DiffData('-', cols1, None, id=id)
-        if row1[0] > row2[0]:
-            cols2 = OrderedDict(zip(self.colnames, row2))
-            return DiffData('+', None, cols2)
+        for keyname in self.pkeycolnames:
+            if row1[keyname] < row2[keyname]:
+                key = OrderedDict(zip(self.pkeycolnames, [row1[colname] for colname in self.pkeycolnames]))
+                return DiffData('-', row1, None, key=key)
+        for keyname in self.pkeycolnames:
+            if row1[keyname] > row2[keyname]:
+                return DiffData('+', None, row2)
         
         return self._compare_data(row1, row2)
 

--- a/pgtoolkit/pgmanager.py	Mon Feb 27 15:12:40 2012 +0100
+++ b/pgtoolkit/pgmanager.py	Mon Mar 05 18:36:46 2012 +0100
@@ -2,7 +2,7 @@
 #
 # PgManager - manage database connections
 #
-# Requires: Python 2.6, psycopg2
+# Requires: Python 3.2, psycopg2
 #
 # Part of pgtoolkit
 # http://hg.devl.cz/pgtoolkit
@@ -70,6 +70,7 @@
 """
 
 from contextlib import contextmanager
+from collections import OrderedDict
 import logging
 import threading
 import multiprocessing
@@ -103,10 +104,13 @@
         self.keep_open = keep_open
 
 
-class RowDict(dict):
+class RowDict(OrderedDict):
 
     def __getattr__(self, key):
-        return self[key]
+        try:
+            return self[key]
+        except KeyError:
+            raise AttributeError(key)
 
 
 class Cursor(psycopg2.extensions.cursor):
@@ -132,17 +136,19 @@
         return RowDict(zip([adjustname(desc[0]) for desc in self.description], row))
 
     def fetchone_dict(self, lstrip=None):
+        '''Return one row as OrderedDict'''
         row = super(Cursor, self).fetchone()
         if row is None:
             return None
         return self.row_dict(row, lstrip)
 
     def fetchall_dict(self, lstrip=None):
+        '''Return all rows as OrderedDict'''
         rows = super(Cursor, self).fetchall()
         return [self.row_dict(row, lstrip) for row in rows]
 
-    def fetchone_adapted(self):
-        '''Like fetchone() but values are quoted for direct inclusion in SQL query.
+    def fetchone_adapted(self, lstrip=None):
+        '''Like fetchone_dict() but values are quoted for direct inclusion in SQL query.
         
         This is useful when you need to generate SQL script from data returned
         by the query. Use mogrify() for simple cases.
@@ -151,12 +157,12 @@
         row = super(Cursor, self).fetchone()
         if row is None:
             return None
-        return [self.mogrify('%s', [x]).decode('utf8') for x in row]
+        return self.row_dict([self.mogrify('%s', [x]).decode('utf8') for x in row], lstrip)
 
-    def fetchall_adapted(self):
-        '''Like fetchall() but values are quoted for direct inclusion in SQL query.'''
+    def fetchall_adapted(self, lstrip=None):
+        '''Like fetchall_dict() but values are quoted for direct inclusion in SQL query.'''
         rows = super(Cursor, self).fetchall()
-        return [[self.mogrify('%s', [x]).decode('utf8') for x in row] for row in rows]
+        return [self.row_dict([self.mogrify('%s', [x]).decode('utf8') for x in row], lstrip) for row in rows]
 
 
 class Connection(psycopg2.extensions.connection):

--- a/pgtoolkit/toolbase.py	Mon Feb 27 15:12:40 2012 +0100
+++ b/pgtoolkit/toolbase.py	Mon Mar 05 18:36:46 2012 +0100
@@ -24,11 +24,10 @@
         self.pgm = pgmanager.get_instance()
         self.target_isolation_level = None
         
-    def init(self, *args):
+    def init(self):
         self.config.load('pgtoolkit.conf')
         self.args = self.parser.parse_args()
         self.init_logging()
-        self.prepare_conns(*args)
     
     def init_logging(self):
         # logging
@@ -75,7 +74,7 @@
                     dsn=dsn)
                 return True
 
-    def prepare_conns(self, *pgm_names):
+    def prepare_conns_from_cmdline_args(self, *pgm_names):
         if self.config.meta_db:
             self.pgm.create_conn(name='meta', dsn=self.config.meta_db)
         
@@ -97,7 +96,8 @@
         self.parser.add_argument('target', metavar='target', type=str, help='Target database')
       
     def init(self):
-        ToolBase.init(self, 'target')
+        ToolBase.init(self)
+        self.prepare_conns_from_cmdline_args('target')
 
 
 class SrcDstTool(ToolBase):
@@ -107,5 +107,6 @@
         self.parser.add_argument('dst', metavar='destination', type=str, help='Destination database')
       
     def init(self):
-        ToolBase.init(self, 'src', 'dst')
+        ToolBase.init(self)
+        self.prepare_conns_from_cmdline_args('src', 'dst')