{"action":"created","issue":{"url":"https://api.github.com/repos/PecanProject/bety/issues/247","labels_url":"https://api.github.com/repos/PecanProject/bety/issues/247/labels{/name}","comments_url":"https://api.github.com/repos/PecanProject/bety/issues/247/comments","events_url":"https://api.github.com/repos/PecanProject/bety/issues/247/events","html_url":"https://github.com/PecanProject/bety/issues/247","id":57865158,"number":247,"title":"species value constraints: needed cleanup and decisions","user":{"login":"gsrohde","id":1706896,"avatar_url":"https://avatars.githubusercontent.com/u/1706896?v=3","gravatar_id":"","url":"https://api.github.com/users/gsrohde","html_url":"https://github.com/gsrohde","followers_url":"https://api.github.com/users/gsrohde/followers","following_url":"https://api.github.com/users/gsrohde/following{/other_user}","gists_url":"https://api.github.com/users/gsrohde/gists{/gist_id}","starred_url":"https://api.github.com/users/gsrohde/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gsrohde/subscriptions","organizations_url":"https://api.github.com/users/gsrohde/orgs","repos_url":"https://api.github.com/users/gsrohde/repos","events_url":"https://api.github.com/users/gsrohde/events{/privacy}","received_events_url":"https://api.github.com/users/gsrohde/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/PecanProject/bety/labels/1%20-%20Ready","name":"1 - Ready","color":"CCCCCC"}],"state":"open","locked":false,"assignee":{"login":"gsrohde","id":1706896,"avatar_url":"https://avatars.githubusercontent.com/u/1706896?v=3","gravatar_id":"","url":"https://api.github.com/users/gsrohde","html_url":"https://github.com/gsrohde","followers_url":"https://api.github.com/users/gsrohde/followers","following_url":"https://api.github.com/users/gsrohde/following{/other_user}","gists_url":"https://api.github.com/users/gsrohde/gists{/gist_id}","starred_url":"https://api.github.com/users/gsrohde/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gsrohde/subscriptions","organizations_url":"https://api.github.com/users/gsrohde/orgs","repos_url":"https://api.github.com/users/gsrohde/repos","events_url":"https://api.github.com/users/gsrohde/events{/privacy}","received_events_url":"https://api.github.com/users/gsrohde/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/PecanProject/bety/milestones/6","html_url":"https://github.com/PecanProject/bety/milestones/Prepare%20Database%20for%20Implementation%20of%20Constraints","labels_url":"https://api.github.com/repos/PecanProject/bety/milestones/6/labels","id":886179,"number":6,"title":"Prepare Database for Implementation of Constraints","description":"Required preparation varies by constraint type:\r\n\r\nForeign-key constraints:\r\n* Non-Null: assign to an appropriate row, assign to a \"catch-all\" row, or delete row\r\n* Existing referent: Reassign or, if nullable, maybe set to NULL \r\n\r\nUniqueness constraints: \r\n* Removed duplicate entries and update foreign-key references as needed.\r\n\r\nValue constraints:\r\n* Update invalid values, set to NULL (if nullable), or remove the row in which they occur\r\n\r\nOther?\r\n\r\n","creator":{"login":"gsrohde","id":1706896,"avatar_url":"https://avatars.githubusercontent.com/u/1706896?v=3","gravatar_id":"","url":"https://api.github.com/users/gsrohde","html_url":"https://github.com/gsrohde","followers_url":"https://api.github.com/users/gsrohde/followers","following_url":"https://api.github.com/users/gsrohde/following{/other_user}","gists_url":"https://api.github.com/users/gsrohde/gists{/gist_id}","starred_url":"https://api.github.com/users/gsrohde/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gsrohde/subscriptions","organizations_url":"https://api.github.com/users/gsrohde/orgs","repos_url":"https://api.github.com/users/gsrohde/repos","events_url":"https://api.github.com/users/gsrohde/events{/privacy}","received_events_url":"https://api.github.com/users/gsrohde/received_events","type":"User","site_admin":false},"open_issues":17,"closed_issues":0,"state":"open","created_at":"2014-12-01T17:06:57Z","updated_at":"2015-02-17T16:37:53Z","due_on":null,"closed_at":null},"comments":5,"created_at":"2015-02-16T23:42:26Z","updated_at":"2015-02-26T21:50:00Z","closed_at":null,"body":"#### spcd\r\n* [x] Decide on range\r\n * spcd is a foreign key to FIA database. okay to set as integer, range 0-10000\r\n* [ ] Implement constraint\r\n\r\n#### genus\r\n* [x] Decide if proposed constraint is ok\r\n * hyphens are allowed\r\n * Capitalize all records genus.\r\n* [ ] Clean up table\r\n* [ ] Add constraint to migration\r\n\r\n#### species\r\n* [ ] Decide on a reasonable constraint\r\n * hyphens, spaces, periods, 'var', 'x',  are all allowed\r\n * trigger conversion from 'x' to multiplication symbol\r\n* [ ] Clean up table\r\n * link to script used to identify duplicates: (?@gsrohde)\r\n * function used to remove duplicates #185 \r\n* [ ] Add constraint to migration\r\n\r\n#### scientificname\r\n* [ ] Decide on a reasonable constraint\r\n * should contain at least genus and species\r\n* [ ] Clean up table\r\n* [ ] Add constraint to migration\r\n\r\n# Details\r\n\r\n## spcd\r\n\r\nCurrent min and max are 0 and 1007.\r\n\r\n## genus\r\n\r\nWe would like to require `genus ~ '^([A-Z][-a-z]*)?$'`.  Use\r\n```sql\r\nSELECT * FROM species WHERE NOT genus ~ '^([A-Z][-a-z]*)?$';\r\n```\r\nto view outliers.\r\n\r\n\r\n## species\r\n\r\nOnly 56 values don't match `'^([a-z]+( x [a-z]+)?)?$'`, and often this is because the genus is included in the species value.  Others include 'var. ...' or have hyphens in the name.\r\n\r\nNote that the multiplication sign (×) is the preferred symbol to use for naming hybrids (see http://en.wikipedia.org/wiki/Multiplication_sign) and not the letter \"x\".  Since standardizing the spelling (including symbols used and whitespace) of names will help prevent duplicates, we should use one or the other and not use them interchangeably when storing names in the database.  And we may as well use the preferred symbol.  Since users will often use the letter \"x\" in place of the preferred symbol, a trigger function can be used to automatically correct this during INSERTs and UPDATEs.\r\n\r\n## scientificname\r\n\r\nGenerally, we would like the scientificname to be the full binomial name of the species, and so usually it should match `genus || ' ' || species`, possibly followed by some qualification (`var. ...` or `ssp. ...`, for example).  The query\r\n```sql\r\nSELECT scientificname, genus, species FROM species WHERE scientificname !~ FORMAT('^%s %s', genus, species) AND species != '';\r\n```\r\nshows 192 cases where this match fails for some reason other than that the species has been left blank.  (Note that the species _is_ blank in all but 1729 of the 50006 rows of the table!)  Sometimes this is due to misspellings, but often the genus names are totally different.  (Perhaps some of these are synonyms.)\r\n\r\nIt might be thought that at the very least we can require that the scientificname column value should textually include both the genus value and the species value, but even this fails in case like `scientificname = \"P. balsamifera x P. simonii\", genus = \"Populus\", species = \"balsamifera x simonii\"`.\r\n\r\n## Other columns\r\n\r\nConstraints on Kingdom, Division, Class, and Family names should probably be similar to that for genus, but we won't concern ourselves with any of the columns to the right of the `updated_at` column for now.\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/PecanProject/bety/issues/comments/76281591","html_url":"https://github.com/PecanProject/bety/issues/247#issuecomment-76281591","issue_url":"https://api.github.com/repos/PecanProject/bety/issues/247","id":76281591,"user":{"login":"gsrohde","id":1706896,"avatar_url":"https://avatars.githubusercontent.com/u/1706896?v=3","gravatar_id":"","url":"https://api.github.com/users/gsrohde","html_url":"https://github.com/gsrohde","followers_url":"https://api.github.com/users/gsrohde/followers","following_url":"https://api.github.com/users/gsrohde/following{/other_user}","gists_url":"https://api.github.com/users/gsrohde/gists{/gist_id}","starred_url":"https://api.github.com/users/gsrohde/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gsrohde/subscriptions","organizations_url":"https://api.github.com/users/gsrohde/orgs","repos_url":"https://api.github.com/users/gsrohde/repos","events_url":"https://api.github.com/users/gsrohde/events{/privacy}","received_events_url":"https://api.github.com/users/gsrohde/received_events","type":"User","site_admin":false},"created_at":"2015-02-26T21:49:59Z","updated_at":"2015-02-26T21:49:59Z","body":"@dlebauer Shall I go ahead and fix what seem to me like obvious errors (including repeated genus name in the scientificname column)?"}}