SQLite Case insensitive Unique Column?

Databases
1

I am trying to create a case insensitive Unique EMail column and am failing miserably…
(yes I know the spec says the first part should be case sensitive - but that is rarely if ever enforced by Email servers these days and effective duplicate EMail addresses would be a problem for what I am doing)

I’ve tried defining the column as :
Email TEXT NOT NULL COLLATE NOCASE UNIQUE

But I can still add records with Email records that are the same except for case

Then I added:
CREATE UNIQUE INDEX UniqueEmail ON Users (Email COLLATE NOCASE);

but that still did not help… Is it because we are using UTF8?

Is there a way to do this?

Thanks,

  • karen
2

Probably. Check: http://www.sqlite.org/datatype3.html#collation

BINARY - Compares string data using memcmp(), regardless of text encoding.
NOCASE - The same as binary, except the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is performed. [b]Note that only ASCII characters are case folded. SQLite does not attempt to do full UTF case folding due to the size of the tables required.[/b]
RTRIM - The same as binary, except that trailing space characters are ignored.
3

I don’t know for sure what’s occurring, but couldn’t be a possible workaround, you just storing a string.Lowercase.Trim of that email and always convert your emails to a .Lowercase.Trim before comparing?

4

Mixed case is more readable and I have a similar situation but it’s not for Email addresses… and there readability is more important…

The obvious work around is in Xojo code to store both the text as entered for display, and an upper (or lower) cased version for testing uniqueness and sorting… But I want to keep as much logic as I reasonably can in the DB itself (I might be able to do it using triggers - but that gets complicated)

Thanks

  • Karen
5

Weird. Your code makes perfect sense if the emails uses standard ascii chars.
Need a feedback case with an example perhaps?

6

SQlIte is NOT compiled with full ICU support so it doesn’t support proper conversion of many non-ascii characters to upper / lower case
Adding this to the plugin would add about 5Mb to your app

So you can’t do it properly in just the DB

But this really isn’t new to the beta at all

7

Norman, a program that does not use SQLite will not have any of this overhead ok? (As this plugin will not be on the pack).

I thought we didn’t have an option/solution. As we have, here we go:

A 5Mb overhead, to have it working properly, is a very good trade option, and for non-English language countries, at least, this should be the default option.

Xojo could have both plugins, defaults to complete version (+5Mb) and have the truncated version of plugin in the extras folder, for people who don’t need Latin/Nordic chars correctly ordered and wants less 5Mb on their package to replace the complete version (I can’t imagine who, Canada has many accented words, USA uses some e.g. Fianc, Rsum,…). Or the opposite, have the full version on extras for the rest of the world. Any not English country will suffer the lack of the correct order. Think about it. :wink:

8

Second that

9

Ugh. No, Xojo should not include this by default. It doesn’t have to. The preferred method of deployment is as a SQLite extension. See:

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

If compiling it yourself is out of the question, the right person to ask is probably Christian. In any case, you probably want to keep close tabs on memory use of connections and performance of your queries if you mix this in. You don’t get this stuff for free.

Also, I’m not sure that even solves Karen’s original problem.

10
11

Me too, but it deserves a test inside Xojo Labs. :slight_smile:

12

Calling SQLite compiled with ICU functions “the full version” is incorrect from the start. Did you even read the link I posted:

[code] The easiest way to compile and use the ICU extension is to build
and use it as a dynamically loadable SQLite extension. To do this
using gcc on *nix:

gcc -shared icu.c `icu-config --ldflags` -o libSqliteIcu.so

You may need to add “-I” flags so that gcc can find sqlite3ext.h
and sqlite3.h. The resulting shared lib, libSqliteIcu.so, may be
loaded into sqlite in the same way as any other dynamically loadable
extension.
[/code]

There are at least 5 reasons why this is the right way to handle this, and multiple versions of the built-in SQLite database plugin are a non-starter.

13

Yes. This means we need a new plugin capable of detecting and loading this extension.
That’s ok too. The extra folder could have the libSqliteIcu.so, libSqliteIcu.dll and libSqliteIcu.dylib
This way, if the correct lib is present in the sqlite plugin folder, will be loaded on start up, and make it international compatible.

14

The current REALSQLDatabase and SQLiteDatabase classes are totally capable of loading the extensions. They are loaded at runtime, not design-time. See the MBS SQLite Extension as an example.

15

Ok, bad to know. Seems that right now there isn’t a plug-and-play solution for the case, for international users; and as there are workarounds, even paid and from third parties, maybe we will not see one soon. :stuck_out_tongue: