Changeset 6229 for branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/prepareHiveDatabase.sql

Timestamp:

05/17/11 18:29:41 (13 years ago)

Author:

cneumuel

Message:

#1233

• added basic statistics recording for (once per minute)
  • executiontime per user
  • usedcores, usedmemory per slave

File:

• branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/prepareHiveDatabase.sql (modified) (2 diffs)

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/prepareHiveDatabase.sql

@@ -104 +104 @@
 GO
 
+ALTER TABLE [dbo].[Statistics] ALTER COLUMN StatisticsId ADD ROWGUIDCOL;
+ALTER TABLE [dbo].[Statistics] WITH NOCHECK ADD CONSTRAINT [DF_Statistics_StatisticsId] DEFAULT (newid()) FOR StatisticsId;
+GO
+
+ALTER TABLE [dbo].[SlaveStatistics]  DROP  CONSTRAINT [Statistics_SlaveStatistics]
+ALTER TABLE [dbo].[SlaveStatistics]  WITH CHECK ADD  CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY([StatisticsId])
+REFERENCES [dbo].[Statistics] ([StatisticsId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+
+ALTER TABLE [dbo].[UserStatistics]  DROP  CONSTRAINT [Statistics_UserStatistics]
+ALTER TABLE [dbo].[UserStatistics]  WITH CHECK ADD  CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY([StatisticsId])
+REFERENCES [dbo].[Statistics] ([StatisticsId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+
 /* create indices */
 CREATE INDEX Index_RequiredPlugins_JobId ON RequiredPlugins(JobId);
@@ -159 +177 @@
   DELETE Job FROM Job INNER JOIN #Table ON Job.JobId = #Table.JobId
 END
+GO
+
+-- =============================================
+-- Author:    cneumuel
+-- Create date: 17.05.2011
+-- Description: Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
+-- =============================================
+--CREATE TRIGGER [dbo].[tr_DeletedJobStats] ON [dbo].[Job] AFTER DELETE AS
+--BEGIN
+--  INSERT INTO DeletedJobStats SELECT JobId, ExecutionTime FROM deleted
+--END