Index: Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql =================================================================== diff -u -r6fc06c18b0f97142ef561664ae361aa42b35b60b -r1007f8dac7b8420a93101ff3470bf28b07a37fe7 --- Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql (.../Migration_17.3_18.1.sql) (revision 6fc06c18b0f97142ef561664ae361aa42b35b60b) +++ Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql (.../Migration_17.3_18.1.sql) (revision 1007f8dac7b8420a93101ff3470bf28b07a37fe7) @@ -56,7 +56,6 @@ NULL FROM [SOURCEPROJECT].ClosingStructuresSectionResultEntity; INSERT INTO DikeProfileEntity SELECT * FROM [SOURCEPROJECT].DikeProfileEntity; -INSERT INTO DuneErosionFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].DuneErosionFailureMechanismMetaEntity; INSERT INTO DuneErosionSectionResultEntity ( [DuneErosionSectionResultEntityId], [FailureMechanismSectionEntityId], @@ -1286,12 +1285,156 @@ JOIN FailureMechanismEntity USING(CalculationGroupEntityId) JOIN AssessmentSectionEntity USING(AssessmentSectionEntityId); +-- Start Dune Location migration +/* +Note, the following conventions are used for the calculation type on AssessmentSectionEntity: +- The dune calculations for the mechanism specific factorized signaling norm = 0. +- The dune calculations for the mechanism specific signaling norm = 1. +- The dune calculations for the mechanism specific lower limit norm = 2. +- The dune calculations for the lower limit norm = 3. +- The dune calculations for the factorized lower limit norm = 4. +*/ +CREATE TEMP TABLE TempDuneCalculationTypes ( 'CalculationType' TINYINT (1) NOT NULL); +INSERT INTO TempDuneCalculationTypes VALUES (0); +INSERT INTO TempDuneCalculationTypes VALUES (1); +INSERT INTO TempDuneCalculationTypes VALUES (2); +INSERT INTO TempDuneCalculationTypes VALUES (3); +INSERT INTO TempDuneCalculationTypes VALUES (4); + +-- Create the calculation collections +CREATE TEMP TABLE TempDuneLocationCalculationCollectionEntity +( + 'DuneLocationCalculationCollectionEntityId' INTEGER NOT NULL, + 'DuneErosionFailureMechanismMetaEntityId' INTEGER NOT NULL, + 'CalculationType' TINYINT (1) NOT NULL, + PRIMARY KEY + ( + 'DuneLocationCalculationCollectionEntityId' AUTOINCREMENT + ) +); + +INSERT INTO TempDuneLocationCalculationCollectionEntity ( + [DuneErosionFailureMechanismMetaEntityId], + [CalculationType]) +SELECT + [DuneErosionFailureMechanismMetaEntityId], + [CalculationType] +FROM [SOURCEPROJECT].DuneErosionFailureMechanismMetaEntity +JOIN ( + SELECT * + FROM TempDuneCalculationTypes +); + +INSERT INTO DuneLocationCalculationCollectionEntity( + [DuneLocationCalculationCollectionEntityId]) +SELECT + [DuneLocationCalculationCollectionEntityId] +FROM TempDuneLocationCalculationCollectionEntity; + +-- Create the calculation entities +CREATE TEMP TABLE TempDuneLocationCalculationEntity +( + 'DuneLocationCalculationEntityId' INTEGER NOT NULL, + 'DuneLocationEntityId' INTEGER NOT NULL, + 'DuneErosionFailureMechanismMetaEntityId' INTEGER, + 'CalculationType' TINYINT (1) NOT NULL, + PRIMARY KEY + ( + 'DuneLocationCalculationEntityId' AUTOINCREMENT + ) +); + +INSERT INTO TempDuneLocationCalculationEntity( + [DuneLocationEntityId], + [DuneErosionFailureMechanismMetaEntityId], + [CalculationType]) +SELECT + [DuneLocationEntityId], + [DuneErosionFailureMechanismMetaEntityId], + [CalculationType] +FROM [SourceProject].FailureMechanismEntity +JOIN [SourceProject].DuneLocationEntity USING(FailureMechanismEntityId) +JOIN [SourceProject].DuneErosionFailureMechanismMetaEntity USING(FailureMechanismEntityId) +JOIN ( + SELECT * + FROM TempDuneCalculationTypes +); + +INSERT INTO DuneLocationCalculationEntity( + [DuneLocationCalculationEntityId], + [DuneLocationEntityId], + [DuneLocationCalculationCollectionEntityId]) +SELECT + [DuneLocationCalculationEntityId], + [DuneLocationEntityId], + [DuneLocationCalculationCollectionEntityId] +FROM TempDuneLocationCalculationCollectionEntity collections +JOIN TempDuneLocationCalculationEntity calculations +ON collections.CalculationType = calculations.CalculationType +AND collections.DuneErosionFailureMechanismMetaEntityId = calculations.DuneErosionFailureMechanismMetaEntityId; + +-- Migrate the dune erosion failure mechanism meta entity +INSERT INTO DuneErosionFailureMechanismMetaEntity ( + DuneErosionFailureMechanismMetaEntityId, + FailureMechanismEntityId, + DuneLocationCalculationCollectionEntity1Id, + DuneLocationCalculationCollectionEntity2Id, + DuneLocationCalculationCollectionEntity3Id, + DuneLocationCalculationCollectionEntity4Id, + DuneLocationCalculationCollectionEntity5Id, + N +) +SELECT + [DuneErosionFailureMechanismMetaEntityId], + [FailureMechanismEntityId], + [CalculationsForMechanismSpecificFactorizedSignalingNorm], + [CalculationsForMechanismSpecificSignalingNorm], + [CalculationsForMechanismSpecificLowerLimitNorm], + [CalculationsForLowerLimitNorm], + [CalculationsForFactorizedLowerLimitNorm], + [N] +FROM [SOURCEPROJECT].DuneErosionFailureMechanismMetaEntity +JOIN ( + SELECT + DuneLocationCalculationCollectionEntityId AS CalculationsForMechanismSpecificFactorizedSignalingNorm, + DuneErosionFailureMechanismMetaEntityId + FROM TempDuneLocationCalculationCollectionEntity WHERE CalculationType = 0 +) USING (DuneErosionFailureMechanismMetaEntityId) +JOIN ( + SELECT + DuneLocationCalculationCollectionEntityId AS CalculationsForMechanismSpecificSignalingNorm, + DuneErosionFailureMechanismMetaEntityId + FROM TempDuneLocationCalculationCollectionEntity WHERE CalculationType = 1 +) USING (DuneErosionFailureMechanismMetaEntityId) +JOIN ( + SELECT + DuneLocationCalculationCollectionEntityId AS CalculationsForMechanismSpecificLowerLimitNorm, + DuneErosionFailureMechanismMetaEntityId + FROM TempDuneLocationCalculationCollectionEntity WHERE CalculationType = 2 +) USING (DuneErosionFailureMechanismMetaEntityId) +JOIN ( + SELECT + DuneLocationCalculationCollectionEntityId AS CalculationsForLowerLimitNorm, + DuneErosionFailureMechanismMetaEntityId + FROM TempDuneLocationCalculationCollectionEntity WHERE CalculationType = 3 +) USING (DuneErosionFailureMechanismMetaEntityId) +JOIN ( + SELECT + DuneLocationCalculationCollectionEntityId AS CalculationsForFactorizedLowerLimitNorm, + DuneErosionFailureMechanismMetaEntityId + FROM TempDuneLocationCalculationCollectionEntity WHERE CalculationType = 4 +) USING (DuneErosionFailureMechanismMetaEntityId); + -- Cleanup DROP TABLE TempCalculationTypes; DROP TABLE TempHydraulicLocationCalculationEntity; DROP TABLE TempHydraulicLocationCalculationCollectionEntity; DROP TABLE TempGrassCoverErosionOutwardsHydraulicBoundaryLocationLookupTable; +DROP TABLE TempDuneCalculationTypes; +DROP TABLE TempDuneLocationCalculationCollectionEntity; +DROP TABLE TempDuneLocationCalculationEntity; + /* Write migration logging */ Index: Application/Ringtoets/test/Application.Ringtoets.Migration.Core.Test/test-data/MigrationTestProject173.rtd =================================================================== diff -u -r6fc06c18b0f97142ef561664ae361aa42b35b60b -r1007f8dac7b8420a93101ff3470bf28b07a37fe7 Binary files differ Index: Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs =================================================================== diff -u -r6fc06c18b0f97142ef561664ae361aa42b35b60b -r1007f8dac7b8420a93101ff3470bf28b07a37fe7 --- Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs (.../MigrationTo181IntegrationTest.cs) (revision 6fc06c18b0f97142ef561664ae361aa42b35b60b) +++ Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs (.../MigrationTo181IntegrationTest.cs) (revision 1007f8dac7b8420a93101ff3470bf28b07a37fe7) @@ -98,6 +98,9 @@ AssertGrassCoverErosionOutwardsFailureMechanismMetaEntity(reader, sourceFilePath); + AssertDuneErosionFailureMechanismMetaEntity(reader, sourceFilePath); + AssertDuneLocations(reader, sourceFilePath); + AssertHeightStructuresSectionResultEntity(reader, sourceFilePath); AssertClosingStructuresSectionResultEntity(reader, sourceFilePath); AssertStabilityPointStructuresSectionResultEntity(reader, sourceFilePath); @@ -707,6 +710,22 @@ AssertWaveHeightCalculationEntitiesOnGrassCoverErosionOutwardsFailureMechanism(reader, queryGenerator); } + private static void AssertDuneLocations(MigratedDatabaseReader reader, string sourceFilePath) + { + var queryGenerator = new DuneErosionFailureMechanismValidationQueryGenerator(sourceFilePath); + + reader.AssertReturnedDataIsValid(queryGenerator.GetDuneLocationCalculationsCountValidationQuery( + DuneErosionFailureMechanismValidationQueryGenerator.CalculationType.CalculationsForMechanismSpecificFactorizedSignalingNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetDuneLocationCalculationsCountValidationQuery( + DuneErosionFailureMechanismValidationQueryGenerator.CalculationType.CalculationsForMechanismSpecificSignalingNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetDuneLocationCalculationsCountValidationQuery( + DuneErosionFailureMechanismValidationQueryGenerator.CalculationType.CalculationsForMechanismSpecificLowerLimitNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetDuneLocationCalculationsCountValidationQuery( + DuneErosionFailureMechanismValidationQueryGenerator.CalculationType.CalculationsForLowerLimitNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetDuneLocationCalculationsCountValidationQuery( + DuneErosionFailureMechanismValidationQueryGenerator.CalculationType.CalculationsForLowerFactorizedLimitNorm)); + } + private static void AssertGrassCoverErosionOutwardsFailureMechanismMetaEntity(MigratedDatabaseReader reader, string sourceFilePath) { string validateMetaEntity = @@ -723,6 +742,21 @@ reader.AssertReturnedDataIsValid(validateMetaEntity); } + private static void AssertDuneErosionFailureMechanismMetaEntity(MigratedDatabaseReader reader, string sourceFilePath) + { + string validateMetaEntity = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT " + + "COUNT() = (SELECT COUNT() FROM [SOURCEPROJECT].DuneErosionFailureMechanismMetaEntity) " + + "FROM DuneErosionFailureMechanismMetaEntity NEW " + + "JOIN [SOURCEPROJECT].DuneErosionFailureMechanismMetaEntity OLD USING(DuneErosionFailureMechanismMetaEntityId) " + + "WHERE new.FailureMechanismEntityId = OLD.FailureMechanismEntityId " + + "AND NEW.N = OLD.N; " + + "DETACH DATABASE SOURCEPROJECT;"; + + reader.AssertReturnedDataIsValid(validateMetaEntity); + } + #region Failure Mechanism Section Result Entities private static void AssertHeightStructuresSectionResultEntity(MigratedDatabaseReader reader, string sourceFilePath) @@ -1590,10 +1624,10 @@ } /// - /// Generates a query to validate the number of created hydraulic boundary location calculations per failure mechanism section. + /// Generates a query to validate the number of created hydraulic boundary location calculations per failure mechanism. /// /// The type of calculation that should be validated. - /// The query to validate the number of hydraulic boundary location calculations per assessment section. + /// The query to validate the number of hydraulic boundary location calculations per failure mechanism. public string GetHydraulicBoundaryLocationCalculationsPerFailureMechanismCountValidationQuery(CalculationType calculationType) { return $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + @@ -1873,6 +1907,101 @@ #endregion + #region Dune Locations + + /// + /// Class to generate queries which can be used if the dune locations are correctly migrated. + /// + private class DuneErosionFailureMechanismValidationQueryGenerator + { + /// + /// Enum to indicate the hydraulic location calculation type. + /// + public enum CalculationType + { + /// + /// Represents the calculations for the mechanism specific factorized signaling norm. + /// + CalculationsForMechanismSpecificFactorizedSignalingNorm = 1, + + /// + /// Represents the calculations for the mechanism specific signaling norm. + /// + CalculationsForMechanismSpecificSignalingNorm = 2, + + /// + /// Represents the calculations for the mechanism specific lower limit norm. + /// + CalculationsForMechanismSpecificLowerLimitNorm = 3, + + /// + /// Represents the calculations for the lower limit norm. + /// + CalculationsForLowerLimitNorm = 4, + + /// + /// Represents the calculations for the factorized lower limit norm. + /// + CalculationsForLowerFactorizedLimitNorm = 5, + } + + private readonly string sourceFilePath; + + /// + /// Creates a new instance of . + /// + /// The file path of the original database. + /// Thrown when + /// is null or empty. + public DuneErosionFailureMechanismValidationQueryGenerator(string sourceFilePath) + { + if (string.IsNullOrWhiteSpace(sourceFilePath)) + { + throw new ArgumentException(@"Sourcefile path cannot be null or empty", + nameof(sourceFilePath)); + } + + this.sourceFilePath = sourceFilePath; + } + + /// + /// Generates a query to validate the number of created dune location calculations per failure mechanism. + /// + /// The type of calculation that should be validated. + /// The query to validate the number of dune location calculations per calculation type. + public string GetDuneLocationCalculationsCountValidationQuery(CalculationType calculationType) + { + return $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT " + + "COUNT() = 0 " + + "FROM " + + "( " + + "SELECT " + + "[DuneErosionFailureMechanismMetaEntityId], " + + "COUNT() as NewCount, " + + "OldCount " + + "FROM DuneErosionFailureMechanismMetaEntity fme " + + "JOIN DuneLocationCalculationCollectionEntity " + + $"ON DuneLocationCalculationCollectionEntityId = fme.DuneLocationCalculationCollectionEntity{(int)calculationType}Id " + + "JOIN DuneLocationCalculationEntity USING (DuneLocationCalculationCollectionEntityId) " + + "LEFT JOIN " + + "( " + + "SELECT " + + "[DuneErosionFailureMechanismMetaEntityId], " + + "COUNT() as OldCount " + + "FROM [SourceProject].DuneErosionFailureMechanismMetaEntity " + + "JOIN [SourceProject].DuneLocationEntity USING(FailureMechanismEntityId) " + + "GROUP BY DuneErosionFailureMechanismMetaEntityId " + + ") USING(DuneErosionFailureMechanismMetaEntityId) " + + "GROUP BY DuneErosionFailureMechanismMetaEntityId " + + ") " + + "WHERE OldCount IS NOT NewCount; " + + "DETACH DATABASE SOURCEPROJECT;"; + } + } + + #endregion + #region Migrated Wave Condition Calculations private static void AssertWaveConditionsCalculations(MigratedDatabaseReader reader, string sourceFilePath)