Fossil SCM

Add the .fullschema command to "fossil sqlite". This command shows the schema and the content of the sqlite_stat tables, all in one go. Useful when reporting problems with the query planner.

jan.nijtmans 2014-06-25 07:57 trunk
Commit 7ba8311e5748985608c3a2481a91fb774fd83cba
Parent 1d30672588ec8be…
1 file changed +39
~ src/shell.c
M src/shell.c
+39
--- src/shell.c
+++ src/shell.c
@@ -1581,10 +1581,11 @@
15811581
 ".echo on|off Turn command echo on or off\n"
15821582
 ".eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN\n"
15831583
 ".exit Exit this program\n"
15841584
 ".explain ?on|off? Turn output mode suitable for EXPLAIN on or off.\n"
15851585
 " With no args, it turns EXPLAIN on.\n"
1586
+ ".fullschema Show schema and the content of sqlite_stat tables\n"
15861587
 ".headers on|off Turn display of headers on or off\n"
15871588
 ".help Show this message\n"
15881589
 ".import FILE TABLE Import data from FILE into TABLE\n"
15891590
 ".indices ?TABLE? Show names of all indices\n"
15901591
 " If TABLE specified, only show indices for tables\n"
@@ -2409,10 +2410,48 @@
24092410
 p->mode = p->explainPrev.mode;
24102411
 p->showHeader = p->explainPrev.showHeader;
24112412
 memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth));
24122413
 }
24132414
 }else
2415
+
2416
+ if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){
2417
+ struct callback_data data;
2418
+ char *zErrMsg = 0;
2419
+ if( nArg!=1 ){
2420
+ fprintf(stderr, "Usage: .fullschema\n");
2421
+ rc = 1;
2422
+ goto meta_command_exit;
2423
+ }
2424
+ open_db(p, 0);
2425
+ memcpy(&data, p, sizeof(data));
2426
+ data.showHeader = 0;
2427
+ data.mode = MODE_Semi;
2428
+ rc = sqlite3_exec(p->db,
2429
+ "SELECT sql FROM"
2430
+ " (SELECT sql sql, type type, tbl_name tbl_name, name name, rowid x"
2431
+ " FROM sqlite_master UNION ALL"
2432
+ " SELECT sql, type, tbl_name, name, rowid FROM sqlite_temp_master) "
2433
+ "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'"
2434
+ "ORDER BY rowid",
2435
+ callback, &data, &zErrMsg
2436
+ );
2437
+ sqlite3_exec(p->db, "SELECT 'ANALYZE sqlite_master;'",
2438
+ callback, &data, &zErrMsg);
2439
+ data.mode = MODE_Insert;
2440
+ data.zDestTable = "sqlite_stat1";
2441
+ shell_exec(p->db, "SELECT * FROM sqlite_stat1",
2442
+ shell_callback, &data,&zErrMsg);
2443
+ data.zDestTable = "sqlite_stat3";
2444
+ shell_exec(p->db, "SELECT * FROM sqlite_stat3",
2445
+ shell_callback, &data,&zErrMsg);
2446
+ data.zDestTable = "sqlite_stat4";
2447
+ shell_exec(p->db, "SELECT * FROM sqlite_stat4",
2448
+ shell_callback, &data, &zErrMsg);
2449
+ data.mode = MODE_Semi;
2450
+ shell_exec(p->db, "SELECT 'ANALYZE sqlite_master;'",
2451
+ shell_callback, &data, &zErrMsg);
2452
+ }else
24142453
24152454
 if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
24162455
 if( nArg==2 ){
24172456
 p->showHeader = booleanValue(azArg[1]);
24182457
 }else{
24192458
--- src/shell.c
+++ src/shell.c
@@ -1581,10 +1581,11 @@
1581 ".echo on|off Turn command echo on or off\n"
1582 ".eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN\n"
1583 ".exit Exit this program\n"
1584 ".explain ?on|off? Turn output mode suitable for EXPLAIN on or off.\n"
1585 " With no args, it turns EXPLAIN on.\n"
 
1586 ".headers on|off Turn display of headers on or off\n"
1587 ".help Show this message\n"
1588 ".import FILE TABLE Import data from FILE into TABLE\n"
1589 ".indices ?TABLE? Show names of all indices\n"
1590 " If TABLE specified, only show indices for tables\n"
@@ -2409,10 +2410,48 @@
2409 p->mode = p->explainPrev.mode;
2410 p->showHeader = p->explainPrev.showHeader;
2411 memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth));
2412 }
2413 }else
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2414
2415 if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
2416 if( nArg==2 ){
2417 p->showHeader = booleanValue(azArg[1]);
2418 }else{
2419
--- src/shell.c
+++ src/shell.c
@@ -1581,10 +1581,11 @@
1581 ".echo on|off Turn command echo on or off\n"
1582 ".eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN\n"
1583 ".exit Exit this program\n"
1584 ".explain ?on|off? Turn output mode suitable for EXPLAIN on or off.\n"
1585 " With no args, it turns EXPLAIN on.\n"
1586 ".fullschema Show schema and the content of sqlite_stat tables\n"
1587 ".headers on|off Turn display of headers on or off\n"
1588 ".help Show this message\n"
1589 ".import FILE TABLE Import data from FILE into TABLE\n"
1590 ".indices ?TABLE? Show names of all indices\n"
1591 " If TABLE specified, only show indices for tables\n"
@@ -2409,10 +2410,48 @@
2410 p->mode = p->explainPrev.mode;
2411 p->showHeader = p->explainPrev.showHeader;
2412 memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth));
2413 }
2414 }else
2415
2416 if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){
2417 struct callback_data data;
2418 char *zErrMsg = 0;
2419 if( nArg!=1 ){
2420 fprintf(stderr, "Usage: .fullschema\n");
2421 rc = 1;
2422 goto meta_command_exit;
2423 }
2424 open_db(p, 0);
2425 memcpy(&data, p, sizeof(data));
2426 data.showHeader = 0;
2427 data.mode = MODE_Semi;
2428 rc = sqlite3_exec(p->db,
2429 "SELECT sql FROM"
2430 " (SELECT sql sql, type type, tbl_name tbl_name, name name, rowid x"
2431 " FROM sqlite_master UNION ALL"
2432 " SELECT sql, type, tbl_name, name, rowid FROM sqlite_temp_master) "
2433 "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'"
2434 "ORDER BY rowid",
2435 callback, &data, &zErrMsg
2436 );
2437 sqlite3_exec(p->db, "SELECT 'ANALYZE sqlite_master;'",
2438 callback, &data, &zErrMsg);
2439 data.mode = MODE_Insert;
2440 data.zDestTable = "sqlite_stat1";
2441 shell_exec(p->db, "SELECT * FROM sqlite_stat1",
2442 shell_callback, &data,&zErrMsg);
2443 data.zDestTable = "sqlite_stat3";
2444 shell_exec(p->db, "SELECT * FROM sqlite_stat3",
2445 shell_callback, &data,&zErrMsg);
2446 data.zDestTable = "sqlite_stat4";
2447 shell_exec(p->db, "SELECT * FROM sqlite_stat4",
2448 shell_callback, &data, &zErrMsg);
2449 data.mode = MODE_Semi;
2450 shell_exec(p->db, "SELECT 'ANALYZE sqlite_master;'",
2451 shell_callback, &data, &zErrMsg);
2452 }else
2453
2454 if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
2455 if( nArg==2 ){
2456 p->showHeader = booleanValue(azArg[1]);
2457 }else{
2458

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button