1 files changed, 268 insertions, 0 deletions
diff --git a/usr/src/lib/libsqlite/test/copy.test b/usr/src/lib/libsqlite/test/copy.test
new file mode 100644
index 0000000000..68fa7f8fd2
--- /dev/null
+++ b/usr/src/lib/libsqlite/test/copy.test
@@ -0,0 +1,268 @@
+
+#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 
+#
+#file delete -force data1.txt data2.txt data3.txt data4.txt data5.txt \
+                   data6.txt dataX.txt
+
+finish_test