Re: [sqlite] BETWEEN and explicit collation assignment

[Nico Williams]

On Mon, Aug 26, 2013 at 2:03 PM, Roman Fleysher
 wrote:

Certainly associating a type conversion with a lexical instance of an
operator, applying to all of the operator's operands (or perhaps even
just to some, while having to explicitly cast others) would work, at
least for operations which can only be accessed via the given
syntactic operator.  But while some implicit type conversions are
useful and handy, I don't think *collations* can be implied, really,
at least not if there are no constant literals around (and we have no
syntax for associating collations with those!)...

Now, perhaps for most operators we can't easily pick one operand to
supply type information for implicit casting of the other(s), but for
BETWEEN you might argue that the type of the first operand should be
used to inform type conversion of the second and third operands.  That
would work, and it'd be easy to remember even.  But conceptually even
then the collation is best thought of as an attribute of types.  For
string values changing their collation is easy, thankfully: it's just
an attribute of the string.  And most systems don't keep track of
collations (or lots of other info) associated with specific strings --
this includes SQLite, IIRC.  In such systems one workaround for the
lack of compile- and/or run-time sting collation information... would
be to specify the collation syntactically.  So you have a point, but I
think the point really is that an RDBMS should keep track of column
and string collations so as to avoid having to require lexical clues
for collation.

Of course, lacking a syntax for associating collations with string
literals there will be times when some, or even all of the operands to
an operation that needs collation information, is missing.  In such
cases the collation information of some operands could be used to cast
the others, or lexical clues might be needed, or a default might be
provided.

So, in short, I think you and James are both right, and maybe we're
squaring a circle.  But still, notionally I'm with James on this:
collation is an attribute of type.

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

Re: [sqlite] Update field from standard input with sqlite3 command line utility

[James K. Lowden]

On Mon, 26 Aug 2013 20:01:08 +0200
Clemens Ladisch  wrote:

> sqlite3 database.db "UPDATE table1 SET column3 = CAST(x'$(hexdump -v
> -e '1/1 "%02x"' file.xml)' AS TEXT) WHERE column1 = 'some name';"

$ hexdump -C input
  20 21 22 23 24 25 26 27  28 29 2a 2b 2c 2d 2e 2f  | !"#$%&'()*+,-./|
0010  30 31 32 33 34 35 36 37  38 39 3a 3b 3c 3d 3e 3f  |0123456789:;<=>?|
0020  40 41 42 43 44 45 46 47  48 49 4a 4b 4c 4d 4e 4f  |@ABCDEFGHIJKLMNO|
0030  50 51 52 53 54 55 56 57  58 59 5a 5b 5c 5d 5e 5f  |PQRSTUVWXYZ[\]^_|
0040  60 61 62 63 64 65 66 67  68 69 6a 6b 6c 6d 6e 6f  |`abcdefghijklmno|
0050  70 71 72 73 74 75 76 77  78 79 7a 7b 7c 7d 7e |pqrstuvwxyz{|}~|
005f

$ DATA=$(sed "s/[']/&&/g" input)
$ sqlite3 db "select '${DATA}' as alphabet;"
alphabet
   
-
  
 !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]
^_`abcdefghijklmnopqrstuvwxyz{|}~
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BETWEEN and explicit collation assignment

[James K. Lowden]

On Mon, 26 Aug 2013 19:03:39 +
Roman Fleysher  wrote:

> However, sometimes, in comparison we want to ignore some of the
> attributes, or compare derived ones. Many busses can carry 25 people,
> and may be considered equal if we simply need to transport people.
> Busses certainly differ by other attributes. 

Busses might indeed differ in many ways, but if you make NAME the
primary key for BUSSES, the rule is not "compare BUSSES, ignoring
columns other than NAME".  The rule is "compare BUSSES.NAME". 

> it is the comparison ( "=", BETWEEN, IN , etc) statements that must
> be modified

This not a syntax issue.  Equality is deeply embedded in the system, in
many places where there's no SQL in play (e.g. keys).  

It's a system of types and operators.  We can already convert between
types and compare them.  If you can show some kind of comparison
that *cannot* be done via type conversion using the operators exactly
as they are, you might have a point.  

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

Re: [sqlite] SQLite, .Net and Datareader: Close method is too slow.

[Larry Brasfield]

*Mário Cardia wrote:*
> Solved:
>
> The Method Close of the Datareader was slow because it was inside a
> try-catch.
> I remove de try-catch and it´s ok now.

The SQLiteDataReader class, properly implementing the IDataReader
interface, exposes and relies upon proper use of the IDispose
interface.

In general, objects exposing IDispose should be created and
disposed using C# 'using' construct or its equivalent in whatever
CLR-targeted language you are using.  This would likely also fix
your slowness issue, and would ward off other difficulties that
are likely to arise in your use of these objects.

You should perhaps read about IDispose and the usage patterns
recommended for classes which need to implement it.  Several
classes in SQLite.NET, because they allocate resources needing
more deterministic release than the .NET garbage collector can
provide, are best used with the 'using' construct.

Best regards,
-- 

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

Re: [sqlite] SQLite, .Net and Datareader: Close method is too slow.

[Mário Cardia]

Solved:

The Method Close of the Datareader was slow because it was inside a
try-catch.
I remove de try-catch and it´s ok now.


[]'s

Mário Cardia


2013/8/27 Mário Cardia 

>
> Hi,
>
> I'm have a batch proccess that reads from many datareaders.
>
> When I finished with de datareader I call the close() method.
>
> This is too slow. If I don´t close the data reader, the application runs
> in 0.4ms
> If I close the datareader, the application runs in 26 seconds.
>
> Does anybody has a tip in how to solve this problem?
>
> There are many datareaders in sub routines. I´m afraid that not closing
> them I will keep trash in memory.
>
>
> []'s
>
> Mário Cardia
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collation advice

[Jan Slodicka]

Big thanks, Igor.
BTW, ICU surprised me.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668p70720.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.8.0 breaks pkgconfig file

[Dan Kennedy]

On 08/27/2013 09:30 PM, Andreas Radke wrote:

A common build in Arch Linux doesn't properly replace

Version: @RELEASE@ in file sqlite3.pc.


Thanks for reporting this. A new package that should fix this
problem is up now.

Dan.


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

Re: [sqlite] 3.8.0 Update

[James Pearson]

On 27 Aug 2013, at 9:43, Dan Kennedy wrote:


On 08/27/2013 09:33 PM, James Pearson wrote:

On 27 Aug 2013, at 8:19, Richard Hipp wrote:

On Tue, Aug 27, 2013 at 10:15 AM, James Pearson  
wrote:


I've just updated to SQLite 3.8.0 and seem to have lost the ability 
to use

the up arrow key to cycle through my command line history.


Which OS?

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

- - -

Mac OS X - 10.8.4


Updated by building the sqlite-autoconf-308.tar.gz
package, correct?

Are you upgrading from an earlier version that you had
built and installed yourself? Or from the system SQLite?

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


- - -

Actually I updated from the precompiled Mac binaries, and my previous 
version was also from the precompiled binaries.


I've never built SQLite myself.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.8.0 Update

[Dan Kennedy]

On 08/27/2013 09:33 PM, James Pearson wrote:

On 27 Aug 2013, at 8:19, Richard Hipp wrote:


On Tue, Aug 27, 2013 at 10:15 AM, James Pearson  wrote:


I've just updated to SQLite 3.8.0 and seem to have lost the ability to use
the up arrow key to cycle through my command line history.


Which OS?

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

- - -

Mac OS X - 10.8.4


Updated by building the sqlite-autoconf-308.tar.gz
package, correct?

Are you upgrading from an earlier version that you had
built and installed yourself? Or from the system SQLite?

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

Re: [sqlite] 3.8.0 Update

[James Pearson]

On 27 Aug 2013, at 8:19, Richard Hipp wrote:

> On Tue, Aug 27, 2013 at 10:15 AM, James Pearson  wrote:
>
>> I've just updated to SQLite 3.8.0 and seem to have lost the ability to use
>> the up arrow key to cycle through my command line history.
>>
>
> Which OS?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

- - -

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

[sqlite] 3.8.0 breaks pkgconfig file

[Andreas Radke]

A common build in Arch Linux doesn't properly replace 

Version: @RELEASE@ in file sqlite3.pc.

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

Re: [sqlite] 3.8.0 Update

[Richard Hipp]

On Tue, Aug 27, 2013 at 10:15 AM, James Pearson  wrote:

> I've just updated to SQLite 3.8.0 and seem to have lost the ability to use
> the up arrow key to cycle through my command line history.
>

Which OS?

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

[sqlite] 3.8.0 Update

[James Pearson]

I've just updated to SQLite 3.8.0 and seem to have lost the ability to 
use the up arrow key to cycle through my command line history.


I was a few versions behind so that functionality may have been removed 
in a previous release.  Is there a way to get command recall back?


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

Re: [sqlite] Collation advice

[Igor Tandetnik]

On 8/27/2013 6:50 AM, Jan Slodicka wrote:



That's not all that unusual: even in English, you might want to sort
Muenster and Münster next to each other.


Thanks, Igor. Do you know more? Do you consider ascii comparison too
dangerous?


At one point, we did in our project the same thing you are trying now: 
check if both strings are pure ASCII then compare them the fast way 
(equivalent to memcpy, though we didn't use it but did the checking and 
comparison together, in one pass); otherwise fall back to the 
OS-provided locale-sensitive comparison.


In the end, we discovered ICU: it manages to be much faster than the OS 
comparisons (not exactly surprising), and even slightly faster than our 
hand-written check-and-compare-ASCII loop, while being correct for all 
locales. Ours is a desktop application, not resource constrained, so 
bundling ICU with it was not a problem.


Here's the summary of all the cases I know of where simple ASCII 
comparison does the wrong thing (which doesn't mean there aren't others 
I don't know of):


- Contractions in various Latin-script-using Eastern-European languages 
(like Hungarian) you are already aware of.


- Several contractions in Welsh:
http://en.wikipedia.org/wiki/Welsh_language#Orthography

- German phonebook sort, that puts AE between A and B, OE between O and 
P, and UE between U and V. German defines two sorts, called "dictionary" 
and "phonebook", which differ only in whether these contractions are 
used. On Windows, the user can configure which sort to use.


- Spanish traditional sort (as opposed to modern sort) puts CH between C 
and D, and LL between L and M. No longer used for anything but the 
academic linguistic studies, can be safely ignored.


- Finnish treats W as a variant of V (it's considered a secondary 
distinction, like that between A and Á).


- Lithuanian puts Y between I and J

--
Igor Tandetnik

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

Re: [sqlite] Collation advice

[Igor Tandetnik]

On 8/27/2013 9:08 AM, Igor Tandetnik wrote:

On 8/27/2013 6:37 AM, Jan Slodicka wrote:

Besides this I am aware of only 1 problem - Swedish should treat v/w
identically.


Not anymore. There was a reform in 2006, and V and W now sort separately.


... but in Finnish, they are still sorted together (so WAX goes after 
VOICE under Swedish rules, but before under Finnish).

--
Igor Tandetnik

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

Re: [sqlite] Collation advice

[Simon Slavin]

On 27 Aug 2013, at 2:08pm, Igor Tandetnik  wrote:

> On 8/27/2013 6:37 AM, Jan Slodicka wrote:
>> Besides this I am aware of only 1 problem - Swedish should treat v/w 
>> identically.
> 
> Not anymore. There was a reform in 2006, and V and W now sort separately.

A great illustration of why a non-professional shouldn't be trying to do this.  
The reason the libraries are complicated and time-consuming is that they need 
to be.  Don't try to do this at home.

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

Re: [sqlite] Collation advice

[Igor Tandetnik]

On 8/27/2013 6:37 AM, Jan Slodicka wrote:

Besides this I am aware of only 1 problem - Swedish should treat v/w 
identically.


Not anymore. There was a reform in 2006, and V and W now sort separately.
--
Igor Tandetnik

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

Re: [sqlite] CREATE INDEX and column order

[Simon Slavin]

On 27 Aug 2013, at 1:07pm, Doug Nebeker  wrote:

> I was reading about the new query planner and came across a few references to 
> that idea that the left most columns in the index definition should be the 
> most unique (as far as values in the column are concerned).
> 
> Is that correct?  In my case, many tables have a timestamp column, and I've 
> been using that as my right-most column, but it seems it would be a great 
> candidate to be switched.

When using an index, SQL has to work from the most significant end -- the left 
-- to the least significant end -- the right.  For instance, suppose you have a 
phone book

CREATE TABLE phonebook (firstname TEXT, surname TEXT, phonenumber TEXT)
CREATE INDEX psf ON phonebook (surname, firstname)

This index is useless for looking someone up by their firstname, because it has 
everyone listed in surname order:

Abelson, David
Abelson, Joan
Smith, David
Smith, Martine
Smith, Tom

If you wanted to look up all the 'Martines' you'd just have to look through the 
whole index anyway.  You might as well scan the original table. [1]

Simon.

[1] Yes, many picky details about this but I'm simplifying for the purpose of 
explanation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite, .Net and Datareader: Close method is too slow.

[Mário Cardia]

Hi,

I'm have a batch proccess that reads from many datareaders.

When I finished with de datareader I call the close() method.

This is too slow. If I don´t close the data reader, the application runs in
0.4ms
If I close the datareader, the application runs in 26 seconds.

Does anybody has a tip in how to solve this problem?

There are many datareaders in sub routines. I´m afraid that not closing
them I will keep trash in memory.


[]'s

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

[sqlite] CREATE INDEX and column order

[Doug Nebeker]

I was reading about the new query planner and came across a few references to 
that idea that the left most columns in the index definition should be the most 
unique (as far as values in the column are concerned).

Is that correct?  In my case, many tables have a timestamp column, and I've 
been using that as my right-most column, but it seems it would be a great 
candidate to be switched.

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

[sqlite] SQLite + .Net + DataReader: Close Method is too slow.

[Mário Cardia]

Hi,

I'm have a batch proccess that reads from many datareaders.

When I finished with de datareader I call the close() method.

This is too slow. If I don´t close the data reader, the application runs in
0.4ms
If I close the datareader, the application runs in 26 seconds.

Does anybody has a tip in how to solve this problem?

There are many datareaders in sub routines. I´m afraid that not closing
them I will keep trash in memory.


[]'s

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

[sqlite] Documentation improvement request

[Simon Slavin]

I think it would be useful if



mentioned that there was a PRAGMA that did the same thing:



This would help people who aren't calling the SQLite API directly but are 
working with a more restrictive shim API.

The reverse reference for this function is already present.  However, it might 
be worth going through all the PRAGMAs which duplicate API calls and mentioning 
them in the documentation for those calls.

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

Re: [sqlite] Database locking Error

[Simon Slavin]

On 27 Aug 2013, at 5:15am, techi eth  wrote:

> For read operation i am doing _prepare(), _step(), _finalize().
> For all other operation i am doing _exec().
> 
> Do you see any issue ?

Nothing obvious from what I already know apart from the fact that you don't 
mention setting a timeout:




Without that, a process getting a lock won't back off and retry, it'll just 
immediately return an error.  Which is almost never what people want.  You 
would probably be better off setting this to 5 seconds or something.

I suspect that the process which is getting the lock is fine and that it's the 
other process which is keeping the file locked.

Are you checking the return codes from /all/ your SQLite calls in other 
processes ?  The code you included doesn't seem to do that but I thought that 
might be pseudocode.

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

Re: [sqlite] Collation advice

[Jan Slodicka]

> btw. Muenster / Münster would fall back to full comparison due to the ü

Yes, but what could fail then is for example Muenster / Muster. 
In case OS sorts ue/ü between uz and v - ascii and OS comparisons would
yield different results.





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668p70702.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collation advice

[Jan Slodicka]

> That's not all that unusual: even in English, you might want to sort
> Muenster and Münster next to each other. 

Thanks, Igor. Do you know more? Do you consider ascii comparison too
dangerous?

Jan



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668p70701.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collation advice

[Jan Slodicka]

Hi Dan

> Sounds like an accident waiting to happen though. A developer could 
update a file using the sqlite shell.

I forgot to mention that our DB uses custom enryption. Hence nobody can
manipulate anything outside the application. The only exception is our
custom-built sqlite shell, which is used inhouse only.

Regards,
Jan



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668p70700.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collation advice

[Jan Slodicka]

Hi Simon

> Given the declared setup we have two concerns:
> 1) dependent on system locale which can be changed 

As I already mentioned in one of previous posts: If it proves to be a
problem, we can remember used locale and force index rebuild if necessary.
(Android version behaves this way already now.)

> 2) requires detailed knowledge of individual languages (how many languages
> can one person know that well ?)

What we need to do is to decide when we can afford to use ascii comparison.
I tried to investigate common (i.e. mostly european) latin-based languages
and found only the digraph problem - the respective languages are excluded
from using ascii optimization. Besides this I am aware of only 1 problem -
Swedish should treat v/w identically. 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668p70699.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collation advice

[Jan Slodicka]

> Unit Tests:   
> I would isolate the comparison in a core function, and primarily test that
> core function

Main application is written i C#. Sqlite and extensions are in dll, hence
the main code cannot directly access the collation function. While we have
low-level tests that are occasionally run, we need something that can be run
and supervised regularly.

That's way I came with the idea to test the outcome of high-level SQL
command such as "SELECT ? < ? COLLATE NOCASE".

At the bottom there is the class used for that purpose. You have to call
TestCollation() with supplied string array. The function tests basic
properties - reflexivity, symmetry, transitivity - and throws an exception
in case of any violation.

There are 2 problems with this attitude:

a) String selection
b) The algoritm is heavily computationally demanding

I decided to apply this systematical approach when we got a sample database
with a corrupted index. However, the test did not discover anything.

 >assert( cmp("Ape", "monkey") < 0); 

Perhaps I should add tests comparing specific string instances as you
suggest. I would invite any advice concerning good test samples.


This is the above mentioned code:




internal class MyComparer : IDisposable
{
  private SqliteCommand cmdLT, cmdGT, cmdEQ;
  private DbParameter parLT1, parLT2, parGT1, parGT2, parEQ1, parEQ2;
  public MyComparer(SqliteConnection cnn)
  {
cmdLT = cnn.CreateCommand(); cmdLT.CommandText = "SELECT ? < ? COLLATE
NOCASE;";
parLT1 = cmdLT.CreateParameter(); parLT1.DbType = DbType.String;
cmdLT.Parameters.Add(parLT1);
parLT2 = cmdLT.CreateParameter(); parLT2.DbType = DbType.String;
cmdLT.Parameters.Add(parLT2);

cmdGT = cnn.CreateCommand(); cmdGT.CommandText = "SELECT ? > ? COLLATE
NOCASE;";
parGT1 = cmdGT.CreateParameter(); parGT1.DbType = DbType.String;
cmdGT.Parameters.Add(parGT1);
parGT2 = cmdGT.CreateParameter(); parGT2.DbType = DbType.String;
cmdGT.Parameters.Add(parGT2);

cmdEQ = cnn.CreateCommand(); cmdEQ.CommandText = "SELECT ? == ? COLLATE
NOCASE;";
parEQ1 = cmdEQ.CreateParameter(); parEQ1.DbType = DbType.String;
cmdEQ.Parameters.Add(parEQ1);
parEQ2 = cmdEQ.CreateParameter(); parEQ2.DbType = DbType.String;
cmdEQ.Parameters.Add(parEQ2);
  }

  bool LT(string s1, string s2) { parLT1.Value = s1; parLT2.Value = s2;
return (Int64)cmdLT.ExecuteScalar() == 1; }
  bool GT(string s1, string s2) { parGT1.Value = s1; parGT2.Value = s2;
return (Int64)cmdGT.ExecuteScalar() == 1; }
  bool EQ(string s1, string s2) { parEQ1.Value = s1; parEQ2.Value = s2;
return (Int64)cmdEQ.ExecuteScalar() == 1; }

  public void Test(string s)
  {
// Test s==s
if (!EQ(s, s))
  throw new Exception(String.Format("NOCASE collate ['{0}', '{0}']
fails", s));
  }

  public void Test(string s1, string s2)
  {
// Exactly one of these relations must happen: s1s2.
int x = 0;
if( LT(s1, s2)) x++;
if( GT(s1, s2)) x++;
if( EQ(s1, s2)) x++;
if (x != 1)
  throw new Exception(String.Format("NOCASE collate ['{0}', '{1}']
fails", s1, s2));
  }

  public void Test(string s1, string s2, string s3)
  {
if( EQ(s1,s2) || EQ(s1,s3) || EQ(s1,s3))
  return;   // cant test transitivity

bool lt12 = LT(s1, s2);
bool lt13 = LT(s1, s3);
bool lt23 = LT(s2, s3);
bool lt21 = !lt12;
bool lt31 = !lt13;
bool lt32 = !lt23;
if (lt12 && lt23) { if (!lt13) goto labelError; }
if (lt13 && lt32) { if (!lt12) goto labelError; }
if (lt21 && lt13) { if (!lt23) goto labelError; }
if (lt23 && lt31) { if (!lt21) goto labelError; }
if (lt31 && lt12) { if (!lt32) goto labelError; }
if (lt32 && lt21) { if (!lt31) goto labelError; }
return;

labelError:
throw new Exception(String.Format("NOCASE collation not transitive for
['{0}', '{1}', '{2}']", s1, s2, s3));
  }

  public void Dispose()
  {
if (cmdLT != null) { cmdLT.Dispose(); cmdLT = null; }
if (cmdGT != null) { cmdGT.Dispose(); cmdGT = null; }
if (cmdEQ != null) { cmdEQ.Dispose(); cmdEQ = null; }
  }
}

void TestCollation( string[] texts )
{
  using (MyComparer comparer = new MyComparer(_cnn))
  {
int n = texts.Length;
foreach (string t in texts)
  comparer.Test(t);

for (int i = 0; i < n; ++i)
for (int j = i + 1; j < n; ++j)
  comparer.Test(texts[i], texts[j]);

for (int i = 0; i < n; ++i)
for (int j = i + 1; j < n; ++j)
for (int k = j + 1; k < n; ++k)
  comparer.Test(texts[i], texts[j], texts[k]);
  }
}




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668p70698.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collation advice

[Jan Slodicka]

P.S.
Based on your doubts (this iOS code wasn't written by me, I normally work on
other platforms), I decided to do some googling. And well,  this article
  
demostrates incompatibility between ascii and CFStringCompare. Perhaps we
should use alternative B for the SAFE_CHAR macro.

Will search more about CFStringCompare.




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668p70697.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collation advice

[Jan Slodicka]

> Anyway, I would definitely  unicode-normalize the strings before putting
them into the database. You might avoid the special handling for the
digraphs if you normalize towards the digraph code points: only strings
actually containing digraphs would escape your optimization. 

Tough stuff. Although I tried to learn something about the Unicode, I am no
expert.

Anyway, the idea is to handle what can be handled safely and fast and let
the OS do the difficult things.  We are here at mercy of the OS, but I
believe they handle correctly 100% of common strings and 99.% of less
frequent strings. Correct me if I am wrong, please.

In fact, our Android version uses Unicode ICU library, but we want to avoid
this in general. (Performance, size, maintenance.)

As is Unicode testing concerned, I'll start a new post.

Regards,
Jan Slodicka



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668p70696.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collation advice

[Jan Slodicka]

> If you build a database using NOCASE version 1, then try to use the 
database with a different NOCASE version X where X>1, the database will 
appear to be corrupt, since the order of the indices will be incorrect.

Thanks, the danger is clear.

Regards,
Jan Slodicka



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668p70695.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collation advice

[Jan Slodicka]

> There's a problem here when comparing empty strings, that is, when nKey1 
== nKey2 == 0. 

Good point. Must have been blind when I wrote this. 

> Why are you comparing elements at index 0 explicitly? memcmp() should do
> the right thing. 

Yes, but it is faster this way.

Regards,
Jan Slodicka



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668p70694.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users