niceSOFT/sqlite: Official Git mirror of the SQLite source tree

test/shell2.test
442 строки15 KB
drh
Improvements to the .import command of the CLI.
28 дек 2025, 17:01
28 дек 2025, 17:0138c8014
# 2009 Nov 11
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# TESTRUNNER: shell
#
# The focus of this file is testing the CLI shell tool.
#
# $Id: shell2.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $
#

# Test plan:
#
#   shell2-1.*: Misc. test of various tickets and reported errors.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set CLI [test_find_cli]
db close
forcedelete test.db test.db-journal test.db-wal
sqlite3 db test.db


#----------------------------------------------------------------------------
#   shell2-1.*: Misc. test of various tickets and reported errors.
#

# Batch mode not creating databases.  
# Reported on mailing list by Ken Zalewski.
# Ticket [aeff892c57].
do_test shell2-1.1.1 {
  forcedelete foo.db
  set rc [ catchcmd "-batch foo.db" "CREATE TABLE t1(a);" ]
  set fexist [file exist foo.db]
  list $rc $fexist
} {{0 {}} 1}

# Shell silently ignores extra parameters.
# Ticket [f5cb008a65].
do_test shell2-1.2.1 {
  catchcmdex {:memory: -list "select+3" "select+4"}
} {0 {3
4
}}

# Test a problem reported on the mailing list. The shell was at one point
# returning the generic SQLITE_ERROR message ("SQL error or missing database")
# instead of the "too many levels..." message in the test below.
#
do_test shell2-1.3 {
  catchcmd "-batch test.db" {
    PRAGMA recursive_triggers = ON;
    CREATE TABLE t5(a PRIMARY KEY, b, c);
    INSERT INTO t5 VALUES(1, 2, 3);
    CREATE TRIGGER au_tble AFTER UPDATE ON t5 BEGIN
      UPDATE OR IGNORE t5 SET a = new.a, c = 10;
    END;

    UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1;
  }
} {1 {Error near line 9: too many levels of trigger recursion}}



# Shell not echoing all commands with echo on.
# Ticket [eb620916be].

# Test with echo off
# NB. whitespace is important
do_test shell2-1.4.1 {
  forcedelete foo.db
  catchcmd "foo.db" {.mode batch
CREATE TABLE foo(a);
INSERT INTO foo(a) VALUES(1);
SELECT * FROM foo;}
} {0 1}

# Test with echo on using command line option
# NB. whitespace is important
do_test shell2-1.4.2 {
  forcedelete foo.db
  catchcmd "-echo foo.db" {CREATE TABLE foo(a);
INSERT INTO foo(a) VALUES(1);
SELECT * FROM foo;}
} {0 {CREATE TABLE foo(a);
INSERT INTO foo(a) VALUES(1);
SELECT * FROM foo;
1}}

# Test with echo on using dot command
# NB. whitespace is important
do_test shell2-1.4.3 {
  forcedelete foo.db
  catchcmd "foo.db" {
.mode batch
.echo ON
CREATE TABLE foo(a);
INSERT INTO foo(a) VALUES(1);
SELECT * FROM foo;}
} {0 {CREATE TABLE foo(a);
INSERT INTO foo(a) VALUES(1);
SELECT * FROM foo;
1}}

# Test with echo on using dot command and 
# turning off mid- processing.
# NB. whitespace is important
do_test shell2-1.4.4 {
  forcedelete foo.db
  catchcmd "foo.db" {
.mode batch
.echo ON
CREATE TABLE foo(a);
.echo OFF
INSERT INTO foo(a) VALUES(1);
SELECT * FROM foo;}
} {0 {CREATE TABLE foo(a);
.echo OFF
1}}

# Test with echo on using dot command and 
# multiple commands per line.
# NB. whitespace is important
do_test shell2-1.4.5 {
  forcedelete foo.db
  catchcmdex "foo.db" {
.mode batch
.echo ON
CREATE TABLE foo1(a);
INSERT INTO foo1(a) VALUES(1);
CREATE TABLE foo2(b);
INSERT INTO foo2(b) VALUES(1);
SELECT * FROM foo1; SELECT * FROM foo2;
INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);
SELECT * FROM foo1; SELECT * FROM foo2;
}
} {0 {CREATE TABLE foo1(a);
INSERT INTO foo1(a) VALUES(1);
CREATE TABLE foo2(b);
INSERT INTO foo2(b) VALUES(1);
SELECT * FROM foo1; SELECT * FROM foo2;
1
1
INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);
SELECT * FROM foo1; SELECT * FROM foo2;
1
2
1
2
}}

# Test with echo on and headers on using dot command and 
# multiple commands per line.
# NB. whitespace is important
do_test shell2-1.4.6 {
  forcedelete foo.db
  catchcmdex "foo.db" {
.mode batch
.echo ON
.headers ON
CREATE TABLE foo1(a);
INSERT INTO foo1(a) VALUES(1);
CREATE TABLE foo2(b);
INSERT INTO foo2(b) VALUES(1);
SELECT * FROM foo1; SELECT * FROM foo2;
INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);
SELECT * FROM foo1; SELECT * FROM foo2;
}
} {0 {.headers ON
CREATE TABLE foo1(a);
INSERT INTO foo1(a) VALUES(1);
CREATE TABLE foo2(b);
INSERT INTO foo2(b) VALUES(1);
SELECT * FROM foo1; SELECT * FROM foo2;
a
1
b
1
INSERT INTO foo1(a) VALUES(2); INSERT INTO foo2(b) VALUES(2);
SELECT * FROM foo1; SELECT * FROM foo2;
a
1
2
b
1
2
}}

# Test for rejection of incomplete input at EOF.
# Reported at https://sqlite.org/forum/forumpost/718f489a43be3197
do_test shell2-1.4.7 {
  catchcmd ":memory:" {
 SELECT 'unclosed;}
} {1 {Parse error near line 2: unrecognized token: "'unclosed;"
  SELECT 'unclosed;
         ^--- error here}}

# Verify that safe mode rejects certain UDFs
# Reported at https://sqlite.org/forum/forumpost/07beac8056151b2f
do_test shell2-1.4.8 {
  catchcmd "-safe :memory:" {
 SELECT edit('DoNotCare');}
} {1 {line 2: cannot use the edit() function in safe mode}}
do_test shell2-1.4.9 {
  catchcmd "-safe :memory:" {
 SELECT writefile('DoNotCare', x'');}
} {1 {line 2: cannot use the writefile() function in safe mode}}

# Verify that .clone handles sequence table.
# See https://sqlite.org/forum/forumpost/71ff9e6c4c
do_test shell2-1.4.9 {
  forcedelete clone.db
  set res [catchcmd :memory: [string trim {
.mode batch
 CREATE TABLE t(id INTEGER PRIMARY KEY AUTOINCREMENT);
 INSERT INTO t VALUES (1),(2);
.clone clone.db
.open clone.db
 SELECT max(seq) FROM sqlite_sequence;}]]
} {0 {t... done
done
2}}

ifcapable vtab {
# Verify that generate_series stays sane near 64-bit range boundaries.
# See overflow report at https://sqlite.org/forum/forumpost/5d34ce5280
do_test shell2-1.4.10 {
 set res [catchcmd :memory: [string trim {
 .mode batch
 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,1);
 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,-1);
 SELECT avg(value),min(value),max(value) FROM generate_series(
  -9223372036854775808,9223372036854775807,1085102592571150095);
 SELECT * FROM generate_series(-9223372036854775808,9223372036854775807,
  9223372036854775807);
 SELECT value FROM generate_series(-4611686018427387904,
  4611686018427387904, 4611686018427387904) ORDER BY value DESC;
 SELECT * FROM generate_series(0,-2,-1);
 SELECT * FROM generate_series(0,-2);
 SELECT * FROM generate_series(0,2) LIMIT 3;}]]
} {0 {9223372036854775807
9223372036854775807
-0.5|-9223372036854775808|9223372036854775807
-9223372036854775808
-1
9223372036854775806
4611686018427387904
0
-4611686018427387904
0
-1
-2
0
1
2}}
do_test shell2-1.4.10b {
 set res [catchcmd :memory: [string trim {
 .mode tty
.print
 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,1);
 SELECT * FROM generate_series(9223372036854775807,9223372036854775807,-1);
 SELECT avg(value),min(value),max(value) FROM generate_series(
  -9223372036854775808,9223372036854775807,1085102592571150095);
 SELECT * FROM generate_series(-9223372036854775808,9223372036854775807,
  9223372036854775807);
 SELECT value FROM generate_series(-4611686018427387904,
  4611686018427387904, 4611686018427387904) ORDER BY value DESC;
 SELECT * FROM generate_series(0,-2,-1);
 SELECT * FROM generate_series(0,-2);
 SELECT * FROM generate_series(0,2) LIMIT 3;}]]
} {0 {
╭─────────────────────╮
│        value        │
╞═════════════════════╡
│ 9223372036854775807 │
╰─────────────────────╯
╭─────────────────────╮
│        value        │
╞═════════════════════╡
│ 9223372036854775807 │
╰─────────────────────╯
╭────────────┬──────────────────────┬─────────────────────╮
│ avg(value) │      min(value)      │     max(value)      │
╞════════════╪══════════════════════╪═════════════════════╡
│       -0.5 │ -9223372036854775808 │ 9223372036854775807 │
╰────────────┴──────────────────────┴─────────────────────╯
╭──────────────────────╮
│        value         │
╞══════════════════════╡
│ -9223372036854775808 │
│                   -1 │
│  9223372036854775806 │
╰──────────────────────╯
╭──────────────────────╮
│        value         │
╞══════════════════════╡
│  4611686018427387904 │
│                    0 │
│ -4611686018427387904 │
╰──────────────────────╯
╭───────╮
│ value │
╞═══════╡
│     0 │
│    -1 │
│    -2 │
╰───────╯
╭───────╮
│ value │
╞═══════╡
│     0 │
│     1 │
│     2 │
╰───────╯}}
}  ;# ifcapable vtab

ifcapable vtab {
# Bug discovered while messing around, .import hangs with
# bit 7 set in column separator.
do_test shell2-1.4.11 {
  forcedelete dummy.csv
  set df [open dummy.csv w]
  puts $df dog,cat
  close $df
  set res [catchcmd :memory: [string trim {
 CREATE TABLE t(line text);
.mode ascii -colsep "\377" -rowsep "\n"
.import dummy.csv t
 SELECT count(*) FROM t;}]]
} {1 {0
Error: .import column separator must be ASCII}}
} ;# ifcapable vtab

# Bug from forum post 7cbe081746dd3803
# Keywords as column names were producing an error message.
do_test shell2-1.4.12 {
  set res [catchcmd :memory: [string trim {.mode batch
 CREATE TABLE "group"("order" text);
 INSERT INTO "group" VALUES ('ABC');
.sha3sum}]]
} {0 ca08bc02b7e95c7df431a3a4b1cc0f8d8743914793473f55b5558e03}

#-------------------------------------------------------------------------

foreach {tn hexdump expect} {
  0 {
| size 8192 pagesize 4096 filename my.db
| page 1 offset 0
|      0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00   SQLite format 3.
|     16: 10 00 01 01 00 40 20 20 00 00 00 03 00 00 00 02   .....@  ........
|     32: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04   ................
|     48: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00   ................
|     80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 03   ................
|     96: 00 2e 8d f8 0d 00 00 00 01 0f df 00 0f df 00 00   ................
|   4048: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 1f   ................
|   4064: 01 06 17 0f 0f 01 2f 74 61 62 6c 65 74 74 02 43   ....../tablett.C
|   4080: 52 45 41 54 45 20 54 41 42 4c 45 20 74 28 78 29   REATE TABLE t(x)
| page 2 offset 4096
|      0: 0d 00 00 00 02 0f ee 00 0f f7 0f ee 00 00 00 00   ................
|   4064: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 07 02   ................
|   4080: 02 17 77 6f 72 6c 64 07 01 02 17 68 65 6c 6c 6f   ..world....hello
| end my.db
  }
  {0 {}}

  1 {
| size 2147483647 pagesize 4096 filename my.db
| page 1 offset 0
|      0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00   SQLite format 3.
|     16: 10 00 01 01 00 40 20 20 00 00 00 03 00 00 00 02   .....@  ........
|     32: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04   ................
|     48: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00   ................
|     80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 03   ................
|     96: 00 2e 8d f8 0d 00 00 00 01 0f df 00 0f df 00 00   ................
|   4048: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 1f   ................
|   4064: 01 06 17 0f 0f 01 2f 74 61 62 6c 65 74 74 02 43   ....../tablett.C
|   4080: 52 45 41 54 45 20 54 41 42 4c 45 20 74 28 78 29   REATE TABLE t(x)
| page 2 offset 4096
|      0: 0d 00 00 00 02 0f ee 00 0f f7 0f ee 00 00 00 00   ................
|   4064: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 07 02   ................
|   4080: 02 17 77 6f 72 6c 64 07 01 02 17 68 65 6c 6c 6f   ..world....hello
| end my.db
  }
  {1 {Error: out of memory}}

  2 {
| size 8192 pagesize 4096 filename my.db
| page 1 offset 2147483647
|      0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00   SQLite format 3.
|     16: 10 00 01 01 00 40 20 20 00 00 00 03 00 00 00 02   .....@  ........
|     32: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04   ................
|     48: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00   ................
|     80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 03   ................
|     96: 00 2e 8d f8 0d 00 00 00 01 0f df 00 0f df 00 00   ................
|   4048: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 1f   ................
|   4064: 01 06 17 0f 0f 01 2f 74 61 62 6c 65 74 74 02 43   ....../tablett.C
|   4080: 52 45 41 54 45 20 54 41 42 4c 45 20 74 28 78 29   REATE TABLE t(x)
| page 2 offset 4096
|      0: 0d 00 00 00 02 0f ee 00 0f f7 0f ee 00 00 00 00   ................
|   4064: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 07 02   ................
|   4080: 02 17 77 6f 72 6c 64 07 01 02 17 68 65 6c 6c 6f   ..world....hello
| end my.db
  }
  {0 {}}

 3 {
| size 8192 pagesize 4096 filename my.db
| page 1 offset 0
|      0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00   SQLite format 3.
|     16: 10 00 01 01 00 40 20 20 00 00 00 03 00 00 00 02   .....@  ........
|     32: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04   ................
|     48: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00   ................
|     80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 03   ................
|     96: 00 2e 8d f8 0d 00 00 00 01 0f df 00 0f df 00 00   ................
|   4048: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 1f   ................
|   4064: 01 06 17 0f 0f 01 2f 74 61 62 6c 65 74 74 02 43   ....../tablett.C
|   4080: 52 45 41 54 45 20 54 41 42 4c 45 20 74 28 78 29   REATE TABLE t(x)
| page 2 offset 4096
|      2147483647: 0d 00 00 00 02 0f ee 00 0f f7 0f ee 00 00 00 00   ................
|   4064: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 07 02   ................
|   4080: 02 17 77 6f 72 6c 64 07 01 02 17 68 65 6c 6c 6f   ..world....hello
| end my.db
}
  {0 {}}

} {
  set fd [open dump.txt w]
  puts $fd [string trim $hexdump]
  close $fd
  do_test shell2-2.$tn.1 {
    set rc [ catchcmd "" ".open --hexdb dump.txt"]
  } $expect
}


finish_test