{"action":"created","comment":{"url":"https://api.github.com/repos/home-assistant/home-assistant/pulls/comments/99216266","pull_request_review_id":19886752,"id":99216266,"diff_hunk":"@@ -305,8 +305,82 @@ def _setup_connection(self):\n         models.Base.metadata.create_all(self.engine)\n         session_factory = sessionmaker(bind=self.engine)\n         Session = scoped_session(session_factory)\n+        self._check_schema()\n         self.db_ready.set()\n \n+    def _check_schema(self):\n+        \"\"\"Check if the schema needs to be upgraded.\"\"\"\n+        import homeassistant.components.recorder.models as models\n+        schema_changes = models.SchemaChanges\n+        current_version = getattr(Session.query(schema_changes).order_by(\n+            schema_changes.change_id.desc()).first(), 'schema_version', None)\n+\n+        if current_version == models.SCHEMA_VERSION:\n+            return\n+        _LOGGER.debug(\"Schema version incorrect: %d\", current_version)\n+\n+        if current_version is None:\n+            current_version = self._inspect_schema_version()\n+            _LOGGER.debug(\"No schema version found. Inspected version: %d\",\n+                          current_version)\n+\n+        for version in range(current_version, models.SCHEMA_VERSION):\n+            new_version = version + 1\n+            _LOGGER.info(\n+                \"Upgrading recorder db schema to version %d\", new_version)\n+            self._migrate_schema(new_version)\n+            self._commit(schema_changes(schema_version=new_version))\n+            _LOGGER.info(\n+                \"Upgraded recorder db schema to version %d\", new_version)\n+\n+    def _migrate_schema(self, new_version):\n+        \"\"\"Perform operations to bring schema up to date.\"\"\"\n+        from sqlalchemy import Index, Table\n+        import homeassistant.components.recorder.models as models\n+\n+        if new_version == 1:\n+            def create_index(table_name, column_name):\n+                \"\"\"Create an index for the specified table and column.\"\"\"\n+                table = Table(table_name, models.Base.metadata)\n+                index_name = \"_\".join((\"ix\", table_name, column_name))\n+                index = Index(index_name, getattr(table.c, column_name))\n+                _LOGGER.debug(\"Creating index for table %s column %s\",\n+                              table_name, column_name)\n+                index.create(self.engine)\n+                _LOGGER.debug(\"Index creation done for table %s column %s\",\n+                              table_name, column_name)\n+\n+            create_index(\"events\", \"time_fired\")\n+        else:\n+            raise ValueError(\"No schema migration defined for version {}\"\n+                             .format(new_version))\n+\n+    def _inspect_schema_version(self):","path":"homeassistant/components/recorder/__init__.py","position":54,"original_position":54,"commit_id":"a53d2ff05b50f2f3ef73610ba26e09c32059bfac","original_commit_id":"a53d2ff05b50f2f3ef73610ba26e09c32059bfac","user":{"login":"armills","id":1386547,"avatar_url":"https://avatars.githubusercontent.com/u/1386547?v=3","gravatar_id":"","url":"https://api.github.com/users/armills","html_url":"https://github.com/armills","followers_url":"https://api.github.com/users/armills/followers","following_url":"https://api.github.com/users/armills/following{/other_user}","gists_url":"https://api.github.com/users/armills/gists{/gist_id}","starred_url":"https://api.github.com/users/armills/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/armills/subscriptions","organizations_url":"https://api.github.com/users/armills/orgs","repos_url":"https://api.github.com/users/armills/repos","events_url":"https://api.github.com/users/armills/events{/privacy}","received_events_url":"https://api.github.com/users/armills/received_events","type":"User","site_admin":false},"body":"OK. I'll update the method name above and merge it tonight.","created_at":"2017-02-02T20:49:12Z","updated_at":"2017-02-02T20:49:12Z","html_url":"https://github.com/home-assistant/home-assistant/pull/5633#discussion_r99216266","pull_request_url":"https://api.github.com/repos/home-assistant/home-assistant/pulls/5633","_links":{"self":{"href":"https://api.github.com/repos/home-assistant/home-assistant/pulls/comments/99216266"},"html":{"href":"https://github.com/home-assistant/home-assistant/pull/5633#discussion_r99216266"},"pull_request":{"href":"https://api.github.com/repos/home-assistant/home-assistant/pulls/5633"}}},"pull_request":{"url":"https://api.github.com/repos/home-assistant/home-assistant/pulls/5633","id":103659538,"html_url":"https://github.com/home-assistant/home-assistant/pull/5633","diff_url":"https://github.com/home-assistant/home-assistant/pull/5633.diff","patch_url":"https://github.com/home-assistant/home-assistant/pull/5633.patch","issue_url":"https://api.github.com/repos/home-assistant/home-assistant/issues/5633","number":5633,"state":"open","locked":false,"title":"[recorder] Index events time_fired to improve logbook performance","user":{"login":"armills","id":1386547,"avatar_url":"https://avatars.githubusercontent.com/u/1386547?v=3","gravatar_id":"","url":"https://api.github.com/users/armills","html_url":"https://github.com/armills","followers_url":"https://api.github.com/users/armills/followers","following_url":"https://api.github.com/users/armills/following{/other_user}","gists_url":"https://api.github.com/users/armills/gists{/gist_id}","starred_url":"https://api.github.com/users/armills/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/armills/subscriptions","organizations_url":"https://api.github.com/users/armills/orgs","repos_url":"https://api.github.com/users/armills/repos","events_url":"https://api.github.com/users/armills/events{/privacy}","received_events_url":"https://api.github.com/users/armills/received_events","type":"User","site_admin":false},"body":"**Description:**\r\nThis PR adds an index to the time_fired column of the Events table to improve performance of the logbook lookup. I also didn't see any existing logic for schema migration. I've included logic here to add the index to existing databases. I don't have much SQL Alchemy experience though, so feel free to pick this code apart.\r\n\r\n**Numbers:**\r\nOn my system (spinning disk drives), the hass SQLite db is at 760MiB. Before this change, warm-cache queries for a logbook day took approximately 1.65 seconds. Adding the index raised the db size to 780MiB. With the index the same queries now take 0.55 seconds.","created_at":"2017-01-29T20:50:52Z","updated_at":"2017-02-02T20:49:12Z","closed_at":null,"merged_at":null,"merge_commit_sha":"0e8a1dd92c31cd7976dfdd4f084858fd23cd530e","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/home-assistant/home-assistant/pulls/5633/commits","review_comments_url":"https://api.github.com/repos/home-assistant/home-assistant/pulls/5633/comments","review_comment_url":"https://api.github.com/repos/home-assistant/home-assistant/pulls/comments{/number}","comments_url":"https://api.github.com/repos/home-assistant/home-assistant/issues/5633/comments","statuses_url":"https://api.github.com/repos/home-assistant/home-assistant/statuses/a53d2ff05b50f2f3ef73610ba26e09c32059bfac","head":{"label":"armills:logbook-performance","ref":"logbook-performance","sha":"a53d2ff05b50f2f3ef73610ba26e09c32059bfac","user":{"login":"armills","id":1386547,"avatar_url":"https://avatars.githubusercontent.com/u/1386547?v=3","gravatar_id":"","url":"https://api.github.com/users/armills","html_url":"https://github.com/armills","followers_url":"https://api.github.com/users/armills/followers","following_url":"https://api.github.com/users/armills/following{/other_user}","gists_url":"https://api.github.com/users/armills/gists{/gist_id}","starred_url":"https://api.github.com/users/armills/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/armills/subscriptions","organizations_url":"https://api.github.com/users/armills/orgs","repos_url":"https://api.github.com/users/armills/repos","events_url":"https://api.github.com/users/armills/events{/privacy}","received_events_url":"https://api.github.com/users/armills/received_events","type":"User","site_admin":false},"repo":{"id":60287084,"name":"home-assistant","full_name":"armills/home-assistant","owner":{"login":"armills","id":1386547,"avatar_url":"https://avatars.githubusercontent.com/u/1386547?v=3","gravatar_id":"","url":"https://api.github.com/users/armills","html_url":"https://github.com/armills","followers_url":"https://api.github.com/users/armills/followers","following_url":"https://api.github.com/users/armills/following{/other_user}","gists_url":"https://api.github.com/users/armills/gists{/gist_id}","starred_url":"https://api.github.com/users/armills/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/armills/subscriptions","organizations_url":"https://api.github.com/users/armills/orgs","repos_url":"https://api.github.com/users/armills/repos","events_url":"https://api.github.com/users/armills/events{/privacy}","received_events_url":"https://api.github.com/users/armills/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/armills/home-assistant","description":":house_with_garden: Open-source home automation platform running on Python 3","fork":true,"url":"https://api.github.com/repos/armills/home-assistant","forks_url":"https://api.github.com/repos/armills/home-assistant/forks","keys_url":"https://api.github.com/repos/armills/home-assistant/keys{/key_id}","collaborators_url":"https://api.github.com/repos/armills/home-assistant/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/armills/home-assistant/teams","hooks_url":"https://api.github.com/repos/armills/home-assistant/hooks","issue_events_url":"https://api.github.com/repos/armills/home-assistant/issues/events{/number}","events_url":"https://api.github.com/repos/armills/home-assistant/events","assignees_url":"https://api.github.com/repos/armills/home-assistant/assignees{/user}","branches_url":"https://api.github.com/repos/armills/home-assistant/branches{/branch}","tags_url":"https://api.github.com/repos/armills/home-assistant/tags","blobs_url":"https://api.github.com/repos/armills/home-assistant/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/armills/home-assistant/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/armills/home-assistant/git/refs{/sha}","trees_url":"https://api.github.com/repos/armills/home-assistant/git/trees{/sha}","statuses_url":"https://api.github.com/repos/armills/home-assistant/statuses/{sha}","languages_url":"https://api.github.com/repos/armills/home-assistant/languages","stargazers_url":"https://api.github.com/repos/armills/home-assistant/stargazers","contributors_url":"https://api.github.com/repos/armills/home-assistant/contributors","subscribers_url":"https://api.github.com/repos/armills/home-assistant/subscribers","subscription_url":"https://api.github.com/repos/armills/home-assistant/subscription","commits_url":"https://api.github.com/repos/armills/home-assistant/commits{/sha}","git_commits_url":"https://api.github.com/repos/armills/home-assistant/git/commits{/sha}","comments_url":"https://api.github.com/repos/armills/home-assistant/comments{/number}","issue_comment_url":"https://api.github.com/repos/armills/home-assistant/issues/comments{/number}","contents_url":"https://api.github.com/repos/armills/home-assistant/contents/{+path}","compare_url":"https://api.github.com/repos/armills/home-assistant/compare/{base}...{head}","merges_url":"https://api.github.com/repos/armills/home-assistant/merges","archive_url":"https://api.github.com/repos/armills/home-assistant/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/armills/home-assistant/downloads","issues_url":"https://api.github.com/repos/armills/home-assistant/issues{/number}","pulls_url":"https://api.github.com/repos/armills/home-assistant/pulls{/number}","milestones_url":"https://api.github.com/repos/armills/home-assistant/milestones{/number}","notifications_url":"https://api.github.com/repos/armills/home-assistant/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/armills/home-assistant/labels{/name}","releases_url":"https://api.github.com/repos/armills/home-assistant/releases{/id}","deployments_url":"https://api.github.com/repos/armills/home-assistant/deployments","created_at":"2016-06-02T18:17:19Z","updated_at":"2016-06-02T18:17:21Z","pushed_at":"2017-02-02T03:23:48Z","git_url":"git://github.com/armills/home-assistant.git","ssh_url":"git@github.com:armills/home-assistant.git","clone_url":"https://github.com/armills/home-assistant.git","svn_url":"https://github.com/armills/home-assistant","homepage":"https://home-assistant.io","size":88844,"stargazers_count":0,"watchers_count":0,"language":"Python","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":"dev"}},"base":{"label":"home-assistant:dev","ref":"dev","sha":"b2180fba6395336e3ad3bd962686c641658cc842","user":{"login":"home-assistant","id":13844975,"avatar_url":"https://avatars.githubusercontent.com/u/13844975?v=3","gravatar_id":"","url":"https://api.github.com/users/home-assistant","html_url":"https://github.com/home-assistant","followers_url":"https://api.github.com/users/home-assistant/followers","following_url":"https://api.github.com/users/home-assistant/following{/other_user}","gists_url":"https://api.github.com/users/home-assistant/gists{/gist_id}","starred_url":"https://api.github.com/users/home-assistant/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/home-assistant/subscriptions","organizations_url":"https://api.github.com/users/home-assistant/orgs","repos_url":"https://api.github.com/users/home-assistant/repos","events_url":"https://api.github.com/users/home-assistant/events{/privacy}","received_events_url":"https://api.github.com/users/home-assistant/received_events","type":"Organization","site_admin":false},"repo":{"id":12888993,"name":"home-assistant","full_name":"home-assistant/home-assistant","owner":{"login":"home-assistant","id":13844975,"avatar_url":"https://avatars.githubusercontent.com/u/13844975?v=3","gravatar_id":"","url":"https://api.github.com/users/home-assistant","html_url":"https://github.com/home-assistant","followers_url":"https://api.github.com/users/home-assistant/followers","following_url":"https://api.github.com/users/home-assistant/following{/other_user}","gists_url":"https://api.github.com/users/home-assistant/gists{/gist_id}","starred_url":"https://api.github.com/users/home-assistant/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/home-assistant/subscriptions","organizations_url":"https://api.github.com/users/home-assistant/orgs","repos_url":"https://api.github.com/users/home-assistant/repos","events_url":"https://api.github.com/users/home-assistant/events{/privacy}","received_events_url":"https://api.github.com/users/home-assistant/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/home-assistant/home-assistant","description":":house_with_garden: Open-source home automation platform running on Python 3","fork":false,"url":"https://api.github.com/repos/home-assistant/home-assistant","forks_url":"https://api.github.com/repos/home-assistant/home-assistant/forks","keys_url":"https://api.github.com/repos/home-assistant/home-assistant/keys{/key_id}","collaborators_url":"https://api.github.com/repos/home-assistant/home-assistant/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/home-assistant/home-assistant/teams","hooks_url":"https://api.github.com/repos/home-assistant/home-assistant/hooks","issue_events_url":"https://api.github.com/repos/home-assistant/home-assistant/issues/events{/number}","events_url":"https://api.github.com/repos/home-assistant/home-assistant/events","assignees_url":"https://api.github.com/repos/home-assistant/home-assistant/assignees{/user}","branches_url":"https://api.github.com/repos/home-assistant/home-assistant/branches{/branch}","tags_url":"https://api.github.com/repos/home-assistant/home-assistant/tags","blobs_url":"https://api.github.com/repos/home-assistant/home-assistant/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/home-assistant/home-assistant/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/home-assistant/home-assistant/git/refs{/sha}","trees_url":"https://api.github.com/repos/home-assistant/home-assistant/git/trees{/sha}","statuses_url":"https://api.github.com/repos/home-assistant/home-assistant/statuses/{sha}","languages_url":"https://api.github.com/repos/home-assistant/home-assistant/languages","stargazers_url":"https://api.github.com/repos/home-assistant/home-assistant/stargazers","contributors_url":"https://api.github.com/repos/home-assistant/home-assistant/contributors","subscribers_url":"https://api.github.com/repos/home-assistant/home-assistant/subscribers","subscription_url":"https://api.github.com/repos/home-assistant/home-assistant/subscription","commits_url":"https://api.github.com/repos/home-assistant/home-assistant/commits{/sha}","git_commits_url":"https://api.github.com/repos/home-assistant/home-assistant/git/commits{/sha}","comments_url":"https://api.github.com/repos/home-assistant/home-assistant/comments{/number}","issue_comment_url":"https://api.github.com/repos/home-assistant/home-assistant/issues/comments{/number}","contents_url":"https://api.github.com/repos/home-assistant/home-assistant/contents/{+path}","compare_url":"https://api.github.com/repos/home-assistant/home-assistant/compare/{base}...{head}","merges_url":"https://api.github.com/repos/home-assistant/home-assistant/merges","archive_url":"https://api.github.com/repos/home-assistant/home-assistant/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/home-assistant/home-assistant/downloads","issues_url":"https://api.github.com/repos/home-assistant/home-assistant/issues{/number}","pulls_url":"https://api.github.com/repos/home-assistant/home-assistant/pulls{/number}","milestones_url":"https://api.github.com/repos/home-assistant/home-assistant/milestones{/number}","notifications_url":"https://api.github.com/repos/home-assistant/home-assistant/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/home-assistant/home-assistant/labels{/name}","releases_url":"https://api.github.com/repos/home-assistant/home-assistant/releases{/id}","deployments_url":"https://api.github.com/repos/home-assistant/home-assistant/deployments","created_at":"2013-09-17T07:29:48Z","updated_at":"2017-02-02T20:07:29Z","pushed_at":"2017-02-02T20:45:12Z","git_url":"git://github.com/home-assistant/home-assistant.git","ssh_url":"git@github.com:home-assistant/home-assistant.git","clone_url":"https://github.com/home-assistant/home-assistant.git","svn_url":"https://github.com/home-assistant/home-assistant","homepage":"https://home-assistant.io","size":49853,"stargazers_count":5688,"watchers_count":5688,"language":"Python","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":1591,"mirror_url":null,"open_issues_count":397,"forks":1591,"open_issues":397,"watchers":5688,"default_branch":"dev"}},"_links":{"self":{"href":"https://api.github.com/repos/home-assistant/home-assistant/pulls/5633"},"html":{"href":"https://github.com/home-assistant/home-assistant/pull/5633"},"issue":{"href":"https://api.github.com/repos/home-assistant/home-assistant/issues/5633"},"comments":{"href":"https://api.github.com/repos/home-assistant/home-assistant/issues/5633/comments"},"review_comments":{"href":"https://api.github.com/repos/home-assistant/home-assistant/pulls/5633/comments"},"review_comment":{"href":"https://api.github.com/repos/home-assistant/home-assistant/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/home-assistant/home-assistant/pulls/5633/commits"},"statuses":{"href":"https://api.github.com/repos/home-assistant/home-assistant/statuses/a53d2ff05b50f2f3ef73610ba26e09c32059bfac"}}}}
{"actor":{"display_login":"armills"}}