Unwarranted and Unnecessary Case Sensitivity in ECTO

We noticed that although databases (SQL standards) are not case sensitive when it comes to object names such as tables, columns,indexes, etc). ECTO somehow wants to enforce case sensitivity onto the DBs. If the database was created using Title Snake Case such as CREATE TABLE User_Details…, Ecto somehow wants the elixir code to use the same case. This is flat out incorrect.

select count(*) from __db_table_details;
SELECT User_Details from __DB_TABLE_Details;

Yield exactly the same results on Oracle, Postgres, MySQL and SQL Server. Yet iin the world of Elixir, I have to match the case to the original used when the table was created in Postgres. THIS IS WRONG! Please follow the SQL Standard and make all schema, database, table, column names etc case insensitive.

Otherwise you are injecting your bias for case insensitivity onto the DB communities. Databases have existed for years before ECTO existed and will be around long after it is an after thought. Legacy database might have evolved over years and there for each query has to be formulated according to how the original person entered the commands in SQL. Since SQL is not case sensitive by definition (see standards) this makes the use of ECTO extremely cumbersome.

Remember, ECTO is not the center of the universe. It is just another tool that is trying to access DBs created by DB professionals using whatever tools they have over the lifespan of the DB. It is unreasonable to ask them to change to fit your view of a DB.

Can you post directly the ecto code that’s not working? It’s possible you’re missing an escape hatch.

1 Like

Welcome! This is clearly something you feel passionately about, and we’d be happy to address any of your concerns. At the moment however I’m not entirely clear what the issue is. Schemas, migrations, and even schema fields can all be configured with arbitrary strings to map to the case found in the database. Ecto may expect that you tell it about the case found in the database for performance reasons so that it can map return column values directly to schema keys and not have to normalize on every request.

This doesn’t change anything about the database. This doesn’t change anything about SQL. This is about configuring Ecto to be an easy to use and efficient boundary layer between SQL and Elixir.

Can you elaborate with some concrete examples?

4 Likes
  CREATE TABLE User_Details
      ( Change_Notes                  Document_Type        NULL,
        Inserted_At                        Dido_Timestamp_Type   NULL,
        Inserted_By                        Dido_Id_Type                 NULL,
        Public_Signature_Key         Dido_Key_Type             NULL,
        Updated_At                         Dido_Timestamp_Type  NULL,
        Updated_By                        Dido_Id_Type                NULL,
        User_Details_Id                   Dido_Id_Type                NOT NULL,
        User_Name                         Dido_Name_Type           NOT NULL,
        Web_Site                             Uri_Type                        NULL,
          CONSTRAINT Pk_User_Details_Id
            PRIMARY KEY ( User_Details_Id )
    );

We get an error that User_Details_Id can not be found. If we change the DB code to be lower snake case (i.e., user_details_id, then it works. I believe you are using the Postgres catalog which I believe does record the original case. But as far as the operation of a DB works, it is case insensitive.

Having Elixir and ECTO be case sensitive is fine an expected in most modern languages, especially those that have roots in C. BUT when actually accessing the DB, it should be case insensitive. Therefore:

user_details_id``` 
in Ecto should map to any case of `User_Details_ID, USER_DETAILS_ID, user_details_id, or even USER_detals_ID`.
defmodule DidoData.Models.UserDetails do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key { :user_details_id, :binary_id, autogenerate: true }
  @foreign_key_type :binary_id
  @derive 
    {  Jason.Encoder, only: 
         [ :change_notes,
           :public_signature_key,
           :user_name,
           :web_site
         ]
    }

  schema "user_details" do

    field       :change_notes,                :string
    field       :public_signature_key,        :boolean
    field       :user_name,                   :string
    field       :web_site,                    :string

    has_many    :domain_board_member,   DidoData.Models.DomainBoardMember, foreign_key: :User_Details_Id, references: :user_details_id
    has_many    :domain_chair,   DidoData.Models.DomainChair, foreign_key: :User_Details_Id, references: :user_details_id
    has_many    :domain_member,   DidoData.Models.DomainMember, foreign_key: :User_Details_Id, references: :user_details_id
    has_many    :ecocsys_board_member,   DidoData.Models.EcocsysBoardMember, foreign_key: :User_Details_Id, references: :user_details_id
    has_many    :ecosphere_board_member,   DidoData.Models.EcosphereBoardMember, foreign_key: :User_Details_Id, references: :user_details_id
    has_many    :ecosphere_chair,   DidoData.Models.EcosphereChair, foreign_key: :User_Details_Id, references: :user_details_id
    has_many    :ecosphere_member,   DidoData.Models.EcosphereMember, foreign_key: :User_Details_Id, references: :user_details_id
    has_many    :ecosphere_sponsor,   DidoData.Models.EcosphereSponsor, foreign_key: :User_Details_Id, references: :user_details_id
    has_many    :ecosys_chair,   DidoData.Models.EcosysChair, foreign_key: :User_Details_Id, references: :user_details_id
    has_many    :ecosys_member,   DidoData.Models.EcosysMember, foreign_key: :User_Details_Id, references: :user_details_id
    has_many    :social_network_connection,   DidoData.Models.SocialNetworkConnection, foreign_key: :User_Details_Id, references: :user_details_id
    has_one     :virtual_contact_card,   DidoData.Models.VirtualContactCard, foreign_key: :User_Details_Id, references: :user_details_id
    has_many    :user_profile,   DidoData.Models.UserProfile, foreign_key: :User_Details_Id, references: :user_details_id
    has_many    :user_role,   DidoData.Models.UserRole, foreign_key: :User_Details_Id, references: :user_details_id
     timestamps() #- updates fields updated_at and inserted_at
  end

  @doc false
  def changeset(user_details, attrs) do
    user_details
    |> cast(attrs, 
        [ :user_name,
        ]
      )
    |> foreign_key_constraint(:domain_board_member)
    |> cast_assoc(:domain_board_member, required: false )
          # DomainBoardMember.creation_changeset/2
    |> foreign_key_constraint(:domain_chair)
    |> cast_assoc(:domain_chair, required: false )
          # DomainChair.creation_changeset/2
    |> foreign_key_constraint(:domain_member)
    |> cast_assoc(:domain_member, required: false )
          # DomainMember.creation_changeset/2
    |> foreign_key_constraint(:ecocsys_board_member)
    |> cast_assoc(:ecocsys_board_member, required: false )
          # EcocsysBoardMember.creation_changeset/2
    |> foreign_key_constraint(:ecosphere_board_member)
    |> cast_assoc(:ecosphere_board_member, required: false )
          # EcosphereBoardMember.creation_changeset/2
    |> foreign_key_constraint(:ecosphere_chair)
    |> cast_assoc(:ecosphere_chair, required: false )
          # EcosphereChair.creation_changeset/2
    |> foreign_key_constraint(:ecosphere_member)
    |> cast_assoc(:ecosphere_member, required: false )
          # EcosphereMember.creation_changeset/2
    |> foreign_key_constraint(:ecosphere_sponsor)
    |> cast_assoc(:ecosphere_sponsor, required: false )
          # EcosphereSponsor.creation_changeset/2
    |> foreign_key_constraint(:ecosys_chair)
    |> cast_assoc(:ecosys_chair, required: false )
          # EcosysChair.creation_changeset/2
    |> foreign_key_constraint(:ecosys_member)
    |> cast_assoc(:ecosys_member, required: false )
          # EcosysMember.creation_changeset/2
    |> foreign_key_constraint(:social_network_connection)
    |> cast_assoc(:social_network_connection, required: false )
          # SocialNetworkConnection.creation_changeset/2
    |> foreign_key_constraint(:virtual_contact_card)
    |> cast_assoc(:virtual_contact_card, required: true )
          # VirtualContactCard.creation_changeset/2
    |> foreign_key_constraint(:user_profile)
    |> cast_assoc(:user_profile, required: true )
          # UserProfile.creation_changeset/2
    |> foreign_key_constraint(:user_role)
    |> cast_assoc(:user_role, required: true )
          # UserRole.creation_changeset/2
  end

  def fetch(user_details, key) when is_map(user_details) do
    case user_details do
      %{^key => value} -> {:ok, value}
      _ -> :error
    end
  end

end```

As noted before, please include the Elixir code you’re using, the code you’re running, and a complete error and stacktrace. You may want to use the source option on https://hexdocs.pm/ecto/Ecto.Schema.html#field/3

EDIT: Ah, I see you edited, one sec.

Yes, I am passionate about it. I have worked in SQL for almost 40 years.

When a db is made, who knows what case was used when it was created. Let alone when a table was altered. Many of these are done at the command line so it up to the digression of the person who wrote the ALTER command and the speed at which they are trying to get the ALTER done.

What’s worse, is I could have a debugged integrated system which will stop working because someone changed the case of the table/column name in the DB catalog. Not good. That is why the SQL standards specifies case insensitivity.

The main thing to remember here is that Ecto has two jobs: Transform Elixir => SQL AND transform sql results back to Elixir. SQL is case insensitive, but Elixir is not. Ecto is also built for systems that go beyond SQL.

I appreciate that you provided the schema. I still need the code you ran that caused the error, along with the error and stacktrace. Ideally please turn on debug logging so that you can include logs of the actual SQL that was run.

3 Likes

I believe identifiers may be case sensitive in Postgres, as per the documentation :

Quoting an identifier also makes it case-sensitive,
whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and “foo” are considered the same by PostgreSQL, but “Foo” and “FOO” are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to “FOO” not “foo” according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

https://www.postgresql.org/docs/9.1/sql-syntax-lexical.html

I believe the Postgres adapter quotes identifiers in queries.

If the tables and columns were quoted upon creation, the Ecto schema must match (and it would be true anywhere). If they were not, then everything in the Ecto schema must be lower case.

(I believe MySQL case sensitivity depends on the os, but I don’t know what the MySQL adapter does).

dido=# \d __db_table_details
 table_name       | character varying |           |          | 
 column_name      | character varying |           |          | 
 ordinal_position | character varying |           |          | 
 data_type        | character varying |           |          | 
 domain_schema    | character varying |           |          | 
 domain_name      | character varying |           |          | 
 nullable         | character varying |           |          | 

dido=# SELECT Table_Name, column_NAME from __db_table_details WHERE Upper(TaBlE_NaMe) = Upper('User_Details');
 user_details | user_details_id
 user_details | user_name
 user_details | public_signature_key
 user_details | web_site

dido=# 

As you can see the names themselves are case insensitive. When I described the table it comes back with table_name. In my query I used the name Table_Name. I Also used weird names like column_Name.

In the WHERE clause I upper case the values in the columns to make the comparison, BUT the actual column names are weird case TaBlE_NaMe.

Don’t confuse how the values in the DB are stored with the meta-data that describes the tables.

So, when someone issues an SQL statement, the names of objects are case insensitive. The contents of the objects is case sensitive.

You are right. IF I want to use special characters, spaces or maintain case sensitivity in columns then I can surround the names in quotes. BUT these tables as you can see in the first post of the CREATE TABLE did NOT surround the names with quotes.

The SQL language specification says that " SQL language characters" (which are used for identifiers and SQL keywords) are case - insensitive . If SQL were case - sensitive , it would not comply with the language standard . So you would either have to change the SQL standard , or else have a rebellious streak – Quora https://www.quora.com/Is-there-a-reason-for-SQL-not-to-be-case-sensitive-parts-that-are-case-insensitive

Unfortunately the SQL standards are ISO which seems to think that you must pay for a copy o the standard :-(. These standards should be readily available but such are the ways of the world. “We have a standard, we want people to use it … BUT they have to pay to see it” doesn’t seen like the path to success. That is why I referred you to Quora and not the standard. ISO/ IEC 9075:2003

This has focused heavily on SQL but I’m still looking for more on the Elixir side of this. What are you running? What error do you get? Nobody can help you without answers to these questions.

1 Like

Is SQL is case Sensitive or Insensitive? – Querychat

In this article, we will learn how case sensitivity works in SQL, learn about case sensitive columns in terms of syntax as well as string searches in SQL Select queries with example.

SQL case sensitivity:

The SQL Keywords are case-insensitive (SELECT, FROM, WHERE, AS, ORDER BY, HAVING, GROUP BY, etc), but are usually written in all capitals. However, in some settings table and column names are case-sensitive. MySQL has a configuration option to enable or disable it.https://www.got-it.ai/solutions/sqlquerychat/sql-help/data-manipulation/is-sql-is-case-sensitive-or-insensitive/

I have told you. We get an error that the name of the column: ‘User_Details_Id’ is not found. IF I change the DB side of the code from ‘User_Details_Id’ to ‘user_details_id’ in the SQL Create Table script then it can fix it. This is an Elixir problem, NOT a DB problem. As I have shown in the rest of the documentation I provided. SQL IS CASE INSENSITIVE for object names. Yet, Elixir is treating it as if it is case sensitive. This is a non-standard extention added by SQL Server … which I have also documented. BUT, by default even SQL Server works according to the SQL specification as case insensitive. One needs to use an option to become case sensitive.

So, how to fix the problem? Do not use the names as specified in the DB catalog but when you query the catalog make your queries case insensitive (surround them with upper of lower case functions).

I’m sorry, I must have been unclear:
I assume you’re using Postgres.
The Postgres documentation clearly states that

unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and “foo” are considered the same by PostgreSQL

As I said the Postgres adapter quotes the identifiers. It follows that your Ecto schema must be all lower case. That is, the request issued by Ecto through the Postgres adapter will be something along the lines

SELECT a0.”id” FROM “foo” as a0

All the fields will be quoted. You must hence define your Ecto schema all in lower case.
This is not specific to Ecto or the adapter.
If you do, from any client,

SELECT “id” from “Foo”

It won’t work whereas

SELECT “id” from Foo

Will or

SELECT “id” from ”foo”

Will.
This may not be conformant, but it is how Postgres work as per the documentation I quoted and linked earlier.

2 Likes

does :User_Details_Id work?

@primary_key { :User_Details_Id, :binary_id, autogenerate: true }

ecto is not an ORM but still a (data) mapper - and erlang/elixir likes to be explicit/precise about what happens…