Identifier Case Sensitivity in PostgreSQL

[Peter Hancox]

Having an issue with identifier case sensitivity in PostgreSQL.  Most databases fold unquoted identifiers to uppercase 

whereas PostgreSQL folds to lowercase.  As my code needs to be portable across multiple databases I specify the

identifier names in uppercase.

Running unit tests against H2 is working.  The schema is created with uppercase identifiers and the SQL

generated by MapperDao is either quoted uppercase, unquoted uppercase, or (in the case of table names) 

unquoted camelcase.  All of which are either uppercase or folded to uppercase by the database.

PostgreSQL is currently failing on insert.  The SQL generated by MapperDao is

insert into Suburb(NAME,POSTCODE,STATE,COUNTRY) values('Longueville','2066','NSW','Australia') RETURNING "ID"

which is being aborted because column "ID" doesn't exist.  The "id" column exists but "ID" doesn't.  Because "ID" 

is quoted in the SQL it is failing.  If it was unquoted as for all the other identifiers in the generated SQL then it

should work.

Suggestions as to how best to fix or work around this?

[samzil...@gmail.com]

I have a mysql solution that might be relevant to you.

Define your db to be utf8_general_ci (case insensitive) collation.

Then it doesn't matter if you call the column ID, Id, iD or id.

I never encountered a usecase that needs the db col names to be case sensitive...

[Peter Hancox]

Thanks for the suggestion.  PostgreSQL has a number of options that can be set but I'd like to leave

pursuing that approach as a last recourse.  As an aside, I thought that setting collation options would 

most likely affect column data rather than the schema identifiers.  Though at the end of the day, the 

schema identifiers are typically data in the columns of a system table anyway.

What I'm hoping for as a solution is that the RETURNING "ID" clause can be unquoted as per the rest

of the query; allowing the databases default case folding to apply.

[Kostas Kougios]

Hi, the "returning id" part of the sql is not controlled by mapperdao. Mapperdao does a batch insert informing the driver that it requires the autogenerated keys. Probably postgres adds that bit.

How do you map the key? Key("ID")? Can you share your mapping?

Cheers

Sent from my self, sorry for any typos

--
You received this message because you are subscribed to the Google Groups "mapperdao" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapperdao+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Tim Pigden]

Peter - are you using upper case in the DDL when you create the tables?

I had to use all lower case in postgres after which I think I didn't have to worry about the case elsewhere.

--
Tim Pigden
Optrak Distribution Software Limited
+44 (0)1992 517100
http://www.linkedin.com/in/timpigden
http://optrak.com
Optrak Distribution Software Ltd is a limited company registered in England and Wales.
Company Registration No. 2327613 Registered Offices: Orland House, Mead Lane, Hertford, SG13 7AT England 
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Optrak Distribution Software Ltd. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error.

[Peter Hancox]

object SuburbEntity extends Entity[Int, SurrogateIntId, Suburb] {

  val id = key("ID") autogenerated (_.id)

  val name = column("NAME") to (_.name)

  val postcode = column("POSTCODE") to (_.postcode)

  val state = column("STATE") to (_.state)

  val country = column("COUNTRY") to (_.country)

  def constructor(implicit m: ValuesMap) =

    new Suburb(name, postcode, state, country) with SurrogateIntId {

      val id: Int = SuburbEntity.id

    }

}

Can you point me at the class that interfaces to the driver?

Seems odd that some identifiers are quoted and others aren't?

REGARDS

Peter

[Peter Hancox]

I'm using Slick to generate the DDL.  As input to Slick I use uppercase names for compatibility with 

most databases.  Then I override the Slick PostgreSQL driver to force identifiers to lowercase.

So in PostgreSQL all my identifiers are lowercase.

The problem is that the insert query generated from MapperDao is putting the return id in quotes as "ID".

When PostgreSQL sees the quotes it doesn't fold the identifier name to lowercase.  So in the example

insert: 

Suburb -> suburb

NAME -> name

"ID" -> ID (thus my problem since PostgreSQL has it as "id" not "ID"

[Konstantinos Kougios]

When working with multiple databases (i.e. the test cases of mapperdao) the approach I used is to assume case insensitive and create all tables and all mappings with a consistent naming, i.e. create table Product(id int, name varchar) and then use key("id"). I recommend this highly.

MapperDao does:

connection.prepareStatement(sql, autogenerated=true)

and then

insert into Suburb(NAME,POSTCODE,STATE,COUNTRY) values(?,?,?,?)

I believe the "RETURNING "ID"" part is only part of the postgresql exception msg and not part of mapperdao debug logging.

As a workaround you can do val id=key(database match {
    case "postgresql"=>"id"
    case _=>"ID"
})

Cheers

[Peter Hancox]

The case I'm using is consistent for each database.  I use the native folding so that when working

with the database directly using other tools I won't have to be quoting everything all the time.

The problem I have is that quoting "ID" on the insert breaks this one particular case.  Your suggested 

work-around would solve the problem but I would have to implement it in 99% of my entity classes and

though not technically a problem would look a bit strange having just one quoted field in different case

to all other identifiers.

Curious that you say MapperDao just does 

connection.prepareStatement(sql, autogenerated=true)

and then

insert into Suburb(NAME,POSTCODE,STATE,COUNTRY) values(?,?,?,?)

I doubt PostgreSQL is adding the RETURNING "ID" as PostgreSQL knows nothing 

about an "ID" column; it knows about a "id" column which is the primary key.  So if

PostgreSQL was adding that clause then it would have added RETURNING "id"

I'd be interested to know the content of the "sql" variable that is passed to prepareStatement ?

[Peter Hancox]

The "sql" variable contains the insert statement as you said.  However, MapperDao also passes "ID" 

as the name of the column to return.  This is correct based on what I've specified.  However, the following

code in the PostgreSQL driver is what is causing my grief:

        sb.append(" RETURNING ");

        for (int i=0; i<columns.length; i++) {

            if (i != 0)

                sb.append(", ");

            // If given user provided column names, quote and escape them.

            // This isn't likely to be popular as it enforces case sensitivity,

            // but it does match up with our handling of things like

            // DatabaseMetaData.getColumns and is necessary for the same

            // reasons.

So unless I can find a way to override the PostgreSQL driver code in some way then I'll have to

do something similar to the work around you proposed.  Unlikely overriding the PostgreSQL code

will be workable, particularly if I want to use a pooled datasource or something similar in production.

If other database drivers don't quote the column identifiers specified for returning auto-generated

columns then I might be able to get away with using lowercase for the id for all databases.

[Konstantinos Kougios]

Hi Peter,

    yes, mapperdao passes the ID column to the jdbc driver, informing the driver that it is autogenerated. But mapperdao as you found out doesn't create the "RETURNING "ID"". It's the jdbc driver who adds that bit along with the double quotes.

What tools require this case-insensitive naming?

I know mysql is case sensitive, postgres+sql server + oracle isn't but maybe you get into more trouble further down the line.

Cheers

[Peter Hancox]

Not so much specific tools that require a case-insensitive mapping; mostly just when I'm 

working with a command line interface.

e.g., I don't want to be typing select "COL_A", "COL_B" from "TABLE_A" with quotes all 

over the place when I'm writing ad-hoc queries in PostgreSQL.

Using lowercase when specifying the autogenerated ID column works for H2 and PostgreSQL.

PostgreSQL driver is now being passed id which it quotes as "id" matching the name in its

schema.  H2 is being passed id which presumably is being used unquoted by the H2 driver

and thus folded to uppercase which matches the name in its schema.

I don't currently use MySQL but if its case sensitive then I'd be back with a similar problem

that I have for PostgreSQL!  In which case the only uniformly workable solution would be to

pass identifiers of different case to MapperDao (and any other library) based on the taret

database.

Perhaps a class along the lines of the Scala s"somestring" string implementation; I'll have to 

look at how this is done.  Good chance to improve my knowledge of Scala internals.

Or alternately, is there somewhere I can hook in to / override where MapperDao passes the

ID column to the driver so that I can force the column names specified by the application

code to lowercase if the database is PostgreSQL?  This would allow me to put that logic in

one place rather than in each entity class definition.  Would be a more MapperDao only

solution but completely hidden in the framework rather than relying on application code

to use the "special string" class for identifier names.

[Konstantinos Kougios]

Hi Peter, I am curious how this works for all databases including mysql. I might then be able to provide a generic solution. What's your status, what databases did you integrate so far?

In the meantime I suppose you impl a solution to lowercase the id for postgres, right?

[Peter Hancox]

It wouldn't work for any database that folded to uppercase and where the driver quotes the 

column name for auto generated columns.  I believe MySQL is case-sensitive with no

folding of identifier names (???) in which case my implementation would work but the

auto-generated column names would be lowercase and other columns would be uppercase.

Bit messy and personally I wouldn't go with it.

The more generic approach I'm playing with is something along the lines of:

  implicit class DbIdHelper(val s: String) extends AnyVal {

    def asDbId(implicit dbms: String) = {

      dbms match {

        case "postgresql" => s.toLowerCase()

        case _ => s.toUpperCase()

      }

    }

Then in the application code:

object SuburbEntity extends Entity[Int, SurrogateIntId, Suburb] {

  implicit val dbms = "postgresql"

  val id = key("ID".asDbId) autogenerated (_.id)

  val name = column("NAME".asDbId) to (_.name)

Still messing with it but looking for something like implicit classes and values to

hide a lot of the boiler plate.  The basic idea being to have a function that when applied

to a string, alters the case based on the target database.  Same approach may well

be useful in other situations.

Wouldn't use implicit string variable in the final solution due to likely collision with

another string implicit.  Probably some enum / case class instead.  I'm still very

much a Scala newby unfortunately ;-(

REGARDS

Peter

[Peter Hancox]

Can you tell me how I can find the dbms (e.g., h2, postgresql) from within the SuburbEntity?  Currently 

hardcoded as "postgresql" in the example.

In an earlier post I saw

object ProductEntity extends Entity[Int, SurrogateIntId, Product]
    {
        override val databaseSchema = Some(Schema("test"))

        val id = key("id") sequence (Setup.database match {
            case "oracle" => Some("ProductSeq")
            case _ => None
        }) autogenerated (_.id)
     
but "Setup.database" doesn't appear to resolve anymore?

[Konstantinos Kougios]

Entity isn't aware of the database (i.e. same entity can be used in 2 different databases) . Overall if you notice, mapperdao doesn't fix down singletons. You can have as many instances of MapperDao/QueryDao as you require and connect to different databases and configure some entities for database A and some others for B

Pretty much it comes down to your application to keep track of the configuration. If you connect to one database at a time you could i.e. have a similar to the "Setup" class I use for test cases:

object Config {
    val database = System.getProperty("database")
}

Looking also at your DbIdHelper, you might just want to subclass Entity and add the functionality you want reg. the id's and the database.

Cheers

[Peter Hancox]

Rather than subclass Entity, my current approach is to parameterise the SuburbEntity class

class SuburbEntity(implicit dbms: String) extends Entity[Int, SurrogateIntId, Suburb] with Logging {

  val id = key("ID".asDbId) autogenerated (_.id)

  val name = column("NAME".asDbId) to (_.name)

I'm guessing (limited Scala knowledge) that if I need to add more generic framework code that

I'd put it into a trait that I could reuse across all my entities.  Will see as the complexity of the

proof of concept grows.

Although current applications only connect to a single database and restart if the database changes,

I won't be limiting the new framework and will want to support connection to multiple databases of

different types at the same time.

Just about to start playing with one2many relations so no doubt will have some questions

on that.

REGARDS

Peter

[anvesh...@gmail.com]

Nice Article !

Really this will help to people of PostgreSQL Community. 

I have also prepared small note on this, Why identifiers are case sensitive in PostgreSQL. 

  

http://www.dbrnd.com/2015/10/postgresql-why-identifiers-are-case-sensitive/