Re: [sqlite] UPPER function depends on Locale?

[sandu]

Simon Slavin-3 wrote
> https://www.sqlite.org/src/artifact?ci=trunkfilename=ext/icu/README.txt;

However the example described here is a little bit simplistic:

lower('I', 'en_us') -> 'i'
lower('I', 'tr_tr') -> 'ı' (small dotless i)

You depend on the encoding of the text editor, which has to match encoding
of the SQLite database. A more realistic example would be:

lower((BLOB CAST AS TEXT), 'tr_tr')
where the BLOB hast to be the string in one of the supported encodings:
UTF8, UTF16, UTF16le, UTF16be.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Simon Slavin]

On 10 Apr 2018, at 7:44am, Dr. Mucibirahman İLBUĞA  
wrote:

> I my opinion, there should be well documented, step by step information about 
> how to add ICU support in SQLite.

I think that the reason this doesn't exist is that different people want to use 
different compilers.  But it would be nice to see instructions on how to do it 
with an example compiler.  If you choose to do this I will read your post with 
interest.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Dr . Mucibirahman İLBUĞA]

10.04.2018 10:30 tarihinde sandu yazdı:

As Simon Slavin wrote, maybe in your case would be much easier to have
dedicated lower/upper functions instead of recompiling with ICU option, if
this is two complex for you.


Hi,

Yes. You and Simon are right. But in this case there is not any step by 
step document how to create function. If I can success to create one 
simple function in SQLite then I will prepare step by step how-to 
document. I swear... :)


--
Regards,
Mucip:)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[sandu]

Dr. Mucibirahman İLBUĞA wrote
> And I really shocked that am I first person on over the world who wants to
> use SQLite in their own language?... :)

You are not the first person that uses SQLite with a different character set
than ASCII and with a different locale other than English US. If you take a
look at the sponsors list which include NDS Consortium, Bloomberg, mozilla,
etc, all are delivering localized applications, so they dealt with this
problem. If you drive a BMW or a Daimler the navigation system will show you
localized names in Turkish, Greek, Hebrew, Chinese, etc and these are
working fine. 

You should have in mind the original purpose of the library: to provide a
light SQL engine for embedded systems. Probably you washing machine is
already logging data in an SQLite db, everything on a low cost CPU which
cannot cost more than 5 euros.

As Simon Slavin wrote, maybe in your case would be much easier to have
dedicated lower/upper functions instead of recompiling with ICU option, if
this is two complex for you.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Dr . Mucibirahman İLBUĞA]

09.04.2018 23:18 tarihinde sandu yazdı:

The example you found is not applicable in your case, because it is
describing an way to develop your own functions in PHP, which is not the
case for you.


Dear Buraga SANDU,

Thanks alot for your kind interest.

I my opinion, there should be well documented, step by step information 
about how to add ICU support in SQLite. And I really shocked that am I 
first person on over the world who wants to use SQLite in their own 
language?... :)


It must be download option prebuild "SQLite with ICU support" versions 
for both Linux and windows in download page. Yes, this may be difficult 
for me to add this support but I'm sure there must be much better person 
to do this.


It's really very big question for me! SQLite is really very good 
database and has lots of avantages. But nobody use it with their 
language! This is really very strange for me?!... :(



--
Kolay gelsin,
Mucip:)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[sandu]

The example you found is not applicable in your case, because it is
describing an way to develop your own functions in PHP, which is not the
case for you.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[sandu]

Simon Slavin-3 wrote
> See section 1.1 of
> 
> https://www.sqlite.org/src/artifact?ci=trunkfilename=ext/icu/README.txt;
> 
> Simon.

It looks good, basically 2 letter ISO 639 language code and ISO 3166 country
code. 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Simon Slavin]

On 9 Apr 2018, at 8:49pm, sandu  wrote:

> I am also curious about how can someone define the locale parameter for the
> LOWER/UPPER functions?

See section 1.1 of



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[sandu]

Simon Slavin-3 wrote
> Unfortunately, bare SQLite understands only ASCII characters.  It does not
> understand Unicode.  To build in full understanding of Unicode would make
> SQLite almost twice as long.
> 
> However, you can add Unicode support to SQLite using ICU:
> 
> https://www.sqlite.org/src/artifact?ci=trunkfilename=ext/icu/README.txt;
> 
> If you're compiling your own SQLite, use section 2 of the above document
> or the option
> 
> SQLITE_ENABLE_ICU
> 
> https://www.sqlite.org/compile.html;
> 
> Simon.

If he recompiles SQLite with Unicode support is this going to guarantee to
him that UPPER and LOWER functions behavior will change? The documentation
explicitly states that this is an ASCII based function:
https://www.sqlite.org/lang_corefunc.html#upper 

LOWER documentation is somehow different: "The lower(X) function returns a
copy of string X with all ASCII characters converted to lower case. The
default built-in lower() function works for ASCII characters only. To do
case conversions on non-ASCII characters, load the ICU extension." 

I am also curious about how can someone define the locale parameter for the
LOWER/UPPER functions?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[sandu]

According to SQLite documentation, it does not depend on locale, because it
is a pure ASCII implementation:

"The upper(X) function returns a copy of input string X in which all
lower-case ASCII characters are converted to their upper-case equivalent."
ASCII character was designed for English US, so the context of the
conversion is implicit.

If we speak about Unicode, the lower/upper casing operations depend on
locale. The prominent use-cases are:

- Turkish/Azeri dotted/dotless I
- Greek upper case sigma which depending on the context can be converted to
small sigma or to final small sigma
- German ß to SS conversion which is not bidirectional. Someone can
uppercase ß into SS, but the reverse operation is not allowed. 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Olivier Mascia]

> Le 9 avr. 2018 à 11:39, Dr. Mucibirahman İLBUĞA  a 
> écrit :
> 
> 09.04.2018 11:08 tarihinde Olivier Mascia yazdı:
>> Is it better / simpler to write your own UPPER/LOWER specialised or to 
>> simply rebuild SQLite with ICU, I have too few details on your needs / goals 
>> to tell.
> 
> Hi,
> 
> Is there any document explains step by step recompiling SQLite with ICU 
> support both Linux and Windows platforms?
> 
> -- 
> Kolay gelsin,
> Mucip:)

Essentially, get ICU, for instance from the following location (other versions 
available too, if more suitable). There are some binary versions available to 
get you quick started, along with their include files (which SQLite source code 
will require).

http://site.icu-project.org/download/61#TOC-ICU4C-Download

Regarding SQLite, what it involves is at the simplest to compile the 
'amalgamation' file (sqlite3.c) along with your application by defining 
SQLITE_ENABLE_ICU (along with some other defines that will be appropriate to 
your use).

This page has a lot of useful (and important) information:
https://www.sqlite.org/compile.html

You will then have to link your app with multiple import libraries from ICU 
project, to resolve the dependencies sqlite3.c will have introduced (using 
SQLITE_ENABLE_ICU). See ICU documentation or link them all for a first run.

I'm not really using ICU today, but merely investigated in the past what I 
would have to do/to learn to use it the day I'll need it. This comes from that 
limited (but successful) experience (only checked the procedure on Windows 
where it generally is a bit more awkward to get these things done right - but 
it ended up to be straightforward).

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Dr . Mucibirahman İLBUĞA]

09.04.2018 11:08 tarihinde Olivier Mascia yazdı:

Is it better / simpler to write your own UPPER/LOWER specialised or to simply 
rebuild SQLite with ICU, I have too few details on your needs / goals to tell.


Hi,

Is there any document explains step by step recompiling SQLite with ICU 
support both Linux and Windows platforms?


--
Kolay gelsin,
Mucip:)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Dr . Mucibirahman İLBUĞA]

09.04.2018 11:08 tarihinde Olivier Mascia yazdı:

Is it better / simpler to write your own UPPER/LOWER specialised or to simply 
rebuild SQLite with ICU, I have too few details on your needs / goals to tell.


Dear Olivier,

Thanks a lot for your valuable informations.

You may be right about recompile SQLite with ICU support much easier. I 
will search for information recompiling SQLite vor both Linux and 
Windows platforms


--
Regards,
Mucip:)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Olivier Mascia]

> Le 9 avr. 2018 à 08:42, Dr. Mucibirahman İLBUĞA  a 
> écrit :
> 
> 09.04.2018 00:56 tarihinde Simon Slavin yazdı:
>> There is another solution which does not involve full ICU support.  You 
>> could instead make your own external functions for UPPER_TR() and LOWER_TR() 
>> which do just the conversions for Turkish.  Presumably they could use tables 
>> to look up which characters to convert.
> 
> Dear Simon,
> 
> I think I need to create this function UPPER_TR() in SQLite?
> 
> I searched little bit in Google.
> 
> http://etutorials.org/Server+Administration/upgrading+php+5/Chapter+4.+SQLite/4.6+User-Defined+Functions/
> 
> Should I create in PHP or is there other way to do it? May I create it in 
> SQLiteStudio for instance?...

Why not read the excellent and rather complete documentation available on 
www.sqlite.org own website, first?

Start with: https://www.sqlite.org/c3ref/intro.html
Get deeper about installing (at runtime) your own custom SQL functions: 
https://www.sqlite.org/c3ref/create_function.html
And you will find implementation example of simple functions in various files 
of SQLite source code, and indeed possibly elsewhere.

To add a SQL function to SQLite, you will have to code it in C (or eventually 
C++ with a C public interface for your functions).  Though it is not impossible 
that through some bridge programming you could do it in other languages.  If I 
understood correctly from previous posts, you're developing in C++ so this 
should not be challenging.

Is it better / simpler to write your own UPPER/LOWER specialised or to simply 
rebuild SQLite with ICU, I have too few details on your needs / goals to tell.

Here is a sample function (what it does is irrelevant and specific to my 
needs), just publishing it for giving you a head start on writing small simple 
SQL functions which you would configure at application startup through 
sqlite3_create_function_v2().

void sql_now(sqlite3_context* context, int argc, sqlite3_value** argv)
{
int64_t offset = 0;
if (argc == 1)
offset = sqlite3_value_int64(argv[0]);

time_t local = time(nullptr) - utc_bias * 60 + offset;
int d = (int)(local / 86400) + 25568;
int64_t pack = dbi::datetime::pack(d, (int)(local % 86400), utc_bias);

sqlite3_result_int64(context, pack);
}

Don't be concerned or ask about dbi::datetime or utc_bias, these are completely 
specific to one of my programs.  What is interesting here for you is that this 
function, takes zero or one parameter and does something slightly different in 
both cases.  It then returns an integer.  Reading the documentation, you can 
easily extrapolate handling other parameters or return values.

In my case, sqlite3_create_function_v2 is then used to publish this function as 
SQL 'now'.
We have our own C++ framework around SQLite and use it to declare functions, 
but in the end this one would end up being added through a call like this (hope 
I did not get it wrong by synthesising it from head based on what the framework 
behind would have called):

sqlite3_create_function_v2(db, "now", -1, 0, nullptr, sql_now, nullptr, 
nullptr, nullptr);

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Dr . Mucibirahman İLBUĞA]

09.04.2018 00:56 tarihinde Simon Slavin yazdı:

There is another solution which does not involve full ICU support.  You could 
instead make your own external functions for UPPER_TR() and LOWER_TR() which do 
just the conversions for Turkish.  Presumably they could use tables to look up 
which characters to convert.


Dear Simon,

I think I need to create this function UPPER_TR() in SQLite?

I searched little bit in Google.

http://etutorials.org/Server+Administration/upgrading+php+5/Chapter+4.+SQLite/4.6+User-Defined+Functions/

Should I create in PHP or is there other way to do it? May I create it 
in SQLiteStudio for instance?...


--
Regards,
Mucip:)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[mucip . ilbuga]

Dear Simon,
OK. Thanks.
It seems that I need to make moore Google search about "Recompiling SQLite
with ICU"...

Regards,
Mucip:)

On Mon, Apr 9, 2018 at 12:56 AM, Simon Slavin  wrote:

> On 8 Apr 2018, at 10:51pm, mucip.ilb...@gmail.com wrote:
>
> > I develop project on QT/C++ and compile both Linux and Windows platforms.
> > In this case, should I recompile SQLite both of these platforms?! Should
> I
> > make some modifications on QT too?!
>
> I think you will need to do all these things.  I know nothing about QT.
>
> > By the way is there any good guidence to add ICU support to SQLite both
> > Linux and Windows platfoems?...
>
> I have reached the limits of my knowledge.  But other people on this list
> may be able to help you.
>
> There is another solution which does not involve full ICU support.  You
> could instead make your own external functions for UPPER_TR() and
> LOWER_TR() which do just the conversions for Turkish.  Presumably they
> could use tables to look up which characters to convert.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Kolay gelsin,
Mucip:)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Simon Slavin]

On 8 Apr 2018, at 10:51pm, mucip.ilb...@gmail.com wrote:

> I develop project on QT/C++ and compile both Linux and Windows platforms.
> In this case, should I recompile SQLite both of these platforms?! Should I
> make some modifications on QT too?!

I think you will need to do all these things.  I know nothing about QT.

> By the way is there any good guidence to add ICU support to SQLite both
> Linux and Windows platfoems?...

I have reached the limits of my knowledge.  But other people on this list may 
be able to help you.

There is another solution which does not involve full ICU support.  You could 
instead make your own external functions for UPPER_TR() and LOWER_TR() which do 
just the conversions for Turkish.  Presumably they could use tables to look up 
which characters to convert.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[mucip . ilbuga]

Dear Simon,
I develop project on QT/C++ and compile both Linux and Windows platforms.
In this case, should I recompile SQLite both of these platforms?! Should I
make some modifications on QT too?!

By the way is there any good guidence to add ICU support to SQLite both
Linux and Windows platfoems?...

On Mon, Apr 9, 2018 at 12:24 AM, Simon Slavin  wrote:

> On 8 Apr 2018, at 9:55pm, Dr. Mucibirahman İLBUĞA 
> wrote:
>
> > As you see above table it returns incorrect. Only i>>I is working like
> in English language?! When I use this code in PostgreSQL it works correct.
> But I got unexpected result in SQLite unfortunatelly.
>
> Thank you for your examples, which help me understand what you're asking
> for.
>
> Unfortunately, bare SQLite understands only ASCII characters.  It does not
> understand Unicode.  To build in full understanding of Unicode would make
> SQLite almost twice as long.
>
> However, you can add Unicode support to SQLite using ICU:
>
> 
>
> If you're compiling your own SQLite, use section 2 of the above document
> or the option
>
> SQLITE_ENABLE_ICU
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Kolay gelsin,
Mucip:)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Simon Slavin]

On 8 Apr 2018, at 9:55pm, Dr. Mucibirahman İLBUĞA  
wrote:

> As you see above table it returns incorrect. Only i>>I is working like in 
> English language?! When I use this code in PostgreSQL it works correct. But I 
> got unexpected result in SQLite unfortunatelly.

Thank you for your examples, which help me understand what you're asking for.

Unfortunately, bare SQLite understands only ASCII characters.  It does not 
understand Unicode.  To build in full understanding of Unicode would make 
SQLite almost twice as long.

However, you can add Unicode support to SQLite using ICU:



If you're compiling your own SQLite, use section 2 of the above document or the 
option

SQLITE_ENABLE_ICU



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Dr . Mucibirahman İLBUĞA]

08.04.2018 23:23 tarihinde Simon Slavin yazdı:

Can you paste into a reply to this message

some lower-case Turkish text,
what you expect to get,
and what you are getting instead

?  It would be useful to have an example of what is going wrong.


Dear Simon,

First of all thanks a lot for your kind interest.

In Turkish we have lover "i" and lover "ı". And the upper of these 
letters like below;


*Lover**
*   *Upper**
*
i
İ
ı
I
ü
Ü
ç
Ç
ö
Ö
ğ
Ğ
ş
Ş


For example;

"SELECT musteri from tartim;" returns; "ğüşiöçı".

But "SELECT UPPER(musteri) from tartim;" returns; "ğüşIöçı"


As you see above table it returns incorrect. Only i>>I is working like 
in English language?! When I use this code in PostgreSQL it works 
correct. But I got unexpected result in SQLite unfortunatelly.  :(


--
Kolay gelsin,
Mucip:)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPPER function depends on Locale?

[Simon Slavin]

On 8 Apr 2018, at 12:50pm, Dr. Mucibirahman İLBUĞA  
wrote:

> The UPPER function is not working properly in Turkish language?! How can I 
> get this?
> 
> For example small "i" is equal to big "I" in Turkish language. But it's not 
> working properly?!

Can you paste into a reply to this message

some lower-case Turkish text,
what you expect to get,
and what you are getting instead

?  It would be useful to have an example of what is going wrong.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users