From 597d36dcf20b838524c250eba55ed0425480565a Mon Sep 17 00:00:00 2001 From: Olivier Goffart Date: Wed, 21 Oct 2015 15:44:16 +0200 Subject: [PATCH] csync_statedb: Use the index in csync_statedb_get_below_path Make an index from the path, and make a query that uses the index --- csync/src/csync_statedb.c | 24 ++++++++---------------- src/libsync/syncjournaldb.cpp | 6 +++--- 2 files changed, 11 insertions(+), 19 deletions(-) diff --git a/csync/src/csync_statedb.c b/csync/src/csync_statedb.c index 92ca528b6..61fc89756 100644 --- a/csync/src/csync_statedb.c +++ b/csync/src/csync_statedb.c @@ -416,15 +416,10 @@ csync_file_stat_t *csync_statedb_get_stat_by_inode(CSYNC *ctx, return st; } -#define BELOW_PATH_QUERY "SELECT phash, pathlen, path, inode, uid, gid, mode, modtime, type, md5, fileid, remotePerm, filesize, ignoredChildrenRemote FROM metadata WHERE pathlen>? AND path LIKE(?)" - int csync_statedb_get_below_path( CSYNC *ctx, const char *path ) { int rc; sqlite3_stmt *stmt = NULL; int64_t cnt = 0; - char *likepath; - int asp; - int min_path_len; if( !path ) { return -1; @@ -434,7 +429,12 @@ int csync_statedb_get_below_path( CSYNC *ctx, const char *path ) { return -1; } - SQLITE_BUSY_HANDLED(sqlite3_prepare_v2(ctx->statedb.db, BELOW_PATH_QUERY, -1, &stmt, NULL)); + /* Select the entries for anything that starts with (path+'/') + * In other words, anything that is between path+'/' and path+'0', + * (because '0' follows '/' in ascii) + */ + const char *below_path_query = "SELECT phash, pathlen, path, inode, uid, gid, mode, modtime, type, md5, fileid, remotePerm, filesize, ignoredChildrenRemote FROM metadata WHERE path > (?||'/') AND path < (?||'0')"; + SQLITE_BUSY_HANDLED(sqlite3_prepare_v2(ctx->statedb.db, below_path_query, -1, &stmt, NULL)); ctx->statedb.lastReturnValue = rc; if( rc != SQLITE_OK ) { CSYNC_LOG(CSYNC_LOG_PRIORITY_ERROR, "WRN: Unable to create stmt for below path query."); @@ -445,15 +445,8 @@ int csync_statedb_get_below_path( CSYNC *ctx, const char *path ) { return -1; } - asp = asprintf( &likepath, "%s/%%%%", path); - if (asp < 0) { - CSYNC_LOG(CSYNC_LOG_PRIORITY_ERROR, "asprintf failed!"); - return -1; - } - - min_path_len = strlen(path); - sqlite3_bind_int(stmt, 1, min_path_len); - sqlite3_bind_text(stmt, 2, likepath, -1, SQLITE_STATIC); + sqlite3_bind_text(stmt, 1, path, -1, SQLITE_STATIC); + sqlite3_bind_text(stmt, 2, path, -1, SQLITE_STATIC); cnt = 0; @@ -496,7 +489,6 @@ int csync_statedb_get_below_path( CSYNC *ctx, const char *path ) { CSYNC_LOG(CSYNC_LOG_PRIORITY_DEBUG, "%" PRId64 " entries read below path %s from db.", cnt, path); } sqlite3_finalize(stmt); - SAFE_FREE(likepath); return 0; } diff --git a/src/libsync/syncjournaldb.cpp b/src/libsync/syncjournaldb.cpp index 4da2ed61f..aaa53a1b5 100644 --- a/src/libsync/syncjournaldb.cpp +++ b/src/libsync/syncjournaldb.cpp @@ -509,12 +509,12 @@ bool SyncJournalDb::updateMetadataTableStructure() if( 1 ) { SqlQuery query(_db); - query.prepare("CREATE INDEX IF NOT EXISTS metadata_pathlen ON metadata(pathlen);"); + query.prepare("CREATE INDEX IF NOT EXISTS metadata_path ON metadata(path);"); if( !query.exec()) { - sqlFail("updateMetadataTableStructure: create index pathlen", query); + sqlFail("updateMetadataTableStructure: create index path", query); re = false; } - commitInternal("update database structure: add pathlen index"); + commitInternal("update database structure: add path index"); } -- 2.30.2