Table of Contents

Quoted identifiers in database

Default behavior in Neos

By default, table and column names are generated between double quotes ("Table_Name") by database migration, using exactly the name given in the metadata. Queries generated by EF Core also contain these same table and column names between double quotes.

This behavior is not a problem in a Neos environment because names are always written in the same case.

Potential problems

However, the tables generated by default by Neos can be difficult to use with certain external libraries. There is, for example, Quartz.NET which internally generates requests with identifiers without quotes.

These difficulties can be explained by the different behavior of the various providers:

  • Sql Server is completely case-insensitive. If you use a different capitalization than the one used at creation time, the table/field is always referenced in a case-insensitive way.
  • In Oracle, delimited identifiers are case sensitive ("table_name" != "Table_Name"), while non quoted identifiers are not, and are transformed to upper case (Table_Name => TABLE_NAME).
  • In PostgreSQL, delimited identifiers are case sensitive ("table_name" != "Table_Name"), while non quoted identifiers are not, and are transformed to lower case (Table_Name => table_name).

The tables below summarise the syntax accepted depending on the case used when creating the metadata in Neos.

Syntaxes accepted for a table created with the name Table_Name ("Table_Name" in migration)

Provider "Table_Name" TABLE_NAME table_name
Sql Server VALID VALID VALID
PostgreSQL VALID ERROR ERROR
Oracle VALID ERROR ERROR

Syntaxes accepted for a table created with the name TABLE_NAME ("TABLE_NAME" in migration)

Provider "Table_Name" TABLE_NAME table_name
Sql Server VALID VALID VALID
PostgreSQL ERROR ERROR (1) ERROR
Oracle ERROR VALID VALID (2)
  1. Non quoted identifiers are transformed to lower case so "table_name" != "TABLE_NAME"
  2. Non quoted identifiers are transformed to upper case so "TABLE_NAME" = "TABLE_NAME"

Syntaxes accepted for a table created with the name table_name ("table_name" in migration)

Provider "Table_Name" TABLE_NAME table_name
Sql Server VALID VALID VALID
PostgreSQL ERROR VALID (1) VALID
Oracle ERROR ERROR ERROR (2)
  1. Non quoted identifiers are transformed to lower case so "table_name" = "table_name"
  2. Non quoted identifiers are transformed to upper case so "TABLE_NAME" != "table_name"

Configure Neos to stop generating case-sensitive identifiers

Warning

Changing the configuration proposed below on an existing cluster will render any existing database unusable, as database migration does not support this case. We therefore strongly recommend that you only change this configuration when you create a new cluster.

It is possible to modify the behavior of Neos so that it no longer systematically generates identifiers between quotes respecting the case entered in the metadata. To do this, you need to add the configuration below to the cluster:

Database:
  QuotedIdentifiers: false

When QuotedIdentifiers is set to false, the database migration behavior is modified:

  • Identifiers are written without quotes whenever possible (exceptions: names beginning with $ and words reserved by the database engine).
  • Identifiers are converted to lower case when the target database is PostgreSQL.
  • Identifiers are converted to uppercase when the target database is Oracle.

The binding on EF Core entities is modified:

  • Table/column names are converted to lower case when the target database is PostgreSQL (in this case, the PostgreSQL provider automatically detects that quotes are not necessary and does not include them in the generated SQL).
  • Table/column names are converted to uppercase when the target database is Oracle (the Oracle provider will continue to generate quotes in the generated SQL, but this has no effect as the names are in upper case).

The tables below summarize the syntax accepted depending on the case used when creating the metadata in Neos when QuotedIdentifiers is set to false.

Syntaxes accepted for a table created with the name Table_Name

Provider "Table_Name" TABLE_NAME table_name
Sql Server VALID VALID VALID
PostgreSQL ERROR (1) VALID VALID
Oracle ERROR (2) VALID VALID
  1. Non quoted identifiers are transformed to lower case so "Table_Name" != "table_name"
  2. Non quoted identifiers are transformed to upper case so "Table_Name" != "TABLE_NAME"

Syntaxes accepted for a table created with the name TABLE_NAME

Provider "Table_Name" TABLE_NAME table_name
Sql Server VALID VALID VALID
PostgreSQL ERROR (1) VALID VALID
Oracle ERROR (2) VALID VALID
  1. Non quoted identifiers are transformed to lower case so "Table_Name" != "table_name"
  2. Non quoted identifiers are transformed to upper case so "Table_Name" != "TABLE_NAME"

Syntaxes accepted for a table created with the name table_name

Provider "Table_Name" TABLE_NAME table_name
Sql Server VALID VALID VALID
PostgreSQL ERROR (1) VALID VALID
Oracle ERROR (2) VALID VALID
  1. Non quoted identifiers are transformed to lower case so "Table_Name" != "table_name"
  2. Non quoted identifiers are transformed to upper case so "Table_Name" != "TABLE_NAME"