Rietveld Code Review Tool
Help | Bug tracker | Discussion group | Source code

Delta Between Two Patch Sets: sitescripts/filterhits/web/query.py

Issue 4615801646612480: Issue 395 - Filter hits statistics backend (Closed)
Left Patch Set: Addressed comments. Created Feb. 24, 2015, 5:59 p.m.
Right Patch Set: Addressed further comments from Sebastian. Created April 2, 2015, 10:13 a.m.
Left:
Right:
Use n/p to move between diff chunks; N/P to move between comments.
Jump to:
Left: Side by side diff | Download
Right: Side by side diff | Download
« no previous file with change/comment | « sitescripts/filterhits/web/common.py ('k') | sitescripts/filterhits/web/submit.py » ('j') | no next file with change/comment »
Toggle Intra-line Diffs ('i') | Expand Comments ('e') | Collapse Comments ('c') | Show Comments Hide Comments ('s')
LEFTRIGHT
1 # coding: utf-8 1 # coding: utf-8
2 2
3 # This file is part of the Adblock Plus web scripts, 3 # This file is part of the Adblock Plus web scripts,
4 # Copyright (C) 2006-2015 Eyeo GmbH 4 # Copyright (C) 2006-2015 Eyeo GmbH
5 # 5 #
6 # Adblock Plus is free software: you can redistribute it and/or modify 6 # Adblock Plus is free software: you can redistribute it and/or modify
7 # it under the terms of the GNU General Public License version 3 as 7 # it under the terms of the GNU General Public License version 3 as
8 # published by the Free Software Foundation. 8 # published by the Free Software Foundation.
9 # 9 #
10 # Adblock Plus is distributed in the hope that it will be useful, 10 # Adblock Plus is distributed in the hope that it will be useful,
11 # but WITHOUT ANY WARRANTY; without even the implied warranty of 11 # but WITHOUT ANY WARRANTY; without even the implied warranty of
12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 # GNU General Public License for more details. 13 # GNU General Public License for more details.
14 # 14 #
15 # You should have received a copy of the GNU General Public License 15 # You should have received a copy of the GNU General Public License
16 # along with Adblock Plus. If not, see <http://www.gnu.org/licenses/>. 16 # along with Adblock Plus. If not, see <http://www.gnu.org/licenses/>.
17 17
18 import os, MySQLdb, json 18 import json
19 import os
20 import traceback
19 from urlparse import parse_qsl 21 from urlparse import parse_qsl
20 22
23 import MySQLdb
24
21 from sitescripts.web import url_handler 25 from sitescripts.web import url_handler
22 from sitescripts.utils import cached, get_config, setupStderr 26 from sitescripts.utils import cached, setupStderr
23 from sitescripts.filterhits import common
24 from sitescripts.filterhits import db 27 from sitescripts.filterhits import db
28 from sitescripts.filterhits.web import common
25 29
26 def query(domain=None, filter=None, skip=0, take=20, order="DESC", order_by="hit s", **_): 30 def query(domain=None, filter=None, skip=0, take=20, order="DESC", order_by="fre quency", **_):
27 """ 31 """
28 Returns the SQL and parameters needed to perform a query of the filterhits dat a. 32 Returns the SQL and parameters needed to perform a query of the filterhits dat a.
29 """ 33 """
30 sql = """SELECT SQL_CALC_FOUND_ROWS domain, filter, hits 34 sql = """SELECT SQL_CALC_FOUND_ROWS domain, filter, frequency
31 FROM geometrical_mean as g 35 FROM frequencies as freq
32 LEFT JOIN filters as f ON f.sha1=g.filter_sha1 36 LEFT JOIN filters as f ON f.sha1=freq.filter_sha1
33 %s 37 %s
34 ORDER BY %s 38 ORDER BY %s
35 LIMIT %%s, %%s""" 39 LIMIT %%s, %%s"""
36 40
37 where_fields = [(s, "%" + p + "%") for s, p in (("domain", domain), 41 where = zip(*[("%s LIKE %%s" % s, "%%%s%%" % p) for s, p in (("domain", domain ),
38 ("filter", filter)) if p] 42 ("filter", filter )) if p])
39 where = " AND ".join([f[0] + " LIKE %s" for f in where_fields]) 43 if where:
40 where_sql = "WHERE " + where if where else "" 44 where_fields, params = where
45 where_sql = "WHERE " + " AND ".join(where_fields)
46 else:
47 where_sql = ""
48 params = []
41 49
42 order = order.upper() if order.upper() in ("ASC", "DESC") else "ASC" 50 order = order.upper() if order.upper() in ("ASC", "DESC") else "ASC"
43 order_by_sql = "`%s` %s" % (MySQLdb.escape_string(order_by), order) 51 if order_by not in ["filter", "domain", "frequency"]:
52 order_by = "frequency"
53 order_by_sql = "`%s` %s" % (order_by, order)
44 54
45 params = [f[1] for f in where_fields] + [int(skip), int(take)] 55 params = list(params) + [int(skip), int(take)]
46 return [sql % (where_sql, order_by_sql)] + params 56 return [sql % (where_sql, order_by_sql)] + params
47 57
48 @url_handler("/query") 58 @url_handler("/query")
49 def query_handler(environ, start_response): 59 def query_handler(environ, start_response):
50 setupStderr(environ["wsgi.errors"]) 60 setupStderr(environ["wsgi.errors"])
51 config = get_config() 61 params = dict(parse_qsl(environ.get("QUERY_STRING", "")))
52 params = dict(parse_qsl(environ.get('QUERY_STRING', '')))
53 62
54 try: 63 try:
55 db_connection = db.connect(config.get("filterhitstats", "dbuser"), 64 db_connection = db.connect()
56 config.get("filterhitstats", "dbpassword"),
57 config.get("filterhitstats", "database"))
58 try: 65 try:
59 results = db.query(db_connection, *query(**params), dict_result=True) 66 results = db.query(db_connection, *query(**params), dict_result=True)
60 total = db.query(db_connection, "SELECT FOUND_ROWS()")[0][0] 67 total = db.query(db_connection, "SELECT FOUND_ROWS()")[0][0]
61 finally: 68 finally:
62 db_connection.close() 69 db_connection.close()
63 except MySQLdb.Error: 70 except MySQLdb.Error:
64 return common.showError("Failed to query database!", start_response, 71 traceback.print_exc()
65 "500 Database error") 72 return common.show_error("Failed to query database!", start_response,
73 "500 Database error")
66 74
67 try: 75 try:
68 echo = int(params["echo"]) 76 echo = int(params["echo"])
69 except (ValueError, KeyError): 77 except (ValueError, KeyError):
70 echo = 0 78 echo = 0
71 79
72 response_headers = [("Content-type", "application/json")] 80 response_headers = [("Content-type", "application/json; charset=utf-8")]
73 start_response("200 OK", response_headers) 81 start_response("200 OK", response_headers)
74 return [json.dumps({"results": results, "echo": echo, 82 return [json.dumps({"results": results, "echo": echo,
75 "total": total, "count": len(results)})] 83 "total": total, "count": len(results)},
84 ensure_ascii=False).encode("utf-8")]
LEFTRIGHT

Powered by Google App Engine
This is Rietveld