{"action":"created","comment":{"url":"https://api.github.com/repos/mantisbt/mantisbt/pulls/comments/48343215","id":48343215,"diff_hunk":"@@ -98,45 +98,49 @@ function file_get_display_name( $p_filename ) {\n }\n \n /**\n- * Check the number of attachments a bug has (if any)\n- * @param integer $p_bug_id A bug identifier.\n- * @return integer\n+ * Fills the cache with the attachement count from a list of bugs\n+ * using one sql query\n+ * @param array $p_bug_ids Array of bug ids\n  */\n-function file_bug_attachment_count( $p_bug_id ) {\n+function file_bug_attachment_count_cache( array $p_bug_ids ){\n \tglobal $g_cache_file_count;\n \n-\t# First check if we have a cache hit\n-\tif( isset( $g_cache_file_count[$p_bug_id] ) ) {\n-\t\treturn $g_cache_file_count[$p_bug_id];\n+\tif( null === $p_bug_ids || !is_array( $p_bug_ids ) ){\n+\t\treturn;\n \t}\n \n-\t# If there is no cache hit, check if there is anything in\n-\t#   the cache. If the cache isn't empty and we didn't have\n-\t#   a hit, then there are not attachments for this bug.\n-\tif( count( $g_cache_file_count ) > 0 ) {\n-\t\treturn 0;\n-\t}\n+\t$t_sql_list= '(' . implode( ', ', $p_bug_ids ) . ')';\n+\t$t_query = 'SELECT B.id AS bug_id, COUNT(F.bug_id) AS attachments'\n+\t\t\t. ' FROM {bug} B LEFT OUTER JOIN {bug_file} F'\n+\t\t\t. ' ON (B.id = F.bug_id)'\n+\t\t\t. ' WHERE B.id IN ' . $t_sql_list . ' GROUP BY B.id';\n ","path":"core/file_api.php","position":33,"original_position":33,"commit_id":"ef4f874dbd888eb9a5111bd32eac5d551cea1ade","original_commit_id":"ef4f874dbd888eb9a5111bd32eac5d551cea1ade","user":{"login":"cproensa","id":14123811,"avatar_url":"https://avatars.githubusercontent.com/u/14123811?v=3","gravatar_id":"","url":"https://api.github.com/users/cproensa","html_url":"https://github.com/cproensa","followers_url":"https://api.github.com/users/cproensa/followers","following_url":"https://api.github.com/users/cproensa/following{/other_user}","gists_url":"https://api.github.com/users/cproensa/gists{/gist_id}","starred_url":"https://api.github.com/users/cproensa/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cproensa/subscriptions","organizations_url":"https://api.github.com/users/cproensa/orgs","repos_url":"https://api.github.com/users/cproensa/repos","events_url":"https://api.github.com/users/cproensa/events{/privacy}","received_events_url":"https://api.github.com/users/cproensa/received_events","type":"User","site_admin":false},"body":"> There is a limit in how many items can fit in (especially with Oracle DB), which will lead to errors when executing the code on large data sets\r\n\r\nRight.\r\nThis is already an issue with bugnote count (see #696), with the current functions.\r\nAnd probably more places: retrieving projects, cache bug rows, etc...\r\n\r\nIn standard usage (with current code base): bug listings, are limited to pagination size, so this is not practical problem (but still it exists and waiting to bite us is some corner cases)\r\nA real use case where a massive enough bug list may trigger the fail situation would be CSV/Excel exports, which currently has disabled the inclusion of these two fields (again, #696)\r\n\r\nSo even if this would not result in fail, with current use cases, I agree to fix first the cache functions to address the long IN failure\r\nSo in order to get this done, i'd like some feedback on what proposed methods would be convenient to use, to provide a uniform approach on other similar situations. Brainstorming, some possibilities:\r\n- loop through the array partitioned in pieces of 500 item size, performing ~N/500 queries. (or any other convenient chunk size)\r\n- use a temporary table to store values and join.\r\n- ¿any other?\r\n\r\n\r\n\r\n","created_at":"2015-12-23T13:04:35Z","updated_at":"2015-12-23T13:04:35Z","html_url":"https://github.com/mantisbt/mantisbt/pull/654#discussion_r48343215","pull_request_url":"https://api.github.com/repos/mantisbt/mantisbt/pulls/654","_links":{"self":{"href":"https://api.github.com/repos/mantisbt/mantisbt/pulls/comments/48343215"},"html":{"href":"https://github.com/mantisbt/mantisbt/pull/654#discussion_r48343215"},"pull_request":{"href":"https://api.github.com/repos/mantisbt/mantisbt/pulls/654"}}},"pull_request":{"url":"https://api.github.com/repos/mantisbt/mantisbt/pulls/654","id":46064802,"html_url":"https://github.com/mantisbt/mantisbt/pull/654","diff_url":"https://github.com/mantisbt/mantisbt/pull/654.diff","patch_url":"https://github.com/mantisbt/mantisbt/pull/654.patch","issue_url":"https://api.github.com/repos/mantisbt/mantisbt/issues/654","number":654,"state":"open","locked":false,"title":"use selective bug cache for attachment count","user":{"login":"cproensa","id":14123811,"avatar_url":"https://avatars.githubusercontent.com/u/14123811?v=3","gravatar_id":"","url":"https://api.github.com/users/cproensa","html_url":"https://github.com/cproensa","followers_url":"https://api.github.com/users/cproensa/followers","following_url":"https://api.github.com/users/cproensa/following{/other_user}","gists_url":"https://api.github.com/users/cproensa/gists{/gist_id}","starred_url":"https://api.github.com/users/cproensa/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cproensa/subscriptions","organizations_url":"https://api.github.com/users/cproensa/orgs","repos_url":"https://api.github.com/users/cproensa/repos","events_url":"https://api.github.com/users/cproensa/events{/privacy}","received_events_url":"https://api.github.com/users/cproensa/received_events","type":"User","site_admin":false},"body":"use selective bug cache for attachment count instead of cache data for all bugs in db\r\nfetch data only for bugs included in filter result set\r\nfixes #0020138\r\n\r\nThe problem identified in bug #0020138, is that caching the attachment count query data for all bugs in database. This is unnecessary, and can penalize in db time, processing time and memory usage.\r\n\r\nModification uses the filter bug list and caches all of its values, only for that set. Usually all bugs accessed are those from the filter result, so subsequent calls to file_bug_attachment_count will hit the cache successfully. In other case, a single query will be issued.\r\n\r\nTypical scenario will perform one query in view_all_bug_page, and several queries in my_view_page (one for each filter box). Hopefully the queries and its processing are more lightweight that original approach.\r\n","created_at":"2015-09-25T22:28:23Z","updated_at":"2015-12-23T13:04:35Z","closed_at":null,"merged_at":null,"merge_commit_sha":"ebc66f92745ae27cab42945f0876109c2983dd73","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/mantisbt/mantisbt/pulls/654/commits","review_comments_url":"https://api.github.com/repos/mantisbt/mantisbt/pulls/654/comments","review_comment_url":"https://api.github.com/repos/mantisbt/mantisbt/pulls/comments{/number}","comments_url":"https://api.github.com/repos/mantisbt/mantisbt/issues/654/comments","statuses_url":"https://api.github.com/repos/mantisbt/mantisbt/statuses/ef4f874dbd888eb9a5111bd32eac5d551cea1ade","head":{"label":"cproensa:0020138_file_bug_attachment_count","ref":"0020138_file_bug_attachment_count","sha":"ef4f874dbd888eb9a5111bd32eac5d551cea1ade","user":{"login":"cproensa","id":14123811,"avatar_url":"https://avatars.githubusercontent.com/u/14123811?v=3","gravatar_id":"","url":"https://api.github.com/users/cproensa","html_url":"https://github.com/cproensa","followers_url":"https://api.github.com/users/cproensa/followers","following_url":"https://api.github.com/users/cproensa/following{/other_user}","gists_url":"https://api.github.com/users/cproensa/gists{/gist_id}","starred_url":"https://api.github.com/users/cproensa/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cproensa/subscriptions","organizations_url":"https://api.github.com/users/cproensa/orgs","repos_url":"https://api.github.com/users/cproensa/repos","events_url":"https://api.github.com/users/cproensa/events{/privacy}","received_events_url":"https://api.github.com/users/cproensa/received_events","type":"User","site_admin":false},"repo":{"id":42000375,"name":"mantisbt","full_name":"cproensa/mantisbt","owner":{"login":"cproensa","id":14123811,"avatar_url":"https://avatars.githubusercontent.com/u/14123811?v=3","gravatar_id":"","url":"https://api.github.com/users/cproensa","html_url":"https://github.com/cproensa","followers_url":"https://api.github.com/users/cproensa/followers","following_url":"https://api.github.com/users/cproensa/following{/other_user}","gists_url":"https://api.github.com/users/cproensa/gists{/gist_id}","starred_url":"https://api.github.com/users/cproensa/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cproensa/subscriptions","organizations_url":"https://api.github.com/users/cproensa/orgs","repos_url":"https://api.github.com/users/cproensa/repos","events_url":"https://api.github.com/users/cproensa/events{/privacy}","received_events_url":"https://api.github.com/users/cproensa/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/cproensa/mantisbt","description":"Mantis Bug Tracker (MantisBT)","fork":true,"url":"https://api.github.com/repos/cproensa/mantisbt","forks_url":"https://api.github.com/repos/cproensa/mantisbt/forks","keys_url":"https://api.github.com/repos/cproensa/mantisbt/keys{/key_id}","collaborators_url":"https://api.github.com/repos/cproensa/mantisbt/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/cproensa/mantisbt/teams","hooks_url":"https://api.github.com/repos/cproensa/mantisbt/hooks","issue_events_url":"https://api.github.com/repos/cproensa/mantisbt/issues/events{/number}","events_url":"https://api.github.com/repos/cproensa/mantisbt/events","assignees_url":"https://api.github.com/repos/cproensa/mantisbt/assignees{/user}","branches_url":"https://api.github.com/repos/cproensa/mantisbt/branches{/branch}","tags_url":"https://api.github.com/repos/cproensa/mantisbt/tags","blobs_url":"https://api.github.com/repos/cproensa/mantisbt/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/cproensa/mantisbt/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/cproensa/mantisbt/git/refs{/sha}","trees_url":"https://api.github.com/repos/cproensa/mantisbt/git/trees{/sha}","statuses_url":"https://api.github.com/repos/cproensa/mantisbt/statuses/{sha}","languages_url":"https://api.github.com/repos/cproensa/mantisbt/languages","stargazers_url":"https://api.github.com/repos/cproensa/mantisbt/stargazers","contributors_url":"https://api.github.com/repos/cproensa/mantisbt/contributors","subscribers_url":"https://api.github.com/repos/cproensa/mantisbt/subscribers","subscription_url":"https://api.github.com/repos/cproensa/mantisbt/subscription","commits_url":"https://api.github.com/repos/cproensa/mantisbt/commits{/sha}","git_commits_url":"https://api.github.com/repos/cproensa/mantisbt/git/commits{/sha}","comments_url":"https://api.github.com/repos/cproensa/mantisbt/comments{/number}","issue_comment_url":"https://api.github.com/repos/cproensa/mantisbt/issues/comments{/number}","contents_url":"https://api.github.com/repos/cproensa/mantisbt/contents/{+path}","compare_url":"https://api.github.com/repos/cproensa/mantisbt/compare/{base}...{head}","merges_url":"https://api.github.com/repos/cproensa/mantisbt/merges","archive_url":"https://api.github.com/repos/cproensa/mantisbt/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/cproensa/mantisbt/downloads","issues_url":"https://api.github.com/repos/cproensa/mantisbt/issues{/number}","pulls_url":"https://api.github.com/repos/cproensa/mantisbt/pulls{/number}","milestones_url":"https://api.github.com/repos/cproensa/mantisbt/milestones{/number}","notifications_url":"https://api.github.com/repos/cproensa/mantisbt/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/cproensa/mantisbt/labels{/name}","releases_url":"https://api.github.com/repos/cproensa/mantisbt/releases{/id}","created_at":"2015-09-06T11:36:59Z","updated_at":"2015-11-22T10:29:20Z","pushed_at":"2015-12-22T09:10:06Z","git_url":"git://github.com/cproensa/mantisbt.git","ssh_url":"git@github.com:cproensa/mantisbt.git","clone_url":"https://github.com/cproensa/mantisbt.git","svn_url":"https://github.com/cproensa/mantisbt","homepage":"http://www.mantisbt.org","size":48688,"stargazers_count":0,"watchers_count":0,"language":"PHP","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"mantisbt:master","ref":"master","sha":"9fcdd066f78ad22f2b644a3fe7d08b4bea77187d","user":{"login":"mantisbt","id":318027,"avatar_url":"https://avatars.githubusercontent.com/u/318027?v=3","gravatar_id":"","url":"https://api.github.com/users/mantisbt","html_url":"https://github.com/mantisbt","followers_url":"https://api.github.com/users/mantisbt/followers","following_url":"https://api.github.com/users/mantisbt/following{/other_user}","gists_url":"https://api.github.com/users/mantisbt/gists{/gist_id}","starred_url":"https://api.github.com/users/mantisbt/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mantisbt/subscriptions","organizations_url":"https://api.github.com/users/mantisbt/orgs","repos_url":"https://api.github.com/users/mantisbt/repos","events_url":"https://api.github.com/users/mantisbt/events{/privacy}","received_events_url":"https://api.github.com/users/mantisbt/received_events","type":"Organization","site_admin":false},"repo":{"id":747755,"name":"mantisbt","full_name":"mantisbt/mantisbt","owner":{"login":"mantisbt","id":318027,"avatar_url":"https://avatars.githubusercontent.com/u/318027?v=3","gravatar_id":"","url":"https://api.github.com/users/mantisbt","html_url":"https://github.com/mantisbt","followers_url":"https://api.github.com/users/mantisbt/followers","following_url":"https://api.github.com/users/mantisbt/following{/other_user}","gists_url":"https://api.github.com/users/mantisbt/gists{/gist_id}","starred_url":"https://api.github.com/users/mantisbt/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mantisbt/subscriptions","organizations_url":"https://api.github.com/users/mantisbt/orgs","repos_url":"https://api.github.com/users/mantisbt/repos","events_url":"https://api.github.com/users/mantisbt/events{/privacy}","received_events_url":"https://api.github.com/users/mantisbt/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/mantisbt/mantisbt","description":"Mantis Bug Tracker (MantisBT)","fork":false,"url":"https://api.github.com/repos/mantisbt/mantisbt","forks_url":"https://api.github.com/repos/mantisbt/mantisbt/forks","keys_url":"https://api.github.com/repos/mantisbt/mantisbt/keys{/key_id}","collaborators_url":"https://api.github.com/repos/mantisbt/mantisbt/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/mantisbt/mantisbt/teams","hooks_url":"https://api.github.com/repos/mantisbt/mantisbt/hooks","issue_events_url":"https://api.github.com/repos/mantisbt/mantisbt/issues/events{/number}","events_url":"https://api.github.com/repos/mantisbt/mantisbt/events","assignees_url":"https://api.github.com/repos/mantisbt/mantisbt/assignees{/user}","branches_url":"https://api.github.com/repos/mantisbt/mantisbt/branches{/branch}","tags_url":"https://api.github.com/repos/mantisbt/mantisbt/tags","blobs_url":"https://api.github.com/repos/mantisbt/mantisbt/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/mantisbt/mantisbt/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/mantisbt/mantisbt/git/refs{/sha}","trees_url":"https://api.github.com/repos/mantisbt/mantisbt/git/trees{/sha}","statuses_url":"https://api.github.com/repos/mantisbt/mantisbt/statuses/{sha}","languages_url":"https://api.github.com/repos/mantisbt/mantisbt/languages","stargazers_url":"https://api.github.com/repos/mantisbt/mantisbt/stargazers","contributors_url":"https://api.github.com/repos/mantisbt/mantisbt/contributors","subscribers_url":"https://api.github.com/repos/mantisbt/mantisbt/subscribers","subscription_url":"https://api.github.com/repos/mantisbt/mantisbt/subscription","commits_url":"https://api.github.com/repos/mantisbt/mantisbt/commits{/sha}","git_commits_url":"https://api.github.com/repos/mantisbt/mantisbt/git/commits{/sha}","comments_url":"https://api.github.com/repos/mantisbt/mantisbt/comments{/number}","issue_comment_url":"https://api.github.com/repos/mantisbt/mantisbt/issues/comments{/number}","contents_url":"https://api.github.com/repos/mantisbt/mantisbt/contents/{+path}","compare_url":"https://api.github.com/repos/mantisbt/mantisbt/compare/{base}...{head}","merges_url":"https://api.github.com/repos/mantisbt/mantisbt/merges","archive_url":"https://api.github.com/repos/mantisbt/mantisbt/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/mantisbt/mantisbt/downloads","issues_url":"https://api.github.com/repos/mantisbt/mantisbt/issues{/number}","pulls_url":"https://api.github.com/repos/mantisbt/mantisbt/pulls{/number}","milestones_url":"https://api.github.com/repos/mantisbt/mantisbt/milestones{/number}","notifications_url":"https://api.github.com/repos/mantisbt/mantisbt/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/mantisbt/mantisbt/labels{/name}","releases_url":"https://api.github.com/repos/mantisbt/mantisbt/releases{/id}","created_at":"2010-06-29T21:42:07Z","updated_at":"2015-12-22T10:25:59Z","pushed_at":"2015-12-23T11:24:06Z","git_url":"git://github.com/mantisbt/mantisbt.git","ssh_url":"git@github.com:mantisbt/mantisbt.git","clone_url":"https://github.com/mantisbt/mantisbt.git","svn_url":"https://github.com/mantisbt/mantisbt","homepage":"http://www.mantisbt.org","size":74103,"stargazers_count":541,"watchers_count":541,"language":"PHP","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":280,"mirror_url":null,"open_issues_count":61,"forks":280,"open_issues":61,"watchers":541,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/mantisbt/mantisbt/pulls/654"},"html":{"href":"https://github.com/mantisbt/mantisbt/pull/654"},"issue":{"href":"https://api.github.com/repos/mantisbt/mantisbt/issues/654"},"comments":{"href":"https://api.github.com/repos/mantisbt/mantisbt/issues/654/comments"},"review_comments":{"href":"https://api.github.com/repos/mantisbt/mantisbt/pulls/654/comments"},"review_comment":{"href":"https://api.github.com/repos/mantisbt/mantisbt/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/mantisbt/mantisbt/pulls/654/commits"},"statuses":{"href":"https://api.github.com/repos/mantisbt/mantisbt/statuses/ef4f874dbd888eb9a5111bd32eac5d551cea1ade"}}}}