SQLite

** Make sure at least one set of Win32 APIs is available.
*/
#if !defined(SQLITE_WIN32_HAS_ANSI) && !defined(SQLITE_WIN32_HAS_WIDE)
#  error "At least one of SQLITE_WIN32_HAS_ANSI and SQLITE_WIN32_HAS_WIDE\
 must be defined."
#endif

/*
** Define the required Windows SDK version constants if they are not
** already available.
*/
#ifndef NTDDI_WIN8
#  define NTDDI_WIN8                        0x06020000
#endif

#ifndef NTDDI_WINBLUE
#  define NTDDI_WINBLUE                     0x06030000
#endif

/*
** Check if the GetVersionEx[AW] functions should be considered deprecated
** and avoid using them in that case.  It should be noted here that if the
** value of the SQLITE_WIN32_GETVERSIONEX pre-processor macro is zero
** (whether via this block or via being manually specified), that implies
** the underlying operating system will always be based on the Windows NT
** Kernel.
*/
#ifndef SQLITE_WIN32_GETVERSIONEX
#  if defined(NTDDI_VERSION) && NTDDI_VERSION >= NTDDI_WINBLUE
#    define SQLITE_WIN32_GETVERSIONEX   0
#  else
#    define SQLITE_WIN32_GETVERSIONEX   1
#  endif
#endif

/*
** This constant should already be defined (in the "WinDef.h" SDK file).
*/
#ifndef MAX_PATH
#  define MAX_PATH                      (260)
#endif

  { "GetTickCount",            (SYSCALL)GetTickCount,            0 },
#else
  { "GetTickCount",            (SYSCALL)0,                       0 },
#endif

#define osGetTickCount ((DWORD(WINAPI*)(VOID))aSyscall[33].pCurrent)

#if defined(SQLITE_WIN32_HAS_ANSI) && defined(SQLITE_WIN32_GETVERSIONEX) && \
        SQLITE_WIN32_GETVERSIONEX
  { "GetVersionExA",           (SYSCALL)GetVersionExA,           0 },
#else
  { "GetVersionExA",           (SYSCALL)0,                       0 },
#endif

#define osGetVersionExA ((BOOL(WINAPI*)( \
        LPOSVERSIONINFOA))aSyscall[34].pCurrent)

#if !SQLITE_OS_WINRT && defined(SQLITE_WIN32_HAS_WIDE) && \
        defined(SQLITE_WIN32_GETVERSIONEX) && SQLITE_WIN32_GETVERSIONEX
  { "GetVersionExW",           (SYSCALL)GetVersionExW,           0 },
#else
  { "GetVersionExW",           (SYSCALL)0,                       0 },
#endif

#define osGetVersionExW ((BOOL(WINAPI*)( \
        LPOSVERSIONINFOW))aSyscall[35].pCurrent)

** Here is an interesting observation:  Win95, Win98, and WinME lack
** the LockFileEx() API.  But we can still statically link against that
** API as long as we don't call it when running Win95/98/ME.  A call to
** this routine is used to determine if the host is Win95/98/ME or
** WinNT/2K/XP so that we will know whether or not we can safely call
** the LockFileEx() API.
*/




#if !defined(SQLITE_WIN32_GETVERSIONEX) || !SQLITE_WIN32_GETVERSIONEX
# define osIsNT()  (1)
#elif SQLITE_OS_WINCE || SQLITE_OS_WINRT || !defined(SQLITE_WIN32_HAS_ANSI)
# define osIsNT()  (1)
#elif !defined(SQLITE_WIN32_HAS_WIDE)
# define osIsNT()  (0)
#else
  static int osIsNT(void){
    if( sqlite3_os_type==0 ){
#if defined(NTDDI_VERSION) && NTDDI_VERSION >= NTDDI_WIN8

            if( iCol==pTab->iPKey ){
              iCol = -1;
            }
            break;
          }
        }
        if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) && HasRowid(pTab) ){
          /* IMP: R-24309-18625 */
          /* IMP: R-44911-55124 */
          iCol = -1;
        }
        if( iCol<pTab->nCol ){
          cnt++;
          if( iCol<0 ){
            pExpr->affinity = SQLITE_AFF_INTEGER;
          }else if( pExpr->iTable==0 ){
            testcase( iCol==31 );

      ** explain mode. However, always executing it allows us an easy
      ** was to reset to explain mode in case the user previously
      ** did an .explain followed by a .width, .mode or .header
      ** command.
      */
      p->mode = MODE_Explain;
      p->showHeader = 1;
      memset(p->colWidth,0,sizeof(p->colWidth));
      p->colWidth[0] = 4;                  /* addr */
      p->colWidth[1] = 13;                 /* opcode */
      p->colWidth[2] = 4;                  /* P1 */
      p->colWidth[3] = 4;                  /* P2 */
      p->colWidth[4] = 4;                  /* P3 */
      p->colWidth[5] = 13;                 /* P4 */
      p->colWidth[6] = 2;                  /* P5 */

**
** <blockquote>
** <table border="1">
** <tr><th> Internal<br>Type <th> Requested<br>Type <th>  Conversion
**
** <tr><td>  NULL    <td> INTEGER   <td> Result is 0
** <tr><td>  NULL    <td>  FLOAT    <td> Result is 0.0
** <tr><td>  NULL    <td>   TEXT    <td> Result is a NULL pointer
** <tr><td>  NULL    <td>   BLOB    <td> Result is a NULL pointer
** <tr><td> INTEGER  <td>  FLOAT    <td> Convert from integer to float
** <tr><td> INTEGER  <td>   TEXT    <td> ASCII rendering of the integer
** <tr><td> INTEGER  <td>   BLOB    <td> Same as INTEGER->TEXT
** <tr><td>  FLOAT   <td> INTEGER   <td> [CAST] to INTEGER
** <tr><td>  FLOAT   <td>   TEXT    <td> ASCII rendering of the float
** <tr><td>  FLOAT   <td>   BLOB    <td> [CAST] to BLOB
** <tr><td>  TEXT    <td> INTEGER   <td> [CAST] to INTEGER
** <tr><td>  TEXT    <td>  FLOAT    <td> [CAST] to REAL
** <tr><td>  TEXT    <td>   BLOB    <td> No change
** <tr><td>  BLOB    <td> INTEGER   <td> [CAST] to INTEGER
** <tr><td>  BLOB    <td>  FLOAT    <td> [CAST] to REAL
** <tr><td>  BLOB    <td>   TEXT    <td> Add a zero terminator if needed
** </table>
** </blockquote>)^
**
** The table above makes reference to standard C library functions atoi()
** and atof().  SQLite does not really use these functions.  It has its
** own equivalent internal routines.  The atoi() and atof() names are

    if( (pIn3->flags & MEM_Int)==0 ){
      if( (pIn3->flags & MEM_Real)==0 ){
        /* If the P3 value cannot be converted into any kind of a number,
        ** then the seek is not possible, so jump to P2 */
        pc = pOp->p2 - 1;
        break;
      }








      /* If the approximation iKey is larger than the actual real search
      ** term, substitute >= for > and < for <=. e.g. if the search term
      ** is 4.9 and the integer approximation 5:
      **

      **        (x >  4.9)    ->     (x >= 5)
      **        (x <= 4.9)    ->     (x <  5)
      */
      if( pIn3->r<(double)iKey ){
        assert( OP_SeekGe==(OP_SeekGt-1) );



        assert( OP_SeekLt==(OP_SeekLe-1) );
        assert( (OP_SeekLe & 0x0001)==(OP_SeekGt & 0x0001) );


        if( (oc & 0x0001)==(OP_SeekGt & 0x0001) ) oc--;
      }







      /* If the approximation iKey is smaller than the actual real search
      ** term, substitute <= for < and > for >=.  */
      else if( pIn3->r>(double)iKey ){
        assert( OP_SeekLe==(OP_SeekLt+1) );

        assert( OP_SeekGt==(OP_SeekGe+1) );
        assert( (OP_SeekLt & 0x0001)==(OP_SeekGe & 0x0001) );
        if( (oc & 0x0001)==(OP_SeekLt & 0x0001) ) oc++;
      }
    } 
    rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, 0, (u64)iKey, 0, &res);
    if( rc!=SQLITE_OK ){
      goto abort_due_to_error;
    }
    if( res==0 ){

  }

#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
  /* Invoke the pre-update hook, if any */
  if( db->xPreUpdateCallback 
   && pOp->p4type==P4_TABLE
   && (!(pOp->p5 & OPFLAG_ISUPDATE) || pC->rowidIsValid==0)
   && HasRowid(pTab)
  ){
    sqlite3VdbePreUpdateHook(p, pC, SQLITE_INSERT, zDb, pTab, iKey, pOp->p2);
  }
#endif

  if( pOp->p5 & OPFLAG_NCHANGE ) p->nChange++;
  if( pOp->p5 & OPFLAG_LASTROWID ) db->lastRowid = lastRowid = iKey;

                          (pOp->p5 & OPFLAG_APPEND)!=0, seekResult
  );
  pC->rowidIsValid = 0;
  pC->deferredMoveto = 0;
  pC->cacheStatus = CACHE_STALE;

  /* Invoke the update-hook if required. */
  if( rc==SQLITE_OK && db->xUpdateCallback && op && HasRowid(pTab) ){
    db->xUpdateCallback(db->pUpdateArg, op, zDb, pTab->zName, iKey);
  }
  break;
}

/* Opcode: Delete P1 P2 P3 P4 *
**

  i64 iKey;
  VdbeCursor *pC;
  const char *zDb;
  Table *pTab;
  int opflags;

  opflags = pOp->p2;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  assert( pC->pCursor!=0 );  /* Only valid for real tables, no pseudotables */
  iKey = pC->lastRowid;      /* Only used for the update hook */

  /* The OP_Delete opcode always follows an OP_NotExists or OP_Last or
  ** OP_Column on the same table without any intervening operations that
  ** might move or invalidate the cursor.  Hence cursor pC is always pointing
  ** to the row to be deleted and the sqlite3VdbeCursorMoveto() operation
  ** below is always a no-op and cannot fail.  We will run it anyhow, though,
  ** to guard against future changes to the code generator.
  **/
  assert( pC->deferredMoveto==0 );
  rc = sqlite3VdbeCursorMoveto(pC);
  if( NEVER(rc!=SQLITE_OK) ) goto abort_due_to_error;

  /* If the update-hook or pre-update-hook will be invoked, set iKey to 
  ** the rowid of the row being deleted. Set zDb and zTab as well.
  */
  if( pOp->p4.z && HAS_UPDATE_HOOK(db) ){
    assert( pC->iDb>=0 );
    assert( pC->rowidIsValid || !HasRowid(pOp->p4.pTab) );

    iKey = pC->lastRowid;
    zDb = db->aDb[pC->iDb].zName;
    pTab = pOp->p4.pTab;
 }

#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
  /* Invoke the pre-update-hook if required. */
  if( db->xPreUpdateCallback && pOp->p4.z && HasRowid(pTab) ){
    assert( !(opflags & OPFLAG_ISUPDATE) || (aMem[pOp->p3].flags & MEM_Int) );
    sqlite3VdbePreUpdateHook(p, pC,
        (opflags & OPFLAG_ISUPDATE) ? SQLITE_UPDATE : SQLITE_DELETE, 
        zDb, pTab, iKey,
        pOp->p3
    );
  }
#endif

  if( opflags & OPFLAG_ISNOOP ) break;

  sqlite3BtreeSetCachedRowid(pC->pCursor, 0);
  rc = sqlite3BtreeDelete(pC->pCursor);
  pC->cacheStatus = CACHE_STALE;

  /* Update the change-counter and invoke the update-hook if required. */
  if( opflags & OPFLAG_NCHANGE ){
    p->nChange++;
    assert( pOp->p4.z );
    if( rc==SQLITE_OK && db->xUpdateCallback && HasRowid(pTab) ){
      db->xUpdateCallback(db->pUpdateArg, SQLITE_DELETE, zDb, pTab->zName,iKey);
    }
  }
  break;
}
/* Opcode: ResetCount * * * * *
**

  Mem *pOut;

  pVm = (Vdbe *)pStmt;
  if( pVm && pVm->pResultSet!=0 && i<pVm->nResColumn && i>=0 ){
    sqlite3_mutex_enter(pVm->db->mutex);
    pOut = &pVm->pResultSet[i];
  }else{





















    if( pVm && ALWAYS(pVm->db) ){
      sqlite3_mutex_enter(pVm->db->mutex);
      sqlite3Error(pVm->db, SQLITE_RANGE, 0);
    }
    pOut = (Mem*)columnNullValue();
  }
  return pOut;

  saved_prereq = pNew->prereq;
  saved_nOut = pNew->nOut;
  pNew->rSetup = 0;
  rLogSize = estLog(sqlite3LogEst(pProbe->aiRowEst[0]));

  /* Consider using a skip-scan if there are no WHERE clause constraints
  ** available for the left-most terms of the index, and if the average
  ** number of repeats in the left-most terms is at least 18.  The magic
  ** number 18 was found by experimentation to be the payoff point where
  ** skip-scan become faster than a full-scan.
  */
  if( pTerm==0
   && saved_nEq==saved_nSkip
   && saved_nEq+1<pProbe->nKeyCol
   && pProbe->aiRowEst[saved_nEq+1]>=18  /* TUNING: Minimum for skip-scan */
  ){
    LogEst nIter;
    pNew->u.btree.nEq++;
    pNew->u.btree.nSkip++;
    pNew->aLTerm[pNew->nLTerm++] = 0;
    pNew->wsFlags |= WHERE_SKIPSCAN;
    nIter = sqlite3LogEst(pProbe->aiRowEst[0]/pProbe->aiRowEst[saved_nEq+1]);

# Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT 
# NULL and CHECK constraints).
#

# EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one
# PRIMARY KEY.
# 
# EVIDENCE-OF: R-31826-01813 An error is raised if more than one PRIMARY
# KEY clause appears in a CREATE TABLE statement.
#
#     To test the two above, show that zero primary keys is Ok, one primary
#     key is Ok, and two or more primary keys is an error.
#
drop_all_tables
do_createtable_tests 4.1.1 {

# Tests for INTEGER PRIMARY KEY and rowid related statements.
#

# EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one
# of the special case-independent names "rowid", "oid", or "_rowid_" in
# place of a column name.
#
# EVIDENCE-OF: R-06726-07466 A column name can be any of the names
# defined in the CREATE TABLE statement or one of the following special
# identifiers: "ROWID", "OID", or "_ROWID_".
#
drop_all_tables
do_execsql_test 5.1.0 {
  CREATE TABLE t1(x, y);
  INSERT INTO t1 VALUES('one', 'first');
  INSERT INTO t1 VALUES('two', 'second');
  INSERT INTO t1 VALUES('three', 'third');
}

  9   "SELECT _RoWiD_ FROM t1"      {1 2 3}
}

# EVIDENCE-OF: R-26501-17306 If a table contains a user defined column
# named "rowid", "oid" or "_rowid_", then that name always refers the
# explicitly declared column and cannot be used to retrieve the integer
# rowid value.
#
# EVIDENCE-OF: R-44615-33286 The special identifiers only refer to the
# row key if the CREATE TABLE statement does not define a real column
# with the same name.
#
do_execsql_test 5.2.0 {
  CREATE TABLE t2(oid, b);
  CREATE TABLE t3(a, _rowid_);
  CREATE TABLE t4(a, b, rowid);

  INSERT INTO t2 VALUES('one', 'two');

} {R1 R2 R3}
do_test e_expr-26.1.6 { set ::evalcount } {5}


#-------------------------------------------------------------------------
# Test statements related to CAST expressions.
#
# EVIDENCE-OF: R-20854-17109 A CAST conversion is similar to the
# conversion that takes place when a column affinity is applied to a
# value except that with the CAST operator the conversion always takes
# place even if the conversion lossy and irreversible, whereas column
# affinity only changes the data type of a value if the change is
# lossless and reversible.
#
do_execsql_test e_expr-27.1.1 {
  CREATE TABLE t3(a TEXT, b REAL, c INTEGER);
  INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5);
  SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3;
} {blob UVU text 1.23abc real 4.5}
do_execsql_test e_expr-27.1.2 {

# EVIDENCE-OF: R-43164-44276 If there is no prefix that can be
# interpreted as an integer number, the result of the conversion is 0.
#
do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0
do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0
do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0

# EVIDENCE-OF: R-02752-50091 A cast of a REAL value into an INTEGER
# results in the integer between the REAL value and zero that is closest
# to the REAL value.
#
do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3
do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1
do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0

# EVIDENCE-OF: R-51517-40824 If a REAL is greater than the greatest
# possible signed integer (+9223372036854775807) then the result is the
# greatest possible signed integer and if the REAL is less than the
# least possible signed integer (-9223372036854775808) then the result
# is the least possible signed integer.
#
do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807
do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808
do_expr_test e_expr-31.2.3 { 
  CAST(-9223372036854775809.0 AS INT)
} integer -9223372036854775808
do_expr_test e_expr-31.2.4 { 

#
foreach {tn expr} {
    1  { ( SELECT x FROM t4 WHERE x>3 ORDER BY x )      }
    2  { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y )  }
} {
  do_expr_test e_expr-36.4.$tn $expr null {}
}

# EVIDENCE-OF: R-62477-06476 For example, the values NULL, 0.0, 0,
# 'english' and '0' are all considered to be false.
#
do_execsql_test e_expr-37.1 {
   SELECT CASE WHEN NULL THEN 'true' ELSE 'false' END;
} {false}
do_execsql_test e_expr-37.2 {
   SELECT CASE WHEN 0.0 THEN 'true' ELSE 'false' END;
} {false}
do_execsql_test e_expr-37.3 {
   SELECT CASE WHEN 0 THEN 'true' ELSE 'false' END;
} {false}
do_execsql_test e_expr-37.4 {
   SELECT CASE WHEN 'engligh' THEN 'true' ELSE 'false' END;
} {false}
do_execsql_test e_expr-37.5 {
   SELECT CASE WHEN '0' THEN 'true' ELSE 'false' END;
} {false}

# EVIDENCE-OF: R-55532-10108 Values 1, 1.0, 0.1, -0.1 and '1english' are
# considered to be true.
#
do_execsql_test e_expr-37.6 {
   SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END;
} {true}
do_execsql_test e_expr-37.7 {
   SELECT CASE WHEN 1.0 THEN 'true' ELSE 'false' END;
} {true}
do_execsql_test e_expr-37.8 {
   SELECT CASE WHEN 0.1 THEN 'true' ELSE 'false' END;
} {true}
do_execsql_test e_expr-37.9 {
   SELECT CASE WHEN -0.1 THEN 'true' ELSE 'false' END;
} {true}
do_execsql_test e_expr-37.10 {
   SELECT CASE WHEN '1english' THEN 'true' ELSE 'false' END;
} {true}


finish_test

# 4.2.* - Check that the update-hook is invoked for rows modified by trigger
#         bodies. Also that the database name is correctly reported when 
#         an attached database is modified.
# 4.3.* - Do some sorting, grouping, compound queries, population and 
#         depopulation of indices, to make sure the update-hook is not 
#         invoked incorrectly.
#
# EVIDENCE-OF: R-21999-45122 The sqlite3_update_hook() interface
# registers a callback function with the database connection identified
# by the first argument to be invoked whenever a row is updated,
# inserted or deleted in a rowid table.

# Simple tests
do_test hook-4.1.1a {
  catchsql {
    DROP TABLE t1;
  }
  unset -nocomplain ::update_hook
  set ::update_hook {}
  db update_hook [list lappend ::update_hook]
  #
  # EVIDENCE-OF: R-52223-27275 The update hook is not invoked when
  # internal system tables are modified (i.e. sqlite_master and
  # sqlite_sequence).
  #
  execsql {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
    CREATE TABLE t1w(a INT PRIMARY KEY, b) WITHOUT ROWID;
  }
  set ::update_hook
} {}
do_test hook-4.1.1b {
  execsql {
    INSERT INTO t1 VALUES(1, 'one');
    INSERT INTO t1 VALUES(2, 'two');
    INSERT INTO t1 VALUES(3, 'three');
    INSERT INTO t1w SELECT * FROM t1;
  }

} {}

# EVIDENCE-OF: R-15506-57666 The second callback argument is one of
# SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE, depending on the
# operation that caused the callback to be invoked.
#
# EVIDENCE-OF: R-29213-61195 The third and fourth arguments to the
# callback contain pointers to the database and table name containing
# the affected row.
#
# EVIDENCE-OF: R-30809-57812 The final callback parameter is the rowid
# of the row.
#
do_test hook-4.1.2 {
  set ::update_hook {}
  execsql {
    INSERT INTO t1 VALUES(4, 'four');
    DELETE FROM t1 WHERE b = 'two';
    UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;
    DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now)
  }
  set ::update_hook
} [list \
    INSERT main t1 4 \
    DELETE main t1 2 \
    UPDATE main t1 1 \
    UPDATE main t1 3 \
    DELETE main t1 1 \
    DELETE main t1 3 \
    DELETE main t1 4 \
]

# EVIDENCE-OF: R-61808-14344 The sqlite3_update_hook() interface does
# not fire callbacks for changes to a WITHOUT ROWID table.
#
# EVIDENCE-OF: R-33257-44249 The update hook is not invoked when WITHOUT
# ROWID tables are modified.
#
breakpoint
do_test hook-4.1.2w {
  set ::update_hook {}
  execsql {
    INSERT INTO t1w VALUES(4, 'four');
    DELETE FROM t1w WHERE b = 'two';
    UPDATE t1w SET b = '' WHERE a = 1 OR a = 3;
    DELETE FROM t1w WHERE 1; -- Avoid the truncate optimization (for now)
  }
  set ::update_hook
} {}

ifcapable trigger {
  # Update hook is not invoked for changes to sqlite_master
  #
  do_test hook-4.1.3 {
    set ::update_hook {}
    execsql {

        create table t1 (k integer primary key);
        insert into t1 values (1);
        insert into t1 values (NULL);
        insert into t1 values (NULL);
        select last_insert_rowid();
    }
} {0 3}

# EVIDENCE-OF: R-47220-63683 The sqlite3_last_insert_rowid() function
# does not work for WITHOUT ROWID tables.
#
do_test lastinsert-1.1w {
    catchsql {
        create table t1w (k integer primary key) WITHOUT ROWID;
        insert into t1w values (123456);
        select last_insert_rowid(); -- returns 3 from above.
    }
} {0 3}

# LIRID unchanged after an update on a table
do_test lastinsert-1.2 {
    catchsql {
        update t1 set k=4 where k=2;
        select last_insert_rowid();
    }

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the magic ROWID column that is
# found on all tables.
#
# EVIDENCE-OF: R-36924-43758 By default, every row in SQLite has a
# special column, usually called the "rowid", that uniquely identifies
# that row within the table.

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {

# 2013-11-27
#
# 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.
#
#***********************************************************************
#
# This file implements tests of the "skip-scan" query strategy.
#
# The test cases in this file are derived from the description of
# the skip-scan query strategy in the "optoverview.html" document.
#

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

do_execsql_test skipscan2-1.1 {
  CREATE TABLE people(
    name TEXT PRIMARY KEY,
    role TEXT NOT NULL,
    height INT NOT NULL, -- in cm
    CHECK( role IN ('student','teacher') )
  );
  CREATE INDEX people_idx1 ON people(role, height);
} {}
do_execsql_test skipscan2-1.2 {
  INSERT INTO people VALUES('Alice','student',156);
  INSERT INTO people VALUES('Bob','student',161);
  INSERT INTO people VALUES('Cindy','student',155);
  INSERT INTO people VALUES('David','student',181);
  INSERT INTO people VALUES('Emily','teacher',158);
  INSERT INTO people VALUES('Fred','student',163);
  INSERT INTO people VALUES('Ginny','student',169);
  INSERT INTO people VALUES('Harold','student',172);
  INSERT INTO people VALUES('Imma','student',179);
  INSERT INTO people VALUES('Jack','student',181);
  INSERT INTO people VALUES('Karen','student',163);
  INSERT INTO people VALUES('Logan','student',177);
  INSERT INTO people VALUES('Megan','teacher',159);
  INSERT INTO people VALUES('Nathan','student',163);
  INSERT INTO people VALUES('Olivia','student',161);
  INSERT INTO people VALUES('Patrick','teacher',180);
  INSERT INTO people VALUES('Quiana','student',182);
  INSERT INTO people VALUES('Robert','student',159);
  INSERT INTO people VALUES('Sally','student',166);
  INSERT INTO people VALUES('Tom','student',171);
  INSERT INTO people VALUES('Ursula','student',170);
  INSERT INTO people VALUES('Vance','student',179);
  INSERT INTO people VALUES('Willma','student',175);
  INSERT INTO people VALUES('Xavier','teacher',185);
  INSERT INTO people VALUES('Yvonne','student',149);
  INSERT INTO people VALUES('Zach','student',170);
}

# Without ANALYZE, a skip-scan is not used
#
do_execsql_test skipscan2-1.3 {
  SELECT name FROM people WHERE height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-1.3eqp {
  EXPLAIN QUERY PLAN
  SELECT name FROM people WHERE height>=180 ORDER BY +name;
} {~/*INDEX people_idx1 */}

# Now do an ANALYZE.  A skip-scan can be used after ANALYZE.
#
do_execsql_test skipscan2-1.4 {
  ANALYZE;
  -- We do not have enough people above to actually force the use
  -- of a skip-scan.  So make a manual adjustment to the stat1 table
  -- to make it seem like there are many more.
  UPDATE sqlite_stat1 SET stat='10000 5000 20' WHERE idx='people_idx1';
  ANALYZE sqlite_master;
}
db cache flush
do_execsql_test skipscan2-1.5 {
  SELECT name FROM people WHERE height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-1.5eqp {
  EXPLAIN QUERY PLAN
  SELECT name FROM people WHERE height>=180 ORDER BY +name;
} {/*INDEX people_idx1 */}

# Same answer with other formulations of the same query
#
do_execsql_test skipscan2-1.6 {
  SELECT name FROM people
   WHERE role IN (SELECT DISTINCT role FROM people)
     AND height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-1.7 {
  SELECT name FROM people WHERE role='teacher' AND height>=180
  UNION ALL
  SELECT name FROM people WHERE role='student' AND height>=180
  ORDER BY 1;
} {David Jack Patrick Quiana Xavier}

# Add 8 more people, bringing the total to 34.  Then the number of
# duplicates in the left-column of the index will be 17 and 
# skip-scan should not be used after an (unfudged) ANALYZE.
#
do_execsql_test skipscan2-1.8 {
  INSERT INTO people VALUES('Angie','student',166);
  INSERT INTO people VALUES('Brad','student',176);
  INSERT INTO people VALUES('Claire','student',168);
  INSERT INTO people VALUES('Donald','student',162);
  INSERT INTO people VALUES('Elaine','student',177);
  INSERT INTO people VALUES('Frazier','student',159);
  INSERT INTO people VALUES('Grace','student',179);
  INSERT INTO people VALUES('Horace','student',166);
  ANALYZE;
  SELECT stat FROM sqlite_stat1 WHERE idx='people_idx1';
} {{34 17 2}}
db cache flush
do_execsql_test skipscan2-1.9 {
  SELECT name FROM people WHERE height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-1.9eqp {
  EXPLAIN QUERY PLAN
  SELECT name FROM people WHERE height>=180 ORDER BY +name;
} {~/*INDEX people_idx1 */}

# Add 2 more people, bringing the total to 36.  Then the number of
# duplicates in the left-column of the index will be 18 and 
# skip-scan will be used after an (unfudged) ANALYZE.
#
do_execsql_test skipscan2-1.10 {
  INSERT INTO people VALUES('Ingrad','student',155);
  INSERT INTO people VALUES('Jacob','student',179);
  ANALYZE;
  SELECT stat FROM sqlite_stat1 WHERE idx='people_idx1';
} {{36 18 2}}
db cache flush
do_execsql_test skipscan2-1.11 {
  SELECT name FROM people WHERE height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-1.11eqp {
  EXPLAIN QUERY PLAN
  SELECT name FROM people WHERE height>=180 ORDER BY +name;
} {/*INDEX people_idx1 */}


# Repeat using a WITHOUT ROWID table.
#
do_execsql_test skipscan2-2.1 {
  CREATE TABLE peoplew(
    name TEXT PRIMARY KEY,
    role TEXT NOT NULL,
    height INT NOT NULL, -- in cm
    CHECK( role IN ('student','teacher') )
  ) WITHOUT ROWID;
  CREATE INDEX peoplew_idx1 ON peoplew(role, height);
  INSERT INTO peoplew(name,role,height)
     SELECT name, role, height FROM  people;
  ALTER TABLE people RENAME TO old_people;
  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-2.2 {
  SELECT name FROM peoplew
   WHERE role IN (SELECT DISTINCT role FROM peoplew)
     AND height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-2.2 {
  SELECT name FROM peoplew WHERE role='teacher' AND height>=180
  UNION ALL
  SELECT name FROM peoplew WHERE role='student' AND height>=180
  ORDER BY 1;
} {David Jack Patrick Quiana Xavier}

# Now do an ANALYZE.  A skip-scan can be used after ANALYZE.
#
do_execsql_test skipscan2-2.4 {
  ANALYZE;
}
db cache flush
do_execsql_test skipscan2-2.5 {
  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-2.5eqp {
  EXPLAIN QUERY PLAN
  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
} {/*INDEX peoplew_idx1 */}



finish_test

  }
  for(i=0; i<sizeof(aMult)/sizeof(aMult[0]); i++){
    if( sqlite3_stricmp(aMult[i].zSuffix, zArg)==0 ){
      v *= aMult[i].iMult;
      break;
    }
  }
  if( v>0x7fffffff ) fatal_error("parameter too large - max 2147483648");
  return (int)(isNeg? -v : v);
}

/* Return the current wall-clock time, in milliseconds */
sqlite3_int64 speedtest1_timestamp(void){
  static sqlite3_vfs *clockVfs = 0;
  sqlite3_int64 t;
  if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);

  }else{
    printf("%4d - %s%.*s ", iTestNum, zName, NAMEWIDTH-n, zDots);
    fflush(stdout);
  }
  sqlite3_free(zName);
  g.nResult = 0;
  g.iStart = speedtest1_timestamp();
  g.x = 0xad131d0b;
  g.y = 0x44f9eac8;
}

/* Complete a test case */
void speedtest1_end_test(void){
  sqlite3_int64 iElapseTime = speedtest1_timestamp() - g.iStart;
  if( !g.bSqlOnly ){
    g.iTotal += iElapseTime;

# 2013-11-26
#
# 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.
#
#***********************************************************************
#
# Requirements testing for WITHOUT ROWID tables.
#

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


# EVIDENCE-OF: R-36924-43758 By default, every row in SQLite has a
# special column, usually called the "rowid", that uniquely identifies
# that row within the table.
#
# EVIDENCE-OF: R-32341-39358 However if the phrase "WITHOUT ROWID" is
# added to the end of a CREATE TABLE statement, then the special "rowid"
# column is omitted.
#
do_execsql_test without_rowid5-1.1 {
  CREATE TABLE t1(a PRIMARY KEY,b,c);
  CREATE TABLE t1w(a PRIMARY KEY,b,c) WITHOUT ROWID;
  INSERT INTO t1 VALUES(1565,681,1148),(1429,1190,1619),(425,358,1306);
  INSERT INTO t1w SELECT a,b,c FROM t1;
  SELECT rowid, _rowid_, oid FROM t1 ORDER BY a DESC;
} {1 1 1 2 2 2 3 3 3}
do_catchsql_test without_rowid5-1.2 {
  SELECT rowid FROM t1w;
} {1 {no such column: rowid}}
do_catchsql_test without_rowid5-1.3 {
  SELECT _rowid_ FROM t1w;
} {1 {no such column: _rowid_}}
do_catchsql_test without_rowid5-1.4 {
  SELECT oid FROM t1w;
} {1 {no such column: oid}}

# EVIDENCE-OF: R-00217-01605 To create a WITHOUT ROWID table, simply add
# the keywords "WITHOUT ROWID" to the end of the CREATE TABLE statement.
# For example: CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY
# KEY, cnt INTEGER ) WITHOUT ROWID;
#
do_execsql_test without_rowid5-2.1 {
  CREATE TABLE IF NOT EXISTS wordcount(
    word TEXT PRIMARY KEY,
    cnt INTEGER
  ) WITHOUT ROWID;
  INSERT INTO wordcount VALUES('one',1);
} {}
do_catchsql_test without_rowid5-2.2 {
  SELECT rowid FROM wordcount;
} {1 {no such column: rowid}}

# EVIDENCE-OF: R-24770-17719 As with all SQL syntax, the case of the
# keywords does not matter. One can write "WITHOUT rowid" or "without
# rowid" or "WiThOuT rOwId" and it will mean the same thing.
#
do_execsql_test without_rowid5-2.3 {
  CREATE TABLE IF NOT EXISTS wordcount_b(
    word TEXT PRIMARY KEY,
    cnt INTEGER
  ) WITHOUT rowid;
  INSERT INTO wordcount_b VALUES('one',1);
} {}
do_catchsql_test without_rowid5-2.4 {
  SELECT rowid FROM wordcount_b;
} {1 {no such column: rowid}}
do_execsql_test without_rowid5-2.5 {
  CREATE TABLE IF NOT EXISTS wordcount_c(
    word TEXT PRIMARY KEY,
    cnt INTEGER
  ) without rowid;
  INSERT INTO wordcount_c VALUES('one',1);
} {}
do_catchsql_test without_rowid5-2.6 {
  SELECT rowid FROM wordcount_c;
} {1 {no such column: rowid}}
do_execsql_test without_rowid5-2.7 {
  CREATE TABLE IF NOT EXISTS wordcount_d(
    word TEXT PRIMARY KEY,
    cnt INTEGER
  ) WITHOUT rowid;
  INSERT INTO wordcount_d VALUES('one',1);
} {}
do_catchsql_test without_rowid5-2.8 {
  SELECT rowid FROM wordcount_d;
} {1 {no such column: rowid}}

# EVIDENCE-OF: R-01418-51310 However, only "rowid" works as the keyword
# in the CREATE TABLE statement.
#
do_catchsql_test without_rowid5-3.1 {
  CREATE TABLE IF NOT EXISTS error1(
    word TEXT PRIMARY KEY,
    cnt INTEGER
  ) WITHOUT _rowid_;
} {1 {unknown table option: _rowid_}}  
do_catchsql_test without_rowid5-3.2 {
  CREATE TABLE IF NOT EXISTS error2(
    word TEXT PRIMARY KEY,
    cnt INTEGER
  ) WITHOUT oid;
} {1 {unknown table option: oid}}  

# EVIDENCE-OF: R-58033-17334 An error is raised if a CREATE TABLE
# statement with the WITHOUT ROWID clause lacks a PRIMARY KEY.
#
# EVIDENCE-OF: R-63443-09418 Every WITHOUT ROWID table must have a
# PRIMARY KEY.
#
# EVIDENCE-OF: R-27966-31616 An attempt to create a WITHOUT ROWID table
# without a PRIMARY KEY results in an error.
#
do_catchsql_test without_rowid5-4.1 {
  CREATE TABLE IF NOT EXISTS error3(
    word TEXT UNIQUE,
    cnt INTEGER
  ) WITHOUT ROWID;
} {1 {PRIMARY KEY missing on table error3}}

# EVIDENCE-OF: R-48230-36247 The special behaviors associated "INTEGER
# PRIMARY KEY" do not apply on WITHOUT ROWID tables.
#
do_execsql_test without_rowid5-5.1 {
  CREATE TABLE ipk(key INTEGER PRIMARY KEY, val TEXT) WITHOUT ROWID;
  INSERT INTO ipk VALUES('rival','bonus'); -- ok to insert non-integer key
  SELECT * FROM ipk;
} {rival bonus}
do_catchsql_test without_rowid5-5.2 {
  INSERT INTO ipk VALUES(NULL,'sample'); -- no automatic generation of keys
} {1 {NOT NULL constraint failed: ipk.key}}

# EVIDENCE-OF: R-33142-02092 AUTOINCREMENT does not work on WITHOUT
# ROWID tables.
#
# EVIDENCE-OF: R-53084-07740 An error is raised if the "AUTOINCREMENT"
# keyword is used in the CREATE TABLE statement for a WITHOUT ROWID
# table.
#
do_catchsql_test without_rowid5-5.3 {
  CREATE TABLE ipk2(key INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)WITHOUT ROWID;
} {1 {AUTOINCREMENT not allowed on WITHOUT ROWID tables}}

# EVIDENCE-OF: R-27831-00579 NOT NULL is enforced on every column of the
# PRIMARY KEY in a WITHOUT ROWID table.
#
# EVIDENCE-OF: R-29781-51289 So, ordinary rowid tables in SQLite violate
# the SQL standard and allow NULL values in PRIMARY KEY fields.
#
# EVIDENCE-OF: R-27472-62612 But WITHOUT ROWID tables do follow the
# standard and will throw an error on any attempt to insert a NULL into
# a PRIMARY KEY column.
#
do_execsql_test without_rowid5-5.4 {
  CREATE TABLE nn(a, b, c, d, e, PRIMARY KEY(c,a,e));
  CREATE TABLE nnw(a, b, c, d, e, PRIMARY KEY(c,a,e)) WITHOUT ROWID;
  INSERT INTO nn VALUES(1,2,3,4,5);
  INSERT INTO nnw VALUES(1,2,3,4,5);
} {}
do_execsql_test without_rowid5-5.5 {
  INSERT INTO nn VALUES(NULL, 3,4,5,6);
  INSERT INTO nn VALUES(3,4,NULL,7,8);
  INSERT INTO nn VALUES(4,5,6,7,NULL);
  SELECT count(*) FROM nn;
} {4}
do_catchsql_test without_rowid5-5.6 {
  INSERT INTO nnw VALUES(NULL, 3,4,5,6);
} {1 {NOT NULL constraint failed: nnw.a}}
do_catchsql_test without_rowid5-5.7 {
  INSERT INTO nnw VALUES(3,4,NULL,7,8)
} {1 {NOT NULL constraint failed: nnw.c}}
do_catchsql_test without_rowid5-5.8 {
  INSERT INTO nnw VALUES(4,5,6,7,NULL)
} {1 {NOT NULL constraint failed: nnw.e}}
do_execsql_test without_rowid5-5.9 {
  SELECT count(*) FROM nnw;
} {1}

# EVIDENCE-OF: R-12643-30541 The incremental blob I/O mechanism does not
# work for WITHOUT ROWID tables.
#
# EVIDENCE-OF: R-25760-33257 The sqlite3_blob_open() interface will fail
# for a WITHOUT ROWID table.
#
do_execsql_test without_rowid5-6.1 {
  CREATE TABLE b1(a INTEGER PRIMARY KEY, b BLOB) WITHOUT ROWID;
  INSERT INTO b1 VALUES(1,x'0102030405060708090a0b0c0d0e0f');
} {}
do_test without_rowid5-6.2 {
  set rc [catch {db incrblob b1 b 1} msg]
  lappend rc $msg
} {1 {cannot open table without rowid: b1}}


finish_test