# 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.13 2003/01/29 18:46:54 drh 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] 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}} # 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 {uniqueness constraint failed}} 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 # file delete -force data1.txt data2.txt data3.txt data4.txt data5.txt \ data6.txt dataX.txt finish_test