UPPER function depends on Locale?

classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|

UPPER function depends on Locale?

Dr. Mucibirahman İLBUĞA
Hi,

I am new in this list and this is my first post.

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?!

Thanks in advance...

--
Regards,
Mucip:)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

Simon Slavin-3
On 8 Apr 2018, at 12:50pm, Dr. Mucibirahman İLBUĞA <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

Simon Slavin-3
On 8 Apr 2018, at 9:55pm, Dr. Mucibirahman İLBUĞA <[hidden email]> 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:

<https://www.sqlite.org/src/artifact?ci=trunk&filename=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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

Dr. Mucibirahman İLBUĞA
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 <[hidden email]> wrote:

> On 8 Apr 2018, at 9:55pm, Dr. Mucibirahman İLBUĞA <[hidden email]>
> 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:
>
> <https://www.sqlite.org/src/artifact?ci=trunk&filename=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.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Kolay gelsin,
Mucip:)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

Simon Slavin-3
On 8 Apr 2018, at 10:51pm, [hidden email] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

Dr. Mucibirahman İLBUĞA
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 <[hidden email]> wrote:

> On 8 Apr 2018, at 10:51pm, [hidden email] 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Kolay gelsin,
Mucip:)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

Dr. Mucibirahman İLBUĞA
In reply to this post by Simon Slavin-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

Olivier Mascia
> Le 9 avr. 2018 à 08:42, Dr. Mucibirahman İLBUĞA <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

Dr. Mucibirahman İLBUĞA
In reply to this post by Olivier Mascia
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

Olivier Mascia
> Le 9 avr. 2018 à 11:39, Dr. Mucibirahman İLBUĞA <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

sandu
In reply to this post by Dr. Mucibirahman İLBUĞA
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

sandu
In reply to this post by Simon Slavin-3
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:
>
> &lt;https://www.sqlite.org/src/artifact?ci=trunk&amp;filename=ext/icu/README.txt&gt;
>
> If you're compiling your own SQLite, use section 2 of the above document
> or the option
>
> SQLITE_ENABLE_ICU
>
> &lt;https://www.sqlite.org/compile.html&gt;
>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

Simon Slavin-3


On 9 Apr 2018, at 8:49pm, sandu <[hidden email]> wrote:

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

See section 1.1 of

<https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/icu/README.txt>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

sandu
Simon Slavin-3 wrote
> See section 1.1 of
>
> &lt;https://www.sqlite.org/src/artifact?ci=trunk&amp;filename=ext/icu/README.txt&gt;
>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPPER function depends on Locale?

sandu
In reply to this post by Dr. Mucibirahman İLBUĞA
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12