Hi Roger, I had tested the workaround just to eliminate the value column from the audit table primary key, but this leads to primary key constrain exception when a collection entry is modified. The reason for this is, that envers will add an entry with REVTYPE = del for the old value (but with the new REV number), when i modify a collection entry. And afterwards envers will add another entry to the audit table with REVTYPE = add.
So, instead of just removing the value column from the audit table primary key, I also added the REVTYPE to the primary key. This works for all of my use cases. For me, it looks like, the problem with the deleted entries (described above) was solved in envers by adding the value column to the PK, but adding REVTYPE would do the same and is less problematically due to database type restrictions. From my point of view, it would be an improvement for envers, to do the same. But for the moment, I still have the problem to find a place, where I am able to hook my workaround into NHibernate/Envers. I know, envers use the infrastructure of NHibernate to create the table on the database, but there must be a place where envers creates automatically a kind of schema definition for the audit table (depending of the configuration and the entity definition to audit, right?). If you have some tipps for me, where this is implemented (or better: how to hook in into this behavior), would be very nice... Thanks a lot ;-) Here is an example, what is happening on the database (I've trunkated all other rows, which are not involved): After initial create: table: HardwareSetting HardwareSetting_id | SettingValue | SettingName *********************************************************************************** 2c98afee-4c34-4d44-b515-a52c00ac6eed | 500 | SettingKey1 table: HardwareSetting_AUD REV | HardwareSetting_id | SettingValue | SettingName | REVTYPE *********************************************************************************** 7 | 2c98afee-4c34-4d44-b515-a52c00ac6eed | 500 | SettingKey1 | 0 After update the value of SettingKey1 from 500 to 600: table: HardwareSetting HardwareSetting_id | SettingValue | SettingName *********************************************************************************** 2c98afee-4c34-4d44-b515-a52c00ac6eed | 600 | SettingKey1 table: HardwareSetting_AUD REV | HardwareSetting_id | SettingValue | SettingName | REVTYPE *********************************************************************************** 7 | 2c98afee-4c34-4d44-b515-a52c00ac6eed | 500 | SettingKey1 | 0 8 | 2c98afee-4c34-4d44-b515-a52c00ac6eed | 500 | SettingKey1 | 2 8 | 2c98afee-4c34-4d44-b515-a52c00ac6eed | 600 | SettingKey1 | 0 After delete SettingKey1 from collection: table: HardwareSetting HardwareSetting_id | SettingValue | SettingName *********************************************************************************** table: HardwareSetting_AUD REV | HardwareSetting_id | SettingValue | SettingName | REVTYPE *********************************************************************************** 7 | 2c98afee-4c34-4d44-b515-a52c00ac6eed | 500 | SettingKey1 | 0 8 | 2c98afee-4c34-4d44-b515-a52c00ac6eed | 500 | SettingKey1 | 2 8 | 2c98afee-4c34-4d44-b515-a52c00ac6eed | 600 | SettingKey1 | 0 9 | 2c98afee-4c34-4d44-b515-a52c00ac6eed | 600 | SettingKey1 | 2 Am Donnerstag, 8. Oktober 2015 18:04:16 UTC+2 schrieb Roger: > > With the mapping you have, the ”SettingValue” is supposed to be part in > the audited table’s primary key. Or rather – part of the generated audited > composite-id mapping for ”HardwareSettings”. > > > > << Has someone an idea where to hook in to change the way Envers creates > the schema for the audit tables?> > > > > Envers doesn't create any schema for you. I guess you use NH Core's > schemaexport to generate the schema for you? That tool will generate schema > for your NH configuration object (which also contains mapping for envers > audited entities). > > I understand this will lead to problem if this pk contains a > “StringClobUserType” mapping. If you manually drop/change your pk after it > has been generated by “X” (=schemaexport), will things work as expected for > you then? > > > > > > > > *From:* [email protected] <javascript:> [mailto: > [email protected] <javascript:>] *On Behalf Of *Matthias Kientz > *Sent:* den 8 oktober 2015 15:56 > *To:* nhusers <[email protected] <javascript:>> > *Subject:* [nhusers] Envers: erroneous primary key for mapped collection > > > > I use NHibernate with Fluent and Envers. > I have an auditable class Hardware, which contains the property Settings, > which is mapped to a collection entity. > > Error: > The created audit table adds also the value column (SettingValue) to the > primary key, which fails in this case (SqlCeException: Long value data type > cannot be indexed.) > > Expected: > The value column SettingValue should not be part of the primary key. > > > */* definition of Setting (in class Hardware) */* > public virtual IDictionary<string, string> Settings > { > get { return _settings; } > set { _settings = value; } > } > > */* automapping override with fluent */* > mapping.HasMany(x => x.Settings) > .Not.LazyLoad() > .AsMap<string>("SettingName") > .KeyColumns.Add("HardwareSettings_id") > .Table("HardwareSettings") > .Element("SettingValue", x => x.Type<StringClobUserType>()); > > */* trace of the resulting Hardware mapping (containing map > HardwareSettings) */* > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> > <class xmlns="urn:nhibernate-mapping-2.2" > name="MyApp.Data.Model.Hardware.Hardware, MyApp.Data.Model, > Version=1.0.0.0, Culture=neutral, PublicKeyToken=866d4a0fa0599fe0" > table="`Hardware`"> > <cache usage="read-write" /> > <id name="Id" type="System.Guid, mscorlib, Version=4.0.0.0, > Culture=neutral, PublicKeyToken=b77a5c561934e089"> > <column name="Id" /> > <generator class="guid.comb" /> > </id> > <map cascade="save-update" lazy="false" name="Settings" > table="HardwareSettings"> > <cache usage="read-write" /> > <key> > <column name="HardwareSettings_id" /> > </key> > <index type="System.String, mscorlib, Version=4.0.0.0, > Culture=neutral, PublicKeyToken=b77a5c561934e089"> > <column name="SettingName" /> > </index> > <element > type="MyApp.DataAccess.NHibernate.UserTypes.StringClobUserType, > MyApp.DataAccess.NHibernate, Version=1.0.0.0, Culture=neutral, > PublicKeyToken=866d4a0fa0599fe0"> > <column name="SettingValue" /> > </element> > </map> > > <!-- several other properties and subclasses --> > > </class> > </hibernate-mapping> > > */* trace of the resulting HardwareSettings autition table mapping */* > <hibernate-mapping assembly="NHibernate.Envers" auto-import="false" > xmlns="urn:nhibernate-mapping-2.2"> > <class entity-name="HardwareSettings_AUD" table="HardwareSettings_AUD"> > <composite-id name="originalId"> > <key-many-to-one class="NHibernate.Envers.DefaultRevisionEntity, > NHibernate.Envers, Version=1.0.0.0, Culture=neutral, > PublicKeyToken=e2c5b946037fb7f8" name="REV"> > <column name="REV" /> > </key-many-to-one> > <key-property name="Hardware_Id" type="Guid"> > <column name="HardwareSettings_id" /> > </key-property> > <key-property name="element" > type="MyApp.DataAccess.NHibernate.UserTypes.StringClobUserType, > MyApp.DataAccess.NHibernate, Version=1.0.0.0, Culture=neutral, > PublicKeyToken=866d4a0fa0599fe0"> > <column name="SettingValue" /> > <type > name="MyApp.DataAccess.NHibernate.UserTypes.StringClobUserType, > MyApp.DataAccess.NHibernate, Version=1.0.0.0, Culture=neutral, > PublicKeyToken=866d4a0fa0599fe0" /> > </key-property> > <key-property name="mapkey" type="String"> > <column name="SettingName" /> > </key-property> > </composite-id> > <property insert="true" update="false" name="REVTYPE" > type="NHibernate.Envers.Entities.RevisionTypeType, NHibernate.Envers, > Version=1.0.0.0, Culture=neutral, PublicKeyToken=e2c5b946037fb7f8" > not-null="true" /> > </class> > </hibernate-mapping> > > > */* created HardwareSettings table */* > create table HardwareSettings ( > HardwareSettings_id UNIQUEIDENTIFIER not null, > SettingValue NTEXT null, > SettingName NVARCHAR(255) not null, > primary key (HardwareSettings_id, SettingName) > ) > > */* created HardwareSettings audition table (with erroneous primary key) > */* > create table HardwareSettings_AUD ( > REV INT not null, > HardwareSettings_id UNIQUEIDENTIFIER not null, > SettingValue NTEXT not null, > SettingName NVARCHAR(255) not null, > REVTYPE TINYINT not null, > primary key (REV, HardwareSettings_id, SettingValue, SettingName) > ) > > > If this cannot be solved by myself or via configuration: > Has someone an idea where to hook in to change the way Envers creates the > schema for the audit tables? > > > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] <javascript:>. > To post to this group, send email to [email protected] <javascript:> > . > Visit this group at http://groups.google.com/group/nhusers. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/d/optout.
