def __schemaCreate(self, schemaDefObj):
"""Test case - create table schema using schema definition"""
startTime = time.time()
self.__lfh.write("\nStarting SchemaDefLoaderDbTest __schemaCreate at %s\n" % time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
try:
tableIdList = schemaDefObj.getTableIdList()
sqlGen = MyDbAdminSqlGen(self.__verbose, self.__lfh)
sqlL = []
for tableId in tableIdList:
tableDefObj = schemaDefObj.getTable(tableId)
sqlL.extend(sqlGen.createTableSQL(databaseName=schemaDefObj.getDatabaseName(), tableDefObj=tableDefObj))
if self.__debug:
self.__lfh.write("\nSchema creation SQL string\n %s\n\n" % "\n".join(sqlL))
myQ = MyDbQuery(dbcon=self.__dbCon, verbose=self.__verbose, log=self.__lfh)
#
# Permit warnings to support "drop table if exists" for missing tables.
#
myQ.setWarning("default")
ret = myQ.sqlCommand(sqlCommandList=sqlL)
if self.__verbose:
self.__lfh.write("\n\n+INFO mysql server returns %r\n" % ret)
except: # noqa: E722 pylint: disable=bare-except
traceback.print_exc(file=self.__lfh)
self.fail()
endTime = time.time()
self.__lfh.write("\nCompleted SchemaDefLoaderDbTest __schemaCreate at %s (%.2f seconds)\n" % (time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - startTime))
def testBirdBatchInsertImport(self):
"""Test case - import loadable data via SQL inserts -"""
startTime = time.time()
self.__lfh.write(
"\nStarting BirdLoaderTests testBirdBatchInsertImport at %s\n" %
time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
try:
self.testPrdPathList()
bsd = BirdSchemaDef()
sml = SchemaDefLoader(schemaDefObj=bsd,
verbose=self.__verbose,
log=self.__lfh)
self.__lfh.write("Length of path list %d\n" %
len(self.__loadPathList))
#
tableDataDict, _containerNameList = sml.fetch(self.__loadPathList)
databaseName = bsd.getDatabaseName()
tableIdList = bsd.getTableIdList()
myQ = MyDbQuery(dbcon=self.__dbCon,
verbose=self.__verbose,
log=self.__lfh)
myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh)
#
for tableId in tableIdList:
tableDefObj = bsd.getTable(tableId)
tableName = tableDefObj.getName()
tableAttributeIdList = tableDefObj.getAttributeIdList()
tableAttributeNameList = tableDefObj.getAttributeNameList()
sqlL = []
if tableId in tableDataDict:
rowList = tableDataDict[tableId]
for row in rowList:
vList = []
aList = []
for tid, nm in zip(tableAttributeIdList,
tableAttributeNameList):
if len(row[tid]) > 0 and row[id] != r"\N":
vList.append(row[tid])
aList.append(nm)
sqlL.append(
(myAd.insertTemplateSQL(databaseName, tableName,
aList), vList))
ok = myQ.sqlBatchTemplateCommand(sqlL)
if self.__verbose and not ok:
# self.__lfh.write("\n\n+ERROR batch insert fails for table %s row %r\n" % (tableName,sqlL))
pass
except: # noqa: E722 pylint: disable=bare-except
traceback.print_exc(file=self.__lfh)
self.fail()
endTime = time.time()
self.__lfh.write(
"\nCompleted BirdLoaderTests testBirdBatchInsertImport at %s (%.2f seconds)\n"
% (time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime))
def __getSqlDeleteList(self,
tableId,
containerNameList=None,
deleteOpt="all"):
"""Return the SQL delete commands for the input table and container name list."""
databaseName = self.__sD.getDatabaseName()
sqlGen = MyDbAdminSqlGen(self.__verbose, self.__lfh)
databaseName = self.__sD.getDatabaseName()
tableDefObj = self.__sD.getTable(tableId)
tableName = tableDefObj.getName()
sqlDeleteList = []
if deleteOpt in ["selected", "delete"
] and containerNameList is not None:
deleteAttributeName = tableDefObj.getDeleteAttributeName()
sqlDeleteList = sqlGen.deleteFromListSQL(databaseName,
tableName,
deleteAttributeName,
containerNameList,
chunkSize=50)
elif deleteOpt in ["all", "truncate"]:
sqlDeleteList = [sqlGen.truncateTableSQL(databaseName, tableName)]
if self.__verbose:
self.__lfh.write(
"+SchemaDefLoader(__getSqlDeleteList) delete SQL for %s : %r\n"
% (tableId, sqlDeleteList))
return sqlDeleteList
def testMessageSchemaCreate(self):
"""Test case - create table schema using message schema definition as an example"""
startTime = time.time()
self.__lfh.write(
"\nStarting MyDbSqlGenTests testMessageSchemaCreate at %s\n" %
time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
try:
msd = MessageSchemaDef(verbose=self.__verbose, log=self.__lfh)
tableIdList = msd.getTableIdList()
myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh)
sqlL = []
for tableId in tableIdList:
tableDefObj = msd.getTable(tableId)
sqlL.extend(
myAd.createTableSQL(databaseName=msd.getDatabaseName(),
tableDefObj=tableDefObj))
if self.__verbose: # pragma: no cover
self.__lfh.write(
"\n\n+MyDbSqlGenTests table creation SQL string\n %s\n\n"
% "\n".join(sqlL))
except: # noqa: E722 pylint: disable=bare-except # pragma: no cover
traceback.print_exc(file=self.__lfh)
self.fail()
endTime = time.time()
self.__lfh.write(
"\nCompleted MyDbSqlGenTests testMessageSchemaCreate at %s (%d seconds)\n"
% (time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime))
def _deleteRequest(self, tableId, **kwargs):
"""Delete from input table records identified by the keyword value pairs provided as input arguments -"""
startTime = time.time()
if self.__debug:
logger.debug("Starting _deleteRequest at %s",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
ret = False
try:
iOpened = False
if self.__dbCon is None:
self._open()
iOpened = True
# tableName = self.__sd.getTableName(tableId)
tableDefObj = self.__sd.getTable(tableId)
#
#
myQ = MyDbQuery(dbcon=self.__dbCon,
verbose=self.__verbose,
log=self.__lfh)
myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh)
#
# Create the attribute and value list for template --
#
vList = []
aList = []
for atId, kwId in self._getAttributeParameterMap(tableId):
if kwId in kwargs and kwargs[kwId] is not None:
vList.append(kwargs[kwId])
aList.append(atId)
sqlT = myAd.idDeleteTemplateSQL(self.__databaseName,
tableDefObj,
conditionAttributeIdList=aList)
if self.__debug:
logger.debug("_deleteRequest delete sql: %s", sqlT)
logger.debug("_deleteReuqest delete values: %r", vList)
ret = myQ.sqlTemplateCommand(sqlTemplate=sqlT, valueList=vList)
if iOpened:
self._close()
except Exception as e:
status = " delete operation error " + str(e)
logger.error("_deleteRequest %s", status)
if self.__verbose:
logger.exception("In _deleteRequest")
if self.__debug:
endTime = time.time()
logger.debug("Completed _deleteRequest at %s (%.3f seconds)",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()),
endTime - startTime)
return ret
def testBirdBatchImport(self):
"""Test case - import loadable files"""
startTime = time.time()
self.__lfh.write(
"\nStarting BirdLoaderTests testBirdBatchImport at %s\n" %
time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
try:
bsd = BirdSchemaDef(verbose=self.__verbose, log=self.__lfh)
databaseName = bsd.getDatabaseName()
tableIdList = bsd.getTableIdList()
myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh)
for tableId in tableIdList:
fn = tableId + "-loadable.tdd"
if os.access(fn, os.F_OK):
self.__lfh.write("+INFO - Found for %s\n" % fn)
tableDefObj = bsd.getTable(tableId)
sqlImport = myAd.importTable(databaseName,
tableDefObj,
importPath=fn,
withTruncate=True)
if self.__verbose:
self.__lfh.write(
"\n\n+MyDbSqlGenTests table import SQL string\n %s\n\n"
% sqlImport)
#
lfn = tableId + "-load.sql"
ofh = open(lfn, "w")
ofh.write("%s\n" % sqlImport)
ofh.close()
#
myQ = MyDbQuery(dbcon=self.__dbCon,
verbose=self.__verbose,
log=self.__lfh)
myQ.setWarning("error")
ret = myQ.sqlCommand(sqlCommandList=[sqlImport])
if self.__verbose:
self.__lfh.write(
"\n\n+INFO mysql server returns %r\n" % ret)
except: # noqa: E722 pylint: disable=bare-except
traceback.print_exc(file=self.__lfh)
self.fail()
endTime = time.time()
self.__lfh.write(
"\nCompleted BirdLoaderTests testBirdBatchImport at %s (%.2f seconds)\n"
% (time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime))
def _createSchema(self):
"""Create table schema using the current class schema definition"""
if self.__debug:
startTime = time.time()
logger.debug("Starting _createSchema at %s",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
ret = False
try:
iOpened = False
if self.__dbCon is None:
self._open()
iOpened = True
#
tableIdList = self.__sd.getTableIdList()
myQ = MyDbQuery(dbcon=self.__dbCon,
verbose=self.__verbose,
log=self.__lfh)
myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh)
for tableId in tableIdList:
sqlL = []
tableDefObj = self.__sd.getTable(tableId)
sqlL.extend(
myAd.createTableSQL(databaseName=self.__databaseName,
tableDefObj=tableDefObj))
ret = myQ.sqlCommand(sqlCommandList=sqlL)
if self.__verbose:
logger.info("for tableId %s server returns: %s", tableId,
ret)
if self.__debug:
logger.debug("SQL: %s", "\n".join(sqlL))
if iOpened:
self._close()
except Exception as e:
status = " table create error " + str(e)
logger.error("%s", status)
if self.__verbose:
logger.exception("_createSchema")
if self.__debug:
endTime = time.time()
logger.debug("Completed at %s (%.3f seconds)",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()),
endTime - startTime)
return ret
def __schemaCreateSQL(self, schemaDefObj):
"""Test case - create table schema using schema definition"""
startTime = time.time()
self.__lfh.write("\nStarting SchemaDefLoaderDbTest __schemaCreateSQL at %s\n" % time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
try:
tableIdList = schemaDefObj.getTableIdList()
sqlGen = MyDbAdminSqlGen(self.__verbose, self.__lfh)
sqlL = []
for tableId in tableIdList:
tableDefObj = schemaDefObj.getTable(tableId)
sqlL.extend(sqlGen.createTableSQL(databaseName=schemaDefObj.getDatabaseName(), tableDefObj=tableDefObj))
self.__lfh.write("\nSchema creation SQL string\n %s\n\n" % "\n".join(sqlL))
except: # noqa: E722 pylint: disable=bare-except
traceback.print_exc(file=self.__lfh)
self.fail()
endTime = time.time()
self.__lfh.write("\nCompleted SchemaDefLoaderDbTest __schemaCreateSQL at %s (%.2f seconds)\n" % (time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - startTime))
def testMessageImportExport(self):
"""Test case - import and export commands --"""
startTime = time.time()
self.__lfh.write(
"\nStarting MyDbSqlGenTests testMessageImportExport at %s\n" %
time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
try:
msd = MessageSchemaDef(verbose=self.__verbose, log=self.__lfh)
databaseName = msd.getDatabaseName()
tableIdList = msd.getTableIdList()
myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh)
for tableId in tableIdList:
tableDefObj = msd.getTable(tableId)
exportPath = os.path.join(TESTOUTPUT,
tableDefObj.getName() + ".tdd")
sqlExport = myAd.exportTable(databaseName,
tableDefObj,
exportPath=exportPath)
if self.__verbose: # pragma: no cover
self.__lfh.write(
"\n\n+MyDbSqlGenTests table export SQL string\n %s\n\n"
% sqlExport)
sqlImport = myAd.importTable(databaseName,
tableDefObj,
importPath=exportPath)
if self.__verbose: # pragma: no cover
self.__lfh.write(
"\n\n+MyDbSqlGenTests table import SQL string\n %s\n\n"
% sqlImport)
except: # noqa: E722 pylint: disable=bare-except # pragma: no cover
traceback.print_exc(file=self.__lfh)
self.fail()
endTime = time.time()
self.__lfh.write(
"\nCompleted MyDbSqlGenTests testMessageImportExport at %s (%d seconds)\n"
% (time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime))
def testStatusHistorySchemaCreate(self):
"""Test case - create table schema using status history schema definition"""
startTime = time.time()
self.__lfh.write(
"\nStarting StatusHistoryLoaderTests testStatusHistorySchemaCreate at %s\n"
% time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
try:
msd = StatusHistorySchemaDef(verbose=self.__verbose,
log=self.__lfh)
tableIdList = msd.getTableIdList()
myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh)
sqlL = []
for tableId in tableIdList:
tableDefObj = msd.getTable(tableId)
sqlL.extend(
myAd.createTableSQL(databaseName=self.__databaseName,
tableDefObj=tableDefObj))
if self.__verbose:
self.__lfh.write(
"\n\n+Status history table creation SQL string\n %s\n\n" %
"\n".join(sqlL))
myQ = MyDbQuery(dbcon=self.__dbCon,
verbose=self.__verbose,
log=self.__lfh)
ret = myQ.sqlCommand(sqlCommandList=sqlL)
if self.__verbose:
self.__lfh.write("\n\n+INFO mysql server returns %r\n" % ret)
except: # noqa: E722 pylint: disable=bare-except
traceback.print_exc(file=self.__lfh)
self.fail()
endTime = time.time()
self.__lfh.write(
"\nCompleted StatusHistoryLoaderTests testStatusHistorySchemaCreate at %s (%.2f seconds)\n"
% (time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime))
def __batchInsertImport(self,
tableId,
rowList=None,
containerNameList=None,
deleteOpt="selected"):
"""Load the input table using bacth inserts of the input list of dictionaries (i.e. d[attributeId]=value).
The containerNameList corresponding to the data within loadable data in rowList can be provided
if 'selected' deletions are to performed prior to the the batch data inserts.
deleteOpt = ['selected','all'] where 'selected' deletes rows corresponding to the input container
list before insert. The 'all' options truncates the table prior to insert.
Deletions are performed in the absence of loadable data.
"""
startTime = time.time()
myQ = MyDbQuery(dbcon=self.__dbCon,
verbose=self.__verbose,
log=self.__lfh)
myQ.setWarning(self.__warningAction)
sqlGen = MyDbAdminSqlGen(self.__verbose, self.__lfh)
#
databaseName = self.__sD.getDatabaseName()
tableDefObj = self.__sD.getTable(tableId)
tableName = tableDefObj.getName()
tableAttributeIdList = tableDefObj.getAttributeIdList()
tableAttributeNameList = tableDefObj.getAttributeNameList()
#
sqlDeleteList = None
if deleteOpt in ["selected", "delete"
] and containerNameList is not None:
deleteAttributeName = tableDefObj.getDeleteAttributeName()
sqlDeleteList = sqlGen.deleteFromListSQL(databaseName,
tableName,
deleteAttributeName,
containerNameList,
chunkSize=10)
if self.__verbose:
self.__lfh.write(
"+SchemaDefLoader(batchInsertImport) delete SQL for %s : %r\n"
% (tableId, sqlDeleteList))
elif deleteOpt in ["all", "truncate"]:
sqlDeleteList = [sqlGen.truncateTableSQL(databaseName, tableName)]
sqlInsertList = []
for row in rowList:
vList = []
aList = []
for tid, nm in zip(tableAttributeIdList, tableAttributeNameList):
if len(row[tid]) > 0 and row[tid] != r"\N":
vList.append(row[tid])
aList.append(nm)
sqlInsertList.append(
(sqlGen.insertTemplateSQL(databaseName, tableName,
aList), vList))
ret = myQ.sqlBatchTemplateCommand(sqlInsertList,
prependSqlList=sqlDeleteList)
if self.__verbose:
if ret:
self.__lfh.write(
"+SchemaDefLoader(__batchInsertImport) batch insert completed for table %s rows %d\n"
% (tableName, len(sqlInsertList)))
else:
self.__lfh.write(
"+SchemaDefLoader(__batchInsertImport) batch insert fails for table %s length %d\n"
% (tableName, len(sqlInsertList)))
endTime = time.time()
if self.__verbose:
self.__lfh.write(
"+SchemaDefLoader(__batchInsertImport) completed at %s (%.3f seconds)\n"
% (time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime))
return ret
def __batchFileImport(self,
tableId,
tableLoadPath,
sqlFilePath=None,
containerNameList=None,
deleteOpt="all"): # pylint: disable=unused-argument
"""Batch load the input table using data in the input loadable data file.
if sqlFilePath is provided then any generated SQL commands are preserved in this file.
deleteOpt None|'selected'| 'all' or 'truncate'
"""
startTime = time.time()
databaseName = self.__sD.getDatabaseName()
sqlGen = MyDbAdminSqlGen(self.__verbose, self.__lfh)
databaseName = self.__sD.getDatabaseName()
tableDefObj = self.__sD.getTable(tableId)
# tableName = tableDefObj.getName()
#
if deleteOpt:
sqlCommandList = self.__getSqlDeleteList(tableId,
containerNameList=None,
deleteOpt=deleteOpt)
else:
sqlCommandList = []
if os.access(tableLoadPath, os.R_OK):
tableDefObj = self.__sD.getTable(tableId)
sqlCommandList.append(
sqlGen.importTable(databaseName,
tableDefObj,
importPath=tableLoadPath))
if self.__verbose:
self.__lfh.write(
"+SchemaDefLoader(__batchFileImport) SQL import command\n%s\n"
% sqlCommandList)
#
if sqlFilePath is not None:
try:
ofh = open(sqlFilePath, "w")
ofh.write("%s" % "\n".join(sqlCommandList))
ofh.close()
except: # noqa: E722 pylint: disable=bare-except
pass
#
myQ = MyDbQuery(dbcon=self.__dbCon,
verbose=self.__verbose,
log=self.__lfh)
myQ.setWarning(self.__warningAction)
ret = myQ.sqlCommand(sqlCommandList=sqlCommandList)
#
#
endTime = time.time()
if self.__verbose:
self.__lfh.write(
"+SchemaDefLoader(__batchFileImport) table %s server returns %r\n"
% (tableId, ret))
self.__lfh.write(
"+SchemaDefLoader(__batchFileImport) completed at %s (%.3f seconds)\n"
% (time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime))
return ret
def _updateRequest(self, tableId, contextId, **kwargs):
"""Update the input table using the keyword value pairs provided as input arguments -
The contextId controls the handling default values for unspecified parameters.
"""
startTime = time.time()
if self.__debug:
logger.debug("Starting _updateRequest at %s",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
ret = False
try:
iOpened = False
if self.__dbCon is None:
self._open()
iOpened = True
#
# tableName = self.__sd.getTableName(tableId)
tableDefObj = self.__sd.getTable(tableId)
#
myQ = MyDbQuery(dbcon=self.__dbCon,
verbose=self.__verbose,
log=self.__lfh)
myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh)
defaultValD = self._getParameterDefaultValues(contextId=contextId)
cIdList = self._getConstraintParameterMap(tableId)
#
# create the value list for template --
#
vList = []
aList = []
cList = []
for atId, kwId in self._getAttributeParameterMap(tableId):
if (atId, kwId) in cIdList:
continue
if kwId in kwargs and kwargs[kwId] is not None:
vList.append(kwargs[kwId])
aList.append(atId)
else:
if kwId in defaultValD and defaultValD[kwId] is not None:
vList.append(defaultValD[kwId])
aList.append(atId)
for atId, kwId in cIdList:
if kwId in kwargs and kwargs[kwId] is not None:
vList.append(kwargs[kwId])
cList.append(atId)
sqlT = myAd.idUpdateTemplateSQL(self.__databaseName,
tableDefObj,
updateAttributeIdList=aList,
conditionAttributeIdList=cList)
if self.__debug:
logger.debug("update sql: %s", sqlT)
logger.debug("update values: %r", vList)
ret = myQ.sqlTemplateCommand(sqlTemplate=sqlT, valueList=vList)
if iOpened:
self._close()
except Exception as e:
status = " update operation error " + str(e)
logger.error("_updateRequest %s", status)
if self.__verbose:
logger.exception("%s", status)
if self.__debug:
endTime = time.time()
logger.debug("Completed _updateRequest %s (%.3f seconds)\n",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()),
endTime - startTime)
return ret
def _insertRequest(self, tableId, contextId, **kwargs):
"""Insert into the input table using the keyword value pairs provided as input arguments -
The contextId controls the handling default values for unspecified parameters.
"""
startTime = time.time()
if self.__debug:
logger.debug("Starting _insertRequest() %s",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
ret = False
try:
iOpened = False
if self.__dbCon is None:
self._open()
iOpened = True
#
# tableName = self.__sd.getTableName(tableId)
tableDefObj = self.__sd.getTable(tableId)
#
myQ = MyDbQuery(dbcon=self.__dbCon,
verbose=self.__verbose,
log=self.__lfh)
myAd = MyDbAdminSqlGen(self.__verbose, self.__lfh)
defaultValD = self._getParameterDefaultValues(contextId=contextId)
#
# Create the attribute and value list for template --
#
vList = []
aList = []
for atId, kwId in self._getAttributeParameterMap(tableId=tableId):
if kwId in kwargs and kwargs[kwId] is not None:
vList.append(kwargs[kwId])
aList.append(atId)
else:
# use the default values if these exist
if kwId in defaultValD and defaultValD[kwId] is not None:
vList.append(defaultValD[kwId])
aList.append(atId)
else:
# appropriate null handling -- all fields must be assigned on insert --
vList.append(tableDefObj.getSqlNullValue(atId))
aList.append(atId)
sqlT = myAd.idInsertTemplateSQL(self.__databaseName,
tableDefObj,
insertAttributeIdList=aList)
if self.__debug:
logger.debug("_insertRequest aList %d vList %d", len(aList),
len(vList))
logger.debug("_insertRequest insert template sql=%s", sqlT)
logger.debug("_insertRequest insert values vList=%r", vList)
# sqlC = sqlT % vList
# self.__lfh.write("+%s.%s insert sql command =\n%s\n" % (self.__class__.__name__, sys._getframe().f_code.co_name, sqlC))
ret = myQ.sqlTemplateCommand(sqlTemplate=sqlT, valueList=vList)
if iOpened:
self._close()
except Exception as e:
status = " insert operation error " + str(e)
logger.error("%s", status)
if self.__verbose:
logger.exception("Exception in _insertRequest")
if self.__debug:
endTime = time.time()
logger.debug("_insertRequest completed at %s (%.3f seconds)",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()),
endTime - startTime)
return ret