Grokbase Groups Cayenne dev June 2015
FAQ
I've run into problems a few times caused by case-mismatch issues with
columns (DbAttributes).

The primary one being when I have SQLTemplate queries that return actual
DataObjects. If the DbAttribute is declared with mixed case (myTablePK)
then my query just returns a list of nulls because it can't find the pk
field to create an ObjectId. I'm using postgres mostly which is not
case-sensitive and returns the columns in lowercase it seems.

What do you think about changing some of this behavior?

Like forcing or providing a switch to ensure DbAttribute names are
lowercase?

Or doing more intelligent matching that ignores case as much as possible?
I.E. if there are no duplicate columns when ignoring case, then ignore case?

John

Search Discussions

  • Andrus Adamchik at Jun 15, 2015 at 3:16 pm
    Hi John,

    IIRC you can force PostgreSQL (or any other DB really) to treat column names as case-sensitive by including them in double quotes when creating and querying the tables. In Cayenne this means setting "Quote SQL Identifiers" to "on" in the DataMap and using explicit double quotes in SQLTemplate.

    Not going to help with "SELECT * FROM", but should address all other cases.
    Like forcing or providing a switch to ensure DbAttribute names are
    lowercase?
    If the above doesn't help, you can probably decorate DataMapLoader service to conditionally convert loaded DataMap to lower case DbAttributes (if you can access DataSource and determine DB type prior to project loading), or do that in the EntityResolver after ServerRuntime startup (less clean, but same end result).

    Andrus

    On Jun 13, 2015, at 12:13 AM, John Huss wrote:

    I've run into problems a few times caused by case-mismatch issues with
    columns (DbAttributes).

    The primary one being when I have SQLTemplate queries that return actual
    DataObjects. If the DbAttribute is declared with mixed case (myTablePK)
    then my query just returns a list of nulls because it can't find the pk
    field to create an ObjectId. I'm using postgres mostly which is not
    case-sensitive and returns the columns in lowercase it seems.

    What do you think about changing some of this behavior?

    Like forcing or providing a switch to ensure DbAttribute names are
    lowercase?

    Or doing more intelligent matching that ignores case as much as possible?
    I.E. if there are no duplicate columns when ignoring case, then ignore case?

    John
  • John Huss at Jun 15, 2015 at 5:19 pm

    On Mon, Jun 15, 2015 at 10:16 AM Andrus Adamchik wrote:

    Hi John,

    IIRC you can force PostgreSQL (or any other DB really) to treat column
    names as case-sensitive by including them in double quotes when creating
    and querying the tables. In Cayenne this means setting "Quote SQL
    Identifiers" to "on" in the DataMap and using explicit double quotes in
    SQLTemplate.

    Not going to help with "SELECT * FROM", but should address all other cases.
    I definitely don't want my DB to be case sensitive, or to make my all my
    SQL statements much longer by adding quotes everywhere.

    Like forcing or providing a switch to ensure DbAttribute names are
    lowercase?
    If the above doesn't help, you can probably decorate DataMapLoader service
    to conditionally convert loaded DataMap to lower case DbAttributes (if you
    can access DataSource and determine DB type prior to project loading), or
    do that in the EntityResolver after ServerRuntime startup (less clean, but
    same end result).
    I've addressed it in my project by doing this. If that's going to be the
    recommended solution it should be part of the library. The code to switch
    everything to lowercase is a bit involved. I wonder if it would be
    reasonable to detect the DB and do this automatically?

    The specific problem I ran into is this: in ObjectResolver.createObjectId
    there is this:

                 Object val = dataRow.get(key);
                 // this is possible when processing left outer joint prefetches
                 if (val == null) {
                     return null;
                 }

    Since the case doesn't match for the PK null is returned. Making this
    fallback to a case-insensitive lookup would help a bit. However, all the
    columns have this problem, not just the PKs. The difference the PK columns
    are more likely to be defined in mixed case because they are generated into
    java source as the PK_COLUMN constant and mixed case is necessary for a
    good name to be generated.

    Andrus

    On Jun 13, 2015, at 12:13 AM, John Huss wrote:

    I've run into problems a few times caused by case-mismatch issues with
    columns (DbAttributes).

    The primary one being when I have SQLTemplate queries that return actual
    DataObjects. If the DbAttribute is declared with mixed case (myTablePK)
    then my query just returns a list of nulls because it can't find the pk
    field to create an ObjectId. I'm using postgres mostly which is not
    case-sensitive and returns the columns in lowercase it seems.

    What do you think about changing some of this behavior?

    Like forcing or providing a switch to ensure DbAttribute names are
    lowercase?

    Or doing more intelligent matching that ignores case as much as possible?
    I.E. if there are no duplicate columns when ignoring case, then ignore case?
    John

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdev @
categoriescayenne
postedJun 12, '15 at 9:14p
activeJun 15, '15 at 5:19p
posts3
users2
websitecayenne.apache.org

2 users in discussion

John Huss: 2 posts Andrus Adamchik: 1 post

People

Translate

site design / logo © 2021 Grokbase