Ticket #2388: RemoveOldStatistics.sql

USE [HeuristicLab.Hive-3.3]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

/* remove statistics from trigger */
ALTER TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS 
BEGIN
  SELECT 
    he.OwnerUserId AS UserId, 
    ROUND(SUM(j.ExecutionTimeMs) / 1000, 0) AS ExecutionTimeS,  
    ROUND(ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END), 0) / 1000, 0) AS ExecutionTimeSFinishedJobs,
    ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(s, fs.DateTime, ls.DateTime) ELSE 0 END), 0) AS StartToEndTimeS
  FROM 
    deleted j, 
    Job he, 
    view_FirstState fs, 
    view_LastState ls
  WHERE 
    he.JobId = j.JobId AND 
    fs.TaskId = j.TaskId AND 
    ls.TaskId = j.TaskId
  GROUP BY he.OwnerUserId

  -- recursively delete jobs
  CREATE TABLE #Table( 
    TaskId uniqueidentifier 
  )
  INSERT INTO #Table (TaskId)
  SELECT TaskId FROM deleted
  
  DECLARE @c INT
  SET @c = 0
  
  WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
    SELECT @c = COUNT(TaskId) FROM #Table
    
    INSERT INTO #Table (TaskId)
      SELECT Task.TaskId
      FROM Task
      LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
      WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
        AND #Table.TaskId IS NULL
  END
  
  DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
  DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
END
GO

/* drop obsolete triggers/views/tables */
DROP TRIGGER [dbo].[tr_StatisticsDeleteCascade]
GO

DROP VIEW [dbo].[view_Statistics]
GO

DROP TABLE [dbo].[DeletedJobStatistics]
GO

DROP TABLE [dbo].[UserStatistics]
GO

DROP TABLE [dbo].[SlaveStatistics]
GO

DROP TABLE [dbo].[Statistics]
GO