Version: 3.0.x (Calligra 3.0.x) (using KDE 4.6.0) OS: Linux Currently there is no way to control how the columns are sorted. Every column collation is defaulted to BINARY. This gives unexpected results when eg. sorting accented vowels. Reproducible: Didn't try Expected Results: User should be able to assign different Collating sequence for each column as it is supported by SQLite, to control the way columns are sorted. Maybe later on support Creating Collation Sequences as per: http://www.sqlite.org/c3ref/create_collation.html
Last I checked, text columns sorting (most interesting here) were defaulted to unicode collation. From http://www.sqlite.org/lang_select.html#orderby this applies: "3. Otherwise, if the ORDER BY expression is a column or an alias of an expression that is a column, then the default collation sequence for the column is used." Anyway, I am accepting the wish for increased flexibility.
I agree but, there is now way to define default collation sequences for columns, so each column collation defaults to BINARY. http://www.sqlite.org/datatype3.html Paragraph 6.0 and 6.1 "Every column of every table has an associated collating function. If no collating function is explicitly defined, then the collating function defaults to BINARY" Am I missing something here?
Thanks. I need to test this than and if possible plug the well defined Unicode collation function. Changed Version to 2.4.x.
This task is one of more laborous out there since research was needed and packaging aspects also appear here. For example, here's the full followup thread on sqlite packaging: http://lists.kde.org/?t=132388156300005&r=1&w=1 I am planning to use ICU (http://icu-project.org) for high quality collating using sqlite extension (it's even already shipped with sqlite code). Other areas would benefit, e.g. case insensitive, localized LIKE operator. Very good features for desktop databases that usually expose the data to user. I think many advanced server databases do not have or enable this kind of features.
Splitted out a 'wish' part of this big to https://bugs.kde.org/show_bug.cgi?id=289293 and changed topic to 'Add collation sequence for unicode characters and make it the default'
Git commit 8f6f75d5b9ebf048f196ea5f7320b99c56b2d9cd by Jaroslaw Staniek. Committed on 20/12/2011 at 00:06. Pushed by staniek into branch 'master'. KexiDB: Use ICU for high quality collating in columns using sqlite ext. BUG:278411 DIGEST: (KexiDB) Use ICU (http://icu-project.org) for high quality collating in unicode text columns using sqlite extension. M +19 -5 CMakeLists.txt M +66 -12 cmake/modules/FindCalligraSqlite.cmake A +82 -0 cmake/modules/FindICU.cmake M +12 -2 kexi/kexidb/driver.h M +2 -1 kexi/kexidb/drivers/sqlite/CMakeLists.txt A +10 -0 kexi/kexidb/drivers/sqlite/icu/CMakeLists.txt A +170 -0 kexi/kexidb/drivers/sqlite/icu/README.txt A +501 -0 kexi/kexidb/drivers/sqlite/icu/icu.c [License: Public Domain] A +27 -0 kexi/kexidb/drivers/sqlite/icu/sqliteicu.h [License: Public Domain] M +56 -6 kexi/kexidb/drivers/sqlite/sqliteconnection.cpp M +4 -0 kexi/kexidb/drivers/sqlite/sqliteconnection.h M +7 -0 kexi/kexidb/drivers/sqlite/sqliteconnection_p.h M +9 -2 kexi/kexidb/drivers/sqlite/sqlitedriver.cpp M +10 -1 kexi/kexidb/drivers/sqlite/sqlitedriver.h M +9 -3 kexi/kexidb/queryschema.cpp M +2 -2 kexi/kexidb/queryschema.h http://commits.kde.org/calligra/8f6f75d5b9ebf048f196ea5f7320b99c56b2d9cd
Git commit a3331e48e5ffdf7a1c93adcc80349d47fc67e2c9 by Jaroslaw Staniek. Committed on 28/05/2012 at 00:02. Pushed by staniek into branch 'master'. Add KexiDB fix: Use ICU for better collating in text columns *Add KexiDB fix: Use ICU (http://icu-project.org) for high quality collating in unicode text columns using sqlite extension **(2011-12-20 calligra master commit 8f6f75d5b9ebf048) M +22 -8 Drivers/CMakeLists.txt M +2 -0 Drivers/sqlite/CMakeLists.txt M +71 -10 Drivers/sqlite/SqliteConnection.cpp M +7 -1 Drivers/sqlite/SqliteConnection.h M +11 -5 Drivers/sqlite/SqliteConnection_p.h M +12 -4 Drivers/sqlite/SqliteDriver.cpp M +10 -1 Drivers/sqlite/SqliteDriver.h A +11 -0 Drivers/sqlite/icu/CMakeLists.txt A +170 -0 Drivers/sqlite/icu/README.txt A +503 -0 Drivers/sqlite/icu/icu.c [License: Public Domain] A +31 -0 Drivers/sqlite/icu/sqliteicu.h [License: Public Domain] M +11 -1 Predicate/Driver.h M +5 -9 Predicate/DriverManager.cpp M +11 -4 Predicate/QuerySchema.cpp M +1 -1 Predicate/QuerySchema.h M +12 -0 Predicate/Utils.cpp M +6 -0 Predicate/Utils.h A +82 -0 cmake/modules/FindICU.cmake M +67 -13 cmake/modules/FindSqlite.cmake http://commits.kde.org/predicate/a3331e48e5ffdf7a1c93adcc80349d47fc67e2c9