Nhibernate generates an error in db create script

Question

I'm trying to generate db schema using fluent nhibernate, nhibernate 3.0, spring.net 1.3.1 and SQLite. The create/update script generated by NHibernate is

create table LogEntries (Id UNIQUEIDENTIFIER not null, Hostname TEXT not null, LoggerName TEXT not null, LogLevel INTEGER not null, Message TEXT not null, primary key (Id))
create table Properties (Id INTEGER not null, Key TEXT, Value TEXT, LogEntry_id UNIQUEIDENTIFIER, Index INTEGER, primary key (Id))

But it fails with the following error

System.Data.SQLite.SQLiteException: SQLite error
near "Index": syntax error

The entities:

public class LogEntry
{
    public virtual Guid Id { get; set; }

    public virtual string LoggerName { get; set; }

    public virtual string Message { get; set; }

    public virtual int LogLevel { get; set; }

    public virtual string Hostname { get; set; }

    public virtual IList<Property> Properties { get; set; }
}

public class Property
{
    public virtual int Id { get; set; }

    public virtual string Key { get; set; }

    public virtual string Value { get; set; }
}

And the mapping classes

public class LogEntryMap : ClassMap<LogEntry>
{
    public LogEntryMap()
    {
        Table("LogEntries");
        Id(x => x.Id).GeneratedBy.GuidComb();

        Map(x => x.Hostname).Not.Nullable();
        Map(x => x.LoggerName).Not.Nullable();
        Map(x => x.LogLevel).Not.Nullable();
        Map(x => x.Message).Not.Nullable();

        HasMany(x => x.Properties).Cascade.AllDeleteOrphan().AsList();
    }
}

public class PropertyMap : ClassMap<Property>
{
    public PropertyMap()
    {
        Table("Properties");

        Id(x => x.Id).GeneratedBy.Increment();

        Map(x => x.Key);
        Map(x => x.Value);
    }
}

Found the problem. In the mapping class for logEntry I'm using AsList(). When I remove this, it works — Thomas, Jan 18 '11 at 09:32

score 3 · Answer 1 · edited May 23 '17 at 12:14

I'm currently learning NHibernate myself (reading NHibernate 3.0 Cookbook), so in no way am I an expert.

I have the same problem at the moment, having a HasMany-map Parent.Children in an SQLite environment. This also crashes on the Index syntax error.

From what I've managed to deduce, Index is a reserved keyword (isn't it in almost every RDBMS?). It seems these keywords are not escaped by default, and hence, the SQL-script is invalid.

According to the book, you can escape the columnnames by adding a backtick to the column-name:

HasMany(x => x.Children).Cascade.AllDeleteOrphan().AsList(p => p.Column("`Index"));

However, even though this "works", it generates the following SQL-query, which seems to have dropped the x:

create table Child (
    Id INTEGER not null,
   ChildType TEXT not null,
   Version INTEGER not null,
   Content TEXT,
   Title TEXT not null,
   Parent_id INTEGER,
   "Inde" INTEGER,
   primary key (Id)
)

So, either consider:

specifying a custom index columnname which isn't a keyword,
rely on the backtick auto-escape (no clue what's happening here, no time to check)
use a different collection type if you don't actually need an ordered list. See List vs Set vs Bag in NHibernate

For me specifying `Index` worked - in your code it looks like your missing the second back-tick. — Oliver, Aug 19 '11 at 16:15

Nhibernate generates an error in db create script

1 Answers1