{"action":"created","issue":{"url":"https://api.github.com/repos/boramalper/magnetico/issues/162","repository_url":"https://api.github.com/repos/boramalper/magnetico","labels_url":"https://api.github.com/repos/boramalper/magnetico/issues/162/labels{/name}","comments_url":"https://api.github.com/repos/boramalper/magnetico/issues/162/comments","events_url":"https://api.github.com/repos/boramalper/magnetico/issues/162/events","html_url":"https://github.com/boramalper/magnetico/issues/162","id":301868395,"number":162,"title":"magneticow API v0","user":{"login":"boramalper","id":5618038,"avatar_url":"https://avatars3.githubusercontent.com/u/5618038?v=4","gravatar_id":"","url":"https://api.github.com/users/boramalper","html_url":"https://github.com/boramalper","followers_url":"https://api.github.com/users/boramalper/followers","following_url":"https://api.github.com/users/boramalper/following{/other_user}","gists_url":"https://api.github.com/users/boramalper/gists{/gist_id}","starred_url":"https://api.github.com/users/boramalper/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/boramalper/subscriptions","organizations_url":"https://api.github.com/users/boramalper/orgs","repos_url":"https://api.github.com/users/boramalper/repos","events_url":"https://api.github.com/users/boramalper/events{/privacy}","received_events_url":"https://api.github.com/users/boramalper/received_events","type":"User","site_admin":false},"labels":[{"id":854165922,"url":"https://api.github.com/repos/boramalper/magnetico/labels/discussion","name":"discussion","color":"000000","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":9,"created_at":"2018-03-02T18:54:03Z","updated_at":"2018-03-09T12:31:44Z","closed_at":null,"author_association":"OWNER","body":"Version 0.7.0 of **magnetico** will offer a simple REST-ful API for 3rd-party developers to make use of **magnetico**'s powers!\r\n\r\nYou can read the draft here: https://app.swaggerhub.com/apis/boramalper/magneticow-api/v0\r\n\r\n## What am I asking?\r\nYour feedback! Is there anything that you think is redundant, or missing? Anything you think could be an improved, or that you are unsure about.\r\n\r\nSee #50 for previous discussion, and many thanks to @lozbrown for the initial idea and many others for their contribution! =)"},"comment":{"url":"https://api.github.com/repos/boramalper/magnetico/issues/comments/371800256","html_url":"https://github.com/boramalper/magnetico/issues/162#issuecomment-371800256","issue_url":"https://api.github.com/repos/boramalper/magnetico/issues/162","id":371800256,"user":{"login":"boramalper","id":5618038,"avatar_url":"https://avatars3.githubusercontent.com/u/5618038?v=4","gravatar_id":"","url":"https://api.github.com/users/boramalper","html_url":"https://github.com/boramalper","followers_url":"https://api.github.com/users/boramalper/followers","following_url":"https://api.github.com/users/boramalper/following{/other_user}","gists_url":"https://api.github.com/users/boramalper/gists{/gist_id}","starred_url":"https://api.github.com/users/boramalper/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/boramalper/subscriptions","organizations_url":"https://api.github.com/users/boramalper/orgs","repos_url":"https://api.github.com/users/boramalper/repos","events_url":"https://api.github.com/users/boramalper/events{/privacy}","received_events_url":"https://api.github.com/users/boramalper/received_events","type":"User","site_admin":false},"created_at":"2018-03-09T12:31:44Z","updated_at":"2018-03-09T12:31:44Z","author_association":"OWNER","body":"## Analysis \r\nI tried analysing the problem by clearly indicating the problematic cases that we need to deal with:\r\n\r\n- There are two kinds of pages:\r\n\r\n  1. Previous Pages\r\n  2. Next Pages\r\n\r\n   Think of Previous Pages and Next Pages as a list of list of result rows. For instance, the following is a result of a query with 9 results in total, and the user fetched two pages:\r\n\r\n  ```\r\n  Previous Pages:\r\n      [[Row_1, Row_2, Row_3], [Row_4, Row_5, Row_6]]\r\n\r\n  Next Pages:\r\n      [[Row_7, Row_8, Row_9], []]\r\n  ```\r\n\r\n  The last list of rows in the previous pages (list) is what we call \"the current page\". Since every search requests returns a page (which might be empty), previous pages list must always have at least one list of results.\r\n\r\n- There are three kinds of modifications/mutations that might happen during the search:\r\n\r\n  1. Insertion (`INSERT INTO`)\r\n  2. Update (`UPDATE`)\r\n  3. Deletion (`DELETE`)\r\n\r\nIn the light of these two categories, we have the following table:\r\n\r\n|                 | Insertion | Update<sup>†</sup> | Deletion |\r\n|-----------------|:-----------:|:--------:|:----------:|\r\n| __Previous  Pages__ |     *PI*     |   *PU*    |    *PD*      |\r\n| __Next Pages__      |     *NI*      |    *NU*    |     *ND*     |\r\n\r\nThe first letter of the case indicates in which page the modified row lies in<sup>†</sup> (*i.e.* previous/next), and the second letter indicates the type of the modification/mutation. For example, *ND* indicates that a row in one of the next pages has been deleted.\r\n\r\n†: We must beware that as a result of an Update operation might affect multiple pages! For instance, when sorted by the number of seeds (`n_seeders`), the same torrent might appear in multiple pages if its statistics were updated during the search. These cases are not represented by any of the symbols in our table.\r\n\r\n## Proposal\r\nI suggest adding a `has_readme` column to the torrents table, for the sake of convenience & performance, instead of running a sub-query again and again.\r\n\r\nI suggest adding an `modified_on` column to the `torrents` table, which is a UNIX timestamp that indicates the last time the entry in modified.\r\n\r\n- For newly added torrents, `modified_on` must be equal to `discovered_on`;\r\n- Whenever a torrent's seeder/leecher statistics are updated, `modified_on` must be equal to `updated_on`;\r\n- Whenever `has_readme` is updated, `modified_on` must be set accordingly.\r\n\r\nIn other words, whenever the attributes of a torrent changes, we must update `modified_on` accordingly. Lastly, a new `modified_on` value must always be greater than or equal to the value it replaces (*duh*). \r\n\r\nI also suggest adding an `epoch` parameter in each search query, which is a UNIX timestamp, to serve one rule:\r\n\r\n- No torrent whose `modified_on` is greater than the `epoch` will be included in the results (*i.e.* `modified_on` (must be) <= `epoch` (for torrent to be included in the results)).\r\n\r\nBy adding the `epoch` parameter, we handle the following cases without further consideration:\r\n\r\n- †\r\n- All insertion cases (*PI* & *NI*)\r\n- All update cases (*PU* & *NU*)\r\n\r\nThere are only two deletion cases left that we need to consider: *PD* and *ND*. Since we are using keyset pagination, deletions in the previous pages cannot disrupt an ongoing search, although the user will receive an error while accessing a deleted torrent (which would be still listed on the results page). And a deletion in the next pages have no harm at all, since they wouldn't even be aware of them.\r\n\r\nHence our query would look like this:\r\n\r\n```sql\r\nSELECT info_hash\r\n     , name\r\n     , total_size\r\n     , discovered_on\r\n     , updated_on\r\n     , n_seeders\r\n     , n_leechers\r\nFROM torrents\r\nINNER JOIN (\r\n    SELECT docid AS id\r\n         , rank(matchinfo(fts_torrents, 'pcnxal')) AS rank\r\n    FROM torrents_idx\r\n    WHERE name MATCH {QUERY}\r\n) AS idx USING(id)\r\nWHERE     modified_on    <= {EPOCH}\r\n      AND total_size     >= {LAST_TOTAL_SIZE}\r\n      AND id  < {LAST_ID}\r\nORDER BY total_size ASC, id DESC\r\nLIMIT 10\r\n;\r\n```\r\n\r\nHere we are searching torrents whose name matches a certain `{QUERY}`, ordered by their `total_size` in ascending order.\r\n\r\nWe are sorting once again by `id`, and in our query we compare each result's `id` with the `{LAST_ID}` to ensure that different results whose `total_size` are same but lies in different pages are not skipped.\r\n\r\n----\r\n\r\nAny questions?"}}
{"actor":{"display_login":"boramalper"}}