Index: Ringtoets/Piping/src/Ringtoets.Piping.IO/Builders/SoilDatabaseQueryBuilder.cs =================================================================== diff -u -r0c4e4faf0839d9ad90af4ff556357fc27c1f333a -r045bb1ffbe520f780522bd6b4f6afe06c096d34c --- Ringtoets/Piping/src/Ringtoets.Piping.IO/Builders/SoilDatabaseQueryBuilder.cs (.../SoilDatabaseQueryBuilder.cs) (revision 0c4e4faf0839d9ad90af4ff556357fc27c1f333a) +++ Ringtoets/Piping/src/Ringtoets.Piping.IO/Builders/SoilDatabaseQueryBuilder.cs (.../SoilDatabaseQueryBuilder.cs) (revision 045bb1ffbe520f780522bd6b4f6afe06c096d34c) @@ -112,16 +112,19 @@ /// that can be read from the database. public static string GetPipingSoilProfileCountQuery() { - return String.Format( - "SELECT " + - "(SELECT COUNT('1') " + - "FROM Mechanism AS m " + - "JOIN MechanismPointLocation AS mpl USING(ME_ID) " + - "JOIN SoilProfile2D AS p2 USING(SP2D_ID) " + - "WHERE m.{0} = @{0}) " + - " + " + - "(SELECT COUNT('1') " + - "FROM SoilProfile1D) AS {1};", MechanismDatabaseColumns.MechanismName, SoilProfileDatabaseColumns.ProfileCount); + return String.Format("SELECT (" + + "SELECT COUNT(DISTINCT s2.SP2D_ID) " + + "FROM Mechanism AS m " + + "JOIN MechanismPointLocation AS mpl USING(ME_ID) " + + "JOIN SoilProfile2D AS p2 USING(SP2D_ID) " + + "JOIN SoilLayer2D AS s2 USING(SP2D_ID) " + + "WHERE m.{0} = @{0} " + + ") + ( " + + "SELECT COUNT(DISTINCT p1.SP1D_ID) " + + "FROM SoilProfile1D AS p1 " + + "JOIN SoilLayer1D AS s1 " + + "USING(SP1D_ID)" + + ") AS {1};", MechanismDatabaseColumns.MechanismName, SoilProfileDatabaseColumns.ProfileCount); } /// Index: Ringtoets/Piping/src/Ringtoets.Piping.IO/SoilProfile/PipingSoilProfileReader.cs =================================================================== diff -u -r0c4e4faf0839d9ad90af4ff556357fc27c1f333a -r045bb1ffbe520f780522bd6b4f6afe06c096d34c --- Ringtoets/Piping/src/Ringtoets.Piping.IO/SoilProfile/PipingSoilProfileReader.cs (.../PipingSoilProfileReader.cs) (revision 0c4e4faf0839d9ad90af4ff556357fc27c1f333a) +++ Ringtoets/Piping/src/Ringtoets.Piping.IO/SoilProfile/PipingSoilProfileReader.cs (.../PipingSoilProfileReader.cs) (revision 045bb1ffbe520f780522bd6b4f6afe06c096d34c) @@ -233,9 +233,9 @@ "max(case when pn.PN_Name = 'BelowPhreaticLevel' then pv.PV_Value end) {1},", "max(case when pn.PN_Name = 'DryUnitWeight' then pv.PV_Value end) {2}", "FROM ParameterNames as pn", - "JOIN ParameterValues as pv ON pn.PN_ID = pv.PN_ID", - "JOIN Materials as m ON m.MA_ID = pv.MA_ID", - "GROUP BY m.MA_ID) as mat ON l.MA_ID = mat.MA_ID"), + "JOIN ParameterValues as pv USING(PN_ID)", + "JOIN Materials as m USING(MA_ID)", + "GROUP BY m.MA_ID) as mat USING(MA_ID)"), SoilProfileDatabaseColumns.AbovePhreaticLevel, SoilProfileDatabaseColumns.BelowPhreaticLevel, SoilProfileDatabaseColumns.DryUnitWeight); @@ -244,22 +244,22 @@ string.Join(" ", "(SELECT SP1D_ID, COUNT(*) as {0}", "FROM SoilLayer1D", - "GROUP BY SP1D_ID) lc ON lc.SP1D_ID = p.SP1D_ID"), SoilProfileDatabaseColumns.LayerCount); + "GROUP BY SP1D_ID) lc USING(SP1D_ID)"), SoilProfileDatabaseColumns.LayerCount); string layer2DCountQuery = string.Format( string.Join(" ", "(SELECT SP2D_ID, COUNT(*) as {0}", "FROM SoilLayer2D", - "GROUP BY SP2D_ID) lc ON lc.SP2D_ID = p.SP2D_ID"), SoilProfileDatabaseColumns.LayerCount); + "GROUP BY SP2D_ID) lc USING(SP2D_ID)"), SoilProfileDatabaseColumns.LayerCount); string layer1DPropertiesQuery = string.Format( string.Join(" ", "(SELECT", "pv.SL1D_ID,", "max(case when pn.PN_Name = 'IsAquifer' then pv.PV_Value end) {0}", "FROM ParameterNames as pn", - "JOIN LayerParameterValues as pv ON pn.PN_ID = pv.PN_ID", - "GROUP BY pv.SL1D_ID) as lpv ON lpv.SL1D_ID = l.SL1D_ID" + "JOIN LayerParameterValues as pv USING(PN_ID)", + "GROUP BY pv.SL1D_ID) as lpv USING(SL1D_ID)" ), SoilProfileDatabaseColumns.IsAquifer); string layer2DPropertiesQuery = string.Format( @@ -268,8 +268,8 @@ "pv.SL2D_ID,", "max(case when pn.PN_Name = 'IsAquifer' then pv.PV_Value end) {0}", "FROM ParameterNames as pn", - "JOIN LayerParameterValues as pv ON pn.PN_ID = pv.PN_ID", - "GROUP BY pv.SL2D_ID) as lpv ON lpv.SL2D_ID = l.SL2D_ID" + "JOIN LayerParameterValues as pv USING(PN_ID)", + "GROUP BY pv.SL2D_ID) as lpv USING(SL2D_ID)" ), SoilProfileDatabaseColumns.IsAquifer); var query1D = string.Format( @@ -286,7 +286,7 @@ "p.SP1D_ID as {9}", "FROM SoilProfile1D as p", "JOIN {10}", - "JOIN SoilLayer1D as l ON l.SP1D_ID = p.SP1D_ID", + "JOIN SoilLayer1D as l USING(SP1D_ID)", "LEFT JOIN {11}", "LEFT JOIN {12}", "ORDER BY ProfileName;"), @@ -318,13 +318,13 @@ "{8},", "p.SP2D_ID as {9}", "FROM Mechanism as m", - "JOIN MechanismPointLocation as mpl ON mpl.ME_ID = m.ME_ID", - "JOIN SoilProfile2D as p ON p.SP2D_ID = mpl.SP2D_ID", + "JOIN MechanismPointLocation as mpl USING(ME_ID)", + "JOIN SoilProfile2D as p USING(SP2D_ID)", "JOIN {10}", - "JOIN SoilLayer2D as l ON l.SP2D_ID = p.SP2D_ID", + "JOIN SoilLayer2D as l USING(SP2D_ID)", "LEFT JOIN {11}", "LEFT JOIN {12}", - "WHERE m.ME_Name = @{13}", + "WHERE m.ME_Name = @ME_Name", "ORDER BY ProfileName;"), SoilProfileDatabaseColumns.Dimension, SoilProfileDatabaseColumns.ProfileName, @@ -338,8 +338,7 @@ SoilProfileDatabaseColumns.SoilProfileId, layer2DCountQuery, materialPropertiesQuery, - layer2DPropertiesQuery, - mechanismParameterName); + layer2DPropertiesQuery); dataReader = CreateDataReader(countQuery + query2D + query1D, new SQLiteParameter { @@ -351,8 +350,7 @@ DbType = DbType.String, ParameterName = String.Format("@{0}", MechanismDatabaseColumns.MechanismName), Value = pipingMechanismName - } - ); + }); } private void GetCount() Index: Ringtoets/Piping/test/Ringtoets.Piping.IO.Test/Builders/SoilDatabaseQueryBuilderTest.cs =================================================================== diff -u -r0c4e4faf0839d9ad90af4ff556357fc27c1f333a -r045bb1ffbe520f780522bd6b4f6afe06c096d34c --- Ringtoets/Piping/test/Ringtoets.Piping.IO.Test/Builders/SoilDatabaseQueryBuilderTest.cs (.../SoilDatabaseQueryBuilderTest.cs) (revision 0c4e4faf0839d9ad90af4ff556357fc27c1f333a) +++ Ringtoets/Piping/test/Ringtoets.Piping.IO.Test/Builders/SoilDatabaseQueryBuilderTest.cs (.../SoilDatabaseQueryBuilderTest.cs) (revision 045bb1ffbe520f780522bd6b4f6afe06c096d34c) @@ -64,15 +64,19 @@ public void GetPipingSoilProfileCountQuery_Always_ReturnsExpectedValues() { // Setup - const string expectedQuery = "SELECT " + - "(SELECT COUNT('1') " + + const string expectedQuery = "SELECT (" + + "SELECT COUNT(DISTINCT s2.SP2D_ID) " + "FROM Mechanism AS m " + "JOIN MechanismPointLocation AS mpl USING(ME_ID) " + "JOIN SoilProfile2D AS p2 USING(SP2D_ID) " + - "WHERE m.ME_Name = @ME_Name) " + - " + " + - "(SELECT COUNT('1') " + - "FROM SoilProfile1D) AS nrOfRows;"; + "JOIN SoilLayer2D AS s2 USING(SP2D_ID) " + + "WHERE m.ME_Name = @ME_Name " + + ") + ( " + + "SELECT COUNT(DISTINCT p1.SP1D_ID) " + + "FROM SoilProfile1D AS p1 " + + "JOIN SoilLayer1D AS s1 " + + "USING(SP1D_ID)" + + ") AS nrOfRows;"; // Call string query = SoilDatabaseQueryBuilder.GetPipingSoilProfileCountQuery(); Index: Ringtoets/Piping/test/Ringtoets.Piping.IO.Test/SoilProfile/PipingSoilProfileReaderTest.cs =================================================================== diff -u -rd82fa09fe9ae053ce7702ba89ef23ae029640d1b -r045bb1ffbe520f780522bd6b4f6afe06c096d34c --- Ringtoets/Piping/test/Ringtoets.Piping.IO.Test/SoilProfile/PipingSoilProfileReaderTest.cs (.../PipingSoilProfileReaderTest.cs) (revision d82fa09fe9ae053ce7702ba89ef23ae029640d1b) +++ Ringtoets/Piping/test/Ringtoets.Piping.IO.Test/SoilProfile/PipingSoilProfileReaderTest.cs (.../PipingSoilProfileReaderTest.cs) (revision 045bb1ffbe520f780522bd6b4f6afe06c096d34c) @@ -121,6 +121,7 @@ { // Assert Assert.IsFalse(pipingSoilProfileReader.HasNext); + Assert.AreEqual(0, pipingSoilProfileReader.Count); } } @@ -181,12 +182,38 @@ } // Assert + Assert.AreEqual(2, pipingSoilProfilesReader.Count); Assert.AreEqual(2, result.Count); Assert.AreEqual(result[0].Name, result[1].Name); } } [Test] + public void ReadProfile_DatabaseWith1DAndOrphan1D_ReturnOneProfile() + { + // Setup + var testFile = "1dprofileWithEmpty1d.soil"; + var dbFile = Path.Combine(testDataPath, testFile); + var expextedProfileName = "Profile"; + + using (var pipingSoilProfilesReader = new PipingSoilProfileReader(dbFile)) + { + var result = new Collection(); + + // Call + while (pipingSoilProfilesReader.HasNext) + { + result.Add(pipingSoilProfilesReader.ReadProfile()); + } + + // Assert + Assert.AreEqual(1, pipingSoilProfilesReader.Count); + Assert.AreEqual(1, result.Count); + Assert.AreEqual(expextedProfileName, result[0].Name); + } + } + + [Test] public void ReadProfile_DatabaseProfileWithInvalid2dLayerGeometry_SkipsTheProfile() { // Setup @@ -463,6 +490,7 @@ // Assert Assert.AreEqual(0, skipped); + Assert.AreEqual(1, pipingSoilProfilesReader.Count); Assert.AreEqual(1, result.Count); Assert.AreEqual(-2.1, result[0].Bottom); CollectionAssert.AreEqual(new[] @@ -498,6 +526,7 @@ // Assert Assert.AreEqual(0, skipped); + Assert.AreEqual(26, pipingSoilProfilesReader.Count); Assert.AreEqual(26, result.Count); CollectionAssert.AreEqual(new[] { Index: Ringtoets/Piping/test/Ringtoets.Piping.IO.Test/test-data/PipingSoilProfilesReader/1dprofileWithEmpty1d.soil =================================================================== diff -u Binary files differ