Changeset 12761


Ignore:
Timestamp:
07/14/15 15:56:26 (6 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
Files:
43 added
12 edited

Legend:

Unmodified
Added
Removed
  • branches/HiveStatistics/sources/HeuristicLab 3.3 Services.sln

    r12691 r12761  
    7171Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "HeuristicLab.Services.WebApp.Statistics-3.3", "HeuristicLab.Services.WebApp.Statistics\3.3\HeuristicLab.Services.WebApp.Statistics-3.3.csproj", "{1BC54353-7D4C-4734-BAA9-3D11B3242B59}"
    7272EndProject
    73 Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ConsoleServiceTest", "ConsoleServiceTest\ConsoleServiceTest.csproj", "{72EAF349-27C8-4CFE-B03B-47E2C5E124FD}"
     73Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "HeuristicLab.Services.WebApp.Maintenance-3.3", "HeuristicLab.Services.WebApp.Maintenance\3.3\HeuristicLab.Services.WebApp.Maintenance-3.3.csproj", "{3F22B7DA-FDDB-48F0-8BB7-0ABA5120FC87}"
    7474EndProject
    7575Global
     
    258258    {1BC54353-7D4C-4734-BAA9-3D11B3242B59}.Release|x86.ActiveCfg = Release|x86
    259259    {1BC54353-7D4C-4734-BAA9-3D11B3242B59}.Release|x86.Build.0 = Release|x86
    260     {72EAF349-27C8-4CFE-B03B-47E2C5E124FD}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
    261     {72EAF349-27C8-4CFE-B03B-47E2C5E124FD}.Debug|Any CPU.Build.0 = Debug|Any CPU
    262     {72EAF349-27C8-4CFE-B03B-47E2C5E124FD}.Debug|x64.ActiveCfg = Debug|Any CPU
    263     {72EAF349-27C8-4CFE-B03B-47E2C5E124FD}.Debug|x86.ActiveCfg = Debug|Any CPU
    264     {72EAF349-27C8-4CFE-B03B-47E2C5E124FD}.Release|Any CPU.ActiveCfg = Release|Any CPU
    265     {72EAF349-27C8-4CFE-B03B-47E2C5E124FD}.Release|Any CPU.Build.0 = Release|Any CPU
    266     {72EAF349-27C8-4CFE-B03B-47E2C5E124FD}.Release|x64.ActiveCfg = Release|Any CPU
    267     {72EAF349-27C8-4CFE-B03B-47E2C5E124FD}.Release|x86.ActiveCfg = Release|Any CPU
     260    {3F22B7DA-FDDB-48F0-8BB7-0ABA5120FC87}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
     261    {3F22B7DA-FDDB-48F0-8BB7-0ABA5120FC87}.Debug|Any CPU.Build.0 = Debug|Any CPU
     262    {3F22B7DA-FDDB-48F0-8BB7-0ABA5120FC87}.Debug|x64.ActiveCfg = Debug|Any CPU
     263    {3F22B7DA-FDDB-48F0-8BB7-0ABA5120FC87}.Debug|x86.ActiveCfg = Debug|Any CPU
     264    {3F22B7DA-FDDB-48F0-8BB7-0ABA5120FC87}.Release|Any CPU.ActiveCfg = Release|Any CPU
     265    {3F22B7DA-FDDB-48F0-8BB7-0ABA5120FC87}.Release|Any CPU.Build.0 = Release|Any CPU
     266    {3F22B7DA-FDDB-48F0-8BB7-0ABA5120FC87}.Release|x64.ActiveCfg = Release|Any CPU
     267    {3F22B7DA-FDDB-48F0-8BB7-0ABA5120FC87}.Release|x86.ActiveCfg = Release|Any CPU
    268268  EndGlobalSection
    269269  GlobalSection(SolutionProperties) = preSolution
  • 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
  • branches/HiveStatistics/sources/HeuristicLab.Services.Hive/3.3/ServiceContracts/IHiveService.cs

    r12012 r12761  
    234234
    235235    [OperationContract]
    236     void UpdateDowntime(Downtime downtime);
     236    void UpdateDowntime(Downtime downtimeDto);
    237237
    238238    [OperationContract]
  • branches/HiveStatistics/sources/HeuristicLab.Services.WebApp.Status/3.3/WebApp/history/history.cshtml

    r12691 r12761  
    5151</div>
    5252
    53 
    5453<div class="default-view-container">
    5554    <div class="row">
  • branches/HiveStatistics/sources/HeuristicLab.Services.WebApp/3.3/Web.config

    r12584 r12761  
    3838      <mimeMap fileExtension=".woff2" mimeType="application/font-woff" />
    3939    </staticContent>
    40  
    4140    <modules>
    4241      <remove name="FormsAuthentication" />
     
    5453      <remove name="TRACEVerbHandler" />
    5554      <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="*" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />
    56     </handlers></system.webServer>
     55    </handlers>
     56  </system.webServer>
    5757  <runtime>
    5858    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
  • branches/HiveStatistics/sources/HeuristicLab.Services.WebApp/3.3/WebApp/app.css

    r12515 r12761  
    416416}
    417417
     418.default-view-header {
     419  padding: 30px;
     420  background: #F5F5F5;
     421  border-bottom: 1px solid #D3D3D3;
     422}
     423
    418424.panel-heading a:after {
    419425    font-family:'Glyphicons Halflings';
Note: See TracChangeset for help on using the changeset viewer.