"""
    JyQL+ - Quick 'n Dirty Jython SQL shell.
            motivation: It is _lot_ more easier to put two jar's and get to the database than installing
                        Oracle client. Moreover, this should illustrate the elegance and ease jython can
                        bring to Java.

    usage:  jython -i %s database_configuration <configuration_file>

    You can type a sql query followed by semi-colon and hit enter to see the results. Some special commands available
    are below:
        exit or quit: gets you out.
        jython: takes you to jython prompt. you can manipulate statement or connection objects here, at the
                very least.

    Notes:
        - There are no exception checks. You type something wrong, you will probably get a long error message!
        - You select huge number of rows, be prepared to wait till all gets displayed.
        - You select LOB or LONG columns at your own risk.
        - It should work for other JDBC drivers too - change the hard-coded driver and url strings.

    Installation:
        - Get and install JRE or JDK from java.sun.com
        - Get and install jython from www.jython.org
        - Drop database drivers into your CLASSPATH
        - Make a configuration file as jysqlp.ini in the same folder from where you call this - a sample is given in the script.
        - Run jython.bat -i <this_file with options as above>

    TODO:
        - trap CTRL-C and CTRL-D
        - check if the shell is started interactively
"""
__author__ = "Vattekkat Satheesh Babu <vsbabu_at_vsbabu_dot_org>"
__date__ = "$Date: 2004/02/24 10:16:02 $"
__version__ = "$Revision: 1.4 $"
#$Source: /home/vsbabu/repository/python/jyqlp.jy,v $
from java.sql import *
from java.lang import *
import sys

def exit():
    statement.close()
    connection.close()
    sys.exit(0)

def shell():
    while 1:
        sql = ""
        while 1:
            if sql == "":
                t_sql = raw_input(prompt).strip()
            else:
                t_sql = raw_input(prompt + ">").strip()
            try:
                if t_sql[-1] == ";":
                    sql = sql + "\n" + t_sql[:-1]
                    break
                else:
                    sql = sql +  "\n " + t_sql
                    continue
            except:
                #skip unnecessary blank entries
                continue
        sql = sql.strip()
        if sql[-1]==';':
            sql = sql[:-1]
        if sql.lower() in ('exit', 'quit'):
            exit()
        if sql.lower() == 'jython':
            if inInteractiveMode>0:
                print "Type shell() to go back to query mode"
                return
            else:
                print "Not in interactive mode. You need to start jython with -i option."
                continue
        if sql.lower() == 'help':
            print __doc__ % sys.argv[0]
            continue
        if sql == '':
            continue
        #see if this is a select
        if sql.lower().find('select') != 0:
            #TODO: provide some feedback
            statement.execute(sql)
            continue
        try:
            resultset  = statement.executeQuery(sql)
        except:
            print sys.exc_type, sys.exc_value
            continue
        resultset_metadata = resultset.getMetaData()
        col_formats=[0]
        underline = "\n"
        for i in range(1, resultset_metadata.getColumnCount()+1):
            if resultset_metadata.getColumnType(i) in (Types.BIGINT, Types.DATE, Types.FLOAT, Types.INTEGER, Types.NUMERIC, Types.REAL, Types.SMALLINT, Types.TIME, Types.TIMESTAMP, Types.TINYINT):
                col_formats.append("%%%ds" % resultset_metadata.getColumnDisplaySize(i))
            else:
                col_formats.append("%%-%ds" % resultset_metadata.getColumnDisplaySize(i))
            underline = underline + ('-'*resultset_metadata.getColumnDisplaySize(i)) + " "
            print col_formats[i] % resultset_metadata.getColumnName(i),
        print underline
        while resultset.next():
            for i in range(1, resultset_metadata.getColumnCount()+1):
                print col_formats[i] % resultset.getString(i),
            print 
        resultset.close()
    return

if __name__ == '__main__':
    from ConfigParser import ConfigParser
    config = ConfigParser()
    if len(sys.argv) >= 2:
        if len(sys.argv) >= 3:
            config.readfp(open(sys.argv[1]))
        else:
            config.readfp(open("jyqlp.ini"))
        if config.has_section(sys.argv[1]):
            driver = config.get(sys.argv[1], "driver")
            url    = config.get(sys.argv[1], "url")
            userid = config.get(sys.argv[1], "userid")
            passwd = config.get(sys.argv[1], "password")
        else:
            print "Could not find a database configuration for", sys.argv[1]
            print __doc__ % sys.argv[0]
            sys.exit(1)
    else:
        print __doc__ % sys.argv[0]
        sys.exit(1)

    #TODO: not reliable to check for ps1 to detect interactive mode
    if hasattr(sys, 'ps1'): 
        inInteractiveMode = 1
    else:
        inInteractiveMode = 0

    Class.forName(driver)
    connection = DriverManager.getConnection(url, userid, passwd)
    statement  = connection.createStatement()

    prompt = userid + ">"
    shell()

## sample configuration
sample = """
[fb_emp]
driver=org.firebirdsql.jdbc.FBDriver
url=jdbc:firebirdsql:localhost/3050:c:\\software\\firebird\\examples\\employee.fdb
userid=sysdba
password=masterkey

[or_scot]
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:ORCL
userid=scott
password=tiger
"""