From a92a3574b3e4a92850b8542502dcf3836b1501cd Mon Sep 17 00:00:00 2001 From: Terry Truong Date: Tue, 17 May 2022 12:11:53 +1000 Subject: Make search suggestions include non-alt-names --- backend/data/genOtolData.py | 1 + backend/server.py | 26 +++++++++++++++++++------- 2 files changed, 20 insertions(+), 7 deletions(-) (limited to 'backend') diff --git a/backend/data/genOtolData.py b/backend/data/genOtolData.py index 2ae154d..d1567d3 100755 --- a/backend/data/genOtolData.py +++ b/backend/data/genOtolData.py @@ -210,6 +210,7 @@ print("Creating nodes and edges tables") dbCon = sqlite3.connect(dbFile) dbCur = dbCon.cursor() dbCur.execute("CREATE TABLE nodes (name TEXT PRIMARY KEY, tips INT)") +dbCur.execute("CREATE INDEX nodes_idx_nc ON nodes(name COLLATE NOCASE)") dbCur.execute("CREATE TABLE edges (node TEXT, child TEXT, p_support INT, PRIMARY KEY (node, child))") dbCur.execute("CREATE INDEX edges_child_idx ON edges(child)") for node in nodeMap.values(): diff --git a/backend/server.py b/backend/server.py index 08b6f57..54e4803 100755 --- a/backend/server.py +++ b/backend/server.py @@ -100,19 +100,31 @@ def lookupName(name, useReducedTree): cur = dbCon.cursor() results = [] hasMore = False - query = None + # Get node names and alt-names + (query1, query2) = (None, None) if not useReducedTree: - query = "SELECT DISTINCT name, alt_name FROM names" \ + query1 = "SELECT DISTINCT name FROM nodes" \ + " WHERE name LIKE ? ORDER BY length(name) LIMIT ?" + query2 = "SELECT DISTINCT alt_name, name FROM names" \ " WHERE alt_name LIKE ? ORDER BY length(alt_name) LIMIT ?" else: - query = "SELECT DISTINCT names.name, alt_name FROM" \ + query1 = "SELECT DISTINCT name FROM r_nodes" \ + " WHERE name LIKE ? ORDER BY length(name) LIMIT ?" + query2 = "SELECT DISTINCT alt_name, names.name FROM" \ " names INNER JOIN r_nodes ON names.name = r_nodes.name" \ " WHERE alt_name LIKE ? ORDER BY length(alt_name) LIMIT ?" - for row in cur.execute(query, (name + "%", SEARCH_SUGG_LIMIT)): - results.append({"name": row[0], "altName": row[1]}) - if len(results) > SEARCH_SUGG_LIMIT: + # Join results, and get shortest + temp = [] + for row in cur.execute(query1, (name + "%", SEARCH_SUGG_LIMIT + 1)): + temp.append({"name": row[0], "canonicalName": None}) + for row in cur.execute(query2, (name + "%", SEARCH_SUGG_LIMIT + 1)): + temp.append({"name": row[0], "canonicalName": row[1]}) + temp.sort(key=lambda x: x["name"]) + temp.sort(key=lambda x: len(x["name"])) + results = temp[:SEARCH_SUGG_LIMIT] + if len(temp) > SEARCH_SUGG_LIMIT: hasMore = True - del results[-1] + # return [results, hasMore] def lookupNodeInfo(name, useReducedTree): cur = dbCon.cursor() -- cgit v1.2.3