Changeset 15737


Ignore:
Timestamp:
02/08/18 11:13:15 (19 months ago)
Author:
jzenisek
Message:

#2839

  • updated sql scripts (necessary foreign key option alterations & introduction of statistic tables)
  • updated HiveService according to changed client side (deletion-routine, permission checking,...)
Location:
branches/HiveProjectManagement
Files:
8 edited

Legend:

Unmodified
Added
Removed
  • branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedJobResourceDao.cs

    r15630 r15737  
    9696          SELECT j.JobId
    9797          FROM [Job] j
    98           WHERE j.ProjectId = '{0}'
     98          WHERE j.ProjectId = {0}
    9999        )
    100100    ";
     
    105105          SELECT j.JobId
    106106          FROM [Job] j
    107           WHERE j.ProjectId = '{0}'
     107          WHERE j.ProjectId = {0}
    108108          AND j.OwnerUserId IN ({1})
    109109        )
  • branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs

    r15666 r15737  
    3737    public override Project GetById(Guid id) {
    3838      return GetByIdQuery(DataContext, id);
     39    }
     40
     41    public void DeleteByIds(IEnumerable<Guid> ids) {
     42      string paramProjectIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x)));
     43      if(!string.IsNullOrWhiteSpace(paramProjectIds)) {
     44        string queryString = string.Format(DeleteByIdsQueryString, paramProjectIds);
     45        DataContext.ExecuteCommand(queryString);
     46      }
    3947    }
    4048
     
    93101
    94102    #region String queries
     103    private const string DeleteByIdsQueryString = @"
     104      DELETE FROM [Project]
     105      WHERE ProjectId IN ({0})
     106    ";
     107
    95108    private const string GetUsageStatsPerProjectQueryString = @"
    96109      SELECT j.ProjectId, SUM(t.CoresNeeded) AS Cores, SUM(t.MemoryNeeded) AS Memory
  • branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourceDao.cs

    r15577 r15737  
    3535    public Resource GetByName(string name) {
    3636      return GetByNameQuery(DataContext, name);
     37    }
     38
     39    public void DeleteByIds(IEnumerable<Guid> ids) {
     40      string paramResourceIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x)));
     41      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
     42        string queryString = string.Format(DeleteByIdsQueryString, paramResourceIds);
     43        DataContext.ExecuteCommand(queryString);
     44      }
    3745    }
    3846
     
    8997
    9098    #region String queries
     99    private const string DeleteByIdsQueryString = @"
     100      DELETE FROM [Resource]
     101      WHERE ResourceId IN ({0})
     102    ";
    91103    private const string CountExistenceQuery = @"
    92104      SELECT COUNT(DISTINCT r.ResourceId)
  • branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

    r15659 r15737  
    182182  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId]);
    183183ALTER TABLE [dbo].[Task]
    184   ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
     184  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId]);
    185185ALTER TABLE [dbo].[Task]
    186   ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
     186  ADD CONSTRAINT [Job_Task] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
    187187ALTER TABLE [dbo].[Downtime]
    188188  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
     
    200200  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
    201201ALTER TABLE [dbo].[Project]
    202   ADD CONSTRAINT [Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
     202  ADD CONSTRAINT [Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project]([ProjectId]);
    203203ALTER TABLE [dbo].[ProjectPermission]
    204204  ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
     
    219219CREATE TABLE [statistics].[DimClient] (
    220220    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimClient_Id] DEFAULT (newsequentialid()) NOT NULL,
    221     [Name]             VARCHAR (MAX)    NOT NULL,
    222221    [ResourceId]       UNIQUEIDENTIFIER NOT NULL,
    223     [ExpirationTime]   DATETIME        NULL,
    224     [ResourceGroupId]  UNIQUEIDENTIFIER NULL,
    225     [ResourceGroup2Id] UNIQUEIDENTIFIER NULL,
    226     [GroupName]        VARCHAR (MAX)    NULL,
    227     [GroupName2]       VARCHAR (MAX)    NULL,
     222  [ParentResourceId] UNIQUEIDENTIFIER NULL,
     223  [Name]             VARCHAR (MAX)    NOT NULL,
     224  [ResourceType]     VARCHAR (MAX)    NULL,
     225  [DateCreated]      DATETIME         NOT NULL,
     226    [DateExpired]      DATETIME         NULL
    228227    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
    229228);
     
    231230    [JobId]          UNIQUEIDENTIFIER NOT NULL,
    232231    [UserId]         UNIQUEIDENTIFIER NOT NULL,
     232  [ProjectId]      UNIQUEIDENTIFIER NOT NULL,
    233233    [JobName]        VARCHAR (MAX)    NOT NULL,
    234234    [UserName]       VARCHAR (MAX)    NOT NULL,
     
    239239    CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
    240240);
     241CREATE TABLE [statistics].[DimProject] (
     242    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimProject_Id] DEFAULT (newsequentialid()) NOT NULL,
     243    [ProjectId]        UNIQUEIDENTIFIER NOT NULL,
     244  [ParentProjectId]  UNIQUEIDENTIFIER NULL,
     245  [Name]             VARCHAR (MAX)    NOT NULL,
     246  [Description]      VARCHAR (MAX)    NULL,
     247  [OwnerUserId]      UNIQUEIDENTIFIER NOT NULL,
     248  [StartDate]        DATETIME         NOT NULL,
     249    [EndDate]          DATETIME         NULL,
     250  [DateCreated]      DATETIME         NOT NULL,
     251    [DateExpired]      DATETIME         NULL
     252  CONSTRAINT [PK_DimProject] PRIMARY KEY CLUSTERED ([Id] ASC)
     253);
    241254CREATE TABLE [statistics].[DimUser] (
    242255    [UserId] UNIQUEIDENTIFIER NOT NULL,
    243256    [Name]   VARCHAR (MAX)    NOT NULL,
    244257    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
     258);
     259
     260
     261
     262CREATE TABLE [statistics].[FactProjectInfo] (
     263  [ProjectId]   UNIQUEIDENTIFIER NOT NULL,
     264  [Time]      DATETIME     NOT NULL,
     265  [NumTotalCores] INT        NOT NULL,
     266  [NumUsedCores]  INT        NOT NULL,
     267  [TotalMemory] INT        NOT NULL,
     268  [UsedMemory]  INT        NOT NULL
     269  CONSTRAINT [PK_FactProjectInfo] PRIMARY KEY CLUSTERED ([ProjectId] ASC, [Time] ASC),
     270  CONSTRAINT [FK_FactProjectInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
     271  CONSTRAINT [FK_FactProjectInfo_DimProject] FOREIGN KEY ([ProjectId]) REFERENCES [statistics].[DimProject] ([Id])
    245272);
    246273CREATE TABLE [statistics].[FactClientInfo] (
  • branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql

    r15659 r15737  
    145145
    146146
    147 -- OBSOLETE (start)
     147-- OBSOLETE - DO NOT PERFORM (start)
    148148/****** Object:  Trigger [dbo].[tr_JobDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
    149149SET ANSI_NULLS ON
  • branches/HiveProjectManagement/HeuristicLab.Services.Hive/3.3/HiveService.cs

    r15715 r15737  
    680680      if (!RoleVerifier.IsInRole(HiveRoles.Administrator)) {
    681681        if(projectDto.ParentProjectId.HasValue) {
    682           AuthorizationManager.AuthorizeForProjectAdministration(projectDto.ParentProjectId.Value);
     682          AuthorizationManager.AuthorizeForProjectAdministration(projectDto.ParentProjectId.Value, false);
    683683        } else {
    684684          throw new SecurityException(NOT_AUTHORIZED_USERPROJECT);
     
    701701      // check if current (non-admin) user is owner of the project or the projectDto's-parents
    702702      if (!RoleVerifier.IsInRole(HiveRoles.Administrator)) {
    703         AuthorizationManager.AuthorizeForProjectAdministration(projectDto.Id);
     703        AuthorizationManager.AuthorizeForProjectAdministration(projectDto.Id, false);
    704704      }
    705705
     
    720720
    721721    public void DeleteProject(Guid projectId) {
    722       RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
    723       // check if current (non-admin) user is owner of the project or the projectDto's-parents
     722      if (projectId == Guid.Empty) return;
     723      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
     724      // check if current (non-admin) user is owner of one of the projectDto's-parents
    724725      if (!RoleVerifier.IsInRole(HiveRoles.Administrator)) {
    725         AuthorizationManager.AuthorizeForProjectAdministration(projectId);
     726        AuthorizationManager.AuthorizeForProjectAdministration(projectId, true);
    726727      }
    727728
     
    731732        var assignedJobResourceDao = pm.AssignedJobResourceDao;
    732733        pm.UseTransaction(() => {
    733           assignedJobResourceDao.DeleteByProjectId(projectId);
    734           projectDao.Delete(projectId);
     734          var projectIds = new HashSet<Guid> { projectId };
     735          projectIds.Union(projectDao.GetChildProjectIdsById(projectId));
     736
     737          assignedJobResourceDao.DeleteByProjectIds(projectIds);
     738          projectDao.DeleteByIds(projectIds);
    735739          pm.SubmitChanges();
    736740        });
     
    797801      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
    798802      if (projectId == null || grantedUserIds == null) return;
    799       AuthorizationManager.AuthorizeForProjectAdministration(projectId);
     803      AuthorizationManager.AuthorizeForProjectAdministration(projectId, false);
    800804      var pm = PersistenceManager;
    801805      using (new PerformanceLogger("SaveProjectPermissions")) {
     
    883887      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
    884888      if (projectId == null || grantedUserIds == null || !grantedUserIds.Any()) return;
    885       AuthorizationManager.AuthorizeForProjectAdministration(projectId);
     889      AuthorizationManager.AuthorizeForProjectAdministration(projectId, false);
    886890      var pm = PersistenceManager;
    887891      using (new PerformanceLogger("RevokeProjectPermissions")) {
     
    904908    public IEnumerable<DT.ProjectPermission> GetProjectPermissions(Guid projectId) {
    905909      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
    906       AuthorizationManager.AuthorizeForProjectAdministration(projectId);
     910      AuthorizationManager.AuthorizeForProjectAdministration(projectId, false);
    907911      var pm = PersistenceManager;
    908912      using (new PerformanceLogger("GetProjectPermissions")) {
     
    10401044    public IEnumerable<DT.AssignedProjectResource> GetAssignedResourcesForProjectAdministration(Guid projectId) {
    10411045      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
    1042       AuthorizationManager.AuthorizeForProjectAdministration(projectId);
     1046      AuthorizationManager.AuthorizeForProjectAdministration(projectId, false);
    10431047      var pm = PersistenceManager;
    10441048      using (new PerformanceLogger("GetAssignedResourcesForProject")) {
     
    12631267
    12641268    public void DeleteSlave(Guid slaveId) {
     1269      if (slaveId == Guid.Empty) return;
    12651270      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
    12661271      AuthorizationManager.AuthorizeForResourceAdministration(slaveId);
     
    12761281
    12771282    public void DeleteSlaveGroup(Guid slaveGroupId) {
     1283      if (slaveGroupId == Guid.Empty) return;
    12781284      RoleVerifier.AuthenticateForAnyRole(HiveRoles.Administrator, HiveRoles.Client);
    12791285      AuthorizationManager.AuthorizeForResourceAdministration(slaveGroupId);
    12801286      var pm = PersistenceManager;
    12811287      using (new PerformanceLogger("DeleteSlaveGroup")) {
    1282         var slaveGroupDao = pm.SlaveGroupDao;
    1283         pm.UseTransaction(() => {
    1284           slaveGroupDao.Delete(slaveGroupId);
     1288        var resourceDao = pm.ResourceDao;
     1289        pm.UseTransaction(() => {
     1290          var resourceIds = new HashSet<Guid> { slaveGroupId };
     1291          resourceIds.Union(resourceDao.GetChildResourceIdsById(slaveGroupId));
     1292          resourceDao.DeleteByIds(resourceIds);
    12851293          pm.SubmitChanges();
    12861294        });
  • branches/HiveProjectManagement/HeuristicLab.Services.Hive/3.3/Interfaces/IAuthorizationManager.cs

    r15540 r15737  
    3737    void AuthorizeForResourceAdministration(Guid resourceId);
    3838
    39     void AuthorizeForProjectAdministration(Guid projectId);
     39    void AuthorizeForProjectAdministration(Guid projectId, bool parentalOwnership);
    4040
    4141    void AuthorizeForProjectResourceAdministration(Guid projectId, IEnumerable<Guid> resourceIds);
  • branches/HiveProjectManagement/HeuristicLab.Services.Hive/3.3/Manager/AuthorizationManager.cs

    r15715 r15737  
    9292
    9393    // authorize if user is admin, project owner or owner of a parent project
    94     public void AuthorizeForProjectAdministration(Guid projectId) {
     94    public void AuthorizeForProjectAdministration(Guid projectId, bool parentalOwnership) {
    9595      if (projectId == null) return;
    9696      var currentUserId = UserManager.CurrentUserId;
     
    9898      var projectDao = pm.ProjectDao;
    9999      pm.UseTransaction(() => {
    100         var projectBranch = projectDao.GetCurrentAndParentProjectsById(projectId).ToList();
    101         if(!projectBranch.Select(x => x.OwnerUserId).Contains(currentUserId)
    102             && !RoleVerifier.IsInRole(HiveRoles.Administrator)) {
     100        List<Project> projectBranch = null;
     101        if(parentalOwnership) projectDao.GetParentProjectsById(projectId).ToList();
     102        else projectBranch = projectDao.GetCurrentAndParentProjectsById(projectId).ToList();
     103
     104        if(!RoleVerifier.IsInRole(HiveRoles.Administrator)
     105            && !projectBranch.Select(x => x.OwnerUserId).Contains(currentUserId)) {
    103106          throw new SecurityException(NOT_AUTHORIZED_USERPROJECT);
    104107        }
Note: See TracChangeset for help on using the changeset viewer.