Prevent an error message when browsing views using SQLite 3.36 or above

MKleusberg · MKleusberg · commit f1df40c94849 · 2021-07-06T19:38:37.000+02:00
Starting with SQLite 3.36 you cannot select the rowid column anymore
when selecting from a view. Trying to do so will throw an error.
Previously SQLite would silently return NULL when trying to do that.
This commit mimicks SQLite's previous behaviour.
diff --git a/src/TableBrowser.cpp b/src/TableBrowser.cpp
@@ -753,7 +753,9 @@ void TableBrowser::updateRecordsetLabel()
 
 sqlb::Query TableBrowser::buildQuery(const BrowseDataTableSettings& storedData, const sqlb::ObjectIdentifier& tablename) const
 {
-    sqlb::Query query(tablename);
+    const auto table = db->getTableByName(tablename);
+
+    sqlb::Query query(tablename, table && table->isView());
 
     // Construct a query from the retrieved settings
 
@@ -771,7 +773,6 @@ sqlb::Query TableBrowser::buildQuery(const BrowseDataTableSettings& storedData,
 
     // Display formats
     bool only_defaults = true;
-    const auto table = db->getTableByName(tablename);
     if(table)
     {
         // When there is at least one custom display format, we have to set all columns for the query explicitly here
diff --git a/src/sql/Query.cpp b/src/sql/Query.cpp
@@ -81,7 +81,13 @@ std::string Query::buildQuery(bool withRowid) const
         // If there is only one rowid column, we leave it as is.
         if(m_rowid_columns.size() == 1)
         {
-            selector = sqlb::escapeIdentifier(m_rowid_columns.at(0)) + ",";
+            // As of SQLite 3.36 when selecting rowid from a view SQLite does not return NULL anymore but instead returns "rowid" and throws
+            // an error. To avoid that error (we don't actually care for the value of this column, so the value is not the issue here) we
+            // explicitly select NULL (without any quotes) here.
+            if(m_is_view && !hasCustomRowIdColumn())
+                selector = "NULL,";
+            else
+                selector = sqlb::escapeIdentifier(m_rowid_columns.at(0)) + ",";
         } else {
             selector += "sqlb_make_single_value(";
             for(size_t i=0;i<m_rowid_columns.size();i++)
diff --git a/src/sql/Query.h b/src/sql/Query.h
@@ -57,8 +57,9 @@ class Query
 {
 public:
     Query() {}
-    explicit Query(const sqlb::ObjectIdentifier& table) :
-        m_table(table)
+    explicit Query(const sqlb::ObjectIdentifier& table, bool is_view = false) :
+        m_table(table),
+        m_is_view(is_view)
     {}
 
     void clear();
@@ -98,6 +99,7 @@ class Query
     std::unordered_map<std::string, std::string> m_where;   // TODO The two where variables should be merged into a single variable which ...
     std::vector<std::string> m_global_where;                // ... holds some sort of general tree structure for all sorts of where conditions.
     std::vector<OrderBy> m_sort;
+    bool m_is_view;
 
     std::vector<SelectedColumn>::iterator findSelectedColumnByName(const std::string& name);
     std::vector<SelectedColumn>::const_iterator findSelectedColumnByName(const std::string& name) const;

Original file line number	Diff line number	Diff line change
`@@ -753,7 +753,9 @@ void TableBrowser::updateRecordsetLabel()`
`753`	`753`
`754`	`754`	`sqlb::Query TableBrowser::buildQuery(const BrowseDataTableSettings& storedData, const sqlb::ObjectIdentifier& tablename) const`
`755`	`755`	`{`
`756`		`- sqlb::Query query(tablename);`
	`756`	`+ const auto table = db->getTableByName(tablename);`
	`757`	`+`
	`758`	`+ sqlb::Query query(tablename, table && table->isView());`
`757`	`759`
`758`	`760`	`// Construct a query from the retrieved settings`
`759`	`761`
`@@ -771,7 +773,6 @@ sqlb::Query TableBrowser::buildQuery(const BrowseDataTableSettings& storedData,`
`771`	`773`
`772`	`774`	`// Display formats`
`773`	`775`	`bool only_defaults = true;`
`774`		`- const auto table = db->getTableByName(tablename);`
`775`	`776`	`if(table)`
`776`	`777`	`{`
`777`	`778`	`// When there is at least one custom display format, we have to set all columns for the query explicitly here`