Annotation of embedaddon/sqlite3/test/intpkey.test, revision 1.1
1.1 ! misho 1: # 2001 September 15
! 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.
! 12: #
! 13: # This file implements tests for the special processing associated
! 14: # with INTEGER PRIMARY KEY columns.
! 15: #
! 16: # $Id: intpkey.test,v 1.24 2007/11/29 17:43:28 danielk1977 Exp $
! 17:
! 18: set testdir [file dirname $argv0]
! 19: source $testdir/tester.tcl
! 20:
! 21: # Create a table with a primary key and a datatype other than
! 22: # integer
! 23: #
! 24: do_test intpkey-1.0 {
! 25: execsql {
! 26: CREATE TABLE t1(a TEXT PRIMARY KEY, b, c);
! 27: }
! 28: } {}
! 29:
! 30: # There should be an index associated with the primary key
! 31: #
! 32: do_test intpkey-1.1 {
! 33: execsql {
! 34: SELECT name FROM sqlite_master
! 35: WHERE type='index' AND tbl_name='t1';
! 36: }
! 37: } {sqlite_autoindex_t1_1}
! 38:
! 39: # Now create a table with an integer primary key and verify that
! 40: # there is no associated index.
! 41: #
! 42: do_test intpkey-1.2 {
! 43: execsql {
! 44: DROP TABLE t1;
! 45: CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
! 46: SELECT name FROM sqlite_master
! 47: WHERE type='index' AND tbl_name='t1';
! 48: }
! 49: } {}
! 50:
! 51: # Insert some records into the new table. Specify the primary key
! 52: # and verify that the key is used as the record number.
! 53: #
! 54: do_test intpkey-1.3 {
! 55: execsql {
! 56: INSERT INTO t1 VALUES(5,'hello','world');
! 57: }
! 58: db last_insert_rowid
! 59: } {5}
! 60: do_test intpkey-1.4 {
! 61: execsql {
! 62: SELECT * FROM t1;
! 63: }
! 64: } {5 hello world}
! 65: do_test intpkey-1.5 {
! 66: execsql {
! 67: SELECT rowid, * FROM t1;
! 68: }
! 69: } {5 5 hello world}
! 70:
! 71: # Attempting to insert a duplicate primary key should give a constraint
! 72: # failure.
! 73: #
! 74: do_test intpkey-1.6 {
! 75: set r [catch {execsql {
! 76: INSERT INTO t1 VALUES(5,'second','entry');
! 77: }} msg]
! 78: lappend r $msg
! 79: } {1 {PRIMARY KEY must be unique}}
! 80: do_test intpkey-1.7 {
! 81: execsql {
! 82: SELECT rowid, * FROM t1;
! 83: }
! 84: } {5 5 hello world}
! 85: do_test intpkey-1.8 {
! 86: set r [catch {execsql {
! 87: INSERT INTO t1 VALUES(6,'second','entry');
! 88: }} msg]
! 89: lappend r $msg
! 90: } {0 {}}
! 91: do_test intpkey-1.8.1 {
! 92: db last_insert_rowid
! 93: } {6}
! 94: do_test intpkey-1.9 {
! 95: execsql {
! 96: SELECT rowid, * FROM t1;
! 97: }
! 98: } {5 5 hello world 6 6 second entry}
! 99:
! 100: # A ROWID is automatically generated for new records that do not specify
! 101: # the integer primary key.
! 102: #
! 103: do_test intpkey-1.10 {
! 104: execsql {
! 105: INSERT INTO t1(b,c) VALUES('one','two');
! 106: SELECT b FROM t1 ORDER BY b;
! 107: }
! 108: } {hello one second}
! 109:
! 110: # Try to change the ROWID for the new entry.
! 111: #
! 112: do_test intpkey-1.11 {
! 113: execsql {
! 114: UPDATE t1 SET a=4 WHERE b='one';
! 115: SELECT * FROM t1;
! 116: }
! 117: } {4 one two 5 hello world 6 second entry}
! 118:
! 119: # Make sure SELECT statements are able to use the primary key column
! 120: # as an index.
! 121: #
! 122: do_test intpkey-1.12.1 {
! 123: execsql {
! 124: SELECT * FROM t1 WHERE a==4;
! 125: }
! 126: } {4 one two}
! 127: do_test intpkey-1.12.2 {
! 128: set sqlite_query_plan
! 129: } {t1 *}
! 130:
! 131: # Try to insert a non-integer value into the primary key field. This
! 132: # should result in a data type mismatch.
! 133: #
! 134: do_test intpkey-1.13.1 {
! 135: set r [catch {execsql {
! 136: INSERT INTO t1 VALUES('x','y','z');
! 137: }} msg]
! 138: lappend r $msg
! 139: } {1 {datatype mismatch}}
! 140: do_test intpkey-1.13.2 {
! 141: set r [catch {execsql {
! 142: INSERT INTO t1 VALUES('','y','z');
! 143: }} msg]
! 144: lappend r $msg
! 145: } {1 {datatype mismatch}}
! 146: do_test intpkey-1.14 {
! 147: set r [catch {execsql {
! 148: INSERT INTO t1 VALUES(3.4,'y','z');
! 149: }} msg]
! 150: lappend r $msg
! 151: } {1 {datatype mismatch}}
! 152: do_test intpkey-1.15 {
! 153: set r [catch {execsql {
! 154: INSERT INTO t1 VALUES(-3,'y','z');
! 155: }} msg]
! 156: lappend r $msg
! 157: } {0 {}}
! 158: do_test intpkey-1.16 {
! 159: execsql {SELECT * FROM t1}
! 160: } {-3 y z 4 one two 5 hello world 6 second entry}
! 161:
! 162: #### INDICES
! 163: # Check to make sure indices work correctly with integer primary keys
! 164: #
! 165: do_test intpkey-2.1 {
! 166: execsql {
! 167: CREATE INDEX i1 ON t1(b);
! 168: SELECT * FROM t1 WHERE b=='y'
! 169: }
! 170: } {-3 y z}
! 171: do_test intpkey-2.1.1 {
! 172: execsql {
! 173: SELECT * FROM t1 WHERE b=='y' AND rowid<0
! 174: }
! 175: } {-3 y z}
! 176: do_test intpkey-2.1.2 {
! 177: execsql {
! 178: SELECT * FROM t1 WHERE b=='y' AND rowid<0 AND rowid>=-20
! 179: }
! 180: } {-3 y z}
! 181: do_test intpkey-2.1.3 {
! 182: execsql {
! 183: SELECT * FROM t1 WHERE b>='y'
! 184: }
! 185: } {-3 y z}
! 186: do_test intpkey-2.1.4 {
! 187: execsql {
! 188: SELECT * FROM t1 WHERE b>='y' AND rowid<10
! 189: }
! 190: } {-3 y z}
! 191:
! 192: do_test intpkey-2.2 {
! 193: execsql {
! 194: UPDATE t1 SET a=8 WHERE b=='y';
! 195: SELECT * FROM t1 WHERE b=='y';
! 196: }
! 197: } {8 y z}
! 198: do_test intpkey-2.3 {
! 199: execsql {
! 200: SELECT rowid, * FROM t1;
! 201: }
! 202: } {4 4 one two 5 5 hello world 6 6 second entry 8 8 y z}
! 203: do_test intpkey-2.4 {
! 204: execsql {
! 205: SELECT rowid, * FROM t1 WHERE b<'second'
! 206: }
! 207: } {5 5 hello world 4 4 one two}
! 208: do_test intpkey-2.4.1 {
! 209: execsql {
! 210: SELECT rowid, * FROM t1 WHERE 'second'>b
! 211: }
! 212: } {5 5 hello world 4 4 one two}
! 213: do_test intpkey-2.4.2 {
! 214: execsql {
! 215: SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b
! 216: }
! 217: } {4 4 one two 5 5 hello world}
! 218: do_test intpkey-2.4.3 {
! 219: execsql {
! 220: SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid
! 221: }
! 222: } {4 4 one two 5 5 hello world}
! 223: do_test intpkey-2.5 {
! 224: execsql {
! 225: SELECT rowid, * FROM t1 WHERE b>'a'
! 226: }
! 227: } {5 5 hello world 4 4 one two 6 6 second entry 8 8 y z}
! 228: do_test intpkey-2.6 {
! 229: execsql {
! 230: DELETE FROM t1 WHERE rowid=4;
! 231: SELECT * FROM t1 WHERE b>'a';
! 232: }
! 233: } {5 hello world 6 second entry 8 y z}
! 234: do_test intpkey-2.7 {
! 235: execsql {
! 236: UPDATE t1 SET a=-4 WHERE rowid=8;
! 237: SELECT * FROM t1 WHERE b>'a';
! 238: }
! 239: } {5 hello world 6 second entry -4 y z}
! 240: do_test intpkey-2.7 {
! 241: execsql {
! 242: SELECT * FROM t1
! 243: }
! 244: } {-4 y z 5 hello world 6 second entry}
! 245:
! 246: # Do an SQL statement. Append the search count to the end of the result.
! 247: #
! 248: proc count sql {
! 249: set ::sqlite_search_count 0
! 250: return [concat [execsql $sql] $::sqlite_search_count]
! 251: }
! 252:
! 253: # Create indices that include the integer primary key as one of their
! 254: # columns.
! 255: #
! 256: do_test intpkey-3.1 {
! 257: execsql {
! 258: CREATE INDEX i2 ON t1(a);
! 259: }
! 260: } {}
! 261: do_test intpkey-3.2 {
! 262: count {
! 263: SELECT * FROM t1 WHERE a=5;
! 264: }
! 265: } {5 hello world 0}
! 266: do_test intpkey-3.3 {
! 267: count {
! 268: SELECT * FROM t1 WHERE a>4 AND a<6;
! 269: }
! 270: } {5 hello world 2}
! 271: do_test intpkey-3.4 {
! 272: count {
! 273: SELECT * FROM t1 WHERE b>='hello' AND b<'hello2';
! 274: }
! 275: } {5 hello world 3}
! 276: do_test intpkey-3.5 {
! 277: execsql {
! 278: CREATE INDEX i3 ON t1(c,a);
! 279: }
! 280: } {}
! 281: do_test intpkey-3.6 {
! 282: count {
! 283: SELECT * FROM t1 WHERE c=='world';
! 284: }
! 285: } {5 hello world 3}
! 286: do_test intpkey-3.7 {
! 287: execsql {INSERT INTO t1 VALUES(11,'hello','world')}
! 288: count {
! 289: SELECT * FROM t1 WHERE c=='world';
! 290: }
! 291: } {5 hello world 11 hello world 5}
! 292: do_test intpkey-3.8 {
! 293: count {
! 294: SELECT * FROM t1 WHERE c=='world' AND a>7;
! 295: }
! 296: } {11 hello world 4}
! 297: do_test intpkey-3.9 {
! 298: count {
! 299: SELECT * FROM t1 WHERE 7<a;
! 300: }
! 301: } {11 hello world 1}
! 302:
! 303: # Test inequality constraints on integer primary keys and rowids
! 304: #
! 305: do_test intpkey-4.1 {
! 306: count {
! 307: SELECT * FROM t1 WHERE 11=rowid
! 308: }
! 309: } {11 hello world 0}
! 310: do_test intpkey-4.2 {
! 311: count {
! 312: SELECT * FROM t1 WHERE 11=rowid AND b=='hello'
! 313: }
! 314: } {11 hello world 0}
! 315: do_test intpkey-4.3 {
! 316: count {
! 317: SELECT * FROM t1 WHERE 11=rowid AND b=='hello' AND c IS NOT NULL;
! 318: }
! 319: } {11 hello world 0}
! 320: do_test intpkey-4.4 {
! 321: count {
! 322: SELECT * FROM t1 WHERE rowid==11
! 323: }
! 324: } {11 hello world 0}
! 325: do_test intpkey-4.5 {
! 326: count {
! 327: SELECT * FROM t1 WHERE oid==11 AND b=='hello'
! 328: }
! 329: } {11 hello world 0}
! 330: do_test intpkey-4.6 {
! 331: count {
! 332: SELECT * FROM t1 WHERE a==11 AND b=='hello' AND c IS NOT NULL;
! 333: }
! 334: } {11 hello world 0}
! 335:
! 336: do_test intpkey-4.7 {
! 337: count {
! 338: SELECT * FROM t1 WHERE 8<rowid;
! 339: }
! 340: } {11 hello world 1}
! 341: do_test intpkey-4.8 {
! 342: count {
! 343: SELECT * FROM t1 WHERE 8<rowid AND 11>=oid;
! 344: }
! 345: } {11 hello world 1}
! 346: do_test intpkey-4.9 {
! 347: count {
! 348: SELECT * FROM t1 WHERE 11<=_rowid_ AND 12>=a;
! 349: }
! 350: } {11 hello world 1}
! 351: do_test intpkey-4.10 {
! 352: count {
! 353: SELECT * FROM t1 WHERE 0>=_rowid_;
! 354: }
! 355: } {-4 y z 1}
! 356: do_test intpkey-4.11 {
! 357: count {
! 358: SELECT * FROM t1 WHERE a<0;
! 359: }
! 360: } {-4 y z 1}
! 361: do_test intpkey-4.12 {
! 362: count {
! 363: SELECT * FROM t1 WHERE a<0 AND a>10;
! 364: }
! 365: } {1}
! 366:
! 367: # Make sure it is OK to insert a rowid of 0
! 368: #
! 369: do_test intpkey-5.1 {
! 370: execsql {
! 371: INSERT INTO t1 VALUES(0,'zero','entry');
! 372: }
! 373: count {
! 374: SELECT * FROM t1 WHERE a=0;
! 375: }
! 376: } {0 zero entry 0}
! 377: do_test intpkey-5.2 {
! 378: execsql {
! 379: SELECT rowid, a FROM t1
! 380: }
! 381: } {-4 -4 0 0 5 5 6 6 11 11}
! 382:
! 383: # Test the ability of the COPY command to put data into a
! 384: # table that contains an integer primary key.
! 385: #
! 386: # COPY command has been removed. But we retain these tests so
! 387: # that the tables will contain the right data for tests that follow.
! 388: #
! 389: do_test intpkey-6.1 {
! 390: execsql {
! 391: BEGIN;
! 392: INSERT INTO t1 VALUES(20,'b-20','c-20');
! 393: INSERT INTO t1 VALUES(21,'b-21','c-21');
! 394: INSERT INTO t1 VALUES(22,'b-22','c-22');
! 395: COMMIT;
! 396: SELECT * FROM t1 WHERE a>=20;
! 397: }
! 398: } {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}
! 399: do_test intpkey-6.2 {
! 400: execsql {
! 401: SELECT * FROM t1 WHERE b=='hello'
! 402: }
! 403: } {5 hello world 11 hello world}
! 404: do_test intpkey-6.3 {
! 405: execsql {
! 406: DELETE FROM t1 WHERE b='b-21';
! 407: SELECT * FROM t1 WHERE b=='b-21';
! 408: }
! 409: } {}
! 410: do_test intpkey-6.4 {
! 411: execsql {
! 412: SELECT * FROM t1 WHERE a>=20
! 413: }
! 414: } {20 b-20 c-20 22 b-22 c-22}
! 415:
! 416: # Do an insert of values with the columns specified out of order.
! 417: #
! 418: do_test intpkey-7.1 {
! 419: execsql {
! 420: INSERT INTO t1(c,b,a) VALUES('row','new',30);
! 421: SELECT * FROM t1 WHERE rowid>=30;
! 422: }
! 423: } {30 new row}
! 424: do_test intpkey-7.2 {
! 425: execsql {
! 426: SELECT * FROM t1 WHERE rowid>20;
! 427: }
! 428: } {22 b-22 c-22 30 new row}
! 429:
! 430: # Do an insert from a select statement.
! 431: #
! 432: do_test intpkey-8.1 {
! 433: execsql {
! 434: CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);
! 435: INSERT INTO t2 SELECT * FROM t1;
! 436: SELECT rowid FROM t2;
! 437: }
! 438: } {-4 0 5 6 11 20 22 30}
! 439: do_test intpkey-8.2 {
! 440: execsql {
! 441: SELECT x FROM t2;
! 442: }
! 443: } {-4 0 5 6 11 20 22 30}
! 444:
! 445: do_test intpkey-9.1 {
! 446: execsql {
! 447: UPDATE t1 SET c='www' WHERE c='world';
! 448: SELECT rowid, a, c FROM t1 WHERE c=='www';
! 449: }
! 450: } {5 5 www 11 11 www}
! 451:
! 452:
! 453: # Check insert of NULL for primary key
! 454: #
! 455: do_test intpkey-10.1 {
! 456: execsql {
! 457: DROP TABLE t2;
! 458: CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);
! 459: INSERT INTO t2 VALUES(NULL, 1, 2);
! 460: SELECT * from t2;
! 461: }
! 462: } {1 1 2}
! 463: do_test intpkey-10.2 {
! 464: execsql {
! 465: INSERT INTO t2 VALUES(NULL, 2, 3);
! 466: SELECT * from t2 WHERE x=2;
! 467: }
! 468: } {2 2 3}
! 469: do_test intpkey-10.3 {
! 470: execsql {
! 471: INSERT INTO t2 SELECT NULL, z, y FROM t2;
! 472: SELECT * FROM t2;
! 473: }
! 474: } {1 1 2 2 2 3 3 2 1 4 3 2}
! 475:
! 476: # This tests checks to see if a floating point number can be used
! 477: # to reference an integer primary key.
! 478: #
! 479: do_test intpkey-11.1 {
! 480: execsql {
! 481: SELECT b FROM t1 WHERE a=2.0+3.0;
! 482: }
! 483: } {hello}
! 484: do_test intpkey-11.1 {
! 485: execsql {
! 486: SELECT b FROM t1 WHERE a=2.0+3.5;
! 487: }
! 488: } {}
! 489:
! 490: integrity_check intpkey-12.1
! 491:
! 492: # Try to use a string that looks like a floating point number as
! 493: # an integer primary key. This should actually work when the floating
! 494: # point value can be rounded to an integer without loss of data.
! 495: #
! 496: do_test intpkey-13.1 {
! 497: execsql {
! 498: SELECT * FROM t1 WHERE a=1;
! 499: }
! 500: } {}
! 501: do_test intpkey-13.2 {
! 502: execsql {
! 503: INSERT INTO t1 VALUES('1.0',2,3);
! 504: SELECT * FROM t1 WHERE a=1;
! 505: }
! 506: } {1 2 3}
! 507: do_test intpkey-13.3 {
! 508: catchsql {
! 509: INSERT INTO t1 VALUES('1.5',3,4);
! 510: }
! 511: } {1 {datatype mismatch}}
! 512: ifcapable {bloblit} {
! 513: do_test intpkey-13.4 {
! 514: catchsql {
! 515: INSERT INTO t1 VALUES(x'123456',3,4);
! 516: }
! 517: } {1 {datatype mismatch}}
! 518: }
! 519: do_test intpkey-13.5 {
! 520: catchsql {
! 521: INSERT INTO t1 VALUES('+1234567890',3,4);
! 522: }
! 523: } {0 {}}
! 524:
! 525: # Compare an INTEGER PRIMARY KEY against a TEXT expression. The INTEGER
! 526: # affinity should be applied to the text value before the comparison
! 527: # takes place.
! 528: #
! 529: do_test intpkey-14.1 {
! 530: execsql {
! 531: CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT);
! 532: INSERT INTO t3 VALUES(1, 1, 'one');
! 533: INSERT INTO t3 VALUES(2, 2, '2');
! 534: INSERT INTO t3 VALUES(3, 3, 3);
! 535: }
! 536: } {}
! 537: do_test intpkey-14.2 {
! 538: execsql {
! 539: SELECT * FROM t3 WHERE a>2;
! 540: }
! 541: } {3 3 3}
! 542: do_test intpkey-14.3 {
! 543: execsql {
! 544: SELECT * FROM t3 WHERE a>'2';
! 545: }
! 546: } {3 3 3}
! 547: do_test intpkey-14.4 {
! 548: execsql {
! 549: SELECT * FROM t3 WHERE a<'2';
! 550: }
! 551: } {1 1 one}
! 552: do_test intpkey-14.5 {
! 553: execsql {
! 554: SELECT * FROM t3 WHERE a<c;
! 555: }
! 556: } {1 1 one}
! 557: do_test intpkey-14.6 {
! 558: execsql {
! 559: SELECT * FROM t3 WHERE a=c;
! 560: }
! 561: } {2 2 2 3 3 3}
! 562:
! 563: # Check for proper handling of primary keys greater than 2^31.
! 564: # Ticket #1188
! 565: #
! 566: do_test intpkey-15.1 {
! 567: execsql {
! 568: INSERT INTO t1 VALUES(2147483647, 'big-1', 123);
! 569: SELECT * FROM t1 WHERE a>2147483648;
! 570: }
! 571: } {}
! 572: do_test intpkey-15.2 {
! 573: execsql {
! 574: INSERT INTO t1 VALUES(NULL, 'big-2', 234);
! 575: SELECT b FROM t1 WHERE a>=2147483648;
! 576: }
! 577: } {big-2}
! 578: do_test intpkey-15.3 {
! 579: execsql {
! 580: SELECT b FROM t1 WHERE a>2147483648;
! 581: }
! 582: } {}
! 583: do_test intpkey-15.4 {
! 584: execsql {
! 585: SELECT b FROM t1 WHERE a>=2147483647;
! 586: }
! 587: } {big-1 big-2}
! 588: do_test intpkey-15.5 {
! 589: execsql {
! 590: SELECT b FROM t1 WHERE a<2147483648;
! 591: }
! 592: } {y zero 2 hello second hello b-20 b-22 new 3 big-1}
! 593: do_test intpkey-15.6 {
! 594: execsql {
! 595: SELECT b FROM t1 WHERE a<12345678901;
! 596: }
! 597: } {y zero 2 hello second hello b-20 b-22 new 3 big-1 big-2}
! 598: do_test intpkey-15.7 {
! 599: execsql {
! 600: SELECT b FROM t1 WHERE a>12345678901;
! 601: }
! 602: } {}
! 603:
! 604:
! 605: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to intpkey.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