public override void Up()
        {
            //Removes
            Sql("DROP View RoutingCounts");
            Sql("DROP View _RoutingCounts");

            //DataMartTypes
            DropForeignKey("DataMarts", "FK_DataMarts_DataMartTypes");
            DropPrimaryKey("DataMartTypes");
            DropColumn("DataMarts", "DataMartTypeId");

            //QueriesDataMarts
            Sql(MigrationHelpers.ClearStatsScript("QueriesDataMarts"));
            DropIndex("QueriesDataMarts", "RequestDataMart_DataMart");

            DropPrimaryKey("QueriesDataMarts");
            DropColumn("QueriesDataMarts", "DataMartId");

            //RequestDataMartSearchResults
            DropForeignKey("RequestDataMartSearchResults", "FK_dbo.RequestDataMartSearchResults_dbo.DataMarts_ResultDataMartId");
            DropForeignKey("RequestDataMartSearchResults", "FK_dbo.RequestDataMartSearchResults_dbo.Queries_SearchRequestId");
            Sql("ALTER TABLE RequestDataMartSearchResults DROP CONSTRAINT [PK_dbo.RequestDataMartSearchResults]");
            DropIndex("RequestDataMartSearchResults", "IX_ResultDataMartId");
            Sql(@"DECLARE @FKName AS nvarchar(max) = (SELECT TOP 1 Tab.CONSTRAINT_NAME from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY '
    AND Col.Table_Name = 'RequestDataMartSearchResults')

DECLARE @Sql AS nvarchar(max) = 'ALTER TABLE RequestDataMartSearchResults DROP CONSTRAINT ' + @FKName

EXECUTE sp_executeSql @Sql");

            DropColumn("RequestDataMartSearchResults", "ResultDataMartId");

            //RequestRoutingInstances
            DropForeignKey("RequestRoutingInstances", "DataMartID", "DataMarts");
            Sql(@"DECLARE @FKName AS nvarchar(max) = (SELECT TOP 1
    f.name AS ForeignKey
FROM 
    sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

WHERE OBJECT_NAME(f.parent_object_id) = 'RequestRoutingInstances' AND COL_NAME(fc.parent_object_id,
    fc.parent_column_id) = 'DataMartID')

DECLARE @Sql AS nvarchar(max) = 'ALTER TABLE RequestRoutingInstances DROP CONSTRAINT ' + @FKName

EXECUTE sp_executeSql @Sql");
            DropColumn("RequestRoutingInstances", "DataMartId");

            //Projects_DataMarts
            Sql(@"DECLARE @FKName AS nvarchar(max) = (SELECT TOP 1 Tab.CONSTRAINT_NAME from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY '
    AND Col.Table_Name = 'Projects_DataMarts')

DECLARE @Sql AS nvarchar(max) = 'ALTER TABLE Projects_DataMarts DROP CONSTRAINT ' + @FKName

EXECUTE sp_executeSql @Sql");

            Sql(@"DECLARE @FKName AS nvarchar(max) = (SELECT TOP 1
    f.name AS ForeignKey
FROM 
    sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

WHERE OBJECT_NAME(f.parent_object_id) = 'Projects_DataMarts' AND COL_NAME(fc.parent_object_id,
    fc.parent_column_id) = 'DataMartId')

DECLARE @Sql AS nvarchar(max) = 'ALTER TABLE Projects_DataMarts DROP CONSTRAINT ' + @FKName

EXECUTE sp_executeSql @Sql");

            DropColumn("Projects_DataMarts", "DataMartId");

            //DataMartAvailabilityPeriods
            DropForeignKey("DataMartAvailabilityPeriods", "FK_DataMartAvailabilityPeriods_DataMarts_DataMartId");
            AddColumn("DataMartAvailabilityPeriods", "DataMartSID", c => c.Guid(true));
            Sql(
                "UPDATE DataMartAvailabilityPeriods SET DataMartSID = (SELECT TOP 1 SID FROM DataMarts WHERE DataMartId = DataMartAvailabilityPeriods.DataMartId)");
            DropColumn("DataMartAvailabilityPeriods", "DataMartId");

            //DataMartInstalledModels
            Sql(@"DECLARE @FKName AS nvarchar(max) = (SELECT TOP 1 Tab.CONSTRAINT_NAME from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY '
    AND Col.Table_Name = 'DataMartInstalledModels')

DECLARE @Sql AS nvarchar(max) = 'ALTER TABLE DataMartInstalledModels DROP CONSTRAINT ' + @FKName

EXECUTE sp_executeSql @Sql");

            Sql(@"DECLARE @FKName AS nvarchar(max) = (SELECT TOP 1
    f.name AS ForeignKey
FROM 
    sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

WHERE OBJECT_NAME(f.parent_object_id) = 'DataMartInstalledModels' AND COL_NAME(fc.parent_object_id,
    fc.parent_column_id) = 'DataMartId')

DECLARE @Sql AS nvarchar(max) = 'ALTER TABLE DataMartInstalledModels DROP CONSTRAINT ' + @FKName

EXECUTE sp_executeSql @Sql");
            DropColumn("DataMartInstalledModels", "DataMartId");

            //DataMarts
            DropPrimaryKey("DataMarts");
            DropColumn("DataMarts", "DataMartId");

            //Adds

            //DataMartTypes
            RenameColumn("DataMartTypes", "SID", "ID");
            AddPrimaryKey("DataMartTypes", "ID");

            //DataMarts
            RenameColumn("DataMarts", "SID", "ID");
            AddPrimaryKey("DataMarts", "ID");
            RenameColumn("DataMarts", "DataMartTypeSID", "DataMartTypeID");
            AlterColumn("DataMarts", "DataMartTypeID", c => c.Guid(true));
            AddForeignKey("DataMarts", "DataMartTypeID", "DataMartTypes", "ID", false);
            CreateIndex("DataMarts", "DataMartTypeID");

            //DataMartAvailabilityPeriods
            RenameColumn("DataMartAvailabilityPeriods", "DataMartSID", "DataMartID");
            AddForeignKey("DataMartAvailabilityPeriods", "DataMartID", "DataMarts", "ID");

            //DataMartInstalledModels
            RenameColumn("DataMartInstalledModels", "DataMartSID", "DataMartID");
            AddPrimaryKey("DataMartInstalledModels", new string[] { "DataMartID", "ModelID" });
            AddForeignKey("DataMartInstalledModels", "DataMartID", "DataMarts", "ID", true);

            //QueriesDataMarts
            RenameTable("QueriesDataMarts", "RequestDataMarts");
            RenameColumn("RequestDataMarts", "DataMartSID", "DataMartID");
            AddPrimaryKey("RequestDataMarts", new string[] { "QueryId", "DataMartID" });
            AddForeignKey("RequestDataMarts", "DataMartID", "DataMarts", "ID", true);

            //RequestDataMartSearchResults
            RenameColumn("RequestDataMartSearchResults", "ResultDataMartSID", "ResultDataMartID");
            AddPrimaryKey("RequestDataMartSearchResults", new string[] { "SearchRequestId", "ResultDatamartID" });
            AddForeignKey("RequestDataMartSearchResults", "ResultDatamartID", "DataMarts", "ID", true);

            //Request Routing Instances
            RenameTable("RequestRoutingInstances", "RequestResponses");
            RenameColumn("RequestResponses", "DataMartSID", "DataMartID");
            AddForeignKey("RequestResponses", "DataMartID", "DataMarts", "ID", true);

            //Projects_DataMarts
            RenameTable("Projects_DataMarts", "ProjectDataMarts");
            RenameColumn("ProjectDataMarts", "DataMartSID", "DataMartID");
            AddPrimaryKey("ProjectDataMarts", new string[] { "ProjectID", "DataMartID" });
            AddForeignKey("ProjectDataMarts", "DataMartID", "DataMarts", "ID", true);

            //RoutingCounts
            Sql(@"create view [dbo].[_RoutingCounts]
with schemabinding
as
select QueryId,
	Sum(case when QueryStatusTypeId = 2 then 1 else 0 end) as Submitted,
	Sum(case when QueryStatusTypeId = 3 OR QueryStatusTypeId = 14 then 1 else 0 end) as Completed,
	Sum(case when QueryStatusTypeId = 4 then 1 else 0 end) as AwaitingRequestApproval,
	Sum(case when QueryStatusTypeId = 10 then 1 else 0 end) as AwaitingResponseApproval,
	Sum(case when QueryStatusTypeId = 5 then 1 else 0 end) as RejectedRequest,
	Sum(case when QueryStatusTypeId = 12 then 1 else 0 end) as RejectedBeforeUploadResults,
	Sum(case when QueryStatusTypeId = 13 then 1 else 0 end) as RejectedAfterUploadResults,
	COUNT_BIG(*) as Total
from
	dbo.RequestDataMarts
group by QueryId");

            Sql(@"create view [dbo].[RoutingCounts] as select * from _RoutingCounts
");
        }
 /// <summary>
 /// return up script
 /// </summary>
 /// <returns></returns>
 public override string UpScript()
 {
     return(MigrationHelpers.ScriptForCreateColumn(Column));
 }
 /// <summary>
 /// return down script
 /// </summary>
 /// <returns></returns>
 public override string DownScript()
 {
     return(MigrationHelpers.ScriptForDropColumn(Column));
 }
 protected override void Down(MigrationBuilder migrationBuilder)
 {
     MigrationHelpers.DropSproc(migrationBuilder);
     MigrationHelpers.DropCustomerOrderView(migrationBuilder);
 }
 protected override void Up(MigrationBuilder migrationBuilder)
 {
     //MigrationHelpers.CreateSproc(migrationBuilder);
     MigrationHelpers.CreateCustomerOrderView(migrationBuilder);
 }
        public override void Up()
        {
            Sql(@"ALTER TRIGGER [dbo].[SecurityGroups_DeleteItem] 
        ON  [dbo].[SecurityGroups]
        AFTER DELETE
    AS 
    BEGIN
		UPDATE SecurityGroups SET ParentSecurityGroupID = NULL WHERE ParentSecurityGroupID IN (SELECT ID FROM deleted)

        DELETE FROM AclDataMarts WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclGlobal WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclGroups WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclOrganizationDataMarts WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclOrganizations WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclProjectDataMartRequestTypes WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclProjectDataMarts WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclProjects WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclRegistries WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclRequests WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclRequestSharedFolders WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclRequestTypes WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclUsers WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclUserEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclDataMartEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclGroupEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclOrganizationEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclProjectEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclRegistryEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        --Add others here
	END"    );

            Sql(@"ALTER TRIGGER [dbo].[SecurityGroups_InsertItem] 
                       ON  [dbo].[SecurityGroups]
                       AFTER INSERT
                    AS 
                    BEGIN
	                    SET NOCOUNT ON;
	                    --Add the contacts from the inherited group
	                    INSERT INTO SecurityGroupUsers (SecurityGroupID, UserID) SELECT inserted.ID, SecurityGroupUsers.UserID FROM SecurityGroupUsers JOIN inserted ON SecurityGroupUsers.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

	                    --Add the ACL's from the inherited group
                        INSERT INTO AclDataMarts (DataMartID, SecurityGroupID, PermissionID, Allowed) SELECT acl.DataMartID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclDataMarts AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclGlobal (SecurityGroupID, PermissionID, Allowed) SELECT inserted.ID, acl.PermissionID, acl.Allowed FROM AclGlobal AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL
						
						INSERT INTO AclGroups(GroupID, SecurityGroupID, PermissionID, Allowed) SELECT acl.GroupID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclGroups AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclOrganizationDataMarts(OrganizationID, DataMartID, SecurityGroupID, PermissionID, Allowed) SELECT acl.OrganizationID, acl.DataMartID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclOrganizationDataMarts AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclOrganizations(OrganizationID, SecurityGroupID, PermissionID, Allowed) SELECT acl.OrganizationID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclOrganizations AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclProjectDataMartRequestTypes(ProjectID, DataMartID, RequestTypeID, SecurityGroupID, PermissionID, Allowed) SELECT acl.ProjectID, acl.DataMartID, acl.RequestTypeID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclProjectDataMartRequestTypes AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclProjectDataMarts(ProjectID, DataMartID, SecurityGroupID, PermissionID, Allowed) SELECT acl.ProjectID, acl.DataMartID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclProjectDataMarts AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclProjects(ProjectID, SecurityGroupID, PermissionID, Allowed) SELECT acl.ProjectID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclProjects AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclRegistries(RegistryID, SecurityGroupID, PermissionID, Allowed) SELECT acl.RegistryID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclRegistries AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclRequests(RequestID, SecurityGroupID, PermissionID, Allowed) SELECT acl.RequestID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclRequests AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclRequestSharedFolders(RequestSharedFolderID, SecurityGroupID, PermissionID, Allowed) SELECT acl.RequestSharedFolderID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclRequestSharedFolders AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclRequestTypes(RequestTypeID, SecurityGroupID, PermissionID, Allowed) SELECT acl.RequestTypeID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclRequestTypes AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclUsers(UserID, SecurityGroupID, PermissionID, Allowed) SELECT acl.UserID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclUsers AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclUserEvents(UserID, EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.UserID, acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclUserEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclDataMartEvents(DataMartID, EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.DataMartID, acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclDataMartEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclGroupEvents(GroupID, EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.GroupID, acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclGroupEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL
                        
                        INSERT INTO AclOrganizationEvents(OrganizationID, EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.OrganizationID, acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclOrganizationEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclProjectEvents(ProjectID, EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.ProjectID, acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM ProjectEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclRegistryEvents(RegistryID, EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.RegistryID, acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclRegistryEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclEvents(EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        --Add other acl tables here


                    END");

            Sql(MigrationHelpers.AddAclDeleteScript("AclEvents"));
            Sql(MigrationHelpers.AddAclDeleteScript("AclDataMartEvents"));
            Sql(MigrationHelpers.AddAclDeleteScript("AclGroupEvents"));
            Sql(MigrationHelpers.AddAclDeleteScript("AclOrganizationEvents"));
            Sql(MigrationHelpers.AddAclDeleteScript("AclProjectEvents"));
            Sql(MigrationHelpers.AddAclDeleteScript("AclRegistryEvents"));
            Sql(MigrationHelpers.AddAclDeleteScript("AclUserEvents"));

            Sql(MigrationHelpers.AddAclInsertScript("AclEvents"));
            Sql(MigrationHelpers.AddAclInsertScript("AclDataMartEvents"));
            Sql(MigrationHelpers.AddAclInsertScript("AclGroupEvents"));
            Sql(MigrationHelpers.AddAclInsertScript("AclOrganizationEvents"));
            Sql(MigrationHelpers.AddAclInsertScript("AclProjectEvents"));
            Sql(MigrationHelpers.AddAclInsertScript("AclRegistryEvents"));
            Sql(MigrationHelpers.AddAclInsertScript("AclUserEvents"));

            Sql(MigrationHelpers.AddAclUpdateScript("AclEvents"));
            Sql(MigrationHelpers.AddAclUpdateScript("AclDataMartEvents"));
            Sql(MigrationHelpers.AddAclUpdateScript("AclGroupEvents"));
            Sql(MigrationHelpers.AddAclUpdateScript("AclOrganizationEvents"));
            Sql(MigrationHelpers.AddAclUpdateScript("AclProjectEvents"));
            Sql(MigrationHelpers.AddAclUpdateScript("AclRegistryEvents"));
            Sql(MigrationHelpers.AddAclUpdateScript("AclUserEvents"));
        }
Example #7
0
 protected override void Up(MigrationBuilder migrationBuilder)
 {
     migrationBuilder.Sql(MigrationHelpers.GetDynamicSqlFromFile(@"SqlScripts/InitialFill.sql"));
     InitialFill.LeaguePlayerLinksFill(migrationBuilder);
 }
Example #8
0
 /// <summary>
 /// return down script
 /// </summary>
 /// <returns></returns>
 public override string DownScript()
 {
     return(MigrationHelpers.ScriptForCreateTable(Table));
 }
Example #9
0
        public override void Up()
        {
            DropTable("ACL");

            AddColumn("RequestDataMarts", "UpdatedOn", c => c.DateTime(false, defaultValueSql: "GETUTCDATE()"));
            Sql(@"DROP TRIGGER UserDeleteTargetsMembershipAndSubjects");
            Sql(@"DROP TRIGGER UserUpdateTargetsMembershipAndSubjects");
            Sql(@"DROP TRIGGER QueryDataMarts_InsertUpdateDeleteItem");
            Sql(@"  CREATE TRIGGER [dbo].[RequestDataMarts_InsertUpdateDeleteItem] 
                        ON  [dbo].[RequestDataMarts]
                        AFTER INSERT, UPDATE, DELETE
                    AS 
                    BEGIN
	                    IF ((SELECT COUNT(*) FROM inserted) > 0)
		                    UPDATE Requests SET UpdatedOn = GETDATE() WHERE Requests.ID IN (SELECT RequestID FROM inserted)
                    END");

            Sql(@"  ALTER TRIGGER [dbo].[RequestDataMartResponses_InsertUpdateDelete] 
		ON  [dbo].[RequestDataMartResponses]
		AFTER INSERT, UPDATE, DELETE
	AS 
	BEGIN
		IF ((SELECT COUNT(*) FROM inserted) > 0)
        BEGIN
            UPDATE RequestDataMarts SET UpdatedOn = GETUTCDATE() WHERE RequestDataMarts.ID IN (SELECT RequestDataMartID FROM inserted)
        END
	END"    );

            //Activities
            DropForeignKey("Activities", "FK_Activities_Activities");
            Sql(MigrationHelpers.DropPrimaryKeyScript("Activities"));
            AddColumn("Activities", "ParentActivityID", c => c.Guid(true));
            Sql(
                "UPDATE Activities SET ParentActivityID = (SELECT TOP 1 SID FROM Activities a WHERE a.id = Activities.ParentId) WHERE NOT ParentId IS NULL");

            AddColumn("Requests", "ActivitySID", c => c.Guid(true));
            Sql(
                "UPDATE Requests SET ActivitySID = (SELECT TOP 1 SID FROM Activities WHERE Activities.Id = Requests.ActivityId) WHERE NOT Requests.ActivityId IS NULL");
            DropColumn("Requests", "ActivityId");
            RenameColumn("Requests", "ActivitySID", "ActivityID");

            DropColumn("Activities", "Id");
            DropIndex("Activities", "IX_ParentID");
            DropColumn("Activities", "ParentId");
            RenameColumn("Activities", "SID", "ID");
            AddPrimaryKey("Activities", "ID");
            CreateIndex("Activities", "ParentActivityID");

            //Requests
            AddColumn("Requests", "CreatedByID", c => c.Guid(true));
            Sql(
                "UPDATE Requests SET CreatedByID = (SELECT TOP 1 SID FROM Users WHERE UserId = Requests.CreatedByUserId)");
            AlterColumn("Requests", "CreatedByID", c => c.Guid(false));
            DropColumn("Requests", "CreatedbyUserId");

            AddColumn("Requests", "UpdatedByID", c => c.Guid(true));
            Sql(
                "UPDATE Requests SET UpdatedByID = (SELECT TOP 1 SID FROM Users WHERE UserId = Requests.UpdatedByUserId)");
            DropColumn("Requests", "UpdatedByUserId");

            //RequestDataMartResponses
            AddColumn("RequestDataMartResponses", "SubmittedByID", c => c.Guid(true));
            Sql(
                "UPDATE RequestDataMartResponses SET SubmittedByID = (SELECT TOP 1 SID FROM Users WHERE UserId = RequestDataMartResponses.SubmittedByUserId)");
            AlterColumn("RequestDataMartResponses", "SubmittedByID", c => c.Guid(false));
            Sql(MigrationHelpers.DropForeignKeyScript("RequestDataMartResponses", "SubmittedByUserId"));
            DropColumn("RequestDataMartResponses", "SubmittedbyUserId");

            Sql(MigrationHelpers.DropForeignKeyScript("RequestDataMartResponses", "RespondedByUserId"));
            AddColumn("RequestDataMartResponses", "RespondedByID", c => c.Guid(true));
            Sql(
                "UPDATE RequestDataMartResponses SET RespondedByID = (SELECT TOP 1 SID FROM Users WHERE UserId = RequestDataMartResponses.RespondedByUserId)");
            DropColumn("RequestDataMartResponses", "RespondedByUserId");

            //Roles
            DropForeignKey("Users", "FK_Users_RoleTypes_RoleTypeId");
            AddColumn("RoleTypes", "ID", c => c.Guid(false, defaultValueSql: MigrationHelpers.GuidDefaultValue));
            Sql(MigrationHelpers.DropPrimaryKeyScript("RoleTypes"));
            RenameColumn("RoleTypes", "RoleType", "Name");
            AlterColumn("RoleTypes", "Name", c => c.String(false, 200));
            AddColumn("RoleTypes", "D", c => c.String(false, defaultValue: ""));
            Sql("UPDATE RoleTypes SET D = Description WHERE NOT Description IS NULL");
            DropColumn("RoleTypes", "Description");
            RenameColumn("RoleTypes", "D", "Description");
            RenameTable("RoleTypes", "Roles");

            //UserPasswordTrace
            DropPrimaryKey("UserPasswordTrace", "UserPasswordTrace_PK");
            DropColumn("UserPasswordTrace", "Id");
            AddColumn("UserPasswordTrace", "UserSID", c => c.Guid(true));
            Sql(
                "UPDATE UserPasswordTrace SET UserSID = (SELECT TOP 1 SID FROM Users WHERE UserId = UserPasswordTrace.UserId)");
            DropForeignKey("UserPasswordTrace", "Fk_UserPwdTrace_Users_UserId");
            DropColumn("UserPasswordTrace", "UserId");
            RenameColumn("UserPasswordTrace", "UserSID", "UserID");
            AlterColumn("UserPasswordTrace", "UserID", c => c.Guid(false));

            AddColumn("UserPasswordTrace", "AddedByID", c => c.Guid(true));
            Sql(
                "UPDATE UserPasswordTrace SET AddedByID = (SELECT TOP 1 SID FROM Users WHERE UserId = UserPasswordTrace.AddedBy)");
            DropForeignKey("UserPasswordTrace", "Fk_UserPwdTrace_Users_ChangedBy");
            DropColumn("UserPasswordTrace", "AddedBy");
            AlterColumn("UserPasswordTrace", "AddedByID", c => c.Guid(false));

            AlterColumn("UserPasswordTrace", "Password", c => c.String(false, 100));
            RenameColumn("UserPasswordTrace", "DateAdded", "AddedOn");
            AddPrimaryKey("UserPasswordTrace", new string[] { "UserID", "AddedOn", "Password" });

            //Subscriptions
            AddColumn("Subscriptions", "SID", c => c.Guid(false, defaultValueSql: MigrationHelpers.GuidDefaultValue));
            Sql(MigrationHelpers.DropPrimaryKeyScript("Subscriptions"));
            DropColumn("Subscriptions", "Id");
            RenameColumn("Subscriptions", "SID", "ID");
            AddPrimaryKey("Subscriptions", "ID");
            AddColumn("Subscriptions", "UserID", c => c.Guid(true));
            Sql(
                "UPDATE Subscriptions SET UserID = (SELECT TOP 1 SID FROM Users WHERE Users.UserId = Subscriptions.OwnerId)");
            Sql(MigrationHelpers.DropForeignKeyScript("Subscriptions", "OwnerId"));
            DropColumn("Subscriptions", "OwnerId");
            AlterColumn("Subscriptions", "UserID", c => c.Guid(false));

            //SecurityGroupUsers
            Sql(MigrationHelpers.DropPrimaryKeyScript("SecurityGroupUsers"));
            DropForeignKey("SecurityGroupUsers", "FK_SecurityGroupUsers_Users_UserID");
            DropColumn("SecurityGroupUsers", "UserID");
            AddColumn("SecurityGroupUsers", "UserID", c => c.Guid(false));
            AddPrimaryKey("SecurityGroupUsers", new string[] { "SecurityGroupID", "UserID" });


            //Users
            DropPrimaryKey("Users", "PK_Users");
            DropColumn("Users", "UserId");
            RenameColumn("Users", "SID", "ID");
            AddColumn("Users", "RoleID", c => c.Guid(true));
            Sql("UPDATE Users SET RoleID = (SELECT TOP 1 ID FROM Roles WHERE RoleTypeId = Users.RoleTypeId)");
            DropColumn("Users", "RoleTypeId");


            //Roles
            DropColumn("Roles", "RoleTypeId");
            AddPrimaryKey("Roles", "ID");
            Sql(
                @"ALTER TABLE Users ADD CONSTRAINT FK_Users_Roles_RoleID FOREIGN KEY (RoleID) REFERENCES Roles(ID) ON DELETE SET NULL ON UPDATE CASCADE");

            //Requests
            Sql(
                @"ALTER TABLE Requests ADD CONSTRAINT FK_Requests_Users_SubmittedByID FOREIGN KEY (SubmittedByID) REFERENCES Users(ID) ON DELETE SET NULL ON UPDATE CASCADE");
            Sql(
                @"ALTER TABLE Requests ADD CONSTRAINT FK_Requests_Activities_ActivityID FOREIGN KEY (ActivityID) REFERENCES Activities(ID) ON DELETE SET NULL ON UPDATE CASCADE");

            AddForeignKey("Requests", "CreatedByID", "Users", "ID", false);
            AddForeignKey("Requests", "UpdatedByID", "Users", "ID", false);

            //RequestDataMartResponses
            AddForeignKey("RequestDataMartResponses", "RespondedByID", "Users", "ID", false);
            AddForeignKey("RequestDataMartResponses", "SubmittedByID", "Users", "ID", false);

            //SecurityGroupUsers
            AddForeignKey("SecurityGroupUsers", "UserID", "Users", "ID", true);
        }
Example #10
0
        public override void Up()
        {
            DropForeignKey("Requests", "FK_Queries_Activities");
            DropForeignKey("Requests", "FK_Queries_QueryTypes");
            DropForeignKey("Requests", "FK_Queries_Users");
            Sql(MigrationHelpers.DropForeignKeyScript("Requests", "UpdatedByUserId"));
            Sql(MigrationHelpers.DropForeignKeyScript("Requests", "ProjectID"));
            Sql("DROP TRIGGER QueryDeleteTargets");
            Sql("DROP TRIGGER QueryUpdateTargets");
            Sql("DROP TRIGGER OrganizationDeleteTargets");
            Sql("DROP TRIGGER OrganizationUpdateTargets");

            AlterColumn("ResponseGroups", "Name", c => c.String(false, 255));
            CreateIndex("ResponseGroups", "Name");

            RenameColumn("DataMarts", "DataMartName", "Name");
            AlterColumn("DataMarts", "Name", c => c.String(false, 255));
            CreateIndex("DataMarts", "Name");
            AddColumn("DataMarts", "A", c => c.String(false, 100, defaultValue: ""));
            Sql("UPDATE DataMarts SET A = Acronym WHERE NOT Acronym IS NULL AND Acronym <> ''");
            DropColumn("DataMarts", "Acronym");
            RenameColumn("DataMarts", "A", "Acronym");
            CreateIndex("DataMarts", "Acronym");

            AlterColumn("Requests", "Name", c => c.String(false, 255));
            CreateIndex("Requests", "Name");
            DropForeignKey("Requests", "FK_Queries_QueryTypes");
            DropColumn("Requests", "QueryTypeId");
            DropColumn("Requests", "QueryText");
            AddColumn("Requests", "Description", c => c.String(false, null, defaultValue: ""));
            Sql(
                "UPDATE Requests SET Description = QueryDescription WHERE NOT QueryDescription IS NULL");
            DropColumn("Requests", "QueryDescription");
            //CreateIndex("Requests", "Description");
            DropIndex("Requests", "Queries_Submitted");
            RenameColumn("Requests", "Submitted", "SubmittedOn");
            CreateIndex("Requests", "SubmittedOn");
            Sql("UPDATE Requests SET isAdminQuery = 0 WHERE isAdminQuery IS NULL");
            AlterColumn("Requests", "IsAdminQuery", c => c.Boolean(false, false));
            AddColumn("Requests", "SubmittedByID", c => c.Guid(true));
            Sql("UPDATE Requests SET SubmittedByID = (SELECT TOP 1 SID FROM Users WHERE Users.UserId = Requests.CreatedByUserId) WHERE NOT SubmittedOn IS NULL");
            RenameColumn("Requests", "CreatedAt", "CreatedOn");
            CreateIndex("Requests", "CreatedOn");
            DropIndex("Requests", "Queries_Updated");
            RenameColumn("Requests", "Updated", "UpdatedOn");
            CreateIndex("Requests", "UpdatedOn");
            AlterColumn("Requests", "ActivityOfQuery", c => c.String(true, 255));
            AlterColumn("Requests", "ActivityDescription", c => c.String(true, 255));
            AlterColumn("Requests", "ActivityPriority", c => c.String(true, 50));

            AlterColumn("Groups", "GroupName", c => c.String(false, 255));
            RenameColumn("Groups", "GroupName", "Name");
            CreateIndex("Groups", "Name");

            RenameColumn("Organizations", "OrganizationName", "Name");
            AlterColumn("Organizations", "Name", c => c.String(false, 255));
            CreateIndex("Organizations", "Name");
            RenameColumn("Organizations", "OrganizationAcronym", "Acronym");
            AddColumn("Organizations", "A", c => c.String(false, 100, defaultValue: ""));
            Sql("UPDATE Organizations SET A = Acronym WHERE NOT Acronym IS NULL");
            DropColumn("Organizations", "Acronym");
            RenameColumn("Organizations", "A", "Acronym");

            DropIndex("RequestDataMartResponses", "IX_Count");
            RenameColumn("RequestDataMartResponses", "SubmitTime", "SubmittedOn");
            CreateIndex("RequestDataMartResponses", "SubmittedOn");
            //modify trigger to for column rename
            Sql(@"ALTER TRIGGER [dbo].[RequestDataMartResponsesInsert] 
	ON  [dbo].[RequestDataMartResponses]
	AFTER INSERT
AS 
BEGIN
	UPDATE RequestDataMartResponses SET Count = (SELECT COUNT(*) FROM RequestDataMartResponses r WHERE r.RequestDataMartID = RequestDataMartResponses.RequestDataMartID AND r.SubmittedOn < RequestDataMartResponses.SubmittedOn) + 1 WHERE RequestDataMartResponses.ID IN (SELECT ID FROM inserted)
END");
            AlterColumn("RequestDataMarts", "ErrorMessage", c => c.String(true));
            AlterColumn("RequestDataMarts", "ErrorDetail", c => c.String(true));
            AlterColumn("RequestDataMarts", "RejectReason", c => c.String(true));



            AlterColumn("DataAvailabilityPeriod", "Period", c => c.String(false, 100));
            AlterColumn("DataAvailabilityPeriodCategory", "CategoryType", c => c.String(false, 255));
            AlterColumn("DataAvailabilityPeriodCategory", "CategoryDescription", c => c.String(false));

            DropIndex("Documents", "IX_Name");
            AlterColumn("Documents", "Name", c => c.String(false, 255));
            CreateIndex("Documents", "Name");
        }
Example #11
0
 /// <summary>
 /// return up script
 /// </summary>
 /// <returns></returns>
 public override string UpScript()
 {
     return(MigrationHelpers.ScriptForDropTable(Table));
 }
        public override void Up()
        {
            //AddColumn("dbo.AclDataMarts", "Overridden", c => c.Boolean(nullable: false));
            //AddColumn("dbo.AclRequestSharedFolders", "Overridden", c => c.Boolean(nullable: false));
            //AddColumn("dbo.AclGroups", "Overridden", c => c.Boolean(nullable: false));
            //AddColumn("dbo.AclOrganizations", "Overridden", c => c.Boolean(nullable: false));
            //AddColumn("dbo.AclOrganizationDataMarts", "Overridden", c => c.Boolean(nullable: false));
            //AddColumn("dbo.AclRegistries", "Overridden", c => c.Boolean(nullable: false));
            //AddColumn("dbo.AclRequests", "Overridden", c => c.Boolean(nullable: false));
            //AddColumn("dbo.AclUsers", "Overridden", c => c.Boolean(nullable: false));
            //AddColumn("dbo.AclProjectDataMarts", "Overridden", c => c.Boolean(nullable: false));
            //AddColumn("dbo.AclProjects", "Overridden", c => c.Boolean(nullable: false));
            //AddColumn("dbo.AclRequestTypes", "Overridden", c => c.Boolean(nullable: false));
            //AddColumn("dbo.AclGlobal", "Overridden", c => c.Boolean(nullable: false));
            //AddColumn("dbo.AclProjectDataMartRequestTypes", "Overridden", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclDataMarts", "Allowed", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclRequestSharedFolders", "Allowed", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclGroups", "Allowed", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclOrganizations", "Allowed", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclOrganizationDataMarts", "Allowed", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclRegistries", "Allowed", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclRequests", "Allowed", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclUsers", "Allowed", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclProjectDataMarts", "Allowed", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclProjects", "Allowed", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclRequestTypes", "Allowed", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclGlobal", "Allowed", c => c.Boolean(nullable: false));
            //AlterColumn("dbo.AclProjectDataMartRequestTypes", "Allowed", c => c.Boolean(nullable: false));

            Sql(MigrationHelpers.AddAclDeleteScript("AclDataMarts", true));
            Sql(MigrationHelpers.AddAclDeleteScript("AclGlobal", true));
            Sql(MigrationHelpers.AddAclDeleteScript("AclGroups", true));
            Sql(MigrationHelpers.AddAclDeleteScript("AclOrganizationDataMarts", true));
            Sql(MigrationHelpers.AddAclDeleteScript("AclOrganizations", true));
            Sql(MigrationHelpers.AddAclDeleteScript("AclProjectDataMartRequestTypes", true));
            Sql(MigrationHelpers.AddAclDeleteScript("AclProjectDataMarts", true));
            Sql(MigrationHelpers.AddAclDeleteScript("AclProjects", true));
            Sql(MigrationHelpers.AddAclDeleteScript("AclRegistries", true));
            Sql(MigrationHelpers.AddAclDeleteScript("AclRequests", true));
            Sql(MigrationHelpers.AddAclDeleteScript("AclRequestSharedFolders", true));
            Sql(MigrationHelpers.AddAclDeleteScript("AclRequestTypes", true));
            Sql(MigrationHelpers.AddAclDeleteScript("AclUsers", true));

            Sql(MigrationHelpers.AddAclInsertScript("AclDataMarts", true));
            Sql(MigrationHelpers.AddAclInsertScript("AclGlobal", true));
            Sql(MigrationHelpers.AddAclInsertScript("AclGroups", true));
            Sql(MigrationHelpers.AddAclInsertScript("AclOrganizationDataMarts", true));
            Sql(MigrationHelpers.AddAclInsertScript("AclOrganizations", true));
            Sql(MigrationHelpers.AddAclInsertScript("AclProjectDataMartRequestTypes", true));
            Sql(MigrationHelpers.AddAclInsertScript("AclProjectDataMarts", true));
            Sql(MigrationHelpers.AddAclInsertScript("AclProjects", true));
            Sql(MigrationHelpers.AddAclInsertScript("AclRegistries", true));
            Sql(MigrationHelpers.AddAclInsertScript("AclRequests", true));
            Sql(MigrationHelpers.AddAclInsertScript("AclRequestSharedFolders", true));
            Sql(MigrationHelpers.AddAclInsertScript("AclRequestTypes", true));
            Sql(MigrationHelpers.AddAclInsertScript("AclUsers", true));

            Sql(MigrationHelpers.AddAclUpdateScript("AclDataMarts", true));
            Sql(MigrationHelpers.AddAclUpdateScript("AclGlobal", true));
            Sql(MigrationHelpers.AddAclUpdateScript("AclGroups", true));
            Sql(MigrationHelpers.AddAclUpdateScript("AclOrganizationDataMarts", true));
            Sql(MigrationHelpers.AddAclUpdateScript("AclOrganizations", true));
            Sql(MigrationHelpers.AddAclUpdateScript("AclProjectDataMartRequestTypes", true));
            Sql(MigrationHelpers.AddAclUpdateScript("AclProjectDataMarts", true));
            Sql(MigrationHelpers.AddAclUpdateScript("AclProjects", true));
            Sql(MigrationHelpers.AddAclUpdateScript("AclRegistries", true));
            Sql(MigrationHelpers.AddAclUpdateScript("AclRequests", true));
            Sql(MigrationHelpers.AddAclUpdateScript("AclRequestSharedFolders", true));
            Sql(MigrationHelpers.AddAclUpdateScript("AclRequestTypes", true));
            Sql(MigrationHelpers.AddAclUpdateScript("AclUsers", true));
        }
 public IEnumerable <JsonPatchOperation> GetRemoveAllRelationsOperations(IBatchMigrationContext batchContext, WorkItem targetWorkItem)
 {
     return(targetWorkItem.Relations?.Select((r, index) => MigrationHelpers.GetRelationRemoveOperation(index)));
 }
Example #14
0
 /// <summary>
 /// return down script
 /// </summary>
 /// <returns></returns>
 public override string DownScript()
 {
     return(MigrationHelpers.ScriptForAlterColumn(NewColumn, OldColumn));
 }
 protected override void Down(MigrationBuilder migrationBuilder)
 {
     MigrationHelpers.DropView(migrationBuilder);
 }
        public override void Up()
        {
            CreateTable("Permissions", c => new
            {
                ID          = c.Guid(false, true, defaultValueSql: MigrationHelpers.GuidDefaultValue),
                TimeStamp   = c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"),
                Name        = c.String(false, 250),
                Description = c.String()
            }).PrimaryKey(t => t.ID)
            .Index(t => t.Name);

            AddColumn("Activities", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));

            AddColumn("DataMarts", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));

            AddColumn("DataMartTypes", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));

            AddColumn("Documents", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));

            AddColumn("Groups", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));

            AddColumn("Networks", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("Organizations", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("OrganizationElectronicHealthRecordSystems", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("Projects", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("Registries", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("Requests", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("RequestDataMarts", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("RequestDataMartResponseSearchResults", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("RequestDataMartResponses", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("ResponseGroups", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("WorkplanTypes", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("RequesterCenters", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("Roles", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
            AddColumn("Users", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));

            AddColumn("SsoEndpoints", "TimeStamp",
                      c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));

            Sql(MigrationHelpers.DropPrimaryKeyScript("SsoEndpoints"));
            DropColumn("SsoEndpoints", "ID");
            AddColumn("SsoEndpoints", "ID", c => c.Guid(false, defaultValueSql: MigrationHelpers.GuidDefaultValue));
            AddPrimaryKey("SsoEndpoints", "ID");

            DropColumn("DataMartTypes", "DataMartTypeID");

            Sql(@"  CREATE TRIGGER [dbo].[WorkplanTypesDelete] 
		ON  [dbo].[WorkplanTypes]
		AFTER DELETE
	AS 
	BEGIN
		UPDATE Requests SET WorkPlanTypeID = NULL WHERE WorkPlanTypeID IN (SELECT ID FROM deleted)
	END"    );
            Sql(@"  CREATE TRIGGER [dbo].[RequesterCentersDelete] 
		ON  [dbo].[RequesterCenters]
		AFTER DELETE
	AS 
	BEGIN
		UPDATE Requests SET RequesterCenterID = NULL WHERE RequesterCenterID IN (SELECT ID FROM deleted)
	END"    );
        }
 protected override void Up(MigrationBuilder migrationBuilder)
 {
     MigrationHelpers.CreateView(migrationBuilder);
 }
Example #18
0
        public override void Up()
        {
            CreateTable(
                "dbo.AclProjectOrganizations",
                c => new
            {
                SecurityGroupID = c.Guid(nullable: false),
                PermissionID    = c.Guid(nullable: false),
                ProjectID       = c.Guid(nullable: false),
                OrganizationID  = c.Guid(nullable: false),
                Allowed         = c.Boolean(nullable: false),
                Overridden      = c.Boolean(nullable: false),
            })
            .PrimaryKey(t => new { t.SecurityGroupID, t.PermissionID, t.ProjectID, t.OrganizationID })
            .ForeignKey("dbo.Permissions", t => t.PermissionID, cascadeDelete: true)
            .ForeignKey("dbo.SecurityGroups", t => t.SecurityGroupID, cascadeDelete: true)
            .ForeignKey("dbo.Organizations", t => t.OrganizationID, cascadeDelete: true)
            .ForeignKey("dbo.Projects", t => t.ProjectID, cascadeDelete: true)
            .Index(t => t.SecurityGroupID)
            .Index(t => t.PermissionID)
            .Index(t => t.ProjectID)
            .Index(t => t.OrganizationID);

            CreateTable(
                "dbo.AclProjectOrganizationUsers",
                c => new
            {
                SecurityGroupID = c.Guid(nullable: false),
                PermissionID    = c.Guid(nullable: false),
                ProjectID       = c.Guid(nullable: false),
                OrganizationID  = c.Guid(nullable: false),
                UserID          = c.Guid(nullable: false),
                Allowed         = c.Boolean(nullable: false),
                Overridden      = c.Boolean(nullable: false),
            })
            .PrimaryKey(t => new { t.SecurityGroupID, t.PermissionID, t.ProjectID, t.OrganizationID, t.UserID })
            .ForeignKey("dbo.Permissions", t => t.PermissionID, cascadeDelete: true)
            .ForeignKey("dbo.SecurityGroups", t => t.SecurityGroupID, cascadeDelete: true)
            .ForeignKey("dbo.Organizations", t => t.OrganizationID, cascadeDelete: true)
            .ForeignKey("dbo.Users", t => t.UserID, cascadeDelete: true)
            .ForeignKey("dbo.Projects", t => t.ProjectID, cascadeDelete: true)
            .Index(t => t.SecurityGroupID)
            .Index(t => t.PermissionID)
            .Index(t => t.ProjectID)
            .Index(t => t.OrganizationID)
            .Index(t => t.UserID);

            CreateTable(
                "dbo.AclProjectUsers",
                c => new
            {
                SecurityGroupID = c.Guid(nullable: false),
                PermissionID    = c.Guid(nullable: false),
                ProjectID       = c.Guid(nullable: false),
                UserID          = c.Guid(nullable: false),
                Allowed         = c.Boolean(nullable: false),
                Overridden      = c.Boolean(nullable: false),
            })
            .PrimaryKey(t => new { t.SecurityGroupID, t.PermissionID, t.ProjectID, t.UserID })
            .ForeignKey("dbo.Permissions", t => t.PermissionID, cascadeDelete: true)
            .ForeignKey("dbo.SecurityGroups", t => t.SecurityGroupID, cascadeDelete: true)
            .ForeignKey("dbo.Users", t => t.UserID, cascadeDelete: true)
            .ForeignKey("dbo.Projects", t => t.ProjectID, cascadeDelete: true)
            .Index(t => t.SecurityGroupID)
            .Index(t => t.PermissionID)
            .Index(t => t.ProjectID)
            .Index(t => t.UserID);

            CreateTable(
                "dbo.AclOrganizationUsers",
                c => new
            {
                SecurityGroupID = c.Guid(nullable: false),
                PermissionID    = c.Guid(nullable: false),
                OrganizationID  = c.Guid(nullable: false),
                UserID          = c.Guid(nullable: false),
                Allowed         = c.Boolean(nullable: false),
                Overridden      = c.Boolean(nullable: false),
            })
            .PrimaryKey(t => new { t.SecurityGroupID, t.PermissionID, t.OrganizationID, t.UserID })
            .ForeignKey("dbo.Organizations", t => t.OrganizationID, cascadeDelete: true)
            .ForeignKey("dbo.Permissions", t => t.PermissionID, cascadeDelete: true)
            .ForeignKey("dbo.SecurityGroups", t => t.SecurityGroupID, cascadeDelete: true)
            .ForeignKey("dbo.Users", t => t.UserID, cascadeDelete: true)
            .Index(t => t.SecurityGroupID)
            .Index(t => t.PermissionID)
            .Index(t => t.OrganizationID)
            .Index(t => t.UserID);

            //Add the permissions
            Sql(
                "INSERT INTO Permissions (ID, Name, Description) VALUES ('FDEE0BA5-AC09-4580-BAA4-496362985BF7', 'Change Routings After Submission', 'Controls who can change the routing of the request after submission')");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('FDEE0BA5-AC09-4580-BAA4-496362985BF7', 22)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('FDEE0BA5-AC09-4580-BAA4-496362985BF7', 21)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('FDEE0BA5-AC09-4580-BAA4-496362985BF7', 20)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('FDEE0BA5-AC09-4580-BAA4-496362985BF7', 19)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('FDEE0BA5-AC09-4580-BAA4-496362985BF7', 9)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('FDEE0BA5-AC09-4580-BAA4-496362985BF7', 4)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('FDEE0BA5-AC09-4580-BAA4-496362985BF7', 3)");



            Sql(
                "INSERT INTO Permissions (ID, Name, Description) VALUES ('D4494B80-966A-473D-A1B3-4B18BBEF1F34', 'View Submitted Request Status', 'Controls who can see the status of a submitted request')");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('D4494B80-966A-473D-A1B3-4B18BBEF1F34', 22)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('D4494B80-966A-473D-A1B3-4B18BBEF1F34', 21)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('D4494B80-966A-473D-A1B3-4B18BBEF1F34', 20)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('D4494B80-966A-473D-A1B3-4B18BBEF1F34', 19)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('D4494B80-966A-473D-A1B3-4B18BBEF1F34', 9)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('D4494B80-966A-473D-A1B3-4B18BBEF1F34', 4)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('D4494B80-966A-473D-A1B3-4B18BBEF1F34', 3)");


            Sql(
                "INSERT INTO Permissions (ID, Name, Description) VALUES ('39683790-A857-4247-85DF-A9B425AC79CC', 'Skip Request Approval', 'Controls who can submit a request to datamarts directly without prior approval')");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('39683790-A857-4247-85DF-A9B425AC79CC', 22)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('39683790-A857-4247-85DF-A9B425AC79CC', 21)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('39683790-A857-4247-85DF-A9B425AC79CC', 20)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('39683790-A857-4247-85DF-A9B425AC79CC', 19)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('39683790-A857-4247-85DF-A9B425AC79CC', 9)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('39683790-A857-4247-85DF-A9B425AC79CC', 4)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('39683790-A857-4247-85DF-A9B425AC79CC', 3)");


            Sql(
                "INSERT INTO Permissions (ID, Name, Description) VALUES ('40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 'Approve/Reject Submission', 'Controls who can approve or reject requests that have been submitted by a user that cannot skip request approval')");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 22)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 21)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 20)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 19)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 9)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 4)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 3)");


            Sql(
                "INSERT INTO Permissions (ID, Name, Description) VALUES ('BDC57049-27BA-41DF-B9F9-A15ABF19B120', 'View Request Results', 'Controls who can view the results list on a request')");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('BDC57049-27BA-41DF-B9F9-A15ABF19B120', 22)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('BDC57049-27BA-41DF-B9F9-A15ABF19B120', 21)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('BDC57049-27BA-41DF-B9F9-A15ABF19B120', 20)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('BDC57049-27BA-41DF-B9F9-A15ABF19B120', 19)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('BDC57049-27BA-41DF-B9F9-A15ABF19B120', 9)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('BDC57049-27BA-41DF-B9F9-A15ABF19B120', 4)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('BDC57049-27BA-41DF-B9F9-A15ABF19B120', 3)");


            Sql(
                "INSERT INTO Permissions (ID, Name, Description) VALUES ('C025131A-B5EC-46D5-B657-ADE567717A0D', 'View Result details', 'Controls who can view the result details of a request')");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('C025131A-B5EC-46D5-B657-ADE567717A0D', 22)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('C025131A-B5EC-46D5-B657-ADE567717A0D', 21)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('C025131A-B5EC-46D5-B657-ADE567717A0D', 20)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('C025131A-B5EC-46D5-B657-ADE567717A0D', 19)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('C025131A-B5EC-46D5-B657-ADE567717A0D', 9)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('C025131A-B5EC-46D5-B657-ADE567717A0D', 4)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('C025131A-B5EC-46D5-B657-ADE567717A0D', 3)");


            Sql(
                "INSERT INTO Permissions (ID, Name, Description) VALUES ('0475D452-4B7A-4D3A-8295-4FC122F6A546', 'View History', 'Controls who can view the history of the request and it''s results')");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0475D452-4B7A-4D3A-8295-4FC122F6A546', 22)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0475D452-4B7A-4D3A-8295-4FC122F6A546', 21)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0475D452-4B7A-4D3A-8295-4FC122F6A546', 20)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0475D452-4B7A-4D3A-8295-4FC122F6A546', 19)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0475D452-4B7A-4D3A-8295-4FC122F6A546', 9)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0475D452-4B7A-4D3A-8295-4FC122F6A546', 4)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0475D452-4B7A-4D3A-8295-4FC122F6A546', 3)");


            Sql(
                "INSERT INTO Permissions (ID, Name, Description) VALUES ('0549F5C8-6C0E-4491-BE90-EE0F29652422', 'View Request', 'Controls who can view requests')");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0549F5C8-6C0E-4491-BE90-EE0F29652422', 22)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0549F5C8-6C0E-4491-BE90-EE0F29652422', 21)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0549F5C8-6C0E-4491-BE90-EE0F29652422', 20)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0549F5C8-6C0E-4491-BE90-EE0F29652422', 19)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0549F5C8-6C0E-4491-BE90-EE0F29652422', 9)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0549F5C8-6C0E-4491-BE90-EE0F29652422', 4)");
            Sql("INSERT INTO PermissionLocations (PermissionID, Type) VALUES ('0549F5C8-6C0E-4491-BE90-EE0F29652422', 3)");

            //Users Only
            Sql(@"INSERT INTO AclUsers (UserID, SecurityGroupID, PermissionID, Allowed, Overridden)
SELECT DISTINCT ID3, SubjectID, PrivilegeID, CASE WHEN DeniedEntries = 0 AND ExplicitDeniedEntries = 0 THEN 1 ELSE 0 END AS Allowed, CASE WHEN ExplicitAllowedEntries > 0 OR ExplicitDeniedEntries > 0 THEN 1 ELSE 0 END Overridden FROM Security_Tuple3 WHERE PrivilegeID IN ('C025131A-B5EC-46D5-B657-ADE567717A0D', '39683790-A857-4247-85DF-A9B425AC79CC', 'D4494B80-966A-473D-A1B3-4B18BBEF1F34', '0475D452-4B7A-4D3A-8295-4FC122F6A546', 'FDEE0BA5-AC09-4580-BAA4-496362985BF7', '40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 'BDC57049-27BA-41DF-B9F9-A15ABF19B120', '0549F5C8-6C0E-4491-BE90-EE0F29652422')
AND ID1 = '6A690001-7579-4C74-ADE1-A2210107FA29' AND ID2 = 'F3AB0001-DEF9-43D1-B862-A22100FE1882' AND EXISTS(SELECT NULL FROM Users WHERE ID = ID3)");


            //Projects Only
            Sql(@"INSERT INTO AclProjects (ProjectID, SecurityGroupID, PermissionID, Allowed, Overridden)
SELECT DISTINCT ID1, SubjectID, PrivilegeID, CASE WHEN DeniedEntries = 0 AND ExplicitDeniedEntries = 0 THEN 1 ELSE 0 END AS Allowed, CASE WHEN ExplicitAllowedEntries > 0 OR ExplicitDeniedEntries > 0 THEN 1 ELSE 0 END Overridden FROM Security_Tuple3 WHERE PrivilegeID IN ('C025131A-B5EC-46D5-B657-ADE567717A0D', '39683790-A857-4247-85DF-A9B425AC79CC', 'D4494B80-966A-473D-A1B3-4B18BBEF1F34', '0475D452-4B7A-4D3A-8295-4FC122F6A546', 'FDEE0BA5-AC09-4580-BAA4-496362985BF7', '40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 'BDC57049-27BA-41DF-B9F9-A15ABF19B120', '0549F5C8-6C0E-4491-BE90-EE0F29652422')
AND ID3 = '1D3A0001-4717-40A3-98A1-A22100FDE0ED' AND ID2 = 'F3AB0001-DEF9-43D1-B862-A22100FE1882' AND EXISTS(SELECT NULL FROM Projects WHERE ID = ID1) ");

            //Organizations Only
            Sql(@"INSERT INTO AclOrganizations (OrganizationID, SecurityGroupID, PermissionID, Allowed, Overridden)
SELECT DISTINCT ID2, SubjectID, PrivilegeID, CASE WHEN DeniedEntries = 0 AND ExplicitDeniedEntries = 0 THEN 1 ELSE 0 END AS Allowed, CASE WHEN ExplicitAllowedEntries > 0 OR ExplicitDeniedEntries > 0 THEN 1 ELSE 0 END Overridden FROM Security_Tuple3 WHERE PrivilegeID IN ('C025131A-B5EC-46D5-B657-ADE567717A0D', '39683790-A857-4247-85DF-A9B425AC79CC', 'D4494B80-966A-473D-A1B3-4B18BBEF1F34', '0475D452-4B7A-4D3A-8295-4FC122F6A546', 'FDEE0BA5-AC09-4580-BAA4-496362985BF7', '40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 'BDC57049-27BA-41DF-B9F9-A15ABF19B120', '0549F5C8-6C0E-4491-BE90-EE0F29652422')
AND ID3 = '1D3A0001-4717-40A3-98A1-A22100FDE0ED' AND ID1 = '6A690001-7579-4C74-ADE1-A2210107FA29' AND EXISTS(SELECT NULL FROM Organizations WHERE ID = ID2)");

            //Project Users
            Sql(@"INSERT INTO AclProjectUsers (ProjectID, UserID, SecurityGroupID, PermissionID, Allowed, Overridden)
SELECT DISTINCT ID1, ID3, SubjectID, PrivilegeID, MIN(CASE WHEN DeniedEntries = 0 AND ExplicitDeniedEntries = 0 THEN 1 ELSE 0 END) AS Allowed, MAX(CASE WHEN ExplicitAllowedEntries > 0 OR ExplicitDeniedEntries > 0 THEN 1 ELSE 0 END) Overridden FROM Security_Tuple3 WHERE PrivilegeID IN ('C025131A-B5EC-46D5-B657-ADE567717A0D', '39683790-A857-4247-85DF-A9B425AC79CC', 'D4494B80-966A-473D-A1B3-4B18BBEF1F34', '0475D452-4B7A-4D3A-8295-4FC122F6A546', 'FDEE0BA5-AC09-4580-BAA4-496362985BF7', '40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 'BDC57049-27BA-41DF-B9F9-A15ABF19B120', '0549F5C8-6C0E-4491-BE90-EE0F29652422')
AND ID2 = 'F3AB0001-DEF9-43D1-B862-A22100FE1882' AND EXISTS(SELECT NULL FROM Projects WHERE ID = ID1) AND EXISTS(SELECT NULL FROM Users WHERE ID = ID3) group by ID1, ID3, SubjectID, PrivilegeID");


            //Project Organizations
            Sql(@"INSERT INTO AclProjectOrganizations (ProjectID, OrganizationID, SecurityGroupID, PermissionID, Allowed, Overridden)
SELECT DISTINCT ID1, ID2, SubjectID, PrivilegeID, CASE WHEN DeniedEntries = 0 AND ExplicitDeniedEntries = 0 THEN 1 ELSE 0 END AS Allowed, CASE WHEN ExplicitAllowedEntries > 0 OR ExplicitDeniedEntries > 0 THEN 1 ELSE 0 END Overridden FROM Security_Tuple3 WHERE PrivilegeID IN ('C025131A-B5EC-46D5-B657-ADE567717A0D', '39683790-A857-4247-85DF-A9B425AC79CC', 'D4494B80-966A-473D-A1B3-4B18BBEF1F34', '0475D452-4B7A-4D3A-8295-4FC122F6A546', 'FDEE0BA5-AC09-4580-BAA4-496362985BF7', '40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 'BDC57049-27BA-41DF-B9F9-A15ABF19B120', '0549F5C8-6C0E-4491-BE90-EE0F29652422')
AND ID3 = '1D3A0001-4717-40A3-98A1-A22100FDE0ED' AND EXISTS(SELECT NULL FROM Projects WHERE ID = ID1) AND EXISTS(SELECT NULL FROM Organizations WHERE ID = ID2)");

            //Organization Users
            Sql(@"INSERT INTO AclOrganizationUsers (OrganizationID, UserID, SecurityGroupID, PermissionID, Allowed, Overridden)
SELECT DISTINCT ID2, ID3, SubjectID, PrivilegeID, MIN(CASE WHEN DeniedEntries = 0 AND ExplicitDeniedEntries = 0 THEN 1 ELSE 0 END) AS Allowed, MAX(CASE WHEN ExplicitAllowedEntries > 0 OR ExplicitDeniedEntries > 0 THEN 1 ELSE 0 END) Overridden FROM Security_Tuple3 WHERE PrivilegeID IN ('C025131A-B5EC-46D5-B657-ADE567717A0D', '39683790-A857-4247-85DF-A9B425AC79CC', 'D4494B80-966A-473D-A1B3-4B18BBEF1F34', '0475D452-4B7A-4D3A-8295-4FC122F6A546', 'FDEE0BA5-AC09-4580-BAA4-496362985BF7', '40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 'BDC57049-27BA-41DF-B9F9-A15ABF19B120', '0549F5C8-6C0E-4491-BE90-EE0F29652422')
AND ID1 = '6A690001-7579-4C74-ADE1-A2210107FA29' AND EXISTS(SELECT NULL FROM Organizations WHERE ID = ID2) AND EXISTS(SELECT NULL FROM Users WHERE ID = ID3) group by ID2, ID3, SubjectID, PrivilegeID");

            // PMN5.0 TWEAKS: REMOVED AS UNUSED AND IS TIMING OUT ON LARGE DB.
            //Project Organization Users
//            Sql(@"INSERT INTO AclProjectOrganizationUsers (ProjectID, OrganizationID, UserID, SecurityGroupID, PermissionID, Allowed, Overridden)
//SELECT DISTINCT ID1, ID2, ID3, SubjectID, PrivilegeID, MIN(CASE WHEN DeniedEntries = 0 AND ExplicitDeniedEntries = 0 THEN 1 ELSE 0 END) AS Allowed, MAX(CASE WHEN ExplicitAllowedEntries > 0 OR ExplicitDeniedEntries > 0 THEN 1 ELSE 0 END) Overridden FROM Security_Tuple3 WHERE PrivilegeID IN ('C025131A-B5EC-46D5-B657-ADE567717A0D', '39683790-A857-4247-85DF-A9B425AC79CC', 'D4494B80-966A-473D-A1B3-4B18BBEF1F34', '0475D452-4B7A-4D3A-8295-4FC122F6A546', 'FDEE0BA5-AC09-4580-BAA4-496362985BF7', '40DB7DE2-EEFA-4D31-B400-7E72AB34DE99', 'BDC57049-27BA-41DF-B9F9-A15ABF19B120', '0549F5C8-6C0E-4491-BE90-EE0F29652422') AND EXISTS(SELECT NULL FROM Projects WHERE ID = ID1) AND EXISTS(SELECT NULL FROM Organizations WHERE ID = ID2) AND EXISTS(SELECT NULL FROM Users WHERE ID = ID3) group by ID1, ID2, ID3, SubjectID, PrivilegeID");

            //Update Triggers
            Sql(MigrationHelpers.AddAclDeleteScript("AclOrganizationUsers"));
            Sql(MigrationHelpers.AddAclDeleteScript("AclProjectUsers"));
            Sql(MigrationHelpers.AddAclDeleteScript("AclProjectOrganizationUsers"));
            Sql(MigrationHelpers.AddAclDeleteScript("AclProjectOrganizations"));

            Sql(MigrationHelpers.AddAclInsertScript("AclOrganizationUsers"));
            Sql(MigrationHelpers.AddAclInsertScript("AclProjectUsers"));
            Sql(MigrationHelpers.AddAclInsertScript("AclProjectOrganizationUsers"));
            Sql(MigrationHelpers.AddAclInsertScript("AclProjectOrganizations"));

            Sql(MigrationHelpers.AddAclUpdateScript("AclOrganizationUsers"));
            Sql(MigrationHelpers.AddAclUpdateScript("AclProjectUsers"));
            Sql(MigrationHelpers.AddAclUpdateScript("AclProjectOrganizationUsers"));
            Sql(MigrationHelpers.AddAclUpdateScript("AclProjectOrganizations"));

            //Update the Security Group triggers to include these 4 tables

            Sql(@"ALTER TRIGGER [dbo].[SecurityGroups_DeleteItem] 
        ON  [dbo].[SecurityGroups]
        AFTER DELETE
    AS 
    BEGIN
		UPDATE SecurityGroups SET ParentSecurityGroupID = NULL WHERE ParentSecurityGroupID IN (SELECT ID FROM deleted)

        DELETE FROM AclDataMarts WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclGlobal WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclGroups WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclOrganizationDataMarts WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclOrganizations WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclProjectDataMartRequestTypes WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclProjectDataMarts WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclProjects WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclRegistries WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclRequests WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclRequestSharedFolders WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclRequestTypes WHERE SecurityGroupID IN (SELECT ID FROM deleted)
		DELETE FROM AclUsers WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclUserEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclDataMartEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclGroupEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclOrganizationEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM ProjectEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclRegistryEvents WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclOrganizationUsers WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclProjectUsers WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclProjectOrganizationUsers WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        DELETE FROM AclProjectOrganizations WHERE SecurityGroupID IN (SELECT ID FROM deleted)
        --Add others here
	END"    );

            Sql(@"ALTER TRIGGER [dbo].[SecurityGroups_InsertItem] 
                       ON  [dbo].[SecurityGroups]
                       AFTER INSERT
                    AS 
                    BEGIN
	                    SET NOCOUNT ON;
	                    --Add the contacts from the inherited group
	                    INSERT INTO SecurityGroupUsers (SecurityGroupID, UserID) SELECT inserted.ID, SecurityGroupUsers.UserID FROM SecurityGroupUsers JOIN inserted ON SecurityGroupUsers.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

	                    --Add the ACL's from the inherited group
                        INSERT INTO AclDataMarts (DataMartID, SecurityGroupID, PermissionID, Allowed) SELECT acl.DataMartID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclDataMarts AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclGlobal (SecurityGroupID, PermissionID, Allowed) SELECT inserted.ID, acl.PermissionID, acl.Allowed FROM AclGlobal AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL
						
						INSERT INTO AclGroups(GroupID, SecurityGroupID, PermissionID, Allowed) SELECT acl.GroupID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclGroups AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclOrganizationDataMarts(OrganizationID, DataMartID, SecurityGroupID, PermissionID, Allowed) SELECT acl.OrganizationID, acl.DataMartID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclOrganizationDataMarts AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclOrganizations(OrganizationID, SecurityGroupID, PermissionID, Allowed) SELECT acl.OrganizationID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclOrganizations AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclProjectDataMartRequestTypes(ProjectID, DataMartID, RequestTypeID, SecurityGroupID, PermissionID, Allowed) SELECT acl.ProjectID, acl.DataMartID, acl.RequestTypeID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclProjectDataMartRequestTypes AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclProjectDataMarts(ProjectID, DataMartID, SecurityGroupID, PermissionID, Allowed) SELECT acl.ProjectID, acl.DataMartID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclProjectDataMarts AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclProjects(ProjectID, SecurityGroupID, PermissionID, Allowed) SELECT acl.ProjectID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclProjects AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclRegistries(RegistryID, SecurityGroupID, PermissionID, Allowed) SELECT acl.RegistryID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclRegistries AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclRequests(RequestID, SecurityGroupID, PermissionID, Allowed) SELECT acl.RequestID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclRequests AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclRequestSharedFolders(RequestSharedFolderID, SecurityGroupID, PermissionID, Allowed) SELECT acl.RequestSharedFolderID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclRequestSharedFolders AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclRequestTypes(RequestTypeID, SecurityGroupID, PermissionID, Allowed) SELECT acl.RequestTypeID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclRequestTypes AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

						INSERT INTO AclUsers(UserID, SecurityGroupID, PermissionID, Allowed) SELECT acl.UserID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclUsers AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclUserEvents(UserID, EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.UserID, acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclUserEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclDataMartEvents(DataMartID, EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.DataMartID, acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclDataMartEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclGroupEvents(GroupID, EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.GroupID, acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclGroupEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL
                        
                        INSERT INTO AclOrganizationEvents(OrganizationID, EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.OrganizationID, acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclOrganizationEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO ProjectEvents(ProjectID, EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.ProjectID, acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM ProjectEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclRegistryEvents(RegistryID, EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.RegistryID, acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclRegistryEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclEvents(EventID, SecurityGroupID, PermissionID, Allowed) SELECT acl.EventID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclEvents AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclOrganizationUsers(OrganizationID, UserID, SecurityGroupID, PermissionID, Allowed) SELECT acl.OrganizationID, acl.UserID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclOrganizationUsers AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclProjectUsers(ProjectID, UserID, SecurityGroupID, PermissionID, Allowed) SELECT acl.ProjectID, acl.UserID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclProjectUsers AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclProjectOrganizationUsers(ProjectID, OrganizationID, UserID, SecurityGroupID, PermissionID, Allowed) SELECT acl.ProjectID, acl.OrganizationID, acl.UserID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclProjectOrganizationUsers AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL

                        INSERT INTO AclProjectOrganizations(ProjectID, OrganizationID, SecurityGroupID, PermissionID, Allowed) SELECT acl.ProjectID, acl.OrganizationID, inserted.ID, acl.PermissionID, acl.Allowed FROM AclProjectOrganizations AS acl JOIN inserted ON acl.SecurityGroupID = inserted.ParentSecurityGroupID WHERE NOT inserted.ParentSecurityGroupID IS NULL
                        --Add other acl tables here


                    END");
        }