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 updatedb(self):
mydb = MyConnectionBase()
mydb.setResource(resourceName="STATUS")
ok = mydb.openConnection()
if not ok:
print("ERROR: Could not open status db")
return
myq = MyDbQuery(dbcon=mydb._dbCon)
query = "select count(ordinal) from taxonomy "
rows = myq.selectRows(queryString=query)
count = rows[0][0]
mydb.closeConnection()
if count >= self.__taxdbsize and count < self.__maxsize:
print("Taxdb at least as big as expected")
return
if self.__useftp:
command = "python -m wwpdb.apps.deposit.depui.taxonomy.loadTaxonomyFromFTP --write_sql"
else:
taxfile = self.__cI.get("TAXONOMY_FILE_NAME")
if not taxfile:
print("Could not find site-config TAXONOMY_FILE_NAME -- cannot load taxonomy")
return
command = "python -m wwpdb.apps.deposit.depui.taxonomy.loadData --input_csv {}".format(taxfile)
self.__exec(command)
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 updatewftasks(self):
""" Handles the addition of new WF tasks """
print("")
print("Checking WF scheme status DB")
mydb = MyConnectionBase()
mydb.setResource(resourceName="STATUS")
ok = mydb.openConnection()
if not ok:
print("ERROR: Could not open resource %s" % 'STATUS')
return
defpath = self.__ci.get('SITE_WF_XML_PATH')
for taskid, fname in self.__wftasks:
fpath = os.path.join(defpath, fname)
if not os.path.exists(fpath):
# print("Skipping %s as does not exist" % fname)
continue
myq = MyDbQuery(dbcon=mydb._dbCon)
query = "select wf_class_id from wf_class_dict where wf_class_id='{}'".format(taskid)
rows = myq.selectRows(queryString=query)
if len(rows) == 0:
print("About to install WF schema %s with name %s" % (taskid, fname))
cmd = 'python -m wwpdb.apps.wf_engine.wf_engine_utils.tasks.WFTaskRequestExec --verbose --load_wf_def_file={}'.format(fname)
self.__exec(cmd)
mydb.closeConnection()
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 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 _notexists(self, dbconn, table, colname):
"""Checks if colname exists in table. Returns True if does not exist"""
myq = MyDbQuery(dbcon=dbconn)
query = "show columns from `{}` LIKE '{}'".format(table, colname)
rows = myq.selectRows(queryString=query)
if len(rows) == 0:
return True
return False
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 _nottableexists(self, dbconn, table):
"""Checks if table does not exist. Returns True if does not exist"""
myq = MyDbQuery(dbcon=dbconn)
query = "SELECT count(*) FROM information_schema.TABLES WHERE TABLE_NAME = '{}' AND TABLE_SCHEMA in (SELECT DATABASE())".format(table)
rows = myq.selectRows(queryString=query)
if len(rows) != 1:
return True
val = rows[0][0]
if val == 0:
return True
return False
def _colwidth(self, dbconn, table, colname, width):
"""Returns True if colname is not width characters"""
myq = MyDbQuery(dbcon=dbconn)
query = "select character_maximum_length from information_schema.columns where table_schema=Database() and table_name='{}' and column_name='{}'".format(table,colname);
rows = myq.selectRows(queryString=query)
if len(rows) == 0:
print("ERROR {}.{} does not exist!!!!!!!".format(table,colname))
return False
size = rows[0][0]
if size != width:
return True
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 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 __testDirectiveWithHistoryQuery(self, qdL, domD):
"""Test case - selection everything for a simple condition -"""
startTime = time.time()
self.__lfh.write(
"\nStarting MyQueryDirectivesTest __testDirectiveWithHistoryQuery at %s\n"
% time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
try:
self.__databaseName = "da_internal"
self.open()
sd = DaInternalSchemaDef(verbose=self.__verbose, log=self.__lfh)
mqd = MyQueryDirectives(schemaDefObj=sd,
verbose=self.__verbose,
log=self.__lfh)
sqlS = mqd.build(queryDirL=qdL,
domD=domD,
appendValueConditonsToSelect=True)
if self.__verbose:
self.__lfh.write(
"\n\n+testDirectiveWithHistoryQuery SQL\n %s\n\n" % sqlS)
self.__lfh.flush()
myQ = MyDbQuery(dbcon=self.__dbCon,
verbose=self.__verbose,
log=self.__lfh)
rowList = myQ.selectRows(queryString=sqlS)
if self.__verbose:
self.__lfh.write(
"\n+testDirectiveWithHistoryQuery mysql server returns row length %d\n"
% len(rowList))
self.__lfh.flush()
for ii, row in enumerate(rowList[:30]):
self.__lfh.write(" %6d %r\n" % (ii, row))
self.close()
except: # noqa: E722 pylint: disable=bare-except
traceback.print_exc(file=self.__lfh)
self.fail()
endTime = time.time()
self.__lfh.write(
"\nCompleted MyQueryDirectivesTest __testDirectiveWithHistoryQuery 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 testPoolQuery(self):
"""Test case - connection pool management -
Setup -
. set-test-env.sh
"""
startTime = time.time()
self.__lfh.write("\nStarting MyDbUtilTests testPoolQuery at %s\n" %
time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
self.__verbose = False
try:
dbUserId = os.getenv("TEST_DB_USER_NAME")
dbUserPwd = os.getenv("TEST_DB_PASSWORD")
dbName = os.getenv("TEST_DB_NAME")
dbHost = os.getenv("TEST_DB_HOST")
for ii in range(5000):
ok = self.open(dbUserId=dbUserId,
dbUserPwd=dbUserPwd,
dbHost=dbHost,
dbName=dbName)
self.assertTrue(ok)
for jj in range(100):
my = MyDbQuery(dbcon=self.__dbCon)
ok = my.testSelectQuery(count=ii + jj)
self.assertTrue(ok)
ok = self.close()
self.assertTrue(ok)
except: # noqa: E722 pylint: disable=bare-except
traceback.print_exc(file=self.__lfh)
self.fail()
endTime = time.time()
self.__lfh.write(
"\nCompleted MyDbUtilTests testPoolQuery at %s (%f seconds)\n" %
(time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime))
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
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 _select(self, tableId, **kwargs):
"""Construct a selection query for input table and optional constraints provided as keyword value pairs in the
input arguments. Return a list of dictionaries of these query details including all table attributes.
"""
startTime = time.time()
if self.__debug:
logger.debug("Starting _select at %s",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
rdList = []
try:
iOpened = False
if self.__dbCon is None:
self._open()
iOpened = True
#
tableDefObj = self.__sd.getTable(tableId)
myQ = MyDbQuery(dbcon=self.__dbCon,
verbose=self.__verbose,
log=self.__lfh)
sqlGen = MyDbQuerySqlGen(schemaDefObj=self.__sd,
verbose=self.__verbose,
log=self.__lfh)
sqlGen.setDatabase(databaseName=self.__databaseName)
sqlConstraint = MyDbConditionSqlGen(schemaDefObj=self.__sd,
verbose=self.__verbose,
log=self.__lfh)
#
atMapL = self._getAttributeParameterMap(tableId=tableId)
for kwArg, kwVal in kwargs.items(): # pylint: disable=unused-variable
for atId, kwId in atMapL:
if kwId == kwArg:
if tableDefObj.isAttributeStringType(atId):
cTup = ((tableId, atId), "EQ", (kwargs[kwId],
"CHAR"))
else:
cTup = ((tableId, atId), "EQ", (kwargs[kwId],
"OTHER"))
sqlConstraint.addValueCondition(
cTup[0], cTup[1], cTup[2])
break
#
# Add optional constraints OR ordering by primary key attributes
if len(sqlConstraint.get()) > 0:
sqlGen.setCondition(sqlConstraint)
else:
for atId in tableDefObj.getPrimaryKeyAttributeIdList():
sqlGen.addOrderByAttributeId(attributeTuple=(tableId,
atId))
atIdList = self.__sd.getAttributeIdList(tableId)
for atId in atIdList:
sqlGen.addSelectAttributeId(attributeTuple=(tableId, atId))
#
sqlS = sqlGen.getSql()
if self.__debug:
logger.debug("_select selection sql: %s", sqlS)
rowList = myQ.selectRows(queryString=sqlS)
sqlGen.clear()
#
# return the result set as a list of dictionaries
#
for iRow, row in enumerate(rowList):
rD = {}
for colVal, atId in zip(row, atIdList):
rD[atId] = colVal
if self.__debug:
logger.debug("_select result set row %d dictionary %r",
iRow, rD.items())
rdList.append(rD)
if iOpened:
self._close()
except Exception as e:
status = " operation error " + str(e)
logger.error("_select %s", status)
if self.__verbose:
logger.eception("_select failed")
if self.__debug:
endTime = time.time()
logger.debug("Completed _select at %s (%.3f seconds)",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()),
endTime - startTime)
return rdList
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