#!/usr/bin/env python

import sys, MySQLdb

def query(q):
    c = db.cursor()
    c.execute(q)
    return c.fetchall()

if len(sys.argv) < 2:
    db = MySQLdb.Connection()
    message = 'Usage: list <database-name>\n\nAvailable databases:\n'
    for (name,) in query('show databases'):
        try:
            count = query('select count(*) from %s.bioentry' % name)[0][0]
        except: continue
        message += '    %s (%d %s)\n' % (
            name, count, count == 1 and 'entry' or 'entries')
    raise SystemExit(message)

db = MySQLdb.Connection(db=sys.argv[1])

for (id, name, auth, desc) in query('select * from biodatabase'):
    print '%d. %s:' % (id, name)
    for row in query("select * from bioentry where biodatabase_id='%s'" % id):
        count = {}
        for name, table in [('dbxref', 'bioentry_dbxref'),
                            ('reference', 'bioentry_reference'),
                            ('sequence', 'biosequence'),
                            ('comment', 'comment'),
                            ('feature', 'seqfeature')]:
            count[name] = query('select count(*) from ' + table + 
                                " where bioentry_id='%s'" % row[0])[0][0]
        print '    %d. %s: %s' % (row[0], row[3],
            ', '.join(['%d %s%s' % (c, n, c != 1 and 's' or '')
                       for n, c in count.items() if c]))

print
for (table,) in query('show tables'):
    count = query('select count(*) from ' + table)[0][0]
    print '%s: %d row%s' % (table, count, count != 1 and 's' or '')
print
