Index: Riskeer/Migration/src/Riskeer.Migration.Core/EmbeddedResources/Migration_23.1_23.2.sql =================================================================== diff -u -rde30fc56cf98317c9354bc3776df62a3fc1e460a -r92bf6a9f96ae038bc280000fa634a3b8bbe5cb02 --- Riskeer/Migration/src/Riskeer.Migration.Core/EmbeddedResources/Migration_23.1_23.2.sql (.../Migration_23.1_23.2.sql) (revision de30fc56cf98317c9354bc3776df62a3fc1e460a) +++ Riskeer/Migration/src/Riskeer.Migration.Core/EmbeddedResources/Migration_23.1_23.2.sql (.../Migration_23.1_23.2.sql) (revision 92bf6a9f96ae038bc280000fa634a3b8bbe5cb02) @@ -45,38 +45,24 @@ INSERT INTO ClosingStructureEntity SELECT * FROM [SOURCEPROJECT].ClosingStructureEntity; INSERT INTO ClosingStructuresCalculationEntity SELECT * FROM [SOURCEPROJECT].ClosingStructuresCalculationEntity; INSERT INTO ClosingStructuresFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].ClosingStructuresFailureMechanismMetaEntity; -INSERT INTO ClosingStructuresOutputEntity SELECT * FROM [SOURCEPROJECT].ClosingStructuresOutputEntity; INSERT INTO DikeProfileEntity SELECT * FROM [SOURCEPROJECT].DikeProfileEntity; INSERT INTO DuneErosionFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].DuneErosionFailureMechanismMetaEntity; INSERT INTO DuneLocationCalculationEntity SELECT * FROM [SOURCEPROJECT].DuneLocationCalculationEntity; INSERT INTO DuneLocationCalculationForTargetProbabilityCollectionEntity SELECT * FROM [SOURCEPROJECT].DuneLocationCalculationForTargetProbabilityCollectionEntity; -INSERT INTO DuneLocationCalculationOutputEntity SELECT * FROM [SOURCEPROJECT].DuneLocationCalculationOutputEntity; INSERT INTO DuneLocationEntity SELECT * FROM [SOURCEPROJECT].DuneLocationEntity; INSERT INTO FailureMechanismEntity SELECT * FROM [SOURCEPROJECT].FailureMechanismEntity; INSERT INTO FailureMechanismFailureMechanismSectionEntity SELECT * FROM [SOURCEPROJECT].FailureMechanismFailureMechanismSectionEntity; INSERT INTO FailureMechanismSectionEntity SELECT * FROM [SOURCEPROJECT].FailureMechanismSectionEntity; -INSERT INTO FaultTreeIllustrationPointEntity SELECT * FROM [SOURCEPROJECT].FaultTreeIllustrationPointEntity; -INSERT INTO FaultTreeIllustrationPointStochastEntity SELECT * FROM [SOURCEPROJECT].FaultTreeIllustrationPointStochastEntity; -INSERT INTO FaultTreeSubmechanismIllustrationPointEntity SELECT * FROM [SOURCEPROJECT].FaultTreeSubmechanismIllustrationPointEntity; INSERT INTO ForeshoreProfileEntity SELECT * FROM [SOURCEPROJECT].ForeshoreProfileEntity; -INSERT INTO GeneralResultFaultTreeIllustrationPointEntity SELECT * FROM [SOURCEPROJECT].GeneralResultFaultTreeIllustrationPointEntity; -INSERT INTO GeneralResultFaultTreeIllustrationPointStochastEntity SELECT * FROM [SOURCEPROJECT].GeneralResultFaultTreeIllustrationPointStochastEntity; -INSERT INTO GeneralResultSubMechanismIllustrationPointEntity SELECT * FROM [SOURCEPROJECT].GeneralResultSubMechanismIllustrationPointEntity; -INSERT INTO GeneralResultSubMechanismIllustrationPointStochastEntity SELECT * FROM [SOURCEPROJECT].GeneralResultSubMechanismIllustrationPointStochastEntity; INSERT INTO GrassCoverErosionInwardsCalculationEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionInwardsCalculationEntity; -INSERT INTO GrassCoverErosionInwardsDikeHeightOutputEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionInwardsDikeHeightOutputEntity; INSERT INTO GrassCoverErosionInwardsFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionInwardsFailureMechanismMetaEntity; -INSERT INTO GrassCoverErosionInwardsOutputEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionInwardsOutputEntity; -INSERT INTO GrassCoverErosionInwardsOvertoppingRateOutputEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionInwardsOvertoppingRateOutputEntity; INSERT INTO GrassCoverErosionOutwardsFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity; INSERT INTO GrassCoverErosionOutwardsWaveConditionsCalculationEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionOutwardsWaveConditionsCalculationEntity; -INSERT INTO GrassCoverErosionOutwardsWaveConditionsOutputEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionOutwardsWaveConditionsOutputEntity; INSERT INTO GrassCoverSlipOffInwardsFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].GrassCoverSlipOffInwardsFailureMechanismMetaEntity; INSERT INTO GrassCoverSlipOffOutwardsFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].GrassCoverSlipOffOutwardsFailureMechanismMetaEntity; INSERT INTO HeightStructureEntity SELECT * FROM [SOURCEPROJECT].HeightStructureEntity; INSERT INTO HeightStructuresCalculationEntity SELECT * FROM [SOURCEPROJECT].HeightStructuresCalculationEntity; INSERT INTO HeightStructuresFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].HeightStructuresFailureMechanismMetaEntity; -INSERT INTO HeightStructuresOutputEntity SELECT * FROM [SOURCEPROJECT].HeightStructuresOutputEntity; INSERT INTO HydraulicBoundaryDataEntity SELECT * FROM [SOURCEPROJECT].HydraulicBoundaryDataEntity; INSERT INTO HydraulicBoundaryDatabaseEntity SELECT * FROM [SOURCEPROJECT].HydraulicBoundaryDatabaseEntity; INSERT INTO HydraulicLocationCalculationCollectionEntity SELECT * FROM [SOURCEPROJECT].HydraulicLocationCalculationCollectionEntity; @@ -85,8 +71,6 @@ INSERT INTO HydraulicLocationCalculationForTargetProbabilityCollectionEntity SELECT * FROM [SOURCEPROJECT].HydraulicLocationCalculationForTargetProbabilityCollectionEntity; INSERT INTO HydraulicLocationCalculationForTargetProbabilityCollectionHydraulicLocationCalculationEntity SELECT * FROM [SOURCEPROJECT].HydraulicLocationCalculationForTargetProbabilityCollectionHydraulicLocationCalculationEntity; INSERT INTO HydraulicLocationEntity SELECT * FROM [SOURCEPROJECT].HydraulicLocationEntity; -INSERT INTO HydraulicLocationOutputEntity SELECT * FROM [SOURCEPROJECT].HydraulicLocationOutputEntity; -INSERT INTO IllustrationPointResultEntity SELECT * FROM [SOURCEPROJECT].IllustrationPointResultEntity; INSERT INTO MacroStabilityInwardsCalculationEntity SELECT * FROM [SOURCEPROJECT].MacroStabilityInwardsCalculationEntity; INSERT INTO MacroStabilityInwardsCalculationOutputEntity SELECT * FROM [SOURCEPROJECT].MacroStabilityInwardsCalculationOutputEntity; INSERT INTO MacroStabilityInwardsCharacteristicPointEntity SELECT * FROM [SOURCEPROJECT].MacroStabilityInwardsCharacteristicPointEntity; @@ -109,7 +93,6 @@ INSERT INTO PipingStochasticSoilProfileEntity SELECT * FROM [SOURCEPROJECT].PipingStochasticSoilProfileEntity; INSERT INTO PipingStructureFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].PipingStructureFailureMechanismMetaEntity; INSERT INTO ProbabilisticPipingCalculationEntity SELECT * FROM [SOURCEPROJECT].ProbabilisticPipingCalculationEntity; -INSERT INTO ProbabilisticPipingCalculationOutputEntity SELECT * FROM [SOURCEPROJECT].ProbabilisticPipingCalculationOutputEntity; INSERT INTO ProjectEntity SELECT * FROM [SOURCEPROJECT].ProjectEntity; INSERT INTO SemiProbabilisticPipingCalculationEntity SELECT * FROM [SOURCEPROJECT].SemiProbabilisticPipingCalculationEntity; INSERT INTO SemiProbabilisticPipingCalculationOutputEntity SELECT * FROM [SOURCEPROJECT].SemiProbabilisticPipingCalculationOutputEntity; @@ -118,21 +101,14 @@ INSERT INTO StabilityPointStructureEntity SELECT * FROM [SOURCEPROJECT].StabilityPointStructureEntity; INSERT INTO StabilityPointStructuresCalculationEntity SELECT * FROM [SOURCEPROJECT].StabilityPointStructuresCalculationEntity; INSERT INTO StabilityPointStructuresFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].StabilityPointStructuresFailureMechanismMetaEntity; -INSERT INTO StabilityPointStructuresOutputEntity SELECT * FROM [SOURCEPROJECT].StabilityPointStructuresOutputEntity; INSERT INTO StabilityStoneCoverFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].StabilityStoneCoverFailureMechanismMetaEntity; INSERT INTO StabilityStoneCoverWaveConditionsCalculationEntity SELECT * FROM [SOURCEPROJECT].StabilityStoneCoverWaveConditionsCalculationEntity; -INSERT INTO StabilityStoneCoverWaveConditionsOutputEntity SELECT * FROM [SOURCEPROJECT].StabilityStoneCoverWaveConditionsOutputEntity; INSERT INTO StochastEntity SELECT * FROM [SOURCEPROJECT].StochastEntity; INSERT INTO StochasticSoilModelEntity SELECT * FROM [SOURCEPROJECT].StochasticSoilModelEntity; -INSERT INTO SubMechanismIllustrationPointEntity SELECT * FROM [SOURCEPROJECT].SubMechanismIllustrationPointEntity; -INSERT INTO SubMechanismIllustrationPointStochastEntity SELECT * FROM [SOURCEPROJECT].SubMechanismIllustrationPointStochastEntity; INSERT INTO SurfaceLineEntity SELECT * FROM [SOURCEPROJECT].SurfaceLineEntity; -INSERT INTO TopLevelFaultTreeIllustrationPointEntity SELECT * FROM [SOURCEPROJECT].TopLevelFaultTreeIllustrationPointEntity; -INSERT INTO TopLevelSubMechanismIllustrationPointEntity SELECT * FROM [SOURCEPROJECT].TopLevelSubMechanismIllustrationPointEntity; INSERT INTO WaterPressureAsphaltCoverFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].WaterPressureAsphaltCoverFailureMechanismMetaEntity; INSERT INTO WaveImpactAsphaltCoverFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].WaveImpactAsphaltCoverFailureMechanismMetaEntity; INSERT INTO WaveImpactAsphaltCoverWaveConditionsCalculationEntity SELECT * FROM [SOURCEPROJECT].WaveImpactAsphaltCoverWaveConditionsCalculationEntity; -INSERT INTO WaveImpactAsphaltCoverWaveConditionsOutputEntity SELECT * FROM [SOURCEPROJECT].WaveImpactAsphaltCoverWaveConditionsOutputEntity; INSERT INTO VersionEntity ( [VersionId], @@ -144,8 +120,38 @@ [Timestamp], [FingerPrint] FROM [SOURCEPROJECT].VersionEntity; - + /* +Outputs that used HydraRing are not migrated +*/ +-- ClosingStructuresOutputEntity +-- DuneLocationOutputEntity +-- GrassCoverErosionInwardsDikeHeightOutputEntity +-- GrassCoverErosionInwardsOutputEntity +-- GrassCoverErosionInwardsOvertoppingRateOutputEntity +-- GrassCoverErosionOutwardsWaveConditionsOutputEntity +-- HeightStructuresOutputEntity +-- HydraulicLocationOutputEntity +-- SemiProbabilisticPipingCalculationOutputEntity where UseManualAssessmentLevel is 0 +-- ProbabilisticPipingCalculationOutputEntity +-- StabilityPointStructuresOutputEntity +-- StabilityStoneCoverWaveConditionsOutputEntity +-- WaveImpactAsphaltCoverWaveConditionsOutputEntity +-- FaultTreeIllustrationPointEntity +-- FaultTreeIllustrationPointStochastEntity +-- FaultTreeSubMechanismIllustrationPointEntity +-- GeneralResultFaultTreeIllustrationPointEntity +-- GeneralResultFaultTreeIllustrationPointStochastEntity +-- GeneralResultSubMechanismIllustrationPointEntity +-- GeneralResultSubMechanismIllustrationPointStochastEntity +-- IllustrationPointResultEntity +-- SubMechanismIllustrationPointEntity +-- SubMechanismIllustrationPointStochastEntity +-- TopLevelFaultTreeIllustrationPointEntity +-- TopLevelSubMechanismIllustrationPointEntity +-- MacroStabilityInwardsCalculationOutputEntity where UseManualAssessmentLevel is 0 + +/* Write migration logging */ ATTACH DATABASE "{1}" AS LOGDATABASE; @@ -156,10 +162,230 @@ 'FromVersion' VARCHAR(20) NOT NULL, 'ToVersion' VARCHAR(20) NOT NULL, 'LogMessage' TEXT NOT NULL); - + +CREATE TEMP TABLE TempLogOutputDeleted +( + 'NrDeleted' INTEGER NOT NULL +); + +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].HydraulicLocationOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].ClosingStructuresOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].DuneLocationCalculationOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionInwardsDikeHeightOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionInwardsOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionInwardsOvertoppingRateOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionOutwardsWaveConditionsOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].HeightStructuresOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].StabilityPointStructuresOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].StabilityStoneCoverWaveConditionsOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].WaveImpactAsphaltCoverWaveConditionsOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].ProbabilisticPipingCalculationOutputEntity; +INSERT INTO TempLogOutputDeleted +SELECT COUNT() +FROM [SOURCEPROJECT].SemiProbabilisticPipingCalculationOutputEntity + JOIN [SOURCEPROJECT].SemiProbabilisticPipingCalculationEntity USING(SemiProbabilisticPipingCalculationEntityId) +WHERE UseAssessmentLevelManualInput = 0; +INSERT INTO TempLogOutputDeleted +SELECT COUNT() +FROM [SOURCEPROJECT].MacroStabilityInwardsCalculationOutputEntity + JOIN [SOURCEPROJECT].MacroStabilityInwardsCalculationEntity USING(MacroStabilityInwardsCalculationEntityId) +WHERE UseAssessmentLevelManualInput = 0; + +CREATE TEMP TABLE TempLogOutputRemaining +( + 'NrRemaining' INTEGER NOT NULL +); + +INSERT INTO TempLogOutputRemaining +SELECT COUNT() + + ( + SELECT COUNT() + FROM MacroStabilityInwardsCalculationOutputEntity + JOIN MacroStabilityInwardsCalculationEntity USING(MacroStabilityInwardsCalculationEntityId) + WHERE UseAssessmentLevelManualInput = 1 + ) +FROM SemiProbabilisticPipingCalculationOutputEntity + JOIN SemiProbabilisticPipingCalculationEntity USING(SemiProbabilisticPipingCalculationEntityId) +WHERE UseAssessmentLevelManualInput = 1; + INSERT INTO [LOGDATABASE].MigrationLogEntity ( [FromVersion], [ToVersion], +[LogMessage]) +SELECT + "22.1", + "23.1", + CASE + WHEN [NrRemaining] > 0 + THEN "* Alle berekende resultaten zijn verwijderd, behalve die van het faalmechanisme 'Piping' en/of 'Macrostabiliteit binnenwaarts' waarbij de waterstand handmatig is ingevuld." + ELSE "* Alle berekende resultaten zijn verwijderd." + END +FROM TempLogOutputDeleted + LEFT JOIN TempLogOutputRemaining +WHERE [NrDeleted] > 0 + LIMIT 1; + +CREATE TEMP TABLE TempFailureMechanisms +( + 'FailureMechanismType' INTEGER NOT NULL, + 'FailureMechanismName' VARCHAR(255) NOT NULL +); + +INSERT INTO TempFailureMechanisms VALUES (1, 'Piping'); +INSERT INTO TempFailureMechanisms VALUES (2, 'Macrostabiliteit binnenwaarts'); +INSERT INTO TempFailureMechanisms VALUES (3, 'Golfklappen op asfaltbekleding'); +INSERT INTO TempFailureMechanisms VALUES (4, 'Grasbekleding erosie buitentalud'); +INSERT INTO TempFailureMechanisms VALUES (5, 'Grasbekleding afschuiven buitentalud'); +INSERT INTO TempFailureMechanisms VALUES (6, 'Grasbekleding erosie kruin en binnentalud'); +INSERT INTO TempFailureMechanisms VALUES (7, 'Stabiliteit steenzetting'); +INSERT INTO TempFailureMechanisms VALUES (8, 'Duinafslag'); +INSERT INTO TempFailureMechanisms VALUES (9, 'Hoogte kunstwerk'); +INSERT INTO TempFailureMechanisms VALUES (10, 'Betrouwbaarheid sluiting kunstwerk'); +INSERT INTO TempFailureMechanisms VALUES (11, 'Piping bij kunstwerk'); +INSERT INTO TempFailureMechanisms VALUES (12, 'Sterkte en stabiliteit puntconstructies'); +INSERT INTO TempFailureMechanisms VALUES (13, 'Microstabiliteit'); +INSERT INTO TempFailureMechanisms VALUES (14, 'Wateroverdruk bij asfaltbekleding'); +INSERT INTO TempFailureMechanisms VALUES (15, 'Grasbekleding afschuiven binnentalud'); + +CREATE TEMP TABLE TempAssessmentSectionFailureMechanism ( + [AssessmentSectionId], + [AssessmentSectionName], + [FailureMechanismId], + [FailureMechanismName] +); + +INSERT INTO TempAssessmentSectionFailureMechanism +SELECT + [AssessmentSectionEntityId], + [Name], + [FailureMechanismEntityId], + [FailureMechanismName] +FROM AssessmentSectionEntity + JOIN FailureMechanismEntity USING (AssessmentSectionEntityId) + JOIN TempFailureMechanisms USING (FailureMechanismType); + +CREATE TEMP TABLE TempChanges ( + [AssessmentSectionId], + [AssessmentSectionName], + [FailureMechanismId], + [FailureMechanismName], + [msg] +); + +INSERT INTO [LOGDATABASE].MigrationLogEntity ( + [FromVersion], + [ToVersion], +[LogMessage] +) WITH RECURSIVE FailureMechanismMessages ( + [FailureMechanismId], + [FailureMechanismName], + [AssessmentSectionId], + [AssessmentSectionName], + [msg], + [level] + ) AS ( + SELECT DISTINCT + [FailureMechanismId], + [FailureMechanismName], + [AssessmentSectionId], + [AssessmentSectionName], + NULL, + 1 + FROM TempChanges + UNION + SELECT + [FailureMechanismId], + NULL, + [AssessmentSectionId], + [AssessmentSectionName], + [msg], + 2 + FROM TempChanges + WHERE TempChanges.[FailureMechanismId] IS [FailureMechanismId] + ORDER BY [AssessmentSectionId], [FailureMechanismId] + ), + AssessmentSectionFailureMechanismMessages ( + [AssessmentSectionId], + [AssessmentSectionName], + [IsAssessmentSectionHeader], + [FailureMechanismId], + [FailureMechanismName], + [msg], + [level], + [Order] + ) AS ( + SELECT DISTINCT + [AssessmentSectionId], + [AssessmentSectionName], + 1, + NULL, + NULL, + NULL, + 1, + 0 + FROM ( + SELECT + [AssessmentSectionId], + [AssessmentSectionName] + FROM FailureMechanismMessages + WHERE [AssessmentSectionId] IS NOT NULL + ) + UNION + SELECT * + FROM ( + SELECT + [AssessmentSectionId], + NULL, + 0 AS [IsAssessmentSectionHeader], + fmm.[FailureMechanismId] AS [FailureMechanismId], + fmm.[FailureMechanismName], + [msg], + fmm.[level] AS [level], + 1 AS [Order] + FROM FailureMechanismMessages AS fmm + WHERE fmm.[AssessmentSectionId] IS [AssessmentSectionId] + ) + ORDER BY + [AssessmentSectionId], + [FailureMechanismId], + [level], + [IsAssessmentSectionHeader] DESC, + [Order] + ) +SELECT + "23.1", + "23.2", + CASE + WHEN [AssessmentSectionName] IS NOT NULL + THEN + CASE + WHEN [IsAssessmentSectionHeader] IS 1 + THEN + "* Traject: '" || [AssessmentSectionName] || "'" + ELSE + " + " || [msg] +END +ELSE + CASE + WHEN [FailureMechanismName] IS NOT NULL + THEN + " + Faalmechanisme: '" || [FailureMechanismName] || "'" + ELSE + " - " || [msg] +END +END +FROM AssessmentSectionFailureMechanismMessages; + +DROP TABLE TempFailureMechanisms; +DROP TABLE TempAssessmentSectionFailureMechanism; +DROP TABLE TempChanges; + +DROP TABLE TempLogOutputDeleted; +DROP TABLE TempLogOutputRemaining; + +INSERT INTO [LOGDATABASE].MigrationLogEntity ( + [FromVersion], + [ToVersion], [LogMessage]) VALUES ( "23.1",