...
Code Block |
---|
SELECT taskId, taskProperties FROM Tasks WHERE taskId='MC00:000000752_12' --no longer available in 2017.02 DELETE FROM CurrentModuleInstanceRuns WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12') DELETE FROM ArchiveMetaData WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12') DELETE FROM Reports WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12') DELETE FROM ModuleInstanceRuns WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12') DELETE FROM ModuleRunTables WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12') DELETE FROM TaskRunCompletions WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12') DELETE FROM TimeSeries WHERE creatorTaskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12') DELETE FROM ArchiveMetaData WHERE taskRunId IN (SELECT taskRunId FROM TaskRuns WHERE taskId='MC00:000000752_12') DELETE FROM TaskRuns WHERE taskId='MC00:000000752_12' DELETE FROM Tasks WHERE taskId='MC00:000000752_12' |
...
Deletion of a faulty TaskRun
When a selection of TaskRuns in the database need to be removed (e.g. Taskruns older than 100 days):
Code Block | ||
---|---|---|
| ||
First use the <basequery> for the database of your choice to find all the taskrunIds
--Oracle
--SELECT taskRunId FROM TaskRuns WHERE creationTime < SYSTIMESTAMP AT TIME ZONE 'GMT' - NUMTODSINTERVAL(100, 'day')
--Postgres
--SELECT taskRunId FROM TaskRuns WHERE creationTime < CURRENT_TIMESTAMP AT TIME ZONE 'GMT' - interval '100 day'
--SQLServer
SELECT taskRunId FROM TaskRuns WHERE creationTime < GETUTCDATE()-100
--when happy with the TaskRuns to be removed do a cascade delete:
DELETE FROM ArchiveMetaData WHERE taskRunId IN (<baseQuery>)
DELETE FROM Reports WHERE taskRunId IN (<baseQuery>)
DELETE FROM ModuleInstanceRuns WHERE taskRunId IN (<baseQuery>)
DELETE FROM ModuleRunTables WHERE taskRunId IN (<baseQuery>)
DELETE FROM TaskRunCompletions WHERE taskRunId IN (<baseQuery>)
DELETE FROM TimeSeries WHERE creatorTaskRunId IN (<baseQuery>)
DELETE FROM ArchiveMetaData WHERE taskRunId IN (<baseQuery>)
DELETE FROM TaskRuns WHERE taskId IN (<baseQuery>) |