# -*- coding: iso-8859-1 -*-
#Author: Igor Támara igor@tamarapatino.org
#Date: 10/03/2006
#No warranties.
"""
     MoinMoin - QueryDb macro

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

   REQUISITES : Install the psycopg 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 : "[[QueryDb(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 [[QueryDb(SELECT count(*) from queries)]] 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, 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 = ["pages","psycopg"]
from psycopg import *

#Change this variable to suit your needs
conexion="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(conexion,serialize=0)
    c=conn.cursor()
    try :
        c.execute(query.encode("iso8859-1"))
    except :
        raise
        return """<p>Query contains errors:
        <pre>
        %s
        </pre>
        """ % query
    rows=c.fetchall()
    if len(rows)==0 :
        return "<p>No results on the query"
    if len(c.description)==1 and len(rows)==1 :
        return str(rows[0][0])
        
    a="""
    <table>
    <tr>
    """
    for i in c.description :
        a+="<th>%s</th>" % i[0].replace("_"," ")
    a+="""</tr>
    """
            
    for reg in rows :
        a+="""
        <tr>"""
        for j in reg :
            a+="<td>%s</td>" % j
        a+="</tr>"
    a+="""
    </table>"""
    conn.close()
    return a
    
def execute(macro, args):
    if len(args)>10 :
        res=showquery(args)
    else :
        res=""
    return macro.formatter.rawHTML(res)