Merge pull request #150115 from sbruder/fix-audacity-sqlite

audacity: fix unclean shutdown due to sqlite error

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 55d5383a819..0e4afa4af7c 100644
--- a/pkgs/applications/audio/audacity/default.nix
+++ b/pkgs/applications/audio/audacity/default.nix
@@ -100,6 +100,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 = ''