Description Brian Vosburgh 2008-07-29 01:14:44 EDT

Currently, Dali ignores the case of the names of database objects. This makes
it impossible for Dali to differentiate among database objects whose names
differ only by their case (e.g. "EMPLOYEE" vs. "employee" vs. "Employee"). As a
result, in situations where the case is variable, validation messages can be
incorrect and drop-down selection lists can be incorrectly populated.

Dali could perform look-ups with strict case-sensitivity; but this would be
inconsistent with the way typical databases parse SQL identifiers (which is
case-insensitive); thus the decision to implement the current behavior.

There are 3 areas where Dali is sensitive to the case of database identifiers:

1. Validation
Dali performs a look-up using the database object name specified in a mapping,
e.g. @Table(name="EMPLOYEE"). Currently, it is likely Dali will generate
incorrect error messages if there are multiple database objects whose names
differ only in case.

2. Selection
When the user selects a particular database object from a drop-down list (in the
Dali Details View or using code-assist), Dali must insert the appropriately-
cased identifier in the Java or XML file. Currently, Dali will insert the
identifier with its case intact; but then validation, as described above, will
ignore the case.

3. Entity Generation (from database tables)
To take advantage of the defaults specified by JPA, Dali only generates
annotations when the defaults do not match the originating database objects.
Currently, Dali ignores the identifier case when determining this match. This
can result in incorrectly-annotated mappings when case is significant.

Candidate Solution

We would like to change Dali to allow the user to indicate, via the identifier
itself, whether case is significant. (Unfortunately, this is complicated by the
diverse set of behaviors among the various database vendors.) If the identifier
is "delimited", in a database-appropriate fashion, Dali will perform a case-
sensitive comparison when looking up the corresponding database object. An
example of a "delimited" identifier:

    @Table(name="\"Employee\"")

The SQL standard method for "delimiting" an identifier is to surround it with
double-quotes (""). While all database vendors allow the use of double-quotes,
some allow alternative techniques: Microsoft and Sybase allow brackets ([]);
and MySQL allows backticks (``). For example, the following would be allowed
when mapping to a Sybase database:

    @Table(name="[Employee]")

If the identifier is not "delimited" (i.e. it is "normal"), Dali will "fold" the
identifier to the database-specific case. For example, when mapping to an Oracle
database, the following table identifier would be folded to uppercase
(i.e. "EMPLOYEE"):

    @Table(name="Employee")

Unfortunately, again, the various database vendors fold "normal" identifiers
differently:

- Oracle, DB2, Derby, HSQLDB, and MaxDB fold to uppercase
  (which is consistent with the SQL standard)
- PostgreSQL and Informix fold to lowercase
- Sybase, Microsoft, and MySQL do not fold at all [in most situations]

This approach of using delimiters to indicate case-sensitivity is reasonably
consistent with the SQL standard. It is also consistent with the JPA Reference
Implementation, EclipseLink.

Along with changing the user-visible behavior of Dali, we would like to make
this behavior pluggable, so it can be modified by extenders. For example, SAP
(as stated in bug 193668) would like to extend the Dali JPA Platform so that
*all* identifiers are case-significant and default identifiers are folded to
uppercase.

The behavior for the following look-ups will be modified with this new behavior:

	Database.getCatalogNamed(String)
	Database.getSchemaNamed(String)
	Catalog.getSchemaNamed(String)
	Schema.getTableNamed(String)
	Schema.getSequenceNamed(String)
	Table.getColumnNamed(String)

For pluggability, all these calls will funnel a single, general purpose method
that can be overridden by extenders:

    DatabaseFinder.getDatabaseObjectNamed(
        DatabaseObject[] databaseObjects,
        String name,
        DefaultCallback defaultCallback
    )

The behavior of Entity Generation will also be changed to better handle the
variety of database identifiers to be encountered.

To allow the generated entities to be consistent with the look-up provided by
extenders (as described above), the interface
EntityGeneratorDatabaseAnnotationNameBuilder will define how an extender can
control the generated annotations:

    buildTableAnnotationName(String entityName, Table table)
    buildColumnAnnotationName(String attributeName, Column column)
    buildColumnAnnotationName(String attributeName, ForeignKey foreignKey)
    buildColumnAnnotationName(Column column)
    buildJoinTableAnnotationName(Table table)