NHibernate Postgres Support - Brackets are generated around column names which will result in an exception during runtime.

[GoogleCodeExporter]

References: 
http://community.codesmithtools.com/Template_Frameworks/f/67/t/11671.aspx

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, "\"");
        }

Original issue reported on code.google.com by bniemyjski on 15 Feb 2011 at 4:34