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]