# Name:		SQLPyDemo.py
# Author:	Dave Renner		dmrenne@comcast.net
# Date:		08/28/11	2245
# Rev:		09/24/12	1945
# Note:		this is a called module of 'pyDemo.py'
#
# Note:		'SQLPyDemo.py' demos Python SQL on its provided database
#			engine for SQLite. Here we create and manipulate tables,
#			records, and data, as well as handling transactions, triggers,
#			foreign keys and data integrity, error detecttion, cursors
#			and multiple row fetches.
#			We will of course use the standard SQL operations 'select',
#			'insert' and 'delete'.
#
# 
import os
import os.path
import sqlite3
import csv

# ############################ BOILERPLATE ##########################
import pickle

myConfigFile = 'pyDemoConfig.dat'

# version has been stored in our calling module 'pyDemo.py'
PythonVersFile = 'version.obj'
versfile = open(PythonVersFile, 'rb')
PythonVers = pickle.load(versfile)
versfile.close()

if PythonVers == '30':
    import configparser
    config = configparser.ConfigParser()
    input_cmd = 'input'
else:
    import ConfigParser
    config = ConfigParser.ConfigParser()
    input_cmd = 'raw_input'

config.read(myConfigFile)
interactive = config.get('Mode', 'interactive')
continue_cmd = config.get('Strings', 'continue_cmd')

# easier to just include this boilerplate function into every module 
# than to import it
def printf(arg):
    cmdText = "print "
    arg_nonSlash = arg.strip('\\')
    if PythonVers == '30':
        cmdFull = cmdText + '("' + arg_nonSlash + '")'
    else:
        cmdFull = cmdText + '"' + arg_nonSlash + '"'
    exec( cmdFull )

# ################################################################### 
EMPTABLE = 'SQLEMPTABLE.csv'
DEPTTABLE = 'SQLDEPTTABLE.csv'
USERTABLE = 'SQLUSERTABLE.csv'
NO_EMP_TABLE_EXISTS = 1
NO_DEPT_TABLE_EXISTS = 1
NO_USER_TABLE_EXISTS = 1
NO_TRIGGERS_EXIST = 1 
DEBUG1 = 1
DEBUG2 = 0
# ###################################################################
printf("")
printf("We are now in 'SQLPyDemo.py' which will demonstratehow\
\\nPython interaces to a SQLite database. SQLite comes as part of\
\\nthe Python distro, so it's very convenient, popular,\
\\nand quite versatile. It is NOT, however, accessible from the command line,\
\\nor outside of Python.\
\\n\
\\nWe will connect to a named database ('SQLdb.db') and,\
\\non first entry to this program, will create some tables for it.\
\\n(This is set by a flag at the top of this module. We've done\
\\nthis before you run this program, so we won't do it again.\
\\nThe code is very simple; feel free to look at it.\
\\nIf you know SQL, it's pretty self-explan...\
\\nno, I won't quite say that....\
\\n\
\\n\
\\nBack on track:\
\\nPython implements SQL by means of three primary 'objects'\
\\nwith accompanying methods and attributes. Those objects are:\
\\n1) Connection\
\\n2) Cursor\
\\n3) SQL module itself")

printf("")
if interactive == '1':
	printf("")
	full_cmd = input_cmd + '("' + continue_cmd + '")'
	eval(full_cmd)	

printf("")
printf("A Connection (that's the class name) object is created\
\\nby a statement such as:\
\\nmyConn = sqlite3.connect('myDatabaseName')\
\\nThe most frequently-used attributes of Connection are settings:\
\\n\
\\n1) 'Connection.text_factory = str',\
\\nwhich changes the storage of text from its Unicode default\
\\nto the more familiar UTF-8 strings;\
\\nvery useful for output, in most non-international situations.\
\\n\
\\n2) 'Connection.isolation_level = 'EXCLUSIVE',\
\\nwhich sets how transactions are to be handled, i.e.,\
\\nwhen tables are to be locked.\
\\nThe default is 'DEFERRED';\
\\nthere is also 'IMMEDIATE',\
\\nand the aforementioned 'EXCLUSIVE'.\
\\nWe won't go into further detail about database transactions here.\
\\n")
if interactive == '1':
	printf("")
	full_cmd = input_cmd + '("' + continue_cmd + '")'
	eval(full_cmd)
printf("")	
printf("In this example, we are just setting the isolation level\
\\nto 'EXCLUSIVE', just to prove that we can.\
\\nNote that this setting must be a string (in quotes);\
\\nthe 'www.Python.org' documentation is in error here.\
\\n\
\\nOK. Other Connection attributes of note are the methods:\
\\nConnection.commit()\
\\nConnection.rollback(), used to undo a transaction that has failed\
\\nConnection.interrupt(), used in error processing\
\\namong different processes.")

printf("")
if interactive == '1':
	printf("")
	full_cmd = input_cmd + '("' + continue_cmd + '")'
	eval(full_cmd)	

printf("")
printf("The 'Cursor' object  is what is used for almost all\
\\nSQL-type transactions; its name derives from the action\
\\nof repeated scrolling, like a cursor. Its two\
\\nmost important methods are:\
\\n1) execute\
\\n2) fetchall\
\\n\
\\n'execute' takes an argument in the form of an SQL-syntax string, like:\
\\nCursor.execute('DROP TABLE employee')\
\\nCursor.execute('SELECT * FROM employee')\
\\n\
\\n'fetchall' gets the result of a multi-record SELECT, like:\
\\nCursor.execute('SELECT * FROM employee')\
\\nfor row in Cursor.fetchall():\
\\n    print row\
\\n\
\\n\
\\nNow, that we know all about databases and SQLite3, let's boogie...\
")
printf("")

mycurdir = os.getcwd()


# ##################################################################
#                       CREATE TABLES
# ##################################################################

conn = sqlite3.connect( 'SQLdb.db' )
cursor = conn.cursor()

# converts text storage from Unicode default to UTF-8 string
conn.text_factory = str

# exclusive R/W lock on database for transaction.
# Not req'd here, but illustrated how transaction processing
# is controlled
conn.isolation_level = 'EXCLUSIVE'

printf("")
if interactive == '1':
	printf("")
	full_cmd = input_cmd + '("' + continue_cmd + '")'
	eval(full_cmd)	

printf("First, we'll create tables, using standard SQL syntax.\
\\nWe'll create the following tables:\
\\n1) employee\
\\n2) department\
\\n3) users\
\\n\
\\nemployee will have the fields and formats:\
\\nemp_id		integer primary	key,\
\\nfirstname	text,\
\\nlastname	text,\
\\ndept		integer,\
\\nphone		text,\
\\nFOREIGN KEY(dept) REFERENCES department(dept_id)\
\\n\
\\ndepartment will have the fields and formats:\
\\ndept_id		integer primary key,\
\\ndeptname	text,\
\\nmanager		integer\
\\n")

if interactive == '1':
	printf("")
	full_cmd = input_cmd + '("' + continue_cmd + '")'
	eval(full_cmd)	

printf("")
printf("users will have the fields and formats:\
\\nuser_id		integer primary key,\
\\nusername	text\
\\nempid		integer,\
\\nFOREIGN KEY(empid) REFERENCES employee(emp_id)\
\\n\
\\nThe 'primary key' qualification ensures that this is\
\\na true relational database and that no records having\
\\nthe same key can be inserted.\
\\n\
\\nThe 'FOREIGN KEY' in one table is, and must be,\
\\na primary key in another.\
\\nSo if the primary key is deleted, the database engine\
\\nwill raise an error if it's referenced in another\
\\nrecord or table somewhere.")

printf("")
printf("")
if interactive == '1':
	printf("")
	full_cmd = input_cmd + '("' + continue_cmd + '")'
	eval(full_cmd)	

# #################################################################
if NO_EMP_TABLE_EXISTS:
    cursor.execute("DROP TABLE employee")
    
    cursor.execute("""CREATE TABLE employee
	( emp_id 	integer NOT NULL PRIMARY KEY,
	firstname 	text NOT NULL,
	lastname  	text NOT NULL,
	dept 		integer NOT NULL,
	phone		text,
    CONSTRAINT fkd_employee_dept FOREIGN KEY(dept) 
    REFERENCES department(dept_id) )""")
    
    cursor.execute("""CREATE INDEX ind_emp_id ON employee(emp_id)""")
    
    printf("")
    printf('employee table created!')
	
# #################################################################
if NO_DEPT_TABLE_EXISTS:	
    cursor.execute("DROP TABLE department")
    cursor.execute("""CREATE TABLE department
    ( dept_id	integer NOT NULL PRIMARY KEY,
    name 		text NOT NULL,
    manager		integer ) """)
    
    cursor.execute("""CREATE INDEX ind_dept_id ON department(dept_id)""")
    
    printf("department table created!")
	
# #################################################################	
if NO_USER_TABLE_EXISTS:
    cursor.execute("DROP TABLE user")
	
    cursor.execute("""CREATE TABLE user  
	( user_id	integer NOT NULL PRIMARY KEY,
	username	text NOT NULL,
	empid		integer NOT NULL,
    CONSTRAINT fkd_user_empid FOREIGN KEY(empid)
    REFERENCES employee(emp_id) )""")
	
    cursor.execute("""CREATE INDEX ind_user_id ON user(user_id)""")
    
    printf("user table created!")

# #################################################################
if NO_TRIGGERS_EXIST:
    printf("")
    printf("Now we will create the appropriate 'triggers'\
\\nto enforce referential integrity, so that:\
\\n1) Delete of an employee will automatically delete rows\
\\n for that employee in the 'user' table\
\\n2) Delete of a department will raise an error\
\\nif any rows in 'employee' contain that dept ID.\
\\n\
\\nSo. Triggers are:")

if interactive == '1':
	printf("")
	full_cmd = input_cmd + '("' + continue_cmd + '")'
	eval(full_cmd)	

printf("cursor.execute(CREATE TRIGGER trig_del_employee_emp_id\
\\n    AFTER DELETE ON employee\
\\n    FOR EACH ROW BEGIN\
\\n        DELETE FROM user WHERE empid = OLD.emp_id;\
\\n    END;)\
\\n\
\\ncursor.execute(CREATE TRIGGER trig_del_department_dept_id\
\\n    BEFORE DELETE ON department\
\\n    FOR EACH ROW BEGIN\
\\n        SELECT RAISE( ROLLBACK, \
\\n        'records in EMP tbl refer to dept_no being deleted')\
\\n        WHERE( SELECT dept FROM employee WHERE dept = \
\\n        OLD.dept_id) IS NOT NULL;\
\\n    END;)\
\\n\
\\ncursor.execute(CREATE TRIGGER trig_ins_user_empid\
\\n    BEFORE INSERT ON user\
\\n    FOR EACH ROW BEGIN\
\\n        SELECT RAISE( ROLLBACK, 'emp_id must be in EMPLOYEE table')\
\\n        WHERE (SELECT emp_id FROM employee \
\\n        WHERE emp_id = NEW.empid ) IS NULL;\
\\n    END;)\
\\n")

if interactive == '1':
	printf("")
	full_cmd = input_cmd + '("' + continue_cmd + '")'
	eval(full_cmd)	

printf("")
printf("cursor.execute(CREATE TRIGGER trig_ins_employee_dept\
\\n\
\\n    BEFORE INSERT ON employee\
\\n    FOR EACH ROW BEGIN \
\\n        SELECT RAISE( ROLLBACK, 'dept must be in DEPARTMENT table')\
\\n        WHERE (SELECT dept_id FROM department\
\\n            WHERE dept_id = NEW.dept ) IS NULL;\
\\n    END;)\
\\n")

printf("")

cursor.execute("""CREATE TRIGGER trig_del_employee_emp_id

    AFTER DELETE ON employee
    FOR EACH ROW BEGIN
        DELETE FROM user WHERE empid = OLD.emp_id;
    END;""")

cursor.execute("""CREATE TRIGGER trig_del_department_dept_id
    BEFORE DELETE ON department
    FOR EACH ROW BEGIN
        SELECT RAISE( ROLLBACK, 'records in EMPLOYEE table still refer to dept_no being deleted')		
        WHERE( SELECT dept FROM employee WHERE dept = OLD.dept_id) IS NOT NULL;
    END;""")

cursor.execute("""CREATE TRIGGER trig_ins_user_empid
    BEFORE INSERT ON user
    FOR EACH ROW BEGIN
        SELECT RAISE( ROLLBACK, 'emp_id must be in EMPLOYEE table')
        WHERE (SELECT emp_id FROM employee WHERE emp_id = NEW.empid ) IS NULL;
    END;""")
	
cursor.execute("""CREATE TRIGGER trig_ins_employee_dept
    BEFORE INSERT ON employee
    FOR EACH ROW BEGIN
        SELECT RAISE( ROLLBACK, 'dept must be in DEPARTMENT table')
        WHERE (SELECT dept_id FROM department WHERE dept_id = NEW.dept ) IS NULL;
    END;""")

	
# ##################################################################
#                       LOAD DATABASE
# ##################################################################

printf("")
if interactive == '1':
	printf("")
	full_cmd = input_cmd + '("' + continue_cmd + '")'
	eval(full_cmd)	

printf("Now, we're ready to load our three database tables\
\\nwith our three input text files.\
\\n\
\\nWe will define the 'query' (SQL statement to be execute).\
\\n(It's not really a 'query', since it asks for no selections\
\\nbut most SQL statements that insert or update are also\
\\ncalled queries).\
\\n\
\\nIt is:\
\\nempInsQuery = 'INSERT INTO employee( emp_id, firstname, lastname,\
\\n    dept, manager, phone) VALUES(?,?,?,?,?,?)'\
\\n\
\\n\
\\nWe will pass values to it to replace the question marks.\
\\nWe get these values (our reocord content) from our input file\
\\nby using the csv (comma-separated-values) module:\
\\n    inFile = open('SQLEMPTABLE.csv', 'r')\
\\n    reader = csv.reader(inFile)\
\\n    for line in reader:\
\\n        #writ to database\
\\nThis eliminates problems with end-of-line carriage returns\
\\nbeing tacked onto the last text field of a record, which happens\
\\nwhen you treat each line of the file as a 'string'.")

if interactive == '1':
	printf("")
	full_cmd = input_cmd + '("' + continue_cmd + '")'
	eval(full_cmd)	

# ########################### DEPT table #########################
fileIn = open(DEPTTABLE, "r")
query = "INSERT INTO department( dept_id, name, manager) VALUES(?,?,?)"
cursor.execute("DELETE FROM department")

printf("")
printf("Now, we will print the DEPT rows we inserted")
printf("")

reader = csv.reader(fileIn)
for line in reader:
    linestr = str(line)
    printf( linestr )
    if DEBUG2:	
        printf (line[0] )
        printf (line[1] )
        printf (line[2] )
    cursor.execute( query, line )
 	# each insertion is independent, so may as well end transaction here
	# rather than waiting till entire table is inserted
    conn.commit()
	
printf("")	
printf("Now, to select from the database, just to make sure...")
printf("We will 'SELECT * FROM department', then we print each row")
printf("")

cursor.execute ( "SELECT * FROM department", ) 
for row in cursor.fetchall():
    rowstr = str(row)
    printf(rowstr)
printf("")
fileIn.close()
 
# ######################### EMPLOYEE table #############################
fileIn = open(EMPTABLE, "r")
query = "INSERT INTO employee( emp_id, firstname, lastname, dept, phone) VALUES(?,?,?,?,?)"

# always start with a clean table
cursor.execute("DELETE FROM employee")
if DEBUG2:
    printf("employee table deleted")
    printf("")
printf("")
printf("Now, we will print the EMP rows we inserted")
printf("")

reader = csv.reader(fileIn)
for line in reader:
    printf( str(line) )
    cursor.execute( query, line )
    conn.commit()
	
printf("")	
printf("Now we will select them from the database, just to make sure...")
cursor.execute ( "SELECT * FROM employee", ) 
for row in cursor.fetchall():
    printf( str(row) )
printf("")
fileIn.close()
		
# ############################ USER table ##############################
fileIn = open(USERTABLE, "r")
query = "INSERT INTO user( user_id, username, empid) VALUES(?,?,?)"

cursor.execute("DELETE FROM user")
printf("")
printf("Now, we will print the USER rows we inserted")
printf("")

reader = csv.reader(fileIn)
for line in reader:
    printf( str(line) )
    cursor.execute(query, line)
    conn.commit()

	
# ###############################################################	
#                 TEST REFERENTIAL INTEGRITY
# ###############################################################	

printf("")
if interactive == '1':
	printf("")
	full_cmd = input_cmd + '("' + continue_cmd + '")'
	eval(full_cmd)	

printf("Now, to select from the database, just to make sure...")
cursor.execute ( "SELECT * FROM user", ) 
for row in cursor.fetchall():
    printf( str(row) )
printf("")
fileIn.close()

printf("")
printf("Now to see if referential integrity through foreign keys\
\\nis enforced. (It better be!)\
\\nWe'll try to delete a row whose primary key is a foreign key\
\\nin other tables, in other words, having rows in other tables\
\\nthat are dependent.\
\\n\
\\n(In real life we also may check first to see if the key\
\\nthat we are deleting really exists.)\
\\n\
\\nIn any case, we'll test this by a 'try' statement,\
\\nresponding to the SQLite3 error 'IntegrityError'.\
\\n\
\\nOddly, in cases where we are using our own exception logic,\
\\nPython doesn't print out its own appropriate SQLite3 error message,\
\\nyet this message would have been the best way to determine\
\\nwhich error condition to handle!\
\\nOtherwise we might not catch the error at all!")

if interactive == '1':
	printf("")
	full_cmd = input_cmd + '("' + continue_cmd + '")'
	eval(full_cmd)	

printf("")
printf("(We circumvent this by our unit testing, of course,\
\\nusing Python's own error testing before we replace it\
\\with our own.")


# DELETE FROM DEPARTMENT TRIGGER TEST
try:
    cursor.execute("DELETE FROM department WHERE dept_id = 3")
    printf("done deleting")
    printf("")
except(sqlite3.IntegrityError):
    printf("This test worked; we cannot delete dept_id 3.")
    printf("")
    printf("Trigger worked! Cannot delete dept 3 until its employee rcd deleted first")
    printf("")
finally:
    printf("Now we will move on to the next test.")
    printf("")
	
# INSERT INTO USER TRIGGER TEST
try:
    cursor.execute("INSERT INTO user( user_id, username, empid) VALUES(77,'dude',444)")
except(sqlite3.IntegrityError):
    printf("Trigger worked! Cannot insert user with non-existant emp_id 444")
    printf("")
finally:

    printf("OK, we are done with SQL testing! See you Thursday, then!")
    printf("")
    printf("")

	
# ###############################################################	
#                          CLEAN UP
# ###############################################################	

cursor.close()
conn.close()