Index: Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.1_17.2.sql =================================================================== diff -u -r8be0baccddc62b1efd47bc2469c597c3e480e6b8 -r4e99812f9957b2b7d9299207726ec4ccc70d157c --- Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.1_17.2.sql (.../Migration_17.1_17.2.sql) (revision 8be0baccddc62b1efd47bc2469c597c3e480e6b8) +++ Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.1_17.2.sql (.../Migration_17.1_17.2.sql) (revision 4e99812f9957b2b7d9299207726ec4ccc70d157c) @@ -493,108 +493,198 @@ [LogMessage]) VALUES ("17.1", "17.2", "Gevolgen van de migratie van versie 17.1 naar versie 17.2:"); -CREATE TEMP TABLE FailureMechanisms ('FailureMechanismType' INTEGER NOT NULL, 'FailureMechanismName' VARCHAR(255) NOT NULL); -INSERT INTO FailureMechanisms VALUES (1, 'Piping'); -INSERT INTO FailureMechanisms VALUES (2, 'Macrostabiliteit binnenwaarts'); -INSERT INTO FailureMechanisms VALUES (3, 'Golfklappen op asfaltbekleding'); -INSERT INTO FailureMechanisms VALUES (4, 'Grasbekleding erosie buitentalud'); -INSERT INTO FailureMechanisms VALUES (5, 'Grasbekleding afschuiven buitentalud'); -INSERT INTO FailureMechanisms VALUES (6, 'Grasbekleding erosie kruin en binnentalud'); -INSERT INTO FailureMechanisms VALUES (7, 'Stabiliteit steenzetting'); -INSERT INTO FailureMechanisms VALUES (8, 'Duinafslag'); -INSERT INTO FailureMechanisms VALUES (9, 'Hoogte kunstwerk'); -INSERT INTO FailureMechanisms VALUES (10, 'Betrouwbaarheid sluiting kunstwerk'); -INSERT INTO FailureMechanisms VALUES (11, 'Piping bij kunstwerk'); -INSERT INTO FailureMechanisms VALUES (12, 'Sterkte en stabiliteit puntconstructies'); -INSERT INTO FailureMechanisms VALUES (13, 'Macrostabiliteit buitenwaarts'); -INSERT INTO FailureMechanisms VALUES (14, 'Microstabiliteit'); -INSERT INTO FailureMechanisms VALUES (15, 'Wateroverdruk bij asfaltbekleding'); -INSERT INTO FailureMechanisms VALUES (16, 'Grasbekleding afschuiven binnentalud'); -INSERT INTO FailureMechanisms VALUES (17, 'Sterkte en stabiliteit langsconstructies'); -INSERT INTO FailureMechanisms VALUES (18, 'Technische innovaties'); +CREATE TEMP TABLE TempFailureMechanisms +( + 'FailureMechanismType' INTEGER NOT NULL, + 'FailureMechanismName' VARCHAR(255) NOT NULL +); -CREATE TEMP TABLE AssessmentSectionFailureMechanism (AssessmentSectionId, AssessmentSectionName, FailureMechanismId, FailureMechanismName); -INSERT INTO AssessmentSectionFailureMechanism - SELECT AssessmentSectionEntityId, Name, FailureMechanismEntityId, FailureMechanismName - FROM AssessmentSectionEntity - JOIN FailureMechanismEntity USING(AssessmentSectionEntityId) - JOIN FailureMechanisms USING(FailureMechanismType); +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, 'Macrostabiliteit buitenwaarts'); +INSERT INTO TempFailureMechanisms VALUES (14, 'Microstabiliteit'); +INSERT INTO TempFailureMechanisms VALUES (15, 'Wateroverdruk bij asfaltbekleding'); +INSERT INTO TempFailureMechanisms VALUES (16, 'Grasbekleding afschuiven binnentalud'); +INSERT INTO TempFailureMechanisms VALUES (17, 'Sterkte en stabiliteit langsconstructies'); +INSERT INTO TempFailureMechanisms VALUES (18, 'Technische innovaties'); -CREATE TEMP TABLE Changes (AssessmentSectionId, AssessmentSectionName, FailureMechanismId, FailureMechanismName, msg); +CREATE TEMP TABLE TempAssessmentSectionFailureMechanism +( + [AssessmentSectionId], + [AssessmentSectionName], + [FailureMechanismId], + [FailureMechanismName] +); -INSERT INTO Changes - SELECT asfm.AssessmentSectionId, asfm.AssessmentSectionName, asfm.FailureMechanismId, asfm.FailureMechanismName, - "Het ID van voorlandprofiel '" || source.Id || "' is veranderd naar '" || fp.Id || "'." - FROM ForeshoreProfileEntity as fp - JOIN [SOURCEPROJECT].ForeshoreProfileEntity as source ON fp.rowid = source.rowid - JOIN AssessmentSectionFailureMechanism as asfm ON asfm.FailureMechanismId = fp.FailureMechanismEntityId - WHERE source.Id IS NOT fp.Id; +INSERT INTO TempAssessmentSectionFailureMechanism +SELECT + [AssessmentSectionEntityId], + [Name], + [FailureMechanismEntityId], + [FailureMechanismName] + FROM AssessmentSectionEntity + JOIN FailureMechanismEntity USING (AssessmentSectionEntityId) + JOIN TempFailureMechanisms USING (FailureMechanismType); -INSERT INTO Changes - SELECT asfm.AssessmentSectionId, asfm.AssessmentSectionName, asfm.FailureMechanismId, asfm.FailureMechanismName, - "Het ID van kunstwerk '" || source.Id || "' is veranderd naar '" || hs.Id || "'." - FROM HeightStructureEntity as hs - JOIN [SOURCEPROJECT].HeightStructureEntity as source ON hs.rowid = source.rowid - JOIN AssessmentSectionFailureMechanism as asfm ON asfm.FailureMechanismId = hs.FailureMechanismEntityId - WHERE source.Id IS NOT hs.Id; +CREATE TEMP TABLE TempChanges +( + [AssessmentSectionId], + [AssessmentSectionName], + [FailureMechanismId], + [FailureMechanismName], + [msg] +); -INSERT INTO Changes - SELECT asfm.AssessmentSectionId, asfm.AssessmentSectionName, asfm.FailureMechanismId, asfm.FailureMechanismName, - "Het ID van kunstwerk '" || source.Id || "' is veranderd naar '" || cs.Id || "'." - FROM ClosingStructureEntity as cs - JOIN [SOURCEPROJECT].ClosingStructureEntity as source ON cs.rowid = source.rowid - JOIN AssessmentSectionFailureMechanism as asfm ON asfm.FailureMechanismId = cs.FailureMechanismEntityId - WHERE source.Id IS NOT cs.Id; +INSERT INTO TempChanges +SELECT + asfm.[AssessmentSectionId], + asfm.[AssessmentSectionName], + asfm.[FailureMechanismId], + asfm.[FailureMechanismName], + "Het ID van voorlandprofiel '" || source.[Id] || "' is veranderd naar '" || fp.[Id] || "'." + FROM ForeshoreProfileEntity AS fp + JOIN [SOURCEPROJECT].ForeshoreProfileEntity AS source ON fp.[rowid] = source.[rowid] + JOIN TempAssessmentSectionFailureMechanism AS asfm ON asfm.[FailureMechanismId] = fp.[FailureMechanismEntityId] + WHERE source.[Id] IS NOT fp.[Id]; -INSERT INTO Changes - SELECT asfm.AssessmentSectionId, asfm.AssessmentSectionName, asfm.FailureMechanismId, asfm.FailureMechanismName, - "Het ID van kunstwerk '" || source.Id || "' is veranderd naar '" || sps.Id || "'." - FROM StabilityPointStructureEntity as sps - JOIN [SOURCEPROJECT].StabilityPointStructureEntity as source ON sps.rowid = source.rowid - JOIN AssessmentSectionFailureMechanism as asfm ON asfm.FailureMechanismId = sps.FailureMechanismEntityId - WHERE source.Id IS NOT sps.Id; +INSERT INTO TempChanges +SELECT + asfm.[AssessmentSectionId], + asfm.[AssessmentSectionName], + asfm.[FailureMechanismId], + asfm.[FailureMechanismName], + "Het ID van kunstwerk '" || source.[Id] || "' is veranderd naar '" || hs.[Id] || "'." + FROM HeightStructureEntity AS hs + JOIN [SOURCEPROJECT].HeightStructureEntity AS source ON hs.[rowid] = source.[rowid] + JOIN TempAssessmentSectionFailureMechanism AS asfm ON asfm.[FailureMechanismId] = hs.[FailureMechanismEntityId] + WHERE source.[Id] IS NOT hs.[Id]; -INSERT INTO [LOGDATABASE].MigrationLogEntity( - [FromVersion], - [ToVersion], - [LogMessage]) +INSERT INTO TempChanges +SELECT + asfm.[AssessmentSectionId], + asfm.[AssessmentSectionName], + asfm.[FailureMechanismId], + asfm.[FailureMechanismName], + "Het ID van kunstwerk '" || source.[Id] || "' is veranderd naar '" || cs.[Id] || "'." + FROM ClosingStructureEntity AS cs + JOIN [SOURCEPROJECT].ClosingStructureEntity AS source ON cs.[rowid] = source.[rowid] + JOIN TempAssessmentSectionFailureMechanism AS asfm ON asfm.[FailureMechanismId] = cs.[FailureMechanismEntityId] + WHERE source.[Id] IS NOT cs.[Id]; + +INSERT INTO TempChanges +SELECT + asfm.[AssessmentSectionId], + asfm.[AssessmentSectionName], + asfm.[FailureMechanismId], + asfm.[FailureMechanismName], + "Het ID van kunstwerk '" || source.[Id] || "' is veranderd naar '" || sps.[Id] || "'." + FROM StabilityPointStructureEntity AS sps + JOIN [SOURCEPROJECT].StabilityPointStructureEntity AS source ON sps.[rowid] = source.[rowid] + JOIN TempAssessmentSectionFailureMechanism AS asfm ON asfm.[FailureMechanismId] = sps.[FailureMechanismEntityId] + WHERE source.[Id] IS NOT sps.[Id]; + +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 Changes - UNION - SELECT FailureMechanismId, null, AssessmentSectionId, AssessmentSectionName, msg, 2 - FROM Changes - WHERE Changes.FailureMechanismId is FailureMechanismId - ORDER BY 1, 3), - - AssessmentSectionFailureMechanismMessages(AssessmentSectionId, AssessmentSectionName, FailureMechanismId, FailureMechanismName, msg, level) AS ( - SELECT DISTINCT AssessmentSectionId, AssessmentSectionName, null, null, null, 0 - FROM FailureMechanismMessages - WHERE AssessmentSectionId IS NOT null - UNION - SELECT AssessmentSectionId, null, fmm.FailureMechanismId, fmm.FailureMechanismName, msg, fmm.level - FROM FailureMechanismMessages as fmm - WHERE fmm.AssessmentSectionId is AssessmentSectionId - ORDER BY 1, 3, 6) +FailureMechanismMessages +( + [FailureMechanismId], + [FailureMechanismName], + [AssessmentSectionId], + [AssessmentSectionName], + [msg], + [level] +) AS ( +SELECT DISTINCT + [FailureMechanismId], + [FailureMechanismName], + [AssessmentSectionId], + [AssessmentSectionName], + NULL, + 1 + FROM TempChanges + UNION SELECT - "17.1", + [FailureMechanismId], + NULL, + [AssessmentSectionId], + [AssessmentSectionName], + [msg], + 2 + FROM TempChanges + WHERE TempChanges.[FailureMechanismId] IS [FailureMechanismId] + ORDER BY 1, 3 +), +AssessmentSectionFailureMechanismMessages +( + [AssessmentSectionId], + [AssessmentSectionName], + [FailureMechanismId], + [FailureMechanismName], + [msg], + [level] +) AS ( +SELECT DISTINCT + [AssessmentSectionId], + [AssessmentSectionName], + NULL, + NULL, + NULL, + 0 + FROM FailureMechanismMessages + WHERE [AssessmentSectionId] IS NOT NULL + UNION +SELECT + [AssessmentSectionId], + NULL, + fmm.[FailureMechanismId], + fmm.[FailureMechanismName], + [msg], + fmm.[level] + FROM FailureMechanismMessages as fmm + WHERE fmm.[AssessmentSectionId] IS [AssessmentSectionId] + ORDER BY 1, 3, 6 +) +SELECT + "17.1", "17.2", - CASE WHEN AssessmentSectionName IS NOT null THEN "* Traject: '" || AssessmentSectionName || "'" ELSE - CASE WHEN FailureMechanismName IS NOT null THEN " + Toetsspoor: '" || FailureMechanismName || "'" ELSE - " - " || msg END END + CASE WHEN [AssessmentSectionName] IS NOT NULL + THEN "* Traject: '" || [AssessmentSectionName] || "'" + ELSE + CASE WHEN [FailureMechanismName] IS NOT NULL + THEN " + Toetsspoor: '" || [FailureMechanismName] || "'" + ELSE + " - " || [msg] + END + END FROM AssessmentSectionFailureMechanismMessages; -DROP TABLE FailureMechanisms; -DROP TABLE AssessmentSectionFailureMechanism; -DROP TABLE Changes; +DROP TABLE TempFailureMechanisms; +DROP TABLE TempAssessmentSectionFailureMechanism; +DROP TABLE TempChanges; -INSERT INTO [LOGDATABASE].MigrationLogEntity( - [FromVersion], - [ToVersion], - [LogMessage]) -SELECT "17.1", "17.2", "* Geen aanpassingen." -WHERE (SELECT COUNT(*) FROM [LOGDATABASE].MigrationLogEntity) IS 1; +INSERT INTO [LOGDATABASE].MigrationLogEntity ( + [FromVersion], + [ToVersion], + [LogMessage]) +SELECT "17.1", + "17.2", + "* Geen aanpassingen." + WHERE ( + SELECT COUNT() FROM [LOGDATABASE].MigrationLogEntity + ) IS 1; DETACH LOGDATABASE; Index: Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_5_17.1.sql =================================================================== diff -u -r8be0baccddc62b1efd47bc2469c597c3e480e6b8 -r4e99812f9957b2b7d9299207726ec4ccc70d157c --- Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_5_17.1.sql (.../Migration_5_17.1.sql) (revision 8be0baccddc62b1efd47bc2469c597c3e480e6b8) +++ Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_5_17.1.sql (.../Migration_5_17.1.sql) (revision 4e99812f9957b2b7d9299207726ec4ccc70d157c) @@ -333,21 +333,22 @@ */ ATTACH DATABASE '{1}' AS LOGDATABASE; -CREATE TEMP TABLE log_output_deleted ( +CREATE TEMP TABLE TempLogOutputDeleted +( 'NrDeleted' INTEGER NOT NULL ); -INSERT INTO log_output_deleted SELECT COUNT() FROM [SOURCEPROJECT].ClosingStructuresOutputEntity; -INSERT INTO log_output_deleted SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionInwardsDikeHeightOutputEntity; -INSERT INTO log_output_deleted SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionInwardsOutputEntity; -INSERT INTO log_output_deleted SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity; -INSERT INTO log_output_deleted SELECT COUNT() FROM [SOURCEPROJECT].HeightStructuresOutputEntity; -INSERT INTO log_output_deleted SELECT COUNT() FROM [SOURCEPROJECT].HydraulicLocationOutputEntity; -INSERT INTO log_output_deleted SELECT COUNT() FROM [SOURCEPROJECT].PipingCalculationOutputEntity; -INSERT INTO log_output_deleted SELECT COUNT() FROM [SOURCEPROJECT].PipingSemiProbabilisticOutputEntity; -INSERT INTO log_output_deleted SELECT COUNT() FROM [SOURCEPROJECT].StabilityPointStructuresOutputEntity; -INSERT INTO log_output_deleted SELECT COUNT() FROM [SOURCEPROJECT].StabilityStoneCoverWaveConditionsOutputEntity; -INSERT INTO log_output_deleted SELECT COUNT() FROM [SOURCEPROJECT].WaveImpactAsphaltCoverWaveConditionsOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].ClosingStructuresOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionInwardsDikeHeightOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionInwardsOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].HeightStructuresOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].HydraulicLocationOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].PipingCalculationOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].PipingSemiProbabilisticOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].StabilityPointStructuresOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].StabilityStoneCoverWaveConditionsOutputEntity; +INSERT INTO TempLogOutputDeleted SELECT COUNT() FROM [SOURCEPROJECT].WaveImpactAsphaltCoverWaveConditionsOutputEntity; CREATE TABLE IF NOT EXISTS [LOGDATABASE].'MigrationLogEntity' ( @@ -357,102 +358,182 @@ 'LogMessage' TEXT NOT NULL ); -INSERT INTO [LOGDATABASE].MigrationLogEntity( - [FromVersion], - [ToVersion], - [LogMessage]) -VALUES ("5", "17.1", "Gevolgen van de migratie van versie 5 naar versie 17.1:"); +INSERT INTO [LOGDATABASE].MigrationLogEntity ( + [FromVersion], + [ToVersion], + [LogMessage]) +VALUES ("5", + "17.1", + "Gevolgen van de migratie van versie 16.4 naar versie 17.1:"); -INSERT INTO [LOGDATABASE].MigrationLogEntity( - [FromVersion], - [ToVersion], - [LogMessage]) +INSERT INTO [LOGDATABASE].MigrationLogEntity ( + [FromVersion], + [ToVersion], + [LogMessage]) SELECT "5", - "17.1", - "* Alle berekende resultaten zijn verwijderd." - FROM log_output_deleted - WHERE [NrDeleted] > 0 - LIMIT 1; + "17.1", + "* Alle berekende resultaten zijn verwijderd." + FROM TempLogOutputDeleted + WHERE [NrDeleted] > 0 + LIMIT 1; -DROP TABLE log_output_deleted; +DROP TABLE TempLogOutputDeleted; -CREATE TEMP TABLE FailureMechanisms ('FailureMechanismType' INTEGER NOT NULL, 'FailureMechanismName' VARCHAR(255) NOT NULL); -INSERT INTO FailureMechanisms VALUES (1, 'Piping'); -INSERT INTO FailureMechanisms VALUES (2, 'Macrostabiliteit binnenwaarts'); -INSERT INTO FailureMechanisms VALUES (3, 'Golfklappen op asfaltbekleding'); -INSERT INTO FailureMechanisms VALUES (4, 'Grasbekleding erosie buitentalud'); -INSERT INTO FailureMechanisms VALUES (5, 'Grasbekleding afschuiven buitentalud'); -INSERT INTO FailureMechanisms VALUES (6, 'Grasbekleding erosie kruin en binnentalud'); -INSERT INTO FailureMechanisms VALUES (7, 'Stabiliteit steenzetting'); -INSERT INTO FailureMechanisms VALUES (8, 'Duinafslag'); -INSERT INTO FailureMechanisms VALUES (9, 'Hoogte kunstwerk'); -INSERT INTO FailureMechanisms VALUES (10, 'Betrouwbaarheid sluiting kunstwerk'); -INSERT INTO FailureMechanisms VALUES (11, 'Piping bij kunstwerk'); -INSERT INTO FailureMechanisms VALUES (12, 'Sterkte en stabiliteit puntconstructies'); -INSERT INTO FailureMechanisms VALUES (13, 'Macrostabiliteit buitenwaarts'); -INSERT INTO FailureMechanisms VALUES (14, 'Microstabiliteit'); -INSERT INTO FailureMechanisms VALUES (15, 'Wateroverdruk bij asfaltbekleding'); -INSERT INTO FailureMechanisms VALUES (16, 'Grasbekleding afschuiven binnentalud'); -INSERT INTO FailureMechanisms VALUES (17, 'Sterkte en stabiliteit langsconstructies'); -INSERT INTO FailureMechanisms VALUES (18, 'Technische innovaties'); +CREATE TEMP TABLE TempFailureMechanisms +( + 'FailureMechanismType' INTEGER NOT NULL, + 'FailureMechanismName' VARCHAR(255) NOT NULL +); -CREATE TEMP TABLE AssessmentSectionFailureMechanism (AssessmentSectionId, AssessmentSectionName, FailureMechanismId, FailureMechanismName); -INSERT INTO AssessmentSectionFailureMechanism - SELECT AssessmentSectionEntityId, Name, FailureMechanismEntityId, FailureMechanismName - FROM AssessmentSectionEntity - JOIN FailureMechanismEntity USING(AssessmentSectionEntityId) - JOIN FailureMechanisms USING(FailureMechanismType); +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, 'Macrostabiliteit buitenwaarts'); +INSERT INTO TempFailureMechanisms VALUES (14, 'Microstabiliteit'); +INSERT INTO TempFailureMechanisms VALUES (15, 'Wateroverdruk bij asfaltbekleding'); +INSERT INTO TempFailureMechanisms VALUES (16, 'Grasbekleding afschuiven binnentalud'); +INSERT INTO TempFailureMechanisms VALUES (17, 'Sterkte en stabiliteit langsconstructies'); +INSERT INTO TempFailureMechanisms VALUES (18, 'Technische innovaties'); -CREATE TEMP TABLE Changes (AssessmentSectionId, AssessmentSectionName, FailureMechanismId, FailureMechanismName, msg); -INSERT INTO Changes - SELECT asfm.AssessmentSectionId, asfm.AssessmentSectionName, asfm.FailureMechanismId, asfm.FailureMechanismName, - "De naam van dijkprofiel '" || source.Name || "' is veranderd naar '" || dp.Id || "' en wordt ook gebruikt als ID." - FROM DikeProfileEntity as dp - JOIN [SOURCEPROJECT].DikeProfileEntity as source ON dp.rowid = source.rowid - JOIN AssessmentSectionFailureMechanism as asfm ON asfm.FailureMechanismId = dp.FailureMechanismEntityId - WHERE source.Name IS NOT dp.Id; +CREATE TEMP TABLE TempAssessmentSectionFailureMechanism +( + [AssessmentSectionId], + [AssessmentSectionName], + [FailureMechanismId], + [FailureMechanismName] +); -INSERT INTO [LOGDATABASE].MigrationLogEntity( - [FromVersion], - [ToVersion], - [LogMessage]) +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 TempChanges +SELECT + asfm.[AssessmentSectionId], + asfm.[AssessmentSectionName], + asfm.[FailureMechanismId], + asfm.[FailureMechanismName], + "De naam van dijkprofiel '" || source.[Name] || "' is veranderd naar '" || dp.[Id] || "' en wordt ook gebruikt als ID." + FROM DikeProfileEntity AS dp + JOIN [SOURCEPROJECT].DikeProfileEntity AS source ON dp.[rowid] = source.[rowid] + JOIN TempAssessmentSectionFailureMechanism AS asfm ON asfm.[FailureMechanismId] = dp.[FailureMechanismEntityId] + WHERE source.[Name] IS NOT dp.[Id]; + +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 Changes - UNION - SELECT FailureMechanismId, null, AssessmentSectionId, AssessmentSectionName, msg, 2 - FROM Changes - WHERE Changes.FailureMechanismId is FailureMechanismId - ORDER BY 1, 3), - - AssessmentSectionFailureMechanismMessages(AssessmentSectionId, AssessmentSectionName, FailureMechanismId, FailureMechanismName, msg, level) AS ( - SELECT DISTINCT AssessmentSectionId, AssessmentSectionName, null, null, null, 0 - FROM FailureMechanismMessages - WHERE AssessmentSectionId IS NOT null - UNION - SELECT AssessmentSectionId, null, fmm.FailureMechanismId, fmm.FailureMechanismName, msg, fmm.level - FROM FailureMechanismMessages as fmm - WHERE fmm.AssessmentSectionId is AssessmentSectionId - ORDER BY 1, 3, 6) +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 1, 3 +), +AssessmentSectionFailureMechanismMessages +( + [AssessmentSectionId], + [AssessmentSectionName], + [FailureMechanismId], + [FailureMechanismName], + [msg], + [level] +) AS ( +SELECT DISTINCT + [AssessmentSectionId], + [AssessmentSectionName], + NULL, + NULL, + NULL, + 0 + FROM FailureMechanismMessages + WHERE [AssessmentSectionId] IS NOT NULL + UNION SELECT - "5", + [AssessmentSectionId], + NULL, + fmm.[FailureMechanismId], + fmm.[FailureMechanismName], + [msg], + fmm.[level] + FROM FailureMechanismMessages AS fmm + WHERE fmm.[AssessmentSectionId] IS [AssessmentSectionId] + ORDER BY 1, 3, 6 +) +SELECT + "5", "17.1", - CASE WHEN AssessmentSectionName IS NOT null THEN "* Traject: '" || AssessmentSectionName || "'" ELSE - CASE WHEN FailureMechanismName IS NOT null THEN " + Toetsspoor: '" || FailureMechanismName || "'" ELSE - " - " || msg END END + CASE WHEN [AssessmentSectionName] IS NOT NULL + THEN "* Traject: '" || [AssessmentSectionName] || "'" + ELSE + CASE WHEN [FailureMechanismName] IS NOT NULL + THEN " + Toetsspoor: '" || [FailureMechanismName] || "'" + ELSE + " - " || [msg] + END + END FROM AssessmentSectionFailureMechanismMessages; -DROP TABLE FailureMechanisms; -DROP TABLE AssessmentSectionFailureMechanism; -DROP TABLE Changes; +DROP TABLE TempFailureMechanisms; +DROP TABLE TempAssessmentSectionFailureMechanism; +DROP TABLE TempChanges; -INSERT INTO [LOGDATABASE].MigrationLogEntity( - [FromVersion], - [ToVersion], - [LogMessage]) -SELECT "5", "17.1", "* Geen aanpassingen." -WHERE (SELECT COUNT(*) FROM [LOGDATABASE].MigrationLogEntity) IS 1; +INSERT INTO [LOGDATABASE].MigrationLogEntity ( + [FromVersion], + [ToVersion], + [LogMessage]) +SELECT "5", + "17.1", + "* Geen aanpassingen." + WHERE ( + SELECT COUNT() FROM [LOGDATABASE].MigrationLogEntity + ) IS 1; DETACH LOGDATABASE; Index: Application/Ringtoets/test/Application.Ringtoets.Migration.Test/RingtoetsProjectMigratorTest.cs =================================================================== diff -u -r8be0baccddc62b1efd47bc2469c597c3e480e6b8 -r4e99812f9957b2b7d9299207726ec4ccc70d157c --- Application/Ringtoets/test/Application.Ringtoets.Migration.Test/RingtoetsProjectMigratorTest.cs (.../RingtoetsProjectMigratorTest.cs) (revision 8be0baccddc62b1efd47bc2469c597c3e480e6b8) +++ Application/Ringtoets/test/Application.Ringtoets.Migration.Test/RingtoetsProjectMigratorTest.cs (.../RingtoetsProjectMigratorTest.cs) (revision 4e99812f9957b2b7d9299207726ec4ccc70d157c) @@ -446,7 +446,7 @@ $"(versie {currentDatabaseVersion})."; var migrationLog = new StringBuilder(); migrationLog.AppendLine(@"Door de migratie is het project aangepast. Bekijk het migratierapport door op details te klikken."); - migrationLog.AppendLine(@"Gevolgen van de migratie van versie 5 naar versie 17.1:"); + migrationLog.AppendLine(@"Gevolgen van de migratie van versie 16.4 naar versie 17.1:"); migrationLog.AppendLine($@"* Alle berekende resultaten zijn verwijderd.{Environment.NewLine}" + $@"* Traject: 'assessmentSection'{Environment.NewLine}" + $@" + Toetsspoor: 'Grasbekleding erosie kruin en binnentalud'{Environment.NewLine}" + Index: Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo171IntegrationTest.cs =================================================================== diff -u -r8be0baccddc62b1efd47bc2469c597c3e480e6b8 -r4e99812f9957b2b7d9299207726ec4ccc70d157c --- Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo171IntegrationTest.cs (.../MigrationTo171IntegrationTest.cs) (revision 8be0baccddc62b1efd47bc2469c597c3e480e6b8) +++ Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo171IntegrationTest.cs (.../MigrationTo171IntegrationTest.cs) (revision 4e99812f9957b2b7d9299207726ec4ccc70d157c) @@ -262,7 +262,7 @@ Assert.AreEqual(6, messages.Count); AssertMigrationLogMessageEqual( - new MigrationLogMessage("5", newVersion, "Gevolgen van de migratie van versie 5 naar versie 17.1:"), + new MigrationLogMessage("5", newVersion, "Gevolgen van de migratie van versie 16.4 naar versie 17.1:"), messages[0]); AssertMigrationLogMessageEqual( new MigrationLogMessage("5", newVersion, "* Alle berekende resultaten zijn verwijderd."),