def push(dirFullPath):
sql = SQL()
sql.conn()
testRunID = str(uuid.uuid4())
fileFullPath = os.path.join(dirFullPath,'TestRun.csv')
s,v = prepsForTestRun(fileFullPath,testRunID)
if s!='': sql.execute(s,v)
fileFullPath = os.path.join(dirFullPath,'TestMeasurement.csv')
s,v = prepsForTestMeasurement(fileFullPath,testRunID)
if s!='': sql.execute(s,v)
fileFullPath = os.path.join(dirFullPath,'StringDictionary.json')
s,v = prepsForDictionary(fileFullPath,testRunID)
if s!='': sql.execute(s,v)
fileFullPath = os.path.join(dirFullPath,'DoubleDictionary.json')
s,v = prepsForDictionary(fileFullPath,testRunID)
if s!='': sql.execute(s,v)
fileFullPath = os.path.join(dirFullPath,'FileDictionary.json')
s,v = prepsForDictionary(fileFullPath,testRunID)
if s!='': sql.execute(s,v)
sql.commit()
sql.close()
class DatabaseApi():
"""
Contains the functions for all the database interactions necessary for the line to function.
"""
def __init__(self,dbConfig):
self.sql = SQL(**dbConfig)
def submitTestRunData(self,testRunSummary,
testMeasurementList,
stringDictionary,
numericDictionary,
fileDictionary,
dependencyDict,
testRunID=None):
"""
Submits the test run data to the database
Args:
* testRunSummary (list): The summary of the run (each entry is a string)
* testMeasurementList (list): The measurement data (see :ref:'label_LimitManager`)
* stringDictionary (dict): Key,value pairs of string data
* numericDictionary (dict): Key,value pairs of numeric data
* fileDictionary (dict): Key,value pairs of fileID and its content (zip and base64 encoded)
* dependencyDict (dict): The subcomponent dependencies of the UUT
Returns:
None
"""
self.sql.conn()
if testRunID==None:
testRunID = str(uuid.uuid4())
s,v = self.prepsForTestRun(testRunSummary,testRunID)
if s!='': self.sql.execute(s,v)
s,v = self.prepsForTestMeasurement(testMeasurementList,testRunID)
if s!='': self.sql.execute(s,v)
s,v = self.prepsForDictionary(stringDictionary,testRunID,'StringDictionary')
if s!='': self.sql.execute(s,v)
s,v = self.prepsForDictionary(numericDictionary,testRunID,'DoubleDictionary')
if s!='': self.sql.execute(s,v)
s,v = self.prepsForDictionary(fileDictionary,testRunID,'FileDictionary')
if s!='': self.sql.execute(s,v)
s,v = self.prepsForDictionary(dependencyDict,testRunID,'Components')
if s!='': self.sql.execute(s,v)
self.sql.commit()
self.sql.close()
def prepsForTestRun(self,testRunSummary,testRunID):
"""
Prepares the sql statement and values vector for the submission of the test run summary to the database
Args:
* testRunSummary (list): The data
* testRunID (str): The unique identifier to the test run
Returns:
A tupple (s,v) where
* s (str): The sql statement
* v (list): Vector with the values
"""
s = 'INSERT INTO TestRun'
s+= ' (testRunID,SN,siteID,stationID,testSequenceID,startTimestamp,endTimestamp,lastTestEntered,isPass)'
s+= ' VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s);'
v = [testRunID]+testRunSummary
#This converts YYYMMDD-hhmmss to YYY-MM-DD hh:mm:ss, if needed leaves it the same otherwise
v[5] = pUtils.dateTimeToString(pUtils.stringToDateTime(v[5]),1)
v[6] = pUtils.dateTimeToString(pUtils.stringToDateTime(v[6]),1)
return s,v
def prepsForTestMeasurement(self,measurementList,testRunID):
"""
Prepares the sql statement and values vector for the submission of the measurement list to the database
Args:
* measurementList (list): The data
* testRunID (str): The unique identifier to the test run
Returns:
A tupple (s,v) where
* s (str): The sql statement
* v (list): Vector with the values
"""
if len(measurementList)==0: return '',[]
s = 'INSERT INTO TestMeasurement'
s+= ' (testRunID,startTimestamp,endTimestamp,testName,testMeasurementName,dataType,stringMin,stringMeasurement,stringMax,doubleMin,doubleMeasurement,doubleMax,isPass)'
s+= ' VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
s+= ',(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'*(len(measurementList)-1)
s+= ';'
v = []
for measurement in measurementList:
data = measurement
if data[4]=='numeric':
formattedMeasurement = [testRunID]+data[0:8]+data[5:8]+data[8:]
else: #Is dataType is string or something else just store it as string
formattedMeasurement = [testRunID]+data[0:8]+[0,0,0]+data[8:]
#This converts YYYMMDD-hhmmss to YYY-MM-DD hh:mm:ss, if needed leaves it the same otherwise
formattedMeasurement[1] = pUtils.dateTimeToString(pUtils.stringToDateTime(formattedMeasurement[1]),1)
formattedMeasurement[2] = pUtils.dateTimeToString(pUtils.stringToDateTime(formattedMeasurement[2]),1)
v += formattedMeasurement
return s,v
def prepsForDictionary(self,d,testRunID,tableName):
"""
Prepares the sql statement and values vector for the submission of the specified dictionary data to the database
Args:
* d (list): The dictionary data
* testRunID (str): The unique identifier to the test run
* tableName (str): The database table to which this data should go
Returns:
A tupple (s,v) where
* s (str): The sql statement
* v (list): Vector with the values
"""
if len(d)==0: return '',[]
s = 'INSERT INTO '+tableName
s+= ' (testRunID,key,value)'
s+= ' VALUES (%s,%s,%s)'
s+= ',(%s,%s,%s)'*(len(d)-1)
s+= ';'
v = []
for key in d:
v += [testRunID]+[key]+[d[key]]
return s,v
def getNextTestSequenceID(self,SN,processID):
"""
| A route control query.
| Gets the ID of the test sequence that should be run on the UUT.
Args:
* SN (str): UUT serial number
* processID (str): The ID for the process to which this UUT belongs on its current assembly stage.
Returns:
* None, if the unit is not in the system
* A string containing the test sequence ID that should be run on the given UUT
"""
s = 'SELECT nextTestSequenceID FROM Transitions'
s+= ' WHERE SN = %s'
s+= ' AND processID = %s'
s+= 'ORDER BY creationTimeStamp DESC Limit 1'
s+= ';'
v = [SN,processID]
t=self.sql.quickSqlRead(s,v)
if len(t)==1:
return t[0][0]
return None
def addTransitions(self,entryList):
"""
| A route control query.
| Updates the transitions table.
Args:
* entryList (list): A list of entries of the form *(SN,processID,nextTestSequenceID)*
Returns:
None
"""
if len(entryList)==0: return
s = 'INSERT INTO Transitions'
s+= ' (SN,processID,nextTestSequenceID)'
s+= ' VALUES (%s,%s,%s)'
s+= ',(%s,%s,%s)'*(len(entryList)-1)
s+= ';'
v = [item for entry in entryList for item in entry]
self.sql.quickSqlWrite(s,v)
def getSubcomponentData(self,SN,testSequenceID):
"""
Retrieves the subcomponents for the specified UUT
"""
s = 'SELECT key,value FROM Components,TestRun'
s+= ' WHERE SN = %s'
s+= ' AND testSequenceID = %s'
s+= ' AND Components.testRunID = TestRun.testRunID'
s+= ';'
v = [SN,testSequenceID]
t=self.sql.quickSqlRead(s,v)
if len(t)==0:
return {}
d=[]
for key,value in t:
d.append({'processID':key,'SN':value})
return d
def createDatabase(self,databaseName,owner):
"""
Creates a database with the given name and owner
"""
v = []
s = 'CREATE DATABASE "%s"' % databaseName
s+= '\n WITH OWNER = postgres'
#v.append(owner)
s+= '\n ENCODING = %s'
v.append('UTF8')
s+= '\n TABLESPACE = pg_default'
s+= '\n LC_COLLATE = %s'
v.append('en_US.UTF-8')
s+= '\n LC_CTYPE = %s'
v.append('en_US.UTF-8')
s+= '\n CONNECTION LIMIT = -1;'
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
self.sql.conn()
self.sql.cnx.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
self.sql.execute(s,v)
self.sql.close()
def setupMTPdatabase(self):
"""
Assumes it is connected to an empty database and it sets it up as an MTP database.
"""
fileFullPath = os.path.join(os.environ['MTP_TESTSTATION'],'MTP','scripts','initDB.sql')
s = pUtils.quickFileRead(fileFullPath)
v = []
self.sql.quickSqlWrite(s,v)
def getTestRunIDlist(self,startRange=None,endRange=None):
"""
Returns a list of Test Run ID's for the given range
"""
def sqlAppend(first,noFirst,isFirst):
if isFirst:
return '\n '+first
return '\n '+noFirst
isFirst = True
v = []
s = 'SELECT testRunID from TestRun'
if startRange!=None:
s+= sqlAppend('WHERE ','AND ',isFirst)+'endTimestamp > %s'
v.append(startRange)
isFirst = False
if endRange!=None:
s+= sqlAppend('WHERE ','AND ',isFirst)+'endTimestamp < %s'
v.append(endRange)
isFirst = False
s+= '\n ;'
t = self.sql.quickSqlRead(s,v)
t = [item[0] for item in t]
return t
