#!/usr/bin/python
#
# Copyright 2004 Manuel Amago <mamago@acm.org>.
# All Rights Reserved.
#
"""Usage: prettySql.py [OPTION]... [SQL]...
Return input SQL in a (hopefully!) pretty indented format.

Example: prettySql.py "select count(*) from table a, another b \\
   where a.name = b.name"

OPTIONS
 -h, --help  print this help message
     --test  output some test SQL strings
 -f <file>   prettify SQL in <file>

ARGUMENTS
  SQL strings passed on end of command will be processed.
"""

import re, cgi


def prettySql(sql, upper=False):
    tokens = tokenise(sql)
    aloner = lambda x: '^%s$'%(x)
    newLinePattern = re.compile(
        "|".join(map(aloner, ('select',
                  'from',
                  'where',
                  'union',
                  'intersect',
                  'order',
                  'group',
                  'having',
                  'connect'))), re.I)
    indentPattern = re.compile(
        "|".join(map(aloner, ('[(]',
                  'case'))), re.I)
    outdentPattern = re.compile(
        "|".join(map(aloner, ('[)]',
                  'end'))), re.I)
    breakPattern = re.compile(
        "|".join(map(aloner, ('and',
                  'or',
                  'when',
                  'then',
                  'else',
                  'end',
                  'start'))), re.I)
    breakAfterPattern = re.compile(',', re.I)
    keywordPattern = re.compile(
        "|".join(map(aloner, ('select',
                  'from',
                  'where',
                  'union',
                  'intersect',
                  'order',
                  'by',
                  'group',
                  'and',
                  'or',
                  'as',
                  'in',
                  'any',
                  'distinct',
                  'exists',
                  'case',
                  'when',
                  'then',
                  'end',
                  'having',
                  'all',
                  'like',
                  'connect',
                  'prior',
                  'start',
                  'with'))), re.I)
    stringPattern = re.compile(
        "'.*?'", re.I)
    noWhiteSpaceBefore = re.compile(',', re.I)

    SPACER = '  '
    indent = 0
    indentStack = []
    prettySql = ''
    previousToken = ''
    for t in tokens:
        if t.strip() == '': continue
        newline = ''
        if newLinePattern.match(t):
            if indent > 0 and not indentPattern.match(previousToken):
                indent = indent - 1
            newline += '\n' + (SPACER * indent)
        if indentPattern.match(t):
            indentStack.append(indent)
            indent = indent + 1
        if outdentPattern.match(t):
            try:
                indent = indentStack.pop()
            except IndexError:
                prettySql += '<<ERROR>>'
        if breakPattern.match(t):
            newline += '\n' + (SPACER * indent)

        if keywordPattern.match(t) and upper: t = t.upper()
        if stringPattern.match(previousToken) \
           and stringPattern.match(t): prettySql = prettySql[:-1]
        if noWhiteSpaceBefore.match(t):
            if prettySql[-1].isspace(): prettySql = prettySql[:-1]
        prettySql += newline + t + ' '

        if newLinePattern.match(t):
            indent = indent + 1
        if breakAfterPattern.match(t):
            prettySql += '\n' + (SPACER * indent)

        if len(t.strip()) > 0: previousToken = t
            
    return prettySql


def tokenise(sql):
    p = re.compile(r"(\(\+\)|[\s()=,]|<>|<=|>=|\|\||!=|'.*?')")
    tokens = p.split(sql)
    return tokens


def stripCode(code):
    p = re.compile(r'[^"]*"(.*)"[^"]*')
    stripped = p.sub(r'\1\n', code) + '\n'
    return stripped


def display_form(sql, upper=True, strip=False):
    pretty = '&nbsp;'
    if (sql != ''):
        if strip:
            sql = stripCode(sql)
        
        pretty = prettySql(sql, upper)
        sql = sql.replace('&', '&amp;')
        sql = sql.replace('<', '&lt;')
        sql = sql.replace('>', '&gt;')
        pretty = pretty.replace('&', '&amp;')
        pretty = pretty.replace('<', '&lt;')
        pretty = pretty.replace('>', '&gt;')
        pretty = pretty.replace('&lt;&lt;ERROR&gt;&gt;', '<span style="color:red">&lt;&lt;ERROR&gt;&gt;</span>')

    checkUpper = ''
    checkStrip = ''
    
    if (upper):
        checkUpper = 'checked'
    if (strip):
        checkStrip = 'checked'

    print """<html><head><title>Pretty SQL</title></head>
<body>
Enter your SQL below:<br/>
<form method="post">
  <textarea name="sql" rows="15" cols="72">%s</textarea><br/>
  <input type="submit" value="Prettify">&nbsp;&nbsp;&nbsp;
  Uppercase keywords: <input type="checkbox" name="upper" %s>&nbsp;&nbsp;&nbsp;
  Strip code: <input type="checkbox" name="strip" %s>
</form>
<hr/>
<p>
Pretty SQL:
<pre style="border: 1px solid #000000; padding: 5px">%s</pre>
</p>
</body>
</html>""" % (sql, checkUpper, checkStrip, pretty)


def main():
    print "Content-Type: text/html\n\n"

    form = cgi.FieldStorage()
    sql = ''
    upper = True
    strip = False
    
    if (form.has_key("sql")):
        sql = form["sql"].value
    if (not form.has_key("upper")):
        upper = False
    if (form.has_key("strip")):
        strip = True
        
    display_form(sql, upper, strip)


main()