Postgres PascalCase naming issue

[samelson1]

Hi guys,

I appreciate the work you do on these projects. I am currently in the process of migrating SQL Server to Postgres. I have converted my database fine and all the tables and columns have the correct names which are PascalCase. I can even connect and get data in my system as I have written a custom naming convention provider so that it preserves the casing.

My issue lies with SchemaUpdate. I have debugged the code and it doesn't seem to recognise quoted table names to apply schema changes. It tries to just recreate each table, I have been playing around and I was able to get it to see the tables by not quoting the table names, however this just causes other issues.

The image above is the method Configuration.GenerateUpdateSchemaScript. The "databaseMetadata.GetTableMetadata" is returning null when quoted table named are passed, and changing the "table.IsQuoted" doesn't seem to make a difference.

Before I dig even further into the code I am wondering if this is a restriction you're aware of?

Also wasn't sure if this should go here on in the nhib core repo, so let me know if I need to ask over there.

Cheers.

[hazzik]

It is really hard to answer anything without seeing any code.

[samelson1]

Hey yeah sorry about that. Here is my config:

Naming strategy:

and then the issue I am having is with trying to update the schema:

Please let me know if I can provide further information.

[hazzik]

You're mixing NHibernate's mapping by code (INamingStrategy) and FluentNHibernate, this is why it does not work. INamingStrategy is just ignored.

[samelson1]

It does seem to use INamingStrategy though as it's being hit and changes the table names and columns, otherwise they are all lowercase. How would you advise I go about this?

[samelson1]

@hazzik If this is out of scope of what you'd normally resolve would you be willing to accept a donation to help us resolve this?

[hazzik]

Ok, I misunderstood the INamingStrategy. It is not a part of mapping by code, it is some ancient mechanism in the NHibernate itself. It operates last before mapping is bound to the model. In theory your code should work. Although you need to modify ClassToTableName method to convert from full class name to table name (eg use StringHelper.Unqualify(className)).

What version of NHiberante and FluentNHibernate do you use?

[samelson1]

So the code mostly works, it just seems to be this UpdateSchema mechanism that is broken. It's unable to get the table meta-data so it just sees it as not existing and tries to recreate it.

Nhibernate v5.5.1 Fluent Nhibernate v3.3.0

This didn't seem to make a difference unfortunately.

When I debug the table name looks as below, although tableMetaData seems to be null always. When I debugged this through with SQL server it was finding some meta data and then adding the relevant alter strings.

[samelson1]

I did also manage to get the tableMapping.IsQuoted to be true but that didn't seem to make a difference.

[samelson1]

How it looks in the database:

[hazzik]

You need to add something like following into your mapping configuration

.FluentMappings.Conventions.Add(
    ConventionBuilder.Class.Always(c => c.Table($"\"{c.EntityType.Name}\"")),
    ConventionBuilder.Property.Always(p => p.Column($"\"{p.Name}\"")) // , ...etc
);

This goes inside the lambda that you pass as a second parameter to CompileConfiguration. And remove INamingStrategy.

The idea is that your tables should be quoted in the mapping itself. INamingStrategy kicks in too late.

[samelson1]

Hey thanks so I am definitely making progress. It seems to mostly create the tables and columns correctly although I am having a few issues atm. Here is my config:

Issue 1, random columns called system.action created:

Issue 2, it doesn't seem to be changing column names for many to many mappings, and I can't see anywhere how to do it under the many to many section:

Issue 3, it seems unable to be able to create audit table:

I really appreciate your help on the matter!

[samelson1]

I basically ended up having to pull up most of the schema updating processes and making some changes manually. Thanks for your help though!