Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Difference From 38d9dbca16607801 To 9282bcde301cee2a
2022-01-17
| ||
03:25 | Bring .import --schema enhancement in. Refine mkshellc include logic. (check-in: e38f45cd user: larrybr tags: cli_extension) | |
2022-01-14
| ||
16:29 | For .import schema, quote it as for other identifiers. (check-in: bff9153c user: larrybr tags: trunk) | |
2022-01-13
| ||
21:22 | Add --schema S option to .import (check-in: 38d9dbca user: larrybr tags: trunk) | |
2022-01-12
| ||
20:31 | When the result of a subquery is to be stored in a register and that subquery has an ORDER BY clause and an OFFSET, NULL out the destination register before starting the ORDER BY so that the register will be set correctly even if the OFFSET is larger than the number of output rows. Fix for the problem reported in forum post 0ec80f12d02acb3f. (check-in: 9282bcde user: drh tags: trunk) | |
01:42 | Make tool/mctimec.tcl effect more regular and obvious (check-in: 02aaa10f user: larrybr tags: trunk) | |
Changes to src/shell.c.in.
︙ | ︙ | |||
4066 4067 4068 4069 4070 4071 4072 | ".headers on|off Turn display of headers on or off", ".help ?-all? ?PATTERN? Show help text for PATTERN", ".import FILE TABLE Import data from FILE into TABLE", " Options:", " --ascii Use \\037 and \\036 as column and row separators", " --csv Use , and \\n as column and row separators", " --skip N Skip the first N rows of input", | < | 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076 4077 4078 4079 | ".headers on|off Turn display of headers on or off", ".help ?-all? ?PATTERN? Show help text for PATTERN", ".import FILE TABLE Import data from FILE into TABLE", " Options:", " --ascii Use \\037 and \\036 as column and row separators", " --csv Use , and \\n as column and row separators", " --skip N Skip the first N rows of input", " -v \"Verbose\" - increase auxiliary output", " Notes:", " * If TABLE does not exist, it is created. The first row of input", " determines the column names.", " * If neither --csv or --ascii are used, the input mode is derived", " from the \".mode\" output mode", " * If FILE begins with \"|\" then it is a command that generates the", |
︙ | ︙ | |||
8379 8380 8381 8382 8383 8384 8385 | }else{ showHelp(p->out, 0); } }else if( c=='i' && strncmp(azArg[0], "import", n)==0 ){ char *zTable = 0; /* Insert data into this table */ | < | 8378 8379 8380 8381 8382 8383 8384 8385 8386 8387 8388 8389 8390 8391 | }else{ showHelp(p->out, 0); } }else if( c=='i' && strncmp(azArg[0], "import", n)==0 ){ char *zTable = 0; /* Insert data into this table */ char *zFile = 0; /* 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 needCommit; /* True to COMMIT or ROLLBACK at end */ int nSep; /* Number of bytes in p->colSeparator[] */ |
︙ | ︙ | |||
8422 8423 8424 8425 8426 8427 8428 | utf8_printf(p->out, "ERROR: extra argument: \"%s\". Usage:\n", z); showHelp(p->out, "import"); rc = 1; goto meta_command_exit; } }else if( strcmp(z,"-v")==0 ){ eVerbose++; | < < | 8420 8421 8422 8423 8424 8425 8426 8427 8428 8429 8430 8431 8432 8433 | utf8_printf(p->out, "ERROR: extra argument: \"%s\". Usage:\n", z); showHelp(p->out, "import"); rc = 1; goto meta_command_exit; } }else if( strcmp(z,"-v")==0 ){ eVerbose++; }else if( strcmp(z,"-skip")==0 && i<nArg-1 ){ nSkip = integerValue(azArg[++i]); }else if( strcmp(z,"-ascii")==0 ){ sCtx.cColSep = SEP_Unit[0]; sCtx.cRowSep = SEP_Record[0]; xRead = ascii_read_one_field; useOutputMode = 0; |
︙ | ︙ | |||
8515 8516 8517 8518 8519 8520 8521 | } if( sCtx.in==0 ){ utf8_printf(stderr, "Error: cannot open \"%s\"\n", zFile); rc = 1; import_cleanup(&sCtx); goto meta_command_exit; } | < | | | | | 8511 8512 8513 8514 8515 8516 8517 8518 8519 8520 8521 8522 8523 8524 8525 8526 8527 8528 8529 8530 8531 8532 8533 8534 8535 8536 8537 8538 8539 8540 8541 8542 8543 8544 8545 8546 8547 8548 8549 8550 8551 8552 8553 8554 8555 8556 8557 8558 8559 8560 8561 8562 8563 8564 8565 8566 8567 8568 8569 8570 | } if( sCtx.in==0 ){ utf8_printf(stderr, "Error: cannot open \"%s\"\n", zFile); rc = 1; import_cleanup(&sCtx); goto meta_command_exit; } if( eVerbose>=2 || (eVerbose>=1 && useOutputMode) ){ char zSep[2]; zSep[1] = 0; zSep[0] = sCtx.cColSep; utf8_printf(p->out, "Column separator "); output_c_string(p->out, zSep); utf8_printf(p->out, ", row separator "); zSep[0] = sCtx.cRowSep; output_c_string(p->out, zSep); utf8_printf(p->out, "\n"); } while( (nSkip--)>0 ){ while( xRead(&sCtx) && sCtx.cTerm==sCtx.cColSep ){} } zSql = sqlite3_mprintf("SELECT * FROM \"%w\"", zTable); if( zSql==0 ){ import_cleanup(&sCtx); shell_out_of_memory(); } nByte = strlen30(zSql); rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0); import_append_char(&sCtx, 0); /* To ensure sCtx.z is allocated */ if( rc && sqlite3_strglob("no such table: *", sqlite3_errmsg(p->db))==0 ){ char *zCreate = sqlite3_mprintf("CREATE TABLE \"%w\"", zTable); char cSep = '('; while( xRead(&sCtx) ){ zCreate = sqlite3_mprintf("%z%c\n \"%w\" TEXT", zCreate, cSep, sCtx.z); cSep = ','; if( sCtx.cTerm!=sCtx.cColSep ) break; } if( cSep=='(' ){ sqlite3_free(zCreate); import_cleanup(&sCtx); utf8_printf(stderr,"%s: empty file\n", sCtx.zFile); rc = 1; goto meta_command_exit; } zCreate = sqlite3_mprintf("%z\n)", zCreate); if( eVerbose>=1 ){ utf8_printf(p->out, "%s\n", zCreate); } rc = sqlite3_exec(p->db, zCreate, 0, 0, 0); sqlite3_free(zCreate); if( rc ){ utf8_printf(stderr, "CREATE TABLE \"%s\"(...) failed: %s\n", zTable, sqlite3_errmsg(p->db)); import_cleanup(&sCtx); rc = 1; goto meta_command_exit; } rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0); } sqlite3_free(zSql); |
︙ | ︙ | |||
8585 8586 8587 8588 8589 8590 8591 | pStmt = 0; if( nCol==0 ) return 0; /* no columns, no error */ zSql = sqlite3_malloc64( nByte*2 + 20 + nCol*2 ); if( zSql==0 ){ import_cleanup(&sCtx); shell_out_of_memory(); } | | < | 8580 8581 8582 8583 8584 8585 8586 8587 8588 8589 8590 8591 8592 8593 8594 | pStmt = 0; if( nCol==0 ) return 0; /* no columns, no error */ zSql = sqlite3_malloc64( nByte*2 + 20 + nCol*2 ); if( zSql==0 ){ import_cleanup(&sCtx); shell_out_of_memory(); } 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; |
︙ | ︙ |
Changes to test/shell5.test.
︙ | ︙ | |||
84 85 86 87 88 89 90 | } {1 {Error: cannot open "FOO"}} # empty import file do_test shell5-1.4.2 { forcedelete shell5.csv set in [open shell5.csv w] close $in | | < | | < | < | < | < | | | | 84 85 86 87 88 89 90 91 92 93 94 95 96 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 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 | } {1 {Error: cannot open "FOO"}} # empty import file do_test shell5-1.4.2 { forcedelete shell5.csv set in [open shell5.csv w] close $in set res [catchcmd "test.db" {.import shell5.csv t1 SELECT COUNT(*) FROM t1;}] } {0 0} # 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 { set in [open shell5.csv w] puts $in "2|3" puts $in "3|4" close $in set res [catchcmd "test.db" {.import shell5.csv t1 SELECT COUNT(*) FROM t1;}] } {0 3} # import file with 1 row, 2 columns, using a comma do_test shell5-1.4.7 { set in [open shell5.csv w] puts $in "4,5" close $in set res [catchcmd "test.db" {.separator , .import shell5.csv t1 SELECT COUNT(*) FROM t1;}] } {0 4} # import file with 1 row, 2 columns, text data do_test shell5-1.4.8.1 { set in [open shell5.csv w] puts $in "5|Now is the time for all good men to come to the aid of their country." close $in |
︙ | ︙ |