{"action":"closed","number":2012,"pull_request":{"url":"https://api.github.com/repos/qgis/QGIS/pulls/2012","id":34203217,"html_url":"https://github.com/qgis/QGIS/pull/2012","diff_url":"https://github.com/qgis/QGIS/pull/2012.diff","patch_url":"https://github.com/qgis/QGIS/pull/2012.patch","issue_url":"https://api.github.com/repos/qgis/QGIS/issues/2012","number":2012,"state":"closed","locked":false,"title":"Add expression compiler for postgres","user":{"login":"m-kuhn","id":588407,"avatar_url":"https://avatars.githubusercontent.com/u/588407?v=3","gravatar_id":"","url":"https://api.github.com/users/m-kuhn","html_url":"https://github.com/m-kuhn","followers_url":"https://api.github.com/users/m-kuhn/followers","following_url":"https://api.github.com/users/m-kuhn/following{/other_user}","gists_url":"https://api.github.com/users/m-kuhn/gists{/gist_id}","starred_url":"https://api.github.com/users/m-kuhn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/m-kuhn/subscriptions","organizations_url":"https://api.github.com/users/m-kuhn/orgs","repos_url":"https://api.github.com/users/m-kuhn/repos","events_url":"https://api.github.com/users/m-kuhn/events{/privacy}","received_events_url":"https://api.github.com/users/m-kuhn/received_events","type":"User","site_admin":false},"body":"**This is a preliminary prototype pull request which is not ready for merging. Do only look into it if you would like to discuss this topic on a theoretical / technical / architectural level.**\r\n\r\nBasics\r\n======\r\n\r\nThis pull request allows to filter features already on server side hence making use of server side indexes, (stronger hardware) and reducing network load. This can result in a *huge* performance gain in certain scenarios (requesting few features of a big table). Only supported expressions will be sent to the database. Expressions using unsupported operators or functions will gracefully fallback to local evaluation.\r\n\r\nTo make use of this feature\r\n\r\n * Enable it in options: data sources\r\n * QgsFeatureRequest().setFilterExpression( expression )\r\n * or QgsVectorLayer::getFeatures( expression )\r\n\r\nTesting\r\n======\r\n\r\nTo be able to develop pragmatically testing for postgres server was introduced. Travis now brings up a postgres (9.1) with postgis (2.1) server and the unit tests run against that. The expressions are executed with and without compiler against that dataset so it can be compared that the same features are filtered, hence the compiler generates a suitable result. This will make it very easy to just let the same compiler run for other databases and test it with the same dataset on other backends.\r\n\r\nVisitor pattern\r\n===========\r\n\r\nIt does not make use of the visitor pattern. The main issue with the visitor pattern is, that there is no way to return something from a visited node (return type is void). It may be possible to overcome this by making the return type a `QVariant` (or keeping somehow track of the currently visited node in the visitor class but that's pretty hacky).\r\nIf that's done, there's still a (possible) problem of not having context information about the currently visited node. I do not know if this will be a requirement but it's worth considering. It would probably be possible to keep track of that in the visitor class.\r\nThe current solution works quite nicely so I am not yet convinced that using the visitor pattern will have benefits. Please prove me wrong :)\r\n\r\nFeature requests with FilterRect\r\n==========\r\n\r\nA `QgsFeatureRequest` can either contain a FilterRect or a FilterExpression but not both (mutually exclusive). FilterRects are pretty well supported already by most providers and sent to the backend. Expressions not (yet). Sometimes a combination of these two filter types may come in handy (In particular in the rule based renderer where you may filter with an expression but also the currently visible extent should be used as filter criteria).\r\n\r\nThe FilterRect could be translated to an expression like `category == 2 AND geom INTERSECTS( visibleextent )`. On most providers this will result in fetching all features and filtering them with the expression engine and thus a considerable (unacceptable) performance impact. So in order to go with that solution one would need to try to compile the expression and only if a successful compilation has been reported the FilterExpression should be set and in any other case the FilterRect should be set.\r\n\r\nThe FilterRect could be changed so it will not be handled mutually exclusive but additionally. This will result in having to rewrite the logic in all the providers to handle that properly. If the FilterRect isNull() it will not be considered, if it is set it will be appended to the WHERE clause with an AND. This is a change in semantics that will probably not affect many things (as hardly ever somebody will have set two filters on a request, because so far only one was considered anyway...).\r\n\r\nAcknowledgements\r\n==========\r\n\r\nThis work has been kindly supported by the Swiss QGIS User Group and SIGE","created_at":"2015-04-27T19:47:40Z","updated_at":"2015-05-21T14:04:19Z","closed_at":"2015-05-21T14:04:19Z","merged_at":null,"merge_commit_sha":"37854e6a4715bb1b361841c5d6d85ffaeae53d0c","assignee":{"login":"m-kuhn","id":588407,"avatar_url":"https://avatars.githubusercontent.com/u/588407?v=3","gravatar_id":"","url":"https://api.github.com/users/m-kuhn","html_url":"https://github.com/m-kuhn","followers_url":"https://api.github.com/users/m-kuhn/followers","following_url":"https://api.github.com/users/m-kuhn/following{/other_user}","gists_url":"https://api.github.com/users/m-kuhn/gists{/gist_id}","starred_url":"https://api.github.com/users/m-kuhn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/m-kuhn/subscriptions","organizations_url":"https://api.github.com/users/m-kuhn/orgs","repos_url":"https://api.github.com/users/m-kuhn/repos","events_url":"https://api.github.com/users/m-kuhn/events{/privacy}","received_events_url":"https://api.github.com/users/m-kuhn/received_events","type":"User","site_admin":false},"milestone":null,"commits_url":"https://api.github.com/repos/qgis/QGIS/pulls/2012/commits","review_comments_url":"https://api.github.com/repos/qgis/QGIS/pulls/2012/comments","review_comment_url":"https://api.github.com/repos/qgis/QGIS/pulls/comments{/number}","comments_url":"https://api.github.com/repos/qgis/QGIS/issues/2012/comments","statuses_url":"https://api.github.com/repos/qgis/QGIS/statuses/aa0c23013d215b9b4f152d1d1c9482fe4fad213d","head":{"label":"m-kuhn:expcomp","ref":"expcomp","sha":"aa0c23013d215b9b4f152d1d1c9482fe4fad213d","user":{"login":"m-kuhn","id":588407,"avatar_url":"https://avatars.githubusercontent.com/u/588407?v=3","gravatar_id":"","url":"https://api.github.com/users/m-kuhn","html_url":"https://github.com/m-kuhn","followers_url":"https://api.github.com/users/m-kuhn/followers","following_url":"https://api.github.com/users/m-kuhn/following{/other_user}","gists_url":"https://api.github.com/users/m-kuhn/gists{/gist_id}","starred_url":"https://api.github.com/users/m-kuhn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/m-kuhn/subscriptions","organizations_url":"https://api.github.com/users/m-kuhn/orgs","repos_url":"https://api.github.com/users/m-kuhn/repos","events_url":"https://api.github.com/users/m-kuhn/events{/privacy}","received_events_url":"https://api.github.com/users/m-kuhn/received_events","type":"User","site_admin":false},"repo":{"id":5270567,"name":"QGIS","full_name":"m-kuhn/QGIS","owner":{"login":"m-kuhn","id":588407,"avatar_url":"https://avatars.githubusercontent.com/u/588407?v=3","gravatar_id":"","url":"https://api.github.com/users/m-kuhn","html_url":"https://github.com/m-kuhn","followers_url":"https://api.github.com/users/m-kuhn/followers","following_url":"https://api.github.com/users/m-kuhn/following{/other_user}","gists_url":"https://api.github.com/users/m-kuhn/gists{/gist_id}","starred_url":"https://api.github.com/users/m-kuhn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/m-kuhn/subscriptions","organizations_url":"https://api.github.com/users/m-kuhn/orgs","repos_url":"https://api.github.com/users/m-kuhn/repos","events_url":"https://api.github.com/users/m-kuhn/events{/privacy}","received_events_url":"https://api.github.com/users/m-kuhn/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/m-kuhn/QGIS","description":"QGIS is a free, open source, cross platform (lin/win/mac) geographical information system (GIS)","fork":true,"url":"https://api.github.com/repos/m-kuhn/QGIS","forks_url":"https://api.github.com/repos/m-kuhn/QGIS/forks","keys_url":"https://api.github.com/repos/m-kuhn/QGIS/keys{/key_id}","collaborators_url":"https://api.github.com/repos/m-kuhn/QGIS/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/m-kuhn/QGIS/teams","hooks_url":"https://api.github.com/repos/m-kuhn/QGIS/hooks","issue_events_url":"https://api.github.com/repos/m-kuhn/QGIS/issues/events{/number}","events_url":"https://api.github.com/repos/m-kuhn/QGIS/events","assignees_url":"https://api.github.com/repos/m-kuhn/QGIS/assignees{/user}","branches_url":"https://api.github.com/repos/m-kuhn/QGIS/branches{/branch}","tags_url":"https://api.github.com/repos/m-kuhn/QGIS/tags","blobs_url":"https://api.github.com/repos/m-kuhn/QGIS/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/m-kuhn/QGIS/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/m-kuhn/QGIS/git/refs{/sha}","trees_url":"https://api.github.com/repos/m-kuhn/QGIS/git/trees{/sha}","statuses_url":"https://api.github.com/repos/m-kuhn/QGIS/statuses/{sha}","languages_url":"https://api.github.com/repos/m-kuhn/QGIS/languages","stargazers_url":"https://api.github.com/repos/m-kuhn/QGIS/stargazers","contributors_url":"https://api.github.com/repos/m-kuhn/QGIS/contributors","subscribers_url":"https://api.github.com/repos/m-kuhn/QGIS/subscribers","subscription_url":"https://api.github.com/repos/m-kuhn/QGIS/subscription","commits_url":"https://api.github.com/repos/m-kuhn/QGIS/commits{/sha}","git_commits_url":"https://api.github.com/repos/m-kuhn/QGIS/git/commits{/sha}","comments_url":"https://api.github.com/repos/m-kuhn/QGIS/comments{/number}","issue_comment_url":"https://api.github.com/repos/m-kuhn/QGIS/issues/comments{/number}","contents_url":"https://api.github.com/repos/m-kuhn/QGIS/contents/{+path}","compare_url":"https://api.github.com/repos/m-kuhn/QGIS/compare/{base}...{head}","merges_url":"https://api.github.com/repos/m-kuhn/QGIS/merges","archive_url":"https://api.github.com/repos/m-kuhn/QGIS/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/m-kuhn/QGIS/downloads","issues_url":"https://api.github.com/repos/m-kuhn/QGIS/issues{/number}","pulls_url":"https://api.github.com/repos/m-kuhn/QGIS/pulls{/number}","milestones_url":"https://api.github.com/repos/m-kuhn/QGIS/milestones{/number}","notifications_url":"https://api.github.com/repos/m-kuhn/QGIS/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/m-kuhn/QGIS/labels{/name}","releases_url":"https://api.github.com/repos/m-kuhn/QGIS/releases{/id}","created_at":"2012-08-02T09:19:28Z","updated_at":"2014-08-13T15:20:34Z","pushed_at":"2015-05-21T14:04:08Z","git_url":"git://github.com/m-kuhn/QGIS.git","ssh_url":"git@github.com:m-kuhn/QGIS.git","clone_url":"https://github.com/m-kuhn/QGIS.git","svn_url":"https://github.com/m-kuhn/QGIS","homepage":"http://qgis.org","size":1064934,"stargazers_count":2,"watchers_count":2,"language":"TypeScript","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":1,"mirror_url":null,"open_issues_count":1,"forks":1,"open_issues":1,"watchers":2,"default_branch":"master"}},"base":{"label":"qgis:master","ref":"master","sha":"7a35d62b09dcb6d8d8aeeff3d00c34b60eb4ac1c","user":{"login":"qgis","id":483444,"avatar_url":"https://avatars.githubusercontent.com/u/483444?v=3","gravatar_id":"","url":"https://api.github.com/users/qgis","html_url":"https://github.com/qgis","followers_url":"https://api.github.com/users/qgis/followers","following_url":"https://api.github.com/users/qgis/following{/other_user}","gists_url":"https://api.github.com/users/qgis/gists{/gist_id}","starred_url":"https://api.github.com/users/qgis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/qgis/subscriptions","organizations_url":"https://api.github.com/users/qgis/orgs","repos_url":"https://api.github.com/users/qgis/repos","events_url":"https://api.github.com/users/qgis/events{/privacy}","received_events_url":"https://api.github.com/users/qgis/received_events","type":"Organization","site_admin":false},"repo":{"id":1690480,"name":"QGIS","full_name":"qgis/QGIS","owner":{"login":"qgis","id":483444,"avatar_url":"https://avatars.githubusercontent.com/u/483444?v=3","gravatar_id":"","url":"https://api.github.com/users/qgis","html_url":"https://github.com/qgis","followers_url":"https://api.github.com/users/qgis/followers","following_url":"https://api.github.com/users/qgis/following{/other_user}","gists_url":"https://api.github.com/users/qgis/gists{/gist_id}","starred_url":"https://api.github.com/users/qgis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/qgis/subscriptions","organizations_url":"https://api.github.com/users/qgis/orgs","repos_url":"https://api.github.com/users/qgis/repos","events_url":"https://api.github.com/users/qgis/events{/privacy}","received_events_url":"https://api.github.com/users/qgis/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/qgis/QGIS","description":"QGIS is a free, open source, cross platform (lin/win/mac) geographical information system (GIS)","fork":false,"url":"https://api.github.com/repos/qgis/QGIS","forks_url":"https://api.github.com/repos/qgis/QGIS/forks","keys_url":"https://api.github.com/repos/qgis/QGIS/keys{/key_id}","collaborators_url":"https://api.github.com/repos/qgis/QGIS/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/qgis/QGIS/teams","hooks_url":"https://api.github.com/repos/qgis/QGIS/hooks","issue_events_url":"https://api.github.com/repos/qgis/QGIS/issues/events{/number}","events_url":"https://api.github.com/repos/qgis/QGIS/events","assignees_url":"https://api.github.com/repos/qgis/QGIS/assignees{/user}","branches_url":"https://api.github.com/repos/qgis/QGIS/branches{/branch}","tags_url":"https://api.github.com/repos/qgis/QGIS/tags","blobs_url":"https://api.github.com/repos/qgis/QGIS/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/qgis/QGIS/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/qgis/QGIS/git/refs{/sha}","trees_url":"https://api.github.com/repos/qgis/QGIS/git/trees{/sha}","statuses_url":"https://api.github.com/repos/qgis/QGIS/statuses/{sha}","languages_url":"https://api.github.com/repos/qgis/QGIS/languages","stargazers_url":"https://api.github.com/repos/qgis/QGIS/stargazers","contributors_url":"https://api.github.com/repos/qgis/QGIS/contributors","subscribers_url":"https://api.github.com/repos/qgis/QGIS/subscribers","subscription_url":"https://api.github.com/repos/qgis/QGIS/subscription","commits_url":"https://api.github.com/repos/qgis/QGIS/commits{/sha}","git_commits_url":"https://api.github.com/repos/qgis/QGIS/git/commits{/sha}","comments_url":"https://api.github.com/repos/qgis/QGIS/comments{/number}","issue_comment_url":"https://api.github.com/repos/qgis/QGIS/issues/comments{/number}","contents_url":"https://api.github.com/repos/qgis/QGIS/contents/{+path}","compare_url":"https://api.github.com/repos/qgis/QGIS/compare/{base}...{head}","merges_url":"https://api.github.com/repos/qgis/QGIS/merges","archive_url":"https://api.github.com/repos/qgis/QGIS/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/qgis/QGIS/downloads","issues_url":"https://api.github.com/repos/qgis/QGIS/issues{/number}","pulls_url":"https://api.github.com/repos/qgis/QGIS/pulls{/number}","milestones_url":"https://api.github.com/repos/qgis/QGIS/milestones{/number}","notifications_url":"https://api.github.com/repos/qgis/QGIS/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/qgis/QGIS/labels{/name}","releases_url":"https://api.github.com/repos/qgis/QGIS/releases{/id}","created_at":"2011-05-02T08:06:26Z","updated_at":"2015-05-21T08:46:10Z","pushed_at":"2015-05-21T14:04:10Z","git_url":"git://github.com/qgis/QGIS.git","ssh_url":"git@github.com:qgis/QGIS.git","clone_url":"https://github.com/qgis/QGIS.git","svn_url":"https://github.com/qgis/QGIS","homepage":"http://qgis.org","size":1181768,"stargazers_count":675,"watchers_count":675,"language":"C++","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":612,"mirror_url":null,"open_issues_count":58,"forks":612,"open_issues":58,"watchers":675,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/qgis/QGIS/pulls/2012"},"html":{"href":"https://github.com/qgis/QGIS/pull/2012"},"issue":{"href":"https://api.github.com/repos/qgis/QGIS/issues/2012"},"comments":{"href":"https://api.github.com/repos/qgis/QGIS/issues/2012/comments"},"review_comments":{"href":"https://api.github.com/repos/qgis/QGIS/pulls/2012/comments"},"review_comment":{"href":"https://api.github.com/repos/qgis/QGIS/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/qgis/QGIS/pulls/2012/commits"},"statuses":{"href":"https://api.github.com/repos/qgis/QGIS/statuses/aa0c23013d215b9b4f152d1d1c9482fe4fad213d"}},"merged":false,"mergeable":false,"mergeable_state":"dirty","merged_by":null,"comments":3,"review_comments":9,"commits":6,"additions":763,"deletions":104,"changed_files":18}}