audacity: fix unclean shutdown due to sqlite error

This applies a patch from upstream[1] that replaces the previously used
method of using the SQLITE_DBPAGE virtual table extension.

This fixes audacity hanging when closing it, which leads to it having to
be killed. That results in incompletely saved project files which
triggers the file recovery dialogue on every start.

Fixes #130347.

[1] https://github.com/audacity/audacity/pull/1802

2 files changed, 358 insertions, 0 deletions

diff --git a/pkgs/applications/audio/audacity/0001-Use-a-different-approach-to-estimate-the-disk-space-.patch b/pkgs/applications/audio/audacity/0001-Use-a-different-approach-to-estimate-the-disk-space-.patch
new file mode 100644
index 00000000000..33b7554db44
--- /dev/null
+++ b/pkgs/applications/audio/audacity/0001-Use-a-different-approach-to-estimate-the-disk-space-.patch
@@ -0,0 +1,355 @@
+From deeb435829d73524df851f6f4c2d4be552c99230 Mon Sep 17 00:00:00 2001
+From: Dmitry Vedenko <dmitry@crsib.me>
+Date: Fri, 1 Oct 2021 16:21:22 +0300
+Subject: [PATCH] Use a different approach to estimate the disk space usage
+
+New a approach is a bit less precise, but removes the requirement for the "private" SQLite3 table and allows Audacity to be built against system SQLite3.
+---
+ cmake-proxies/sqlite/CMakeLists.txt |   5 -
+ src/DBConnection.h                  |   4 +-
+ src/ProjectFileIO.cpp               | 269 +++++-----------------------
+ 3 files changed, 44 insertions(+), 234 deletions(-)
+
+diff --git a/cmake-proxies/sqlite/CMakeLists.txt b/cmake-proxies/sqlite/CMakeLists.txt
+index 63d70637c..d7b9b95ef 100644
+--- a/cmake-proxies/sqlite/CMakeLists.txt
++++ b/cmake-proxies/sqlite/CMakeLists.txt
+@@ -19,11 +19,6 @@ list( APPEND INCLUDES
+ 
+ list( APPEND DEFINES
+    PRIVATE
+-      #
+-      # We need the dbpage table for space calculations.
+-      #
+-      SQLITE_ENABLE_DBPAGE_VTAB=1
+-
+       # Can't be set after a WAL mode database is initialized, so change
+       # the default here to ensure all project files get the same page 
+       # size.
+diff --git a/src/DBConnection.h b/src/DBConnection.h
+index 16a7fc9d4..07d3af95e 100644
+--- a/src/DBConnection.h
++++ b/src/DBConnection.h
+@@ -75,8 +75,8 @@ public:
+       LoadSampleBlock,
+       InsertSampleBlock,
+       DeleteSampleBlock,
+-      GetRootPage,
+-      GetDBPage
++      GetSampleBlockSize,
++      GetAllSampleBlocksSize
+    };
+    sqlite3_stmt *Prepare(enum StatementID id, const char *sql);
+ 
+diff --git a/src/ProjectFileIO.cpp b/src/ProjectFileIO.cpp
+index 3b3e2e1fd..c9bc45af4 100644
+--- a/src/ProjectFileIO.cpp
++++ b/src/ProjectFileIO.cpp
+@@ -35,6 +35,7 @@ Paul Licameli split from AudacityProject.cpp
+ #include "widgets/ProgressDialog.h"
+ #include "wxFileNameWrapper.h"
+ #include "xml/XMLFileReader.h"
++#include "MemoryX.h"`
+ 
+ #undef NO_SHM
+ #if !defined(__WXMSW__)
+@@ -2357,255 +2358,69 @@ int64_t ProjectFileIO::GetTotalUsage()
+ }
+ 
+ //
+-// Returns the amount of disk space used by the specified sample blockid or all
+-// of the sample blocks if the blockid is 0.  It does this by using the raw SQLite
+-// pages available from the "sqlite_dbpage" virtual table to traverse the SQLite
+-// table b-tree described here:  https://www.sqlite.org/fileformat.html
++// Returns the estimation of disk space used by the specified sample blockid or all
++// of the sample blocks if the blockid is 0. This does not include small overhead
++// of the internal SQLite structures, only the size used by the data
+ //
+ int64_t ProjectFileIO::GetDiskUsage(DBConnection &conn, SampleBlockID blockid /* = 0 */)
+ {
+-   // Information we need to track our travels through the b-tree
+-   typedef struct
+-   {
+-      int64_t pgno;
+-      int currentCell;
+-      int numCells;
+-      unsigned char data[65536];
+-   } page;
+-   std::vector<page> stack;
+-
+-   int64_t total = 0;
+-   int64_t found = 0;
+-   int64_t right = 0;
+-   int rc;
++   sqlite3_stmt* stmt = nullptr;
+ 
+-   // Get the rootpage for the sampleblocks table.
+-   sqlite3_stmt *stmt =
+-      conn.Prepare(DBConnection::GetRootPage,
+-                    "SELECT rootpage FROM sqlite_master WHERE tbl_name = 'sampleblocks';");
+-   if (stmt == nullptr || sqlite3_step(stmt) != SQLITE_ROW)
++   if (blockid == 0)
+    {
+-      return 0;
+-   }
+-
+-   // And store it in our first stack frame
+-   stack.push_back({sqlite3_column_int64(stmt, 0)});
++      static const char* statement =
++R"(SELECT 
++	sum(length(blockid) + length(sampleformat) + 
++	length(summin) + length(summax) + length(sumrms) + 
++	length(summary256) + length(summary64k) +
++	length(samples))
++FROM sampleblocks;)";
+ 
+-   // All done with the statement
+-   sqlite3_clear_bindings(stmt);
+-   sqlite3_reset(stmt);
+-
+-   // Prepare/retrieve statement to read raw database page
+-   stmt = conn.Prepare(DBConnection::GetDBPage,
+-      "SELECT data FROM sqlite_dbpage WHERE pgno = ?1;");
+-   if (stmt == nullptr)
+-   {
+-      return 0;
++      stmt = conn.Prepare(DBConnection::GetAllSampleBlocksSize, statement);
+    }
+-
+-   // Traverse the b-tree until we've visited all of the leaf pages or until
+-   // we find the one corresponding to the passed in sample blockid. Because we
+-   // use an integer primary key for the sampleblocks table, the traversal will
+-   // be in ascending blockid sequence.
+-   do
++   else
+    {
+-      // Acces the top stack frame
+-      page &pg = stack.back();
++      static const char* statement =
++R"(SELECT 
++	length(blockid) + length(sampleformat) + 
++	length(summin) + length(summax) + length(sumrms) + 
++	length(summary256) + length(summary64k) +
++	length(samples)
++FROM sampleblocks WHERE blockid = ?1;)";
+ 
+-      // Read the page from the sqlite_dbpage table if it hasn't yet been loaded
+-      if (pg.numCells == 0)
+-      {
+-         // Bind the page number
+-         sqlite3_bind_int64(stmt, 1, pg.pgno);
++      stmt = conn.Prepare(DBConnection::GetSampleBlockSize, statement);
++   }
+ 
+-         // And retrieve the page
+-         if (sqlite3_step(stmt) != SQLITE_ROW)
++   auto cleanup = finally(
++      [stmt]() {
++         // Clear statement bindings and rewind statement
++         if (stmt != nullptr)
+          {
+-            // REVIEW: Likely harmless failure - says size is zero on
+-            // this error.
+-            // LLL: Yea, but not much else we can do.
+-            return 0;
++            sqlite3_clear_bindings(stmt);
++            sqlite3_reset(stmt);
+          }
++      });
+ 
+-         // Copy the page content to the stack frame
+-         memcpy(&pg.data,
+-                sqlite3_column_blob(stmt, 0),
+-                sqlite3_column_bytes(stmt, 0));
+-
+-         // And retrieve the total number of cells within it
+-         pg.numCells = get2(&pg.data[3]);
+-
+-         // Reset statement for next usage
+-         sqlite3_clear_bindings(stmt);
+-         sqlite3_reset(stmt);
+-      }
+-
+-      //wxLogDebug("%*.*spgno %lld currentCell %d numCells %d", (stack.size() - 1) * 2, (stack.size() - 1) * 2, "", pg.pgno, pg.currentCell, pg.numCells);
+-
+-      // Process an interior table b-tree page
+-      if (pg.data[0] == 0x05)
+-      {
+-         // Process the next cell if we haven't examined all of them yet
+-         if (pg.currentCell < pg.numCells)
+-         {
+-            // Remember the right-most leaf page number.
+-            right = get4(&pg.data[8]);
+-
+-            // Iterate over the cells.
+-            //
+-            // If we're not looking for a specific blockid, then we always push the
+-            // target page onto the stack and leave the loop after a single iteration.
+-            //
+-            // Otherwise, we match the blockid against the highest integer key contained
+-            // within the cell and if the blockid falls within the cell, we stack the
+-            // page and stop the iteration.
+-            //
+-            // In theory, we could do a binary search for a specific blockid here, but
+-            // because our sample blocks are always large, we will get very few cells
+-            // per page...usually 6 or less.
+-            //
+-            // In both cases, the stacked page can be either an internal or leaf page.
+-            bool stacked = false;
+-            while (pg.currentCell < pg.numCells)
+-            {
+-               // Get the offset to this cell using the offset in the cell pointer
+-               // array.
+-               //
+-               // The cell pointer array starts immediately after the page header
+-               // at offset 12 and the retrieved offset is from the beginning of
+-               // the page.
+-               int celloff = get2(&pg.data[12 + (pg.currentCell * 2)]);
+-
+-               // Bump to the next cell for the next iteration.
+-               pg.currentCell++;
+-
+-               // Get the page number this cell describes
+-               int pagenum = get4(&pg.data[celloff]);
+-
+-               // And the highest integer key, which starts at offset 4 within the cell.
+-               int64_t intkey = 0;
+-               get_varint(&pg.data[celloff + 4], &intkey);
+-
+-               //wxLogDebug("%*.*sinternal - right %lld celloff %d pagenum %d intkey %lld", (stack.size() - 1) * 2, (stack.size() - 1) * 2, " ", right, celloff, pagenum, intkey);
+-
+-               // Stack the described page if we're not looking for a specific blockid
+-               // or if this page contains the given blockid.
+-               if (!blockid || blockid <= intkey)
+-               {
+-                  stack.push_back({pagenum, 0, 0});
+-                  stacked = true;
+-                  break;
+-               }
+-            }
+-
+-            // If we pushed a new page onto the stack, we need to jump back up
+-            // to read the page
+-            if (stacked)
+-            {
+-               continue;
+-            }
+-         }
++   if (blockid != 0)
++   {
++      int rc = sqlite3_bind_int64(stmt, 1, blockid);
+ 
+-         // We've exhausted all the cells with this page, so we stack the right-most
+-         // leaf page.  Ensure we only process it once.
+-         if (right)
+-         {
+-            stack.push_back({right, 0, 0});
+-            right = 0;
+-            continue;
+-         }
+-      }
+-      // Process a leaf table b-tree page
+-      else if (pg.data[0] == 0x0d)
++      if (rc != SQLITE_OK)
+       {
+-         // Iterate over the cells
+-         //
+-         // If we're not looking for a specific blockid, then just accumulate the
+-         // payload sizes. We will be reading every leaf page in the sampleblocks
+-         // table.
+-         //
+-         // Otherwise we break out when we find the matching blockid. In this case,
+-         // we only ever look at 1 leaf page.
+-         bool stop = false;
+-         for (int i = 0; i < pg.numCells; i++)
+-         {
+-            // Get the offset to this cell using the offset in the cell pointer
+-            // array.
+-            //
+-            // The cell pointer array starts immediately after the page header
+-            // at offset 8 and the retrieved offset is from the beginning of
+-            // the page.
+-            int celloff = get2(&pg.data[8 + (i * 2)]);
+-
+-            // Get the total payload size in bytes of the described row.
+-            int64_t payload = 0;
+-            int digits = get_varint(&pg.data[celloff], &payload);
+-
+-            // Get the integer key for this row.
+-            int64_t intkey = 0;
+-            get_varint(&pg.data[celloff + digits], &intkey);
+-
+-            //wxLogDebug("%*.*sleaf - celloff %4d intkey %lld payload %lld", (stack.size() - 1) * 2, (stack.size() - 1) * 2, " ", celloff, intkey, payload);
+-
+-            // Add this payload size to the total if we're not looking for a specific
+-            // blockid
+-            if (!blockid)
+-            {
+-               total += payload;
+-            }
+-            // Otherwise, return the payload size for a matching row
+-            else if (blockid == intkey)
+-            {
+-               return payload;
+-            }
+-         }
++         conn.ThrowException(false);
+       }
++   }
+ 
+-      // Done with the current branch, so pop back up to the previous one (if any)
+-      stack.pop_back();
+-   } while (!stack.empty());
+-
+-   // Return the total used for all sample blocks
+-   return total;
+-}
+-
+-// Retrieves a 2-byte big-endian integer from the page data
+-unsigned int ProjectFileIO::get2(const unsigned char *ptr)
+-{
+-   return (ptr[0] << 8) | ptr[1];
+-}
+-
+-// Retrieves a 4-byte big-endian integer from the page data
+-unsigned int ProjectFileIO::get4(const unsigned char *ptr)
+-{
+-   return ((unsigned int) ptr[0] << 24) |
+-          ((unsigned int) ptr[1] << 16) |
+-          ((unsigned int) ptr[2] << 8)  |
+-          ((unsigned int) ptr[3]);
+-}
+-
+-// Retrieves a variable length integer from the page data. Returns the
+-// number of digits used to encode the integer and the stores the
+-// value at the given location.
+-int ProjectFileIO::get_varint(const unsigned char *ptr, int64_t *out)
+-{
+-   int64_t val = 0;
+-   int i;
++   int rc = sqlite3_step(stmt);
+ 
+-   for (i = 0; i < 8; ++i)
++   if (rc != SQLITE_ROW)
+    {
+-      val = (val << 7) + (ptr[i] & 0x7f);
+-      if ((ptr[i] & 0x80) == 0)
+-      {
+-         *out = val;
+-         return i + 1;
+-      }
++      conn.ThrowException(false);
+    }
+ 
+-   val = (val << 8) + (ptr[i] & 0xff);
+-   *out = val;
++   const int64_t size = sqlite3_column_int64(stmt, 0);
+ 
+-   return 9;
++   return size;
+ }
+ 
+ InvisibleTemporaryProject::InvisibleTemporaryProject()
+-- 
+2.33.1
+
diff --git a/pkgs/applications/audio/audacity/default.nix b/pkgs/applications/audio/audacity/default.nix
index 0832bb1dd96..bd218d4b2c7 100644
--- a/pkgs/applications/audio/audacity/default.nix
+++ b/pkgs/applications/audio/audacity/default.nix
@@ -84,6 +84,9 @@ stdenv.mkDerivation rec {
       sha256 = "0zp2iydd46analda9cfnbmzdkjphz5m7dynrdj5qdnmq6j3px9fw";
       name = "audacity_xdg_paths.patch";
     })
+    # This is required to make audacity work with nixpkgs’ sqlite
+    # https://github.com/audacity/audacity/pull/1802 rebased onto 3.0.2
+    ./0001-Use-a-different-approach-to-estimate-the-disk-space-.patch
   ];
 
   postPatch = ''