subversion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhuij...@apache.org
Subject svn commit: r1142059 - in /subversion/trunk/subversion/libsvn_wc: wc-queries.sql wc_db.c
Date Fri, 01 Jul 2011 20:04:03 GMT
Author: rhuijben
Date: Fri Jul  1 20:04:03 2011
New Revision: 1142059

URL: http://svn.apache.org/viewvc?rev=1142059&view=rev
Log:
Replace most of the LIKE in our wc-ng queries on NODES with > and < operations
that can use an index. The way we used LIKE SQLite would always performs a
table scan.

* subversion/libsvn_wc/wc-queries.sql
  (STMT_DELETE_ACTUAL_NODE_RECURSIVE,
   STMT_DELETE_ACTUAL_NODE_LEAVING_CHANGELIST,
   STMT_DELETE_ACTUAL_NODE_LEAVING_CHANGELIST_RECURSIVE,
   STMT_CLEAR_ACTUAL_NODE_LEAVING_CHANGELIST_RECURSIVE,
   STMT_DELETE_WC_LOCK_ORPHAN_RECURSIVE,
   STMT_UPDATE_OP_DEPTH_INCREASE_RECURSIVE,
   STMT_HAS_ABSENT_NODES,
   STMT_SELECT_ALL_ABSENT_NODES,
   STMT_SELECT_MIN_MAX_REVISIONS,
   STMT_HAS_SPARSE_NODES,
   STMT_SUBTREE_HAS_TREE_MODIFICATIONS,
   STMT_SUBTREE_HAS_PROP_MODIFICATIONS,
   STMT_SELECT_BASE_FILES_RECURSIVE): Use indexable queries instead of LIKE.
     Handle cases where only descendants should be handled.

* subversion/libsvn_wc/wc_db.c
  (catch_copy_of_absent,
   op_revert_txn,
   op_revert_recursive_txn,
   remove_node_txn,
   op_delete_txn,
   get_min_max_revisions,
   is_sparse_checkout_internal,
   svn_wc__db_get_absent_subtrees,
   has_local_mods): Update query users.

Modified:
    subversion/trunk/subversion/libsvn_wc/wc-queries.sql
    subversion/trunk/subversion/libsvn_wc/wc_db.c

Modified: subversion/trunk/subversion/libsvn_wc/wc-queries.sql
URL: http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_wc/wc-queries.sql?rev=1142059&r1=1142058&r2=1142059&view=diff
==============================================================================
--- subversion/trunk/subversion/libsvn_wc/wc-queries.sql (original)
+++ subversion/trunk/subversion/libsvn_wc/wc-queries.sql Fri Jul  1 20:04:03 2011
@@ -581,7 +581,10 @@ WHERE wc_id = ?1 AND local_relpath = ?2
 
 -- STMT_DELETE_ACTUAL_NODE_RECURSIVE
 DELETE FROM actual_node
-WHERE wc_id = ?1 AND (local_relpath = ?2 OR local_relpath LIKE ?3 ESCAPE '#')
+WHERE wc_id = ?1
+  AND (?2 = ''
+       OR local_relpath = ?2
+       OR (local_relpath > ?2 || '/' AND local_relpath < ?2 || '0'))
 
 -- STMT_DELETE_ACTUAL_NODE_WITHOUT_CONFLICT
 DELETE FROM actual_node
@@ -590,21 +593,23 @@ WHERE wc_id = ?1 AND local_relpath = ?2
 
 -- STMT_DELETE_ACTUAL_NODE_LEAVING_CHANGELIST
 DELETE FROM actual_node
-WHERE wc_id = ?1 AND local_relpath = ?2
-      AND (changelist IS NULL
-           OR local_relpath NOT IN (SELECT local_relpath FROM nodes_current
-                                     WHERE wc_id  = ?1 AND local_relpath = ?2
-                                       AND kind = 'file'))
+WHERE wc_id = ?1
+  AND local_relpath = ?2
+  AND (changelist IS NULL
+       OR NOT EXISTS (SELECT 1 FROM nodes_current c
+                      WHERE c.wc_id = ?1 AND c.local_relpath = local_relpath
+                        AND kind = 'file'))
 
 -- STMT_DELETE_ACTUAL_NODE_LEAVING_CHANGELIST_RECURSIVE
 DELETE FROM actual_node
-WHERE wc_id = ?1 AND (local_relpath = ?2 OR local_relpath LIKE ?3 ESCAPE '#')
-      AND (changelist IS NULL
-           OR local_relpath NOT IN (SELECT local_relpath FROM nodes_current
-                                    WHERE wc_id = ?1
-                                      AND (local_relpath = ?2
-                                           OR local_relpath LIKE ?3 ESCAPE '#')
-                                      AND kind = 'file'))
+WHERE wc_id = ?1
+  AND (?2 = ''
+       OR local_relpath = ?2
+       OR (local_relpath > ?2 || '/' AND local_relpath < ?2 || '0'))
+  AND (changelist IS NULL
+       OR NOT EXISTS (SELECT 1 FROM nodes_current c
+                      WHERE c.wc_id = ?1 AND c.local_relpath = local_relpath
+                        AND kind = 'file'))
 
 -- STMT_CLEAR_ACTUAL_NODE_LEAVING_CHANGELIST
 UPDATE actual_node
@@ -632,7 +637,10 @@ SET properties = NULL,
     older_checksum = NULL,
     left_checksum = NULL,
     right_checksum = NULL
-WHERE wc_id = ?1 AND (local_relpath = ?2 OR local_relpath LIKE ?3 ESCAPE '#')
+WHERE wc_id = ?1
+  AND (?2 = ''
+       OR local_relpath = ?2
+       OR (local_relpath > ?2 || '/' AND local_relpath < ?2 || '0'))
 
 -- STMT_UPDATE_NODE_BASE_DEPTH
 UPDATE nodes SET depth = ?3
@@ -762,10 +770,12 @@ AND NOT EXISTS (SELECT 1 FROM nodes
 -- STMT_DELETE_WC_LOCK_ORPHAN_RECURSIVE
 DELETE FROM wc_lock
 WHERE wc_id = ?1
-AND (local_dir_relpath = ?2 OR local_dir_relpath LIKE ?3 ESCAPE '#')
-AND NOT EXISTS (SELECT 1 FROM nodes
-                 WHERE nodes.wc_id = ?1
-                   AND nodes.local_relpath = wc_lock.local_dir_relpath)
+  AND (?2 = ''
+       OR local_dir_relpath = ?2
+       OR (local_dir_relpath > ?2 || '/' AND local_dir_relpath < ?2 || '0'))
+  AND NOT EXISTS (SELECT 1 FROM nodes
+                   WHERE nodes.wc_id = ?1
+                     AND nodes.local_relpath = wc_lock.local_dir_relpath)
 
 -- STMT_APPLY_CHANGES_TO_BASE_NODE
 /* translated_size and last_mod_time are not mentioned here because they will
@@ -823,7 +833,10 @@ WHERE wc_id = ?1 AND local_relpath = ?2 
 
 -- STMT_UPDATE_OP_DEPTH_INCREASE_RECURSIVE
 UPDATE nodes SET op_depth = ?3 + 1
-WHERE wc_id = ?1 AND local_relpath LIKE ?2 ESCAPE '#' AND op_depth = ?3
+WHERE wc_id = ?1
+  AND (?2 = ''
+       OR (local_relpath > ?2 || '/' AND local_relpath < ?2 || '0'))
+ AND op_depth = ?3
 
 -- STMT_DOES_NODE_EXIST
 SELECT 1 FROM nodes WHERE wc_id = ?1 AND local_relpath = ?2
@@ -831,14 +844,22 @@ LIMIT 1
 
 -- STMT_HAS_ABSENT_NODES
 SELECT local_relpath FROM nodes
-WHERE wc_id = ?1 AND (local_relpath = ?2 OR local_relpath LIKE ?3 ESCAPE '#')
-  AND op_depth = 0 AND presence = 'absent' LIMIT 1
+WHERE wc_id = ?1
+  AND (?2 = ''
+       OR local_relpath = ?2
+       OR (local_relpath > ?2 || '/' AND local_relpath < ?2 || '0'))
+  AND op_depth = 0 AND presence = 'absent'
+LIMIT 1
 
 /* ### Select all absent nodes. */
 -- STMT_SELECT_ALL_ABSENT_NODES
 SELECT local_relpath FROM nodes
-WHERE wc_id = ?1 AND (local_relpath = ?2 OR local_relpath LIKE ?3 ESCAPE '#')
-  AND op_depth = 0 AND presence = 'absent'
+WHERE wc_id = ?1
+  AND (?2 = ''
+       OR local_relpath = ?2
+       OR (local_relpath > ?2 || '/' AND local_relpath < ?2 || '0'))
+  AND op_depth = 0
+  AND presence = 'absent'
 
 -- STMT_INSERT_WORKING_NODE_COPY_FROM_BASE
 INSERT OR REPLACE INTO nodes (
@@ -1177,14 +1198,19 @@ DROP TABLE IF EXISTS delete_list
 SELECT MIN(revision), MAX(revision),
        MIN(changed_revision), MAX(changed_revision) FROM nodes
   WHERE wc_id = ?1
-  AND (local_relpath = ?2 OR local_relpath LIKE ?3 ESCAPE '#')
-  AND presence IN ('normal', 'incomplete')
-  AND file_external IS NULL
-  AND op_depth = 0
+    AND (?2 = ''
+       OR local_relpath = ?2
+       OR (local_relpath > ?2 || '/' AND local_relpath < ?2 || '0'))
+    AND presence IN ('normal', 'incomplete')
+    AND file_external IS NULL
+    AND op_depth = 0
 
 -- STMT_HAS_SPARSE_NODES
 SELECT 1 FROM nodes
-WHERE wc_id = ?1 AND (local_relpath = ?2 OR local_relpath LIKE ?3 ESCAPE '#')
+WHERE wc_id = ?1
+  AND (?2 = ''
+       OR local_relpath = ?2
+       OR (local_relpath > ?2 || '/' AND local_relpath < ?2 || '0'))
   AND op_depth = 0
   AND (presence IN ('absent', 'excluded')
         OR depth NOT IN ('infinity', 'unknown'))
@@ -1193,13 +1219,19 @@ LIMIT 1
 
 -- STMT_SUBTREE_HAS_TREE_MODIFICATIONS
 SELECT 1 FROM nodes
-WHERE wc_id = ?1 AND (local_relpath = ?2 OR local_relpath LIKE ?3 ESCAPE '#')
+WHERE wc_id = ?1
+  AND (?2 = ''
+       OR local_relpath = ?2
+       OR (local_relpath > ?2 || '/' AND local_relpath < ?2 || '0'))
   AND op_depth > 0
 LIMIT 1
 
 -- STMT_SUBTREE_HAS_PROP_MODIFICATIONS
 SELECT 1 FROM actual_node
-WHERE wc_id = ?1 AND (local_relpath = ?2 OR local_relpath LIKE ?3 ESCAPE '#')
+WHERE wc_id = ?1
+  AND (?2 = ''
+       OR local_relpath = ?2
+       OR (local_relpath > ?2 || '/' AND local_relpath < ?2 || '0'))
   AND properties IS NOT NULL
 LIMIT 1
 
@@ -1275,7 +1307,9 @@ LIMIT 1
 -- STMT_SELECT_BASE_FILES_RECURSIVE
 SELECT local_relpath, translated_size, last_mod_time FROM nodes AS n
 WHERE wc_id = ?1
-  AND (local_relpath = ?2 OR local_relpath LIKE ?3 ESCAPE '#')
+  AND (?2 = ''
+       OR local_relpath = ?2
+       OR (local_relpath > ?2 || '/' AND local_relpath < ?2 || '0'))
   AND op_depth = 0
   AND kind='file'
   AND presence='normal'

Modified: subversion/trunk/subversion/libsvn_wc/wc_db.c
URL: http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_wc/wc_db.c?rev=1142059&r1=1142058&r2=1142059&view=diff
==============================================================================
--- subversion/trunk/subversion/libsvn_wc/wc_db.c (original)
+++ subversion/trunk/subversion/libsvn_wc/wc_db.c Fri Jul  1 20:04:03 2011
@@ -4033,11 +4033,9 @@ catch_copy_of_absent(svn_wc__db_wcroot_t
 
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                     STMT_HAS_ABSENT_NODES));
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss",
+  SVN_ERR(svn_sqlite__bindf(stmt, "is",
                             wcroot->wc_id,
-                            local_relpath,
-                            construct_like_arg(local_relpath,
-                                               scratch_pool)));
+                            local_relpath));
   SVN_ERR(svn_sqlite__step(&have_row, stmt));
   if (have_row)
     absent_relpath = svn_sqlite__column_text(stmt, 0, scratch_pool);
@@ -5241,8 +5239,7 @@ op_revert_txn(void *baton,
       SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                      STMT_UPDATE_OP_DEPTH_INCREASE_RECURSIVE));
       SVN_ERR(svn_sqlite__bindf(stmt, "isi", wcroot->wc_id,
-                                construct_like_arg(local_relpath,
-                                                   scratch_pool),
+                                local_relpath,
                                 op_depth));
       SVN_ERR(svn_sqlite__step_done(stmt));
 
@@ -5285,7 +5282,6 @@ op_revert_recursive_txn(void *baton,
   svn_boolean_t have_row;
   apr_int64_t op_depth;
   int affected_rows;
-  const char *like_arg = construct_like_arg(local_relpath, scratch_pool);
 
   /* ### Similar structure to op_revert_txn, should they be
          combined? */
@@ -5300,8 +5296,8 @@ op_revert_recursive_txn(void *baton,
 
       SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                         STMT_DELETE_ACTUAL_NODE_RECURSIVE));
-      SVN_ERR(svn_sqlite__bindf(stmt, "iss", wcroot->wc_id,
-                                local_relpath, like_arg));
+      SVN_ERR(svn_sqlite__bindf(stmt, "is", wcroot->wc_id,
+                                local_relpath));
       SVN_ERR(svn_sqlite__step(&affected_rows, stmt));
 
       if (affected_rows)
@@ -5336,21 +5332,21 @@ op_revert_recursive_txn(void *baton,
 
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                         STMT_DELETE_ACTUAL_NODE_LEAVING_CHANGELIST_RECURSIVE));
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss", wcroot->wc_id,
-                            local_relpath, like_arg));
+  SVN_ERR(svn_sqlite__bindf(stmt, "is", wcroot->wc_id,
+                            local_relpath));
   SVN_ERR(svn_sqlite__step_done(stmt));
 
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                         STMT_CLEAR_ACTUAL_NODE_LEAVING_CHANGELIST_RECURSIVE));
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss", wcroot->wc_id,
-                            local_relpath, like_arg));
+  SVN_ERR(svn_sqlite__bindf(stmt, "is", wcroot->wc_id,
+                            local_relpath));
   SVN_ERR(svn_sqlite__step_done(stmt));
 
   /* ### This removes the locks, but what about the access batons? */
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                     STMT_DELETE_WC_LOCK_ORPHAN_RECURSIVE));
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss", wcroot->wc_id,
-                            local_relpath, like_arg));
+  SVN_ERR(svn_sqlite__bindf(stmt, "is", wcroot->wc_id,
+                            local_relpath));
   SVN_ERR(svn_sqlite__step_done(stmt));
 
   return SVN_NO_ERROR;
@@ -5739,8 +5735,6 @@ remove_node_txn(void *baton,
   apr_int64_t repos_id;
   const char *repos_relpath;
 
-  const char *like_arg = construct_like_arg(local_relpath, scratch_pool);
-
   SVN_ERR_ASSERT(*local_relpath != '\0'); /* Never on a wcroot */
 
   /* Need info for not_present node? */
@@ -5763,9 +5757,8 @@ remove_node_txn(void *baton,
                                     STMT_DELETE_ACTUAL_NODE_RECURSIVE));
 
   /* Delete all actual nodes at or below local_relpath */
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss", wcroot->wc_id,
-                                         local_relpath,
-                                         like_arg));
+  SVN_ERR(svn_sqlite__bindf(stmt, "is", wcroot->wc_id,
+                                         local_relpath));
   SVN_ERR(svn_sqlite__step_done(stmt));
 
   /* Should we leave a not-present node? */
@@ -6039,7 +6032,6 @@ op_delete_txn(void *baton,
   svn_boolean_t have_row, op_root;
   svn_boolean_t add_work = FALSE;
   svn_sqlite__stmt_t *stmt;
-  const char *like_arg;
   apr_int64_t select_depth; /* Depth of what is to be deleted */
   svn_boolean_t refetch_depth = FALSE;
 
@@ -6057,12 +6049,10 @@ op_delete_txn(void *baton,
       || status == svn_wc__db_status_not_present)
     return SVN_NO_ERROR;
 
-  like_arg = construct_like_arg(local_relpath, scratch_pool);
-
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                     STMT_HAS_ABSENT_NODES));
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss",
-                            wcroot->wc_id, local_relpath, like_arg));
+  SVN_ERR(svn_sqlite__bindf(stmt, "is",
+                            wcroot->wc_id, local_relpath));
   SVN_ERR(svn_sqlite__step(&have_row, stmt));
   if (have_row)
     {
@@ -6124,20 +6114,20 @@ op_delete_txn(void *baton,
      and a NODES row. */
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                          STMT_DELETE_ACTUAL_NODE_LEAVING_CHANGELIST_RECURSIVE));
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss",
-                            wcroot->wc_id, local_relpath, like_arg));
+  SVN_ERR(svn_sqlite__bindf(stmt, "is",
+                            wcroot->wc_id, local_relpath));
   SVN_ERR(svn_sqlite__step_done(stmt));
 
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                          STMT_CLEAR_ACTUAL_NODE_LEAVING_CHANGELIST_RECURSIVE));
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss",
-                            wcroot->wc_id, local_relpath, like_arg));
+  SVN_ERR(svn_sqlite__bindf(stmt, "is",
+                            wcroot->wc_id, local_relpath));
   SVN_ERR(svn_sqlite__step_done(stmt));
 
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                     STMT_DELETE_WC_LOCK_ORPHAN_RECURSIVE));
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss", wcroot->wc_id,
-                            local_relpath, like_arg));
+  SVN_ERR(svn_sqlite__bindf(stmt, "is", wcroot->wc_id,
+                            local_relpath));
   SVN_ERR(svn_sqlite__step_done(stmt));
 
   if (add_work)
@@ -11516,8 +11506,7 @@ get_min_max_revisions(svn_revnum_t *min_
 
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                     STMT_SELECT_MIN_MAX_REVISIONS));
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss", wcroot->wc_id, local_relpath,
-                            construct_like_arg(local_relpath, scratch_pool)));
+  SVN_ERR(svn_sqlite__bindf(stmt, "is", wcroot->wc_id, local_relpath));
   SVN_ERR(svn_sqlite__step(&have_row, stmt));
   if (have_row)
     {
@@ -11589,11 +11578,9 @@ is_sparse_checkout_internal(svn_boolean_
 
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                     STMT_HAS_SPARSE_NODES));
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss",
+  SVN_ERR(svn_sqlite__bindf(stmt, "is",
                             wcroot->wc_id,
-                            local_relpath,
-                            construct_like_arg(local_relpath,
-                                               scratch_pool)));
+                            local_relpath));
   /* If this query returns a row, the working copy is sparse. */
   SVN_ERR(svn_sqlite__step(&have_row, stmt));
   *is_sparse_checkout = have_row;
@@ -11754,11 +11741,9 @@ svn_wc__db_get_absent_subtrees(apr_hash_
   VERIFY_USABLE_WCROOT(wcroot);
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                     STMT_SELECT_ALL_ABSENT_NODES));
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss",
+  SVN_ERR(svn_sqlite__bindf(stmt, "is",
                             wcroot->wc_id,
-                            local_relpath,
-                            construct_like_arg(local_relpath,
-                                               scratch_pool)));
+                            local_relpath));
   SVN_ERR(svn_sqlite__step(&have_row, stmt));
 
   if (have_row)
@@ -11797,8 +11782,7 @@ has_local_mods(svn_boolean_t *is_modifie
   /* Check for additions or deletions. */
   SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                     STMT_SUBTREE_HAS_TREE_MODIFICATIONS));
-  SVN_ERR(svn_sqlite__bindf(stmt, "iss", wcroot->wc_id, local_relpath,
-                            construct_like_arg(local_relpath, scratch_pool)));
+  SVN_ERR(svn_sqlite__bindf(stmt, "is", wcroot->wc_id, local_relpath));
   /* If this query returns a row, the working copy is modified. */
   SVN_ERR(svn_sqlite__step(is_modified, stmt));
   SVN_ERR(svn_sqlite__reset(stmt));
@@ -11811,9 +11795,7 @@ has_local_mods(svn_boolean_t *is_modifie
       /* Check for property modifications. */
       SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                         STMT_SUBTREE_HAS_PROP_MODIFICATIONS));
-      SVN_ERR(svn_sqlite__bindf(stmt, "iss", wcroot->wc_id, local_relpath,
-                                construct_like_arg(local_relpath,
-                                                   scratch_pool)));
+      SVN_ERR(svn_sqlite__bindf(stmt, "is", wcroot->wc_id, local_relpath));
       /* If this query returns a row, the working copy is modified. */
       SVN_ERR(svn_sqlite__step(is_modified, stmt));
       SVN_ERR(svn_sqlite__reset(stmt));
@@ -11830,9 +11812,7 @@ has_local_mods(svn_boolean_t *is_modifie
       /* Check for text modifications. */
       SVN_ERR(svn_sqlite__get_statement(&stmt, wcroot->sdb,
                                         STMT_SELECT_BASE_FILES_RECURSIVE));
-      SVN_ERR(svn_sqlite__bindf(stmt, "iss", wcroot->wc_id, local_relpath,
-                                construct_like_arg(local_relpath,
-                                                   scratch_pool)));
+      SVN_ERR(svn_sqlite__bindf(stmt, "is", wcroot->wc_id, local_relpath));
       SVN_ERR(svn_sqlite__step(&have_row, stmt));
       if (have_row)
         iterpool = svn_pool_create(scratch_pool);



Mime
View raw message