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"));
}
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(MigrationHelpers.GetDynamicSqlFromFile(@"SqlScripts/InitialFill.sql"));
InitialFill.LeaguePlayerLinksFill(migrationBuilder);
}
/// <summary>
/// return down script
/// </summary>
/// <returns></returns>
public override string DownScript()
{
return(MigrationHelpers.ScriptForCreateTable(Table));
}
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);
}
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");
}
/// <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)));
}
/// <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);
}
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");
}