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|ξ
-- ROOT collation declared for table column as default collation
sqlite> create table test2 ( name text COLLATE '' );
sqlite> insert into test2 values ( "ć" );
sqlite> insert into test2 values ( "ą" );
sqlite> insert into test2 values ( "Ć" );
sqlite> insert into test2 values ( "a" );
sqlite> insert into test2 values ( "A" );
sqlite> insert into test2 values ( "C" );
-- using the default collation
sqlite> select * from test2 order by name;
a
A
ą
C
ć
Ć
-- forcing binary collation
sqlite> select * from test2 order by name COLLATE BINARY;
A
a
C
ą
Ć
ć
-- "A" > "a" for ROOT collation 
select * from test2 where name > "A"
A
C
ą
Ć
ć

Compatibility with 3rd-party software

SQLite has three simple collation sequence types built-in: NOCASE, RTRIM. Other collations have to be loaded.

The ICU-based collation sequences is provided via the SQLite extension API, so it works both in Kexi SQLite3 driver (which loads the needed collation automatically) and the sqlite3 shell (where .load command can be used). However any 3rd-party software that intends to use tables or queries with these collations have to load the plugin. This can be done either using load_extension() or SQL command or using [http://www.sqlite.org/c3ref/load_extension.html sqlite3_load_extension() C API.

sqlite3_enable_load_extension(1) have to be called before to enable extension loading. Certain tools have no extensions enabled in their usage of SQLite3 and altering of their code may be needed.

When user attempts to use tables or queries with custom collations the following error message appears (note that the message can be hidden by the 3rd-party program):

-- executing query on table with column that use custom collation, from the previous example: create table test2 ( name text COLLATE '' )
select * from test2;
-- result:
> Error: no such collation sequence: 
-- workaround is to force BINARY collation:
select * from test2 order by name COLLATE BINARY;
> OK

-- executing query that uses custom collation on table with standard collation:
select * from test order by name COLLATE '';
> Error: no such collation sequence: 
-- workaround is to remove COLLATE part so the collation defaults to BINARY:
select * from test order by name;
> OK

Ensuring Kexi is built with capable version of libsqlite3

For improved modularity and to enable proper use of sqlite3 shell, custom collations are used in Kexi via extensions API of libsqlite3. The potential problem is that the extensions API are not enabled by default for libsqlite3 builds. Read libsqlite3 status in distros section to check status for particular OS distribution.

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