NHibernate "database" schema confusion [.\hibernate-mapping\@schema]

Question

I'm using NHibernate primarily against an MSSQL database, where I've used MSSQL schemas for the various tables.

In my NH mapping (HBM) files, I've specified the schema for each table in the mapping as follows:

<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   auto-import="true"
                   schema="xyz">                 <!-- schema specified -->
  <class name="Customer">
    <id name="Id">
      <generator class="native" />
    </id>
    <property name="Name" />
  </class>
</hibernate-mapping>

For my unit testing, I've been experimenting with SQLite, however my mappings fail now as NH reports that the database "xyz" cannot be found.

I understand there is a difference in interpretation of schema, so what is NH's interpretation/implementation, and what is the best approach when using schema's?

BTW: Searching the web using keywords like "nhibernate database schema" doen't yielded anything relevant.

Answer 1

The NHibernate.Mapping.Table class has an GetQualifiedName(NHibernate.Dialect.Dialect dialect) method, which is defined as follows:

public string GetQualifiedName(NHibernate.Dialect.Dialect dialect)
{
    string quotedName = this.GetQuotedName(dialect);
    return ((this.schema == null) ? 
        quotedName : 
        (this.GetQuotedSchemaName(dialect) + '.' + quotedName));
}

So there's basically no way you can make SQLite to ignore schema name other than to have a separate set of mappings for every scenario (or preprocessing them before compilation).

Answer 2

You can specify the schema (if you need it) in the configuration file using property default_schema. You can use multiple configuration files, or alter the one you're using - one for production and the other for test.

It's possible you can simply ignore the schema setting and use different credentials.

Answer 3

The "standard" interpretation is that a table has a three-part name: "CATALOG.SCHEMA.TABLE" : these are the names used in the standard (ISO-SQL standard?) "information_schema" views. Hibernate (presumably also NHibernate) follows this convention, you can specify catalog and schema in a class mapping, and default_catalog and default_schema in a configuration.

In my own unit test environment (using Hypersonic), I massaged the Hibernate Configuration before building the SessionFactory: I myself did this to do things like setting HSQL-compatible IdentifierGenerators, but you can problably go through clearing the schema properties of the mapped classes.

In general, I try to avoid specifying schemas and catalogs in applications at all. In Oracle, I generally create synonyms so users see the objects in their own namespace; in PostgreSQL, set the search_path in the database configuration; in SQL Server, put all tables into 'dbo'.