def ingest_normalization_report(request, uuid): query = getNormalizationReportQuery() cursor = connection.cursor() cursor.execute(query, ( uuid, uuid, uuid, uuid, uuid, uuid, uuid, uuid )) objects = helpers.dictfetchall(cursor) return render(request, 'ingest/normalization_report.html', locals())
def getNormalizationReportQuery(sipUUID, idsRestriction=""): if idsRestriction: idsRestriction = 'AND (%s)' % idsRestriction cursor = connection.cursor() # not fetching name of ID Tool, don't think we need it. sql = """ select CONCAT(a.currentLocation, ' ', a.fileUUID,' ', IFNULL(a.fileID, "")) AS 'pagingIndex', a.fileUUID, a.location, substring(a.currentLocation,23) as fileName, a.fileID, a.description, a.already_in_access_format, a.already_in_preservation_format, case when c.exitCode < 2 and a.fileID is not null then 1 else 0 end as access_normalization_attempted, case when a.fileID is not null and c.exitcode = 1 then 1 else 0 end as access_normalization_failed, case when b.exitCode < 2 and a.fileID is not null then 1 else 0 end as preservation_normalization_attempted, case when a.fileID is not null and b.exitcode = 1 then 1 else 0 end as preservation_normalization_failed, c.taskUUID as access_normalization_task_uuid, b.taskUUID as preservation_normalization_task_uuid, c.exitCode as access_task_exitCode, b.exitCode as preservation_task_exitCode from ( select f.fileUUID, f.sipUUID, f.originalLocation as location, f.currentLocation, fid.uuid as 'fileID', fid.description, f.fileGrpUse, fid.access_format AS 'already_in_access_format', fid.preservation_format AS 'already_in_preservation_format' from Files f Left Join FilesIdentifiedIDs fii on f.fileUUID = fii.fileUUID Left Join fpr_formatversion fid on fii.fileID = fid.uuid where f.fileGrpUse in ('original', 'service') and f.sipUUID = %s ) a Left Join ( select j.sipUUID, t.fileUUID, t.taskUUID, t.exitcode from Jobs j Join Tasks t on t.jobUUID = j.jobUUID where j.jobType = 'Normalize for preservation' ) b on a.fileUUID = b.fileUUID and a.sipUUID = b.sipUUID Left Join ( select j.sipUUID, t.fileUUID, t.taskUUID, t.exitcode from Jobs j join Tasks t on t.jobUUID = j.jobUUID Where j.jobType = 'Normalize for access' ) c ON a.fileUUID = c.fileUUID AND a.sipUUID = c.sipUUID WHERE a.sipUUID = %s order by (access_normalization_failed + preservation_normalization_failed) desc; """ cursor.execute(sql, (sipUUID, sipUUID)) objects = helpers.dictfetchall(cursor) return objects
def getNormalizationReportQuery(sipUUID, idsRestriction=""): if idsRestriction: idsRestriction = 'AND (%s)' % idsRestriction cursor = connection.cursor() sql = """ SELECT variableValue FROM UnitVariables WHERE unitType = 'SIP' AND variable = 'normalizationFileIdentificationToolIdentifierTypes' AND unitUUID = '{0}'; """.format(sipUUID) cursor.execute(sql) fileIDTypeUsed = cursor.fetchone() fileIDTypeUsed = str(fileIDTypeUsed[0]) print "fileIDTypeUsed " + fileIDTypeUsed sql = """ select CONCAT(a.currentLocation, ' ', a.fileUUID,' ', IFNULL(b.fileID, "")) AS 'pagingIndex', a.fileUUID, a.location, substring(a.currentLocation,23) as fileName, a.fileID, a.description, a.already_in_access_format, a.already_in_preservation_format, b.access_normalization_attempted, b.preservation_normalization_attempted, b.access_normalization_task_uuid, b.preservation_normalization_task_uuid, b.access_normalization_failed, b.preservation_normalization_failed, b.access_task_exitCode, b.preservation_task_exitCode from (select f.fileUUID, f.sipUUID, f.originalLocation as location, f.currentLocation, fid.pk as 'fileID', fid.description, fid.validAccessFormat AS 'already_in_access_format', fid.validPreservationFormat AS 'already_in_preservation_format' from Files f Join FilesIdentifiedIDs fii on f.fileUUID = fii.fileUUID Join FileIDs fid on fii.fileID = fid.pk Join FileIDTypes on FileIDTypes.pk = fid.fileIDType where f.fileGrpUse in ('original', 'service') and f.sipUUID = '{0}' and ({1}) ) a Left Join (select cr.fileID, j.sipUUID, max(if(cc.classification = 'access', t.taskUUID, null)) IS NOT NULL as access_normalization_attempted, max(if(cc.classification = 'preservation', t.taskUUID, null)) IS NOT NULL as preservation_normalization_attempted, max(if(cc.classification = 'access', t.taskUUID, null)) as access_normalization_task_uuid, max(if(cc.classification = 'preservation', t.taskUUID, null)) as preservation_normalization_task_uuid, max(if(cc.classification = 'access', t.exitCode, null)) != 0 AS access_normalization_failed, max(if(cc.classification = 'preservation', t.exitCode, null)) != 0 AS preservation_normalization_failed, max(if(cc.classification = 'access', t.exitCode, null)) as access_task_exitCode, max(if(cc.classification = 'preservation', t.exitCode, null)) as preservation_task_exitCode from CommandRelationships cr Join CommandClassifications cc on cr.commandClassification = cc.pk Join TasksConfigs tc on tc.taskTypePKReference = cr.pk join MicroServiceChainLinks ml on tc.pk = ml.currentTask Join Jobs j on j.MicroServiceChainLinksPK = ml.pk join Tasks t on t.jobUUID = j.jobUUID where cc.classification in ('preservation', 'access') and j.sipUUID = '{0}' group by cr.fileID ) b on a.fileID = b.fileID and a.sipUUID = b.sipUUID where a.sipUUID = '{0}' order by fileName, fileID; """.format(sipUUID, fileIDTypeUsed) cursor.execute(sql) objects = helpers.dictfetchall(cursor) #objects = databaseInterface.queryAllSQL(sql) return objects
def getNormalizationReportQuery(sipUUID, idsRestriction=""): if idsRestriction: idsRestriction = 'AND (%s)' % idsRestriction cursor = connection.cursor() sql = """ SELECT variableValue FROM UnitVariables WHERE unitType = 'SIP' AND variable = 'normalizationFileIdentificationToolIdentifierTypes' AND unitUUID = '{0}'; """.format(sipUUID) cursor.execute(sql) fileIDTypeUsed = cursor.fetchone() fileIDTypeUsed = str(fileIDTypeUsed[0]) print "fileIDTypeUsed " + fileIDTypeUsed sql = """ select CONCAT(a.currentLocation, ' ', a.fileUUID,' ', IFNULL(b.fileID, "")) AS 'pagingIndex', a.fileUUID, a.location, substring(a.currentLocation,23) as fileName, a.fileID, a.description, a.already_in_access_format, a.already_in_preservation_format, b.access_normalization_attempted, b.preservation_normalization_attempted, b.access_normalization_task_uuid, b.preservation_normalization_task_uuid, b.access_normalization_failed, b.access_task_exitCode, b.preservation_task_exitCode from (select f.fileUUID, f.sipUUID, f.originalLocation as location, f.currentLocation, fid.pk as 'fileID', fid.description, fid.validAccessFormat AS 'already_in_access_format', fid.validPreservationFormat AS 'already_in_preservation_format' from Files f Join FilesIdentifiedIDs fii on f.fileUUID = fii.fileUUID Join FileIDs fid on fii.fileID = fid.pk Join FileIDTypes on FileIDTypes.pk = fid.fileIDType where f.fileGrpUse in ('original', 'service') and f.sipUUID = '{0}' and {1} ) a Left Join (select cr.fileID, j.sipUUID, max(if(cc.classification = 'access', t.taskUUID, null)) IS NOT NULL as access_normalization_attempted, max(if(cc.classification = 'preservation', t.taskUUID, null)) IS NOT NULL as preservation_normalization_attempted, max(if(cc.classification = 'access', t.taskUUID, null)) as access_normalization_task_uuid, max(if(cc.classification = 'preservation', t.taskUUID, null)) as preservation_normalization_task_uuid, max(if(cc.classification = 'access', t.exitCode, null)) != 0 AS access_normalization_failed, max(if(cc.classification = 'preservation', t.exitCode, null)) != 0 AS preservation_normalization_failed, max(if(cc.classification = 'access', t.exitCode, null)) as access_task_exitCode, max(if(cc.classification = 'preservation', t.exitCode, null)) as preservation_task_exitCode from CommandRelationships cr Join CommandClassifications cc on cr.commandClassification = cc.pk Join TasksConfigs tc on tc.taskTypePKReference = cr.pk join MicroServiceChainLinks ml on tc.pk = ml.currentTask Join Jobs j on j.MicroServiceChainLinksPK = ml.pk join Tasks t on t.jobUUID = j.jobUUID where cc.classification in ('preservation', 'access') group by cr.fileID ) b on a.fileID = b.fileID and a.sipUUID = b.sipUUID; """.format(sipUUID, fileIDTypeUsed) cursor.execute(sql) objects = helpers.dictfetchall(cursor) #objects = databaseInterface.queryAllSQL(sql) return objects