KDb/Drivers/SQLite

Requirements for libsqlite3 builds

Configure as follows:

mkdir builddir
cd builddir
REQUIRED_CPPFLAGS="-DSQLITE_ENABLE_COLUMN_METADATA=1"
REQUIRED_OPTIONS="--enable-load-extension"
RECOMMENDED_CPPFLAGS="-DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \
-DSQLITE_SECURE_DELETE=1 -DSQLITE_ENABLE_MEMORY_MANAGEMENT=1 \
-DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_STAT2=1 \
-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1 -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \
-DSQLITE_SOUNDEX=1"
RECOMMENDED_OPTIONS="--enable-threadsafe"
CPPFLAGS="$REQUIRED_CPPFLAGS $RECOMMENDED_CPPFLAGS" \
../sqlite-src-???/configure --prefix=.... $REQUIRED_OPTIONS $RECOMMENDED_OPTIONS

--enable-load-extension is a must to have support for setting up unicode collations.

Optional: append --enable-debug for debug version.

See also explanation in Deploying SQLite blog entry.

libsqlite3 status in distros

Debian: OK

TODO...

Collations

By default Kexi forces ROOT collation, i.e. combined collations for all locales. Example test session in sqlite3 shell:

.load path/to/libkexidb_sqlite3_icu.so
-- original order: 3 locales mixed (polish, german, greek)
1|aaaa
2|ąąąą
3|β
4|żżżż
5|bbbb
6|ü
7|źźźź
8|ξ
9|α
10|u
-- binary collation, sqlite default
sqlite> select * from collation order by name; 
1|aaaa
5|bbbb
10|u
6|ü
2|ąąąą
7|źźźź
4|żżżż
9|α
3|β
8|ξ
-- ROOT collation
sqlite> SELECT icu_load_collation('', '');
sqlite> select * from test order by name COLLATE '';
1|aaaa
2|ąąąą
5|bbbb
10|u
6|ü
7|źźźź
4|żżżż
9|α
3|β
8|ξ

Links

• http://stackoverflow.com/questions/611459/how-to-sort-text-in-sqlite3-with-specified-locale
• http://www.sqlite.org/c3ref/load_extension.html
• http://www.sqlite.org/lang_corefunc.html#load_extension
• http://wiki.apache.org/solr/UnicodeCollation#Sorting_text_for_multiple_languages