Hiberante 4.3.6 DDL generation: Uppercase names for columns included in indexes

Question

I have some questions related to the case sensitivity of the names of indexes and constraints in the database schemas generated by hibernate. My intentention is to get them uppercase but I cannot achieve it.

These are the main features of the libraries/components of the system:

Hibernate 4.3.6 against a PostgreSQL 9.3 database.
I am using annotations for the entities and trying to generate the schema with the sentence:

Persistence.createEntityManagerFactory("vegaUnitDDL").createEntityManager();

Next follows a test entity I use to try different options:

package net.test.software.test001.persistence.jpa;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Index;
import javax.persistence.Table;

@Entity
@Table(name="\"V_TEST\""
   , catalog="\"VEGA_DB_SCHEMA\""
   , indexes = {@Index(name="V_TEST_IDX", columnList="`TYPE`")}
)
public class Test {

  @Column(name="\"TEST_ID\"")
  @Id
  private long id = 0;

  @Column(name="\"TYPE\"", nullable=false)
  private String type = null;

}

The persistene.xml is:



  
      
      net.test.software.test001.persistence.jpa.Test

The main class to launch the schema generation:

package net.test.software.test001.persistence.jpa.model;

import javax.persistence.Persistence;

public class SchemaGenerator {
    public SchemaGenerator() {
        System.out.println("Creating the entity manager factory.");
        Persistence.createEntityManagerFactory("vegaUnitDDL").createEntityManager();
        System.out.println("DB schema created.");
    }

    public static void main(String[] args) {
        try {
            new SchemaGenerator();
            System.out.print("+-*.:+-*.:+-*.:+-*.:+-*.:");
            System.exit(0);
        } catch(Exception ex) {
            ex.printStackTrace();
            System.err.print(">>>>>>>>>>>>>>>>>>>>>>>>>");
            System.exit(-1);
        }
    }
}

And finally the ddl of the table and the index returned by the pgAdmin tool (once the schema was created in the database):

-- Table: "VEGA_DB_SCHEMA"."V_TEST"

-- DROP TABLE "VEGA_DB_SCHEMA"."V_TEST";

CREATE TABLE "VEGA_DB_SCHEMA"."V_TEST"
(
   "TEST_ID" bigint NOT NULL,
   "TYPE" character varying(255) NOT NULL,
   CONSTRAINT "V_TEST_pkey" PRIMARY KEY ("TEST_ID")
)
WITH (
    OIDS=FALSE
);

ALTER TABLE "VEGA_DB_SCHEMA"."V_TEST"
OWNER TO "VEGA_SYS";

-- Index: "VEGA_DB_SCHEMA".v_test_type_idx

-- DROP INDEX "VEGA_DB_SCHEMA".v_test_type_idx;

CREATE INDEX v_test_type_idx
  ON "VEGA_DB_SCHEMA"."V_TEST"
USING btree
  ("TYPE" COLLATE pg_catalog."default");

QUESTIONS:

1.- How can I get V_TEST_pkey to be full uppercase? Is there any property in the persistence.xml file I can use?

2.- Must I write the columns in columnList parameter for @Index between back ticks? Should not they be between double quotes? (i.e columnList="\"TYPE\"" instead of columnList="`TYPE`")

It is supposed that the column TYPE must be double quoted in the @Column annotation if I want hibernate to create it uppercase. That is the way I declared it in the Test class. Nevertheless, regarding to the @Index annotation, it fails if I use \"TYPE\" instead of `TYPE`.

I had to make some debugs to find how hibernates manages it.

A) First, let's see the way hibernate parses any column information: In this process, it builds a map between the logical name of the column, a name used for inner references, and its physical name, the name declared in DDL when the table is created. It also makes the reverse map of the physical name to the logical name. The logical name is for internal use in the library only and depends on the hibernate naming strategy. In order to get it, hibernates processes the name indicated in the java annotation @Column through a serial operations:

It starts turning the double quotes into backticks (\"TEST_ID\" -> `TEST_ID`; \"TYPE\" -> `TYPE`)

final String columnName = nameNormalizer.normalizeIdentifierQuoting( col.name() );

In a later step, when it get the logical-physical mapping, it uses the naming strategy to see the string it has to take for the logical name:

String logicalColumnName = mappings.getNamingStrategy().logicalColumnName(this.logicalColumnName, propertyName );

You can see the later sentece in the Configure class, where it is called for the column binding:

protected void addColumnBinding(SimpleValue value) {
    String logicalColumnName = mappings.getNamingStrategy()
        .logicalColumnName( this.logicalColumnName, propertyName );
    mappings.addColumnBinding( logicalColumnName, getMappingColumn(), value.getTable());
}

In our case, just with the configuration previously shown in the persistence.xml file, the names returned for the columns TEST_ID and TYPE are the same than the values passed as argument to the logicalColumnName method, i.e. `TEST_ID`, `TYPE`.

To save the reference between physical and logical columns, hibernate put them in hash tables: See it in the method bindLogicalToPhysical of the inner class MappingsImp of the Configuration class. The key it uses for the logical name is the one we pass to it all turn into lowercase (`TEST_ID` -> `test_id`; `TYPE` -> `type`):

private void bindLogicalToPhysical(String logicalName, Column physicalColumn) throws DuplicateMappingException {
    final String logicalKey = logicalName.toLowerCase();
    final String physicalName = physicalColumn.getQuotedName();
    final String existingPhysicalName = ( String ) logicalToPhysical.put( logicalKey, physicalName );
    ...
}

B) Now let's see how hibernate manages the column indicated in @Index:

It is supposed that it must take the name of the column in columnList and get its logical name. Then it will get the physical name through the mapping previously done in the column info parsing. Just, when getting the logical name is where hibernate seems procced distinct.

It takes the name written in the @Index annotation but it does not apply the "idenfier quoting normalization", then it does not change the double quotes for back ticks. Then when searching in the map it uses the lowercase transformation of the name as key in the same way it did when creating the mapping. As consequence, we must use `TYPE` instead of \"TYPE\" in columList. Is this is a bug? In my opinion it has more sense take the same string you wrote in @Column for @Index's columnList parameter.

All these problems are over when the names are lowercase: Hibernate: Create Index

GYLZ · Accepted Answer

The official documentation of Hibernate indicates to use the backtick marks for these kinds of issues.

http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch05.html#mapping-quotedidentifiers

There is also some previous post in stackoverflow:

Hibernate: use backticks for MySQL but not for HSQL

How do I put backticks in a column name called key for hibernate

Hiberante 4.3.6 DDL generation: Uppercase names for columns included in indexes

Answers (1)

Related Questions