Annotation of embedaddon/sqlite3/test/index.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. The
! 12: # focus of this file is testing the CREATE INDEX statement.
! 13: #
! 14: # $Id: index.test,v 1.43 2008/01/16 18:20:42 danielk1977 Exp $
! 15:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18:
! 19: # Create a basic index and verify it is added to sqlite_master
! 20: #
! 21: do_test index-1.1 {
! 22: execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
! 23: execsql {CREATE INDEX index1 ON test1(f1)}
! 24: execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
! 25: } {index1 test1}
! 26: do_test index-1.1b {
! 27: execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
! 28: WHERE name='index1'}
! 29: } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
! 30: do_test index-1.1c {
! 31: db close
! 32: sqlite3 db test.db
! 33: execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
! 34: WHERE name='index1'}
! 35: } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
! 36: do_test index-1.1d {
! 37: db close
! 38: sqlite3 db test.db
! 39: execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
! 40: } {index1 test1}
! 41:
! 42: # Verify that the index dies with the table
! 43: #
! 44: do_test index-1.2 {
! 45: execsql {DROP TABLE test1}
! 46: execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
! 47: } {}
! 48:
! 49: # Try adding an index to a table that does not exist
! 50: #
! 51: do_test index-2.1 {
! 52: set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg]
! 53: lappend v $msg
! 54: } {1 {no such table: main.test1}}
! 55:
! 56: # Try adding an index on a column of a table where the table
! 57: # exists but the column does not.
! 58: #
! 59: do_test index-2.1 {
! 60: execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
! 61: set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg]
! 62: lappend v $msg
! 63: } {1 {table test1 has no column named f4}}
! 64:
! 65: # Try an index with some columns that match and others that do now.
! 66: #
! 67: do_test index-2.2 {
! 68: set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg]
! 69: execsql {DROP TABLE test1}
! 70: lappend v $msg
! 71: } {1 {table test1 has no column named f4}}
! 72:
! 73: # Try creating a bunch of indices on the same table
! 74: #
! 75: set r {}
! 76: for {set i 1} {$i<100} {incr i} {
! 77: lappend r [format index%02d $i]
! 78: }
! 79: do_test index-3.1 {
! 80: execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)}
! 81: for {set i 1} {$i<100} {incr i} {
! 82: set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
! 83: execsql $sql
! 84: }
! 85: execsql {SELECT name FROM sqlite_master
! 86: WHERE type='index' AND tbl_name='test1'
! 87: ORDER BY name}
! 88: } $r
! 89: integrity_check index-3.2.1
! 90: ifcapable {reindex} {
! 91: do_test index-3.2.2 {
! 92: execsql REINDEX
! 93: } {}
! 94: }
! 95: integrity_check index-3.2.3
! 96:
! 97:
! 98: # Verify that all the indices go away when we drop the table.
! 99: #
! 100: do_test index-3.3 {
! 101: execsql {DROP TABLE test1}
! 102: execsql {SELECT name FROM sqlite_master
! 103: WHERE type='index' AND tbl_name='test1'
! 104: ORDER BY name}
! 105: } {}
! 106:
! 107: # Create a table and insert values into that table. Then create
! 108: # an index on that table. Verify that we can select values
! 109: # from the table correctly using the index.
! 110: #
! 111: # Note that the index names "index9" and "indext" are chosen because
! 112: # they both have the same hash.
! 113: #
! 114: do_test index-4.1 {
! 115: execsql {CREATE TABLE test1(cnt int, power int)}
! 116: for {set i 1} {$i<20} {incr i} {
! 117: execsql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])"
! 118: }
! 119: execsql {CREATE INDEX index9 ON test1(cnt)}
! 120: execsql {CREATE INDEX indext ON test1(power)}
! 121: execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
! 122: } {index9 indext test1}
! 123: do_test index-4.2 {
! 124: execsql {SELECT cnt FROM test1 WHERE power=4}
! 125: } {2}
! 126: do_test index-4.3 {
! 127: execsql {SELECT cnt FROM test1 WHERE power=1024}
! 128: } {10}
! 129: do_test index-4.4 {
! 130: execsql {SELECT power FROM test1 WHERE cnt=6}
! 131: } {64}
! 132: do_test index-4.5 {
! 133: execsql {DROP INDEX indext}
! 134: execsql {SELECT power FROM test1 WHERE cnt=6}
! 135: } {64}
! 136: do_test index-4.6 {
! 137: execsql {SELECT cnt FROM test1 WHERE power=1024}
! 138: } {10}
! 139: do_test index-4.7 {
! 140: execsql {CREATE INDEX indext ON test1(cnt)}
! 141: execsql {SELECT power FROM test1 WHERE cnt=6}
! 142: } {64}
! 143: do_test index-4.8 {
! 144: execsql {SELECT cnt FROM test1 WHERE power=1024}
! 145: } {10}
! 146: do_test index-4.9 {
! 147: execsql {DROP INDEX index9}
! 148: execsql {SELECT power FROM test1 WHERE cnt=6}
! 149: } {64}
! 150: do_test index-4.10 {
! 151: execsql {SELECT cnt FROM test1 WHERE power=1024}
! 152: } {10}
! 153: do_test index-4.11 {
! 154: execsql {DROP INDEX indext}
! 155: execsql {SELECT power FROM test1 WHERE cnt=6}
! 156: } {64}
! 157: do_test index-4.12 {
! 158: execsql {SELECT cnt FROM test1 WHERE power=1024}
! 159: } {10}
! 160: do_test index-4.13 {
! 161: execsql {DROP TABLE test1}
! 162: execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
! 163: } {}
! 164: integrity_check index-4.14
! 165:
! 166: # Do not allow indices to be added to sqlite_master
! 167: #
! 168: do_test index-5.1 {
! 169: set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
! 170: lappend v $msg
! 171: } {1 {table sqlite_master may not be indexed}}
! 172: do_test index-5.2 {
! 173: execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
! 174: } {}
! 175:
! 176: # Do not allow indices with duplicate names to be added
! 177: #
! 178: do_test index-6.1 {
! 179: execsql {CREATE TABLE test1(f1 int, f2 int)}
! 180: execsql {CREATE TABLE test2(g1 real, g2 real)}
! 181: execsql {CREATE INDEX index1 ON test1(f1)}
! 182: set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg]
! 183: lappend v $msg
! 184: } {1 {index index1 already exists}}
! 185: do_test index-6.1.1 {
! 186: catchsql {CREATE INDEX [index1] ON test2(g1)}
! 187: } {1 {index index1 already exists}}
! 188: do_test index-6.1b {
! 189: execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
! 190: } {index1 test1 test2}
! 191: do_test index-6.1c {
! 192: catchsql {CREATE INDEX IF NOT EXISTS index1 ON test1(f1)}
! 193: } {0 {}}
! 194: do_test index-6.2 {
! 195: set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg]
! 196: lappend v $msg
! 197: } {1 {there is already a table named test1}}
! 198: do_test index-6.2b {
! 199: execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
! 200: } {index1 test1 test2}
! 201: do_test index-6.3 {
! 202: execsql {DROP TABLE test1}
! 203: execsql {DROP TABLE test2}
! 204: execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
! 205: } {}
! 206: do_test index-6.4 {
! 207: execsql {
! 208: CREATE TABLE test1(a,b);
! 209: CREATE INDEX index1 ON test1(a);
! 210: CREATE INDEX index2 ON test1(b);
! 211: CREATE INDEX index3 ON test1(a,b);
! 212: DROP TABLE test1;
! 213: SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name;
! 214: }
! 215: } {}
! 216: integrity_check index-6.5
! 217:
! 218:
! 219: # Create a primary key
! 220: #
! 221: do_test index-7.1 {
! 222: execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
! 223: for {set i 1} {$i<20} {incr i} {
! 224: execsql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])"
! 225: }
! 226: execsql {SELECT count(*) FROM test1}
! 227: } {19}
! 228: do_test index-7.2 {
! 229: execsql {SELECT f1 FROM test1 WHERE f2=65536}
! 230: } {16}
! 231: do_test index-7.3 {
! 232: execsql {
! 233: SELECT name FROM sqlite_master
! 234: WHERE type='index' AND tbl_name='test1'
! 235: }
! 236: } {sqlite_autoindex_test1_1}
! 237: do_test index-7.4 {
! 238: execsql {DROP table test1}
! 239: execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
! 240: } {}
! 241: integrity_check index-7.5
! 242:
! 243: # Make sure we cannot drop a non-existant index.
! 244: #
! 245: do_test index-8.1 {
! 246: set v [catch {execsql {DROP INDEX index1}} msg]
! 247: lappend v $msg
! 248: } {1 {no such index: index1}}
! 249:
! 250: # Make sure we don't actually create an index when the EXPLAIN keyword
! 251: # is used.
! 252: #
! 253: do_test index-9.1 {
! 254: execsql {CREATE TABLE tab1(a int)}
! 255: ifcapable {explain} {
! 256: execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
! 257: }
! 258: execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
! 259: } {tab1}
! 260: do_test index-9.2 {
! 261: execsql {CREATE INDEX idx1 ON tab1(a)}
! 262: execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
! 263: } {idx1 tab1}
! 264: integrity_check index-9.3
! 265:
! 266: # Allow more than one entry with the same key.
! 267: #
! 268: do_test index-10.0 {
! 269: execsql {
! 270: CREATE TABLE t1(a int, b int);
! 271: CREATE INDEX i1 ON t1(a);
! 272: INSERT INTO t1 VALUES(1,2);
! 273: INSERT INTO t1 VALUES(2,4);
! 274: INSERT INTO t1 VALUES(3,8);
! 275: INSERT INTO t1 VALUES(1,12);
! 276: SELECT b FROM t1 WHERE a=1 ORDER BY b;
! 277: }
! 278: } {2 12}
! 279: do_test index-10.1 {
! 280: execsql {
! 281: SELECT b FROM t1 WHERE a=2 ORDER BY b;
! 282: }
! 283: } {4}
! 284: do_test index-10.2 {
! 285: execsql {
! 286: DELETE FROM t1 WHERE b=12;
! 287: SELECT b FROM t1 WHERE a=1 ORDER BY b;
! 288: }
! 289: } {2}
! 290: do_test index-10.3 {
! 291: execsql {
! 292: DELETE FROM t1 WHERE b=2;
! 293: SELECT b FROM t1 WHERE a=1 ORDER BY b;
! 294: }
! 295: } {}
! 296: do_test index-10.4 {
! 297: execsql {
! 298: DELETE FROM t1;
! 299: INSERT INTO t1 VALUES (1,1);
! 300: INSERT INTO t1 VALUES (1,2);
! 301: INSERT INTO t1 VALUES (1,3);
! 302: INSERT INTO t1 VALUES (1,4);
! 303: INSERT INTO t1 VALUES (1,5);
! 304: INSERT INTO t1 VALUES (1,6);
! 305: INSERT INTO t1 VALUES (1,7);
! 306: INSERT INTO t1 VALUES (1,8);
! 307: INSERT INTO t1 VALUES (1,9);
! 308: INSERT INTO t1 VALUES (2,0);
! 309: SELECT b FROM t1 WHERE a=1 ORDER BY b;
! 310: }
! 311: } {1 2 3 4 5 6 7 8 9}
! 312: do_test index-10.5 {
! 313: ifcapable subquery {
! 314: execsql { DELETE FROM t1 WHERE b IN (2, 4, 6, 8); }
! 315: } else {
! 316: execsql { DELETE FROM t1 WHERE b = 2 OR b = 4 OR b = 6 OR b = 8; }
! 317: }
! 318: execsql {
! 319: SELECT b FROM t1 WHERE a=1 ORDER BY b;
! 320: }
! 321: } {1 3 5 7 9}
! 322: do_test index-10.6 {
! 323: execsql {
! 324: DELETE FROM t1 WHERE b>2;
! 325: SELECT b FROM t1 WHERE a=1 ORDER BY b;
! 326: }
! 327: } {1}
! 328: do_test index-10.7 {
! 329: execsql {
! 330: DELETE FROM t1 WHERE b=1;
! 331: SELECT b FROM t1 WHERE a=1 ORDER BY b;
! 332: }
! 333: } {}
! 334: do_test index-10.8 {
! 335: execsql {
! 336: SELECT b FROM t1 ORDER BY b;
! 337: }
! 338: } {0}
! 339: integrity_check index-10.9
! 340:
! 341: # Automatically create an index when we specify a primary key.
! 342: #
! 343: do_test index-11.1 {
! 344: execsql {
! 345: CREATE TABLE t3(
! 346: a text,
! 347: b int,
! 348: c float,
! 349: PRIMARY KEY(b)
! 350: );
! 351: }
! 352: for {set i 1} {$i<=50} {incr i} {
! 353: execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
! 354: }
! 355: set sqlite_search_count 0
! 356: concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
! 357: } {0.1 2}
! 358: integrity_check index-11.2
! 359:
! 360:
! 361: # Numeric strings should compare as if they were numbers. So even if the
! 362: # strings are not character-by-character the same, if they represent the
! 363: # same number they should compare equal to one another. Verify that this
! 364: # is true in indices.
! 365: #
! 366: # Updated for sqlite3 v3: SQLite will now store these values as numbers
! 367: # (because the affinity of column a is NUMERIC) so the quirky
! 368: # representations are not retained. i.e. '+1.0' becomes '1'.
! 369: do_test index-12.1 {
! 370: execsql {
! 371: CREATE TABLE t4(a NUM,b);
! 372: INSERT INTO t4 VALUES('0.0',1);
! 373: INSERT INTO t4 VALUES('0.00',2);
! 374: INSERT INTO t4 VALUES('abc',3);
! 375: INSERT INTO t4 VALUES('-1.0',4);
! 376: INSERT INTO t4 VALUES('+1.0',5);
! 377: INSERT INTO t4 VALUES('0',6);
! 378: INSERT INTO t4 VALUES('00000',7);
! 379: SELECT a FROM t4 ORDER BY b;
! 380: }
! 381: } {0 0 abc -1 1 0 0}
! 382: do_test index-12.2 {
! 383: execsql {
! 384: SELECT a FROM t4 WHERE a==0 ORDER BY b
! 385: }
! 386: } {0 0 0 0}
! 387: do_test index-12.3 {
! 388: execsql {
! 389: SELECT a FROM t4 WHERE a<0.5 ORDER BY b
! 390: }
! 391: } {0 0 -1 0 0}
! 392: do_test index-12.4 {
! 393: execsql {
! 394: SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
! 395: }
! 396: } {0 0 abc 1 0 0}
! 397: do_test index-12.5 {
! 398: execsql {
! 399: CREATE INDEX t4i1 ON t4(a);
! 400: SELECT a FROM t4 WHERE a==0 ORDER BY b
! 401: }
! 402: } {0 0 0 0}
! 403: do_test index-12.6 {
! 404: execsql {
! 405: SELECT a FROM t4 WHERE a<0.5 ORDER BY b
! 406: }
! 407: } {0 0 -1 0 0}
! 408: do_test index-12.7 {
! 409: execsql {
! 410: SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
! 411: }
! 412: } {0 0 abc 1 0 0}
! 413: integrity_check index-12.8
! 414:
! 415: # Make sure we cannot drop an automatically created index.
! 416: #
! 417: do_test index-13.1 {
! 418: execsql {
! 419: CREATE TABLE t5(
! 420: a int UNIQUE,
! 421: b float PRIMARY KEY,
! 422: c varchar(10),
! 423: UNIQUE(a,c)
! 424: );
! 425: INSERT INTO t5 VALUES(1,2,3);
! 426: SELECT * FROM t5;
! 427: }
! 428: } {1 2.0 3}
! 429: do_test index-13.2 {
! 430: set ::idxlist [execsql {
! 431: SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
! 432: }]
! 433: llength $::idxlist
! 434: } {3}
! 435: for {set i 0} {$i<[llength $::idxlist]} {incr i} {
! 436: do_test index-13.3.$i {
! 437: catchsql "
! 438: DROP INDEX '[lindex $::idxlist $i]';
! 439: "
! 440: } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
! 441: }
! 442: do_test index-13.4 {
! 443: execsql {
! 444: INSERT INTO t5 VALUES('a','b','c');
! 445: SELECT * FROM t5;
! 446: }
! 447: } {1 2.0 3 a b c}
! 448: integrity_check index-13.5
! 449:
! 450: # Check the sort order of data in an index.
! 451: #
! 452: do_test index-14.1 {
! 453: execsql {
! 454: CREATE TABLE t6(a,b,c);
! 455: CREATE INDEX t6i1 ON t6(a,b);
! 456: INSERT INTO t6 VALUES('','',1);
! 457: INSERT INTO t6 VALUES('',NULL,2);
! 458: INSERT INTO t6 VALUES(NULL,'',3);
! 459: INSERT INTO t6 VALUES('abc',123,4);
! 460: INSERT INTO t6 VALUES(123,'abc',5);
! 461: SELECT c FROM t6 ORDER BY a,b;
! 462: }
! 463: } {3 5 2 1 4}
! 464: do_test index-14.2 {
! 465: execsql {
! 466: SELECT c FROM t6 WHERE a='';
! 467: }
! 468: } {2 1}
! 469: do_test index-14.3 {
! 470: execsql {
! 471: SELECT c FROM t6 WHERE b='';
! 472: }
! 473: } {1 3}
! 474: do_test index-14.4 {
! 475: execsql {
! 476: SELECT c FROM t6 WHERE a>'';
! 477: }
! 478: } {4}
! 479: do_test index-14.5 {
! 480: execsql {
! 481: SELECT c FROM t6 WHERE a>='';
! 482: }
! 483: } {2 1 4}
! 484: do_test index-14.6 {
! 485: execsql {
! 486: SELECT c FROM t6 WHERE a>123;
! 487: }
! 488: } {2 1 4}
! 489: do_test index-14.7 {
! 490: execsql {
! 491: SELECT c FROM t6 WHERE a>=123;
! 492: }
! 493: } {5 2 1 4}
! 494: do_test index-14.8 {
! 495: execsql {
! 496: SELECT c FROM t6 WHERE a<'abc';
! 497: }
! 498: } {5 2 1}
! 499: do_test index-14.9 {
! 500: execsql {
! 501: SELECT c FROM t6 WHERE a<='abc';
! 502: }
! 503: } {5 2 1 4}
! 504: do_test index-14.10 {
! 505: execsql {
! 506: SELECT c FROM t6 WHERE a<='';
! 507: }
! 508: } {5 2 1}
! 509: do_test index-14.11 {
! 510: execsql {
! 511: SELECT c FROM t6 WHERE a<'';
! 512: }
! 513: } {5}
! 514: integrity_check index-14.12
! 515:
! 516: do_test index-15.1 {
! 517: execsql {
! 518: DELETE FROM t1;
! 519: SELECT * FROM t1;
! 520: }
! 521: } {}
! 522: do_test index-15.2 {
! 523: execsql {
! 524: INSERT INTO t1 VALUES('1.234e5',1);
! 525: INSERT INTO t1 VALUES('12.33e04',2);
! 526: INSERT INTO t1 VALUES('12.35E4',3);
! 527: INSERT INTO t1 VALUES('12.34e',4);
! 528: INSERT INTO t1 VALUES('12.32e+4',5);
! 529: INSERT INTO t1 VALUES('12.36E+04',6);
! 530: INSERT INTO t1 VALUES('12.36E+',7);
! 531: INSERT INTO t1 VALUES('+123.10000E+0003',8);
! 532: INSERT INTO t1 VALUES('+',9);
! 533: INSERT INTO t1 VALUES('+12347.E+02',10);
! 534: INSERT INTO t1 VALUES('+12347E+02',11);
! 535: INSERT INTO t1 VALUES('+.125E+04',12);
! 536: INSERT INTO t1 VALUES('-.125E+04',13);
! 537: INSERT INTO t1 VALUES('.125E+0',14);
! 538: INSERT INTO t1 VALUES('.125',15);
! 539: SELECT b FROM t1 ORDER BY a, b;
! 540: }
! 541: } {13 14 15 12 8 5 2 1 3 6 10 11 9 4 7}
! 542: do_test index-15.3 {
! 543: execsql {
! 544: SELECT b FROM t1 WHERE typeof(a) IN ('integer','real') ORDER BY b;
! 545: }
! 546: } {1 2 3 5 6 8 10 11 12 13 14 15}
! 547: integrity_check index-15.4
! 548:
! 549: # The following tests - index-16.* - test that when a table definition
! 550: # includes qualifications that specify the same constraint twice only a
! 551: # single index is generated to enforce the constraint.
! 552: #
! 553: # For example: "CREATE TABLE abc( x PRIMARY KEY, UNIQUE(x) );"
! 554: #
! 555: do_test index-16.1 {
! 556: execsql {
! 557: CREATE TABLE t7(c UNIQUE PRIMARY KEY);
! 558: SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
! 559: }
! 560: } {1}
! 561: do_test index-16.2 {
! 562: execsql {
! 563: DROP TABLE t7;
! 564: CREATE TABLE t7(c UNIQUE PRIMARY KEY);
! 565: SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
! 566: }
! 567: } {1}
! 568: do_test index-16.3 {
! 569: execsql {
! 570: DROP TABLE t7;
! 571: CREATE TABLE t7(c PRIMARY KEY, UNIQUE(c) );
! 572: SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
! 573: }
! 574: } {1}
! 575: do_test index-16.4 {
! 576: execsql {
! 577: DROP TABLE t7;
! 578: CREATE TABLE t7(c, d , UNIQUE(c, d), PRIMARY KEY(c, d) );
! 579: SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
! 580: }
! 581: } {1}
! 582: do_test index-16.5 {
! 583: execsql {
! 584: DROP TABLE t7;
! 585: CREATE TABLE t7(c, d , UNIQUE(c), PRIMARY KEY(c, d) );
! 586: SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
! 587: }
! 588: } {2}
! 589:
! 590: # Test that automatically create indices are named correctly. The current
! 591: # convention is: "sqlite_autoindex_<table name>_<integer>"
! 592: #
! 593: # Then check that it is an error to try to drop any automtically created
! 594: # indices.
! 595: do_test index-17.1 {
! 596: execsql {
! 597: DROP TABLE t7;
! 598: CREATE TABLE t7(c, d UNIQUE, UNIQUE(c), PRIMARY KEY(c, d) );
! 599: SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
! 600: }
! 601: } {sqlite_autoindex_t7_1 sqlite_autoindex_t7_2 sqlite_autoindex_t7_3}
! 602: do_test index-17.2 {
! 603: catchsql {
! 604: DROP INDEX sqlite_autoindex_t7_1;
! 605: }
! 606: } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
! 607: do_test index-17.3 {
! 608: catchsql {
! 609: DROP INDEX IF EXISTS sqlite_autoindex_t7_1;
! 610: }
! 611: } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
! 612: do_test index-17.4 {
! 613: catchsql {
! 614: DROP INDEX IF EXISTS no_such_index;
! 615: }
! 616: } {0 {}}
! 617:
! 618:
! 619: # The following tests ensure that it is not possible to explicitly name
! 620: # a schema object with a name beginning with "sqlite_". Granted that is a
! 621: # little outside the focus of this test scripts, but this has got to be
! 622: # tested somewhere.
! 623: do_test index-18.1 {
! 624: catchsql {
! 625: CREATE TABLE sqlite_t1(a, b, c);
! 626: }
! 627: } {1 {object name reserved for internal use: sqlite_t1}}
! 628: do_test index-18.2 {
! 629: catchsql {
! 630: CREATE INDEX sqlite_i1 ON t7(c);
! 631: }
! 632: } {1 {object name reserved for internal use: sqlite_i1}}
! 633: ifcapable view {
! 634: do_test index-18.3 {
! 635: catchsql {
! 636: CREATE VIEW sqlite_v1 AS SELECT * FROM t7;
! 637: }
! 638: } {1 {object name reserved for internal use: sqlite_v1}}
! 639: } ;# ifcapable view
! 640: ifcapable {trigger} {
! 641: do_test index-18.4 {
! 642: catchsql {
! 643: CREATE TRIGGER sqlite_tr1 BEFORE INSERT ON t7 BEGIN SELECT 1; END;
! 644: }
! 645: } {1 {object name reserved for internal use: sqlite_tr1}}
! 646: }
! 647: do_test index-18.5 {
! 648: execsql {
! 649: DROP TABLE t7;
! 650: }
! 651: } {}
! 652:
! 653: # These tests ensure that if multiple table definition constraints are
! 654: # implemented by a single indice, the correct ON CONFLICT policy applies.
! 655: ifcapable conflict {
! 656: do_test index-19.1 {
! 657: execsql {
! 658: CREATE TABLE t7(a UNIQUE PRIMARY KEY);
! 659: CREATE TABLE t8(a UNIQUE PRIMARY KEY ON CONFLICT ROLLBACK);
! 660: INSERT INTO t7 VALUES(1);
! 661: INSERT INTO t8 VALUES(1);
! 662: }
! 663: } {}
! 664: do_test index-19.2 {
! 665: catchsql {
! 666: BEGIN;
! 667: INSERT INTO t7 VALUES(1);
! 668: }
! 669: } {1 {column a is not unique}}
! 670: do_test index-19.3 {
! 671: catchsql {
! 672: BEGIN;
! 673: }
! 674: } {1 {cannot start a transaction within a transaction}}
! 675: do_test index-19.4 {
! 676: catchsql {
! 677: INSERT INTO t8 VALUES(1);
! 678: }
! 679: } {1 {column a is not unique}}
! 680: do_test index-19.5 {
! 681: catchsql {
! 682: BEGIN;
! 683: COMMIT;
! 684: }
! 685: } {0 {}}
! 686: do_test index-19.6 {
! 687: catchsql {
! 688: DROP TABLE t7;
! 689: DROP TABLE t8;
! 690: CREATE TABLE t7(
! 691: a PRIMARY KEY ON CONFLICT FAIL,
! 692: UNIQUE(a) ON CONFLICT IGNORE
! 693: );
! 694: }
! 695: } {1 {conflicting ON CONFLICT clauses specified}}
! 696: } ; # end of "ifcapable conflict" block
! 697:
! 698: ifcapable {reindex} {
! 699: do_test index-19.7 {
! 700: execsql REINDEX
! 701: } {}
! 702: }
! 703: integrity_check index-19.8
! 704:
! 705: # Drop index with a quoted name. Ticket #695.
! 706: #
! 707: do_test index-20.1 {
! 708: execsql {
! 709: CREATE INDEX "t6i2" ON t6(c);
! 710: DROP INDEX "t6i2";
! 711: }
! 712: } {}
! 713: do_test index-20.2 {
! 714: execsql {
! 715: DROP INDEX "t6i1";
! 716: }
! 717: } {}
! 718:
! 719:
! 720: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to index.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