/ Check-in [4c02b344]
Login

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

Overview
Comment:If the filename argument to the ".import" command in the command-line shell begins with '|' then treat it as an input pipe rather than a file.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:4c02b344f5c6f6fb1c61b79d51063a1e0e2d75c0
User & Date: drh 2013-06-27 14:07:53
Context
2013-06-27
14:24
Add a test to ensure that if BEGIN IMMEDIATE fails with SQLITE_BUSY, it does not leave the user with an open read transaction (unless one was already open). check-in: 22bced36 user: dan tags: trunk
14:07
If the filename argument to the ".import" command in the command-line shell begins with '|' then treat it as an input pipe rather than a file. check-in: 4c02b344 user: drh tags: trunk
13:26
Improved handling of backslash escapes on double-quoted arguments to dot-commands in the command-line shell. check-in: 656a1fe5 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.

  1991   1991       if( HAS_TIMER ){
  1992   1992         fprintf(stderr,"%s",zTimerHelp);
  1993   1993       }
  1994   1994     }else
  1995   1995   
  1996   1996     if( c=='i' && strncmp(azArg[0], "import", n)==0 && nArg==3 ){
  1997   1997       char *zTable = azArg[2];    /* Insert data into this table */
         1998  +    char *zFile = azArg[1];     /* Name of file to extra content from */
  1998   1999       sqlite3_stmt *pStmt = NULL; /* A statement */
  1999   2000       int nCol;                   /* Number of columns in the table */
  2000   2001       int nByte;                  /* Number of bytes in an SQL string */
  2001   2002       int i, j;                   /* Loop counters */
  2002   2003       int nSep;                   /* Number of bytes in p->separator[] */
  2003   2004       char *zSql;                 /* An SQL statement */
  2004   2005       CSVReader sCsv;             /* Reader context */
         2006  +    int (*xCloser)(FILE*);      /* Procedure to close th3 connection */
  2005   2007   
  2006   2008       seenInterrupt = 0;
  2007   2009       memset(&sCsv, 0, sizeof(sCsv));
  2008         -    sCsv.zFile = azArg[1];
  2009         -    sCsv.nLine = 1;
  2010   2010       open_db(p);
  2011   2011       nSep = strlen30(p->separator);
  2012   2012       if( nSep==0 ){
  2013   2013         fprintf(stderr, "Error: non-null separator required for import\n");
  2014   2014         return 1;
  2015   2015       }
  2016   2016       if( nSep>1 ){
  2017   2017         fprintf(stderr, "Error: multi-character separators not allowed"
  2018   2018                         " for import\n");
  2019   2019         return 1;
  2020   2020       }
  2021         -    sCsv.in = fopen(sCsv.zFile, "rb");
         2021  +    sCsv.zFile = zFile;
         2022  +    sCsv.nLine = 1;
         2023  +    if( sCsv.zFile[0]=='|' ){
         2024  +      sCsv.in = popen(sCsv.zFile+1, "r");
         2025  +      sCsv.zFile = "<pipe>";
         2026  +      xCloser = pclose;
         2027  +    }else{
         2028  +      sCsv.in = fopen(sCsv.zFile, "rb");
         2029  +      xCloser = fclose;
         2030  +    }
  2022   2031       if( sCsv.in==0 ){
  2023         -      fprintf(stderr, "Error: cannot open \"%s\"\n", sCsv.zFile);
         2032  +      fprintf(stderr, "Error: cannot open \"%s\"\n", zFile);
  2024   2033         return 1;
  2025   2034       }
  2026   2035       sCsv.cSeparator = p->separator[0];
  2027   2036       zSql = sqlite3_mprintf("SELECT * FROM %s", zTable);
  2028   2037       if( zSql==0 ){
  2029   2038         fprintf(stderr, "Error: out of memory\n");
  2030         -      fclose(sCsv.in);
         2039  +      xCloser(sCsv.in);
  2031   2040         return 1;
  2032   2041       }
  2033   2042       nByte = strlen30(zSql);
  2034   2043       rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0);
  2035   2044       if( rc && sqlite3_strglob("no such table: *", sqlite3_errmsg(db))==0 ){
  2036   2045         char *zCreate = sqlite3_mprintf("CREATE TABLE %s", zTable);
  2037   2046         char cSep = '(';
  2038   2047         while( csv_read_one_field(&sCsv) ){
  2039   2048           zCreate = sqlite3_mprintf("%z%c\n  \"%s\" TEXT", zCreate, cSep, sCsv.z);
  2040   2049           cSep = ',';
  2041   2050           if( sCsv.cTerm!=sCsv.cSeparator ) break;
         2051  +      }
         2052  +      if( cSep=='(' ){
         2053  +        sqlite3_free(zCreate);
         2054  +        sqlite3_free(sCsv.z);
         2055  +        xCloser(sCsv.in);
         2056  +        fprintf(stderr,"%s: empty file\n", sCsv.zFile);
         2057  +        return 1;
  2042   2058         }
  2043   2059         zCreate = sqlite3_mprintf("%z\n)", zCreate);
  2044   2060         rc = sqlite3_exec(p->db, zCreate, 0, 0, 0);
  2045   2061         sqlite3_free(zCreate);
  2046   2062         if( rc ){
  2047   2063           fprintf(stderr, "CREATE TABLE %s(...) failed: %s\n", zTable,
  2048   2064                   sqlite3_errmsg(db));
  2049   2065           sqlite3_free(sCsv.z);
  2050         -        fclose(sCsv.in);
         2066  +        xCloser(sCsv.in);
  2051   2067           return 1;
  2052   2068         }
  2053   2069         rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0);
  2054   2070       }
  2055   2071       sqlite3_free(zSql);
  2056   2072       if( rc ){
  2057   2073         if (pStmt) sqlite3_finalize(pStmt);
  2058   2074         fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));
  2059         -      fclose(sCsv.in);
         2075  +      xCloser(sCsv.in);
  2060   2076         return 1;
  2061   2077       }
  2062   2078       nCol = sqlite3_column_count(pStmt);
  2063   2079       sqlite3_finalize(pStmt);
  2064   2080       pStmt = 0;
  2065   2081       if( nCol==0 ) return 0; /* no columns, no error */
  2066   2082       zSql = sqlite3_malloc( nByte*2 + 20 + nCol*2 );
  2067   2083       if( zSql==0 ){
  2068   2084         fprintf(stderr, "Error: out of memory\n");
  2069         -      fclose(sCsv.in);
         2085  +      xCloser(sCsv.in);
  2070   2086         return 1;
  2071   2087       }
  2072   2088       sqlite3_snprintf(nByte+20, zSql, "INSERT INTO \"%w\" VALUES(?", zTable);
  2073   2089       j = strlen30(zSql);
  2074   2090       for(i=1; i<nCol; i++){
  2075   2091         zSql[j++] = ',';
  2076   2092         zSql[j++] = '?';
................................................................................
  2078   2094       zSql[j++] = ')';
  2079   2095       zSql[j] = 0;
  2080   2096       rc = sqlite3_prepare(p->db, zSql, -1, &pStmt, 0);
  2081   2097       sqlite3_free(zSql);
  2082   2098       if( rc ){
  2083   2099         fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
  2084   2100         if (pStmt) sqlite3_finalize(pStmt);
  2085         -      fclose(sCsv.in);
         2101  +      xCloser(sCsv.in);
  2086   2102         return 1;
  2087   2103       }
  2088   2104       do{
  2089   2105         int startLine = sCsv.nLine;
  2090   2106         for(i=0; i<nCol; i++){
  2091   2107           char *z = csv_read_one_field(&sCsv);
  2092   2108           if( z==0 && i==0 ) break;
................................................................................
  2114   2130           if( rc!=SQLITE_OK ){
  2115   2131             fprintf(stderr, "%s:%d: INSERT failed: %s\n", sCsv.zFile, startLine,
  2116   2132                     sqlite3_errmsg(db));
  2117   2133           }
  2118   2134         }
  2119   2135       }while( sCsv.cTerm!=EOF );
  2120   2136   
  2121         -    fclose(sCsv.in);
         2137  +    xCloser(sCsv.in);
  2122   2138       sqlite3_free(sCsv.z);
  2123   2139       sqlite3_finalize(pStmt);
  2124   2140       sqlite3_exec(p->db, "COMMIT", 0, 0, 0);
  2125   2141     }else
  2126   2142   
  2127   2143     if( c=='i' && strncmp(azArg[0], "indices", n)==0 && nArg<3 ){
  2128   2144       struct callback_data data;

Changes to test/shell5.test.

   211    211     puts $in $data
   212    212     close $in
   213    213     set res [catchcmd "test.db" {.import shell5.csv t2
   214    214   SELECT COUNT(*) FROM t2;}]
   215    215   } {0 1}
   216    216   
   217    217   # try importing a large number of rows
   218         -set rows 99999
          218  +set rows 9999
   219    219   do_test shell5-1.7.1 {
   220    220     set in [open shell5.csv w]
   221    221     puts $in a
   222    222     for {set i 1} {$i<=$rows} {incr i} {
   223    223       puts $in $i
   224    224     }
   225    225     close $in
   226    226     set res [catchcmd "test.db" {.mode csv
   227    227   .import shell5.csv t3
   228    228   SELECT COUNT(*) FROM t3;}]
   229    229   } [list 0 $rows]
          230  +
          231  +# Inport from a pipe.  (Unix only, as it requires "awk")
          232  +if {$tcl_platform(platform)=="unix"} {
          233  +  do_test shell5-1.8 {
          234  +    file delete -force test.db
          235  +    catchcmd test.db {.mode csv
          236  +.import "|awk 'END{print \"x,y\";for(i=1;i<=5;i++){print i \",this is \" i}}'" t1
          237  +SELECT * FROM t1;}
          238  +  } {0 {1,"this is 1"
          239  +2,"this is 2"
          240  +3,"this is 3"
          241  +4,"this is 4"
          242  +5,"this is 5"}}
          243  +}
   230    244   
   231    245   finish_test