/ Changes On Branch asciiMode
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes In Branch asciiMode Excluding Merge-Ins

This is equivalent to a diff from acb0d1e8 to ea99f4b2

2015-01-09
00:38
Add the "ascii" mode to the command-line shell. (check-in: e1518a94 user: drh tags: trunk)
2015-01-02
21:54
Fix a harmless compiler warning. (check-in: e0de5807 user: drh tags: trunk)
20:06
Merge updates from trunk. (Closed-Leaf check-in: ea99f4b2 user: mistachkin tags: asciiMode)
19:17
Merge the latest changes from trunk into the apple-osx branch. (check-in: df3cdf9f user: drh tags: apple-osx)
15:55
Add the SQLITE_CONFIG_PMASZ start-time option. (check-in: acb0d1e8 user: drh tags: trunk)
2015-01-01
23:02
Add logic to releasetest.tcl that detects -fsanitize=undefined errors. Fix a few false-positivies that come up when running the sanitize=undefined test. (check-in: 2835e79a user: drh tags: trunk)
2014-12-19
22:20
Merge updates from trunk. (check-in: 555fb71f user: mistachkin tags: asciiMode)

Changes to src/shell.c.

   467    467     FILE *traceOut;        /* Output for sqlite3_trace() */
   468    468     int nErr;              /* Number of errors seen */
   469    469     int mode;              /* An output mode setting */
   470    470     int writableSchema;    /* True if PRAGMA writable_schema=ON */
   471    471     int showHeader;        /* True to show column names in List or Column mode */
   472    472     unsigned shellFlgs;    /* Various flags */
   473    473     char *zDestTable;      /* Name of destination table when MODE_Insert */
   474         -  char separator[20];    /* Separator character for MODE_List */
   475         -  char newline[20];      /* Record separator in MODE_Csv */
          474  +  char colSeparator[20]; /* Column separator character for several modes */
          475  +  char rowSeparator[20]; /* Row separator character for MODE_Ascii */
   476    476     int colWidth[100];     /* Requested width of each column when in column mode*/
   477    477     int actualWidth[100];  /* Actual width of each column */
   478         -  char nullvalue[20];    /* The text to print when a NULL comes back from
          478  +  char nullValue[20];    /* The text to print when a NULL comes back from
   479    479                            ** the database */
   480    480     SavedModeInfo normalMode;/* Holds the mode just before .explain ON */
   481    481     char outfile[FILENAME_MAX]; /* Filename for *out */
   482    482     const char *zDbFilename;    /* name of the database file */
   483    483     char *zFreeOnClose;         /* Filename to free when closing */
   484    484     const char *zVfs;           /* Name of VFS to use */
   485    485     sqlite3_stmt *pStmt;   /* Current statement if any. */
................................................................................
   504    504   #define MODE_List     2  /* One record per line with a separator */
   505    505   #define MODE_Semi     3  /* Same as MODE_List but append ";" to each line */
   506    506   #define MODE_Html     4  /* Generate an XHTML table */
   507    507   #define MODE_Insert   5  /* Generate SQL "insert" statements */
   508    508   #define MODE_Tcl      6  /* Generate ANSI-C or TCL quoted elements */
   509    509   #define MODE_Csv      7  /* Quote strings, numbers are plain */
   510    510   #define MODE_Explain  8  /* Like MODE_Column, but do not truncate data */
          511  +#define MODE_Ascii    9  /* Use ASCII unit and record separators (0x1F/0x1E) */
   511    512   
   512    513   static const char *modeDescr[] = {
   513    514     "line",
   514    515     "column",
   515    516     "list",
   516    517     "semi",
   517    518     "html",
   518    519     "insert",
   519    520     "tcl",
   520    521     "csv",
   521    522     "explain",
          523  +  "ascii",
   522    524   };
   523    525   
          526  +/*
          527  +** These are the column/row/line separators used by the various
          528  +** import/export modes.
          529  +*/
          530  +#define SEP_Column    "|"
          531  +#define SEP_Row       "\n"
          532  +#define SEP_Tab       "\t"
          533  +#define SEP_Space     " "
          534  +#define SEP_Comma     ","
          535  +#define SEP_CrLf      "\r\n"
          536  +#define SEP_Unit      "\x1F"
          537  +#define SEP_Record    "\x1E"
          538  +
   524    539   /*
   525    540   ** Number of elements in an array
   526    541   */
   527    542   #define ArraySize(X)  (int)(sizeof(X)/sizeof(X[0]))
   528    543   
   529    544   /*
   530    545   ** Compute a string length that is limited to what can be stored in
................................................................................
   673    688     1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   
   674    689     1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   
   675    690     1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   
   676    691     1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   
   677    692   };
   678    693   
   679    694   /*
   680         -** Output a single term of CSV.  Actually, p->separator is used for
   681         -** the separator, which may or may not be a comma.  p->nullvalue is
          695  +** Output a single term of CSV.  Actually, p->colSeparator is used for
          696  +** the separator, which may or may not be a comma.  p->nullValue is
   682    697   ** the null value.  Strings are quoted if necessary.  The separator
   683    698   ** is only issued if bSep is true.
   684    699   */
   685    700   static void output_csv(ShellState *p, const char *z, int bSep){
   686    701     FILE *out = p->out;
   687    702     if( z==0 ){
   688         -    fprintf(out,"%s",p->nullvalue);
          703  +    fprintf(out,"%s",p->nullValue);
   689    704     }else{
   690    705       int i;
   691         -    int nSep = strlen30(p->separator);
          706  +    int nSep = strlen30(p->colSeparator);
   692    707       for(i=0; z[i]; i++){
   693    708         if( needCsvQuote[((unsigned char*)z)[i]] 
   694         -         || (z[i]==p->separator[0] && 
   695         -             (nSep==1 || memcmp(z, p->separator, nSep)==0)) ){
          709  +         || (z[i]==p->colSeparator[0] && 
          710  +             (nSep==1 || memcmp(z, p->colSeparator, nSep)==0)) ){
   696    711           i = 0;
   697    712           break;
   698    713         }
   699    714       }
   700    715       if( i==0 ){
   701    716         putc('"', out);
   702    717         for(i=0; z[i]; i++){
................................................................................
   705    720         }
   706    721         putc('"', out);
   707    722       }else{
   708    723         fprintf(out, "%s", z);
   709    724       }
   710    725     }
   711    726     if( bSep ){
   712         -    fprintf(p->out, "%s", p->separator);
          727  +    fprintf(p->out, "%s", p->colSeparator);
   713    728     }
   714    729   }
   715    730   
   716    731   #ifdef SIGINT
   717    732   /*
   718    733   ** This routine runs when the user presses Ctrl-C
   719    734   */
................................................................................
   743    758       case MODE_Line: {
   744    759         int w = 5;
   745    760         if( azArg==0 ) break;
   746    761         for(i=0; i<nArg; i++){
   747    762           int len = strlen30(azCol[i] ? azCol[i] : "");
   748    763           if( len>w ) w = len;
   749    764         }
   750         -      if( p->cnt++>0 ) fprintf(p->out,"\n");
          765  +      if( p->cnt++>0 ) fprintf(p->out, "%s", p->rowSeparator);
   751    766         for(i=0; i<nArg; i++){
   752         -        fprintf(p->out,"%*s = %s\n", w, azCol[i],
   753         -                azArg[i] ? azArg[i] : p->nullvalue);
          767  +        fprintf(p->out,"%*s = %s%s", w, azCol[i],
          768  +                azArg[i] ? azArg[i] : p->nullValue, p->rowSeparator);
   754    769         }
   755    770         break;
   756    771       }
   757    772       case MODE_Explain:
   758    773       case MODE_Column: {
   759    774         if( p->cnt++==0 ){
   760    775           for(i=0; i<nArg; i++){
................................................................................
   763    778               w = p->colWidth[i];
   764    779             }else{
   765    780               w = 0;
   766    781             }
   767    782             if( w==0 ){
   768    783               w = strlen30(azCol[i] ? azCol[i] : "");
   769    784               if( w<10 ) w = 10;
   770         -            n = strlen30(azArg && azArg[i] ? azArg[i] : p->nullvalue);
          785  +            n = strlen30(azArg && azArg[i] ? azArg[i] : p->nullValue);
   771    786               if( w<n ) w = n;
   772    787             }
   773    788             if( i<ArraySize(p->actualWidth) ){
   774    789               p->actualWidth[i] = w;
   775    790             }
   776    791             if( p->showHeader ){
   777    792               if( w<0 ){
   778         -              fprintf(p->out,"%*.*s%s",-w,-w,azCol[i], i==nArg-1 ? "\n": "  ");
          793  +              fprintf(p->out,"%*.*s%s",-w,-w,azCol[i],
          794  +                      i==nArg-1 ? p->rowSeparator : "  ");
   779    795               }else{
   780         -              fprintf(p->out,"%-*.*s%s",w,w,azCol[i], i==nArg-1 ? "\n": "  ");
          796  +              fprintf(p->out,"%-*.*s%s",w,w,azCol[i],
          797  +                      i==nArg-1 ? p->rowSeparator : "  ");
   781    798               }
   782    799             }
   783    800           }
   784    801           if( p->showHeader ){
   785    802             for(i=0; i<nArg; i++){
   786    803               int w;
   787    804               if( i<ArraySize(p->actualWidth) ){
................................................................................
   788    805                  w = p->actualWidth[i];
   789    806                  if( w<0 ) w = -w;
   790    807               }else{
   791    808                  w = 10;
   792    809               }
   793    810               fprintf(p->out,"%-*.*s%s",w,w,"-----------------------------------"
   794    811                      "----------------------------------------------------------",
   795         -                    i==nArg-1 ? "\n": "  ");
          812  +                    i==nArg-1 ? p->rowSeparator : "  ");
   796    813             }
   797    814           }
   798    815         }
   799    816         if( azArg==0 ) break;
   800    817         for(i=0; i<nArg; i++){
   801    818           int w;
   802    819           if( i<ArraySize(p->actualWidth) ){
................................................................................
   811    828             if( p->iIndent<p->nIndent ){
   812    829               fprintf(p->out, "%*.s", p->aiIndent[p->iIndent], "");
   813    830             }
   814    831             p->iIndent++;
   815    832           }
   816    833           if( w<0 ){
   817    834             fprintf(p->out,"%*.*s%s",-w,-w,
   818         -              azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": "  ");
          835  +              azArg[i] ? azArg[i] : p->nullValue,
          836  +              i==nArg-1 ? p->rowSeparator : "  ");
   819    837           }else{
   820    838             fprintf(p->out,"%-*.*s%s",w,w,
   821         -              azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": "  ");
          839  +              azArg[i] ? azArg[i] : p->nullValue,
          840  +              i==nArg-1 ? p->rowSeparator : "  ");
   822    841           }
   823    842         }
   824    843         break;
   825    844       }
   826    845       case MODE_Semi:
   827    846       case MODE_List: {
   828    847         if( p->cnt++==0 && p->showHeader ){
   829    848           for(i=0; i<nArg; i++){
   830         -          fprintf(p->out,"%s%s",azCol[i], i==nArg-1 ? "\n" : p->separator);
          849  +          fprintf(p->out,"%s%s",azCol[i],
          850  +                  i==nArg-1 ? p->rowSeparator : p->colSeparator);
   831    851           }
   832    852         }
   833    853         if( azArg==0 ) break;
   834    854         for(i=0; i<nArg; i++){
   835    855           char *z = azArg[i];
   836         -        if( z==0 ) z = p->nullvalue;
          856  +        if( z==0 ) z = p->nullValue;
   837    857           fprintf(p->out, "%s", z);
   838    858           if( i<nArg-1 ){
   839         -          fprintf(p->out, "%s", p->separator);
          859  +          fprintf(p->out, "%s", p->colSeparator);
   840    860           }else if( p->mode==MODE_Semi ){
   841         -          fprintf(p->out, ";\n");
          861  +          fprintf(p->out, ";%s", p->rowSeparator);
   842    862           }else{
   843         -          fprintf(p->out, "\n");
          863  +          fprintf(p->out, "%s", p->rowSeparator);
   844    864           }
   845    865         }
   846    866         break;
   847    867       }
   848    868       case MODE_Html: {
   849    869         if( p->cnt++==0 && p->showHeader ){
   850    870           fprintf(p->out,"<TR>");
................................................................................
   855    875           }
   856    876           fprintf(p->out,"</TR>\n");
   857    877         }
   858    878         if( azArg==0 ) break;
   859    879         fprintf(p->out,"<TR>");
   860    880         for(i=0; i<nArg; i++){
   861    881           fprintf(p->out,"<TD>");
   862         -        output_html_string(p->out, azArg[i] ? azArg[i] : p->nullvalue);
          882  +        output_html_string(p->out, azArg[i] ? azArg[i] : p->nullValue);
   863    883           fprintf(p->out,"</TD>\n");
   864    884         }
   865    885         fprintf(p->out,"</TR>\n");
   866    886         break;
   867    887       }
   868    888       case MODE_Tcl: {
   869    889         if( p->cnt++==0 && p->showHeader ){
   870    890           for(i=0; i<nArg; i++){
   871    891             output_c_string(p->out,azCol[i] ? azCol[i] : "");
   872         -          if(i<nArg-1) fprintf(p->out, "%s", p->separator);
          892  +          if(i<nArg-1) fprintf(p->out, "%s", p->colSeparator);
   873    893           }
   874         -        fprintf(p->out,"\n");
          894  +        fprintf(p->out, "%s", p->rowSeparator);
   875    895         }
   876    896         if( azArg==0 ) break;
   877    897         for(i=0; i<nArg; i++){
   878         -        output_c_string(p->out, azArg[i] ? azArg[i] : p->nullvalue);
   879         -        if(i<nArg-1) fprintf(p->out, "%s", p->separator);
          898  +        output_c_string(p->out, azArg[i] ? azArg[i] : p->nullValue);
          899  +        if(i<nArg-1) fprintf(p->out, "%s", p->colSeparator);
   880    900         }
   881         -      fprintf(p->out,"\n");
          901  +      fprintf(p->out, "%s", p->rowSeparator);
   882    902         break;
   883    903       }
   884    904       case MODE_Csv: {
   885    905   #if defined(WIN32) || defined(_WIN32)
   886    906         fflush(p->out);
   887    907         _setmode(_fileno(p->out), _O_BINARY);
   888    908   #endif
   889    909         if( p->cnt++==0 && p->showHeader ){
   890    910           for(i=0; i<nArg; i++){
   891    911             output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1);
   892    912           }
   893         -        fprintf(p->out,"%s",p->newline);
          913  +        fprintf(p->out, "%s", p->rowSeparator);
   894    914         }
   895    915         if( nArg>0 ){
   896    916           for(i=0; i<nArg; i++){
   897    917             output_csv(p, azArg[i], i<nArg-1);
   898    918           }
   899         -        fprintf(p->out,"%s",p->newline);
          919  +        fprintf(p->out, "%s", p->rowSeparator);
   900    920         }
   901    921   #if defined(WIN32) || defined(_WIN32)
   902    922         fflush(p->out);
   903    923         _setmode(_fileno(p->out), _O_TEXT);
   904    924   #endif
   905    925         break;
   906    926       }
................................................................................
   928    948           }else{
   929    949             if( zSep[0] ) fprintf(p->out,"%s",zSep);
   930    950             output_quoted_string(p->out, azArg[i]);
   931    951           }
   932    952         }
   933    953         fprintf(p->out,");\n");
   934    954         break;
          955  +    }
          956  +    case MODE_Ascii: {
          957  +      if( p->cnt++==0 && p->showHeader ){
          958  +        for(i=0; i<nArg; i++){
          959  +          if( i>0 ) fprintf(p->out, "%s", p->colSeparator);
          960  +          fprintf(p->out,"%s",azCol[i] ? azCol[i] : "");
          961  +        }
          962  +        fprintf(p->out, "%s", p->rowSeparator);
          963  +      }
          964  +      if( azArg==0 ) break;
          965  +      for(i=0; i<nArg; i++){
          966  +        if( i>0 ) fprintf(p->out, "%s", p->colSeparator);
          967  +        fprintf(p->out,"%s",azArg[i] ? azArg[i] : p->nullValue);
          968  +      }
          969  +      fprintf(p->out, "%s", p->rowSeparator);
          970  +      break;
   935    971       }
   936    972     }
   937    973     return 0;
   938    974   }
   939    975   
   940    976   /*
   941    977   ** This is the callback routine that the SQLite library
................................................................................
  1694   1730     ".iotrace FILE          Enable I/O diagnostic logging to FILE\n"
  1695   1731   #endif
  1696   1732   #ifndef SQLITE_OMIT_LOAD_EXTENSION
  1697   1733     ".load FILE ?ENTRY?     Load an extension library\n"
  1698   1734   #endif
  1699   1735     ".log FILE|off          Turn logging on or off.  FILE can be stderr/stdout\n"
  1700   1736     ".mode MODE ?TABLE?     Set output mode where MODE is one of:\n"
         1737  +  "                         ascii    Columns/rows delimited by 0x1F and 0x1E\n"
  1701   1738     "                         csv      Comma-separated values\n"
  1702   1739     "                         column   Left-aligned columns.  (See .width)\n"
  1703   1740     "                         html     HTML <table> code\n"
  1704   1741     "                         insert   SQL insert statements for TABLE\n"
  1705   1742     "                         line     One value per line\n"
  1706         -  "                         list     Values delimited by .separator string\n"
         1743  +  "                         list     Values delimited by .separator strings\n"
  1707   1744     "                         tabs     Tab-separated values\n"
  1708   1745     "                         tcl      TCL list elements\n"
  1709   1746     ".nullvalue STRING      Use STRING in place of NULL values\n"
  1710   1747     ".once FILENAME         Output for the next SQL command only to FILENAME\n"
  1711   1748     ".open ?FILENAME?       Close existing database and reopen FILENAME\n"
  1712   1749     ".output ?FILENAME?     Send output to FILENAME or stdout\n"
  1713   1750     ".print STRING...       Print literal STRING\n"
................................................................................
  1716   1753     ".read FILENAME         Execute SQL in FILENAME\n"
  1717   1754     ".restore ?DB? FILE     Restore content of DB (default \"main\") from FILE\n"
  1718   1755     ".save FILE             Write in-memory database into FILE\n"
  1719   1756     ".scanstats on|off      Turn sqlite3_stmt_scanstatus() metrics on or off\n"
  1720   1757     ".schema ?TABLE?        Show the CREATE statements\n"
  1721   1758     "                         If TABLE specified, only show tables matching\n"
  1722   1759     "                         LIKE pattern TABLE.\n"
  1723         -  ".separator STRING ?NL? Change separator used by output mode and .import\n"
  1724         -  "                         NL is the end-of-line mark for CSV\n"
         1760  +  ".separator COL ?ROW?   Change the column separator and optionally the row\n"
         1761  +  "                         separator for both the output mode and .import\n"
  1725   1762     ".shell CMD ARGS...     Run CMD ARGS... in a system shell\n"
  1726   1763     ".show                  Show the current values for various settings\n"
  1727   1764     ".stats on|off          Turn stats on or off\n"
  1728   1765     ".system CMD ARGS...    Run CMD ARGS... in a system shell\n"
  1729   1766     ".tables ?TABLE?        List names of tables\n"
  1730   1767     "                         If TABLE specified, only list tables matching\n"
  1731   1768     "                         LIKE pattern TABLE.\n"
................................................................................
  1998   2035   */
  1999   2036   static void test_breakpoint(void){
  2000   2037     static int nCall = 0;
  2001   2038     nCall++;
  2002   2039   }
  2003   2040   
  2004   2041   /*
  2005         -** An object used to read a CSV file
         2042  +** An object used to read a CSV and other files for import.
  2006   2043   */
  2007         -typedef struct CSVReader CSVReader;
  2008         -struct CSVReader {
         2044  +typedef struct ImportCtx ImportCtx;
         2045  +struct ImportCtx {
  2009   2046     const char *zFile;  /* Name of the input file */
  2010   2047     FILE *in;           /* Read the CSV text from this input stream */
  2011   2048     char *z;            /* Accumulated text for a field */
  2012   2049     int n;              /* Number of bytes in z */
  2013   2050     int nAlloc;         /* Space allocated for z[] */
  2014   2051     int nLine;          /* Current line number */
  2015   2052     int cTerm;          /* Character that terminated the most recent field */
  2016         -  int cSeparator;     /* The separator character.  (Usually ",") */
         2053  +  int cColSep;        /* The column separator character.  (Usually ",") */
         2054  +  int cRowSep;        /* The row separator character.  (Usually "\n") */
  2017   2055   };
  2018   2056   
  2019   2057   /* Append a single byte to z[] */
  2020         -static void csv_append_char(CSVReader *p, int c){
         2058  +static void import_append_char(ImportCtx *p, int c){
  2021   2059     if( p->n+1>=p->nAlloc ){
  2022   2060       p->nAlloc += p->nAlloc + 100;
  2023   2061       p->z = sqlite3_realloc(p->z, p->nAlloc);
  2024   2062       if( p->z==0 ){
  2025   2063         fprintf(stderr, "out of memory\n");
  2026   2064         exit(1);
  2027   2065       }
................................................................................
  2031   2069   
  2032   2070   /* Read a single field of CSV text.  Compatible with rfc4180 and extended
  2033   2071   ** with the option of having a separator other than ",".
  2034   2072   **
  2035   2073   **   +  Input comes from p->in.
  2036   2074   **   +  Store results in p->z of length p->n.  Space to hold p->z comes
  2037   2075   **      from sqlite3_malloc().
  2038         -**   +  Use p->cSep as the separator.  The default is ",".
         2076  +**   +  Use p->cSep as the column separator.  The default is ",".
         2077  +**   +  Use p->rSep as the row separator.  The default is "\n".
  2039   2078   **   +  Keep track of the line number in p->nLine.
  2040   2079   **   +  Store the character that terminates the field in p->cTerm.  Store
  2041   2080   **      EOF on end-of-file.
  2042   2081   **   +  Report syntax errors on stderr
  2043   2082   */
  2044         -static char *csv_read_one_field(CSVReader *p){
  2045         -  int c, pc, ppc;
  2046         -  int cSep = p->cSeparator;
         2083  +static char *csv_read_one_field(ImportCtx *p){
         2084  +  int c;
         2085  +  int cSep = p->cColSep;
         2086  +  int rSep = p->cRowSep;
  2047   2087     p->n = 0;
  2048   2088     c = fgetc(p->in);
  2049   2089     if( c==EOF || seenInterrupt ){
  2050   2090       p->cTerm = EOF;
  2051   2091       return 0;
  2052   2092     }
  2053   2093     if( c=='"' ){
         2094  +    int pc, ppc;
  2054   2095       int startLine = p->nLine;
  2055   2096       int cQuote = c;
  2056   2097       pc = ppc = 0;
  2057   2098       while( 1 ){
  2058   2099         c = fgetc(p->in);
  2059         -      if( c=='\n' ) p->nLine++;
         2100  +      if( c==rSep ) p->nLine++;
  2060   2101         if( c==cQuote ){
  2061   2102           if( pc==cQuote ){
  2062   2103             pc = 0;
  2063   2104             continue;
  2064   2105           }
  2065   2106         }
  2066   2107         if( (c==cSep && pc==cQuote)
  2067         -       || (c=='\n' && pc==cQuote)
  2068         -       || (c=='\n' && pc=='\r' && ppc==cQuote)
         2108  +       || (c==rSep && pc==cQuote)
         2109  +       || (c==rSep && pc=='\r' && ppc==cQuote)
  2069   2110          || (c==EOF && pc==cQuote)
  2070   2111         ){
  2071   2112           do{ p->n--; }while( p->z[p->n]!=cQuote );
  2072   2113           p->cTerm = c;
  2073   2114           break;
  2074   2115         }
  2075   2116         if( pc==cQuote && c!='\r' ){
  2076   2117           fprintf(stderr, "%s:%d: unescaped %c character\n",
  2077   2118                   p->zFile, p->nLine, cQuote);
  2078   2119         }
  2079   2120         if( c==EOF ){
  2080   2121           fprintf(stderr, "%s:%d: unterminated %c-quoted field\n",
  2081   2122                   p->zFile, startLine, cQuote);
  2082         -        p->cTerm = EOF;
         2123  +        p->cTerm = c;
  2083   2124           break;
  2084   2125         }
  2085         -      csv_append_char(p, c);
         2126  +      import_append_char(p, c);
  2086   2127         ppc = pc;
  2087   2128         pc = c;
  2088   2129       }
  2089   2130     }else{
  2090         -    while( c!=EOF && c!=cSep && c!='\n' ){
  2091         -      csv_append_char(p, c);
         2131  +    while( c!=EOF && c!=cSep && c!=rSep ){
         2132  +      import_append_char(p, c);
  2092   2133         c = fgetc(p->in);
  2093   2134       }
  2094         -    if( c=='\n' ){
         2135  +    if( c==rSep ){
  2095   2136         p->nLine++;
  2096   2137         if( p->n>0 && p->z[p->n-1]=='\r' ) p->n--;
  2097   2138       }
  2098   2139       p->cTerm = c;
  2099   2140     }
  2100   2141     if( p->z ) p->z[p->n] = 0;
  2101   2142     return p->z;
  2102   2143   }
         2144  +
         2145  +/* Read a single field of ASCII delimited text.
         2146  +**
         2147  +**   +  Input comes from p->in.
         2148  +**   +  Store results in p->z of length p->n.  Space to hold p->z comes
         2149  +**      from sqlite3_malloc().
         2150  +**   +  Use p->cSep as the column separator.  The default is "\x1F".
         2151  +**   +  Use p->rSep as the row separator.  The default is "\x1E".
         2152  +**   +  Keep track of the row number in p->nLine.
         2153  +**   +  Store the character that terminates the field in p->cTerm.  Store
         2154  +**      EOF on end-of-file.
         2155  +**   +  Report syntax errors on stderr
         2156  +*/
         2157  +static char *ascii_read_one_field(ImportCtx *p){
         2158  +  int c;
         2159  +  int cSep = p->cColSep;
         2160  +  int rSep = p->cRowSep;
         2161  +  p->n = 0;
         2162  +  c = fgetc(p->in);
         2163  +  if( c==EOF || seenInterrupt ){
         2164  +    p->cTerm = EOF;
         2165  +    return 0;
         2166  +  }
         2167  +  while( c!=EOF && c!=cSep && c!=rSep ){
         2168  +    import_append_char(p, c);
         2169  +    c = fgetc(p->in);
         2170  +  }
         2171  +  if( c==rSep ){
         2172  +    p->nLine++;
         2173  +  }
         2174  +  p->cTerm = c;
         2175  +  if( p->z ) p->z[p->n] = 0;
         2176  +  return p->z;
         2177  +}
  2103   2178   
  2104   2179   /*
  2105   2180   ** Try to transfer data for table zTable.  If an error is seen while
  2106   2181   ** moving forward, try to go backwards.  The backwards movement won't
  2107   2182   ** work for WITHOUT ROWID tables.
  2108   2183   */
  2109   2184   static void tryToCloneData(
................................................................................
  2651   2726       char *zTable;               /* Insert data into this table */
  2652   2727       char *zFile;                /* Name of file to extra content from */
  2653   2728       sqlite3_stmt *pStmt = NULL; /* A statement */
  2654   2729       int nCol;                   /* Number of columns in the table */
  2655   2730       int nByte;                  /* Number of bytes in an SQL string */
  2656   2731       int i, j;                   /* Loop counters */
  2657   2732       int needCommit;             /* True to COMMIT or ROLLBACK at end */
  2658         -    int nSep;                   /* Number of bytes in p->separator[] */
         2733  +    int nSep;                   /* Number of bytes in p->colSeparator[] */
  2659   2734       char *zSql;                 /* An SQL statement */
  2660         -    CSVReader sCsv;             /* Reader context */
         2735  +    ImportCtx sCtx;             /* Reader context */
         2736  +    char *(*xRead)(ImportCtx*); /* Procedure to read one value */
  2661   2737       int (*xCloser)(FILE*);      /* Procedure to close th3 connection */
  2662   2738   
  2663   2739       if( nArg!=3 ){
  2664   2740         fprintf(stderr, "Usage: .import FILE TABLE\n");
  2665   2741         goto meta_command_exit;
  2666   2742       }
  2667   2743       zFile = azArg[1];
  2668   2744       zTable = azArg[2];
  2669   2745       seenInterrupt = 0;
  2670         -    memset(&sCsv, 0, sizeof(sCsv));
         2746  +    memset(&sCtx, 0, sizeof(sCtx));
  2671   2747       open_db(p, 0);
  2672         -    nSep = strlen30(p->separator);
         2748  +    nSep = strlen30(p->colSeparator);
         2749  +    if( nSep==0 ){
         2750  +      fprintf(stderr, "Error: non-null column separator required for import\n");
         2751  +      return 1;
         2752  +    }
         2753  +    if( nSep>1 ){
         2754  +      fprintf(stderr, "Error: multi-character column separators not allowed"
         2755  +                      " for import\n");
         2756  +      return 1;
         2757  +    }
         2758  +    nSep = strlen30(p->rowSeparator);
  2673   2759       if( nSep==0 ){
  2674         -      fprintf(stderr, "Error: non-null separator required for import\n");
         2760  +      fprintf(stderr, "Error: non-null row separator required for import\n");
  2675   2761         return 1;
  2676   2762       }
         2763  +    if( nSep==2 && p->mode==MODE_Csv && strcmp(p->rowSeparator, SEP_CrLf)==0 ){
         2764  +      /* When importing CSV (only), if the row separator is set to the
         2765  +      ** default output row separator, change it to the default input
         2766  +      ** row separator.  This avoids having to maintain different input
         2767  +      ** and output row separators. */
         2768  +      sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_Row);
         2769  +      nSep = strlen30(p->rowSeparator);
         2770  +    }
  2677   2771       if( nSep>1 ){
  2678         -      fprintf(stderr, "Error: multi-character separators not allowed"
         2772  +      fprintf(stderr, "Error: multi-character row separators not allowed"
  2679   2773                         " for import\n");
  2680   2774         return 1;
  2681   2775       }
  2682         -    sCsv.zFile = zFile;
  2683         -    sCsv.nLine = 1;
  2684         -    if( sCsv.zFile[0]=='|' ){
  2685         -      sCsv.in = popen(sCsv.zFile+1, "r");
  2686         -      sCsv.zFile = "<pipe>";
         2776  +    sCtx.zFile = zFile;
         2777  +    sCtx.nLine = 1;
         2778  +    if( sCtx.zFile[0]=='|' ){
         2779  +      sCtx.in = popen(sCtx.zFile+1, "r");
         2780  +      sCtx.zFile = "<pipe>";
  2687   2781         xCloser = pclose;
  2688   2782       }else{
  2689         -      sCsv.in = fopen(sCsv.zFile, "rb");
         2783  +      sCtx.in = fopen(sCtx.zFile, "rb");
  2690   2784         xCloser = fclose;
  2691   2785       }
  2692         -    if( sCsv.in==0 ){
         2786  +    if( p->mode==MODE_Ascii ){
         2787  +      xRead = ascii_read_one_field;
         2788  +    }else{
         2789  +      xRead = csv_read_one_field;
         2790  +    }
         2791  +    if( sCtx.in==0 ){
  2693   2792         fprintf(stderr, "Error: cannot open \"%s\"\n", zFile);
  2694   2793         return 1;
  2695   2794       }
  2696         -    sCsv.cSeparator = p->separator[0];
         2795  +    sCtx.cColSep = p->colSeparator[0];
         2796  +    sCtx.cRowSep = p->rowSeparator[0];
  2697   2797       zSql = sqlite3_mprintf("SELECT * FROM %s", zTable);
  2698   2798       if( zSql==0 ){
  2699   2799         fprintf(stderr, "Error: out of memory\n");
  2700         -      xCloser(sCsv.in);
         2800  +      xCloser(sCtx.in);
  2701   2801         return 1;
  2702   2802       }
  2703   2803       nByte = strlen30(zSql);
  2704   2804       rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
  2705         -    csv_append_char(&sCsv, 0);    /* To ensure sCsv.z is allocated */
         2805  +    import_append_char(&sCtx, 0);    /* To ensure sCtx.z is allocated */
  2706   2806       if( rc && sqlite3_strglob("no such table: *", sqlite3_errmsg(db))==0 ){
  2707   2807         char *zCreate = sqlite3_mprintf("CREATE TABLE %s", zTable);
  2708   2808         char cSep = '(';
  2709         -      while( csv_read_one_field(&sCsv) ){
  2710         -        zCreate = sqlite3_mprintf("%z%c\n  \"%s\" TEXT", zCreate, cSep, sCsv.z);
         2809  +      while( xRead(&sCtx) ){
         2810  +        zCreate = sqlite3_mprintf("%z%c\n  \"%s\" TEXT", zCreate, cSep, sCtx.z);
  2711   2811           cSep = ',';
  2712         -        if( sCsv.cTerm!=sCsv.cSeparator ) break;
         2812  +        if( sCtx.cTerm!=sCtx.cColSep ) break;
  2713   2813         }
  2714   2814         if( cSep=='(' ){
  2715   2815           sqlite3_free(zCreate);
  2716         -        sqlite3_free(sCsv.z);
  2717         -        xCloser(sCsv.in);
  2718         -        fprintf(stderr,"%s: empty file\n", sCsv.zFile);
         2816  +        sqlite3_free(sCtx.z);
         2817  +        xCloser(sCtx.in);
         2818  +        fprintf(stderr,"%s: empty file\n", sCtx.zFile);
  2719   2819           return 1;
  2720   2820         }
  2721   2821         zCreate = sqlite3_mprintf("%z\n)", zCreate);
  2722   2822         rc = sqlite3_exec(p->db, zCreate, 0, 0, 0);
  2723   2823         sqlite3_free(zCreate);
  2724   2824         if( rc ){
  2725   2825           fprintf(stderr, "CREATE TABLE %s(...) failed: %s\n", zTable,
  2726   2826                   sqlite3_errmsg(db));
  2727         -        sqlite3_free(sCsv.z);
  2728         -        xCloser(sCsv.in);
         2827  +        sqlite3_free(sCtx.z);
         2828  +        xCloser(sCtx.in);
  2729   2829           return 1;
  2730   2830         }
  2731   2831         rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
  2732   2832       }
  2733   2833       sqlite3_free(zSql);
  2734   2834       if( rc ){
  2735   2835         if (pStmt) sqlite3_finalize(pStmt);
  2736   2836         fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));
  2737         -      xCloser(sCsv.in);
         2837  +      xCloser(sCtx.in);
  2738   2838         return 1;
  2739   2839       }
  2740   2840       nCol = sqlite3_column_count(pStmt);
  2741   2841       sqlite3_finalize(pStmt);
  2742   2842       pStmt = 0;
  2743   2843       if( nCol==0 ) return 0; /* no columns, no error */
  2744   2844       zSql = sqlite3_malloc( nByte*2 + 20 + nCol*2 );
  2745   2845       if( zSql==0 ){
  2746   2846         fprintf(stderr, "Error: out of memory\n");
  2747         -      xCloser(sCsv.in);
         2847  +      xCloser(sCtx.in);
  2748   2848         return 1;
  2749   2849       }
  2750   2850       sqlite3_snprintf(nByte+20, zSql, "INSERT INTO \"%w\" VALUES(?", zTable);
  2751   2851       j = strlen30(zSql);
  2752   2852       for(i=1; i<nCol; i++){
  2753   2853         zSql[j++] = ',';
  2754   2854         zSql[j++] = '?';
................................................................................
  2756   2856       zSql[j++] = ')';
  2757   2857       zSql[j] = 0;
  2758   2858       rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
  2759   2859       sqlite3_free(zSql);
  2760   2860       if( rc ){
  2761   2861         fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
  2762   2862         if (pStmt) sqlite3_finalize(pStmt);
  2763         -      xCloser(sCsv.in);
         2863  +      xCloser(sCtx.in);
  2764   2864         return 1;
  2765   2865       }
  2766   2866       needCommit = sqlite3_get_autocommit(db);
  2767   2867       if( needCommit ) sqlite3_exec(db, "BEGIN", 0, 0, 0);
  2768   2868       do{
  2769         -      int startLine = sCsv.nLine;
         2869  +      int startLine = sCtx.nLine;
  2770   2870         for(i=0; i<nCol; i++){
  2771         -        char *z = csv_read_one_field(&sCsv);
         2871  +        char *z = xRead(&sCtx);
         2872  +        /*
         2873  +        ** Did we reach end-of-file before finding any columns?
         2874  +        ** If so, stop instead of NULL filling the remaining columns.
         2875  +        */
  2772   2876           if( z==0 && i==0 ) break;
         2877  +        /*
         2878  +        ** Did we reach end-of-file OR end-of-line before finding any
         2879  +        ** columns in ASCII mode?  If so, stop instead of NULL filling
         2880  +        ** the remaining columns.
         2881  +        */
         2882  +        if( p->mode==MODE_Ascii && (z==0 || z[0]==0) && i==0 ) break;
  2773   2883           sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT);
  2774         -        if( i<nCol-1 && sCsv.cTerm!=sCsv.cSeparator ){
         2884  +        if( i<nCol-1 && sCtx.cTerm!=sCtx.cColSep ){
  2775   2885             fprintf(stderr, "%s:%d: expected %d columns but found %d - "
  2776   2886                             "filling the rest with NULL\n",
  2777         -                          sCsv.zFile, startLine, nCol, i+1);
         2887  +                          sCtx.zFile, startLine, nCol, i+1);
  2778   2888             i++;
  2779   2889             while( i<=nCol ){ sqlite3_bind_null(pStmt, i); i++; }
  2780   2890           }
  2781   2891         }
  2782         -      if( sCsv.cTerm==sCsv.cSeparator ){
         2892  +      if( sCtx.cTerm==sCtx.cColSep ){
  2783   2893           do{
  2784         -          csv_read_one_field(&sCsv);
         2894  +          xRead(&sCtx);
  2785   2895             i++;
  2786         -        }while( sCsv.cTerm==sCsv.cSeparator );
         2896  +        }while( sCtx.cTerm==sCtx.cColSep );
  2787   2897           fprintf(stderr, "%s:%d: expected %d columns but found %d - "
  2788   2898                           "extras ignored\n",
  2789         -                        sCsv.zFile, startLine, nCol, i);
         2899  +                        sCtx.zFile, startLine, nCol, i);
  2790   2900         }
  2791   2901         if( i>=nCol ){
  2792   2902           sqlite3_step(pStmt);
  2793   2903           rc = sqlite3_reset(pStmt);
  2794   2904           if( rc!=SQLITE_OK ){
  2795         -          fprintf(stderr, "%s:%d: INSERT failed: %s\n", sCsv.zFile, startLine,
         2905  +          fprintf(stderr, "%s:%d: INSERT failed: %s\n", sCtx.zFile, startLine,
  2796   2906                     sqlite3_errmsg(db));
  2797   2907           }
  2798   2908         }
  2799         -    }while( sCsv.cTerm!=EOF );
         2909  +    }while( sCtx.cTerm!=EOF );
  2800   2910   
  2801         -    xCloser(sCsv.in);
  2802         -    sqlite3_free(sCsv.z);
         2911  +    xCloser(sCtx.in);
         2912  +    sqlite3_free(sCtx.z);
  2803   2913       sqlite3_finalize(pStmt);
  2804   2914       if( needCommit ) sqlite3_exec(db, "COMMIT", 0, 0, 0);
  2805   2915     }else
  2806   2916   
  2807   2917     if( c=='i' && strncmp(azArg[0], "indices", n)==0 ){
  2808   2918       ShellState data;
  2809   2919       char *zErrMsg = 0;
................................................................................
  2913   3023         p->mode = MODE_Column;
  2914   3024       }else if( c2=='l' && n2>2 && strncmp(azArg[1],"list",n2)==0 ){
  2915   3025         p->mode = MODE_List;
  2916   3026       }else if( c2=='h' && strncmp(azArg[1],"html",n2)==0 ){
  2917   3027         p->mode = MODE_Html;
  2918   3028       }else if( c2=='t' && strncmp(azArg[1],"tcl",n2)==0 ){
  2919   3029         p->mode = MODE_Tcl;
  2920         -      sqlite3_snprintf(sizeof(p->separator), p->separator, " ");
         3030  +      sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Space);
  2921   3031       }else if( c2=='c' && strncmp(azArg[1],"csv",n2)==0 ){
  2922   3032         p->mode = MODE_Csv;
  2923         -      sqlite3_snprintf(sizeof(p->separator), p->separator, ",");
  2924         -      sqlite3_snprintf(sizeof(p->newline), p->newline, "\r\n");
         3033  +      sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Comma);
         3034  +      sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_CrLf);
  2925   3035       }else if( c2=='t' && strncmp(azArg[1],"tabs",n2)==0 ){
  2926   3036         p->mode = MODE_List;
  2927         -      sqlite3_snprintf(sizeof(p->separator), p->separator, "\t");
         3037  +      sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Tab);
  2928   3038       }else if( c2=='i' && strncmp(azArg[1],"insert",n2)==0 ){
  2929   3039         p->mode = MODE_Insert;
  2930   3040         set_table_name(p, nArg>=3 ? azArg[2] : "table");
         3041  +    }else if( c2=='a' && strncmp(azArg[1],"ascii",n2)==0 ){
         3042  +      p->mode = MODE_Ascii;
         3043  +      sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Unit);
         3044  +      sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_Record);
  2931   3045       }else {
  2932   3046         fprintf(stderr,"Error: mode should be one of: "
  2933         -         "column csv html insert line list tabs tcl\n");
         3047  +         "ascii column csv html insert line list tabs tcl\n");
  2934   3048         rc = 1;
  2935   3049       }
  2936   3050     }else
  2937   3051   
  2938   3052     if( c=='n' && strncmp(azArg[0], "nullvalue", n)==0 ){
  2939   3053       if( nArg==2 ){
  2940         -      sqlite3_snprintf(sizeof(p->nullvalue), p->nullvalue,
  2941         -                       "%.*s", (int)ArraySize(p->nullvalue)-1, azArg[1]);
         3054  +      sqlite3_snprintf(sizeof(p->nullValue), p->nullValue,
         3055  +                       "%.*s", (int)ArraySize(p->nullValue)-1, azArg[1]);
  2942   3056       }else{
  2943   3057         fprintf(stderr, "Usage: .nullvalue STRING\n");
  2944   3058         rc = 1;
  2945   3059       }
  2946   3060     }else
  2947   3061   
  2948   3062     if( c=='o' && strncmp(azArg[0], "open", n)==0 && n>=2 ){
................................................................................
  3219   3333         }
  3220   3334       }
  3221   3335     }else
  3222   3336   #endif
  3223   3337   
  3224   3338     if( c=='s' && strncmp(azArg[0], "separator", n)==0 ){
  3225   3339       if( nArg<2 || nArg>3 ){
  3226         -      fprintf(stderr, "Usage: .separator SEPARATOR ?NEWLINE?\n");
         3340  +      fprintf(stderr, "Usage: .separator COL ?ROW?\n");
  3227   3341         rc = 1;
  3228   3342       }
  3229   3343       if( nArg>=2 ){
  3230         -      sqlite3_snprintf(sizeof(p->separator), p->separator, azArg[1]);
         3344  +      sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator,
         3345  +                       "%.*s", (int)ArraySize(p->colSeparator)-1, azArg[1]);
  3231   3346       }
  3232   3347       if( nArg>=3 ){
  3233         -      sqlite3_snprintf(sizeof(p->newline), p->newline, azArg[2]);
         3348  +      sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator,
         3349  +                       "%.*s", (int)ArraySize(p->rowSeparator)-1, azArg[2]);
  3234   3350       }
  3235   3351     }else
  3236   3352   
  3237   3353     if( c=='s'
  3238   3354      && (strncmp(azArg[0], "shell", n)==0 || strncmp(azArg[0],"system",n)==0)
  3239   3355     ){
  3240   3356       char *zCmd;
................................................................................
  3257   3373     if( c=='s' && strncmp(azArg[0], "show", n)==0 ){
  3258   3374       int i;
  3259   3375       if( nArg!=1 ){
  3260   3376         fprintf(stderr, "Usage: .show\n");
  3261   3377         rc = 1;
  3262   3378         goto meta_command_exit;
  3263   3379       }
  3264         -    fprintf(p->out,"%9.9s: %s\n","echo", p->echoOn ? "on" : "off");
  3265         -    fprintf(p->out,"%9.9s: %s\n","eqp", p->autoEQP ? "on" : "off");
         3380  +    fprintf(p->out,"%12.12s: %s\n","echo", p->echoOn ? "on" : "off");
         3381  +    fprintf(p->out,"%12.12s: %s\n","eqp", p->autoEQP ? "on" : "off");
  3266   3382       fprintf(p->out,"%9.9s: %s\n","explain", p->normalMode.valid ? "on" :"off");
  3267         -    fprintf(p->out,"%9.9s: %s\n","headers", p->showHeader ? "on" : "off");
  3268         -    fprintf(p->out,"%9.9s: %s\n","mode", modeDescr[p->mode]);
  3269         -    fprintf(p->out,"%9.9s: ", "nullvalue");
  3270         -      output_c_string(p->out, p->nullvalue);
         3383  +    fprintf(p->out,"%12.12s: %s\n","headers", p->showHeader ? "on" : "off");
         3384  +    fprintf(p->out,"%12.12s: %s\n","mode", modeDescr[p->mode]);
         3385  +    fprintf(p->out,"%12.12s: ", "nullvalue");
         3386  +      output_c_string(p->out, p->nullValue);
  3271   3387         fprintf(p->out, "\n");
  3272         -    fprintf(p->out,"%9.9s: %s\n","output",
         3388  +    fprintf(p->out,"%12.12s: %s\n","output",
  3273   3389               strlen30(p->outfile) ? p->outfile : "stdout");
  3274         -    fprintf(p->out,"%9.9s: ", "separator");
  3275         -      output_c_string(p->out, p->separator);
  3276         -      fprintf(p->out," ");
  3277         -      output_c_string(p->out, p->newline);
         3390  +    fprintf(p->out,"%12.12s: ", "colseparator");
         3391  +      output_c_string(p->out, p->colSeparator);
         3392  +      fprintf(p->out, "\n");
         3393  +    fprintf(p->out,"%12.12s: ", "rowseparator");
         3394  +      output_c_string(p->out, p->rowSeparator);
  3278   3395         fprintf(p->out, "\n");
  3279         -    fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off");
  3280         -    fprintf(p->out,"%9.9s: ","width");
         3396  +    fprintf(p->out,"%12.12s: %s\n","stats", p->statsOn ? "on" : "off");
         3397  +    fprintf(p->out,"%12.12s: ","width");
  3281   3398       for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) {
  3282   3399         fprintf(p->out,"%d ",p->colWidth[i]);
  3283   3400       }
  3284   3401       fprintf(p->out,"\n");
  3285   3402     }else
  3286   3403   
  3287   3404     if( c=='s' && strncmp(azArg[0], "stats", n)==0 ){
................................................................................
  3934   4051     return rc;
  3935   4052   }
  3936   4053   
  3937   4054   /*
  3938   4055   ** Show available command line options
  3939   4056   */
  3940   4057   static const char zOptions[] = 
         4058  +  "   -ascii               set output mode to 'ascii'\n"
  3941   4059     "   -bail                stop after hitting an error\n"
  3942   4060     "   -batch               force batch I/O\n"
  3943   4061     "   -column              set output mode to 'column'\n"
  3944   4062     "   -cmd COMMAND         run \"COMMAND\" before reading stdin\n"
  3945   4063     "   -csv                 set output mode to 'csv'\n"
  3946   4064     "   -echo                print commands before execution\n"
  3947   4065     "   -init FILENAME       read/process named file\n"
................................................................................
  3955   4073     "   -line                set output mode to 'line'\n"
  3956   4074     "   -list                set output mode to 'list'\n"
  3957   4075     "   -lookaside SIZE N    use N entries of SZ bytes for lookaside memory\n"
  3958   4076     "   -mmap N              default mmap size set to N\n"
  3959   4077   #ifdef SQLITE_ENABLE_MULTIPLEX
  3960   4078     "   -multiplex           enable the multiplexor VFS\n"
  3961   4079   #endif
  3962         -  "   -newline SEP         set newline character(s) for CSV\n"
         4080  +  "   -newline SEP         set output row separator. Default: '\\n'\n"
  3963   4081     "   -nullvalue TEXT      set text string for NULL values. Default ''\n"
  3964   4082     "   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory\n"
  3965   4083     "   -scratch SIZE N      use N slots of SZ bytes each for scratch memory\n"
  3966         -  "   -separator SEP       set output field separator. Default: '|'\n"
         4084  +  "   -separator SEP       set output column separator. Default: '|'\n"
  3967   4085     "   -stats               print memory stats before each finalize\n"
  3968   4086     "   -version             show SQLite version\n"
  3969   4087     "   -vfs NAME            use NAME as the default VFS\n"
  3970   4088   #ifdef SQLITE_ENABLE_VFSTRACE
  3971   4089     "   -vfstrace            enable tracing of all VFS calls\n"
  3972   4090   #endif
  3973   4091   ;
................................................................................
  3986   4104   
  3987   4105   /*
  3988   4106   ** Initialize the state information in data
  3989   4107   */
  3990   4108   static void main_init(ShellState *data) {
  3991   4109     memset(data, 0, sizeof(*data));
  3992   4110     data->mode = MODE_List;
  3993         -  memcpy(data->separator,"|", 2);
  3994         -  memcpy(data->newline,"\r\n", 3);
         4111  +  memcpy(data->colSeparator,SEP_Column, 2);
         4112  +  memcpy(data->rowSeparator,SEP_Row, 2);
  3995   4113     data->showHeader = 0;
  3996   4114     data->shellFlgs = SHFLG_Lookaside;
  3997   4115     sqlite3_config(SQLITE_CONFIG_URI, 1);
  3998   4116     sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data);
  3999   4117     sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
  4000   4118     sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> ");
  4001   4119     sqlite3_snprintf(sizeof(continuePrompt), continuePrompt,"   ...> ");
................................................................................
  4226   4344         data.mode = MODE_List;
  4227   4345       }else if( strcmp(z,"-line")==0 ){
  4228   4346         data.mode = MODE_Line;
  4229   4347       }else if( strcmp(z,"-column")==0 ){
  4230   4348         data.mode = MODE_Column;
  4231   4349       }else if( strcmp(z,"-csv")==0 ){
  4232   4350         data.mode = MODE_Csv;
  4233         -      memcpy(data.separator,",",2);
         4351  +      memcpy(data.colSeparator,",",2);
         4352  +    }else if( strcmp(z,"-ascii")==0 ){
         4353  +      data.mode = MODE_Ascii;
         4354  +      sqlite3_snprintf(sizeof(data.colSeparator), data.colSeparator,
         4355  +                       SEP_Unit);
         4356  +      sqlite3_snprintf(sizeof(data.rowSeparator), data.rowSeparator,
         4357  +                       SEP_Record);
  4234   4358       }else if( strcmp(z,"-separator")==0 ){
  4235         -      sqlite3_snprintf(sizeof(data.separator), data.separator,
         4359  +      sqlite3_snprintf(sizeof(data.colSeparator), data.colSeparator,
  4236   4360                          "%s",cmdline_option_value(argc,argv,++i));
  4237   4361       }else if( strcmp(z,"-newline")==0 ){
  4238         -      sqlite3_snprintf(sizeof(data.newline), data.newline,
         4362  +      sqlite3_snprintf(sizeof(data.rowSeparator), data.rowSeparator,
  4239   4363                          "%s",cmdline_option_value(argc,argv,++i));
  4240   4364       }else if( strcmp(z,"-nullvalue")==0 ){
  4241         -      sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue,
         4365  +      sqlite3_snprintf(sizeof(data.nullValue), data.nullValue,
  4242   4366                          "%s",cmdline_option_value(argc,argv,++i));
  4243   4367       }else if( strcmp(z,"-header")==0 ){
  4244   4368         data.showHeader = 1;
  4245   4369       }else if( strcmp(z,"-noheader")==0 ){
  4246   4370         data.showHeader = 0;
  4247   4371       }else if( strcmp(z,"-echo")==0 ){
  4248   4372         data.echoOn = 1;

Changes to test/shell1.test.

   203    203     catchcmd "test.db" ".explain \"OFF"
   204    204   } {0 {}}
   205    205   do_test shell1-2.2.4 {
   206    206     catchcmd "test.db" ".explain \'OFF"
   207    207   } {0 {}}
   208    208   do_test shell1-2.2.5 {
   209    209     catchcmd "test.db" ".mode \"insert FOO"
   210         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          210  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   211    211   do_test shell1-2.2.6 {
   212    212     catchcmd "test.db" ".mode \'insert FOO"
   213         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          213  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   214    214   
   215    215   # check multiple tokens, and quoted tokens
   216    216   do_test shell1-2.3.1 {
   217    217     catchcmd "test.db" ".explain 1"
   218    218   } {0 {}}
   219    219   do_test shell1-2.3.2 {
   220    220     catchcmd "test.db" ".explain on"
................................................................................
   234    234   do_test shell1-2.3.7 {
   235    235     catchcmd "test.db" ".\'explain\' \'OFF\'"
   236    236   } {0 {}}
   237    237   
   238    238   # check quoted args are unquoted
   239    239   do_test shell1-2.4.1 {
   240    240     catchcmd "test.db" ".mode FOO"
   241         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          241  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   242    242   do_test shell1-2.4.2 {
   243    243     catchcmd "test.db" ".mode csv"
   244    244   } {0 {}}
   245    245   do_test shell1-2.4.2 {
   246    246     catchcmd "test.db" ".mode \"csv\""
   247    247   } {0 {}}
   248    248   
................................................................................
   417    417   } {0 {}}
   418    418   do_test shell1-3.12.3 {
   419    419     # too many arguments
   420    420     catchcmd "test.db" ".indices FOO BAD"
   421    421   } {1 {Usage: .indices ?LIKE-PATTERN?}}
   422    422   
   423    423   # .mode MODE ?TABLE?     Set output mode where MODE is one of:
          424  +#                          ascii    Columns/rows delimited by 0x1F and 0x1E
   424    425   #                          csv      Comma-separated values
   425    426   #                          column   Left-aligned columns.  (See .width)
   426    427   #                          html     HTML <table> code
   427    428   #                          insert   SQL insert statements for TABLE
   428    429   #                          line     One value per line
   429         -#                          list     Values delimited by .separator string
          430  +#                          list     Values delimited by .separator strings
   430    431   #                          tabs     Tab-separated values
   431    432   #                          tcl      TCL list elements
   432    433   do_test shell1-3.13.1 {
   433    434     catchcmd "test.db" ".mode"
   434         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          435  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   435    436   do_test shell1-3.13.2 {
   436    437     catchcmd "test.db" ".mode FOO"
   437         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          438  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   438    439   do_test shell1-3.13.3 {
   439    440     catchcmd "test.db" ".mode csv"
   440    441   } {0 {}}
   441    442   do_test shell1-3.13.4 {
   442    443     catchcmd "test.db" ".mode column"
   443    444   } {0 {}}
   444    445   do_test shell1-3.13.5 {
................................................................................
   463    464     # extra arguments ignored
   464    465     catchcmd "test.db" ".mode tcl BAD"
   465    466   } {0 {}}
   466    467   
   467    468   # don't allow partial mode type matches
   468    469   do_test shell1-3.13.12 {
   469    470     catchcmd "test.db" ".mode l"
   470         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          471  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   471    472   do_test shell1-3.13.13 {
   472    473     catchcmd "test.db" ".mode li"
   473         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          474  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   474    475   do_test shell1-3.13.14 {
   475    476     catchcmd "test.db" ".mode lin"
   476    477   } {0 {}}
   477    478   
   478    479   # .nullvalue STRING      Print STRING in place of NULL values
   479    480   do_test shell1-3.14.1 {
   480    481     catchcmd "test.db" ".nullvalue"
................................................................................
   582    583     }
   583    584     catchcmd "test.db" ".schema"
   584    585   } {0 {CREATE TABLE t1(x);
   585    586   CREATE VIEW v2 AS SELECT x+1 AS y FROM t1;
   586    587   CREATE VIEW v1 AS SELECT y+1 FROM v2;}}
   587    588   db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;}
   588    589   
   589         -# .separator STRING      Change separator used by output mode and .import
          590  +# .separator STRING  Change column separator used by output and .import
   590    591   do_test shell1-3.22.1 {
   591    592     catchcmd "test.db" ".separator"
   592         -} {1 {Usage: .separator SEPARATOR ?NEWLINE?}}
          593  +} {1 {Usage: .separator COL ?ROW?}}
   593    594   do_test shell1-3.22.2 {
   594    595     catchcmd "test.db" ".separator FOO"
   595    596   } {0 {}}
   596    597   do_test shell1-3.22.3 {
   597    598     catchcmd "test.db" ".separator ABC XYZ"
   598    599   } {0 {}}
   599    600   do_test shell1-3.22.4 {
   600    601     # too many arguments
   601    602     catchcmd "test.db" ".separator FOO BAD BAD2"
   602         -} {1 {Usage: .separator SEPARATOR ?NEWLINE?}}
          603  +} {1 {Usage: .separator COL ?ROW?}}
   603    604   
   604    605   # .show                  Show the current values for various settings
   605    606   do_test shell1-3.23.1 {
   606    607     set res [catchcmd "test.db" ".show"]
   607    608     list [regexp {echo:} $res] \
   608    609          [regexp {explain:} $res] \
   609    610          [regexp {headers:} $res] \
   610    611          [regexp {mode:} $res] \
   611    612          [regexp {nullvalue:} $res] \
   612    613          [regexp {output:} $res] \
   613         -       [regexp {separator:} $res] \
          614  +       [regexp {colseparator:} $res] \
          615  +       [regexp {rowseparator:} $res] \
   614    616          [regexp {stats:} $res] \
   615    617          [regexp {width:} $res]
   616         -} {1 1 1 1 1 1 1 1 1}
          618  +} {1 1 1 1 1 1 1 1 1 1}
   617    619   do_test shell1-3.23.2 {
   618    620     # too many arguments
   619    621     catchcmd "test.db" ".show BAD"
   620    622   } {1 {Usage: .show}}
   621    623   
   622    624   # .stats ON|OFF          Turn stats on or off
   623    625   do_test shell1-3.23b.1 {

Changes to test/shell5.test.

    51     51     # too many arguments
    52     52     catchcmd "test.db" ".import FOO BAR BAD"
    53     53   } {1 {Usage: .import FILE TABLE}}
    54     54   
    55     55   # .separator STRING      Change separator used by output mode and .import
    56     56   do_test shell5-1.2.1 {
    57     57     catchcmd "test.db" ".separator"
    58         -} {1 {Usage: .separator SEPARATOR ?NEWLINE?}}
           58  +} {1 {Usage: .separator COL ?ROW?}}
    59     59   do_test shell5-1.2.2 {
    60     60     catchcmd "test.db" ".separator ONE"
    61     61   } {0 {}}
    62     62   do_test shell5-1.2.3 {
    63     63     catchcmd "test.db" ".separator ONE TWO"
    64     64   } {0 {}}
    65     65   do_test shell5-1.2.4 {
    66     66     # too many arguments
    67     67     catchcmd "test.db" ".separator ONE TWO THREE"
    68         -} {1 {Usage: .separator SEPARATOR ?NEWLINE?}}
           68  +} {1 {Usage: .separator COL ?ROW?}}
    69     69   
    70         -# separator should default to "|"
    71         -do_test shell5-1.3.1 {
           70  +# column separator should default to "|"
           71  +do_test shell5-1.3.1.1 {
           72  +  set res [catchcmd "test.db" ".show"]
           73  +  list [regexp {colseparator: \"\|\"} $res]
           74  +} {1}
           75  +
           76  +# row separator should default to "\n"
           77  +do_test shell5-1.3.1.2 {
    72     78     set res [catchcmd "test.db" ".show"]
    73         -  list [regexp {separator: \"\|\"} $res]
           79  +  list [regexp {rowseparator: \"\\n\"} $res]
    74     80   } {1}
    75     81   
    76     82   # set separator to different value.
    77     83   # check that .show reports new value
    78     84   do_test shell5-1.3.2 {
    79     85     set res [catchcmd "test.db" {.separator ,
    80     86   .show}]
................................................................................
   368    374   .mode csv
   369    375   CREATE TABLE t4(a, b);
   370    376   .import shell5.csv t4
   371    377     }]
   372    378     db eval { SELECT * FROM t4 }
   373    379   } {xy\" hello one 2 {} {}}
   374    380   
          381  +#----------------------------------------------------------------------------
          382  +# Tests for the shell "ascii" import/export mode.
          383  +#
          384  +do_test shell5-3.1 {
          385  +  set fd [open shell5.csv w]
          386  +  fconfigure $fd -encoding binary -translation binary
          387  +  puts -nonewline $fd "\"test 1\"\x1F,test 2\r\n\x1E"
          388  +  puts -nonewline $fd "test 3\x1Ftest 4\n"
          389  +  close $fd
          390  +  catchcmd test.db {
          391  +.mode ascii
          392  +CREATE TABLE t5(a, b);
          393  +.import shell5.csv t5
          394  +  }
          395  +  db eval { SELECT * FROM t5 }
          396  +} "\{\"test 1\"} \{,test 2\r\n\} \{test 3\} \{test 4\n\}"
          397  +
          398  +#
          399  +# NOTE: This test ends up converting the "\r\n" to "\n\n" due
          400  +#       to end-of-line translation on the "stdout" channel.
          401  +#
          402  +do_test shell5-3.2 {
          403  +  catchcmd test.db {
          404  +.mode ascii
          405  +SELECT * FROM t5;
          406  +  }
          407  +} "0 \{\"test 1\"\x1F,test 2\n\n\x1Etest 3\x1Ftest 4\n\x1E\}"
   375    408   
   376    409   finish_test