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 UPDATE statement. 25# 26# $Id: update.test,v 1.4 2000/06/21 13:59:14 drh Exp $ 27 28set testdir [file dirname $argv0] 29source $testdir/tester.tcl 30 31# Try to update an non-existent table 32# 33do_test update-1.1 { 34 set v [catch {execsql {UPDATE test1 SET f2=5 WHERE f1<1}} msg] 35 lappend v $msg 36} {1 {no such table: test1}} 37 38# Try to update a read-only table 39# 40do_test update-2.1 { 41 set v [catch \ 42 {execsql {UPDATE sqlite_master SET name='xyz' WHERE name='123'}} msg] 43 lappend v $msg 44} {1 {table sqlite_master may not be modified}} 45 46# Create a table to work with 47# 48do_test update-3.1 { 49 execsql {CREATE TABLE test1(f1 int,f2 int)} 50 for {set i 1} {$i<=10} {incr i} { 51 set sql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])" 52 execsql $sql 53 } 54 execsql {SELECT * FROM test1 ORDER BY f1} 55} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024} 56 57# Unknown column name in an expression 58# 59do_test update-3.2 { 60 set v [catch {execsql {UPDATE test1 SET f1=f3*2 WHERE f2==32}} msg] 61 lappend v $msg 62} {1 {no such column: f3}} 63do_test update-3.3 { 64 set v [catch {execsql {UPDATE test1 SET f1=test2.f1*2 WHERE f2==32}} msg] 65 lappend v $msg 66} {1 {no such column: test2.f1}} 67do_test update-3.4 { 68 set v [catch {execsql {UPDATE test1 SET f3=f1*2 WHERE f2==32}} msg] 69 lappend v $msg 70} {1 {no such column: f3}} 71 72# Actually do some updates 73# 74do_test update-3.5 { 75 execsql {UPDATE test1 SET f2=f2*3} 76 execsql {SELECT * FROM test1 ORDER BY f1} 77} {1 6 2 12 3 24 4 48 5 96 6 192 7 384 8 768 9 1536 10 3072} 78do_test update-3.6 { 79 execsql {UPDATE test1 SET f2=f2/3 WHERE f1<=5} 80 execsql {SELECT * FROM test1 ORDER BY f1} 81} {1 2 2 4 3 8 4 16 5 32 6 192 7 384 8 768 9 1536 10 3072} 82do_test update-3.7 { 83 execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5} 84 execsql {SELECT * FROM test1 ORDER BY f1} 85} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024} 86 87# Swap the values of f1 and f2 for all elements 88# 89do_test update-3.8 { 90 execsql {UPDATE test1 SET F2=f1, F1=f2} 91 execsql {SELECT * FROM test1 ORDER BY F1} 92} {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1024 10} 93 94# Create an index and make sure updating works with an index. 95# 96do_test update-3.9 { 97 execsql {CREATE INDEX index1 ON test1(f1)} 98 execsql {CREATE INDEX index2 ON test1(f1)} 99 execsql {UPDATE test1 SET f1=1025 WHERE f2==10} 100 execsql {SELECT * FROM test1 ORDER BY f1} 101} {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1025 10} 102do_test update-3.10 { 103 execsql {SELECT * FROM test1 WHERE f1=1025} 104} {1025 10} 105do_test update-3.11 { 106 execsql {UPDATE test1 SET f2=11 WHERE f1==1025} 107 execsql {SELECT * FROM test1 ORDER BY f1} 108} {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1025 11} 109do_test update-3.12 { 110 execsql {SELECT * FROM test1 WHERE f1=1025} 111} {1025 11} 112 113# Error messages 114# 115do_test update-4.1 { 116 set v [catch {execsql { 117 UPDATE test1 SET x=11 WHERE f1=1025 118 }} msg] 119 lappend v $msg 120} {1 {no such column: x}} 121do_test update-4.2 { 122 set v [catch {execsql { 123 UPDATE test1 SET f1=x(11) WHERE f1=1025 124 }} msg] 125 lappend v $msg 126} {1 {no such function: x}} 127do_test update-4.3 { 128 set v [catch {execsql { 129 UPDATE test1 SET f1=11 WHERE x=1025 130 }} msg] 131 lappend v $msg 132} {1 {no such column: x}} 133do_test update-4.4 { 134 set v [catch {execsql { 135 UPDATE test1 SET f1=11 WHERE x(f1)=1025 136 }} msg] 137 lappend v $msg 138} {1 {no such function: x}} 139 140 141 142 143 144 145finish_test 146