"""
    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 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_string = ''
        for (k, v) in parameters.iteritems():
            connection_string += '%s=%s;' % (k, v)
        connection = pyodbc.connect(connection_string)

    elif connection_type == 'mysql':
        import MySQLdb
        if 'port' in parameters:
            port = parameters['port']
        else:
            port = 3306
        connection = MySQLdb.connect(host = parameters['server'], port = port, user = parameters['uid'], passwd = parameters['pwd'],db = parameters['database'])

    elif connection_type == 'oracle':
        import cx_Oracle
        if 'port' in parameters:
            port = parameters['port']
        else:
            port = 1521
        connection = cx_Oracle.connect(parameters['uid'], parameters['pwd'], cx_Oracle.makedsn(parameters['server'], port, parameters['database']))

    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.append(formatter.table_row(True))
        for i in range(0, len(fields)):
            result.append(formatter.table_cell(True))
            result.append(formatter.strong(True))
            result.append(unicode(str(fields[i][0]), 'ISO-8859-1'))
            result.append(formatter.strong(False))
            result.append(formatter.table_cell(False))
        result.append(formatter.table_row(False))

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

    connection.close()
    return u'\n'.join(result)


def error(macro, message):
    formatter = macro.request.formatter
    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'

    result = [ '', formatter.preformatted(True) ]
    result.append("""
DataBase macro error:
    %s

Usage:
    << DataBase(source, headers) >> or
    << DataBase(source, header, "query") >>
    source   %s
    header   specifies if field names should be displayed as table header
    "query"  SQL select query (only if source allows/requires queries)
""" % (message, source_comment))
    result.append(formatter.preformatted(False))
    return u'\n'.join(result)