{"action":"created","issue":{"url":"https://api.github.com/repos/globaleaks/GlobaLeaks/issues/1821","repository_url":"https://api.github.com/repos/globaleaks/GlobaLeaks","labels_url":"https://api.github.com/repos/globaleaks/GlobaLeaks/issues/1821/labels{/name}","comments_url":"https://api.github.com/repos/globaleaks/GlobaLeaks/issues/1821/comments","events_url":"https://api.github.com/repos/globaleaks/GlobaLeaks/issues/1821/events","html_url":"https://github.com/globaleaks/GlobaLeaks/issues/1821","id":192009612,"number":1821,"title":"Force unique filename constraint on wbfiles in the database schema","user":{"login":"NSkelsey","id":1095794,"avatar_url":"https://avatars.githubusercontent.com/u/1095794?v=3","gravatar_id":"","url":"https://api.github.com/users/NSkelsey","html_url":"https://github.com/NSkelsey","followers_url":"https://api.github.com/users/NSkelsey/followers","following_url":"https://api.github.com/users/NSkelsey/following{/other_user}","gists_url":"https://api.github.com/users/NSkelsey/gists{/gist_id}","starred_url":"https://api.github.com/users/NSkelsey/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/NSkelsey/subscriptions","organizations_url":"https://api.github.com/users/NSkelsey/orgs","repos_url":"https://api.github.com/users/NSkelsey/repos","events_url":"https://api.github.com/users/NSkelsey/events{/privacy}","received_events_url":"https://api.github.com/users/NSkelsey/received_events","type":"User","site_admin":false},"labels":[{"id":29427316,"url":"https://api.github.com/repos/globaleaks/GlobaLeaks/labels/C:%20Backend","name":"C: Backend","color":"eb6420","default":false},{"id":355294157,"url":"https://api.github.com/repos/globaleaks/GlobaLeaks/labels/S:%20Blocked","name":"S: Blocked","color":"e99695","default":false},{"id":55784712,"url":"https://api.github.com/repos/globaleaks/GlobaLeaks/labels/T:%20Refactoring","name":"T: Refactoring","color":"207de5","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/globaleaks/GlobaLeaks/milestones/45","html_url":"https://github.com/globaleaks/GlobaLeaks/milestones/2016%20December","labels_url":"https://api.github.com/repos/globaleaks/GlobaLeaks/milestones/45/labels","id":2163179,"number":45,"title":"2016 December","description":"","creator":{"login":"evilaliv3","id":217034,"avatar_url":"https://avatars.githubusercontent.com/u/217034?v=3","gravatar_id":"","url":"https://api.github.com/users/evilaliv3","html_url":"https://github.com/evilaliv3","followers_url":"https://api.github.com/users/evilaliv3/followers","following_url":"https://api.github.com/users/evilaliv3/following{/other_user}","gists_url":"https://api.github.com/users/evilaliv3/gists{/gist_id}","starred_url":"https://api.github.com/users/evilaliv3/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/evilaliv3/subscriptions","organizations_url":"https://api.github.com/users/evilaliv3/orgs","repos_url":"https://api.github.com/users/evilaliv3/repos","events_url":"https://api.github.com/users/evilaliv3/events{/privacy}","received_events_url":"https://api.github.com/users/evilaliv3/received_events","type":"User","site_admin":false},"open_issues":2,"closed_issues":0,"state":"open","created_at":"2016-11-29T09:18:38Z","updated_at":"2016-11-29T09:19:50Z","due_on":"2016-12-31T00:00:00Z","closed_at":null},"comments":1,"created_at":"2016-11-28T13:45:33Z","updated_at":"2016-11-30T10:00:26Z","closed_at":null,"body":"Currently the enforcement of filename uniqueness for wbfile uploaded by recipients is done in python.\r\n\r\nTo perform the enforcement with the database requires the addition of a many-to-many table that expresses the relationship.\r\n\r\nIn python the code looks roughly like this:\r\n```python\r\nwbfile_names = store.find(WhistleblowerFile, WhistleblowerFile.recievertip_id == ReceiverTip.receivertip_id, \r\n                                              InternalTip.id == rtip.itip)                                                                 \r\nif new_name in wfile_names:\r\n     return errors.FailedSanityCheck()\r\n```\r\n\r\nThe sql instead looks like this:\r\n\r\n```sql\r\nCREATE TABLE wbfile_itip_ref (                                                                           \r\n    whistleblowerfile_id,\r\n    internaltip_id,                                                                                      \r\n    name TEXT NOT NULL, \r\n    PRIMARY KEY (name, internaltip_id),                                                                  \r\n    UNIQUE(whistleblowerfile_id),                                                                        \r\n    FOREIGN KEY (whistleblowerfile_id) REFERENCES whistleblowerfile(id) ON DELETE CASCADE,               \r\n    FOREIGN KEY (internaltip_id) REFERENCES internaltip(id) ON DELETE CASCADE                \r\n);  \r\n```\r\n\r\nThis is blocked until another migration can be prepared. And when @evilaliv3 thinks it is a good idea. Hint (it is a good idea)"},"comment":{"url":"https://api.github.com/repos/globaleaks/GlobaLeaks/issues/comments/263832071","html_url":"https://github.com/globaleaks/GlobaLeaks/issues/1821#issuecomment-263832071","issue_url":"https://api.github.com/repos/globaleaks/GlobaLeaks/issues/1821","id":263832071,"user":{"login":"NSkelsey","id":1095794,"avatar_url":"https://avatars.githubusercontent.com/u/1095794?v=3","gravatar_id":"","url":"https://api.github.com/users/NSkelsey","html_url":"https://github.com/NSkelsey","followers_url":"https://api.github.com/users/NSkelsey/followers","following_url":"https://api.github.com/users/NSkelsey/following{/other_user}","gists_url":"https://api.github.com/users/NSkelsey/gists{/gist_id}","starred_url":"https://api.github.com/users/NSkelsey/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/NSkelsey/subscriptions","organizations_url":"https://api.github.com/users/NSkelsey/orgs","repos_url":"https://api.github.com/users/NSkelsey/repos","events_url":"https://api.github.com/users/NSkelsey/events{/privacy}","received_events_url":"https://api.github.com/users/NSkelsey/received_events","type":"User","site_admin":false},"created_at":"2016-11-30T10:00:26Z","updated_at":"2016-11-30T10:00:26Z","body":"@evilaliv3 because adding a `UNIQUE` constraint like that would mean every file name across the whole system would be unique. Using `internaltip_id` and `name` instead enforces uniqueness across each tip. Obviously we can add an `internaltip_id` to the whistleblowerfile table, but if you want to use normalized sql adding this table many-to-many is how to do ti."}}
{"actor":{"display_login":"NSkelsey"}}