NAVmoble - the pocket-sized ERP
Optimized for Microsoft Dynamics NAV and Windows Mobile powered devices

Tuesday, April 10, 2007

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!

3 comments:

Anonymous said...

How do you do the thing you mention in the update?

Do not forget to register the function on application startup:

SqLiteCyrHelper.RegisterFunction(typeof(SqLiteCyrHelper));

When I try to compile I get an error that RegisterFunction() is not a method for SqLiteCyrHelper

Ruslan Trifonov said...

This is valid for the Compact Framework Version only.
If you're referencing the full(desktop) version you don't have to register your sqllite functions - it is done automatically.

Jay Godse said...

SQLite is a great tool. Check out souptonuts.sourceforge.net for a tutorial on how to write user-defined functions.

For more information on SQLite (including books, videos, code...), check out my site/blog at www.squidoo.com/sqlitehammer.