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 08:57 branch-1.29 merge
Commit 4f62b6017b7e38b3d24ef7115a2c20195345d8e0
Parent 1b648ebacf84484…
2 files changed +136 -29 +136 -29
~ src/shell.c ~ src/shell.c
M src/shell.c
+136 -29
--- src/shell.c
+++ src/shell.c
@@ -62,10 +62,11 @@
6262
 # define stifle_history(X)
6363
 #endif
6464
6565
 #if defined(_WIN32) || defined(WIN32)
6666
 # include <io.h>
67
+# include <fcntl.h>
6768
 #define isatty(h) _isatty(h)
6869
 #ifndef access
6970
 # define access(f,m) _access((f),(m))
7071
 #endif
7172
 #undef popen
@@ -456,10 +457,11 @@
456457
 int mode; /* An output mode setting */
457458
 int writableSchema; /* True if PRAGMA writable_schema=ON */
458459
 int showHeader; /* True to show column names in List or Column mode */
459460
 char *zDestTable; /* Name of destination table when MODE_Insert */
460461
 char separator[20]; /* Separator character for MODE_List */
462
+ char newline[20]; /* Record separator in MODE_Csv */
461463
 int colWidth[100]; /* Requested width of each column when in column mode*/
462464
 int actualWidth[100]; /* Actual width of each column */
463465
 char nullvalue[20]; /* The text to print when a NULL comes back from
464466
 ** the database */
465467
 struct previous_mode_data explainPrev;
@@ -657,11 +659,12 @@
657659
 };
658660
659661
 /*
660662
 ** Output a single term of CSV. Actually, p->separator is used for
661663
 ** the separator, which may or may not be a comma. p->nullvalue is
662
-** the null value. Strings are quoted if necessary.
664
+** the null value. Strings are quoted if necessary. The separator
665
+** is only issued if bSep is true.
663666
 */
664667
 static void output_csv(struct callback_data *p, const char *z, int bSep){
665668
 FILE *out = p->out;
666669
 if( z==0 ){
667670
 fprintf(out,"%s",p->nullvalue);
@@ -853,21 +856,30 @@
853856
 }
854857
 fprintf(p->out,"\n");
855858
 break;
856859
 }
857860
 case MODE_Csv: {
861
+#if defined(WIN32) || defined(_WIN32)
862
+ fflush(p->out);
863
+ _setmode(_fileno(p->out), _O_BINARY);
864
+#endif
858865
 if( p->cnt++==0 && p->showHeader ){
859866
 for(i=0; i<nArg; i++){
860867
 output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1);
861868
 }
862
- fprintf(p->out,"\n");
869
+ fprintf(p->out,"%s",p->newline);
863870
 }
864
- if( azArg==0 ) break;
865
- for(i=0; i<nArg; i++){
866
- output_csv(p, azArg[i], i<nArg-1);
871
+ if( azArg>0 ){
872
+ for(i=0; i<nArg; i++){
873
+ output_csv(p, azArg[i], i<nArg-1);
874
+ }
875
+ fprintf(p->out,"%s",p->newline);
867876
 }
868
- fprintf(p->out,"\n");
877
+#if defined(WIN32) || defined(_WIN32)
878
+ fflush(p->out);
879
+ _setmode(_fileno(p->out), _O_TEXT);
880
+#endif
869881
 break;
870882
 }
871883
 case MODE_Insert: {
872884
 p->cnt++;
873885
 if( azArg==0 ) break;
@@ -1150,12 +1162,14 @@
11501162
 fprintf(pArg->out, "Fullscan Steps: %d\n", iCur);
11511163
 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_SORT, bReset);
11521164
 fprintf(pArg->out, "Sort Operations: %d\n", iCur);
11531165
 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_AUTOINDEX, bReset);
11541166
 fprintf(pArg->out, "Autoindex Inserts: %d\n", iCur);
1155
- iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_VM_STEP, bReset);
1156
- fprintf(pArg->out, "Virtual Machine Steps: %d\n", iCur);
1167
+ if( sqlite3_libversion_number()>=3008000 ){
1168
+ iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_VM_STEP, bReset);
1169
+ fprintf(pArg->out, "Virtual Machine Steps: %d\n", iCur);
1170
+ }
11571171
 }
11581172
11591173
 return 0;
11601174
 }
11611175
@@ -1617,11 +1631,12 @@
16171631
 ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n"
16181632
 ".save FILE Write in-memory database into FILE\n"
16191633
 ".schema ?TABLE? Show the CREATE statements\n"
16201634
 " If TABLE specified, only show tables matching\n"
16211635
 " LIKE pattern TABLE.\n"
1622
- ".separator STRING Change separator used by output mode and .import\n"
1636
+ ".separator STRING ?NL? Change separator used by output mode and .import\n"
1637
+ " NL is the end-of-line mark for CSV\n"
16231638
 ".shell CMD ARGS... Run CMD ARGS... in a system shell\n"
16241639
 ".show Show the current values for various settings\n"
16251640
 ".stats on|off Turn stats on or off\n"
16261641
 ".system CMD ARGS... Run CMD ARGS... in a system shell\n"
16271642
 ".tables ?TABLE? List names of tables\n"
@@ -1635,10 +1650,73 @@
16351650
 " Negative values right-justify\n"
16361651
 ;
16371652
16381653
 /* Forward reference */
16391654
 static int process_input(struct callback_data *p, FILE *in);
1655
+/*
1656
+** Implementation of the "readfile(X)" SQL function. The entire content
1657
+** of the file named X is read and returned as a BLOB. NULL is returned
1658
+** if the file does not exist or is unreadable.
1659
+*/
1660
+static void readfileFunc(
1661
+ sqlite3_context *context,
1662
+ int argc,
1663
+ sqlite3_value **argv
1664
+){
1665
+ const char *zName;
1666
+ FILE *in;
1667
+ long nIn;
1668
+ void *pBuf;
1669
+
1670
+ zName = (const char*)sqlite3_value_text(argv[0]);
1671
+ if( zName==0 ) return;
1672
+ in = fopen(zName, "rb");
1673
+ if( in==0 ) return;
1674
+ fseek(in, 0, SEEK_END);
1675
+ nIn = ftell(in);
1676
+ rewind(in);
1677
+ pBuf = sqlite3_malloc( nIn );
1678
+ if( pBuf && 1==fread(pBuf, nIn, 1, in) ){
1679
+ sqlite3_result_blob(context, pBuf, nIn, sqlite3_free);
1680
+ }else{
1681
+ sqlite3_free(pBuf);
1682
+ }
1683
+ fclose(in);
1684
+}
1685
+
1686
+/*
1687
+** Implementation of the "writefile(X,Y)" SQL function. The argument Y
1688
+** is written into file X. The number of bytes written is returned. Or
1689
+** NULL is returned if something goes wrong, such as being unable to open
1690
+** file X for writing.
1691
+*/
1692
+static void writefileFunc(
1693
+ sqlite3_context *context,
1694
+ int argc,
1695
+ sqlite3_value **argv
1696
+){
1697
+ FILE *out;
1698
+ const char *z;
1699
+ int n;
1700
+ sqlite3_int64 rc;
1701
+ const char *zFile;
1702
+
1703
+ zFile = (const char*)sqlite3_value_text(argv[0]);
1704
+ if( zFile==0 ) return;
1705
+ out = fopen(zFile, "wb");
1706
+ if( out==0 ) return;
1707
+ z = (const char*)sqlite3_value_blob(argv[1]);
1708
+ if( z==0 ){
1709
+ n = 0;
1710
+ rc = 0;
1711
+ }else{
1712
+ n = sqlite3_value_bytes(argv[1]);
1713
+ rc = fwrite(z, 1, n, out);
1714
+ }
1715
+ fclose(out);
1716
+ sqlite3_result_int64(context, rc);
1717
+}
16401718
16411719
 /*
16421720
 ** Make sure the database is open. If it is not, then open it. If
16431721
 ** the database fails to open, print an error message and exit.
16441722
 */
@@ -1658,10 +1736,14 @@
16581736
 exit(1);
16591737
 }
16601738
 #ifndef SQLITE_OMIT_LOAD_EXTENSION
16611739
 sqlite3_enable_load_extension(p->db, 1);
16621740
 #endif
1741
+ sqlite3_create_function(db, "readfile", 1, SQLITE_UTF8, 0,
1742
+ readfileFunc, 0, 0);
1743
+ sqlite3_create_function(db, "writefile", 2, SQLITE_UTF8, 0,
1744
+ writefileFunc, 0, 0);
16631745
 }
16641746
 }
16651747
16661748
 /*
16671749
 ** Do C-language style dequoting.
@@ -2414,10 +2496,11 @@
24142496
 }else
24152497
24162498
 if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){
24172499
 struct callback_data data;
24182500
 char *zErrMsg = 0;
2501
+ int doStats = 0;
24192502
 if( nArg!=1 ){
24202503
 fprintf(stderr, "Usage: .fullschema\n");
24212504
 rc = 1;
24222505
 goto meta_command_exit;
24232506
 }
@@ -2432,25 +2515,37 @@
24322515
 " SELECT sql, type, tbl_name, name, rowid FROM sqlite_temp_master) "
24332516
 "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'"
24342517
 "ORDER BY rowid",
24352518
 callback, &data, &zErrMsg
24362519
 );
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);
2520
+ if( rc==SQLITE_OK ){
2521
+ sqlite3_stmt *pStmt;
2522
+ rc = sqlite3_prepare_v2(p->db,
2523
+ "SELECT rowid FROM sqlite_master"
2524
+ " WHERE name GLOB 'sqlite_stat[134]'",
2525
+ -1, &pStmt, 0);
2526
+ doStats = sqlite3_step(pStmt)==SQLITE_ROW;
2527
+ sqlite3_finalize(pStmt);
2528
+ }
2529
+ if( doStats==0 ){
2530
+ fprintf(p->out, "/* No STAT tables available */\n");
2531
+ }else{
2532
+ fprintf(p->out, "ANALYZE sqlite_master;\n");
2533
+ sqlite3_exec(p->db, "SELECT 'ANALYZE sqlite_master'",
2534
+ callback, &data, &zErrMsg);
2535
+ data.mode = MODE_Insert;
2536
+ data.zDestTable = "sqlite_stat1";
2537
+ shell_exec(p->db, "SELECT * FROM sqlite_stat1",
2538
+ shell_callback, &data,&zErrMsg);
2539
+ data.zDestTable = "sqlite_stat3";
2540
+ shell_exec(p->db, "SELECT * FROM sqlite_stat3",
2541
+ shell_callback, &data,&zErrMsg);
2542
+ data.zDestTable = "sqlite_stat4";
2543
+ shell_exec(p->db, "SELECT * FROM sqlite_stat4",
2544
+ shell_callback, &data, &zErrMsg);
2545
+ fprintf(p->out, "ANALYZE sqlite_master;\n");
2546
+ }
24522547
 }else
24532548
24542549
 if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
24552550
 if( nArg==2 ){
24562551
 p->showHeader = booleanValue(azArg[1]);
@@ -2736,10 +2831,11 @@
27362831
 p->mode = MODE_Tcl;
27372832
 sqlite3_snprintf(sizeof(p->separator), p->separator, " ");
27382833
 }else if( c2=='c' && strncmp(azArg[1],"csv",n2)==0 ){
27392834
 p->mode = MODE_Csv;
27402835
 sqlite3_snprintf(sizeof(p->separator), p->separator, ",");
2836
+ sqlite3_snprintf(sizeof(p->newline), p->newline, "\r\n");
27412837
 }else if( c2=='t' && strncmp(azArg[1],"tabs",n2)==0 ){
27422838
 p->mode = MODE_List;
27432839
 sqlite3_snprintf(sizeof(p->separator), p->separator, "\t");
27442840
 }else if( c2=='i' && strncmp(azArg[1],"insert",n2)==0 ){
27452841
 p->mode = MODE_Insert;
@@ -3014,17 +3110,20 @@
30143110
 }
30153111
 }else
30163112
 #endif
30173113
30183114
 if( c=='s' && strncmp(azArg[0], "separator", n)==0 ){
3019
- if( nArg==2 ){
3020
- sqlite3_snprintf(sizeof(p->separator), p->separator,
3021
- "%.*s", (int)sizeof(p->separator)-1, azArg[1]);
3022
- }else{
3023
- fprintf(stderr, "Usage: .separator STRING\n");
3115
+ if( nArg<2 || nArg>3 ){
3116
+ fprintf(stderr, "Usage: .separator SEPARATOR ?NEWLINE?\n");
30243117
 rc = 1;
30253118
 }
3119
+ if( nArg>=2 ){
3120
+ sqlite3_snprintf(sizeof(p->separator), p->separator, azArg[1]);
3121
+ }
3122
+ if( nArg>=3 ){
3123
+ sqlite3_snprintf(sizeof(p->newline), p->newline, azArg[2]);
3124
+ }
30263125
 }else
30273126
30283127
 if( c=='s'
30293128
 && (strncmp(azArg[0], "shell", n)==0 || strncmp(azArg[0],"system",n)==0)
30303129
 ){
@@ -3061,10 +3160,12 @@
30613160
 fprintf(p->out, "\n");
30623161
 fprintf(p->out,"%9.9s: %s\n","output",
30633162
 strlen30(p->outfile) ? p->outfile : "stdout");
30643163
 fprintf(p->out,"%9.9s: ", "separator");
30653164
 output_c_string(p->out, p->separator);
3165
+ fprintf(p->out," ");
3166
+ output_c_string(p->out, p->newline);
30663167
 fprintf(p->out, "\n");
30673168
 fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off");
30683169
 fprintf(p->out,"%9.9s: ","width");
30693170
 for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) {
30703171
 fprintf(p->out,"%d ",p->colWidth[i]);
@@ -3677,10 +3778,11 @@
36773778
 " -list set output mode to 'list'\n"
36783779
 " -mmap N default mmap size set to N\n"
36793780
 #ifdef SQLITE_ENABLE_MULTIPLEX
36803781
 " -multiplex enable the multiplexor VFS\n"
36813782
 #endif
3783
+ " -newline SEP set newline character(s) for CSV\n"
36823784
 " -nullvalue TEXT set text string for NULL values. Default ''\n"
36833785
 " -separator SEP set output field separator. Default: '|'\n"
36843786
 " -stats print memory stats before each finalize\n"
36853787
 " -version show SQLite version\n"
36863788
 " -vfs NAME use NAME as the default VFS\n"
@@ -3706,10 +3808,11 @@
37063808
 */
37073809
 static void main_init(struct callback_data *data) {
37083810
 memset(data, 0, sizeof(*data));
37093811
 data->mode = MODE_List;
37103812
 memcpy(data->separator,"|", 2);
3813
+ memcpy(data->newline,"\r\n", 3);
37113814
 data->showHeader = 0;
37123815
 sqlite3_config(SQLITE_CONFIG_URI, 1);
37133816
 sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data);
37143817
 sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> ");
37153818
 sqlite3_snprintf(sizeof(continuePrompt), continuePrompt," ...> ");
@@ -3798,10 +3901,11 @@
37983901
 return 1;
37993902
 }
38003903
 if( z[1]=='-' ) z++;
38013904
 if( strcmp(z,"-separator")==0
38023905
 || strcmp(z,"-nullvalue")==0
3906
+ || strcmp(z,"-newline")==0
38033907
 || strcmp(z,"-cmd")==0
38043908
 ){
38053909
 (void)cmdline_option_value(argc, argv, ++i);
38063910
 }else if( strcmp(z,"-init")==0 ){
38073911
 zInitFile = cmdline_option_value(argc, argv, ++i);
@@ -3907,10 +4011,13 @@
39074011
 data.mode = MODE_Csv;
39084012
 memcpy(data.separator,",",2);
39094013
 }else if( strcmp(z,"-separator")==0 ){
39104014
 sqlite3_snprintf(sizeof(data.separator), data.separator,
39114015
 "%s",cmdline_option_value(argc,argv,++i));
4016
+ }else if( strcmp(z,"-newline")==0 ){
4017
+ sqlite3_snprintf(sizeof(data.newline), data.newline,
4018
+ "%s",cmdline_option_value(argc,argv,++i));
39124019
 }else if( strcmp(z,"-nullvalue")==0 ){
39134020
 sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue,
39144021
 "%s",cmdline_option_value(argc,argv,++i));
39154022
 }else if( strcmp(z,"-header")==0 ){
39164023
 data.showHeader = 1;
39174024
--- src/shell.c
+++ src/shell.c
@@ -62,10 +62,11 @@
62 # define stifle_history(X)
63 #endif
64
65 #if defined(_WIN32) || defined(WIN32)
66 # include <io.h>
 
67 #define isatty(h) _isatty(h)
68 #ifndef access
69 # define access(f,m) _access((f),(m))
70 #endif
71 #undef popen
@@ -456,10 +457,11 @@
456 int mode; /* An output mode setting */
457 int writableSchema; /* True if PRAGMA writable_schema=ON */
458 int showHeader; /* True to show column names in List or Column mode */
459 char *zDestTable; /* Name of destination table when MODE_Insert */
460 char separator[20]; /* Separator character for MODE_List */
 
461 int colWidth[100]; /* Requested width of each column when in column mode*/
462 int actualWidth[100]; /* Actual width of each column */
463 char nullvalue[20]; /* The text to print when a NULL comes back from
464 ** the database */
465 struct previous_mode_data explainPrev;
@@ -657,11 +659,12 @@
657 };
658
659 /*
660 ** Output a single term of CSV. Actually, p->separator is used for
661 ** the separator, which may or may not be a comma. p->nullvalue is
662 ** the null value. Strings are quoted if necessary.
 
663 */
664 static void output_csv(struct callback_data *p, const char *z, int bSep){
665 FILE *out = p->out;
666 if( z==0 ){
667 fprintf(out,"%s",p->nullvalue);
@@ -853,21 +856,30 @@
853 }
854 fprintf(p->out,"\n");
855 break;
856 }
857 case MODE_Csv: {
 
 
 
 
858 if( p->cnt++==0 && p->showHeader ){
859 for(i=0; i<nArg; i++){
860 output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1);
861 }
862 fprintf(p->out,"\n");
863 }
864 if( azArg==0 ) break;
865 for(i=0; i<nArg; i++){
866 output_csv(p, azArg[i], i<nArg-1);
 
 
867 }
868 fprintf(p->out,"\n");
 
 
 
869 break;
870 }
871 case MODE_Insert: {
872 p->cnt++;
873 if( azArg==0 ) break;
@@ -1150,12 +1162,14 @@
1150 fprintf(pArg->out, "Fullscan Steps: %d\n", iCur);
1151 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_SORT, bReset);
1152 fprintf(pArg->out, "Sort Operations: %d\n", iCur);
1153 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_AUTOINDEX, bReset);
1154 fprintf(pArg->out, "Autoindex Inserts: %d\n", iCur);
1155 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_VM_STEP, bReset);
1156 fprintf(pArg->out, "Virtual Machine Steps: %d\n", iCur);
 
 
1157 }
1158
1159 return 0;
1160 }
1161
@@ -1617,11 +1631,12 @@
1617 ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n"
1618 ".save FILE Write in-memory database into FILE\n"
1619 ".schema ?TABLE? Show the CREATE statements\n"
1620 " If TABLE specified, only show tables matching\n"
1621 " LIKE pattern TABLE.\n"
1622 ".separator STRING Change separator used by output mode and .import\n"
 
1623 ".shell CMD ARGS... Run CMD ARGS... in a system shell\n"
1624 ".show Show the current values for various settings\n"
1625 ".stats on|off Turn stats on or off\n"
1626 ".system CMD ARGS... Run CMD ARGS... in a system shell\n"
1627 ".tables ?TABLE? List names of tables\n"
@@ -1635,10 +1650,73 @@
1635 " Negative values right-justify\n"
1636 ;
1637
1638 /* Forward reference */
1639 static int process_input(struct callback_data *p, FILE *in);
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1640
1641 /*
1642 ** Make sure the database is open. If it is not, then open it. If
1643 ** the database fails to open, print an error message and exit.
1644 */
@@ -1658,10 +1736,14 @@
1658 exit(1);
1659 }
1660 #ifndef SQLITE_OMIT_LOAD_EXTENSION
1661 sqlite3_enable_load_extension(p->db, 1);
1662 #endif
 
 
 
 
1663 }
1664 }
1665
1666 /*
1667 ** Do C-language style dequoting.
@@ -2414,10 +2496,11 @@
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 }
@@ -2432,25 +2515,37 @@
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]);
@@ -2736,10 +2831,11 @@
2736 p->mode = MODE_Tcl;
2737 sqlite3_snprintf(sizeof(p->separator), p->separator, " ");
2738 }else if( c2=='c' && strncmp(azArg[1],"csv",n2)==0 ){
2739 p->mode = MODE_Csv;
2740 sqlite3_snprintf(sizeof(p->separator), p->separator, ",");
 
2741 }else if( c2=='t' && strncmp(azArg[1],"tabs",n2)==0 ){
2742 p->mode = MODE_List;
2743 sqlite3_snprintf(sizeof(p->separator), p->separator, "\t");
2744 }else if( c2=='i' && strncmp(azArg[1],"insert",n2)==0 ){
2745 p->mode = MODE_Insert;
@@ -3014,17 +3110,20 @@
3014 }
3015 }else
3016 #endif
3017
3018 if( c=='s' && strncmp(azArg[0], "separator", n)==0 ){
3019 if( nArg==2 ){
3020 sqlite3_snprintf(sizeof(p->separator), p->separator,
3021 "%.*s", (int)sizeof(p->separator)-1, azArg[1]);
3022 }else{
3023 fprintf(stderr, "Usage: .separator STRING\n");
3024 rc = 1;
3025 }
 
 
 
 
 
 
3026 }else
3027
3028 if( c=='s'
3029 && (strncmp(azArg[0], "shell", n)==0 || strncmp(azArg[0],"system",n)==0)
3030 ){
@@ -3061,10 +3160,12 @@
3061 fprintf(p->out, "\n");
3062 fprintf(p->out,"%9.9s: %s\n","output",
3063 strlen30(p->outfile) ? p->outfile : "stdout");
3064 fprintf(p->out,"%9.9s: ", "separator");
3065 output_c_string(p->out, p->separator);
 
 
3066 fprintf(p->out, "\n");
3067 fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off");
3068 fprintf(p->out,"%9.9s: ","width");
3069 for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) {
3070 fprintf(p->out,"%d ",p->colWidth[i]);
@@ -3677,10 +3778,11 @@
3677 " -list set output mode to 'list'\n"
3678 " -mmap N default mmap size set to N\n"
3679 #ifdef SQLITE_ENABLE_MULTIPLEX
3680 " -multiplex enable the multiplexor VFS\n"
3681 #endif
 
3682 " -nullvalue TEXT set text string for NULL values. Default ''\n"
3683 " -separator SEP set output field separator. Default: '|'\n"
3684 " -stats print memory stats before each finalize\n"
3685 " -version show SQLite version\n"
3686 " -vfs NAME use NAME as the default VFS\n"
@@ -3706,10 +3808,11 @@
3706 */
3707 static void main_init(struct callback_data *data) {
3708 memset(data, 0, sizeof(*data));
3709 data->mode = MODE_List;
3710 memcpy(data->separator,"|", 2);
 
3711 data->showHeader = 0;
3712 sqlite3_config(SQLITE_CONFIG_URI, 1);
3713 sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data);
3714 sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> ");
3715 sqlite3_snprintf(sizeof(continuePrompt), continuePrompt," ...> ");
@@ -3798,10 +3901,11 @@
3798 return 1;
3799 }
3800 if( z[1]=='-' ) z++;
3801 if( strcmp(z,"-separator")==0
3802 || strcmp(z,"-nullvalue")==0
 
3803 || strcmp(z,"-cmd")==0
3804 ){
3805 (void)cmdline_option_value(argc, argv, ++i);
3806 }else if( strcmp(z,"-init")==0 ){
3807 zInitFile = cmdline_option_value(argc, argv, ++i);
@@ -3907,10 +4011,13 @@
3907 data.mode = MODE_Csv;
3908 memcpy(data.separator,",",2);
3909 }else if( strcmp(z,"-separator")==0 ){
3910 sqlite3_snprintf(sizeof(data.separator), data.separator,
3911 "%s",cmdline_option_value(argc,argv,++i));
 
 
 
3912 }else if( strcmp(z,"-nullvalue")==0 ){
3913 sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue,
3914 "%s",cmdline_option_value(argc,argv,++i));
3915 }else if( strcmp(z,"-header")==0 ){
3916 data.showHeader = 1;
3917
--- src/shell.c
+++ src/shell.c
@@ -62,10 +62,11 @@
62 # define stifle_history(X)
63 #endif
64
65 #if defined(_WIN32) || defined(WIN32)
66 # include <io.h>
67 # include <fcntl.h>
68 #define isatty(h) _isatty(h)
69 #ifndef access
70 # define access(f,m) _access((f),(m))
71 #endif
72 #undef popen
@@ -456,10 +457,11 @@
457 int mode; /* An output mode setting */
458 int writableSchema; /* True if PRAGMA writable_schema=ON */
459 int showHeader; /* True to show column names in List or Column mode */
460 char *zDestTable; /* Name of destination table when MODE_Insert */
461 char separator[20]; /* Separator character for MODE_List */
462 char newline[20]; /* Record separator in MODE_Csv */
463 int colWidth[100]; /* Requested width of each column when in column mode*/
464 int actualWidth[100]; /* Actual width of each column */
465 char nullvalue[20]; /* The text to print when a NULL comes back from
466 ** the database */
467 struct previous_mode_data explainPrev;
@@ -657,11 +659,12 @@
659 };
660
661 /*
662 ** Output a single term of CSV. Actually, p->separator is used for
663 ** the separator, which may or may not be a comma. p->nullvalue is
664 ** the null value. Strings are quoted if necessary. The separator
665 ** is only issued if bSep is true.
666 */
667 static void output_csv(struct callback_data *p, const char *z, int bSep){
668 FILE *out = p->out;
669 if( z==0 ){
670 fprintf(out,"%s",p->nullvalue);
@@ -853,21 +856,30 @@
856 }
857 fprintf(p->out,"\n");
858 break;
859 }
860 case MODE_Csv: {
861 #if defined(WIN32) || defined(_WIN32)
862 fflush(p->out);
863 _setmode(_fileno(p->out), _O_BINARY);
864 #endif
865 if( p->cnt++==0 && p->showHeader ){
866 for(i=0; i<nArg; i++){
867 output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1);
868 }
869 fprintf(p->out,"%s",p->newline);
870 }
871 if( azArg>0 ){
872 for(i=0; i<nArg; i++){
873 output_csv(p, azArg[i], i<nArg-1);
874 }
875 fprintf(p->out,"%s",p->newline);
876 }
877 #if defined(WIN32) || defined(_WIN32)
878 fflush(p->out);
879 _setmode(_fileno(p->out), _O_TEXT);
880 #endif
881 break;
882 }
883 case MODE_Insert: {
884 p->cnt++;
885 if( azArg==0 ) break;
@@ -1150,12 +1162,14 @@
1162 fprintf(pArg->out, "Fullscan Steps: %d\n", iCur);
1163 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_SORT, bReset);
1164 fprintf(pArg->out, "Sort Operations: %d\n", iCur);
1165 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_AUTOINDEX, bReset);
1166 fprintf(pArg->out, "Autoindex Inserts: %d\n", iCur);
1167 if( sqlite3_libversion_number()>=3008000 ){
1168 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_VM_STEP, bReset);
1169 fprintf(pArg->out, "Virtual Machine Steps: %d\n", iCur);
1170 }
1171 }
1172
1173 return 0;
1174 }
1175
@@ -1617,11 +1631,12 @@
1631 ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n"
1632 ".save FILE Write in-memory database into FILE\n"
1633 ".schema ?TABLE? Show the CREATE statements\n"
1634 " If TABLE specified, only show tables matching\n"
1635 " LIKE pattern TABLE.\n"
1636 ".separator STRING ?NL? Change separator used by output mode and .import\n"
1637 " NL is the end-of-line mark for CSV\n"
1638 ".shell CMD ARGS... Run CMD ARGS... in a system shell\n"
1639 ".show Show the current values for various settings\n"
1640 ".stats on|off Turn stats on or off\n"
1641 ".system CMD ARGS... Run CMD ARGS... in a system shell\n"
1642 ".tables ?TABLE? List names of tables\n"
@@ -1635,10 +1650,73 @@
1650 " Negative values right-justify\n"
1651 ;
1652
1653 /* Forward reference */
1654 static int process_input(struct callback_data *p, FILE *in);
1655 /*
1656 ** Implementation of the "readfile(X)" SQL function. The entire content
1657 ** of the file named X is read and returned as a BLOB. NULL is returned
1658 ** if the file does not exist or is unreadable.
1659 */
1660 static void readfileFunc(
1661 sqlite3_context *context,
1662 int argc,
1663 sqlite3_value **argv
1664 ){
1665 const char *zName;
1666 FILE *in;
1667 long nIn;
1668 void *pBuf;
1669
1670 zName = (const char*)sqlite3_value_text(argv[0]);
1671 if( zName==0 ) return;
1672 in = fopen(zName, "rb");
1673 if( in==0 ) return;
1674 fseek(in, 0, SEEK_END);
1675 nIn = ftell(in);
1676 rewind(in);
1677 pBuf = sqlite3_malloc( nIn );
1678 if( pBuf && 1==fread(pBuf, nIn, 1, in) ){
1679 sqlite3_result_blob(context, pBuf, nIn, sqlite3_free);
1680 }else{
1681 sqlite3_free(pBuf);
1682 }
1683 fclose(in);
1684 }
1685
1686 /*
1687 ** Implementation of the "writefile(X,Y)" SQL function. The argument Y
1688 ** is written into file X. The number of bytes written is returned. Or
1689 ** NULL is returned if something goes wrong, such as being unable to open
1690 ** file X for writing.
1691 */
1692 static void writefileFunc(
1693 sqlite3_context *context,
1694 int argc,
1695 sqlite3_value **argv
1696 ){
1697 FILE *out;
1698 const char *z;
1699 int n;
1700 sqlite3_int64 rc;
1701 const char *zFile;
1702
1703 zFile = (const char*)sqlite3_value_text(argv[0]);
1704 if( zFile==0 ) return;
1705 out = fopen(zFile, "wb");
1706 if( out==0 ) return;
1707 z = (const char*)sqlite3_value_blob(argv[1]);
1708 if( z==0 ){
1709 n = 0;
1710 rc = 0;
1711 }else{
1712 n = sqlite3_value_bytes(argv[1]);
1713 rc = fwrite(z, 1, n, out);
1714 }
1715 fclose(out);
1716 sqlite3_result_int64(context, rc);
1717 }
1718
1719 /*
1720 ** Make sure the database is open. If it is not, then open it. If
1721 ** the database fails to open, print an error message and exit.
1722 */
@@ -1658,10 +1736,14 @@
1736 exit(1);
1737 }
1738 #ifndef SQLITE_OMIT_LOAD_EXTENSION
1739 sqlite3_enable_load_extension(p->db, 1);
1740 #endif
1741 sqlite3_create_function(db, "readfile", 1, SQLITE_UTF8, 0,
1742 readfileFunc, 0, 0);
1743 sqlite3_create_function(db, "writefile", 2, SQLITE_UTF8, 0,
1744 writefileFunc, 0, 0);
1745 }
1746 }
1747
1748 /*
1749 ** Do C-language style dequoting.
@@ -2414,10 +2496,11 @@
2496 }else
2497
2498 if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){
2499 struct callback_data data;
2500 char *zErrMsg = 0;
2501 int doStats = 0;
2502 if( nArg!=1 ){
2503 fprintf(stderr, "Usage: .fullschema\n");
2504 rc = 1;
2505 goto meta_command_exit;
2506 }
@@ -2432,25 +2515,37 @@
2515 " SELECT sql, type, tbl_name, name, rowid FROM sqlite_temp_master) "
2516 "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'"
2517 "ORDER BY rowid",
2518 callback, &data, &zErrMsg
2519 );
2520 if( rc==SQLITE_OK ){
2521 sqlite3_stmt *pStmt;
2522 rc = sqlite3_prepare_v2(p->db,
2523 "SELECT rowid FROM sqlite_master"
2524 " WHERE name GLOB 'sqlite_stat[134]'",
2525 -1, &pStmt, 0);
2526 doStats = sqlite3_step(pStmt)==SQLITE_ROW;
2527 sqlite3_finalize(pStmt);
2528 }
2529 if( doStats==0 ){
2530 fprintf(p->out, "/* No STAT tables available */\n");
2531 }else{
2532 fprintf(p->out, "ANALYZE sqlite_master;\n");
2533 sqlite3_exec(p->db, "SELECT 'ANALYZE sqlite_master'",
2534 callback, &data, &zErrMsg);
2535 data.mode = MODE_Insert;
2536 data.zDestTable = "sqlite_stat1";
2537 shell_exec(p->db, "SELECT * FROM sqlite_stat1",
2538 shell_callback, &data,&zErrMsg);
2539 data.zDestTable = "sqlite_stat3";
2540 shell_exec(p->db, "SELECT * FROM sqlite_stat3",
2541 shell_callback, &data,&zErrMsg);
2542 data.zDestTable = "sqlite_stat4";
2543 shell_exec(p->db, "SELECT * FROM sqlite_stat4",
2544 shell_callback, &data, &zErrMsg);
2545 fprintf(p->out, "ANALYZE sqlite_master;\n");
2546 }
2547 }else
2548
2549 if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
2550 if( nArg==2 ){
2551 p->showHeader = booleanValue(azArg[1]);
@@ -2736,10 +2831,11 @@
2831 p->mode = MODE_Tcl;
2832 sqlite3_snprintf(sizeof(p->separator), p->separator, " ");
2833 }else if( c2=='c' && strncmp(azArg[1],"csv",n2)==0 ){
2834 p->mode = MODE_Csv;
2835 sqlite3_snprintf(sizeof(p->separator), p->separator, ",");
2836 sqlite3_snprintf(sizeof(p->newline), p->newline, "\r\n");
2837 }else if( c2=='t' && strncmp(azArg[1],"tabs",n2)==0 ){
2838 p->mode = MODE_List;
2839 sqlite3_snprintf(sizeof(p->separator), p->separator, "\t");
2840 }else if( c2=='i' && strncmp(azArg[1],"insert",n2)==0 ){
2841 p->mode = MODE_Insert;
@@ -3014,17 +3110,20 @@
3110 }
3111 }else
3112 #endif
3113
3114 if( c=='s' && strncmp(azArg[0], "separator", n)==0 ){
3115 if( nArg<2 || nArg>3 ){
3116 fprintf(stderr, "Usage: .separator SEPARATOR ?NEWLINE?\n");
 
 
 
3117 rc = 1;
3118 }
3119 if( nArg>=2 ){
3120 sqlite3_snprintf(sizeof(p->separator), p->separator, azArg[1]);
3121 }
3122 if( nArg>=3 ){
3123 sqlite3_snprintf(sizeof(p->newline), p->newline, azArg[2]);
3124 }
3125 }else
3126
3127 if( c=='s'
3128 && (strncmp(azArg[0], "shell", n)==0 || strncmp(azArg[0],"system",n)==0)
3129 ){
@@ -3061,10 +3160,12 @@
3160 fprintf(p->out, "\n");
3161 fprintf(p->out,"%9.9s: %s\n","output",
3162 strlen30(p->outfile) ? p->outfile : "stdout");
3163 fprintf(p->out,"%9.9s: ", "separator");
3164 output_c_string(p->out, p->separator);
3165 fprintf(p->out," ");
3166 output_c_string(p->out, p->newline);
3167 fprintf(p->out, "\n");
3168 fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off");
3169 fprintf(p->out,"%9.9s: ","width");
3170 for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) {
3171 fprintf(p->out,"%d ",p->colWidth[i]);
@@ -3677,10 +3778,11 @@
3778 " -list set output mode to 'list'\n"
3779 " -mmap N default mmap size set to N\n"
3780 #ifdef SQLITE_ENABLE_MULTIPLEX
3781 " -multiplex enable the multiplexor VFS\n"
3782 #endif
3783 " -newline SEP set newline character(s) for CSV\n"
3784 " -nullvalue TEXT set text string for NULL values. Default ''\n"
3785 " -separator SEP set output field separator. Default: '|'\n"
3786 " -stats print memory stats before each finalize\n"
3787 " -version show SQLite version\n"
3788 " -vfs NAME use NAME as the default VFS\n"
@@ -3706,10 +3808,11 @@
3808 */
3809 static void main_init(struct callback_data *data) {
3810 memset(data, 0, sizeof(*data));
3811 data->mode = MODE_List;
3812 memcpy(data->separator,"|", 2);
3813 memcpy(data->newline,"\r\n", 3);
3814 data->showHeader = 0;
3815 sqlite3_config(SQLITE_CONFIG_URI, 1);
3816 sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data);
3817 sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> ");
3818 sqlite3_snprintf(sizeof(continuePrompt), continuePrompt," ...> ");
@@ -3798,10 +3901,11 @@
3901 return 1;
3902 }
3903 if( z[1]=='-' ) z++;
3904 if( strcmp(z,"-separator")==0
3905 || strcmp(z,"-nullvalue")==0
3906 || strcmp(z,"-newline")==0
3907 || strcmp(z,"-cmd")==0
3908 ){
3909 (void)cmdline_option_value(argc, argv, ++i);
3910 }else if( strcmp(z,"-init")==0 ){
3911 zInitFile = cmdline_option_value(argc, argv, ++i);
@@ -3907,10 +4011,13 @@
4011 data.mode = MODE_Csv;
4012 memcpy(data.separator,",",2);
4013 }else if( strcmp(z,"-separator")==0 ){
4014 sqlite3_snprintf(sizeof(data.separator), data.separator,
4015 "%s",cmdline_option_value(argc,argv,++i));
4016 }else if( strcmp(z,"-newline")==0 ){
4017 sqlite3_snprintf(sizeof(data.newline), data.newline,
4018 "%s",cmdline_option_value(argc,argv,++i));
4019 }else if( strcmp(z,"-nullvalue")==0 ){
4020 sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue,
4021 "%s",cmdline_option_value(argc,argv,++i));
4022 }else if( strcmp(z,"-header")==0 ){
4023 data.showHeader = 1;
4024
M src/shell.c
+136 -29
--- src/shell.c
+++ src/shell.c
@@ -62,10 +62,11 @@
6262
 # define stifle_history(X)
6363
 #endif
6464
6565
 #if defined(_WIN32) || defined(WIN32)
6666
 # include <io.h>
67
+# include <fcntl.h>
6768
 #define isatty(h) _isatty(h)
6869
 #ifndef access
6970
 # define access(f,m) _access((f),(m))
7071
 #endif
7172
 #undef popen
@@ -456,10 +457,11 @@
456457
 int mode; /* An output mode setting */
457458
 int writableSchema; /* True if PRAGMA writable_schema=ON */
458459
 int showHeader; /* True to show column names in List or Column mode */
459460
 char *zDestTable; /* Name of destination table when MODE_Insert */
460461
 char separator[20]; /* Separator character for MODE_List */
462
+ char newline[20]; /* Record separator in MODE_Csv */
461463
 int colWidth[100]; /* Requested width of each column when in column mode*/
462464
 int actualWidth[100]; /* Actual width of each column */
463465
 char nullvalue[20]; /* The text to print when a NULL comes back from
464466
 ** the database */
465467
 struct previous_mode_data explainPrev;
@@ -657,11 +659,12 @@
657659
 };
658660
659661
 /*
660662
 ** Output a single term of CSV. Actually, p->separator is used for
661663
 ** the separator, which may or may not be a comma. p->nullvalue is
662
-** the null value. Strings are quoted if necessary.
664
+** the null value. Strings are quoted if necessary. The separator
665
+** is only issued if bSep is true.
663666
 */
664667
 static void output_csv(struct callback_data *p, const char *z, int bSep){
665668
 FILE *out = p->out;
666669
 if( z==0 ){
667670
 fprintf(out,"%s",p->nullvalue);
@@ -853,21 +856,30 @@
853856
 }
854857
 fprintf(p->out,"\n");
855858
 break;
856859
 }
857860
 case MODE_Csv: {
861
+#if defined(WIN32) || defined(_WIN32)
862
+ fflush(p->out);
863
+ _setmode(_fileno(p->out), _O_BINARY);
864
+#endif
858865
 if( p->cnt++==0 && p->showHeader ){
859866
 for(i=0; i<nArg; i++){
860867
 output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1);
861868
 }
862
- fprintf(p->out,"\n");
869
+ fprintf(p->out,"%s",p->newline);
863870
 }
864
- if( azArg==0 ) break;
865
- for(i=0; i<nArg; i++){
866
- output_csv(p, azArg[i], i<nArg-1);
871
+ if( azArg>0 ){
872
+ for(i=0; i<nArg; i++){
873
+ output_csv(p, azArg[i], i<nArg-1);
874
+ }
875
+ fprintf(p->out,"%s",p->newline);
867876
 }
868
- fprintf(p->out,"\n");
877
+#if defined(WIN32) || defined(_WIN32)
878
+ fflush(p->out);
879
+ _setmode(_fileno(p->out), _O_TEXT);
880
+#endif
869881
 break;
870882
 }
871883
 case MODE_Insert: {
872884
 p->cnt++;
873885
 if( azArg==0 ) break;
@@ -1150,12 +1162,14 @@
11501162
 fprintf(pArg->out, "Fullscan Steps: %d\n", iCur);
11511163
 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_SORT, bReset);
11521164
 fprintf(pArg->out, "Sort Operations: %d\n", iCur);
11531165
 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_AUTOINDEX, bReset);
11541166
 fprintf(pArg->out, "Autoindex Inserts: %d\n", iCur);
1155
- iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_VM_STEP, bReset);
1156
- fprintf(pArg->out, "Virtual Machine Steps: %d\n", iCur);
1167
+ if( sqlite3_libversion_number()>=3008000 ){
1168
+ iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_VM_STEP, bReset);
1169
+ fprintf(pArg->out, "Virtual Machine Steps: %d\n", iCur);
1170
+ }
11571171
 }
11581172
11591173
 return 0;
11601174
 }
11611175
@@ -1617,11 +1631,12 @@
16171631
 ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n"
16181632
 ".save FILE Write in-memory database into FILE\n"
16191633
 ".schema ?TABLE? Show the CREATE statements\n"
16201634
 " If TABLE specified, only show tables matching\n"
16211635
 " LIKE pattern TABLE.\n"
1622
- ".separator STRING Change separator used by output mode and .import\n"
1636
+ ".separator STRING ?NL? Change separator used by output mode and .import\n"
1637
+ " NL is the end-of-line mark for CSV\n"
16231638
 ".shell CMD ARGS... Run CMD ARGS... in a system shell\n"
16241639
 ".show Show the current values for various settings\n"
16251640
 ".stats on|off Turn stats on or off\n"
16261641
 ".system CMD ARGS... Run CMD ARGS... in a system shell\n"
16271642
 ".tables ?TABLE? List names of tables\n"
@@ -1635,10 +1650,73 @@
16351650
 " Negative values right-justify\n"
16361651
 ;
16371652
16381653
 /* Forward reference */
16391654
 static int process_input(struct callback_data *p, FILE *in);
1655
+/*
1656
+** Implementation of the "readfile(X)" SQL function. The entire content
1657
+** of the file named X is read and returned as a BLOB. NULL is returned
1658
+** if the file does not exist or is unreadable.
1659
+*/
1660
+static void readfileFunc(
1661
+ sqlite3_context *context,
1662
+ int argc,
1663
+ sqlite3_value **argv
1664
+){
1665
+ const char *zName;
1666
+ FILE *in;
1667
+ long nIn;
1668
+ void *pBuf;
1669
+
1670
+ zName = (const char*)sqlite3_value_text(argv[0]);
1671
+ if( zName==0 ) return;
1672
+ in = fopen(zName, "rb");
1673
+ if( in==0 ) return;
1674
+ fseek(in, 0, SEEK_END);
1675
+ nIn = ftell(in);
1676
+ rewind(in);
1677
+ pBuf = sqlite3_malloc( nIn );
1678
+ if( pBuf && 1==fread(pBuf, nIn, 1, in) ){
1679
+ sqlite3_result_blob(context, pBuf, nIn, sqlite3_free);
1680
+ }else{
1681
+ sqlite3_free(pBuf);
1682
+ }
1683
+ fclose(in);
1684
+}
1685
+
1686
+/*
1687
+** Implementation of the "writefile(X,Y)" SQL function. The argument Y
1688
+** is written into file X. The number of bytes written is returned. Or
1689
+** NULL is returned if something goes wrong, such as being unable to open
1690
+** file X for writing.
1691
+*/
1692
+static void writefileFunc(
1693
+ sqlite3_context *context,
1694
+ int argc,
1695
+ sqlite3_value **argv
1696
+){
1697
+ FILE *out;
1698
+ const char *z;
1699
+ int n;
1700
+ sqlite3_int64 rc;
1701
+ const char *zFile;
1702
+
1703
+ zFile = (const char*)sqlite3_value_text(argv[0]);
1704
+ if( zFile==0 ) return;
1705
+ out = fopen(zFile, "wb");
1706
+ if( out==0 ) return;
1707
+ z = (const char*)sqlite3_value_blob(argv[1]);
1708
+ if( z==0 ){
1709
+ n = 0;
1710
+ rc = 0;
1711
+ }else{
1712
+ n = sqlite3_value_bytes(argv[1]);
1713
+ rc = fwrite(z, 1, n, out);
1714
+ }
1715
+ fclose(out);
1716
+ sqlite3_result_int64(context, rc);
1717
+}
16401718
16411719
 /*
16421720
 ** Make sure the database is open. If it is not, then open it. If
16431721
 ** the database fails to open, print an error message and exit.
16441722
 */
@@ -1658,10 +1736,14 @@
16581736
 exit(1);
16591737
 }
16601738
 #ifndef SQLITE_OMIT_LOAD_EXTENSION
16611739
 sqlite3_enable_load_extension(p->db, 1);
16621740
 #endif
1741
+ sqlite3_create_function(db, "readfile", 1, SQLITE_UTF8, 0,
1742
+ readfileFunc, 0, 0);
1743
+ sqlite3_create_function(db, "writefile", 2, SQLITE_UTF8, 0,
1744
+ writefileFunc, 0, 0);
16631745
 }
16641746
 }
16651747
16661748
 /*
16671749
 ** Do C-language style dequoting.
@@ -2414,10 +2496,11 @@
24142496
 }else
24152497
24162498
 if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){
24172499
 struct callback_data data;
24182500
 char *zErrMsg = 0;
2501
+ int doStats = 0;
24192502
 if( nArg!=1 ){
24202503
 fprintf(stderr, "Usage: .fullschema\n");
24212504
 rc = 1;
24222505
 goto meta_command_exit;
24232506
 }
@@ -2432,25 +2515,37 @@
24322515
 " SELECT sql, type, tbl_name, name, rowid FROM sqlite_temp_master) "
24332516
 "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'"
24342517
 "ORDER BY rowid",
24352518
 callback, &data, &zErrMsg
24362519
 );
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);
2520
+ if( rc==SQLITE_OK ){
2521
+ sqlite3_stmt *pStmt;
2522
+ rc = sqlite3_prepare_v2(p->db,
2523
+ "SELECT rowid FROM sqlite_master"
2524
+ " WHERE name GLOB 'sqlite_stat[134]'",
2525
+ -1, &pStmt, 0);
2526
+ doStats = sqlite3_step(pStmt)==SQLITE_ROW;
2527
+ sqlite3_finalize(pStmt);
2528
+ }
2529
+ if( doStats==0 ){
2530
+ fprintf(p->out, "/* No STAT tables available */\n");
2531
+ }else{
2532
+ fprintf(p->out, "ANALYZE sqlite_master;\n");
2533
+ sqlite3_exec(p->db, "SELECT 'ANALYZE sqlite_master'",
2534
+ callback, &data, &zErrMsg);
2535
+ data.mode = MODE_Insert;
2536
+ data.zDestTable = "sqlite_stat1";
2537
+ shell_exec(p->db, "SELECT * FROM sqlite_stat1",
2538
+ shell_callback, &data,&zErrMsg);
2539
+ data.zDestTable = "sqlite_stat3";
2540
+ shell_exec(p->db, "SELECT * FROM sqlite_stat3",
2541
+ shell_callback, &data,&zErrMsg);
2542
+ data.zDestTable = "sqlite_stat4";
2543
+ shell_exec(p->db, "SELECT * FROM sqlite_stat4",
2544
+ shell_callback, &data, &zErrMsg);
2545
+ fprintf(p->out, "ANALYZE sqlite_master;\n");
2546
+ }
24522547
 }else
24532548
24542549
 if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
24552550
 if( nArg==2 ){
24562551
 p->showHeader = booleanValue(azArg[1]);
@@ -2736,10 +2831,11 @@
27362831
 p->mode = MODE_Tcl;
27372832
 sqlite3_snprintf(sizeof(p->separator), p->separator, " ");
27382833
 }else if( c2=='c' && strncmp(azArg[1],"csv",n2)==0 ){
27392834
 p->mode = MODE_Csv;
27402835
 sqlite3_snprintf(sizeof(p->separator), p->separator, ",");
2836
+ sqlite3_snprintf(sizeof(p->newline), p->newline, "\r\n");
27412837
 }else if( c2=='t' && strncmp(azArg[1],"tabs",n2)==0 ){
27422838
 p->mode = MODE_List;
27432839
 sqlite3_snprintf(sizeof(p->separator), p->separator, "\t");
27442840
 }else if( c2=='i' && strncmp(azArg[1],"insert",n2)==0 ){
27452841
 p->mode = MODE_Insert;
@@ -3014,17 +3110,20 @@
30143110
 }
30153111
 }else
30163112
 #endif
30173113
30183114
 if( c=='s' && strncmp(azArg[0], "separator", n)==0 ){
3019
- if( nArg==2 ){
3020
- sqlite3_snprintf(sizeof(p->separator), p->separator,
3021
- "%.*s", (int)sizeof(p->separator)-1, azArg[1]);
3022
- }else{
3023
- fprintf(stderr, "Usage: .separator STRING\n");
3115
+ if( nArg<2 || nArg>3 ){
3116
+ fprintf(stderr, "Usage: .separator SEPARATOR ?NEWLINE?\n");
30243117
 rc = 1;
30253118
 }
3119
+ if( nArg>=2 ){
3120
+ sqlite3_snprintf(sizeof(p->separator), p->separator, azArg[1]);
3121
+ }
3122
+ if( nArg>=3 ){
3123
+ sqlite3_snprintf(sizeof(p->newline), p->newline, azArg[2]);
3124
+ }
30263125
 }else
30273126
30283127
 if( c=='s'
30293128
 && (strncmp(azArg[0], "shell", n)==0 || strncmp(azArg[0],"system",n)==0)
30303129
 ){
@@ -3061,10 +3160,12 @@
30613160
 fprintf(p->out, "\n");
30623161
 fprintf(p->out,"%9.9s: %s\n","output",
30633162
 strlen30(p->outfile) ? p->outfile : "stdout");
30643163
 fprintf(p->out,"%9.9s: ", "separator");
30653164
 output_c_string(p->out, p->separator);
3165
+ fprintf(p->out," ");
3166
+ output_c_string(p->out, p->newline);
30663167
 fprintf(p->out, "\n");
30673168
 fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off");
30683169
 fprintf(p->out,"%9.9s: ","width");
30693170
 for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) {
30703171
 fprintf(p->out,"%d ",p->colWidth[i]);
@@ -3677,10 +3778,11 @@
36773778
 " -list set output mode to 'list'\n"
36783779
 " -mmap N default mmap size set to N\n"
36793780
 #ifdef SQLITE_ENABLE_MULTIPLEX
36803781
 " -multiplex enable the multiplexor VFS\n"
36813782
 #endif
3783
+ " -newline SEP set newline character(s) for CSV\n"
36823784
 " -nullvalue TEXT set text string for NULL values. Default ''\n"
36833785
 " -separator SEP set output field separator. Default: '|'\n"
36843786
 " -stats print memory stats before each finalize\n"
36853787
 " -version show SQLite version\n"
36863788
 " -vfs NAME use NAME as the default VFS\n"
@@ -3706,10 +3808,11 @@
37063808
 */
37073809
 static void main_init(struct callback_data *data) {
37083810
 memset(data, 0, sizeof(*data));
37093811
 data->mode = MODE_List;
37103812
 memcpy(data->separator,"|", 2);
3813
+ memcpy(data->newline,"\r\n", 3);
37113814
 data->showHeader = 0;
37123815
 sqlite3_config(SQLITE_CONFIG_URI, 1);
37133816
 sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data);
37143817
 sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> ");
37153818
 sqlite3_snprintf(sizeof(continuePrompt), continuePrompt," ...> ");
@@ -3798,10 +3901,11 @@
37983901
 return 1;
37993902
 }
38003903
 if( z[1]=='-' ) z++;
38013904
 if( strcmp(z,"-separator")==0
38023905
 || strcmp(z,"-nullvalue")==0
3906
+ || strcmp(z,"-newline")==0
38033907
 || strcmp(z,"-cmd")==0
38043908
 ){
38053909
 (void)cmdline_option_value(argc, argv, ++i);
38063910
 }else if( strcmp(z,"-init")==0 ){
38073911
 zInitFile = cmdline_option_value(argc, argv, ++i);
@@ -3907,10 +4011,13 @@
39074011
 data.mode = MODE_Csv;
39084012
 memcpy(data.separator,",",2);
39094013
 }else if( strcmp(z,"-separator")==0 ){
39104014
 sqlite3_snprintf(sizeof(data.separator), data.separator,
39114015
 "%s",cmdline_option_value(argc,argv,++i));
4016
+ }else if( strcmp(z,"-newline")==0 ){
4017
+ sqlite3_snprintf(sizeof(data.newline), data.newline,
4018
+ "%s",cmdline_option_value(argc,argv,++i));
39124019
 }else if( strcmp(z,"-nullvalue")==0 ){
39134020
 sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue,
39144021
 "%s",cmdline_option_value(argc,argv,++i));
39154022
 }else if( strcmp(z,"-header")==0 ){
39164023
 data.showHeader = 1;
39174024
--- src/shell.c
+++ src/shell.c
@@ -62,10 +62,11 @@
62 # define stifle_history(X)
63 #endif
64
65 #if defined(_WIN32) || defined(WIN32)
66 # include <io.h>
 
67 #define isatty(h) _isatty(h)
68 #ifndef access
69 # define access(f,m) _access((f),(m))
70 #endif
71 #undef popen
@@ -456,10 +457,11 @@
456 int mode; /* An output mode setting */
457 int writableSchema; /* True if PRAGMA writable_schema=ON */
458 int showHeader; /* True to show column names in List or Column mode */
459 char *zDestTable; /* Name of destination table when MODE_Insert */
460 char separator[20]; /* Separator character for MODE_List */
 
461 int colWidth[100]; /* Requested width of each column when in column mode*/
462 int actualWidth[100]; /* Actual width of each column */
463 char nullvalue[20]; /* The text to print when a NULL comes back from
464 ** the database */
465 struct previous_mode_data explainPrev;
@@ -657,11 +659,12 @@
657 };
658
659 /*
660 ** Output a single term of CSV. Actually, p->separator is used for
661 ** the separator, which may or may not be a comma. p->nullvalue is
662 ** the null value. Strings are quoted if necessary.
 
663 */
664 static void output_csv(struct callback_data *p, const char *z, int bSep){
665 FILE *out = p->out;
666 if( z==0 ){
667 fprintf(out,"%s",p->nullvalue);
@@ -853,21 +856,30 @@
853 }
854 fprintf(p->out,"\n");
855 break;
856 }
857 case MODE_Csv: {
 
 
 
 
858 if( p->cnt++==0 && p->showHeader ){
859 for(i=0; i<nArg; i++){
860 output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1);
861 }
862 fprintf(p->out,"\n");
863 }
864 if( azArg==0 ) break;
865 for(i=0; i<nArg; i++){
866 output_csv(p, azArg[i], i<nArg-1);
 
 
867 }
868 fprintf(p->out,"\n");
 
 
 
869 break;
870 }
871 case MODE_Insert: {
872 p->cnt++;
873 if( azArg==0 ) break;
@@ -1150,12 +1162,14 @@
1150 fprintf(pArg->out, "Fullscan Steps: %d\n", iCur);
1151 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_SORT, bReset);
1152 fprintf(pArg->out, "Sort Operations: %d\n", iCur);
1153 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_AUTOINDEX, bReset);
1154 fprintf(pArg->out, "Autoindex Inserts: %d\n", iCur);
1155 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_VM_STEP, bReset);
1156 fprintf(pArg->out, "Virtual Machine Steps: %d\n", iCur);
 
 
1157 }
1158
1159 return 0;
1160 }
1161
@@ -1617,11 +1631,12 @@
1617 ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n"
1618 ".save FILE Write in-memory database into FILE\n"
1619 ".schema ?TABLE? Show the CREATE statements\n"
1620 " If TABLE specified, only show tables matching\n"
1621 " LIKE pattern TABLE.\n"
1622 ".separator STRING Change separator used by output mode and .import\n"
 
1623 ".shell CMD ARGS... Run CMD ARGS... in a system shell\n"
1624 ".show Show the current values for various settings\n"
1625 ".stats on|off Turn stats on or off\n"
1626 ".system CMD ARGS... Run CMD ARGS... in a system shell\n"
1627 ".tables ?TABLE? List names of tables\n"
@@ -1635,10 +1650,73 @@
1635 " Negative values right-justify\n"
1636 ;
1637
1638 /* Forward reference */
1639 static int process_input(struct callback_data *p, FILE *in);
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1640
1641 /*
1642 ** Make sure the database is open. If it is not, then open it. If
1643 ** the database fails to open, print an error message and exit.
1644 */
@@ -1658,10 +1736,14 @@
1658 exit(1);
1659 }
1660 #ifndef SQLITE_OMIT_LOAD_EXTENSION
1661 sqlite3_enable_load_extension(p->db, 1);
1662 #endif
 
 
 
 
1663 }
1664 }
1665
1666 /*
1667 ** Do C-language style dequoting.
@@ -2414,10 +2496,11 @@
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 }
@@ -2432,25 +2515,37 @@
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]);
@@ -2736,10 +2831,11 @@
2736 p->mode = MODE_Tcl;
2737 sqlite3_snprintf(sizeof(p->separator), p->separator, " ");
2738 }else if( c2=='c' && strncmp(azArg[1],"csv",n2)==0 ){
2739 p->mode = MODE_Csv;
2740 sqlite3_snprintf(sizeof(p->separator), p->separator, ",");
 
2741 }else if( c2=='t' && strncmp(azArg[1],"tabs",n2)==0 ){
2742 p->mode = MODE_List;
2743 sqlite3_snprintf(sizeof(p->separator), p->separator, "\t");
2744 }else if( c2=='i' && strncmp(azArg[1],"insert",n2)==0 ){
2745 p->mode = MODE_Insert;
@@ -3014,17 +3110,20 @@
3014 }
3015 }else
3016 #endif
3017
3018 if( c=='s' && strncmp(azArg[0], "separator", n)==0 ){
3019 if( nArg==2 ){
3020 sqlite3_snprintf(sizeof(p->separator), p->separator,
3021 "%.*s", (int)sizeof(p->separator)-1, azArg[1]);
3022 }else{
3023 fprintf(stderr, "Usage: .separator STRING\n");
3024 rc = 1;
3025 }
 
 
 
 
 
 
3026 }else
3027
3028 if( c=='s'
3029 && (strncmp(azArg[0], "shell", n)==0 || strncmp(azArg[0],"system",n)==0)
3030 ){
@@ -3061,10 +3160,12 @@
3061 fprintf(p->out, "\n");
3062 fprintf(p->out,"%9.9s: %s\n","output",
3063 strlen30(p->outfile) ? p->outfile : "stdout");
3064 fprintf(p->out,"%9.9s: ", "separator");
3065 output_c_string(p->out, p->separator);
 
 
3066 fprintf(p->out, "\n");
3067 fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off");
3068 fprintf(p->out,"%9.9s: ","width");
3069 for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) {
3070 fprintf(p->out,"%d ",p->colWidth[i]);
@@ -3677,10 +3778,11 @@
3677 " -list set output mode to 'list'\n"
3678 " -mmap N default mmap size set to N\n"
3679 #ifdef SQLITE_ENABLE_MULTIPLEX
3680 " -multiplex enable the multiplexor VFS\n"
3681 #endif
 
3682 " -nullvalue TEXT set text string for NULL values. Default ''\n"
3683 " -separator SEP set output field separator. Default: '|'\n"
3684 " -stats print memory stats before each finalize\n"
3685 " -version show SQLite version\n"
3686 " -vfs NAME use NAME as the default VFS\n"
@@ -3706,10 +3808,11 @@
3706 */
3707 static void main_init(struct callback_data *data) {
3708 memset(data, 0, sizeof(*data));
3709 data->mode = MODE_List;
3710 memcpy(data->separator,"|", 2);
 
3711 data->showHeader = 0;
3712 sqlite3_config(SQLITE_CONFIG_URI, 1);
3713 sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data);
3714 sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> ");
3715 sqlite3_snprintf(sizeof(continuePrompt), continuePrompt," ...> ");
@@ -3798,10 +3901,11 @@
3798 return 1;
3799 }
3800 if( z[1]=='-' ) z++;
3801 if( strcmp(z,"-separator")==0
3802 || strcmp(z,"-nullvalue")==0
 
3803 || strcmp(z,"-cmd")==0
3804 ){
3805 (void)cmdline_option_value(argc, argv, ++i);
3806 }else if( strcmp(z,"-init")==0 ){
3807 zInitFile = cmdline_option_value(argc, argv, ++i);
@@ -3907,10 +4011,13 @@
3907 data.mode = MODE_Csv;
3908 memcpy(data.separator,",",2);
3909 }else if( strcmp(z,"-separator")==0 ){
3910 sqlite3_snprintf(sizeof(data.separator), data.separator,
3911 "%s",cmdline_option_value(argc,argv,++i));
 
 
 
3912 }else if( strcmp(z,"-nullvalue")==0 ){
3913 sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue,
3914 "%s",cmdline_option_value(argc,argv,++i));
3915 }else if( strcmp(z,"-header")==0 ){
3916 data.showHeader = 1;
3917
--- src/shell.c
+++ src/shell.c
@@ -62,10 +62,11 @@
62 # define stifle_history(X)
63 #endif
64
65 #if defined(_WIN32) || defined(WIN32)
66 # include <io.h>
67 # include <fcntl.h>
68 #define isatty(h) _isatty(h)
69 #ifndef access
70 # define access(f,m) _access((f),(m))
71 #endif
72 #undef popen
@@ -456,10 +457,11 @@
457 int mode; /* An output mode setting */
458 int writableSchema; /* True if PRAGMA writable_schema=ON */
459 int showHeader; /* True to show column names in List or Column mode */
460 char *zDestTable; /* Name of destination table when MODE_Insert */
461 char separator[20]; /* Separator character for MODE_List */
462 char newline[20]; /* Record separator in MODE_Csv */
463 int colWidth[100]; /* Requested width of each column when in column mode*/
464 int actualWidth[100]; /* Actual width of each column */
465 char nullvalue[20]; /* The text to print when a NULL comes back from
466 ** the database */
467 struct previous_mode_data explainPrev;
@@ -657,11 +659,12 @@
659 };
660
661 /*
662 ** Output a single term of CSV. Actually, p->separator is used for
663 ** the separator, which may or may not be a comma. p->nullvalue is
664 ** the null value. Strings are quoted if necessary. The separator
665 ** is only issued if bSep is true.
666 */
667 static void output_csv(struct callback_data *p, const char *z, int bSep){
668 FILE *out = p->out;
669 if( z==0 ){
670 fprintf(out,"%s",p->nullvalue);
@@ -853,21 +856,30 @@
856 }
857 fprintf(p->out,"\n");
858 break;
859 }
860 case MODE_Csv: {
861 #if defined(WIN32) || defined(_WIN32)
862 fflush(p->out);
863 _setmode(_fileno(p->out), _O_BINARY);
864 #endif
865 if( p->cnt++==0 && p->showHeader ){
866 for(i=0; i<nArg; i++){
867 output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1);
868 }
869 fprintf(p->out,"%s",p->newline);
870 }
871 if( azArg>0 ){
872 for(i=0; i<nArg; i++){
873 output_csv(p, azArg[i], i<nArg-1);
874 }
875 fprintf(p->out,"%s",p->newline);
876 }
877 #if defined(WIN32) || defined(_WIN32)
878 fflush(p->out);
879 _setmode(_fileno(p->out), _O_TEXT);
880 #endif
881 break;
882 }
883 case MODE_Insert: {
884 p->cnt++;
885 if( azArg==0 ) break;
@@ -1150,12 +1162,14 @@
1162 fprintf(pArg->out, "Fullscan Steps: %d\n", iCur);
1163 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_SORT, bReset);
1164 fprintf(pArg->out, "Sort Operations: %d\n", iCur);
1165 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_AUTOINDEX, bReset);
1166 fprintf(pArg->out, "Autoindex Inserts: %d\n", iCur);
1167 if( sqlite3_libversion_number()>=3008000 ){
1168 iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_VM_STEP, bReset);
1169 fprintf(pArg->out, "Virtual Machine Steps: %d\n", iCur);
1170 }
1171 }
1172
1173 return 0;
1174 }
1175
@@ -1617,11 +1631,12 @@
1631 ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n"
1632 ".save FILE Write in-memory database into FILE\n"
1633 ".schema ?TABLE? Show the CREATE statements\n"
1634 " If TABLE specified, only show tables matching\n"
1635 " LIKE pattern TABLE.\n"
1636 ".separator STRING ?NL? Change separator used by output mode and .import\n"
1637 " NL is the end-of-line mark for CSV\n"
1638 ".shell CMD ARGS... Run CMD ARGS... in a system shell\n"
1639 ".show Show the current values for various settings\n"
1640 ".stats on|off Turn stats on or off\n"
1641 ".system CMD ARGS... Run CMD ARGS... in a system shell\n"
1642 ".tables ?TABLE? List names of tables\n"
@@ -1635,10 +1650,73 @@
1650 " Negative values right-justify\n"
1651 ;
1652
1653 /* Forward reference */
1654 static int process_input(struct callback_data *p, FILE *in);
1655 /*
1656 ** Implementation of the "readfile(X)" SQL function. The entire content
1657 ** of the file named X is read and returned as a BLOB. NULL is returned
1658 ** if the file does not exist or is unreadable.
1659 */
1660 static void readfileFunc(
1661 sqlite3_context *context,
1662 int argc,
1663 sqlite3_value **argv
1664 ){
1665 const char *zName;
1666 FILE *in;
1667 long nIn;
1668 void *pBuf;
1669
1670 zName = (const char*)sqlite3_value_text(argv[0]);
1671 if( zName==0 ) return;
1672 in = fopen(zName, "rb");
1673 if( in==0 ) return;
1674 fseek(in, 0, SEEK_END);
1675 nIn = ftell(in);
1676 rewind(in);
1677 pBuf = sqlite3_malloc( nIn );
1678 if( pBuf && 1==fread(pBuf, nIn, 1, in) ){
1679 sqlite3_result_blob(context, pBuf, nIn, sqlite3_free);
1680 }else{
1681 sqlite3_free(pBuf);
1682 }
1683 fclose(in);
1684 }
1685
1686 /*
1687 ** Implementation of the "writefile(X,Y)" SQL function. The argument Y
1688 ** is written into file X. The number of bytes written is returned. Or
1689 ** NULL is returned if something goes wrong, such as being unable to open
1690 ** file X for writing.
1691 */
1692 static void writefileFunc(
1693 sqlite3_context *context,
1694 int argc,
1695 sqlite3_value **argv
1696 ){
1697 FILE *out;
1698 const char *z;
1699 int n;
1700 sqlite3_int64 rc;
1701 const char *zFile;
1702
1703 zFile = (const char*)sqlite3_value_text(argv[0]);
1704 if( zFile==0 ) return;
1705 out = fopen(zFile, "wb");
1706 if( out==0 ) return;
1707 z = (const char*)sqlite3_value_blob(argv[1]);
1708 if( z==0 ){
1709 n = 0;
1710 rc = 0;
1711 }else{
1712 n = sqlite3_value_bytes(argv[1]);
1713 rc = fwrite(z, 1, n, out);
1714 }
1715 fclose(out);
1716 sqlite3_result_int64(context, rc);
1717 }
1718
1719 /*
1720 ** Make sure the database is open. If it is not, then open it. If
1721 ** the database fails to open, print an error message and exit.
1722 */
@@ -1658,10 +1736,14 @@
1736 exit(1);
1737 }
1738 #ifndef SQLITE_OMIT_LOAD_EXTENSION
1739 sqlite3_enable_load_extension(p->db, 1);
1740 #endif
1741 sqlite3_create_function(db, "readfile", 1, SQLITE_UTF8, 0,
1742 readfileFunc, 0, 0);
1743 sqlite3_create_function(db, "writefile", 2, SQLITE_UTF8, 0,
1744 writefileFunc, 0, 0);
1745 }
1746 }
1747
1748 /*
1749 ** Do C-language style dequoting.
@@ -2414,10 +2496,11 @@
2496 }else
2497
2498 if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){
2499 struct callback_data data;
2500 char *zErrMsg = 0;
2501 int doStats = 0;
2502 if( nArg!=1 ){
2503 fprintf(stderr, "Usage: .fullschema\n");
2504 rc = 1;
2505 goto meta_command_exit;
2506 }
@@ -2432,25 +2515,37 @@
2515 " SELECT sql, type, tbl_name, name, rowid FROM sqlite_temp_master) "
2516 "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'"
2517 "ORDER BY rowid",
2518 callback, &data, &zErrMsg
2519 );
2520 if( rc==SQLITE_OK ){
2521 sqlite3_stmt *pStmt;
2522 rc = sqlite3_prepare_v2(p->db,
2523 "SELECT rowid FROM sqlite_master"
2524 " WHERE name GLOB 'sqlite_stat[134]'",
2525 -1, &pStmt, 0);
2526 doStats = sqlite3_step(pStmt)==SQLITE_ROW;
2527 sqlite3_finalize(pStmt);
2528 }
2529 if( doStats==0 ){
2530 fprintf(p->out, "/* No STAT tables available */\n");
2531 }else{
2532 fprintf(p->out, "ANALYZE sqlite_master;\n");
2533 sqlite3_exec(p->db, "SELECT 'ANALYZE sqlite_master'",
2534 callback, &data, &zErrMsg);
2535 data.mode = MODE_Insert;
2536 data.zDestTable = "sqlite_stat1";
2537 shell_exec(p->db, "SELECT * FROM sqlite_stat1",
2538 shell_callback, &data,&zErrMsg);
2539 data.zDestTable = "sqlite_stat3";
2540 shell_exec(p->db, "SELECT * FROM sqlite_stat3",
2541 shell_callback, &data,&zErrMsg);
2542 data.zDestTable = "sqlite_stat4";
2543 shell_exec(p->db, "SELECT * FROM sqlite_stat4",
2544 shell_callback, &data, &zErrMsg);
2545 fprintf(p->out, "ANALYZE sqlite_master;\n");
2546 }
2547 }else
2548
2549 if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
2550 if( nArg==2 ){
2551 p->showHeader = booleanValue(azArg[1]);
@@ -2736,10 +2831,11 @@
2831 p->mode = MODE_Tcl;
2832 sqlite3_snprintf(sizeof(p->separator), p->separator, " ");
2833 }else if( c2=='c' && strncmp(azArg[1],"csv",n2)==0 ){
2834 p->mode = MODE_Csv;
2835 sqlite3_snprintf(sizeof(p->separator), p->separator, ",");
2836 sqlite3_snprintf(sizeof(p->newline), p->newline, "\r\n");
2837 }else if( c2=='t' && strncmp(azArg[1],"tabs",n2)==0 ){
2838 p->mode = MODE_List;
2839 sqlite3_snprintf(sizeof(p->separator), p->separator, "\t");
2840 }else if( c2=='i' && strncmp(azArg[1],"insert",n2)==0 ){
2841 p->mode = MODE_Insert;
@@ -3014,17 +3110,20 @@
3110 }
3111 }else
3112 #endif
3113
3114 if( c=='s' && strncmp(azArg[0], "separator", n)==0 ){
3115 if( nArg<2 || nArg>3 ){
3116 fprintf(stderr, "Usage: .separator SEPARATOR ?NEWLINE?\n");
 
 
 
3117 rc = 1;
3118 }
3119 if( nArg>=2 ){
3120 sqlite3_snprintf(sizeof(p->separator), p->separator, azArg[1]);
3121 }
3122 if( nArg>=3 ){
3123 sqlite3_snprintf(sizeof(p->newline), p->newline, azArg[2]);
3124 }
3125 }else
3126
3127 if( c=='s'
3128 && (strncmp(azArg[0], "shell", n)==0 || strncmp(azArg[0],"system",n)==0)
3129 ){
@@ -3061,10 +3160,12 @@
3160 fprintf(p->out, "\n");
3161 fprintf(p->out,"%9.9s: %s\n","output",
3162 strlen30(p->outfile) ? p->outfile : "stdout");
3163 fprintf(p->out,"%9.9s: ", "separator");
3164 output_c_string(p->out, p->separator);
3165 fprintf(p->out," ");
3166 output_c_string(p->out, p->newline);
3167 fprintf(p->out, "\n");
3168 fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off");
3169 fprintf(p->out,"%9.9s: ","width");
3170 for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) {
3171 fprintf(p->out,"%d ",p->colWidth[i]);
@@ -3677,10 +3778,11 @@
3778 " -list set output mode to 'list'\n"
3779 " -mmap N default mmap size set to N\n"
3780 #ifdef SQLITE_ENABLE_MULTIPLEX
3781 " -multiplex enable the multiplexor VFS\n"
3782 #endif
3783 " -newline SEP set newline character(s) for CSV\n"
3784 " -nullvalue TEXT set text string for NULL values. Default ''\n"
3785 " -separator SEP set output field separator. Default: '|'\n"
3786 " -stats print memory stats before each finalize\n"
3787 " -version show SQLite version\n"
3788 " -vfs NAME use NAME as the default VFS\n"
@@ -3706,10 +3808,11 @@
3808 */
3809 static void main_init(struct callback_data *data) {
3810 memset(data, 0, sizeof(*data));
3811 data->mode = MODE_List;
3812 memcpy(data->separator,"|", 2);
3813 memcpy(data->newline,"\r\n", 3);
3814 data->showHeader = 0;
3815 sqlite3_config(SQLITE_CONFIG_URI, 1);
3816 sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data);
3817 sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> ");
3818 sqlite3_snprintf(sizeof(continuePrompt), continuePrompt," ...> ");
@@ -3798,10 +3901,11 @@
3901 return 1;
3902 }
3903 if( z[1]=='-' ) z++;
3904 if( strcmp(z,"-separator")==0
3905 || strcmp(z,"-nullvalue")==0
3906 || strcmp(z,"-newline")==0
3907 || strcmp(z,"-cmd")==0
3908 ){
3909 (void)cmdline_option_value(argc, argv, ++i);
3910 }else if( strcmp(z,"-init")==0 ){
3911 zInitFile = cmdline_option_value(argc, argv, ++i);
@@ -3907,10 +4011,13 @@
4011 data.mode = MODE_Csv;
4012 memcpy(data.separator,",",2);
4013 }else if( strcmp(z,"-separator")==0 ){
4014 sqlite3_snprintf(sizeof(data.separator), data.separator,
4015 "%s",cmdline_option_value(argc,argv,++i));
4016 }else if( strcmp(z,"-newline")==0 ){
4017 sqlite3_snprintf(sizeof(data.newline), data.newline,
4018 "%s",cmdline_option_value(argc,argv,++i));
4019 }else if( strcmp(z,"-nullvalue")==0 ){
4020 sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue,
4021 "%s",cmdline_option_value(argc,argv,++i));
4022 }else if( strcmp(z,"-header")==0 ){
4023 data.showHeader = 1;
4024

Keyboard Shortcuts

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