I am using Hibernate 5.2.9.Final (version of both the core & Envers). When added auditing to a table, created with:
CREATE TABLE Transaction (
id BIGINT PRIMARY KEY
,name VARCHAR(120) NOT NULL
,currency CHAR(3) REFERENCES Currency(code)
,country VARCHAR(3)
,status INTEGER
,description VARCHAR(1000)
,amount NUMERIC(30,5)
,closingDate TIMESTAMP WITH TIME ZONE
,rangeStart TIMESTAMP WITH TIME ZONE
,rangeEnd TIMESTAMP WITH TIME ZONE
,isin VARCHAR(12)
,version INTEGER NOT NULL DEFAULT 0
);
Hibernate Envers created the _AUD table, it spit out the following:
CREATE TABLE transaction_aud (
id bigint NOT NULL,
rev integer NOT NULL,
revtype smallint,
closingdate timestamp without time zone,
closingdate_mod boolean,
country character varying(255),
country_mod boolean,
currency character varying(255),
currency_mod boolean,
description character varying(255),
description_mod boolean,
amount numeric(19,2),
isin character varying(255),
isin_mod boolean,
name character varying(255),
name_mod boolean,
rangeend timestamp without time zone,
rangeend_mod boolean,
rangestart timestamp without time zone,
rangestart_mod boolean,
status integer,
status_mod boolean
);
The entity mapping file is:
<entity name="Transaction" class="com.project.datamodel.TransactionTO" access="FIELD">
<attributes>
<id name="id" access="PROPERTY">
<generated-value strategy="SEQUENCE" generator="TransactionIdSeq" />
<sequence-generator name="TransactionIdSeq" sequence-name="TransactionIdSeq" allocation-size="1" />
</id>
<basic name="name" optional="false"> <column length="120" /> </basic>
<basic name="currency"> <column columnDefinition="CHAR(3)" /> </basic>
<basic name="country"> <column length="3" /> </basic>
<basic name="status" />
<basic name="description"> <column length="1000" /> </basic>
<basic name="amount"> <column columnDefinition="NUMERIC(30,5)" /> </basic>
<basic name="closingDate"> <column columnDefinition="TIMESTAMP WITH TIME ZONE" /> </basic>
<basic name="rangeStart"> <column columnDefinition="TIMESTAMP WITH TIME ZONE" /> </basic>
<basic name="rangeEnd"> <column columnDefinition="TIMESTAMP WITH TIME ZONE" /> </basic>
<basic name="isin"> <column length="12" /> </basic>
<transient name="favorite" />
<version name="version" />
</attributes>
</entity>
NOTE: I have removed all the joined many-to-one relations from the entity as they just add bulk without contributing to my main question.
The problems are:
VARCHAR/CHAR fields type & length ignoredALL CHAR and VARCHAR fields, regardless of their length were converted to VARCHAR(255)/character varying(255) fields.
This caused a problem when saving data in the description field that should normally hold 1000 characters. After the 255 limit size was exceeded, the database engine through an exception because the data was too wide for the column size.
Also, what sense does it make to cast a CHAR(3) to a VARCHAR(255)? It seems awfully wasteful to me, but I'm hoping that there is some other reason I am not aware of.
TIMESTAMP time zone attribute ignoredThe TIMESTAMP-typed fields changed from being explicitly defined WITH a time zone defined to not having the timezone defined (I'm aware that the TIMESTAMP type in PostgreSQL -which is my backing database engine- defined them without the time zone by default).
The amount field defined as a NUMERIC (30,5) was defined in the audit table as NUMERIC(19,2).
Why does Hibernate Envers not respect the column types of the original table when generating the _AUD table?
I have also found that it respects the size in the case of one table: LocalizedMessage
<entity name="LocalizedMessage" class="com.project.datamodel.to.LocalizedMessageTO" access="FIELD">
<attributes>
<id name="id" access="PROPERTY">
<generated-value strategy="SEQUENCE" generator="LocalizedMessageIdSeq" />
<sequence-generator name="LocalizedMessageIdSeq" sequence-name="LocalizedMessageIdSeq" allocation-size="1" />
</id>
<many-to-one name="adminMessage"> <join-column name="adminMessageId" /> </many-to-one>
<basic name="localeCode"> <column length="7" /> </basic>
<basic name="message"> <column length="500" /> </basic>
<version name="version" />
</attributes>
</entity>
The original table definition is:
CREATE TABLE LocalizedMessage (
id BIGINT PRIMARY KEY
,adminMessageId BIGINT REFERENCES AdminMessage(id)
,localeCode VARCHAR(7)
,message VARCHAR(500)
,version INTEGER NOT NULL DEFAULT 0
);
And the generated _AUD table was:
CREATE TABLE localizedmessage_aud (
id bigint NOT NULL,
rev integer NOT NULL,
revtype smallint,
localecode character varying(7),
localecode_mod boolean,
message character varying(500),
message_mod boolean,
adminmessageid bigint,
adminmessage_mod boolean
);
NOTE: This was done AFTER adding the length directives to the mapping file. The only problematic fields now are the TIMESTAMP WITH TIME ZONE and the NUMERIC fields (where the precision is changed), along with the CHAR() fields.
In essence, the columnDefinition attribute of the JPA XML mapping file is not respected.
Enabling trace logging for org.hibernate.envers.boot.internal.AdditionalJaxbMappingProducerImpl produced log entries like the following:
2017-06-10 15:52:19,981 TRACE org.hibernate.envers.boot.internal.AdditionalJaxbMappingProducerImpl ~ ------------------------------------------------------------
2017-06-10 15:52:19,984 TRACE org.hibernate.envers.boot.internal.AdditionalJaxbMappingProducerImpl ~ Envers-generate entity mapping -----------------------------
<hibernate-mapping auto-import="false">
<class entity-name="com.project.datamodel.to.TransactionTO_AUD" discriminator-value="Transaction" table="Transaction_AUD" schema="app" abstract="false">
<composite-id name="originalId">
<key-property name="id" type="long">
<column name="id" length="255" scale="2" precision="19"/>
</key-property>
<key-many-to-one type="integer" class="com.project.datamodel.to.CustomRevisionEntity" name="REV">
<column name="REV"/>
</key-many-to-one>
</composite-id>
<property insert="true" update="false" name="REVTYPE" type="org.hibernate.envers.internal.entities.RevisionTypeType"/>
<property insert="true" update="false" name="closingDate" type="timestamp">
<column name="closingDate" length="255" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="closingDate_MOD" type="boolean"/>
<property insert="true" update="false" name="country" type="string">
<column name="country" length="3" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="country_MOD" type="boolean"/>
<property insert="true" update="false" name="currency" type="string">
<column name="currency" length="255" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="currency_MOD" type="boolean"/>
<property insert="true" update="false" name="description" type="string">
<column name="description" length="1000" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="description_MOD" type="boolean"/>
<property insert="true" update="false" name="amount" type="big_decimal">
<column name="amount" length="255" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="amount_MOD" type="boolean"/>
<property insert="true" update="false" name="isin" type="string">
<column name="isin" length="12" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="isin_MOD" type="boolean"/>
<property insert="true" update="false" name="name" type="string">
<column name="name" length="120" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="name_MOD" type="boolean"/>
<property insert="true" update="false" name="rangeEnd" type="timestamp">
<column name="rangeEnd" length="255" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="rangeEnd_MOD" type="boolean"/>
<property insert="true" update="false" name="rangeStart" type="timestamp">
<column name="rangeStart" length="255" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="rangeStart_MOD" type="boolean"/>
<property insert="true" update="false" name="status" type="converted::com.project.datamodel.converter.StatusConverter">
<column name="status" length="255" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="status_MOD" type="boolean"/>
</class>
</hibernate-mapping>
And for the LocalizedMessage table:
2017-06-10 15:52:19,947 TRACE org.hibernate.envers.boot.internal.AdditionalJaxbMappingProducerImpl ~ ------------------------------------------------------------
2017-06-10 15:52:19,949 TRACE org.hibernate.envers.boot.internal.AdditionalJaxbMappingProducerImpl ~ Envers-generate entity mapping -----------------------------
<?xml version="1.0" encoding="UTF-8"?>
<hibernate-mapping auto-import="false">
<class entity-name="com.project.datamodel.to.LocalizedMessageTO_AUD" discriminator-value="LocalizedMessage" table="LocalizedMessage_AUD" schema="app" abstract="false">
<composite-id name="originalId">
<key-property name="id" type="long">
<column name="id" length="255" scale="2" precision="19"/>
</key-property>
<key-many-to-one type="integer" class="com.project.datamodel.to.CustomRevisionEntity" name="REV">
<column name="REV"/>
</key-many-to-one>
</composite-id>
<property insert="true" update="false" name="REVTYPE" type="org.hibernate.envers.internal.entities.RevisionTypeType"/>
<property insert="true" update="false" name="localeCode" type="string">
<column name="localeCode" length="7" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="localeCode_MOD" type="boolean"/>
<property insert="true" update="false" name="message" type="string">
<column name="message" length="500" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="message_MOD" type="boolean"/>
<property insert="true" update="false" name="adminMessage_id" type="long">
<column name="adminMessageId" length="255" scale="2" precision="19"/>
</property>
<property insert="true" update="false" name="adminMessage_MOD" type="boolean"/>
</class>
</hibernate-mapping>
Why does it apply correctly in one case and not in another?
As indicated in my comment, Envers does not interpret its schema from the existing database schema in any way. In fact, its entire mapping model production process is based on inspecting Hibernate ORM's boot-time mapping model, which you can find in org.hibernate.mapping.
This means that for things such as column lengths, they should be supplied as part of your mapping model via either annotations or in your XML mapping file in order for Envers to generate properties that use those same lengths or in some cases custom column definitions.
If you specify the lengths in your XML mapping file and regenerate the Envers schema, it should align itself very closely with your Hibernate entity schema.
UPDATE
The reason you don't see the column definitions being rendered in the Envers mappings is because your JPA ORM.XML file is invalid, it isn't following the schema definition and thus not only would Envers not be seeing your column definitions, neither would Hibernate.
Your mappings should be:
<column column-definition="TIMESTAMP WITH TIME ZONE"/>
You'll notice the attribute is named column-definition and not columnDefinition.