Fossil SCM

Add the ".eqp" and ".fullschema" dot-commands to the ".help" output in the command-line shell. Fix CSV import issue, reported via the mailing list, in the shell when the file to be imported ends with an empty line.

jan.nijtmans 2014-06-17 13:51 trunk
Commit 1b648ebacf844840bd9114a7ee369af60b88bf2a
Parent 3e5ebe2b9027cde…
2 files changed +41 -1 +41 -1
~ src/shell.c ~ src/shell.c
M src/shell.c
+41 -1
--- src/shell.c
+++ src/shell.c
@@ -1577,13 +1577,15 @@
15771577
 ".databases List names and files of attached databases\n"
15781578
 ".dump ?TABLE? ... Dump the database in an SQL text format\n"
15791579
 " If TABLE specified, only dump tables matching\n"
15801580
 " LIKE pattern TABLE.\n"
15811581
 ".echo on|off Turn command echo on or off\n"
1582
+ ".eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN\n"
15821583
 ".exit Exit this program\n"
15831584
 ".explain ?on|off? Turn output mode suitable for EXPLAIN on or off.\n"
15841585
 " With no args, it turns EXPLAIN on.\n"
1586
+ ".fullschema Show schema and the content of sqlite_stat tables\n"
15851587
 ".headers on|off Turn display of headers on or off\n"
15861588
 ".help Show this message\n"
15871589
 ".import FILE TABLE Import data from FILE into TABLE\n"
15881590
 ".indices ?TABLE? Show names of all indices\n"
15891591
 " If TABLE specified, only show indices for tables\n"
@@ -2408,10 +2410,48 @@
24082410
 p->mode = p->explainPrev.mode;
24092411
 p->showHeader = p->explainPrev.showHeader;
24102412
 memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth));
24112413
 }
24122414
 }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
24132453
24142454
 if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
24152455
 if( nArg==2 ){
24162456
 p->showHeader = booleanValue(azArg[1]);
24172457
 }else{
@@ -2551,11 +2591,11 @@
25512591
 if( i<nCol-1 && sCsv.cTerm!=sCsv.cSeparator ){
25522592
 fprintf(stderr, "%s:%d: expected %d columns but found %d - "
25532593
 "filling the rest with NULL\n",
25542594
 sCsv.zFile, startLine, nCol, i+1);
25552595
 i++;
2556
- while( i<nCol ){ sqlite3_bind_null(pStmt, i); i++; }
2596
+ while( i<=nCol ){ sqlite3_bind_null(pStmt, i); i++; }
25572597
 }
25582598
 }
25592599
 if( sCsv.cTerm==sCsv.cSeparator ){
25602600
 do{
25612601
 csv_read_one_field(&sCsv);
25622602
--- src/shell.c
+++ src/shell.c
@@ -1577,13 +1577,15 @@
1577 ".databases List names and files of attached databases\n"
1578 ".dump ?TABLE? ... Dump the database in an SQL text format\n"
1579 " If TABLE specified, only dump tables matching\n"
1580 " LIKE pattern TABLE.\n"
1581 ".echo on|off Turn command echo on or off\n"
 
1582 ".exit Exit this program\n"
1583 ".explain ?on|off? Turn output mode suitable for EXPLAIN on or off.\n"
1584 " With no args, it turns EXPLAIN on.\n"
 
1585 ".headers on|off Turn display of headers on or off\n"
1586 ".help Show this message\n"
1587 ".import FILE TABLE Import data from FILE into TABLE\n"
1588 ".indices ?TABLE? Show names of all indices\n"
1589 " If TABLE specified, only show indices for tables\n"
@@ -2408,10 +2410,48 @@
2408 p->mode = p->explainPrev.mode;
2409 p->showHeader = p->explainPrev.showHeader;
2410 memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth));
2411 }
2412 }else
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2413
2414 if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
2415 if( nArg==2 ){
2416 p->showHeader = booleanValue(azArg[1]);
2417 }else{
@@ -2551,11 +2591,11 @@
2551 if( i<nCol-1 && sCsv.cTerm!=sCsv.cSeparator ){
2552 fprintf(stderr, "%s:%d: expected %d columns but found %d - "
2553 "filling the rest with NULL\n",
2554 sCsv.zFile, startLine, nCol, i+1);
2555 i++;
2556 while( i<nCol ){ sqlite3_bind_null(pStmt, i); i++; }
2557 }
2558 }
2559 if( sCsv.cTerm==sCsv.cSeparator ){
2560 do{
2561 csv_read_one_field(&sCsv);
2562
--- src/shell.c
+++ src/shell.c
@@ -1577,13 +1577,15 @@
1577 ".databases List names and files of attached databases\n"
1578 ".dump ?TABLE? ... Dump the database in an SQL text format\n"
1579 " If TABLE specified, only dump tables matching\n"
1580 " LIKE pattern TABLE.\n"
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"
@@ -2408,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{
@@ -2551,11 +2591,11 @@
2591 if( i<nCol-1 && sCsv.cTerm!=sCsv.cSeparator ){
2592 fprintf(stderr, "%s:%d: expected %d columns but found %d - "
2593 "filling the rest with NULL\n",
2594 sCsv.zFile, startLine, nCol, i+1);
2595 i++;
2596 while( i<=nCol ){ sqlite3_bind_null(pStmt, i); i++; }
2597 }
2598 }
2599 if( sCsv.cTerm==sCsv.cSeparator ){
2600 do{
2601 csv_read_one_field(&sCsv);
2602
M src/shell.c
+41 -1
--- src/shell.c
+++ src/shell.c
@@ -1577,13 +1577,15 @@
15771577
 ".databases List names and files of attached databases\n"
15781578
 ".dump ?TABLE? ... Dump the database in an SQL text format\n"
15791579
 " If TABLE specified, only dump tables matching\n"
15801580
 " LIKE pattern TABLE.\n"
15811581
 ".echo on|off Turn command echo on or off\n"
1582
+ ".eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN\n"
15821583
 ".exit Exit this program\n"
15831584
 ".explain ?on|off? Turn output mode suitable for EXPLAIN on or off.\n"
15841585
 " With no args, it turns EXPLAIN on.\n"
1586
+ ".fullschema Show schema and the content of sqlite_stat tables\n"
15851587
 ".headers on|off Turn display of headers on or off\n"
15861588
 ".help Show this message\n"
15871589
 ".import FILE TABLE Import data from FILE into TABLE\n"
15881590
 ".indices ?TABLE? Show names of all indices\n"
15891591
 " If TABLE specified, only show indices for tables\n"
@@ -2408,10 +2410,48 @@
24082410
 p->mode = p->explainPrev.mode;
24092411
 p->showHeader = p->explainPrev.showHeader;
24102412
 memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth));
24112413
 }
24122414
 }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
24132453
24142454
 if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
24152455
 if( nArg==2 ){
24162456
 p->showHeader = booleanValue(azArg[1]);
24172457
 }else{
@@ -2551,11 +2591,11 @@
25512591
 if( i<nCol-1 && sCsv.cTerm!=sCsv.cSeparator ){
25522592
 fprintf(stderr, "%s:%d: expected %d columns but found %d - "
25532593
 "filling the rest with NULL\n",
25542594
 sCsv.zFile, startLine, nCol, i+1);
25552595
 i++;
2556
- while( i<nCol ){ sqlite3_bind_null(pStmt, i); i++; }
2596
+ while( i<=nCol ){ sqlite3_bind_null(pStmt, i); i++; }
25572597
 }
25582598
 }
25592599
 if( sCsv.cTerm==sCsv.cSeparator ){
25602600
 do{
25612601
 csv_read_one_field(&sCsv);
25622602
--- src/shell.c
+++ src/shell.c
@@ -1577,13 +1577,15 @@
1577 ".databases List names and files of attached databases\n"
1578 ".dump ?TABLE? ... Dump the database in an SQL text format\n"
1579 " If TABLE specified, only dump tables matching\n"
1580 " LIKE pattern TABLE.\n"
1581 ".echo on|off Turn command echo on or off\n"
 
1582 ".exit Exit this program\n"
1583 ".explain ?on|off? Turn output mode suitable for EXPLAIN on or off.\n"
1584 " With no args, it turns EXPLAIN on.\n"
 
1585 ".headers on|off Turn display of headers on or off\n"
1586 ".help Show this message\n"
1587 ".import FILE TABLE Import data from FILE into TABLE\n"
1588 ".indices ?TABLE? Show names of all indices\n"
1589 " If TABLE specified, only show indices for tables\n"
@@ -2408,10 +2410,48 @@
2408 p->mode = p->explainPrev.mode;
2409 p->showHeader = p->explainPrev.showHeader;
2410 memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth));
2411 }
2412 }else
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2413
2414 if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
2415 if( nArg==2 ){
2416 p->showHeader = booleanValue(azArg[1]);
2417 }else{
@@ -2551,11 +2591,11 @@
2551 if( i<nCol-1 && sCsv.cTerm!=sCsv.cSeparator ){
2552 fprintf(stderr, "%s:%d: expected %d columns but found %d - "
2553 "filling the rest with NULL\n",
2554 sCsv.zFile, startLine, nCol, i+1);
2555 i++;
2556 while( i<nCol ){ sqlite3_bind_null(pStmt, i); i++; }
2557 }
2558 }
2559 if( sCsv.cTerm==sCsv.cSeparator ){
2560 do{
2561 csv_read_one_field(&sCsv);
2562
--- src/shell.c
+++ src/shell.c
@@ -1577,13 +1577,15 @@
1577 ".databases List names and files of attached databases\n"
1578 ".dump ?TABLE? ... Dump the database in an SQL text format\n"
1579 " If TABLE specified, only dump tables matching\n"
1580 " LIKE pattern TABLE.\n"
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"
@@ -2408,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{
@@ -2551,11 +2591,11 @@
2591 if( i<nCol-1 && sCsv.cTerm!=sCsv.cSeparator ){
2592 fprintf(stderr, "%s:%d: expected %d columns but found %d - "
2593 "filling the rest with NULL\n",
2594 sCsv.zFile, startLine, nCol, i+1);
2595 i++;
2596 while( i<=nCol ){ sqlite3_bind_null(pStmt, i); i++; }
2597 }
2598 }
2599 if( sCsv.cTerm==sCsv.cSeparator ){
2600 do{
2601 csv_read_one_field(&sCsv);
2602

Keyboard Shortcuts

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