SQLite

Check-in Differences
Login

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

Difference From 9282bcde301cee2a To 38d9dbca16607801

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

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",







>







4066
4067
4068
4069
4070
4071
4072
4073
4074
4075
4076
4077
4078
4079
4080
  ".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",
  "     --schema S            Target table to be S.TABLE",
  "     -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",
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[] */







>







8379
8380
8381
8382
8383
8384
8385
8386
8387
8388
8389
8390
8391
8392
8393
    }else{
      showHelp(p->out, 0);
    }
  }else

  if( c=='i' && strncmp(azArg[0], "import", n)==0 ){
    char *zTable = 0;           /* Insert data into this table */
    char *zSchema = "main";     /* within this schema */
    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[] */
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;







>
>







8422
8423
8424
8425
8426
8427
8428
8429
8430
8431
8432
8433
8434
8435
8436
8437
          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,"-schema")==0 && i<nArg-1 ){
        zSchema = azArg[++i];
      }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;
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);







>














|








|




















|
|







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
8571
8572
8573
8574
8575
    }
    if( sCtx.in==0 ){
      utf8_printf(stderr, "Error: cannot open \"%s\"\n", zFile);
      rc = 1;
      import_cleanup(&sCtx);
      goto meta_command_exit;
    }
    /* Below, resources must be freed before 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 %s.\"%w\"", zSchema, 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 %s.\"%w\"", zSchema,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.\"%s\"(...) failed: %s\n",
                    zSchema, 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);
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;







|
>







8585
8586
8587
8588
8589
8590
8591
8592
8593
8594
8595
8596
8597
8598
8599
8600
    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 %s.\"%w\" VALUES(?",
                     zSchema, 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
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







|
>
|







|
>







|
>




















|
>
|







>
|
|
|







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
148
149
150
151
152
} {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 ":memory:" {ATTACH 'test.db' AS test;
.import -schema test shell5.csv t1
SELECT COUNT(*) FROM test.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 ":memory:" {ATTACH 'test.db' AS test;
.import -schema test 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 ":memory:" {ATTACH 'test.db' AS test;
.import --schema test 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 ":memory:" {ATTACH 'test.db' AS test;
.import -schema test shell5.csv t1
SELECT COUNT(*) FROM test.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 ":memory:" {ATTACH 'test.db' AS test;
.separator ,
.import --schema test shell5.csv t1
SELECT COUNT(*) FROM test.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