Mentat

Instead of validating login and email from every form, I decided to convert the values to lowercase in the database model using validates from sqlalchemy.orm. I also wrote a migration for existing data so it should be solved.

So, as we discussed during the meeting on June 22, I didn't consider the case when there are two users who have the same login when in lowercase (e.g. login123 and LOGIN123). The proposed way of solving this was to add some suffix in order to maintain UNIQUE constraint and disable the user.

I am trying to come up with an SQL query, which adds the suffix to users who don't have unique login when in lowercase. So far I can only identify users who don't have login in lowercase and add random suffix. Here is my code (I feel like I will need to join the tables for the final query, even though this query can be constructed without JOIN):

SELECT concat(u1.login, floor(random() * 10000 + 1)) FROM users AS u1 JOIN users AS u2 ON u1.id = u2.id WHERE lower(u1.login)  <> u1.login;

Should be fixed now in 92e95adf. Is my solution OK?

Rajmund Hruska wrote in #note-4:

Should be fixed now in 92e95adf. Is my solution OK?

I did not run it, but it seems fine. I do have a remark, but that is better to discuss during a videocall. Setting TBD flag.

Rajmund Hruska wrote in #note-6:

Based on today's meeting, I changed the migration so that all users with conflicting logins will get disabled and their login will change. Radko Krkoš could you look at this query, please? Is it OK? I tried the query on our data and it seems fine.

[...]

If it was tested, it is fine by me. I see no obvious problems. I would make the last WHERE uppercase to be consistent. Also I like AS, but that is purely preferential, so feel free to ignore that.
Functionally, the uppercase '_CONFLICT_CASE' does not match today's conclusion of enforcing lowercase as soon as possible; and would definitely conflict with the discussed constraint `CHECK (login = lower(login))`.

Merged into devel and deployed on mentat-alt.

I forgot to add CHECK constraint.

Rajmund Hruska wrote in #note-9:

I forgot to add CHECK constraint.

Fixed in b4dc04de and tested locally. If no one is opposed I will merge it to devel.

Radko Krkoš wrote in #note-11:

Rajmund Hruska wrote in #note-10:

Rajmund Hruska wrote in #note-9:

I forgot to add CHECK constraint.

Fixed in b4dc04de and tested locally. If no one is opposed I will merge it to devel.

I suggest altering the naming. A name such as chk_login is not descriptive of what the CHECK actually does. I suggest login_lowercase or chk_login_lowercase if you prefer. Also, I suggest to think of it as a CONSTRAINT, of which CHECK is just one variant out of six (For details: https://www.postgresql.org/docs/14/ddl-constraints.html).

Thank you, I renamed the constraints.

Deployed on mentat-alt. We still have all data and the constraints exist so hopefully it's all good.