"""
PgSQLConnect.py -- Query a PostgreSQL database

by Jörg Haustein <jchaustein -[AT]- gmx.de>, 2006
Heavy modification of William Waites macros:
SQLConnect, SQLQuery and SQLClose

This program is Free Software and is released
under the terms of the GNU General Public License.
Please see http://www.gnu.org/licenses/gpl for
the full text of the terms and conditions.

In order to use this macro you need
to have pgdb installed. (www.pygresql.org)

You must create a file called 'moin_sql.py'
in your python path (i.e. the root directory of your
wiki). This file should contain definitions for 
your database connections in the following format:

mydb = {'host': 'fill in address:port (i.e. localhost:5432)',
        'database': 'name of the database',
	'user' : 'username',
	'password' : 'password for username'}

The connection parameters all default to None
if left out.

This protects your database access information.
NOTE: If you don't want other users to use your database
connections, protect your page with ACLs!

You can use this macro in your wiki 
pages like this:

[[PgSQLQuery(mydb, SELECT * FROM table)]]

PgSQLQuery  will connect to the database and
output the query results in simple text if it is
one row and one column, otherwise in a table.

NOTE: For security reasons, PgSQLQuery only allows
"SELECT" statements as queries.
"""
from MoinMoin import config
from MoinMoin.parser import wiki
from MoinMoin.parser.wiki import Parser
import re,string, pgdb, cStringIO


_arg_query = r',\s*(?P<query>[Ss][Ee][Ll][Ee][Cc][Tt].+)\s*$'
_arg_pattern =r'^\s*(?P<db_conn>[^,]+)%s' % _arg_query

def execute(macro, args):
    # check arguments
    args_re = re.compile(_arg_pattern)
    if args:
	args=args_re.match(args.encode('utf-8'))
    if not args:
	msg = 'Not enough arguments or query not a SELECT statement. Example: PgSQLQuery (mydb, SELECT * FROM table)'
	return "%s%s%s" % (macro.formatter.sysmsg(1),
			   macro.formatter.text(msg),
			   macro.formatter.sysmsg(0))

    # get the connection string
    try:
        moin_sql = __import__("moin_sql", {}, {}, None)
    except ImportError, message:
	msg = 'Connection definition file not found.'
	return "%s%s%s" % (macro.formatter.sysmsg(1),
			   macro.formatter.text(msg),
			   macro.formatter.sysmsg(0))
    try:
        conn_def = getattr(moin_sql, args.group('db_conn'))
    except AttributeError, message:
	msg = 'Connection name not found in connection definition file.'
	return "%s%s%s" % (macro.formatter.sysmsg(1),
			   macro.formatter.text(msg),
			   macro.formatter.sysmsg(0))

    # connect to database
    try:
        db = pgdb.connect(host=conn_def.get('host'),
			  database=conn_def.get('database'),
			  user=conn_def.get('user'),
			  password=conn_def.get('password'))
	cursor = db.cursor()
    except Exception, message:
	msg = 'Database connection failed: %s.' % message
	return "%s%s%s" % (macro.formatter.sysmsg(1),
			   macro.formatter.text(msg),
			   macro.formatter.sysmsg(0))
    
    # set client encoding to unicode, so we can always assume returned values to be utf-8
    # this will only fail if the database encoding is 'MULE_INTERNAL'
    try:
        cursor. execute("SET client_encoding TO 'UNICODE'")
    except Exception, message:
	msg = 'Set encoding to UNICODE failed: %s' % message
	return "%s%s%s" % (macro.formatter.sysmsg(1),
			   macro.formatter.text(msg),
			   macro.formatter.sysmsg(0))
    
    # execute query
    try:
	cursor.execute(args.group('query'))
        rows = cursor.fetchall()
    except Exception, message:
	msg = 'Query failed: %s' % message
	return "%s%s%s" % (macro.formatter.sysmsg(1),
			   macro.formatter.text(msg),
			   macro.formatter.sysmsg(0))

    # simply leave quietly if there was no return
    if cursor.rowcount==0:
        cursor.close()
	db.close()
	return ""

    # just output result if only one value was queried
    if len(rows)==1 and len(rows[0])==1:
        cursor.close()
	db.close()
	text=rows[0][0] or ''
	return macro.request.redirectedOutput(Parser(text.decode('utf-8'),macro.request).format,macro.formatter)

    # output a whole table otherwise
    table=[]
    table.append(macro.formatter.table(1))
    table.append(macro.formatter.table_row(1))

    for header in cursor.description:
        table.append(macro.formatter.table_cell(1))
        table.append(macro.formatter.strong(1))
	text=header[0] or ''
        text = macro.request.redirectedOutput(Parser(text.decode('utf-8'),macro.request).format,macro.formatter)
        table.append(text)
        table.append(macro.formatter.strong(0))
        table.append(macro.formatter.table_cell(0))
        
    table.append(macro.formatter.table_row(0))
    for row in rows:
        table.append(macro.formatter.table_row(1))
	for col in row:
            table.append(macro.formatter.table_cell(1))
            if col:
                text = macro.request.redirectedOutput(Parser(col.decode('utf-8'),macro.request).format,macro.formatter)
                table.append(text)
            table.append(macro.formatter.table_cell(0))
        table.append(macro.formatter.table_row(0))

    table.append(macro.formatter.table(0))
    cursor.close()
    db.close()
    return ''.join(table)