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
