Re: DB uniqueness constraints and dealing with them
Re: DB uniqueness constraints and dealing with them
- Subject: Re: DB uniqueness constraints and dealing with them
- From: Florijan Stamenkovic <email@hidden>
- Date: Tue, 08 Jul 2008 11:14:14 -0400
A slightly modified version of Chuck's db specific stuff, for OpenBase:
public class OpenBaseExInterpreter implements ExInterpreter{
private Pattern
column = Pattern.compile("column \\'(.*)\\' is not unique"),
table = Pattern.compile("SQL\\: INSERT INTO (\\w*)");
public String columnNameForUniquenessRestraintFailure(
EOGeneralAdaptorException ex){
return column.matcher(ex.getMessage()).group(1);
}
public boolean isUniquenessRestraintFailure
(EOGeneralAdaptorException ex){
return column.matcher(ex.getMessage()).find();
}
public String tableNameForUniquenessRestraintFailure
(EOGeneralAdaptorException ex){
return table.matcher(ex.getMessage()).group(1);
}
}
This will extract the relevant info from OpenBase style uniqueness
failure feedback, such as:
... Next exception:SQL State:42000 -- error code: 0 -- msg: ERROR -
Value for column 'NAME' is not unique.
SQL: INSERT INTO USER(UID, PERMISSIONS, PASSWORD, USER_ID,
CONTACT_ID, NAME) VALUES (230, '7faaa000', 'sfasfdasdfasd', 39, 2,
'flor385')
You can't get a restraint name because OpenBase does not use
restraints to define uniqueness, but an index flag (per field).
F
On Jul 05, 2008, at 00:03, Chuck Hill wrote:
I guess I will play show and tell too :-)
It all starts in an EOEditingContext subclass, with the saveChanges
method:
public void saveChanges()
{
try
{
super.saveChanges();
}
catch (EOGeneralAdaptorException e)
{
throw interpretEOGeneralAdaptorException(e);
}
}
The exception is interpreted with
/**
* Examines the exception and dispatches it to one of the more
specific interpret...Exception methods.
*
* @param theException the exception as raised from saveChanges()
* @return an exception with a localized error message and a
customized userInfo dictionary
*/
public RuntimeException interpretEOGeneralAdaptorException
(EOGeneralAdaptorException theException)
{
/** require [valid_param] theException != null; **/
// In case we can not interpret this exception, ensure that
it is returned as is.
RuntimeException interpretedException = theException;
if (NSExceptionAdditions.isOptimisticLockingFailure
(theException))
{
interpretedException = interpretOptimisticLockingFailure
(theException);
}
else if (NSExceptionAdditions.isIntegrityConstraintViolation
(theException))
{
interpretedException =
interpretIntegrityConstraintViolation(theException);
}
else if
(NSExceptionAdditions.isAdaptorOperationFailureException
(theException))
{
int failedOperator =
NSExceptionAdditions.failedAdaptorOperator(theException);
switch (failedOperator)
{
// case EODatabaseOperation.EOAdaptorLockOperator :
interpretedException = interpretLockingFailureFromException
(theException); break;
// This should never happen with integrity
constraint violations handled above and barring model exception
case EODatabaseOperation.AdaptorInsertOperator :
interpretedException =
interpretInsertFailureFromException(theException);
break;
// case
EODatabaseOperation.EOAdaptorUpdateOperator : interpretedException
= interpretUpdateFailureFromException(theException); break;
// case
EODatabaseOperation.EOAdaptorDeleteOperator : interpretedException
= interpretDeleteFailureFromException(theException); break;
// case
EODatabaseOperation.EOAdaptorStoredProcedureOperator :
interpretedException = interpretStoredProcedureFailureFromException
(theException); break;
default:
// Later, once the other cases are implemented
we can do this:
// default :
// interpretedException =
new RuntimeException("Unknown Adaptor Operator (" + failedOperator
+")");
}
}
if (interpretedException instanceof EOFValidationException)
{
((EOFValidationException)
interpretedException).setOriginalException(theException);
}
return interpretedException;
}
The relevant part of this is:
f (NSExceptionAdditions.isIntegrityConstraintViolation(theException))
{
interpretedException =
interpretIntegrityConstraintViolation(theException);
}
The NSExceptionAdditions class has these relevant methods:
static public boolean isIntegrityConstraintViolation
(EOGeneralAdaptorException exception)
{
/** require [valid_param] exception != null; **/
return isDatabaseFailureException(exception) && sqlException
(exception).getSQLState().startsWith("23");
}
static public boolean isDatabaseFailureException
(EOGeneralAdaptorException exception)
{
/** require [valid_param] exception != null; **/
return isAdaptorOperationFailureException(exception) ||
exception instanceof JDBCAdaptorException;
}
static public boolean isAdaptorOperationFailureException
(EOGeneralAdaptorException exception)
{
/** require [valid_param] exception != null; **/
boolean isAdaptorOperationFailureException = false;
if (exception.userInfo() != null)
{
isAdaptorOperationFailureException = exception.userInfo
().objectForKey(EOAdaptorChannel.FailedAdaptorOperationKey) != null;
}
return isAdaptorOperationFailureException;
}
static public SQLException sqlException
(EOGeneralAdaptorException exception)
{
/** require [valid_param] exception != null;
[isDatabaseFailureException]
isDatabaseFailureException(exception); **/
JDBCAdaptorException jdbcException = null;
if (isAdaptorOperationFailureException(exception))
{
EOAdaptorOperation failedOperation =
(EOAdaptorOperation)exception.userInfo().objectForKey
(EOAdaptorChannel.FailedAdaptorOperationKey);
Throwable opException = failedOperation.exception();
if (opException == null || ! (opException instanceof
com.webobjects.jdbcadaptor.JDBCAdaptorException))
{
throw new NSForwardException(exception,
"EOGeneralAdaptorException failed operation has unexpected
exception: " + opException);
}
jdbcException = (JDBCAdaptorException)opException;
}
else
{
jdbcException = (JDBCAdaptorException)exception;
}
return jdbcException.sqlException();
/** ensure [valid_result] Result != null; **/
}
OK, so now we know it is an integrity constraint failure
exception. Now, to interpret it into something user meaningful:
public EOFValidationException
interpretIntegrityConstraintViolation(EOGeneralAdaptorException
theException)
{
/** require
[valid_param] theException != null;
[exception_is_optimistic_locking_failure]
NSExceptionAdditions.isIntegrityConstraintViolation(theException); **/
EOEntity entity = NSExceptionAdditions.entitySaveFailedOn
(theException, this);
EOEnterpriseObject object =
NSExceptionAdditions.isAdaptorOperationFailureException
(theException) ?
NSExceptionAdditions.objectSaveFailedOn(theException) : null;
String violatedIntegrityConstraintName =
NSExceptionAdditions.violatedIntegrityConstraintName(theException);
return new EOFValidationException(object, entity.name(),
violatedIntegrityConstraintName,
EOFValidation.IntegrityConstraintViolation,
NSExceptionAdditions.violatedIntegrityConstraintName(theException));
}
OK, now back to NSExceptionAdditions to get the name of the
constraint that failed:
public static String violatedIntegrityConstraintName
(EOGeneralAdaptorException exception)
{
/** require [valid_param] exception != null;
[isIntegrityConstraintViolation]
isIntegrityConstraintViolation(exception); **/
return exceptionInterpreterFor(databaseTypeFromException
(exception)).violatedIntegrityConstraintName(sqlException(exception));
/** ensure [valid_result] Result != null; **/
}
Here is where things have to get database specific.
/**
* Dictionary of upper-case names from JDBC URLs (e.g.
SQLSERVER from jdbc:sqlserver:...) to readable names that are used
* for other purposed, e.g. to generate class names (e.g.
SQLServerExceptionInterpreter).
*/
public static NSMutableDictionary DatabaseTypeNamesForJDBCTypes
= new NSMutableDictionary(new String[] {FrontBase, SQLServer, Oracle},
new String[] {"FRONTBASE", "SQLSERVER", "ORACLE"});
public static String databaseTypeFromException
(EOGeneralAdaptorException exception)
{
/** require [valid_param] exception != null;
[isDatabaseFailureException]
isDatabaseFailureException(exception); **/
String databaseType = null;
if (isAdaptorOperationFailureException(exception))
{
EOAdaptorOperation failedOperation =
(EOAdaptorOperation)exception.userInfo().objectForKey
(EOAdaptorChannel.FailedAdaptorOperationKey);
String dbURL = (String)failedOperation.entity().model
().connectionDictionary().objectForKey(JDBCAdaptor.URLKey);
int firstColon = dbURL.indexOf(':');
int secondColon = dbURL.indexOf(':', firstColon + 1);
String jdbcType = dbURL.substring(firstColon + 1,
secondColon);
databaseType = (String)
DatabaseTypeNamesForJDBCTypes.objectForKey(jdbcType.toUpperCase());
if (databaseType == null)
{
throw new RuntimeException
("DatabaseTypeNamesForJDBCTypes does not have a name defined for "
+ jdbcType);
}
}
/* OK, this part is nasty.
* We have a JDCBAdaptorException here. We don't know the
JDCBAdaptor, only the SQLException.
* To work around this, we could implement the
EOAdaptorContext.Delegate to track the last context to try to
* commit in this thread and record the adaptor in the
thread for use here. We would then extract the
* connectionDictionaryURL() from the adaptor.
*
* In the meantime, we will cheat by guessing from the
message.
*/
else if (exception.getMessage().startsWith("Exception
condition"))
{
databaseType = FrontBase;
}
else {
throw new RuntimeException("Can't determine database
type from message " + exception.getMessage());
}
return databaseType;
/** ensure [valid_result] Result != null; **/
}
public static DataBaseExceptionInterpreter
exceptionInterpreterFor(String dbType)
{
/** require [non_null_type] dbType != null; **/
DataBaseExceptionInterpreter exceptionInterpreter =
(DataBaseExceptionInterpreter) ExceptionInterpreters.objectForKey
(dbType);
// Lazy creation
if (exceptionInterpreter == null)
{
Class exceptionInterpreterClass =
_NSUtilities.classWithName(dbType + "ExceptionInterpreter");
if (exceptionInterpreterClass == null)
{
throw new RuntimeException("Class not found for " +
dbType + "ExceptionInterpreter");
}
try
{
exceptionInterpreter =
(DataBaseExceptionInterpreter)exceptionInterpreterClass.newInstance();
ExceptionInterpreters.setObjectForKey
(exceptionInterpreter, dbType);
}
catch (Exception e)
{
throw new ExceptionConverter(e);
}
}
return exceptionInterpreter;
/** ensure [valid_result] Result != null; **/
}
As you can see, it is still a work in progress. I will also attach
three of the DB specific exception converters.
Yes, it is an ugly mess down there.
Chuck
<FrontBaseExceptionInterpreter.java>
<OracleExceptionInterpreter.java>
<SQLServerExceptionInterpreter.java>
On Jul 3, 2008, at 10:11 AM, Florijan Stamenkovic wrote:
On Jul 03, 2008, at 12:58, Florijan Stamenkovic wrote:
Hi all,
While reading older discussions on dealing with DB uniqueness
restraints I've found out that the EOGeneralAdaptorException
thrown differs among databases. Is there some generic code that
deals with this in absolute terms ( don't you just *love* the
word absolute being used in conjunction with software :) ? If
there is in WOnder, could someone please be so kind to point me
in the right direction (which part of WOnder) so I can look at it?
Ah, looking over this I realize I do not say what I want the
generic code to do... All I want is find out for which key the
uniqueness constraint failed, so I can throw a validation exception.
Or should I write some pure EOF code like: Fetch -> Check
uniqueness -> Create new record -> Save -> Fetch -> Check, or
something along those lines? This would assume that I know which
attributes should be unique in code, which can be done. I'd
rather not deal with this like this, it seems a nuisance.
F
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
40global-village.net
This email sent to email@hidden
--
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