SQLite

**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the virtual table interfaces.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test8.c,v 1.14 2006/06/14 07:41:32 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

      sqlite3_free(zQuery);
      zQuery = zNew;
      zSep = "AND";
      pUsage->argvIndex = ++nArg;
      pUsage->omit = 1;
    }
  }

  /* If there is only one term in the ORDER BY clause, and it is
  ** on a column that this virtual table has an index for, then consume 
  ** the ORDER BY clause.
  */
  if( pIdxInfo->nOrderBy==1 && pVtab->aIndex[pIdxInfo->aOrderBy->iColumn] ){
    char *zCol = pVtab->aCol[pIdxInfo->aOrderBy->iColumn];
    char *zDir = pIdxInfo->aOrderBy->desc?"DESC":"ASC";
    zNew = sqlite3_mprintf("%s ORDER BY %s %s", zQuery, zCol, zDir);
    sqlite3_free(zQuery);
    zQuery = zNew;
    pIdxInfo->orderByConsumed = 1;
  }

  const int nOrderBy;        /* Number of terms in the ORDER BY clause */
  const struct sqlite3_index_orderby {
     int iColumn;              /* Column number */
     unsigned char desc;       /* True for DESC.  False for ASC. */
  } *const aOrderBy;         /* The ORDER BY clause */

  appendToEchoModule(pVtab->interp, "xBestIndex");;
  appendToEchoModule(pVtab->interp, zQuery);

  pIdxInfo->idxStr = zQuery;
  pIdxInfo->needToFreeIdxStr = 1;
  pIdxInfo->estimatedCost = 1.0;
  return SQLITE_OK;

# 2006 June 10
#
# 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 regression tests for SQLite library.  The
# focus of this file is creating and dropping virtual tables.
#
# $Id: vtab1.test,v 1.13 2006/06/14 07:41:32 danielk1977 Exp $

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

ifcapable !vtab {
  finish_test
  return

    SELECT * FROM t1 WHERE b BETWEEN 2 AND 10;
  }
} {1 2 3 4 5 6}
do_test vtab1-3.13 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?}      \
        xFilter    {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 2 10 ]

#----------------------------------------------------------------------
# Test case vtab1-3 test table scans and the echo module's 
# xBestIndex/xFilter handling of ORDER BY clauses.

# This procedure executes the SQL.  Then it checks to see if the OP_Sort
# opcode was executed.  If an OP_Sort did occur, then "sort" is appended
# to the result.  If no OP_Sort happened, then "nosort" is appended.
#
# This procedure is used to check to make sure sorting is or is not
# occurring as expected.
#
proc cksort {sql} {
  set ::sqlite_sort_count 0
  set data [execsql $sql]
  if {$::sqlite_sort_count} {set x sort} {set x nosort}
  lappend data $x
  return $data
}

do_test vtab1-4.1 {
  set echo_module ""
  cksort {
    SELECT b FROM t1 ORDER BY b;
  }
} {2 5 nosort}
do_test vtab1-4.2 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b ASC} \
        xFilter    {SELECT rowid, * FROM 'treal' ORDER BY b ASC} ]
do_test vtab1-4.3 {
  set echo_module ""
  cksort {
    SELECT b FROM t1 ORDER BY b DESC;
  }
} {5 2 nosort}
do_test vtab1-4.4 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b DESC} \
        xFilter    {SELECT rowid, * FROM 'treal' ORDER BY b DESC} ]
do_test vtab1-4.3 {
  set echo_module ""
  cksort {
    SELECT b FROM t1 ORDER BY b||'';
  }
} {2 5 sort}
do_test vtab1-4.4 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xFilter    {SELECT rowid, * FROM 'treal'} ]

finish_test