Index: Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql =================================================================== diff -u -rde6bbc678fed36fcc4cd4509635416e85e4c66ba -r0603ceccd7d126c95612923d0d13b78c7a2e8ad2 --- Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql (.../Migration_17.3_18.1.sql) (revision de6bbc678fed36fcc4cd4509635416e85e4c66ba) +++ Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql (.../Migration_17.3_18.1.sql) (revision 0603ceccd7d126c95612923d0d13b78c7a2e8ad2) @@ -321,9 +321,72 @@ [LocationX], [LocationY], [Order] - FROM [SOURCEPROJECT].HydraulicLocationEntity AS sp + FROM [SOURCEPROJECT].HydraulicLocationEntity JOIN TempHydraulicLocationCalculationEntity USING (HydraulicLocationEntityId); +-- Perform the migration of the output entities + +INSERT INTO HydraulicLocationOutputEntity ( + [HydraulicLocationEntityOutputId], + [HydraulicLocationCalculationEntityId], + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence]) +SELECT + [HydraulicLocationEntityOutputId], + CASE WHEN [HydraulicLocationOutputType] = 1 + THEN [Calculation2Id] + ELSE + [Calculation6Id] + END, + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence] +FROM [SOURCEPROJECT].HydraulicLocationOutputEntity hlo +JOIN [SOURCEPROJECT].HydraulicLocationEntity hl ON hlo.HydraulicLocationEntityId = hl.HydraulicLocationEntityId +JOIN TempHydraulicLocationCalculationEntity USING (HydraulicLocationEntityId) +JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) +WHERE NormativeNormType = 2; + + +INSERT INTO HydraulicLocationOutputEntity ( + [HydraulicLocationEntityOutputId], + [HydraulicLocationCalculationEntityId], + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence]) +SELECT + [HydraulicLocationEntityOutputId], + CASE WHEN [HydraulicLocationOutputType] = 1 + THEN [Calculation3Id] + ELSE + [Calculation7Id] + END, + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence] +FROM [SOURCEPROJECT].HydraulicLocationOutputEntity hlo +JOIN [SOURCEPROJECT].HydraulicLocationEntity hl ON hlo.HydraulicLocationEntityId = hl.HydraulicLocationEntityId +JOIN TempHydraulicLocationCalculationEntity USING (HydraulicLocationEntityId) +JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) +WHERE NormativeNormType = 1; + -- Update the calculation inputs based on the norm UPDATE HydraulicLocationCalculationEntity SET [ShouldIllustrationPointsBeCalculated] = 1 Index: Application/Ringtoets/test/Application.Ringtoets.Migration.Core.Test/test-data/FullTestProject173.rtd =================================================================== diff -u -rde6bbc678fed36fcc4cd4509635416e85e4c66ba -r0603ceccd7d126c95612923d0d13b78c7a2e8ad2 Binary files differ Index: Application/Ringtoets/test/Application.Ringtoets.Migration.Core.Test/test-data/FullTestProject181.rtd =================================================================== diff -u -r7d89262d3b971dceec1e987e22d0027bf675c196 -r0603ceccd7d126c95612923d0d13b78c7a2e8ad2 Binary files differ Index: Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs =================================================================== diff -u -rde6bbc678fed36fcc4cd4509635416e85e4c66ba -r0603ceccd7d126c95612923d0d13b78c7a2e8ad2 --- Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs (.../MigrationTo181IntegrationTest.cs) (revision de6bbc678fed36fcc4cd4509635416e85e4c66ba) +++ Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs (.../MigrationTo181IntegrationTest.cs) (revision 0603ceccd7d126c95612923d0d13b78c7a2e8ad2) @@ -249,28 +249,199 @@ reader.AssertReturnedDataIsValid(validatePreprocessorSettings); } + private static void AssertPipingSoilLayers(MigratedDatabaseReader reader) + { + const string validateBelowPhreaticLevel = + "SELECT COUNT() = 0 " + + "FROM PipingSoilLayerEntity " + + "WHERE [BelowPhreaticLevelMean] < [BelowPhreaticLevelShift] " + + "OR [BelowPhreaticLevelMean] <= 0 " + + "OR [BelowPhreaticLevelDeviation] < 0;"; + reader.AssertReturnedDataIsValid(validateBelowPhreaticLevel); + + const string validateDiameter70 = + "SELECT COUNT() = 0 " + + "FROM PipingSoilLayerEntity " + + "WHERE [DiameterD70Mean] <= 0 " + + "OR [DiameterD70CoefficientOfVariation] < 0;"; + reader.AssertReturnedDataIsValid(validateDiameter70); + + const string validatePermeability = + "SELECT COUNT() = 0 " + + "FROM PipingSoilLayerEntity " + + "WHERE [PermeabilityMean] <= 0 " + + "OR [PermeabilityCoefficientOfVariation] < 0;"; + reader.AssertReturnedDataIsValid(validatePermeability); + } + + private static void AssertStabilityStoneCoverFailureMechanism(MigratedDatabaseReader reader) + { + const string validateStabilityStoneCoverFailureMechanism = + "SELECT COUNT() = 0 " + + "FROM [StabilityStoneCoverFailureMechanismMetaEntity] " + + "WHERE [N] IS NOT 4;"; + reader.AssertReturnedDataIsValid(validateStabilityStoneCoverFailureMechanism); + } + + #region Migrated Hydraulic Boundary Locations + private static void AssertHydraulicBoundaryLocations(MigratedDatabaseReader reader, string sourceFilePath) { + string validateHydraulicBoundaryLocationEntities = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT COUNT() = (SELECT COUNT() FROM [SOURCEPROJECT].HydraulicLocationEntity) " + + "FROM HydraulicLocationEntity NEW " + + "JOIN [SOURCEPROJECT].HydraulicLocationEntity OLD USING(HydraulicLocationEntityId) " + + "WHERE NEW.AssessmentSectionEntityId = OLD.AssessmentSectionEntityId " + + "AND NEW.LocationId = OLD.LocationId " + + "AND NEW.Name = OLD.Name " + + "AND NEW.LocationX IS OLD.LocationX " + + "AND NEW.LocationY IS OLD.LocationY;" + + "DETACH DATABASE SOURCEPROJECT;"; + reader.AssertReturnedDataIsValid(validateHydraulicBoundaryLocationEntities); + string validateNrOfHydraulicBoundaryCalculationEntities = $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + "SELECT COUNT() = (SELECT COUNT() * 8 FROM [SOURCEPROJECT].HydraulicLocationEntity) " + "FROM HydraulicLocationCalculationEntity; " + - "DETACH SOURCEPROJECT;"; + "DETACH DATABASE SOURCEPROJECT;"; reader.AssertReturnedDataIsValid(validateNrOfHydraulicBoundaryCalculationEntities); + string validateNrOfHydraulicBoundaryCalculationOutputEntities = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT " + + "COUNT() = (SELECT COUNT() FROM [SOURCEPROJECT].HydraulicLocationOutputEntity) " + + "FROM HydraulicLocationOutputEntity NEW " + + "JOIN [SOURCEPROJECT].HydraulicLocationOutputEntity OLD USING(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 IS OLD.CalculationConvergence; " + + "DETACH DATABASE SOURCEPROJECT;"; + reader.AssertReturnedDataIsValid(validateNrOfHydraulicBoundaryCalculationOutputEntities); + AssertMigratedHydraulicLocationCalculations(reader, sourceFilePath); } + private static void AssertNewHydraulicLocationCalculations(MigratedDatabaseReader reader, string sourceFilePath, int entityCalculationNumber) + { + string validateNewHydraulicCalculations = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT COUNT() = (SELECT COUNT() FROM [SOURCEPROJECT].HydraulicLocationEntity) " + + "FROM HydraulicLocationEntity AS NEW " + + $"JOIN HydraulicLocationCalculationEntity hlce ON NEW.HydraulicLocationCalculationEntity{entityCalculationNumber}Id = hlce.HydraulicLocationCalculationEntityId " + + "WHERE hlce.ShouldIllustrationPointsBeCalculated = 0;" + + "DETACH DATABASE SOURCEPROJECT;"; + reader.AssertReturnedDataIsValid(validateNewHydraulicCalculations); + } + private static void AssertMigratedHydraulicLocationCalculations(MigratedDatabaseReader reader, string sourceFilePath) { + AssertDesignWaterLevelCalculations(reader, sourceFilePath); + + AssertWaveHeightCalculations(reader, sourceFilePath); + + AssertMigratedHydraulicLocationCalculationOutputs(reader, sourceFilePath); + } + + private static void AssertMigratedHydraulicLocationCalculationOutputs(MigratedDatabaseReader reader, string sourceFilePath) + { + const string validateOutputNewCalculations = + "SELECT COUNT() = 0 " + + "FROM HydraulicLocationEntity hl " + + "JOIN HydraulicLocationCalculationEntity calc1 ON calc1.HydraulicLocationCalculationEntityId = hl.HydraulicLocationCalculationEntity1Id " + + "JOIN HydraulicLocationCalculationEntity calc4 ON calc4.HydraulicLocationCalculationEntityId = hl.HydraulicLocationCalculationEntity4Id " + + "JOIN HydraulicLocationCalculationEntity calc5 ON calc5.HydraulicLocationCalculationEntityId = hl.HydraulicLocationCalculationEntity5Id " + + "JOIN HydraulicLocationCalculationEntity calc8 ON calc8.HydraulicLocationCalculationEntityId = hl.HydraulicLocationCalculationEntity8Id " + + "JOIN HydraulicLocationOutputEntity USING(HydraulicLocationCalculationEntityId);"; + reader.AssertReturnedDataIsValid(validateOutputNewCalculations); + + string validateDesignWaterLevelCalculationsWithSignalingNormOutput = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT;" + + "SELECT COUNT() = " + + "( " + + "SELECT COUNT() " + + "FROM [SOURCEPROJECT].HydraulicLocationOutputEntity " + + "JOIN [SOURCEPROJECT].HydraulicLocationEntity USING (HydraulicLocationEntityId) " + + "JOIN [SOURCEPROJECT].AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "WHERE NormativeNormType = 2 AND HydraulicLocationOutputType = 1 " + + ") " + + "FROM HydraulicLocationEntity NEWHL " + + "JOIN HydraulicLocationCalculationEntity calc ON calc.HydraulicLocationCalculationEntityId = NEWHL.HydraulicLocationCalculationEntity2Id " + + "JOIN HydraulicLocationOutputEntity NEW USING(HydraulicLocationCalculationEntityId) " + + "JOIN [SOURCEPROJECT].HydraulicLocationOutputEntity OLD ON NEW.HydraulicLocationEntityOutputId = OLD.HydraulicLocationEntityOutputId " + + "WHERE OLD.HydraulicLocationEntityId = NEWHL.HydraulicLocationEntityId;" + + "DETACH DATABASE SOURCEPROJECT;"; + reader.AssertReturnedDataIsValid(validateDesignWaterLevelCalculationsWithSignalingNormOutput); + + string validateDesignWaterLevelCalculationsWithLowerLimitNormOutput = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT;" + + "SELECT COUNT() = " + + "( " + + "SELECT COUNT() " + + "FROM [SOURCEPROJECT].HydraulicLocationOutputEntity " + + "JOIN [SOURCEPROJECT].HydraulicLocationEntity USING (HydraulicLocationEntityId) " + + "JOIN [SOURCEPROJECT].AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "WHERE NormativeNormType = 1 AND HydraulicLocationOutputType = 1 " + + ") " + + "FROM HydraulicLocationEntity NEWHL " + + "JOIN HydraulicLocationCalculationEntity calc ON calc.HydraulicLocationCalculationEntityId = NEWHL.HydraulicLocationCalculationEntity3Id " + + "JOIN HydraulicLocationOutputEntity NEW USING(HydraulicLocationCalculationEntityId) " + + "JOIN [SOURCEPROJECT].HydraulicLocationOutputEntity OLD ON NEW.HydraulicLocationEntityOutputId = OLD.HydraulicLocationEntityOutputId " + + "WHERE OLD.HydraulicLocationEntityId = NEWHL.HydraulicLocationEntityId;" + + "DETACH DATABASE SOURCEPROJECT;"; + reader.AssertReturnedDataIsValid(validateDesignWaterLevelCalculationsWithLowerLimitNormOutput); + + string validateWaveHeightCalculationsWithSignalingNormOutput = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT;" + + "SELECT COUNT() = " + + "( " + + "SELECT COUNT() " + + "FROM [SOURCEPROJECT].HydraulicLocationOutputEntity " + + "JOIN [SOURCEPROJECT].HydraulicLocationEntity USING (HydraulicLocationEntityId) " + + "JOIN [SOURCEPROJECT].AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "WHERE NormativeNormType = 2 AND HydraulicLocationOutputType = 2 " + + ") " + + "FROM HydraulicLocationEntity NEWHL " + + "JOIN HydraulicLocationCalculationEntity calc ON calc.HydraulicLocationCalculationEntityId = NEWHL.HydraulicLocationCalculationEntity6Id " + + "JOIN HydraulicLocationOutputEntity NEW USING(HydraulicLocationCalculationEntityId) " + + "JOIN [SOURCEPROJECT].HydraulicLocationOutputEntity OLD ON NEW.HydraulicLocationEntityOutputId = OLD.HydraulicLocationEntityOutputId " + + "WHERE OLD.HydraulicLocationEntityId = NEWHL.HydraulicLocationEntityId;" + + "DETACH DATABASE SOURCEPROJECT;"; + reader.AssertReturnedDataIsValid(validateWaveHeightCalculationsWithSignalingNormOutput); + + string validateWaveHeightCalculationsWithLowerLimitNormOutput = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT;" + + "SELECT COUNT() = " + + "( " + + "SELECT COUNT() " + + "FROM [SOURCEPROJECT].HydraulicLocationOutputEntity " + + "JOIN [SOURCEPROJECT].HydraulicLocationEntity USING (HydraulicLocationEntityId) " + + "JOIN [SOURCEPROJECT].AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "WHERE NormativeNormType = 1 AND HydraulicLocationOutputType = 2 " + + ") " + + "FROM HydraulicLocationEntity NEWHL " + + "JOIN HydraulicLocationCalculationEntity calc ON calc.HydraulicLocationCalculationEntityId = NEWHL.HydraulicLocationCalculationEntity7Id " + + "JOIN HydraulicLocationOutputEntity NEW USING(HydraulicLocationCalculationEntityId) " + + "JOIN [SOURCEPROJECT].HydraulicLocationOutputEntity OLD ON NEW.HydraulicLocationEntityOutputId = OLD.HydraulicLocationEntityOutputId " + + "WHERE OLD.HydraulicLocationEntityId = NEWHL.HydraulicLocationEntityId;" + + "DETACH DATABASE SOURCEPROJECT;"; + reader.AssertReturnedDataIsValid(validateWaveHeightCalculationsWithLowerLimitNormOutput); + } + + private static void AssertDesignWaterLevelCalculations(MigratedDatabaseReader reader, string sourceFilePath) + { AssertNewHydraulicLocationCalculations(reader, sourceFilePath, 1); string validateDesignWaterLevelCalculationsWithSignalingNorm = $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + "SELECT COUNT() = " + "( " + "SELECT COUNT() " + "FROM [SOURCEPROJECT].HydraulicLocationEntity " + - "JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "JOIN [SOURCEPROJECT].AssessmentSectionEntity USING (AssessmentSectionEntityId) " + "WHERE NormativeNormType = 2 " + ") " + "FROM HydraulicLocationEntity AS NEW " + @@ -286,7 +457,7 @@ "( " + "SELECT COUNT() " + "FROM [SOURCEPROJECT].HydraulicLocationEntity " + - "JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "JOIN [SOURCEPROJECT].AssessmentSectionEntity USING (AssessmentSectionEntityId) " + "WHERE NormativeNormType = 1 " + ") " + "FROM HydraulicLocationEntity AS NEW " + @@ -297,15 +468,18 @@ "DETACH DATABASE SOURCEPROJECT;"; reader.AssertReturnedDataIsValid(validateDesignWaterLevelCalculationsWithLowerLimitNorm); AssertNewHydraulicLocationCalculations(reader, sourceFilePath, 4); + } + private static void AssertWaveHeightCalculations(MigratedDatabaseReader reader, string sourceFilePath) + { AssertNewHydraulicLocationCalculations(reader, sourceFilePath, 5); string validateWaveHeightCalculationsWithSignalingNorm = $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + "SELECT COUNT() = " + "( " + "SELECT COUNT() " + "FROM [SOURCEPROJECT].HydraulicLocationEntity " + - "JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "JOIN [SOURCEPROJECT].AssessmentSectionEntity USING (AssessmentSectionEntityId) " + "WHERE NormativeNormType = 2 " + ") " + "FROM HydraulicLocationEntity AS NEW " + @@ -321,7 +495,7 @@ "( " + "SELECT COUNT() " + "FROM [SOURCEPROJECT].HydraulicLocationEntity " + - "JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "JOIN [SOURCEPROJECT].AssessmentSectionEntity USING (AssessmentSectionEntityId) " + "WHERE NormativeNormType = 1 " + ") " + "FROM HydraulicLocationEntity AS NEW " + @@ -334,50 +508,6 @@ AssertNewHydraulicLocationCalculations(reader, sourceFilePath, 8); } - private static void AssertNewHydraulicLocationCalculations(MigratedDatabaseReader reader, string sourceFilePath, int entityCalculationNumber) - { - string validateNewHydraulicCalculations = - $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + - "SELECT COUNT() = (SELECT COUNT() FROM [SOURCEPROJECT].HydraulicLocationEntity) " + - "FROM HydraulicLocationEntity AS NEW " + - $"INNER JOIN HydraulicLocationCalculationEntity hlce ON NEW.HydraulicLocationCalculationEntity{entityCalculationNumber}Id = hlce.HydraulicLocationCalculationEntityId " + - "WHERE hlce.ShouldIllustrationPointsBeCalculated = 0;" + - "DETACH DATABASE SOURCEPROJECT;"; - reader.AssertReturnedDataIsValid(validateNewHydraulicCalculations); - } - - private static void AssertPipingSoilLayers(MigratedDatabaseReader reader) - { - const string validateBelowPhreaticLevel = - "SELECT COUNT() = 0 " + - "FROM PipingSoilLayerEntity " + - "WHERE [BelowPhreaticLevelMean] < [BelowPhreaticLevelShift] " + - "OR [BelowPhreaticLevelMean] <= 0 " + - "OR [BelowPhreaticLevelDeviation] < 0;"; - reader.AssertReturnedDataIsValid(validateBelowPhreaticLevel); - - const string validateDiameter70 = - "SELECT COUNT() = 0 " + - "FROM PipingSoilLayerEntity " + - "WHERE [DiameterD70Mean] <= 0 " + - "OR [DiameterD70CoefficientOfVariation] < 0;"; - reader.AssertReturnedDataIsValid(validateDiameter70); - - const string validatePermeability = - "SELECT COUNT() = 0 " + - "FROM PipingSoilLayerEntity " + - "WHERE [PermeabilityMean] <= 0 " + - "OR [PermeabilityCoefficientOfVariation] < 0;"; - reader.AssertReturnedDataIsValid(validatePermeability); - } - - private static void AssertStabilityStoneCoverFailureMechanism(MigratedDatabaseReader reader) - { - const string validateStabilityStoneCoverFailureMechanism = - "SELECT COUNT() = 0 " + - "FROM [StabilityStoneCoverFailureMechanismMetaEntity] " + - "WHERE [N] IS NOT 4;"; - reader.AssertReturnedDataIsValid(validateStabilityStoneCoverFailureMechanism); - } + #endregion } } \ No newline at end of file