1#
2# 2001 September 15
3#
4# The author disclaims copyright to this source code.  In place of
5# a legal notice, here is a blessing:
6#
7#    May you do good and not evil.
8#    May you find forgiveness for yourself and forgive others.
9#    May you share freely, never taking more than you give.
10#
11#***********************************************************************
12# This file implements regression tests for SQLite library.  The
13# focus of this file is testing the COPY statement.
14#
15# $Id: copy.test,v 1.17 2004/02/17 18:26:57 dougcurrie Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# Create a file of data from which to copy.
21#
22set f [open data1.txt w]
23puts $f "11\t22\t33"
24puts $f "22\t33\t11"
25close $f
26set f [open data2.txt w]
27puts $f "11\t22\t33"
28puts $f "\\."
29puts $f "22\t33\t11"
30close $f
31set f [open data3.txt w]
32puts $f "11\t22\t33\t44"
33puts $f "22\t33\t11"
34close $f
35set f [open data4.txt w]
36puts $f "11 | 22 | 33"
37puts $f "22 | 33 | 11"
38close $f
39set f [open data5.txt w]
40puts $f "11|22|33"
41puts $f "22|33|11"
42close $f
43set f [open dataX.txt w]
44fconfigure $f -translation binary
45puts -nonewline $f "11|22|33\r"
46puts -nonewline $f "22|33|44\r\n"
47puts -nonewline $f "33|44|55\n"
48puts -nonewline $f "44|55|66\r"
49puts -nonewline $f "55|66|77\r\n"
50puts -nonewline $f "66|77|88\n"
51close $f
52
53# Try to COPY into a non-existant table.
54#
55do_test copy-1.1 {
56  set v [catch {execsql {COPY test1 FROM 'data1.txt'}} msg]
57  lappend v $msg
58} {1 {no such table: test1}}
59
60# Try to insert into sqlite_master
61#
62do_test copy-1.2 {
63  set v [catch {execsql {COPY sqlite_master FROM 'data2.txt'}} msg]
64  lappend v $msg
65} {1 {table sqlite_master may not be modified}}
66
67# Do some actual inserts
68#
69do_test copy-1.3 {
70  execsql {CREATE TABLE test1(one int, two int, three int)}
71  execsql {COPY test1 FROM 'data1.txt'}
72  execsql {SELECT * FROM test1 ORDER BY one}
73} {11 22 33 22 33 11}
74
75# Make sure input terminates at \.
76#
77do_test copy-1.4 {
78  execsql {DELETE FROM test1}
79  execsql {COPY test1 FROM 'data2.txt'}
80  execsql {SELECT * FROM test1 ORDER BY one}
81} {11 22 33}
82
83# Test out the USING DELIMITERS clause
84#
85do_test copy-1.5 {
86  execsql {DELETE FROM test1}
87  execsql {COPY test1 FROM 'data4.txt' USING DELIMITERS ' | '}
88  execsql {SELECT * FROM test1 ORDER BY one}
89} {11 22 33 22 33 11}
90do_test copy-1.6 {
91  execsql {DELETE FROM test1}
92  execsql {COPY test1 FROM 'data5.txt' USING DELIMITERS '|'}
93  execsql {SELECT * FROM test1 ORDER BY one}
94} {11 22 33 22 33 11}
95do_test copy-1.7 {
96  execsql {DELETE FROM test1}
97  execsql {COPY test1 FROM 'data4.txt' USING DELIMITERS '|'}
98  execsql {SELECT * FROM test1 ORDER BY one}
99} {{11 } { 22 } { 33} {22 } { 33 } { 11}}
100
101# Try copying into a table that has one or more indices.
102#
103do_test copy-1.8 {
104  execsql {DELETE FROM test1}
105  execsql {CREATE INDEX index1 ON test1(one)}
106  execsql {CREATE INDEX index2 ON test1(two)}
107  execsql {CREATE INDEX index3 ON test1(three)}
108  execsql {COPY test1 from 'data1.txt'}
109  execsql {SELECT * FROM test1 WHERE one=11}
110} {11 22 33}
111do_test copy-1.8b {
112  execsql {SELECT * FROM test1 WHERE one=22}
113} {22 33 11}
114do_test copy-1.8c {
115  execsql {SELECT * FROM test1 WHERE two=22}
116} {11 22 33}
117do_test copy-1.8d {
118  execsql {SELECT * FROM test1 WHERE three=11}
119} {22 33 11}
120
121
122# Try inserting really long data
123#
124set x {}
125for {set i 0} {$i<100} {incr i} {
126  append x "($i)-abcdefghijklmnopqrstyvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-"
127}
128do_test copy-2.1 {
129  execsql {CREATE TABLE test2(a int, x text)}
130  set f [open data21.txt w]
131  puts $f "123\t$x"
132  close $f
133  execsql {COPY test2 FROM 'data21.txt'}
134  execsql {SELECT x from test2}
135} $x
136file delete -force data21.txt
137
138# Test the escape character mechanism
139#
140do_test copy-3.1 {
141  set fd [open data6.txt w]
142  puts $fd "hello\\\tworld\t1"
143  puts $fd "hello\tworld\\\t2"
144  close $fd
145  execsql {
146    CREATE TABLE t1(a text, b text);
147    COPY t1 FROM 'data6.txt';
148    SELECT * FROM t1 ORDER BY a;
149  }
150} {hello {world	2} {hello	world} 1}
151do_test copy-3.2 {
152  set fd [open data6.txt w]
153  puts $fd "1\thello\\\nworld"
154  puts $fd "2\thello world"
155  close $fd
156  execsql {
157    DELETE FROM t1;
158    COPY t1 FROM 'data6.txt';
159    SELECT * FROM t1 ORDER BY a;
160  }
161} {1 {hello
162world} 2 {hello world}}
163do_test copy-3.3 {
164  set fd [open data6.txt w]
165  puts $fd "1:hello\\b\\f\\n\\r\\t\\vworld"
166  puts $fd "2:hello world"
167  close $fd
168  execsql {
169    DELETE FROM t1;
170    COPY t1 FROM 'data6.txt' USING DELIMITERS ':';
171    SELECT * FROM t1 ORDER BY a;
172  }
173} [list 1 "hello\b\f\n\r\t\vworld" 2 "hello world"]
174
175# Test the embedded NULL logic.
176#
177do_test copy-4.1 {
178  set fd [open data6.txt w]
179  puts $fd "1\t\\N"
180  puts $fd "\\N\thello world"
181  close $fd
182  execsql {
183    DELETE FROM t1;
184    COPY t1 FROM 'data6.txt';
185    SELECT * FROM t1 WHERE a IS NULL;
186  }
187} {{} {hello world}}
188do_test copy-4.2 {
189  execsql {
190    SELECT * FROM t1 WHERE b IS NULL;
191  }
192} {1 {}}
193
194# Test the conflict resolution logic for COPY
195#
196do_test copy-5.1 {
197  execsql {
198    DROP TABLE t1;
199    CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
200    COPY t1 FROM 'data5.txt' USING DELIMITERS '|';
201    SELECT * FROM t1;
202  }
203} {11 22 33 22 33 11}
204do_test copy-5.2 {
205  set fd [open data6.txt w]
206  puts $fd "33|22|44"
207  close $fd
208  catchsql {
209    COPY t1 FROM 'data6.txt' USING DELIMITERS '|';
210    SELECT * FROM t1;
211  }
212} {1 {column b is not unique}}
213do_test copy-5.3 {
214  set fd [open data6.txt w]
215  puts $fd "33|22|44"
216  close $fd
217  catchsql {
218    COPY OR IGNORE t1 FROM 'data6.txt' USING DELIMITERS '|';
219    SELECT * FROM t1;
220  }
221} {0 {11 22 33 22 33 11}}
222do_test copy-5.4 {
223  set fd [open data6.txt w]
224  puts $fd "33|22|44"
225  close $fd
226  catchsql {
227    COPY OR REPLACE t1 FROM 'data6.txt' USING DELIMITERS '|';
228    SELECT * FROM t1;
229  }
230} {0 {22 33 11 33 22 44}}
231
232do_test copy-5.5 {
233  execsql {
234    DELETE FROM t1;
235    PRAGMA count_changes=on;
236    COPY t1 FROM 'data5.txt' USING DELIMITERS '|';
237  }
238} {2}
239do_test copy-5.6 {
240  execsql {
241    COPY OR REPLACE t1 FROM 'data5.txt' USING DELIMITERS '|';
242  }
243} {2}
244do_test copy-5.7 {
245  execsql {
246    COPY OR IGNORE t1 FROM 'data5.txt' USING DELIMITERS '|';
247  }
248} {0}
249
250do_test copy-6.1 {
251  execsql {
252    PRAGMA count_changes=off;
253    CREATE TABLE t2(a,b,c);
254    COPY t2 FROM 'dataX.txt' USING DELIMITERS '|';
255    SELECT * FROM t2;
256  }
257} {11 22 33 22 33 44 33 44 55 44 55 66 55 66 77 66 77 88}
258
259integrity_check copy-7.1
260
261# Cleanup
262#
263#file delete -force data1.txt data2.txt data3.txt data4.txt data5.txt \
264                   data6.txt dataX.txt
265
266finish_test
267