def __schemaCreate(self, schemaDefObj):
"""Test case - create table schema using schema definition"""
ret = 0
try:
tableIdList = schemaDefObj.getTableIdList()
sqlGen = SqlGenAdmin(self.__verbose, serverType="CockroachDb")
dbName = schemaDefObj.getVersionedDatabaseName()
sqlL = sqlGen.createDatabaseSQL(dbName)
for tableId in tableIdList:
tableDefObj = schemaDefObj.getTable(tableId)
sqlL.extend(sqlGen.createTableSQL(databaseName=schemaDefObj.getVersionedDatabaseName(), tableDefObj=tableDefObj))
logger.debug("\nSchema creation SQL string\n %s\n\n", "\n".join(sqlL))
logger.info("Creating schema using database %s", schemaDefObj.getVersionedDatabaseName())
#
with Connection(cfgOb=self.__cfgOb, resourceName=self.__resourceName) as client:
crQ = CockroachDbQuery(dbcon=client, verbose=self.__verbose)
ret = crQ.sqlCommandList(sqlCommandList=sqlL)
# ret = crQ.sqlCommand(' '.join(sqlL))
logger.info("Schema create command returns %r\n", ret)
return ret
#
except Exception as e:
logger.exception("Failing with %s", str(e))
self.fail()
def __testImportExport(self, sD):
"""Test case - import and export commands --
"""
try:
databaseName = sD.getDatabaseName()
tableIdList = sD.getSchemaIdList()
myAd = SqlGenAdmin(self.__verbose)
for tableId in tableIdList:
tableDefObj = sD.getSchemaObject(tableId)
exportPath = os.path.join(HERE, "test-output",
tableDefObj.getName() + ".tdd")
sqlExport = myAd.exportTable(databaseName,
tableDefObj,
exportPath=exportPath)
logger.debug(
"\n\n+SqlGenTests table export SQL string\n %s\n\n",
sqlExport)
sqlImport = myAd.importTable(databaseName,
tableDefObj,
importPath=exportPath)
logger.debug(
"\n\n+SqlGenTests table import SQL string\n %s\n\n",
sqlImport)
self.assertGreaterEqual(len(sqlImport), 100)
except Exception as e:
logger.exception("Failing with %s", str(e))
self.fail()
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 = SqlGenAdmin(self.__verbose)
databaseName = self.__sD.getDatabaseName()
tableDefObj = self.__sD.getSchemaObject(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)]
logger.debug("Delete SQL for %s : %r\n", tableId, sqlDeleteList)
return sqlDeleteList
def __schemaCreate(self, schemaDefObj):
"""Create table schema using schema definition"""
try:
tableIdList = schemaDefObj.getSchemaIdList()
sqlGen = SqlGenAdmin(self.__verbose)
sqlL = sqlGen.createDatabaseSQL(schemaDefObj.getDatabaseName())
for tableId in tableIdList:
tableDefObj = schemaDefObj.getSchemaObject(tableId)
sqlL.extend(
sqlGen.createTableSQL(
databaseName=schemaDefObj.getDatabaseName(),
tableDefObj=tableDefObj))
logger.debug("Schema creation SQL string\n %s\n\n",
"\n".join(sqlL))
with Connection(cfgOb=self.__cfgOb,
resourceName=self.__resourceName) as client:
myQ = MyDbQuery(dbcon=client, verbose=self.__verbose)
#
# Permit warnings to support "drop table if exists" for missing tables.
#
myQ.setWarning("ignore")
ret = myQ.sqlCommand(sqlCommandList=sqlL)
logger.debug("\n\n+INFO mysql server returns %r\n", ret)
self.assertTrue(ret)
except Exception as e:
logger.exception("Failing with %s", str(e))
self.fail()
def __schemaRemove(self, schemaDefObj):
"""Test case - remove table schema using schema definition"""
ret = 0
try:
tableIdList = schemaDefObj.getTableIdList()
sqlGen = SqlGenAdmin(self.__verbose, serverType="cratedb")
sqlL = []
for tableId in tableIdList:
if tableId in self.__tableIdSkipD:
continue
tableDefObj = schemaDefObj.getTable(tableId)
sqlL.extend(
sqlGen.dropTableSQL(
databaseName=schemaDefObj.getVersionedDatabaseName(),
tableDefObj=tableDefObj))
sqlL.extend(
sqlGen.dropTableSQL(
databaseName=schemaDefObj.getDatabaseName(),
tableDefObj=tableDefObj))
logger.debug("Schema Remove SQL string\n %s", "\n".join(sqlL))
with Connection(cfgOb=self.__cfgOb,
resourceName=self.__resourceName) as client:
crQ = CrateDbQuery(dbcon=client, verbose=self.__verbose)
ret = crQ.sqlCommandList(sqlCommandList=sqlL)
logger.debug("Schema remove command returns %r\n", ret)
return ret
#
except Exception as e:
logger.exception("Failing with %s", str(e))
self.fail()
def __batchFileImport(self,
tableId,
tableLoadPath,
sqlFilePath=None,
containerNameList=None,
deleteOpt="all"):
"""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 = SqlGenAdmin(self.__verbose)
databaseName = self.__sD.getDatabaseName()
tableDefObj = self.__sD.getSchemaObject(tableId)
# tableName = tableDefObj.getName()
#
if deleteOpt:
sqlCommandList = self.__getSqlDeleteList(
tableId,
containerNameList=containerNameList,
deleteOpt=deleteOpt)
else:
sqlCommandList = []
if os.access(tableLoadPath, os.R_OK):
tableDefObj = self.__sD.getSchemaObject(tableId)
sqlCommandList.append(
sqlGen.importTable(databaseName,
tableDefObj,
importPath=tableLoadPath))
if self.__verbose:
logger.debug("SQL import command\n%s\n", sqlCommandList)
#
if sqlFilePath is not None:
try:
with open(sqlFilePath, "w", encoding="utf-8") as ofh:
ofh.write("%s" % "\n".join(sqlCommandList))
except Exception:
pass
#
myQ = MyDbQuery(dbcon=self.__dbCon, verbose=self.__verbose)
myQ.setWarning(self.__warningAction)
ret = myQ.sqlCommand(sqlCommandList=sqlCommandList)
#
#
endTime = time.time()
logger.debug("Table %s server returns %r\n", tableId, ret)
logger.debug("Completed at %s (%.3f seconds)\n",
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 batch 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)
myQ.setWarning(self.__warningAction)
sqlGen = SqlGenAdmin(self.__verbose)
#
databaseName = self.__sD.getDatabaseName()
tableDefObj = self.__sD.getSchemaObject(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:
logger.debug("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[id]) > 0 and row[id] != r'\N':
if row[tid] is not None 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 ret:
logger.debug("Batch insert completed for table %s rows %d\n", tableName, len(sqlInsertList))
else:
logger.error("Batch insert fails for table %s length %d\n", tableName, len(sqlInsertList))
endTime = time.time()
if self.__verbose:
logger.debug("Completed at %s (%.3f seconds)\n", time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - startTime)
return ret
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.info("Starting 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
tableDefObj = self.__sD.getSchemaObject(tableId)
#
#
myQ = MyDbQuery(dbcon=self.__dbCon, verbose=self.__verbose)
myAd = SqlGenAdmin(self.__verbose)
#
# 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.info("delete sql: %s", sqlT)
logger.info("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.info("status %s", status)
if self.__verbose:
logger.exception("Failing with %s", str(e))
if self.__debug:
endTime = time.time()
logger.info("Completedat %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"""
sqlL = []
try:
tableIdList = schemaDefObj.getTableIdList()
sqlGen = SqlGenAdmin(self.__verbose, serverType="CockroachDb")
dbName = schemaDefObj.getVersionedDatabaseName()
sqlL = sqlGen.createDatabaseSQL(dbName)
for tableId in tableIdList:
tableDefObj = schemaDefObj.getTable(tableId)
sqlL.extend(sqlGen.createTableSQL(databaseName=schemaDefObj.getVersionedDatabaseName(), tableDefObj=tableDefObj))
logger.debug("\nSchema creation SQL string\n %s\n\n", "\n".join(sqlL))
except Exception as e:
logger.exception("Failing with %s", str(e))
self.fail()
return sqlL
def __deleteFromTable(self, tableIdList, deleteValue):
""" Delete data from the input table list where the schema table delete attribute
has the input value "deleteValue".
"""
databaseName = self.__sD.getDatabaseName()
sqlList = []
sqlGen = SqlGenAdmin(self.__verbose)
for tableId in tableIdList:
tableName = self.__sD.getSchemaName(tableId)
tableDefObj = self.__sD.getSchemaObject(tableId)
atName = tableDefObj.getDeleteAttributeName()
sqlTemp = sqlGen.deleteTemplateSQL(databaseName, tableName, [atName])
sqlList.append(sqlTemp % deleteValue)
#
return sqlList
def _createSchema(self):
""" Create table schema using the current class schema definition
"""
if self.__debug:
startTime = time.time()
logger.info("Starting 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.getSchemaIdList()
myQ = MyDbQuery(dbcon=self.__dbCon, verbose=self.__verbose)
myAd = SqlGenAdmin(self.__verbose)
for tableId in tableIdList:
sqlL = []
tableDefObj = self.__sD.getSchemaObject(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\n", tableId,
ret)
if self.__debug:
logger.info("SQL: %s\n", "\n".join(sqlL))
if iOpened:
self._close()
except Exception as e:
status = " table create error " + str(e)
logger.info("status %s\n", status)
if self.__verbose:
logger.exception("Failing with %s", str(e))
if self.__debug:
endTime = time.time()
logger.info("Completed at %s (%.3f seconds)\n",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()),
endTime - startTime)
return ret
def __schemaRemove(self, schemaDefObj):
"""Test case - remove table schema using schema definition"""
ret = 0
try:
dbName = schemaDefObj.getVersionedDatabaseName()
sqlGen = SqlGenAdmin(self.__verbose, serverType="CockroachDb")
sqlL = sqlGen.removeDatabaseSQL(dbName)
logger.debug("Schema Remove SQL string\n %s", "\n".join(sqlL))
with Connection(cfgOb=self.__cfgOb, resourceName=self.__resourceName) as client:
crQ = CockroachDbQuery(dbcon=client, verbose=self.__verbose)
ret = crQ.sqlCommandList(sqlCommandList=sqlL)
# ret = crQ.sqlCommand(' '.join(sqlL))
logger.debug("Schema remove command returns %r\n", ret)
return ret
#
except Exception as e:
logger.exception("Failing with %s", str(e))
self.fail()
def __testSchemaCreate(self, sD):
"""Test case - create table schema using input schema definition as an example"""
try:
tableIdList = sD.getSchemaIdList()
myAd = SqlGenAdmin(self.__verbose)
sqlL = []
for tableId in tableIdList:
tableDefObj = sD.getSchemaObject(tableId)
sqlL.extend(
myAd.createTableSQL(databaseName=sD.getDatabaseName(),
tableDefObj=tableDefObj))
logger.debug(
"\n\n+SqlGenTests table creation SQL string\n %s\n\n",
"\n".join(sqlL))
self.assertGreaterEqual(len(sqlL), 10)
except Exception as e:
logger.exception("Failing with %s", str(e))
self.fail()
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.info("Starting 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
#
tableDefObj = self.__sD.getSchemaObject(tableId)
#
myQ = MyDbQuery(dbcon=self.__dbCon, verbose=self.__verbose)
myAd = SqlGenAdmin(self.__verbose)
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.info("update sql: %s", sqlT)
logger.info("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.info("status %s", status)
if self.__verbose:
logger.exception("Failing with %s", str(e))
if self.__debug:
endTime = time.time()
logger.info("Completed at %s (%.3f seconds)",
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.info("Starting at %s\n",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()))
ret = False
try:
iOpened = False
if self.__dbCon is None:
self._open()
iOpened = True
#
tableDefObj = self.__sD.getSchemaObject(tableId)
#
myQ = MyDbQuery(dbcon=self.__dbCon, verbose=self.__verbose)
myAd = SqlGenAdmin(self.__verbose)
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.getAppNullValue(atId))
aList.append(atId)
sqlT = myAd.idInsertTemplateSQL(self.__databaseName,
tableDefObj,
insertAttributeIdList=aList)
if self.__debug:
logger.info("aList %d vList %d\n", len(aList), len(vList))
logger.info("insert template sql=\n%s\n", sqlT)
logger.info("insert values vList=\n%r\n", vList)
# sqlC = sqlT % vList
# logger.info("insert sql command =\n%s\n", sqlC)
ret = myQ.sqlTemplateCommand(sqlTemplate=sqlT, valueList=vList)
if iOpened:
self._close()
except Exception as e:
status = " insert operation error " + str(e)
logger.info("status %s\n", status)
if self.__verbose:
logger.exception("Failing with %s", str(e))
if self.__debug:
endTime = time.time()
logger.info("Completed %s (%.3f seconds)\n",
time.strftime("%Y %m %d %H:%M:%S", time.localtime()),
endTime - startTime)
return ret
def __cockroachInsertImport(self, tableId, rowList=None, containerNameList=None, deleteOpt="selected", sqlMode="many"):
"""Load the input table using sql cockroach templated inserts of the input rowlist 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.
Args:
tableId (TYPE): Description
rowList (None, optional): Description
containerNameList (None, optional): Description
deleteOpt (str, optional): Description
sqlMode (str, optional): Description
Returns:
TYPE: Description
"""
startTime = time.time()
crQ = CockroachDbQuery(dbcon=self.__dbCon, verbose=self.__verbose)
sqlGen = SqlGenAdmin(self.__verbose)
#
databaseName = self.__sD.getVersionedDatabaseName()
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()
logger.debug("tableName %s delete attribute %s", tableName, deleteAttributeName)
sqlDeleteList = sqlGen.deleteFromListSQL(databaseName, tableName, deleteAttributeName, containerNameList, chunkSize=10)
# logger.debug("Delete SQL for %s : %r" % (tableId, sqlDeleteList))
elif deleteOpt in ["all", "truncate"]:
sqlDeleteList = [sqlGen.truncateTableSQL(databaseName, tableName)]
#
lenC = len(rowList)
logger.debug("Deleting from table %s length %d", tableName, lenC)
crQ.sqlCommandList(sqlDeleteList)
endTime1 = time.time()
logger.debug("Deleting succeeds for table %s %d rows at %s (%.3f seconds)", tableName, lenC, time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime1 - startTime)
logger.debug("Delete commands %s", sqlDeleteList)
if not rowList:
logger.debug("Skipping insert for table %s length %d", tableName, len(containerNameList))
return True
#
logger.debug("Insert begins for table %s with row length %d", tableName, len(rowList))
sqlInsertList = []
tupL = list(zip(tableAttributeIdList, tableAttributeNameList))
if sqlMode == "many":
aList = []
for tId, nm in tupL:
aList.append(tId)
#
vLists = []
for row in rowList:
vList = []
for tId, nm in tupL:
if row[tId] and row[tId] != r"\N":
vList.append(row[tId])
else:
vList.append(None)
vLists.append(vList)
#
ret = crQ.sqlTemplateCommandMany(sqlTemplate=sqlGen.idInsertTemplateSQL(databaseName, tableDefObj, aList), valueLists=vLists)
endTime = time.time()
if ret:
logger.debug("Insert succeeds for table %s %d rows at %s (%.3f seconds)", tableName, lenC, time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - endTime1)
else:
logger.error("Insert fails for table %s %d rows at %s (%.3f seconds)", tableName, lenC, time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - endTime1)
else:
lenT = -1
lenR = -1
aList = []
for tId, nm in tupL:
aList.append(nm)
#
for row in rowList:
vList = []
for tId, nm in tupL:
if row[tId] is not None and row[tId] != r"\N":
vList.append(row[tId])
else:
vList.append(None)
sqlInsertList.append((sqlGen.insertTemplateSQL(databaseName, tableName, aList), vList))
#
lenT = len(sqlInsertList)
lenR = crQ.sqlTemplateCommandList(sqlInsertList)
#
ret = lenR == lenT
endTime = time.time()
if ret:
logger.debug(
"Insert succeeds for table %s %d of %d rows at %s (%.3f seconds)",
tableName,
lenR,
lenT,
time.strftime("%Y %m %d %H:%M:%S", time.localtime()),
endTime - endTime1,
)
else:
logger.error(
"Insert fails for table %s %d of %d rows at %s (%.3f seconds)", tableName, lenR, lenT, time.strftime("%Y %m %d %H:%M:%S", time.localtime()), endTime - endTime1
)
return ret
def __crateInsertImport(self,
tableId,
rowList=None,
containerNameList=None,
deleteOpt="selected",
sqlMode="many",
refresh=True):
"""Load the input table using sql crate templated inserts of the input rowlist 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()
sqlRefresh = None
crQ = CrateDbQuery(dbcon=self.__dbCon, verbose=self.__verbose)
sqlGen = SqlGenAdmin(self.__verbose)
#
databaseName = self.__sD.getVersionedDatabaseName()
tableDefObj = self.__sD.getSchemaObject(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)
logger.debug("Delete SQL for %s : %r", tableId, sqlDeleteList)
elif deleteOpt in ["all", "truncate"]:
sqlDeleteList = [sqlGen.truncateTableSQL(databaseName, tableName)]
#
logger.debug("Deleting from table %s length %d", tableName,
len(containerNameList))
crQ.sqlCommandList(sqlDeleteList)
logger.debug("Delete commands %s", sqlDeleteList)
if not rowList:
return True
if refresh:
sqlRefresh = sqlGen.refreshTableSQLCrate(databaseName, tableName)
crQ.sqlCommand(sqlRefresh)
#
logger.info("Insert begins for table %s with row length %d", tableName,
len(rowList))
sqlInsertList = []
tupL = list(zip(tableAttributeIdList, tableAttributeNameList))
if sqlMode == "many":
aList = []
for tId, nm in tupL:
aList.append(nm)
#
vLists = []
for row in rowList:
vList = []
for tId, nm in tupL:
if row[tId] and row[tId] != r"\N":
vList.append(row[tId])
else:
vList.append(None)
vLists.append(vList)
#
lenT = len(vLists)
lenR = crQ.sqlTemplateCommandMany(
sqlTemplate=sqlGen.insertTemplateSQLCrate(
databaseName, tableName, aList),
valueLists=vLists)
ret = lenR == len(vLists)
else:
aList = []
for tId, nm in tupL:
aList.append(nm)
#
for row in rowList:
vList = []
for tId, nm in tupL:
if row[tId] is not None and row[tId] != r"\N":
vList.append(row[tId])
else:
vList.append(None)
sqlInsertList.append(
(sqlGen.insertTemplateSQLCrate(databaseName, tableName,
aList), vList))
#
lenT = len(sqlInsertList)
lenR = crQ.sqlTemplateCommandList(sqlInsertList)
ret = lenR == lenT
if refresh:
sqlRefresh = sqlGen.refreshTableSQLCrate(databaseName, tableName)
crQ.sqlCommand(sqlRefresh)
#
endTime = time.time()
if ret:
logger.info(
"Insert succeeds for table %s %d of %d rows at %s (%.3f seconds)",
tableName, lenR, lenT,
time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime)
else:
logger.info(
"Insert fails for table %s %d of %d rows at %s (%.3f seconds)",
tableName, lenR, lenT,
time.strftime("%Y %m %d %H:%M:%S",
time.localtime()), endTime - startTime)
return ret