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.

1 files changed, 9 insertions, 6 deletions

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()