SQLite

  switch( op ){

    /* Mutex configuration options are only available in a threadsafe
    ** compile.
    */
#if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0  /* IMP: R-54466-46756 */
    case SQLITE_CONFIG_SINGLETHREAD: {
      /* EVIDENCE-OF: R-02748-19096 This option sets the threading mode to
      ** Single-thread. */
      sqlite3GlobalConfig.bCoreMutex = 0;  /* Disable mutex on core */
      sqlite3GlobalConfig.bFullMutex = 0;  /* Disable mutex on connections */
      break;
    }
#endif
#if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0 /* IMP: R-20520-54086 */
    case SQLITE_CONFIG_MULTITHREAD: {
      /* EVIDENCE-OF: R-14374-42468 This option sets the threading mode to
      ** Multi-thread. */
      sqlite3GlobalConfig.bCoreMutex = 1;  /* Enable mutex on core */
      sqlite3GlobalConfig.bFullMutex = 0;  /* Disable mutex on connections */
      break;
    }
#endif
#if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0 /* IMP: R-59593-21810 */
    case SQLITE_CONFIG_SERIALIZED: {
      /* EVIDENCE-OF: R-41220-51800 This option sets the threading mode to
      ** Serialized. */
      sqlite3GlobalConfig.bCoreMutex = 1;  /* Enable mutex on core */
      sqlite3GlobalConfig.bFullMutex = 1;  /* Enable mutex on connections */
      break;
    }
#endif
#if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0 /* IMP: R-63666-48755 */
    case SQLITE_CONFIG_MUTEX: {
      /* Specify an alternative mutex implementation */
      sqlite3GlobalConfig.mutex = *va_arg(ap, sqlite3_mutex_methods*);

    5.1    "INSERT INTO a1 DEFAULT VALUES"     {}
    5.2    "SELECT * FROM a1"                  {{} {}}

    6.1    "INSERT INTO a1 DEFAULT VALUES"     {}
    6.2    "SELECT * FROM a1"                  {{} {} {} {}}
}

# EVIDENCE-OF: R-03235-45250 The "REPLACE" and "INSERT OR action" forms
# specify an alternative constraint conflict resolution algorithm to use
# during this one INSERT command.
#
# EVIDENCE-OF: R-23110-47146 the parser allows the use of the single
# keyword REPLACE as an alias for "INSERT OR REPLACE".
#
#    The two requirements above are tested by e_select-4.1.* and
#    e_select-4.2.*, respectively.
#

# 2008-10-04
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix indexedby

# Create a schema with some indexes.
#

  0 1 0 {SCAN TABLE t1}
}

# Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 
# attached to a table in the FROM clause, but not to a sub-select or
# SQL view. Also test that specifying an index that does not exist or
# is attached to a different table is detected as an error.
#
# EVIDENCE-OF: R-63761-48810 -- syntax diagram qualified-table-name
# 
# EVIDENCE-OF: R-58230-57098 The "INDEXED BY index-name" phrase
# specifies that the named index must be used in order to look up values
# on the preceding table.
#
do_test indexedby-2.1 {
  execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
} {}
do_test indexedby-2.1b {
  execsql { SELECT * FROM main.t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
} {}
do_test indexedby-2.2 {
  execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
} {}
do_test indexedby-2.2b {
  execsql { SELECT * FROM main.t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
} {}
do_test indexedby-2.3 {
  execsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'}
} {}
# EVIDENCE-OF: R-44699-55558 The INDEXED BY clause does not give the
# optimizer hints about which index to use; it gives the optimizer a
# requirement of which index to use.
# EVIDENCE-OF: R-15800-25719 If index-name does not exist or cannot be
# used for the query, then the preparation of the SQL statement fails.
#
do_test indexedby-2.4 {
  catchsql { SELECT * FROM t1 INDEXED BY i3 WHERE a = 'one' AND b = 'two'}
} {1 {no such index: i3}}

# EVIDENCE-OF: R-62112-42456 If the query optimizer is unable to use the
# index specified by the INDEX BY clause, then the query will fail with
# an error.
do_test indexedby-2.4.1 {
  catchsql { SELECT b FROM t1 INDEXED BY i1 WHERE b = 'two' }
} {1 {no query solution}}

do_test indexedby-2.5 {
  catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'}
} {1 {no such index: i5}}
do_test indexedby-2.6 {
  catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'}
} {1 {near "WHERE": syntax error}}
do_test indexedby-2.7 {
  catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' }
} {1 {no such index: i1}}


# Tests for single table cases.
#
# EVIDENCE-OF: R-37002-28871 The "NOT INDEXED" clause specifies that no
# index shall be used when accessing the preceding table, including
# implied indices create by UNIQUE and PRIMARY KEY constraints. However,
# the rowid can still be used to look up entries even when "NOT INDEXED"
# is specified.
#
do_execsql_test indexedby-3.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a = 'one' AND b = 'two'
} {/SEARCH TABLE t1 USING INDEX/}
do_execsql_test indexedby-3.1.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
} {0 0 0 {SCAN TABLE t1}}
do_execsql_test indexedby-3.1.2 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE rowid=1
} {/SEARCH TABLE t1 USING INTEGER PRIMARY KEY .rowid=/}


do_execsql_test indexedby-3.2 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
do_execsql_test indexedby-3.3 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'

do_execsql_test indexedby-6.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 
} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
do_execsql_test indexedby-6.2 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 
} {0 0 0 {SCAN TABLE t1}}

# EVIDENCE-OF: R-40297-14464 The INDEXED BY phrase forces the SQLite
# query planner to use a particular named index on a DELETE, SELECT, or
# UPDATE statement.
#
# Test that "INDEXED BY" can be used in a DELETE statement.
# 
do_execsql_test indexedby-7.1 {
  EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 
} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
do_execsql_test indexedby-7.2 {
  EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5