users: make keys

[gsrohde]

• [x] Verify proposed keys
• [ ] Eliminate NULLs and duplicates if necessary
• [ ] Add constraints for keys
  • [x] Constrain login to be unique
  • [ ] Constrain email to be unique
  • [ ] Constrain apikey to be unique (if not null)
• [x] Add value constraints to key columns

  Proposed keys

The spreadsheet proposes (apikey), (email), (login), and (salt) as keys, and the GIST proposes (crypted_password) as well. I'm only convinced that login, apikey, and possibly email should be a keys.

It seems unlikely that crypted_password or salt will ever be non-unique on the production site, and I wouldn't see any harm even if they ever should be non-unique. On the other hand, the database update script makes multiple rows on local user machines that share one or both of these values.

As for email, it might be convenient if a user could have accounts with different logins but the same email. I don't know if there is anywhere that the Rails app relies upon emails being unique.

I'm assuming that apikey is used to look up the permissions of a user when access to BETYdb is via the api key. (If not, then I question whether it needs to be unique.) Currently this is allowed to be, and usually is, NULL. (Only 3 users have non-NULL api keys.) If we want to require this to be non-NULL (as the spreadsheet and GIST suggest) we have to ensure the Rails app always generates a value for this.

NULLs

As mentioned, only three rows have non-NULL values for apikey.

For the other proposed keys, there are only 2 cases of violations of proposed non-NULL constraints: two rows have a NULL value for salt.

Duplicates

login, crypted_password, and email are currently always unique. apikey is unique when it is not NULL. Two rows have the same non-NULL value for salt.

Value constraints

email should look like an e-mail address

login and apikey should not contain whitespace.

[dlebauer]

Having the key [login, apikey, and email] would be sufficient.

[gsrohde]

To do:

• add uniqueness contraints on apikey and email
• maybe require salt to be non-null

There are only two rows in ebi_production where salt is null (names Carl Davidson and Jacklyn Rodriguez). I think they can be deleted (they aren't in the UI directory). I think Rails sets salt, so I don't know why there are NULLs in this column.