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 delete(self, tableId, containerNameList=None, deleteOpt="all"): # pylint: disable=unused-argument
#
startTime = time.time()
sqlCommandList = self.__getSqlDeleteList(tableId,
containerNameList=None,
deleteOpt=deleteOpt)
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(delete) table %s server returns %r\n" %
(tableId, ret))
self.__lfh.write(
"+SchemaDefLoader(delete) completed at %s (%.3f seconds)\n" %
(time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime))
return ret
else:
if self.__verbose:
self.__lfh.write("+SchemaDefLoader(delete) failse for %s\n" %
tableId)
return False
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 __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