#!/usr/bin/python2
""">> sqlcl << command line query tool by Tamas Budavari <budavari@jhu.edu>
Usage: sqlcl [options] sqlfile(s)

Options:
		-s url	   : URL with the ASP interface (default: pha)
		-f fmt	   : set output format (html,xml,csv - default: csv)
		-q query   : specify query on the command line
		-l		   : skip first line of output with column names
		-v	   : verbose mode dumps settings in header
		-h	   : show this message"""

formats = ['csv','xml','html']

astro_url='http://cas.sdss.org/astro/en/tools/search/x_sql.asp'
public_url='http://cas.sdss.org/public/en/tools/search/x_sql.asp'

default_url=public_url
default_fmt='csv'

def usage(status, msg=''):
	"Error message and usage"
	print __doc__
	if msg:
		print '-- ERROR: %s' % msg
	sys.exit(status)

def filtercomment(sql):
	"Get rid of comments starting with --"
	import os
	fsql = ''
	for line in sql.split('\n'):
		fsql += line.split('--')[0] + ' ' + os.linesep;
	return fsql

def query(sql,url=default_url,fmt=default_fmt):
	"Run query and return file object"
	import urllib
	fsql = filtercomment(sql)
	params = urllib.urlencode({'cmd': fsql, 'format': fmt})
	return urllib.urlopen(url+'?%s' % params)	 

def write_header(ofp,pre,url,qry):
	import	time
	ofp.write('%s SOURCE: %s\n' % (pre,url))
	ofp.write('%s TIME: %s\n' % (pre,time.asctime()))	 
	ofp.write('%s QUERY:\n' % pre)
	for l in qry.split('\n'):
		ofp.write('%s	%s\n' % (pre,l))
	
def main(argv):
	"Parse command line and do it..."
	import os, getopt, string
	
	queries = []
	url = os.getenv("SQLCLURL",default_url)
	fmt = default_fmt
	writefirst = 1
	verbose = 0
	
	# Parse command line
	try:
		optlist, args = getopt.getopt(argv[1:],'s:f:q:vlh?')
	except getopt.error, e:
		usage(1,e)
		
	for o,a in optlist:
		if	 o=='-s': url = a
		elif o=='-f': fmt = a
		elif o=='-q': queries.append(a)
		elif o=='-l': writefirst = 0
		elif o=='-v': verbose += 1
		else: usage(0)
		
	if fmt not in formats:
		usage(1,'Wrong format!')

	# Enqueue queries in files
	for fname in args:
		try:
			queries.append(open(fname).read())
		except IOError, e:
			usage(1,e)

	# Run all queries sequentially
	for qry in queries:
		ofp = sys.stdout
		if verbose:
			write_header(ofp,'#',url,qry)
		file = query(qry,url,fmt)
		# Output line by line (in case it's big)
		line = file.readline()
		if line.startswith("ERROR"): # SQL Statement Error -> stderr
			ofp = sys.stderr
		if writefirst:
			ofp.write(string.rstrip(line)+os.linesep)
		line = file.readline()
		while line:
			ofp.write(string.rstrip(line)+os.linesep)
			line = file.readline()


if __name__=='__main__':
	import sys
	main(sys.argv)






