A collation would also work, I don’t think there’s a need to preserve case but 
I guess I have thus far and perhaps that’s an easier route than attempting to 
alter data in place, I could simply add the function in a way it can blend in 
perhaps.

I tried to find a decent wiki page, but does anyone have good examples of 
migrations that add constraints or do fancy stuff? 

Do I have to stuff raw SQL into a migration or are there functions I can’t see 
in there--

> On Nov 24, 2021, at 8:52 AM, Samuel Pelletier <sam...@samkar.com> wrote:
> 
> Jesse,
> 
> If you specify a case insensitive collation for your column in the table, you 
> can preserve case and maintains case insensitive uniqueness. If you do not 
> know about collation, begin by reading on the subject, they basically define 
> how to compare and sort strings values.
> 
> Depending on the probability of duplicate and how you want to handle this 
> problem, you can try-catch or pre check before saving, you probably prefer 
> try-catch because it save a round-trip to the database. Tu use try-catch, you 
> need the contraint in the database though.
> 
> Samuel
> 
>> Le 24 nov. 2021 à 08:02, Jesse Tayler <jtay...@oeinc.com 
>> <mailto:jtay...@oeinc.com>> a écrit :
>> 
>> so, basically, you are suggesting that I store them flat lowercase and put a 
>> constraint on these two strings and just lose any case the user entered 
>> which is fine I think.
>> 
>> With the lowercase assured the constraint will prevent duplicates and I’d 
>> catch that exception during creation and handle it
>> 
>>> On Nov 24, 2021, at 12:19 AM, Samuel Pelletier <sam...@samkar.com 
>>> <mailto:sam...@samkar.com>> wrote:
>>> 
>>> If your usernames (or keyString) are case insensitive, store them in a 
>>> normalized case (in lowercase for exemple). 
>>> 
>>> You can add an overridden 
>>> public void setKeyString(String value) {
>>>     if (value != null) {
>>>             value = value.toLowerCase();
>>>     }
>>>     super.setKeyString(value);
>>> }
>>> 
>>> You may also specify a collation to the column in the database if you want 
>>> to preserve case but index and compare as case insensitive.
>>> 
>>> Samuel
>>> 
>>>> Le 23 nov. 2021 à 17:26, Jesse Tayler via Webobjects-dev 
>>>> <webobjects-dev@lists.apple.com <mailto:webobjects-dev@lists.apple.com>> a 
>>>> écrit :
>>>> 
>>>> 
>>>> 
>>>>> On Nov 23, 2021, at 5:17 PM, Paul Hoadley <pa...@logicsquad.net 
>>>>> <mailto:pa...@logicsquad.net>> wrote:
>>>>> 
>>>>> Are you able to paste in some code? There's probably a solution, but this 
>>>>> is getting a bit hard to follow in the abstract.
>>>>> 
>>>> 
>>>> So, I fetch first
>>>> 
>>>>            EOQualifier qual = 
>>>> DataPoint.TYPE.eq("twitter").and(DataPoint.KEY_STRING.likeInsensitive(username));
>>>> 
>>>> If there’s no EO, I create and save right away but at high volumes this 
>>>> CREATE statement must create only unique entries and those entries must 
>>>> match this qualifier which uses insensitive case
>>>> 
>>>> I figure the pattern should be to create an object with a DB level 
>>>> constraint such that a duplicate raises an error, upon catching that 
>>>> error, I can simply fetch again and return the one, single EO representing 
>>>> that record
>>>> 
>>>> When I tried regular constraints I did not see a way to replicate the 
>>>> required logic, so I found some advise about triggers and some other 
>>>> things I didn’t fully understand.
>>>> 
>>>> I realize usernames generally have this kind of issue, so I figure this is 
>>>> a design pattern that is hardly unique to us and I should get advice!
>>>> 
>>>> _______________________________________________
>>>> Do not post admin requests to the list. They will be ignored.
>>>> Webobjects-dev mailing list      (Webobjects-dev@lists.apple.com 
>>>> <mailto:Webobjects-dev@lists.apple.com>)
>>>> Help/Unsubscribe/Update your Subscription:
>>>> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com 
>>>> <https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com>
>>>> 
>>>> This email sent to sam...@samkar.com <mailto:sam...@samkar.com>
>>> 
>> 
> 

 _______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list      (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com

This email sent to arch...@mail-archive.com

Reply via email to