Mapping help

[Alec Whittington]

Hi all,

   I realize this might not be the best place to ask, but thought I would start here first then move up the stack as needed. 

I have a class, ApplicationLog, that I use for logging Log4Net messages. One of the properties for the class is "Message". Nothing special, just a string.

I would like to represent this as a text field in the database. My mapping for the field is as such:

Map(x => x.Message).CustomTypeIs("StringClob").CustomSqlTypeIs("text");

In the mapping file (shown in the applications text log) I noticed that it was correct with the exception that is has a length on it.

When I generate the SQL script, it does not generate that field in the table correctly. It generates it as a nvarchar(100) instead of ntext. I am sure I am not the only one that has encountered this issue within the group. I am generating the SQL script against a SQL 2005 Dev Ed database, not SQLExpress. Here is the code for generating the SQL file:

        [Test]
        public void CreateSchema()
        {
            Configuration cfg = new Configuration().Configure();
            var persistenceModel = new PersistenceModel();
            persistenceModel.addMappingsFromAssembly(
                Assembly.Load("Tubbed.Data"));
            persistenceModel.Configure(cfg);
            new SchemaExport(cfg).SetOutputFile(@"c:\tubbed.sql").Create(true, false);
            //new SchemaUpdate(cfg).Execute(true, false);
        }

Here is my mapping file:

        public ApplicationLogMap()
        {
            WithTable("ApplicationLog");

            Id(x => x.LogEntryID);

            Map(x => x.Date);
            Map(x => x.Level).WithLengthOf(50);
            Map(x => x.Logger).WithLengthOf(255);
            Map(x => x.Message)CustomTypeIs("StringClob").CustomSqlTypeIs("text");
            Map(x => x.Thread).WithLengthOf(50);
        }
As stated above, when I look at the mapping file in the log, it appears correct, with exception of the length field. All fields look good in the SQL script except for Message which comes out as:
Message NVARCHAR(100) NULL
 Any help is appreciated.
Thanks,
Alec Whittington

[Alec Whittington]

No Ideas? This has me stumped so far.

Alec Whittington

[Paul Batum]

Alec,

Its probably worth trying to find out what the generated hbm xml looks like. The PersistenceModel class has a WriteMappingsTo method that you should be able to use. Alternatively you could use the debugger and step your way into the guts and check the xml document when it is created.

Post the xml once you have it.. if the type comes up  as "text" in the hbm xml then we know the problem is the schema generation. If the type "text" does not appear then we know its a fluent nhibernate bug.

Paul Batum

[Alec Whittington]

Paul,
  thank you, I will get implemented when I get home this evening. Thanks for your reply.

Alec Whittington

[Alec Whittington]

Here it is Paul, 

    as you can see the mapping for the field is correct with regards to type and sql-type, but a length field has been applied to it. That is incorrect for a text field in SQL. 

<?xml version="1.0" encoding="utf-8"?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false" assembly="Tubbed.Core" namespace="Tubbed.Core">

  <class name="ApplicationLog" table="ApplicationLog" xmlns="urn:nhibernate-mapping-2.2">

    <id name="LogEntryID" column="LogEntryID" type="Int32">

      <generator class="identity" />

    </id>

    <property name="Date" column="Date" type="DateTime">

      <column name="Date" />

    </property>

    <property name="Level" column="Level" length="50" type="String">

      <column name="Level" />

    </property>

    <property name="Logger" column="Logger" length="255" type="String">

      <column name="Logger" />

    </property>

    <property name="Message" column="Message" length="100" type="StringClob">

      <column name="Message" sql-type="text" />

    </property>

    <property name="Thread" column="Thread" length="50" type="String">

      <column name="Thread" />

    </property>

  </class>

</hibernate-mapping>

Mapping File:

    public class ApplicationLogMap : ClassMap<ApplicationLog>

    {

        public ApplicationLogMap()

        {

            WithTable("ApplicationLog");

            Id(x => x.LogEntryID);

            Map(x => x.Date);

            Map(x => x.Level).WithLengthOf(50);

            Map(x => x.Logger).WithLengthOf(255);

            Map(x => x.Message).CustomTypeIs("StringClob").CustomSqlTypeIs("text");

            Map(x => x.Thread).WithLengthOf(50);

        }

    }

Alec Whittington

[Paul Batum]

Okay the first thing I would try to do is experiment with setting the length to a value that is not going to cause problems. Zero or null would be my first attempts, have you tried those? You wont be able to use the WithLengthOf method to set it to null because it only accepts an integer, so try using SetAttribute.

One more thing I would try, you can call AddAlteration and manipulate the xml. So maybe you can do AddAlteration(x => x.RemoveAttribute("length").

Paul Batum

[Alec Whittington]

Thank you for your reply Paul, here are the results of me making the changes;

Original mapping file:

<?xml version="1.0" encoding="utf-8"?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false" assembly="Tubbed.Core" namespace="Tubbed.Core">

  <class name="ApplicationLog" table="ApplicationLog" xmlns="urn:nhibernate-mapping-2.2">

    <id name="LogEntryID" column="LogEntryID" type="Int32">

      <generator class="identity" />

    </id>

    <property name="Date" column="Date" type="DateTime">

      <column name="Date" />

    </property>

    <property name="Level" column="Level" length="50" type="String">

      <column name="Level" />

    </property>

    <property name="Logger" column="Logger" length="255" type="String">

      <column name="Logger" />

    </property>

    <property name="Message" column="Message" length="100" type="StringClob">

      <column name="Message" sql-type="text" />

    </property>

    <property name="Thread" column="Thread" length="50" type="String">

      <column name="Thread" />

    </property>

  </class>

</hibernate-mapping>

Original SQL output:

create table ApplicationLog (

  LogEntryID INT IDENTITY NOT NULL,

   Message NVARCHAR(100) null,

   Thread NVARCHAR(50) null,

   Logger NVARCHAR(255) null,

   Date DATETIME null,

   Level NVARCHAR(50) null,

   primary key (LogEntryID)

)

New mapping file:

<?xml version="1.0" encoding="utf-8"?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false" assembly="Tubbed.Core" namespace="Tubbed.Core">

  <class name="ApplicationLog" table="ApplicationLog" xmlns="urn:nhibernate-mapping-2.2">

    <id name="LogEntryID" column="LogEntryID" type="Int32">

      <generator class="identity" />

    </id>

    <property name="Date" column="Date" type="DateTime">

      <column name="Date" />

    </property>

    <property name="Level" column="Level" length="50" type="String">

      <column name="Level" />

    </property>

    <property name="Logger" column="Logger" length="255" type="String">

      <column name="Logger" />

    </property>

    <property name="Message" column="Message" type="StringClob">

      <column name="Message" sql-type="text" />

    </property>

    <property name="Thread" column="Thread" length="50" type="String">

      <column name="Thread" />

    </property>

  </class>

</hibernate-mapping>

New SQL Output:

create table ApplicationLog (

  LogEntryID INT IDENTITY NOT NULL,

   Message NVARCHAR(255) null,

   Thread NVARCHAR(50) null,

   Logger NVARCHAR(255) null,

   Date DATETIME null,

   Level NVARCHAR(50) null,

   primary key (LogEntryID)

)

So the mapping changed to the correct mapping in the XML and the SQL output changed, yet the SQL output is still not correct. The code being used to generate the SQL output is this:

        public void CreateSchema()

        {

            Configuration cfg = new Configuration().Configure();

            var persistenceModel = new PersistenceModel();

            persistenceModel.addMappingsFromAssembly(Assembly.Load("Tubbed.Data"));

            persistenceModel.Configure(cfg);

            persistenceModel.WriteMappingsTo(@"D:\Tubbed.com\Mappings");

            new SchemaExport(cfg).SetOutputFile(@"c:\tubbed.sql").Create(true, false);

        }

Seems pretty straight forward, but at this point I am not sure where the problem actually lies. It does seem that if a type of StringClob is declared with no length, then it should not default one.Then on the other hand, when the SQL is generated, with the proper mapping, it is still incorrect.

Alec Whittington

[Paul Batum]

At this point Alec I think you would be best asking for some help on the nhibernate users list. I don't know a great deal about the schema generation tool, and I am simply out of ideas. That second XML looked like it would do the trick.

Paul Batum

[Tuna Toksöz]

Looks like NH issue, yes. Can you raise it in NH users list, or create an issue in jira.nhibernate.org so we can see what's happening?

Thanks!

--
Tuna Toksöz
http://tunatoksoz.com

Typos included to enhance the readers attention!

[Alec Whittington]

Ok so I will answer my own question here.

My old project had it's dialect set to MsSQl2000Dialect, while the new one is MsSQl2005Dialect. Since SQL 2005 depreciated TEXT AND NTEXT to VARCHAR(MAX) AND NVARCHAR(MAX), NVARCHAR(MAX) is what I am looking for.

There is a bug with Fluent nHibernate though, related to length on StringClobs and BinaryClobs. You have to set fields you want as them using WithLengthOf(10000), this will force it to use the MAX version as it should.

So in the end, the schema generator is correct, and the error was on my end compounded by the length issue in Fluent nHibernate. Thanks for looking into it Paul and Tuna.

Alec Whittington

[Paul Batum]

Sorry Alec, can you clarify, what exactly is the bug?

[Alec Whittington]

Paul,

   first off thank you for your help. It pointed me in the right direction. In the case of my issue, it was two fold:

1. In my older project, the NH dialect was specified as MsSql2000Dialect, thus when it saw "StringClob" with a sql-type of "text", it generated a column for the table of type "text" or "ntext". My newest project has MsSql2005Dialect as its NH dialect, so the proper output should be varchar(max) or nvarchar(max) for the same mapping. This was completely my own misunderstanding and had nothing to do with Fluent NH.
   
   
2. The bug with Fluent NH has to do with the length attribute when specifying a CustomTypeIs("StringClob").CustomSqlTypeIs("text"). It should not apply a length at all in this case. Same with BinaryBlob. When you use the SchemaExport in NH hbm2ddl, it sees these types and should output it to the proper column type based on the NH dialect chosen. To get around this, you need to remove the length attribute with an AddAlteration. 

Now with that being said, the SchemaExport class was refactored / changed between the NH 2.0.0.1 and 2.0.4.x release. It now has a bug in it as well. When it sees "StringClob" or "BinaryBlob", it is no longer specifying a length internally so that it generates the proper column type. Might not call it a bug, as it might have been by design, but it most definitely not backwards compatible. So the fix for this is to instead of using AddAlteration as mentioned above to remove the length attribute, you instead use WithLengthOf(10000) to force the SchemaExport to create the proper column type.

Points of Interest

• Fluent NH should not specify a length when it sees StringClob or BinaryBlob
• NH hbm2ddl SchemaExport has changed and no longer sets the length behind the scenes for you so that when specifying a clob or blob it maps to the proper column type. You must set a length greater than 8000 (max length for a varchar field) to force it to set the proper column type for clob or blob. 

Hope that answers your question Paul.

Alec Whittington

[Paul Batum]

Ok now I follow, thanks Alec. There is a convention in place that assigns all mapped strings a length unless a length was assigned explicitly. It seems this convention should not be applied to mapped strings of type StringClob. I will take a look at modifying the convention accordingly.

BinaryBlobs shouldn't be a problem as they won't be mapped against a string and therefore will not be affected by the existing convention.

Thanks,

Paul Batum