#!/usr/bin/python

import MySQLdb, sys, hashlib
from optparse import OptionParser

#def initDB(db, cursor):
#	querystr = 
"""CREATE TABLE `tests` (
  `date` date NOT NULL,
  `matrix` varchar(500) NOT NULL,
  `comparator` varchar(500) NOT NULL,
  `test` varchar(500) NOT NULL,
  `correct` int(11) NOT NULL,
  `incorrect` int(11) NOT NULL,
  `skipped` int(11) NOT NULL,
  `accuracy` double NOT NULL,
  `hash` varchar(200) NOT NULL,
  `comment` varchar(500) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='WBST results table';"""
#	cursor.execute(querystr)

def downloadResults(options, outfile):
	# Open database connection
	db = MySQLdb.connect(options.host,options.user,options.passwd,options.db,port = int(options.port) )

	# prepare a cursor object using cursor() method
	cursor = db.cursor()

	# execute SQL query using execute() method.
	
	ofile = open(outfile,"w")
	ofile.write("Matrix no.; Matrix; Compare function;Test no.; Test name; Correct; Incorrect;Skipped;Accuracy\n")

	matrixInd = 0
	testInd = 0

	matrixDict = {}
	testDict = {}

	querystr = options.query
	cursor.execute(querystr)
	r = cursor.fetchone()
	while not r == None:
		r = list(r)
		if r[0] not in matrixDict:
			matrixDict[r[0]] = str(matrixInd)
			matrixInd += 1
		if r[2] not in testDict:
			testDict[r[2]] = str(testInd)
			testInd += 1	
		ofile.write(matrixDict[r[0]]+";"+str(r[0])+";"+str(r[1])+";"+testDict[r[2]]+";"+str(r[2])+";"+str(r[3])+";"+str(r[4])+";"+str(r[5])+";"+str(r[6])+"\n")
		r = cursor.fetchone()
		
		
	ofile.write("\nLegend:\nMatrices:\nNumber;Name;Path\n")
	for m in sorted(matrixDict.keys()):
		ofile.write(str(m)+";"+matrixDict[m]+";unknown\n")
	
	ofile.write("\nWBST files:\nNumber;test;;;file\n")
	for t in sorted(testDict.keys()):
		ofile.write(str(t)+";"+testDict[t]+";;;unknown\n")

	# disconnect from server
	db.close()

def main():
	parser = OptionParser()
	parser.add_option("-u", "--user", dest="user", default = 'wbst', help="Database user")
	parser.add_option("-H", "--host", dest="host", default = 'nlp.pwr.wroc.pl', help="Database host")
	parser.add_option("-p", "--passwd", dest="passwd", default = 'aXD5ejnh8KsesdTf', help="Database password")
	parser.add_option("-d", "--db", dest="db", default = 'wbst', help="Database schema")
	parser.add_option("-P", "--port", dest="port", default = '3308', help="Database port")
	parser.add_option("-o", "--output", dest="output", default = '/home/lukasz/wbst/results', help="Result file")
	parser.add_option("-q", "--query", dest="query", default="SELECT matrix,comparator,test,correct,incorrect,skipped,accuracy FROM tests", help = "A query for the database")

	options = parser.parse_args(sys.argv)[0]

	ofile = options.output
	downloadResults(options,ofile)

if __name__ == "__main__":
	main()
