From 85a76ddc121345db184d1952dd1ec363bd45bd63 Mon Sep 17 00:00:00 2001 From: dan Date: Tue, 26 Feb 2019 15:43:45 +0000 Subject: [PATCH] Add the ".shared-schema check|fix DB1 DB2..." command to the shell tool. For checking if a database is eligible to share an in-memory with the main database, and for fixing small problems that prevent it from being so. FossilOrigin-Name: 7d8e8a957235479fba568e1d3ff2cdfe4695184ee1a7ac64bce905a993725164 --- manifest | 12 +- manifest.uuid | 2 +- src/shell.c.in | 399 ++++++++++++++++++++++++++++++++++++++++--------- 3 files changed, 337 insertions(+), 76 deletions(-) diff --git a/manifest b/manifest index 5e4ac75799..8ff4b6b4a3 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Fix\sa\scomment\sin\sbuild.c. -D 2019-02-25T19:23:01.367 +C Add\sthe\s".shared-schema\scheck|fix\sDB1\sDB2..."\scommand\sto\sthe\sshell\stool.\sFor\schecking\sif\sa\sdatabase\sis\seligible\sto\sshare\san\sin-memory\swith\sthe\smain\sdatabase,\sand\sfor\sfixing\ssmall\sproblems\sthat\sprevent\sit\sfrom\sbeing\sso. +D 2019-02-26T15:43:45.362 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F Makefile.in 56456706c4da271309914c756c9c8ea537685f1c79f8785afa72f968d6810482 @@ -517,7 +517,7 @@ F src/random.c 80f5d666f23feb3e6665a6ce04c7197212a88384 F src/resolve.c 09419ad5c432190b69be7c0c326e03abb548a97c2c50675b81b459e1b382d1d2 F src/rowset.c d977b011993aaea002cab3e0bb2ce50cf346000dff94e944d547b989f4b1fe93 F src/select.c c998f694759e37799929e28df8a2649747f8774d4fc233529ab6bda689388e15 -F src/shell.c.in d7d63fd1ecef44d19088adc188652252327eab782d59cf1958657943132b5082 +F src/shell.c.in 89848d1ebdf1bb2078c18c173fb05d61a8ee0b35b5f4f26b21d02c9c9e4fe870 F src/sqlite.h.in a25272dca43fdb730faacade32ec4650d9f5c86839551853c560e01a8861c590 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 F src/sqlite3ext.h 960f1b86c3610fa23cb6a267572a97dcf286e77aa0dd3b9b23292ffaa1ea8683 @@ -1812,7 +1812,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 5c1cf30859ed734eb2d6bc52adb057ca62a6b5a7eef66d8da1fa833e84468dee -R 8e28445e51bbd44868112d648be6aca8 +P d6a9bff6f5cc52957deffe47fdba1197db111cac110760dec7680f91499a99f1 +R 34f2e101b58b731079737a037ea057bd U dan -Z 15ea225cf86c07603b7ff5af7f2624e7 +Z 9d509830be576ee0a26a75cf3961d117 diff --git a/manifest.uuid b/manifest.uuid index cea19022f6..40b5f249cc 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -d6a9bff6f5cc52957deffe47fdba1197db111cac110760dec7680f91499a99f1 \ No newline at end of file +7d8e8a957235479fba568e1d3ff2cdfe4695184ee1a7ac64bce905a993725164 \ No newline at end of file diff --git a/src/shell.c.in b/src/shell.c.in index 48b6d2224f..e4c8505165 100644 --- a/src/shell.c.in +++ b/src/shell.c.in @@ -2939,6 +2939,331 @@ static int expertDotCommand( } #endif /* ifndef SQLITE_OMIT_VIRTUALTABLE */ +static void shellPrepare( + sqlite3 *db, + int *pRc, + const char *zSql, + sqlite3_stmt **ppStmt +){ + *ppStmt = 0; + if( *pRc==SQLITE_OK ){ + int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0); + if( rc!=SQLITE_OK ){ + raw_printf(stderr, "sql error: %s (%d)\n", + sqlite3_errmsg(db), sqlite3_errcode(db) + ); + *pRc = rc; + } + } +} + +static void shellExecPrintf( + sqlite3 *db, + int *pRc, + const char *zFmt, + ... +){ + if( *pRc==SQLITE_OK ){ + va_list ap; + char *z; + va_start(ap, zFmt); + z = sqlite3_vmprintf(zFmt, ap); + va_end(ap); + if( z==0 ){ + *pRc = SQLITE_NOMEM; + }else{ + *pRc = sqlite3_exec(db, z, 0, 0, 0); + sqlite3_free(z); + } + } +} + +static void shellPreparePrintf( + sqlite3 *db, + int *pRc, + sqlite3_stmt **ppStmt, + const char *zFmt, + ... +){ + *ppStmt = 0; + if( *pRc==SQLITE_OK ){ + va_list ap; + char *z; + va_start(ap, zFmt); + z = sqlite3_vmprintf(zFmt, ap); + va_end(ap); + if( z==0 ){ + *pRc = SQLITE_NOMEM; + }else{ + shellPrepare(db, pRc, z, ppStmt); + sqlite3_free(z); + } + } +} + +static void shellFinalize( + int *pRc, + sqlite3_stmt *pStmt +){ + if( pStmt ){ + sqlite3 *db = sqlite3_db_handle(pStmt); + int rc = sqlite3_finalize(pStmt); + if( *pRc==SQLITE_OK ){ + if( rc!=SQLITE_OK ){ + raw_printf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); + } + *pRc = rc; + } + } +} + +static void shellReset( + int *pRc, + sqlite3_stmt *pStmt +){ + int rc = sqlite3_reset(pStmt); + if( *pRc==SQLITE_OK ){ + if( rc!=SQLITE_OK ){ + sqlite3 *db = sqlite3_db_handle(pStmt); + raw_printf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); + } + *pRc = rc; + } +} + +static int sharedSchemaFix(ShellState *pState, const char *zDb, int eFix){ + int rc = SQLITE_OK; + i64 iLast = 0; + int iCookie = 0; + int iAutoVacuum = 0; + sqlite3_stmt *pStmt = 0; + + shellExecPrintf(pState->db, &rc, "ATTACH '%q' AS _shared_schema_tmp", zDb); + shellExecPrintf(pState->db, &rc, "PRAGMA writable_schema = 1"); + shellExecPrintf(pState->db, &rc, "BEGIN"); + shellPreparePrintf(pState->db, &rc, &pStmt, + "SELECT max(rowid) FROM _shared_schema_tmp.sqlite_master" + ); + sqlite3_step(pStmt); + iLast = sqlite3_column_int64(pStmt, 0); + shellFinalize(&rc, pStmt); + shellPreparePrintf(pState->db, &rc, &pStmt, + "INSERT INTO _shared_schema_tmp.sqlite_master SELECT " + " type, name, tbl_name, (" + " SELECT rootpage FROM _shared_schema_tmp.sqlite_master WHERE " + " type IS o.type AND name IS o.name AND rowid<=?" + " ), sql FROM main.sqlite_master AS o" + ); + sqlite3_bind_int64(pStmt, 1, iLast); + sqlite3_step(pStmt); + shellFinalize(&rc, pStmt); + + shellExecPrintf(pState->db, &rc, + "DELETE FROM _shared_schema_tmp.sqlite_master WHERE rowid<=%lld", + iLast + ); + shellExecPrintf(pState->db, &rc, "COMMIT"); + sqlite3_exec(pState->db, "PRAGMA writable_schema = 0", 0, 0, 0); + + /* Copy the auto-vacuum setting from main to the target db */ + shellPreparePrintf(pState->db, &rc, &pStmt, "PRAGMA main.auto_vacuum"); + sqlite3_step(pStmt); + iAutoVacuum = sqlite3_column_int(pStmt, 0); + shellFinalize(&rc, pStmt); + shellExecPrintf(pState->db, &rc, + "PRAGMA _shared_schema_tmp.auto_vacuum = %d", iAutoVacuum + ); + + /* Vacuum the db in order to standardize the rootpage numbers. */ + shellExecPrintf(pState->db, &rc, "VACUUM _shared_schema_tmp"); + + /* Set the schema-cookie value to the same as database "main" */ + shellPreparePrintf(pState->db, &rc, &pStmt, "PRAGMA main.schema_version"); + sqlite3_step(pStmt); + iCookie = sqlite3_column_int(pStmt, 0); + shellFinalize(&rc, pStmt); + shellExecPrintf(pState->db, &rc, + "PRAGMA _shared_schema_tmp.schema_version = %d", iCookie + ); + + sqlite3_exec(pState->db, "DETACH _shared_schema_tmp", 0, 0, 0); + return rc; +} + +static int sharedSchemaCheck(ShellState *pState, const char *zDb, int *peFix){ + int rc = SQLITE_OK; + int bFailed = 0; + sqlite3_stmt *pStmt = 0; + + if( peFix ) *peFix = 0; + shellExecPrintf(pState->db, &rc, "ATTACH '%q' AS _shared_schema_tmp", zDb); + + /* Check if this database has the same set of objects as the current db */ + shellPreparePrintf(pState->db, &rc, &pStmt, + "SELECT type, name FROM _shared_schema_tmp.sqlite_master AS o " + "WHERE NOT EXISTS (" + " SELECT 1 FROM main.sqlite_master " + " WHERE name IS o.name AND type IS o.type" + ")" + " UNION ALL " + "SELECT type, name FROM main.sqlite_master AS o " + "WHERE NOT EXISTS (" + " SELECT 1 FROM _shared_schema_tmp.sqlite_master " + " WHERE name IS o.name AND type IS o.type" + ")" + ); + if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ + utf8_printf(pState->out, "%s is NOT compatible (objects)\n", zDb); + bFailed = 1; + } + shellFinalize(&rc, pStmt); + + /* Check if this database has the same set of SQL statements as the + ** current db. */ + if( bFailed==0 ){ + shellPreparePrintf(pState->db, &rc, &pStmt, + "SELECT 1 FROM _shared_schema_tmp.sqlite_master AS o " + "WHERE sql IS NOT (" + " SELECT sql FROM main.sqlite_master " + " WHERE name IS o.name AND type IS o.type" + ")" + ); + if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ + utf8_printf(pState->out, "%s is NOT compatible (SQL)\n", zDb); + bFailed = 1; + } + shellFinalize(&rc, pStmt); + } + + /* Check if this database has the same set of root pages as the current + ** db. */ + if( bFailed==0 ){ + shellPreparePrintf(pState->db, &rc, &pStmt, + "SELECT 1 FROM _shared_schema_tmp.sqlite_master AS o " + "WHERE rootpage IS NOT (" + " SELECT rootpage FROM main.sqlite_master " + " WHERE name IS o.name AND type IS o.type" + ")" + ); + if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ + if( peFix==0 ){ + utf8_printf(pState->out, "%s is NOT compatible (root pages)\n", zDb); + } + bFailed = 1; + if( peFix ) *peFix = 1; + } + shellFinalize(&rc, pStmt); + } + + if( bFailed==0 ){ + shellPreparePrintf(pState->db, &rc, &pStmt, + "SELECT 1 WHERE (" + " SELECT group_concat(rootpage || '.' || name || '.' || sql, '.') " + " FROM _shared_schema_tmp.sqlite_master" + ") IS NOT (" + " SELECT group_concat(rootpage || '.' || name || '.' || sql, '.') " + " FROM main.sqlite_master" + ")" + ); + if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ + if( peFix==0 ){ + utf8_printf(pState->out, + "%s is NOT compatible (order of sqlite_master rows)\n", zDb + ); + } + bFailed = 1; + if( peFix ) *peFix = 2; + } + shellFinalize(&rc, pStmt); + } + + if( bFailed==0 ){ + int iMain = -1; + int iNew = +1; + shellPreparePrintf(pState->db, &rc, &pStmt, + "PRAGMA main.schema_version" + ); + if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ + iMain = sqlite3_column_int(pStmt, 0); + } + shellFinalize(&rc, pStmt); + shellPreparePrintf(pState->db, &rc, &pStmt, + "PRAGMA _shared_schema_tmp.schema_version" + ); + if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ + iNew = sqlite3_column_int(pStmt, 0); + } + shellFinalize(&rc, pStmt); + if( rc==SQLITE_OK && iMain!=iNew ){ + if( peFix==0 ){ + utf8_printf(pState->out, + "%s is NOT compatible (schema cookie)\n", zDb + ); + } + bFailed = 1; + if( peFix ) *peFix = 3; + } + } + + if( rc==SQLITE_OK && bFailed==0 ){ + utf8_printf(pState->out, "%s is compatible\n", zDb); + } + + sqlite3_exec(pState->db, "DETACH _shared_schema_tmp", 0, 0, 0); + return rc; +} + +/* +** .shared-schema check|fix DB1 DB2... +*/ +static int sharedSchemaDotCommand( + ShellState *pState, /* Current shell tool state */ + char **azArg, /* Array of arguments passed to dot command */ + int nArg /* Number of entries in azArg[] */ +){ + int rc = SQLITE_OK; + int bFix = 0; /* Fix databases if possible */ + int n1; + int i; + if( nArg<3 ){ + goto shared_schema_usage; + } + + n1 = (int)strlen(azArg[1]); + if( n1>0 && n1<=3 && memcmp("fix", azArg[1], n1)==0 ){ + bFix = 1; + }else if( n1==0 || n1>5 || memcmp("check", azArg[1], n1) ){ + goto shared_schema_usage; + } + + for(i=2; rc==SQLITE_OK && iout, "Fixing %s... ", azArg[i]); + fflush(pState->out); + rc = sharedSchemaFix(pState, azArg[i], eFix); + if( rc==SQLITE_OK ){ + rc = sharedSchemaCheck(pState, azArg[i], &eFix); + if( rc==SQLITE_OK && eFix ){ + utf8_printf(pState->out, "VACUUMing main... "); + fflush(pState->out); + rc = sqlite3_exec(pState->db, "VACUUM main", 0, 0, 0); + if( rc==SQLITE_OK ){ + rc = sharedSchemaCheck(pState, azArg[i], 0); + } + } + } + } + } + + return rc; + shared_schema_usage: + raw_printf(stderr, "usage: .shared-schema check|fix DB1 DB2...\n"); + return SQLITE_ERROR; +} + + /* ** Execute a statement or set of statements. Print ** any result rows/columns depending on the current mode @@ -5196,76 +5521,7 @@ static int lintDotCommand( /********************************************************************************* ** The ".archive" or ".ar" command. */ -static void shellPrepare( - sqlite3 *db, - int *pRc, - const char *zSql, - sqlite3_stmt **ppStmt -){ - *ppStmt = 0; - if( *pRc==SQLITE_OK ){ - int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0); - if( rc!=SQLITE_OK ){ - raw_printf(stderr, "sql error: %s (%d)\n", - sqlite3_errmsg(db), sqlite3_errcode(db) - ); - *pRc = rc; - } - } -} -static void shellPreparePrintf( - sqlite3 *db, - int *pRc, - sqlite3_stmt **ppStmt, - const char *zFmt, - ... -){ - *ppStmt = 0; - if( *pRc==SQLITE_OK ){ - va_list ap; - char *z; - va_start(ap, zFmt); - z = sqlite3_vmprintf(zFmt, ap); - va_end(ap); - if( z==0 ){ - *pRc = SQLITE_NOMEM; - }else{ - shellPrepare(db, pRc, z, ppStmt); - sqlite3_free(z); - } - } -} - -static void shellFinalize( - int *pRc, - sqlite3_stmt *pStmt -){ - if( pStmt ){ - sqlite3 *db = sqlite3_db_handle(pStmt); - int rc = sqlite3_finalize(pStmt); - if( *pRc==SQLITE_OK ){ - if( rc!=SQLITE_OK ){ - raw_printf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); - } - *pRc = rc; - } - } -} - -static void shellReset( - int *pRc, - sqlite3_stmt *pStmt -){ - int rc = sqlite3_reset(pStmt); - if( *pRc==SQLITE_OK ){ - if( rc!=SQLITE_OK ){ - sqlite3 *db = sqlite3_db_handle(pStmt); - raw_printf(stderr, "SQL error: %s\n", sqlite3_errmsg(db)); - } - *pRc = rc; - } -} /* ** Structure representing a single ".ar" command. */ @@ -7776,6 +8032,11 @@ static int do_meta_command(char *zLine, ShellState *p){ sqlite3_free(zSql); }else + if( c=='s' && strncmp(azArg[0], "shared-schema", n)==0 ){ + open_db(p, 0); + sharedSchemaDotCommand(p, azArg, nArg); + }else + #ifndef SQLITE_NOHAVE_SYSTEM if( c=='s' && (strncmp(azArg[0], "shell", n)==0 || strncmp(azArg[0],"system",n)==0) -- 2.47.3