commit 377771bb9b8302d5207b776dc2111d23f69992bc Author: Karsten Loesing <karsten.loesing@gmx.net> Date: Thu Sep 27 14:24:27 2012 +0200 Use the same LIKE-based approach for lookup parameter. There were two problems with requests containing the lookup parameter: - We currently mix AND and OR conditions in the SELECT statement without correctly parethesizing them. This could lead to surprising results when combining the lookup parameter with other parameters. - Lookup requests took 1.6 seconds on my local machine compared to 20 milliseconds for other requests. Using the same LIKE-based approach for the lookup parameter that we also used for the search parameter fixes both problems. --- pyonionoo/database.py | 17 +++++++++-------- pyonionoo/handlers/arguments.py | 8 +++----- pyonionoo/parser.py | 2 ++ 3 files changed, 14 insertions(+), 13 deletions(-) diff --git a/pyonionoo/database.py b/pyonionoo/database.py index 1360973..f84f2c4 100644 --- a/pyonionoo/database.py +++ b/pyonionoo/database.py @@ -54,7 +54,8 @@ hostname TEXT, time_lookup TEXT, flags TEXT, addresses TEXT, -search TEXT collate NOCASE +search TEXT collate NOCASE, +lookup TEXT collate NOCASE """ def _create_table(conn, tbl_name, schema): @@ -144,7 +145,8 @@ def update_databases(summary_file=None): # id field right after we execute the (individual) insert statements. summary_fields = ('type', 'nickname', 'fingerprint', 'hashed_fingerprint', 'running', 'time_published', 'or_port', 'dir_port', 'consensus_weight', - 'country_code', 'hostname', 'time_lookup', 'flags', 'addresses', 'search') + 'country_code', 'hostname', 'time_lookup', 'flags', 'addresses', + 'search', 'lookup') insert_stmt = 'insert into %s (%s) values (%s)' @@ -185,7 +187,7 @@ def get_database_conn(): conn = sqlite3.connect(DBNAME) return conn -def query_summary_tbl(running_filter=None, type_filter=None, hex_fingerprint_filter=None, +def query_summary_tbl(running_filter=None, type_filter=None, lookup_filter=None, country_filter=None, search_filter=None, order_field=None, order_asc=True, offset_value=None, limit_value=None, fields=('fingerprint',)): @@ -204,9 +206,8 @@ def query_summary_tbl(running_filter=None, type_filter=None, hex_fingerprint_fil clauses.append("running = %s" % int(running_filter)) if type_filter: clauses.append("type = '%s'" % type_filter) - if hex_fingerprint_filter: - clauses.append("fingerprint = '%s' or hashed_fingerprint = '%s'" % - (hex_fingerprint_filter, hex_fingerprint_filter)) + if lookup_filter: + clauses.append("lookup like '%% %s%%'" % lookup_filter) if country_filter: clauses.append("country_code = '%s'" % country_filter) where_clause = ('WHERE %s' % ' and '.join(clauses)) if clauses else '' @@ -254,7 +255,7 @@ def get_timestamp(): return (relay_timestamp, bridge_timestamp) -def get_summary_routers(running_filter=None, type_filter=None, hex_fingerprint_filter=None, +def get_summary_routers(running_filter=None, type_filter=None, lookup_filter=None, country_filter=None, search_filter=None, order_field=None, order_asc=True, offset_value=None, limit_value=None): """ @@ -272,7 +273,7 @@ def get_summary_routers(running_filter=None, type_filter=None, hex_fingerprint_f relays, bridges = [], [] fields = ('type', 'nickname', 'fingerprint', 'running', 'country_code', 'time_published', 'consensus_weight') - for row in query_summary_tbl(running_filter, type_filter, hex_fingerprint_filter, + for row in query_summary_tbl(running_filter, type_filter, lookup_filter, country_filter, search_filter,order_field, order_asc, offset_value, limit_value, fields): router = Router() diff --git a/pyonionoo/handlers/arguments.py b/pyonionoo/handlers/arguments.py index e954921..ecb8653 100644 --- a/pyonionoo/handlers/arguments.py +++ b/pyonionoo/handlers/arguments.py @@ -26,10 +26,8 @@ def parse(arguments): # request parameter is not present at all). running_filter = None type_filter = None - hex_fingerprint_filter = None + lookup_filter = None country_filter = None - - # TODO: Handle 'search' parameter. search_filter = None # Ordering offset and limit. @@ -65,7 +63,7 @@ def parse(arguments): raise cyclone.web.HTTPError(400, error_msg) if key == "lookup": - hex_fingerprint_filter = values[0] + lookup_filter = values[0] if key == "country": country_filter = values[0] @@ -109,7 +107,7 @@ def parse(arguments): return { 'running_filter' : running_filter, 'type_filter' : type_filter, - 'hex_fingerprint_filter' : hex_fingerprint_filter, + 'lookup_filter' : lookup_filter, 'country_filter' : country_filter, 'search_filter' : search_filter, 'order_field' : order_field, diff --git a/pyonionoo/parser.py b/pyonionoo/parser.py index 8e62e0a..f61f945 100644 --- a/pyonionoo/parser.py +++ b/pyonionoo/parser.py @@ -89,6 +89,8 @@ class Router: if field == "search": value = ' %s %s %s %s' % (self.fingerprint, self.hashed_fingerprint, self.nickname, self.address) + elif field == "lookup": + value = ' %s %s' % (self.fingerprint, self.hashed_fingerprint) elif field == "flags": value = ' '.join(self.flags) # add leading space