Changeset 12761 for branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts

Timestamp:

07/14/15 15:56:26 (9 years ago)

Author:

dglaser

Message:

#2429: Worked on the maintenance WebApp plugin:

• Space Usage Page: Displays the number of rows and allocated disk space for every database table
• Plugin Page: Shows unused plugins and provides functionality to delete all and specific plugins
• FactTask Page: Allows to aggregate all Job Tasks to a single task for a given job or jobs within an selected time period
• FactClientInfo Page: Allows to aggregate consecutive FactClientInfo entries with the same state and isallowedtocalculate flag

File:

• branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql (modified) (3 diffs)

branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql

@@ -182 +182 @@
 CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS 
 BEGIN
-  DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId
-  DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId
+    DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId
+    DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId
 END
 GO
@@ -194 +194 @@
 CREATE TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS 
 BEGIN
-  -- add statistics
-  INSERT INTO dbo.DeletedJobStatistics (UserId, ExecutionTimeS, ExecutionTimeSFinishedJobs, StartToEndTimeS)
-  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
+    -- add statistics
+    INSERT INTO dbo.DeletedJobStatistics (UserId, ExecutionTimeS, ExecutionTimeSFinishedJobs, StartToEndTimeS)
+    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
@@ -241 +241 @@
 CREATE TRIGGER [dbo].[tr_StatisticsDeleteCascade] ON [dbo].[Statistics] INSTEAD OF DELETE AS 
 BEGIN
-  DELETE SlaveStatistics FROM deleted, SlaveStatistics WHERE deleted.StatisticsId = SlaveStatistics.StatisticsId
-  -- should also remove UserStatistics here 
-  DELETE [Statistics] FROM deleted, [Statistics] WHERE deleted.StatisticsId = [Statistics].StatisticsId
+    DELETE SlaveStatistics FROM deleted, SlaveStatistics WHERE deleted.StatisticsId = SlaveStatistics.StatisticsId
+    -- should also remove UserStatistics here 
+    DELETE [Statistics] FROM deleted, [Statistics] WHERE deleted.StatisticsId = [Statistics].StatisticsId
 END
 GO