Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | 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. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | reuse-schema |
| Files: | files | file ages | folders |
| SHA3-256: | 7d8e8a957235479fba568e1d3ff2cdfe |
| User & Date: | dan 2019-02-26 15:43:45 |
| Wiki: | reuse-schema |
Context
|
2019-02-26
| ||
| 16:13 | Add new test file reuse5.test. For testing the shell tool ".shared-schema" command. check-in: fa6008d5 user: dan tags: reuse-schema | |
| 15:43 | 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. check-in: 7d8e8a95 user: dan tags: reuse-schema | |
|
2019-02-25
| ||
| 19:23 | Fix a comment in build.c. check-in: d6a9bff6 user: dan tags: reuse-schema | |
Changes
Changes to src/shell.c.in.
2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 2945 2946 2947 .... 5192 5193 5194 5195 5196 5197 5198 5199 5200 5201 5202 5203 5204 5205 5206 5207 5208 5209 5210 5211 5212 5213 5214 5215 5216 5217 5218 5219 5220 5221 5222 5223 5224 5225 5226 5227 5228 5229 5230 5231 5232 5233 5234 5235 5236 5237 5238 5239 5240 5241 5242 5243 5244 5245 5246 5247 5248 5249 5250 5251 5252 5253 5254 5255 5256 5257 5258 5259 5260 5261 5262 5263 5264 5265 5266 5267 5268 5269 5270 5271 5272 5273 5274 5275 .... 7771 7772 7773 7774 7775 7776 7777 7778 7779 7780 7781 7782 7783 7784 |
);
}
}
return rc;
}
#endif /* ifndef SQLITE_OMIT_VIRTUALTABLE */
/*
** Execute a statement or set of statements. Print
** any result rows/columns depending on the current mode
** set via the supplied callback.
**
** This is very similar to SQLite's built-in sqlite3_exec()
................................................................................
return SQLITE_ERROR;
}
#if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB)
/*********************************************************************************
** 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.
*/
typedef struct ArCommand ArCommand;
struct ArCommand {
u8 eCmd; /* An AR_CMD_* value */
u8 bVerbose; /* True if --verbose */
................................................................................
if( bDebug ){
utf8_printf(p->out, "%s\n", zSql);
}else{
shell_exec(p, zSql, 0);
}
sqlite3_free(zSql);
}else
#ifndef SQLITE_NOHAVE_SYSTEM
if( c=='s'
&& (strncmp(azArg[0], "shell", n)==0 || strncmp(azArg[0],"system",n)==0)
){
char *zCmd;
int i, x;
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > < < < < < < < < < < < < < < | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < > > > > > |
2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961 2962 2963 2964 2965 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 2990 2991 2992 2993 2994 2995 2996 2997 2998 2999 3000 3001 3002 3003 3004 3005 3006 3007 3008 3009 3010 3011 3012 3013 3014 3015 3016 3017 3018 3019 3020 3021 3022 3023 3024 3025 3026 3027 3028 3029 3030 3031 3032 3033 3034 3035 3036 3037 3038 3039 3040 3041 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 3053 3054 3055 3056 3057 3058 3059 3060 3061 3062 3063 3064 3065 3066 3067 3068 3069 3070 3071 3072 3073 3074 3075 3076 3077 3078 3079 3080 3081 3082 3083 3084 3085 3086 3087 3088 3089 3090 3091 3092 3093 3094 3095 3096 3097 3098 3099 3100 3101 3102 3103 3104 3105 3106 3107 3108 3109 3110 3111 3112 3113 3114 3115 3116 3117 3118 3119 3120 3121 3122 3123 3124 3125 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 3148 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 3176 3177 3178 3179 3180 3181 3182 3183 3184 3185 3186 3187 3188 3189 3190 3191 3192 3193 3194 3195 3196 3197 3198 3199 3200 3201 3202 3203 3204 3205 3206 3207 3208 3209 3210 3211 3212 3213 3214 3215 3216 3217 3218 3219 3220 3221 3222 3223 3224 3225 3226 3227 3228 3229 3230 3231 3232 3233 3234 3235 3236 3237 3238 3239 3240 3241 3242 3243 3244 3245 3246 3247 3248 3249 3250 3251 3252 3253 3254 3255 3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 3266 3267 3268 3269 3270 3271 3272 .... 5517 5518 5519 5520 5521 5522 5523 5524 5525 5526 5527 5528 5529 5530 5531 .... 8027 8028 8029 8030 8031 8032 8033 8034 8035 8036 8037 8038 8039 8040 8041 8042 8043 8044 8045 |
);
}
}
return rc;
}
#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 && i<nArg; i++){
int eFix = 0;
rc = sharedSchemaCheck(pState, azArg[i], bFix ? &eFix : 0);
if( rc==SQLITE_OK && bFix && eFix ){
utf8_printf(pState->out, "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
** set via the supplied callback.
**
** This is very similar to SQLite's built-in sqlite3_exec()
................................................................................
return SQLITE_ERROR;
}
#if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB)
/*********************************************************************************
** The ".archive" or ".ar" command.
*/
/*
** Structure representing a single ".ar" command.
*/
typedef struct ArCommand ArCommand;
struct ArCommand {
u8 eCmd; /* An AR_CMD_* value */
u8 bVerbose; /* True if --verbose */
................................................................................
if( bDebug ){
utf8_printf(p->out, "%s\n", zSql);
}else{
shell_exec(p, zSql, 0);
}
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)
){
char *zCmd;
int i, x;
|