SQLite

**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.448 2007/11/12 09:50:26 danielk1977 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.

  sqlite3ExprDelete(pCheckExpr);
}

/*
** Set the collation function of the most recently parsed table column
** to the CollSeq given.
*/
void sqlite3AddCollateType(Parse *pParse, Token *pToken){
  Table *p;
  int i;
  char *zColl;              /* Dequoted name of collation sequence */

  if( (p = pParse->pNewTable)==0 ) return;
  i = p->nCol-1;

  zColl = sqlite3NameFromToken(pParse->db, pToken);
  if( !zColl ) return;

  if( sqlite3LocateCollSeq(pParse, zColl, -1) ){
    Index *pIdx;
    p->aCol[i].zColl = zColl;
  
    /* If the column is declared as "<name> PRIMARY KEY COLLATE <type>",
    ** then an index may have been created on this column before the
    ** collation type was added. Correct this if it is the case.
    */
    for(pIdx=p->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->nColumn==1 );
      if( pIdx->aiColumn[0]==i ){
        pIdx->azColl[0] = p->aCol[i].zColl;
      }
    }
  }else{
    sqlite3_free(zColl);
  }
}

/*
** This function returns the collation sequence for database native text
** encoding identified by the string zName, length nName.
**

    return;
  }

  if( pName1==0 || pName1->z==0 ){
    reindexDatabases(pParse, 0);
    return;
  }else if( pName2==0 || pName2->z==0 ){
    char *zColl;
    assert( pName1->z );
    zColl = sqlite3NameFromToken(pParse->db, pName1);
    if( !zColl ) return;
    pColl = sqlite3FindCollSeq(db, ENC(db), zColl, -1, 0);
    if( pColl ){

      if( zColl ){
        reindexDatabases(pParse, zColl);
        sqlite3_free(zColl);
      }
      return;
    }
    sqlite3_free(zColl);
  }
  iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pObjName);
  if( iDb<0 ) return;
  z = sqlite3NameFromToken(db, pObjName);
  if( z==0 ) return;
  zDb = db->aDb[iDb].zName;
  pTab = sqlite3FindTable(db, z, zDb);

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.316 2007/11/12 09:50:26 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**

** Set the collating sequence for expression pExpr to be the collating
** sequence named by pToken.   Return a pointer to the revised expression.
** The collating sequence is marked as "explicit" using the EP_ExpCollate
** flag.  An explicit collating sequence will override implicit
** collating sequences.
*/
Expr *sqlite3ExprSetColl(Parse *pParse, Expr *pExpr, Token *pName){
  char *zColl = 0;            /* Dequoted name of collation sequence */
  CollSeq *pColl;
  zColl = sqlite3NameFromToken(pParse->db, pName);
  if( pExpr && zColl ){
    pColl = sqlite3LocateCollSeq(pParse, zColl, -1);
    if( pColl ){
      pExpr->pColl = pColl;
      pExpr->flags |= EP_ExpCollate;
    }
  }
  sqlite3_free(zColl);
  return pExpr;
}

/*
** Return the default collation sequence for the expression pExpr. If
** there is no default collation type, return 0.
*/

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.235 2007/11/12 09:50:26 danielk1977 Exp $
*/

// All token codes are small integers with #defines that begin with "TK_"
%token_prefix TK_

// The type of the data attached to each token is Token.  This is also the
// default type for non-terminals.

ccons ::= PRIMARY KEY sortorder(Z) onconf(R) autoinc(I).
                                     {sqlite3AddPrimaryKey(pParse,0,R,I,Z);}
ccons ::= UNIQUE onconf(R).    {sqlite3CreateIndex(pParse,0,0,0,0,R,0,0,0,0);}
ccons ::= CHECK LP expr(X) RP.       {sqlite3AddCheckConstraint(pParse,X);}
ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
                                {sqlite3CreateForeignKey(pParse,0,&T,TA,R);}
ccons ::= defer_subclause(D).   {sqlite3DeferForeignKey(pParse,D);}
ccons ::= COLLATE ids(C).  {sqlite3AddCollateType(pParse, &C);}

// The optional AUTOINCREMENT keyword
%type autoinc {int}
autoinc(X) ::= .          {X = 0;}
autoinc(X) ::= AUTOINCR.  {X = 1;}

// The next group of rules parses the arguments to a REFERENCES clause

term(A) ::= STRING(X).       {A = sqlite3PExpr(pParse, @X, 0, 0, &X);}
expr(A) ::= REGISTER(X).     {A = sqlite3RegisterExpr(pParse, &X);}
expr(A) ::= VARIABLE(X).     {
  Token *pToken = &X;
  Expr *pExpr = A = sqlite3PExpr(pParse, TK_VARIABLE, 0, 0, pToken);
  sqlite3ExprAssignVarNumber(pParse, pExpr);
}
expr(A) ::= expr(E) COLLATE ids(C). {
  A = sqlite3ExprSetColl(pParse, E, &C);
}
%ifndef SQLITE_OMIT_CAST
expr(A) ::= CAST(X) LP expr(E) AS typetoken(T) RP(Y). {
  A = sqlite3PExpr(pParse, TK_CAST, E, 0, &T);
  sqlite3ExprSpan(A,&X,&Y);
}

idxlist_opt(A) ::= .                         {A = 0;}
idxlist_opt(A) ::= LP idxlist(X) RP.         {A = X;}
idxlist(A) ::= idxlist(X) COMMA idxitem(Y) collate(C) sortorder(Z).  {
  Expr *p = 0;
  if( C.n>0 ){
    p = sqlite3PExpr(pParse, TK_COLUMN, 0, 0, 0);
    sqlite3ExprSetColl(pParse, p, &C);
  }
  A = sqlite3ExprListAppend(pParse,X, p, &Y);
  sqlite3ExprListCheckLength(pParse, A, SQLITE_MAX_COLUMN, "index");
  if( A ) A->a[A->nExpr-1].sortOrder = Z;
}
idxlist(A) ::= idxitem(Y) collate(C) sortorder(Z). {
  Expr *p = 0;
  if( C.n>0 ){
    p = sqlite3PExpr(pParse, TK_COLUMN, 0, 0, 0);
    sqlite3ExprSetColl(pParse, p, &C);
  }
  A = sqlite3ExprListAppend(pParse,0, p, &Y);
  sqlite3ExprListCheckLength(pParse, A, SQLITE_MAX_COLUMN, "index");
  if( A ) A->a[A->nExpr-1].sortOrder = Z;
}
idxitem(A) ::= nm(X).              {A = X;}

%type collate {Token}
collate(C) ::= .                {C.z = 0; C.n = 0;}
collate(C) ::= COLLATE ids(X).   {C = X;}


///////////////////////////// The DROP INDEX command /////////////////////////
//
cmd ::= DROP INDEX ifexists(E) fullname(X).   {sqlite3DropIndex(pParse, X, E);}

///////////////////////////// The VACUUM command /////////////////////////////

/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.618 2007/11/12 09:50:26 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks

void sqlite3StartTable(Parse*,Token*,Token*,int,int,int,int);
void sqlite3AddColumn(Parse*,Token*);
void sqlite3AddNotNull(Parse*, int);
void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int, int);
void sqlite3AddCheckConstraint(Parse*, Expr*);
void sqlite3AddColumnType(Parse*,Token*);
void sqlite3AddDefaultValue(Parse*,Expr*);
void sqlite3AddCollateType(Parse*, Token*);
void sqlite3EndTable(Parse*,Token*,Token*,Select*);

void sqlite3CreateView(Parse*,Token*,Token*,Token*,Select*,int,int);

#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE)
  int sqlite3ViewGetColumnNames(Parse*,Table*);
#else

#
# 2007 November 12
#
# 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 making sure that the names of collation
# sequences may be quoted using double quotes in SQL statements.
#
# $Id: collate9.test,v 1.1 2007/11/12 09:50:26 danielk1977 Exp $

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

proc reverse_sort {lhs rhs} {
  return [string compare $rhs $lhs]
}
db collate "reverse sort" reverse_sort

# 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 plan:
#
#     collate9-1.* - Test collation sequences attached to table columns
#     collate9-2.* - Test collation sequences attached to expressions
#     collate9-3.* - Test collation sequences attached to an index
#     collate9-4.* - Test collation sequences as an argument to REINDEX
#

do_test collate9-1.1 {
  execsql {
    CREATE TABLE xy(x COLLATE "reverse sort", y COLLATE binary);
    INSERT INTO xy VALUES('one', 'one');
    INSERT INTO xy VALUES('two', 'two');
    INSERT INTO xy VALUES('three', 'three');
  }
} {}
do_test collate9-1.2 {
  execsql { 
    SELECT x FROM xy ORDER BY x
  }
} {two three one}
do_test collate9-1.3 {
  execsql { 
    SELECT y FROM xy ORDER BY y
  }
} {one three two}
do_test collate9-1.4 {
  cksort { 
    SELECT x FROM xy ORDER BY x
  }
} {two three one sort}
do_test collate9-1.5 {
  execsql { 
    CREATE INDEX xy_i ON xy(x)
  }
} {}
do_test collate9-1.6 {
  cksort { 
    SELECT x FROM xy ORDER BY x
  }
} {two three one nosort}

do_test collate9-2.1 {
  execsql { 
    SELECT x, x < 'seven' FROM xy ORDER BY x
  }
} {two 1 three 1 one 0}
do_test collate9-2.2 {
  execsql { 
    SELECT y, y < 'seven' FROM xy ORDER BY x
  }
} {two 0 three 0 one 1}
do_test collate9-2.3 {
  execsql { 
    SELECT y, y COLLATE "reverse sort" < 'seven' FROM xy ORDER BY x
  }
} {two 1 three 1 one 0}
do_test collate9-2.4 {
  execsql {
    SELECT y FROM xy ORDER BY y
  }
} {one three two}
do_test collate9-2.5 {
  execsql {
    SELECT y FROM xy ORDER BY y COLLATE "reverse sort"
  }
} {two three one}

do_test collate9-3.1 {
  execsql {
    CREATE INDEX xy_i2 ON xy(y COLLATE "reverse sort");
  }
} {}
do_test collate9-3.2 {
  cksort { 
    SELECT y FROM xy ORDER BY y 
  }
} {one three two sort}
do_test collate9-3.3 {
  cksort { 
    SELECT y FROM xy ORDER BY y COLLATE "reverse sort"
  }
} {two three one nosort}

ifcapable reindex {
  do_test collate9-4.1 {
    execsql {
      REINDEX "reverse sort"
    }
  } {}

  # Modify the "reverse sort" collation so that it now sorts in the same
  # order as binary.
  proc reverse_sort {lhs rhs} {
    return [string compare $lhs $rhs]
  }

  # The integrity check should now fail because the indexes created using
  # "reverse sort" are no longer in sync with the collation sequence
  # implementation.
  do_test collate9-4.2 {
    expr {"ok" eq [execsql { PRAGMA integrity_check }]}
  } {0}

  do_test collate9-4.3 {
    execsql {
      REINDEX "reverse sort"
    }
  } {}

  # Integrity check should now pass.
  do_test collate9-4.4 {
    expr {"ok" eq [execsql { PRAGMA integrity_check }]}
  } {1}

  do_test collate9-4.5 {
    cksort {
      SELECT x FROM xy ORDER BY x COLLATE "reverse sort"
    }
  } {one three two nosort}
}

finish_test