Index: src/shell.c ================================================================== --- src/shell.c +++ src/shell.c @@ -469,15 +469,15 @@ int mode; /* An output mode setting */ int writableSchema; /* True if PRAGMA writable_schema=ON */ int showHeader; /* True to show column names in List or Column mode */ unsigned shellFlgs; /* Various flags */ char *zDestTable; /* Name of destination table when MODE_Insert */ - char separator[20]; /* Separator character for MODE_List */ - char newline[20]; /* Record separator in MODE_Csv */ + char colSeparator[20]; /* Column separator character for several modes */ + char rowSeparator[20]; /* Row separator character for MODE_Ascii */ int colWidth[100]; /* Requested width of each column when in column mode*/ int actualWidth[100]; /* Actual width of each column */ - char nullvalue[20]; /* The text to print when a NULL comes back from + char nullValue[20]; /* The text to print when a NULL comes back from ** the database */ SavedModeInfo normalMode;/* Holds the mode just before .explain ON */ char outfile[FILENAME_MAX]; /* Filename for *out */ const char *zDbFilename; /* name of the database file */ char *zFreeOnClose; /* Filename to free when closing */ @@ -506,10 +506,11 @@ #define MODE_Html 4 /* Generate an XHTML table */ #define MODE_Insert 5 /* Generate SQL "insert" statements */ #define MODE_Tcl 6 /* Generate ANSI-C or TCL quoted elements */ #define MODE_Csv 7 /* Quote strings, numbers are plain */ #define MODE_Explain 8 /* Like MODE_Column, but do not truncate data */ +#define MODE_Ascii 9 /* Use ASCII unit and record separators (0x1F/0x1E) */ static const char *modeDescr[] = { "line", "column", "list", @@ -517,12 +518,26 @@ "html", "insert", "tcl", "csv", "explain", + "ascii", }; +/* +** These are the column/row/line separators used by the various +** import/export modes. +*/ +#define SEP_Column "|" +#define SEP_Row "\n" +#define SEP_Tab "\t" +#define SEP_Space " " +#define SEP_Comma "," +#define SEP_CrLf "\r\n" +#define SEP_Unit "\x1F" +#define SEP_Record "\x1E" + /* ** Number of elements in an array */ #define ArraySize(X) (int)(sizeof(X)/sizeof(X[0])) @@ -675,26 +690,26 @@ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, }; /* -** Output a single term of CSV. Actually, p->separator is used for -** the separator, which may or may not be a comma. p->nullvalue is +** Output a single term of CSV. Actually, p->colSeparator is used for +** the separator, which may or may not be a comma. p->nullValue is ** the null value. Strings are quoted if necessary. The separator ** is only issued if bSep is true. */ static void output_csv(ShellState *p, const char *z, int bSep){ FILE *out = p->out; if( z==0 ){ - fprintf(out,"%s",p->nullvalue); + fprintf(out,"%s",p->nullValue); }else{ int i; - int nSep = strlen30(p->separator); + int nSep = strlen30(p->colSeparator); for(i=0; z[i]; i++){ if( needCsvQuote[((unsigned char*)z)[i]] - || (z[i]==p->separator[0] && - (nSep==1 || memcmp(z, p->separator, nSep)==0)) ){ + || (z[i]==p->colSeparator[0] && + (nSep==1 || memcmp(z, p->colSeparator, nSep)==0)) ){ i = 0; break; } } if( i==0 ){ @@ -707,11 +722,11 @@ }else{ fprintf(out, "%s", z); } } if( bSep ){ - fprintf(p->out, "%s", p->separator); + fprintf(p->out, "%s", p->colSeparator); } } #ifdef SIGINT /* @@ -745,14 +760,14 @@ if( azArg==0 ) break; for(i=0; iw ) w = len; } - if( p->cnt++>0 ) fprintf(p->out,"\n"); + if( p->cnt++>0 ) fprintf(p->out, "%s", p->rowSeparator); for(i=0; iout,"%*s = %s\n", w, azCol[i], - azArg[i] ? azArg[i] : p->nullvalue); + fprintf(p->out,"%*s = %s%s", w, azCol[i], + azArg[i] ? azArg[i] : p->nullValue, p->rowSeparator); } break; } case MODE_Explain: case MODE_Column: { @@ -765,21 +780,23 @@ w = 0; } if( w==0 ){ w = strlen30(azCol[i] ? azCol[i] : ""); if( w<10 ) w = 10; - n = strlen30(azArg && azArg[i] ? azArg[i] : p->nullvalue); + n = strlen30(azArg && azArg[i] ? azArg[i] : p->nullValue); if( wactualWidth) ){ p->actualWidth[i] = w; } if( p->showHeader ){ if( w<0 ){ - fprintf(p->out,"%*.*s%s",-w,-w,azCol[i], i==nArg-1 ? "\n": " "); + fprintf(p->out,"%*.*s%s",-w,-w,azCol[i], + i==nArg-1 ? p->rowSeparator : " "); }else{ - fprintf(p->out,"%-*.*s%s",w,w,azCol[i], i==nArg-1 ? "\n": " "); + fprintf(p->out,"%-*.*s%s",w,w,azCol[i], + i==nArg-1 ? p->rowSeparator : " "); } } } if( p->showHeader ){ for(i=0; iout,"%-*.*s%s",w,w,"-----------------------------------" "----------------------------------------------------------", - i==nArg-1 ? "\n": " "); + i==nArg-1 ? p->rowSeparator : " "); } } } if( azArg==0 ) break; for(i=0; iiIndent++; } if( w<0 ){ fprintf(p->out,"%*.*s%s",-w,-w, - azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": " "); + azArg[i] ? azArg[i] : p->nullValue, + i==nArg-1 ? p->rowSeparator : " "); }else{ fprintf(p->out,"%-*.*s%s",w,w, - azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": " "); + azArg[i] ? azArg[i] : p->nullValue, + i==nArg-1 ? p->rowSeparator : " "); } } break; } case MODE_Semi: case MODE_List: { if( p->cnt++==0 && p->showHeader ){ for(i=0; iout,"%s%s",azCol[i], i==nArg-1 ? "\n" : p->separator); + fprintf(p->out,"%s%s",azCol[i], + i==nArg-1 ? p->rowSeparator : p->colSeparator); } } if( azArg==0 ) break; for(i=0; inullvalue; + if( z==0 ) z = p->nullValue; fprintf(p->out, "%s", z); if( iout, "%s", p->separator); + fprintf(p->out, "%s", p->colSeparator); }else if( p->mode==MODE_Semi ){ - fprintf(p->out, ";\n"); + fprintf(p->out, ";%s", p->rowSeparator); }else{ - fprintf(p->out, "\n"); + fprintf(p->out, "%s", p->rowSeparator); } } break; } case MODE_Html: { @@ -857,30 +877,30 @@ } if( azArg==0 ) break; fprintf(p->out,""); for(i=0; iout,""); - output_html_string(p->out, azArg[i] ? azArg[i] : p->nullvalue); + output_html_string(p->out, azArg[i] ? azArg[i] : p->nullValue); fprintf(p->out,"\n"); } fprintf(p->out,"\n"); break; } case MODE_Tcl: { if( p->cnt++==0 && p->showHeader ){ for(i=0; iout,azCol[i] ? azCol[i] : ""); - if(iout, "%s", p->separator); + if(iout, "%s", p->colSeparator); } - fprintf(p->out,"\n"); + fprintf(p->out, "%s", p->rowSeparator); } if( azArg==0 ) break; for(i=0; iout, azArg[i] ? azArg[i] : p->nullvalue); - if(iout, "%s", p->separator); + output_c_string(p->out, azArg[i] ? azArg[i] : p->nullValue); + if(iout, "%s", p->colSeparator); } - fprintf(p->out,"\n"); + fprintf(p->out, "%s", p->rowSeparator); break; } case MODE_Csv: { #if defined(WIN32) || defined(_WIN32) fflush(p->out); @@ -888,17 +908,17 @@ #endif if( p->cnt++==0 && p->showHeader ){ for(i=0; iout,"%s",p->newline); + fprintf(p->out, "%s", p->rowSeparator); } if( nArg>0 ){ for(i=0; iout,"%s",p->newline); + fprintf(p->out, "%s", p->rowSeparator); } #if defined(WIN32) || defined(_WIN32) fflush(p->out); _setmode(_fileno(p->out), _O_TEXT); #endif @@ -930,10 +950,26 @@ output_quoted_string(p->out, azArg[i]); } } fprintf(p->out,");\n"); break; + } + case MODE_Ascii: { + if( p->cnt++==0 && p->showHeader ){ + for(i=0; i0 ) fprintf(p->out, "%s", p->colSeparator); + fprintf(p->out,"%s",azCol[i] ? azCol[i] : ""); + } + fprintf(p->out, "%s", p->rowSeparator); + } + if( azArg==0 ) break; + for(i=0; i0 ) fprintf(p->out, "%s", p->colSeparator); + fprintf(p->out,"%s",azArg[i] ? azArg[i] : p->nullValue); + } + fprintf(p->out, "%s", p->rowSeparator); + break; } } return 0; } @@ -1696,16 +1732,17 @@ #ifndef SQLITE_OMIT_LOAD_EXTENSION ".load FILE ?ENTRY? Load an extension library\n" #endif ".log FILE|off Turn logging on or off. FILE can be stderr/stdout\n" ".mode MODE ?TABLE? Set output mode where MODE is one of:\n" + " ascii Columns/rows delimited by 0x1F and 0x1E\n" " csv Comma-separated values\n" " column Left-aligned columns. (See .width)\n" " html HTML code\n" " insert SQL insert statements for TABLE\n" " line One value per line\n" - " list Values delimited by .separator string\n" + " list Values delimited by .separator strings\n" " tabs Tab-separated values\n" " tcl TCL list elements\n" ".nullvalue STRING Use STRING in place of NULL values\n" ".once FILENAME Output for the next SQL command only to FILENAME\n" ".open ?FILENAME? Close existing database and reopen FILENAME\n" @@ -1718,12 +1755,12 @@ ".save FILE Write in-memory database into FILE\n" ".scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off\n" ".schema ?TABLE? Show the CREATE statements\n" " If TABLE specified, only show tables matching\n" " LIKE pattern TABLE.\n" - ".separator STRING ?NL? Change separator used by output mode and .import\n" - " NL is the end-of-line mark for CSV\n" + ".separator COL ?ROW? Change the column separator and optionally the row\n" + " separator for both the output mode and .import\n" ".shell CMD ARGS... Run CMD ARGS... in a system shell\n" ".show Show the current values for various settings\n" ".stats on|off Turn stats on or off\n" ".system CMD ARGS... Run CMD ARGS... in a system shell\n" ".tables ?TABLE? List names of tables\n" @@ -2000,26 +2037,27 @@ static int nCall = 0; nCall++; } /* -** An object used to read a CSV file +** An object used to read a CSV and other files for import. */ -typedef struct CSVReader CSVReader; -struct CSVReader { +typedef struct ImportCtx ImportCtx; +struct ImportCtx { const char *zFile; /* Name of the input file */ FILE *in; /* Read the CSV text from this input stream */ char *z; /* Accumulated text for a field */ int n; /* Number of bytes in z */ int nAlloc; /* Space allocated for z[] */ int nLine; /* Current line number */ int cTerm; /* Character that terminated the most recent field */ - int cSeparator; /* The separator character. (Usually ",") */ + int cColSep; /* The column separator character. (Usually ",") */ + int cRowSep; /* The row separator character. (Usually "\n") */ }; /* Append a single byte to z[] */ -static void csv_append_char(CSVReader *p, int c){ +static void import_append_char(ImportCtx *p, int c){ if( p->n+1>=p->nAlloc ){ p->nAlloc += p->nAlloc + 100; p->z = sqlite3_realloc(p->z, p->nAlloc); if( p->z==0 ){ fprintf(stderr, "out of memory\n"); @@ -2033,41 +2071,44 @@ ** with the option of having a separator other than ",". ** ** + Input comes from p->in. ** + Store results in p->z of length p->n. Space to hold p->z comes ** from sqlite3_malloc(). -** + Use p->cSep as the separator. The default is ",". +** + Use p->cSep as the column separator. The default is ",". +** + Use p->rSep as the row separator. The default is "\n". ** + Keep track of the line number in p->nLine. ** + Store the character that terminates the field in p->cTerm. Store ** EOF on end-of-file. ** + Report syntax errors on stderr */ -static char *csv_read_one_field(CSVReader *p){ - int c, pc, ppc; - int cSep = p->cSeparator; +static char *csv_read_one_field(ImportCtx *p){ + int c; + int cSep = p->cColSep; + int rSep = p->cRowSep; p->n = 0; c = fgetc(p->in); if( c==EOF || seenInterrupt ){ p->cTerm = EOF; return 0; } if( c=='"' ){ + int pc, ppc; int startLine = p->nLine; int cQuote = c; pc = ppc = 0; while( 1 ){ c = fgetc(p->in); - if( c=='\n' ) p->nLine++; + if( c==rSep ) p->nLine++; if( c==cQuote ){ if( pc==cQuote ){ pc = 0; continue; } } if( (c==cSep && pc==cQuote) - || (c=='\n' && pc==cQuote) - || (c=='\n' && pc=='\r' && ppc==cQuote) + || (c==rSep && pc==cQuote) + || (c==rSep && pc=='\r' && ppc==cQuote) || (c==EOF && pc==cQuote) ){ do{ p->n--; }while( p->z[p->n]!=cQuote ); p->cTerm = c; break; @@ -2077,31 +2118,65 @@ p->zFile, p->nLine, cQuote); } if( c==EOF ){ fprintf(stderr, "%s:%d: unterminated %c-quoted field\n", p->zFile, startLine, cQuote); - p->cTerm = EOF; + p->cTerm = c; break; } - csv_append_char(p, c); + import_append_char(p, c); ppc = pc; pc = c; } }else{ - while( c!=EOF && c!=cSep && c!='\n' ){ - csv_append_char(p, c); + while( c!=EOF && c!=cSep && c!=rSep ){ + import_append_char(p, c); c = fgetc(p->in); } - if( c=='\n' ){ + if( c==rSep ){ p->nLine++; if( p->n>0 && p->z[p->n-1]=='\r' ) p->n--; } p->cTerm = c; } if( p->z ) p->z[p->n] = 0; return p->z; } + +/* Read a single field of ASCII delimited text. +** +** + Input comes from p->in. +** + Store results in p->z of length p->n. Space to hold p->z comes +** from sqlite3_malloc(). +** + Use p->cSep as the column separator. The default is "\x1F". +** + Use p->rSep as the row separator. The default is "\x1E". +** + Keep track of the row number in p->nLine. +** + Store the character that terminates the field in p->cTerm. Store +** EOF on end-of-file. +** + Report syntax errors on stderr +*/ +static char *ascii_read_one_field(ImportCtx *p){ + int c; + int cSep = p->cColSep; + int rSep = p->cRowSep; + p->n = 0; + c = fgetc(p->in); + if( c==EOF || seenInterrupt ){ + p->cTerm = EOF; + return 0; + } + while( c!=EOF && c!=cSep && c!=rSep ){ + import_append_char(p, c); + c = fgetc(p->in); + } + if( c==rSep ){ + p->nLine++; + } + p->cTerm = c; + if( p->z ) p->z[p->n] = 0; + return p->z; +} /* ** Try to transfer data for table zTable. If an error is seen while ** moving forward, try to go backwards. The backwards movement won't ** work for WITHOUT ROWID tables. @@ -2653,100 +2728,125 @@ sqlite3_stmt *pStmt = NULL; /* A statement */ int nCol; /* Number of columns in the table */ int nByte; /* Number of bytes in an SQL string */ int i, j; /* Loop counters */ int needCommit; /* True to COMMIT or ROLLBACK at end */ - int nSep; /* Number of bytes in p->separator[] */ + int nSep; /* Number of bytes in p->colSeparator[] */ char *zSql; /* An SQL statement */ - CSVReader sCsv; /* Reader context */ + ImportCtx sCtx; /* Reader context */ + char *(*xRead)(ImportCtx*); /* Procedure to read one value */ int (*xCloser)(FILE*); /* Procedure to close th3 connection */ if( nArg!=3 ){ fprintf(stderr, "Usage: .import FILE TABLE\n"); goto meta_command_exit; } zFile = azArg[1]; zTable = azArg[2]; seenInterrupt = 0; - memset(&sCsv, 0, sizeof(sCsv)); + memset(&sCtx, 0, sizeof(sCtx)); open_db(p, 0); - nSep = strlen30(p->separator); + nSep = strlen30(p->colSeparator); + if( nSep==0 ){ + fprintf(stderr, "Error: non-null column separator required for import\n"); + return 1; + } + if( nSep>1 ){ + fprintf(stderr, "Error: multi-character column separators not allowed" + " for import\n"); + return 1; + } + nSep = strlen30(p->rowSeparator); if( nSep==0 ){ - fprintf(stderr, "Error: non-null separator required for import\n"); + fprintf(stderr, "Error: non-null row separator required for import\n"); return 1; + } + if( nSep==2 && p->mode==MODE_Csv && strcmp(p->rowSeparator, SEP_CrLf)==0 ){ + /* When importing CSV (only), if the row separator is set to the + ** default output row separator, change it to the default input + ** row separator. This avoids having to maintain different input + ** and output row separators. */ + sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_Row); + nSep = strlen30(p->rowSeparator); } if( nSep>1 ){ - fprintf(stderr, "Error: multi-character separators not allowed" + fprintf(stderr, "Error: multi-character row separators not allowed" " for import\n"); return 1; } - sCsv.zFile = zFile; - sCsv.nLine = 1; - if( sCsv.zFile[0]=='|' ){ - sCsv.in = popen(sCsv.zFile+1, "r"); - sCsv.zFile = ""; + sCtx.zFile = zFile; + sCtx.nLine = 1; + if( sCtx.zFile[0]=='|' ){ + sCtx.in = popen(sCtx.zFile+1, "r"); + sCtx.zFile = ""; xCloser = pclose; }else{ - sCsv.in = fopen(sCsv.zFile, "rb"); + sCtx.in = fopen(sCtx.zFile, "rb"); xCloser = fclose; } - if( sCsv.in==0 ){ + if( p->mode==MODE_Ascii ){ + xRead = ascii_read_one_field; + }else{ + xRead = csv_read_one_field; + } + if( sCtx.in==0 ){ fprintf(stderr, "Error: cannot open \"%s\"\n", zFile); return 1; } - sCsv.cSeparator = p->separator[0]; + sCtx.cColSep = p->colSeparator[0]; + sCtx.cRowSep = p->rowSeparator[0]; zSql = sqlite3_mprintf("SELECT * FROM %s", zTable); if( zSql==0 ){ fprintf(stderr, "Error: out of memory\n"); - xCloser(sCsv.in); + xCloser(sCtx.in); return 1; } nByte = strlen30(zSql); rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0); - csv_append_char(&sCsv, 0); /* To ensure sCsv.z is allocated */ + import_append_char(&sCtx, 0); /* To ensure sCtx.z is allocated */ if( rc && sqlite3_strglob("no such table: *", sqlite3_errmsg(db))==0 ){ char *zCreate = sqlite3_mprintf("CREATE TABLE %s", zTable); char cSep = '('; - while( csv_read_one_field(&sCsv) ){ - zCreate = sqlite3_mprintf("%z%c\n \"%s\" TEXT", zCreate, cSep, sCsv.z); + while( xRead(&sCtx) ){ + zCreate = sqlite3_mprintf("%z%c\n \"%s\" TEXT", zCreate, cSep, sCtx.z); cSep = ','; - if( sCsv.cTerm!=sCsv.cSeparator ) break; + if( sCtx.cTerm!=sCtx.cColSep ) break; } if( cSep=='(' ){ sqlite3_free(zCreate); - sqlite3_free(sCsv.z); - xCloser(sCsv.in); - fprintf(stderr,"%s: empty file\n", sCsv.zFile); + sqlite3_free(sCtx.z); + xCloser(sCtx.in); + fprintf(stderr,"%s: empty file\n", sCtx.zFile); return 1; } zCreate = sqlite3_mprintf("%z\n)", zCreate); rc = sqlite3_exec(p->db, zCreate, 0, 0, 0); sqlite3_free(zCreate); if( rc ){ fprintf(stderr, "CREATE TABLE %s(...) failed: %s\n", zTable, sqlite3_errmsg(db)); - sqlite3_free(sCsv.z); - xCloser(sCsv.in); + sqlite3_free(sCtx.z); + xCloser(sCtx.in); return 1; } rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0); } sqlite3_free(zSql); if( rc ){ if (pStmt) sqlite3_finalize(pStmt); fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db)); - xCloser(sCsv.in); + xCloser(sCtx.in); return 1; } nCol = sqlite3_column_count(pStmt); sqlite3_finalize(pStmt); pStmt = 0; if( nCol==0 ) return 0; /* no columns, no error */ zSql = sqlite3_malloc( nByte*2 + 20 + nCol*2 ); if( zSql==0 ){ fprintf(stderr, "Error: out of memory\n"); - xCloser(sCsv.in); + xCloser(sCtx.in); return 1; } sqlite3_snprintf(nByte+20, zSql, "INSERT INTO \"%w\" VALUES(?", zTable); j = strlen30(zSql); for(i=1; idb, zSql, -1, &pStmt, 0); sqlite3_free(zSql); if( rc ){ fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db)); if (pStmt) sqlite3_finalize(pStmt); - xCloser(sCsv.in); + xCloser(sCtx.in); return 1; } needCommit = sqlite3_get_autocommit(db); if( needCommit ) sqlite3_exec(db, "BEGIN", 0, 0, 0); do{ - int startLine = sCsv.nLine; + int startLine = sCtx.nLine; for(i=0; imode==MODE_Ascii && (z==0 || z[0]==0) && i==0 ) break; sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT); - if( i=nCol ){ sqlite3_step(pStmt); rc = sqlite3_reset(pStmt); if( rc!=SQLITE_OK ){ - fprintf(stderr, "%s:%d: INSERT failed: %s\n", sCsv.zFile, startLine, + fprintf(stderr, "%s:%d: INSERT failed: %s\n", sCtx.zFile, startLine, sqlite3_errmsg(db)); } } - }while( sCsv.cTerm!=EOF ); + }while( sCtx.cTerm!=EOF ); - xCloser(sCsv.in); - sqlite3_free(sCsv.z); + xCloser(sCtx.in); + sqlite3_free(sCtx.z); sqlite3_finalize(pStmt); if( needCommit ) sqlite3_exec(db, "COMMIT", 0, 0, 0); }else if( c=='i' && strncmp(azArg[0], "indices", n)==0 ){ @@ -2915,32 +3025,36 @@ p->mode = MODE_List; }else if( c2=='h' && strncmp(azArg[1],"html",n2)==0 ){ p->mode = MODE_Html; }else if( c2=='t' && strncmp(azArg[1],"tcl",n2)==0 ){ p->mode = MODE_Tcl; - sqlite3_snprintf(sizeof(p->separator), p->separator, " "); + sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Space); }else if( c2=='c' && strncmp(azArg[1],"csv",n2)==0 ){ p->mode = MODE_Csv; - sqlite3_snprintf(sizeof(p->separator), p->separator, ","); - sqlite3_snprintf(sizeof(p->newline), p->newline, "\r\n"); + sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Comma); + sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_CrLf); }else if( c2=='t' && strncmp(azArg[1],"tabs",n2)==0 ){ p->mode = MODE_List; - sqlite3_snprintf(sizeof(p->separator), p->separator, "\t"); + sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Tab); }else if( c2=='i' && strncmp(azArg[1],"insert",n2)==0 ){ p->mode = MODE_Insert; set_table_name(p, nArg>=3 ? azArg[2] : "table"); + }else if( c2=='a' && strncmp(azArg[1],"ascii",n2)==0 ){ + p->mode = MODE_Ascii; + sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, SEP_Unit); + sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, SEP_Record); }else { fprintf(stderr,"Error: mode should be one of: " - "column csv html insert line list tabs tcl\n"); + "ascii column csv html insert line list tabs tcl\n"); rc = 1; } }else if( c=='n' && strncmp(azArg[0], "nullvalue", n)==0 ){ if( nArg==2 ){ - sqlite3_snprintf(sizeof(p->nullvalue), p->nullvalue, - "%.*s", (int)ArraySize(p->nullvalue)-1, azArg[1]); + sqlite3_snprintf(sizeof(p->nullValue), p->nullValue, + "%.*s", (int)ArraySize(p->nullValue)-1, azArg[1]); }else{ fprintf(stderr, "Usage: .nullvalue STRING\n"); rc = 1; } }else @@ -3221,18 +3335,20 @@ }else #endif if( c=='s' && strncmp(azArg[0], "separator", n)==0 ){ if( nArg<2 || nArg>3 ){ - fprintf(stderr, "Usage: .separator SEPARATOR ?NEWLINE?\n"); + fprintf(stderr, "Usage: .separator COL ?ROW?\n"); rc = 1; } if( nArg>=2 ){ - sqlite3_snprintf(sizeof(p->separator), p->separator, azArg[1]); + sqlite3_snprintf(sizeof(p->colSeparator), p->colSeparator, + "%.*s", (int)ArraySize(p->colSeparator)-1, azArg[1]); } if( nArg>=3 ){ - sqlite3_snprintf(sizeof(p->newline), p->newline, azArg[2]); + sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, + "%.*s", (int)ArraySize(p->rowSeparator)-1, azArg[2]); } }else if( c=='s' && (strncmp(azArg[0], "shell", n)==0 || strncmp(azArg[0],"system",n)==0) @@ -3259,27 +3375,28 @@ if( nArg!=1 ){ fprintf(stderr, "Usage: .show\n"); rc = 1; goto meta_command_exit; } - fprintf(p->out,"%9.9s: %s\n","echo", p->echoOn ? "on" : "off"); - fprintf(p->out,"%9.9s: %s\n","eqp", p->autoEQP ? "on" : "off"); + fprintf(p->out,"%12.12s: %s\n","echo", p->echoOn ? "on" : "off"); + fprintf(p->out,"%12.12s: %s\n","eqp", p->autoEQP ? "on" : "off"); fprintf(p->out,"%9.9s: %s\n","explain", p->normalMode.valid ? "on" :"off"); - fprintf(p->out,"%9.9s: %s\n","headers", p->showHeader ? "on" : "off"); - fprintf(p->out,"%9.9s: %s\n","mode", modeDescr[p->mode]); - fprintf(p->out,"%9.9s: ", "nullvalue"); - output_c_string(p->out, p->nullvalue); + fprintf(p->out,"%12.12s: %s\n","headers", p->showHeader ? "on" : "off"); + fprintf(p->out,"%12.12s: %s\n","mode", modeDescr[p->mode]); + fprintf(p->out,"%12.12s: ", "nullvalue"); + output_c_string(p->out, p->nullValue); fprintf(p->out, "\n"); - fprintf(p->out,"%9.9s: %s\n","output", + fprintf(p->out,"%12.12s: %s\n","output", strlen30(p->outfile) ? p->outfile : "stdout"); - fprintf(p->out,"%9.9s: ", "separator"); - output_c_string(p->out, p->separator); - fprintf(p->out," "); - output_c_string(p->out, p->newline); + fprintf(p->out,"%12.12s: ", "colseparator"); + output_c_string(p->out, p->colSeparator); + fprintf(p->out, "\n"); + fprintf(p->out,"%12.12s: ", "rowseparator"); + output_c_string(p->out, p->rowSeparator); fprintf(p->out, "\n"); - fprintf(p->out,"%9.9s: %s\n","stats", p->statsOn ? "on" : "off"); - fprintf(p->out,"%9.9s: ","width"); + fprintf(p->out,"%12.12s: %s\n","stats", p->statsOn ? "on" : "off"); + fprintf(p->out,"%12.12s: ","width"); for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) { fprintf(p->out,"%d ",p->colWidth[i]); } fprintf(p->out,"\n"); }else @@ -3936,10 +4053,11 @@ /* ** Show available command line options */ static const char zOptions[] = + " -ascii set output mode to 'ascii'\n" " -bail stop after hitting an error\n" " -batch force batch I/O\n" " -column set output mode to 'column'\n" " -cmd COMMAND run \"COMMAND\" before reading stdin\n" " -csv set output mode to 'csv'\n" @@ -3957,15 +4075,15 @@ " -lookaside SIZE N use N entries of SZ bytes for lookaside memory\n" " -mmap N default mmap size set to N\n" #ifdef SQLITE_ENABLE_MULTIPLEX " -multiplex enable the multiplexor VFS\n" #endif - " -newline SEP set newline character(s) for CSV\n" + " -newline SEP set output row separator. Default: '\\n'\n" " -nullvalue TEXT set text string for NULL values. Default ''\n" " -pagecache SIZE N use N slots of SZ bytes each for page cache memory\n" " -scratch SIZE N use N slots of SZ bytes each for scratch memory\n" - " -separator SEP set output field separator. Default: '|'\n" + " -separator SEP set output column separator. Default: '|'\n" " -stats print memory stats before each finalize\n" " -version show SQLite version\n" " -vfs NAME use NAME as the default VFS\n" #ifdef SQLITE_ENABLE_VFSTRACE " -vfstrace enable tracing of all VFS calls\n" @@ -3988,12 +4106,12 @@ ** Initialize the state information in data */ static void main_init(ShellState *data) { memset(data, 0, sizeof(*data)); data->mode = MODE_List; - memcpy(data->separator,"|", 2); - memcpy(data->newline,"\r\n", 3); + memcpy(data->colSeparator,SEP_Column, 2); + memcpy(data->rowSeparator,SEP_Row, 2); data->showHeader = 0; data->shellFlgs = SHFLG_Lookaside; sqlite3_config(SQLITE_CONFIG_URI, 1); sqlite3_config(SQLITE_CONFIG_LOG, shellLog, data); sqlite3_config(SQLITE_CONFIG_MULTITHREAD); @@ -4228,19 +4346,25 @@ data.mode = MODE_Line; }else if( strcmp(z,"-column")==0 ){ data.mode = MODE_Column; }else if( strcmp(z,"-csv")==0 ){ data.mode = MODE_Csv; - memcpy(data.separator,",",2); + memcpy(data.colSeparator,",",2); + }else if( strcmp(z,"-ascii")==0 ){ + data.mode = MODE_Ascii; + sqlite3_snprintf(sizeof(data.colSeparator), data.colSeparator, + SEP_Unit); + sqlite3_snprintf(sizeof(data.rowSeparator), data.rowSeparator, + SEP_Record); }else if( strcmp(z,"-separator")==0 ){ - sqlite3_snprintf(sizeof(data.separator), data.separator, + sqlite3_snprintf(sizeof(data.colSeparator), data.colSeparator, "%s",cmdline_option_value(argc,argv,++i)); }else if( strcmp(z,"-newline")==0 ){ - sqlite3_snprintf(sizeof(data.newline), data.newline, + sqlite3_snprintf(sizeof(data.rowSeparator), data.rowSeparator, "%s",cmdline_option_value(argc,argv,++i)); }else if( strcmp(z,"-nullvalue")==0 ){ - sqlite3_snprintf(sizeof(data.nullvalue), data.nullvalue, + sqlite3_snprintf(sizeof(data.nullValue), data.nullValue, "%s",cmdline_option_value(argc,argv,++i)); }else if( strcmp(z,"-header")==0 ){ data.showHeader = 1; }else if( strcmp(z,"-noheader")==0 ){ data.showHeader = 0; Index: test/shell1.test ================================================================== --- test/shell1.test +++ test/shell1.test @@ -205,14 +205,14 @@ do_test shell1-2.2.4 { catchcmd "test.db" ".explain \'OFF" } {0 {}} do_test shell1-2.2.5 { catchcmd "test.db" ".mode \"insert FOO" -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}} do_test shell1-2.2.6 { catchcmd "test.db" ".mode \'insert FOO" -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}} # check multiple tokens, and quoted tokens do_test shell1-2.3.1 { catchcmd "test.db" ".explain 1" } {0 {}} @@ -236,11 +236,11 @@ } {0 {}} # check quoted args are unquoted do_test shell1-2.4.1 { catchcmd "test.db" ".mode FOO" -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}} do_test shell1-2.4.2 { catchcmd "test.db" ".mode csv" } {0 {}} do_test shell1-2.4.2 { catchcmd "test.db" ".mode \"csv\"" @@ -419,24 +419,25 @@ # too many arguments catchcmd "test.db" ".indices FOO BAD" } {1 {Usage: .indices ?LIKE-PATTERN?}} # .mode MODE ?TABLE? Set output mode where MODE is one of: +# ascii Columns/rows delimited by 0x1F and 0x1E # csv Comma-separated values # column Left-aligned columns. (See .width) # html HTML
code # insert SQL insert statements for TABLE # line One value per line -# list Values delimited by .separator string +# list Values delimited by .separator strings # tabs Tab-separated values # tcl TCL list elements do_test shell1-3.13.1 { catchcmd "test.db" ".mode" -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}} do_test shell1-3.13.2 { catchcmd "test.db" ".mode FOO" -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}} do_test shell1-3.13.3 { catchcmd "test.db" ".mode csv" } {0 {}} do_test shell1-3.13.4 { catchcmd "test.db" ".mode column" @@ -465,14 +466,14 @@ } {0 {}} # don't allow partial mode type matches do_test shell1-3.13.12 { catchcmd "test.db" ".mode l" -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}} do_test shell1-3.13.13 { catchcmd "test.db" ".mode li" -} {1 {Error: mode should be one of: column csv html insert line list tabs tcl}} +} {1 {Error: mode should be one of: ascii column csv html insert line list tabs tcl}} do_test shell1-3.13.14 { catchcmd "test.db" ".mode lin" } {0 {}} # .nullvalue STRING Print STRING in place of NULL values @@ -584,24 +585,24 @@ } {0 {CREATE TABLE t1(x); CREATE VIEW v2 AS SELECT x+1 AS y FROM t1; CREATE VIEW v1 AS SELECT y+1 FROM v2;}} db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;} -# .separator STRING Change separator used by output mode and .import +# .separator STRING Change column separator used by output and .import do_test shell1-3.22.1 { catchcmd "test.db" ".separator" -} {1 {Usage: .separator SEPARATOR ?NEWLINE?}} +} {1 {Usage: .separator COL ?ROW?}} do_test shell1-3.22.2 { catchcmd "test.db" ".separator FOO" } {0 {}} do_test shell1-3.22.3 { catchcmd "test.db" ".separator ABC XYZ" } {0 {}} do_test shell1-3.22.4 { # too many arguments catchcmd "test.db" ".separator FOO BAD BAD2" -} {1 {Usage: .separator SEPARATOR ?NEWLINE?}} +} {1 {Usage: .separator COL ?ROW?}} # .show Show the current values for various settings do_test shell1-3.23.1 { set res [catchcmd "test.db" ".show"] list [regexp {echo:} $res] \ @@ -608,14 +609,15 @@ [regexp {explain:} $res] \ [regexp {headers:} $res] \ [regexp {mode:} $res] \ [regexp {nullvalue:} $res] \ [regexp {output:} $res] \ - [regexp {separator:} $res] \ + [regexp {colseparator:} $res] \ + [regexp {rowseparator:} $res] \ [regexp {stats:} $res] \ [regexp {width:} $res] -} {1 1 1 1 1 1 1 1 1} +} {1 1 1 1 1 1 1 1 1 1} do_test shell1-3.23.2 { # too many arguments catchcmd "test.db" ".show BAD" } {1 {Usage: .show}} Index: test/shell5.test ================================================================== --- test/shell5.test +++ test/shell5.test @@ -53,26 +53,32 @@ } {1 {Usage: .import FILE TABLE}} # .separator STRING Change separator used by output mode and .import do_test shell5-1.2.1 { catchcmd "test.db" ".separator" -} {1 {Usage: .separator SEPARATOR ?NEWLINE?}} +} {1 {Usage: .separator COL ?ROW?}} do_test shell5-1.2.2 { catchcmd "test.db" ".separator ONE" } {0 {}} do_test shell5-1.2.3 { catchcmd "test.db" ".separator ONE TWO" } {0 {}} do_test shell5-1.2.4 { # too many arguments catchcmd "test.db" ".separator ONE TWO THREE" -} {1 {Usage: .separator SEPARATOR ?NEWLINE?}} +} {1 {Usage: .separator COL ?ROW?}} + +# column separator should default to "|" +do_test shell5-1.3.1.1 { + set res [catchcmd "test.db" ".show"] + list [regexp {colseparator: \"\|\"} $res] +} {1} -# separator should default to "|" -do_test shell5-1.3.1 { +# row separator should default to "\n" +do_test shell5-1.3.1.2 { set res [catchcmd "test.db" ".show"] - list [regexp {separator: \"\|\"} $res] + list [regexp {rowseparator: \"\\n\"} $res] } {1} # set separator to different value. # check that .show reports new value do_test shell5-1.3.2 { @@ -370,7 +376,34 @@ .import shell5.csv t4 }] db eval { SELECT * FROM t4 } } {xy\" hello one 2 {} {}} +#---------------------------------------------------------------------------- +# Tests for the shell "ascii" import/export mode. +# +do_test shell5-3.1 { + set fd [open shell5.csv w] + fconfigure $fd -encoding binary -translation binary + puts -nonewline $fd "\"test 1\"\x1F,test 2\r\n\x1E" + puts -nonewline $fd "test 3\x1Ftest 4\n" + close $fd + catchcmd test.db { +.mode ascii +CREATE TABLE t5(a, b); +.import shell5.csv t5 + } + db eval { SELECT * FROM t5 } +} "\{\"test 1\"} \{,test 2\r\n\} \{test 3\} \{test 4\n\}" + +# +# NOTE: This test ends up converting the "\r\n" to "\n\n" due +# to end-of-line translation on the "stdout" channel. +# +do_test shell5-3.2 { + catchcmd test.db { +.mode ascii +SELECT * FROM t5; + } +} "0 \{\"test 1\"\x1F,test 2\n\n\x1Etest 3\x1Ftest 4\n\x1E\}" finish_test