PecanProject
users: make keys
- [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.
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.