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: | 4c02b344f5c6f6fb1c61b79d51063a1e |
| 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
Changes to src/shell.c.
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 .... 2078 2079 2080 2081 2082 2083 2084 2085 2086 2087 2088 2089 2090 2091 2092 .... 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 |
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;
................................................................................
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;
|
> > < < > > > > > > > | > > | | > > > > > > > | | | | | |
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 .... 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 .... 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 |
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]; /* Name of file to extra content from */
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 */
int (*xCloser)(FILE*); /* Procedure to close th3 connection */
seenInterrupt = 0;
memset(&sCsv, 0, sizeof(sCsv));
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.zFile = zFile;
sCsv.nLine = 1;
if( sCsv.zFile[0]=='|' ){
sCsv.in = popen(sCsv.zFile+1, "r");
sCsv.zFile = "<pipe>";
xCloser = pclose;
}else{
sCsv.in = fopen(sCsv.zFile, "rb");
xCloser = fclose;
}
if( sCsv.in==0 ){
fprintf(stderr, "Error: cannot open \"%s\"\n", 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");
xCloser(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;
}
if( cSep=='(' ){
sqlite3_free(zCreate);
sqlite3_free(sCsv.z);
xCloser(sCsv.in);
fprintf(stderr,"%s: empty file\n", sCsv.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);
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));
xCloser(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");
xCloser(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);
xCloser(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;
................................................................................
if( rc!=SQLITE_OK ){
fprintf(stderr, "%s:%d: INSERT failed: %s\n", sCsv.zFile, startLine,
sqlite3_errmsg(db));
}
}
}while( sCsv.cTerm!=EOF );
xCloser(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;
|
Changes to test/shell5.test.
211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 |
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
|
| > > > > > > > > > > > > > > |
211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 |
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 9999
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]
# Inport from a pipe. (Unix only, as it requires "awk")
if {$tcl_platform(platform)=="unix"} {
do_test shell5-1.8 {
file delete -force test.db
catchcmd test.db {.mode csv
.import "|awk 'END{print \"x,y\";for(i=1;i<=5;i++){print i \",this is \" i}}'" t1
SELECT * FROM t1;}
} {0 {1,"this is 1"
2,"this is 2"
3,"this is 3"
4,"this is 4"
5,"this is 5"}}
}
finish_test
|