KDb/Drivers/SQLite: Difference between revisions
| Line 69: | Line 69: | ||
3|β | 3|β | ||
8|ξ | 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 | |||
ą | |||
Ć | |||
ć | |||
</pre> | </pre> | ||
Revision as of 11:39, 17 December 2011
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
ą
Ć
ć
Links