Split nodes table into nodes and edgessplit-nodes-table

This noticeably slowed server responses. Responses to a client's initial
node query slowed from 20-30ms to 200-300ms.

3 files changed, 34 insertions, 25 deletions

diff --git a/backend/data/README.md b/backend/data/README.md
index 9f0ea82..b3fd53b 100644
--- a/backend/data/README.md
+++ b/backend/data/README.md
@@ -3,8 +3,8 @@ File Generation Process
 
 1   Tree Structure Data
     1   Obtain data in otol/, as specified in it's README.
-    2   Run genOtolData.py, which creates data.db, and adds a 'nodes'
-        table using data in otol/*.
+    2   Run genOtolData.py, which creates data.db, and adds
+        'nodes' and 'edges' tables using data in otol/*.
 2   Name Data for Search
     1   Obtain data in eol/, as specified in it's README.
     2   Run genEolNameData.py, which adds 'names' and 'eol\_ids' tables to data.db, 
@@ -26,7 +26,9 @@ File Generation Process
 data.db tables
 ==============
 -   nodes <br>
-    name TEXT PRIMARY KEY, children TEXT, parent TEXT, tips INT, p\_support INT
+    name TEXT PRIMARY KEY, tips INT
+-   edges <br>
+    node TEXT, child TEXT, p\_support INT, PRIMARY KEY (node, child)
 -   names <br>
     name TEXT, alt\_name TEXT, pref\_alt INT, PRIMARY KEY(name, alt\_name)
 -   eol\_ids <br>
diff --git a/backend/data/genOtolData.py b/backend/data/genOtolData.py
index 7dfac54..236c537 100755
--- a/backend/data/genOtolData.py
+++ b/backend/data/genOtolData.py
@@ -210,14 +210,17 @@ for [id, node] in nodeMap.items():
 	if node["parent"] == None:
 		node["pSupport"] = True
 # Create db
-print("Creating nodes table")
+print("Creating nodes and edges tables")
 dbCon = sqlite3.connect(dbFile)
 dbCur = dbCon.cursor()
-dbCur.execute("CREATE TABLE nodes (name TEXT PRIMARY KEY, children TEXT, parent TEXT, tips INT, p_support INT)")
+dbCur.execute("CREATE TABLE nodes (name TEXT PRIMARY KEY, tips INT)")
+dbCur.execute("CREATE TABLE edges (node TEXT, child TEXT, p_support INT, PRIMARY KEY (node, child))")
 for node in nodeMap.values():
-	childNames = [nodeMap[id]["name"] for id in node["children"]]
-	parentName = "" if node["parent"] == None else nodeMap[node["parent"]]["name"]
-	dbCur.execute("INSERT INTO nodes VALUES (?, ?, ?, ?, ?)",
-		(node["name"], json.dumps(childNames), parentName, node["tips"], 1 if node["pSupport"] else 0))
+	dbCur.execute("INSERT INTO nodes VALUES (?, ?)", (node["name"], node["tips"]))
+	childIds = node["children"]
+	for childId in childIds:
+		childNode = nodeMap[childId]
+		dbCur.execute("INSERT INTO edges VALUES (?, ?, ?)",
+			(node["name"], childNode["name"], 1 if childNode["pSupport"] else 0))
 dbCon.commit()
 dbCon.close()
diff --git a/backend/server.py b/backend/server.py
index 8e4a90f..f0292aa 100755
--- a/backend/server.py
+++ b/backend/server.py
@@ -28,25 +28,31 @@ dbCon.enable_load_extension(True)
 dbCon.load_extension('./data/spellfix')
 # Some functions
 def lookupNodes(names):
+	# Get node info
 	nodeObjs = {}
 	cur = dbCon.cursor()
-	# Get node info
-	query = "SELECT name, children, parent, tips, p_support FROM nodes WHERE" \
-		" name IN ({})".format(",".join(["?"] * len(names)))
-	namesForImgs = []
-	firstSubnames = {}
-	secondSubnames = {}
-	for row in cur.execute(query, names):
-		name = row[0]
-		nodeObj = {
-			"children": json.loads(row[1]),
-			"parent": None if row[2] == "" else row[2],
-			"tips": row[3],
-			"pSupport": True if row[4] == 1 else False,
+	query = "SELECT name, tips from nodes WHERE name IN ({})".format(",".join(["?"] * len(names)))
+	for (nodeName, tips) in cur.execute(query, names):
+		nodeObjs[nodeName] = {
+			"children": [],
+			"parent": None,
+			"tips": tips,
+			"pSupport": False,
 			"commonName": None,
 			"imgName": None,
 		}
-		# Check for image file
+	query = "SELECT node, child FROM edges WHERE node IN ({})".format(",".join(["?"] * len(names)))
+	for (nodeName, childName) in cur.execute(query, names):
+		nodeObjs[nodeName]["children"].append(childName)
+	query = "SELECT node, child, p_support FROM edges WHERE child IN ({})".format(",".join(["?"] * len(names)))
+	for (nodeName, childName, pSupport) in cur.execute(query, names):
+		nodeObjs[childName]["parent"] = nodeName
+		nodeObjs[childName]["pSupport"] = pSupport
+	# Get names for image files
+	namesForImgs = []
+	firstSubnames = {}
+	secondSubnames = {}
+	for (name, nodeObj) in nodeObjs.items():
 		match = re.fullmatch(r"\[(.+) \+ (.+)]", name)
 		if match == None:
 			namesForImgs.append(name)
@@ -56,8 +62,6 @@ def lookupNodes(names):
 			namesForImgs.extend([name1, name2])
 			firstSubnames[name1] = name
 			secondSubnames[name2] = name
-		# Add node object
-		nodeObjs[name] = nodeObj
 	# Get image names
 	query = "SELECT name, id FROM eol_ids WHERE" \
 		" name IN ({})".format(",".join(["?"] * len(namesForImgs)))