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
hereIt 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!