{"action":"created","comment":{"url":"https://api.github.com/repos/opentraffic/datastore/pulls/comments/102952238","pull_request_review_id":23726247,"id":102952238,"diff_hunk":"@@ -0,0 +1,265 @@\n+#!/usr/bin/env python\n+\n+'''\n+If you're running this from this directory you can start the server with the following command:\n+./query.py localhost:8005\n+\n+sample url looks like this:\n+http://localhost:8005/query_by_ids?json={\"segments\":[345678,345780,345795]}'''\n+\n+import sys\n+import json\n+import multiprocessing\n+import threading\n+from Queue import Queue\n+import socket\n+from BaseHTTPServer import HTTPServer, BaseHTTPRequestHandler\n+from SocketServer import ThreadingMixIn\n+from cgi import urlparse\n+import psycopg2\n+import os\n+import time\n+import calendar\n+import datetime\n+\n+actions = set(['query_by_ids','query_by_range'])\n+\n+#use a thread pool instead of just frittering off new threads for every request\n+class ThreadPoolMixIn(ThreadingMixIn):\n+  allow_reuse_address = True  # seems to fix socket.error on server restart\n+\n+  def serve_forever(self):\n+    # set up the threadpool\n+    self.requests = Queue(multiprocessing.cpu_count())\n+    for x in range(multiprocessing.cpu_count()):\n+      t = threading.Thread(target = self.process_request_thread)\n+      t.setDaemon(1)\n+      t.start()\n+    # server main loop\n+    while True:\n+      self.handle_request()\n+    self.server_close()\n+\n+  def make_thread_locals(self):\n+    credentials = (os.environ['POSTGRES_DB'], os.environ['POSTGRES_USER'], os.environ['POSTGRES_HOST'], \n+                   os.environ['POSTGRES_PASSWORD'], os.environ['POSTGRES_PORT'])\n+    try:\n+      sql_conn = psycopg2.connect(\"dbname='%s' user='%s' host='%s' password='%s' port='%s'\" % credentials)\n+    except Exception as e:\n+      raise Exception('Failed to connect to database.')\n+\n+    sys.stdout.write(\"Connected to db\\n\")\n+    sys.stdout.flush()\n+\n+    try:\n+      # check and see if prepare statements exists...if not, create them\n+      cursor = sql_conn.cursor()\n+      cursor.execute(\"select exists(select name from pg_prepared_statements where name = 'query_by_id');\")\n+\n+      if cursor.fetchone()[0] == False:\n+        try:\n+          prepare_statement = \"PREPARE query_by_id AS SELECT segment_id, prev_segment_id, start_time, \" \\\n+                              \"start_time_dow, start_time_hour, end_time, speed, length FROM segments where \" \\\n+                              \"segment_id = ANY ($1) order by segment_id, prev_segment_id;\"\n+          cursor.execute(prepare_statement)\n+          sql_conn.commit()\n+        except Exception as e:\n+          raise Exception(\"Can't create prepare statements\")\n+\n+      cursor.execute(\"select exists(select name from pg_prepared_statements where name = 'query_by_range');\")\n+\n+      if cursor.fetchone()[0] == False:\n+        try:\n+          prepare_statement = \"PREPARE query_by_range AS SELECT segment_id, prev_segment_id, start_time, \" \\\n+                              \"start_time_dow, start_time_hour, end_time, speed, length FROM segments where \" \\\n+                              \"start_time >= $1 and start_time < $2 order by segment_id, prev_segment_id;\"\n+          cursor.execute(prepare_statement)\n+          sql_conn.commit()\n+        except Exception as e:\n+          raise Exception(\"Can't create prepare statements\")\n+    except Exception as e:\n+      raise Exception(\"Can't check for prepare statements\")\n+    self.sql_conn = sql_conn\n+    sys.stdout.write(\"Created prepare statements.\\n\")\n+    sys.stdout.flush()\n+\n+  def process_request_thread(self):\n+    self.make_thread_locals()\n+    while True:\n+      request, client_address = self.requests.get()\n+      ThreadingMixIn.process_request_thread(self, request, client_address)\n+\n+  def handle_request(self):\n+    try:\n+      request, client_address = self.get_request()\n+    except socket.error:\n+      return\n+    if self.verify_request(request, client_address):\n+      self.requests.put((request, client_address))\n+\n+#enable threaded server\n+class ThreadedHTTPServer(ThreadPoolMixIn, HTTPServer):\n+  pass\n+\n+#custom handler for getting routes\n+class StoreHandler(BaseHTTPRequestHandler):\n+\n+  #boiler plate parsing\n+  def parse_segments(self, post):\n+    #split the query from the path\n+    try:\n+      split = urlparse.urlsplit(self.path)\n+    except:\n+      raise Exception('Try a url that looks like /action?query_string')\n+    #path has the action in it\n+    try:\n+      if split.path.split('/')[-1] not in actions:\n+        raise\n+    except:\n+      raise Exception('Try a valid action: ' + str([k for k in actions]))\n+    #handle POST\n+    if post:\n+      body = self.rfile.read(int(self.headers['Content-Length'])).decode('utf-8')\n+      return json.loads(body)\n+    #handle GET\n+    else:\n+      params = urlparse.parse_qs(split.query)\n+      if 'json' in params:      \n+        return json.loads(params['json'][0])\n+    raise Exception('No json provided')\n+\n+  #parse the request because we dont get this for free!\n+  def handle_request(self, post):\n+    #get the query data\n+    segments = self.parse_segments(post)\n+\n+    try:   \n+      # get the provider. \n+      list_of_ids = segments['segments'] if 'segments' in segments else None\n+      start_date_time = segments.get('start_date_time', None)\n+      end_date_time = segments.get('end_date_time', None)\n+      cursor = self.server.sql_conn.cursor()\n+\n+      # send it to the cursor.\n+\n+      if None in (start_date_time, end_date_time):\n+        cursor.execute(\"execute query_by_id (%s)\",(list_of_ids,))\n+      else:\n+        s_date_time = calendar.timegm(time.strptime(start_date_time,\"%Y-%m-%d\"))\n+        # add a day so that we include the end day in the range.\n+        d = datetime.datetime.strptime(end_date_time,\"%Y-%m-%d\") + datetime.timedelta(days=1)\n+        e_date_time = calendar.timegm(d.timetuple())\n+\n+        cursor.execute(\"execute query_by_range (%s, %s)\",(s_date_time,e_date_time))\n+      rows = cursor.fetchall()      \n+      segments = {'segments':[]}\n+\n+      for row in rows:\n+        d = dict()\n+        d['segment_id'] = row[0]\n+        d['prev_segment_id'] = row[1]\n+        d['start_time'] = row[2]\n+        d['start_time_dow'] = row[3]\n+        d['start_time_hour'] = row[4]\n+        d['end_time'] = row[5]\n+        d['speed'] = row[6]\n+        d['length'] = row[7]\n+\n+        segments['segments'].append(d)\n+\n+      results = json.dumps(segments)\n+\n+    except Exception as e:\n+      # must commit if failure\n+      #self.server.sql_conn.commit()             \n+      return 400, str(e)\n+\n+    #hand it back\n+    return 200, json.loads(results)","path":"py/query.py","position":178,"original_position":178,"commit_id":"10075050d60c00529d31c2243d94f378d0a8e73a","original_commit_id":"10075050d60c00529d31c2243d94f378d0a8e73a","user":{"login":"kevinkreiser","id":697548,"avatar_url":"https://avatars.githubusercontent.com/u/697548?v=3","gravatar_id":"","url":"https://api.github.com/users/kevinkreiser","html_url":"https://github.com/kevinkreiser","followers_url":"https://api.github.com/users/kevinkreiser/followers","following_url":"https://api.github.com/users/kevinkreiser/following{/other_user}","gists_url":"https://api.github.com/users/kevinkreiser/gists{/gist_id}","starred_url":"https://api.github.com/users/kevinkreiser/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kevinkreiser/subscriptions","organizations_url":"https://api.github.com/users/kevinkreiser/orgs","repos_url":"https://api.github.com/users/kevinkreiser/repos","events_url":"https://api.github.com/users/kevinkreiser/events{/privacy}","received_events_url":"https://api.github.com/users/kevinkreiser/received_events","type":"User","site_admin":false},"body":"there is still no need to dump and load it. change the bit above from `segments = {'segments':[]}` to `results = {'segments':[]}` and then in either case results will be a dict which you can return from this function","created_at":"2017-02-24T14:36:00Z","updated_at":"2017-02-24T14:36:01Z","html_url":"https://github.com/opentraffic/datastore/pull/21#discussion_r102952238","pull_request_url":"https://api.github.com/repos/opentraffic/datastore/pulls/21","_links":{"self":{"href":"https://api.github.com/repos/opentraffic/datastore/pulls/comments/102952238"},"html":{"href":"https://github.com/opentraffic/datastore/pull/21#discussion_r102952238"},"pull_request":{"href":"https://api.github.com/repos/opentraffic/datastore/pulls/21"}}},"pull_request":{"url":"https://api.github.com/repos/opentraffic/datastore/pulls/21","id":107698313,"html_url":"https://github.com/opentraffic/datastore/pull/21","diff_url":"https://github.com/opentraffic/datastore/pull/21.diff","patch_url":"https://github.com/opentraffic/datastore/pull/21.patch","issue_url":"https://api.github.com/repos/opentraffic/datastore/issues/21","number":21,"state":"open","locked":false,"title":"some datastore updates and init copy of query","user":{"login":"gknisely","id":2676277,"avatar_url":"https://avatars.githubusercontent.com/u/2676277?v=3","gravatar_id":"","url":"https://api.github.com/users/gknisely","html_url":"https://github.com/gknisely","followers_url":"https://api.github.com/users/gknisely/followers","following_url":"https://api.github.com/users/gknisely/following{/other_user}","gists_url":"https://api.github.com/users/gknisely/gists{/gist_id}","starred_url":"https://api.github.com/users/gknisely/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gknisely/subscriptions","organizations_url":"https://api.github.com/users/gknisely/orgs","repos_url":"https://api.github.com/users/gknisely/repos","events_url":"https://api.github.com/users/gknisely/events{/privacy}","received_events_url":"https://api.github.com/users/gknisely/received_events","type":"User","site_admin":false},"body":"fixes #17 \r\n\r\n@heffergm let me know if the error shows back up ","created_at":"2017-02-23T19:46:19Z","updated_at":"2017-02-24T14:36:01Z","closed_at":null,"merged_at":null,"merge_commit_sha":"5aa6b553efa0618fdc5c9ba1a7f4f180e982ab4d","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/opentraffic/datastore/pulls/21/commits","review_comments_url":"https://api.github.com/repos/opentraffic/datastore/pulls/21/comments","review_comment_url":"https://api.github.com/repos/opentraffic/datastore/pulls/comments{/number}","comments_url":"https://api.github.com/repos/opentraffic/datastore/issues/21/comments","statuses_url":"https://api.github.com/repos/opentraffic/datastore/statuses/10075050d60c00529d31c2243d94f378d0a8e73a","head":{"label":"opentraffic:gk","ref":"gk","sha":"10075050d60c00529d31c2243d94f378d0a8e73a","user":{"login":"opentraffic","id":10747601,"avatar_url":"https://avatars.githubusercontent.com/u/10747601?v=3","gravatar_id":"","url":"https://api.github.com/users/opentraffic","html_url":"https://github.com/opentraffic","followers_url":"https://api.github.com/users/opentraffic/followers","following_url":"https://api.github.com/users/opentraffic/following{/other_user}","gists_url":"https://api.github.com/users/opentraffic/gists{/gist_id}","starred_url":"https://api.github.com/users/opentraffic/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/opentraffic/subscriptions","organizations_url":"https://api.github.com/users/opentraffic/orgs","repos_url":"https://api.github.com/users/opentraffic/repos","events_url":"https://api.github.com/users/opentraffic/events{/privacy}","received_events_url":"https://api.github.com/users/opentraffic/received_events","type":"Organization","site_admin":false},"repo":{"id":80648213,"name":"datastore","full_name":"opentraffic/datastore","owner":{"login":"opentraffic","id":10747601,"avatar_url":"https://avatars.githubusercontent.com/u/10747601?v=3","gravatar_id":"","url":"https://api.github.com/users/opentraffic","html_url":"https://github.com/opentraffic","followers_url":"https://api.github.com/users/opentraffic/followers","following_url":"https://api.github.com/users/opentraffic/following{/other_user}","gists_url":"https://api.github.com/users/opentraffic/gists{/gist_id}","starred_url":"https://api.github.com/users/opentraffic/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/opentraffic/subscriptions","organizations_url":"https://api.github.com/users/opentraffic/orgs","repos_url":"https://api.github.com/users/opentraffic/repos","events_url":"https://api.github.com/users/opentraffic/events{/privacy}","received_events_url":"https://api.github.com/users/opentraffic/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/opentraffic/datastore","description":null,"fork":false,"url":"https://api.github.com/repos/opentraffic/datastore","forks_url":"https://api.github.com/repos/opentraffic/datastore/forks","keys_url":"https://api.github.com/repos/opentraffic/datastore/keys{/key_id}","collaborators_url":"https://api.github.com/repos/opentraffic/datastore/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/opentraffic/datastore/teams","hooks_url":"https://api.github.com/repos/opentraffic/datastore/hooks","issue_events_url":"https://api.github.com/repos/opentraffic/datastore/issues/events{/number}","events_url":"https://api.github.com/repos/opentraffic/datastore/events","assignees_url":"https://api.github.com/repos/opentraffic/datastore/assignees{/user}","branches_url":"https://api.github.com/repos/opentraffic/datastore/branches{/branch}","tags_url":"https://api.github.com/repos/opentraffic/datastore/tags","blobs_url":"https://api.github.com/repos/opentraffic/datastore/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/opentraffic/datastore/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/opentraffic/datastore/git/refs{/sha}","trees_url":"https://api.github.com/repos/opentraffic/datastore/git/trees{/sha}","statuses_url":"https://api.github.com/repos/opentraffic/datastore/statuses/{sha}","languages_url":"https://api.github.com/repos/opentraffic/datastore/languages","stargazers_url":"https://api.github.com/repos/opentraffic/datastore/stargazers","contributors_url":"https://api.github.com/repos/opentraffic/datastore/contributors","subscribers_url":"https://api.github.com/repos/opentraffic/datastore/subscribers","subscription_url":"https://api.github.com/repos/opentraffic/datastore/subscription","commits_url":"https://api.github.com/repos/opentraffic/datastore/commits{/sha}","git_commits_url":"https://api.github.com/repos/opentraffic/datastore/git/commits{/sha}","comments_url":"https://api.github.com/repos/opentraffic/datastore/comments{/number}","issue_comment_url":"https://api.github.com/repos/opentraffic/datastore/issues/comments{/number}","contents_url":"https://api.github.com/repos/opentraffic/datastore/contents/{+path}","compare_url":"https://api.github.com/repos/opentraffic/datastore/compare/{base}...{head}","merges_url":"https://api.github.com/repos/opentraffic/datastore/merges","archive_url":"https://api.github.com/repos/opentraffic/datastore/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/opentraffic/datastore/downloads","issues_url":"https://api.github.com/repos/opentraffic/datastore/issues{/number}","pulls_url":"https://api.github.com/repos/opentraffic/datastore/pulls{/number}","milestones_url":"https://api.github.com/repos/opentraffic/datastore/milestones{/number}","notifications_url":"https://api.github.com/repos/opentraffic/datastore/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/opentraffic/datastore/labels{/name}","releases_url":"https://api.github.com/repos/opentraffic/datastore/releases{/id}","deployments_url":"https://api.github.com/repos/opentraffic/datastore/deployments","created_at":"2017-02-01T18:06:16Z","updated_at":"2017-02-06T20:16:45Z","pushed_at":"2017-02-23T21:29:00Z","git_url":"git://github.com/opentraffic/datastore.git","ssh_url":"git@github.com:opentraffic/datastore.git","clone_url":"https://github.com/opentraffic/datastore.git","svn_url":"https://github.com/opentraffic/datastore","homepage":null,"size":64,"stargazers_count":1,"watchers_count":1,"language":"Python","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":6,"forks":0,"open_issues":6,"watchers":1,"default_branch":"master"}},"base":{"label":"opentraffic:master","ref":"master","sha":"8db5884775068c2691c2bc8b43b9e7257aaca740","user":{"login":"opentraffic","id":10747601,"avatar_url":"https://avatars.githubusercontent.com/u/10747601?v=3","gravatar_id":"","url":"https://api.github.com/users/opentraffic","html_url":"https://github.com/opentraffic","followers_url":"https://api.github.com/users/opentraffic/followers","following_url":"https://api.github.com/users/opentraffic/following{/other_user}","gists_url":"https://api.github.com/users/opentraffic/gists{/gist_id}","starred_url":"https://api.github.com/users/opentraffic/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/opentraffic/subscriptions","organizations_url":"https://api.github.com/users/opentraffic/orgs","repos_url":"https://api.github.com/users/opentraffic/repos","events_url":"https://api.github.com/users/opentraffic/events{/privacy}","received_events_url":"https://api.github.com/users/opentraffic/received_events","type":"Organization","site_admin":false},"repo":{"id":80648213,"name":"datastore","full_name":"opentraffic/datastore","owner":{"login":"opentraffic","id":10747601,"avatar_url":"https://avatars.githubusercontent.com/u/10747601?v=3","gravatar_id":"","url":"https://api.github.com/users/opentraffic","html_url":"https://github.com/opentraffic","followers_url":"https://api.github.com/users/opentraffic/followers","following_url":"https://api.github.com/users/opentraffic/following{/other_user}","gists_url":"https://api.github.com/users/opentraffic/gists{/gist_id}","starred_url":"https://api.github.com/users/opentraffic/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/opentraffic/subscriptions","organizations_url":"https://api.github.com/users/opentraffic/orgs","repos_url":"https://api.github.com/users/opentraffic/repos","events_url":"https://api.github.com/users/opentraffic/events{/privacy}","received_events_url":"https://api.github.com/users/opentraffic/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/opentraffic/datastore","description":null,"fork":false,"url":"https://api.github.com/repos/opentraffic/datastore","forks_url":"https://api.github.com/repos/opentraffic/datastore/forks","keys_url":"https://api.github.com/repos/opentraffic/datastore/keys{/key_id}","collaborators_url":"https://api.github.com/repos/opentraffic/datastore/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/opentraffic/datastore/teams","hooks_url":"https://api.github.com/repos/opentraffic/datastore/hooks","issue_events_url":"https://api.github.com/repos/opentraffic/datastore/issues/events{/number}","events_url":"https://api.github.com/repos/opentraffic/datastore/events","assignees_url":"https://api.github.com/repos/opentraffic/datastore/assignees{/user}","branches_url":"https://api.github.com/repos/opentraffic/datastore/branches{/branch}","tags_url":"https://api.github.com/repos/opentraffic/datastore/tags","blobs_url":"https://api.github.com/repos/opentraffic/datastore/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/opentraffic/datastore/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/opentraffic/datastore/git/refs{/sha}","trees_url":"https://api.github.com/repos/opentraffic/datastore/git/trees{/sha}","statuses_url":"https://api.github.com/repos/opentraffic/datastore/statuses/{sha}","languages_url":"https://api.github.com/repos/opentraffic/datastore/languages","stargazers_url":"https://api.github.com/repos/opentraffic/datastore/stargazers","contributors_url":"https://api.github.com/repos/opentraffic/datastore/contributors","subscribers_url":"https://api.github.com/repos/opentraffic/datastore/subscribers","subscription_url":"https://api.github.com/repos/opentraffic/datastore/subscription","commits_url":"https://api.github.com/repos/opentraffic/datastore/commits{/sha}","git_commits_url":"https://api.github.com/repos/opentraffic/datastore/git/commits{/sha}","comments_url":"https://api.github.com/repos/opentraffic/datastore/comments{/number}","issue_comment_url":"https://api.github.com/repos/opentraffic/datastore/issues/comments{/number}","contents_url":"https://api.github.com/repos/opentraffic/datastore/contents/{+path}","compare_url":"https://api.github.com/repos/opentraffic/datastore/compare/{base}...{head}","merges_url":"https://api.github.com/repos/opentraffic/datastore/merges","archive_url":"https://api.github.com/repos/opentraffic/datastore/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/opentraffic/datastore/downloads","issues_url":"https://api.github.com/repos/opentraffic/datastore/issues{/number}","pulls_url":"https://api.github.com/repos/opentraffic/datastore/pulls{/number}","milestones_url":"https://api.github.com/repos/opentraffic/datastore/milestones{/number}","notifications_url":"https://api.github.com/repos/opentraffic/datastore/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/opentraffic/datastore/labels{/name}","releases_url":"https://api.github.com/repos/opentraffic/datastore/releases{/id}","deployments_url":"https://api.github.com/repos/opentraffic/datastore/deployments","created_at":"2017-02-01T18:06:16Z","updated_at":"2017-02-06T20:16:45Z","pushed_at":"2017-02-23T21:29:00Z","git_url":"git://github.com/opentraffic/datastore.git","ssh_url":"git@github.com:opentraffic/datastore.git","clone_url":"https://github.com/opentraffic/datastore.git","svn_url":"https://github.com/opentraffic/datastore","homepage":null,"size":64,"stargazers_count":1,"watchers_count":1,"language":"Python","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":6,"forks":0,"open_issues":6,"watchers":1,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/opentraffic/datastore/pulls/21"},"html":{"href":"https://github.com/opentraffic/datastore/pull/21"},"issue":{"href":"https://api.github.com/repos/opentraffic/datastore/issues/21"},"comments":{"href":"https://api.github.com/repos/opentraffic/datastore/issues/21/comments"},"review_comments":{"href":"https://api.github.com/repos/opentraffic/datastore/pulls/21/comments"},"review_comment":{"href":"https://api.github.com/repos/opentraffic/datastore/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/opentraffic/datastore/pulls/21/commits"},"statuses":{"href":"https://api.github.com/repos/opentraffic/datastore/statuses/10075050d60c00529d31c2243d94f378d0a8e73a"}}}}
{"actor":{"display_login":"kevinkreiser"}}