{"action":"created","comment":{"url":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls/comments/59240318","id":59240318,"diff_hunk":"@@ -0,0 +1,165 @@\n+from datetime import timedelta\n+from datetime import datetime\n+\n+from opcua import ua, Node\n+from opcua.server.history import HistoryStorageInterface\n+\n+import struct\n+import sqlite3\n+\n+\n+class HistorySQLite(HistoryStorageInterface):\n+    \"\"\"\n+    very minimal history backend storing data in SQLite database\n+    \"\"\"\n+    # FIXME: need to check on if sql_conn.commit() should be inside try block; and if .rollback() needs to be except\n+\n+    def __init__(self):\n+        self._datachanges_period = {}\n+        self._events = {}\n+        self._db_file = \"history.db\"\n+\n+        self._conn = sqlite3.connect(self._db_file, detect_types=sqlite3.PARSE_DECLTYPES, check_same_thread=False)\n+\n+    def new_historized_node(self, node, period, count=0):\n+        _c_new = self._conn.cursor()\n+\n+        node_id = self._get_table_name(node)\n+\n+        self._datachanges_period[node_id] = period\n+\n+        # create a table for the node which will store attributes of the DataValue object\n+        try:\n+            _c_new.execute('CREATE TABLE \"{tn}\" (ServerTimestamp TIMESTAMP,'\n+                           ' SourceTimestamp TIMESTAMP,'\n+                           ' StatusCode INTEGER,'\n+                           ' Value TEXT,'\n+                           ' VariantType INTEGER,'\n+                           ' ValueBinary BLOB)'.format(tn=node_id))\n+\n+        except sqlite3.Error as e:\n+            print(node_id, 'Historizing SQL Table Creation Error:', e)\n+\n+        self._conn.commit()\n+\n+    def save_node_value(self, node, datavalue):\n+        _c_sub = self._conn.cursor()\n+\n+        node_id = self._get_table_name(node)\n+\n+        value_blob = self._pack_value(datavalue)\n+\n+        # insert the data change into the database\n+        try:\n+            _c_sub.execute('INSERT INTO \"{tn}\" VALUES (?, ?, ?, ?, ?, ?)'.format(tn=node_id), (datavalue.ServerTimestamp,\n+                                                                                               datavalue.SourceTimestamp,\n+                                                                                               datavalue.StatusCode.value,\n+                                                                                               str(datavalue.Value.Value),\n+                                                                                               datavalue.Value.VariantType.value,\n+                                                                                               value_blob))\n+        except sqlite3.Error as e:\n+            print(node_id, 'Historizing SQL Insert Error:', e)\n+\n+        # get this node's period from the period dict and calculate the limit\n+        period = self._datachanges_period[node_id]\n+        date_limit = datetime.now() - period\n+\n+        # after the insert, delete all values older than period\n+        try:\n+            _c_sub.execute('DELETE FROM \"{tn}\" WHERE ServerTimestamp < ?'.format(tn=node_id),\n+                                                                                (date_limit.isoformat(' '),))\n+        except sqlite3.Error as e:\n+            print(node_id, 'Historizing SQL Delete Old Data Error:', e)\n+\n+        self._conn.commit()\n+\n+    def read_node_history(self, node, start, end, nb_values):\n+        _c_read = self._conn.cursor()\n+\n+        if end is None:\n+            end = datetime.now() + timedelta(days=1)\n+        if start is None:\n+            start = ua.DateTimeMinValue\n+\n+        node_id = self._get_table_name(node)\n+\n+        cont = None\n+        results = []\n+\n+        start_time = start.isoformat(' ')\n+        end_time = end.isoformat(' ')\n+\n+        # select values from the database\n+        try:\n+            for row in _c_read.execute('SELECT * FROM \"{tn}\" WHERE \"ServerTimestamp\" BETWEEN ? AND ? '\n+                                       'LIMIT ?'.format(tn=node_id), (start_time, end_time, nb_values,)):\n+\n+                value = self._unpack_value(row[4], row[5])\n+\n+                dv = ua.DataValue(ua.Variant(value, ua.VariantType(row[4])))\n+                dv.ServerTimestamp = row[0]\n+                dv.SourceTimestamp = row[1]\n+                dv.StatusCode = ua.StatusCode(row[2])\n+\n+                results.append(dv)\n+\n+        except sqlite3.Error as e:\n+            print(node_id, 'Historizing SQL Read Error:', e)\n+\n+        return results, cont\n+\n+    def new_historized_event(self, event, period):\n+        raise NotImplementedError\n+\n+    def save_event(self, event):\n+        raise NotImplementedError\n+\n+    def read_event_history(self, start, end, evfilter):\n+        raise NotImplementedError\n+\n+    def _pack_value(self, datavalue):\n+        variant_code = datavalue.Value.VariantType.value\n+        return struct.pack(self._get_pack_type(variant_code), datavalue.Value.Value)","path":"opcua/server/history_sql.py","position":122,"original_position":122,"commit_id":"94296ff2ed1f189e436dcc421e0736e5c944e346","original_commit_id":"94296ff2ed1f189e436dcc421e0736e5c944e346","user":{"login":"oroulet","id":2564046,"avatar_url":"https://avatars.githubusercontent.com/u/2564046?v=3","gravatar_id":"","url":"https://api.github.com/users/oroulet","html_url":"https://github.com/oroulet","followers_url":"https://api.github.com/users/oroulet/followers","following_url":"https://api.github.com/users/oroulet/following{/other_user}","gists_url":"https://api.github.com/users/oroulet/gists{/gist_id}","starred_url":"https://api.github.com/users/oroulet/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/oroulet/subscriptions","organizations_url":"https://api.github.com/users/oroulet/orgs","repos_url":"https://api.github.com/users/oroulet/repos","events_url":"https://api.github.com/users/oroulet/events{/privacy}","received_events_url":"https://api.github.com/users/oroulet/received_events","type":"User","site_admin":false},"body":"Wrap your byte array with opcua.utils.Buffer then it will work","created_at":"2016-04-11T16:58:04Z","updated_at":"2016-04-11T16:58:04Z","html_url":"https://github.com/FreeOpcUa/python-opcua/pull/156#discussion_r59240318","pull_request_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls/156","_links":{"self":{"href":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls/comments/59240318"},"html":{"href":"https://github.com/FreeOpcUa/python-opcua/pull/156#discussion_r59240318"},"pull_request":{"href":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls/156"}}},"pull_request":{"url":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls/156","id":65928875,"html_url":"https://github.com/FreeOpcUa/python-opcua/pull/156","diff_url":"https://github.com/FreeOpcUa/python-opcua/pull/156.diff","patch_url":"https://github.com/FreeOpcUa/python-opcua/pull/156.patch","issue_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/issues/156","number":156,"state":"open","locked":false,"title":"Updated SQLite Historizing","user":{"login":"zerox1212","id":13090472,"avatar_url":"https://avatars.githubusercontent.com/u/13090472?v=3","gravatar_id":"","url":"https://api.github.com/users/zerox1212","html_url":"https://github.com/zerox1212","followers_url":"https://api.github.com/users/zerox1212/followers","following_url":"https://api.github.com/users/zerox1212/following{/other_user}","gists_url":"https://api.github.com/users/zerox1212/gists{/gist_id}","starred_url":"https://api.github.com/users/zerox1212/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zerox1212/subscriptions","organizations_url":"https://api.github.com/users/zerox1212/orgs","repos_url":"https://api.github.com/users/zerox1212/repos","events_url":"https://api.github.com/users/zerox1212/events{/privacy}","received_events_url":"https://api.github.com/users/zerox1212/received_events","type":"User","site_admin":false},"body":"I tried to fix everything you told me about in https://github.com/FreeOpcUa/python-opcua/pull/155\r\n\r\nI don't know how you want to organize the code. I also don't know how the user should choose which history method, for now I just commented out the SQLite import.\r\n\r\nPerhaps you can just take history_sql.py into the main branch and then reorganize the code how you see fit.","created_at":"2016-04-11T00:35:53Z","updated_at":"2016-04-11T16:58:05Z","closed_at":null,"merged_at":null,"merge_commit_sha":"3ddffc66e52e2c2a6b9e3952a9584e83525a8e9e","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls/156/commits","review_comments_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls/156/comments","review_comment_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls/comments{/number}","comments_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/issues/156/comments","statuses_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/statuses/94296ff2ed1f189e436dcc421e0736e5c944e346","head":{"label":"zerox1212:master","ref":"master","sha":"94296ff2ed1f189e436dcc421e0736e5c944e346","user":{"login":"zerox1212","id":13090472,"avatar_url":"https://avatars.githubusercontent.com/u/13090472?v=3","gravatar_id":"","url":"https://api.github.com/users/zerox1212","html_url":"https://github.com/zerox1212","followers_url":"https://api.github.com/users/zerox1212/followers","following_url":"https://api.github.com/users/zerox1212/following{/other_user}","gists_url":"https://api.github.com/users/zerox1212/gists{/gist_id}","starred_url":"https://api.github.com/users/zerox1212/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zerox1212/subscriptions","organizations_url":"https://api.github.com/users/zerox1212/orgs","repos_url":"https://api.github.com/users/zerox1212/repos","events_url":"https://api.github.com/users/zerox1212/events{/privacy}","received_events_url":"https://api.github.com/users/zerox1212/received_events","type":"User","site_admin":false},"repo":{"id":55805788,"name":"python-opcua","full_name":"zerox1212/python-opcua","owner":{"login":"zerox1212","id":13090472,"avatar_url":"https://avatars.githubusercontent.com/u/13090472?v=3","gravatar_id":"","url":"https://api.github.com/users/zerox1212","html_url":"https://github.com/zerox1212","followers_url":"https://api.github.com/users/zerox1212/followers","following_url":"https://api.github.com/users/zerox1212/following{/other_user}","gists_url":"https://api.github.com/users/zerox1212/gists{/gist_id}","starred_url":"https://api.github.com/users/zerox1212/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zerox1212/subscriptions","organizations_url":"https://api.github.com/users/zerox1212/orgs","repos_url":"https://api.github.com/users/zerox1212/repos","events_url":"https://api.github.com/users/zerox1212/events{/privacy}","received_events_url":"https://api.github.com/users/zerox1212/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/zerox1212/python-opcua","description":"LGPL Pure Python OPC-UA Client and Server","fork":true,"url":"https://api.github.com/repos/zerox1212/python-opcua","forks_url":"https://api.github.com/repos/zerox1212/python-opcua/forks","keys_url":"https://api.github.com/repos/zerox1212/python-opcua/keys{/key_id}","collaborators_url":"https://api.github.com/repos/zerox1212/python-opcua/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/zerox1212/python-opcua/teams","hooks_url":"https://api.github.com/repos/zerox1212/python-opcua/hooks","issue_events_url":"https://api.github.com/repos/zerox1212/python-opcua/issues/events{/number}","events_url":"https://api.github.com/repos/zerox1212/python-opcua/events","assignees_url":"https://api.github.com/repos/zerox1212/python-opcua/assignees{/user}","branches_url":"https://api.github.com/repos/zerox1212/python-opcua/branches{/branch}","tags_url":"https://api.github.com/repos/zerox1212/python-opcua/tags","blobs_url":"https://api.github.com/repos/zerox1212/python-opcua/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/zerox1212/python-opcua/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/zerox1212/python-opcua/git/refs{/sha}","trees_url":"https://api.github.com/repos/zerox1212/python-opcua/git/trees{/sha}","statuses_url":"https://api.github.com/repos/zerox1212/python-opcua/statuses/{sha}","languages_url":"https://api.github.com/repos/zerox1212/python-opcua/languages","stargazers_url":"https://api.github.com/repos/zerox1212/python-opcua/stargazers","contributors_url":"https://api.github.com/repos/zerox1212/python-opcua/contributors","subscribers_url":"https://api.github.com/repos/zerox1212/python-opcua/subscribers","subscription_url":"https://api.github.com/repos/zerox1212/python-opcua/subscription","commits_url":"https://api.github.com/repos/zerox1212/python-opcua/commits{/sha}","git_commits_url":"https://api.github.com/repos/zerox1212/python-opcua/git/commits{/sha}","comments_url":"https://api.github.com/repos/zerox1212/python-opcua/comments{/number}","issue_comment_url":"https://api.github.com/repos/zerox1212/python-opcua/issues/comments{/number}","contents_url":"https://api.github.com/repos/zerox1212/python-opcua/contents/{+path}","compare_url":"https://api.github.com/repos/zerox1212/python-opcua/compare/{base}...{head}","merges_url":"https://api.github.com/repos/zerox1212/python-opcua/merges","archive_url":"https://api.github.com/repos/zerox1212/python-opcua/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/zerox1212/python-opcua/downloads","issues_url":"https://api.github.com/repos/zerox1212/python-opcua/issues{/number}","pulls_url":"https://api.github.com/repos/zerox1212/python-opcua/pulls{/number}","milestones_url":"https://api.github.com/repos/zerox1212/python-opcua/milestones{/number}","notifications_url":"https://api.github.com/repos/zerox1212/python-opcua/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/zerox1212/python-opcua/labels{/name}","releases_url":"https://api.github.com/repos/zerox1212/python-opcua/releases{/id}","deployments_url":"https://api.github.com/repos/zerox1212/python-opcua/deployments","created_at":"2016-04-08T19:52:03Z","updated_at":"2016-04-08T19:52:04Z","pushed_at":"2016-04-11T00:30:05Z","git_url":"git://github.com/zerox1212/python-opcua.git","ssh_url":"git@github.com:zerox1212/python-opcua.git","clone_url":"https://github.com/zerox1212/python-opcua.git","svn_url":"https://github.com/zerox1212/python-opcua","homepage":"http://freeopcua.github.io/","size":2196,"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":"master"}},"base":{"label":"FreeOpcUa:master","ref":"master","sha":"eb491cfacefb3f03a411755f441ac068a5ba91fe","user":{"login":"FreeOpcUa","id":7480453,"avatar_url":"https://avatars.githubusercontent.com/u/7480453?v=3","gravatar_id":"","url":"https://api.github.com/users/FreeOpcUa","html_url":"https://github.com/FreeOpcUa","followers_url":"https://api.github.com/users/FreeOpcUa/followers","following_url":"https://api.github.com/users/FreeOpcUa/following{/other_user}","gists_url":"https://api.github.com/users/FreeOpcUa/gists{/gist_id}","starred_url":"https://api.github.com/users/FreeOpcUa/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/FreeOpcUa/subscriptions","organizations_url":"https://api.github.com/users/FreeOpcUa/orgs","repos_url":"https://api.github.com/users/FreeOpcUa/repos","events_url":"https://api.github.com/users/FreeOpcUa/events{/privacy}","received_events_url":"https://api.github.com/users/FreeOpcUa/received_events","type":"Organization","site_admin":false},"repo":{"id":31074280,"name":"python-opcua","full_name":"FreeOpcUa/python-opcua","owner":{"login":"FreeOpcUa","id":7480453,"avatar_url":"https://avatars.githubusercontent.com/u/7480453?v=3","gravatar_id":"","url":"https://api.github.com/users/FreeOpcUa","html_url":"https://github.com/FreeOpcUa","followers_url":"https://api.github.com/users/FreeOpcUa/followers","following_url":"https://api.github.com/users/FreeOpcUa/following{/other_user}","gists_url":"https://api.github.com/users/FreeOpcUa/gists{/gist_id}","starred_url":"https://api.github.com/users/FreeOpcUa/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/FreeOpcUa/subscriptions","organizations_url":"https://api.github.com/users/FreeOpcUa/orgs","repos_url":"https://api.github.com/users/FreeOpcUa/repos","events_url":"https://api.github.com/users/FreeOpcUa/events{/privacy}","received_events_url":"https://api.github.com/users/FreeOpcUa/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/FreeOpcUa/python-opcua","description":"LGPL Pure Python OPC-UA Client and Server","fork":false,"url":"https://api.github.com/repos/FreeOpcUa/python-opcua","forks_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/forks","keys_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/keys{/key_id}","collaborators_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/teams","hooks_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/hooks","issue_events_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/issues/events{/number}","events_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/events","assignees_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/assignees{/user}","branches_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/branches{/branch}","tags_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/tags","blobs_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/git/refs{/sha}","trees_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/git/trees{/sha}","statuses_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/statuses/{sha}","languages_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/languages","stargazers_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/stargazers","contributors_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/contributors","subscribers_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/subscribers","subscription_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/subscription","commits_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/commits{/sha}","git_commits_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/git/commits{/sha}","comments_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/comments{/number}","issue_comment_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/issues/comments{/number}","contents_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/contents/{+path}","compare_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/compare/{base}...{head}","merges_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/merges","archive_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/downloads","issues_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/issues{/number}","pulls_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls{/number}","milestones_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/milestones{/number}","notifications_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/labels{/name}","releases_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/releases{/id}","deployments_url":"https://api.github.com/repos/FreeOpcUa/python-opcua/deployments","created_at":"2015-02-20T16:46:53Z","updated_at":"2016-04-02T21:32:22Z","pushed_at":"2016-04-11T00:35:53Z","git_url":"git://github.com/FreeOpcUa/python-opcua.git","ssh_url":"git@github.com:FreeOpcUa/python-opcua.git","clone_url":"https://github.com/FreeOpcUa/python-opcua.git","svn_url":"https://github.com/FreeOpcUa/python-opcua","homepage":"http://freeopcua.github.io/","size":2554,"stargazers_count":48,"watchers_count":48,"language":"Python","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":25,"mirror_url":null,"open_issues_count":16,"forks":25,"open_issues":16,"watchers":48,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls/156"},"html":{"href":"https://github.com/FreeOpcUa/python-opcua/pull/156"},"issue":{"href":"https://api.github.com/repos/FreeOpcUa/python-opcua/issues/156"},"comments":{"href":"https://api.github.com/repos/FreeOpcUa/python-opcua/issues/156/comments"},"review_comments":{"href":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls/156/comments"},"review_comment":{"href":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/FreeOpcUa/python-opcua/pulls/156/commits"},"statuses":{"href":"https://api.github.com/repos/FreeOpcUa/python-opcua/statuses/94296ff2ed1f189e436dcc421e0736e5c944e346"}}}}