SQLite

/*
** 2004 April 6
**
** 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.
**
*************************************************************************
** $Id: btree.c,v 1.276 2005/12/21 18:36:46 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.

}

/* Move the cursor so that it points to an entry near pKey/nKey.
** Return a success code.
**
** For INTKEY tables, only the nKey parameter is used.  pKey is
** ignored.  For other tables, nKey is the number of bytes of data
** in pKey.  The comparison function specified when the cursor was
** created is used to compare keys.
**
** If an exact match is not found, then the cursor is always
** left pointing at a leaf page which would hold the entry if it
** were present.  The cursor might point to an entry that comes
** before or after the key.
**

    }
    return sqlite3pager_sync(pBt->pPager, zMaster, nTrunc);
#endif
    return sqlite3pager_sync(pBt->pPager, zMaster, 0);
  }
  return SQLITE_OK;
}

**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.361 2005/12/21 18:36:46 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.

**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.508 2005/12/21 18:36:46 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*

      rc = sqlite3BtreeMoveto(pC->pCursor, 0, (u64)iKey, &res);
      if( rc!=SQLITE_OK ){
        goto abort_due_to_error;
      }
      pC->lastRowid = pTos->i;
      pC->rowidIsValid = res==0;
    }else{
      assert( pTos->flags & MEM_Blob );
      /* Stringify(pTos, db->enc); */
      rc = sqlite3BtreeMoveto(pC->pCursor, pTos->z, pTos->n, &res);
      if( rc!=SQLITE_OK ){
        goto abort_due_to_error;
      }
      pC->rowidIsValid = 0;
    }
    pC->deferredMoveto = 0;

    if( pKeyInfo->incrKey ){
      rc = -1;
    }else if( d1<nKey1 ){
      rc = 1;
    }else if( d2<nKey2 ){
      rc = -1;
    }

  }else if( pKeyInfo->aSortOrder && i<pKeyInfo->nField
               && pKeyInfo->aSortOrder[i] ){
    rc = -rc;
  }

  return rc;
}

/*

** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.189 2005/12/21 18:36:46 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)

      */
      int start;
      int nEq = pLevel->nEq;
      int topEq=0;        /* True if top limit uses ==. False is strictly < */
      int btmEq=0;        /* True if btm limit uses ==. False if strictly > */
      int topOp, btmOp;   /* Operators for the top and bottom search bounds */
      int testOp;
      int nNotNull;       /* Number of rows of index that must be non-NULL */
      int topLimit = (pLevel->flags & WHERE_TOP_LIMIT)!=0;
      int btmLimit = (pLevel->flags & WHERE_BTM_LIMIT)!=0;

      /* Generate code to evaluate all constraint terms using == or IN
      ** and level the values of those terms on the stack.
      */
      codeAllEqualityTerms(pParse, pLevel, &wc, notReady, brk);

      }

      /* Figure out what comparison operators to use for top and bottom 
      ** search bounds. For an ascending index, the bottom bound is a > or >=
      ** operator and the top bound is a < or <= operator.  For a descending
      ** index the operators are reversed.
      */
      nNotNull = nEq + topLimit;
      if( pIdx->keyInfo.aSortOrder[nEq]==SQLITE_SO_ASC ){
        topOp = WO_LT|WO_LE;
        btmOp = WO_GT|WO_GE;
      }else{
        topOp = WO_GT|WO_GE;
        btmOp = WO_LT|WO_LE;
        SWAP(int, topLimit, btmLimit);

        sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqlite3VdbeAddOp(v, testOp, iIdxCur, brk);
        if( (topEq && !bRev) || (!btmEq && bRev) ){
          sqlite3VdbeChangeP3(v, -1, "+", P3_STATIC);
        }
      }
      sqlite3VdbeAddOp(v, OP_RowKey, iIdxCur, 0);
      sqlite3VdbeAddOp(v, OP_IdxIsNull, nNotNull, cont);
      if( !omitTable ){
        sqlite3VdbeAddOp(v, OP_IdxRowid, iIdxCur, 0);
        sqlite3VdbeAddOp(v, OP_MoveGe, iCur, 0);
      }

      /* Record the instruction used to terminate the loop.
      */

# 2005 December 21
#
# 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 script is descending indices.
#
# $Id: descidx1.test,v 1.2 2005/12/21 18:36:46 drh Exp $
#

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

# This procedure sets the value of the file-format in file 'test.db'
# to $newval. Also, the schema cookie is incremented.

} {4 5 6 7 nosort}
do_test descidx1-3.25 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
} {4 5 6 7 nosort}
do_test descidx1-3.26 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
} {7 6 5 4 nosort}

# Create a table with indices that are descending on some terms and
# ascending on others.
#
do_test descidx1-4.1 {
  execsql {
    CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL);
    CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC);
    CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC);
    INSERT INTO t2 VALUES(1,'one',x'31',1.0);
    INSERT INTO t2 VALUES(2,'two',x'3232',2.0);
    INSERT INTO t2 VALUES(3,'three',x'333333',3.0);
    INSERT INTO t2 VALUES(4,'four',x'34343434',4.0);
    INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0);
    INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0);
    INSERT INTO t2 VALUES(2,'two',x'323232',2.1);
    INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2);
    INSERT INTO t2 VALUES(2,NULL,NULL,2.3);
    SELECT count(*) FROM t2;
  }
} {9}
do_test descidx1-4.2 {
  execsql {
    SELECT d FROM t2 ORDER BY a;
  }
} {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
do_test descidx1-4.3 {
  execsql {
    SELECT d FROM t2 WHERE a>=2;
  }
} {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
do_test descidx1-4.4 {
  execsql {
    SELECT d FROM t2 WHERE a>2;
  }
} {3.0 4.0 5.0 6.0}
do_test descidx1-4.5 {
  execsql {
    SELECT d FROM t2 WHERE a=2 AND b>'two';
  }
} {2.2}
do_test descidx1-4.6 {
  execsql {
    SELECT d FROM t2 WHERE a=2 AND b>='two';
  }
} {2.2 2.0 2.1}
do_test descidx1-4.7 {
  execsql {
    SELECT d FROM t2 WHERE a=2 AND b<'two';
  }
} {}
do_test descidx1-4.8 {
  execsql {
    SELECT d FROM t2 WHERE a=2 AND b<='two';
  }
} {2.0 2.1}

do_test descidx1-5.1 {
  execsql {
    CREATE TABLE t3(a,b,c,d);
    CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC);
    INSERT INTO t3 VALUES(0,0,0,0);
    INSERT INTO t3 VALUES(0,0,0,1);
    INSERT INTO t3 VALUES(0,0,1,0);
    INSERT INTO t3 VALUES(0,0,1,1);
    INSERT INTO t3 VALUES(0,1,0,0);
    INSERT INTO t3 VALUES(0,1,0,1);
    INSERT INTO t3 VALUES(0,1,1,0);
    INSERT INTO t3 VALUES(0,1,1,1);
    INSERT INTO t3 VALUES(1,0,0,0);
    INSERT INTO t3 VALUES(1,0,0,1);
    INSERT INTO t3 VALUES(1,0,1,0);
    INSERT INTO t3 VALUES(1,0,1,1);
    INSERT INTO t3 VALUES(1,1,0,0);
    INSERT INTO t3 VALUES(1,1,0,1);
    INSERT INTO t3 VALUES(1,1,1,0);
    INSERT INTO t3 VALUES(1,1,1,1);
    SELECT count(*) FROM t3;
  }
} {16}
do_test descidx1-5.2 {
  cksort {
    SELECT a||b||c||d FROM t3 ORDER BY a,b,c,d;
  }
} {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111 sort}
do_test descidx1-5.3 {
  cksort {
    SELECT a||b||c||d FROM t3 ORDER BY a DESC, b ASC, c DESC, d ASC;
  }
} {1010 1011 1000 1001 1110 1111 1100 1101 0010 0011 0000 0001 0110 0111 0100 0101 nosort}
do_test descidx1-5.4 {
  cksort {
    SELECT a||b||c||d FROM t3 ORDER BY a ASC, b DESC, c ASC, d DESC;
  }
} {0101 0100 0111 0110 0001 0000 0011 0010 1101 1100 1111 1110 1001 1000 1011 1010 nosort}
do_test descidx1-5.5 {
  cksort {
    SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b ASC, c DESC
  }
} {101 100 111 110 001 000 011 010 nosort}
do_test descidx1-5.6 {
  cksort {
    SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c ASC
  }
} {010 011 000 001 110 111 100 101 nosort}
do_test descidx1-5.7 {
  cksort {
    SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c DESC
  }
} {011 010 001 000 111 110 101 100 sort}
do_test descidx1-5.8 {
  cksort {
    SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b ASC, c ASC
  }
} {000 001 010 011 100 101 110 111 sort}
do_test descidx1-5.9 {
  cksort {
    SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b DESC, c ASC
  }
} {110 111 100 101 010 011 000 001 sort}

finish_test

# 2005 December 21
#
# 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 script is descending indices.
#
# $Id: descidx2.test,v 1.1 2005/12/21 18:36:46 drh Exp $
#

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

# This procedure sets the value of the file-format in file 'test.db'
# to $newval. Also, the schema cookie is incremented.
# 
proc set_file_format {newval} {
  set bt [btree_open test.db 10 0]
  btree_begin_transaction $bt
  set meta [btree_get_meta $bt]
  lset meta 2 $newval                    ;# File format
  lset meta 1 [expr [lindex $meta 1]+1]  ;# Schema cookie
  eval "btree_update_meta $bt $meta"
  btree_commit $bt
  btree_close $bt
}

# This procedure returns the value of the file-format in file 'test.db'.
# 
proc get_file_format {{fname test.db}} {
  set bt [btree_open $fname 10 0]
  set meta [btree_get_meta $bt]
  btree_close $bt
  lindex $meta 2
}

# Verify that the file format jumps to (at least) 4 as soon as a
# descending index is created.
#
do_test descidx2-1.1 {
  execsql {
    CREATE TABLE t1(a,b);
    CREATE INDEX i1 ON t1(b ASC);
  }
  get_file_format
} {1}
do_test descidx2-1.2 {
  execsql {
    CREATE INDEX i2 ON t1(a DESC);
  }
  get_file_format
} {4}

# Before adding any information to the database, set the file format
# back to three.  Then close and reopen the database.  With the file
# format set to three, SQLite should ignore the DESC argument on the
# index.
#
do_test descidx2-2.0 {
  set_file_format 3
  db close
  sqlite3 db test.db
  get_file_format
} {3}

# Put some information in the table and verify that the DESC
# on the index is ignored.
#
do_test descidx2-2.1 {
  execsql {
    INSERT INTO t1 VALUES(1,1);
    INSERT INTO t1 VALUES(2,2);
    INSERT INTO t1 SELECT a+2, a+2 FROM t1;
    INSERT INTO t1 SELECT a+4, a+4 FROM t1;
    SELECT b FROM t1 WHERE a>3 AND a<7;
  }
} {4 5 6}
do_test descidx2-2.2 {
  execsql {
    SELECT a FROM t1 WHERE b>3 AND b<7;
  }
} {4 5 6}
do_test descidx2-2.3 {
  execsql {
    SELECT b FROM t1 WHERE a>=3 AND a<7;
  }
} {3 4 5 6}
do_test descidx2-2.4 {
  execsql {
    SELECT b FROM t1 WHERE a>3 AND a<=7;
  }
} {4 5 6 7}
do_test descidx2-2.5 {
  execsql {
    SELECT b FROM t1 WHERE a>=3 AND a<=7;
  }
} {3 4 5 6 7}
do_test descidx2-2.6 {
  execsql {
    SELECT a FROM t1 WHERE b>=3 AND b<=7;
  }
} {3 4 5 6 7}

# 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
}

# Test sorting using a descending index.
#
do_test descidx2-3.1 {
  cksort {SELECT a FROM t1 ORDER BY a}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.2 {
  cksort {SELECT a FROM t1 ORDER BY a ASC}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.3 {
  cksort {SELECT a FROM t1 ORDER BY a DESC}
} {8 7 6 5 4 3 2 1 nosort}
do_test descidx2-3.4 {
  cksort {SELECT b FROM t1 ORDER BY a}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.5 {
  cksort {SELECT b FROM t1 ORDER BY a ASC}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.6 {
  cksort {SELECT b FROM t1 ORDER BY a DESC}
} {8 7 6 5 4 3 2 1 nosort}
do_test descidx2-3.7 {
  cksort {SELECT a FROM t1 ORDER BY b}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.8 {
  cksort {SELECT a FROM t1 ORDER BY b ASC}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.9 {
  cksort {SELECT a FROM t1 ORDER BY b DESC}
} {8 7 6 5 4 3 2 1 nosort}
do_test descidx2-3.10 {
  cksort {SELECT b FROM t1 ORDER BY b}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.11 {
  cksort {SELECT b FROM t1 ORDER BY b ASC}
} {1 2 3 4 5 6 7 8 nosort}
do_test descidx2-3.12 {
  cksort {SELECT b FROM t1 ORDER BY b DESC}
} {8 7 6 5 4 3 2 1 nosort}

do_test descidx2-3.21 {
  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
} {4 5 6 7 nosort}
do_test descidx2-3.22 {
  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
} {4 5 6 7 nosort}
do_test descidx2-3.23 {
  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
} {7 6 5 4 nosort}
do_test descidx2-3.24 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
} {4 5 6 7 nosort}
do_test descidx2-3.25 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
} {4 5 6 7 nosort}
do_test descidx2-3.26 {
  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
} {7 6 5 4 nosort}

finish_test