# HG changeset patch
# User Alain Mazy <am@orthanc.team>
# Date 1728399918 -7200
# Node ID 40f236ad829c6c3bbf55265bb0b52cc1f0dfb0eb
# Parent  963945d780d6c82c3eb055137efd4681795ab680
SQLite 3.46 + push NULL values at the end of the order by clauses

diff -r 963945d780d6 -r 40f236ad829c NEWS
--- a/NEWS	Mon Oct 07 18:10:08 2024 +0200
+++ b/NEWS	Tue Oct 08 17:05:18 2024 +0200
@@ -61,6 +61,7 @@
   - added 2 metrics: orthanc_storage_cache_miss_count & orthanc_storage_cache_hit_count 
 * Upgraded dependencies for static builds:
   - curl 8.9.0
+  - SQLite 3.46
 * Added a new fallback when trying to decode a frame: transcode the file using the plugin
   before decoding the frame.  This solves some issues with JP2K Lossy compression:
   https://discourse.orthanc-server.org/t/decoding-displaying-jpeg2000-lossy-images/5117
diff -r 963945d780d6 -r 40f236ad829c OrthancFramework/Resources/CMake/SQLiteConfiguration.cmake
--- a/OrthancFramework/Resources/CMake/SQLiteConfiguration.cmake	Mon Oct 07 18:10:08 2024 +0200
+++ b/OrthancFramework/Resources/CMake/SQLiteConfiguration.cmake	Tue Oct 08 17:05:18 2024 +0200
@@ -37,11 +37,11 @@
 
 
 if (SQLITE_STATIC)
-  SET(SQLITE_SOURCES_DIR ${CMAKE_BINARY_DIR}/sqlite-amalgamation-3270100)
-  SET(SQLITE_MD5 "16717b26358ba81f0bfdac07addc77da")
-  SET(SQLITE_URL "https://orthanc.uclouvain.be/downloads/third-party-downloads/sqlite-amalgamation-3270100.zip")
+  SET(SQLITE_SOURCES_DIR ${CMAKE_BINARY_DIR}/sqlite-amalgamation-3460100)
+  SET(SQLITE_MD5 "1fb0f7ebbee45752098cf453b6dffff3")
+  SET(SQLITE_URL "https://orthanc.uclouvain.be/downloads/third-party-downloads/sqlite-amalgamation-3460100.zip")
 
-  set(ORTHANC_SQLITE_VERSION 3027001)
+  set(ORTHANC_SQLITE_VERSION 3046001)
 
   DownloadPackage(${SQLITE_MD5} ${SQLITE_URL} "${SQLITE_SOURCES_DIR}")
 
diff -r 963945d780d6 -r 40f236ad829c OrthancServer/Sources/Search/ISqlLookupFormatter.cpp
--- a/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp	Mon Oct 07 18:10:08 2024 +0200
+++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp	Tue Oct 08 17:05:18 2024 +0200
@@ -759,7 +759,15 @@
         }
         orderingJoins += orderingJoin;
         
-        std::string orderByField = "order" + boost::lexical_cast<std::string>(counter) + ".value";
+        std::string orderByField;
+
+#if ORTHANC_SQLITE_VERSION < 3030001
+        // this is a way to push NULL values at the end before "NULLS LAST" was introduced:
+        // first filter by 0/1 and then by the column value itself
+        orderByField += "order" + boost::lexical_cast<std::string>(counter) + ".value IS NULL, ";
+#endif
+        orderByField += "order" + boost::lexical_cast<std::string>(counter) + ".value";
+
         if ((*it)->GetDirection() == FindRequest::OrderingDirection_Ascending)
         {
           orderByField += " ASC";
@@ -774,7 +782,12 @@
 
       std::string orderByFieldsString;
       Toolbox::JoinStrings(orderByFieldsString, orderByFields, ", ");
-      ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + ") AS rowNumber";
+
+      ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString;
+#if ORTHANC_SQLITE_VERSION >= 3030001
+      ordering += " NULLS LAST";
+#endif
+      ordering += ") AS rowNumber";
     }
     else
     {