1# Copyright (c) 1999, 2000 D. Richard Hipp 2# 3# This program is free software; you can redistribute it and/or 4# modify it under the terms of the GNU General Public 5# License as published by the Free Software Foundation; either 6# version 2 of the License, or (at your option) any later version. 7# 8# This program is distributed in the hope that it will be useful, 9# but WITHOUT ANY WARRANTY; without even the implied warranty of 10# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 11# General Public License for more details. 12# 13# You should have received a copy of the GNU General Public 14# License along with this library; if not, write to the 15# Free Software Foundation, Inc., 59 Temple Place - Suite 330, 16# Boston, MA 02111-1307, USA. 17# 18# Author contact information: 19# [email protected] 20# http://www.hwaci.com/drh/ 21# 22#*********************************************************************** 23# This file implements regression tests for SQLite library. The 24# focus of this file is testing the INSERT statement. 25# 26# $Id: insert.test,v 1.4 2000/06/07 14:42:27 drh Exp $ 27 28set testdir [file dirname $argv0] 29source $testdir/tester.tcl 30 31# Try to insert into a non-existant table. 32# 33do_test insert-1.1 { 34 set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3)}} msg] 35 lappend v $msg 36} {1 {no such table: test1}} 37 38# Try to insert into sqlite_master 39# 40do_test insert-1.2 { 41 set v [catch {execsql {INSERT INTO sqlite_master VALUES(1,2,3,4)}} msg] 42 lappend v $msg 43} {1 {table sqlite_master may not be modified}} 44 45# Try to insert the wrong number of entries. 46# 47do_test insert-1.3 { 48 execsql {CREATE TABLE test1(one int, two int, three int)} 49 set v [catch {execsql {INSERT INTO test1 VALUES(1,2)}} msg] 50 lappend v $msg 51} {1 {table test1 has 3 columns but 2 values were supplied}} 52do_test insert-1.3b { 53 set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3,4)}} msg] 54 lappend v $msg 55} {1 {table test1 has 3 columns but 4 values were supplied}} 56do_test insert-1.3c { 57 set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1,2,3,4)}} msg] 58 lappend v $msg 59} {1 {4 values for 2 columns}} 60do_test insert-1.3d { 61 set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1)}} msg] 62 lappend v $msg 63} {1 {1 values for 2 columns}} 64 65# Try to insert into a non-existant column of a table. 66# 67do_test insert-1.4 { 68 set v [catch {execsql {INSERT INTO test1(one,four) VALUES(1,2)}} msg] 69 lappend v $msg 70} {1 {table test1 has no column named four}} 71 72# Make sure the inserts actually happen 73# 74do_test insert-1.5 { 75 execsql {INSERT INTO test1 VALUES(1,2,3)} 76 execsql {SELECT * FROM test1} 77} {1 2 3} 78do_test insert-1.5b { 79 execsql {INSERT INTO test1 VALUES(4,5,6)} 80 execsql {SELECT * FROM test1 ORDER BY one} 81} {1 2 3 4 5 6} 82do_test insert-1.5c { 83 execsql {INSERT INTO test1 VALUES(7,8,9)} 84 execsql {SELECT * FROM test1 ORDER BY one} 85} {1 2 3 4 5 6 7 8 9} 86 87do_test insert-1.6 { 88 execsql {DELETE FROM test1} 89 execsql {INSERT INTO test1(one,two) VALUES(1,2)} 90 execsql {SELECT * FROM test1 ORDER BY one} 91} {1 2 {}} 92do_test insert-1.6b { 93 execsql {INSERT INTO test1(two,three) VALUES(5,6)} 94 execsql {SELECT * FROM test1 ORDER BY one} 95} {{} 5 6 1 2 {}} 96do_test insert-1.6c { 97 execsql {INSERT INTO test1(three,one) VALUES(7,8)} 98 execsql {SELECT * FROM test1 ORDER BY one} 99} {{} 5 6 1 2 {} 8 {} 7} 100 101# A table to use for testing default values 102# 103execsql { 104 CREATE TABLE test2( 105 f1 int default 111, 106 f2 real default -4.32, 107 f3 text default hi, 108 f4 text default 'abc-123', 109 f5 varchar(10) 110 ) 111} 112 113do_test insert-2.1 { 114 execsql {SELECT * from test2} 115} {} 116do_test insert-2.2 { 117 execsql {INSERT INTO test2(f2,f4) VALUES(-2.22,'hi!')} 118 execsql {SELECT * FROM test2} 119} {111 -2.22 hi hi! {}} 120do_test insert-2.3 { 121 execsql {INSERT INTO test2(f1,f5) VALUES(1,'xyzzy')} 122 execsql {SELECT * FROM test2 ORDER BY f1} 123} {1 -4.32 hi abc-123 xyzzy 111 -2.22 hi hi! {}} 124 125# Do additional inserts with default values, but this time 126# on a table that has indices. In particular we want to verify 127# that the correct default values are inserted into the indices. 128# 129do_test insert-3.1 { 130 execsql { 131 DELETE FROM test2; 132 CREATE INDEX index9 ON test2(f1,f2); 133 CREATE INDEX indext ON test2(f4,f5); 134 SELECT * from test2; 135 } 136} {} 137do_test insert-3.2 { 138 execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')} 139 execsql {SELECT * FROM test2 WHERE f1=111 AND f2=-3.33} 140} {111 -3.33 hi hum {}} 141do_test insert-3.3 { 142 execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')} 143 execsql {SELECT * FROM test2 WHERE f1=111 AND f2=-3.33} 144} {111 -3.33 hi hum {}} 145do_test insert-3.4 { 146 execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44} 147} {22 -4.44 hi abc-123 wham} 148 149finish_test 150