From a4673571570816a06d4188169fc00dada79ec0a3 Mon Sep 17 00:00:00 2001 From: Terry Truong Date: Sun, 15 May 2022 00:46:39 +1000 Subject: Converted nodes+r_nodes tables into nodes+edges+r_nodes+r_edges Conversion avoids encoding node children as JSON strings, and allows for easier querying of edge data. Adjusted server to use new format. Also added some table indexes for common operations. --- backend/data/genOtolData.py | 20 ++++++++++---------- 1 file changed, 10 insertions(+), 10 deletions(-) (limited to 'backend/data/genOtolData.py') diff --git a/backend/data/genOtolData.py b/backend/data/genOtolData.py index 9298106..2ae154d 100755 --- a/backend/data/genOtolData.py +++ b/backend/data/genOtolData.py @@ -30,10 +30,6 @@ idToName = {} # Maps node IDs to names nameToFirstId = {} # Maps node names to first found ID (names might have multiple IDs) dupNameToIds = {} # Maps names of nodes with multiple IDs to those node IDs -# Check for existing db -if os.path.exists(dbFile): - print("ERROR: Existing {} db".format(dbFile), file=sys.stderr) - sys.exit(1) # Parse treeFile print("Parsing tree file") data = None @@ -210,14 +206,18 @@ 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))") +dbCur.execute("CREATE INDEX edges_child_idx ON edges(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() -- cgit v1.2.3