"""
MoinMoin macro to display query results from an SQL database
SQL.py

by Tim Cera

Based on macro/MySQLOutput.py by
sergio rodrigues giraldo sergiorgiraldo at gmail dot com

    Install:
      Oracle prerequesites:
        * Oracle client libraries (http://www.oracle.com)
        * set '_oracle_home' variable below to ORACLE_HOME
        * cx_Oracle - http://www.cxtools.net/default.aspx?nav=cxorlb
          you have to compile it yourself, or to use the pre-compiled
          binaries you have to have control over the environment 
          variables within the web server environment.  I don't 
          have control over the server, but I think all you need to
          do is set LD_LIBRARY_PATH to $ORACLE_HOME/lib.
          When compiling cx_Oracle:
          1) set the environment variable FORCE_RPATH to anything
          2) python setup.py install
             cx_Oracle needs to find a couple of libraries and the
             only way it works is for cx_Oracle to be compiled with
             the run time paths to the libraries it needs.  The
             compilation flags to include the run time paths are 
             triggered by the FORCE_RPATH environment variable.
      MySQL:
        I don't know what the requirements are here, but I think
        everything is unchanged from MySQLOutput.py

    Usage:
        [[SQL(host|user|password|database|type|query)]]

         Note that this macro expects parameters to be separated by '|' 
         This is so the query can contain commas.

    Example : 
              [[SQL(localhost|root|root|mysql|mysql|
                SELECT NAME, EXAMPLE FROM help_topic LIMIT 5)]]

    Demo : Try pasting the above line into a MoinMoin sandbox page.
"""

# Add new databases here and in the if/elif statement later on.
_database_types = ['mysql', 'oracle']

# Need to set this to the ORACLE_HOME if using Oracle
_oracle_home = '/beodata/software/relink_package_manager/vapp/oracle_home'



def usage(num_args, text, err_text):
    return """
<pre>
SQL macro is called with the format:
        [[SQL(hostname|user|password|database_name|database_type|
              SELECT NAME, EXAMPLE FROM help_topic LIMIT 5)]]

Note the use of the '|' symbol to separate arguments.  This allows commas
to be used in the 'SELECT' SQL statement.

You gave the following %i arguments:
 %s

This macro supports the following database_type(s):
 %s

 Error: %s
</pre>
""" % (num_args, text, ' '.join(_database_types), err_text)



def execute(macro, text):

    # Argument error checking.
    if text is None: # macro call without parameters
        return usage(0, ' ', 'no parameters')
    else:
        text_list = text.split('|')
        try:
            pHost, pUser, pPassword, pDatabase, pDatabaseType, pQuery = text_list
        except ValueError:
            return usage(len(text_list), text, 'incorrect number of parameters ')
    if not pQuery.lower().startswith('select'):
        return usage(len(text_list), text, 'macro will only process "select" queries')

    # Get the data.
    if pDatabaseType == 'mysql':
        import MySQLdb
        db = MySQLdb.connect (host = pHost,user = pUser,passwd = pPassword,db = pDatabase)
    elif pDatabaseType == 'oracle':
        import os
        os.environ['ORACLE_HOME'] = _oracle_home
        import cx_Oracle
        db = eval('cx_Oracle.connect("%s", "%s", cx_Oracle.makedsn("%s", 1521, "%s"))' % (pUser, pPassword, pHost, pDatabase))
    else:
        return usage(len(text_list), text, 'database_type is incorrect')
    c = db.cursor()
    c.execute(str(pQuery))

    # Format
    formatter = macro.request.formatter
    result = formatter.table(True)
    for l in c.fetchall():
        result += formatter.table_row(True)
        for i in range(0, len(l)):
            result += formatter.table_cell(True)
            result += unicode(str(l[i]), 'ISO-8859-1') 
            result += formatter.table_cell(False)
        result += formatter.table_row(False)
    result += formatter.table(False)

    # Cleanup
    db.close()

    # Display
    return result