{"action":"created","issue":{"url":"https://api.github.com/repos/catalyst/moodle-local_datacleaner/issues/17","repository_url":"https://api.github.com/repos/catalyst/moodle-local_datacleaner","labels_url":"https://api.github.com/repos/catalyst/moodle-local_datacleaner/issues/17/labels{/name}","comments_url":"https://api.github.com/repos/catalyst/moodle-local_datacleaner/issues/17/comments","events_url":"https://api.github.com/repos/catalyst/moodle-local_datacleaner/issues/17/events","html_url":"https://github.com/catalyst/moodle-local_datacleaner/issues/17","id":191428599,"number":17,"title":"The user scrambling feature has a rounding error which makes a lot of duplicates","user":{"login":"brendanheywood","id":187449,"avatar_url":"https://avatars.githubusercontent.com/u/187449?v=3","gravatar_id":"","url":"https://api.github.com/users/brendanheywood","html_url":"https://github.com/brendanheywood","followers_url":"https://api.github.com/users/brendanheywood/followers","following_url":"https://api.github.com/users/brendanheywood/following{/other_user}","gists_url":"https://api.github.com/users/brendanheywood/gists{/gist_id}","starred_url":"https://api.github.com/users/brendanheywood/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/brendanheywood/subscriptions","organizations_url":"https://api.github.com/users/brendanheywood/orgs","repos_url":"https://api.github.com/users/brendanheywood/repos","events_url":"https://api.github.com/users/brendanheywood/events{/privacy}","received_events_url":"https://api.github.com/users/brendanheywood/received_events","type":"User","site_admin":false},"labels":[{"id":345413680,"url":"https://api.github.com/repos/catalyst/moodle-local_datacleaner/labels/bug","name":"bug","color":"fc2929","default":true}],"state":"open","locked":false,"assignee":{"login":"roperto","id":3672494,"avatar_url":"https://avatars.githubusercontent.com/u/3672494?v=3","gravatar_id":"","url":"https://api.github.com/users/roperto","html_url":"https://github.com/roperto","followers_url":"https://api.github.com/users/roperto/followers","following_url":"https://api.github.com/users/roperto/following{/other_user}","gists_url":"https://api.github.com/users/roperto/gists{/gist_id}","starred_url":"https://api.github.com/users/roperto/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/roperto/subscriptions","organizations_url":"https://api.github.com/users/roperto/orgs","repos_url":"https://api.github.com/users/roperto/repos","events_url":"https://api.github.com/users/roperto/events{/privacy}","received_events_url":"https://api.github.com/users/roperto/received_events","type":"User","site_admin":false},"assignees":[{"login":"roperto","id":3672494,"avatar_url":"https://avatars.githubusercontent.com/u/3672494?v=3","gravatar_id":"","url":"https://api.github.com/users/roperto","html_url":"https://github.com/roperto","followers_url":"https://api.github.com/users/roperto/followers","following_url":"https://api.github.com/users/roperto/following{/other_user}","gists_url":"https://api.github.com/users/roperto/gists{/gist_id}","starred_url":"https://api.github.com/users/roperto/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/roperto/subscriptions","organizations_url":"https://api.github.com/users/roperto/orgs","repos_url":"https://api.github.com/users/roperto/repos","events_url":"https://api.github.com/users/roperto/events{/privacy}","received_events_url":"https://api.github.com/users/roperto/received_events","type":"User","site_admin":false}],"milestone":null,"comments":6,"created_at":"2016-11-24T03:56:59Z","updated_at":"2016-11-28T01:25:14Z","closed_at":null,"body":"The aim is to have a deterministic, but non reversible way of taking the existing names and mixing them up. We have N records and we have F fields we want to randomize.\r\n\r\nThe way this should work is to create a temporary table with a sorted list of names, and then using two prime numbers make pseudo random combinations from this list.\r\n\r\nSo if you had a bunch of real names like this:\r\n\r\nid | First | Last\r\n--|------------ | -------------\r\n1 | David | Smith\r\n2 | Nicholas | Hoobin\r\n3 | Bill | Jones\r\n4 | Daniel | Roperto \r\n5 | Brendan | Heywood\r\n6 | Sarah | Bryce\r\n\r\nIn this case we have N = 6, and F = 2.\r\n\r\nWhat we want is to find the smallest two adjacent prime numbers P1 and P2 which when multiplied gives us the number of records we have or higher. In this case 6 = 2 x 3. (note a quick heuristic is to start this search at it's square root and then work down / up from there. So ceil(root(6)) = 3).\r\n\r\nFor N = 100 records we'd have P1 = 9 and P2 = 11    (sqrt(100 = 10))\r\n\r\nFor N = 1000 records we'd get P1 = 31 and P2 = 37    (sqrt(1000) = 31)\r\n\r\nFor N = 10000 records we'd get P1 = 101 and P2 = 102    (sqrt(10000) = 100)\r\n\r\n\r\nSo we are going to create a temp table with 3 (the bigger prime) records. This process is the same regardless of F.\r\n\r\nIf we had 5 fields to randomize then F = 5, we want 5 prime numbers. So as well as P1 and P2 we just find the next couple primes higher than P1, P2, etc.\r\n\r\nNow we create a temp table which has as many records as our highest prime. We only have P1 and P2, and P2 = 3. So we want 3 records in our temp table. Let's start by cloning the first 3 records from our real data into a temp table:\r\n\r\nid | First | Last\r\n--|------------ | -------------\r\n1 | David | Smith\r\n2 | Nicholas | Hoobin\r\n3 | Bill | Jones\r\n\r\nWe don't want to be able to figure out who was who originally so lets sort each column independently:\r\n\r\nid | First | Last\r\n--|------------ | -------------\r\n1 | Bill | Hoobin\r\n2 | David | Jones\r\n3 | Nicholas | Smith\r\n\r\nNow we pick the index modulo 2 for the first column, and the modulo 3 for the second column (and so on if we had more columns) and then lookup what value this maps to in the temp table as our new scrambled value:\r\n\r\nid | Old First | Old Last | id % P1 (2) | New First | id % P2 (3) | New Last\r\n--|------------ | -------------|--|--|--|--\r\n1 | David | Smith | 1 | Bill | 1 | Hoobin \r\n2 | Nicholas | Hoobin | 2 | David |  2 | Jones\r\n3 | Bill | Jones | 1 | Bill | 3 | Smith\r\n4 | Daniel | Roperto | 2 | David | 1 | Hoobin\r\n5 | Brendan | Heywood | 1 | Bill | 2 | Jones\r\n6 | Sarah | Bryce | 2 | David | 3 | Smith\r\n\r\nAnd voila, we have now scrambled everyone! Everyone has a realistic name, the names cannot be easily reversed back to the original records, and because of the prime numbers we guarantee that everyone has a unique combination of names. Because it is a single sql update is it incredibly quick. And because we are sampling from our original names we get realistic data that looks familiar.\r\n\r\nNote with a small N, there data does look repetitive, but for larger data-sets this deficiency fades away. If you have 1000 students, and typical course sizes of 30 students, then you shouldn't see too may duplicates within a course.\r\n\r\nAlso because it is deterministic if we re-run this each night we will get the same result so it's easier for a tester to use the same user again and again and not have it be wildly different each time (eg if we used pure random numbers)."},"comment":{"url":"https://api.github.com/repos/catalyst/moodle-local_datacleaner/issues/comments/263163671","html_url":"https://github.com/catalyst/moodle-local_datacleaner/issues/17#issuecomment-263163671","issue_url":"https://api.github.com/repos/catalyst/moodle-local_datacleaner/issues/17","id":263163671,"user":{"login":"roperto","id":3672494,"avatar_url":"https://avatars.githubusercontent.com/u/3672494?v=3","gravatar_id":"","url":"https://api.github.com/users/roperto","html_url":"https://github.com/roperto","followers_url":"https://api.github.com/users/roperto/followers","following_url":"https://api.github.com/users/roperto/following{/other_user}","gists_url":"https://api.github.com/users/roperto/gists{/gist_id}","starred_url":"https://api.github.com/users/roperto/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/roperto/subscriptions","organizations_url":"https://api.github.com/users/roperto/orgs","repos_url":"https://api.github.com/users/roperto/repos","events_url":"https://api.github.com/users/roperto/events{/privacy}","received_events_url":"https://api.github.com/users/roperto/received_events","type":"User","site_admin":false},"created_at":"2016-11-28T01:25:14Z","updated_at":"2016-11-28T01:25:14Z","body":"The only thing missing is when to get a prime number before the sqrt(). As of now it is already working for the output in my comment above."}}
{"actor":{"display_login":"roperto"}}