{"action":"created","issue":{"url":"https://api.github.com/repos/jbrzusto/TO_DO/issues/101","repository_url":"https://api.github.com/repos/jbrzusto/TO_DO","labels_url":"https://api.github.com/repos/jbrzusto/TO_DO/issues/101/labels{/name}","comments_url":"https://api.github.com/repos/jbrzusto/TO_DO/issues/101/comments","events_url":"https://api.github.com/repos/jbrzusto/TO_DO/issues/101/events","html_url":"https://github.com/jbrzusto/TO_DO/issues/101","id":237313248,"number":101,"title":"master DB schema doesn't allow an efficient query for runs changed during an update","user":{"login":"jbrzusto","id":1228504,"avatar_url":"https://avatars1.githubusercontent.com/u/1228504?v=3","gravatar_id":"","url":"https://api.github.com/users/jbrzusto","html_url":"https://github.com/jbrzusto","followers_url":"https://api.github.com/users/jbrzusto/followers","following_url":"https://api.github.com/users/jbrzusto/following{/other_user}","gists_url":"https://api.github.com/users/jbrzusto/gists{/gist_id}","starred_url":"https://api.github.com/users/jbrzusto/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jbrzusto/subscriptions","organizations_url":"https://api.github.com/users/jbrzusto/orgs","repos_url":"https://api.github.com/users/jbrzusto/repos","events_url":"https://api.github.com/users/jbrzusto/events{/privacy}","received_events_url":"https://api.github.com/users/jbrzusto/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2017-06-20T19:06:14Z","updated_at":"2017-06-20T19:25:32Z","closed_at":null,"body":"Suppose we have batches from multiple receivers, and a run\r\non one receiver that spans three of its batches.\r\nUnder the current, these would appear like so in final form:\r\n```\r\n___hits________________               ___runs_______________________________\r\nhitID   runID   batchID               runID   batchIDbegin batchIDend length\r\n h1       r1      b1                   r1        b1           b5        9\r\n h2       r1      b1\r\n h3       r1      b1\r\n h4       r1      b3\r\n h5       r1      b3\r\n h6       r1      b3\r\n h7       r1      b5\r\n h8       r1      b5\r\n h9       r1      b5\r\n\r\n___batches_______\r\nbatchID  deviceID\r\n  b1       d1\r\n  b2       d2\r\n  b3       d1\r\n  b4       d2\r\n  b5       d1\r\n```\r\nAfter only batches b1 and b2 have been received, the client-side DB looks like this:\r\n```\r\n___hits________________               ___runs_______________________________\r\nhitID   runID   batchID               runID   batchIDbegin batchIDend length\r\n h1       r1      b1                   r1        b1           null      3\r\n h2       r1      b1\r\n h3       r1      b1\r\n\r\n___batches_______\r\nbatchID  deviceID\r\n  b1       d1\r\n  b2       d2\r\n```\r\nIf the next update occurs after batch b3 has been processed, here's what we'd want the\r\nclient-side to look like:\r\n```\r\n___hits________________               ___runs_______________________________\r\nhitID   runID   batchID               runID   batchIDbegin batchIDend length\r\n h1       r1      b1                   r1        b1           null      6\r\n h2       r1      b1\r\n h3       r1      b1\r\n h4       r1      b3\r\n h5       r1      b3\r\n h6       r1      b3\r\n\r\n___batches_______\r\nbatchID  deviceID\r\n  b1       d1\r\n  b2       d2\r\n  b3       d1\r\n```\r\nHowever, the only way to determine that the record for run r1 needs to be updated is:\r\n\r\n```\r\n   batchID=3 -> hits with batchID=3 -> runIDs\r\n```\r\ni.e. we must examine each hit to find the unique set of runs involved in batch 3.\r\n\r\nA better schema would link like so:\r\n```\r\n___hits______     ___runs________________\r\nhitID   runID     runID  length  complete\r\n h1       r1       r1      9       true\r\n h2       r1\r\n h3       r1\r\n h4       r1\r\n h5       r1\r\n h6       r1\r\n h7       r1\r\n h8       r1\r\n h9       r1\r\n\r\n___batches_______   ___batchRuns_________________________\r\nbatchID  deviceID   runID  batchID  firstHitID  lastHitID\r\n  b1       d1         r1     b1         h1         h3\r\n  b2       d2         r1     b3         h4         h6\r\n  b3       d1         r1     b5         h7         h9\r\n  b4       d2\r\n  b5       d1\r\n```\r\nwhich uses a new table batchRuns, with indexes on runID and batchID,\r\nand a many-to-many relation between these two columns, to track\r\nwhich runs overlap which batches.  The hits from run R within\r\nbatch B could then be queried by:\r\n\r\n```sql\r\nselect t3.* from\r\n   batchRuns as t1\r\n   join runs as t2 on t1.runID=t2.runID\r\n   join hits as t3 on t3.runID=t2.runID\r\n   where t1.batchID=B and t2.runID=R\r\n```\r\n\r\nAll hits from batch B could be obtained as\r\n```sql\r\nselect t3.* from\r\n   batchRuns as t1\r\n   join runs as t2\r\n      on t1.runID=t2.runID\r\n   join hits as t3\r\n      on t3.runID=t2.runID\r\n   where t1.batchID=B\r\n      and t3.hitID >= t1.firstHitID\r\n      and t3.hitID <= t1.lastHitID\r\n```\r\nbut is this worth the savings of removing the batchID field from hits?\r\n"},"comment":{"url":"https://api.github.com/repos/jbrzusto/TO_DO/issues/comments/309862233","html_url":"https://github.com/jbrzusto/TO_DO/issues/101#issuecomment-309862233","issue_url":"https://api.github.com/repos/jbrzusto/TO_DO/issues/101","id":309862233,"user":{"login":"jbrzusto","id":1228504,"avatar_url":"https://avatars1.githubusercontent.com/u/1228504?v=3","gravatar_id":"","url":"https://api.github.com/users/jbrzusto","html_url":"https://github.com/jbrzusto","followers_url":"https://api.github.com/users/jbrzusto/followers","following_url":"https://api.github.com/users/jbrzusto/following{/other_user}","gists_url":"https://api.github.com/users/jbrzusto/gists{/gist_id}","starred_url":"https://api.github.com/users/jbrzusto/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jbrzusto/subscriptions","organizations_url":"https://api.github.com/users/jbrzusto/orgs","repos_url":"https://api.github.com/users/jbrzusto/repos","events_url":"https://api.github.com/users/jbrzusto/events{/privacy}","received_events_url":"https://api.github.com/users/jbrzusto/received_events","type":"User","site_admin":false},"created_at":"2017-06-20T19:25:32Z","updated_at":"2017-06-20T19:25:32Z","body":"the new schema would require changes to:\r\n\r\n### find_tags_motus ###\r\nDBFiler::begin_run:\r\n   - don't include batchIDbegin field in query\r\n   - set done=0\r\n\r\nDBFiler::end_run:\r\n   - add an entry to batchRuns for current batchID, runID\r\n   - don't include batchIDend field in query\r\n   - set done=1 if not just updating length\r\n\r\n### motusServer ###\r\n\r\ncounts of mention of batchIDbegin or batchIDend\r\n\r\nfile | count\r\n---|---\r\ndataServer.R              |37\r\ndeleteFromMotus.R         |1\r\ndropBatchesFromTransfer.R |3\r\nensureTagProjDB.R         |2\r\nmakeTagProjDB.R           |1\r\npurgeFromMotus.R          |1\r\npushToMotus.R             |11\r\nsgEnsureDBTables.R        |5\r\n\r\n\r\n### motus ###\r\n\r\ncounts of mention of batchIDbegin or batchIDend\r\n\r\nfile | count\r\n---|---\r\nensureDBTables.R|2\r\nsrvRunsForReceiverProject.R|2\r\nsrvRunsForTagProject.R|2\r\n"}}
{"actor":{"display_login":"jbrzusto"}}