This change is kind of complex and requires a read through of all the content 
listed below. In an overview, we are suppling the correct owner but the 
NHibernate core seems to have issues with this. We need to work around this 
issue by possibly excluding the owner. This may take some investigating.

References: 
http://community.codesmithtools.com/Template_Frameworks/f/67/t/11671.aspx
and
http://groups.google.com/group/nhusers/browse_thread/thread/ae019f3ba802f849/535
5bf08e6a63344?#5355bf08e6a63344

Hi,

I'm using Postgres 9, connecting using Npgsql 2.0.10.0, with the Codesmith 
5.3.2 revision 12664 and the Nhibernate templates. I have a few questions 
regarding the Hbm.xml files which are generated from the template frameworks. 
Apologies if these are really basic problems but if the answers are elsewhere, 
I haven't found them yet. I've been using Postgres for just a few months so am 
by no means an expert.

My postgres table names are capitalised (not my choice!) so we have to use 
quotes etc. within the SQL, as postgres dictates (I didn't alter anything in 
Codesmith to achieve this).


The above changes (http://code.google.com/p/codesmith/issues/detail?id=561) 
seems to get me over the syntax errors which come out of Postgres or Npgsql, 
but still produces an error. This seems to relate to the distinction between 
"owner" vs "schema". The error I get when running with the results of the above 
changes is:

schema "postgres" does not exist
 Severity: ERROR
 Code: 3F000
 at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext()
 at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject()
 at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
 at Npgsql.ForwardsOnlyDataReader.NextResult()
 at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError)
 at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
 at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
 at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
 at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
 at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)

which is perfectly true. My DB owner is "postgres" but the schema which owns 
these tables is "public". I can get it to work by manually changing "postgres" 
to "public" in the hbm.xml file. e.g.

  <class name="JCALF.DAL.DataObjects.Country, JCALF.DAL" table='"public"."Country"' lazy="true">

but is there a way to get the schema name in code, so that I could use the 
schema name instead of sourceTable.Owner in the 
NHibernateHelper.TableFullSafeSqlName method?

4. Hbm.cst has the following section for a table's Id field:

  <id name="Id" column="[<%= entityManager.PrimaryKey.KeyColumn.ColumnName %>]">
    <%= entityManager.PrimaryKey.Generator %>
  </id> 

which I had to change to the following to get it to work:

  <id name="Id" column="`<%= entityManager.PrimaryKey.KeyColumn.ColumnName %>`">
    <%-- <%= entityManager.PrimaryKey.Generator %> --%>
   <%-- SJH hard-coded this change into the template. PrimaryKey.Generator looks at KeyColumn.Column.ExtendedProperties.Contains(Identity)
    which doesn't work for our current schema. Documentation refers to ExtendedProperties feature on MSSQL Server. Not sure how to apply this 
    to Postgres -%>
   <generator class="native" />
  </id>

 All my PKs are Postgres "Serial" types, e.g.

CREATE TABLE "Country"
(
  "CountryID" bigserial NOT NULL, -- The unique ID of the Country record.
  "Code" character varying(3) NOT NULL, -- A unique 2- or 3-char ISO Country code
  "Name" character varying(64) NOT NULL, -- The Country name.
  CONSTRAINT "pkCountry" PRIMARY KEY ("CountryID")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "Country" OWNER TO postgres;