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)); }