SQLite4
Check-in [de90289df1c867ccb9086173435330cb07cfd508]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
SHA1 Hash:de90289df1c867ccb9086173435330cb07cfd508
Date: 2013-06-29 20:12:15
User: dan
Comment:Also run legacy scripts index.test and laststmtchanges.test.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c

2387
2388
2389
2390
2391
2392
2393
2394


2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
    sqlite4ErrorMsg(pParse, "virtual tables may not be indexed");
    return 0;
  }

  /* Ensure that the proposed index name is not reserved. */
  assert( pName->z!=0 );
  zName = sqlite4NameFromToken(db, pName);
  if( zName==0 || sqlite4CheckObjectName(pParse, zName) ) return 0;



  /* Unless SQLite is currently parsing an existing database schema, check
  ** that there is not already an index or table using the proposed name.  */
  if( !db->init.busy ){
    char *zDb = db->aDb[iDb].zName;
    if( sqlite4FindTable(db, zName, zDb)!=0 ){
      sqlite4ErrorMsg(pParse, "there is already a table named %s", zName);
    }
    else if( sqlite4FindIndex(db, zName, zDb)!=0 ){
      if( p->bIfnotexist ){
        assert( !db->init.busy );







|
>
>



|







2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
    sqlite4ErrorMsg(pParse, "virtual tables may not be indexed");
    return 0;
  }

  /* Ensure that the proposed index name is not reserved. */
  assert( pName->z!=0 );
  zName = sqlite4NameFromToken(db, pName);
  if( zName==0 || sqlite4CheckObjectName(pParse, zName) ){
    pTab = 0;
  }

  /* Unless SQLite is currently parsing an existing database schema, check
  ** that there is not already an index or table using the proposed name.  */
  else if( !db->init.busy ){
    char *zDb = db->aDb[iDb].zName;
    if( sqlite4FindTable(db, zName, zDb)!=0 ){
      sqlite4ErrorMsg(pParse, "there is already a table named %s", zName);
    }
    else if( sqlite4FindIndex(db, zName, zDb)!=0 ){
      if( p->bIfnotexist ){
        assert( !db->init.busy );

Changes to test/index.test

229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
...
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
...
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
...
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
610
611
612
613
614
615
616
...
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
  execsql {SELECT f1 FROM test1 WHERE f2=65536}
} {16}
do_test index-7.3 {
  execsql {
    SELECT name FROM sqlite_master 
    WHERE type='index' AND tbl_name='test1'
  }
} {sqlite_autoindex_test1_1}
do_test index-7.4 {
  execsql {DROP table test1}
  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
} {}
integrity_check index-7.5

# Make sure we cannot drop a non-existant index.
................................................................................
      PRIMARY KEY(b)
    );
  }
  for {set i 1} {$i<=50} {incr i} {
    execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
  }
  set sqlite_search_count 0
  concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
} {0.1 2}
integrity_check index-11.2


# Numeric strings should compare as if they were numbers.  So even if the
# strings are not character-by-character the same, if they represent the
# same number they should compare equal to one another.  Verify that this
# is true in indices.
................................................................................
   );
   INSERT INTO t5 VALUES(1,2,3);
   SELECT * FROM t5;
  }
} {1 2.0 3}
do_test index-13.2 {
  set ::idxlist [execsql {
    SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
  }]
  llength $::idxlist
} {3}
for {set i 0} {$i<[llength $::idxlist]} {incr i} {
  do_test index-13.3.$i {
    catchsql "
      DROP INDEX '[lindex $::idxlist $i]';
    "
  } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
}
................................................................................
} {1}
do_test index-16.3 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c PRIMARY KEY, UNIQUE(c) );
    SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {1}
do_test index-16.4 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c, d , UNIQUE(c, d), PRIMARY KEY(c, d) );
    SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {1}
do_test index-16.5 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c, d , UNIQUE(c), PRIMARY KEY(c, d) );
    SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {2}

# Test that automatically create indices are named correctly. The current
# convention is: "sqlite_autoindex_<table name>_<integer>"
#
# Then check that it is an error to try to drop any automtically created
# indices.
do_test index-17.1 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c, d UNIQUE, UNIQUE(c), PRIMARY KEY(c, d) );
    SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {sqlite_autoindex_t7_1 sqlite_autoindex_t7_2 sqlite_autoindex_t7_3}
do_test index-17.2 {
  catchsql {
    DROP INDEX sqlite_autoindex_t7_1;
  }
} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
do_test index-17.3 {
  catchsql {
    DROP INDEX IF EXISTS sqlite_autoindex_t7_1;
  }
} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
do_test index-17.4 {
  catchsql {
    DROP INDEX IF EXISTS no_such_index;
  }
} {0 {}}
................................................................................
    }
  } {}
  do_test index-19.2 {
    catchsql {
      BEGIN;
      INSERT INTO t7 VALUES(1);
    }
  } {1 {column a is not unique}}
  do_test index-19.3 {
    catchsql {
      BEGIN;
    }
  } {1 {cannot start a transaction within a transaction}}
  do_test index-19.4 {
    catchsql {
      INSERT INTO t8 VALUES(1);
    }
  } {1 {column a is not unique}}
  do_test index-19.5 {
    catchsql {
      BEGIN;
      COMMIT;
    }
  } {0 {}}
  do_test index-19.6 {







|







 







|
|







 







|


|







 







|













|












|


|




|







 







|









|







229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
...
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
...
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
...
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
610
611
612
613
614
615
616
...
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
  execsql {SELECT f1 FROM test1 WHERE f2=65536}
} {16}
do_test index-7.3 {
  execsql {
    SELECT name FROM sqlite_master 
    WHERE type='index' AND tbl_name='test1'
  }
} {}
do_test index-7.4 {
  execsql {DROP table test1}
  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
} {}
integrity_check index-7.5

# Make sure we cannot drop a non-existant index.
................................................................................
      PRIMARY KEY(b)
    );
  }
  for {set i 1} {$i<=50} {incr i} {
    execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
  }
  set sqlite_search_count 0
  execsql {SELECT c FROM t3 WHERE b==10}
} {0.1}
integrity_check index-11.2


# Numeric strings should compare as if they were numbers.  So even if the
# strings are not character-by-character the same, if they represent the
# same number they should compare equal to one another.  Verify that this
# is true in indices.
................................................................................
   );
   INSERT INTO t5 VALUES(1,2,3);
   SELECT * FROM t5;
  }
} {1 2.0 3}
do_test index-13.2 {
  set ::idxlist [execsql {
    SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='t5';
  }]
  llength $::idxlist
} {2}
for {set i 0} {$i<[llength $::idxlist]} {incr i} {
  do_test index-13.3.$i {
    catchsql "
      DROP INDEX '[lindex $::idxlist $i]';
    "
  } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
}
................................................................................
} {1}
do_test index-16.3 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c PRIMARY KEY, UNIQUE(c) );
    SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {0}
do_test index-16.4 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c, d , UNIQUE(c, d), PRIMARY KEY(c, d) );
    SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {1}
do_test index-16.5 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c, d , UNIQUE(c), PRIMARY KEY(c, d) );
    SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {1}

# Test that automatically create indices are named correctly. The current
# convention is: "sqlite_autoindex_<table name>_<integer>"
#
# Then check that it is an error to try to drop any automtically created
# indices.
do_test index-17.1 {
  execsql {
    DROP TABLE t7;
    CREATE TABLE t7(c, d UNIQUE, UNIQUE(c), PRIMARY KEY(c, d) );
    SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
  }
} {sqlite_t7_unique1 sqlite_t7_unique2}
do_test index-17.2 {
  catchsql {
    DROP INDEX sqlite_t7_unique1;
  }
} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
do_test index-17.3 {
  catchsql {
    DROP INDEX IF EXISTS sqlite_t7_unique1;
  }
} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
do_test index-17.4 {
  catchsql {
    DROP INDEX IF EXISTS no_such_index;
  }
} {0 {}}
................................................................................
    }
  } {}
  do_test index-19.2 {
    catchsql {
      BEGIN;
      INSERT INTO t7 VALUES(1);
    }
  } {1 {PRIMARY KEY must be unique}}
  do_test index-19.3 {
    catchsql {
      BEGIN;
    }
  } {1 {cannot start a transaction within a transaction}}
  do_test index-19.4 {
    catchsql {
      INSERT INTO t8 VALUES(1);
    }
  } {1 {PRIMARY KEY must be unique}}
  do_test index-19.5 {
    catchsql {
      BEGIN;
      COMMIT;
    }
  } {0 {}}
  do_test index-19.6 {

Changes to test/interrupt.test

76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
    COMMIT;
    UPDATE t1 SET b=substr(b,-5,5);
    SELECT count(*) from t1;
  }
} 64
set origsize [file size test.db]
set cksum [db eval {SELECT md5sum(a || b) FROM t1}]
ifcapable {vacuum} {
  interrupt_test interrupt-2.2 {VACUUM} {} 100
}
do_test interrupt-2.3 {
  execsql {
    SELECT md5sum(a || b) FROM t1;
  }
} $cksum
ifcapable {vacuum && !default_autovacuum} {
  do_test interrupt-2.4 {
    expr {$::origsize>[file size test.db]}
  } 1
}
ifcapable {explain} {
  do_test interrupt-2.5 {
    set sql {EXPLAIN SELECT max(a,b), a, b FROM t1}
    execsql $sql
    set rc [catch {db eval $sql {sqlite4_interrupt $DB}} msg]
    lappend rc $msg
  } {1 interrupted}







<
<
<





<
<
<
<
<







76
77
78
79
80
81
82



83
84
85
86
87





88
89
90
91
92
93
94
    COMMIT;
    UPDATE t1 SET b=substr(b,-5,5);
    SELECT count(*) from t1;
  }
} 64
set origsize [file size test.db]
set cksum [db eval {SELECT md5sum(a || b) FROM t1}]



do_test interrupt-2.3 {
  execsql {
    SELECT md5sum(a || b) FROM t1;
  }
} $cksum





ifcapable {explain} {
  do_test interrupt-2.5 {
    set sql {EXPLAIN SELECT max(a,b), a, b FROM t1}
    execsql $sql
    set rc [catch {db eval $sql {sqlite4_interrupt $DB}} msg]
    lappend rc $msg
  } {1 interrupted}

Changes to test/laststmtchanges.test

146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
# changes() changed properly after update into table containing after trigger
do_test laststmtchanges-3.1 {
    catchsql {
        drop trigger r1;
        delete from t2; delete from t2;
        create trigger r1 after update on t1 for each row begin
            insert into t2 values (NULL, changes(), NULL);
            delete from t0 where oid=1 or oid=2;
            update t2 set v2=changes();
        end;
        update t1 set k=k;
        select changes();
    }
} {0 1}








|







146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
# changes() changed properly after update into table containing after trigger
do_test laststmtchanges-3.1 {
    catchsql {
        drop trigger r1;
        delete from t2; delete from t2;
        create trigger r1 after update on t1 for each row begin
            insert into t2 values (NULL, changes(), NULL);
            delete from t0 where rowid=1 or rowid=2;
            update t2 set v2=changes();
        end;
        update t1 set k=k;
        select changes();
    }
} {0 1}

Changes to test/permutations.test

180
181
182
183
184
185
186
187
188
189
190

191
192
193
194
195
196
197
  expr.test
  exec.test
  exists.test
  fkey1.test fkey2.test fkey3.test fkey4.test
  func.test func2.test func3.test 
  fuzz.test fuzz2.test 
  in.test in2.test in3.test in4.test
  index2.test index3.test index4.test 
  insert.test insert2.test insert3.test insert5.test
  join.test join2.test join3.test join4.test join5.test join6.test
  keyword1.test

  limit.test
  main.test
  manydb.test
  misc5.test misc6.test
  misuse.test
  notnull.test
  null.test







|



>







180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
  expr.test
  exec.test
  exists.test
  fkey1.test fkey2.test fkey3.test fkey4.test
  func.test func2.test func3.test 
  fuzz.test fuzz2.test 
  in.test in2.test in3.test in4.test
  index.test index2.test index3.test index4.test 
  insert.test insert2.test insert3.test insert5.test
  join.test join2.test join3.test join4.test join5.test join6.test
  keyword1.test
  laststmtchanges.test
  limit.test
  main.test
  manydb.test
  misc5.test misc6.test
  misuse.test
  notnull.test
  null.test