{"action":"created","issue":{"url":"https://api.github.com/repos/almighty/almighty-core/issues/480","repository_url":"https://api.github.com/repos/almighty/almighty-core","labels_url":"https://api.github.com/repos/almighty/almighty-core/issues/480/labels{/name}","comments_url":"https://api.github.com/repos/almighty/almighty-core/issues/480/comments","events_url":"https://api.github.com/repos/almighty/almighty-core/issues/480/events","html_url":"https://github.com/almighty/almighty-core/issues/480","id":188705314,"number":480,"title":"Search Architecture","user":{"login":"tsmaeder","id":13163770,"avatar_url":"https://avatars.githubusercontent.com/u/13163770?v=3","gravatar_id":"","url":"https://api.github.com/users/tsmaeder","html_url":"https://github.com/tsmaeder","followers_url":"https://api.github.com/users/tsmaeder/followers","following_url":"https://api.github.com/users/tsmaeder/following{/other_user}","gists_url":"https://api.github.com/users/tsmaeder/gists{/gist_id}","starred_url":"https://api.github.com/users/tsmaeder/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tsmaeder/subscriptions","organizations_url":"https://api.github.com/users/tsmaeder/orgs","repos_url":"https://api.github.com/users/tsmaeder/repos","events_url":"https://api.github.com/users/tsmaeder/events{/privacy}","received_events_url":"https://api.github.com/users/tsmaeder/received_events","type":"User","site_admin":false},"labels":[{"id":423118855,"url":"https://api.github.com/repos/almighty/almighty-core/labels/architecture","name":"architecture","color":"b60205","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/almighty/almighty-core/milestones/7","html_url":"https://github.com/almighty/almighty-core/milestones/Sprint%20%23123","labels_url":"https://api.github.com/repos/almighty/almighty-core/milestones/7/labels","id":2028837,"number":7,"title":"Sprint #123","description":"Sprint Goals https://github.com/almighty/almighty-core/issues/472","creator":{"login":"maxandersen","id":54129,"avatar_url":"https://avatars.githubusercontent.com/u/54129?v=3","gravatar_id":"","url":"https://api.github.com/users/maxandersen","html_url":"https://github.com/maxandersen","followers_url":"https://api.github.com/users/maxandersen/followers","following_url":"https://api.github.com/users/maxandersen/following{/other_user}","gists_url":"https://api.github.com/users/maxandersen/gists{/gist_id}","starred_url":"https://api.github.com/users/maxandersen/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/maxandersen/subscriptions","organizations_url":"https://api.github.com/users/maxandersen/orgs","repos_url":"https://api.github.com/users/maxandersen/repos","events_url":"https://api.github.com/users/maxandersen/events{/privacy}","received_events_url":"https://api.github.com/users/maxandersen/received_events","type":"User","site_admin":false},"open_issues":27,"closed_issues":0,"state":"open","created_at":"2016-09-26T13:20:51Z","updated_at":"2016-11-11T08:02:41Z","due_on":"2016-11-29T00:00:00Z","closed_at":null},"comments":2,"created_at":"2016-11-11T08:02:40Z","updated_at":"2016-11-11T14:19:53Z","closed_at":null,"body":"From a post to almighty-public: \r\n\r\n>As we move to offer more search functionality in our API, I am becoming a bit worried about our architecture in that respect. If I look at the search service we have written for the \"link typeahead\" feature, we see code that parses a query string in a format that is special to this exact API call, and converts it directly into a query on the postgres db.\r\n\r\n>If we look at what the code does, it could be expressed like this:\r\n\r\n```\r\nsearchTerms:= parseSpecialSyntax(queryString)\r\n`return select * from work_items i where i.id matches(searchTearms) or i.title matches(searchTerms) or i.Description matches(searchTerms)\r\n```\r\n\r\n>While the first line is specific to this particular API call, the second part can be reused in other queries. I have tried to express this separation in the WorkItemRepository.List() method by passing in a tree of operators that describes the query instead of a query string.\r\n\r\n>I am quite open to discuss how we want to offer a search API on our object model to the request handlers, but I think that we need an internal search API layer that offers what we can search for in a structure way. Query parsing for special case queries (for example, parsing well known URLs) should not be part of this API, but done in a layer above. This way we can reuse a lot of checks (for example for sql injection) and the translation to the sql layer. I believe that we will write a bunch of special case queries that we will be able to map to a relatively simple set internal search API calls.\r\n\r\nThe purpose of this issue is to track this concern and to develop a proposal how to address it."},"comment":{"url":"https://api.github.com/repos/almighty/almighty-core/issues/comments/259966615","html_url":"https://github.com/almighty/almighty-core/issues/480#issuecomment-259966615","issue_url":"https://api.github.com/repos/almighty/almighty-core/issues/480","id":259966615,"user":{"login":"tsmaeder","id":13163770,"avatar_url":"https://avatars.githubusercontent.com/u/13163770?v=3","gravatar_id":"","url":"https://api.github.com/users/tsmaeder","html_url":"https://github.com/tsmaeder","followers_url":"https://api.github.com/users/tsmaeder/followers","following_url":"https://api.github.com/users/tsmaeder/following{/other_user}","gists_url":"https://api.github.com/users/tsmaeder/gists{/gist_id}","starred_url":"https://api.github.com/users/tsmaeder/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tsmaeder/subscriptions","organizations_url":"https://api.github.com/users/tsmaeder/orgs","repos_url":"https://api.github.com/users/tsmaeder/repos","events_url":"https://api.github.com/users/tsmaeder/events{/privacy}","received_events_url":"https://api.github.com/users/tsmaeder/received_events","type":"User","site_admin":false},"created_at":"2016-11-11T14:19:53Z","updated_at":"2016-11-11T14:19:53Z","body":"Here are some query execution times: \r\n```\r\nexplain (analyze, costs) select fields->'system.title' from work_items where to_tsvector('english', fields->>'system.title') @@ to_tsquery('english', 'NullPointerException') or to_tsvector('english', fields->>'system.description') @@ to_tsquery('english', 'NullPointerException') or to_tsvector('english', id::text) @@ to_tsquery('english', 'NullPointerException)\r\n\r\nBitmap Heap Scan on work_items  (cost=68.34..7104.24 rows=1972 width=510) (actual time=0.045..0.172 rows=50 loops=1)\r\n   Recheck Cond: ((to_tsvector('english'::regconfig, (fields ->> 'system.title'::text)) @@ '''nullpointerexcept'''::tsquery) OR (to_tsvector('english'::regconfig, (fields ->> 'system.description'::text)) @@ '''nullpointerexcept'''::tsquery) OR (to_tsvector('english'::regconfig, (id)::text) @@ '''nullpointerexcept'''::tsquery))\r\n   Heap Blocks: exact=43\r\n   ->  BitmapOr  (cost=68.34..68.34 rows=1982 width=0) (actual time=0.033..0.033 rows=0 loops=1)\r\n         ->  Bitmap Index Scan on wi_title_ftidx  (cost=0.00..20.95 rows=661 width=0) (actual time=0.021..0.021 rows=42 loops=1)\r\n               Index Cond: (to_tsvector('english'::regconfig, (fields ->> 'system.title'::text)) @@ '''nullpointerexcept'''::tsquery)\r\n         ->  Bitmap Index Scan on wi_des_ftidx  (cost=0.00..24.95 rows=661 width=0) (actual time=0.008..0.008 rows=12 loops=1)\r\n               Index Cond: (to_tsvector('english'::regconfig, (fields ->> 'system.description'::text)) @@ '''nullpointerexcept'''::tsquery)\r\n         ->  Bitmap Index Scan on wi_id_ftidx  (cost=0.00..20.95 rows=661 width=0) (actual time=0.004..0.004 rows=0 loops=1)\r\n               Index Cond: (to_tsvector('english'::regconfig, (id)::text) @@ '''nullpointerexcept'''::tsquery)\r\n Planning time: 0.160 ms\r\n Execution time: 0.205 ms\r\n\r\nexplain (analyze, costs) select fields->>'title' from work_items where tsv @@ to_tsquery('english', 'NullPointerException');\r\n Bitmap Heap Scan on work_items  (cost=170.34..1248.40 rows=302 width=510) (actual time=0.275..0.405 rows=50 loops=1)\r\n   Recheck Cond: (tsv @@ '''nullpointerexcept'''::tsquery)\r\n   Heap Blocks: exact=43\r\n   ->  Bitmap Index Scan on fulltext_search_index  (cost=0.00..170.26 rows=302 width=0) (actual time=0.264..0.264 rows=50 loops=1)\r\n         Index Cond: (tsv @@ '''nullpointerexcept'''::tsquery)\r\n Planning time: 0.194 ms\r\n Execution time: 0.429 ms\r\n```\r\nIt is interesting to observe that the query for individual fields is faster than the query for the combined field. "}}
{"actor":{"display_login":"tsmaeder"}}