Mailing-List: contact commits-help@subversion.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@subversion.apache.org
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r1817042 - in /subversion/trunk: ./ build/generator/
 subversion/libsvn_fs_fs/ subversion/libsvn_subr/ subversion/tests/cmdline/
Date: Sun, 03 Dec 2017 17:28:29 -0000
To: commits@subversion.apache.org
From: kotkov@apache.org
Message-Id: <20171203172832.C1EE63A0098@svn01-us-west.apache.org>
archived-at: Sun, 03 Dec 2017 17:28:36 -0000

Author: kotkov
Date: Sun Dec  3 17:28:29 2017
New Revision: 1817042

URL: http://svn.apache.org/viewvc?rev=1817042&view=rev
Log:
fsfs: Use the `WITHOUT ROWID` optimization for rep-cache.db in format 8.

This optimization, introduced in SQLite 3.8.2, works well for tables that
have non-integer primary keys, such as

    hash TEXT NOT NULL PRIMARY KEY

in the rep-cache.db.  (See the https://sqlite.org/withoutrowid.html article
for additional details.)

A quick experiment showed a reduction of the on-disk size of the database
by ~1.75x.  The lookups should also be faster, both due to the reduced
database size and due to the lesser amount of internal bsearches.  This
should improve the times of new commits and `svnadmin load`, especially
for large repositories that also have large rep-cache.db files.

In order to maintain compatibility, since SQLite versions prior to 3.8.2
do not support this statement, we only start using it for fsfs format 8
repositories and simultaneously bump the minimal required SQLite version
from 3.7.12 (May 2012) to 3.8.2 (December 2013).  The last step ensures that
all binaries compiled to support format 8 can work with the tables with
this optimization.  Also, as the various scripts have both the minimal
and recommended (3.7.15.1) SQLite versions, we bump the recommended
version to the last 3.8.x patch version, which is 3.8.11.1.

* subversion/libsvn_fs_fs/rep-cache-db.sql
  (STMT_CREATE_SCHEMA): Rename this ...
  (STMT_CREATE_SCHEMA_V1): ...to this.
  (STMT_CREATE_SCHEMA_V2): New, enables `WITHOUT ROWID` optimization.
  (STMT_GET_REP, STMT_SET_REP, STMT_GET_REPS_FOR_RANGE,
   STMT_GET_MAX_REV, STMT_DEL_REPS_YOUNGER_THAN_REV,
   STMT_LOCK_REP, STMT_UNLOCK_REP):
   Note that these statements work for both V1 and V2 schemas.

* subversion/libsvn_fs_fs/fs.h
  (SVN_FS_FS__MIN_REP_CACHE_SCHEMA_V2_FORMAT): New.

* subversion/libsvn_fs_fs/rep-cache.c
  (REP_CACHE_SCHEMA_FORMAT): Remove.
  (open_rep_cache): Select between creating a V1 or V2 schemas based
   on the format of the filesystem.

* subversion/libsvn_subr/sqlite.c
  (): Bump minimum required SQLite version to 3.8.2.

* subversion/tests/cmdline/svnadmin_tests.py
  (check_hotcopy_fsfs_fsx): Check if the Python's built-in SQLite version
   is enough to interpret the schema of rep-cache.db, and skip the check
   if it's not.

* build/generator/gen_win_dependencies.py
  (_find_sqlite): Bump minimum required SQLite version to 3.8.2.

* configure.ac
  (SQLITE_MINIMUM_VER): Bump to 3.8.2.
  (SQLITE_RECOMMENDED_VER): Bump to 3.8.11.1.
  (SQLITE_RECOMMENDED_VER_REL_YEAR): New, required to construct the
   download URL which includes the release year for the newer SQLite
   amalgamation versions.
  (SQLITE_URL): Update the download URL.

* get-deps.sh
  (SQLITE_VERSION): Bump to 3.8.11.1.
  (SQLITE_VERSION_REL_YEAR): New.
  (get_sqlite): Update the download URL that includes the release year
   for the newer SQLite amalgamation versions.

* INSTALL
  (C.12.SQLite): Bump minimum required SQLite version to 3.8.2.
  (E.1.Prerequisites): Bump the minimum and recommended SQLite versions.

Modified:
    subversion/trunk/INSTALL
    subversion/trunk/build/generator/gen_win_dependencies.py
    subversion/trunk/configure.ac
    subversion/trunk/get-deps.sh
    subversion/trunk/subversion/libsvn_fs_fs/fs.h
    subversion/trunk/subversion/libsvn_fs_fs/rep-cache-db.sql
    subversion/trunk/subversion/libsvn_fs_fs/rep-cache.c
    subversion/trunk/subversion/libsvn_subr/sqlite.c
    subversion/trunk/subversion/tests/cmdline/svnadmin_tests.py

Modified: subversion/trunk/INSTALL
URL: http://svn.apache.org/viewvc/subversion/trunk/INSTALL?rev=1817042&r1=1817041&r2=1817042&view=diff
==============================================================================
--- subversion/trunk/INSTALL (original)
+++ subversion/trunk/INSTALL Sun Dec  3 17:28:29 2017
@@ -430,7 +430,7 @@ I.    INTRODUCTION
 
       12. SQLite  (REQUIRED)
 
-      Subversion requires SQLite version 3.7.12 or above.  You can meet this
+      Subversion requires SQLite version 3.8.2 or above.  You can meet this
       dependency several ways:
         * Use an SQLite amalgamation file.
         * Specify an SQLite installation to use.
@@ -727,8 +727,8 @@ II.   INSTALLATION
         1.3 or later (1.2 for apr-iconv). If you are building from a Subversion
         checkout and have not downloaded Apache 2, then get these 3 libraries
         from https://www.apache.org/dist/apr/.
-      * SQLite 3.7.12 or higher from https://www.sqlite.org/download.html
-        (3.8.0 or higher recommended)
+      * SQLite 3.8.2 or higher from https://www.sqlite.org/download.html
+        (3.8.11.1 or higher recommended)
       * ZLib 1.2 or higher is required and can be obtained from
         http://www.zlib.net/
       * Either a Subversion client binary from

Modified: subversion/trunk/build/generator/gen_win_dependencies.py
URL: http://svn.apache.org/viewvc/subversion/trunk/build/generator/gen_win_dependencies.py?rev=1817042&r1=1817041&r2=1817042&view=diff
==============================================================================
--- subversion/trunk/build/generator/gen_win_dependencies.py (original)
+++ subversion/trunk/build/generator/gen_win_dependencies.py Sun Dec  3 17:28:29 2017
@@ -1383,7 +1383,7 @@ class GenDependenciesBase(gen_base.Gener
   def _find_sqlite(self, show_warnings):
     "Find the Sqlite library and version"
 
-    minimal_sqlite_version = (3, 7, 12)
+    minimal_sqlite_version = (3, 8, 2)
 
     # For SQLite we support 3 scenarios:
     # - Installed in standard directory layout

Modified: subversion/trunk/configure.ac
URL: http://svn.apache.org/viewvc/subversion/trunk/configure.ac?rev=1817042&r1=1817041&r2=1817042&view=diff
==============================================================================
--- subversion/trunk/configure.ac (original)
+++ subversion/trunk/configure.ac Sun Dec  3 17:28:29 2017
@@ -152,9 +152,11 @@ SVN_FIND_APACHE(20051115, $apache_whitel
 dnl Search for SQLite.  If you change SQLITE_URL from a .zip to
 dnl something else also update build/ac-macros/sqlite.m4 to reflect
 dnl the correct command to unpack the downloaded file.
-SQLITE_MINIMUM_VER="3.7.12"
-SQLITE_RECOMMENDED_VER="3.7.15.1"
-SQLITE_URL="http://www.sqlite.org/sqlite-amalgamation-$(printf %d%02d%02d%02d $(echo ${SQLITE_RECOMMENDED_VER} | sed -e 's/\./ /g')).zip"
+SQLITE_MINIMUM_VER="3.8.2"
+SQLITE_RECOMMENDED_VER="3.8.11.1"
+dnl Used to construct the SQLite download URL.
+SQLITE_RECOMMENDED_VER_REL_YEAR="2015"
+SQLITE_URL="http://www.sqlite.org/$SQLITE_RECOMMENDED_VER_REL_YEAR/sqlite-amalgamation-$(printf %d%02d%02d%02d $(echo ${SQLITE_RECOMMENDED_VER} | sed -e 's/\./ /g')).zip"
 
 SVN_LIB_SQLITE(${SQLITE_MINIMUM_VER}, ${SQLITE_RECOMMENDED_VER},
                ${SQLITE_URL})

Modified: subversion/trunk/get-deps.sh
URL: http://svn.apache.org/viewvc/subversion/trunk/get-deps.sh?rev=1817042&r1=1817041&r2=1817042&view=diff
==============================================================================
--- subversion/trunk/get-deps.sh (original)
+++ subversion/trunk/get-deps.sh Sun Dec  3 17:28:29 2017
@@ -35,7 +35,9 @@ APR_VERSION=${APR_VERSION:-"1.4.6"}
 APU_VERSION=${APU_VERSION:-"1.5.1"}
 SERF_VERSION=${SERF_VERSION:-"1.3.8"}
 ZLIB_VERSION=${ZLIB_VERSION:-"1.2.8"}
-SQLITE_VERSION=${SQLITE_VERSION:-"3.7.15.1"}
+SQLITE_VERSION=${SQLITE_VERSION:-"3.8.11.1"}
+# Used to construct the SQLite download URL.
+SQLITE_VERSION_REL_YEAR=2015
 GTEST_VERSION=${GMOCK_VERSION:-"1.7.0"}
 GMOCK_VERSION=${GMOCK_VERSION:-"1.7.0"}
 HTTPD_VERSION=${HTTPD_VERSION:-"2.4.10"}
@@ -116,7 +118,7 @@ get_sqlite() {
     test -d $BASEDIR/sqlite-amalgamation && return
 
     cd $TEMPDIR
-    $HTTP_FETCH http://www.sqlite.org/$SQLITE.zip
+    $HTTP_FETCH http://www.sqlite.org/$SQLITE_VERSION_REL_YEAR/$SQLITE.zip
     cd $BASEDIR
 
     unzip -q $TEMPDIR/$SQLITE.zip

Modified: subversion/trunk/subversion/libsvn_fs_fs/fs.h
URL: http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_fs_fs/fs.h?rev=1817042&r1=1817041&r2=1817042&view=diff
==============================================================================
--- subversion/trunk/subversion/libsvn_fs_fs/fs.h (original)
+++ subversion/trunk/subversion/libsvn_fs_fs/fs.h Sun Dec  3 17:28:29 2017
@@ -196,6 +196,10 @@ extern "C" {
  */
 #define SVN_FS_FS__MIN_REP_STRING_OPTIONAL_VALUES_FORMAT 8
 
+ /* The minimum format number that supports V2 schema of the rep-cache.db
+    database. */
+#define SVN_FS_FS__MIN_REP_CACHE_SCHEMA_V2_FORMAT 8
+
 /* On most operating systems apr implements file locks per process, not
    per file.  On Windows apr implements the locking as per file handle
    locks, so we don't have to add our own mutex for just in-process

Modified: subversion/trunk/subversion/libsvn_fs_fs/rep-cache-db.sql
URL: http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_fs_fs/rep-cache-db.sql?rev=1817042&r1=1817041&r2=1817042&view=diff
==============================================================================
--- subversion/trunk/subversion/libsvn_fs_fs/rep-cache-db.sql (original)
+++ subversion/trunk/subversion/libsvn_fs_fs/rep-cache-db.sql Sun Dec  3 17:28:29 2017
@@ -21,7 +21,7 @@
  * ====================================================================
  */
 
--- STMT_CREATE_SCHEMA
+-- STMT_CREATE_SCHEMA_V1
 /* A table mapping representation hashes to locations in a rev file. */
 CREATE TABLE rep_cache (
   hash TEXT NOT NULL PRIMARY KEY,
@@ -33,36 +33,63 @@ CREATE TABLE rep_cache (
 
 PRAGMA USER_VERSION = 1;
 
+-- STMT_CREATE_SCHEMA_V2
+/* A table mapping representation hashes to locations in a rev file.
+   Same as in V1 schema, except that it uses the `WITHOUT ROWID` optimization:
+   https://sqlite.org/withoutrowid.html
+
+   Note that this optimization is only supported starting from SQLite version
+   3.8.2 (2013-12-06).  To keep compatibility with existing binaries, it is
+   only used for newer filesystem formats that were released together with
+   bumping the minimum required SQLite version.
+ */
+CREATE TABLE rep_cache (
+  hash TEXT NOT NULL PRIMARY KEY,
+  revision INTEGER NOT NULL,
+  offset INTEGER NOT NULL,
+  size INTEGER NOT NULL,
+  expanded_size INTEGER NOT NULL
+  ) WITHOUT ROWID;
+
+PRAGMA USER_VERSION = 2;
 
 -- STMT_GET_REP
+/* Works for both V1 and V2 schemas. */
 SELECT revision, offset, size, expanded_size
 FROM rep_cache
 WHERE hash = ?1
 
 -- STMT_SET_REP
+/* Works for both V1 and V2 schemas. */
 INSERT OR FAIL INTO rep_cache (hash, revision, offset, size, expanded_size)
 VALUES (?1, ?2, ?3, ?4, ?5)
 
 -- STMT_GET_REPS_FOR_RANGE
+/* Works for both V1 and V2 schemas. */
 SELECT hash, revision, offset, size, expanded_size
 FROM rep_cache
 WHERE revision >= ?1 AND revision <= ?2
 
 -- STMT_GET_MAX_REV
+/* Works for both V1 and V2 schemas. */
 SELECT MAX(revision)
 FROM rep_cache
 
 -- STMT_DEL_REPS_YOUNGER_THAN_REV
+/* Works for both V1 and V2 schemas. */
 DELETE FROM rep_cache
 WHERE revision > ?1
 
 /* An INSERT takes an SQLite reserved lock that prevents other writes
    but doesn't block reads.  The incomplete transaction means that no
    permanent change is made to the database and the transaction is
-   removed when the database is closed.  */
+   removed when the database is closed.
+
+   Works for both V1 and V2 schemas.  */
 -- STMT_LOCK_REP
 BEGIN TRANSACTION;
 INSERT INTO rep_cache VALUES ('dummy', 0, 0, 0, 0)
 
 -- STMT_UNLOCK_REP
+/* Works for both V1 and V2 schemas. */
 ROLLBACK TRANSACTION;

Modified: subversion/trunk/subversion/libsvn_fs_fs/rep-cache.c
URL: http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_fs_fs/rep-cache.c?rev=1817042&r1=1817041&r2=1817042&view=diff
==============================================================================
--- subversion/trunk/subversion/libsvn_fs_fs/rep-cache.c (original)
+++ subversion/trunk/subversion/libsvn_fs_fs/rep-cache.c Sun Dec  3 17:28:29 2017
@@ -36,9 +36,6 @@
 
 #include "rep-cache-db.h"
 
-/* A few magic values */
-#define REP_CACHE_SCHEMA_FORMAT   1
-
 REP_CACHE_DB_SQL_DECLARE_STATEMENTS(statements);
 
 
@@ -102,13 +99,17 @@ open_rep_cache(void *baton,
 
   SVN_SQLITE__ERR_CLOSE(svn_sqlite__read_schema_version(&version, sdb, pool),
                         sdb);
-  if (version < REP_CACHE_SCHEMA_FORMAT)
+  /* If we have an uninitialized database, go ahead and create the schema. */
+  if (version <= 0)
     {
-      /* Must be 0 -- an uninitialized (no schema) database. Create
-         the schema. Results in schema version of 1.  */
-      SVN_SQLITE__ERR_CLOSE(svn_sqlite__exec_statements(sdb,
-                                                        STMT_CREATE_SCHEMA),
-                            sdb);
+      int stmt;
+
+      if (ffd->format >= SVN_FS_FS__MIN_REP_CACHE_SCHEMA_V2_FORMAT)
+        stmt = STMT_CREATE_SCHEMA_V2;
+      else
+        stmt = STMT_CREATE_SCHEMA_V1;
+
+      SVN_SQLITE__ERR_CLOSE(svn_sqlite__exec_statements(sdb, stmt), sdb);
     }
 
   /* This is used as a flag that the database is available so don't

Modified: subversion/trunk/subversion/libsvn_subr/sqlite.c
URL: http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_subr/sqlite.c?rev=1817042&r1=1817041&r2=1817042&view=diff
==============================================================================
--- subversion/trunk/subversion/libsvn_subr/sqlite.c (original)
+++ subversion/trunk/subversion/libsvn_subr/sqlite.c Sun Dec  3 17:28:29 2017
@@ -65,8 +65,8 @@ extern int (*const svn_sqlite3__api_conf
 #  include <sqlite3.h>
 #endif
 
-#if !SQLITE_VERSION_AT_LEAST(3,7,12)
-#error SQLite is too old -- version 3.7.12 is the minimum required version
+#if !SQLITE_VERSION_AT_LEAST(3,8,2)
+#error SQLite is too old -- version 3.8.2 is the minimum required version
 #endif
 
 #ifndef SQLITE_DETERMINISTIC

Modified: subversion/trunk/subversion/tests/cmdline/svnadmin_tests.py
URL: http://svn.apache.org/viewvc/subversion/trunk/subversion/tests/cmdline/svnadmin_tests.py?rev=1817042&r1=1817041&r2=1817042&view=diff
==============================================================================
--- subversion/trunk/subversion/tests/cmdline/svnadmin_tests.py (original)
+++ subversion/trunk/subversion/tests/cmdline/svnadmin_tests.py Sun Dec  3 17:28:29 2017
@@ -129,6 +129,15 @@ def check_hotcopy_fsfs_fsx(src, dst):
         if src_file == 'rep-cache.db':
           db1 = svntest.sqlite3.connect(src_path)
           db2 = svntest.sqlite3.connect(dst_path)
+          schema1 = db1.execute("pragma user_version").fetchone()[0]
+          schema2 = db2.execute("pragma user_version").fetchone()[0]
+          if schema1 != schema2:
+            raise svntest.Failure("rep-cache schema differs: '%s' vs. '%s'"
+                                  % (schema1, schema2))
+          # Can't test newer rep-cache schemas with an old built-in SQLite.
+          if schema1 >= 2 and svntest.sqlite3.sqlite_version_info < (3, 8, 2):
+            continue
+
           rows1 = []
           rows2 = []
           for row in db1.execute("select * from rep_cache order by hash"):