#!/usr/bin/env python2.4
import cx_Oracle
import sys
import os
import traceback
try:
	import readline
except ImportError:
	pass

if len(sys.argv) < 2:
	connstr = raw_input('Connect string: ')
else:
	connstr = sys.argv[1]

def softpad(s, l, padchar = " "):
	padding = padchar * l
	if len(s) >= l:
		return s + padchar
	return (s + padding)[:l]
def pad(s, l, padchar = " "):
	padding = padchar * l
	return (s + padding)[:l]

rowbuffer = None
def print_row():
	global rowbuffer
	if not cur or not cur.description:
		return False
	try:
		page = int(opts['page'])
		colhdrs = [x[0] for x in cur.description]
		allcols = []
		lens = []
		for i in range(len(colhdrs)):
			l = min(len(colhdrs[i]), 10)
			lens.append(l)
		if rowbuffer:
			rowbuffer = [repr(x) for x in rowbuffer]
			allcols = [rowbuffer]
			for i in range(len(rowbuffer)):
				l = len(rowbuffer[i])
				if i > len(lens):
					print lens
					print rowbuffer
				if l > lens[i]:
					lens[i] = l
			rowbuffer = None
		while len(allcols) < page:
			cols = cur.fetchone()
			if not cols:
				break
			cols = [repr(x) for x in cols]
			allcols.append(cols)
			for i in range(len(cols)):
				l = len(cols[i])
				if l > lens[i]:
					lens[i] = l
		if not allcols:
			return False
		for i in range(len(colhdrs)):
			colhdrs[i] = pad(colhdrs[i], lens[i])

		for i in range(len(allcols)):
			cols = allcols[i]
			for j in range(len(cols)):
				cols[j] = pad(cols[j], lens[j])
		
		colhdrrow = ' | '.join(colhdrs)
		seprow = '-' * len(colhdrrow)
		print colhdrrow
		print seprow
		for cols in allcols:
			colsrow = ' | '.join(cols)
			print colsrow
		
		rowbuffer = cur.fetchone()
		if rowbuffer:
			print "  ( more )  "
		return True
	except:
		traceback.print_exc()
		return False

db = cx_Oracle.connect(connstr)
cur = db.cursor()
opts = {
	'page': '20',
	'hide_null_tablespace': '1',
	'hide_tablespaces': 'SYSTEM;SYSAUX;SDE;SPATIAL_DATA'
}
do_quit = False
while True:
	try:
		v = raw_input('# ')
	except EOFError:
		do_quit = True

	if do_quit:
		break
	v = v.strip()
	vl = v.lower()
	
	if vl == 'quit' or vl == 'exit':
		break
	elif vl == 'commit':
		db.commit()
	elif vl == 'rollback':
		db.rollback()
	elif vl == 'tables':
		rowbuffer = None
		conds = ["tablespace_name != '%s'" % (x,) for x in opts['hide_tablespaces'].split(';')]
		if opts['hide_null_tablespace'][0:1] in ('1', 'Y', 'y', 'T', 't'):
			conds += ['tablespace_name is not null']
		conds = ' and '.join(conds)
		if conds:
			conds = ' where ' + conds
		cur.execute("select table_name, tablespace_name from all_tables %s" % (conds,))
		print_row()
	elif vl[:5] == 'exec ':
		cmd = v[5:]
		try:
			exec cmd
		except:
			traceback.print_exc()
	elif vl[:5] == 'cols ':
		rowbuffer = None
		vs = v.split(' ')
		c2 = db.cursor()
		try:
			c2.execute("select * from %s where 1 is null" % (vs[1],))
			for coldata in c2.description:
				print "%s%s" % (softpad(coldata[0], 40), coldata[1])
		except cx_Oracle.DatabaseError, e:
			sys.stdout.write(str(e))
	elif vl[:4] == 'set ':
		vs = v[4:]
		vi = vs.find('=')
		vn = vs[:vi]
		vv = vs[vi+1:]
		opts[vn] = vv
	elif vl == '':
		print_row()
	elif vl == 'help':
		print """
commands:
  tables       lists tables
  cols <table> lists columns of table
  commit       commits any pending updates or inserts
  rollback     aborts and pending updates or inserts
  help         displays this help
  quit         exits sql.py
"""

			
	else:
		rowbuffer = None
		try:
			v = v.rstrip(';')
			cur.execute(v)
			print_row()
		except cx_Oracle.DatabaseError, e:
			try:			
				cur.execute("SELECT 1 FROM DUAL")
			except cx_Oracle.DatabaseError:
				print "Database connection lost!"
				sys.exit(1)
			sys.stdout.write(str(e))

			

print ""