#             Perforce Defect Tracking Integration Project
#              <http://www.ravenbrook.com/project/p4dti/>
#
#  MYSQL_TO_HTML.PY -- CONVERT MYSQL TABLE DESCRIPTIONS TO HTML TABLES
#
#             Nick Barnes, Ravenbrook Limited, 2001-03-08
#
#
# 1. INTRODUCTION
#
# This module converts MySQL table descriptions into HTML tables, for
# use in design documents.
#
# The intended readership is project developers.
#
# This document is not confidential.


error = 'table suck error'

import MySQLdb
db = MySQLdb.connect(db='bugs', user='bugs')
cursor = db.cursor()

def fetchall():
    rows = cursor.fetchall()
    # for some reason, if no rows are returned sometimes one gets () here.
    if len(rows) == 0:
        rows = []
    return rows

def select_rows(select):
    rows = cursor.execute(select)
    if cursor.description == None :
        raise error, ("Trying to fetch rows from non-select '%s'"
                      % select)
    values = fetchall()
    if values == None :
        raise error, ("Select '%s' returned unfetchable rows."
                      % select)
    return values

def column_names():
    keys = []
    for i in range(len(cursor.description)):
        keys.append(cursor.description[i][0])
    return keys

def fetch_rows_as_list_of_dictionaries(select):
    results = []
    values = select_rows(select)
    keys = column_names()
    for value in values:
        result={}
        if len(keys) != len(value) :
            raise error, ("Select '%s' returns %d keys but %d columns."
                          % (select, len(keys), len(value)))
        for j in range(len(keys)):
            result[keys[j]] = value[j]
        results.append(result)
    return results

tables = map(lambda x:x[0],select_rows('show tables'))

def output_description(description):
    print '<table border="1" cellspacing="0" cellpadding="5">'

    print '  <tr valign="top" align="left">'
    print '    <th>Field</th>'
    print '    <th>Type</th>'
    print '    <th>Default</th>'
    print '    <th>Properties</th>'
    print '    <th>Remarks</th>'
    print '  </tr>'
    keys=['Field', 'Type', 'Default', 'Extra']
    for dict in description:
        if dict['Null'] == 'YES':
            if dict['Extra'] == '':
                dict['Extra'] = 'null'
            else:
                dict['Extra'] = dict['Extra']+', null'
        print '  <tr valign="top" align="left">'
        for key in keys:
            if dict[key] == '':
                dict[key] = '&nbsp;'
            print ('    <td>%s</td>' % dict[key])
        print '  <td>&nbsp;</td>'
        print '  </tr>'
    print '</table>'

for table in tables:
    print ('<h2>The "%s" table</h2>\n' % table)
    description = fetch_rows_as_list_of_dictionaries('describe %s' % table)
    output_description(description)
    print "\n\n"


# A. REFERENCES
#
#
# B. DOCUMENT HISTORY
#
# 2001-03-08 NB Created.
#
#
# C. COPYRIGHT AND LICENCE
#
# This file is copyright (c) 2001 Perforce Software, Inc.  All rights
# reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are
# met:
#
# 1.  Redistributions of source code must retain the above copyright
#     notice, this list of conditions and the following disclaimer.
#
# 2.  Redistributions in binary form must reproduce the above copyright
#     notice, this list of conditions and the following disclaimer in
#     the documentation and/or other materials provided with the
#     distribution.
#
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
# "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
# LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
# A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
# HOLDERS AND CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
# INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
# BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
# OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
# TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
# USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
# DAMAGE.
#
#
# $Id: //info.ravenbrook.com/project/p4dti/version/1.4/tool/mysql_to_html.py#1 $