Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

[Pavel Ivanov]

> I have created my own function  which uses a global variable for the default
> locale. (see attached)

I guess I received your attachment because you sent this email to me
directly but generally this list doesn't allow attachments - you
should include your code into email.

And about your code: I can't check it all I've never written user
functions for SQLite. But your setLocale function is incorrect because
your copy the characters from zLocale and don't copy zero-termination
of the string.

>>   At application startup you fill this variable with value
>>   from table db_locale or with some default value if there's nothing in
>>   db_locale. Then each time you change your application's variable you
>>   save new value in table db_locale. That's it.
>
> I am struggling with this one, could someone help me out with sample code.
> I figure that the code would go to  openDatabase(..), but that is about all
> I know at the moment.

No, you won't use openDatabase and won't use any internal functions of
SQLite. You will use sqlite3_open, sqlite3_prepare_v2, sqlite3_step
and sqlite3_finalize just like with any regular access to SQLite
database.


Pavel


On Fri, Mar 2, 2012 at 5:51 PM, Grace Simon Batumbya
 wrote:
>
>   That's a wrong approach. First, you don't need to modify functions in
>   SQLite code, you need to create your own. Your function will convert
>   using locale saved in some variable in your application.
>
> I have created my own function  which uses a global variable for the default
> locale. (see attached)
>
> At application startup you fill this variable with value
>   from table db_locale or with some default value if there's nothing in
>   db_locale. Then each time you change your application's variable you
>   save new value in table db_locale. That's it.
>
> I am struggling with this one, could someone help me out with sample code.
> I figure that the code would go to  openDatabase(..), but that is about all
> I know at the moment.
>
> Thanks.
>
> Regards,
>
> Grace Batumbya
> Research Assistant | Seneca CDOT
> Phone: 416-491-5050 x3548
> cdot.senecac.on.ca
>
> On 3/1/2012 09:48, Pavel Ivanov wrote:
>
> Given that there exists a table db_locale [CREATE TABLE db_locale (locale
> text)],
> what lines of code would be used to query that table from inside this
> function?
>
> That's a wrong approach. First, you don't need to modify functions in
> SQLite code, you need to create your own. Your function will convert
> using locale saved in some variable in your application. At
> application startup you fill this variable with value from table
> db_locale or with some default value if there's nothing in db_locale.
> Then each time you change your application's variable you save new
> value in table db_locale. That's it.
>
>
> Pavel
>
>
> On Thu, Mar 1, 2012 at 9:33 AM, Grace Simon Batumbya
>  wrote:
>
> I found the function that I would need to modify (see below).
>
> static void icuCaseFunc16(sqlite3_context *p, int nArg, sqlite3_value
> **apArg){
>   const UChar *zInput;
>   UChar *zOutput;
>   int nInput;
>   int nOutput;
>
>   UErrorCode status = U_ZERO_ERROR;
>   const char *zLocale = 0;
>
>   assert(nArg==1 || nArg==2);
>   if( nArg==2 ){
>     zLocale = (const char *)sqlite3_value_text(apArg[1]);
>   }
>
>   zInput = sqlite3_value_text16(apArg[0]);
>   if( !zInput ){
>     return;
>   }
>   nInput = sqlite3_value_bytes16(apArg[0]);
>
>   nOutput = nInput * 2 + 2;
>   zOutput = sqlite3_malloc(nOutput);
>   if( !zOutput ){
>     return;
>   }
>
>   if( sqlite3_user_data(p) ){
>     u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale, );
>   }else{
>     u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale, );
>   }
>
>   if( !U_SUCCESS(status) ){
>     icuFunctionError(p, "u_strToLower()/u_strToUpper", status);
>     return;
>   }
>
>   sqlite3_result_text16(p, zOutput, -1, xFree);
> }
>
> Given that there exists a table db_locale [CREATE TABLE db_locale (locale
> text)],
> what lines of code would be used to query that table from inside this
> function?
>
> Grace Batumbya
> Research Assistant | Seneca CDOT
> Phone: 416-491-5050 x3548
> cdot.senecac.on.ca
>
> On 3/1/2012 08:56, Grace Batumbya wrote:
>
> Is there an example extension you know that I could look at that does this?
> (i am a novice at SQLite)
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Pavel Ivanov [paiva...@gmail.com]
> Sent: March 1, 2012 8:52 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma
> variable
>
> On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya
>  wrote:
>
> You can simply register your
> own lower/upper with one argument that looks wherever you want to know
> what locale to use.
>
> The part of registering a function to override lower/upper I think 

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

[Grace Simon Batumbya]

   That's a wrong approach. First, you don't need to modify functions in
   SQLite code, you need to create your own. Your function will convert
   using locale saved in some variable in your application.
I have created my own function  which uses a global variable for the 
default locale. (see attached)

At application startup you fill this variable with value
   from table db_locale or with some default value if there's nothing in
   db_locale. Then each time you change your application's variable you
   save new value in table db_locale. That's it.

I am struggling with this one, could someone help me out with sample code.
I figure that the code would go to openDatabase(..), but that is about 
all I know at the moment.


Thanks.

Regards,
*Grace Batumbya*
Research Assistant | Seneca CDOT
Phone: 416-491-5050 x3548
cdot.senecac.on.ca 

On 3/1/2012 09:48, Pavel Ivanov wrote:

Given that there exists a table db_locale [CREATE TABLE db_locale (locale
text)],
what lines of code would be used to query that table from inside this
function?

That's a wrong approach. First, you don't need to modify functions in
SQLite code, you need to create your own. Your function will convert
using locale saved in some variable in your application. At
application startup you fill this variable with value from table
db_locale or with some default value if there's nothing in db_locale.
Then each time you change your application's variable you save new
value in table db_locale. That's it.


Pavel


On Thu, Mar 1, 2012 at 9:33 AM, Grace Simon Batumbya
  wrote:

I found the function that I would need to modify (see below).

static void icuCaseFunc16(sqlite3_context *p, int nArg, sqlite3_value
**apArg){
   const UChar *zInput;
   UChar *zOutput;
   int nInput;
   int nOutput;

   UErrorCode status = U_ZERO_ERROR;
   const char *zLocale = 0;

   assert(nArg==1 || nArg==2);
   if( nArg==2 ){
 zLocale = (const char *)sqlite3_value_text(apArg[1]);
   }

   zInput = sqlite3_value_text16(apArg[0]);
   if( !zInput ){
 return;
   }
   nInput = sqlite3_value_bytes16(apArg[0]);

   nOutput = nInput * 2 + 2;
   zOutput = sqlite3_malloc(nOutput);
   if( !zOutput ){
 return;
   }

   if( sqlite3_user_data(p) ){
 u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale,);
   }else{
 u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale,);
   }

   if( !U_SUCCESS(status) ){
 icuFunctionError(p, "u_strToLower()/u_strToUpper", status);
 return;
   }

   sqlite3_result_text16(p, zOutput, -1, xFree);
}

Given that there exists a table db_locale [CREATE TABLE db_locale (locale
text)],
what lines of code would be used to query that table from inside this
function?

Grace Batumbya
Research Assistant | Seneca CDOT
Phone: 416-491-5050 x3548
cdot.senecac.on.ca

On 3/1/2012 08:56, Grace Batumbya wrote:

Is there an example extension you know that I could look at that does this?
(i am a novice at SQLite)

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Pavel Ivanov [paiva...@gmail.com]
Sent: March 1, 2012 8:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma
variable

On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya
  wrote:

You can simply register your
own lower/upper with one argument that looks wherever you want to know
what locale to use.

The part of registering a function to override lower/upper I think I
understand.

But if I wanted to persist the locale, so that even if I disconnect and
reconnect it is still set to the last time I set it, how do I go about
accomplishing this.

Create a special table for that and store your last locale value in
it. Then after reconnect read the locale value from this table.


Pavel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
// DB's default locale
char *DBLocale = 0;
int nLength = 0;

void setLocale(char *zLocale)
{
   sqlite3_free(DBLocale);
   nLength = strlen(zLocale);
   DBLocale = (char *) sqlite3_malloc(nLength);

   strcpy(DBLocale, zLocale);
}

void deleteDBLocale(void * zLocale)
{
   // do thing, since zLocale points to DBLocale.
}

// lower and upper using the DB locale 
static void lowerUpperDefaultLocale(sqlite3_context *p, int nArg, sqlite3_value 
**apArg)
{
   sqlite3_value *args[2];

   if (!DBLocale)
   {
  // hand off to inbuilt functions
  icuCaseFunc16(p, nArg, apArg);

  return;
   }

args[0] = apArg[0];
   args[1] = (sqlite3_value *)malloc(sizeof(sqlite3_value));
   args[1]->db= apArg[0]->db;   /* The associated database 

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

[Pavel Ivanov]

> Given that there exists a table db_locale [CREATE TABLE db_locale (locale
> text)],
> what lines of code would be used to query that table from inside this
> function?

That's a wrong approach. First, you don't need to modify functions in
SQLite code, you need to create your own. Your function will convert
using locale saved in some variable in your application. At
application startup you fill this variable with value from table
db_locale or with some default value if there's nothing in db_locale.
Then each time you change your application's variable you save new
value in table db_locale. That's it.


Pavel


On Thu, Mar 1, 2012 at 9:33 AM, Grace Simon Batumbya
 wrote:
> I found the function that I would need to modify (see below).
>
> static void icuCaseFunc16(sqlite3_context *p, int nArg, sqlite3_value
> **apArg){
>   const UChar *zInput;
>   UChar *zOutput;
>   int nInput;
>   int nOutput;
>
>   UErrorCode status = U_ZERO_ERROR;
>   const char *zLocale = 0;
>
>   assert(nArg==1 || nArg==2);
>   if( nArg==2 ){
>     zLocale = (const char *)sqlite3_value_text(apArg[1]);
>   }
>
>   zInput = sqlite3_value_text16(apArg[0]);
>   if( !zInput ){
>     return;
>   }
>   nInput = sqlite3_value_bytes16(apArg[0]);
>
>   nOutput = nInput * 2 + 2;
>   zOutput = sqlite3_malloc(nOutput);
>   if( !zOutput ){
>     return;
>   }
>
>   if( sqlite3_user_data(p) ){
>     u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale, );
>   }else{
>     u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale, );
>   }
>
>   if( !U_SUCCESS(status) ){
>     icuFunctionError(p, "u_strToLower()/u_strToUpper", status);
>     return;
>   }
>
>   sqlite3_result_text16(p, zOutput, -1, xFree);
> }
>
> Given that there exists a table db_locale [CREATE TABLE db_locale (locale
> text)],
> what lines of code would be used to query that table from inside this
> function?
>
> Grace Batumbya
> Research Assistant | Seneca CDOT
> Phone: 416-491-5050 x3548
> cdot.senecac.on.ca
>
> On 3/1/2012 08:56, Grace Batumbya wrote:
>
> Is there an example extension you know that I could look at that does this?
> (i am a novice at SQLite)
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Pavel Ivanov [paiva...@gmail.com]
> Sent: March 1, 2012 8:52 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma
> variable
>
> On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya
>  wrote:
>
> You can simply register your
> own lower/upper with one argument that looks wherever you want to know
> what locale to use.
>
> The part of registering a function to override lower/upper I think I
> understand.
>
> But if I wanted to persist the locale, so that even if I disconnect and
> reconnect it is still set to the last time I set it, how do I go about
> accomplishing this.
>
> Create a special table for that and store your last locale value in
> it. Then after reconnect read the locale value from this table.
>
>
> Pavel
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

[Grace Simon Batumbya]

I found the function that I would need to modify (see below).

   |static void icuCaseFunc16(sqlite3_context *p, int nArg,
   sqlite3_value **apArg){
  const UChar *zInput;
  UChar *zOutput;
  int nInput;
  int nOutput;

  UErrorCode status = U_ZERO_ERROR;
  const char *zLocale = 0;

  assert(nArg==1 || nArg==2);
  if( nArg==2 ){
zLocale = (const char *)sqlite3_value_text(apArg[1]);
  }

  zInput = sqlite3_value_text16(apArg[0]);
  if( !zInput ){
return;
  }
  nInput = sqlite3_value_bytes16(apArg[0]);

  nOutput = nInput * 2 + 2;
  zOutput = sqlite3_malloc(nOutput);
  if( !zOutput ){
return;
  }

  if( sqlite3_user_data(p) ){
u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale,
   );
  }else{
u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale,
   );
  }

  if( !U_SUCCESS(status) ){
icuFunctionError(p, "u_strToLower()/u_strToUpper", status);
return;
  }

  sqlite3_result_text16(p, zOutput, -1, xFree);
   }
   | 

Given that there exists a table db_locale [CREATE TABLE db_locale 
(locale text)],
what lines of code would be used to query that table from inside this 
function?


*Grace Batumbya*
Research Assistant | Seneca CDOT
Phone: 416-491-5050 x3548
cdot.senecac.on.ca 

On 3/1/2012 08:56, Grace Batumbya wrote:

Is there an example extension you know that I could look at that does this? (i 
am a novice at SQLite)

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Pavel Ivanov [paiva...@gmail.com]
Sent: March 1, 2012 8:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya
  wrote:

You can simply register your
own lower/upper with one argument that looks wherever you want to know
what locale to use.

The part of registering a function to override lower/upper I think I understand.

But if I wanted to persist the locale, so that even if I disconnect and 
reconnect it is still set to the last time I set it, how do I go about 
accomplishing this.

Create a special table for that and store your last locale value in
it. Then after reconnect read the locale value from this table.


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

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

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

[Grace Batumbya]

Is there an example extension you know that I could look at that does this? (i 
am a novice at SQLite)

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Pavel Ivanov [paiva...@gmail.com]
Sent: March 1, 2012 8:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya
 wrote:
>>You can simply register your
>>own lower/upper with one argument that looks wherever you want to know
>>what locale to use.
>
> The part of registering a function to override lower/upper I think I 
> understand.
>
> But if I wanted to persist the locale, so that even if I disconnect and 
> reconnect it is still set to the last time I set it, how do I go about 
> accomplishing this.

Create a special table for that and store your last locale value in
it. Then after reconnect read the locale value from this table.


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

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

[Pavel Ivanov]

On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya
 wrote:
>>You can simply register your
>>own lower/upper with one argument that looks wherever you want to know
>>what locale to use.
>
> The part of registering a function to override lower/upper I think I 
> understand.
>
> But if I wanted to persist the locale, so that even if I disconnect and 
> reconnect it is still set to the last time I set it, how do I go about 
> accomplishing this.

Create a special table for that and store your last locale value in
it. Then after reconnect read the locale value from this table.


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

[sqlite] Set Locale for upper() and lower() using a pragma variable

[Grace Batumbya]

>You can simply register your
>own lower/upper with one argument that looks wherever you want to know
>what locale to use.

The part of registering a function to override lower/upper I think I understand.

But if I wanted to persist the locale, so that even if I disconnect and 
reconnect it is still set to the last time I set it, how do I go about 
accomplishing this.

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

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 29/02/12 12:23, Carl Desautels wrote:
> I would like to be able to run one statement that sets the locale for
> upper() and lower()

If you register a function with the same name and number of arguments as a
builtin one, then yours will take priority.  You can simply register your
own lower/upper with one argument that looks wherever you want to know
what locale to use.  It will not affect the one registered by ICU for two
arguments.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9Os04ACgkQmOOfHg372QREcwCgigKHsMfew3d54rfV7hTg5iI+
JXoAoLfDRHglplt/JNxt8JzY57PDTUkb
=8+Au
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Set Locale for upper() and lower() using a pragma variable

[Carl Desautels]

From the ICU documentation, (
http://www.sqlite.org/src/artifact?ci=trunk=ext/icu/README.txt)

 To access ICU "language specific" case mapping, upper() or lower()
should be invoked with two arguments.[...]
lower('I', 'tr_tr') -> 'ı' (small dotless i)


With an ICU enabled build of SQLite, I would like to be able to run one
statement that sets the locale for upper() and lower() so that they default
to that locale instead of ASCII.

At the moment I am thinking of adding a "pragma" variable, which would then
be used by "icuCaseFunc16" as the default locale, how do I go about adding
a "pragma" variable?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users