Index: Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql =================================================================== diff -u -r014485245a412072446920555cbce8586686f723 -reb4dabff7c25ca660e5c27b500ec94fa895ae2d9 --- Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql (.../Migration_17.3_18.1.sql) (revision 014485245a412072446920555cbce8586686f723) +++ Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql (.../Migration_17.3_18.1.sql) (revision eb4dabff7c25ca660e5c27b500ec94fa895ae2d9) @@ -583,7 +583,95 @@ WHERE ase.NormativeNormType = 1 AND source.ShouldWaveHeightIllustrationPointsBeCalculated = 1 ); +--Migrate the outputs on AssessmentSection level +INSERT INTO HydraulicLocationOutputEntity ( + [HydraulicLocationOutputEntityId], + [HydraulicLocationCalculationEntityId], + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence]) +SELECT + [HydraulicLocationEntityOutputId], + [HydraulicLocationCalculationEntityId], + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence] +FROM AssessmentSectionEntity ase +JOIN HydraulicLocationCalculationCollectionEntity hlcce ON ase.HydraulicLocationCalculationCollectionEntity2Id = hlcce.HydraulicLocationCalculationCollectionEntityId +JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) +JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) +JOIN HydraulicLocationEntity USING (HydraulicLocationEntityId) +JOIN [SOURCEPROJECT].HydraulicLocationOutputEntity USING (HydraulicLocationEntityId) +WHERE HydraulicLocationOutputType = 1 AND NormativeNormType = 2 +UNION + +SELECT + [HydraulicLocationEntityOutputId], + [HydraulicLocationCalculationEntityId], + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence] +FROM AssessmentSectionEntity ase +JOIN HydraulicLocationCalculationCollectionEntity hlcce ON ase.HydraulicLocationCalculationCollectionEntity3Id = hlcce.HydraulicLocationCalculationCollectionEntityId +JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) +JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) +JOIN HydraulicLocationEntity USING (HydraulicLocationEntityId) +JOIN [SOURCEPROJECT].HydraulicLocationOutputEntity USING (HydraulicLocationEntityId) +WHERE HydraulicLocationOutputType = 1 AND NormativeNormType = 1 + +UNION + +SELECT + [HydraulicLocationEntityOutputId], + [HydraulicLocationCalculationEntityId], + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence] +FROM AssessmentSectionEntity ase +JOIN HydraulicLocationCalculationCollectionEntity hlcce ON ase.HydraulicLocationCalculationCollectionEntity6Id = hlcce.HydraulicLocationCalculationCollectionEntityId +JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) +JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) +JOIN HydraulicLocationEntity USING (HydraulicLocationEntityId) +JOIN [SOURCEPROJECT].HydraulicLocationOutputEntity USING (HydraulicLocationEntityId) +WHERE HydraulicLocationOutputType = 2 AND NormativeNormType = 2 + +UNION + +SELECT + [HydraulicLocationEntityOutputId], + [HydraulicLocationCalculationEntityId], + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence] +FROM AssessmentSectionEntity ase +JOIN HydraulicLocationCalculationCollectionEntity hlcce ON ase.HydraulicLocationCalculationCollectionEntity7Id = hlcce.HydraulicLocationCalculationCollectionEntityId +JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) +JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) +JOIN HydraulicLocationEntity USING (HydraulicLocationEntityId) +JOIN [SOURCEPROJECT].HydraulicLocationOutputEntity USING (HydraulicLocationEntityId) +WHERE HydraulicLocationOutputType = 2 AND NormativeNormType = 1; + -- Cleanup DROP TABLE TempHydraulicLocationCalculationEntity; DROP TABLE TempHydraulicLocationCalculationCollectionEntity; Index: Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs =================================================================== diff -u -r014485245a412072446920555cbce8586686f723 -reb4dabff7c25ca660e5c27b500ec94fa895ae2d9 --- Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs (.../MigrationTo181IntegrationTest.cs) (revision 014485245a412072446920555cbce8586686f723) +++ Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs (.../MigrationTo181IntegrationTest.cs) (revision eb4dabff7c25ca660e5c27b500ec94fa895ae2d9) @@ -400,7 +400,8 @@ "AND NEW.HydraulicDatabaseVersion IS OLD.HydraulicDatabaseVersion " + "AND NEW.HydraulicDatabaseLocation IS OLD.HydraulicDatabaseLocation " + "AND NEW.Composition = OLD.Composition " + - "AND NEW.ReferenceLinePointXml IS OLD.ReferenceLinePointXml; " + + "AND NEW.ReferenceLinePointXml IS OLD.ReferenceLinePointXml " + + "AND NEW.\"Order\" = OLD.\"Order\"; " + "DETACH DATABASE SOURCEPROJECT;"; reader.AssertReturnedDataIsValid(validateAssessmentSectionEntities); @@ -414,8 +415,8 @@ reader.AssertReturnedDataIsValid(queryGenerator.GetHydraulicLocationsValidationQuery()); reader.AssertReturnedDataIsValid(queryGenerator.GetNrOfHydraulicBoundaryLocationCalculationsValidationQuery()); - reader.AssertReturnedDataIsValid(queryGenerator.GetHydraulicBoundaryLocationCalculationInputValidationQuery()); - + reader.AssertReturnedDataIsValid(HydraulicLocationValidationQueryGenerator.GetHydraulicBoundaryLocationCalculationInputValidationQuery()); + AssertDesignWaterLevelCalculationEntities(reader, queryGenerator); AssertWaveHeightCalculationEntities(reader, queryGenerator); } @@ -440,6 +441,11 @@ HydraulicLocationValidationQueryGenerator.CalculationType.WaveHeightCalculationsForFactorizedSignalingNorm)); reader.AssertReturnedDataIsValid(queryGenerator.GetNewCalculationsValidationQuery( HydraulicLocationValidationQueryGenerator.CalculationType.WaveHeightCalculationsForFactorizedLowerLimitNorm)); + + reader.AssertReturnedDataIsValid(queryGenerator.GetDesignWaterLevelCalculationOutputsValidationQuery( + HydraulicLocationValidationQueryGenerator.NormativeNormType.LowerLimitNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetDesignWaterLevelCalculationOutputsValidationQuery( + HydraulicLocationValidationQueryGenerator.NormativeNormType.SignalingNorm)); } private static void AssertDesignWaterLevelCalculationEntities(MigratedDatabaseReader reader, HydraulicLocationValidationQueryGenerator queryGenerator) @@ -462,6 +468,11 @@ HydraulicLocationValidationQueryGenerator.CalculationType.WaterLevelCalculationsForFactorizedSignalingNorm)); reader.AssertReturnedDataIsValid(queryGenerator.GetNewCalculationsValidationQuery( HydraulicLocationValidationQueryGenerator.CalculationType.WaterLevelCalculationsForFactorizedLowerLimitNorm)); + + reader.AssertReturnedDataIsValid(queryGenerator.GetWaveHeightCalculationOutputsValidationQuery( + HydraulicLocationValidationQueryGenerator.NormativeNormType.LowerLimitNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetWaveHeightCalculationOutputsValidationQuery( + HydraulicLocationValidationQueryGenerator.NormativeNormType.SignalingNorm)); } /// @@ -594,7 +605,7 @@ /// values. /// /// The query to validate the hydraulic boundary location calculation inputs. - public string GetHydraulicBoundaryLocationCalculationInputValidationQuery() + public static string GetHydraulicBoundaryLocationCalculationInputValidationQuery() { return "SELECT " + "COUNT() = 0 " + @@ -650,24 +661,14 @@ /// but is unsupported. public string GetDesignWaterLevelCalculationsValidationQuery(NormativeNormType normType) { - CalculationType calculationType; - switch (normType) - { - case NormativeNormType.LowerLimitNorm: - calculationType = CalculationType.WaterLevelCalculationsForLowerLimitNorm; - break; - case NormativeNormType.SignalingNorm: - calculationType = CalculationType.WaterLevelCalculationsForSignalingNorm; - break; - default: - throw new NotSupportedException(); - } + CalculationType calculationType = ConvertToDesignWaterLevelCalculationType(normType); return $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + "SELECT " + "COUNT() = 0 " + "FROM AssessmentSectionEntity ase " + - $"JOIN HydraulicLocationCalculationCollectionEntity hlcce ON ase.HydraulicLocationCalculationCollectionEntity{(int) calculationType}Id = hlcce.HydraulicLocationCalculationCollectionEntityId " + + "JOIN HydraulicLocationCalculationCollectionEntity hlcce " + + $"ON ase.HydraulicLocationCalculationCollectionEntity{(int) calculationType}Id = hlcce.HydraulicLocationCalculationCollectionEntityId " + "JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) " + "JOIN HydraulicLocationCalculationEntity NEW USING (HydraulicLocationCalculationEntityId) " + "JOIN [SOURCEPROJECT].HydraulicLocationEntity OLD USING (HydraulicLocationEntityId) " + @@ -676,6 +677,47 @@ } /// + /// Generates a query to validate if the hydraulic boundary location calculation outputs related to the design water level calculations + /// are migrated correctly to the corresponding calculation entities. + /// + /// The norm type to generate the query for. + /// A query to validate the hydraulic boundary location calculation outputs. + /// Thrown when + /// is an invalid value of . + /// Thrown when is an unsupported value, + /// but is unsupported. + public string GetDesignWaterLevelCalculationOutputsValidationQuery(NormativeNormType normType) + { + CalculationType calculationType = ConvertToDesignWaterLevelCalculationType(normType); + + return $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT " + + "COUNT() = " + + "( " + + "SELECT COUNT() " + + "FROM [SOURCEPROJECT].HydraulicLocationOutputEntity sourceHlo " + + "JOIN [SOURCEPROJECT].HydraulicLocationEntity USING (HydraulicLocationEntityId) " + + "JOIN [SOURCEPROJECT].AssessmentSectionEntity sourceAse USING (AssessmentSectionEntityId) " + + $"WHERE sourceHlo.HydraulicLocationOutputType = 1 AND sourceAse.NormativeNormType = {(int) normType} " + + ") " + + "FROM AssessmentSectionEntity ase " + + "JOIN HydraulicLocationCalculationCollectionEntity hlcce " + + $"ON ase.HydraulicLocationCalculationCollectionEntity{(int) calculationType}Id = hlcce.HydraulicLocationCalculationCollectionEntityId " + + "JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) " + + "JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) " + + "JOIN HydraulicLocationOutputEntity NEW USING (HydraulicLocationCalculationEntityId) " + + "JOIN [SOURCEPROJECT].HydraulicLocationOutputEntity OLD ON NEW.HydraulicLocationOutputEntityId = OLD.HydraulicLocationEntityOutputId " + + "WHERE NEW.GeneralResultSubMechanismIllustrationPointEntityId IS OLD.GeneralResultSubMechanismIllustrationPointEntityId " + + "AND NEW.Result IS OLD.Result " + + "AND NEW.TargetProbability IS OLD.TargetProbability " + + "AND NEW.TargetReliability IS OLD.TargetReliability " + + "AND NEW.CalculatedProbability IS OLD.CalculatedProbability " + + "AND NEW.CalculatedReliability IS OLD.CalculatedReliability " + + "AND NEW.CalculationConvergence = OLD.CalculationConvergence; " + + "DETACH DATABASE SOURCEPROJECT;"; + } + + /// /// Generates a query to validate if the hydraulic boundary calculation input for the wave height calculations /// are migrated correctly. /// @@ -687,29 +729,14 @@ /// but is unsupported. public string GetWaveHeightCalculationsValidationQuery(NormativeNormType normType) { - if (!Enum.IsDefined(typeof(NormativeNormType), normType)) - { - throw new InvalidEnumArgumentException(nameof(normType), (int)normType, typeof(NormativeNormType)); - } + CalculationType calculationType = ConvertToWaveHeightCalculationType(normType); - CalculationType calculationType; - switch (normType) - { - case NormativeNormType.LowerLimitNorm: - calculationType = CalculationType.WaveHeightCalculationsForLowerLimitNorm; - break; - case NormativeNormType.SignalingNorm: - calculationType = CalculationType.WaveHeightCalculationsForSignalingNorm; - break; - default: - throw new NotSupportedException(); - } - return $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + "SELECT " + "COUNT() = 0 " + "FROM AssessmentSectionEntity ase " + - $"JOIN HydraulicLocationCalculationCollectionEntity hlcce ON ase.HydraulicLocationCalculationCollectionEntity{(int) calculationType}Id = hlcce.HydraulicLocationCalculationCollectionEntityId " + + "JOIN HydraulicLocationCalculationCollectionEntity hlcce " + + $"ON ase.HydraulicLocationCalculationCollectionEntity{(int) calculationType}Id = hlcce.HydraulicLocationCalculationCollectionEntityId " + "JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) " + "JOIN HydraulicLocationCalculationEntity NEW USING (HydraulicLocationCalculationEntityId) " + "JOIN [SOURCEPROJECT].HydraulicLocationEntity OLD USING (HydraulicLocationEntityId) " + @@ -718,6 +745,47 @@ } /// + /// Generates a query to validate if the hydraulic boundary location calculation outputs related to the wave height calculations + /// are migrated correctly to the corresponding calculation entities. + /// + /// The norm type to generate the query for. + /// A query to validate the hydraulic boundary location calculation outputs. + /// Thrown when + /// is an invalid value of . + /// Thrown when is an unsupported value, + /// but is unsupported. + public string GetWaveHeightCalculationOutputsValidationQuery(NormativeNormType normType) + { + CalculationType calculationType = ConvertToDesignWaterLevelCalculationType(normType); + + return $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT " + + "COUNT() = " + + "( " + + "SELECT COUNT() " + + "FROM [SOURCEPROJECT].HydraulicLocationOutputEntity sourceHlo " + + "JOIN [SOURCEPROJECT].HydraulicLocationEntity USING (HydraulicLocationEntityId) " + + "JOIN [SOURCEPROJECT].AssessmentSectionEntity sourceAse USING (AssessmentSectionEntityId) " + + $"WHERE sourceHlo.HydraulicLocationOutputType = 2 AND sourceAse.NormativeNormType = {(int) normType} " + + ") " + + "FROM AssessmentSectionEntity ase " + + "JOIN HydraulicLocationCalculationCollectionEntity hlcce " + + $"ON ase.HydraulicLocationCalculationCollectionEntity{(int) calculationType}Id = hlcce.HydraulicLocationCalculationCollectionEntityId " + + "JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) " + + "JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) " + + "JOIN HydraulicLocationOutputEntity NEW USING (HydraulicLocationCalculationEntityId) " + + "JOIN [SOURCEPROJECT].HydraulicLocationOutputEntity OLD ON NEW.HydraulicLocationOutputEntityId = OLD.HydraulicLocationEntityOutputId " + + "WHERE NEW.GeneralResultSubMechanismIllustrationPointEntityId IS OLD.GeneralResultSubMechanismIllustrationPointEntityId " + + "AND NEW.Result IS OLD.Result " + + "AND NEW.TargetProbability IS OLD.TargetProbability " + + "AND NEW.TargetReliability IS OLD.TargetReliability " + + "AND NEW.CalculatedProbability IS OLD.CalculatedProbability " + + "AND NEW.CalculatedReliability IS OLD.CalculatedReliability " + + "AND NEW.CalculationConvergence = OLD.CalculationConvergence; " + + "DETACH DATABASE SOURCEPROJECT;"; + } + + /// /// Generates a query to validate the new hydraulic boundary location calculations that are not based on migrated data. /// /// The type of calculation on which the input should be validated. @@ -734,6 +802,60 @@ "WHERE hlce.ShouldIllustrationPointsBeCalculated = 0;" + "DETACH DATABASE SOURCEPROJECT;"; } + + /// + /// Converts the to the corresponding design water level calculation from . + /// + /// The norm type to convert. + /// Returns the converted . + /// Thrown when + /// is an invalid value of . + /// Thrown when is an unsupported value, + /// but is unsupported. + private static CalculationType ConvertToDesignWaterLevelCalculationType(NormativeNormType normType) + { + if (!Enum.IsDefined(typeof(NormativeNormType), normType)) + { + throw new InvalidEnumArgumentException(nameof(normType), (int) normType, typeof(NormativeNormType)); + } + + switch (normType) + { + case NormativeNormType.LowerLimitNorm: + return CalculationType.WaterLevelCalculationsForLowerLimitNorm; + case NormativeNormType.SignalingNorm: + return CalculationType.WaterLevelCalculationsForSignalingNorm; + default: + throw new NotSupportedException(); + } + } + + /// + /// Converts the to the corresponding wave height calculation from . + /// + /// The norm type to convert. + /// Returns the converted . + /// Thrown when + /// is an invalid value of . + /// Thrown when is an unsupported value, + /// but is unsupported. + private static CalculationType ConvertToWaveHeightCalculationType(NormativeNormType normType) + { + if (!Enum.IsDefined(typeof(NormativeNormType), normType)) + { + throw new InvalidEnumArgumentException(nameof(normType), (int) normType, typeof(NormativeNormType)); + } + + switch (normType) + { + case NormativeNormType.LowerLimitNorm: + return CalculationType.WaveHeightCalculationsForLowerLimitNorm; + case NormativeNormType.SignalingNorm: + return CalculationType.WaveHeightCalculationsForSignalingNorm; + default: + throw new NotSupportedException(); + } + } } #endregion