Index: Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql =================================================================== diff -u -r343779e3d40beea995062803e20eebe37cad8128 -rf77a5ebf15c19ec7aa0c3d32f00b290dc819cef8 --- Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql (.../Migration_17.3_18.1.sql) (revision 343779e3d40beea995062803e20eebe37cad8128) +++ Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql (.../Migration_17.3_18.1.sql) (revision f77a5ebf15c19ec7aa0c3d32f00b290dc819cef8) @@ -171,7 +171,6 @@ 0, 1 FROM [SOURCEPROJECT].GrassCoverErosionOutwardsSectionResultEntity; -INSERT INTO GrassCoverErosionOutwardsWaveConditionsCalculationEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionOutwardsWaveConditionsCalculationEntity; INSERT INTO GrassCoverErosionOutwardsWaveConditionsOutputEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionOutwardsWaveConditionsOutputEntity; INSERT INTO GrassCoverSlipOffInwardsSectionResultEntity ( [GrassCoverSlipOffInwardsSectionResultEntityId], @@ -823,6 +822,22 @@ -- performed based on the LocationId and the AssessmentSection. -- Note 2: The Hydraulic Location ID on assessment section level is chosen as a reference, because the hydraulic boundary -- location calculation entities contain references to these entities. +CREATE TEMP TABLE TempGrassCoverErosionOutwardsHydraulicBoundaryLocationLookupTable ( + 'GrassCoverErosionOutwardsHydraulicLocationEntityId' INTEGER NOT NULL UNIQUE, + 'HydraulicLocationEntityId' INTEGER NOT NULL UNIQUE +); + +INSERT INTO TempGrassCoverErosionOutwardsHydraulicBoundaryLocationLookupTable ( + [GrassCoverErosionOutwardsHydraulicLocationEntityId], + [HydraulicLocationEntityId] +) +SELECT + GrassCoverErosionOutwardsHydraulicLocationEntityId, + HydraulicLocationEntityId +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity gceohl +JOIN [SOURCEPROJECT].FailureMechanismEntity fm USING(FailureMechanismEntityId) +JOIN [SOURCEPROJECT].HydraulicLocationEntity hl ON (hl.LocationId = gceohl.LocationId AND hl.AssessmentSectionEntityId = fm.AssessmentSectionEntityId); + INSERT INTO TempHydraulicLocationCalculationEntity ( [HydraulicLocationEntityId], [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], @@ -1083,15 +1098,8 @@ OR gceofmme.HydraulicLocationCalculationCollectionEntity6Id = hlcce.HydraulicLocationCalculationCollectionEntityId JOIN HydraulicLocationCalculationEntity USING(HydraulicLocationCalculationCollectionEntityId) JOIN HydraulicLocationEntity hl USING(HydraulicLocationEntityId) - JOIN ( - SELECT - [LocationId], - [AssessmentSectionEntityId], - [ShouldDesignWaterLevelIllustrationPointsBeCalculated], - [ShouldWaveHeightIllustrationPointsBeCalculated] - FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity - JOIN [SOURCEPROJECT].FailureMechanismEntity USING(FailureMechanismEntityId) - ) SOURCE ON (SOURCE.LocationId = hl.LocationId AND SOURCE.AssessmentSectionEntityId = fm.AssessmentSectionEntityId) + JOIN TempGrassCoverErosionOutwardsHydraulicBoundaryLocationLookupTable USING(HydraulicLocationEntityId) + JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity USING(GrassCoverErosionOutwardsHydraulicLocationEntityId) WHERE (ShouldDesignWaterLevelIllustrationPointsBeCalculated = 1 AND NormativeNormType = 2 AND gceofmme.HydraulicLocationCalculationCollectionEntity2Id = hlcce.HydraulicLocationCalculationCollectionEntityId) OR (ShouldDesignWaterLevelIllustrationPointsBeCalculated = 1 AND NormativeNormType = 1 AND gceofmme.HydraulicLocationCalculationCollectionEntity3Id = hlcce.HydraulicLocationCalculationCollectionEntityId) OR (ShouldWaveHeightIllustrationPointsBeCalculated = 1 AND NormativeNormType = 2 AND gceofmme.HydraulicLocationCalculationCollectionEntity5Id = hlcce.HydraulicLocationCalculationCollectionEntityId) @@ -1127,31 +1135,58 @@ OR gceofmme.HydraulicLocationCalculationCollectionEntity6Id = hlcce.HydraulicLocationCalculationCollectionEntityId JOIN HydraulicLocationCalculationEntity USING(HydraulicLocationCalculationCollectionEntityId) JOIN HydraulicLocationEntity hl USING(HydraulicLocationEntityId) -JOIN ( - SELECT - [LocationId], - [AssessmentSectionEntityId], - [GeneralResultSubMechanismIllustrationPointEntityId], - [Result], - [TargetProbability], - [TargetReliability], - [CalculatedProbability], - [CalculatedReliability], - [CalculationConvergence], - [HydraulicLocationOutputType] - FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity - JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity USING(GrassCoverErosionOutwardsHydraulicLocationEntityId) - JOIN [SOURCEPROJECT].FailureMechanismEntity USING(FailureMechanismEntityId) -) SOURCE ON (SOURCE.LocationId = hl.LocationId AND SOURCE.AssessmentSectionEntityId = fm.AssessmentSectionEntityId) +JOIN TempGrassCoverErosionOutwardsHydraulicBoundaryLocationLookupTable USING(HydraulicLocationEntityId) +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity USING(GrassCoverErosionOutwardsHydraulicLocationEntityId) WHERE (HydraulicLocationOutputType = 1 AND NormativeNormType = 2 AND gceofmme.HydraulicLocationCalculationCollectionEntity2Id = hlcce.HydraulicLocationCalculationCollectionEntityId) OR (HydraulicLocationOutputType = 1 AND NormativeNormType = 1 AND gceofmme.HydraulicLocationCalculationCollectionEntity3Id = hlcce.HydraulicLocationCalculationCollectionEntityId) OR (HydraulicLocationOutputType = 2 AND NormativeNormType = 2 AND gceofmme.HydraulicLocationCalculationCollectionEntity5Id = hlcce.HydraulicLocationCalculationCollectionEntityId) OR (HydraulicLocationOutputType = 2 AND NormativeNormType = 1 AND gceofmme.HydraulicLocationCalculationCollectionEntity6Id = hlcce.HydraulicLocationCalculationCollectionEntityId); +-- Migrate the wave conditions calculations +INSERT INTO GrassCoverErosionOutwardsWaveConditionsCalculationEntity ( + [GrassCoverErosionOutwardsWaveConditionsCalculationEntityId], + [CalculationGroupEntityId], + [ForeshoreProfileEntityId], + [HydraulicLocationEntityId], + [Order], + [Name], + [Comments], + [UseBreakWater], + [BreakWaterType], + [BreakWaterHeight], + [UseForeshore], + [Orientation], + [UpperBoundaryRevetment], + [LowerBoundaryRevetment], + [UpperBoundaryWaterLevels], + [LowerBoundaryWaterLevels], + [StepSize]) +SELECT + [GrassCoverErosionOutwardsWaveConditionsCalculationEntityId], + [CalculationGroupEntityId], + [ForeshoreProfileEntityId], + lookup.HydraulicLocationEntityId, + [Order], + [Name], + [Comments], + [UseBreakWater], + [BreakWaterType], + [BreakWaterHeight], + [UseForeshore], + [Orientation], + [UpperBoundaryRevetment], + [LowerBoundaryRevetment], + [UpperBoundaryWaterLevels], + [LowerBoundaryWaterLevels], + [StepSize] +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsWaveConditionsCalculationEntity calc +LEFT JOIN TempGrassCoverErosionOutwardsHydraulicBoundaryLocationLookupTable lookup USING(GrassCoverErosionOutwardsHydraulicLocationEntityId); + -- Cleanup DROP TABLE TempCalculationTypes; DROP TABLE TempHydraulicLocationCalculationEntity; DROP TABLE TempHydraulicLocationCalculationCollectionEntity; +DROP TABLE TempGrassCoverErosionOutwardsHydraulicBoundaryLocationLookupTable; /* Write migration logging Index: Application/Ringtoets/test/Application.Ringtoets.Migration.Core.Test/test-data/FullTestProject173.rtd =================================================================== diff -u -rd46a891e1b44443392e77b1f84224c89e1ec8785 -rf77a5ebf15c19ec7aa0c3d32f00b290dc819cef8 Binary files differ Index: Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs =================================================================== diff -u -r343779e3d40beea995062803e20eebe37cad8128 -rf77a5ebf15c19ec7aa0c3d32f00b290dc819cef8 --- Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs (.../MigrationTo181IntegrationTest.cs) (revision 343779e3d40beea995062803e20eebe37cad8128) +++ Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs (.../MigrationTo181IntegrationTest.cs) (revision f77a5ebf15c19ec7aa0c3d32f00b290dc819cef8) @@ -95,6 +95,7 @@ AssertStabilityPointStructuresOutput(reader, sourceFilePath); AssertGrassCoverErosionOutwardsFailureMechanismMetaEntity(reader, sourceFilePath); + AssertGrassCoverErosionOutwardsWaveCalculationEntity(reader, sourceFilePath); AssertHeightStructuresSectionResultEntity(reader, sourceFilePath); AssertClosingStructuresSectionResultEntity(reader, sourceFilePath); @@ -720,6 +721,59 @@ reader.AssertReturnedDataIsValid(validateMetaEntity); } + private static void AssertGrassCoverErosionOutwardsWaveCalculationEntity(MigratedDatabaseReader reader, string sourceFilePath) + { + string validateMetaEntity = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT COUNT() = (SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionOutwardsWaveConditionsCalculationEntity) " + + "FROM GrassCoverErosionOutwardsWaveConditionsCalculationEntity NEW " + + "LEFT JOIN HydraulicLocationEntity hl USING(HydraulicLocationEntityId) " + + "JOIN ( " + + "SELECT " + + "[GrassCoverErosionOutwardsWaveConditionsCalculationEntityId], " + + "[LocationId], " + + "[AssessmentSectionEntityId], " + + "calc.CalculationGroupEntityId, " + + "[ForeshoreProfileEntityId], " + + "calc.'Order', " + + "calc.Name, " + + "[Comments], " + + "[UseBreakWater], " + + "[BreakWaterType], " + + "[BreakWaterHeight], " + + "[UseForeshore], " + + "[Orientation], " + + "[UpperBoundaryRevetment], " + + "[LowerBoundaryRevetment], " + + "[UpperBoundaryWaterLevels], " + + "[LowerBoundaryWaterLevels], " + + "[StepSize] " + + "FROM [SOURCEPROJECT].GrassCoverErosionOutwardsWaveConditionsCalculationEntity calc " + + "LEFT JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity USING(GrassCoverErosionOutwardsHydraulicLocationEntityId) " + + "LEFT JOIN [SOURCEPROJECT].FailureMechanismEntity USING(FailureMechanismEntityId) " + + ") OLD USING (GrassCoverErosionOutwardsWaveConditionsCalculationEntityId) " + + "WHERE OLD.LocationId IS hl.LocationId " + + "AND OLD.AssessmentSectionEntityId IS hl.AssessmentSectionEntityId " + + "AND OLD.CalculationGroupEntityId = NEW.CalculationGroupEntityId " + + "AND OLD.ForeshoreProfileEntityId IS NEW.ForeshoreProfileEntityId " + + "AND OLD.'Order' = NEW.'Order' " + + "AND OLD.Name IS NEW.Name " + + "AND OLD.Comments IS NEW.Comments " + + "AND OLD.UseBreakWater = NEW.UseBreakWater " + + "AND OLD.BreakWaterType = NEW.BreakWaterType " + + "AND OLD.BreakWaterHeight IS NEW.BreakWaterHeight " + + "AND OLD.UseForeshore = NEW.UseForeshore " + + "AND OLD.Orientation IS NEW.Orientation " + + "AND OLD.UpperBoundaryRevetment IS NEW.UpperBoundaryRevetment " + + "AND OLD.LowerBoundaryRevetment IS NEW.LowerBoundaryRevetment " + + "AND OLD.UpperBoundaryWaterLevels IS NEW.UpperBoundaryWaterLevels " + + "AND OLD.LowerBoundaryWaterLevels IS NEW.LowerBoundaryWaterLevels " + + "AND OLD.StepSize = New.StepSize; " + + "DETACH DATABASE SOURCEPROJECT;"; + + reader.AssertReturnedDataIsValid(validateMetaEntity); + } + #region Failure Mechanism Section Result Entities private static void AssertHeightStructuresSectionResultEntity(MigratedDatabaseReader reader, string sourceFilePath)