Page 1 of 2
UTF sorting in sqlite databases
Posted: Mon 14 Jun 2010 22:52
by sandy771
Is this supported in the unidac product?
Edit to add that I have stCaseInsensitive set in TuniConnection but my tables are still sorted as case sensitive.
The sqlite faq (
http://www.sqlite.org/faq.html#q18) does say that in order to provide unicode sorting external routines would need to be added. As I am working through the devart routines I assume I need to rely on Devart to implement this for me?
If there is a way of achieving case senstive sorting (pretty essential for my app) then could you please supply an example.
Posted: Tue 15 Jun 2010 15:20
by bork
Hello
When you write query like the following:
Code: Select all
select *
from my_table
order by name
Then sorting is executed on the SQLite server (usually it is sqlite3.dll) and UniDAC gets already sorted data. As I know SQLite has some problems with operators LIKE and ORDER BY when text contains chars in the UTF encoding. But unfortunately we cannot change behavior of SQLite. If you don't like behavior of ORDER BY operator then you should ask SQLite developers to fix it or you can try to find some SQLite extension that fixes this problem.
Posted: Tue 15 Jun 2010 15:26
by sandy771
Hi Bork
Thanks for the answer.
If you read the faq linked to in my previous email it seems that YOU can implement sorting by overriding the sqlite inbuilt collation routines - as far as I can see I cant do this.
The sqlite devlepers have added a route for you to do this as part of the unidac controls, but becuase I am accessing via your controls this route seems to be denied to me.
Could I please ask that you look at this and see about implementing a fix.
The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. The reason for this is that doing full Unicode case-insensitive comparisons and case conversions requires tables and logic that would nearly double the size of the SQLite library. The SQLite developers reason that any application that needs full Unicode case support probably already has the necessary tables and functions and so SQLite should not take up space to duplicate this ability.
Instead of providing full Unicode case support by default, SQLite provides the ability to link against external Unicode comparison and conversion routines. The application can overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in like(), upper(), and lower() functions (using sqlite3_create_function()). The SQLite source code includes an "ICU" extension that does these overloads. Or, developers can write their own overloads based on their own Unicode-aware comparison routines already contained within their project.
Posted: Wed 16 Jun 2010 09:54
by sandy771
It seems to me that this is an issue that you can easily resolve and one that the developers of sqlite have left for you to resolve.
It does not seem to be appropriate for ME to have to ask the sqlite developers to fix something when they have clearly left it for you as the developer of the DAC.
Could you please confirm that you will be implementing a UTF sort option - if you won't then I need to be looking for a solution to this problem and I dont really want to have to spend my money on a different DAC package.
Please support your customers.
Posted: Wed 16 Jun 2010 13:06
by bork
We have read this FAQ before and we have investigated the possibility to add this functionality to UniDAC. If you want to have this functionality inside UniDAC then we will try to add it in the next UniDAC build.
Posted: Wed 16 Jun 2010 13:38
by sandy771
bork wrote:We have read this FAQ before and we have investigated the possibility to add this functionality to UniDAC. If you want to have this functionality inside UniDAC then we will try to add it in the next UniDAC build.
Bork - Thank You very much - I look forward to it.
Posted: Tue 13 Jul 2010 12:10
by sandy771
Hi Bork
Not chasing but just wondered if you had an approximate timescale for the release of the unidac build including this feature
Posted: Tue 13 Jul 2010 12:47
by bork
Hello
This feature was added and will be included in the next UniDAC build.
To register you own collation function you should add the LiteClassesUni unit to the USES section and call the RegisterCollation function.
Posted: Tue 13 Jul 2010 14:55
by sandy771
Sorry Bork
Are you saying that when the new version is released I will need to add my own collate function or that I can add the collate function to the current version?
when will the new version be out?
thank You
Posted: Wed 14 Jul 2010 15:01
by bork
Hello
The next version of UniDAC will be released in 1-2 weeks.
Posted: Thu 15 Jul 2010 13:33
by sandy771
Excellent - thanks
Posted: Wed 18 Aug 2010 09:08
by sandy771
New build of UniDAC version 3.00.0.11 is available for download now.
This version includes:
* Added UTF sorting for the SQLite database
Thanks for this guys - now how do I implement it. Selecting foCaseInsenstive = true in the filter options makes no difference when sorting a column all lower case follow all the upper case
Posted: Thu 19 Aug 2010 12:02
by bork
Hello
If you want to use UTF sorting for SQLite, you can use the following sample:
Declare callback function for the collation:
Code: Select all
function collation_callback(UserData: Pointer;
l1: Integer; const s1: Pointer;
l2: Integer; const s2: Pointer): Integer; cdecl;
function GetString(Buffer: Pointer; Len: integer): string;
begin
SetLength(Result, Len);
Move(Buffer^, Result[1], Len);
Result := UTF8Decode(Result)
end;
var
wstr1, wstr2: WideString;
begin
wstr1 := GetString(s1, l1);
wstr2 := GetString(s2, l2);
wstr1 := WideUpperCase(wstr1);
wstr2 := WideUpperCase(wstr2);
if wstr1 > wstr2 then
Result := 1
else if wstr1 < wstr2 then
Result := -1
else
Result := 0;
end;
Register callback function for the collation :
Code: Select all
RegisterCollation(TUniUtils.GetCRConnection(UniConnection1), 'SYSTEMNOCASE', SQLITE_UTF8, nil , collation_callback);
Use query with this collation:
Code: Select all
select *
from BTEST
order by name COLLATE SYSTEMNOCASE
To use this code you should add LiteClassesUni and LiteCallUni units to the USES section.
Posted: Mon 18 Oct 2010 18:52
by sandy771
I have been unavoidably away for a while and have not, until now, had time to try and implement this.
I have the code below. When I execute RegisterCollation I get an access violation read of address 00000000. Any ideas what I might be doing wrong?
RegisterCollation(TUniUtils::GetCRConnection(UniConnection1), "SYSTEMNOCASE", SQLITE_UTF8, NULL, collation_callback);
and
int __cdecl collation_callback(void* userdata, int l1, const void *s1, int l2, const void *s2)
{
WideString w1, w2;
w1 = WideUpperCase(UTF8Decode((char*)s1));
w2 = WideUpperCase(UTF8Decode((char*)s2));
if(w1 > w2)
return 1;
else
if( w1 < w2)
return -1;
else
return 0;
}
Posted: Wed 20 Oct 2010 08:14
by AlexP
Hello,
I tried execute your example, and it works correctly.
I tested it on C++Builder 2010 with UniDAC 3.50.0.12.
Please specify your version of IDE and UniDAC, or send me the example project to demonstrate the problem to alexp*devart*com.