Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | In the command-line shell, in CSV output mode, terminate rows with CRNL but do not expand NL characters in data into CRNL. Provide the extra -newline command-line option and the extra argument to .separator to designate an alternative newline character sequence for CSV output. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
16c8ce10e1530731441e6c4538691b71 |
User & Date: | drh 2014-07-24 12:09:47.370 |
Context
2014-07-24
| ||
12:19 | Add support for hexadecimal integer literals in the parser. (check-in: f8f79f2878 user: drh tags: trunk) | |
12:09 | In the command-line shell, in CSV output mode, terminate rows with CRNL but do not expand NL characters in data into CRNL. Provide the extra -newline command-line option and the extra argument to .separator to designate an alternative newline character sequence for CSV output. (check-in: 16c8ce10e1 user: drh tags: trunk) | |
2014-07-23
| ||
23:57 | Add experimental "costmult" logic. Only enabled when compiled with -DSQLITE_ENABLE_COSTMULT. (check-in: 729ece4088 user: drh tags: trunk) | |
Changes
Changes to src/shell.c.
︙ | ︙ | |||
60 61 62 63 64 65 66 67 68 69 70 71 72 73 | # define read_history(X) # define write_history(X) # define stifle_history(X) #endif #if defined(_WIN32) || defined(WIN32) # include <io.h> #define isatty(h) _isatty(h) #ifndef access # define access(f,m) _access((f),(m)) #endif #undef popen #define popen _popen #undef pclose | > | 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | # define read_history(X) # define write_history(X) # define stifle_history(X) #endif #if defined(_WIN32) || defined(WIN32) # include <io.h> # include <fcntl.h> #define isatty(h) _isatty(h) #ifndef access # define access(f,m) _access((f),(m)) #endif #undef popen #define popen _popen #undef pclose |
︙ | ︙ | |||
454 455 456 457 458 459 460 461 462 463 464 465 466 467 | FILE *traceOut; /* Output for sqlite3_trace() */ int nErr; /* Number of errors seen */ int mode; /* An output mode setting */ int writableSchema; /* True if PRAGMA writable_schema=ON */ int showHeader; /* True to show column names in List or Column mode */ char *zDestTable; /* Name of destination table when MODE_Insert */ char separator[20]; /* Separator character for MODE_List */ int colWidth[100]; /* Requested width of each column when in column mode*/ int actualWidth[100]; /* Actual width of each column */ char nullvalue[20]; /* The text to print when a NULL comes back from ** the database */ struct previous_mode_data explainPrev; /* Holds the mode information just before ** .explain ON */ | > | 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 | FILE *traceOut; /* Output for sqlite3_trace() */ int nErr; /* Number of errors seen */ int mode; /* An output mode setting */ int writableSchema; /* True if PRAGMA writable_schema=ON */ int showHeader; /* True to show column names in List or Column mode */ char *zDestTable; /* Name of destination table when MODE_Insert */ char separator[20]; /* Separator character for MODE_List */ char newline[20]; /* Record separator in MODE_Csv */ int colWidth[100]; /* Requested width of each column when in column mode*/ int actualWidth[100]; /* Actual width of each column */ char nullvalue[20]; /* The text to print when a NULL comes back from ** the database */ struct previous_mode_data explainPrev; /* Holds the mode information just before ** .explain ON */ |
︙ | ︙ | |||
655 656 657 658 659 660 661 | 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, }; /* ** Output a single term of CSV. Actually, p->separator is used for ** the separator, which may or may not be a comma. p->nullvalue is | | > | 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 | 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, }; /* ** Output a single term of CSV. Actually, p->separator is used for ** the separator, which may or may not be a comma. p->nullvalue is ** the null value. Strings are quoted if necessary. The separator ** is only issued if bSep is true. */ static void output_csv(struct callback_data *p, const char *z, int bSep){ FILE *out = p->out; if( z==0 ){ fprintf(out,"%s",p->nullvalue); }else{ int i; |
︙ | ︙ | |||
851 852 853 854 855 856 857 858 859 860 861 | output_c_string(p->out, azArg[i] ? azArg[i] : p->nullvalue); if(i<nArg-1) fprintf(p->out, "%s", p->separator); } fprintf(p->out,"\n"); break; } case MODE_Csv: { if( p->cnt++==0 && p->showHeader ){ for(i=0; i<nArg; i++){ output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1); } | > > > > | | | | | | > > > > > | 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 | output_c_string(p->out, azArg[i] ? azArg[i] : p->nullvalue); if(i<nArg-1) fprintf(p->out, "%s", p->separator); } fprintf(p->out,"\n"); break; } case MODE_Csv: { #if defined(WIN32) || defined(_WIN32) fflush(p->out); _setmode(_fileno(p->out), _O_BINARY); #endif if( p->cnt++==0 && p->showHeader ){ for(i=0; i<nArg; i++){ output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1); } fprintf(p->out,"%s",p->newline); } if( azArg>0 ){ for(i=0; i<nArg; i++){ output_csv(p, azArg[i], i<nArg-1); } fprintf(p->out,"%s",p->newline); } #if defined(WIN32) || defined(_WIN32) fflush(p->out); _setmode(_fileno(p->out), _O_TEXT); #endif break; } case MODE_Insert: { p->cnt++; if( azArg==0 ) break; fprintf(p->out,"INSERT INTO %s VALUES(",p->zDestTable); for(i=0; i<nArg; i++){ |
︙ | ︙ | |||
1615 1616 1617 1618 1619 1620 1621 | ".quit Exit this program\n" ".read FILENAME Execute SQL in FILENAME\n" ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n" ".save FILE Write in-memory database into FILE\n" ".schema ?TABLE? Show the CREATE statements\n" " If TABLE specified, only show tables matching\n" " LIKE pattern TABLE.\n" | | > | 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 | ".quit Exit this program\n" ".read FILENAME Execute SQL in FILENAME\n" ".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n" ".save FILE Write in-memory database into FILE\n" ".schema ?TABLE? Show the CREATE statements\n" " If TABLE specified, only show tables matching\n" " LIKE pattern TABLE.\n" ".separator STRING ?NL? Change separator used by output mode and .import\n" " NL is the end-of-line mark for CSV\n" ".shell CMD ARGS... Run CMD ARGS... in a system shell\n" ".show Show the current values for various settings\n" ".stats on|off Turn stats on or off\n" ".system CMD ARGS... Run CMD ARGS... in a system shell\n" ".tables ?TABLE? List names of tables\n" " If TABLE specified, only list tables matching\n" " LIKE pattern TABLE.\n" |
︙ | ︙ | |||
2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 2760 | p->mode = MODE_Html; }else if( c2=='t' && strncmp(azArg[1],"tcl",n2)==0 ){ p->mode = MODE_Tcl; sqlite3_snprintf(sizeof(p->separator), p->separator, " "); }else if( c2=='c' && strncmp(azArg[1],"csv",n2)==0 ){ p->mode = MODE_Csv; sqlite3_snprintf(sizeof(p->separator), p->separator, ","); }else if( c2=='t' && strncmp(azArg[1],"tabs",n2)==0 ){ p->mode = MODE_List; sqlite3_snprintf(sizeof(p->separator), p->separator, "\t"); }else if( c2=='i' && strncmp(azArg[1],"insert",n2)==0 ){ p->mode = MODE_Insert; set_table_name(p, nArg>=3 ? azArg[2] : "table"); }else { | > | 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 2771 2772 2773 2774 | p->mode = MODE_Html; }else if( c2=='t' && strncmp(azArg[1],"tcl",n2)==0 ){ p->mode = MODE_Tcl; sqlite3_snprintf(sizeof(p->separator), p->separator, " "); }else if( c2=='c' && strncmp(azArg[1],"csv",n2)==0 ){ p->mode = MODE_Csv; sqlite3_snprintf(sizeof(p->separator), p->separator, ","); sqlite3_snprintf(sizeof(p->newline), p->newline, "\r\n"); }else if( c2=='t' && strncmp(azArg[1],"tabs",n2)==0 ){ p->mode = MODE_List; sqlite3_snprintf(sizeof(p->separator), p->separator, "\t"); }else if( c2=='i' && strncmp(azArg[1],"insert",n2)==0 ){ p->mode = MODE_Insert; set_table_name(p, nArg>=3 ? azArg[2] : "table"); }else { |
︙ | ︙ | |||
3025 3026 3027 3028 3029 3030 3031 | fprintf(p->out, "%s", zBuf); } } }else #endif if( c=='s' && strncmp(azArg[0], "separator", n)==0 ){ | | < < < | > > > > > > | 3039 3040 3041 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 3053 3054 3055 3056 3057 3058 3059 3060 3061 3062 | fprintf(p->out, "%s", zBuf); } } }else #endif if( c=='s' && strncmp(azArg[0], "separator", n)==0 ){ if( nArg<2 || nArg>3 ){ fprintf(stderr, "Usage: .separator SEPARATOR ?NEWLINE?\n"); rc = 1; } if( nArg>=2 ){ sqlite3_snprintf(sizeof(p->separator), p->separator, azArg[1]); } if( nArg>=3 ){ sqlite3_snprintf(sizeof(p->newline), p->newline, azArg[2]); } }else if( c=='s' && (strncmp(azArg[0], "shell", n)==0 || strncmp(azArg[0],"system",n)==0) ){ char *zCmd; int i; |
︙ | ︙ | |||
3072 3073 3074 3075 3076 3077 3078 3079 3080 3081 3082 3083 3084 3085 | fprintf(p->out,"%9.9s: ", "nullvalue"); output_c_string(p->out, p->nullvalue); fprintf(p->out, "\n"); fprintf(p->out,"%9.9s: %s\n","output", strlen30(p->outfile) ? p->outfile : "stdout"); fprintf(p->out,"%9.9s: ", "separator"); output_c_string(p->out, p->separator); fprintf(p->out, "\n"); fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off"); fprintf(p->out,"%9.9s: ","width"); for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) { fprintf(p->out,"%d ",p->colWidth[i]); } fprintf(p->out,"\n"); | > > | 3089 3090 3091 3092 3093 3094 3095 3096 3097 3098 3099 3100 3101 3102 3103 3104 | fprintf(p->out,"%9.9s: ", "nullvalue"); output_c_string(p->out, p->nullvalue); fprintf(p->out, "\n"); fprintf(p->out,"%9.9s: %s\n","output", strlen30(p->outfile) ? p->outfile : "stdout"); fprintf(p->out,"%9.9s: ", "separator"); output_c_string(p->out, p->separator); fprintf(p->out," "); output_c_string(p->out, p->newline); fprintf(p->out, "\n"); fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off"); fprintf(p->out,"%9.9s: ","width"); for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) { fprintf(p->out,"%d ",p->colWidth[i]); } fprintf(p->out,"\n"); |
︙ | ︙ | |||
3688 3689 3690 3691 3692 3693 3694 3695 3696 3697 3698 3699 3700 3701 | " -interactive force interactive I/O\n" " -line set output mode to 'line'\n" " -list set output mode to 'list'\n" " -mmap N default mmap size set to N\n" #ifdef SQLITE_ENABLE_MULTIPLEX " -multiplex enable the multiplexor VFS\n" #endif " -nullvalue TEXT set text string for NULL values. Default ''\n" " -separator SEP set output field separator. Default: '|'\n" " -stats print memory stats before each finalize\n" " -version show SQLite version\n" " -vfs NAME use NAME as the default VFS\n" #ifdef SQLITE_ENABLE_VFSTRACE " -vfstrace enable tracing of all VFS calls\n" | > | 3707 3708 3709 3710 3711 3712 3713 3714 3715 3716 3717 3718 3719 3720 3721 | " -interactive force interactive I/O\n" " -line set output mode to 'line'\n" " -list set output mode to 'list'\n" " -mmap N default mmap size set to N\n" #ifdef SQLITE_ENABLE_MULTIPLEX " -multiplex enable the multiplexor VFS\n" #endif " -newline SEP set newline character(s) for CSV\n" " -nullvalue TEXT set text string for NULL values. Default ''\n" " -separator SEP set output field separator. Default: '|'\n" " -stats print memory stats before each finalize\n" " -version show SQLite version\n" " -vfs NAME use NAME as the default VFS\n" #ifdef SQLITE_ENABLE_VFSTRACE " -vfstrace enable tracing of all VFS calls\n" |
︙ | ︙ | |||
3717 3718 3719 3720 3721 3722 3723 3724 3725 3726 3727 3728 3729 3730 | /* ** Initialize the state information in data */ static void main_init(struct callback_data *data) { memset(data, 0, sizeof(*data)); data->mode = MODE_List; memcpy(data->separator,"|", 2); data->showHeader = 0; sqlite3_config(SQLITE_CONFIG_URI, 1); sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data); sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> "); sqlite3_snprintf(sizeof(continuePrompt), continuePrompt," ...> "); sqlite3_config(SQLITE_CONFIG_SINGLETHREAD); } | > | 3737 3738 3739 3740 3741 3742 3743 3744 3745 3746 3747 3748 3749 3750 3751 | /* ** Initialize the state information in data */ static void main_init(struct callback_data *data) { memset(data, 0, sizeof(*data)); data->mode = MODE_List; memcpy(data->separator,"|", 2); memcpy(data->newline,"\r\n", 3); data->showHeader = 0; sqlite3_config(SQLITE_CONFIG_URI, 1); sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data); sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> "); sqlite3_snprintf(sizeof(continuePrompt), continuePrompt," ...> "); sqlite3_config(SQLITE_CONFIG_SINGLETHREAD); } |
︙ | ︙ | |||
3809 3810 3811 3812 3813 3814 3815 3816 3817 3818 3819 3820 3821 3822 | fprintf(stderr,"%s: Error: too many options: \"%s\"\n", Argv0, argv[i]); fprintf(stderr,"Use -help for a list of options.\n"); return 1; } if( z[1]=='-' ) z++; if( strcmp(z,"-separator")==0 || strcmp(z,"-nullvalue")==0 || strcmp(z,"-cmd")==0 ){ (void)cmdline_option_value(argc, argv, ++i); }else if( strcmp(z,"-init")==0 ){ zInitFile = cmdline_option_value(argc, argv, ++i); }else if( strcmp(z,"-batch")==0 ){ /* Need to check for batch mode here to so we can avoid printing | > | 3830 3831 3832 3833 3834 3835 3836 3837 3838 3839 3840 3841 3842 3843 3844 | fprintf(stderr,"%s: Error: too many options: \"%s\"\n", Argv0, argv[i]); fprintf(stderr,"Use -help for a list of options.\n"); return 1; } if( z[1]=='-' ) z++; if( strcmp(z,"-separator")==0 || strcmp(z,"-nullvalue")==0 || strcmp(z,"-newline")==0 || strcmp(z,"-cmd")==0 ){ (void)cmdline_option_value(argc, argv, ++i); }else if( strcmp(z,"-init")==0 ){ zInitFile = cmdline_option_value(argc, argv, ++i); }else if( strcmp(z,"-batch")==0 ){ /* Need to check for batch mode here to so we can avoid printing |
︙ | ︙ | |||
3918 3919 3920 3921 3922 3923 3924 3925 3926 3927 3928 3929 3930 3931 | data.mode = MODE_Column; }else if( strcmp(z,"-csv")==0 ){ data.mode = MODE_Csv; memcpy(data.separator,",",2); }else if( strcmp(z,"-separator")==0 ){ sqlite3_snprintf(sizeof(data.separator), data.separator, "%s",cmdline_option_value(argc,argv,++i)); }else if( strcmp(z,"-nullvalue")==0 ){ sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue, "%s",cmdline_option_value(argc,argv,++i)); }else if( strcmp(z,"-header")==0 ){ data.showHeader = 1; }else if( strcmp(z,"-noheader")==0 ){ data.showHeader = 0; | > > > | 3940 3941 3942 3943 3944 3945 3946 3947 3948 3949 3950 3951 3952 3953 3954 3955 3956 | data.mode = MODE_Column; }else if( strcmp(z,"-csv")==0 ){ data.mode = MODE_Csv; memcpy(data.separator,",",2); }else if( strcmp(z,"-separator")==0 ){ sqlite3_snprintf(sizeof(data.separator), data.separator, "%s",cmdline_option_value(argc,argv,++i)); }else if( strcmp(z,"-newline")==0 ){ sqlite3_snprintf(sizeof(data.newline), data.newline, "%s",cmdline_option_value(argc,argv,++i)); }else if( strcmp(z,"-nullvalue")==0 ){ sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue, "%s",cmdline_option_value(argc,argv,++i)); }else if( strcmp(z,"-header")==0 ){ data.showHeader = 1; }else if( strcmp(z,"-noheader")==0 ){ data.showHeader = 0; |
︙ | ︙ |
Changes to test/shell1.test.
︙ | ︙ | |||
584 585 586 587 588 589 590 | CREATE VIEW v2 AS SELECT x+1 AS y FROM t1; CREATE VIEW v1 AS SELECT y+1 FROM v2;}} db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;} # .separator STRING Change separator used by output mode and .import do_test shell1-3.22.1 { catchcmd "test.db" ".separator" | | > > > | | | 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 | CREATE VIEW v2 AS SELECT x+1 AS y FROM t1; CREATE VIEW v1 AS SELECT y+1 FROM v2;}} db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;} # .separator STRING Change separator used by output mode and .import do_test shell1-3.22.1 { catchcmd "test.db" ".separator" } {1 {Usage: .separator SEPARATOR ?NEWLINE?}} do_test shell1-3.22.2 { catchcmd "test.db" ".separator FOO" } {0 {}} do_test shell1-3.22.3 { catchcmd "test.db" ".separator ABC XYZ" } {0 {}} do_test shell1-3.22.4 { # too many arguments catchcmd "test.db" ".separator FOO BAD BAD2" } {1 {Usage: .separator SEPARATOR ?NEWLINE?}} # .show Show the current values for various settings do_test shell1-3.23.1 { set res [catchcmd "test.db" ".show"] list [regexp {echo:} $res] \ [regexp {explain:} $res] \ [regexp {headers:} $res] \ |
︙ | ︙ |
Changes to test/shell5.test.
︙ | ︙ | |||
51 52 53 54 55 56 57 | # too many arguments catchcmd "test.db" ".import FOO BAR BAD" } {1 {Usage: .import FILE TABLE}} # .separator STRING Change separator used by output mode and .import do_test shell5-1.2.1 { catchcmd "test.db" ".separator" | | | > > > | | | 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | # too many arguments catchcmd "test.db" ".import FOO BAR BAD" } {1 {Usage: .import FILE TABLE}} # .separator STRING Change separator used by output mode and .import do_test shell5-1.2.1 { catchcmd "test.db" ".separator" } {1 {Usage: .separator SEPARATOR ?NEWLINE?}} do_test shell5-1.2.2 { catchcmd "test.db" ".separator ONE" } {0 {}} do_test shell5-1.2.3 { catchcmd "test.db" ".separator ONE TWO" } {0 {}} do_test shell5-1.2.4 { # too many arguments catchcmd "test.db" ".separator ONE TWO THREE" } {1 {Usage: .separator SEPARATOR ?NEWLINE?}} # separator should default to "|" do_test shell5-1.3.1 { set res [catchcmd "test.db" ".show"] list [regexp {separator: \"\|\"} $res] } {1} |
︙ | ︙ |