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?
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.
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?
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)
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.
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.
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
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.
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.
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.