--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/.hgignore	Mon Jul 18 17:39:37 2011 +0200
@@ -0,0 +1,2 @@
+.*\.pyc
+pgtoolkit\.conf

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/bigtables.py	Mon Jul 18 17:39:37 2011 +0200
@@ -0,0 +1,23 @@
+#!/usr/bin/env python3.2
+
+from pgtools import pgbrowser
+from common import prettysize
+from toolbase import ToolBase
+
+
+class BigTablesTool(ToolBase):
+    def __init__(self):
+        ToolBase.__init__(self, name='bigtables', desc='List largest tables.')
+        self.init()
+
+    def main(self):
+        browser = pgbrowser.PgBrowser(self.pgm.get_conn('target'))
+
+        tables = browser.list_tables()
+        for table in sorted(tables, reverse=True, key=lambda x: x['size'])[:20]:
+            print(prettysize.prettysize_short(table['size']).rjust(8), table['name'], sep='  ')
+
+
+tool = BigTablesTool()
+tool.main()
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/common/config.py	Mon Jul 18 17:39:37 2011 +0200
@@ -0,0 +1,44 @@
+import logging
+
+
+class ConfigParser:
+    def __init__(self):
+        self.args = {}       # config file arguments
+        self.registered_args = {}
+        self.log = logging.getLogger('config') 
+
+    def add_argument(self, name, type=str, default=None):
+        self.registered_args[name] = {'type':type, 'default':default}
+
+    def load(self, fname):
+        # parse config file
+        with open(fname) as f:
+            exec(f.read(), self.args)
+        # check contents
+        return self.check()
+
+    def check(self):
+        ok = True
+        for key in self.args.keys():
+            if key == '__builtins__':
+                continue
+            if key in self.registered_args:
+                # arg registered, check type
+                type = self.registered_args[key]['type']
+                if not isinstance(self.args[key], type):
+                    ok = False
+                    self.log.error("Bad type of config parameter '%s': is %s but should be %s",
+                        key, type(self.args[key]), type)
+            else:
+                # arg not registered
+                    ok = False
+                    self.log.error("Unknown config parameter '%s'.", key)
+        return ok
+
+    def __getattr__(self, name):
+        if name in self.args:
+            return self.args[name]
+        
+        # raise error if not found
+        raise AttributeError()
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/common/prettysize.py	Mon Jul 18 17:39:37 2011 +0200
@@ -0,0 +1,67 @@
+# -*- coding: utf-8 -*-
+#
+# prettysize - print data sizes in format easily readable by humans
+#
+# Copyright (c) 2011  Radek Brich <radek.brich@devl.cz>
+#
+# Permission is hereby granted, free of charge, to any person obtaining a copy
+# of this software and associated documentation files (the "Software"), to deal
+# in the Software without restriction, including without limitation the rights
+# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
+# copies of the Software, and to permit persons to whom the Software is
+# furnished to do so, subject to the following conditions:
+#
+# The above copyright notice and this permission notice shall be included in
+# all copies or substantial portions of the Software.
+#
+# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
+# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
+# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
+# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
+# THE SOFTWARE.
+
+iec_prefixes = ('Ki', 'Mi', 'Gi', 'Ti', 'Pi', 'Ei', 'Zi', 'Yi')
+si_prefixes = 'kMGTPEZY'
+big_prefixes = 'KMGTPEZY'
+
+
+def prettysize(num, base=1024, prefixes=si_prefixes, unit='B', sep=' '):
+    '''Convert data size in bytes to human readable aproximation.
+
+    Beware that default output is invalid by recent standardization.
+    Still, this form was in use for ages.
+
+    For standardized output, see prettysize_si and prettysize_iec.
+
+    '''
+    prefixes = list(prefixes)
+    prefix = ''
+    num = float(num)
+    while True:
+        if num < base:
+            # format number
+            out = '{:.2f}'.format(num)
+            # cut off trailing zeros
+            out = out.rstrip('0')
+            if out.endswith('.'):
+                out = out[:-1]
+            return out + sep + prefix + unit
+        num /= base
+        prefix = prefixes.pop(0)
+
+    raise OverflowError('Number is too large.')
+
+
+def prettysize_si(num, **kw):
+    return prettysize(num, base=1000, **kw)
+
+
+def prettysize_short(num):
+    return prettysize(num, unit='', sep='', prefixes=big_prefixes)
+
+
+def prettysize_iec(num):
+    return prettysize(num, prefixes=iec_prefixes)
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/diff.py	Mon Jul 18 17:39:37 2011 +0200
@@ -0,0 +1,50 @@
+#!/usr/bin/env python3.2
+
+import sys
+import argparse
+
+from pgtools import pgmanager, pgbrowser, pgdiff
+from common import config
+
+parser = argparse.ArgumentParser(description='Database schema diff.')
+parser.add_argument('source', metavar='source', type=str, help='Source database')
+parser.add_argument('destination', metavar='destination', type=str, help='Destination database')
+parser.add_argument('-s', dest='schema', nargs='*', help='Schema filter')
+parser.add_argument('-t', dest='table', nargs='*', help='Table filter')
+args = parser.parse_args()
+
+config = config.ConfigParser()
+config.add_argument('databases', type=dict)
+config.add_argument('meta_db')
+config.add_argument('meta_query')
+config.load('pgtoolkit.conf')
+
+pgm = pgmanager.get_instance()
+
+pgm.create_conn(name='meta', dns=config.meta_db)
+
+def build_conn(name, targetname):
+    with pgm.cursor('meta') as curs:
+        curs.execute(config.meta_query, [targetname])
+        row = curs.fetchone_dict()
+        curs.connection.commit()
+        
+        pgm.create_conn(name=name, **row)
+
+build_conn('src', args.source)
+build_conn('dst', args.destination)
+
+pgm.close_conn('meta')
+
+srcbrowser = pgbrowser.PgBrowser(pgm.get_conn('src'))
+dstbrowser = pgbrowser.PgBrowser(pgm.get_conn('dst'))
+
+pgd = pgdiff.PgDiff(srcbrowser, dstbrowser)
+
+if args.schema:
+    pgd.filter_schemas(include=args.schema)
+
+if args.table:
+    pgd.filter_tables(include=args.table)
+
+pgd.print_diff()

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/longqueries.py	Mon Jul 18 17:39:37 2011 +0200
@@ -0,0 +1,22 @@
+#!/usr/bin/env python3.2
+
+from pgtools import pgstats
+from toolbase import ToolBase
+
+
+class LongQueriesTool(ToolBase):
+    def __init__(self):
+        ToolBase.__init__(self, name='longqueries', desc='Lis long queries.')
+        self.init()
+
+    def main(self):
+        stats = pgstats.PgStats(self.pgm.get_conn('target'))
+
+        for ln in stats.list_long_queries():
+            print(ln['query'])
+            print('   ', 'query_start:', ln['query_start'].strftime('%F %T'))
+
+
+tool = LongQueriesTool()
+tool.main()
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pgtoolkit.conf.example	Mon Jul 18 17:39:37 2011 +0200
@@ -0,0 +1,10 @@
+### named connections
+databases = {
+  'test' : 'host=127.0.0.1 user=test password=test dbname=test'
+}
+
+### meta database
+# connection string (dsn) to meta db
+meta_db = 'host=10.8.0.1 dbname=central'
+# query in meta db, input is database name (will be placed instead of %s), output is values for create_conn
+meta_query = '''SELECT host, port, dbname, user, password FROM config.databases WHERE name = %s LIMIT 1'''

--- a/pgtools/pgmanager.py	Thu May 26 18:09:05 2011 +0200
+++ b/pgtools/pgmanager.py	Mon Jul 18 17:39:37 2011 +0200
@@ -151,7 +151,7 @@
     def create_conn(self, name='default', isolation_level=None, dsn=None, **kw):
         '''Create named connection.'''
         if name in self.conn_known:
-            raise PgManagerError('Connection name "%s" already registered.', name)
+            raise PgManagerError('Connection name "%s" already registered.' % name)
 
         if dsn is None:
             dsn = ' '.join([x[0]+'='+str(x[1]) for x in kw.items()])
@@ -179,7 +179,7 @@
         
         '''
         if not name in self.conn_known:
-            raise PgManagerError('Connection name "%s" not registered.', name)
+            raise PgManagerError('Connection name "%s" not registered.' % name)
 
         self.close_conn(name)
 
@@ -191,7 +191,7 @@
         self.lock.acquire()
         try:
             if not name in self.conn_known:
-                raise PgManagerError("Connection name '%s' not registered.", name)
+                raise PgManagerError("Connection name '%s' not registered." % name)
 
             conn = None
             while len(self.conn_pool[name]) and conn is None:
@@ -222,7 +222,7 @@
         self.lock.acquire()
         try:
             if not name in self.conn_known:
-                raise PgManagerError("Connection name '%s' not registered.", name)
+                raise PgManagerError("Connection name '%s' not registered." % name)
 
             if len(self.conn_pool[name]) >= self.conn_known[name].keep_open:
                 conn.close()

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pgtools/pgstats.py	Mon Jul 18 17:39:37 2011 +0200
@@ -0,0 +1,49 @@
+# -*- coding: utf-8 -*-
+#
+# PgStats - browse database statistics
+#
+# Copyright (c) 2011  Radek Brich <radek.brich@devl.cz>
+#
+# Permission is hereby granted, free of charge, to any person obtaining a copy
+# of this software and associated documentation files (the "Software"), to deal
+# in the Software without restriction, including without limitation the rights
+# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
+# copies of the Software, and to permit persons to whom the Software is
+# furnished to do so, subject to the following conditions:
+#
+# The above copyright notice and this permission notice shall be included in
+# all copies or substantial portions of the Software.
+#
+# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
+# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
+# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
+# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
+# THE SOFTWARE.
+
+
+class PgStats:
+    def __init__(self, conn=None):
+        self.conn = conn
+        
+    def setconn(self, conn=None):
+        self.conn = conn
+
+    def _query(self, query, *args):
+        try:
+            curs = self.conn.cursor()
+            curs.execute(query, args)
+            curs.connection.commit()
+            rows = curs.fetchall()
+            return [dict(zip([desc[0] for desc in curs.description], row)) for row in rows]
+        finally:
+            curs.close()
+
+    def list_long_queries(self, longer_than='1m'):
+        return self._query('''SELECT datname, procpid, usename, current_query AS query,
+            waiting, xact_start, query_start, backend_start
+            FROM pg_stat_activity
+            WHERE current_query <> '<IDLE>' AND query_start < now() - interval %s;''',
+            longer_than)
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/tests/testprettysize.py	Mon Jul 18 17:39:37 2011 +0200
@@ -0,0 +1,26 @@
+#!/usr/bin/env python3
+
+import unittest
+import prettysize
+
+class TestHumansize(unittest.TestCase):
+    def test_humansize(self):
+        psize = prettysize.prettysize_short
+        expected = (
+            (0, '0'),
+            (1000, '1000'),
+            (1024, '1K'),
+            (1050, '1.03K'),
+            (2050, '2K'),
+            (333333, '325.52K'),
+            (1048576, '1M'),
+            (1050000, '1M'),
+            (5555555, '5.3M'),
+            (1073741824, '1G'),
+            )
+        for input, result in expected:
+            self.assertEqual(psize(input), result)
+
+
+if __name__ == '__main__':
+    unittest.main()

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/toolbase.py	Mon Jul 18 17:39:37 2011 +0200
@@ -0,0 +1,38 @@
+import argparse
+
+from pgtools import pgmanager
+from common import config
+
+
+class ToolBase:
+    def __init__(self, name, desc):
+        self.parser = argparse.ArgumentParser(description=desc)
+        self.parser.add_argument('target', metavar='target', type=str, help='Target database')
+        
+        self.config = config.ConfigParser()
+        self.config.add_argument('databases', type=dict)
+        self.config.add_argument('meta_db')
+        self.config.add_argument('meta_query')
+        
+        self.pgm = pgmanager.get_instance()
+        
+    def init(self):
+        self.config.load('pgtoolkit.conf')
+        self.args = self.parser.parse_args()
+        
+        if self.config.meta_db:
+            self.pgm.create_conn(name='meta', dsn=self.config.meta_db)
+            self.buildconn('target', self.args.target)
+            self.pgm.close_conn('meta')
+
+    def buildconn(self, name, targetname):
+        with self.pgm.cursor('meta') as curs:
+            curs.execute(self.config.meta_query, [targetname])
+            row = curs.fetchone_dict()
+            curs.connection.commit()
+            
+            if not row:
+                raise Exception('Unknown database "%s"' % targetname)
+            
+            self.pgm.create_conn(name=name, **row)
+