Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add tests to e_update.test. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
528f71e29c5422af778dbae2c1dce3b0 |
User & Date: | dan 2010-09-21 16:59:16.000 |
Context
2010-09-21
| ||
19:00 | Add new file e_delete.test. (check-in: 14e8659e57 user: dan tags: trunk) | |
16:59 | Add tests to e_update.test. (check-in: 528f71e29c user: dan tags: trunk) | |
2010-09-20
| ||
19:17 | Add test file e_update.test. (check-in: 03985ed4a0 user: dan tags: trunk) | |
Changes
Changes to test/e_insert.test.
︙ | ︙ | |||
34 35 36 37 38 39 40 | do_execsql_test e_insert-0.0 { CREATE TABLE a1(a, b); CREATE TABLE a2(a, b, c DEFAULT 'xyz'); CREATE TABLE a3(x DEFAULT 1.0, y DEFAULT 'string', z); CREATE TABLE a4(c UNIQUE, d); } {} | < < < < < < | 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | do_execsql_test e_insert-0.0 { CREATE TABLE a1(a, b); CREATE TABLE a2(a, b, c DEFAULT 'xyz'); CREATE TABLE a3(x DEFAULT 1.0, y DEFAULT 'string', z); CREATE TABLE a4(c UNIQUE, d); } {} proc do_insert_tests {args} { uplevel do_select_tests $args } # EVIDENCE-OF: R-41448-54465 -- syntax diagram insert-stmt # do_insert_tests e_insert-0 { |
︙ | ︙ |
Changes to test/e_update.test.
︙ | ︙ | |||
10 11 12 13 14 15 16 17 18 19 20 21 | #*********************************************************************** # # This file implements tests to verify that the "testable statements" in # the lang_update.html document are correct. # set testdir [file dirname $argv0] source $testdir/tester.tcl forcedelete test.db2 do_execsql_test e_update-0.0 { CREATE TABLE t1(a, b); | > > > > > > > > > > > > > > | < > | > | > | > | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | #*********************************************************************** # # This file implements tests to verify that the "testable statements" in # the lang_update.html document are correct. # set testdir [file dirname $argv0] source $testdir/tester.tcl #-------------------- # Test organization: # # e_update-1.*: Test statements describing the workings of UPDATE statements. # # e_update-2.*: Test the restrictions on the UPDATE statement syntax that # can be used within triggers. # # e_update-3.*: Test the special LIMIT/OFFSET and ORDER BY clauses that can # be used with UPDATE when SQLite is compiled with # SQLITE_ENABLE_UPDATE_DELETE_LIMIT. # forcedelete test.db2 do_execsql_test e_update-0.0 { ATTACH 'test.db2' AS aux; CREATE TABLE t1(a, b); CREATE TABLE t2(a, b, c); CREATE TABLE t3(a, b UNIQUE); CREATE TABLE t6(x, y); CREATE INDEX i1 ON t1(a); CREATE TEMP TABLE t4(x, y); CREATE TEMP TABLE t6(x, y); CREATE TABLE aux.t1(a, b); CREATE TABLE aux.t5(a, b); } {} proc do_update_tests {args} { uplevel do_select_tests $args } # EVIDENCE-OF: R-05685-44205 -- syntax diagram update-stmt |
︙ | ︙ | |||
183 184 185 186 187 188 189 | # EVIDENCE-OF: R-40598-36595 For each affected row, the named columns # are set to the values found by evaluating the corresponding scalar # expressions. # # EVIDENCE-OF: R-40472-60438 Columns that do not appear in the list of # assignments are left unmodified. # | | | | 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 | # EVIDENCE-OF: R-40598-36595 For each affected row, the named columns # are set to the values found by evaluating the corresponding scalar # expressions. # # EVIDENCE-OF: R-40472-60438 Columns that do not appear in the list of # assignments are left unmodified. # do_execsql_test e_update-1.5.0 { INSERT INTO t2(rowid, a, b, c) VALUES(1, 3, 1, 4); INSERT INTO t2(rowid, a, b, c) VALUES(2, 1, 5, 9); INSERT INTO t2(rowid, a, b, c) VALUES(3, 2, 6, 5); } {} do_update_tests e_update-1.5 -query { SELECT * FROM t2 } { 1 "UPDATE t2 SET c = 1+1 WHERE a=2" {3 1 4 1 5 9 2 6 2} 2 "UPDATE t2 SET b = 4/2, c=CAST((0.4*5) AS INTEGER) WHERE a<3" {3 1 4 1 2 2 2 2 2} |
︙ | ︙ | |||
217 218 219 220 221 222 223 | {3 1 4 1 5 9 2 6 5} } # EVIDENCE-OF: R-09060-20018 If a single column-name appears more than # once in the list of assignment expressions, all but the rightmost # occurence is ignored. # | | | | | | 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 | {3 1 4 1 5 9 2 6 5} } # EVIDENCE-OF: R-09060-20018 If a single column-name appears more than # once in the list of assignment expressions, all but the rightmost # occurence is ignored. # do_update_tests e_update-1.6 -query { SELECT * FROM t2 } { 1 "UPDATE t2 SET c=5, c=6, c=7 WHERE rowid=1" {3 1 7 1 5 9 2 6 5} 2 "UPDATE t2 SET c=7, c=6, c=5 WHERE rowid=1" {3 1 5 1 5 9 2 6 5} 3 "UPDATE t2 SET c=5, b=6, c=7 WHERE rowid=1" {3 6 7 1 5 9 2 6 5} } # EVIDENCE-OF: R-36239-04077 The scalar expressions may refer to columns # of the row being updated. # # EVIDENCE-OF: R-04558-24451 In this case all scalar expressions are # evaluated before any assignments are made. # do_execsql_test e_update-1.7.0 { DELETE FROM t2; INSERT INTO t2(rowid, a, b, c) VALUES(1, 3, 1, 4); INSERT INTO t2(rowid, a, b, c) VALUES(2, 1, 5, 9); INSERT INTO t2(rowid, a, b, c) VALUES(3, 2, 6, 5); } {} do_update_tests e_update-1.7 -query { SELECT * FROM t2 } { 1 "UPDATE t2 SET a=b+c" {5 1 4 14 5 9 11 6 5} 2 "UPDATE t2 SET a=b, b=a" {1 5 4 5 14 9 6 11 5} 3 "UPDATE t2 SET a=c||c, c=NULL" {44 5 {} 99 14 {} 55 11 {}} } # EVIDENCE-OF: R-12619-24112 The optional conflict-clause allows the # user to nominate a specific constraint conflict resolution algorithm # to use during this one UPDATE command. # do_execsql_test e_update-1.8.0 { DELETE FROM t3; INSERT INTO t3 VALUES(1, 'one'); INSERT INTO t3 VALUES(2, 'two'); INSERT INTO t3 VALUES(3, 'three'); INSERT INTO t3 VALUES(4, 'four'); } {} foreach {tn sql error ac data } { |
︙ | ︙ | |||
292 293 294 295 296 297 298 | 11 "UPDATE OR REPLACE t3 SET b='four' WHERE a=3" {} 0 {2 two 3 four} 12 "UPDATE OR ROLLBACK t3 SET b='four'" {column b is not unique} 1 {2 three 3 one 4 four} } { | | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 | 11 "UPDATE OR REPLACE t3 SET b='four' WHERE a=3" {} 0 {2 two 3 four} 12 "UPDATE OR ROLLBACK t3 SET b='four'" {column b is not unique} 1 {2 three 3 one 4 four} } { do_catchsql_test e_update-1.8.$tn.1 $sql [list [expr {$error!=""}] $error] do_execsql_test e_update-1.8.$tn.2 {SELECT * FROM t3} [list {*}$data] do_test e_update-1.8.$tn.3 {sqlite3_get_autocommit db} $ac } # EVIDENCE-OF: R-12123-54095 The table-name specified as part of an # UPDATE statement within a trigger body must be unqualified. # # EVIDENCE-OF: R-09690-36749 In other words, the database-name. prefix # on the table name of the UPDATE is not allowed within triggers. # do_update_tests e_update-2.1 -error { qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers } { 1 { CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN UPDATE main.t2 SET a=1, b=2, c=3; END; } {} 2 { CREATE TRIGGER tr1 BEFORE UPDATE ON t2 BEGIN UPDATE aux.t1 SET a=1, b=2; END; } {} 3 { CREATE TRIGGER tr1 AFTER DELETE ON t4 BEGIN UPDATE main.t1 SET a=1, b=2; END; } {} } # EVIDENCE-OF: R-06085-13761 Unless the table to which the trigger is # attached is in the TEMP database, the table being updated by the # trigger program must reside in the same database as it. # do_update_tests e_update-2.2 -error { no such table: %s } { 1 { CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN UPDATE t4 SET x=x+1; END; INSERT INTO t1 VALUES(1, 2); } "main.t4" 2 { CREATE TRIGGER aux.tr1 AFTER INSERT ON t5 BEGIN UPDATE t4 SET x=x+1; END; INSERT INTO t5 VALUES(1, 2); } "aux.t4" } do_execsql_test e_update-2.2.X { DROP TRIGGER tr1; DROP TRIGGER aux.tr1; } {} # EVIDENCE-OF: R-29512-54644 If the table to which the trigger is # attached is in the TEMP database, then the unqualified name of the # table being updated is resolved in the same way as it is for a # top-level statement (by searching first the TEMP database, then the # main database, then any other databases in the order they were # attached). # do_execsql_test e_update-2.3.0 { SELECT 'main', tbl_name FROM main.sqlite_master WHERE type = 'table' UNION ALL SELECT 'temp', tbl_name FROM sqlite_temp_master WHERE type = 'table' UNION ALL SELECT 'aux', tbl_name FROM aux.sqlite_master WHERE type = 'table' } [list {*}{ main t1 main t2 main t3 main t6 temp t4 temp t6 aux t1 aux t5 }] do_execsql_test e_update-2.3.1 { DELETE FROM main.t6; DELETE FROM temp.t6; INSERT INTO main.t6 VALUES(1, 2); INSERT INTO temp.t6 VALUES(1, 2); CREATE TRIGGER temp.tr1 AFTER INSERT ON t4 BEGIN UPDATE t6 SET x=x+1; END; INSERT INTO t4 VALUES(1, 2); SELECT * FROM main.t6; SELECT * FROM temp.t6; } {1 2 2 2} do_execsql_test e_update-2.3.2 { DELETE FROM main.t1; DELETE FROM aux.t1; INSERT INTO main.t1 VALUES(1, 2); INSERT INTO aux.t1 VALUES(1, 2); CREATE TRIGGER temp.tr2 AFTER DELETE ON t4 BEGIN UPDATE t1 SET a=a+1; END; DELETE FROM t4; SELECT * FROM main.t1; SELECT * FROM aux.t1; } {2 2 1 2} do_execsql_test e_update-2.3.3 { DELETE FROM aux.t5; INSERT INTO aux.t5 VALUES(1, 2); INSERT INTO t4 VALUES('x', 'y'); CREATE TRIGGER temp.tr3 AFTER UPDATE ON t4 BEGIN UPDATE t5 SET a=a+1; END; UPDATE t4 SET x=10; SELECT * FROM aux.t5; } {2 2} # EVIDENCE-OF: R-19619-42762 The INDEXED BY and NOT INDEXED clauses are # not allowed on UPDATE statements within triggers. # do_update_tests e_update-2.4 -error { the %s %s clause is not allowed on UPDATE or DELETE statements within triggers } { 1 { CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN UPDATE t1 INDEXED BY i1 SET a=a+1; END; } {INDEXED BY} 2 { CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN UPDATE t1 NOT INDEXED SET a=a+1; END; } {NOT INDEXED} } ifcapable update_delete_limit { # EVIDENCE-OF: R-57359-59558 The LIMIT and ORDER BY clauses for UPDATE # are unsupported within triggers, regardless of the compilation options # used to build SQLite. # do_update_tests e_update-2.5 -error { near "%s": syntax error } { 1 { CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN UPDATE t1 SET a=a+1 LIMIT 10; END; } {LIMIT} 2 { CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN UPDATE t1 SET a=a+1 ORDER BY a LIMIT 10; END; } {ORDER} 3 { CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN UPDATE t1 SET a=a+1 ORDER BY a LIMIT 10 OFFSET 2; END; } {ORDER} 4 { CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN UPDATE t1 SET a=a+1 LIMIT 10 OFFSET 2; END; } {LIMIT} } # EVIDENCE-OF: R-59581-44104 If SQLite is built with the # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax # of the UPDATE statement is extended with optional ORDER BY and LIMIT # clauses # # EVIDENCE-OF: R-07033-64910 -- syntax diagram update-stmt-limited # do_update_tests e_update-3.0 { 1 "UPDATE t1 SET a=b LIMIT 5" {} 2 "UPDATE t1 SET a=b LIMIT 5-1 OFFSET 2+2" {} 3 "UPDATE t1 SET a=b LIMIT 2+2, 16/4" {} 4 "UPDATE t1 SET a=b ORDER BY a LIMIT 5" {} 5 "UPDATE t1 SET a=b ORDER BY a LIMIT 5-1 OFFSET 2+2" {} 6 "UPDATE t1 SET a=b ORDER BY a LIMIT 2+2, 16/4" {} 7 "UPDATE t1 SET a=b WHERE a>2 LIMIT 5" {} 8 "UPDATE t1 SET a=b WHERE a>2 LIMIT 5-1 OFFSET 2+2" {} 9 "UPDATE t1 SET a=b WHERE a>2 LIMIT 2+2, 16/4" {} 10 "UPDATE t1 SET a=b WHERE a>2 ORDER BY a LIMIT 5" {} 11 "UPDATE t1 SET a=b WHERE a>2 ORDER BY a LIMIT 5-1 OFFSET 2+2" {} 12 "UPDATE t1 SET a=b WHERE a>2 ORDER BY a LIMIT 2+2, 16/4" {} } do_execsql_test e_update-3.1.0 { CREATE TABLE t7(q, r, s); INSERT INTO t7 VALUES(1, 'one', 'X'); INSERT INTO t7 VALUES(2, 'two', 'X'); INSERT INTO t7 VALUES(3, 'three', 'X'); INSERT INTO t7 VALUES(4, 'four', 'X'); INSERT INTO t7 VALUES(5, 'five', 'X'); INSERT INTO t7 VALUES(6, 'six', 'X'); INSERT INTO t7 VALUES(7, 'seven', 'X'); INSERT INTO t7 VALUES(8, 'eight', 'X'); INSERT INTO t7 VALUES(9, 'nine', 'X'); INSERT INTO t7 VALUES(10, 'ten', 'X'); } {} # EVIDENCE-OF: R-58862-44169 If an UPDATE statement has a LIMIT clause, # the maximum number of rows that will be updated is found by evaluating # the accompanying expression and casting it to an integer value. # do_update_tests e_update-3.1 -query { SELECT s FROM t7 } { 1 "UPDATE t7 SET s = q LIMIT 5" {1 2 3 4 5 X X X X X} 2 "UPDATE t7 SET s = r WHERE q>2 LIMIT 4" {1 2 three four five six X X X X} 3 "UPDATE t7 SET s = q LIMIT 0" {1 2 three four five six X X X X} } # EVIDENCE-OF: R-63582-45120 A negative value is interpreted as "no limit". # do_update_tests e_update-3.2 -query { SELECT s FROM t7 } { 1 "UPDATE t7 SET s = q LIMIT -1" {1 2 3 4 5 6 7 8 9 10} 2 "UPDATE t7 SET s = r WHERE q>4 LIMIT -1" {1 2 3 4 five six seven eight nine ten} 3 "UPDATE t7 SET s = 'X' LIMIT -1" {X X X X X X X X X X} } # EVIDENCE-OF: R-18628-11938 If the LIMIT expression evaluates to # non-negative value N and the UPDATE statement has an ORDER BY clause, # then all rows that would be updated in the absence of the LIMIT clause # are sorted according to the ORDER BY and the first N updated. # do_update_tests e_update-3.3 -query { SELECT s FROM t7 } { 1 "UPDATE t7 SET s = q ORDER BY r LIMIT 3" {X X X 4 5 X X 8 X X} 2 "UPDATE t7 SET s = r ORDER BY r DESC LIMIT 2" {X two three 4 5 X X 8 X X} 3 "UPDATE t7 SET s = q ORDER BY q DESC LIMIT 5" {X two three 4 5 6 7 8 9 10} X "UPDATE t7 SET s = 'X'" {X X X X X X X X X X} } # EVIDENCE-OF: R-30955-38324 If the UPDATE statement also has an OFFSET # clause, then it is similarly evaluated and cast to an integer value. # If the OFFSET expression evaluates to a non-negative value M, then the # first M rows are skipped and the following N rows updated instead. # do_update_tests e_update-3.3 -query { SELECT s FROM t7 } { 1 "UPDATE t7 SET s = q ORDER BY q LIMIT 3 OFFSET 2" {X X 3 4 5 X X X X X} 2 "UPDATE t7 SET s = q ORDER BY q DESC LIMIT 2, 3 " {X X 3 4 5 6 7 8 X X} X "UPDATE t7 SET s = 'X'" {X X X X X X X X X X} } # EVIDENCE-OF: R-19486-35828 If the UPDATE statement has no ORDER BY # clause, then all rows that would be updated in the absence of the # LIMIT clause are assembled in an arbitrary order before applying the # LIMIT and OFFSET clauses to determine which are actually updated. # # In practice, "arbitrary order" is rowid order. This is also tested # by e_update-3.2.* above. # do_update_tests e_update-3.4 -query { SELECT s FROM t7 } { 1 "UPDATE t7 SET s = q LIMIT 4, 2" {X X X X 5 6 X X X X} 2 "UPDATE t7 SET s = q LIMIT 2 OFFSET 7" {X X X X 5 6 X 8 9 X} } # EVIDENCE-OF: R-10927-26133 The ORDER BY clause on an UPDATE statement # is used only to determine which rows fall within the LIMIT. The order # in which rows are modified is arbitrary and is not influenced by the # ORDER BY clause. # do_execsql_test e_update-3.5.0 { CREATE TABLE t8(x); CREATE TRIGGER tr7 BEFORE UPDATE ON t7 BEGIN INSERT INTO t8 VALUES(old.q); END; } {} do_update_tests e_update-3.5 -query { SELECT x FROM t8 ; DELETE FROM t8 } { 1 "UPDATE t7 SET s = q ORDER BY r LIMIT -1" {1 2 3 4 5 6 7 8 9 10} 2 "UPDATE t7 SET s = q ORDER BY r ASC LIMIT -1" {1 2 3 4 5 6 7 8 9 10} 3 "UPDATE t7 SET s = q ORDER BY r DESC LIMIT -1" {1 2 3 4 5 6 7 8 9 10} 4 "UPDATE t7 SET s = q ORDER BY q DESC LIMIT 5" {6 7 8 9 10} } } ;# ifcapable update_delete_limit finish_test |
Changes to test/tester.tcl.
︙ | ︙ | |||
398 399 400 401 402 403 404 405 406 407 408 409 410 411 | } else { set res [list 1 [string trim [format $errfmt {*}$res]]] uplevel do_catchsql_test ${prefix}.${tn} [list $sql] [list $res] } } } # Run an SQL script. # Return the number of microseconds per statement. # proc speed_trial {name numstmt units sql} { puts -nonewline [format {%-21.21s } $name...] flush stdout | > > > > > | 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 | } else { set res [list 1 [string trim [format $errfmt {*}$res]]] uplevel do_catchsql_test ${prefix}.${tn} [list $sql] [list $res] } } } proc delete_all_data {} { db eval {SELECT tbl_name AS t FROM sqlite_master WHERE type = 'table'} { db eval "DELETE FROM '[string map {' ''} $t]'" } } # Run an SQL script. # Return the number of microseconds per statement. # proc speed_trial {name numstmt units sql} { puts -nonewline [format {%-21.21s } $name...] flush stdout |
︙ | ︙ |