Ignore:
Timestamp:
07/16/15 15:09:12 (6 years ago)
Author:
dglaser
Message:

#2388:

HeuristicLab.Services.Hive.DataAccess-3.3:

  • Removed old statistics tables
  • Updated SQL Scripts

HeuristicLab.Services.WebApp-3.3:
HeuristicLab.Services.WebApp.Status-3.3:
HeuristicLab.Services.WebApp.Statistics-3.3:

  • Minor changes
File:
1 edited

Legend:

Unmodified
Added
Removed
  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql

    r12761 r12768  
    9393GO
    9494
    95 ALTER TABLE [dbo].[Statistics] ALTER COLUMN StatisticsId ADD ROWGUIDCOL;
    96 ALTER TABLE [dbo].[Statistics] WITH NOCHECK ADD CONSTRAINT [DF_Statistics_StatisticsId] DEFAULT (NEWSEQUENTIALID()) FOR StatisticsId;
    97 GO
    98 
    99 ALTER TABLE [dbo].[DeletedJobStatistics] ALTER COLUMN DeletedJobStatisticsId ADD ROWGUIDCOL;
    100 ALTER TABLE [dbo].[DeletedJobStatistics] WITH NOCHECK ADD CONSTRAINT [DF_DeletedJobStatistics_DeletedJobStatisticsId] DEFAULT (NEWSEQUENTIALID()) FOR DeletedJobStatisticsId;
    101 GO
    102 
    103 ALTER TABLE [dbo].[SlaveStatistics]  DROP  CONSTRAINT [Statistics_SlaveStatistics]
    104 ALTER TABLE [dbo].[SlaveStatistics]  WITH CHECK ADD  CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY([StatisticsId])
    105 REFERENCES [dbo].[Statistics] ([StatisticsId])
    106 ON UPDATE CASCADE
    107 ON DELETE CASCADE
    108 GO
    109 
    110 ALTER TABLE [dbo].[UserStatistics]  DROP  CONSTRAINT [Statistics_UserStatistics]
    111 ALTER TABLE [dbo].[UserStatistics]  WITH CHECK ADD  CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY([StatisticsId])
    112 REFERENCES [dbo].[Statistics] ([StatisticsId])
    113 ON UPDATE CASCADE
    114 ON DELETE CASCADE
    115 GO
    116 
    11795/* create indices */
    11896CREATE INDEX Index_RequiredPlugins_TaskId ON RequiredPlugins(TaskId);
     
    147125GO
    148126
    149 -- =============================================
    150 -- Author:    cneumuel
    151 -- Description: returns aggregates statistic information for every minute
    152 -- =============================================
    153 CREATE VIEW [dbo].[view_Statistics]
    154 AS
    155 SELECT  CONVERT(VARCHAR(19), MIN(s.Timestamp), 120) AS DateTime, SUM(ss.Cores) AS Cores, SUM(ss.FreeCores) AS FreeCores,
    156         AVG(ss.CpuUtilization) AS CpuUtilization, SUM(ss.Memory) AS Memory, SUM(ss.FreeMemory) AS FreeMemory, x.exSum AS ExecutionTimeHours,
    157         x.exFinishedSum AS ExecutionTimeFinished, x.exStartToEndSum AS StartToEndTimeFinished
    158 FROM         dbo.SlaveStatistics AS ss INNER JOIN
    159                       dbo.[Statistics] AS s ON ss.StatisticsId = s.StatisticsId INNER JOIN
    160                       dbo.Resource AS r ON ss.SlaveId = r.ResourceId INNER JOIN
    161                           (SELECT     StatisticsId, SUM(ExecutionTimeMs) / 1000 / 60 / 60 AS exSum, SUM(ExecutionTimeMsFinishedJobs) / 1000 / 60 / 60 AS exFinishedSum,
    162                                                    SUM(StartToEndTimeMs) / 1000 / 60 / 60 AS exStartToEndSum
    163                             FROM          dbo.UserStatistics AS us
    164                             GROUP BY StatisticsId) AS x ON s.StatisticsId = x.StatisticsId
    165 GROUP BY s.StatisticsId, x.exSum, x.exFinishedSum, x.exStartToEndSum
    166 
    167 /* triggers */
    168 GO
    169127/****** Object:  Trigger [dbo].[tr_JobDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
    170128SET ANSI_NULLS ON
     
    191149-- Create date: 11.11.2010
    192150-- Description: Recursively deletes all child-jobs of a job when it is deleted. (Source: http://devio.wordpress.com/2008/05/23/recursive-delete-in-sql-server/)
    193 -- =============================================
     151-- =============================================DeletedJobStatistics
    194152CREATE TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS
    195153BEGIN
    196     -- add statistics
    197     INSERT INTO dbo.DeletedJobStatistics (UserId, ExecutionTimeS, ExecutionTimeSFinishedJobs, StartToEndTimeS)
    198154    SELECT
    199155        he.OwnerUserId AS UserId,
     
    238194GO
    239195
    240 
    241 CREATE TRIGGER [dbo].[tr_StatisticsDeleteCascade] ON [dbo].[Statistics] INSTEAD OF DELETE AS
    242 BEGIN
    243     DELETE SlaveStatistics FROM deleted, SlaveStatistics WHERE deleted.StatisticsId = SlaveStatistics.StatisticsId
    244     -- should also remove UserStatistics here
    245     DELETE [Statistics] FROM deleted, [Statistics] WHERE deleted.StatisticsId = [Statistics].StatisticsId
    246 END
    247 GO
    248 
    249 
    250196-- ============================================================
    251197-- Description: Create indices to speed up execution of queries
Note: See TracChangeset for help on using the changeset viewer.