Welcome to the CodeSmith Community!

Re: Postgres - questions regarding generated code (quoted entity/column names, schema vs owner, native ID generation)

Template Frameworks

A description has not yet been added to this group.

Postgres - questions regarding generated code (quoted entity/column names, schema vs owner, native ID generation)

Answered (Verified) This question is answered

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).

1. Hbm.cst sticks in square brackets around column names. Please correct me if I'm wrong, but I don't think postgres supports this. I had to change Hbm.cst to use the ` quote mark instead as it didn't interpret the square brackets when running the generated code. Using the square brackets as generated from the standard templates gives me the error :

syntax error at or near "["
Severity: ERROR
Code: 42601
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)

 

2. Hbm.cst has the following in the table name section:

table="<%= entityManager.TableFullSafeSqlName %>"

 

I changed the above line from Hbm.cst (replaced double quotes with single) as follows:

 table='<%= entityManager.TableFullSafeSqlName %>'

 and I also had to modify the code in NHibernateHelper.TableFullSafeSqlName as below, again to get over the issue I was having with square brackets :

 

 

        public static string TableFullSafeSqlName(TableSchema sourceTable)
        {
            // SJH Commented out - to use ` instead of square brackets - this ends up in the hbm file and thence in the SQL
            // and Postgres doesn't like it

            //var safeName = String.IsNullOrEmpty(sourceTable.Owner)
            //                   ? String.Empty
            //                   : String.Concat("[", sourceTable.Owner, "].");

            //return String.Concat(safeName, "[", sourceTable.Name, "]");

            var safeName = String.IsNullOrEmpty(sourceTable.Owner)
                               ? String.Empty
                               : String.Concat("\"", sourceTable.Owner, "\".");

            return String.Concat(safeName, "\"", sourceTable.Name, "\"");
        }

  

 

 

3. The above change 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;

Thanks in advance for any help with these issues.

  • Post Points: 35
Verified Answer
All Replies
Page 1 of 1 (5 items)