Select all LoadNG protocol messages in this log:
SELECT * FROM Frame WHERE (CmdType & 16383) = 4096 OR (CmdType & 16383) = 4097 OR (CmdType & 16383) = 4098
Or, select last path responses for a single node:
SELECT * FROM Frame,PREP WHERE Frame.idFrame = PREP.PREP_idframe and PREP.Originator=3 ORDER BY idFrame DESC LIMIT 1
As opposed to MeshHeader table, PREP only contains short address information, therefore, filter for Originator field is simply an integer.
Paths are stored in two tables: LoadNG_ForwardPaths and LoadNG_ReversePaths. Both tables are linked to Frame/PREP tables by a foreign key. To retrieve the forward Path for a Frame we need to join three tables like:
SELECT LOADng_ForwardPaths.* FROM LOADng_ForwardPaths WHERE ForwardPaths_idFrame = (SELECT idFrame FROM Frame,PREP WHERE Frame.idFrame = PREP.PREP_idframe and PREP.Originator=3 ORDER BY idFrame DESC LIMIT 1)
Equivalently, the reverse path statement will be:
SELECT * FROM LOADng_ReversePaths WHERE ReversePaths_idFrame = (SELECT idFrame FROM Frame,PREP WHERE Frame.idFrame = PREP.PREP_idframe and PREP.Originator=3 ORDER BY idFrame DESC LIMIT 1)
If we need the full path (forward and reverse) in one selection it is possible to execute a union of both selects:
SELECT LOADng_ForwardPaths.* FROM LOADng_ForwardPaths WHERE ForwardPaths_idFrame = (SELECT idFrame FROM Frame, PREP WHERE Frame.idFrame = PREP.PREP_idframe and PREP.Originator=3 ORDER BY idFrame DESC LIMIT 1)
UNION
SELECT * FROM LOADng_ReversePaths WHERE ReversePaths_idFrame = (SELECT idFrame FROM Frame, PREP WHERE Frame.idFrame = PREP.PREP_idframe and PREP.Originator=3 ORDER BY idFrame DESC LIMIT 1)