##########################################################################
# DbUtils.py:  Python source file for the Simcal1 module.                #
# Copyright (c) 2004 Daniel Dvorkin.                                     #
# Distributed under the Artistic License, WITHOUT "optional clause 8"    #
# (see http://www.opensource.org/licenses/artistic-license.php).         #
# All rights not granted in this license are reserved by the author.     #
##########################################################################

"""Defines low-level database interactions.

DbUtils performs a number of roles.  It takes care of dependencies by
importing the database API (via Simcal1), defines connection information
(again by importing Simcal1), and creates a database connection.  However,
its most important role is to define the class DbRecord, which provides an
object representation of a database record (row), and the query functions
valq, rowq, objq, mobq, and setq, which automate much of the process of 
performing various types of queries."""

# MASTER IMPORT STATEMENT

from Simcal1 import *

# CONNECTION
# (May have to be changed for use with DB API'S other than MySQLdb)

conn = DbAPI.connect (host = HOSTNAME, db = DBNAME, 
 					  user = USERNAME, passwd = PASSWDNAME)
					  
				
# DBRECORD CLASS DEFINITION

class DbRecord(object):
	"""An object representation of a database record (row)
    
    Takes two sequence (list or tuple) arguments.  The first is the 
    field (column) names in the record, and the second is their values.
    The length of the two sequences must be identical or an exception
    will be raised.  The sequences must be correctly aligned for the 
    results to be meaningful.
    
    Example of usage:
    
        from DbUtils import *
        
        curs = conn.cursor()
        curs.execute("select Id, Name
                      from   People 
                      where  DOB = '1970-01-01'")
        fields = [desc[0] for desc in cursor.description]
        
        theRecord = DbRecord(fields, cursor.fetchone())
        print theRecord.Id, theRecord.Name
        
    This will produce:
    
        1 Joe Schmoe"""
        
	# CONSTRUCTOR
	
	def __init__(self, keys, vals):
	
		length = len(keys)
		
		if length != len(vals):
			raise ValueError, \
				"DbRecord:  key and value lengths don't match!"
		else:
			for i in xrange(length):
				self.__dict__[keys[i]] = vals[i]
				
				
# FUNCTION DEFINITIONS

def valq(sql, *args):
	"""VALue Query
    
    Takes a sql query as an argument and returns a single value as
    a result.  Optionally, takes one-element tuple for use as a second 
    argument.  Examples of usage:
    
        print valq(\"""select Id 
                      from   People
                      where  Name like 'Joe Schmoe'\""")
    
        print valq(\"""select Id 
                      from   People
                      where  Name like %s\""", "Joe Schmoe")
                    
    Either of these will produce:
    
        1"""
		
	# set up necessary variables
	argsize = len(args) # will determine which execute* method to use
	curs = conn.cursor() # set up cursor for this query
	
	# determine how to execute query
	if argsize == 0:
		curs.execute(sql)
	else:
		curs.execute(sql, args)
		
	# see if we got a valid result
	if curs.rowcount > 0:
		retn = curs.fetchone()[0]
	else:
		retn = None
		
	# clean up and get us out of here
	curs.close()
	return retn
	
	
def rowq(sql, *args):
	"""ROW Query
    
    Takes a sql query as an argument and returns a row (list) as
    a result.  Optionally, takes one-element tuple for use as a second 
    argument.  Example of usage:
    
        print rowq(\"""select Name, Id 
                      from   People
                      where  Name like 'Joe Schmoe'\""")
                    
    This will produce:
    
        ['Joe Schmoe', 1L]"""
		
	# set up necessary variables
	argsize = len(args) # will determine which execute* method to use
	curs = conn.cursor() # set up cursor for this query
	
	# determine how to execute query
	# determine how to execute query
	if argsize == 0:
		curs.execute(sql)
	else:
		curs.execute(sql, args)
		
	# see if we got a valid result
	if curs.rowcount > 0:
		retn = list(curs.fetchone())
	else:
		retn = []
		
	# clean up and get us out of here
	curs.close()
	return retn
	

def objq(sql, *args):
	"""OBJect Query
    
    Takes a sql query as an argument and returns an object of class
    DbRecord as a result.  Optionally, takes one-element tuple for use
    as a second argument.  Example of usage:
    
        theRecord = objq(\"""select Name, Id 
                            from   People
                            where  Name like 'Joe Schmoe'\""")
        print theRecord.Id, theRecord.Name
        
    This will produce:
    
        1 Joe Schmoe"""
		
	# set up necessary variables
	argsize = len(args) # will determine which execute* method to use
	curs = conn.cursor() # set up cursor for this query
	
	# determine how to execute query
	if argsize == 0:
		curs.execute(sql)
	else:
		curs.execute(sql, args)
		
	# see if we got a valid result
	if curs.rowcount > 0:
		fields = [desc[0] for desc in curs.description]
		values = curs.fetchone()
	else:
		fields = values = []
		
	# clean up and get us out of here
	curs.close()
	return DbRecord(fields, values)
	

def mobq(sql, *args):
	"""Multiple OBject Query
    
    Takes a sql query as an argument and returns a list of objects of 
    the DbRecord class as a result.  Optionally, takes one-element tuple 
    for use as a second argument.  Example of usage:
    
        theRecords = mobq(\"""select Name, Id 
                             from   People\""")
        for theRecord in theRecords:
            print theRecord.Id, theRecord.Name
        
    This will produce:
    
        1 Joe Schmoe
        2 Jane Schmoe"""
		
	# set up necessary variables
	argsize = len(args) # will determine which execute* method to use
	curs = conn.cursor() # set up cursor for this query
	
	# determine how to execute query
	if argsize == 0:
		curs.execute(sql)
	else:
		curs.execute(sql, args)
		
	# see if we got a valid result
	if curs.rowcount > 0:
		fields = [desc[0] for desc in curs.description]
		rows = curs.fetchall()
		retn = [DbRecord(fields, values) for values in rows]
	else:
		retn = []
		
	# clean up and get us out of here
	curs.close()
	return retn
	
	
def setq(sql, *args):
	"""SET Query
    
    Takes a sql query as an argument and executes it, usually with
    the assumption that a change will be made to the database.
    Returns the number of rows affected by the operation, so can be 
    used for primitive debugging as well.  Optionally, takes any
    number of "params" arguments for use with the "executemany"
    syntax.  Example of usage:
    
        setq(\"""insert into People
                set    Id = 3, Name = 'Jim Schmoe'\""")
              
        setq(\"""insert into People
                set    Id = %s, Name = %s\""",
                (4, 'John Doe'), (5, 'Jane Doe'))
              
        setq(\"""create table Places
                (
                  Id      int     not null,
                  City    text    not null,
                  Country text    not null
                )\""")
                
    Note that the second example above is equivalent to:
    
    	vals = [(4, 'John Doe'), (5, 'Jane Doe')]
        setq(\"""insert into People
                set    Id = %s, Name = %s\""", *vals)
                
    This usage may sometimes be more useful when building a long
    list of arguments.  Note also that setq, unlike the other query
    functions, which are expected to return a result set, _cannot_
    be called as "setq(sql, arg1, arg2, ...)" where arg1, arg2 etc.
    are scalars.  If called with arguments other than the sql string,
    these arguments _must_ be sequences (tuples or lists.)"""
		
	# set up necessary variables
	argsize = len(args) # will determine which execute* method to use
	curs = conn.cursor() # set up cursor for this query
	
	# determine which execute* method to use
	if argsize == 0:
		curs.execute(sql)
	else:
		curs.executemany(sql, args)
		
	# clean up and get us out of here
	retn = curs.rowcount
	curs.close()
	return retn