SqLite: Improve database experience with managed user-defined functions

Are you using SQLite?

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine.

A good .NET and Compact Framework Providers may be obtained from here

It is very fast! However the speed comes to a price - no foreign keys, no stored procedures, etc.

One of the major challenges with SqLite for me was related to a problem with the ciryllic supopport. In general, if one tries to execute an sql query containing the expression "upper(MyField) = @MyField " may get unexpected result. The SqLite UPPER function has a problem, when dealing with Cyrillic characters.
So is there a solution?
I came with 2 solutions(Both have issues):
1. Store only upper characters in the database and do not use the UPPER function
2. Implement custom managed "Upper" function for SQL:

[SQLiteFunction(Name="CYR_UPPER",Arguments=1,FuncTyp=FunctionType.Scalar)]
public class SqLiteCyrHelper:SQLiteFunction
{
  public override object Invoke(object[] args)
  { 
     return args[0]!=null?((string)args[0]).ToUpper():null;
  }
}

Then you may use it in the following way:

SELECT * FROM my_cyr_table WHERE cyr_upper(cyr_column) = @cyr_string

This all comes with a performance cost, however it is a very powerful way to enhance the database experience.

SQLite supports custom aggregates , collate and scalar function and the sqlite.phxsoftware.com implementation allows these function to be written in managed code.

UPDATE:

Do not forget to register the function on application startup:

SqLiteCyrHelper.RegisterFunction(typeof(SqLiteCyrHelper));

Links:

Managed SQLite Provider (.NET & compact Framework)

SQLite.org - the offical SQLite web site



Enjoy!