To get to the bottom of this we need:
2 Likes
I’m not sure where you are getting your data to find the User_Detals_Id not found. BUT if I change the name in the Create Script, the problem goes away. Yes, Postgres stores all of its table.column names as lower case: We use lower case in the Elixir code. But it still has a problem finding the column by name.
The names in the Information schema are lower case. Here is a query in Postgres of the Information_schema:
dido=# SELECT Table_Name,
dido-# Column_Name
dido-# FROM information_schema.columns
dido-# WHERE table_schema = 'public'
dido-# AND table_name = 'user_details'
dido-# AND column_name = 'user_details_id';
user_details | user_details_id
As you can see it gets the right results. That is why we originally used lower snakecase letters in the Elixir code. BUT you can’t seem to find it unless I changes the CREATE SCRIPT.
So, the mystery is where are you getting the need for the User_Detils_Id
Please note that even if SQL is your passion, drawing conclusion like this does not increase the quality of the discussion 
4 Likes
I just realised that the elixir code you posted has the following:
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
Note the foreign_key: :User_Details_Id
This is almost certainly wrong. Someone here is mixing camel case and snake case. It should almost certainly be something like :user_details_id.
3 Likes
Just to be clarify I think what krstfk is suggesting is that if you normalize those atoms on the elixir side to be downcased it will likely tolerate the odd casing of your tables on the postgres side. It’s perfectly valid for elixir to enforce case sensitivity on atoms, which is an elixir/erlang specific concept, and not in any way related to whether or not ecto is compliant with the SQL standard.
2 Likes
Testing that as we speak. I looked at this go=code til I’m blue in the face. Thanks. I’ll post the resuts
Thanks. Thant fixed the problem. I will submut a new response that outlines the errors that I found.
Okay, here is what I found.
- SQL standard is case insensitivity for database, schema, tables, columns etc,
- EXTO is case sensitive BUT as long as the case used for names is consistent, there should be no problem
The symptom that ECTO was using a case used in the CREATE TABLE script was an anomaly cause by a mix of cases used in the ECTO file (user error) and NOT because they needed the DB object names to be changed.
Thanks to everyone for helping solve this mystery!!
As a side note, we had a problem using Visual Code. We had searched the Exto files for the Title Snake Version User_Details_Id and we didn’t find any. The reason was because the code was generated on another machine and the updates to the filesystem we were on. It had not made all the updates in the machine we were on. When we went back and search later they were found just as expected.
Another note to everyone. There needs to be a barrier if you will between Ecto and the datastores. Ecto should not impose any restrictions on the datastores. Another problem we ran into was the assumption that all primary keys would have be <table_Name)_ID and that we should just use ‘id’ in our code. Sine the database existed before the ECTO, this made it hard and we did not find good examples of how to manage that situation. Yes, we eventually found all the documentation in various places to get what we needed done, but it was painful. If anyone is interested we will be glad to provide full examples.
