Migrations: identifier name length

[Remco Blok]

Hello,

My app targets either Microsoft SQL Server or Oracle depending on the connection string. All code is supposed to be database agnostic. I try to use automatic migrations where possible. I find that most migrations can work automatically when targeting MS SQL Server. But the same automatic migration cannot be applied when targeting Oracle because the generated identifier names of foreign keys and indices are too long. Now for the sake of Oracle I have to do code-based migrations to explicitly name those identifiers where SQL Server was happy with the generated identifiers and automatic migrations. I must say I don't care too much about the names of foreign keys and indices and having to explicitly name them for Oracle only is a pain. It's great that Devart has validation on the identifier name length to prevent the migration from even starting, but it would be better if the convention for generating names for foreign keys and indices could be changed so that migrations can be done automatically again. Is this something that can be done in Devart's provider, or is this naming convention part of core EntityFramework that cannot be changed by Devart?

Remco

[Shalex]

At first glance, automatic renaming in our provider can solve this issue. However, after close examination new insoluble issues appear:

• tables and columns cannot be renamed because these names are used for mapping and SQL queries will contain these long names
• renaming of the objects that are not used in SQL (primary keys, foreign keys, indexes, sequences/triggers) seems possible at first sight. But how can this be performed without all the model metadata or alive database connection to know whether the new identifier will be unique? Here is the example, demonstrating the problem for foreign keys, however such examples can easily be created for any objects.
  Migration #1 contains an operation of adding a foreign key between the "Instrument" and "InstrumentComponent" - AddForeignKeyOperation with the name "FK_Instrument_InstrumentComponent_ColumnName". This name is too long for Oracle, so it is truncated to "FK_Instrument_InstrumentCompon".
  Operation executed successfully.
  Migration #2 contains a operation of adding the second foreign key between the "Instrument" and "InstrumentComponent" - AddForeignKeyOperation with the name "FK_Instrument_InstrumentComponent_AnotherColumnName". This name is also too long for Oracle and is truncated to "FK_Instrument_InstrumentCompon". This DDL causes an error because the constraint name is not unique. And we cannot determine if the name is unique.

Unfortunately our negotiations with Microsoft to make EF Code-First Migration engine truncate correctly the names during generation of automatic or code-based migration were not successful. Current Microsoft's point of view: explicit names may be provided when using code-base migrations and so the user is free to change any default names.

[Shalex]

Could you please specify if you want us to implement the following option (by default, false)?
The activation of this option will make all default identificators for PK, FK, indices and sequences be truncated after the 30th symbol when using Code-First Migrations. This will mean no check for uniqueness of the name. Just truncation. Do you need such feature?

[teckerstorfer]

Would it be possible that you give the option to the user of having a function which returns the name of the object?

[Shalex]

We will investigate such possibility and post here about the results.

[Remco Blok]

Hi Shalex, Apologies, I seem to have missed the notification of your further reply to this post.

I don't think I would want the identifiers simply truncated if that's only going to give potential conflicts due to non-uniqueness.

Since I don't care about the identifier name of indices and foreign keys at all, I was really hoping the provider could generate some random unique name, and store this in the metadata of the model so that it can compare the version in the database with the version of the model. But I can imagine that may be quite tricky.

The problem with being forced to do a code-based migration because of the identifier name of indices and foreign keys being over 30 chars is that the code based migration then specifies everything in code. For instance for a new table the migration now contains the CreateTable and PrimaryKey statements when I only want the migration to explicitly specify the name of a foreign key or index. So if I could specify the name of foreign keys and indices by decorating (navigation)properties on my entity classes, then perhaps I can do an automatic migration again.

[Shalex]

Remco Blok and teckerstorfer, thank you for your suggestions.

We have investigated the Code-First Migrations architecture more deeply and decided to implement the option which, additionally to the truncation after the 30th symbol, will rename long identificators using a particular algorithm to provide the highest uniqueness of the new names and guarantee that the name conversion will be done in the same way every time (this will allow to calculate the name of the constraint to remove if necessary in the future).

We are going to implement the feature in one of the nearest builds. We will post here about the results.

[Shalex]

The config.CodeFirstOptions.TruncateLongDefaultNames configuration option is added (default value is False) to provide the possibility of truncating long default names of indexes, primary keys, foreign keys, triggers, and sequences generated by Code-First Migrations.
We will post here when the corresponding build of dotConnect for Oracle is available for download.

[Shalex]

New version of dotConnect for Oracle 7.1 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=24522 .

[teckerstorfer]

Could this functionality also be implemented for PostgreSQL? Maximum length for PG is 63.

[Shalex]

Thank you for your suggestion. We will post here when the config.CodeFirstOptions.TruncateLongDefaultNames configuration option is implemented in dotConnect for PostgreSQL.

[Shalex]

The new features which will be available in the next public build of dotConnect for PostgreSQL (we will post here when it is available for download):
* The validation of identifier name length in Code-First Migrations is implemented
* The config.CodeFirstOptions.TruncateLongDefaultNames configuration option (default value is False) is added to provide the possibility of truncating long default names of indexes, primary keys, and foreign keys generated by Code-First Migrations

[Shalex]

New build of dotConnect for PostgreSQL 6.2.96 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=3&t=25018.