SQLite

/*
** 2013-02-28
**
** 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 contains code to implement the next_char(A,T,F,W,C) SQL function.
**
** The next_char(A,T,F,W,C) function finds all valid "next" characters for
** string A given the vocabulary in T.F.  If the W value exists and is a
** non-empty string, then it is an SQL expression that limits the entries
** in T.F that will be considered.  If C exists and is a non-empty string,
** then it is the name of the collating sequence to use for comparison.  If
** 
** Only the first three arguments are required.  If the C parameter is 
** omitted or is NULL or is an empty string, then the default collating 
** sequence of T.F is used for comparision.  If the W parameter is omitted
** or is NULL or is an empty string, then no filtering of the output is
** done.
**
** The T.F column should be indexed using collation C or else this routine
** will be quite slow.
**
** For example, suppose an application has a dictionary like this:
**
**   CREATE TABLE dictionary(word TEXT UNIQUE);
**
** Further suppose that for user keypad entry, it is desired to disable
** (gray out) keys that are not valid as the next character.  If the

  int argc,
  sqlite3_value **argv
){
  nextCharContext c;
  const unsigned char *zTable = sqlite3_value_text(argv[1]);
  const unsigned char *zField = sqlite3_value_text(argv[2]);
  const unsigned char *zWhere;
  const unsigned char *zCollName;
  char *zWhereClause = 0;
  char *zColl = 0;
  char *zSql;
  int rc;

  memset(&c, 0, sizeof(c));
  c.db = sqlite3_context_db_handle(context);
  c.zPrefix = sqlite3_value_text(argv[0]);
  c.nPrefix = sqlite3_value_bytes(argv[0]);
  if( zTable==0 || zField==0 || c.zPrefix==0 ) return;
  if( argc>=4
   && (zWhere = sqlite3_value_text(argv[3]))!=0
   && zWhere[0]!=0
  ){
    zWhereClause = sqlite3_mprintf("AND (%s)", zWhere);
    if( zWhereClause==0 ){
      sqlite3_result_error_nomem(context);
      return;
    }
  }else{
    zWhereClause = "";
  }
  if( argc>=5
   && (zCollName = sqlite3_value_text(argv[4]))!=0
   && zCollName[0]!=0 
  ){
    zColl = sqlite3_mprintf("collate \"%w\"", zCollName);


    if( zColl==0 ){
      sqlite3_result_error_nomem(context);
      if( zWhereClause[0] ) sqlite3_free(zWhereClause);
      return;
    }
  }else{
    zColl = "";
  }
  zSql = sqlite3_mprintf(
    "SELECT \"%w\" FROM \"%w\""
    " WHERE \"%w\">=(?1 || ?2) %s"
    "   AND \"%w\"<=(?1 || char(1114111)) %s" /* 1114111 == 0x10ffff */
    "   %s"
    " ORDER BY 1 %s ASC LIMIT 1",
    zField, zTable, zField, zColl, zField, zColl, zWhereClause, zColl

  );
  if( zWhereClause[0] ) sqlite3_free(zWhereClause);
  if( zColl[0] ) sqlite3_free(zColl);
  if( zSql==0 ){
    sqlite3_result_error_nomem(context);
    return;
  }

  rc = sqlite3_prepare_v2(c.db, zSql, -1, &c.pStmt, 0);
  sqlite3_free(zSql);

  (void)pzErrMsg;  /* Unused parameter */
  rc = sqlite3_create_function(db, "next_char", 3, SQLITE_UTF8, 0,
                               nextCharFunc, 0, 0);
  if( rc==SQLITE_OK ){
    rc = sqlite3_create_function(db, "next_char", 4, SQLITE_UTF8, 0,
                                 nextCharFunc, 0, 0);
  }
  if( rc==SQLITE_OK ){
    rc = sqlite3_create_function(db, "next_char", 5, SQLITE_UTF8, 0,
                                 nextCharFunc, 0, 0);
  }
  return rc;
}

} {ae}
do_execsql_test 1.13 {
  SELECT next_char('','vocab','w');
} {r}
do_test 1.14 {
  catchsql {SELECT next_char('','xyzzy','a')}
} {1 {no such table: xyzzy}}

do_execsql_test 1.20 {
  CREATE TABLE vocab2(w TEXT);
  CREATE INDEX vocab2w ON vocab2(w COLLATE nocase);
  INSERT INTO vocab2 VALUES('abc'), ('ABD'), ('aBe'), ('AbF');
  SELECT next_char('ab', 'vocab2', 'w', null, 'nocase');
} {cDeF}
do_execsql_test 1.21 {
  SELECT next_char('ab','vocab2','w',null,null);
} {c}
do_execsql_test 1.22 {
  SELECT next_char('AB','vocab2','w',null,'NOCASE');
} {cDeF}
do_execsql_test 1.23 {
  SELECT next_char('ab','vocab2','w',null,'binary');
} {c}

do_execsql_test 2.1 {
  CREATE VIRTUAL TABLE t2 USING spellfix1;
  INSERT INTO t2 (word, soundslike) VALUES('school', 'skuul');
  INSERT INTO t2 (word, soundslike) VALUES('psalm', 'sarm');
  SELECT word, matchlen FROM t2 WHERE word MATCH 'sar*' LIMIT 5;
} {psalm 4}