# 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.
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing the COPY statement.
# $Id:
copy.test,v 1.17 2004/02/17 18:26:57 dougcurrie Exp $
set testdir [file dirname $argv0]
# Create a file of data from which to copy.
fconfigure $f -translation binary
puts -nonewline $f "11|22|33\r"
puts -nonewline $f "22|33|44\r\n"
puts -nonewline $f "33|44|55\n"
puts -nonewline $f "44|55|66\r"
puts -nonewline $f "55|66|77\r\n"
puts -nonewline $f "66|77|88\n"
# Try to COPY into a non-existant table.
set v [catch {execsql {COPY test1 FROM '
data1.txt'}} msg]
} {1 {no such table: test1}}
# Try to insert into sqlite_master
set v [catch {execsql {COPY sqlite_master FROM '
data2.txt'}} msg]
} {1 {table sqlite_master may not be modified}}
execsql {CREATE TABLE test1(one int, two int, three int)}
execsql {SELECT * FROM test1 ORDER BY one}
# Make sure input terminates at \.
execsql {DELETE FROM test1}
execsql {SELECT * FROM test1 ORDER BY one}
# Test out the USING DELIMITERS clause
execsql {DELETE FROM test1}
execsql {COPY test1 FROM '
data4.txt' USING DELIMITERS ' | '}
execsql {SELECT * FROM test1 ORDER BY one}
execsql {DELETE FROM test1}
execsql {COPY test1 FROM '
data5.txt' USING DELIMITERS '|'}
execsql {SELECT * FROM test1 ORDER BY one}
execsql {DELETE FROM test1}
execsql {COPY test1 FROM '
data4.txt' USING DELIMITERS '|'}
execsql {SELECT * FROM test1 ORDER BY one}
} {{11 } { 22 } { 33} {22 } { 33 } { 11}}
# Try copying into a table that has one or more indices.
execsql {DELETE FROM test1}
execsql {CREATE INDEX index1 ON test1(one)}
execsql {CREATE INDEX index2 ON test1(two)}
execsql {CREATE INDEX index3 ON test1(three)}
execsql {SELECT * FROM test1 WHERE one=11}
execsql {SELECT * FROM test1 WHERE one=22}
execsql {SELECT * FROM test1 WHERE two=22}
execsql {SELECT * FROM test1 WHERE three=11}
# Try inserting really long data
for {set i 0} {$i<100} {incr i} {
append x "($i)-abcdefghijklmnopqrstyvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-"
execsql {CREATE TABLE test2(a int, x text)}
execsql {SELECT x from test2}
# Test the escape character mechanism
puts $fd "hello\\\tworld\t1"
puts $fd "hello\tworld\\\t2"
CREATE TABLE t1(a text, b text);
SELECT * FROM t1 ORDER BY a;
} {hello {world 2} {hello world} 1}
puts $fd "1\thello\\\nworld"
puts $fd "2\thello world"
SELECT * FROM t1 ORDER BY a;
puts $fd "1:hello\\b\\f\\n\\r\\t\\vworld"
COPY t1 FROM '
data6.txt' USING DELIMITERS ':';
SELECT * FROM t1 ORDER BY a;
} [list 1 "hello\b\f\n\r\t\vworld" 2 "hello world"]
# Test the embedded NULL logic.
puts $fd "\\N\thello world"
SELECT * FROM t1 WHERE a IS NULL;
SELECT * FROM t1 WHERE b IS NULL;
# Test the conflict resolution logic for COPY
CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
COPY t1 FROM '
data5.txt' USING DELIMITERS '|';
COPY t1 FROM '
data6.txt' USING DELIMITERS '|';
} {1 {column b is not unique}}
COPY OR IGNORE t1 FROM '
data6.txt' USING DELIMITERS '|';
} {0 {11 22 33 22 33 11}}
COPY OR REPLACE t1 FROM '
data6.txt' USING DELIMITERS '|';
} {0 {22 33 11 33 22 44}}
COPY t1 FROM '
data5.txt' USING DELIMITERS '|';
COPY OR REPLACE t1 FROM '
data5.txt' USING DELIMITERS '|';
COPY OR IGNORE t1 FROM '
data5.txt' USING DELIMITERS '|';
PRAGMA count_changes=off;
COPY t2 FROM '
dataX.txt' USING DELIMITERS '|';
} {11 22 33 22 33 44 33 44 55 44 55 66 55 66 77 66 77 88}