public override void Up()
{
Sql(SqlHelpers.DropConstraintIfExists("dbo.ScenarioResources", "CK_ScenarioResources_FileDetailsAllNullOrNotNull"));
Sql(SqlHelpers.DropConstraintIfExists("dbo.ScenarioResources", "Unique_ScenarioResources_ScenarioIdFileName"));
AddColumn("dbo.Departments", "AdminApproved", c => c.Boolean(nullable: false, defaultValue: false));
AddColumn("dbo.Institutions", "AdminApproved", c => c.Boolean(nullable: false, defaultValue: false));
AlterColumn("dbo.ScenarioResources", "FileName", c => c.String(nullable: false, maxLength: 256));
AlterColumn("dbo.ScenarioResources", "FileModified", c => c.DateTime(nullable: false));
AlterColumn("dbo.ScenarioResources", "FileSize", c => c.Long(nullable: false));
Sql("UPDATE [dbo].[Institutions] SET AdminApproved = 1");
Sql("UPDATE [dbo].[Departments] SET AdminApproved = 1");
Sql(SqlHelpers.CreateUniqueConstraint <ScenarioResource>("ScenarioResources", e => e.ScenarioId, e => e.FileName));
}
internal static IEnumerable <string> GetConstraints()
{
return(new[] {
SqlHelpers.DropConstraintIfExists("ProfessionalRoles", "Unique_RoleDescription"),
SqlHelpers.CreateUniqueConstraint <ProfessionalRole>("ProfessionalRoles", e => e.Category, e => e.Description),
SqlHelpers.CreateUniqueConstraint <HotDrink>("HotDrinks", e => e.Description),
SqlHelpers.CreateUniqueConstraint <Institution>("Institutions", e => e.LocaleCode, e => e.Name),
SqlHelpers.CreateUniqueConstraint <Department>("Departments", e => e.InstitutionId, e => e.Name),
SqlHelpers.CreateUniqueConstraint <Department>("Departments", e => e.InstitutionId, e => e.Abbreviation),
SqlHelpers.CreateUniqueConstraint <Scenario>("Scenarios", e => e.DepartmentId, e => e.BriefDescription),
SqlHelpers.CreateUniqueConstraint <Manikin>("Manikins", e => e.DepartmentId, e => e.Description),
SqlHelpers.CreateUniqueConstraint <ScenarioResource>("ScenarioResources", e => e.ScenarioId, e => e.FileName)
});
}
public override void Up()
{
Sql("ALTER TABLE [dbo].[Activities] DROP CONSTRAINT [CheckCousinsFilenames]");
Sql("DROP FUNCTION [dbo].[OtherTeachingResourcesWithSameFilename]");
Sql("DROP INDEX [Unique_ScenarioResources_ScenarioIdResourceFilename] ON [dbo].[ScenarioResources]");
AddColumn("dbo.Activities", "FileName", c => c.String(maxLength: 256));
AddColumn("dbo.Activities", "FileModified", c => c.DateTime());
AddColumn("dbo.Activities", "FileSize", c => c.Long());
AddColumn("dbo.ScenarioResources", "FileName", c => c.String(maxLength: 256));
AddColumn("dbo.ScenarioResources", "FileModified", c => c.DateTime());
AddColumn("dbo.ScenarioResources", "FileSize", c => c.Long());
DropColumn("dbo.Activities", "ResourceFilename");
DropColumn("dbo.Scenarios", "TemplateFilename");
DropColumn("dbo.ScenarioResources", "ResourceFilename");
foreach (var tbl in new[] { "ScenarioResources", "Activities" })
{
string constraintName = $"CK_{tbl}_FileDetailsAllNullOrNotNull";
Sql(string.Format(@"ALTER TABLE dbo.{0} WITH CHECK ADD CONSTRAINT {1} CHECK(([FileName] IS NULL AND [FileModified] IS NULL AND [FileSize] IS NULL) OR ([FileName] IS NOT NULL AND [FileModified] IS NOT NULL AND [FileSize] IS NOT NULL));
ALTER TABLE dbo.{0} CHECK CONSTRAINT {1};", tbl, constraintName), true);
}
Sql(SqlHelpers.CreateUniqueConstraint <ScenarioResource>("ScenarioResources", e => e.ScenarioId, e => e.FileName));
Sql(@"CREATE FUNCTION [dbo].[OtherTeachingResourcesWithSameFilename]
(
-- Add the parameters for the function here
@courseActivityId uniqueidentifier,
@activityId uniqueidentifier,
@filename nvarchar
)
RETURNS bit
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar bit
-- Add the T-SQL statements to compute the return value here
SET @ResultVar = CASE
WHEN @filename IS NOT NULL AND EXISTS(
SELECT 1
FROM dbo.CourseActivities as allCa
INNER JOIN dbo.Activities as atr ON atr.CourseActivityId = allCa.Id
WHERE allCa.CourseTypeId IN
(SELECT ca.CourseTypeId
FROM dbo.CourseActivities as ca
WHERE ca.Id = @CourseActivityId)
AND atr.FileName = @filename AND atr.Id <> @activityId
)
THEN 1
ELSE 0
END
-- Return the result of the function
RETURN @ResultVar
END;");
Sql(@"ALTER TABLE[dbo].[Activities] WITH CHECK ADD CONSTRAINT[CheckCousinsFilenames] CHECK(([dbo].[OtherTeachingResourcesWithSameFilename]([CourseActivityId],[Id],[FileName]) = (0)));
ALTER TABLE [dbo].[Activities] CHECK CONSTRAINT [CheckCousinsFilenames];");
}
public override void Up()
{
AddColumn("dbo.AspNetUsers", "AdminApproved", c => c.Boolean(nullable: false));
Sql("UPDATE dbo.AspNetUsers SET AdminApproved=1");
Sql(SqlHelpers.CreateUniqueConstraint <Participant>("AspNetUsers", p => p.FullName, p => p.DefaultDepartmentId, p => p.DefaultProfessionalRoleId));
}
