2N/A#pragma ident "%Z%%M% %I% %E% SMI"
2N/A# The author disclaims copyright to this source code. In place of
2N/A# a legal notice, here is a blessing:
2N/A# May you do good and not evil.
2N/A# May you find forgiveness for yourself and forgive others.
2N/A# May you share freely, never taking more than you give.
2N/A#***********************************************************************
2N/A# This file implements regression tests for SQLite library. The
2N/A# focus of this file is testing the COPY statement.
2N/Aset testdir [file dirname $argv0]
2N/A# Create a file of data from which to copy.
2N/Aputs $f "11\t22\t33\t44"
2N/Aputs $f "11 | 22 | 33"
2N/Aputs $f "22 | 33 | 11"
2N/Afconfigure $f -translation binary
2N/Aputs -nonewline $f "11|22|33\r"
2N/Aputs -nonewline $f "22|33|44\r\n"
2N/Aputs -nonewline $f "33|44|55\n"
2N/Aputs -nonewline $f "44|55|66\r"
2N/Aputs -nonewline $f "55|66|77\r\n"
2N/Aputs -nonewline $f "66|77|88\n"
2N/A# Try to COPY into a non-existant table.
2N/A} {1 {no such table: test1}}
2N/A# Try to insert into sqlite_master
2N/A set v [catch {execsql {COPY sqlite_master FROM '
data2.txt'}} msg]
2N/A} {1 {table sqlite_master may not be modified}}
2N/A# Do some actual inserts
2N/A execsql {CREATE TABLE test1(one int, two int, three int)}
2N/A execsql {SELECT * FROM test1 ORDER BY one}
2N/A} {11 22 33 22 33 11}
2N/A# Make sure input terminates at \.
2N/A execsql {DELETE FROM test1}
2N/A execsql {SELECT * FROM test1 ORDER BY one}
2N/A# Test out the USING DELIMITERS clause
2N/A execsql {DELETE FROM test1}
2N/A execsql {COPY test1 FROM '
data4.txt' USING DELIMITERS ' | '}
2N/A execsql {SELECT * FROM test1 ORDER BY one}
2N/A} {11 22 33 22 33 11}
2N/A execsql {DELETE FROM test1}
2N/A execsql {SELECT * FROM test1 ORDER BY one}
2N/A} {11 22 33 22 33 11}
2N/A execsql {DELETE FROM test1}
2N/A execsql {SELECT * FROM test1 ORDER BY one}
2N/A} {{11 } { 22 } { 33} {22 } { 33 } { 11}}
2N/A# Try copying into a table that has one or more indices.
2N/A execsql {DELETE FROM test1}
2N/A execsql {CREATE INDEX index1 ON test1(one)}
2N/A execsql {CREATE INDEX index2 ON test1(two)}
2N/A execsql {CREATE INDEX index3 ON test1(three)}
2N/A execsql {SELECT * FROM test1 WHERE one=11}
2N/A execsql {SELECT * FROM test1 WHERE one=22}
2N/A execsql {SELECT * FROM test1 WHERE two=22}
2N/A execsql {SELECT * FROM test1 WHERE three=11}
2N/A# Try inserting really long data
2N/Afor {set i 0} {$i<100} {incr i} {
2N/A append x "($i)-abcdefghijklmnopqrstyvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-"
2N/A execsql {CREATE TABLE test2(a int, x text)}
2N/A execsql {SELECT x from test2}
2N/A# Test the escape character mechanism
2N/A puts $fd "hello\\\tworld\t1"
2N/A puts $fd "hello\tworld\\\t2"
2N/A CREATE TABLE t1(a text, b text);
2N/A SELECT * FROM t1 ORDER BY a;
2N/A} {hello {world 2} {hello world} 1}
2N/A puts $fd "1\thello\\\nworld"
2N/A puts $fd "2\thello world"
2N/A SELECT * FROM t1 ORDER BY a;
2N/Aworld} 2 {hello world}}
2N/A puts $fd "1:hello\\b\\f\\n\\r\\t\\vworld"
2N/A puts $fd "2:hello world"
2N/A SELECT * FROM t1 ORDER BY a;
2N/A} [list 1 "hello\b\f\n\r\t\vworld" 2 "hello world"]
2N/A# Test the embedded NULL logic.
2N/A puts $fd "\\N\thello world"
2N/A SELECT * FROM t1 WHERE a IS NULL;
2N/A SELECT * FROM t1 WHERE b IS NULL;
2N/A# Test the conflict resolution logic for COPY
2N/A CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
2N/A} {11 22 33 22 33 11}
2N/A} {1 {column b is not unique}}
2N/A} {0 {11 22 33 22 33 11}}
2N/A} {0 {22 33 11 33 22 44}}
2N/A PRAGMA count_changes=on;
2N/A PRAGMA count_changes=off;
2N/A CREATE TABLE t2(a,b,c);
2N/A} {11 22 33 22 33 44 33 44 55 44 55 66 55 66 77 66 77 88}
2N/Aintegrity_check copy-7.1