def checkIfReady():
_dbConn = ichorORM.getDatabaseConnection()
now = datetime.datetime.now()
isReady = False
resultRows = _dbConn.doSelect('SELECT id, is_complete, created_at FROM ichor_orm_tests WHERE state_num = ' + str(_TEST_DATA_STATE_NUM))
badIds = []
for resultRow in resultRows:
(resId, resIsComplete, resCreatedAt) = resultRow
if str(resIsComplete) == '1':
isReady = True
else:
recordAge = now - resCreatedAt
if recordAge.total_seconds() > 120:
# If more than 2 min old, clear bad record.
badIds.append(resId)
if badIds:
# If any bad ids, clear them to prevent delays in future ops
try:
dbConn.executeSql('DELETE FROM ichor_orm_tests WHERE id IN ( %s )' %( ', '.join([str(badId) for badId in badIds])))
except:
pass
return isReady
def test_Connect(self):
dbConn = ichorORM.getDatabaseConnection()
pass
pass
b = 1
c = 2
def test_getDatabaseConnection(self):
'''
Test the getDatabaseConnection method more in depth
'''
ALT_HOST = 'X_MY_HOST'
ALT_PORT = 9999
ALT_DB_NAME = 'X_MY_DB'
ALT_USER = '******'
ALT_PASS = '******'
dbConnGlobal = ichorORM.getDatabaseConnection()
dbConnAlt = ichorORM.getDatabaseConnection(host=ALT_HOST, port=ALT_PORT, dbname=ALT_DB_NAME, user=ALT_USER, password=ALT_PASS)
assert dbConnAlt.host == ALT_HOST , 'Expected ALT_HOST to be set on database connection. Expected %s but got %s' %( repr(ALT_HOST), repr(dbConnAlt.host) )
assert dbConnAlt.port == ALT_PORT , 'Expected ALT_PORT to be set on database connection. Expected %s but got %s' %( repr(ALT_PORT), repr(dbConnAlt.port) )
assert dbConnAlt.user == ALT_USER , 'Expected ALT_USER to be set on database connection. Expected %s but got %s' %( repr(ALT_USER), repr(dbConnAlt.user) )
assert dbConnAlt.dbname == ALT_DB_NAME , 'Expected ALT_DB_NAME to be set on database connection. Expected %s but got %s' %( repr(ALT_DB_NAME), repr(dbConnAlt.dbname) )
assert dbConnAlt.password == ALT_PASS , 'Expected ALT_PASS to be set on database connection. Expected %s but got %s' %( repr(ALT_PASS), repr(dbConnAlt.password) )
# Now, try a couple fields and see that we properly inherit global
for attrName, attrValue in [ ('host', ALT_HOST), ('port', ALT_PORT), ('user', ALT_USER), ('dbname', ALT_DB_NAME), ('password', ALT_PASS) ]:
args = { attrName : attrValue }
dbConnAlt = ichorORM.getDatabaseConnection(**args)
assert getattr(dbConnAlt, attrName) == attrValue , 'Expected to set %s=%s but object had value %s.' %( attrName, repr(attrValue), repr(getattr(dbConnAlt, attrName)) )
for connField in ( 'host', 'port', 'user', 'dbname', 'password' ):
if connField == attrName:
# Skip if this is the field we modified
continue
globalVal = getattr(dbConnGlobal, connField)
altVal = getattr(dbConnAlt, connField)
assert globalVal == altVal , 'Expected that when changing field %s but not %s we would inherit global value for %s. Expected %s but got %s' %( attrName, connField, connField, repr(globalVal), repr(altVal) )
def teardown_method(self, meth):
'''
teardown_method - Called after execution of each method to clean up
@param meth <built-in method> - The method being tested (compare meth == self.someMethod)
'''
if meth in (self.test_generalDelete, self.test_deleteWithQueryStr,
self.test_deleteWithSelectQuery, self.test_deleteAll):
dbConn = ichorORM.getDatabaseConnection()
dbConn.executeSql('DELETE FROM %s' % (MyPersonModel.TABLE_NAME, ))
def test_getCursor(self):
'''
test_getCursor - Test if we can obtain a cursor with global connection params
'''
dbConn = ichorORM.getDatabaseConnection()
dbCursor = dbConn.getCursor()
assert dbCursor , 'Expected to be able to get a cursor, but did not.'
def teardown_method(self, meth):
'''
teardown_method - Called after every method.
@param meth <builtins.method> - The test method that completed
'''
try:
dbConn = ichorORM.getDatabaseConnection()
dbConn.executeSql("DELETE FROM %s" % (MyPersonModel.TABLE_NAME, ))
except Exception as e:
pass
def createModel(cls):
'''
createModel - Will create this model
'''
cls.dropModel()
dbConn = getDatabaseConnection()
createQuery = '''CREATE TABLE ''' + cls.TABLE_NAME + ''' ( id serial primary key, first_name varchar(255) NOT NULL, last_name varchar(255) NOT NULL, age smallint NULL, birth_day smallint NULL, birth_month smallint NULL )'''
dbConn.executeSql(createQuery)
def teardown_class(self):
'''
teardown_class - Destroy any data generated by this test.
Ran after all tests have completed
'''
try:
dbConn = ichorORM.getDatabaseConnection()
dbConn.executeSql("DROP TABLE %s CASCADE" %
(MyPersonModel.TABLE_NAME, ))
except Exception as e:
pass
def test_doSelectFromParams(self):
'''
test_doSelectFromParams - Test the doSelect method (using the params insert)
'''
# Perform inserts and such here
try:
self.test_executeSqlParams()
except AssertionError as e:
raise AssertionError('Unable to run test_doSelectFromParams because test_executeSqlParams failed.')
dbConn = getDatabaseConnection()
query = 'SELECT id, name, value, extra_data FROM ichor_test_conn_table'
results = dbConn.doSelect(query)
assert results , 'Expected to get results from select query, but did not.'
assert len(results) == 3 , 'Expected to get 3 rows from doSelect, but got %d. %s' %( len(results), repr(results))
foundOne = False
foundTwo = False
foundThree = False
#_insertIntoTestTable(dbConn, 'one', 'Hello', None)
#_insertIntoTestTable(dbConn, 'two', 'Goodbye', None)
#_insertIntoTestTable(dbConn, 'three', 'Goodbye', 'Some extra data')
for result in results:
assert len(result) == 4 , 'Expected 4 columns in result (id, name, value, extra_data) but got %d. %s' %(len(result), repr(result))
(fetchedId, fetchedName, fetchedValue, fetchedExtraData) = result
if fetchedName == 'one':
foundOne = True
assert fetchedValue == 'Hello' , 'Got mixed up data. Expected name="one" to have value="Hello". Row was: ' + repr(result)
assert fetchedExtraData is None , 'Expected NULL result to have value of None. Got: %s' %(repr(fetchedExtraData), )
elif fetchedName == 'two':
foundTwo = True
assert fetchedValue == 'Goodbye' , 'Got mixed up data. Expected name="two" to have value="Goodbye". Row was: ' + repr(result)
assert fetchedExtraData is None , 'Expected NULL result to have value of None. Got: %s' %(repr(fetchedExtraData), )
elif fetchedName == 'three':
foundThree = True
assert fetchedValue == 'Goodbye' , 'Got mixed up data. Expected name="three" to have value="Goodbye". Row was: ' + repr(result)
assert fetchedExtraData == 'Some extra data' , 'Expected extra data on name="three" to be "Some extra data" but got %s' %(repr(fetchedExtraData), )
else:
raise AssertionError("Got unknown row: %s" %(repr(result), ))
assert foundOne , 'Did not find name="one"'
assert foundTwo , 'Did not find name="two"'
assert foundThree , 'Did not find name="three"'
def test_executeSqlError(self):
'''
test_executeSqlError - Test that errors are correctly raised with executeSql
'''
dbConn = getDatabaseConnection()
gotException = False
try:
dbConn.executeSql('SELECT booooh FIGGLE whop')
except Exception as e:
gotException = e
assert gotException is not False , 'Expected to get exception from executeSql with nonsense query, but did not.'
def _createTestTable(self, dropIfExists=True):
'''
_createTestTable - Create a table for this test
@param dropIfExists <bool> default True - If True, will attempt to drop table first (if exists)
'''
if dropIfExists:
try:
self._dropTestTable()
except:
pass
dbConn = ichorORM.getDatabaseConnection()
dbConn.executeSql('CREATE TABLE ichor_test_conn_table(id serial PRIMARY KEY, name varchar(255) NOT NULL, value varchar(255) NOT NULL, extra_data text NULL)')
def test_deleteWithSelectQuery(self):
'''
test_deleteWithSelectQuery - Test using a SelectQuery within DeleteQuery
'''
dbConn = getDatabaseConnection()
numberPeople = self._getNumberPeople()
assert numberPeople == 5, 'Expected 5 rows in Person model, but got %d' % (
numberPeople, )
delQ = DeleteQuery(MyPersonModel)
delQWhere = delQ.addStage()
subSelQ = SelectQuery(MyPersonModel, selectFields=['id'])
subSelQWhere = subSelQ.addStage()
subSelQWhere.addCondition('first_name', '=', 'John')
delQWhere.addCondition('id', 'in', subSelQ)
gotException = False
try:
delQ.executeDelete()
except Exception as e:
gotException = e
assert gotException is False, "Got unexpected exception trying to delete where first_name = 'John'. %s %s" % (
str(type(gotException)), str(gotException))
numberPeople = self._getNumberPeople()
assert numberPeople == 3, 'Expected 3 rows in Person model after delete, but got %d' % (
numberPeople, )
selQ = SelectQuery(MyPersonModel)
allPeople = selQ.executeGetObjs()
assert len(
allPeople
) == 3, 'Expected 3 results for all remaining people, but got %d. %s' % (
len(allPeople), repr(allPeople))
for person in allPeople:
if person.first_name == 'John':
raise AssertionError(
'Found a person with first_name "John" after deleting all Johns. %s'
% (repr(person), ))
def dropModel(cls):
'''
dropModel - Will drop this model's table
'''
dbConn = getDatabaseConnection()
didDrop = False
# Drop table if it exists
try:
dbConn.executeSql('DROP TABLE %s' % (cls.TABLE_NAME, ))
didDrop = True
except:
pass
return didDrop
def _deleteDataset(self, tableName):
'''
_deleteDataset - Delete all records in a given table which have a field, "datasetuid",
which is generated at the start of this test and is unique to this run-through
@param tableName <str> - The name of the SQL table
'''
try:
dbConn = ichorORM.getDatabaseConnection()
dbConn.executeSql("DELETE FROM %s WHERE datasetUid = '%s'" % (
tableName,
self.datasetUid,
))
except Exception as e:
sys.stderr.write(
'Error deleting all %s objects with dataset uid "%s": %s %s\n'
% (tableName, self.datasetUid, str(type(e)), str(e)))
def _getNumberPeople(self):
'''
_getNumberPeople - Get the number of rows in the Person table matching our dataset uid
'''
dbConn = getDatabaseConnection()
query = "SELECT COUNT(*) FROM %s" % (MyPersonModel.TABLE_NAME, )
results = dbConn.doSelect(query)
assert results, 'Did not get any results for COUNT(*) query'
assert len(
results
) == 1, 'Expected 1 row of results for COUNT(*) query, but got %d. %s' % (
len(results), repr(results))
return int(results[0][0])
def _testCanConnectDatabase():
dbConn = ichorORM.getDatabaseConnection(isTransactionMode=False)
failed = False
try:
results = dbConn.doSelect("SELECT 'Hello World'")
except Exception as e:
failed = True
results = []
if not len(results) or results[0][0] != 'Hello World':
sys.stderr.write("WARNING: Seemed to be able to connect but database could not process command.\n")
failed = True
if failed:
sys.stderr.write('CANNOT connect to database. Make sure you have properly configured LocalConfig.py in the test directory.\n')
return False
return True
def test_executeSql(self):
'''
test_executeSql - Test executing SQL commands
'''
dbConn = ichorORM.getDatabaseConnection()
gotException = False
try:
self._createTestTable(dropIfExists=True)
except Exception as e:
gotException = e
assert gotException is False , 'Expected to be able to create a table with executeSql. Got exception %s: %s' %(str(type(gotException)), str(gotException))
def _insertIntoTestTable(dbConn, name, value, extra_data=None):
'''
_insertIntoTestTable - Insert data into test table
@param name <str> - Name
@param value <str> - value
@param extra_data <None/str> default None - Extra_data field
'''
if extra_data == None:
extraDataVal = 'NULL'
else:
extraDataVal = "'%s'" %(extra_data, )
query = '''INSERT INTO ichor_test_conn_table(name, value, extra_data) VALUES ('%s', '%s', %s)''' %(name, value, extraDataVal)
dbConn.executeSql(query)
gotException = False
try:
_insertIntoTestTable(dbConn, 'one', 'Hello', None)
_insertIntoTestTable(dbConn, 'two', 'Goodbye', None)
_insertIntoTestTable(dbConn, 'three', 'Goodbye', 'Some extra data')
except Exception as e:
gotException = e
assert gotException is False , 'Got exception inserting test data using executeSql: %s %s' %(str(type(gotException)), str(gotException))
def test_executeSqlParams(self):
'''
test_executeSqlParams - Test executing SQL commands with params
'''
dbConn = ichorORM.getDatabaseConnection()
gotException = False
try:
self._createTestTable(dropIfExists=True)
except Exception as e:
gotException = e
assert gotException is False , 'Expected to be able to create a table with executeSql. Got exception %s: %s' %(str(type(gotException)), str(gotException))
def _insertIntoTestTable(dbConn, name, value, extra_data=None):
'''
_insertIntoTestTable - Insert data into test table
@param name <str> - Name
@param value <str> - value
@param extra_data <None/str> default None - Extra_data field
'''
query = '''INSERT INTO ichor_test_conn_table(name, value, extra_data) VALUES (%(name)s, %(value)s, %(extra_data)s)'''
dbConn.executeSqlParams(query, {'name' : name, 'value' : value, 'extra_data' : extra_data })
gotException = False
try:
nextOne = 'one'
_insertIntoTestTable(dbConn, 'one', 'Hello', None)
nextOne = 'two'
_insertIntoTestTable(dbConn, 'two', 'Goodbye', None)
nextOne = 'three'
_insertIntoTestTable(dbConn, 'three', 'Goodbye', 'Some extra data')
except Exception as e:
gotException = e
assert gotException is False , 'Got exception inserting test data name=%s using executeSqlParams: %s %s' %(repr(nextOne), str(type(gotException)), str(gotException))
def test_executeSqlParamsError(self):
'''
test_executeSqlParamsError - Test that errors are correctly raised with executeSqlParams
'''
dbConn = getDatabaseConnection()
gotException = False
try:
dbConn.executeSqlParams('SELECT booooh FIGGLE whop')
except Exception as e:
gotException = e
assert gotException is not False , 'Expected to get exception from executeSqlParams with nonsense query, but did not.'
gotException = False
try:
query = '''INSERT INTO ichor_test_conn_table(name, value, extra_data) VALUES (%(name)s, %(value)s, %(extra_data)s)'''
dbConn.executeSqlParams(query, {'nameX' : name, 'value' : value, 'extra_data' : extra_data })
except Exception as e:
gotException = e
assert gotException is not False , 'Expected to get exception from executeSqlParams with a param that does not match expected values.'
def test_doInsertError(self):
'''
test_doInsertError
'''
dbConn = getDatabaseConnection()
gotException = False
try:
dbConn.doInsert("INSERT blargiety(id, value) VALUES ('1', '2')")
except Exception as e:
gotException = e
assert gotException is not False , 'Expected to get exception from doInsert with nonsense query, but did not.'
gotException = False
try:
query = '''INSERT INTO ichor_test_conn_table(name, value, extra_data) VALUES (%(name)s, %(value)s, %(extra_data)s)'''
dbConn.doInsert(query, valueDicts=[{'nameX' : name, 'value' : value, 'extra_data' : extra_data }])
except Exception as e:
gotException = e
assert gotException is not False , 'Expected to get exception from doInsert with a param that does not match expected values.'
def setup_method(self, meth):
'''
setup_method - Called before every method.
@param meth <builtins.method> - The test method that is about to be executed
'''
self.fullDataSet = []
dbConn = ichorORM.getDatabaseConnection(isTransactionMode=True)
pks = dbConn.doInsert(
"INSERT INTO " + MyPersonModel.TABLE_NAME +
" (first_name, last_name, age, birth_day, birth_month) VALUES ( %(first_name)s, %(last_name)s, %(age)s, %(birth_day)s, %(birth_month)s )",
valueDicts=self.dataSet,
doCommit=False,
returnPk=True)
dbConn.commit()
for i in range(len(self.dataSet)):
self.dataSet[i]['id'] = pks[i]
self.fullDataSet += self.dataSet
if meth in (self.test_filterNull, ):
pks = dbConn.doInsert(
"INSERT INTO " + MyPersonModel.TABLE_NAME +
" (first_name, last_name, age, birth_day, birth_month) VALUES ( %(first_name)s, %(last_name)s, %(age)s, %(birth_day)s, %(birth_month)s )",
valueDicts=self.nullDataSet,
doCommit=False,
returnPk=True)
dbConn.commit()
for i in range(len(self.nullDataSet)):
self.nullDataSet[i]['id'] = pks[i]
self.fullDataSet.append(self.nullDataSet[i])
def test_configApplied(self):
'''
test_configApplied - Test that the config from LocalConfig was applied properly
'''
gotException = False
try:
dbConn = ichorORM.getDatabaseConnection()
except Exception as e:
gotException = e
assert gotException is False , 'Got exception calling getDatabaseConnection: %s %s' %( str(type(gotException)), str(gotException) )
assert dbConn.host == LocalConfig._CONFIG_HOSTNAME , 'Expected LocalConfig._CONFIG_HOSTNAME to be set on database connection. Expected %s but got %s' %( repr(LocalConfig._CONFIG_HOSTNAME), repr(dbConn.host) )
assert dbConn.port == LocalConfig._CONFIG_PORT , 'Expected LocalConfig._CONFIG_PORT to be set on database connection. Expected %s but got %s' %( repr(LocalConfig._CONFIG_PORT), repr(dbConn.port) )
assert dbConn.user == LocalConfig._CONFIG_USERNAME , 'Expected LocalConfig._CONFIG_USERNAME to be set on database connection. Expected %s but got %s' %( repr(LocalConfig._CONFIG_USERNAME), repr(dbConn.user) )
assert dbConn.dbname == LocalConfig._CONFIG_DBNAME , 'Expected LocalConfig._CONFIG_DBNAME to be set on database connection. Expected %s but got %s' %( repr(LocalConfig._CONFIG_DBNAME), repr(dbConn.dbname) )
assert dbConn.password == LocalConfig._CONFIG_PASSWORD , 'Expected LocalConfig._CONFIG_PASSWORD to be set on database connection. Expected %s but got %s' %( repr(LocalConfig._CONFIG_PASSWORD), repr(dbConn.password) )
def setup_method(self, meth):
'''
setup_method - Called prior to each method to perform setup specific to it.
@param meth <built-in method> - The method being tested (compare meth == self.someMethod)
'''
if meth in (self.test_generalUpdate, self.test_updateTransaction,
self.test_updateWithQueryStr):
# self.DEFAULT_PERSON_DATASET - A sample dataset of field -> value for Person model
self.DEFAULT_PERSON_DATASET = [
{
"first_name": "John",
"last_name": "Smith",
"eye_color": "blue",
'age': 35,
"birth_month": 4,
"birth_day": 16
},
{
"first_name": "John",
"last_name": "Doe",
"eye_color": "blue",
'age': 22,
"birth_month": 4,
"birth_day": 26
},
{
"first_name": "Jane",
"last_name": "Doe",
"eye_color": "green",
'age': 19,
"birth_month": 6,
"birth_day": 24
},
{
"first_name": "Bill",
"last_name": "Johnson",
"eye_color": "brown",
'age': 19,
"birth_month": 1,
"birth_day": 30
},
{
"first_name": "Ted",
"last_name": "Karma",
"eye_color": "green",
'age': 29,
"birth_month": 4,
"birth_day": 16
},
]
# Mark the dataset id
for i in range(len(self.DEFAULT_PERSON_DATASET)):
self.DEFAULT_PERSON_DATASET[i]['datasetuid'] = self.datasetUid
# Perform the inserts
dbConn = ichorORM.getDatabaseConnection()
pks = dbConn.doInsert(
query=
"INSERT INTO person ( first_name, last_name, eye_color, age, birth_day, birth_month, datasetuid ) VALUES ( %(first_name)s, %(last_name)s, %(eye_color)s, %(age)s, %(birth_day)s, %(birth_month)s, %(datasetuid)s )",
valueDicts=self.DEFAULT_PERSON_DATASET,
returnPk=True)
self.personIdToDataset = {}
# Add the "id" to each dataset and build ref map
for i in range(len(self.DEFAULT_PERSON_DATASET)):
self.DEFAULT_PERSON_DATASET[i]['id'] = pks[i]
self.personIdToDataset[pks[i]] = self.DEFAULT_PERSON_DATASET[i]
def test_updateWithQueryStr(self):
'''
test_updateWithQueryStr - This will test an update which uses a QueryStr (inline SQL)
'''
# Increment everyone's age by 1
upQ = UpdateQuery(Person)
upQ.setFieldValue('age', QueryStr('age + 1'))
# Apply to all items in this dataset
upQWhere = upQ.addStage()
upQWhere.addCondition('datasetuid', '=', self.datasetUid)
gotException = False
try:
upQ.execute()
except Exception as e:
gotException = e
assert gotException == False, 'Got exception on update of all ages + 1: %s %s' % (
str(type(gotException)), str(gotException))
# Check that the update occured, now that we have committed the update transaction
dbConn = ichorORM.getDatabaseConnection()
results = dbConn.doSelect(
"SELECT id, first_name, last_name, eye_color, age, birth_day, birth_month, datasetuid FROM Person WHERE datasetuid = '%s'"
% (self.datasetUid, ))
assert results, 'Did not get results back from select.'
assert len(results) == len(
self.DEFAULT_PERSON_DATASET
), 'Did not get expected number of results back. Expected %d but got %d' % (
len(self.DEFAULT_PERSON_DATASET), len(results))
for resultRow in results:
(_id, first_name, last_name, eye_color, age, birth_day,
birth_month, datasetuid) = resultRow
myFetchedValues = {
'id': _id,
'first_name': first_name,
'last_name': last_name,
'eye_color': eye_color,
'age': age,
'birth_day': birth_day,
'birth_month': birth_month,
'datasetuid': datasetuid,
}
testAgainstValues = self.personIdToDataset[_id]
for fieldName, fieldValue in myFetchedValues.items():
expectedValue = testAgainstValues[fieldName]
# We incremented age, so check against orig age + 1
if fieldName == 'age':
expectedValue += 1
assert fieldValue == expectedValue, 'Got unexpected value after update incrementing age. On person %s %s field "%s" does not have expected value %s. Fetched value was %s' % (
first_name, last_name, fieldName, repr(expectedValue),
repr(fieldValue))
def test_updateTransaction(self):
'''
test_updateTransaction - Test doing some updates within a transaction
'''
janeOrigValues = [
x for x in self.DEFAULT_PERSON_DATASET
if x['first_name'] == 'Jane' and x['last_name'] == 'Doe'
]
assert len(
janeOrigValues
) == 1, 'Expected to find "Jane Doe" in same set but could not.'
janeOrigValues = janeOrigValues[0]
janeId = janeOrigValues['id']
johnSmithOrigValues = [
x for x in self.DEFAULT_PERSON_DATASET
if x['first_name'] == 'John' and x['last_name'] == 'Smith'
]
assert len(
johnSmithOrigValues
) == 1, 'Expected to find "John Smith" in same set but could not.'
johnSmithOrigValues = johnSmithOrigValues[0]
johnSmithId = johnSmithOrigValues['id']
dbConnTrans = ichorORM.getDatabaseConnection(isTransactionMode=True)
# Add one update query to transaction for jane
upQ = UpdateQuery(Person)
upQ.setFieldValue('age', 21)
upQ.setFieldValue('eye_color', 'red')
upQWhere = upQ.addStage()
upQWhere.addCondition('id', '=', janeId)
gotException = False
try:
upQ.execute(dbConn=dbConnTrans, doCommit=False)
except Exception as e:
gotException = e
assert gotException == False, 'Got exception on update of Jane Doe: %s %s' % (
str(type(gotException)), str(gotException))
# Do another update query for John in same transaction
upQ = UpdateQuery(Person)
upQ.setFieldValue('age', 25)
upQ.setFieldValue('eye_color', 'purple')
upQWhere = upQ.addStage()
upQWhere.addCondition('first_name', '=', 'John')
upQWhere.addCondition('last_name', '=', 'Smith')
upQWhere.addCondition('datasetuid', '=', self.datasetUid)
gotException = False
try:
upQ.execute(dbConn=dbConnTrans, doCommit=False)
except Exception as e:
gotException = e
assert gotException == False, 'Got exception on update of John Smith: %s %s' % (
str(type(gotException)), str(gotException))
# We have not yet committed, so make sure no changes took place
dbConn = ichorORM.getDatabaseConnection()
results = dbConn.doSelect(
"SELECT id, first_name, last_name, eye_color, age, birth_day, birth_month, datasetuid FROM Person WHERE datasetuid = '%s'"
% (self.datasetUid, ))
assert results, 'Did not get results back from select.'
assert len(results) == len(
self.DEFAULT_PERSON_DATASET
), 'Did not get expected number of results back. Expected %d but got %d' % (
len(self.DEFAULT_PERSON_DATASET), len(results))
for resultRow in results:
(_id, first_name, last_name, eye_color, age, birth_day,
birth_month, datasetuid) = resultRow
myFetchedValues = {
'id': _id,
'first_name': first_name,
'last_name': last_name,
'eye_color': eye_color,
'age': age,
'birth_day': birth_day,
'birth_month': birth_month,
'datasetuid': datasetuid,
}
testAgainstValues = self.personIdToDataset[_id]
for fieldName, fieldValue in myFetchedValues.items():
expectedValue = testAgainstValues[fieldName]
assert fieldValue == expectedValue, 'Got unexpected value before transaction commit. On person %s %s field "%s" does not have expected value %s. Fetched value was %s' % (
first_name, last_name, fieldName, repr(expectedValue),
repr(fieldValue))
gotException = False
try:
dbConnTrans.commit()
except Exception as e:
gotException = e
assert gotException == False, 'Got exception on transaction commit. %s %s' % (
str(type(e)), str(e))
# Check that the update occured, now that we have committed the update transaction
dbConn = ichorORM.getDatabaseConnection()
results = dbConn.doSelect(
"SELECT id, first_name, last_name, eye_color, age, birth_day, birth_month, datasetuid FROM Person WHERE datasetuid = '%s'"
% (self.datasetUid, ))
assert results, 'Did not get results back from select.'
assert len(results) == len(
self.DEFAULT_PERSON_DATASET
), 'Did not get expected number of results back. Expected %d but got %d' % (
len(self.DEFAULT_PERSON_DATASET), len(results))
for resultRow in results:
(_id, first_name, last_name, eye_color, age, birth_day,
birth_month, datasetuid) = resultRow
myFetchedValues = {
'id': _id,
'first_name': first_name,
'last_name': last_name,
'eye_color': eye_color,
'age': age,
'birth_day': birth_day,
'birth_month': birth_month,
'datasetuid': datasetuid,
}
if _id == janeId:
# Update our sample with the new fields
janeNewValues = copy.deepcopy(janeOrigValues)
janeNewValues['age'] = 21
janeNewValues['eye_color'] = 'red'
testAgainstValues = janeNewValues
elif _id == johnSmithId:
johnSmithNewValues = copy.deepcopy(johnSmithOrigValues)
johnSmithNewValues['age'] = 25
johnSmithNewValues['eye_color'] = 'purple'
testAgainstValues = johnSmithNewValues
else:
testAgainstValues = self.personIdToDataset[_id]
for fieldName, fieldValue in myFetchedValues.items():
expectedValue = testAgainstValues[fieldName]
assert fieldValue == expectedValue, 'Got unexpected value after update transaction commit. On person %s %s field "%s" does not have expected value %s. Fetched value was %s' % (
first_name, last_name, fieldName, repr(expectedValue),
repr(fieldValue))
def test_generalUpdate(self):
janeOrigValues = [
x for x in self.DEFAULT_PERSON_DATASET
if x['first_name'] == 'Jane' and x['last_name'] == 'Doe'
]
assert len(
janeOrigValues
) == 1, 'Expected to find "Jane Doe" in same set but could not.'
janeOrigValues = janeOrigValues[0]
janeId = janeOrigValues['id']
upQ = UpdateQuery(Person)
upQ.setFieldValue('age', 21)
upQ.setFieldValue('eye_color', 'red')
upQWhere = upQ.addStage()
upQWhere.addCondition('id', '=', janeId)
gotException = False
try:
upQ.execute()
except Exception as e:
gotException = e
assert gotException == False, 'Got exception on update: %s %s' % (str(
type(gotException)), str(gotException))
# Check that the update occured
dbConn = ichorORM.getDatabaseConnection()
results = dbConn.doSelect(
"SELECT id, first_name, last_name, eye_color, age, birth_day, birth_month, datasetuid FROM Person WHERE datasetuid = '%s'"
% (self.datasetUid, ))
assert results, 'Did not get results back from select.'
assert len(results) == len(
self.DEFAULT_PERSON_DATASET
), 'Did not get expected number of results back. Expected %d but got %d' % (
len(self.DEFAULT_PERSON_DATASET), len(results))
for resultRow in results:
(_id, first_name, last_name, eye_color, age, birth_day,
birth_month, datasetuid) = resultRow
myFetchedValues = {
'id': _id,
'first_name': first_name,
'last_name': last_name,
'eye_color': eye_color,
'age': age,
'birth_day': birth_day,
'birth_month': birth_month,
'datasetuid': datasetuid,
}
if _id == janeId:
# Update our sample with the new fields
janeNewValues = copy.deepcopy(janeOrigValues)
janeNewValues['age'] = 21
janeNewValues['eye_color'] = 'red'
testAgainstValues = janeNewValues
else:
testAgainstValues = self.personIdToDataset[_id]
for fieldName, fieldValue in myFetchedValues.items():
expectedValue = testAgainstValues[fieldName]
assert fieldValue == expectedValue, 'Got unexpected value after Jane Doe update. On person %s %s field "%s" does not have expected value %s. Fetched value was %s' % (
first_name, last_name, fieldName, repr(expectedValue),
repr(fieldValue))
def setup_method(self, meth):
'''
setup_method - Called prior to each method to perform setup specific to it.
@param meth <built-in method> - The method being tested (compare meth == self.someMethod)
'''
if meth in (self.test_generalGetMapping, self.test_generalGetDictObjs,
self.test_tableStarSelectFields):
# self.DEFAULT_PERSON_DATASET - A sample dataset of field -> value for Person model
self.DEFAULT_PERSON_DATASET = [
{
"id": None,
"first_name": "John",
"last_name": "Smith",
"eye_color": "blue",
'age': 35,
"birth_month": 4,
"birth_day": 16
},
{
"id": None,
"first_name": "John",
"last_name": "Doe",
"eye_color": "blue",
'age': 22,
"birth_month": 4,
"birth_day": 26
},
{
"id": None,
"first_name": "Jane",
"last_name": "Doe",
"eye_color": "green",
'age': 19,
"birth_month": 6,
"birth_day": 24
},
{
"id": None,
"first_name": "Bill",
"last_name": "Johnson",
"eye_color": "brown",
'age': 19,
"birth_month": 1,
"birth_day": 30
},
{
"id": None,
"first_name": "Ted",
"last_name": "Karma",
"eye_color": "green",
'age': 29,
"birth_month": 4,
"birth_day": 16
},
]
# Mark the dataset id
for i in range(len(self.DEFAULT_PERSON_DATASET)):
self.DEFAULT_PERSON_DATASET[i]['datasetuid'] = self.datasetUid
dbConn = ichorORM.getDatabaseConnection(isTransactionMode=True)
pks = dbConn.doInsert(
query="INSERT INTO " + Person.TABLE_NAME +
" (first_name, last_name, eye_color, age, birth_month, birth_day, datasetuid) VALUES ( %(first_name)s, %(last_name)s, %(eye_color)s, %(age)s, %(birth_month)s, %(birth_day)s, %(datasetuid)s )",
valueDicts=self.DEFAULT_PERSON_DATASET,
doCommit=False)
dbConn.commit()
self.personIdToData = {}
for i in range(len(pks)):
self.DEFAULT_PERSON_DATASET[i]['id'] = pks[i]
self.personIdToData[pks[i]] = self.DEFAULT_PERSON_DATASET[i]
#FIELDS = ['id', 'food_group', 'item_name', 'price', 'id_person', 'datasetuid']
def getMealForPerson(mealDict, personIdx):
mealDict = copy.deepcopy(mealDict)
mealDict['id_person'] = self.DEFAULT_PERSON_DATASET[personIdx][
'id']
return mealDict
MEAL_ICE_CREAM = { \
"id" : None, "food_group" : "desert", "item_name" : "ice cream",
"price" : 3.99, "id_person" : None, "datasetuid" : None,
}
MEAL_PIZZA = { \
"id" : None, "food_group" : "junk", "item_name" : "pizza",
"price" : 9.99, "id_person" : None, "datasetuid" : None,
}
MEAL_MILK = { \
"id" : None, "food_group" : "dairy", "item_name" : "milk",
"price" : 4.20, "id_person" : None, "datasetuid" : None,
}
self.DEFAULT_MEAL_DATASET = [
getMealForPerson(MEAL_ICE_CREAM, 0),
getMealForPerson(MEAL_ICE_CREAM, 1),
getMealForPerson(MEAL_ICE_CREAM, 2),
getMealForPerson(MEAL_ICE_CREAM, 3),
getMealForPerson(MEAL_ICE_CREAM, 4),
getMealForPerson(MEAL_PIZZA, 2),
getMealForPerson(MEAL_PIZZA, 4),
getMealForPerson(MEAL_MILK, 0),
getMealForPerson(MEAL_MILK, 1),
getMealForPerson(MEAL_MILK, 2),
]
for i in range(len(self.DEFAULT_MEAL_DATASET)):
self.DEFAULT_MEAL_DATASET[i]['datasetuid'] = self.datasetUid
dbConn = ichorORM.getDatabaseConnection(isTransactionMode=True)
pks = dbConn.doInsert(
query="INSERT INTO " + Meal.TABLE_NAME +
" (food_group, item_name, price, id_person, datasetuid) VALUES ( %(food_group)s, %(item_name)s, %(price)s, %(id_person)s, %(datasetuid)s )",
valueDicts=self.DEFAULT_MEAL_DATASET,
doCommit=False)
dbConn.commit()
self.mealIdToData = {}
for i in range(len(pks)):
self.DEFAULT_MEAL_DATASET[i]['id'] = pks[i]
self.mealIdToData[pks[i]] = self.DEFAULT_MEAL_DATASET[i]
def test_doSelectFromDoInsertTrans(self):
'''
test_doSelectFromDoInsertTrans - Test the doSelect method (using the doInsert insert with transaction)
'''
# Perform inserts and such here
try:
self.test_doInsertTransaction()
except AssertionError as e:
raise AssertionError('Unable to run test_doSelectFromDoInsertTrans because test_doInsertTransaction() failed.')
dbConn = getDatabaseConnection()
query = 'SELECT id, name, value, extra_data FROM ichor_test_conn_table'
results = dbConn.doSelect(query)
assert results , 'Expected to get results from select query, but did not.'
assert len(results) == 4 , 'Expected to get 4 rows from doSelect, but got %d. %s' %( len(results), repr(results))
foundOne = False
foundTwo = False
foundThree = False
foundFour = False
for result in results:
assert len(result) == 4 , 'Expected 4 columns in result (id, name, value, extra_data) but got %d. %s' %(len(result), repr(result))
(fetchedId, fetchedName, fetchedValue, fetchedExtraData) = result
if fetchedName == 'one':
foundOne = True
assert fetchedValue == 'Hello' , 'Got mixed up data. Expected name="one" to have value="Hello". Row was: ' + repr(result)
assert fetchedExtraData is None , 'Expected NULL result to have value of None. Got: %s' %(repr(fetchedExtraData), )
assert fetchedId == self.nameToPk['one'] , 'Expected pk for name="one" to match the one returned by doInsert. Got %s but expected %s' %( repr(fetchedId), repr(self.nameToPk['one']))
elif fetchedName == 'two':
foundTwo = True
assert fetchedValue == 'Goodbye' , 'Got mixed up data. Expected name="two" to have value="Goodbye". Row was: ' + repr(result)
assert fetchedExtraData is None , 'Expected NULL result to have value of None. Got: %s' %(repr(fetchedExtraData), )
assert fetchedId == self.nameToPk['two'] , 'Expected pk for name="two" to match the two returned by doInsert. Got %s but expected %s' %( repr(fetchedId), repr(self.nameToPk['two']))
elif fetchedName == 'three':
foundThree = True
assert fetchedValue == 'Goodbye' , 'Got mixed up data. Expected name="three" to have value="Goodbye". Row was: ' + repr(result)
assert fetchedExtraData == 'Some extra data' , 'Expected extra data on name="three" to be "Some extra data" but got %s' %(repr(fetchedExtraData), )
assert fetchedId == self.nameToPk['three'] , 'Expected pk for name="three" to match the three returned by doInsert. Got %s but expected %s' %( repr(fetchedId), repr(self.nameToPk['three']))
elif fetchedName == 'four':
foundFour = True
assert fetchedValue == 'Cheese' , 'Got mixed up data. Expected name="four" to have value="Cheese". Row was: ' + repr(result)
assert fetchedExtraData == 'Yummy yum yum' , 'Expected extra data on name="four" to be "Yummy yum yum" but got %s' %(repr(fetchedExtraData), )
assert fetchedId == self.nameToPk['four'] , 'Expected pk for name="four" to match the four returned by doInsert. Got %s but expected %s' %( repr(fetchedId), repr(self.nameToPk['four']))
else:
raise AssertionError("Got unknown row: %s" %(repr(result), ))
assert foundOne , 'Did not find name="one"'
assert foundTwo , 'Did not find name="two"'
assert foundThree , 'Did not find name="three"'
assert foundFour , 'Did not find name="four"'
def test_doInsertTransaction(self):
'''
test_doInsertTransaction - Test insert via doInsert using a transaction
'''
self.nameToPk = {}
dbConn = ichorORM.getDatabaseConnection(isTransactionMode=True)
gotException = False
try:
self._createTestTable(dropIfExists=True)
except Exception as e:
gotException = e
assert gotException is False , 'Expected to be able to create a table with executeSql. Got exception %s: %s' %(str(type(gotException)), str(gotException))
# Insert with one valueDict
queryParams = '''INSERT INTO ichor_test_conn_table(name, value, extra_data) VALUES (%(name)s, %(value)s, %(extra_data)s)'''
gotException = False
try:
pks = dbConn.doInsert(queryParams, valueDicts=[{'name' : 'one', 'value' : 'Hello', 'extra_data' : None }], returnPk=True, doCommit=False)
except Exception as e:
gotException = e
assert gotException is False , 'Got unexpected exception from doInsert with transaction: %s %s' %(str(type(gotException)), str(gotException))
assert pks and len(pks) == 1 and pks[0] , 'Expected to get primary key back from doInsert, but did not.'
self.nameToPk['one'] = pks[0]
# insert with inline values and no valueDict
queryInline = '''INSERT INTO ichor_test_conn_table(name, value, extra_data) VALUES ('two', 'Goodbye', NULL)'''
gotException = False
try:
pks = dbConn.doInsert(queryInline, returnPk=True, doCommit=False)
except Exception as e:
gotException = e
assert gotException is False , 'Got unexpected exception from doInsert with transaction: %s %s' %(str(type(gotException)), str(gotException))
assert pks and len(pks) == 1 and pks[0] , 'Expected to get primary key back from doInsert, but did not.'
self.nameToPk['two'] = pks[0]
# insert multiple valueDicts
valueDicts = [
{ 'name' : 'three', 'value' : 'Goodbye', 'extra_data' : 'Some extra data' },
{ 'name' : 'four', 'value' : 'Cheese', 'extra_data' : 'Yummy yum yum'},
]
gotException = False
try:
pks = dbConn.doInsert(queryParams, valueDicts=valueDicts, returnPk=True, doCommit=False)
except Exception as e:
gotException = e
assert gotException is False , 'Got unexpected exception from doInsert with transaction: %s %s' %(str(type(gotException)), str(gotException))
assert pks , 'Expected to get primary keys back from doInsert with transaction, but did not.'
assert len(pks) == 2 , 'Expected to get 2 primary keys back from insert with 2 rows, but got %d %s' %(len(pks), repr(pks))
assert len([x for x in pks if x]) == 2, 'Expected to have primary keys set, but got empty/missing values. Got: ' + repr(pks)
self.nameToPk['three'] = pks[0]
self.nameToPk['four'] = pks[1]
# Verify that there are no values because we have not committed
dbConn2 = getDatabaseConnection()
countResults = dbConn2.doSelect('SELECT count(*) FROM ichor_test_conn_table')
assert countResults , 'Did not get any return from SELECT count(*) query'
assert len(countResults) == 1 , 'Expected count(*) query to return 1 row, but got %d. %s' %(len(countResults), repr(countResults))
assert countResults[0][0] == 0 , 'Expected no rows to be present before commit with doInsert using transaction. count(*) returned %s' %(repr(countResults), )
# commit
gotException = False
try:
dbConn.commit()
except Exception as e:
gotException = e
# TODO: Also test error on commit
assert gotException is False , 'Got an error trying to commit transaction: %s %s' %(str(type(gotException)), str(gotException))
# Count should now be 4
countResults = dbConn2.doSelect('SELECT count(*) FROM ichor_test_conn_table')
assert countResults , 'Did not get any return from SELECT count(*) query'
assert len(countResults) == 1 , 'Expected count(*) query to return 1 row, but got %d. %s' %(len(countResults), repr(countResults))
assert countResults[0][0] == 4 , 'Expected 4 rows to be present after commit with doInsert using transaction. count(*) returned %s' %(repr(countResults), )
def test_doInsert(self):
'''
test_doInsert - Test insert via doInsert
'''
self.nameToPk = {}
dbConn = ichorORM.getDatabaseConnection()
gotException = False
try:
self._createTestTable(dropIfExists=True)
except Exception as e:
gotException = e
assert gotException is False , 'Expected to be able to create a table with executeSql. Got exception %s: %s' %(str(type(gotException)), str(gotException))
# Insert with one valueDict
queryParams = '''INSERT INTO ichor_test_conn_table(name, value, extra_data) VALUES (%(name)s, %(value)s, %(extra_data)s)'''
gotException = False
try:
pks = dbConn.doInsert(queryParams, valueDicts=[{'name' : 'one', 'value' : 'Hello', 'extra_data' : None }], returnPk=True)
except Exception as e:
gotException = e
assert gotException is False , 'Got unexpected exception from doInsert: %s %s' %(str(type(gotException)), str(gotException))
assert pks and len(pks) == 1 and pks[0] , 'Expected to get primary key back from doInsert, but did not.'
self.nameToPk['one'] = pks[0]
# insert with inline values and no valueDict
queryInline = '''INSERT INTO ichor_test_conn_table(name, value, extra_data) VALUES ('two', 'Goodbye', NULL)'''
gotException = False
try:
pks = dbConn.doInsert(queryInline, returnPk=True)
except Exception as e:
gotException = e
assert gotException is False , 'Got unexpected exception from doInsert: %s %s' %(str(type(gotException)), str(gotException))
assert pks and len(pks) == 1 and pks[0] , 'Expected to get primary key back from doInsert, but did not.'
self.nameToPk['two'] = pks[0]
# insert multiple valueDicts
valueDicts = [
{ 'name' : 'three', 'value' : 'Goodbye', 'extra_data' : 'Some extra data' },
{ 'name' : 'four', 'value' : 'Cheese', 'extra_data' : 'Yummy yum yum'},
]
gotException = False
try:
pks = dbConn.doInsert(queryParams, valueDicts=valueDicts, returnPk=True)
except Exception as e:
gotException = e
assert gotException is False , 'Got unexpected exception from doInsert: %s %s' %(str(type(gotException)), str(gotException))
assert pks , 'Expected to get primary keys back from doInsert, but did not.'
assert len(pks) == 2 , 'Expected to get 2 primary keys back from insert with 2 rows, but got %d %s' %(len(pks), repr(pks))
assert len([x for x in pks if x]) == 2, 'Expected to have primary keys set, but got empty/missing values. Got: ' + repr(pks)
self.nameToPk['three'] = pks[0]
self.nameToPk['four'] = pks[1]
