mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-16 10:18:47 +00:00
276 lines
7.2 KiB
Plaintext
276 lines
7.2 KiB
Plaintext
# 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.
|
|
#
|
|
#***********************************************************************
|
|
#
|
|
# 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: "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 {Runtime 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" {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" {.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" {.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" {.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" {.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 {
|
|
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}}
|
|
|
|
# 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 {
|
|
SELECT * FROM generate_series(9223372036854775807,9223372036854775807,1);
|
|
SELECT * FROM generate_series(9223372036854775807,9223372036854775807,-1);
|
|
SELECT avg(rowid),min(value),max(value) FROM generate_series(
|
|
-9223372036854775808,9223372036854775807,1085102592571150095);
|
|
SELECT * FROM generate_series(-9223372036854775808,9223372036854775807,
|
|
9223372036854775807);
|
|
SELECT value,rowid 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
|
|
9.5|-9223372036854775808|9223372036854775807
|
|
-9223372036854775808
|
|
-1
|
|
9223372036854775806
|
|
4611686018427387904|3
|
|
0|2
|
|
-4611686018427387904|1
|
|
0
|
|
-1
|
|
-2
|
|
0
|
|
1
|
|
2}}
|
|
|
|
# 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
|
|
.separator "\377" "\n"
|
|
.import dummy.csv t
|
|
SELECT count(*) FROM t;}]]
|
|
} {0 1}
|
|
|
|
# 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 {
|
|
CREATE TABLE "group"("order" text);
|
|
INSERT INTO "group" VALUES ('ABC');
|
|
.sha3sum}]]
|
|
} {0 ca08bc02b7e95c7df431a3a4b1cc0f8d8743914793473f55b5558e03}
|
|
|
|
|
|
finish_test
|