SQLite

*/

#if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_ICU)

#include <unicode/utypes.h>
#include <unicode/uregex.h>
#include <unicode/ustring.h>
#include <unicode/ucol.h>

#include <assert.h>
#include "sqlite3.h"

#ifndef SQLITE_CORE
  #include "sqlite3ext.h"
  SQLITE_EXTENSION_INIT1

}

/*
** LIKE operator.
**
** http://unicode.org/reports/tr21/tr21-5.html#Caseless_Matching
*/

/*
** This function is called when an ICU function called from within
** the implementation of an SQL scalar function returns an error.
**
** The scalar function context passed as the first argument is 
** loaded with an error message based on the following two args.
*/
static void icuFunctionError(
  sqlite3_context *pCtx,       /* SQLite scalar function context */
  const char *zName,           /* Name of ICU function that failed */
  UErrorCode e                 /* Error code returned by ICU function */
){
  char zBuf[128];
  sqlite3_snprintf(128, zBuf, "ICU error: %s(): %s", zName, u_errorName(e));
  zBuf[127] = '\0';
  sqlite3_result_error(pCtx, zBuf, -1);
}

/*
** Function to delete compiled regexp objects. Registered as
** a destructor function with sqlite3_set_auxdata().
*/
static void icuRegexpDelete(void *p){
  URegularExpression *pExpr = (URegularExpression *)p;

    }
    pExpr = uregex_open(zPattern, -1, 0, 0, &status);

    if( U_SUCCESS(status) ){
      sqlite3_set_auxdata(p, 0, pExpr, icuRegexpDelete);
    }else{
      assert(!pExpr);
      icuFunctionError(p, "uregex_open", status);
      return;
    }
  }

  /* Configure the text that the regular expression operates on. */
  uregex_setText(pExpr, zString, -1, &status);
  if( !U_SUCCESS(status) ){
    icuFunctionError(p, "uregex_setText", status);
    return;
  }

  /* Attempt the match */
  res = uregex_matches(pExpr, 0, &status);
  if( !U_SUCCESS(status) ){
    icuFunctionError(p, "uregex_matches", status);
    return;
  }

  /* Set the text that the regular expression operates on to a NULL
  ** pointer. This is not really necessary, but it is tidier than 
  ** leaving the regular expression object configured with an invalid
  ** pointer after this function returns.

  if( sqlite3_user_data(p) ){
    u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale, &status);
  }else{
    u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale, &status);
  }

  if( !U_SUCCESS(status) ){
    icuFunctionError(p, "u_strToLower()/u_strToUpper", status);
    return;
  }

  sqlite3_result_text16(p, zOutput, -1, xFree);
}

/*
** Collation sequence destructor function. The pCtx argument points to
** a UCollator structure previously allocated using ucol_open().
*/
static void icuCollationDel(void *pCtx){
  UCollator *p = (UCollator *)pCtx;
  ucol_close(p);
}

/*
** Collation sequence comparison function. The pCtx argument points to
** a UCollator structure previously allocated using ucol_open().
*/
static int icuCollationColl(
  void *pCtx,
  int nLeft,
  const void *zLeft,
  int nRight,
  const void *zRight
){
  UCollationResult res;
  UCollator *p = (UCollator *)pCtx;
  res = ucol_strcoll(p, (UChar *)zLeft, nLeft/2, (UChar *)zRight, nRight/2);
  switch( res ){
    case UCOL_LESS:    return -1;
    case UCOL_GREATER: return +1;
    case UCOL_EQUAL:   return 0;
  }
  assert(!"Bad return value from ucol_strcoll()");
  return 0;
}

/*
** Implementation of the scalar function icu_load_collation().
**
** This scalar function is used to add ICU collation based collation 
** types to an SQLite database connection. It is intended to be called
** as follows:
**
**     SELECT icu_load_collation(<locale>, <collation-name>);
**
** Where <locale> is a string containing an ICU locale identifier (i.e.
** "en_AU", "tr_TR" etc.) and <collation-name> is the name of the
** collation sequence to create.
*/
static void icuLoadCollation(
  sqlite3_context *p, 
  int nArg, 
  sqlite3_value **apArg
){
  sqlite3 *db = (sqlite3 *)sqlite3_user_data(p);
  UErrorCode status = U_ZERO_ERROR;
  const char *zLocale;      /* Locale identifier - (eg. "jp_JP") */
  const char *zName;        /* SQL Collation sequence name (eg. "japanese") */
  UCollator *pUCollator;    /* ICU library collation object */
  int rc;                   /* Return code from sqlite3_create_collation_x() */

  assert(nArg==2);
  zLocale = (const char *)sqlite3_value_text(apArg[0]);
  zName = (const char *)sqlite3_value_text(apArg[1]);

  if( !zLocale || !zName ){
    return;
  }

  pUCollator = ucol_open(zLocale, &status);
  if( !U_SUCCESS(status) ){
    icuFunctionError(p, "ucol_open", status);
    return;
  }
  assert(p);

  rc = sqlite3_create_collation_x(db, zName, SQLITE_UTF16, (void *)pUCollator, 
      icuCollationColl, icuCollationDel
  );
  if( rc!=SQLITE_OK ){
    ucol_close(pUCollator);
    sqlite3_result_error(p, "Error registering collation function", -1);
  }
}

/*
** Register the ICU extension functions with database db.
*/
int sqlite3IcuInit(sqlite3 *db){
  struct IcuScalar {
    const char *zName;                        /* Function name */

    {"upper",  1, SQLITE_UTF16, (void*)1, icuCaseFunc16},
    {"upper",  2, SQLITE_UTF16, (void*)1, icuCaseFunc16},

    {"lower",  1, SQLITE_UTF8,         0, icuCaseFunc16},
    {"lower",  2, SQLITE_UTF8,         0, icuCaseFunc16},
    {"upper",  1, SQLITE_UTF8,  (void*)1, icuCaseFunc16},
    {"upper",  2, SQLITE_UTF8,  (void*)1, icuCaseFunc16},

    {"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
  };

  int rc = SQLITE_OK;
  int i;

  for(i=0; rc==SQLITE_OK && i<(sizeof(scalars)/sizeof(struct IcuScalar)); i++){
    struct IcuScalar *p = &scalars[i];

# 2007 May 1
#
# 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: icu.test,v 1.1 2007/05/07 11:53:14 danielk1977 Exp $
#

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

ifcapable !icu {
  finish_test
  return
}

# Create a table to work with.
#
execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}
execsql {INSERT INTO test1 VALUES(1,2,1.1,2.2,'hello','world')}
proc test_expr {name settings expr result} {
  do_test $name [format {
    db one {
      BEGIN; 
      UPDATE test1 SET %s; 
      SELECT %s FROM test1; 
      ROLLBACK;
    }
  } $settings $expr] $result
}

# Tests of the REGEXP operator.
#
test_expr icu-1.1 {i1='hello'} {i1 REGEXP 'hello'}  1
test_expr icu-1.2 {i1='hello'} {i1 REGEXP '.ello'}  1
test_expr icu-1.3 {i1='hello'} {i1 REGEXP '.ell'}   0
test_expr icu-1.4 {i1='hello'} {i1 REGEXP '.ell.*'} 1
test_expr icu-1.5 {i1=NULL}    {i1 REGEXP '.ell.*'} {}

# Some non-ascii characters with defined case mappings
#
set ::EGRAVE "\xC8"
set ::egrave "\xE8"

set ::OGRAVE "\xD2"
set ::ograve "\xF2"

# That German letter that looks a bit like a B. The
# upper-case version of which is "SS" (two characters).
#
set ::szlig "\xDF" 

# Tests of the upper()/lower() functions.
#
test_expr icu-2.1 {i1='HellO WorlD'} {upper(i1)} {HELLO WORLD}
test_expr icu-2.2 {i1='HellO WorlD'} {lower(i1)} {hello world}
test_expr icu-2.3 {i1=$::egrave} {lower(i1)}     $::egrave
test_expr icu-2.4 {i1=$::egrave} {upper(i1)}     $::EGRAVE
test_expr icu-2.5 {i1=$::ograve} {lower(i1)}     $::ograve
test_expr icu-2.6 {i1=$::ograve} {upper(i1)}     $::OGRAVE
test_expr icu-2.3 {i1=$::EGRAVE} {lower(i1)}     $::egrave
test_expr icu-2.4 {i1=$::EGRAVE} {upper(i1)}     $::EGRAVE
test_expr icu-2.5 {i1=$::OGRAVE} {lower(i1)}     $::ograve
test_expr icu-2.6 {i1=$::OGRAVE} {upper(i1)}     $::OGRAVE

test_expr icu-2.7 {i1=$::szlig} {upper(i1)}      "SS"
test_expr icu-2.8 {i1='SS'} {lower(i1)}          "ss"

# In turkish (locale="tr_TR"), the lower case version of I
# is "small dotless i" (code point 0x131 (decimal 305)).
#
set ::small_dotless_i "\u0131"
test_expr icu-3.1 {i1='I'} {lower(i1)}           "i"
test_expr icu-3.2 {i1='I'} {lower(i1, 'tr_tr')}  $::small_dotless_i
test_expr icu-3.3 {i1='I'} {lower(i1, 'en_AU')}  "i"

#--------------------------------------------------------------------
# Test the collation sequence function.
#
do_test icu-4.1 {
  execsql {
    CREATE TABLE fruit(name);
    INSERT INTO fruit VALUES('plum');
    INSERT INTO fruit VALUES('cherry');
    INSERT INTO fruit VALUES('apricot');
    INSERT INTO fruit VALUES('peach');
    INSERT INTO fruit VALUES('chokecherry');
    INSERT INTO fruit VALUES('yamot');
  }
} {}
do_test icu-4.2 {
  execsql {
    SELECT icu_load_collation('en_US', 'AmericanEnglish');
    SELECT icu_load_collation('lt_LT', 'Lithuanian');
  }
  execsql {
    SELECT name FROM fruit ORDER BY name COLLATE AmericanEnglish ASC;
  }
} {apricot cherry chokecherry peach plum yamot}


# Test collation using Lithuanian rules. In the Lithuanian
# alphabet, "y" comes right after "i".
#
do_test icu-4.3 {
  execsql {
    SELECT name FROM fruit ORDER BY name COLLATE Lithuanian ASC;
  }
} {apricot cherry chokecherry yamot peach plum}

finish_test