# -*- coding: utf-8 -*-
#Author: Igor Támara igor@tamarapatino.org
#Date: 10/03/2006
#No warranties.
"""
     MoinMoin - QueryPg macro

   PURPOSE : Show the result of some queries on a postgresql db.

   REQUISITES : Install the psycopg2 module.

   INPUTS : receives as a single argument the query.

   OUTPUT : If the query returns one column with one row, just outputs
   the result, if it returns many columns or many rows, it prints a table
   the headers of the tables will show spaces replacing underscores.

   EXAMPLES : "<<QueryPg(SELECT name FROM countries)>>" would fetch every
   name of the table country ;) and if there are results it will present
   the results in a table.
   "There are <<QueryPg(SELECT count(*) from countries)>> query records" will
   inline the result on your html code

   IMPROVEMENTS : If you plan to use multiple wikis or multiple databases,
   you could create a separate file with dictionaries of identifiers and
   string connections, as a first input you would receive the key of the
   dictionary that will let you connect to the desired database,
   the second argument would be the query.

   RECOMMENDATIONS :   Create a reading user on your database, people
   looking with ?action=raw could deduce your database schema, if you are not
   concerned about security don't bother.   Remember that anyone with write
   access to your wiki could do a nasty sql query to slow down you or D.O.S.
   attack your system.

   If you find any bugs, please let me know.
"""

Dependencies = ['psycopg2']
from psycopg2 import *

#Change this variable to suit your needs
connvars = 'dbname=flisol host=localhost user=igor password=superflisol'


def showquery(query):
    """Pre: Gets a query
    Post: Shows a table on that query replacing on titles "_" by " " """
    conn = connect(connvars)
    conn.set_client_encoding('UTF-8')
    c = conn.cursor()
    try:
        c.execute(query)
    except:
        raise
        return """<p>Query contains errors:
        <pre>
        {0} 
        </pre>
        """.format(query)
    rows = c.fetchall()
    if len(rows) == 0:
        return u"<p>No results on the query"
    if len(c.description) == 1 and len(rows) == 1:
        return str(rows[0][0])

    res = u"""
    <table>
    <tr>
    """
    for title in c.description:
        res += u"<th>{0}</th>".format(title[0].replace(u"_", u" "))
    res += u"""</tr>
    """

    for reg in rows:
        res += u"""
        <tr>"""
        for row in reg:
            res += u"<td>{0}</td>".format(row.decode('utf-8'))
        res += u"</tr>"
    res += u"""
    </table>"""
    conn.close()
    return res


def execute(macro, args):
    if len(args) > 10:
        res = showquery(args)
    else:
        res = u''
    return macro.formatter.rawHTML(res)