Changing Custom SQL Type for Identity Properties

[Isaac Inyang]

Hello,

Using Fluent NHibernate, how do I specify the Custom SQL Type which my Identity properties will be mapped to?

Specifically, I want all Int32 fields to be mapped to MEDIUMINT (3 bytes) within MySQL. Referring to the code below, I want PersonId to be of type MEDIUMINT in the database. 

Entity Definition:

    public class Person

    {

        public virtual int PersonId { get; set; }

        public virtual string FullName { get; set; }

        public virtual int Age { get; set; }

    }

Mapping Class:

    public class PersonMap : ClassMap<Person>

    {

        public PersonMap()

        {

            Id(i => i.PersonId).GeneratedBy.Identity();

            Map(i => i.FullName);

            Map(i => i.Age).CustomSqlType("mediumint");

        }

    }

Generated HBM File:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property" auto-import="true" default-cascade="none" default-lazy="true">

  <class xmlns="urn:nhibernate-mapping-2.2" name="netquepgcurrent.Entities.Person, netquepgcurrent, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="`Person`">

    <id name="PersonId" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">

      <column name="PersonId" />

      <generator class="identity" />

    </id>

    <property name="FullName" type="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">

      <column name="FullName" />

    </property>

    <property name="Age" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">

      <column name="Age" sql-type="mediumint" />

    </property>

  </class>

</hibernate-mapping>

[Paul Batum]

I believe something like this should do it:

 Id(i => i.PersonId)

  .CustomSqlType("MEDIUMINT")
  .GeneratedBy.Identity();

--
You received this message because you are subscribed to the Google Groups "Fluent NHibernate" group.
To post to this group, send email to fluent-n...@googlegroups.com.
To unsubscribe from this group, send email to fluent-nhibern...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/fluent-nhibernate?hl=en.

[Isaac Inyang]

Paul,

From my experience, CustomSqlType is only available to PropertyPart (i.e. the Map method) but not to IdentityPart (i.e. the Id method).

Is there some way to get this done?

Thank you.

Isaac

[Paul Batum]

Perhaps you don't have the latest version of the code base?

http://github.com/jagregory/fluent-nhibernate/blob/master/src/FluentNHibernate/Mapping/IdentityPart.cs

(towards the bottom, you'll see theres a CustomSqlType method)

[Isaac Inyang]

Hello Paul,

I thank you very much for your mail. I downloaded a newer version of FNH.

Below is the convention class which I wrote to apply to all entities whose ID are of the UINT type.

Convention Code

    public class UIntPrimaryKeyNamingConvention : IIdConvention, IIdConventionAcceptance

    {

        public void Apply(IIdentityInstance id)

        {

            id.CustomSqlType("MEDIUMINT UNSIGNED");

        }

        public void Accept(IAcceptanceCriteria<IIdentityInspector> criteria)

        {

            criteria.Expect(i => i.Type == typeof(uint));

        }

    }

Applying this to create my database throws exceptions. Looking closely at the exceptions, I noticed the exceptions are being thrown when REFERENCES to such entities are being applied.

Please, what can be done?

Thanking you in anticipation.

Isaac

Details of the exception thrown can be seen below:

    alter table `ScratchFail`

        add index (StudentEnrolId),

        add constraint FK19A86696AF8F2A12

        foreign key (StudentEnrolId)

        references `StudentEnrol` (StudentEnrolId)

System.TypeInitializationException: The type initializer for 'Nested' threw an exception. ---> NHibernate.HibernateException: Can't create table 'netquepgcurrent.#sql-f0_27' (errno

: 150) ---> MySql.Data.MySqlClient.MySqlException: Can't create table 'netquepgcurrent.#sql-f0_27' (errno: 150)

   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()

   at MySql.Data.MySqlClient.NativeDriver.ReadResult()

   at MySql.Data.MySqlClient.ResultSet.NextResult()

   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()

   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)

   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()

   at NHibernate.Tool.hbm2ddl.SchemaExport.Execute(Action`1 scriptAction, Boolean export, Boolean throwOnError, TextWriter exportOutput, IDbCommand statement, String sql)

   at NHibernate.Tool.hbm2ddl.SchemaExport.Execute(Action`1 scriptAction, Boolean export, Boolean justDrop, IDbConnection connection, TextWriter exportOutput)

   at NHibernate.Tool.hbm2ddl.SchemaExport.Execute(Action`1 scriptAction, Boolean export, Boolean justDrop)

   --- End of inner exception stack trace ---

   at NHibernate.Tool.hbm2ddl.SchemaExport.Execute(Action`1 scriptAction, Boolean export, Boolean justDrop)

   at NHibernate.Tool.hbm2ddl.SchemaExport.Execute(Boolean script, Boolean export, Boolean justDrop)

   at netquepgcurrent.NHH..ctor() in E:\source.code\Visual Studio 2008\Projects\PG\@College\College Tools\netquepgcurrent\NHH.cs:line 40

   at netquepgcurrent.NHH.Nested..cctor() in E:\source.code\Visual Studio 2008\Projects\PG\@College\College Tools\netquepgcurrent\NHH.cs:line 89

   --- End of inner exception stack trace ---

   at netquepgcurrent.NHH.get_Instance() in E:\source.code\Visual Studio 2008\Projects\PG\@College\College Tools\netquepgcurrent\NHH.cs:line 66

   at netquepgcurrent.NHH.Init() in E:\source.code\Visual Studio 2008\Projects\PG\@College\College Tools\netquepgcurrent\NHH.cs:line 71

   at ConsoleApplication4_Conversion.Program.Run() in E:\source.code\Visual Studio 2008\Projects\PG\@College\College Tools\ConsoleApplication4-Conversion\Program.cs:line 41

   at ConsoleApplication4_Conversion.Program.Main(String[] args) in E:\source.code\Visual Studio 2008\Projects\PG\@College\College Tools\ConsoleApplication4-Conversion\Program.cs:line 24

[Paul Batum]

Your convention looks correct to me. I have no experience with MySQL so I'm not really familiar with the error you are getting.

Can you clarify, do you still get this error without the convention?

[Isaac Inyang]

Without the convention, everything runs well. 

With the convention enabled, I suspect there is a type mismatch between the database columns i.e. MEDIUMINT in table 1 and INT In table 2.

Thank you.

Isaac

[Paul Batum]

Have you tried verifying that the same problem occurs when specifying the MEDIUMINT type using an automapping override in one particular instance, rather than as a convention across the board?

Without MySql knowledge, I'm afraid I'm going to run out of ideas pretty fast on this one.

[Isaac Inyang]

Same thing happened using when I use CustomSqlType with IdentityPart. This behavior seems to be independent of the data type passed to the method.

            Id(i => i.StudentId).CustomSqlType("MEDIUMINT").GeneratedBy.Identity();

I wish other developers with MySql could be of assistance.

Thank you very much.

Isaac

[Paul Batum]

About the only other thing I can suggest is that you try to get some assistance from the nhibernate users mailing list. If you can find some xml that will work with MEDIUMINT, then we can try to get FNH to generate it for you.

[Isaac Inyang]

Paul,

Thank you. I will contact the nh-user list.

Isaac