Thanks Ted, actually I was looking to see how to make a compound constraint -
one static string type=“twitter” and I guess a lowercase version of a
user-entered display string keyStringToLowercase = username and the only way I
saw to add that into migrations was to write SQL and inject it?
But I did NOT know how to adjust those error strings! I have several bothersome
error reports so, you’ve opened my eyes! I’ll look into that .strings key stuff
there and see if I can figure that out. I might ping you about it...
> On Nov 24, 2021, at 2:43 PM, Theodore Petrosky <tpetro...@agencysacks.com>
> wrote:
>
> An example of a migration:
> Postgresql throws the exception then in ValidationException.strings I have:
> {
> "UniqueConstraintException.login_idx" = "Please choose a different
> login (It must be unique).";
>
> "Quote.quoteAmount"="You must enter a dollar amount in the format
> 123.00 (you entered @@escapedValue@@)!";
>
> }
> To present readable error.
> Is this what you are looking for?
>
> ERXMigrationTable personTable = database.newTableNamed("Person");
> personTable.newFlagBooleanColumn("active", NOT_NULL);
> personTable.newLargeStringColumn("addressline1", ALLOWS_NULL);
> personTable.newLargeStringColumn("addressline2", ALLOWS_NULL);
> personTable.newLargeStringColumn("city", ALLOWS_NULL);
> personTable.newDateColumn("creationdate", NOT_NULL);
> personTable.newIntegerColumn("financialID", NOT_NULL);
> personTable.newLargeStringColumn("firstname", NOT_NULL);
> personTable.newIntegerColumn("id", NOT_NULL);
> personTable.newLargeStringColumn("lastname", NOT_NULL);
> personTable.newLargeStringColumn("login", ALLOWS_NULL);
> personTable.newLargeStringColumn("password", ALLOWS_NULL);
> personTable.newIntegerColumn("securityID", NOT_NULL);
> personTable.newLargeStringColumn("state", ALLOWS_NULL);
> personTable.newLargeStringColumn("zipcode", ALLOWS_NULL);
> personTable.create();
> personTable.setPrimaryKey("id");
> personTable.addIndex(new ERXMigrationIndex(
> "login_idx", true
> ,new ColumnIndex("login")
> ));
>
>
> From: "Ted Petrosky (WO)" <webobjects-dev@lists.apple.com>
> Reply-To: Jesse Tayler <jtay...@oeinc.com>
> Date: Wednesday, November 24, 2021 at 9:41 AM
> To: Samuel Pelletier <sam...@samkar.com>
> Cc: "Ted Petrosky (WO)" <webobjects-dev@lists.apple.com>
> Subject: Re: Single thread creation queue?
>
> 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> 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> 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> a écrit :
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>> On Nov 23, 2021, at 5:17 PM, Paul Hoadley <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)
>>>>> Help/Unsubscribe/Update your Subscription:
>>>>> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com
>>>>>
>>>>> This email sent to 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
