Core/Waypoints: Refactor to split data into path and node related info in db (#29506)

2 files changed, 26 insertions, 29 deletions

diff --git a/src/server/database/Database/Implementation/WorldDatabase.cpp b/src/server/database/Database/Implementation/WorldDatabase.cpp
index fbcc654c7e5..e90a67f4392 100644
--- a/src/server/database/Database/Implementation/WorldDatabase.cpp
+++ b/src/server/database/Database/Implementation/WorldDatabase.cpp
@@ -44,19 +44,20 @@ void WorldDatabaseConnection::DoPrepareStatements()
     PrepareStatement(WORLD_UPD_CREATURE_WANDER_DISTANCE, "UPDATE creature SET wander_distance = ?, MovementType = ? WHERE guid = ?", CONNECTION_ASYNC);
     PrepareStatement(WORLD_UPD_CREATURE_SPAWN_TIME_SECS, "UPDATE creature SET spawntimesecs = ? WHERE guid = ?", CONNECTION_ASYNC);
     PrepareStatement(WORLD_INS_CREATURE_FORMATION, "INSERT INTO creature_formations (leaderGUID, memberGUID, dist, angle, groupAI) VALUES (?, ?, ?, ?, ?)", CONNECTION_ASYNC);
-    PrepareStatement(WORLD_INS_WAYPOINT_DATA, "INSERT INTO waypoint_data (id, point, position_x, position_y, position_z, orientation) VALUES (?, ?, ?, ?, ?, ?)", CONNECTION_ASYNC);
-    PrepareStatement(WORLD_DEL_WAYPOINT_DATA, "DELETE FROM waypoint_data WHERE id = ? AND point = ?", CONNECTION_ASYNC);
-    PrepareStatement(WORLD_UPD_WAYPOINT_DATA_POINT, "UPDATE waypoint_data SET point = point - 1 WHERE id = ? AND point > ?", CONNECTION_ASYNC);
-    PrepareStatement(WORLD_UPD_WAYPOINT_DATA_POSITION, "UPDATE waypoint_data SET position_x = ?, position_y = ?, position_z = ?, orientation = ? where id = ? AND point = ?", CONNECTION_ASYNC);
-    PrepareStatement(WORLD_SEL_WAYPOINT_DATA_MAX_ID, "SELECT MAX(id) FROM waypoint_data", CONNECTION_SYNCH);
-    PrepareStatement(WORLD_SEL_WAYPOINT_DATA_MAX_POINT, "SELECT MAX(point) FROM waypoint_data WHERE id = ?", CONNECTION_SYNCH);
-    PrepareStatement(WORLD_SEL_WAYPOINT_DATA_BY_ID, "SELECT point, position_x, position_y, position_z, orientation, move_type, delay FROM waypoint_data WHERE id = ? ORDER BY point", CONNECTION_SYNCH);
-    PrepareStatement(WORLD_SEL_WAYPOINT_DATA_POS_BY_ID, "SELECT point, position_x, position_y, position_z, orientation FROM waypoint_data WHERE id = ?", CONNECTION_SYNCH);
-    PrepareStatement(WORLD_SEL_WAYPOINT_DATA_POS_FIRST_BY_ID, "SELECT position_x, position_y, position_z, orientation FROM waypoint_data WHERE point = 1 AND id = ?", CONNECTION_SYNCH);
-    PrepareStatement(WORLD_SEL_WAYPOINT_DATA_POS_LAST_BY_ID, "SELECT position_x, position_y, position_z, orientation FROM waypoint_data WHERE id = ? ORDER BY point DESC LIMIT 1", CONNECTION_SYNCH);
-    PrepareStatement(WORLD_SEL_WAYPOINT_DATA_BY_POS, "SELECT id, point FROM waypoint_data WHERE (abs(position_x - ?) <= ?) and (abs(position_y - ?) <= ?) and (abs(position_z - ?) <= ?)", CONNECTION_SYNCH);
-    PrepareStatement(WORLD_INS_CREATURE_ADDON, "INSERT INTO creature_addon(guid, path_id) VALUES (?, ?)", CONNECTION_ASYNC);
-    PrepareStatement(WORLD_UPD_CREATURE_ADDON_PATH, "UPDATE creature_addon SET path_id = ? WHERE guid = ?", CONNECTION_ASYNC);
+    PrepareStatement(WORLD_SEL_WAYPOINT_PATH_BY_PATHID, "SELECT PathId, MoveType, Flags FROM waypoint_path WHERE PathId = ?", CONNECTION_SYNCH);
+    PrepareStatement(WORLD_INS_WAYPOINT_PATH_NODE, "INSERT INTO waypoint_path_node (PathId, NodeId, PositionX, PositionY, PositionZ, Orientation) VALUES (?, ?, ?, ?, ?, ?)", CONNECTION_ASYNC);
+    PrepareStatement(WORLD_DEL_WAYPOINT_PATH_NODE, "DELETE FROM waypoint_path_node WHERE PathId = ? AND NodeId = ?", CONNECTION_ASYNC);
+    PrepareStatement(WORLD_UPD_WAYPOINT_PATH_NODE, "UPDATE waypoint_path_node SET NodeId = NodeId - 1 WHERE PathId = ? AND NodeId > ?", CONNECTION_ASYNC);
+    PrepareStatement(WORLD_UPD_WAYPOINT_PATH_NODE_POSITION, "UPDATE waypoint_path_node SET PositionX = ?, PositionY = ?, PositionZ = ?, Orientation = ? WHERE PathId = ? AND NodeId = ?", CONNECTION_ASYNC);
+    PrepareStatement(WORLD_SEL_WAYPOINT_PATH_NODE_MAX_PATHID, "SELECT MAX(PathId) FROM waypoint_path_node", CONNECTION_SYNCH);
+    PrepareStatement(WORLD_SEL_WAYPOINT_PATH_NODE_MAX_NODEID, "SELECT MAX(NodeId) FROM waypoint_path_node WHERE PathId = ?", CONNECTION_SYNCH);
+    PrepareStatement(WORLD_SEL_WAYPOINT_PATH_NODE_BY_PATHID, "SELECT PathId, NodeId, PositionX, PositionY, PositionZ, Orientation, Delay FROM waypoint_path_node WHERE PathId = ? ORDER BY NodeId", CONNECTION_SYNCH);
+    PrepareStatement(WORLD_SEL_WAYPOINT_PATH_NODE_POS_BY_PATHID, "SELECT NodeId, PositionX, PositionY, PositionZ, Orientation FROM waypoint_path_node WHERE PathId = ?", CONNECTION_SYNCH);
+    PrepareStatement(WORLD_SEL_WAYPOINT_PATH_NODE_POS_FIRST_BY_PATHID, "SELECT PositionX, PositionY, PositionZ, Orientation FROM waypoint_path_node WHERE NodeId = 1 AND PathId = ?", CONNECTION_SYNCH);
+    PrepareStatement(WORLD_SEL_WAYPOINT_PATH_NODE_POS_LAST_BY_PATHID, "SELECT PositionX, PositionY, PositionZ, Orientation FROM waypoint_path_node WHERE PathId = ? ORDER BY NodeId DESC LIMIT 1", CONNECTION_SYNCH);
+    PrepareStatement(WORLD_SEL_WAYPOINT_PATH_NODE_BY_POS, "SELECT PathId, NodeId FROM waypoint_path_node WHERE (abs(PositionX - ?) <= ?) and (abs(PositionY - ?) <= ?) and (abs(PositionZ - ?) <= ?)", CONNECTION_SYNCH);
+    PrepareStatement(WORLD_INS_CREATURE_ADDON, "INSERT INTO creature_addon(guid, PathId) VALUES (?, ?)", CONNECTION_ASYNC);
+    PrepareStatement(WORLD_UPD_CREATURE_ADDON_PATH, "UPDATE creature_addon SET PathId = ? WHERE guid = ?", CONNECTION_ASYNC);
     PrepareStatement(WORLD_DEL_CREATURE_ADDON, "DELETE FROM creature_addon WHERE guid = ?", CONNECTION_ASYNC);
     PrepareStatement(WORLD_SEL_CREATURE_ADDON_BY_GUID, "SELECT guid FROM creature_addon WHERE guid = ?", CONNECTION_SYNCH);
     PrepareStatement(WORLD_DEL_CREATURE, "DELETE FROM creature WHERE guid = ?", CONNECTION_ASYNC);
diff --git a/src/server/database/Database/Implementation/WorldDatabase.h b/src/server/database/Database/Implementation/WorldDatabase.h
index ea6475786d9..d24be6862b1 100644
--- a/src/server/database/Database/Implementation/WorldDatabase.h
+++ b/src/server/database/Database/Implementation/WorldDatabase.h
@@ -49,22 +49,18 @@ enum WorldDatabaseStatements : uint32
     WORLD_UPD_CREATURE_WANDER_DISTANCE,
     WORLD_UPD_CREATURE_SPAWN_TIME_SECS,
     WORLD_INS_CREATURE_FORMATION,
-    WORLD_INS_WAYPOINT_DATA,
-    WORLD_DEL_WAYPOINT_DATA,
-    WORLD_UPD_WAYPOINT_DATA_POINT,
-    WORLD_UPD_WAYPOINT_DATA_POSITION,
-    WORLD_UPD_WAYPOINT_DATA_WPGUID,
-    WORLD_UPD_WAYPOINT_DATA_ALL_WPGUID,
-    WORLD_SEL_WAYPOINT_DATA_MAX_ID,
-    WORLD_SEL_WAYPOINT_DATA_BY_ID,
-    WORLD_SEL_WAYPOINT_DATA_POS_BY_ID,
-    WORLD_SEL_WAYPOINT_DATA_POS_FIRST_BY_ID,
-    WORLD_SEL_WAYPOINT_DATA_POS_LAST_BY_ID,
-    WORLD_SEL_WAYPOINT_DATA_BY_WPGUID,
-    WORLD_SEL_WAYPOINT_DATA_ALL_BY_WPGUID,
-    WORLD_SEL_WAYPOINT_DATA_MAX_POINT,
-    WORLD_SEL_WAYPOINT_DATA_BY_POS,
-    WORLD_SEL_WAYPOINT_DATA_WPGUID_BY_ID,
+    WORLD_SEL_WAYPOINT_PATH_BY_PATHID,
+    WORLD_INS_WAYPOINT_PATH_NODE,
+    WORLD_DEL_WAYPOINT_PATH_NODE,
+    WORLD_UPD_WAYPOINT_PATH_NODE,
+    WORLD_UPD_WAYPOINT_PATH_NODE_POSITION,
+    WORLD_SEL_WAYPOINT_PATH_NODE_MAX_PATHID,
+    WORLD_SEL_WAYPOINT_PATH_NODE_BY_PATHID,
+    WORLD_SEL_WAYPOINT_PATH_NODE_POS_BY_PATHID,
+    WORLD_SEL_WAYPOINT_PATH_NODE_POS_FIRST_BY_PATHID,
+    WORLD_SEL_WAYPOINT_PATH_NODE_POS_LAST_BY_PATHID,
+    WORLD_SEL_WAYPOINT_PATH_NODE_MAX_NODEID,
+    WORLD_SEL_WAYPOINT_PATH_NODE_BY_POS,
     WORLD_UPD_CREATURE_ADDON_PATH,
     WORLD_INS_CREATURE_ADDON,
     WORLD_DEL_CREATURE_ADDON,