Index: Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/DatabaseStructure18.1.sql =================================================================== diff -u -rad2a5ea28040c4887578b062706c42556a6c5cc2 -r9e87591d99c3f1a53f2c93e4dbba8c8ebae492a3 --- Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/DatabaseStructure18.1.sql (.../DatabaseStructure18.1.sql) (revision ad2a5ea28040c4887578b062706c42556a6c5cc2) +++ Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/DatabaseStructure18.1.sql (.../DatabaseStructure18.1.sql) (revision 9e87591d99c3f1a53f2c93e4dbba8c8ebae492a3) @@ -1,6 +1,6 @@ /* ---------------------------------------------------- */ /* Generated by Enterprise Architect Version 12.0 */ -/* Created On : 28-Mar-2018 11:33:36 */ +/* Created On : 30-Mar-2018 08:11:27 */ /* DBMS : SQLite */ /* ---------------------------------------------------- */ @@ -21,10 +21,10 @@ DROP TABLE IF EXISTS 'AssessmentSectionEntity' ; -DROP TABLE IF EXISTS 'FailureMechanismSectionEntity' +DROP TABLE IF EXISTS 'MacroStabilityOutwardsFailureMechanismMetaEntity' ; -DROP TABLE IF EXISTS 'MacroStabilityOutwardsFailureMechanismMetaEntity' +DROP TABLE IF EXISTS 'FailureMechanismSectionEntity' ; DROP TABLE IF EXISTS 'FailureMechanismEntity' @@ -51,27 +51,27 @@ DROP TABLE IF EXISTS 'PipingCalculationEntity' ; -DROP TABLE IF EXISTS 'StabilityPointStructuresFailureMechanismMetaEntity' -; - DROP TABLE IF EXISTS 'GrassCoverErosionInwardsFailureMechanismMetaEntity' ; -DROP TABLE IF EXISTS 'DuneErosionFailureMechanismMetaEntity' +DROP TABLE IF EXISTS 'StabilityPointStructuresFailureMechanismMetaEntity' ; DROP TABLE IF EXISTS 'GrassCoverErosionInwardsCalculationEntity' ; -DROP TABLE IF EXISTS 'GrassCoverErosionOutwardsFailureMechanismMetaEntity' +DROP TABLE IF EXISTS 'DuneErosionFailureMechanismMetaEntity' ; -DROP TABLE IF EXISTS 'HeightStructuresFailureMechanismMetaEntity' +DROP TABLE IF EXISTS 'GrassCoverErosionOutwardsFailureMechanismMetaEntity' ; DROP TABLE IF EXISTS 'PipingSoilLayerEntity' ; +DROP TABLE IF EXISTS 'HeightStructuresFailureMechanismMetaEntity' +; + DROP TABLE IF EXISTS 'PipingSoilProfileEntity' ; @@ -174,9 +174,6 @@ DROP TABLE IF EXISTS 'GrassCoverErosionOutwardsWaveConditionsCalculationEntity' ; -DROP TABLE IF EXISTS 'GrassCoverErosionOutwardsHydraulicLocationEntity' -; - DROP TABLE IF EXISTS 'GrassCoverErosionOutwardsWaveConditionsOutputEntity' ; @@ -207,9 +204,6 @@ DROP TABLE IF EXISTS 'HydraulicLocationOutputEntity' ; -DROP TABLE IF EXISTS 'GrassCoverErosionOutwardsHydraulicLocationOutputEntity' -; - DROP TABLE IF EXISTS 'DuneLocationEntity' ; @@ -384,17 +378,26 @@ CONSTRAINT 'FK_AssessmentSectionEntity_HydraulicLocationCalculationCollectionEntity7' FOREIGN KEY ('HydraulicLocationCalculationCollectionEntity7Id') REFERENCES 'HydraulicLocationCalculationCollectionEntity' ('HydraulicLocationCalculationCollectionEntityId') ON DELETE No Action ON UPDATE Cascade, CONSTRAINT 'FK_AssessmentSectionEntity_HydraulicLocationCalculationCollectionEntity8' FOREIGN KEY ('HydraulicLocationCalculationCollectionEntity8Id') REFERENCES 'HydraulicLocationCalculationCollectionEntity' ('HydraulicLocationCalculationCollectionEntityId') ON DELETE No Action ON UPDATE Cascade, CONSTRAINT 'FK_AssessmentSectionEntity_ProjectEntity' FOREIGN KEY ('ProjectEntityId') REFERENCES 'ProjectEntity' ('ProjectEntityId') ON DELETE Cascade ON UPDATE Cascade, - CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity1' UNIQUE ('HydraulicLocationCalculationCollectionEntity1Id'), - CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity2' UNIQUE ('HydraulicLocationCalculationCollectionEntity2Id'), - CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity3' UNIQUE ('HydraulicLocationCalculationCollectionEntity3Id'), - CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity4' UNIQUE ('HydraulicLocationCalculationCollectionEntity4Id'), - CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity5' UNIQUE ('HydraulicLocationCalculationCollectionEntity5Id'), - CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity6' UNIQUE ('HydraulicLocationCalculationCollectionEntity6Id'), - CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity7' UNIQUE ('HydraulicLocationCalculationCollectionEntity7Id'), - CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity8' UNIQUE ('HydraulicLocationCalculationCollectionEntity8Id') + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity1Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity1Id'), + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity2Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity2Id'), + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity3Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity3Id'), + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity4Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity4Id'), + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity5Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity5Id'), + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity6Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity6Id'), + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity7Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity7Id'), + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity8Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity8Id') ) ; +CREATE TABLE 'MacroStabilityOutwardsFailureMechanismMetaEntity' +( + 'MacroStabilityOutwardsFailureMechanismMetaEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + 'FailureMechanismEntityId' INTEGER NOT NULL, + 'A' REAL NOT NULL, + CONSTRAINT 'FK_MacroStabilityOutwardsFailureMechanismMetaEntity_FailureMechanismEntity' FOREIGN KEY ('FailureMechanismEntityId') REFERENCES 'FailureMechanismEntity' ('FailureMechanismEntityId') ON DELETE Cascade ON UPDATE Cascade +) +; + CREATE TABLE 'FailureMechanismSectionEntity' ( 'FailureMechanismSectionEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, @@ -405,15 +408,6 @@ ) ; -CREATE TABLE 'MacroStabilityOutwardsFailureMechanismMetaEntity' -( - 'MacroStabilityOutwardsFailureMechanismMetaEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - 'FailureMechanismEntityId' INTEGER NOT NULL, - 'A' REAL NOT NULL, - CONSTRAINT 'FK_MacroStabilityOutwardsFailureMechanismMetaEntity_FailureMechanismEntity' FOREIGN KEY ('FailureMechanismEntityId') REFERENCES 'FailureMechanismEntity' ('FailureMechanismEntityId') ON DELETE Cascade ON UPDATE Cascade -) -; - CREATE TABLE 'FailureMechanismEntity' ( 'FailureMechanismEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, @@ -586,17 +580,6 @@ ) ; -CREATE TABLE 'StabilityPointStructuresFailureMechanismMetaEntity' -( - 'StabilityPointStructuresFailureMechanismMetaEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - 'FailureMechanismEntityId' INTEGER NOT NULL, - 'N' REAL NOT NULL, - 'ForeshoreProfileCollectionSourcePath' TEXT, - 'StabilityPointStructureCollectionSourcePath' TEXT, - CONSTRAINT 'FK_StrengthStabilityPointConstructionFailureMechanismMetaEntity_FailureMechanismEntity' FOREIGN KEY ('FailureMechanismEntityId') REFERENCES 'FailureMechanismEntity' ('FailureMechanismEntityId') ON DELETE Cascade ON UPDATE Cascade -) -; - CREATE TABLE 'GrassCoverErosionInwardsFailureMechanismMetaEntity' ( 'GrassCoverErosionInwardsFailureMechanismMetaEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, @@ -607,12 +590,14 @@ ) ; -CREATE TABLE 'DuneErosionFailureMechanismMetaEntity' +CREATE TABLE 'StabilityPointStructuresFailureMechanismMetaEntity' ( - 'DuneErosionFailureMechanismMetaEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + 'StabilityPointStructuresFailureMechanismMetaEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 'FailureMechanismEntityId' INTEGER NOT NULL, 'N' REAL NOT NULL, - CONSTRAINT 'FK_DuneErosionFailureMechanismMetaEntity_FailureMechanismEntity' FOREIGN KEY ('FailureMechanismEntityId') REFERENCES 'FailureMechanismEntity' ('FailureMechanismEntityId') ON DELETE Cascade ON UPDATE Cascade + 'ForeshoreProfileCollectionSourcePath' TEXT, + 'StabilityPointStructureCollectionSourcePath' TEXT, + CONSTRAINT 'FK_StrengthStabilityPointConstructionFailureMechanismMetaEntity_FailureMechanismEntity' FOREIGN KEY ('FailureMechanismEntityId') REFERENCES 'FailureMechanismEntity' ('FailureMechanismEntityId') ON DELETE Cascade ON UPDATE Cascade ) ; @@ -644,24 +629,40 @@ ) ; -CREATE TABLE 'GrassCoverErosionOutwardsFailureMechanismMetaEntity' +CREATE TABLE 'DuneErosionFailureMechanismMetaEntity' ( - 'GrassCoverErosionOutwardsFailureMechanismMetaEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + 'DuneErosionFailureMechanismMetaEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 'FailureMechanismEntityId' INTEGER NOT NULL, 'N' REAL NOT NULL, - 'ForeshoreProfileCollectionSourcePath' TEXT, - CONSTRAINT 'FK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_FailureMechanismEntity' FOREIGN KEY ('FailureMechanismEntityId') REFERENCES 'FailureMechanismEntity' ('FailureMechanismEntityId') ON DELETE Cascade ON UPDATE Cascade + CONSTRAINT 'FK_DuneErosionFailureMechanismMetaEntity_FailureMechanismEntity' FOREIGN KEY ('FailureMechanismEntityId') REFERENCES 'FailureMechanismEntity' ('FailureMechanismEntityId') ON DELETE Cascade ON UPDATE Cascade ) ; -CREATE TABLE 'HeightStructuresFailureMechanismMetaEntity' +CREATE TABLE 'GrassCoverErosionOutwardsFailureMechanismMetaEntity' ( - 'HeightStructuresFailureMechanismMetaEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + 'GrassCoverErosionOutwardsFailureMechanismMetaEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 'FailureMechanismEntityId' INTEGER NOT NULL, + 'HydraulicLocationCalculationCollectionEntity1Id' INTEGER NOT NULL, -- Represents the design water level failure mechanism specific factorized signaling norm + 'HydraulicLocationCalculationCollectionEntity2Id' INTEGER NOT NULL, -- Represents the design water level failure mechanism specific signaling norm + 'HydraulicLocationCalculationCollectionEntity3Id' INTEGER NOT NULL, -- Represents the design water level failure mechanism specific lower limit norm + 'HydraulicLocationCalculationCollectionEntity4Id' INTEGER NOT NULL, -- Represents the wave height failure mechanism specific factorized signaling norm + 'HydraulicLocationCalculationCollectionEntity5Id' INTEGER NOT NULL, -- Represents the wave height failure mechanism specific signaling norm + 'HydraulicLocationCalculationCollectionEntity6Id' INTEGER NOT NULL, -- Represents the wave height failure mechanism specific lower limit norm 'N' REAL NOT NULL, - 'HeightStructureCollectionSourcePath' TEXT, 'ForeshoreProfileCollectionSourcePath' TEXT, - CONSTRAINT 'FK_HeightStructuresFailureMechanismMetaEntity_FailureMechanismEntity' FOREIGN KEY ('FailureMechanismEntityId') REFERENCES 'FailureMechanismEntity' ('FailureMechanismEntityId') ON DELETE Cascade ON UPDATE Cascade + CONSTRAINT 'FK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_FailureMechanismEntity' FOREIGN KEY ('FailureMechanismEntityId') REFERENCES 'FailureMechanismEntity' ('FailureMechanismEntityId') ON DELETE Cascade ON UPDATE Cascade, + CONSTRAINT 'FK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_HydraulicLocationCalculationCollectionEntity1' FOREIGN KEY ('HydraulicLocationCalculationCollectionEntity1Id') REFERENCES 'HydraulicLocationCalculationCollectionEntity' ('HydraulicLocationCalculationCollectionEntityId') ON DELETE No Action ON UPDATE Cascade, + CONSTRAINT 'FK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_HydraulicLocationCalculationCollectionEntity2' FOREIGN KEY ('HydraulicLocationCalculationCollectionEntity2Id') REFERENCES 'HydraulicLocationCalculationCollectionEntity' ('HydraulicLocationCalculationCollectionEntityId') ON DELETE No Action ON UPDATE Cascade, + CONSTRAINT 'FK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_HydraulicLocationCalculationCollectionEntity3' FOREIGN KEY ('HydraulicLocationCalculationCollectionEntity3Id') REFERENCES 'HydraulicLocationCalculationCollectionEntity' ('HydraulicLocationCalculationCollectionEntityId') ON DELETE No Action ON UPDATE Cascade, + CONSTRAINT 'FK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_HydraulicLocationCalculationCollectionEntity4' FOREIGN KEY ('HydraulicLocationCalculationCollectionEntity4Id') REFERENCES 'HydraulicLocationCalculationCollectionEntity' ('HydraulicLocationCalculationCollectionEntityId') ON DELETE No Action ON UPDATE Cascade, + CONSTRAINT 'FK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_HydraulicLocationCalculationCollectionEntity5' FOREIGN KEY ('HydraulicLocationCalculationCollectionEntity5Id') REFERENCES 'HydraulicLocationCalculationCollectionEntity' ('HydraulicLocationCalculationCollectionEntityId') ON DELETE No Action ON UPDATE Cascade, + CONSTRAINT 'FK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_HydraulicLocationCalculationCollectionEntity6' FOREIGN KEY ('HydraulicLocationCalculationCollectionEntity6Id') REFERENCES 'HydraulicLocationCalculationCollectionEntity' ('HydraulicLocationCalculationCollectionEntityId') ON DELETE No Action ON UPDATE Cascade, + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity1Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity1Id'), + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity2Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity2Id'), + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity3Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity3Id'), + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity4Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity4Id'), + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity5Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity5Id'), + CONSTRAINT 'U_HydraulicLocationCalculationCollectionEntity6Id' UNIQUE ('HydraulicLocationCalculationCollectionEntity6Id') ) ; @@ -685,6 +686,17 @@ ) ; +CREATE TABLE 'HeightStructuresFailureMechanismMetaEntity' +( + 'HeightStructuresFailureMechanismMetaEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + 'FailureMechanismEntityId' INTEGER NOT NULL, + 'N' REAL NOT NULL, + 'HeightStructureCollectionSourcePath' TEXT, + 'ForeshoreProfileCollectionSourcePath' TEXT, + CONSTRAINT 'FK_HeightStructuresFailureMechanismMetaEntity_FailureMechanismEntity' FOREIGN KEY ('FailureMechanismEntityId') REFERENCES 'FailureMechanismEntity' ('FailureMechanismEntityId') ON DELETE Cascade ON UPDATE Cascade +) +; + CREATE TABLE 'PipingSoilProfileEntity' ( 'PipingSoilProfileEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, @@ -1130,7 +1142,7 @@ 'GrassCoverErosionOutwardsWaveConditionsCalculationEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 'CalculationGroupEntityId' INTEGER NOT NULL, 'ForeshoreProfileEntityId' INTEGER, - 'GrassCoverErosionOutwardsHydraulicLocationEntityId' INTEGER, + 'HydraulicLocationEntityId' INTEGER, 'Order' INT (4) NOT NULL, 'Name' VARCHAR (260), 'Comments' TEXT, @@ -1146,25 +1158,10 @@ 'StepSize' TINYINT (1) NOT NULL, -- Enum: 1 = 0.5, 2 = 1.0, 3 = 2.0 CONSTRAINT 'FK_GrassCoverErosionOutwardsWaveConditionsCalculationEntity_CalculationGroupEntity' FOREIGN KEY ('CalculationGroupEntityId') REFERENCES 'CalculationGroupEntity' ('CalculationGroupEntityId') ON DELETE Cascade ON UPDATE Cascade, CONSTRAINT 'FK_GrassCoverErosionOutwardsWaveConditionsCalculationEntity_ForeshoreProfileEntity' FOREIGN KEY ('ForeshoreProfileEntityId') REFERENCES 'ForeshoreProfileEntity' ('ForeshoreProfileEntityId') ON DELETE Set Null ON UPDATE Cascade, - CONSTRAINT 'FK_GrassCoverErosionOutwardsWaveConditionsCalculationEntity_GrassCoverErosionOutwardsHydraulicLocationEntity' FOREIGN KEY ('GrassCoverErosionOutwardsHydraulicLocationEntityId') REFERENCES 'GrassCoverErosionOutwardsHydraulicLocationEntity' ('GrassCoverErosionOutwardsHydraulicLocationEntityId') ON DELETE Set Null ON UPDATE Cascade + CONSTRAINT 'FK_GrassCoverErosionOutwardsWaveConditionsCalculationEntity_HydraulicLocationEntity' FOREIGN KEY ('HydraulicLocationEntityId') REFERENCES 'HydraulicLocationEntity' ('HydraulicLocationEntityId') ON DELETE Set Null ON UPDATE Cascade ) ; -CREATE TABLE 'GrassCoverErosionOutwardsHydraulicLocationEntity' -( - 'GrassCoverErosionOutwardsHydraulicLocationEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - 'FailureMechanismEntityId' INTEGER NOT NULL, - 'LocationId' INTEGER NOT NULL, - 'Name' VARCHAR (260) NOT NULL, - 'LocationX' REAL, - 'LocationY' REAL, - 'ShouldWaveHeightIllustrationPointsBeCalculated' TINYINT (1) NOT NULL, -- true or false - 'ShouldDesignWaterLevelIllustrationPointsBeCalculated' TINYINT (1) NOT NULL, -- true or false - 'Order' INT (4) NOT NULL, - CONSTRAINT 'FK_GrassCoverErosionOutwardsHydraulicLocationEntity_FailureMechanismEntity' FOREIGN KEY ('FailureMechanismEntityId') REFERENCES 'FailureMechanismEntity' ('FailureMechanismEntityId') ON DELETE Cascade ON UPDATE Cascade -) -; - CREATE TABLE 'GrassCoverErosionOutwardsWaveConditionsOutputEntity' ( 'GrassCoverErosionOutwardsWaveConditionsOutputEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, @@ -1452,24 +1449,6 @@ ) ; -CREATE TABLE 'GrassCoverErosionOutwardsHydraulicLocationOutputEntity' -( - 'GrassCoverErosionOutwardsHydraulicLocationOutputEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - 'GrassCoverErosionOutwardsHydraulicLocationEntityId' INTEGER NOT NULL, - 'GeneralResultSubMechanismIllustrationPointEntityId' INTEGER, - 'HydraulicLocationOutputType' TINYINT (1) NOT NULL, -- Enum: 1 = DesignWaterLevel, 2 = WaveHeight - 'Result' REAL, - 'TargetProbability' REAL, - 'TargetReliability' REAL, - 'CalculatedProbability' REAL, - 'CalculatedReliability' REAL, - 'CalculationConvergence' TINYINT (1) NOT NULL, -- Enum: 1 = NotCalculated, 2 = CalculatedNotConverged, 3 = CalculatedConverged - CONSTRAINT 'FK_GrassCoverErosionOutwardsHydraulicLocationOutputEntity_GeneralResultSubMechanismIllustrationPointEntity' FOREIGN KEY ('GeneralResultSubMechanismIllustrationPointEntityId') REFERENCES 'GeneralResultSubMechanismIllustrationPointEntity' ('GeneralResultSubMechanismIllustrationPointEntityId') ON DELETE Set Null ON UPDATE Cascade, - CONSTRAINT 'FK_GrassCoverErosionOutwardsHydraulicLocationOutputEntity_GrassCoverErosionOutwardsHydraulicLocationEntity' FOREIGN KEY ('GrassCoverErosionOutwardsHydraulicLocationEntityId') REFERENCES 'GrassCoverErosionOutwardsHydraulicLocationEntity' ('GrassCoverErosionOutwardsHydraulicLocationEntityId') ON DELETE Cascade ON UPDATE Cascade, - CONSTRAINT 'U_HydraulicLocationOutputType' UNIQUE ('GrassCoverErosionOutwardsHydraulicLocationEntityId','HydraulicLocationOutputType') -) -; - CREATE TABLE 'DuneLocationEntity' ( 'DuneLocationEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, @@ -2039,14 +2018,14 @@ END; ; -CREATE INDEX 'IXFK_FailureMechanismSectionEntity_FailureMechanismEntity' - ON 'FailureMechanismSectionEntity' ('FailureMechanismEntityId' ASC) -; - CREATE INDEX 'IXFK_MacroStabilityOutwardsFailureMechanismMetaEntity_FailureMechanismEntity' ON 'MacroStabilityOutwardsFailureMechanismMetaEntity' ('FailureMechanismEntityId' ASC) ; +CREATE INDEX 'IXFK_FailureMechanismSectionEntity_FailureMechanismEntity' + ON 'FailureMechanismSectionEntity' ('FailureMechanismEntityId' ASC) +; + CREATE INDEX 'IXFK_FailureMechanismEntity_AssessmentSectionEntity' ON 'FailureMechanismEntity' ('AssessmentSectionEntityId' ASC) ; @@ -2107,16 +2086,12 @@ ON 'PipingCalculationEntity' ('CalculationGroupEntityId' ASC) ; -CREATE INDEX 'IXFK_StrengthStabilityPointConstructionFailureMechanismMetaEntity_FailureMechanismEntity' - ON 'StabilityPointStructuresFailureMechanismMetaEntity' ('FailureMechanismEntityId' ASC) -; - CREATE INDEX 'IXFK_GrassCoverErosionInwardsFailureMechanismMetaEntity_FailureMechanismEntity' ON 'GrassCoverErosionInwardsFailureMechanismMetaEntity' ('FailureMechanismEntityId' ASC) ; -CREATE INDEX 'IXFK_DuneErosionFailureMechanismMetaEntity_FailureMechanismEntity' - ON 'DuneErosionFailureMechanismMetaEntity' ('FailureMechanismEntityId' ASC) +CREATE INDEX 'IXFK_StrengthStabilityPointConstructionFailureMechanismMetaEntity_FailureMechanismEntity' + ON 'StabilityPointStructuresFailureMechanismMetaEntity' ('FailureMechanismEntityId' ASC) ; CREATE INDEX 'IXFK_GrassCoverErosionInwardsCalculationEntity_CalculationGroupEntity' @@ -2131,10 +2106,55 @@ ON 'GrassCoverErosionInwardsCalculationEntity' ('HydraulicLocationEntityId' ASC) ; +CREATE INDEX 'IXFK_DuneErosionFailureMechanismMetaEntity_FailureMechanismEntity' + ON 'DuneErosionFailureMechanismMetaEntity' ('FailureMechanismEntityId' ASC) +; + CREATE INDEX 'IXFK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_FailureMechanismEntity' ON 'GrassCoverErosionOutwardsFailureMechanismMetaEntity' ('FailureMechanismEntityId' ASC) ; +CREATE INDEX 'IXFK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_HydraulicLocationCalculationCollectionEntity1' + ON 'GrassCoverErosionOutwardsFailureMechanismMetaEntity' ('HydraulicLocationCalculationCollectionEntity1Id' ASC) +; + +CREATE INDEX 'IXFK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_HydraulicLocationCalculationCollectionEntity2' + ON 'GrassCoverErosionOutwardsFailureMechanismMetaEntity' ('HydraulicLocationCalculationCollectionEntity2Id' ASC) +; + +CREATE INDEX 'IXFK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_HydraulicLocationCalculationCollectionEntity3' + ON 'GrassCoverErosionOutwardsFailureMechanismMetaEntity' ('HydraulicLocationCalculationCollectionEntity3Id' ASC) +; + +CREATE INDEX 'IXFK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_HydraulicLocationCalculationCollectionEntity4' + ON 'GrassCoverErosionOutwardsFailureMechanismMetaEntity' ('HydraulicLocationCalculationCollectionEntity4Id' ASC) +; + +CREATE INDEX 'IXFK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_HydraulicLocationCalculationCollectionEntity5' + ON 'GrassCoverErosionOutwardsFailureMechanismMetaEntity' ('HydraulicLocationCalculationCollectionEntity5Id' ASC) +; + +CREATE INDEX 'IXFK_GrassCoverErosionOutwardsFailureMechanismMetaEntity_HydraulicLocationCalculationCollectionEntity6' + ON 'GrassCoverErosionOutwardsFailureMechanismMetaEntity' ('HydraulicLocationCalculationCollectionEntity6Id' ASC) +; +CREATE TRIGGER [TR_AfterDeleteGrassCoverErosionOutwardsFailureMechanismMetaEntity_RemoveHydraulicLocationCalculationCollectionEntities] + AFTER DELETE + ON GrassCoverErosionOutwardsFailureMechanismMetaEntity + FOR EACH ROW +BEGIN + DELETE + FROM HydraulicLocationCalculationCollectionEntity + WHERE HydraulicLocationCalculationCollectionEntityId IS OLD.HydraulicLocationCalculationCollectionEntity1Id + OR HydraulicLocationCalculationCollectionEntityId IS OLD.HydraulicLocationCalculationCollectionEntity2Id + OR HydraulicLocationCalculationCollectionEntityId IS OLD.HydraulicLocationCalculationCollectionEntity3Id + OR HydraulicLocationCalculationCollectionEntityId IS OLD.HydraulicLocationCalculationCollectionEntity4Id + OR HydraulicLocationCalculationCollectionEntityId IS OLD.HydraulicLocationCalculationCollectionEntity5Id + OR HydraulicLocationCalculationCollectionEntityId IS OLD.HydraulicLocationCalculationCollectionEntity6Id + OR HydraulicLocationCalculationCollectionEntityId IS OLD.HydraulicLocationCalculationCollectionEntity7Id + OR HydraulicLocationCalculationCollectionEntityId IS OLD.HydraulicLocationCalculationCollectionEntity8Id; +END; +; + CREATE INDEX 'IXFK_HeightStructuresFailureMechanismMetaEntity_FailureMechanismEntity' ON 'HeightStructuresFailureMechanismMetaEntity' ('FailureMechanismEntityId' ASC) ; @@ -2326,14 +2346,10 @@ ON 'GrassCoverErosionOutwardsWaveConditionsCalculationEntity' ('ForeshoreProfileEntityId' ASC) ; -CREATE INDEX 'IXFK_GrassCoverErosionOutwardsWaveConditionsCalculationEntity_GrassCoverErosionOutwardsHydraulicLocationEntity' - ON 'GrassCoverErosionOutwardsWaveConditionsCalculationEntity' ('GrassCoverErosionOutwardsHydraulicLocationEntityId' ASC) +CREATE INDEX 'IXFK_GrassCoverErosionOutwardsWaveConditionsCalculationEntity_HydraulicLocationEntity' + ON 'GrassCoverErosionOutwardsWaveConditionsCalculationEntity' ('HydraulicLocationEntityId' ASC) ; -CREATE INDEX 'IXFK_GrassCoverErosionOutwardsHydraulicLocationEntity_FailureMechanismEntity' - ON 'GrassCoverErosionOutwardsHydraulicLocationEntity' ('FailureMechanismEntityId' ASC) -; - CREATE INDEX 'IXFK_GrassCoverErosionOutwardsWaveConditionsOutputEntity_GrassCoverErosionOutwardsWaveConditionsCalculationEntity' ON 'GrassCoverErosionOutwardsWaveConditionsOutputEntity' ('GrassCoverErosionOutwardsWaveConditionsCalculationEntityId' ASC) ; @@ -2446,25 +2462,6 @@ END; ; -CREATE INDEX 'IXFK_GrassCoverErosionOutwardsHydraulicLocationOutputEntity_GeneralResultSubMechanismIllustrationPointEntity' - ON 'GrassCoverErosionOutwardsHydraulicLocationOutputEntity' ('GeneralResultSubMechanismIllustrationPointEntityId' ASC) -; - -CREATE INDEX 'IXFK_GrassCoverErosionOutwardsHydraulicLocationOutputEntity_GrassCoverErosionOutwardsHydraulicLocationEntity' - ON 'GrassCoverErosionOutwardsHydraulicLocationOutputEntity' ('GrassCoverErosionOutwardsHydraulicLocationEntityId' ASC) -; -CREATE TRIGGER [TR_AfterDeleteGrassCoverErosionOutwardsHydraulicLocationOutputEntity_RemoveGeneralResultSubMechanismIllustrationPointEntity] - AFTER DELETE - ON GrassCoverErosionOutwardsHydraulicLocationOutputEntity - FOR EACH ROW -BEGIN - DELETE - FROM GeneralResultSubMechanismIllustrationPointEntity - WHERE OLD.GeneralResultSubMechanismIllustrationPointEntityId IS NOT NULL - AND GeneralResultSubMechanismIllustrationPointEntityId IS OLD.GeneralResultSubMechanismIllustrationPointEntityId; -END; -; - CREATE INDEX 'IXFK_DuneLocationEntity_FailureMechanismEntity' ON 'DuneLocationEntity' ('FailureMechanismEntityId' ASC) ; Index: Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql =================================================================== diff -u -re1d52b38682e96c50966c621bc5920846420cc2a -r9e87591d99c3f1a53f2c93e4dbba8c8ebae492a3 --- Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql (.../Migration_17.3_18.1.sql) (revision e1d52b38682e96c50966c621bc5920846420cc2a) +++ Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql (.../Migration_17.3_18.1.sql) (revision 9e87591d99c3f1a53f2c93e4dbba8c8ebae492a3) @@ -62,9 +62,6 @@ FROM [SOURCEPROJECT].GrassCoverErosionInwardsOutputEntity; INSERT INTO GrassCoverErosionInwardsOvertoppingRateOutputEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionInwardsOvertoppingRateOutputEntity; INSERT INTO GrassCoverErosionInwardsSectionResultEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionInwardsSectionResultEntity; -INSERT INTO GrassCoverErosionOutwardsFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity; -INSERT INTO GrassCoverErosionOutwardsHydraulicLocationEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity; -INSERT INTO GrassCoverErosionOutwardsHydraulicLocationOutputEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity; INSERT INTO GrassCoverErosionOutwardsSectionResultEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionOutwardsSectionResultEntity; INSERT INTO GrassCoverErosionOutwardsWaveConditionsCalculationEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionOutwardsWaveConditionsCalculationEntity; INSERT INTO GrassCoverErosionOutwardsWaveConditionsOutputEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionOutwardsWaveConditionsOutputEntity; @@ -272,104 +269,179 @@ 1.0 FROM FailureMechanismEntity WHERE FailureMechanismType = 11; - /* Note, the following conventions are used for the calculation type on AssessmentSectionEntity: -- The water level calculations for the factorized signaling norm = 1. -- The water level calculations for the signaling norm = 2. -- The water level calculations for the lower limit norm = 3. -- The water level calculations for the factorized lower limit norm = 4. -- The wave height calculations for the factorized signaling norm = 5. -- The wave height calculations for the signaling norm = 6. -- The wave height calculations for the lower limit norm = 7. -- The wave height calculations for the factorized lower limit norm = 8. -*/ +- The water level calculations for the factorized signaling norm = 0. +- The water level calculations for the signaling norm = 1. +- The water level calculations for the lower limit norm = 2. +- The water level calculations for the factorized lower limit norm = 3. +- The wave height calculations for the factorized signaling norm = 4. +- The wave height calculations for the signaling norm = 5. +- The wave height calculations for the lower limit norm = 6. +- The wave height calculations for the factorized lower limit norm = 7. +For grass cover erosion outwards: +- The design water level failure mechanism specific factorized signaling norm = 8. +- The design water level failure mechanism specific signaling norm = 9. +- The design water level failure mechanism specific lower limit norm = 10. +- The wave height failure mechanism specific factorized signaling norm = 11. +- The wave height failure mechanism specific signaling norm = 12. +- The wave height failure mechanism specific lower limit norm = 13. +*/ -- Migrate the hydraulic boundary location calculations on assessment section level -- Create the calculation entities CREATE TEMP TABLE TempHydraulicLocationCalculationEntity ( 'HydraulicLocationCalculationEntityId' INTEGER NOT NULL, 'HydraulicLocationEntityId' INTEGER NOT NULL, - 'AssessmentSectionEntityId' INTEGER NOT NULL, + 'AssessmentSectionEntityId' INTEGER, + 'GrassCoverErosionOutwardsFailureMechanismMetaEntityId' INTEGER, 'CalculationType' TINYINT (1) NOT NULL, - PRIMARY KEY + PRIMARY KEY ( 'HydraulicLocationCalculationEntityId' AUTOINCREMENT ) ); --- UNION ALL is used to repeat the operation for the calculations eight times +-- Create the calculations for the Hydraulic Boundary Locations on AssessmentSection level +-- UNION ALL is used to repeate the operation for the calculations eight times INSERT INTO TempHydraulicLocationCalculationEntity ( [HydraulicLocationEntityId], [AssessmentSectionEntityId], - CalculationType) + [CalculationType]) SELECT - HydraulicLocationEntityId, - AssessmentSectionEntityId, + [HydraulicLocationEntityId], + [AssessmentSectionEntityId], 0 FROM HydraulicLocationEntity UNION ALL SELECT - HydraulicLocationEntityId, - AssessmentSectionEntityId, + [HydraulicLocationEntityId], + [AssessmentSectionEntityId], 1 FROM HydraulicLocationEntity UNION ALL SELECT - HydraulicLocationEntityId, - AssessmentSectionEntityId, + [HydraulicLocationEntityId], + [AssessmentSectionEntityId], 2 FROM HydraulicLocationEntity UNION ALL SELECT - HydraulicLocationEntityId, - AssessmentSectionEntityId, + [HydraulicLocationEntityId], + [AssessmentSectionEntityId], 3 FROM HydraulicLocationEntity UNION ALL SELECT - HydraulicLocationEntityId, - AssessmentSectionEntityId, + [HydraulicLocationEntityId], + [AssessmentSectionEntityId], 4 FROM HydraulicLocationEntity UNION ALL SELECT - HydraulicLocationEntityId, - AssessmentSectionEntityId, + [HydraulicLocationEntityId], + [AssessmentSectionEntityId], 5 FROM HydraulicLocationEntity UNION ALL SELECT - HydraulicLocationEntityId, - AssessmentSectionEntityId, + [HydraulicLocationEntityId], + [AssessmentSectionEntityId], 6 FROM HydraulicLocationEntity UNION ALL SELECT - HydraulicLocationEntityId, - AssessmentSectionEntityId, + [HydraulicLocationEntityId], + [AssessmentSectionEntityId], 7 FROM HydraulicLocationEntity; +-- Create the calculations for the Hydraulic Boundary Locations on Grass Cover Erosion Outwards Failure Mechanism level +-- Note: it is assumed that the HBL entities of the grass cover erosion outwards failure mechanism have the same PK value +-- as the HBL entities on assessment section level. +-- Otherwise, joints will have to be used for each +INSERT INTO TempHydraulicLocationCalculationEntity ( + [HydraulicLocationEntityId], + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + [CalculationType]) +SELECT + GrassCoverErosionOutwardsHydraulicLocationEntityId, + GrassCoverErosionOutwardsFailureMechanismMetaEntityId, + 8 +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity +JOIN [SOURCEPROJECT].FailureMechanismEntity USING (FailureMechanismEntityId) +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity USING (FailureMechanismEntityId) + +UNION ALL + +SELECT + [GrassCoverErosionOutwardsHydraulicLocationEntityId], + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + 9 +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity +JOIN [SOURCEPROJECT].FailureMechanismEntity USING (FailureMechanismEntityId) +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity USING (FailureMechanismEntityId) + +UNION ALL + +SELECT + [GrassCoverErosionOutwardsHydraulicLocationEntityId], + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + 10 +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity +JOIN [SOURCEPROJECT].FailureMechanismEntity USING (FailureMechanismEntityId) +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity USING (FailureMechanismEntityId) + +UNION ALL + +SELECT + [GrassCoverErosionOutwardsHydraulicLocationEntityId], + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + 11 +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity +JOIN [SOURCEPROJECT].FailureMechanismEntity USING (FailureMechanismEntityId) +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity USING (FailureMechanismEntityId) + +UNION ALL + +SELECT + [GrassCoverErosionOutwardsHydraulicLocationEntityId], + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + 12 +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity +JOIN [SOURCEPROJECT].FailureMechanismEntity USING (FailureMechanismEntityId) +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity USING (FailureMechanismEntityId) + +UNION ALL + +SELECT + [GrassCoverErosionOutwardsHydraulicLocationEntityId], + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + 13 +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity +JOIN [SOURCEPROJECT].FailureMechanismEntity USING (FailureMechanismEntityId) +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity USING (FailureMechanismEntityId); + INSERT INTO HydraulicLocationCalculationEntity ( [HydraulicLocationCalculationEntityId], [HydraulicLocationEntityId], [ShouldIllustrationPointsBeCalculated]) SELECT - HydraulicLocationCalculationEntityId, - HydraulicLocationEntityId, + [HydraulicLocationCalculationEntityId], + [HydraulicLocationEntityId], 0 FROM TempHydraulicLocationCalculationEntity; -- Create the calculation collections CREATE TEMP TABLE TempHydraulicLocationCalculationCollectionEntity ( 'HydraulicLocationCalculationCollectionEntityId' INTEGER NOT NULL, - 'AssessmentSectionEntityId' INTEGER NOT NULL, + 'AssessmentSectionEntityId' INTEGER, + 'GrassCoverErosionOutwardsFailureMechanismMetaEntityId' INTEGER, 'CalculationType' TINYINT (1) NOT NULL, - PRIMARY KEY - ( + PRIMARY KEY + ( 'HydraulicLocationCalculationCollectionEntityId' AUTOINCREMENT ) ); @@ -378,64 +450,118 @@ [AssessmentSectionEntityId], [CalculationType]) SELECT - AssessmentSectionEntityId, + [AssessmentSectionEntityId], 0 FROM [SOURCEPROJECT].AssessmentSectionEntity UNION ALL SELECT - AssessmentSectionEntityId, + [AssessmentSectionEntityId], 1 FROM [SOURCEPROJECT].AssessmentSectionEntity UNION ALL SELECT - AssessmentSectionEntityId, + [AssessmentSectionEntityId], 2 FROM [SOURCEPROJECT].AssessmentSectionEntity UNION ALL SELECT - AssessmentSectionEntityId, + [AssessmentSectionEntityId], 3 FROM [SOURCEPROJECT].AssessmentSectionEntity UNION ALL SELECT - AssessmentSectionEntityId, + [AssessmentSectionEntityId], 4 FROM [SOURCEPROJECT].AssessmentSectionEntity UNION ALL SELECT - AssessmentSectionEntityId, + [AssessmentSectionEntityId], 5 FROM [SOURCEPROJECT].AssessmentSectionEntity UNION ALL SELECT - AssessmentSectionEntityId, + [AssessmentSectionEntityId], 6 FROM [SOURCEPROJECT].AssessmentSectionEntity UNION ALL SELECT - AssessmentSectionEntityId, + [AssessmentSectionEntityId], 7 FROM [SOURCEPROJECT].AssessmentSectionEntity; +INSERT INTO TempHydraulicLocationCalculationCollectionEntity ( + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + [CalculationType]) +SELECT + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + 8 +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity + +UNION ALL + +SELECT + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + 9 +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity + +UNION ALL + +SELECT + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + 10 +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity + +UNION ALL + +SELECT + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + 11 +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity + +UNION ALL + +SELECT + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + 12 +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity + +UNION ALL + +SELECT + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + 13 +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity; + INSERT INTO HydraulicLocationCalculationCollectionEntity ( [HydraulicLocationCalculationCollectionEntityId]) SELECT - HydraulicLocationCalculationCollectionEntityId + [HydraulicLocationCalculationCollectionEntityId] FROM TempHydraulicLocationCalculationCollectionEntity; -- Map the calculations into the collections and add them to the association table INSERT INTO HydraulicLocationCalculationCollectionToHydraulicCalculationEntity ( [HydraulicLocationCalculationCollectionEntityId], [HydraulicLocationCalculationEntityId]) -SELECT +SELECT [HydraulicLocationCalculationCollectionEntityId], [HydraulicLocationCalculationEntityId] -FROM TempHydraulicLocationCalculationEntity calculationTable -JOIN TempHydraulicLocationCalculationCollectionEntity calculationCollectionTable - ON calculationTable.AssessmentSectionEntityId = calculationCollectionTable.AssessmentSectionEntityId - AND calculationTable.CalculationType = calculationCollectionTable.CalculationType; +FROM TempHydraulicLocationCalculationEntity calculationTable +JOIN TempHydraulicLocationCalculationCollectionEntity calculationCollectionTable +ON calculationTable.AssessmentSectionEntityId = calculationCollectionTable.AssessmentSectionEntityId +AND calculationTable.CalculationType = calculationCollectionTable.CalculationType; --- Migrate the AssessmentSectionEntity +INSERT INTO HydraulicLocationCalculationCollectionToHydraulicCalculationEntity ( + [HydraulicLocationCalculationCollectionEntityId], + [HydraulicLocationCalculationEntityId]) +SELECT + [HydraulicLocationCalculationCollectionEntityId], + [HydraulicLocationCalculationEntityId] +FROM TempHydraulicLocationCalculationEntity calculationTable +JOIN TempHydraulicLocationCalculationCollectionEntity calculationCollectionTable +ON calculationTable.GrassCoverErosionOutwardsFailureMechanismMetaEntityId = calculationCollectionTable.GrassCoverErosionOutwardsFailureMechanismMetaEntityId +AND calculationTable.CalculationType = calculationCollectionTable.CalculationType; + +-- Migration for the Hydraulic Boundary Locations on Assessment Section Level INSERT INTO AssessmentSectionEntity ( [AssessmentSectionEntityId], [ProjectEntityId], @@ -458,7 +584,7 @@ [Composition], [ReferenceLinePointXml], [Order]) -SELECT +SELECT [AssessmentSectionEntityId], [ProjectEntityId], [WaterLevelCalculationsForFactorizedSignalingNormId], @@ -480,7 +606,7 @@ [Composition], [ReferenceLinePointXml], [Order] -FROM [SOURCEPROJECT].AssessmentSectionEntity +FROM [SOURCEPROJECT].AssessmentSectionEntity JOIN ( SELECT HydraulicLocationCalculationCollectionEntityId AS WaterLevelCalculationsForFactorizedSignalingNormId, @@ -534,144 +660,358 @@ UPDATE HydraulicLocationCalculationEntity SET [ShouldIllustrationPointsBeCalculated] = 1 WHERE HydraulicLocationCalculationEntityId IN ( - SELECT + SELECT HydraulicLocationCalculationEntityId FROM AssessmentSectionEntity ase JOIN HydraulicLocationCalculationCollectionEntity hlcce ON ase.HydraulicLocationCalculationCollectionEntity2Id = hlcce.HydraulicLocationCalculationCollectionEntityId JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) - JOIN [SOURCEPROJECT].HydraulicLocationEntity AS source USING (HydraulicLocationEntityId) + JOIN [SOURCEPROJECT].HydraulicLocationEntity AS source USING (HydraulicLocationEntityId) WHERE ase.NormativeNormType = 2 AND source.ShouldDesignWaterLevelIllustrationPointsBeCalculated = 1 ); UPDATE HydraulicLocationCalculationEntity SET [ShouldIllustrationPointsBeCalculated] = 1 WHERE HydraulicLocationCalculationEntityId IN ( - SELECT + SELECT HydraulicLocationCalculationEntityId FROM AssessmentSectionEntity ase JOIN HydraulicLocationCalculationCollectionEntity hlcce ON ase.HydraulicLocationCalculationCollectionEntity3Id = hlcce.HydraulicLocationCalculationCollectionEntityId JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) - JOIN [SOURCEPROJECT].HydraulicLocationEntity AS source USING (HydraulicLocationEntityId) + JOIN [SOURCEPROJECT].HydraulicLocationEntity AS source USING (HydraulicLocationEntityId) WHERE ase.NormativeNormType = 1 AND source.ShouldDesignWaterLevelIllustrationPointsBeCalculated = 1 ); UPDATE HydraulicLocationCalculationEntity SET [ShouldIllustrationPointsBeCalculated] = 1 WHERE HydraulicLocationCalculationEntityId IN ( - SELECT + SELECT HydraulicLocationCalculationEntityId FROM AssessmentSectionEntity ase JOIN HydraulicLocationCalculationCollectionEntity hlcce ON ase.HydraulicLocationCalculationCollectionEntity6Id = hlcce.HydraulicLocationCalculationCollectionEntityId JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) - JOIN [SOURCEPROJECT].HydraulicLocationEntity AS source USING (HydraulicLocationEntityId) + JOIN [SOURCEPROJECT].HydraulicLocationEntity AS source USING (HydraulicLocationEntityId) WHERE ase.NormativeNormType = 2 AND source.ShouldWaveHeightIllustrationPointsBeCalculated = 1 ); UPDATE HydraulicLocationCalculationEntity SET [ShouldIllustrationPointsBeCalculated] = 1 WHERE HydraulicLocationCalculationEntityId IN ( - SELECT + SELECT HydraulicLocationCalculationEntityId FROM AssessmentSectionEntity ase JOIN HydraulicLocationCalculationCollectionEntity hlcce ON ase.HydraulicLocationCalculationCollectionEntity7Id = hlcce.HydraulicLocationCalculationCollectionEntityId JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) - JOIN [SOURCEPROJECT].HydraulicLocationEntity AS source USING (HydraulicLocationEntityId) + JOIN [SOURCEPROJECT].HydraulicLocationEntity AS source USING (HydraulicLocationEntityId) 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] + [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 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 +UNION ALL -SELECT - [HydraulicLocationEntityOutputId], - [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.HydraulicLocationCalculationCollectionEntity3Id = hlcce.HydraulicLocationCalculationCollectionEntityId +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 +UNION ALL -SELECT - [HydraulicLocationEntityOutputId], - [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.HydraulicLocationCalculationCollectionEntity6Id = hlcce.HydraulicLocationCalculationCollectionEntityId +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 +UNION ALL -SELECT - [HydraulicLocationEntityOutputId], - [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.HydraulicLocationCalculationCollectionEntity7Id = hlcce.HydraulicLocationCalculationCollectionEntityId +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; +-- Migration for the Hydraulic Boundary Locations on Grass Cover Erosion Outwards Failure Mechanism Level +INSERT INTO GrassCoverErosionOutwardsFailureMechanismMetaEntity ( + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + [FailureMechanismEntityId], + [HydraulicLocationCalculationCollectionEntity1Id], + [HydraulicLocationCalculationCollectionEntity2Id], + [HydraulicLocationCalculationCollectionEntity3Id], + [HydraulicLocationCalculationCollectionEntity4Id], + [HydraulicLocationCalculationCollectionEntity5Id], + [HydraulicLocationCalculationCollectionEntity6Id], + [N], + [ForeshoreProfileCollectionSourcePath]) +SELECT + [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], + [FailureMechanismEntityId], + [WaterLevelCalculationsForFailureMechanismSpecificFactorizedSignalingNormId], + [WaterLevelCalculationsForFailureMechanismSpecificSignalingNormId], + [WaterLevelCalculationsForFailureMechanismSpecificLowerLimitNormId], + [WaveHeightCalculationsForFailureMechanismSpecificFactorizedSignalingNormId], + [WaveHeightCalculationsForFailureMechanismSpecificSignalingNormId], + [WaveHeightCalculationsForFailureMechanismSpecificLowerLimitNormId], + [N], + [ForeshoreProfileCollectionSourcePath] +FROM [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity +JOIN ( + SELECT + HydraulicLocationCalculationCollectionEntityId AS WaterLevelCalculationsForFailureMechanismSpecificFactorizedSignalingNormId, + GrassCoverErosionOutwardsFailureMechanismMetaEntityId + FROM TempHydraulicLocationCalculationCollectionEntity WHERE CalculationType = 8 +) USING (GrassCoverErosionOutwardsFailureMechanismMetaEntityId) +JOIN ( + SELECT + HydraulicLocationCalculationCollectionEntityId AS WaterLevelCalculationsForFailureMechanismSpecificSignalingNormId, + GrassCoverErosionOutwardsFailureMechanismMetaEntityId + FROM TempHydraulicLocationCalculationCollectionEntity WHERE CalculationType = 9 +) USING (GrassCoverErosionOutwardsFailureMechanismMetaEntityId) +JOIN ( + SELECT + HydraulicLocationCalculationCollectionEntityId AS WaterLevelCalculationsForFailureMechanismSpecificLowerLimitNormId, + GrassCoverErosionOutwardsFailureMechanismMetaEntityId + FROM TempHydraulicLocationCalculationCollectionEntity WHERE CalculationType = 10 +) USING (GrassCoverErosionOutwardsFailureMechanismMetaEntityId) +JOIN ( + SELECT + HydraulicLocationCalculationCollectionEntityId AS WaveHeightCalculationsForFailureMechanismSpecificFactorizedSignalingNormId, + GrassCoverErosionOutwardsFailureMechanismMetaEntityId + FROM TempHydraulicLocationCalculationCollectionEntity WHERE CalculationType = 11 +) USING (GrassCoverErosionOutwardsFailureMechanismMetaEntityId) +JOIN ( + SELECT + HydraulicLocationCalculationCollectionEntityId AS WaveHeightCalculationsForFailureMechanismSpecificSignalingNormId, + GrassCoverErosionOutwardsFailureMechanismMetaEntityId + FROM TempHydraulicLocationCalculationCollectionEntity WHERE CalculationType = 12 +) USING (GrassCoverErosionOutwardsFailureMechanismMetaEntityId) +JOIN ( + SELECT + HydraulicLocationCalculationCollectionEntityId AS WaveHeightCalculationsForFailureMechanismSpecificLowerLimitNormId, + GrassCoverErosionOutwardsFailureMechanismMetaEntityId + FROM TempHydraulicLocationCalculationCollectionEntity WHERE CalculationType = 13 +) USING (GrassCoverErosionOutwardsFailureMechanismMetaEntityId); + +-- Update the calculation inputs +UPDATE HydraulicLocationCalculationEntity + SET [ShouldIllustrationPointsBeCalculated] = 1 + WHERE HydraulicLocationCalculationEntityId IN ( + SELECT + HydraulicLocationCalculationEntityId + FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity gceofmme + JOIN HydraulicLocationCalculationCollectionEntity hlcce ON gceofmme.HydraulicLocationCalculationCollectionEntity2Id = hlcce.HydraulicLocationCalculationCollectionEntityId + JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) + JOIN HydraulicLocationCalculationEntity hlce USING (HydraulicLocationCalculationEntityId) + JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity source ON source.GrassCoverErosionOutwardsHydraulicLocationEntityId = hlce.HydraulicLocationEntityId + JOIN FailureMechanismEntity USING (FailureMechanismEntityId) + JOIN AssessmentSectionEntity ase USING (AssessmentSectionEntityId) + WHERE ase.NormativeNormType = 2 AND source.ShouldDesignWaterLevelIllustrationPointsBeCalculated = 1 +); + +UPDATE HydraulicLocationCalculationEntity + SET [ShouldIllustrationPointsBeCalculated] = 1 + WHERE HydraulicLocationCalculationEntityId IN ( + SELECT + HydraulicLocationCalculationEntityId + FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity gceofmme + JOIN HydraulicLocationCalculationCollectionEntity hlcce ON gceofmme.HydraulicLocationCalculationCollectionEntity3Id = hlcce.HydraulicLocationCalculationCollectionEntityId + JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) + JOIN HydraulicLocationCalculationEntity hlce USING (HydraulicLocationCalculationEntityId) + JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity source ON source.GrassCoverErosionOutwardsHydraulicLocationEntityId = hlce.HydraulicLocationEntityId + JOIN FailureMechanismEntity USING (FailureMechanismEntityId) + JOIN AssessmentSectionEntity ase USING (AssessmentSectionEntityId) + WHERE ase.NormativeNormType = 1 AND source.ShouldDesignWaterLevelIllustrationPointsBeCalculated = 1 +); + +UPDATE HydraulicLocationCalculationEntity + SET [ShouldIllustrationPointsBeCalculated] = 1 + WHERE HydraulicLocationCalculationEntityId IN ( + SELECT + HydraulicLocationCalculationEntityId + FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity gceofmme + JOIN HydraulicLocationCalculationCollectionEntity hlcce ON gceofmme.HydraulicLocationCalculationCollectionEntity5Id = hlcce.HydraulicLocationCalculationCollectionEntityId + JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) + JOIN HydraulicLocationCalculationEntity hlce USING (HydraulicLocationCalculationEntityId) + JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity source ON source.GrassCoverErosionOutwardsHydraulicLocationEntityId = hlce.HydraulicLocationEntityId + JOIN FailureMechanismEntity USING (FailureMechanismEntityId) + JOIN AssessmentSectionEntity ase USING (AssessmentSectionEntityId) + WHERE ase.NormativeNormType = 2 AND source.ShouldWaveHeightIllustrationPointsBeCalculated = 1 +); + +UPDATE HydraulicLocationCalculationEntity + SET [ShouldIllustrationPointsBeCalculated] = 1 + WHERE HydraulicLocationCalculationEntityId IN ( + SELECT + HydraulicLocationCalculationEntityId + FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity gceofmme + JOIN HydraulicLocationCalculationCollectionEntity hlcce ON gceofmme.HydraulicLocationCalculationCollectionEntity6Id = hlcce.HydraulicLocationCalculationCollectionEntityId + JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) + JOIN HydraulicLocationCalculationEntity hlce USING (HydraulicLocationCalculationEntityId) + JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity source ON source.GrassCoverErosionOutwardsHydraulicLocationEntityId = hlce.HydraulicLocationEntityId + JOIN FailureMechanismEntity USING (FailureMechanismEntityId) + JOIN AssessmentSectionEntity ase USING (AssessmentSectionEntityId) + WHERE ase.NormativeNormType = 1 AND source.ShouldWaveHeightIllustrationPointsBeCalculated = 1 +); + +-- Insert outputs +INSERT INTO HydraulicLocationOutputEntity ( + [HydraulicLocationCalculationEntityId], + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence]) +SELECT + [HydraulicLocationCalculationEntityId], + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence] +FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity gceofmme +JOIN FailureMechanismEntity USING (FailureMechanismEntityId) +JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) +JOIN HydraulicLocationCalculationCollectionEntity ON gceofmme.HydraulicLocationCalculationCollectionEntity2Id = HydraulicLocationCalculationCollectionEntityId +JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) +JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity ON GrassCoverErosionOutwardsHydraulicLocationEntityId = HydraulicLocationEntityId +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity USING (GrassCoverErosionOutwardsHydraulicLocationEntityId) +WHERE NormativeNormType = 2 AND HydraulicLocationOutputType = 1 + +UNION ALL + +SELECT + [HydraulicLocationCalculationEntityId], + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence] +FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity gceofmme +JOIN FailureMechanismEntity USING (FailureMechanismEntityId) +JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) +JOIN HydraulicLocationCalculationCollectionEntity ON gceofmme.HydraulicLocationCalculationCollectionEntity3Id = HydraulicLocationCalculationCollectionEntityId +JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) +JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity ON GrassCoverErosionOutwardsHydraulicLocationEntityId = HydraulicLocationEntityId +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity USING (GrassCoverErosionOutwardsHydraulicLocationEntityId) +WHERE NormativeNormType = 1 AND HydraulicLocationOutputType = 1 + +UNION ALL + +SELECT + [HydraulicLocationCalculationEntityId], + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence] +FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity gceofmme +JOIN FailureMechanismEntity USING (FailureMechanismEntityId) +JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) +JOIN HydraulicLocationCalculationCollectionEntity ON gceofmme.HydraulicLocationCalculationCollectionEntity5Id = HydraulicLocationCalculationCollectionEntityId +JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) +JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity ON GrassCoverErosionOutwardsHydraulicLocationEntityId = HydraulicLocationEntityId +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity USING (GrassCoverErosionOutwardsHydraulicLocationEntityId) +WHERE NormativeNormType = 2 AND HydraulicLocationOutputType = 2 + +UNION ALL + +SELECT + [HydraulicLocationCalculationEntityId], + [GeneralResultSubMechanismIllustrationPointEntityId], + [Result], + [TargetProbability], + [TargetReliability], + [CalculatedProbability], + [CalculatedReliability], + [CalculationConvergence] +FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity gceofmme +JOIN FailureMechanismEntity USING (FailureMechanismEntityId) +JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) +JOIN HydraulicLocationCalculationCollectionEntity ON gceofmme.HydraulicLocationCalculationCollectionEntity6Id = HydraulicLocationCalculationCollectionEntityId +JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) +JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity ON GrassCoverErosionOutwardsHydraulicLocationEntityId = HydraulicLocationEntityId +JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity USING (GrassCoverErosionOutwardsHydraulicLocationEntityId) +WHERE NormativeNormType = 1 AND HydraulicLocationOutputType = 2; + -- Cleanup DROP TABLE TempHydraulicLocationCalculationEntity; DROP TABLE TempHydraulicLocationCalculationCollectionEntity; Index: Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs =================================================================== diff -u -r146a14cd6e338acf7600d9a1fb3da2a00cbb6e13 -r9e87591d99c3f1a53f2c93e4dbba8c8ebae492a3 --- Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs (.../MigrationTo181IntegrationTest.cs) (revision 146a14cd6e338acf7600d9a1fb3da2a00cbb6e13) +++ Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs (.../MigrationTo181IntegrationTest.cs) (revision 9e87591d99c3f1a53f2c93e4dbba8c8ebae492a3) @@ -81,6 +81,7 @@ AssertAssessmentSection(reader, sourceFilePath); AssertHydraulicBoundaryLocationsProperties(reader, sourceFilePath); AssertHydraulicBoundaryLocationsOnAssessmentSection(reader, sourceFilePath); + AssertHydraulicBoundaryLocationsOnGrassCoverErosionOutwardsFailureMechanism(reader, sourceFilePath); AssertPipingSoilLayers(reader); AssertHydraRingPreprocessor(reader); @@ -92,6 +93,8 @@ AssertClosingStructuresOutput(reader, sourceFilePath); AssertHeightStructuresOutput(reader, sourceFilePath); AssertStabilityPointStructuresOutput(reader, sourceFilePath); + + AssertGrassCoverErosionOutwardsFailureMechanismMetaEntity(reader, sourceFilePath ); } AssertLogDatabase(logFilePath); @@ -133,8 +136,6 @@ "GrassCoverErosionInwardsOvertoppingRateOutputEntity", "GrassCoverErosionInwardsSectionResultEntity", "GrassCoverErosionOutwardsFailureMechanismMetaEntity", - "GrassCoverErosionOutwardsHydraulicLocationEntity", - "GrassCoverErosionOutwardsHydraulicLocationOutputEntity", "GrassCoverErosionOutwardsSectionResultEntity", "GrassCoverErosionOutwardsWaveConditionsCalculationEntity", "GrassCoverErosionOutwardsWaveConditionsOutputEntity", @@ -147,7 +148,6 @@ "HeightStructuresSectionResultEntity", "HydraRingPreprocessorEntity", "HydraulicLocationEntity", - "HydraulicLocationOutputEntity", "IllustrationPointResultEntity", "MacroStabilityInwardsCalculationEntity", "MacroStabilityInwardsCalculationOutputEntity", @@ -485,6 +485,30 @@ AssertWaveHeightCalculationEntitiesOnAssessmentSection(reader, queryGenerator); } + private static void AssertHydraulicBoundaryLocationsOnGrassCoverErosionOutwardsFailureMechanism(MigratedDatabaseReader reader, string sourceFilePath) + { + var queryGenerator = new HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator(sourceFilePath); + + AssertDesignWaterLevelCalculationEntitiesOnGrassCoverErosionOutwardsFailureMechanism(reader, queryGenerator); + AssertWaveHeightCalculationEntitiesOnGrassCoverErosionOutwardsFailureMechanism(reader, queryGenerator); + } + + private static void AssertGrassCoverErosionOutwardsFailureMechanismMetaEntity(MigratedDatabaseReader reader, string sourceFilePath) + { + string validateMetaEntity = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT " + + "COUNT() = (SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity) " + + "FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity new " + + "JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity source USING (GrassCoverErosionOutwardsFailureMechanismMetaEntityId) " + + "WHERE new.FailureMechanismEntityId = source.FailureMechanismEntityId " + + "AND new.N = source.N " + + "AND new.ForeshoreProfileCollectionSourcePath IS source.ForeshoreProfileCollectionSourcePath; " + + "DETACH DATABASE SOURCEPROJECT;"; + + reader.AssertReturnedDataIsValid(validateMetaEntity); + } + #region Migrated Hydraulic Boundary Locations on Assessment section private static void AssertWaveHeightCalculationEntitiesOnAssessmentSection(MigratedDatabaseReader reader, @@ -883,5 +907,366 @@ } #endregion + + #region Migrated Hydraulic Boundary Locations on Grass Cover Erosion Outwards Failure Mechanism + + private static void AssertDesignWaterLevelCalculationEntitiesOnGrassCoverErosionOutwardsFailureMechanism(MigratedDatabaseReader reader, + HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator queryGenerator) + { + reader.AssertReturnedDataIsValid(queryGenerator.GetNrOfHydraulicBoundaryLocationCalculationsPerFailureMechanism( + HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator.CalculationType.WaterLevelCalculationsForMechanismSpecificFactorizedSignalingNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetNrOfHydraulicBoundaryLocationCalculationsPerFailureMechanism( + HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator.CalculationType.WaterLevelCalculationsForMechanismSpecificSignalingNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetNrOfHydraulicBoundaryLocationCalculationsPerFailureMechanism( + HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator.CalculationType.WaterLevelCalculationsForMechanismSpecificLowerLimitNorm)); + + reader.AssertReturnedDataIsValid(queryGenerator.GetMigratedDesignWaterLevelCalculationsValidationQuery(NormativeNormType.SignalingNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetMigratedDesignWaterLevelCalculationOutputsValidationQuery(NormativeNormType.SignalingNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetMigratedDesignWaterLevelCalculationsValidationQuery(NormativeNormType.LowerLimitNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetMigratedDesignWaterLevelCalculationOutputsValidationQuery(NormativeNormType.LowerLimitNorm)); + + reader.AssertReturnedDataIsValid(queryGenerator.GetNewCalculationsValidationQuery( + HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator.CalculationType.WaterLevelCalculationsForMechanismSpecificFactorizedSignalingNorm)); + reader.AssertReturnedDataIsValid(HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator.GetNewCalculationOutputsValidationQuery( + HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator.CalculationType.WaterLevelCalculationsForMechanismSpecificFactorizedSignalingNorm)); + } + + private static void AssertWaveHeightCalculationEntitiesOnGrassCoverErosionOutwardsFailureMechanism(MigratedDatabaseReader reader, + HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator queryGenerator) + { + reader.AssertReturnedDataIsValid(queryGenerator.GetNrOfHydraulicBoundaryLocationCalculationsPerFailureMechanism( + HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator.CalculationType.WaveHeightCalculationsForMechanismSpecificFactorizedSignalingNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetNrOfHydraulicBoundaryLocationCalculationsPerFailureMechanism( + HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator.CalculationType.WaveHeightCalculationsForMechanismSpecificSignalingNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetNrOfHydraulicBoundaryLocationCalculationsPerFailureMechanism( + HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator.CalculationType.WaveHeightCalculationsForMechanismSpecificLowerLimitNorm)); + + reader.AssertReturnedDataIsValid(queryGenerator.GetMigratedWaveHeightCalculationsValidationQuery(NormativeNormType.SignalingNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetMigratedWaveHeightCalculationOutputsValidationQuery(NormativeNormType.SignalingNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetMigratedWaveHeightCalculationsValidationQuery(NormativeNormType.LowerLimitNorm)); + reader.AssertReturnedDataIsValid(queryGenerator.GetMigratedWaveHeightCalculationOutputsValidationQuery(NormativeNormType.LowerLimitNorm)); + + reader.AssertReturnedDataIsValid(queryGenerator.GetNewCalculationsValidationQuery( + HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator.CalculationType.WaveHeightCalculationsForMechanismSpecificFactorizedSignalingNorm)); + reader.AssertReturnedDataIsValid(HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator.GetNewCalculationOutputsValidationQuery( + HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator.CalculationType.WaveHeightCalculationsForMechanismSpecificFactorizedSignalingNorm)); + } + + private class HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator + { + /// + /// Enum to indicate the hydraulic location calculation type. + /// + public enum CalculationType + { + /// + /// Represents the water level calculations for the mechanism specific factorized signaling norm. + /// + WaterLevelCalculationsForMechanismSpecificFactorizedSignalingNorm = 1, + + /// + /// Represents the water level calculations for the mechanism specific signaling norm. + /// + WaterLevelCalculationsForMechanismSpecificSignalingNorm = 2, + + /// + /// Represents the water level calculations for the mechanism specific lower limit norm. + /// + WaterLevelCalculationsForMechanismSpecificLowerLimitNorm = 3, + + /// + /// Represents the wave height calculations for the mechanism specific factorized signaling norm. + /// + WaveHeightCalculationsForMechanismSpecificFactorizedSignalingNorm = 4, + + /// + /// Represents the wave height calculations for the mechanism specific signaling norm. + /// + WaveHeightCalculationsForMechanismSpecificSignalingNorm = 5, + + /// + /// Represents the wave height calculations for the mechanism specific lower limit norm. + /// + WaveHeightCalculationsForMechanismSpecificLowerLimitNorm = 6 + } + + private readonly string sourceFilePath; + + public HydraulicLocationOnGrassCoverErosionOutwardsFailureMechanismValidationQueryGenerator(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 hydraulic boundary location calculations per failure mechanism section. + /// + /// The type of calculation that should be validated. + /// The query to validate the number of hydraulic boundary location calculations per assessment section. + public string GetNrOfHydraulicBoundaryLocationCalculationsPerFailureMechanism(CalculationType calculationType) + { + return $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT COUNT() = 0 " + + "FROM " + + "( " + + "SELECT " + + "[FailureMechanismEntityId], " + + "COUNT(distinct GrassCoverErosionOutwardsHydraulicLocationEntityId) as OldCount, " + + "NEWCount " + + "FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity " + + "JOIN [SOURCEPROJECT].FailureMechanismEntity USING (FailureMechanismEntityId) " + + "LEFT JOIN " + + "( " + + "SELECT " + + "[FailureMechanismEntityId], " + + "COUNT(distinct HydraulicLocationEntityId) AS NewCount " + + "FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity " + + "JOIN HydraulicLocationCalculationCollectionEntity " + + $"ON HydraulicLocationCalculationCollectionEntity{(int) calculationType}Id = HydraulicLocationCalculationCollectionEntityId " + + "JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) " + + "JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) " + + "JOIN FailureMechanismEntity USING (FailureMechanismEntityId) " + + "GROUP BY GrassCoverErosionOutwardsFailureMechanismMetaEntityId " + + ") USING (FailureMechanismEntityId) " + + "GROUP BY FailureMechanismEntityId " + + ") " + + "WHERE OldCount IS NOT NewCount; " + + "DETACH DATABASE SOURCEPROJECT;"; + } + + /// + /// Generates a query to validate if the hydraulic boundary calculation input for the wave height calculations + /// are migrated correctly. + /// + /// The norm type to generate the query for. + /// A query to validate the hydraulic boundary location calculation input for the wave height calculations. + /// Thrown when + /// is an invalid value of . + /// Thrown when is a valid value, + /// but is unsupported. + public string GetMigratedDesignWaterLevelCalculationsValidationQuery(NormativeNormType normType) + { + CalculationType calculationType = ConvertToDesignWaterLevelCalculationType(normType); + + return $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT " + + "COUNT() = 0 " + + "FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity gceofmme " + + "JOIN HydraulicLocationCalculationCollectionEntity hlcce " + + $"ON gceofmme.HydraulicLocationCalculationCollectionEntity{(int)calculationType}Id = hlcce.HydraulicLocationCalculationCollectionEntityId " + + "JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) " + + "JOIN HydraulicLocationCalculationEntity NEW USING (HydraulicLocationCalculationEntityId) " + + "JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity OLD ON OLD.GrassCoverErosionOutwardsHydraulicLocationEntityId = NEW.HydraulicLocationEntityId " + + "JOIN FailureMechanismEntity USING (FailureMechanismEntityId) " + + "JOIN AssessmentSectionEntity ase USING (AssessmentSectionEntityId) " + + $"WHERE OLD.ShouldDesignWaterLevelIllustrationPointsBeCalculated != NEW.ShouldIllustrationPointsBeCalculated AND ase.NormativeNormType = {(int)normType}; " + + "DETACH DATABASE SOURCEPROJECT;"; + } + + /// + /// 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 GetMigratedDesignWaterLevelCalculationOutputsValidationQuery(NormativeNormType normType) + { + CalculationType calculationType = ConvertToDesignWaterLevelCalculationType(normType); + + return $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT " + + "COUNT() = ( " + + "SELECT COUNT() " + + "FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity " + + "JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity USING (GrassCoverErosionOutwardsHydraulicLocationEntityId) " + + "JOIN [SOURCEPROJECT].FailureMechanismEntity USING (FailureMechanismEntityId) " + + "JOIN [SOURCEPROJECT].AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + $"WHERE HydraulicLocationOutputType = 1 AND NormativeNormType = {(int) normType}" + + ") " + + "FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity gceofmme " + + "JOIN HydraulicLocationCalculationCollectionEntity hlcce " + + $"ON gceofmme.HydraulicLocationCalculationCollectionEntity{(int) calculationType}Id = hlcce.HydraulicLocationCalculationCollectionEntityId " + + "JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) " + + "JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) " + + "JOIN HydraulicLocationOutputEntity NEW USING (HydraulicLocationCalculationEntityId) " + + "JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity OLD ON 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. + /// + /// The norm type to generate the query for. + /// A query to validate the hydraulic boundary location calculation input for the wave height calculations. + /// Thrown when + /// is an invalid value of . + /// Thrown when is a valid value, + /// but is unsupported. + public string GetMigratedWaveHeightCalculationsValidationQuery(NormativeNormType normType) + { + CalculationType calculationType = ConvertToWaveHeightCalculationType(normType); + + return $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT " + + "COUNT() = 0 " + + "FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity gceofmme " + + "JOIN HydraulicLocationCalculationCollectionEntity hlcce " + + $"ON gceofmme.HydraulicLocationCalculationCollectionEntity{(int) calculationType}Id = hlcce.HydraulicLocationCalculationCollectionEntityId " + + "JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) " + + "JOIN HydraulicLocationCalculationEntity NEW USING (HydraulicLocationCalculationEntityId) " + + "JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity OLD ON OLD.GrassCoverErosionOutwardsHydraulicLocationEntityId = NEW.HydraulicLocationEntityId " + + "JOIN FailureMechanismEntity USING (FailureMechanismEntityId) " + + "JOIN AssessmentSectionEntity ase USING (AssessmentSectionEntityId) " + + $"WHERE OLD.ShouldWaveHeightIllustrationPointsBeCalculated != NEW.ShouldIllustrationPointsBeCalculated AND ase.NormativeNormType = {(int) normType}; " + + "DETACH DATABASE SOURCEPROJECT;"; + } + + /// + /// 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 GetMigratedWaveHeightCalculationOutputsValidationQuery(NormativeNormType normType) + { + CalculationType calculationType = ConvertToWaveHeightCalculationType(normType); + + return $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT " + + "COUNT() = ( " + + "SELECT COUNT() " + + "FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity " + + "JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity USING (GrassCoverErosionOutwardsHydraulicLocationEntityId) " + + "JOIN [SOURCEPROJECT].FailureMechanismEntity USING (FailureMechanismEntityId) " + + "JOIN [SOURCEPROJECT].AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + $"WHERE HydraulicLocationOutputType = 2 AND NormativeNormType = {(int) normType}" + + ") " + + "FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity gceofmme " + + "JOIN HydraulicLocationCalculationCollectionEntity hlcce " + + $"ON gceofmme.HydraulicLocationCalculationCollectionEntity{(int) calculationType}Id = hlcce.HydraulicLocationCalculationCollectionEntityId " + + "JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) " + + "JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) " + + "JOIN HydraulicLocationOutputEntity NEW USING (HydraulicLocationCalculationEntityId) " + + "JOIN [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationOutputEntity OLD ON 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. + /// The query to validate the hydraulic boundary location calculation input. + public string GetNewCalculationsValidationQuery(CalculationType calculationType) + { + return $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT COUNT() = (SELECT COUNT() FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity) " + + "FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity " + + "JOIN HydraulicLocationCalculationCollectionEntity " + + $"ON HydraulicLocationCalculationCollectionEntity{(int) calculationType}Id = HydraulicLocationCalculationCollectionEntityId " + + "JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) " + + "JOIN HydraulicLocationCalculationEntity hlce USING (HydraulicLocationCalculationEntityId) " + + "WHERE hlce.ShouldIllustrationPointsBeCalculated = 0;" + + "DETACH DATABASE SOURCEPROJECT;"; + } + + /// + /// Generates a query to validate the new hydraulic boundary location calculation outputs that are not based on migrated data. + /// + /// The type of calculation on which the output should be validated. + /// The query to validate the hydraulic boundary location calculation input. + public static string GetNewCalculationOutputsValidationQuery(CalculationType calculationType) + { + return "SELECT " + + "COUNT() = 0 " + + "FROM GrassCoverErosionOutwardsFailureMechanismMetaEntity " + + "JOIN HydraulicLocationCalculationCollectionEntity " + + $"ON HydraulicLocationCalculationCollectionEntity{(int) calculationType}Id = HydraulicLocationCalculationCollectionEntityId " + + "JOIN HydraulicLocationCalculationCollectionToHydraulicCalculationEntity USING (HydraulicLocationCalculationCollectionEntityId) " + + "JOIN HydraulicLocationCalculationEntity USING (HydraulicLocationCalculationEntityId) " + + "JOIN HydraulicLocationOutputEntity USING (HydraulicLocationCalculationEntityId); "; + } + + /// + /// 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.WaterLevelCalculationsForMechanismSpecificLowerLimitNorm; + case NormativeNormType.SignalingNorm: + return CalculationType.WaterLevelCalculationsForMechanismSpecificSignalingNorm; + 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.WaveHeightCalculationsForMechanismSpecificLowerLimitNorm; + case NormativeNormType.SignalingNorm: + return CalculationType.WaveHeightCalculationsForMechanismSpecificSignalingNorm; + default: + throw new NotSupportedException(); + } + } + } + + #endregion } } \ No newline at end of file