/ Check-in [93aa17d8]
Login

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

Overview
Comment:Enhance the command-lin shell to interpret CSV files as described by the wikipedia article on CSV.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 93aa17d866873e11dde5ffbefe74497f229977c1
User & Date: drh 2012-01-16 16:56:31
Context
2012-01-17
15:29
Remove the undocumented PRAGMA omit_readlock hack. check-in: 96900c47 user: drh tags: trunk
2012-01-16
16:56
Enhance the command-lin shell to interpret CSV files as described by the wikipedia article on CSV. check-in: 93aa17d8 user: drh tags: trunk
16:39
Test the fts4 content and prefix options together. check-in: de11cd5c user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/shell.c.

53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
...
330
331
332
333
334
335
336
337
338
339
340

341
342
343
344
345
346
347
...
357
358
359
360
361
362
363
364



365
366
367
368
369
370
371
372
...
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
...
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
....
1765
1766
1767
1768
1769
1770
1771
1772
1773

1774
1775
1776


1777
1778
1779
1780
1781
1782
1783
1784
....
1790
1791
1792
1793
1794
1795
1796








1797
1798
1799
1800
1801
1802
1803
# include <editline/editline.h>
#endif
#if defined(HAVE_READLINE) && HAVE_READLINE==1
# include <readline/readline.h>
# include <readline/history.h>
#endif
#if !defined(HAVE_EDITLINE) && (!defined(HAVE_READLINE) || HAVE_READLINE!=1)
# define readline(p) local_getline(p,stdin)
# define add_history(X)
# define read_history(X)
# define write_history(X)
# define stifle_history(X)
#endif

#if defined(_WIN32) || defined(WIN32)
................................................................................
** the text in memory obtained from malloc() and returns a pointer
** to the text.  NULL is returned at end of file, or if malloc()
** fails.
**
** The interface is like "readline" but no command-line editing
** is done.
*/
static char *local_getline(char *zPrompt, FILE *in){
  char *zLine;
  int nLine;
  int n;


  if( zPrompt && *zPrompt ){
    printf("%s",zPrompt);
    fflush(stdout);
  }
  nLine = 100;
  zLine = malloc( nLine );
................................................................................
      if( n==0 ){
        free(zLine);
        return 0;
      }
      zLine[n] = 0;
      break;
    }
    while( zLine[n] ){ n++; }



    if( n>0 && zLine[n-1]=='\n' ){
      n--;
      if( n>0 && zLine[n-1]=='\r' ) n--;
      zLine[n] = 0;
      break;
    }
  }
  zLine = realloc( zLine, n+1 );
................................................................................
** zPrior is a string of prior text retrieved.  If not the empty
** string, then issue a continuation prompt.
*/
static char *one_input_line(const char *zPrior, FILE *in){
  char *zPrompt;
  char *zResult;
  if( in!=0 ){
    return local_getline(0, in);
  }
  if( zPrior && zPrior[0] ){
    zPrompt = continuePrompt;
  }else{
    zPrompt = mainPrompt;
  }
  zResult = readline(zPrompt);
................................................................................
  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   
  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   
};

/*
** Output a single term of CSV.  Actually, p->separator is used for
** the separator, which may or may not be a comma.  p->nullvalue is
** the null value.  Strings are quoted using ANSI-C rules.  Numbers
** appear outside of quotes.
*/
static void output_csv(struct callback_data *p, const char *z, int bSep){
  FILE *out = p->out;
  if( z==0 ){
    fprintf(out,"%s",p->nullvalue);
  }else{
    int i;
................................................................................
      fprintf(stderr, "Error: out of memory\n");
      fclose(in);
      sqlite3_finalize(pStmt);
      return 1;
    }
    sqlite3_exec(p->db, "BEGIN", 0, 0, 0);
    zCommit = "COMMIT";
    while( (zLine = local_getline(0, in))!=0 ){
      char *z;

      lineno++;
      azCol[0] = zLine;
      for(i=0, z=zLine; *z && *z!='\n' && *z!='\r'; z++){


        if( *z==p->separator[0] && strncmp(z, p->separator, nSep)==0 ){
          *z = 0;
          i++;
          if( i<nCol ){
            azCol[i] = &z[nSep];
            z += nSep-1;
          }
        }
................................................................................
                zFile, lineno, nCol, i+1);
        zCommit = "ROLLBACK";
        free(zLine);
        rc = 1;
        break; /* from while */
      }
      for(i=0; i<nCol; i++){








        sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC);
      }
      sqlite3_step(pStmt);
      rc = sqlite3_reset(pStmt);
      free(zLine);
      if( rc!=SQLITE_OK ){
        fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));







|







 







|



>







 







|
>
>
>
|







 







|







 







|
<







 







|
|
>


|
>
>
|







 







>
>
>
>
>
>
>
>







53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
...
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
...
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
...
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
...
614
615
616
617
618
619
620
621

622
623
624
625
626
627
628
....
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
....
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
# include <editline/editline.h>
#endif
#if defined(HAVE_READLINE) && HAVE_READLINE==1
# include <readline/readline.h>
# include <readline/history.h>
#endif
#if !defined(HAVE_EDITLINE) && (!defined(HAVE_READLINE) || HAVE_READLINE!=1)
# define readline(p) local_getline(p,stdin,0)
# define add_history(X)
# define read_history(X)
# define write_history(X)
# define stifle_history(X)
#endif

#if defined(_WIN32) || defined(WIN32)
................................................................................
** the text in memory obtained from malloc() and returns a pointer
** to the text.  NULL is returned at end of file, or if malloc()
** fails.
**
** The interface is like "readline" but no command-line editing
** is done.
*/
static char *local_getline(char *zPrompt, FILE *in, int csvFlag){
  char *zLine;
  int nLine;
  int n;
  int inQuote = 0;

  if( zPrompt && *zPrompt ){
    printf("%s",zPrompt);
    fflush(stdout);
  }
  nLine = 100;
  zLine = malloc( nLine );
................................................................................
      if( n==0 ){
        free(zLine);
        return 0;
      }
      zLine[n] = 0;
      break;
    }
    while( zLine[n] ){
      if( zLine[n]=='"' ) inQuote = !inQuote;
      n++;
    }
    if( n>0 && zLine[n-1]=='\n' && (!inQuote || !csvFlag) ){
      n--;
      if( n>0 && zLine[n-1]=='\r' ) n--;
      zLine[n] = 0;
      break;
    }
  }
  zLine = realloc( zLine, n+1 );
................................................................................
** zPrior is a string of prior text retrieved.  If not the empty
** string, then issue a continuation prompt.
*/
static char *one_input_line(const char *zPrior, FILE *in){
  char *zPrompt;
  char *zResult;
  if( in!=0 ){
    return local_getline(0, in, 0);
  }
  if( zPrior && zPrior[0] ){
    zPrompt = continuePrompt;
  }else{
    zPrompt = mainPrompt;
  }
  zResult = readline(zPrompt);
................................................................................
  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   
  1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,   
};

/*
** Output a single term of CSV.  Actually, p->separator is used for
** the separator, which may or may not be a comma.  p->nullvalue is
** the null value.  Strings are quoted if necessary.

*/
static void output_csv(struct callback_data *p, const char *z, int bSep){
  FILE *out = p->out;
  if( z==0 ){
    fprintf(out,"%s",p->nullvalue);
  }else{
    int i;
................................................................................
      fprintf(stderr, "Error: out of memory\n");
      fclose(in);
      sqlite3_finalize(pStmt);
      return 1;
    }
    sqlite3_exec(p->db, "BEGIN", 0, 0, 0);
    zCommit = "COMMIT";
    while( (zLine = local_getline(0, in, 1))!=0 ){
      char *z, c;
      int inQuote = 0;
      lineno++;
      azCol[0] = zLine;
      for(i=0, z=zLine; (c = *z)!=0; z++){
        if( c=='"' ) inQuote = !inQuote;
        if( c=='\n' ) lineno++;
        if( !inQuote && c==p->separator[0] && strncmp(z,p->separator,nSep)==0 ){
          *z = 0;
          i++;
          if( i<nCol ){
            azCol[i] = &z[nSep];
            z += nSep-1;
          }
        }
................................................................................
                zFile, lineno, nCol, i+1);
        zCommit = "ROLLBACK";
        free(zLine);
        rc = 1;
        break; /* from while */
      }
      for(i=0; i<nCol; i++){
        if( azCol[i][0]=='"' ){
          int k;
          for(z=azCol[i], j=1, k=0; z[j]; j++){
            if( z[j]=='"' ){ j++; if( z[j]==0 ) break; }
            z[k++] = z[j];
          }
          z[k] = 0;
        }
        sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC);
      }
      sqlite3_step(pStmt);
      rc = sqlite3_reset(pStmt);
      free(zLine);
      if( rc!=SQLITE_OK ){
        fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));

Changes to tool/shell5.test.

190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
  close $in
  set res [catchcmd "test.db" {.import shell5.csv t1
SELECT COUNT(*) FROM t1;}]
} {0 7}

do_test shell5-1.4.10.2 {
  catchcmd "test.db" {SELECT b FROM t1 WHERE a='7';}
} {0 {"Now is the time for all good men to come to the aid of their country."}}

# check importing very long field
do_test shell5-1.5.1 {
  set str [string repeat X 999]
  set in [open shell5.csv w]
  puts $in "8|$str"
  close $in







|







190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
  close $in
  set res [catchcmd "test.db" {.import shell5.csv t1
SELECT COUNT(*) FROM t1;}]
} {0 7}

do_test shell5-1.4.10.2 {
  catchcmd "test.db" {SELECT b FROM t1 WHERE a='7';}
} {0 {Now is the time for all good men to come to the aid of their country.}}

# check importing very long field
do_test shell5-1.5.1 {
  set str [string repeat X 999]
  set in [open shell5.csv w]
  puts $in "8|$str"
  close $in