Wrap all automatic index changes inside SQLITE_OMIT_AUTOMATIC_INDEX. Add

the automatic_index PRAGMA to turn it on and off.

FossilOrigin-Name: a811a47fbe4f757a7ab575ae5a0f65177a3f07c0

9 files changed, 62 insertions, 30 deletions

diff --git a/manifest b/manifest
index b147f9bb0..c4e8fd2cd 100644
--- a/manifest
+++ b/manifest
@@ -1,8 +1,8 @@
 -----BEGIN PGP SIGNED MESSAGE-----
 Hash: SHA1
 
-C Make\ssure\sthat\sall\sautomatic\sindices\sare\scovering\sindices.\s\sOtherwise,\sthe\ntable\sand\sindex\smight\sbe\sused\stogether\sin\sa\squery\sbut\sthe\stable\scould\sget\nout\sof\ssync\swith\sthe\sautomatic\sindex\sthrough\sout-of-band\schanges.
-D 2010-04-07T14:59:45
+C Wrap\sall\sautomatic\sindex\schanges\sinside\sSQLITE_OMIT_AUTOMATIC_INDEX.\s\sAdd\nthe\sautomatic_index\sPRAGMA\sto\sturn\sit\son\sand\soff.
+D 2010-04-07T16:54:58
 F Makefile.arm-wince-mingw32ce-gcc fcd5e9cd67fe88836360bb4f9ef4cb7f8e2fb5a0
 F Makefile.in 4f2f967b7e58a35bb74fb7ec8ae90e0f4ca7868b
 F Makefile.linux-gcc d53183f4aa6a9192d249731c90dbdffbd2c68654
@@ -118,7 +118,7 @@ F src/btreeInt.h 22447d259639271774a931cbf66aa55112846681
 F src/build.c 11100b66fb97638d2d874c1d34d8db90650bb1d7
 F src/callback.c 908f3e0172c3d4058f4ca0acd42c637c52e9669f
 F src/complete.c dc1d136c0feee03c2f7550bafc0d29075e36deac
-F src/ctime.c ceb247eb31620bba66a94c3f697db489a1652353
+F src/ctime.c 51553a859994d01d8bf3500747f66a890c459774
 F src/date.c 485a4409a384310e6d93fd1104a9d0a8658becd9
 F src/delete.c 610dc008e88a9599f905f5cbe9577ac9c36e0581
 F src/expr.c 6baed2a0448d494233d9c0a610eea018ab386a32
@@ -134,7 +134,7 @@ F src/journal.c b0ea6b70b532961118ab70301c00a33089f9315c
 F src/legacy.c a199d7683d60cef73089e892409113e69c23a99f
 F src/lempar.c 7f026423f4d71d989e719a743f98a1cbd4e6d99e
 F src/loadext.c 1c7a61ce1281041f437333f366a96aa0d29bb581
-F src/main.c 7d89bb6dcc6993a8d32f4f22dae3e57c50a41399
+F src/main.c c0e7192bad5b90544508b241eb2487ac661de890
 F src/malloc.c a08f16d134f0bfab6b20c3cd142ebf3e58235a6a
 F src/mem0.c 6a55ebe57c46ca1a7d98da93aaa07f99f1059645
 F src/mem1.c 89d4ea8d5cdd55635cbaa48ad53132af6294cbb2
@@ -161,7 +161,7 @@ F src/parse.y ace5c7a125d9f2a410e431ee3209034105045f7e
 F src/pcache.c ace8f6a5ecd4711cc66a1b23053be7109bd437cf
 F src/pcache.h c683390d50f856d4cd8e24342ae62027d1bb6050
 F src/pcache1.c 6dc1871ce8ead9187161c370a58cd06c84221f76
-F src/pragma.c 56d95f76154a5f873c32eae485bb625f3c70be46
+F src/pragma.c e166ea41544f8e57a08db86dbe87212b7d378fe8
 F src/prepare.c e8164a925274cb7803b5f8ab3cf42f2a508ad33f
 F src/printf.c 5f5b65a83e63f2096a541a340722a509fa0240a7
 F src/random.c cd4a67b3953b88019f8cd4ccd81394a8ddfaba50
@@ -171,7 +171,7 @@ F src/select.c 5a08245cb18b7ddf2456274653599cbf738d3830
 F src/shell.c c40427c7245535a04a9cb4a417b6cc05c022e6a4
 F src/sqlite.h.in c5001b77dc0cb046136da65d8dbdf234048be21d
 F src/sqlite3ext.h 69dfb8116af51b84a029cddb3b35062354270c89
-F src/sqliteInt.h 2b4b274e934ecad5505aabcef5f10b3f2d6f80a6
+F src/sqliteInt.h bd7ff54663bdd5b57e0eb8b49aca5a3a3c60119a
 F src/sqliteLimit.h 3afab2291762b5d09ae20c18feb8e9fa935a60a6
 F src/status.c 4df6fe7dce2d256130b905847c6c60055882bdbe
 F src/table.c 2cd62736f845d82200acfa1287e33feb3c15d62e
@@ -189,7 +189,7 @@ F src/test_async.c c1656facbaf43cb2e71b62621e5b9eb080e2621c
 F src/test_autoext.c 30e7bd98ab6d70a62bb9ba572e4c7df347fe645e
 F src/test_backup.c c129c91127e9b46e335715ae2e75756e25ba27de
 F src/test_btree.c 47cd771250f09cdc6e12dda5bc71bc0b3abc96e2
-F src/test_config.c 5844274bf6cec4af3e6461fb3e2d349082635e81
+F src/test_config.c 8c408fbffbe8082d1d3dc55044ddfd5580f3c9b9
 F src/test_devsym.c de3c9af2bb9a8b1e44525c449e4ec3f88e3d4110
 F src/test_func.c 13b582345fb1185a93e46c53310fae8547dcce20
 F src/test_hexio.c 1237f000ec7a491009b1233f5c626ea71bce1ea2
@@ -224,7 +224,7 @@ F src/vdbemem.c 2a82f455f6ca6f78b59fb312f96054c04ae0ead1
 F src/vdbetrace.c 864cef96919323482ebd9986f2132435115e9cc2
 F src/vtab.c a0f8a40274e4261696ef57aa806de2776ab72cda
 F src/walker.c 3112bb3afe1d85dc52317cb1d752055e9a781f8f
-F src/where.c 186b72b01f9afe0b4dbb68e90e14d6dceb287aed
+F src/where.c b3a57071f47adf46ddc8c0a2c2ccac2a7bcc5350
 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2
 F test/alias.test 4529fbc152f190268a15f9384a5651bbbabc9d87
 F test/all.test 14165b3e32715b700b5f0cbf8f6e3833dda0be45
@@ -285,7 +285,7 @@ F test/coalesce.test cee0dccb9fbd2d494b77234bccf9dc6c6786eb91
 F test/collate1.test e3eaa48c21e150814be1a7b852d2a8af24458d04
 F test/collate2.test 04cebe4a033be319d6ddbb3bbc69464e01700b49
 F test/collate3.test d28d2cfab2c3a3d4628ae4b2b7afc9965daa3b4c
-F test/collate4.test 9e685f813b6312e43f8a338facd47fc3154afe23
+F test/collate4.test 3d3f123f83fd8ccda6f48d617e44e661b9870c7d
 F test/collate5.test fe0f43c4740d7b71b959cac668d19e42f2e06e4d
 F test/collate6.test 8be65a182abaac8011a622131486dafb8076e907
 F test/collate7.test fac8db7aac3978466c04ae892cc74dcf2bc031aa
@@ -797,14 +797,14 @@ F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff
 F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224
 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e
 F tool/vdbe-compress.tcl d70ea6d8a19e3571d7ab8c9b75cba86d1173ff0f
-P c0f67ea131c3e237e46311b1a3dcb3febf2ea036
-R 0e5421f4e341a57c85b79d77e22a68e3
+P 2364313142668b3d0ad144447b4862709be929cd
+R 3be9dccae2ed8d28aad0029df3535432
 U drh
-Z b97f0b4c3618aad21acc127e7b33602d
+Z 13e999191d01e0cdb3247533af5674e2
 -----BEGIN PGP SIGNATURE-----
 Version: GnuPG v1.4.6 (GNU/Linux)
 
-iD8DBQFLvJ3koxKgR168RlERAm7oAJ0Xzm3BtJ0ZfA9RjpPh9mHBaxTARgCfQDNh
-ievdlvN7dk9MClGNwMCoKvk=
-=ZzMO
+iD8DBQFLvLjloxKgR168RlERAidKAJ4r8IFGHOGa4sY/vEmjxAhz+N1UcACffePf
+Df12WbFRqxuZFQNtbO5BXhs=
+=2bnY
 -----END PGP SIGNATURE-----
diff --git a/manifest.uuid b/manifest.uuid
index 3ea21ea2f..7a41613e7 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-2364313142668b3d0ad144447b4862709be929cd
\ No newline at end of file
+a811a47fbe4f757a7ab575ae5a0f65177a3f07c0
\ No newline at end of file
diff --git a/src/ctime.c b/src/ctime.c
index dacdc5883..e6af8f173 100644
--- a/src/ctime.c
+++ b/src/ctime.c
@@ -171,6 +171,9 @@ static const char * const azCompileOpt[] = {
 #ifdef SQLITE_OMIT_AUTOINIT
   "OMIT_AUTOINIT",
 #endif
+#ifdef SQLITE_OMIT_AUTOMATIC_INDEX
+  "OMIT_AUTOMATIC_INDEX",
+#endif
 #ifdef SQLITE_OMIT_AUTOVACUUM
   "OMIT_AUTOVACUUM",
 #endif
diff --git a/src/main.c b/src/main.c
index c80250791..cc03d50ed 100644
--- a/src/main.c
+++ b/src/main.c
@@ -1608,7 +1608,7 @@ static int openDatabase(
   db->autoCommit = 1;
   db->nextAutovac = -1;
   db->nextPagesize = 0;
-  db->flags |= SQLITE_ShortColNames
+  db->flags |= SQLITE_ShortColNames | SQLITE_AutoIndex
 #if SQLITE_DEFAULT_FILE_FORMAT<4
                  | SQLITE_LegacyFileFmt
 #endif
diff --git a/src/pragma.c b/src/pragma.c
index bd96e47c1..f03078f24 100644
--- a/src/pragma.c
+++ b/src/pragma.c
@@ -173,6 +173,9 @@ static int flagPragma(Parse *pParse, const char *zLeft, const char *zRight){
     { "legacy_file_format",       SQLITE_LegacyFileFmt },
     { "fullfsync",                SQLITE_FullFSync     },
     { "reverse_unordered_selects", SQLITE_ReverseOrder  },
+#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
+    { "automatic_index",          SQLITE_AutoIndex     },
+#endif
 #ifdef SQLITE_DEBUG
     { "sql_trace",                SQLITE_SqlTrace      },
     { "vdbe_listing",             SQLITE_VdbeListing   },
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 42ff66dd5..6007b8af9 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -912,6 +912,7 @@ struct sqlite3 {
 #define SQLITE_ReverseOrder   0x01000000  /* Reverse unordered SELECTs */
 #define SQLITE_RecTriggers    0x02000000  /* Enable recursive triggers */
 #define SQLITE_ForeignKeys    0x04000000  /* Enforce foreign key constraints  */
+#define SQLITE_AutoIndex      0x08000000  /* Enable automatic indexes */
 
 /*
 ** Bits of the sqlite3.flags field that are used by the
diff --git a/src/test_config.c b/src/test_config.c
index 735039264..8544ee535 100644
--- a/src/test_config.c
+++ b/src/test_config.c
@@ -127,6 +127,12 @@ static void set_options(Tcl_Interp *interp){
   Tcl_SetVar2(interp, "sqlite_options", "autoinc", "1", TCL_GLOBAL_ONLY);
 #endif
 
+#ifdef SQLITE_OMIT_AUTOMATIC_INDEX
+  Tcl_SetVar2(interp, "sqlite_options", "autoindex", "0", TCL_GLOBAL_ONLY);
+#else
+  Tcl_SetVar2(interp, "sqlite_options", "autoindex", "1", TCL_GLOBAL_ONLY);
+#endif
+
 #ifdef SQLITE_OMIT_AUTOVACUUM
   Tcl_SetVar2(interp, "sqlite_options", "autovacuum", "0", TCL_GLOBAL_ONLY);
 #else
diff --git a/src/where.c b/src/where.c
index b5601922a..6d6b967c4 100644
--- a/src/where.c
+++ b/src/where.c
@@ -1637,6 +1637,7 @@ static void bestOrClauseIndex(
 #endif /* SQLITE_OMIT_OR_OPTIMIZATION */
 }
 
+#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
 /*
 ** Return TRUE if the WHERE clause term pTerm is of a form where it
 ** could be used with an index to access pSrc, assuming an appropriate
@@ -1655,7 +1656,9 @@ static int termCanDriveIndex(
   if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
   return 1;
 }
+#endif
 
+#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
 /*
 ** If the query plan for pSrc specified in pCost is a full table scan
 ** and indexing is allows (if there is no NOT INDEXED clause) and it
@@ -1664,7 +1667,7 @@ static int termCanDriveIndex(
 ** is taken into account, then alter the query plan to use the
 ** transient index.
 */
-static void bestTransientIndex(
+static void bestAutomaticIndex(
   Parse *pParse,              /* The parsing context */
   WhereClause *pWC,           /* The WHERE clause */
   struct SrcList_item *pSrc,  /* The FROM clause term to search */
@@ -1678,6 +1681,10 @@ static void bestTransientIndex(
   WhereTerm *pWCEnd;          /* End of pWC->a[] */
   Table *pTable;              /* Table tht might be indexed */
 
+  if( (pParse->db->flags & SQLITE_AutoIndex)==0 ){
+    /* Automatic indices are disabled at run-time */
+    return;
+  }
   if( (pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)!=0 ){
     /* We already have some kind of index in use for this query. */
     return;
@@ -1712,12 +1719,18 @@ static void bestTransientIndex(
     }
   }
 }
+#else
+# define bestAutomaticIndex(A,B,C,D,E)  /* no-op */
+#endif /* SQLITE_OMIT_AUTOMATIC_INDEX */
 
+
+#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
 /*
-** Generate code to construct a transient index.  Also create the
-** corresponding Index structure and put it in pLevel->plan.u.pIdx.
+** Generate code to construct the Index object for an automatic index
+** and to set up the WhereLevel object pLevel so that the code generator
+** makes use of the automatic index.
 */
-static void constructTransientIndex(
+static void constructAutomaticIndex(
   Parse *pParse,              /* The parsing context */
   WhereClause *pWC,           /* The WHERE clause */
   struct SrcList_item *pSrc,  /* The FROM clause term to get the next index */
@@ -1811,7 +1824,8 @@ static void constructTransientIndex(
   }
   assert( n==pLevel->plan.nEq );
 
-  /* Add additional columns needed to make the index into a covering index */
+  /* Add additional columns needed to make the automatic index into
+  ** a covering index */
   for(i=0; i<mxBitCol; i++){
     if( extraCols & (1<<i) ){
       pIdx->aiColumn[n] = i;
@@ -1828,14 +1842,14 @@ static void constructTransientIndex(
   }
   assert( n==nColumn );
 
-  /* Create the transient index */
+  /* Create the automatic index */
   pKeyinfo = sqlite3IndexKeyinfo(pParse, pIdx);
   assert( pLevel->iIdxCur>=0 );
   sqlite3VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nColumn+1, 0,
                     (char*)pKeyinfo, P4_KEYINFO_HANDOFF);
   VdbeComment((v, "for %s", pTable->zName));
 
-  /* Fill the transient index with content */
+  /* Fill the automatic index with content */
   addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur);
   regRecord = sqlite3GetTempReg(pParse);
   sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 1);
@@ -1849,6 +1863,7 @@ static void constructTransientIndex(
   /* Jump here when skipping the initialization */
   sqlite3VdbeJumpHere(v, addrInit);
 }
+#endif /* SQLITE_OMIT_AUTOMATIC_INDEX */
 
 #ifndef SQLITE_OMIT_VIRTUALTABLE
 /*
@@ -2744,7 +2759,7 @@ static void bestBtreeIndex(
   ));
   
   bestOrClauseIndex(pParse, pWC, pSrc, notReady, pOrderBy, pCost);
-  bestTransientIndex(pParse, pWC, pSrc, notReady, pCost);
+  bestAutomaticIndex(pParse, pWC, pSrc, notReady, pCost);
   pCost->plan.wsFlags |= eqTermMask;
 }
 
@@ -4151,9 +4166,12 @@ WhereInfo *sqlite3WhereBegin(
     }else{
       sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
     }
+#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
     if( (pLevel->plan.wsFlags & WHERE_TEMP_INDEX)!=0 ){
-      constructTransientIndex(pParse, pWC, pTabItem, notReady, pLevel);
-    }else if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
+      constructAutomaticIndex(pParse, pWC, pTabItem, notReady, pLevel);
+    }else
+#endif
+    if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
       Index *pIx = pLevel->plan.u.pIdx;
       KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIx);
       int iIdxCur = pLevel->iIdxCur;
diff --git a/test/collate4.test b/test/collate4.test
index 7ed9f6069..12bc16ef2 100644
--- a/test/collate4.test
+++ b/test/collate4.test
@@ -321,6 +321,7 @@ do_test collate4-1.2.25 {
 #
 do_test collate4-2.1.0 {
   execsql {
+    PRAGMA automatic_index=OFF;
     CREATE TABLE collate4t1(a COLLATE NOCASE);
     CREATE TABLE collate4t2(b COLLATE TEXT);
 
@@ -341,7 +342,7 @@ do_test collate4-2.1.0 {
 } {}
 do_test collate4-2.1.1 {
   count {
-    SELECT * FROM collate4t2 NOT INDEXED, collate4t1 NOT INDEXED WHERE a = b;
+    SELECT * FROM collate4t2, collate4t1 WHERE a = b;
   }
 } {A a A A 19}
 do_test collate4-2.1.2 {
@@ -356,7 +357,7 @@ do_test collate4-2.1.3 {
   count {
     SELECT * FROM collate4t2, collate4t1 WHERE b = a;
   }
-} {A A 13}
+} {A A 19}
 do_test collate4-2.1.4 {
   execsql {
     DROP INDEX collate4i1;
@@ -365,7 +366,7 @@ do_test collate4-2.1.4 {
   count {
     SELECT * FROM collate4t2, collate4t1 WHERE a = b;
   }
-} {A a A A 14}
+} {A a A A 19}
 do_test collate4-2.1.5 {
   count {
     SELECT * FROM collate4t2, collate4t1 WHERE b = a;