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