{"action":"created","issue":{"url":"https://api.github.com/repos/PecanProject/bety/issues/237","labels_url":"https://api.github.com/repos/PecanProject/bety/issues/237/labels{/name}","comments_url":"https://api.github.com/repos/PecanProject/bety/issues/237/comments","events_url":"https://api.github.com/repos/PecanProject/bety/issues/237/events","html_url":"https://github.com/PecanProject/bety/issues/237","id":57808770,"number":237,"title":"inputs 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/2%20-%20Working","name":"2 - Working","color":"CCCCCC"},{"url":"https://api.github.com/repos/PecanProject/bety/labels/needs%20feedback","name":"needs feedback","color":"d4c5f9"}],"state":"open","locked":false,"assignee":{"login":"dlebauer","id":464871,"avatar_url":"https://avatars.githubusercontent.com/u/464871?v=3","gravatar_id":"","url":"https://api.github.com/users/dlebauer","html_url":"https://github.com/dlebauer","followers_url":"https://api.github.com/users/dlebauer/followers","following_url":"https://api.github.com/users/dlebauer/following{/other_user}","gists_url":"https://api.github.com/users/dlebauer/gists{/gist_id}","starred_url":"https://api.github.com/users/dlebauer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dlebauer/subscriptions","organizations_url":"https://api.github.com/users/dlebauer/orgs","repos_url":"https://api.github.com/users/dlebauer/repos","events_url":"https://api.github.com/users/dlebauer/events{/privacy}","received_events_url":"https://api.github.com/users/dlebauer/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/PecanProject/bety/milestones/5","html_url":"https://github.com/PecanProject/bety/milestones/Design%20Database%20Constraints","labels_url":"https://api.github.com/repos/PecanProject/bety/milestones/5/labels","id":886165,"number":5,"title":"Design Database Constraints","description":"Make a list of all constraints the BETYdb database should satisfy.\r\n\r\nRelated to Redmine issues:\r\n* https://ebi-forecast.igb.illinois.edu/redmine/issues/1915","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":25,"closed_issues":0,"state":"open","created_at":"2014-12-01T16:58:41Z","updated_at":"2015-05-13T14:43:27Z","due_on":null,"closed_at":null},"comments":2,"created_at":"2015-02-16T14:14:15Z","updated_at":"2015-05-20T19:26:08Z","closed_at":null,"body":"#### notes\r\n* [x] Add not NULL constraint to migration\r\n\r\n#### start_date, end_date\r\n* [ ] Decide if should be not NULL\r\n* [ ] Decide if there should be other constraints (see below)\r\n* [ ] Fix violations\r\n* [ ] Add constraints to migration\r\n\r\n#### name\r\n* [x] Add not NULL and is_whitespace_normalized constraints to migration\r\n\r\n#### access_level\r\n##### nullness\r\n* [x] Decide if should be not NULL\r\n * should be not null\r\n\r\nIf so:\r\n* [x] Fix violations\r\n * change null to 1 (conservatively assuming private)\r\n* [x] Add constraint to migration\r\n\r\n##### range\r\n* [x] Decide whether to require access_level IN (1, 2, 3, 4)\r\n * yes\r\n\r\nIf so:\r\n* [x] Fix violations\r\n* [x] Make domain and change column type to use it\r\n\r\n#### raw\r\n* [x] Decide if should be not NULL\r\n * should be either 0 or 1; null by default (assuming raw) \r\n\r\nIf so:\r\n* [x] Fix violations\r\n * ~~convert t --> 1, f --0 (please confirm @robkooper) to be consistent with other boolean fields~~ I left this as a boolean for now.  Why change to a type that requires much more storage when boolean yields the same amount of information?\r\n * convert NULL -> 1 (assume raw)  -- I CHANGED THESE TO TRUE INSTEAD.\r\n* [x] Add constraint to migration\r\n\r\n#### format_id\r\n* [ ] Get rid of NULLs (2 currently)\r\n* [ ] Add non null constraint to migration\r\n\r\n# Details\r\n\r\n## start_date, end_date\r\n\r\n```sql\r\nSELECT start_date, end_date FROM inputs WHERE start_date IS NULL OR end_date IS NULL;\r\n```\r\nshows 18 rows with null start_date and 19 with null end_date.\r\n\r\n```sql\r\nSELECT start_date, end_date FROM inputs WHERE start_date >= end_date;\r\n```\r\nshows 1 row where start_date > end_date and 2 where they are equal.\r\n\r\nCHECK (start_date < end_date) seem like a reasonable constraint (unless we want to attach special significance to them being equal or to end_date being before start_date).\r\n\r\n```sql\r\nSELECT start_date, end_date FROM inputs WHERE start_date > NOW() OR end_date > NOW();\r\n```\r\nshows 3 end_date values way in the future.  We would think that CHECK (end_date < NOW()) is a reasonable constraint.\r\n\r\n## access_level\r\n\r\n```sql\r\nSELECT COUNT(*), access_level FROM inputs GROUP BY access_level ORDER BY access_level;\r\n```\r\nshows that there are 2 rows with an access_level of -1 and 192 rows where access_level is NULL.\r\n\r\nIt's not clear what access_level does since BETYdb Rails app users with only acces_level 4 can view and edit all inputs.\r\n * A: access_level defines who should / should not access files, independent of rails app\r\n * @robkooper shouldn't this be handled in dbfiles (or preferably at filesystem level)?\r\n\r\n## raw\r\n\r\n```\r\nSELECT id, name FROM inputs WHERE raw IS NULL;\r\n```\r\nshows 8 rows where raw is NULL."},"comment":{"url":"https://api.github.com/repos/PecanProject/bety/issues/comments/104005547","html_url":"https://github.com/PecanProject/bety/issues/237#issuecomment-104005547","issue_url":"https://api.github.com/repos/PecanProject/bety/issues/237","id":104005547,"user":{"login":"dlebauer","id":464871,"avatar_url":"https://avatars.githubusercontent.com/u/464871?v=3","gravatar_id":"","url":"https://api.github.com/users/dlebauer","html_url":"https://github.com/dlebauer","followers_url":"https://api.github.com/users/dlebauer/followers","following_url":"https://api.github.com/users/dlebauer/following{/other_user}","gists_url":"https://api.github.com/users/dlebauer/gists{/gist_id}","starred_url":"https://api.github.com/users/dlebauer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dlebauer/subscriptions","organizations_url":"https://api.github.com/users/dlebauer/orgs","repos_url":"https://api.github.com/users/dlebauer/repos","events_url":"https://api.github.com/users/dlebauer/events{/privacy}","received_events_url":"https://api.github.com/users/dlebauer/received_events","type":"User","site_admin":false},"created_at":"2015-05-20T19:26:08Z","updated_at":"2015-05-20T19:26:08Z","body":"Depends on definition of \"raw\" and specific datasets. I still think default should be null, and perhaps existing files evaluated on a per file basis\r\n\r\nStart date / end date do not apply to some inputs. Perhaps these should be allowed to be NULL"}}