Re: Handling DB Unique Constraints
Re: Handling DB Unique Constraints
- Subject: Re: Handling DB Unique Constraints
- From: Ramsey Lee Gurley <email@hidden>
- Date: Sun, 29 Nov 2009 23:47:38 -0500
On Nov 29, 2009, at 10:54 PM, Kieran Kelleher wrote:
>
> On Nov 29, 2009, at 10:12 PM, Chuck Hill wrote:
>
>>
>> On Nov 29, 2009, at 6:01 PM, Ramsey Lee Gurley wrote:
>>
>>>
>>> On Nov 24, 2009, at 5:21 PM, Chuck Hill wrote:
>>>
>>>>
>>>> On Nov 24, 2009, at 2:17 PM, Anjo Krank wrote:
>>>>
>>>>> Not really sure of the messages can be useful, tough, as they use the name of the constraint:
>>>>>
>>>>> ERROR: duplicate key value violates unique constraint "bb"
>>>>>
>>>>> and not the key (postgres). So the end user probably won't know about this. But it may still be better than a real exception.
>>>>
>>>> Run the constraint name + entity name through the "validation exception to nice message" part of Wonder.
>>>>
>>>> e.g.
>>>> LoginUser.uniqueUserName = "You have already registered under this user name."
>>>>
>>>> Where uniqueUserName is the constraint name.
>>>>
>>>>
>>>> Chuck
>>>
>>> That'd be awesome on 5.4. EOEntity.indexNamed(foo).
>>
>> Except that is not completely implemented. Maybe in 5.5.
>>
>>
>>> If only I could get an index name... Look at what MySQL returns for a table with a two column unique index:
>>
>> Yet another reason to not use MySQLToy. :-)
>
> Blah, blah, blah .....
>
>>>
>>> EvaluateExpression failed: <com.webobjects.jdbcadaptor._MySQLPlugIn$MySQLExpression: "INSERT INTO Parent(parentLastName, id, parentName) VALUES (?, ?, ?)" withBindings: 1:"bil-ly"(parentLastName), 2:9(id), 3:"blanks"(parentName)>:
>>> Next exception:SQL State:23000 -- error code: 1062 -- msg: Duplicate entry 'blanks-bil-ly' for key 2
>>>
>>> What a disaster... Even if I wanted to parse that to construct an error message, I don't get all the keys that are required by the unique constraint. :-/
>>
>> The constraint name is what you really want. Maybe you can dig down into the SQLException and extract it. Every database is different. Some are more different than others.
>
> If you execute the SQL "SHOW KEYS IN <tablename>", you get back a list of keys. "key 2" above in your message corresponds to the second item returned.
That also seems strange. 2:9(id) has a unique index, but the id isn't the cause of the exception. I assumed key 2 was 0, 1, 2 where the bindings were a 1, 2, 3.... you know, just to add spice to our dull lives. The cause of this exception in this case is 1:"bil-ly"(parentLastName) and 3:"blanks"(parentName) bindings. I can change the existing, saved object to violate the 2 key unique index by removing that hyphen and I get...
com.webobjects.eoaccess.EOGeneralAdaptorException: EvaluateExpression failed: <com.webobjects.jdbcadaptor._MySQLPlugIn$MySQLExpression: "UPDATE Parent SET parentLastName = ? WHERE (id = ? AND parentLastName = ? AND parentName = ?)" withBindings: 1:"billy"(parentLastName), 2:5(id), 3:"bil-ly"(parentLastName), 4:"blanks"(parentName)>:
Next exception:SQL State:23000 -- error code: 1062 -- msg: Duplicate entry 'blanks-billy' for key 2
Anyhow, if I can't get an EO I don't think it will matter. Without that, I don't get anything in my validation exception's localized key except "UniqueConstraintException", so that's only a partial victory.
The eo should be obtainable from the editing context on the databaseContext though, right? The entity is easy enough to get, and the pk should be in the bindings. It might be worth a try...
Anyway, If anyone would like to take a whack at it, in the MySQLSQLHelper class I've added:
@Override
public boolean handleDatabaseException(EODatabaseContext databaseContext, Throwable throwable) {
if(throwable instanceof EOGeneralAdaptorException) {
EOGeneralAdaptorException e = (EOGeneralAdaptorException)throwable;
EOAdaptorOperation failedOp = (EOAdaptorOperation)e.userInfo().objectForKey(EOAdaptorChannel.FailedAdaptorOperationKey);
if(failedOp != null && failedOp.exception() instanceof JDBCAdaptorException) {
JDBCAdaptorException ae = (JDBCAdaptorException)failedOp.exception();
// MySQL error codes: http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
switch(ae.sqlException().getErrorCode()) {
case 1062: //Violates unique constraint
handleUniqueConstraintAdaptorException(databaseContext, failedOp);
break;
default:
}
}
}
return false;
}
/**
* Throws a validation exception for a unique constraint failure.
* @param context The database context
* @param failedOp The operation that failed
* @throws NSValidation.ValidationException The exception thrown
*/
protected void handleUniqueConstraintAdaptorException(EODatabaseContext context, EOAdaptorOperation failedOp)
throws NSValidation.ValidationException {
NSValidation.ValidationException ve = ERXValidationFactory.defaultFactory().createCustomException(null, "UniqueConstraintException");
throw ve;
}
>>> Anyway, I do have a question related to this: Is it possible to get a handle to the eo in there? I can't find it in the db context or the throwable. Right now, the best I can do is throw a
>>>
>>> ERXValidationFactory.defaultFactory().createCustomException(null, "UniqueConstraintException");
>>>
>>> and return a message like
>>>
>>> "Could not save your changes: The value was not unique."
>>>
>>> I suppose that's better than crashing, but it's not exactly helpful either. Perhaps I could reconstruct the object in a disposable ec from the failed AdaptorOperation and pass that 'clone' object to the validation factory? Or is that asking for trouble? If I could just get "Parent.parentName.UniqueConstraintException" I could at least make a reasonable assumption about the unique constraint in some/most cases.
>>
>>
>> You can get the exact EO if the constraint is not deferred. If it is deferred, you are out of luck. You can match up the table name to the entity name (give or take some inheritance quirks).
>>
>>
>> Chuck
>>
>> --
>> Chuck Hill Senior Consultant / VP Development
>>
>> Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems.
>> http://www.global-village.net/products/practical_webobjects
>>
>>
>>
>>
>>
>>
>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list (email@hidden)
>> Help/Unsubscribe/Update your Subscription:
>>
>> This email sent to email@hidden
>
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden