# HG changeset patch # User Radek Brich # Date 1330969006 -3600 # Node ID c2e6e24b83d93526f229f14a63b15fc19839ee95 # Parent a8b7cd92f39f7d9507615592d663df554d223079 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. diff -r a8b7cd92f39f -r c2e6e24b83d9 browser.py --- /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() + diff -r a8b7cd92f39f -r c2e6e24b83d9 gtkbrowser.py diff -r a8b7cd92f39f -r c2e6e24b83d9 listdepends.py --- /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() + diff -r a8b7cd92f39f -r c2e6e24b83d9 pgtoolkit/pgbrowser.py --- 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}) + diff -r a8b7cd92f39f -r c2e6e24b83d9 pgtoolkit/pgdatadiff.py --- 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) diff -r a8b7cd92f39f -r c2e6e24b83d9 pgtoolkit/pgmanager.py --- 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): diff -r a8b7cd92f39f -r c2e6e24b83d9 pgtoolkit/toolbase.py --- 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')