Issue 395 - Filter hits statistics backend

sitescripts/filterhits/web/query.py

 # coding: utf-8
 
 # This file is part of the Adblock Plus web scripts,
-# Copyright (C) 2006-2014 Eyeo GmbH
+# Copyright (C) 2006-2015 Eyeo GmbH
 #
 # Adblock Plus is free software: you can redistribute it and/or modify
 # it under the terms of the GNU General Public License version 3 as
 # published by the Free Software Foundation.
 #
 # Adblock Plus is distributed in the hope that it will be useful,
 # but WITHOUT ANY WARRANTY; without even the implied warranty of
 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 # GNU General Public License for more details.
 #
 # You should have received a copy of the GNU General Public License
 # along with Adblock Plus.  If not, see <http://www.gnu.org/licenses/>.
 
 import os, MySQLdb, json
-from urlparse import parse_qs
+from urlparse import parse_qsl
+
 from sitescripts.web import url_handler
-from sitescripts.utils import cached, get_config, setupStderr
+from sitescripts.utils import cached, setupStderr
+from sitescripts.filterhits import common
+from sitescripts.filterhits import db
+
+def query(domain=None, filter=None, skip=0, take=20, order="DESC", order_by="hits", **_):
+  """
+  Returns the SQL and parameters needed to perform a query of the filterhits data.
+  """
+  sql = """SELECT SQL_CALC_FOUND_ROWS domain, filter, hits
+           FROM geometrical_mean as g
+           LEFT JOIN filters as f ON f.sha1=g.filter_sha1
+           %s
+           ORDER BY %s
+           LIMIT %%s, %%s"""
+
+  where_fields = [(s, "%" + p + "%") for s, p in (("domain", domain),
+                                                  ("filter", filter)) if p]
+  where = " AND ".join([f[0] + " LIKE %s" for f in where_fields])
+  where_sql = "WHERE " + where if where else ""
+
+  order = order.upper() if order.upper() in ("ASC", "DESC") else "ASC"
+  order_by_sql = "`%s` %s" % (MySQLdb.escape_string(order_by), order)
+
+  params = [f[1] for f in where_fields] + [int(skip), int(take)]
+  return [sql % (where_sql, order_by_sql)] + params
 
 @url_handler("/query")
-def submit(environ, start_response):
+def query_handler(environ, start_response):
   setupStderr(environ["wsgi.errors"])
+  params = dict(parse_qsl(environ.get('QUERY_STRING', '')))
 
-  response_headers = [("Content-type", "text/plain")]
+  try:
+    db_connection = db.connect()
+    try:
+      results = db.query(db_connection, *query(**params), dict_result=True)
+      total = db.query(db_connection, "SELECT FOUND_ROWS()")[0][0]
+    finally:
+      db_connection.close()
+  except MySQLdb.Error:
+    return common.show_error("Failed to query database!", start_response,
+                             "500 Database error")
+
+  try:
+    echo = int(params["echo"])
+  except (ValueError, KeyError):
+    echo = 0
+
+  response_headers = [("Content-type", "application/json")]
   start_response("200 OK", response_headers)
-  return ["Hello world"]
+  return [json.dumps({"results": results, "echo": echo,
+                      "total": total, "count": len(results)})]