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: |
93f632152e464a89322a0130adaf9f34 |
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
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 | 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 */ | < < < < < | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > | | < < < < < < < < < < < | < < < > | > | | > > > | | < < < < < | < < | | | > > > | < | | | < < < < | < < < < < < < < < < | | < | > | < < < | > | | | > | | 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 | # .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}} | | | | | 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 | # .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}} | | | | | 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 | # 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}] | | | | > | 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 | 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 { | < | > > > < < | > | | 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 |