Free cookie consent management tool by TermsFeed Policy Generator

Ignore:
Timestamp:
07/16/15 15:09:12 (9 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
Location:
branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts
Files:
2 edited

Legend:

Unmodified
Added
Removed

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

    r12584 r12768  
    131131  [DateEnqueued] DateTime NOT NULL,
    132132  CONSTRAINT [PK_UserPriority] PRIMARY KEY ([UserId])
    133   )
    134 CREATE TABLE [DeletedJobStatistics](
    135   [UserId] UniqueIdentifier NOT NULL,
    136   [ExecutionTimeS] float NOT NULL,
    137   [ExecutionTimeSFinishedJobs] float NOT NULL,
    138   [StartToEndTimeS] float NOT NULL,
    139   [DeletedJobStatisticsId] UniqueIdentifier NOT NULL,
    140   CONSTRAINT [PK_DeletedJobStatistics] PRIMARY KEY ([DeletedJobStatisticsId])
    141   )
    142 CREATE TABLE [UserStatistics](
    143   [StatisticsId] UniqueIdentifier NOT NULL,
    144   [UserId] UniqueIdentifier NOT NULL,
    145   [ExecutionTimeMs] float NOT NULL,
    146   [UsedCores] Int NOT NULL,
    147   [ExecutionTimeMsFinishedJobs] float NOT NULL,
    148   [StartToEndTimeMs] float NOT NULL,
    149   CONSTRAINT [PK_UserStatistics] PRIMARY KEY ([StatisticsId], [UserId])
    150   )
    151 CREATE TABLE [SlaveStatistics](
    152   [StatisticsId] UniqueIdentifier NOT NULL,
    153   [SlaveId] UniqueIdentifier NOT NULL,
    154   [Cores] Int NOT NULL,
    155   [FreeCores] Int NOT NULL,
    156   [CpuUtilization] float NOT NULL,
    157   [Memory] Int NOT NULL,
    158   [FreeMemory] Int NOT NULL,
    159   CONSTRAINT [PK_SlaveStatistics] PRIMARY KEY ([StatisticsId], [SlaveId])
    160   )
    161 CREATE TABLE [Statistics](
    162   [StatisticsId] UniqueIdentifier NOT NULL,
    163   [Timestamp] DateTime NOT NULL,
    164   CONSTRAINT [PK_Statistics] PRIMARY KEY ([StatisticsId])
    165133  )
    166134ALTER TABLE [dbo].[AssignedResources]
    … …  
    192160ALTER TABLE [dbo].[JobPermission]
    193161  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
    194 ALTER TABLE [UserStatistics]
    195   ADD CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
    196 ALTER TABLE [SlaveStatistics]
    197   ADD CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
    198162
    199163GO

  • 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.