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 __updatemissingtables(self):
"""Update missing tables"""
for upd in self.__tableexists:
name = upd[0]
resource = upd[1]
table = upd[2]
func = upd[3]
mth = getattr(self, func, None)
commands = upd[4]
#print(name, resource, table, func, mth)
if mth is None:
print("INTERNAL ERROR: %s does not exist" % func)
return
mydb = MyConnectionBase()
mydb.setResource(resourceName=resource)
ok = mydb.openConnection()
if not ok:
print("ERROR: Could not open resource %s" % resource)
return
rc = mth(mydb._dbCon, table)
if rc:
print("About to load schema for %s" % table)
self.prettyprintcommands(commands)
if not self.__noop:
myq = MyDbQuery(dbcon=mydb._dbCon)
ret = myq.sqlCommand(commands)
if not ret:
print("ERROR CREATING TABLE %s" % table)
mydb.closeConnection()
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 updateschema(self):
"""Updates the schema configurations"""
print("")
print("Checking/Updating DB schema")
for upd in self.__configuration:
name = upd[0]
resource = upd[1]
table = upd[2]
func = upd[3]
#getattr(self, upd[3], None)
mth = getattr(self, func, None)
coldata = upd[4]
#print(name, resource, table, func, mth)
if mth is None:
print("INTERNAL ERROR: %s does not exist" % func)
return
mydb = MyConnectionBase()
mydb.setResource(resourceName=resource)
ok = mydb.openConnection()
if not ok:
print("ERROR: Could not open resource %s" % resource)
return
for row in coldata:
colname = row[0]
cmd = row[1]
if len(row) == 3:
opt = row[2]
rc = mth(mydb._dbCon, table, colname, opt)
else:
rc = mth(mydb._dbCon, table, colname)
if rc:
myq = MyDbQuery(dbcon=mydb._dbCon)
query = "ALTER TABLE `{}` {}".format(table, cmd)
print(query)
if not self.__noop:
ret = myq.sqlCommand([query])
if not ret:
print("ERROR UPDATING SCHEMA %s" % query)
mydb.closeConnection()
self.__updatemissingtables()
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 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 __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