#pragma ident "%Z%%M% %I% %E% SMI"
# 2001 September 15
#
# 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]
source $testdir/tester.tcl
# Create a file of data from which to copy.
#
set f [open data1.txt w]
puts $f "11\t22\t33"
puts $f "22\t33\t11"
close $f
set f [open data2.txt w]
puts $f "11\t22\t33"
puts $f "\\."
puts $f "22\t33\t11"
close $f
set f [open data3.txt w]
puts $f "11\t22\t33\t44"
puts $f "22\t33\t11"
close $f
set f [open data4.txt w]
puts $f "11 | 22 | 33"
puts $f "22 | 33 | 11"
close $f
set f [open data5.txt w]
puts $f "11|22|33"
puts $f "22|33|11"
close $f
set f [open dataX.txt w]
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"
close $f
# Try to COPY into a non-existant table.
#
do_test copy-1.1 {
set v [catch {execsql {COPY test1 FROM 'data1.txt'}} msg]
lappend v $msg
} {1 {no such table: test1}}
# Try to insert into sqlite_master
#
do_test copy-1.2 {
set v [catch {execsql {COPY sqlite_master FROM 'data2.txt'}} msg]
lappend v $msg
} {1 {table sqlite_master may not be modified}}
# Do some actual inserts
#
do_test copy-1.3 {
execsql {CREATE TABLE test1(one int, two int, three int)}
execsql {COPY test1 FROM 'data1.txt'}
execsql {SELECT * FROM test1 ORDER BY one}
} {11 22 33 22 33 11}
# Make sure input terminates at \.
#
do_test copy-1.4 {
execsql {DELETE FROM test1}
execsql {COPY test1 FROM 'data2.txt'}
execsql {SELECT * FROM test1 ORDER BY one}
} {11 22 33}
# Test out the USING DELIMITERS clause
#
do_test copy-1.5 {
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}
do_test copy-1.6 {
execsql {DELETE FROM test1}
execsql {COPY test1 FROM 'data5.txt' USING DELIMITERS '|'}
execsql {SELECT * FROM test1 ORDER BY one}
} {11 22 33 22 33 11}
do_test copy-1.7 {
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.
#
do_test copy-1.8 {
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 {COPY test1 from 'data1.txt'}
execsql {SELECT * FROM test1 WHERE one=11}
} {11 22 33}
do_test copy-1.8b {
execsql {SELECT * FROM test1 WHERE one=22}
} {22 33 11}
do_test copy-1.8c {
execsql {SELECT * FROM test1 WHERE two=22}
} {11 22 33}
do_test copy-1.8d {
execsql {SELECT * FROM test1 WHERE three=11}
} {22 33 11}
# Try inserting really long data
#
set x {}
for {set i 0} {$i<100} {incr i} {
append x "($i)-abcdefghijklmnopqrstyvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-"
}
do_test copy-2.1 {
execsql {CREATE TABLE test2(a int, x text)}
set f [open data21.txt w]
puts $f "123\t$x"
close $f
execsql {COPY test2 FROM 'data21.txt'}
execsql {SELECT x from test2}
} $x
file delete -force data21.txt
# Test the escape character mechanism
#
do_test copy-3.1 {
set fd [open data6.txt w]
puts $fd "hello\\\tworld\t1"
puts $fd "hello\tworld\\\t2"
close $fd
execsql {
CREATE TABLE t1(a text, b text);
COPY t1 FROM 'data6.txt';
SELECT * FROM t1 ORDER BY a;
}
} {hello {world 2} {hello world} 1}
do_test copy-3.2 {
set fd [open data6.txt w]
puts $fd "1\thello\\\nworld"
puts $fd "2\thello world"
close $fd
execsql {
DELETE FROM t1;
COPY t1 FROM 'data6.txt';
SELECT * FROM t1 ORDER BY a;
}
} {1 {hello
world} 2 {hello world}}
do_test copy-3.3 {
set fd [open data6.txt w]
puts $fd "1:hello\\b\\f\\n\\r\\t\\vworld"
puts $fd "2:hello world"
close $fd
execsql {
DELETE FROM t1;
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.
#
do_test copy-4.1 {
set fd [open data6.txt w]
puts $fd "1\t\\N"
puts $fd "\\N\thello world"
close $fd
execsql {
DELETE FROM t1;
COPY t1 FROM 'data6.txt';
SELECT * FROM t1 WHERE a IS NULL;
}
} {{} {hello world}}
do_test copy-4.2 {
execsql {
SELECT * FROM t1 WHERE b IS NULL;
}
} {1 {}}
# Test the conflict resolution logic for COPY
#
do_test copy-5.1 {
execsql {
DROP TABLE t1;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
COPY t1 FROM 'data5.txt' USING DELIMITERS '|';
SELECT * FROM t1;
}
} {11 22 33 22 33 11}
do_test copy-5.2 {
set fd [open data6.txt w]
puts $fd "33|22|44"
close $fd
catchsql {
COPY t1 FROM 'data6.txt' USING DELIMITERS '|';
SELECT * FROM t1;
}
} {1 {column b is not unique}}
do_test copy-5.3 {
set fd [open data6.txt w]
puts $fd "33|22|44"
close $fd
catchsql {
COPY OR IGNORE t1 FROM 'data6.txt' USING DELIMITERS '|';
SELECT * FROM t1;
}
} {0 {11 22 33 22 33 11}}
do_test copy-5.4 {
set fd [open data6.txt w]
puts $fd "33|22|44"
close $fd
catchsql {
COPY OR REPLACE t1 FROM 'data6.txt' USING DELIMITERS '|';
SELECT * FROM t1;
}
} {0 {22 33 11 33 22 44}}
do_test copy-5.5 {
execsql {
DELETE FROM t1;
PRAGMA count_changes=on;
COPY t1 FROM 'data5.txt' USING DELIMITERS '|';
}
} {2}
do_test copy-5.6 {
execsql {
COPY OR REPLACE t1 FROM 'data5.txt' USING DELIMITERS '|';
}
} {2}
do_test copy-5.7 {
execsql {
COPY OR IGNORE t1 FROM 'data5.txt' USING DELIMITERS '|';
}
} {0}
do_test copy-6.1 {
execsql {
PRAGMA count_changes=off;
CREATE TABLE t2(a,b,c);
COPY t2 FROM 'dataX.txt' USING DELIMITERS '|';
SELECT * FROM t2;
}
} {11 22 33 22 33 44 33 44 55 44 55 66 55 66 77 66 77 88}
integrity_check copy-7.1
# Cleanup
#
finish_test