/ Check-in [7fe601ea]
Login

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

Overview
Comment:Add new ASCII mode to the shell capable of importing and exporting using the official unit and record separators (i.e. 0x1F and 0x1E, respectively).
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | asciiMode
Files: files | file ages | folders
SHA1: 7fe601ead0d0ae26cb09d0dbc7d6367785376567
User & Date: mistachkin 2014-07-19 20:15:16
Context
2014-07-24
22:13
Merge updates from trunk. check-in: 8dc0cdf6 user: mistachkin tags: asciiMode
2014-07-19
20:15
Add new ASCII mode to the shell capable of importing and exporting using the official unit and record separators (i.e. 0x1F and 0x1E, respectively). check-in: 7fe601ea user: mistachkin tags: asciiMode
17:57
Update the sqlite3_stmt_busy() function so that it correctly returns true for "ROLLBACK" statements that have been stepped but not yet reset. check-in: 61cee3c0 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.

   453    453     FILE *out;             /* Write results here */
   454    454     FILE *traceOut;        /* Output for sqlite3_trace() */
   455    455     int nErr;              /* Number of errors seen */
   456    456     int mode;              /* An output mode setting */
   457    457     int writableSchema;    /* True if PRAGMA writable_schema=ON */
   458    458     int showHeader;        /* True to show column names in List or Column mode */
   459    459     char *zDestTable;      /* Name of destination table when MODE_Insert */
   460         -  char separator[20];    /* Separator character for MODE_List */
          460  +  char colSeparator[20]; /* Column separator character for several modes */
          461  +  char rowSeparator[20]; /* Row separator character for MODE_Ascii */
   461    462     int colWidth[100];     /* Requested width of each column when in column mode*/
   462    463     int actualWidth[100];  /* Actual width of each column */
   463    464     char nullvalue[20];    /* The text to print when a NULL comes back from
   464    465                            ** the database */
   465    466     struct previous_mode_data explainPrev;
   466    467                            /* Holds the mode information just before
   467    468                            ** .explain ON */
................................................................................
   484    485   #define MODE_List     2  /* One record per line with a separator */
   485    486   #define MODE_Semi     3  /* Same as MODE_List but append ";" to each line */
   486    487   #define MODE_Html     4  /* Generate an XHTML table */
   487    488   #define MODE_Insert   5  /* Generate SQL "insert" statements */
   488    489   #define MODE_Tcl      6  /* Generate ANSI-C or TCL quoted elements */
   489    490   #define MODE_Csv      7  /* Quote strings, numbers are plain */
   490    491   #define MODE_Explain  8  /* Like MODE_Column, but do not truncate data */
          492  +#define MODE_Ascii    9  /* Use ASCII unit and record separators (0x1F/0x1E) */
   491    493   
   492    494   static const char *modeDescr[] = {
   493    495     "line",
   494    496     "column",
   495    497     "list",
   496    498     "semi",
   497    499     "html",
   498    500     "insert",
   499    501     "tcl",
   500    502     "csv",
   501    503     "explain",
          504  +  "ascii",
   502    505   };
   503    506   
          507  +/*
          508  +** These are the column/row separators used by the ASCII mode.
          509  +*/
          510  +#define SEP_Line      "\n"
          511  +#define SEP_Column    "\x1F"
          512  +#define SEP_Row       "\x1E"
          513  +
   504    514   /*
   505    515   ** Number of elements in an array
   506    516   */
   507    517   #define ArraySize(X)  (int)(sizeof(X)/sizeof(X[0]))
   508    518   
   509    519   /*
   510    520   ** Compute a string length that is limited to what can be stored in
................................................................................
   663    673   */
   664    674   static void output_csv(struct callback_data *p, const char *z, int bSep){
   665    675     FILE *out = p->out;
   666    676     if( z==0 ){
   667    677       fprintf(out,"%s",p->nullvalue);
   668    678     }else{
   669    679       int i;
   670         -    int nSep = strlen30(p->separator);
          680  +    int nSep = strlen30(p->colSeparator);
   671    681       for(i=0; z[i]; i++){
   672    682         if( needCsvQuote[((unsigned char*)z)[i]] 
   673         -         || (z[i]==p->separator[0] && 
   674         -             (nSep==1 || memcmp(z, p->separator, nSep)==0)) ){
          683  +         || (z[i]==p->colSeparator[0] && 
          684  +             (nSep==1 || memcmp(z, p->colSeparator, nSep)==0)) ){
   675    685           i = 0;
   676    686           break;
   677    687         }
   678    688       }
   679    689       if( i==0 ){
   680    690         putc('"', out);
   681    691         for(i=0; z[i]; i++){
................................................................................
   684    694         }
   685    695         putc('"', out);
   686    696       }else{
   687    697         fprintf(out, "%s", z);
   688    698       }
   689    699     }
   690    700     if( bSep ){
   691         -    fprintf(p->out, "%s", p->separator);
          701  +    fprintf(p->out, "%s", p->colSeparator);
   692    702     }
   693    703   }
   694    704   
   695    705   #ifdef SIGINT
   696    706   /*
   697    707   ** This routine runs when the user presses Ctrl-C
   698    708   */
................................................................................
   716    726       case MODE_Line: {
   717    727         int w = 5;
   718    728         if( azArg==0 ) break;
   719    729         for(i=0; i<nArg; i++){
   720    730           int len = strlen30(azCol[i] ? azCol[i] : "");
   721    731           if( len>w ) w = len;
   722    732         }
   723         -      if( p->cnt++>0 ) fprintf(p->out,"\n");
          733  +      if( p->cnt++>0 ) fprintf(p->out, "%s", p->rowSeparator);
   724    734         for(i=0; i<nArg; i++){
   725         -        fprintf(p->out,"%*s = %s\n", w, azCol[i],
   726         -                azArg[i] ? azArg[i] : p->nullvalue);
          735  +        fprintf(p->out,"%*s = %s%s", w, azCol[i],
          736  +                azArg[i] ? azArg[i] : p->nullvalue, p->rowSeparator);
   727    737         }
   728    738         break;
   729    739       }
   730    740       case MODE_Explain:
   731    741       case MODE_Column: {
   732    742         if( p->cnt++==0 ){
   733    743           for(i=0; i<nArg; i++){
................................................................................
   744    754               if( w<n ) w = n;
   745    755             }
   746    756             if( i<ArraySize(p->actualWidth) ){
   747    757               p->actualWidth[i] = w;
   748    758             }
   749    759             if( p->showHeader ){
   750    760               if( w<0 ){
   751         -              fprintf(p->out,"%*.*s%s",-w,-w,azCol[i], i==nArg-1 ? "\n": "  ");
          761  +              fprintf(p->out,"%*.*s%s",-w,-w,azCol[i],
          762  +                      i==nArg-1 ? p->rowSeparator : "  ");
   752    763               }else{
   753         -              fprintf(p->out,"%-*.*s%s",w,w,azCol[i], i==nArg-1 ? "\n": "  ");
          764  +              fprintf(p->out,"%-*.*s%s",w,w,azCol[i],
          765  +                      i==nArg-1 ? p->rowSeparator : "  ");
   754    766               }
   755    767             }
   756    768           }
   757    769           if( p->showHeader ){
   758    770             for(i=0; i<nArg; i++){
   759    771               int w;
   760    772               if( i<ArraySize(p->actualWidth) ){
................................................................................
   761    773                  w = p->actualWidth[i];
   762    774                  if( w<0 ) w = -w;
   763    775               }else{
   764    776                  w = 10;
   765    777               }
   766    778               fprintf(p->out,"%-*.*s%s",w,w,"-----------------------------------"
   767    779                      "----------------------------------------------------------",
   768         -                    i==nArg-1 ? "\n": "  ");
          780  +                    i==nArg-1 ? p->rowSeparator : "  ");
   769    781             }
   770    782           }
   771    783         }
   772    784         if( azArg==0 ) break;
   773    785         for(i=0; i<nArg; i++){
   774    786           int w;
   775    787           if( i<ArraySize(p->actualWidth) ){
................................................................................
   784    796             if( p->iIndent<p->nIndent ){
   785    797               fprintf(p->out, "%*.s", p->aiIndent[p->iIndent], "");
   786    798             }
   787    799             p->iIndent++;
   788    800           }
   789    801           if( w<0 ){
   790    802             fprintf(p->out,"%*.*s%s",-w,-w,
   791         -              azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": "  ");
          803  +              azArg[i] ? azArg[i] : p->nullvalue,
          804  +              i==nArg-1 ? p->rowSeparator : "  ");
   792    805           }else{
   793    806             fprintf(p->out,"%-*.*s%s",w,w,
   794         -              azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": "  ");
          807  +              azArg[i] ? azArg[i] : p->nullvalue,
          808  +              i==nArg-1 ? p->rowSeparator : "  ");
   795    809           }
   796    810         }
   797    811         break;
   798    812       }
   799    813       case MODE_Semi:
   800    814       case MODE_List: {
   801    815         if( p->cnt++==0 && p->showHeader ){
   802    816           for(i=0; i<nArg; i++){
   803         -          fprintf(p->out,"%s%s",azCol[i], i==nArg-1 ? "\n" : p->separator);
          817  +          fprintf(p->out,"%s%s",azCol[i],
          818  +                  i==nArg-1 ? p->rowSeparator : p->colSeparator);
   804    819           }
   805    820         }
   806    821         if( azArg==0 ) break;
   807    822         for(i=0; i<nArg; i++){
   808    823           char *z = azArg[i];
   809    824           if( z==0 ) z = p->nullvalue;
   810    825           fprintf(p->out, "%s", z);
   811    826           if( i<nArg-1 ){
   812         -          fprintf(p->out, "%s", p->separator);
          827  +          fprintf(p->out, "%s", p->colSeparator);
   813    828           }else if( p->mode==MODE_Semi ){
   814         -          fprintf(p->out, ";\n");
          829  +          fprintf(p->out, ";%s", p->rowSeparator);
   815    830           }else{
   816         -          fprintf(p->out, "\n");
          831  +          fprintf(p->out, "%s", p->rowSeparator);
   817    832           }
   818    833         }
   819    834         break;
   820    835       }
   821    836       case MODE_Html: {
   822    837         if( p->cnt++==0 && p->showHeader ){
   823    838           fprintf(p->out,"<TR>");
................................................................................
   838    853         fprintf(p->out,"</TR>\n");
   839    854         break;
   840    855       }
   841    856       case MODE_Tcl: {
   842    857         if( p->cnt++==0 && p->showHeader ){
   843    858           for(i=0; i<nArg; i++){
   844    859             output_c_string(p->out,azCol[i] ? azCol[i] : "");
   845         -          if(i<nArg-1) fprintf(p->out, "%s", p->separator);
          860  +          if(i<nArg-1) fprintf(p->out, "%s", p->colSeparator);
   846    861           }
   847         -        fprintf(p->out,"\n");
          862  +        fprintf(p->out, "%s", p->rowSeparator);
   848    863         }
   849    864         if( azArg==0 ) break;
   850    865         for(i=0; i<nArg; i++){
   851    866           output_c_string(p->out, azArg[i] ? azArg[i] : p->nullvalue);
   852         -        if(i<nArg-1) fprintf(p->out, "%s", p->separator);
          867  +        if(i<nArg-1) fprintf(p->out, "%s", p->colSeparator);
   853    868         }
   854         -      fprintf(p->out,"\n");
          869  +      fprintf(p->out, "%s", p->rowSeparator);
   855    870         break;
   856    871       }
   857    872       case MODE_Csv: {
   858    873         if( p->cnt++==0 && p->showHeader ){
   859    874           for(i=0; i<nArg; i++){
   860    875             output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1);
   861    876           }
   862         -        fprintf(p->out,"\n");
          877  +        fprintf(p->out, "%s", p->rowSeparator);
   863    878         }
   864    879         if( azArg==0 ) break;
   865    880         for(i=0; i<nArg; i++){
   866    881           output_csv(p, azArg[i], i<nArg-1);
   867    882         }
   868         -      fprintf(p->out,"\n");
          883  +      fprintf(p->out, "%s", p->rowSeparator);
   869    884         break;
   870    885       }
   871    886       case MODE_Insert: {
   872    887         p->cnt++;
   873    888         if( azArg==0 ) break;
   874    889         fprintf(p->out,"INSERT INTO %s VALUES(",p->zDestTable);
   875    890         for(i=0; i<nArg; i++){
................................................................................
   892    907           }else{
   893    908             if( zSep[0] ) fprintf(p->out,"%s",zSep);
   894    909             output_quoted_string(p->out, azArg[i]);
   895    910           }
   896    911         }
   897    912         fprintf(p->out,");\n");
   898    913         break;
          914  +    }
          915  +    case MODE_Ascii: {
          916  +      if( p->cnt++==0 && p->showHeader ){
          917  +        for(i=0; i<nArg; i++){
          918  +          if( i>0 ) fprintf(p->out, "%s", p->colSeparator);
          919  +          fprintf(p->out,"%s",azCol[i] ? azCol[i] : "");
          920  +        }
          921  +        fprintf(p->out, "%s", p->rowSeparator);
          922  +      }
          923  +      if( azArg==0 ) break;
          924  +      for(i=0; i<nArg; i++){
          925  +        if( i>0 ) fprintf(p->out, "%s", p->colSeparator);
          926  +        fprintf(p->out,"%s",azArg[i] ? azArg[i] : p->nullvalue);
          927  +      }
          928  +      fprintf(p->out, "%s", p->rowSeparator);
          929  +      break;
   899    930       }
   900    931     }
   901    932     return 0;
   902    933   }
   903    934   
   904    935   /*
   905    936   ** This is the callback routine that the SQLite library
................................................................................
  1570   1601   /*
  1571   1602   ** Text of a help message
  1572   1603   */
  1573   1604   static char zHelp[] =
  1574   1605     ".backup ?DB? FILE      Backup DB (default \"main\") to FILE\n"
  1575   1606     ".bail on|off           Stop after hitting an error.  Default OFF\n"
  1576   1607     ".clone NEWDB           Clone data into NEWDB from the existing database\n"
         1608  +  ".colseparator STRING   This is an alias for .separator\n"
  1577   1609     ".databases             List names and files of attached databases\n"
  1578   1610     ".dump ?TABLE? ...      Dump the database in an SQL text format\n"
  1579   1611     "                         If TABLE specified, only dump tables matching\n"
  1580   1612     "                         LIKE pattern TABLE.\n"
  1581   1613     ".echo on|off           Turn command echo on or off\n"
  1582   1614     ".eqp on|off            Enable or disable automatic EXPLAIN QUERY PLAN\n"
  1583   1615     ".exit                  Exit this program\n"
................................................................................
  1594   1626     ".iotrace FILE          Enable I/O diagnostic logging to FILE\n"
  1595   1627   #endif
  1596   1628   #ifndef SQLITE_OMIT_LOAD_EXTENSION
  1597   1629     ".load FILE ?ENTRY?     Load an extension library\n"
  1598   1630   #endif
  1599   1631     ".log FILE|off          Turn logging on or off.  FILE can be stderr/stdout\n"
  1600   1632     ".mode MODE ?TABLE?     Set output mode where MODE is one of:\n"
         1633  +  "                         ascii    Columns/rows delimited with 0x1F and 0x1E\n"
  1601   1634     "                         csv      Comma-separated values\n"
  1602   1635     "                         column   Left-aligned columns.  (See .width)\n"
  1603   1636     "                         html     HTML <table> code\n"
  1604   1637     "                         insert   SQL insert statements for TABLE\n"
  1605   1638     "                         line     One value per line\n"
  1606   1639     "                         list     Values delimited by .separator string\n"
  1607   1640     "                         tabs     Tab-separated values\n"
................................................................................
  1611   1644     ".open ?FILENAME?       Close existing database and reopen FILENAME\n"
  1612   1645     ".output ?FILENAME?     Send output to FILENAME or stdout\n"
  1613   1646     ".print STRING...       Print literal STRING\n"
  1614   1647     ".prompt MAIN CONTINUE  Replace the standard prompts\n"
  1615   1648     ".quit                  Exit this program\n"
  1616   1649     ".read FILENAME         Execute SQL in FILENAME\n"
  1617   1650     ".restore ?DB? FILE     Restore content of DB (default \"main\") from FILE\n"
         1651  +  ".rowseparator STRING   Change row separator for output mode and .import\n"
  1618   1652     ".save FILE             Write in-memory database into FILE\n"
  1619   1653     ".schema ?TABLE?        Show the CREATE statements\n"
  1620   1654     "                         If TABLE specified, only show tables matching\n"
  1621   1655     "                         LIKE pattern TABLE.\n"
  1622         -  ".separator STRING      Change separator used by output mode and .import\n"
         1656  +  ".separator STRING      Change column separator for output mode and .import\n"
  1623   1657     ".shell CMD ARGS...     Run CMD ARGS... in a system shell\n"
  1624   1658     ".show                  Show the current values for various settings\n"
  1625   1659     ".stats on|off          Turn stats on or off\n"
  1626   1660     ".system CMD ARGS...    Run CMD ARGS... in a system shell\n"
  1627   1661     ".tables ?TABLE?        List names of tables\n"
  1628   1662     "                         If TABLE specified, only list tables matching\n"
  1629   1663     "                         LIKE pattern TABLE.\n"
................................................................................
  1828   1862   */
  1829   1863   static void test_breakpoint(void){
  1830   1864     static int nCall = 0;
  1831   1865     nCall++;
  1832   1866   }
  1833   1867   
  1834   1868   /*
  1835         -** An object used to read a CSV file
         1869  +** An object used to read a CSV and other files for import.
  1836   1870   */
  1837         -typedef struct CSVReader CSVReader;
  1838         -struct CSVReader {
         1871  +typedef struct ImportCtx ImportCtx;
         1872  +struct ImportCtx {
  1839   1873     const char *zFile;  /* Name of the input file */
  1840   1874     FILE *in;           /* Read the CSV text from this input stream */
  1841   1875     char *z;            /* Accumulated text for a field */
  1842   1876     int n;              /* Number of bytes in z */
  1843   1877     int nAlloc;         /* Space allocated for z[] */
  1844   1878     int nLine;          /* Current line number */
  1845   1879     int cTerm;          /* Character that terminated the most recent field */
  1846         -  int cSeparator;     /* The separator character.  (Usually ",") */
         1880  +  int cColSep;        /* The column separator character.  (Usually ",") */
         1881  +  int cRowSep;        /* The row separator character.  (Usually "\n") */
  1847   1882   };
  1848   1883   
  1849   1884   /* Append a single byte to z[] */
  1850         -static void csv_append_char(CSVReader *p, int c){
         1885  +static void import_append_char(ImportCtx *p, int c){
  1851   1886     if( p->n+1>=p->nAlloc ){
  1852   1887       p->nAlloc += p->nAlloc + 100;
  1853   1888       p->z = sqlite3_realloc(p->z, p->nAlloc);
  1854   1889       if( p->z==0 ){
  1855   1890         fprintf(stderr, "out of memory\n");
  1856   1891         exit(1);
  1857   1892       }
................................................................................
  1861   1896   
  1862   1897   /* Read a single field of CSV text.  Compatible with rfc4180 and extended
  1863   1898   ** with the option of having a separator other than ",".
  1864   1899   **
  1865   1900   **   +  Input comes from p->in.
  1866   1901   **   +  Store results in p->z of length p->n.  Space to hold p->z comes
  1867   1902   **      from sqlite3_malloc().
  1868         -**   +  Use p->cSep as the separator.  The default is ",".
         1903  +**   +  Use p->cSep as the column separator.  The default is ",".
         1904  +**   +  Use p->rSep as the row separator.  The default is "\n".
  1869   1905   **   +  Keep track of the line number in p->nLine.
  1870   1906   **   +  Store the character that terminates the field in p->cTerm.  Store
  1871   1907   **      EOF on end-of-file.
  1872   1908   **   +  Report syntax errors on stderr
  1873   1909   */
  1874         -static char *csv_read_one_field(CSVReader *p){
  1875         -  int c, pc, ppc;
  1876         -  int cSep = p->cSeparator;
         1910  +static char *csv_read_one_field(ImportCtx *p){
         1911  +  int c;
         1912  +  int cSep = p->cColSep;
         1913  +  int rSep = p->cRowSep;
  1877   1914     p->n = 0;
  1878   1915     c = fgetc(p->in);
  1879   1916     if( c==EOF || seenInterrupt ){
  1880   1917       p->cTerm = EOF;
  1881   1918       return 0;
  1882   1919     }
  1883   1920     if( c=='"' ){
         1921  +    int pc, ppc;
  1884   1922       int startLine = p->nLine;
  1885   1923       int cQuote = c;
  1886   1924       pc = ppc = 0;
  1887   1925       while( 1 ){
  1888   1926         c = fgetc(p->in);
  1889         -      if( c=='\n' ) p->nLine++;
         1927  +      if( c==rSep ) p->nLine++;
  1890   1928         if( c==cQuote ){
  1891   1929           if( pc==cQuote ){
  1892   1930             pc = 0;
  1893   1931             continue;
  1894   1932           }
  1895   1933         }
  1896   1934         if( (c==cSep && pc==cQuote)
  1897         -       || (c=='\n' && pc==cQuote)
  1898         -       || (c=='\n' && pc=='\r' && ppc==cQuote)
         1935  +       || (c==rSep && pc==cQuote)
         1936  +       || (c==rSep && pc=='\r' && ppc==cQuote)
  1899   1937          || (c==EOF && pc==cQuote)
  1900   1938         ){
  1901   1939           do{ p->n--; }while( p->z[p->n]!=cQuote );
  1902   1940           p->cTerm = c;
  1903   1941           break;
  1904   1942         }
  1905   1943         if( pc==cQuote && c!='\r' ){
  1906   1944           fprintf(stderr, "%s:%d: unescaped %c character\n",
  1907   1945                   p->zFile, p->nLine, cQuote);
  1908   1946         }
  1909   1947         if( c==EOF ){
  1910   1948           fprintf(stderr, "%s:%d: unterminated %c-quoted field\n",
  1911   1949                   p->zFile, startLine, cQuote);
  1912         -        p->cTerm = EOF;
         1950  +        p->cTerm = c;
  1913   1951           break;
  1914   1952         }
  1915         -      csv_append_char(p, c);
         1953  +      import_append_char(p, c);
  1916   1954         ppc = pc;
  1917   1955         pc = c;
  1918   1956       }
  1919   1957     }else{
  1920         -    while( c!=EOF && c!=cSep && c!='\n' ){
  1921         -      csv_append_char(p, c);
         1958  +    while( c!=EOF && c!=cSep && c!=rSep ){
         1959  +      import_append_char(p, c);
  1922   1960         c = fgetc(p->in);
  1923   1961       }
  1924         -    if( c=='\n' ){
         1962  +    if( c==rSep ){
  1925   1963         p->nLine++;
  1926   1964         if( p->n>0 && p->z[p->n-1]=='\r' ) p->n--;
  1927   1965       }
  1928   1966       p->cTerm = c;
  1929   1967     }
  1930   1968     if( p->z ) p->z[p->n] = 0;
  1931   1969     return p->z;
  1932   1970   }
         1971  +
         1972  +/* Read a single field of ASCII delimited text.
         1973  +**
         1974  +**   +  Input comes from p->in.
         1975  +**   +  Store results in p->z of length p->n.  Space to hold p->z comes
         1976  +**      from sqlite3_malloc().
         1977  +**   +  Use p->cSep as the column separator.  The default is "\x1F".
         1978  +**   +  Use p->rSep as the row separator.  The default is "\x1E".
         1979  +**   +  Keep track of the row number in p->nLine.
         1980  +**   +  Store the character that terminates the field in p->cTerm.  Store
         1981  +**      EOF on end-of-file.
         1982  +**   +  Report syntax errors on stderr
         1983  +*/
         1984  +static char *ascii_read_one_field(ImportCtx *p){
         1985  +  int c;
         1986  +  int cSep = p->cColSep;
         1987  +  int rSep = p->cRowSep;
         1988  +  p->n = 0;
         1989  +  c = fgetc(p->in);
         1990  +  if( c==EOF || seenInterrupt ){
         1991  +    p->cTerm = EOF;
         1992  +    return 0;
         1993  +  }
         1994  +  while( c!=EOF && c!=cSep && c!=rSep ){
         1995  +    import_append_char(p, c);
         1996  +    c = fgetc(p->in);
         1997  +  }
         1998  +  if( c==rSep ){
         1999  +    p->nLine++;
         2000  +  }
         2001  +  p->cTerm = c;
         2002  +  if( p->z ) p->z[p->n] = 0;
         2003  +  return p->z;
         2004  +}
  1933   2005   
  1934   2006   /*
  1935   2007   ** Try to transfer data for table zTable.  If an error is seen while
  1936   2008   ** moving forward, try to go backwards.  The backwards movement won't
  1937   2009   ** work for WITHOUT ROWID tables.
  1938   2010   */
  1939   2011   static void tryToCloneData(
................................................................................
  2481   2553       char *zTable;               /* Insert data into this table */
  2482   2554       char *zFile;                /* Name of file to extra content from */
  2483   2555       sqlite3_stmt *pStmt = NULL; /* A statement */
  2484   2556       int nCol;                   /* Number of columns in the table */
  2485   2557       int nByte;                  /* Number of bytes in an SQL string */
  2486   2558       int i, j;                   /* Loop counters */
  2487   2559       int needCommit;             /* True to COMMIT or ROLLBACK at end */
  2488         -    int nSep;                   /* Number of bytes in p->separator[] */
         2560  +    int nSep;                   /* Number of bytes in p->colSeparator[] */
  2489   2561       char *zSql;                 /* An SQL statement */
  2490         -    CSVReader sCsv;             /* Reader context */
         2562  +    ImportCtx sCtx;             /* Reader context */
         2563  +    char *(*xRead)(ImportCtx*); /* Procecure to read one value */
  2491   2564       int (*xCloser)(FILE*);      /* Procedure to close th3 connection */
  2492   2565   
  2493   2566       if( nArg!=3 ){
  2494   2567         fprintf(stderr, "Usage: .import FILE TABLE\n");
  2495   2568         goto meta_command_exit;
  2496   2569       }
  2497   2570       zFile = azArg[1];
  2498   2571       zTable = azArg[2];
  2499   2572       seenInterrupt = 0;
  2500         -    memset(&sCsv, 0, sizeof(sCsv));
         2573  +    memset(&sCtx, 0, sizeof(sCtx));
  2501   2574       open_db(p, 0);
  2502         -    nSep = strlen30(p->separator);
         2575  +    nSep = strlen30(p->colSeparator);
         2576  +    if( nSep==0 ){
         2577  +      fprintf(stderr, "Error: non-null column separator required for import\n");
         2578  +      return 1;
         2579  +    }
         2580  +    if( nSep>1 ){
         2581  +      fprintf(stderr, "Error: multi-character column separators not allowed"
         2582  +                      " for import\n");
         2583  +      return 1;
         2584  +    }
         2585  +    nSep = strlen30(p->rowSeparator);
  2503   2586       if( nSep==0 ){
  2504         -      fprintf(stderr, "Error: non-null separator required for import\n");
         2587  +      fprintf(stderr, "Error: non-null row separator required for import\n");
  2505   2588         return 1;
  2506   2589       }
  2507   2590       if( nSep>1 ){
  2508         -      fprintf(stderr, "Error: multi-character separators not allowed"
         2591  +      fprintf(stderr, "Error: multi-character row separators not allowed"
  2509   2592                         " for import\n");
  2510   2593         return 1;
  2511   2594       }
  2512         -    sCsv.zFile = zFile;
  2513         -    sCsv.nLine = 1;
  2514         -    if( sCsv.zFile[0]=='|' ){
  2515         -      sCsv.in = popen(sCsv.zFile+1, "r");
  2516         -      sCsv.zFile = "<pipe>";
         2595  +    sCtx.zFile = zFile;
         2596  +    sCtx.nLine = 1;
         2597  +    if( sCtx.zFile[0]=='|' ){
         2598  +      sCtx.in = popen(sCtx.zFile+1, "r");
         2599  +      sCtx.zFile = "<pipe>";
  2517   2600         xCloser = pclose;
  2518   2601       }else{
  2519         -      sCsv.in = fopen(sCsv.zFile, "rb");
         2602  +      sCtx.in = fopen(sCtx.zFile, "rb");
  2520   2603         xCloser = fclose;
  2521   2604       }
  2522         -    if( sCsv.in==0 ){
         2605  +    if( p->mode==MODE_Ascii ){
         2606  +      xRead = ascii_read_one_field;
         2607  +    }else{
         2608  +      xRead = csv_read_one_field;
         2609  +    }
         2610  +    if( sCtx.in==0 ){
  2523   2611         fprintf(stderr, "Error: cannot open \"%s\"\n", zFile);
  2524   2612         return 1;
  2525   2613       }
  2526         -    sCsv.cSeparator = p->separator[0];
         2614  +    sCtx.cColSep = p->colSeparator[0];
         2615  +    sCtx.cRowSep = p->rowSeparator[0];
  2527   2616       zSql = sqlite3_mprintf("SELECT * FROM %s", zTable);
  2528   2617       if( zSql==0 ){
  2529   2618         fprintf(stderr, "Error: out of memory\n");
  2530         -      xCloser(sCsv.in);
         2619  +      xCloser(sCtx.in);
  2531   2620         return 1;
  2532   2621       }
  2533   2622       nByte = strlen30(zSql);
  2534   2623       rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
  2535         -    csv_append_char(&sCsv, 0);    /* To ensure sCsv.z is allocated */
         2624  +    import_append_char(&sCtx, 0);    /* To ensure sCtx.z is allocated */
  2536   2625       if( rc && sqlite3_strglob("no such table: *", sqlite3_errmsg(db))==0 ){
  2537   2626         char *zCreate = sqlite3_mprintf("CREATE TABLE %s", zTable);
  2538   2627         char cSep = '(';
  2539         -      while( csv_read_one_field(&sCsv) ){
  2540         -        zCreate = sqlite3_mprintf("%z%c\n  \"%s\" TEXT", zCreate, cSep, sCsv.z);
         2628  +      while( xRead(&sCtx) ){
         2629  +        zCreate = sqlite3_mprintf("%z%c\n  \"%s\" TEXT", zCreate, cSep, sCtx.z);
  2541   2630           cSep = ',';
  2542         -        if( sCsv.cTerm!=sCsv.cSeparator ) break;
         2631  +        if( sCtx.cTerm!=sCtx.cColSep ) break;
  2543   2632         }
  2544   2633         if( cSep=='(' ){
  2545   2634           sqlite3_free(zCreate);
  2546         -        sqlite3_free(sCsv.z);
  2547         -        xCloser(sCsv.in);
  2548         -        fprintf(stderr,"%s: empty file\n", sCsv.zFile);
         2635  +        sqlite3_free(sCtx.z);
         2636  +        xCloser(sCtx.in);
         2637  +        fprintf(stderr,"%s: empty file\n", sCtx.zFile);
  2549   2638           return 1;
  2550   2639         }
  2551   2640         zCreate = sqlite3_mprintf("%z\n)", zCreate);
  2552   2641         rc = sqlite3_exec(p->db, zCreate, 0, 0, 0);
  2553   2642         sqlite3_free(zCreate);
  2554   2643         if( rc ){
  2555   2644           fprintf(stderr, "CREATE TABLE %s(...) failed: %s\n", zTable,
  2556   2645                   sqlite3_errmsg(db));
  2557         -        sqlite3_free(sCsv.z);
  2558         -        xCloser(sCsv.in);
         2646  +        sqlite3_free(sCtx.z);
         2647  +        xCloser(sCtx.in);
  2559   2648           return 1;
  2560   2649         }
  2561   2650         rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
  2562   2651       }
  2563   2652       sqlite3_free(zSql);
  2564   2653       if( rc ){
  2565   2654         if (pStmt) sqlite3_finalize(pStmt);
  2566   2655         fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));
  2567         -      xCloser(sCsv.in);
         2656  +      xCloser(sCtx.in);
  2568   2657         return 1;
  2569   2658       }
  2570   2659       nCol = sqlite3_column_count(pStmt);
  2571   2660       sqlite3_finalize(pStmt);
  2572   2661       pStmt = 0;
  2573   2662       if( nCol==0 ) return 0; /* no columns, no error */
  2574   2663       zSql = sqlite3_malloc( nByte*2 + 20 + nCol*2 );
  2575   2664       if( zSql==0 ){
  2576   2665         fprintf(stderr, "Error: out of memory\n");
  2577         -      xCloser(sCsv.in);
         2666  +      xCloser(sCtx.in);
  2578   2667         return 1;
  2579   2668       }
  2580   2669       sqlite3_snprintf(nByte+20, zSql, "INSERT INTO \"%w\" VALUES(?", zTable);
  2581   2670       j = strlen30(zSql);
  2582   2671       for(i=1; i<nCol; i++){
  2583   2672         zSql[j++] = ',';
  2584   2673         zSql[j++] = '?';
................................................................................
  2586   2675       zSql[j++] = ')';
  2587   2676       zSql[j] = 0;
  2588   2677       rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
  2589   2678       sqlite3_free(zSql);
  2590   2679       if( rc ){
  2591   2680         fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
  2592   2681         if (pStmt) sqlite3_finalize(pStmt);
  2593         -      xCloser(sCsv.in);
         2682  +      xCloser(sCtx.in);
  2594   2683         return 1;
  2595   2684       }
  2596   2685       needCommit = sqlite3_get_autocommit(db);
  2597   2686       if( needCommit ) sqlite3_exec(db, "BEGIN", 0, 0, 0);
  2598   2687       do{
  2599         -      int startLine = sCsv.nLine;
         2688  +      int startLine = sCtx.nLine;
  2600   2689         for(i=0; i<nCol; i++){
  2601         -        char *z = csv_read_one_field(&sCsv);
         2690  +        char *z = xRead(&sCtx);
         2691  +        /*
         2692  +        ** Did we reach end-of-file before finding any columns?
         2693  +        ** If so, stop instead of NULL filling the remaining columns.
         2694  +        */
  2602   2695           if( z==0 && i==0 ) break;
         2696  +        /*
         2697  +        ** Did we reach end-of-file OR end-of-line before finding any
         2698  +        ** columns in ASCII mode?  If so, stop instead of NULL filling
         2699  +        ** the remaining columns.
         2700  +        */
         2701  +        if( p->mode==MODE_Ascii && (z==0 || z[0]==0) && i==0 ) break;
  2603   2702           sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT);
  2604         -        if( i<nCol-1 && sCsv.cTerm!=sCsv.cSeparator ){
         2703  +        if( i<nCol-1 && sCtx.cTerm!=sCtx.cColSep ){
  2605   2704             fprintf(stderr, "%s:%d: expected %d columns but found %d - "
  2606   2705                             "filling the rest with NULL\n",
  2607         -                          sCsv.zFile, startLine, nCol, i+1);
         2706  +                          sCtx.zFile, startLine, nCol, i+1);
  2608   2707             i++;
  2609   2708             while( i<=nCol ){ sqlite3_bind_null(pStmt, i); i++; }
  2610   2709           }
  2611   2710         }
  2612         -      if( sCsv.cTerm==sCsv.cSeparator ){
         2711  +      if( sCtx.cTerm==sCtx.cColSep ){
  2613   2712           do{
  2614         -          csv_read_one_field(&sCsv);
         2713  +          xRead(&sCtx);
  2615   2714             i++;
  2616         -        }while( sCsv.cTerm==sCsv.cSeparator );
         2715  +        }while( sCtx.cTerm==sCtx.cColSep );
  2617   2716           fprintf(stderr, "%s:%d: expected %d columns but found %d - "
  2618   2717                           "extras ignored\n",
  2619         -                        sCsv.zFile, startLine, nCol, i);
         2718  +                        sCtx.zFile, startLine, nCol, i);
  2620   2719         }
  2621   2720         if( i>=nCol ){
  2622   2721           sqlite3_step(pStmt);
  2623   2722           rc = sqlite3_reset(pStmt);
  2624   2723           if( rc!=SQLITE_OK ){
  2625         -          fprintf(stderr, "%s:%d: INSERT failed: %s\n", sCsv.zFile, startLine,
         2724  +          fprintf(stderr, "%s:%d: INSERT failed: %s\n", sCtx.zFile, startLine,
  2626   2725                     sqlite3_errmsg(db));
  2627   2726           }
  2628   2727         }
  2629         -    }while( sCsv.cTerm!=EOF );
         2728  +    }while( sCtx.cTerm!=EOF );
  2630   2729   
  2631         -    xCloser(sCsv.in);
  2632         -    sqlite3_free(sCsv.z);
         2730  +    xCloser(sCtx.in);
         2731  +    sqlite3_free(sCtx.z);
  2633   2732       sqlite3_finalize(pStmt);
  2634   2733       if( needCommit ) sqlite3_exec(db, "COMMIT", 0, 0, 0);
  2635   2734     }else
  2636   2735   
  2637   2736     if( c=='i' && strncmp(azArg[0], "indices", n)==0 ){
  2638   2737       struct callback_data data;
  2639   2738       char *zErrMsg = 0;
................................................................................
  2743   2842         p->mode = MODE_Column;
  2744   2843       }else if( c2=='l' && n2>2 && strncmp(azArg[1],"list",n2)==0 ){
  2745   2844         p->mode = MODE_List;
  2746   2845       }else if( c2=='h' && strncmp(azArg[1],"html",n2)==0 ){
  2747   2846         p->mode = MODE_Html;
  2748   2847       }else if( c2=='t' && strncmp(azArg[1],"tcl",n2)==0 ){
  2749   2848         p->mode = MODE_Tcl;
  2750         -      sqlite3_snprintf(sizeof(p->separator), p->separator, " ");
         2849  +      sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, " ");
         2850  +      sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_Line);
  2751   2851       }else if( c2=='c' && strncmp(azArg[1],"csv",n2)==0 ){
  2752   2852         p->mode = MODE_Csv;
  2753         -      sqlite3_snprintf(sizeof(p->separator), p->separator, ",");
         2853  +      sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, ",");
         2854  +      sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_Line);
  2754   2855       }else if( c2=='t' && strncmp(azArg[1],"tabs",n2)==0 ){
  2755   2856         p->mode = MODE_List;
  2756         -      sqlite3_snprintf(sizeof(p->separator), p->separator, "\t");
         2857  +      sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, "\t");
         2858  +      sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_Line);
  2757   2859       }else if( c2=='i' && strncmp(azArg[1],"insert",n2)==0 ){
  2758   2860         p->mode = MODE_Insert;
  2759   2861         set_table_name(p, nArg>=3 ? azArg[2] : "table");
         2862  +    }else if( c2=='a' && strncmp(azArg[1],"ascii",n2)==0 ){
         2863  +      p->mode = MODE_Ascii;
         2864  +      sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Column);
         2865  +      sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_Row);
  2760   2866       }else {
  2761   2867         fprintf(stderr,"Error: mode should be one of: "
  2762         -         "column csv html insert line list tabs tcl\n");
         2868  +         "ascii column csv html insert line list tabs tcl\n");
  2763   2869         rc = 1;
  2764   2870       }
  2765   2871     }else
  2766   2872   
  2767   2873     if( c=='n' && strncmp(azArg[0], "nullvalue", n)==0 ){
  2768   2874       if( nArg==2 ){
  2769   2875         sqlite3_snprintf(sizeof(p->nullvalue), p->nullvalue,
................................................................................
  3023   3129           v = integerValue(azArg[i]);
  3024   3130           sqlite3_snprintf(sizeof(zBuf),zBuf,"%s: %lld 0x%llx\n", azArg[i],v,v);
  3025   3131           fprintf(p->out, "%s", zBuf);
  3026   3132         }
  3027   3133       }
  3028   3134     }else
  3029   3135   #endif
         3136  +
         3137  +  if( c=='r' && strncmp(azArg[0], "rowseparator", n)==0 ){
         3138  +    if( nArg==2 ){
         3139  +      sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator,
         3140  +                       "%.*s", (int)sizeof(p->rowSeparator)-1, azArg[1]);
         3141  +    }else{
         3142  +      fprintf(stderr, "Usage: .rowseparator STRING\n");
         3143  +      rc = 1;
         3144  +    }
         3145  +  }else
         3146  +
         3147  +  if( c=='c' && strncmp(azArg[0], "colseparator", n)==0 ){
         3148  +    if( nArg==2 ){
         3149  +      sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator,
         3150  +                       "%.*s", (int)sizeof(p->colSeparator)-1, azArg[1]);
         3151  +    }else{
         3152  +      fprintf(stderr, "Usage: .colseparator STRING\n");
         3153  +      rc = 1;
         3154  +    }
         3155  +  }else
  3030   3156   
  3031   3157     if( c=='s' && strncmp(azArg[0], "separator", n)==0 ){
  3032   3158       if( nArg==2 ){
  3033         -      sqlite3_snprintf(sizeof(p->separator), p->separator,
  3034         -                       "%.*s", (int)sizeof(p->separator)-1, azArg[1]);
         3159  +      sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator,
         3160  +                       "%.*s", (int)sizeof(p->colSeparator)-1, azArg[1]);
  3035   3161       }else{
  3036   3162         fprintf(stderr, "Usage: .separator STRING\n");
  3037   3163         rc = 1;
  3038   3164       }
  3039   3165     }else
  3040   3166   
  3041   3167     if( c=='s'
................................................................................
  3060   3186     if( c=='s' && strncmp(azArg[0], "show", n)==0 ){
  3061   3187       int i;
  3062   3188       if( nArg!=1 ){
  3063   3189         fprintf(stderr, "Usage: .show\n");
  3064   3190         rc = 1;
  3065   3191         goto meta_command_exit;
  3066   3192       }
  3067         -    fprintf(p->out,"%9.9s: %s\n","echo", p->echoOn ? "on" : "off");
  3068         -    fprintf(p->out,"%9.9s: %s\n","eqp", p->autoEQP ? "on" : "off");
  3069         -    fprintf(p->out,"%9.9s: %s\n","explain", p->explainPrev.valid ? "on" :"off");
  3070         -    fprintf(p->out,"%9.9s: %s\n","headers", p->showHeader ? "on" : "off");
  3071         -    fprintf(p->out,"%9.9s: %s\n","mode", modeDescr[p->mode]);
  3072         -    fprintf(p->out,"%9.9s: ", "nullvalue");
         3193  +    fprintf(p->out,"%12.12s: %s\n","echo", p->echoOn ? "on" : "off");
         3194  +    fprintf(p->out,"%12.12s: %s\n","eqp", p->autoEQP ? "on" : "off");
         3195  +    fprintf(p->out,"%12.12s: %s\n","explain", p->explainPrev.valid ? "on" :"off");
         3196  +    fprintf(p->out,"%12.12s: %s\n","headers", p->showHeader ? "on" : "off");
         3197  +    fprintf(p->out,"%12.12s: %s\n","mode", modeDescr[p->mode]);
         3198  +    fprintf(p->out,"%12.12s: ", "nullvalue");
  3073   3199         output_c_string(p->out, p->nullvalue);
  3074   3200         fprintf(p->out, "\n");
  3075         -    fprintf(p->out,"%9.9s: %s\n","output",
         3201  +    fprintf(p->out,"%12.12s: %s\n","output",
  3076   3202               strlen30(p->outfile) ? p->outfile : "stdout");
  3077         -    fprintf(p->out,"%9.9s: ", "separator");
  3078         -      output_c_string(p->out, p->separator);
         3203  +    fprintf(p->out,"%12.12s: ", "colseparator");
         3204  +      output_c_string(p->out, p->colSeparator);
         3205  +      fprintf(p->out, "\n");
         3206  +    fprintf(p->out,"%12.12s: ", "rowseparator");
         3207  +      output_c_string(p->out, p->rowSeparator);
  3079   3208         fprintf(p->out, "\n");
  3080         -    fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off");
  3081         -    fprintf(p->out,"%9.9s: ","width");
         3209  +    fprintf(p->out,"%12.12s: %s\n","stats", p->statsOn ? "on" : "off");
         3210  +    fprintf(p->out,"%12.12s: ","width");
  3082   3211       for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) {
  3083   3212         fprintf(p->out,"%d ",p->colWidth[i]);
  3084   3213       }
  3085   3214       fprintf(p->out,"\n");
  3086   3215     }else
  3087   3216   
  3088   3217     if( c=='s' && strncmp(azArg[0], "stats", n)==0 ){
................................................................................
  3668   3797     return rc;
  3669   3798   }
  3670   3799   
  3671   3800   /*
  3672   3801   ** Show available command line options
  3673   3802   */
  3674   3803   static const char zOptions[] = 
         3804  +  "   -ascii               set output mode to 'ascii'\n"
  3675   3805     "   -bail                stop after hitting an error\n"
  3676   3806     "   -batch               force batch I/O\n"
         3807  +  "   -colseparator SEP    same as -separator\n"
  3677   3808     "   -column              set output mode to 'column'\n"
  3678   3809     "   -cmd COMMAND         run \"COMMAND\" before reading stdin\n"
  3679   3810     "   -csv                 set output mode to 'csv'\n"
  3680   3811     "   -echo                print commands before execution\n"
  3681   3812     "   -init FILENAME       read/process named file\n"
  3682   3813     "   -[no]header          turn headers on or off\n"
  3683   3814   #if defined(SQLITE_ENABLE_MEMSYS3) || defined(SQLITE_ENABLE_MEMSYS5)
................................................................................
  3689   3820     "   -line                set output mode to 'line'\n"
  3690   3821     "   -list                set output mode to 'list'\n"
  3691   3822     "   -mmap N              default mmap size set to N\n"
  3692   3823   #ifdef SQLITE_ENABLE_MULTIPLEX
  3693   3824     "   -multiplex           enable the multiplexor VFS\n"
  3694   3825   #endif
  3695   3826     "   -nullvalue TEXT      set text string for NULL values. Default ''\n"
         3827  +  "   -rowseparator SEP    set output line separator. Default: '\\n'\n"
  3696   3828     "   -separator SEP       set output field separator. Default: '|'\n"
  3697   3829     "   -stats               print memory stats before each finalize\n"
  3698   3830     "   -version             show SQLite version\n"
  3699   3831     "   -vfs NAME            use NAME as the default VFS\n"
  3700   3832   #ifdef SQLITE_ENABLE_VFSTRACE
  3701   3833     "   -vfstrace            enable tracing of all VFS calls\n"
  3702   3834   #endif
................................................................................
  3716   3848   
  3717   3849   /*
  3718   3850   ** Initialize the state information in data
  3719   3851   */
  3720   3852   static void main_init(struct callback_data *data) {
  3721   3853     memset(data, 0, sizeof(*data));
  3722   3854     data->mode = MODE_List;
  3723         -  memcpy(data->separator,"|", 2);
         3855  +  memcpy(data->colSeparator,"|", 2);
         3856  +  memcpy(data->rowSeparator,"\n", 2);
  3724   3857     data->showHeader = 0;
  3725   3858     sqlite3_config(SQLITE_CONFIG_URI, 1);
  3726   3859     sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data);
  3727   3860     sqlite3_snprintf(sizeof(mainPrompt), mainPrompt,"sqlite> ");
  3728   3861     sqlite3_snprintf(sizeof(continuePrompt), continuePrompt,"   ...> ");
  3729   3862     sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
  3730   3863   }
................................................................................
  3914   4047         data.mode = MODE_List;
  3915   4048       }else if( strcmp(z,"-line")==0 ){
  3916   4049         data.mode = MODE_Line;
  3917   4050       }else if( strcmp(z,"-column")==0 ){
  3918   4051         data.mode = MODE_Column;
  3919   4052       }else if( strcmp(z,"-csv")==0 ){
  3920   4053         data.mode = MODE_Csv;
  3921         -      memcpy(data.separator,",",2);
  3922         -    }else if( strcmp(z,"-separator")==0 ){
  3923         -      sqlite3_snprintf(sizeof(data.separator), data.separator,
         4054  +      memcpy(data.colSeparator,",",2);
         4055  +    }else if( strcmp(z,"-ascii")==0 ){
         4056  +      data.mode = MODE_Ascii;
         4057  +      sqlite3_snprintf(sizeof(data.colSeparator), data.colSeparator,
         4058  +                       SEP_Column);
         4059  +      sqlite3_snprintf(sizeof(data.rowSeparator), data.rowSeparator,
         4060  +                       SEP_Row);
         4061  +    }else if( strcmp(z,"-separator")==0 || strcmp(z,"-colseparator")==0 ){
         4062  +      sqlite3_snprintf(sizeof(data.colSeparator), data.colSeparator,
         4063  +                       "%s",cmdline_option_value(argc,argv,++i));
         4064  +    }else if( strcmp(z,"-rowseparator")==0 ){
         4065  +      sqlite3_snprintf(sizeof(data.rowSeparator), data.rowSeparator,
  3924   4066                          "%s",cmdline_option_value(argc,argv,++i));
  3925   4067       }else if( strcmp(z,"-nullvalue")==0 ){
  3926   4068         sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue,
  3927   4069                          "%s",cmdline_option_value(argc,argv,++i));
  3928   4070       }else if( strcmp(z,"-header")==0 ){
  3929   4071         data.showHeader = 1;
  3930   4072       }else if( strcmp(z,"-noheader")==0 ){

Changes to test/shell1.test.

   202    202     catchcmd "test.db" ".explain \"OFF"
   203    203   } {0 {}}
   204    204   do_test shell1-2.2.4 {
   205    205     catchcmd "test.db" ".explain \'OFF"
   206    206   } {0 {}}
   207    207   do_test shell1-2.2.5 {
   208    208     catchcmd "test.db" ".mode \"insert FOO"
   209         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          209  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   210    210   do_test shell1-2.2.6 {
   211    211     catchcmd "test.db" ".mode \'insert FOO"
   212         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          212  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   213    213   
   214    214   # check multiple tokens, and quoted tokens
   215    215   do_test shell1-2.3.1 {
   216    216     catchcmd "test.db" ".explain 1"
   217    217   } {0 {}}
   218    218   do_test shell1-2.3.2 {
   219    219     catchcmd "test.db" ".explain on"
................................................................................
   233    233   do_test shell1-2.3.7 {
   234    234     catchcmd "test.db" ".\'explain\' \'OFF\'"
   235    235   } {0 {}}
   236    236   
   237    237   # check quoted args are unquoted
   238    238   do_test shell1-2.4.1 {
   239    239     catchcmd "test.db" ".mode FOO"
   240         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          240  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   241    241   do_test shell1-2.4.2 {
   242    242     catchcmd "test.db" ".mode csv"
   243    243   } {0 {}}
   244    244   do_test shell1-2.4.2 {
   245    245     catchcmd "test.db" ".mode \"csv\""
   246    246   } {0 {}}
   247    247   
................................................................................
   416    416   } {0 {}}
   417    417   do_test shell1-3.12.3 {
   418    418     # too many arguments
   419    419     catchcmd "test.db" ".indices FOO BAD"
   420    420   } {1 {Usage: .indices ?LIKE-PATTERN?}}
   421    421   
   422    422   # .mode MODE ?TABLE?     Set output mode where MODE is one of:
          423  +#                          ascii    Columns/rows delimited with 0x1F and 0x1E
   423    424   #                          csv      Comma-separated values
   424    425   #                          column   Left-aligned columns.  (See .width)
   425    426   #                          html     HTML <table> code
   426    427   #                          insert   SQL insert statements for TABLE
   427    428   #                          line     One value per line
   428    429   #                          list     Values delimited by .separator string
   429    430   #                          tabs     Tab-separated values
   430    431   #                          tcl      TCL list elements
   431    432   do_test shell1-3.13.1 {
   432    433     catchcmd "test.db" ".mode"
   433         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          434  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   434    435   do_test shell1-3.13.2 {
   435    436     catchcmd "test.db" ".mode FOO"
   436         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          437  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   437    438   do_test shell1-3.13.3 {
   438    439     catchcmd "test.db" ".mode csv"
   439    440   } {0 {}}
   440    441   do_test shell1-3.13.4 {
   441    442     catchcmd "test.db" ".mode column"
   442    443   } {0 {}}
   443    444   do_test shell1-3.13.5 {
................................................................................
   462    463     # extra arguments ignored
   463    464     catchcmd "test.db" ".mode tcl BAD"
   464    465   } {0 {}}
   465    466   
   466    467   # don't allow partial mode type matches
   467    468   do_test shell1-3.13.12 {
   468    469     catchcmd "test.db" ".mode l"
   469         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          470  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   470    471   do_test shell1-3.13.13 {
   471    472     catchcmd "test.db" ".mode li"
   472         -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}}
          473  +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}}
   473    474   do_test shell1-3.13.14 {
   474    475     catchcmd "test.db" ".mode lin"
   475    476   } {0 {}}
   476    477   
   477    478   # .nullvalue STRING      Print STRING in place of NULL values
   478    479   do_test shell1-3.14.1 {
   479    480     catchcmd "test.db" ".nullvalue"
................................................................................
   581    582     }
   582    583     catchcmd "test.db" ".schema"
   583    584   } {0 {CREATE TABLE t1(x);
   584    585   CREATE VIEW v2 AS SELECT x+1 AS y FROM t1;
   585    586   CREATE VIEW v1 AS SELECT y+1 FROM v2;}}
   586    587   db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;}
   587    588   
   588         -# .separator STRING      Change separator used by output mode and .import
          589  +# .separator STRING  Change column separator used by output and .import
   589    590   do_test shell1-3.22.1 {
   590    591     catchcmd "test.db" ".separator"
   591    592   } {1 {Usage: .separator STRING}}
   592    593   do_test shell1-3.22.2 {
   593    594     catchcmd "test.db" ".separator FOO"
   594    595   } {0 {}}
   595    596   do_test shell1-3.22.3 {
................................................................................
   602    603     set res [catchcmd "test.db" ".show"]
   603    604     list [regexp {echo:} $res] \
   604    605          [regexp {explain:} $res] \
   605    606          [regexp {headers:} $res] \
   606    607          [regexp {mode:} $res] \
   607    608          [regexp {nullvalue:} $res] \
   608    609          [regexp {output:} $res] \
   609         -       [regexp {separator:} $res] \
          610  +       [regexp {colseparator:} $res] \
          611  +       [regexp {rowseparator:} $res] \
   610    612          [regexp {stats:} $res] \
   611    613          [regexp {width:} $res]
   612         -} {1 1 1 1 1 1 1 1 1}
          614  +} {1 1 1 1 1 1 1 1 1 1}
   613    615   do_test shell1-3.23.2 {
   614    616     # too many arguments
   615    617     catchcmd "test.db" ".show BAD"
   616    618   } {1 {Usage: .show}}
   617    619   
   618    620   # .stats ON|OFF          Turn stats on or off
   619    621   do_test shell1-3.23b.1 {
................................................................................
   804    806   } {0 {"\""
   805    807   "["
   806    808   "]"
   807    809   "\\{"
   808    810   "\\}"
   809    811   ";"
   810    812   "$"} 7}
          813  +
          814  +# .colseparator STRING  Change column separator used by output and .import
          815  +do_test shell1-5.1.1 {
          816  +  catchcmd "test.db" ".colseparator"
          817  +} {1 {Usage: .colseparator STRING}}
          818  +do_test shell1-5.1.2 {
          819  +  catchcmd "test.db" ".colseparator FOO"
          820  +} {0 {}}
          821  +do_test shell1-5.1.3 {
          822  +  # too many arguments
          823  +  catchcmd "test.db" ".colseparator FOO BAD"
          824  +} {1 {Usage: .colseparator STRING}}
          825  +
          826  +# .rowseparator STRING  Change row separator used by output and .import
          827  +do_test shell1-6.1.1 {
          828  +  catchcmd "test.db" ".rowseparator"
          829  +} {1 {Usage: .rowseparator STRING}}
          830  +do_test shell1-6.1.2 {
          831  +  catchcmd "test.db" ".rowseparator FOO"
          832  +} {0 {}}
          833  +do_test shell1-6.1.3 {
          834  +  # too many arguments
          835  +  catchcmd "test.db" ".rowseparator FOO BAD"
          836  +} {1 {Usage: .rowseparator STRING}}
   811    837   
   812    838   finish_test

Changes to test/shell5.test.

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