"""
    DataBase.py - MoinMoin macro to display query results from databases
    
    @copyright: 2007 Wolfgang Fischer
    @license: GNU GPL, see COPYING for details.

    based on SQL.py by Tim Cera

    Information on prerequisites, installation an usage can be found at
        http://moinmo.in/MacroMarket/DataBase

"""
import re

Dependencies = ['External Data']  # TODO: what do comparable macros use?


def error(macro, message):
    try:
        display_keys = macro.request.cfg.database_macro_display_keys
    except AttributeError:
        display_keys = False
    if display_keys:
        source_comment = 'one of the following data sources: %s' % data_sources.keys()
    else:
        source_comment = 'data source key'
    return """
<pre>
DataBase macro error:
    %s

Usage:
    << DataBase(source, header, "query") >> or
    << DataBase(source, headers) >>
    source   %s
    header   specifies if field names should be displayed as table header
    "query"  SQL select query (only if defined source allows/requires queries)
</pre>
""" % (message, source_comment)


def execute(macro, args):
    param_re = re.compile(r'^(?P<source>.+?),(?P<header>[^,"]*?)($|,\s*"(?P<query>.*)"\s*$)')
    match_object = re.match(param_re, args)
    if match_object:
        source = match_object.group('source')
        header = match_object.group('header')
        query = match_object.group('query')
    else:
        return error(macro, 'Parameter syntax error.')
    source = source.strip().lower()
    try:
        data_sources = macro.request.cfg.database_macro_sources
    except AttributeError:
        return error(macro, 'No data sources specified')
    if source not in data_sources:
        return error(macro, 'The data source "%s" specified is unknown' % source)
    if query and not query.lower().startswith('select'):
        return error(macro, 'Only "select" queries processed')

    # Determine the query
    if len(data_sources[source]) == 3:
        if query:
            return error(macro, 'The data source "%s" doesn\'t allow queries' % source)
        query = data_sources[source][2]
    if not query:
        return error(macro, 'Data source "%s" requires a query' % source)

    # Connect to data source
    connection_type = data_sources[source][0].lower()
    parameters = data_sources[source][1]

    if connection_type == 'odbc':
        import pyodbc
        connection = pyodbc.connect(parameters)
    elif connection_type == 'mysql':
        import MySQLdb
        connection = MySQLdb.connect(host = parameters[0], user = parameters[1], passwd = parameters[2],db = parameters[3])
    elif connection_type == 'oracle':
        try:
            oracle_home = macro.request.cfg.database_macro_oracle_home
        except AttributeError:
            return error(macro, 'No oracle home directory specified in wikiconfig.py. Please contact you system administrator.')
        import os
        os.environ['ORACLE_HOME'] = _oracle_home
        import cx_Oracle
        connection = eval(parameters)
    else:
        return error(macro, 'Connection type of data source "%s" is incorrect. Please contact you system administrator.' % source)

    cursor = connection.cursor()
    cursor.execute(query)

    formatter = macro.request.formatter
    result = formatter.table(True)

    # Display header
    if header:
        fields = cursor.description
        result += formatter.table_row(True)
        for i in range(0, len(fields)):
            result += formatter.table_cell(True)
            result += formatter.strong(True)
            result += unicode(str(fields[i][0]), 'ISO-8859-1')
            result += formatter.strong(False)
            result += formatter.table_cell(False)
        result += formatter.table_row(False)

    # Display rows
    for row in cursor:
        result += formatter.table_row(True)
        for i in range(0, len(row)):
            result += formatter.table_cell(True)
            result += unicode(str(row[i]), 'ISO-8859-1') 
            result += formatter.table_cell(False)
        result += formatter.table_row(False)
    result += formatter.table(False)

    connection.close()
    return result