def dump(**kwargs):
if kwargs["configFileFullPath"]:
DATAMINING_CONFIG = json.loads(pUtils.quickFileRead(kwargs["configFileFullPath"]))
else:
DATAMINING_CONFIG = json.loads(
pUtils.quickFileRead(
os.path.join(os.environ["MTP_TESTSTATION"], "MTP", "config", "miselu", "database", kwargs["config"])
)
)
sql = SQL(**DATAMINING_CONFIG)
sql.conn()
### Create virtual table of testRunID's ###
SNlist = None
v = []
s = "SELECT testRunID INTO TEMP TABLE SelectedTestRunID FROM TestRun"
s += "\n WHERE creationTimestamp>=%s"
v.append(kwargs["creationTimestamp_rangeStart"])
if kwargs["creationTimestamp_rangeEnd"] != None:
s += "\n AND creationTimestamp<=%s"
if kwargs["startTimestamp_rangeStart"] != None:
s += "\n AND startTimestamp>=%s"
v.append(kwargs["startTimestamp_rangeStart"])
if kwargs["startTimestamp_rangeEnd"] != None:
s += "\n AND startTimestamp<=%s"
v.append(kwargs["startTimestamp_rangeEnd"])
if kwargs["endTimestamp_rangeStart"] != None:
s += "\n AND endTimestamp>%s"
v.append(kwargs["endTimestamp_rangeStart"])
if kwargs["endTimestamp_rangeEnd"] != None:
s += "\n AND endTimestamp<%s"
v.append(kwargs["endTimestamp_rangeEnd"])
if SNlist != None:
s += "\n AND ( SN=%s"
s += "\n OR SN=%s" * (len(SNlist) - 1)
s += "\n )"
v += SNlist
if kwargs["siteID"] != None:
s += "\n AND siteID=%s"
v.append(kwargs["siteID"])
if kwargs["stationID"] != None:
s += "\n AND stationID=%s"
v.append(kwargs["stationID"])
if kwargs["testSequenceID"] != None:
s += "\n AND testSequenceID=%s"
v.append(kwargs["testSequenceID"])
if kwargs["isPass"] != None:
s += "\n AND isPass=%s"
v.append(kwargs["isPass"])
# s+=' limit 10'
s += "\n ;"
sql.execute(s, v) ##TODO check if it is blocking or not
# For every table, filtered with the TestRunID's table and write it to a file
directoryFullPath = kwargs["directoryFullPath"]
pUtils.createDirectory(directoryFullPath)
if len(os.listdir(directoryFullPath)) != 0:
raise Exception("Directory specified for dump is not empty")
for tableName in tableNameList:
v = []
s = "COPY"
s += "\n (SELECT %s.* FROM %s,SelectedTestRunID"
s += "\n WHERE SelectedTestRunID.testRunID = %s.testRunID)"
s += "\n TO STDOUT"
s += "\n WITH CSV "
s += "\n HEADER "
s = s % ((tableName,) * 3)
fileFullPath = os.path.join(directoryFullPath, fileBaseName + "_" + tableName + "." + extension)
with open(fileFullPath, "wt") as f:
sql.cur.copy_expert(s, f)
sql.close()
createManifest(MANIFEST_FILE_NAME, directoryFullPath)
return {"retCode": 0, "errMsg": None}
def load(**kwargs):
directoryFullPath = kwargs["directoryFullPath"]
t = verify(manifestFileFullPath=os.path.join(directoryFullPath, MANIFEST_FILE_NAME))
if t["retCode"] != 0:
return {"retCode": 1, "errMsg": "Checksum verification failed!", "debug": t}
if kwargs["configFileFullPath"]:
DATAMINING_CONFIG = json.loads(pUtils.quickFileRead(kwargs["configFileFullPath"]))
else:
DATAMINING_CONFIG = json.loads(
pUtils.quickFileRead(
os.path.join(os.environ["MTP_TESTSTATION"], "MTP", "config", "miselu", "database", kwargs["config"])
)
)
sql = SQL(**DATAMINING_CONFIG)
sql.conn()
for tableName in tableNameList:
# Create Empty Temp table
v = []
s = "SELECT * INTO TEMP TABLE %s FROM %s"
s += "\n WHERE testRunID!=testRunID"
s = s % (tableName + "_t", tableName)
sql.execute(s, v)
# Load temp table
v = []
s = "COPY"
s += "\n %s"
s += "\n FROM STDIN"
s += "\n WITH CSV "
s += "\n HEADER"
s = s % (tableName + "_t")
fileFullPath = os.path.join(directoryFullPath, fileBaseName + "_" + tableName + "." + extension)
with open(fileFullPath, "rt") as f:
sql.cur.copy_expert(s, f)
# Create TestRubID_input reference table
v = ["Table1", "Table2", "Table2"]
s = "WITH Table2 as (SELECT testrunID FROM TestRun_t)"
s += "\n, TableDiff AS ("
s += "\n SELECT MIN(tableName) as tableName"
s += "\n , testrunID"
s += "\n FROM"
s += "\n ("
s += "\n SELECT %s AS tableName"
s += "\n , testrunID"
s += "\n FROM TestRun"
s += "\n UNION ALL"
s += "\n SELECT %s AS tableName"
s += "\n , testrunID"
s += "\n FROM Table2"
s += "\n ) AS MCH_UnionTable1"
s += "\n GROUP BY testRunID"
s += "\n HAVING COUNT(*) = 1"
s += "\n ORDER BY tableName"
s += "\n )"
s += "SELECT * INTO TEMP TABLE TestRunID_input FROM TableDiff WHERE tableName = %s"
s += "\n;"
sql.execute(s, v)
for tableName in tableNameList:
# Insert new records
v = []
s = "INSERT INTO %s"
s += "\n (SELECT %s.* FROM %s,TestRunID_input WHERE TestRunID_input.testRunID = %s.testRunID)"
s += "\n;"
s = s % ((tableName,) + (tableName + "_t",) * 3)
sql.execute(s, v)
newRecordsAmount = sql.read("SELECT COUNT(*) FROM TestRunID_input", [])[0][0]
sql.commit()
sql.close()
return {"retCode": 0, "errMsg": None, "newRecordsAmount": newRecordsAmount}
