##^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Lance Simms, Stanford University 2009
##This script is an example of how I can use MySQL and create a set
##of databases that do not stink of redundancy

##IMPORT THE MODULE LOCATED IN /u1/ki
import MySQLdb
import sys

##Open up the connection and include some error handling
try:
	conn=MySQLdb.connect (host="localhost",
	           	      user="root",
		              passwd="mysql",
	        	      db="test",
			      unix_socket="/tmp/mysql.sock")
except MySQLdb.Error, e:
	print "All Bad Error"
	print "Error $d: $s" % (e.args[0],e.args[1])
	sys.exit

##invoke the cursor() method to create a cursor object for processing 
##queries

cursor=conn.cursor()

try:
	cursor.execute("CREATE DATABASE Test_Normalization")
	print "Database Created"
except MySQLdb.Error, e:
        print "Databse Test_Normalization Already Exists"

try:
	cursor.execute("use Test_Normalization");

except MySQLdb.Error, e:
        print "Error in creating first database"

##Create the Two Tables ###########################################
try:
	cursor.execute('CREATE TABLE PersistentImages('+\
			'PIXID mediumint NOT NULL PRIMARY KEY,'+\
			'Min float,'+\
			'Max float);')
except MySQLdb.Error, e:
        print "Error in creating first table"

try:
        cursor.execute('CREATE TABLE OriginalImages('+\
                        'PIXID mediumint,'+\
                        'MaxRad mediumint,'+\
                        'MinRad float);')
except MySQLdb.Error, e:
        print "Error in creating second table"

##Insert related info into the two databases
cursor.execute("INSERT INTO PersistentImages VALUES(100, 2, 4)")
cursor.execute("INSERT INTO PersistentImages VALUES(200, 1, 1)")

cursor.execute("INSERT INTO OriginalImages VALUES(100, 22, 24)")
cursor.execute("INSERT INTO OriginalImages VALUES(100, 23, 25)")
cursor.execute("INSERT INTO OriginalImages VALUES(100, 22, 21)")
cursor.execute("INSERT INTO OriginalImages VALUES(100, 29, 23)")
cursor.execute("INSERT INTO OriginalImages VALUES(100, 23, 24)")

##Now we have a two tables.  Persistent Images contains basic info about 
##each of the pixels that would be redundant in the other table.  
##OriginalImages contains info for particular pixels identified by PIXID
cursor.execute("select MinRad, MaxRad, Min, Max from OriginalImages, PersistentImages where PersistentImages.PIXID = 200 and OriginalImages.PIXID = 200;")
cursor.close()