SQLite

Check-in [93f632152e]
Login

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

Overview
Comment:Update the ".import" command of the command-line shell so that it can accept field values that span multiple lines and so that it issues error messages if the input text does not strictly conform to RFC4180.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 93f632152e464a89322a0130adaf9f342411bf7d
User & Date: drh 2013-06-26 22:46:00.198
Context
2013-06-27
11:46
Add extended error code SQLITE_BUSY_SNAPSHOT - returned in WAL mode when a read-transaction cannot be upgraded to a write-transaction because it is reading from a snapshot other than the most recently committed. (check-in: 361c22969a user: dan tags: trunk)
2013-06-26
22:46
Update the ".import" command of the command-line shell so that it can accept field values that span multiple lines and so that it issues error messages if the input text does not strictly conform to RFC4180. (check-in: 93f632152e user: drh tags: trunk)
18:04
Add the "vtshim" extension, implementing a wrapper around the virtual table interface to make it Disposable for the CLR. No changes to the core. (check-in: 6c3839ef31 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/shell.c.
1645
1646
1647
1648
1649
1650
1651





































































































1652
1653
1654
1655
1656
1657
1658
** A no-op routine that runs with the ".breakpoint" doc-command.  This is
** a useful spot to set a debugger breakpoint.
*/
static void test_breakpoint(void){
  static int nCall = 0;
  nCall++;
}






































































































/*
** If an input line begins with "." then invoke this routine to
** process that line.
**
** Return 1 on error, 2 to exit, and 0 otherwise.
*/







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
** A no-op routine that runs with the ".breakpoint" doc-command.  This is
** a useful spot to set a debugger breakpoint.
*/
static void test_breakpoint(void){
  static int nCall = 0;
  nCall++;
}

/*
** An object used to read a CSV file
*/
typedef struct CSVReader CSVReader;
struct CSVReader {
  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 ",") */
};

/* Append a single byte to z[] */
static void csv_append_char(CSVReader *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");
      exit(1);
    }
  }
  p->z[p->n++] = (char)c;
}

/* Read a single field of CSV text.  Compatible with rfc4180 and extended
** 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 ",".
**   +  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;
  int cSep = p->cSeparator;
  p->n = 0;
  c = fgetc(p->in);
  if( c==EOF || seenInterrupt ){
    p->cTerm = EOF;
    return 0;
  }
  if( c=='"' ){
    int startLine = p->nLine;
    int cQuote = c;
    pc = 0;
    while( 1 ){
      c = fgetc(p->in);
      if( c=='\n' ) p->nLine++;
      if( c==cQuote ){
        if( pc==cQuote ){
          pc = 0;
          continue;
        }
      }
      if( (c==cSep && pc==cQuote)
       || (c=='\n' && pc==cQuote)
       || (c=='\n' && pc=='\r' && p->n>2 && p->z[p->n-2]==cQuote)
       || (c==EOF && pc==cQuote)
      ){
        do{ p->n--; }while( p->z[p->n]!=cQuote );
        p->z[p->n] = 0;
        p->cTerm = c;
        break;
      }
      if( pc==cQuote && c!='\r' ){
        fprintf(stderr, "%s:%d: unescaped %c character\n",
                p->zFile, p->nLine, cQuote);
      }
      if( c==EOF ){
        fprintf(stderr, "%s:%d: unterminated %c-quoted field\n",
                p->zFile, startLine, cQuote);
        p->z[p->n] = 0;
        p->cTerm = EOF;
        break;
      }
      csv_append_char(p, c);
      pc = c;
    }      
  }else{
    csv_append_char(p, c);
    while( (c = fgetc(p->in))!=EOF && c!=cSep && c!='\n' ){
      csv_append_char(p, c);
    }
    if( c=='\n' ){
      p->nLine++;
      if( p->n>1 && p->z[p->n-1]=='\r' ) p->n--;
    }
    p->z[p->n] = 0;
    p->cTerm = c;
  }
  return p->z;
}

/*
** If an input line begins with "." then invoke this routine to
** process that line.
**
** Return 1 on error, 2 to exit, and 0 otherwise.
*/
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903




1904
1905
1906
1907
1908
1909











1910
1911
1912

1913
1914
1915
1916




















1917
1918
1919
1920

1921
1922
1923
1924
1925
1926
1927
1928
1929

1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961

1962

1963
1964



1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978



1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003

2004
2005
2006
2007
2008

2009
2010
2011

2012
2013
2014
2015
2016
2017
2018
2019
2020
    if( HAS_TIMER ){
      fprintf(stderr,"%s",zTimerHelp);
    }
  }else

  if( c=='i' && strncmp(azArg[0], "import", n)==0 && nArg==3 ){
    char *zTable = azArg[2];    /* Insert data into this table */
    char *zFile = azArg[1];     /* The file from which to extract data */
    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 nSep;                   /* Number of bytes in p->separator[] */
    char *zSql;                 /* An SQL statement */
    char *zLine;                /* A single line of input from the file */
    char **azCol;               /* zLine[] broken up into columns */
    char *zCommit;              /* How to commit changes */   
    FILE *in;                   /* The input file */
    int lineno = 0;             /* Line number of input file */





    open_db(p);
    nSep = strlen30(p->separator);
    if( nSep==0 ){
      fprintf(stderr, "Error: non-null separator required for import\n");
      return 1;
    }











    zSql = sqlite3_mprintf("SELECT * FROM %s", zTable);
    if( zSql==0 ){
      fprintf(stderr, "Error: out of memory\n");

      return 1;
    }
    nByte = strlen30(zSql);
    rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0);




















    sqlite3_free(zSql);
    if( rc ){
      if (pStmt) sqlite3_finalize(pStmt);
      fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));

      return 1;
    }
    nCol = sqlite3_column_count(pStmt);
    sqlite3_finalize(pStmt);
    pStmt = 0;
    if( nCol==0 ) return 0; /* no columns, no error */
    zSql = malloc( nByte + 20 + nCol*2 );
    if( zSql==0 ){
      fprintf(stderr, "Error: out of memory\n");

      return 1;
    }
    sqlite3_snprintf(nByte+20, zSql, "INSERT INTO %s VALUES(?", zTable);
    j = strlen30(zSql);
    for(i=1; i<nCol; i++){
      zSql[j++] = ',';
      zSql[j++] = '?';
    }
    zSql[j++] = ')';
    zSql[j] = 0;
    rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0);
    free(zSql);
    if( rc ){
      fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
      if (pStmt) sqlite3_finalize(pStmt);
      return 1;
    }
    in = fopen(zFile, "rb");
    if( in==0 ){
      fprintf(stderr, "Error: cannot open \"%s\"\n", zFile);
      sqlite3_finalize(pStmt);
      return 1;
    }
    azCol = malloc( sizeof(azCol[0])*(nCol+1) );
    if( azCol==0 ){
      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;
          }
        }
      } /* end for */



      *z = 0;
      if( i+1!=nCol ){
        fprintf(stderr,
                "Error: %s line %d: expected %d columns of data but found %d\n",
                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));
        zCommit = "ROLLBACK";
        rc = 1;
        break; /* from while */
      }

    } /* end while */
    free(azCol);
    fclose(in);

    sqlite3_finalize(pStmt);
    sqlite3_exec(p->db, zCommit, 0, 0, 0);
  }else

  if( c=='i' && strncmp(azArg[0], "indices", n)==0 && nArg<3 ){
    struct callback_data data;
    char *zErrMsg = 0;
    open_db(p);
    memcpy(&data, p, sizeof(data));







<






<
<
<
<
|

>
>
>
>






>
>
>
>
>
>
>
>
>
>
>



>




>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




>






|


>


|








|



<
<
<
<
<
<
<
<
<
<
<
|
<


<
<
>
|
>
|
|
>
>
>
|
|
<
<
<
<
<

|
<
<
|
|
|
>
>
>
|
<
|
|
|
<
<
<
<

|
<
<
<
<
<
<
<
<
<
<
|
|
<
|
>
|
<
<
<
|
>
|
|
|
>

|







1985
1986
1987
1988
1989
1990
1991

1992
1993
1994
1995
1996
1997




1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078











2079

2080
2081


2082
2083
2084
2085
2086
2087
2088
2089
2090
2091





2092
2093


2094
2095
2096
2097
2098
2099
2100

2101
2102
2103




2104
2105










2106
2107

2108
2109
2110



2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
    if( HAS_TIMER ){
      fprintf(stderr,"%s",zTimerHelp);
    }
  }else

  if( c=='i' && strncmp(azArg[0], "import", n)==0 && nArg==3 ){
    char *zTable = azArg[2];    /* Insert data into this table */

    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 nSep;                   /* Number of bytes in p->separator[] */
    char *zSql;                 /* An SQL statement */




    CSVReader sCsv;             /* Reader context */

    seenInterrupt = 0;
    memset(&sCsv, 0, sizeof(sCsv));
    sCsv.zFile = azArg[1];
    sCsv.nLine = 1;
    open_db(p);
    nSep = strlen30(p->separator);
    if( nSep==0 ){
      fprintf(stderr, "Error: non-null separator required for import\n");
      return 1;
    }
    if( nSep>1 ){
      fprintf(stderr, "Error: multi-character separators not allowed"
                      " for import\n");
      return 1;
    }
    sCsv.in = fopen(sCsv.zFile, "rb");
    if( sCsv.in==0 ){
      fprintf(stderr, "Error: cannot open \"%s\"\n", sCsv.zFile);
      return 1;
    }
    sCsv.cSeparator = p->separator[0];
    zSql = sqlite3_mprintf("SELECT * FROM %s", zTable);
    if( zSql==0 ){
      fprintf(stderr, "Error: out of memory\n");
      fclose(sCsv.in);
      return 1;
    }
    nByte = strlen30(zSql);
    rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0);
    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);
        cSep = ',';
        if( sCsv.cTerm!=sCsv.cSeparator ) break;
      }
      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);
        fclose(sCsv.in);
        return 1;
      }
      rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0);
    }
    sqlite3_free(zSql);
    if( rc ){
      if (pStmt) sqlite3_finalize(pStmt);
      fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));
      fclose(sCsv.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");
      fclose(sCsv.in);
      return 1;
    }
    sqlite3_snprintf(nByte+20, zSql, "INSERT INTO \"%w\" VALUES(?", zTable);
    j = strlen30(zSql);
    for(i=1; i<nCol; i++){
      zSql[j++] = ',';
      zSql[j++] = '?';
    }
    zSql[j++] = ')';
    zSql[j] = 0;
    rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0);
    sqlite3_free(zSql);
    if( rc ){
      fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
      if (pStmt) sqlite3_finalize(pStmt);











      fclose(sCsv.in);

      return 1;
    }


    do{
      int startLine = sCsv.nLine;
      for(i=0; i<nCol; i++){
        char *z = csv_read_one_field(&sCsv);
        if( z==0 && i==0 ) break;
        sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT);
        if( i<nCol-1 && sCsv.cTerm!=sCsv.cSeparator ){
          fprintf(stderr, "%s:%d: expected %d columns but found %d - "
                          "filling the rest with NULL\n",
                          sCsv.zFile, startLine, nCol, i+1);





          i++;
          while( i<nCol ){ sqlite3_bind_null(pStmt, i); i++; }


        }
      }
      if( sCsv.cTerm==sCsv.cSeparator ){
        do{
          csv_read_one_field(&sCsv);
          i++;
        }while( sCsv.cTerm==sCsv.cSeparator );

        fprintf(stderr, "%s:%d: expected %d columns but found %d - "
                        "extras ignored\n",
                        sCsv.zFile, startLine, nCol, i);




      }
      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,
                  sqlite3_errmsg(db));



        }
      }
    }while( sCsv.cTerm!=EOF );

    fclose(sCsv.in);
    sqlite3_free(sCsv.z);
    sqlite3_finalize(pStmt);
    sqlite3_exec(p->db, "COMMIT", 0, 0, 0);
  }else

  if( c=='i' && strncmp(azArg[0], "indices", n)==0 && nArg<3 ){
    struct callback_data data;
    char *zErrMsg = 0;
    open_db(p);
    memcpy(&data, p, sizeof(data));
Changes to test/shell1.test.
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
# .import FILE TABLE     Import data from FILE into TABLE
do_test shell1-3.11.1 {
  catchcmd "test.db" ".import"
} {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}
do_test shell1-3.11.2 {
  catchcmd "test.db" ".import FOO"
} {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}
do_test shell1-3.11.2 {
  catchcmd "test.db" ".import FOO BAR"
} {1 {Error: no such table: BAR}}
do_test shell1-3.11.3 {
  # too many arguments
  catchcmd "test.db" ".import FOO BAR BAD"
} {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}

# .indices ?TABLE?       Show names of all indices
#                          If TABLE specified, only show indices for tables







|
|
|







393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
# .import FILE TABLE     Import data from FILE into TABLE
do_test shell1-3.11.1 {
  catchcmd "test.db" ".import"
} {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}
do_test shell1-3.11.2 {
  catchcmd "test.db" ".import FOO"
} {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}
#do_test shell1-3.11.2 {
#  catchcmd "test.db" ".import FOO BAR"
#} {1 {Error: no such table: BAR}}
do_test shell1-3.11.3 {
  # too many arguments
  catchcmd "test.db" ".import FOO BAR BAD"
} {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}

# .indices ?TABLE?       Show names of all indices
#                          If TABLE specified, only show indices for tables
Changes to test/shell5.test.
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
# .import FILE TABLE     Import data from FILE into TABLE
do_test shell5-1.1.1 {
  catchcmd "test.db" ".import"
} {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}
do_test shell5-1.1.2 {
  catchcmd "test.db" ".import FOO"
} {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}
do_test shell5-1.1.2 {
  catchcmd "test.db" ".import FOO BAR"
} {1 {Error: no such table: BAR}}
do_test shell5-1.1.3 {
  # too many arguments
  catchcmd "test.db" ".import FOO BAR BAD"
} {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}

# .separator STRING      Change separator used by output mode and .import
do_test shell1-1.2.1 {







|
|
|







41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
# .import FILE TABLE     Import data from FILE into TABLE
do_test shell5-1.1.1 {
  catchcmd "test.db" ".import"
} {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}
do_test shell5-1.1.2 {
  catchcmd "test.db" ".import FOO"
} {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}
#do_test shell5-1.1.2 {
#  catchcmd "test.db" ".import FOO BAR"
#} {1 {Error: no such table: BAR}}
do_test shell5-1.1.3 {
  # too many arguments
  catchcmd "test.db" ".import FOO BAR BAD"
} {1 {Error: unknown command or invalid arguments:  "import". Enter ".help" for help}}

# .separator STRING      Change separator used by output mode and .import
do_test shell1-1.2.1 {
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

120
121
122
123
124
125
126

# import file with 1 row, 1 column (expecting 2 cols)
do_test shell5-1.4.3 {
  set in [open shell5.csv w]
  puts $in "1"
  close $in
  set res [catchcmd "test.db" {.import shell5.csv t1}]
} {1 {Error: shell5.csv line 1: expected 2 columns of data but found 1}}

# import file with 1 row, 3 columns (expecting 2 cols)
do_test shell5-1.4.4 {
  set in [open shell5.csv w]
  puts $in "1|2|3"
  close $in
  set res [catchcmd "test.db" {.import shell5.csv t1}]
} {1 {Error: shell5.csv line 1: expected 2 columns of data but found 3}}

# import file with 1 row, 2 columns
do_test shell5-1.4.5 {
  set in [open shell5.csv w]
  puts $in "1|2"
  close $in
  set res [catchcmd "test.db" {.import shell5.csv t1

SELECT COUNT(*) FROM t1;}]
} {0 1}

# import file with 2 rows, 2 columns
# note we end up with 3 rows because of the 1 row 
# imported above.
do_test shell5-1.4.6 {







|







|






|
>







97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127

# import file with 1 row, 1 column (expecting 2 cols)
do_test shell5-1.4.3 {
  set in [open shell5.csv w]
  puts $in "1"
  close $in
  set res [catchcmd "test.db" {.import shell5.csv t1}]
} {1 {shell5.csv:1: expected 2 columns but found 1 - filling the rest with NULL}}

# import file with 1 row, 3 columns (expecting 2 cols)
do_test shell5-1.4.4 {
  set in [open shell5.csv w]
  puts $in "1|2|3"
  close $in
  set res [catchcmd "test.db" {.import shell5.csv t1}]
} {1 {shell5.csv:1: expected 2 columns but found 3 - extras ignored}}

# import file with 1 row, 2 columns
do_test shell5-1.4.5 {
  set in [open shell5.csv w]
  puts $in "1|2"
  close $in
  set res [catchcmd "test.db" {DELETE FROM t1;
.import shell5.csv t1
SELECT COUNT(*) FROM t1;}]
} {0 1}

# import file with 2 rows, 2 columns
# note we end up with 3 rows because of the 1 row 
# imported above.
do_test shell5-1.4.6 {
193
194
195
196
197
198
199
200
201
202
203



204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219

220
221
222
223
224
225
226
227
228
229
SELECT length(b) FROM t1 WHERE a='8';}]
} {0 999}

# try importing into a table with a large number of columns.
# This is limited by SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.
set cols 999
do_test shell5-1.6.1 {
  set sql {CREATE TABLE t2(}
  set data {}
  for {set i 1} {$i<$cols} {incr i} {
    append sql "c$i,"



    append data "$i|"
  }
  append sql "c$cols);"
  append data "$cols"
  catchcmd "test.db" $sql
  set in [open shell5.csv w]
  puts $in $data
  close $in
  set res [catchcmd "test.db" {.import shell5.csv t2
SELECT COUNT(*) FROM t2;}]
} {0 1}

# try importing a large number of rows
set rows 999999
do_test shell5-1.7.1 {
  set in [open shell5.csv w]

  for {set i 1} {$i<=$rows} {incr i} {
    puts $in $i
  }
  close $in
  set res [catchcmd "test.db" {CREATE TABLE t3(a);
.import shell5.csv t3
SELECT COUNT(*) FROM t3;}]
} [list 0 $rows]

finish_test







<


|
>
>
>


<

<








|


>




|





194
195
196
197
198
199
200

201
202
203
204
205
206
207
208

209

210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
SELECT length(b) FROM t1 WHERE a='8';}]
} {0 999}

# try importing into a table with a large number of columns.
# This is limited by SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.
set cols 999
do_test shell5-1.6.1 {

  set data {}
  for {set i 1} {$i<$cols} {incr i} {
    append data "c$i|"
  }
  append data "c$cols\n";
  for {set i 1} {$i<$cols} {incr i} {
    append data "$i|"
  }

  append data "$cols"

  set in [open shell5.csv w]
  puts $in $data
  close $in
  set res [catchcmd "test.db" {.import shell5.csv t2
SELECT COUNT(*) FROM t2;}]
} {0 1}

# try importing a large number of rows
set rows 99999
do_test shell5-1.7.1 {
  set in [open shell5.csv w]
  puts $in a
  for {set i 1} {$i<=$rows} {incr i} {
    puts $in $i
  }
  close $in
  set res [catchcmd "test.db" {.mode csv
.import shell5.csv t3
SELECT COUNT(*) FROM t3;}]
} [list 0 $rows]

finish_test