Annotation of embedaddon/sqlite3/test/capi2.test, revision 1.1
1.1 ! misho 1: # 2003 January 29
! 2: #
! 3: # The author disclaims copyright to this source code. In place of
! 4: # a legal notice, here is a blessing:
! 5: #
! 6: # May you do good and not evil.
! 7: # May you find forgiveness for yourself and forgive others.
! 8: # May you share freely, never taking more than you give.
! 9: #
! 10: #***********************************************************************
! 11: # This file implements regression tests for SQLite library. The
! 12: # focus of this script testing the callback-free C/C++ API.
! 13: #
! 14: # $Id: capi2.test,v 1.37 2008/12/30 17:55:00 drh Exp $
! 15: #
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: # Return the text values from the current row pointed at by STMT as a list.
! 21: proc get_row_values {STMT} {
! 22: set VALUES [list]
! 23: for {set i 0} {$i < [sqlite3_data_count $STMT]} {incr i} {
! 24: lappend VALUES [sqlite3_column_text $STMT $i]
! 25: }
! 26: return $VALUES
! 27: }
! 28:
! 29: # Return the column names followed by declaration types for the result set
! 30: # of the SQL statement STMT.
! 31: #
! 32: # i.e. for:
! 33: # CREATE TABLE abc(a text, b integer);
! 34: # SELECT * FROM abc;
! 35: #
! 36: # The result is {a b text integer}
! 37: proc get_column_names {STMT} {
! 38: set VALUES [list]
! 39: for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
! 40: lappend VALUES [sqlite3_column_name $STMT $i]
! 41: }
! 42: for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
! 43: lappend VALUES [sqlite3_column_decltype $STMT $i]
! 44: }
! 45: return $VALUES
! 46: }
! 47:
! 48: # Check basic functionality
! 49: #
! 50: do_test capi2-1.1 {
! 51: set DB [sqlite3_connection_pointer db]
! 52: execsql {CREATE TABLE t1(a,b,c)}
! 53: set VM [sqlite3_prepare $DB {SELECT name, rowid FROM sqlite_master} -1 TAIL]
! 54: set TAIL
! 55: } {}
! 56: do_test capi2-1.2 {
! 57: sqlite3_step $VM
! 58: } {SQLITE_ROW}
! 59: do_test capi2-1.3 {
! 60: sqlite3_data_count $VM
! 61: } {2}
! 62: do_test capi2-1.4 {
! 63: get_row_values $VM
! 64: } {t1 1}
! 65: do_test capi2-1.5 {
! 66: get_column_names $VM
! 67: } {name rowid text INTEGER}
! 68: do_test capi2-1.6 {
! 69: sqlite3_step $VM
! 70: } {SQLITE_DONE}
! 71: do_test capi2-1.7 {
! 72: list [sqlite3_column_count $VM] [get_row_values $VM] [get_column_names $VM]
! 73: } {2 {} {name rowid text INTEGER}}
! 74:
! 75: # This used to be SQLITE_MISUSE. But now we automatically reset prepared
! 76: # statements.
! 77: ifcapable autoreset {
! 78: do_test capi2-1.8 {
! 79: sqlite3_step $VM
! 80: } {SQLITE_ROW}
! 81: } else {
! 82: do_test capi2-1.8 {
! 83: sqlite3_step $VM
! 84: } {SQLITE_MISUSE}
! 85: }
! 86:
! 87: # Update: In v2, once SQLITE_MISUSE is returned the statement handle cannot
! 88: # be interrogated for more information. However in v3, since the column
! 89: # count, names and types are determined at compile time, these are still
! 90: # accessible after an SQLITE_MISUSE error.
! 91: do_test capi2-1.9 {
! 92: sqlite3_reset $VM
! 93: list [sqlite3_column_count $VM] [get_row_values $VM] [get_column_names $VM]
! 94: } {2 {} {name rowid text INTEGER}}
! 95: do_test capi2-1.10 {
! 96: sqlite3_data_count $VM
! 97: } {0}
! 98:
! 99: do_test capi2-1.11 {
! 100: sqlite3_finalize $VM
! 101: } {SQLITE_OK}
! 102:
! 103: # Check to make sure that the "tail" of a multi-statement SQL script
! 104: # is returned by sqlite3_prepare.
! 105: #
! 106: do_test capi2-2.1 {
! 107: set SQL {
! 108: SELECT name, rowid FROM sqlite_master;
! 109: SELECT name, rowid FROM sqlite_master WHERE 0;
! 110: -- A comment at the end
! 111: }
! 112: set VM [sqlite3_prepare $DB $SQL -1 SQL]
! 113: set SQL
! 114: } {
! 115: SELECT name, rowid FROM sqlite_master WHERE 0;
! 116: -- A comment at the end
! 117: }
! 118: do_test capi2-2.2 {
! 119: set r [sqlite3_step $VM]
! 120: lappend r [sqlite3_column_count $VM] \
! 121: [get_row_values $VM] \
! 122: [get_column_names $VM]
! 123: } {SQLITE_ROW 2 {t1 1} {name rowid text INTEGER}}
! 124: do_test capi2-2.3 {
! 125: set r [sqlite3_step $VM]
! 126: lappend r [sqlite3_column_count $VM] \
! 127: [get_row_values $VM] \
! 128: [get_column_names $VM]
! 129: } {SQLITE_DONE 2 {} {name rowid text INTEGER}}
! 130: do_test capi2-2.4 {
! 131: sqlite3_finalize $VM
! 132: } {SQLITE_OK}
! 133: do_test capi2-2.5 {
! 134: set VM [sqlite3_prepare $DB $SQL -1 SQL]
! 135: set SQL
! 136: } {
! 137: -- A comment at the end
! 138: }
! 139: do_test capi2-2.6 {
! 140: set r [sqlite3_step $VM]
! 141: lappend r [sqlite3_column_count $VM] \
! 142: [get_row_values $VM] \
! 143: [get_column_names $VM]
! 144: } {SQLITE_DONE 2 {} {name rowid text INTEGER}}
! 145: do_test capi2-2.7 {
! 146: sqlite3_finalize $VM
! 147: } {SQLITE_OK}
! 148: do_test capi2-2.8 {
! 149: set VM [sqlite3_prepare $DB $SQL -1 SQL]
! 150: list $SQL $VM
! 151: } {{} {}}
! 152:
! 153: # Check the error handling.
! 154: #
! 155: do_test capi2-3.1 {
! 156: set rc [catch {
! 157: sqlite3_prepare $DB {select bogus from sqlite_master} -1 TAIL
! 158: } msg]
! 159: lappend rc $msg $TAIL
! 160: } {1 {(1) no such column: bogus} {}}
! 161: do_test capi2-3.2 {
! 162: set rc [catch {
! 163: sqlite3_prepare $DB {select bogus from } -1 TAIL
! 164: } msg]
! 165: lappend rc $msg $TAIL
! 166: } {1 {(1) near " ": syntax error} {}}
! 167: do_test capi2-3.3 {
! 168: set rc [catch {
! 169: sqlite3_prepare $DB {;;;;select bogus from sqlite_master} -1 TAIL
! 170: } msg]
! 171: lappend rc $msg $TAIL
! 172: } {1 {(1) no such column: bogus} {}}
! 173: do_test capi2-3.4 {
! 174: set rc [catch {
! 175: sqlite3_prepare $DB {select bogus from sqlite_master;x;} -1 TAIL
! 176: } msg]
! 177: lappend rc $msg $TAIL
! 178: } {1 {(1) no such column: bogus} {x;}}
! 179: do_test capi2-3.5 {
! 180: set rc [catch {
! 181: sqlite3_prepare $DB {select bogus from sqlite_master;;;x;} -1 TAIL
! 182: } msg]
! 183: lappend rc $msg $TAIL
! 184: } {1 {(1) no such column: bogus} {;;x;}}
! 185: do_test capi2-3.6 {
! 186: set rc [catch {
! 187: sqlite3_prepare $DB {select 5/0} -1 TAIL
! 188: } VM]
! 189: lappend rc $TAIL
! 190: } {0 {}}
! 191: do_test capi2-3.7 {
! 192: list [sqlite3_step $VM] \
! 193: [sqlite3_column_count $VM] \
! 194: [get_row_values $VM] \
! 195: [get_column_names $VM]
! 196: } {SQLITE_ROW 1 {{}} {5/0 {}}}
! 197: do_test capi2-3.8 {
! 198: sqlite3_finalize $VM
! 199: } {SQLITE_OK}
! 200: do_test capi2-3.9 {
! 201: execsql {CREATE UNIQUE INDEX i1 ON t1(a)}
! 202: set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(1,2,3)} -1 TAIL]
! 203: set TAIL
! 204: } {}
! 205: do_test capi2-3.9b {db changes} {0}
! 206: do_test capi2-3.10 {
! 207: list [sqlite3_step $VM] \
! 208: [sqlite3_column_count $VM] \
! 209: [get_row_values $VM] \
! 210: [get_column_names $VM]
! 211: } {SQLITE_DONE 0 {} {}}
! 212:
! 213: # Update for v3 - the change has not actually happened until the query is
! 214: # finalized. Is this going to cause trouble for anyone? Lee Nelson maybe?
! 215: # (Later:) The change now happens just before SQLITE_DONE is returned.
! 216: do_test capi2-3.10b {db changes} {1}
! 217: do_test capi2-3.11 {
! 218: sqlite3_finalize $VM
! 219: } {SQLITE_OK}
! 220: do_test capi2-3.11b {db changes} {1}
! 221: #do_test capi2-3.12-misuse {
! 222: # sqlite3_finalize $VM
! 223: #} {SQLITE_MISUSE}
! 224: do_test capi2-3.13 {
! 225: set VM [sqlite3_prepare $DB {INSERT INTO t1 VALUES(1,3,4)} -1 TAIL]
! 226: list [sqlite3_step $VM] \
! 227: [sqlite3_column_count $VM] \
! 228: [get_row_values $VM] \
! 229: [get_column_names $VM]
! 230: } {SQLITE_ERROR 0 {} {}}
! 231:
! 232: # Update for v3: Preparing a statement does not affect the change counter.
! 233: # (Test result changes from 0 to 1). (Later:) change counter updates occur
! 234: # when sqlite3_step returns, not at finalize time.
! 235: do_test capi2-3.13b {db changes} {0}
! 236:
! 237: do_test capi2-3.14 {
! 238: list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]
! 239: } {SQLITE_CONSTRAINT {column a is not unique}}
! 240: do_test capi2-3.15 {
! 241: set VM [sqlite3_prepare $DB {CREATE TABLE t2(a NOT NULL, b)} -1 TAIL]
! 242: set TAIL
! 243: } {}
! 244: do_test capi2-3.16 {
! 245: list [sqlite3_step $VM] \
! 246: [sqlite3_column_count $VM] \
! 247: [get_row_values $VM] \
! 248: [get_column_names $VM]
! 249: } {SQLITE_DONE 0 {} {}}
! 250: do_test capi2-3.17 {
! 251: list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]
! 252: } {SQLITE_OK {not an error}}
! 253: do_test capi2-3.18 {
! 254: set VM [sqlite3_prepare $DB {INSERT INTO t2 VALUES(NULL,2)} -1 TAIL]
! 255: list [sqlite3_step $VM] \
! 256: [sqlite3_column_count $VM] \
! 257: [get_row_values $VM] \
! 258: [get_column_names $VM]
! 259: } {SQLITE_ERROR 0 {} {}}
! 260: do_test capi2-3.19 {
! 261: list [sqlite3_finalize $VM] [sqlite3_errmsg $DB]
! 262: } {SQLITE_CONSTRAINT {t2.a may not be NULL}}
! 263:
! 264: do_test capi2-3.20 {
! 265: execsql {
! 266: CREATE TABLE a1(message_id, name , UNIQUE(message_id, name) );
! 267: INSERT INTO a1 VALUES(1, 1);
! 268: }
! 269: } {}
! 270: do_test capi2-3.21 {
! 271: set VM [sqlite3_prepare $DB {INSERT INTO a1 VALUES(1, 1)} -1 TAIL]
! 272: sqlite3_step $VM
! 273: } {SQLITE_ERROR}
! 274: do_test capi2-3.22 {
! 275: sqlite3_errcode $DB
! 276: } {SQLITE_ERROR}
! 277: do_test capi2-3.23 {
! 278: sqlite3_finalize $VM
! 279: } {SQLITE_CONSTRAINT}
! 280: do_test capi2-3.24 {
! 281: sqlite3_errcode $DB
! 282: } {SQLITE_CONSTRAINT}
! 283:
! 284: # Two or more virtual machines exists at the same time.
! 285: #
! 286: do_test capi2-4.1 {
! 287: set VM1 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(1,2)} -1 TAIL]
! 288: set TAIL
! 289: } {}
! 290: do_test capi2-4.2 {
! 291: set VM2 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(2,3)} -1 TAIL]
! 292: set TAIL
! 293: } {}
! 294: do_test capi2-4.3 {
! 295: set VM3 [sqlite3_prepare $DB {INSERT INTO t2 VALUES(3,4)} -1 TAIL]
! 296: set TAIL
! 297: } {}
! 298: do_test capi2-4.4 {
! 299: list [sqlite3_step $VM2] \
! 300: [sqlite3_column_count $VM2] \
! 301: [get_row_values $VM2] \
! 302: [get_column_names $VM2]
! 303: } {SQLITE_DONE 0 {} {}}
! 304: do_test capi2-4.5 {
! 305: execsql {SELECT * FROM t2 ORDER BY a}
! 306: } {2 3}
! 307: do_test capi2-4.6 {
! 308: sqlite3_finalize $VM2
! 309: } {SQLITE_OK}
! 310: do_test capi2-4.7 {
! 311: list [sqlite3_step $VM3] \
! 312: [sqlite3_column_count $VM3] \
! 313: [get_row_values $VM3] \
! 314: [get_column_names $VM3]
! 315: } {SQLITE_DONE 0 {} {}}
! 316: do_test capi2-4.8 {
! 317: execsql {SELECT * FROM t2 ORDER BY a}
! 318: } {2 3 3 4}
! 319: do_test capi2-4.9 {
! 320: sqlite3_finalize $VM3
! 321: } {SQLITE_OK}
! 322: do_test capi2-4.10 {
! 323: list [sqlite3_step $VM1] \
! 324: [sqlite3_column_count $VM1] \
! 325: [get_row_values $VM1] \
! 326: [get_column_names $VM1]
! 327: } {SQLITE_DONE 0 {} {}}
! 328: do_test capi2-4.11 {
! 329: execsql {SELECT * FROM t2 ORDER BY a}
! 330: } {1 2 2 3 3 4}
! 331: do_test capi2-4.12 {
! 332: sqlite3_finalize $VM1
! 333: } {SQLITE_OK}
! 334:
! 335: # Interleaved SELECTs
! 336: #
! 337: do_test capi2-5.1 {
! 338: set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
! 339: set VM2 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
! 340: set VM3 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
! 341: list [sqlite3_step $VM1] \
! 342: [sqlite3_column_count $VM1] \
! 343: [get_row_values $VM1] \
! 344: [get_column_names $VM1]
! 345: } {SQLITE_ROW 2 {2 3} {a b {} {}}}
! 346: do_test capi2-5.2 {
! 347: list [sqlite3_step $VM2] \
! 348: [sqlite3_column_count $VM2] \
! 349: [get_row_values $VM2] \
! 350: [get_column_names $VM2]
! 351: } {SQLITE_ROW 2 {2 3} {a b {} {}}}
! 352: do_test capi2-5.3 {
! 353: list [sqlite3_step $VM1] \
! 354: [sqlite3_column_count $VM1] \
! 355: [get_row_values $VM1] \
! 356: [get_column_names $VM1]
! 357: } {SQLITE_ROW 2 {3 4} {a b {} {}}}
! 358: do_test capi2-5.4 {
! 359: list [sqlite3_step $VM3] \
! 360: [sqlite3_column_count $VM3] \
! 361: [get_row_values $VM3] \
! 362: [get_column_names $VM3]
! 363: } {SQLITE_ROW 2 {2 3} {a b {} {}}}
! 364: do_test capi2-5.5 {
! 365: list [sqlite3_step $VM3] \
! 366: [sqlite3_column_count $VM3] \
! 367: [get_row_values $VM3] \
! 368: [get_column_names $VM3]
! 369: } {SQLITE_ROW 2 {3 4} {a b {} {}}}
! 370: do_test capi2-5.6 {
! 371: list [sqlite3_step $VM3] \
! 372: [sqlite3_column_count $VM3] \
! 373: [get_row_values $VM3] \
! 374: [get_column_names $VM3]
! 375: } {SQLITE_ROW 2 {1 2} {a b {} {}}}
! 376: do_test capi2-5.7 {
! 377: list [sqlite3_step $VM3] \
! 378: [sqlite3_column_count $VM3] \
! 379: [get_row_values $VM3] \
! 380: [get_column_names $VM3]
! 381: } {SQLITE_DONE 2 {} {a b {} {}}}
! 382: do_test capi2-5.8 {
! 383: sqlite3_finalize $VM3
! 384: } {SQLITE_OK}
! 385: do_test capi2-5.9 {
! 386: list [sqlite3_step $VM1] \
! 387: [sqlite3_column_count $VM1] \
! 388: [get_row_values $VM1] \
! 389: [get_column_names $VM1]
! 390: } {SQLITE_ROW 2 {1 2} {a b {} {}}}
! 391: do_test capi2-5.10 {
! 392: sqlite3_finalize $VM1
! 393: } {SQLITE_OK}
! 394: do_test capi2-5.11 {
! 395: list [sqlite3_step $VM2] \
! 396: [sqlite3_column_count $VM2] \
! 397: [get_row_values $VM2] \
! 398: [get_column_names $VM2]
! 399: } {SQLITE_ROW 2 {3 4} {a b {} {}}}
! 400: do_test capi2-5.12 {
! 401: list [sqlite3_step $VM2] \
! 402: [sqlite3_column_count $VM2] \
! 403: [get_row_values $VM2] \
! 404: [get_column_names $VM2]
! 405: } {SQLITE_ROW 2 {1 2} {a b {} {}}}
! 406: do_test capi2-5.11 {
! 407: sqlite3_finalize $VM2
! 408: } {SQLITE_OK}
! 409:
! 410: # Check for proper SQLITE_BUSY returns.
! 411: #
! 412: do_test capi2-6.1 {
! 413: execsql {
! 414: BEGIN;
! 415: CREATE TABLE t3(x counter);
! 416: INSERT INTO t3 VALUES(1);
! 417: INSERT INTO t3 VALUES(2);
! 418: INSERT INTO t3 SELECT x+2 FROM t3;
! 419: INSERT INTO t3 SELECT x+4 FROM t3;
! 420: INSERT INTO t3 SELECT x+8 FROM t3;
! 421: COMMIT;
! 422: }
! 423: set VM1 [sqlite3_prepare $DB {SELECT * FROM t3} -1 TAIL]
! 424: sqlite3 db2 test.db
! 425: execsql {BEGIN} db2
! 426: } {}
! 427: # Update for v3: BEGIN doesn't write-lock the database. It is quite
! 428: # difficult to get v3 to write-lock the database, which causes a few
! 429: # problems for test scripts.
! 430: #
! 431: # do_test capi2-6.2 {
! 432: # list [sqlite3_step $VM1] \
! 433: # [sqlite3_column_count $VM1] \
! 434: # [get_row_values $VM1] \
! 435: # [get_column_names $VM1]
! 436: # } {SQLITE_BUSY 0 {} {}}
! 437: do_test capi2-6.3 {
! 438: execsql {COMMIT} db2
! 439: } {}
! 440: do_test capi2-6.4 {
! 441: list [sqlite3_step $VM1] \
! 442: [sqlite3_column_count $VM1] \
! 443: [get_row_values $VM1] \
! 444: [get_column_names $VM1]
! 445: } {SQLITE_ROW 1 1 {x counter}}
! 446: do_test capi2-6.5 {
! 447: catchsql {INSERT INTO t3 VALUES(10);} db2
! 448: } {1 {database is locked}}
! 449: do_test capi2-6.6 {
! 450: list [sqlite3_step $VM1] \
! 451: [sqlite3_column_count $VM1] \
! 452: [get_row_values $VM1] \
! 453: [get_column_names $VM1]
! 454: } {SQLITE_ROW 1 2 {x counter}}
! 455: do_test capi2-6.7 {
! 456: execsql {SELECT * FROM t2} db2
! 457: } {2 3 3 4 1 2}
! 458: do_test capi2-6.8 {
! 459: list [sqlite3_step $VM1] \
! 460: [sqlite3_column_count $VM1] \
! 461: [get_row_values $VM1] \
! 462: [get_column_names $VM1]
! 463: } {SQLITE_ROW 1 3 {x counter}}
! 464: do_test capi2-6.9 {
! 465: execsql {SELECT * FROM t2}
! 466: } {2 3 3 4 1 2}
! 467: do_test capi2-6.10 {
! 468: list [sqlite3_step $VM1] \
! 469: [sqlite3_column_count $VM1] \
! 470: [get_row_values $VM1] \
! 471: [get_column_names $VM1]
! 472: } {SQLITE_ROW 1 4 {x counter}}
! 473: do_test capi2-6.11 {
! 474: execsql {BEGIN}
! 475: } {}
! 476: do_test capi2-6.12 {
! 477: list [sqlite3_step $VM1] \
! 478: [sqlite3_column_count $VM1] \
! 479: [get_row_values $VM1] \
! 480: [get_column_names $VM1]
! 481: } {SQLITE_ROW 1 5 {x counter}}
! 482:
! 483: # A read no longer blocks a write in the same connection.
! 484: #do_test capi2-6.13 {
! 485: # catchsql {UPDATE t3 SET x=x+1}
! 486: #} {1 {database table is locked}}
! 487:
! 488: do_test capi2-6.14 {
! 489: list [sqlite3_step $VM1] \
! 490: [sqlite3_column_count $VM1] \
! 491: [get_row_values $VM1] \
! 492: [get_column_names $VM1]
! 493: } {SQLITE_ROW 1 6 {x counter}}
! 494: do_test capi2-6.15 {
! 495: execsql {SELECT * FROM t1}
! 496: } {1 2 3}
! 497: do_test capi2-6.16 {
! 498: list [sqlite3_step $VM1] \
! 499: [sqlite3_column_count $VM1] \
! 500: [get_row_values $VM1] \
! 501: [get_column_names $VM1]
! 502: } {SQLITE_ROW 1 7 {x counter}}
! 503: do_test capi2-6.17 {
! 504: catchsql {UPDATE t1 SET b=b+1}
! 505: } {0 {}}
! 506: do_test capi2-6.18 {
! 507: list [sqlite3_step $VM1] \
! 508: [sqlite3_column_count $VM1] \
! 509: [get_row_values $VM1] \
! 510: [get_column_names $VM1]
! 511: } {SQLITE_ROW 1 8 {x counter}}
! 512: do_test capi2-6.19 {
! 513: execsql {SELECT * FROM t1}
! 514: } {1 3 3}
! 515: do_test capi2-6.20 {
! 516: list [sqlite3_step $VM1] \
! 517: [sqlite3_column_count $VM1] \
! 518: [get_row_values $VM1] \
! 519: [get_column_names $VM1]
! 520: } {SQLITE_ROW 1 9 {x counter}}
! 521: #do_test capi2-6.21 {
! 522: # execsql {ROLLBACK; SELECT * FROM t1}
! 523: #} {1 2 3}
! 524: do_test capi2-6.22 {
! 525: list [sqlite3_step $VM1] \
! 526: [sqlite3_column_count $VM1] \
! 527: [get_row_values $VM1] \
! 528: [get_column_names $VM1]
! 529: } {SQLITE_ROW 1 10 {x counter}}
! 530: #do_test capi2-6.23 {
! 531: # execsql {BEGIN TRANSACTION;}
! 532: #} {}
! 533: do_test capi2-6.24 {
! 534: list [sqlite3_step $VM1] \
! 535: [sqlite3_column_count $VM1] \
! 536: [get_row_values $VM1] \
! 537: [get_column_names $VM1]
! 538: } {SQLITE_ROW 1 11 {x counter}}
! 539: do_test capi2-6.25 {
! 540: execsql {
! 541: INSERT INTO t1 VALUES(2,3,4);
! 542: SELECT * FROM t1;
! 543: }
! 544: } {1 3 3 2 3 4}
! 545: do_test capi2-6.26 {
! 546: list [sqlite3_step $VM1] \
! 547: [sqlite3_column_count $VM1] \
! 548: [get_row_values $VM1] \
! 549: [get_column_names $VM1]
! 550: } {SQLITE_ROW 1 12 {x counter}}
! 551: do_test capi2-6.27 {
! 552: catchsql {
! 553: INSERT INTO t1 VALUES(2,4,5);
! 554: SELECT * FROM t1;
! 555: }
! 556: } {1 {column a is not unique}}
! 557: do_test capi2-6.28 {
! 558: list [sqlite3_step $VM1] \
! 559: [sqlite3_column_count $VM1] \
! 560: [get_row_values $VM1] \
! 561: [get_column_names $VM1]
! 562: } {SQLITE_ROW 1 13 {x counter}}
! 563: do_test capi2-6.99 {
! 564: sqlite3_finalize $VM1
! 565: } {SQLITE_OK}
! 566: catchsql {ROLLBACK}
! 567:
! 568: do_test capi2-7.1 {
! 569: stepsql $DB {
! 570: SELECT * FROM t1
! 571: }
! 572: } {0 1 2 3}
! 573: do_test capi2-7.2 {
! 574: stepsql $DB {
! 575: PRAGMA count_changes=on
! 576: }
! 577: } {0}
! 578: do_test capi2-7.3 {
! 579: stepsql $DB {
! 580: UPDATE t1 SET a=a+10;
! 581: }
! 582: } {0 1}
! 583: do_test capi2-7.4 {
! 584: stepsql $DB {
! 585: INSERT INTO t1 SELECT a+1,b+1,c+1 FROM t1;
! 586: }
! 587: } {0 1}
! 588: do_test capi2-7.4b {sqlite3_changes $DB} {1}
! 589: do_test capi2-7.5 {
! 590: stepsql $DB {
! 591: UPDATE t1 SET a=a+10;
! 592: }
! 593: } {0 2}
! 594: do_test capi2-7.5b {sqlite3_changes $DB} {2}
! 595: do_test capi2-7.6 {
! 596: stepsql $DB {
! 597: SELECT * FROM t1;
! 598: }
! 599: } {0 21 2 3 22 3 4}
! 600: do_test capi2-7.7 {
! 601: stepsql $DB {
! 602: INSERT INTO t1 SELECT a+2,b+2,c+2 FROM t1;
! 603: }
! 604: } {0 2}
! 605: do_test capi2-7.8 {
! 606: sqlite3_changes $DB
! 607: } {2}
! 608: do_test capi2-7.9 {
! 609: stepsql $DB {
! 610: SELECT * FROM t1;
! 611: }
! 612: } {0 21 2 3 22 3 4 23 4 5 24 5 6}
! 613: do_test capi2-7.10 {
! 614: stepsql $DB {
! 615: UPDATE t1 SET a=a-20;
! 616: SELECT * FROM t1;
! 617: }
! 618: } {0 4 1 2 3 2 3 4 3 4 5 4 5 6}
! 619:
! 620: # Update for version 3: A SELECT statement no longer resets the change
! 621: # counter (Test result changes from 0 to 4).
! 622: do_test capi2-7.11 {
! 623: sqlite3_changes $DB
! 624: } {4}
! 625: do_test capi2-7.11a {
! 626: execsql {SELECT count(*) FROM t1}
! 627: } {4}
! 628:
! 629: ifcapable {explain} {
! 630: do_test capi2-7.12 {
! 631: set x [stepsql $DB {EXPLAIN SELECT * FROM t1}]
! 632: lindex $x 0
! 633: } {0}
! 634: }
! 635:
! 636: # Ticket #261 - make sure we can finalize before the end of a query.
! 637: #
! 638: do_test capi2-8.1 {
! 639: set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
! 640: sqlite3_finalize $VM1
! 641: } {SQLITE_OK}
! 642:
! 643: # Tickets #384 and #385 - make sure the TAIL argument to sqlite3_prepare
! 644: # and all of the return pointers in sqlite_step can be null.
! 645: #
! 646: do_test capi2-9.1 {
! 647: set VM1 [sqlite3_prepare $DB {SELECT * FROM t2} -1 DUMMY]
! 648: sqlite3_step $VM1
! 649: sqlite3_finalize $VM1
! 650: } {SQLITE_OK}
! 651:
! 652: # Test that passing a NULL pointer to sqlite3_finalize() or sqlite3_reset
! 653: # does not cause an error.
! 654: do_test capi2-10.1 {
! 655: sqlite3_finalize 0
! 656: } {SQLITE_OK}
! 657: do_test capi2-10.2 {
! 658: sqlite3_reset 0
! 659: } {SQLITE_OK}
! 660:
! 661: #---------------------------------------------------------------------------
! 662: # The following tests - capi2-11.* - test the "column origin" APIs.
! 663: #
! 664: # sqlite3_column_origin_name()
! 665: # sqlite3_column_database_name()
! 666: # sqlite3_column_table_name()
! 667: #
! 668:
! 669: ifcapable columnmetadata {
! 670:
! 671: # This proc uses the database handle $::DB to compile the SQL statement passed
! 672: # as a parameter. The return value of this procedure is a list with one
! 673: # element for each column returned by the compiled statement. Each element of
! 674: # this list is itself a list of length three, consisting of the origin
! 675: # database, table and column for the corresponding returned column.
! 676: proc check_origins {sql} {
! 677: set ret [list]
! 678: set ::STMT [sqlite3_prepare $::DB $sql -1 dummy]
! 679: for {set i 0} {$i < [sqlite3_column_count $::STMT]} {incr i} {
! 680: lappend ret [list \
! 681: [sqlite3_column_database_name $::STMT $i] \
! 682: [sqlite3_column_table_name $::STMT $i] \
! 683: [sqlite3_column_origin_name $::STMT $i] \
! 684: ]
! 685: }
! 686: sqlite3_finalize $::STMT
! 687: return $ret
! 688: }
! 689: do_test capi2-11.1 {
! 690: execsql {
! 691: CREATE TABLE tab1(col1, col2);
! 692: }
! 693: } {}
! 694: do_test capi2-11.2 {
! 695: check_origins {SELECT col2, col1 FROM tab1}
! 696: } [list {main tab1 col2} {main tab1 col1}]
! 697: do_test capi2-11.3 {
! 698: check_origins {SELECT col2 AS hello, col1 AS world FROM tab1}
! 699: } [list {main tab1 col2} {main tab1 col1}]
! 700:
! 701: ifcapable subquery {
! 702: do_test capi2-11.4 {
! 703: check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM tab1)}
! 704: } [list {main tab1 col2} {main tab1 col1}]
! 705: do_test capi2-11.5 {
! 706: check_origins {SELECT (SELECT col2 FROM tab1), (SELECT col1 FROM tab1)}
! 707: } [list {main tab1 col2} {main tab1 col1}]
! 708: do_test capi2-11.6 {
! 709: check_origins {SELECT (SELECT col2), (SELECT col1) FROM tab1}
! 710: } [list {main tab1 col2} {main tab1 col1}]
! 711: do_test capi2-11.7 {
! 712: check_origins {SELECT * FROM tab1}
! 713: } [list {main tab1 col1} {main tab1 col2}]
! 714: do_test capi2-11.8 {
! 715: check_origins {SELECT * FROM (SELECT * FROM tab1)}
! 716: } [list {main tab1 col1} {main tab1 col2}]
! 717: }
! 718:
! 719: ifcapable view&&subquery {
! 720: do_test capi2-12.1 {
! 721: execsql {
! 722: CREATE VIEW view1 AS SELECT * FROM tab1;
! 723: }
! 724: } {}
! 725: do_test capi2-12.2 {
! 726: check_origins {SELECT col2, col1 FROM view1}
! 727: } [list {main tab1 col2} {main tab1 col1}]
! 728: do_test capi2-12.3 {
! 729: check_origins {SELECT col2 AS hello, col1 AS world FROM view1}
! 730: } [list {main tab1 col2} {main tab1 col1}]
! 731: do_test capi2-12.4 {
! 732: check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM view1)}
! 733: } [list {main tab1 col2} {main tab1 col1}]
! 734: do_test capi2-12.5 {
! 735: check_origins {SELECT (SELECT col2 FROM view1), (SELECT col1 FROM view1)}
! 736: } [list {main tab1 col2} {main tab1 col1}]
! 737: do_test capi2-12.6 {
! 738: check_origins {SELECT (SELECT col2), (SELECT col1) FROM view1}
! 739: } [list {main tab1 col2} {main tab1 col1}]
! 740: do_test capi2-12.7 {
! 741: check_origins {SELECT * FROM view1}
! 742: } [list {main tab1 col1} {main tab1 col2}]
! 743: do_test capi2-12.8 {
! 744: check_origins {select * from (select * from view1)}
! 745: } [list {main tab1 col1} {main tab1 col2}]
! 746: do_test capi2-12.9 {
! 747: check_origins {select * from (select * from (select * from view1))}
! 748: } [list {main tab1 col1} {main tab1 col2}]
! 749: do_test capi2-12.10 {
! 750: db close
! 751: sqlite3 db test.db
! 752: set ::DB [sqlite3_connection_pointer db]
! 753: check_origins {select * from (select * from (select * from view1))}
! 754: } [list {main tab1 col1} {main tab1 col2}]
! 755:
! 756: # This view will thwart the flattening optimization.
! 757: do_test capi2-13.1 {
! 758: execsql {
! 759: CREATE VIEW view2 AS SELECT * FROM tab1 limit 10 offset 10;
! 760: }
! 761: } {}
! 762: do_test capi2-13.2 {
! 763: check_origins {SELECT col2, col1 FROM view2}
! 764: } [list {main tab1 col2} {main tab1 col1}]
! 765: do_test capi2-13.3 {
! 766: check_origins {SELECT col2 AS hello, col1 AS world FROM view2}
! 767: } [list {main tab1 col2} {main tab1 col1}]
! 768: do_test capi2-13.4 {
! 769: check_origins {SELECT b, a FROM (SELECT col1 AS a, col2 AS b FROM view2)}
! 770: } [list {main tab1 col2} {main tab1 col1}]
! 771: do_test capi2-13.5 {
! 772: check_origins {SELECT (SELECT col2 FROM view2), (SELECT col1 FROM view2)}
! 773: } [list {main tab1 col2} {main tab1 col1}]
! 774: do_test capi2-13.6 {
! 775: check_origins {SELECT (SELECT col2), (SELECT col1) FROM view2}
! 776: } [list {main tab1 col2} {main tab1 col1}]
! 777: do_test capi2-13.7 {
! 778: check_origins {SELECT * FROM view2}
! 779: } [list {main tab1 col1} {main tab1 col2}]
! 780: do_test capi2-13.8 {
! 781: check_origins {select * from (select * from view2)}
! 782: } [list {main tab1 col1} {main tab1 col2}]
! 783: do_test capi2-13.9 {
! 784: check_origins {select * from (select * from (select * from view2))}
! 785: } [list {main tab1 col1} {main tab1 col2}]
! 786: do_test capi2-13.10 {
! 787: db close
! 788: sqlite3 db test.db
! 789: set ::DB [sqlite3_connection_pointer db]
! 790: check_origins {select * from (select * from (select * from view2))}
! 791: } [list {main tab1 col1} {main tab1 col2}]
! 792: do_test capi2-13.11 {
! 793: check_origins {select * from (select * from tab1 limit 10 offset 10)}
! 794: } [list {main tab1 col1} {main tab1 col2}]
! 795: }
! 796:
! 797:
! 798: } ;# ifcapable columnmetadata
! 799:
! 800: db2 close
! 801: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to capi2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test