{"action":"closed","issue":{"url":"https://api.github.com/repos/MyWebIntelligence/MyWebIntelligence/issues/134","labels_url":"https://api.github.com/repos/MyWebIntelligence/MyWebIntelligence/issues/134/labels{/name}","comments_url":"https://api.github.com/repos/MyWebIntelligence/MyWebIntelligence/issues/134/comments","events_url":"https://api.github.com/repos/MyWebIntelligence/MyWebIntelligence/issues/134/events","html_url":"https://github.com/MyWebIntelligence/MyWebIntelligence/issues/134","id":77054601,"number":134,"title":"Using abstract ids for expressions/aliases/references (instead of URLs)","user":{"login":"DavidBruant","id":165829,"avatar_url":"https://avatars.githubusercontent.com/u/165829?v=3","gravatar_id":"","url":"https://api.github.com/users/DavidBruant","html_url":"https://github.com/DavidBruant","followers_url":"https://api.github.com/users/DavidBruant/followers","following_url":"https://api.github.com/users/DavidBruant/following{/other_user}","gists_url":"https://api.github.com/users/DavidBruant/gists{/gist_id}","starred_url":"https://api.github.com/users/DavidBruant/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DavidBruant/subscriptions","organizations_url":"https://api.github.com/users/DavidBruant/orgs","repos_url":"https://api.github.com/users/DavidBruant/repos","events_url":"https://api.github.com/users/DavidBruant/events{/privacy}","received_events_url":"https://api.github.com/users/DavidBruant/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2015-05-16T14:48:20Z","updated_at":"2015-05-21T10:33:01Z","closed_at":"2015-05-21T10:33:01Z","body":"# Current situation\r\n\r\n[A single SQL table](https://github.com/MyWebIntelligence/MyWebIntelligence/blob/69a1948554c53f69a0d998b74f07b379f3d0f5c9/postgresDB/createTables.sql#L3-L12) is used to store the entire sub-web we save for a given territoire.\r\n\r\nAn Expression has a numerical id, but is more closely identified by its `uri`. `aliases` list all alias URIs (redirects for now, meta-canonical and other cases eventually). `references` links all **uris** being linked to.\r\n\r\n## Recreating the graph\r\n\r\nQueryResults are stored as a list of URLs returned by the oracle. This is on purpose. It's a good thing for historization and else to save things as they are returned by the oracles with no modification whatsoever.\r\n\r\nCurrently, these first URLs are used to get the first set of Expressions by comparisons with the `uri` and `aliases` fields. The `uri` can benefit from being indexed. The `aliases` array might be indexable for O(log(n)) searches but may require non-trivial work (apparently, B-Trees don't work, because they're a whole-value thing. There exists other type of indexes that may be better suited for arrays).\r\n\r\nAfter the first expressions are acquired, a set of the URLs they `reference` is built and the Expression table is queried again by `uri`+`aliases`, etc. until natural exhaustion.\r\n\r\n## Downsides\r\n\r\nThe index on `uri` most likely does it job well. Not having a proper index on `aliases` is likely the cause of several-second graph extractions even when the number of expressions is in the 10000s\r\n\r\nMore work into finding the right index type could work.\r\n\r\n\r\n# Possible alternate plan\r\n\r\n* Keep QueryResults as they are\r\n* Make a table binding urls and an id either to an expression, or an alias\r\n* Make a table for references (source, target)\r\n\r\n```sql\r\nCREATE TABLE IF NOT EXISTS expressions (\r\n    id           SERIAL PRIMARY KEY,\r\n    main_html    text,\r\n    main_text    text,\r\n    title        varchar(2000),\r\n    meta_description   text\r\n);\r\n\r\nCREATE TABLE IF NOT EXISTS resources (\r\n    id          SERIAL PRIMARY KEY,\r\n    url         varchar(2000) UNIQUE NOT NULL,\r\n    alias_of    integer UNIQUE REFERENCES resources (id), -- nullable if it doesn't alias anything\r\n    expression  integer UNIQUE REFERENCES expressions (id) -- NULL initially and when uri is an alias\r\n)\r\n-- Per http://www.postgresql.org/docs/9.4/static/indexes-unique.html Postgresql already create all the necessary indices via the UNIQUE constraints\r\n\r\nCREATE TABLE IF NOT EXISTS references (\r\n    source      integer REFERENCES resources (id),\r\n    target      integer REFERENCES resources (id)\r\n)\r\n\r\n-- to find graph edges quickly\r\nCREATE INDEX references_from ON references (source);\r\n```\r\n\r\n* When query results come back\r\n    * (create a QueryResults entry with the URLs)\r\n    * create as many \"blank\" `resources` as there are URLs\r\n    * (create the getExpressionTasks)\r\n* When an expression is made from the network\r\n    * update the `resource` if the URL was an alias\r\n    * create a new `resource` if the URL was an alias\r\n    * create an `expression`, update the corresponding `resource.expression` column with the id\r\n    * create as many \"blank\" `resource` as there are new URLs in the expression links. Create corresponding `references` entries.\r\n* When getting the graph for a given territoire\r\n    * get all urls in corresponding QueryResults\r\n    * get all `resources` for these URLs (n*log(m) with `resources.url` UNIQUE index)\r\n        * If resource is an expression, find what it `references` (n*log(m) thanks to references_from index) (creates a new set of ids)\r\n        * If the resource is an alias, follow this alias (add to the new set of ids)\r\n        * If the resource is nothing, do nothing 'cause there is nothing to do\r\n    * iterate with the new id set\r\n    * On the server side, keep track of all URLs and ids, get rid of aliases eventually\r\n\r\nFor now, let's do the graph with multiple back-and-forth with the server. Should work well enough for now especially with all the new indices we get for free.\r\n\r\n"}}