Free cookie consent management tool by TermsFeed Policy Generator

Ignore:
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
Location:
branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3
Files:
2 added
7 edited

Legend:

Unmodified
Added
Removed

  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/DimTimeDao.cs

    r12691 r12761  
    3131      return GetByIdQuery(DataContext, id);
    3232    }
     33
    3334    public DimTime GetLastEntry() {
    3435      return GetLastEntryQuery(DataContext);
     36    }
     37
     38    public int DeleteUnusedTimes() {
     39      return DataContext.ExecuteCommand(DeleteUnusedTimeEntriesStringQuery);
    3540    }
    3641
    … …  
    4853         select dimTime).FirstOrDefault());
    4954    #endregion
     55
     56    #region String queries
     57    private const string DeleteUnusedTimeEntriesStringQuery = @"
     58      DELETE FROM [statistics].[DimTime]
     59      WHERE NOT EXISTS (SELECT [Time] FROM [statistics].[FactClientInfo] fci
     60                        WHERE fci.[Time] = [statistics].[DimTime].[Time]);   
     61    ";
     62    #endregion
    5063  }
    5164}

  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/FactTaskDao.cs

    r12691 r12761  
    8989    }
    9090
     91    public void DeleteByJobId(Guid jobId) {
     92      DataContext.ExecuteCommand(DeleteByJobIdQuery, jobId);
     93    }
     94
    9195    #region Compiled queries
    9296    private static readonly Func<DataContext, Guid, FactTask> GetByIdQuery =
    … …  
    108112      @"DELETE FROM [statistics].[FactTask]
    109113         WHERE TaskId IN ({0});";
     114
     115    private const string DeleteByJobIdQuery =
     116      @"DELETE FROM [statistics].[FactTask]
     117         WHERE JobId = {0};";
    110118    #endregion
    111119  }

  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/PluginDao.cs

    r12691 r12761  
    3737    }
    3838
     39    public int DeleteUnusedPlugins() {
     40      return DataContext.ExecuteCommand(DeleteUnusedPluginsStringQuery);
     41    }
     42
    3943    #region Compiled queries
    4044    private static readonly Func<DataContext, Guid, Plugin> GetByIdQuery =
    … …  
    5054         select plugin));
    5155    #endregion
     56
     57    #region String queries
     58    private const string DeleteUnusedPluginsStringQuery = @"
     59      DELETE FROM [Plugin]
     60      WHERE  [Plugin].[PluginId] NOT IN (
     61        SELECT DISTINCT rp.[PluginId]
     62        FROM [RequiredPlugins] rp
     63        WHERE EXISTS (SELECT [TaskId]
     64                      FROM [Task] t
     65                      WHERE t.[TaskId] = rp.[TaskId])
     66      );
     67    ";
     68    #endregion
    5269  }
    5370}

  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/HeuristicLab.Services.Hive.DataAccess-3.3.csproj

    r12468 r12761  
    127127    <Compile Include="Daos\TaskDataDao.cs" />
    128128    <Compile Include="Daos\UserPriorityDao.cs" />
     129    <Compile Include="Data\TableInformation.cs" />
    129130    <Compile Include="Interfaces\IGenericDao.cs" />
    130131    <Compile Include="Enums\Command.cs" />

  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/Interfaces/IPersistenceManager.cs

    r12468 r12761  
    2424using HeuristicLab.Services.Hive.DataAccess.Daos;
    2525using HeuristicLab.Services.Hive.DataAccess.Daos.HiveStatistics;
     26using HeuristicLab.Services.Hive.DataAccess.Data;
    2627
    2728namespace HeuristicLab.Services.Hive.DataAccess.Interfaces {
    … …  
    6263    #endregion
    6364
     65    TableInformation GetTableInformation(string table);
    6466    void SubmitChanges();
    6567  }

  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/Manager/PersistenceManager.cs

    r12691 r12761  
    2222using System;
    2323using System.Data.Linq;
     24using System.Linq;
    2425using System.Transactions;
    2526using HeuristicLab.Services.Hive.DataAccess.Daos;
    2627using HeuristicLab.Services.Hive.DataAccess.Daos.HiveStatistics;
     28using HeuristicLab.Services.Hive.DataAccess.Data;
    2729using HeuristicLab.Services.Hive.DataAccess.Interfaces;
    2830
    … …  
    200202    #endregion
    201203
     204    // str.Remove(str.IndexOf(','));
     205    public TableInformation GetTableInformation(string table) {
     206      string query = string.Format("sp_spaceused '{0}', @updateusage='true'", table);
     207      var result = dataContext.ExecuteQuery<SqlServerTableInformation>(query).FirstOrDefault();
     208      if (result == null) return null;
     209      return new TableInformation {
     210        Name = result.Name,
     211        Rows = int.Parse(result.Rows.Remove(result.Rows.IndexOf(' '))),
     212        Reserved = int.Parse(result.Reserved.Remove(result.Reserved.IndexOf(' '))),
     213        Data = int.Parse(result.Data.Remove(result.Data.IndexOf(' '))),
     214        IndexSize = int.Parse(result.Index_Size.Remove(result.Index_Size.IndexOf(' '))),
     215        Unused = int.Parse(result.Unused.Remove(result.Unused.IndexOf(' ')))
     216      };
     217    }
     218
    202219    public void SubmitChanges() {
    203220      if (dataContext != null) {
    … …  
    211228      //}
    212229    }
     230
     231    private class SqlServerTableInformation {
     232      public string Name { get; set; }
     233      public string Rows { get; set; }
     234      public string Reserved { get; set; }
     235      public string Data { get; set; }
     236      public string Index_Size { get; set; } // naming of sp_spaceused...
     237      public string Unused { get; set; }
     238    }
    213239  }
    214240}

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

    r9665 r12761  
    182182CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS
    183183BEGIN
    184   DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId
    185   DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId
     184    DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId
     185    DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId
    186186END
    187187GO
    … …  
    194194CREATE TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS
    195195BEGIN
    196   -- add statistics
    197   INSERT INTO dbo.DeletedJobStatistics (UserId, ExecutionTimeS, ExecutionTimeSFinishedJobs, StartToEndTimeS)
    198   SELECT
    199     he.OwnerUserId AS UserId,
    200     ROUND(SUM(j.ExecutionTimeMs) / 1000, 0) AS ExecutionTimeS, 
    201     ROUND(ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END), 0) / 1000, 0) AS ExecutionTimeSFinishedJobs,
    202     ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(s, fs.DateTime, ls.DateTime) ELSE 0 END), 0) AS StartToEndTimeS
    203   FROM
    204     deleted j,
    205     Job he,
    206     view_FirstState fs,
    207     view_LastState ls
    208   WHERE
    209     he.JobId = j.JobId AND
    210     fs.TaskId = j.TaskId AND
    211     ls.TaskId = j.TaskId
    212   GROUP BY he.OwnerUserId
    213 
    214   -- recursively delete jobs
    215   CREATE TABLE #Table(
    216     TaskId uniqueidentifier
    217   )
    218   INSERT INTO #Table (TaskId)
    219   SELECT TaskId FROM deleted
    220  
    221   DECLARE @c INT
    222   SET @c = 0
    223  
    224   WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
    225     SELECT @c = COUNT(TaskId) FROM #Table
    226    
    227     INSERT INTO #Table (TaskId)
    228       SELECT Task.TaskId
    229       FROM Task
    230       LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
    231       WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
    232         AND #Table.TaskId IS NULL
    233   END
    234  
    235   DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
    236   DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
     196    -- add statistics
     197    INSERT INTO dbo.DeletedJobStatistics (UserId, ExecutionTimeS, ExecutionTimeSFinishedJobs, StartToEndTimeS)
     198    SELECT
     199        he.OwnerUserId AS UserId,
     200        ROUND(SUM(j.ExecutionTimeMs) / 1000, 0) AS ExecutionTimeS, 
     201        ROUND(ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END), 0) / 1000, 0) AS ExecutionTimeSFinishedJobs,
     202        ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(s, fs.DateTime, ls.DateTime) ELSE 0 END), 0) AS StartToEndTimeS
     203    FROM
     204        deleted j,
     205        Job he,
     206        view_FirstState fs,
     207        view_LastState ls
     208    WHERE
     209        he.JobId = j.JobId AND
     210        fs.TaskId = j.TaskId AND
     211        ls.TaskId = j.TaskId
     212    GROUP BY he.OwnerUserId
     213
     214    -- recursively delete jobs
     215    CREATE TABLE #Table(
     216        TaskId uniqueidentifier
     217    )
     218    INSERT INTO #Table (TaskId)
     219    SELECT TaskId FROM deleted
     220   
     221    DECLARE @c INT
     222    SET @c = 0
     223   
     224    WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
     225        SELECT @c = COUNT(TaskId) FROM #Table
     226       
     227        INSERT INTO #Table (TaskId)
     228            SELECT Task.TaskId
     229            FROM Task
     230            LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
     231            WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
     232                AND #Table.TaskId IS NULL
     233    END
     234   
     235    DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
     236    DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
    237237END
    238238GO
    … …  
    241241CREATE TRIGGER [dbo].[tr_StatisticsDeleteCascade] ON [dbo].[Statistics] INSTEAD OF DELETE AS
    242242BEGIN
    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
     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
    246246END
    247247GO
Note: See TracChangeset for help on using the changeset viewer.