Sqlite3 "chained" query

Question

I need to create a configuration file from a data file that looks as follows:

MAN1_TIME '01-JAN-2010 00:00:00.0000 UTC'
MAN1_RX 123.45
MAN1_RY 123.45
MAN1_RZ 123.45
MAN1_NEXT 'MAN2'
MAN2_TIME '01-MAR-2010 00:00:00.0000 UTC'
MAN2_RX 123.45
[...]
MAN2_NEXT 'MANX'
[...]
MANX_TIME [...]

This file describes different "legs" of a trajectory. In this case, MAN1 is chained to MAN2, and MAN2 to MANX. In the original file, the chains are not as obvious (i.e., they are non-sequential).

I've managed to read the file and store in an Sqlite3 database (I'm using the Python interface). The table is stored with three columns: Id, Par, and Val; for instance, Id='MAN1', Par='RX', and Val='123.45'.

I'm interested in querying such database for obtaining the information related to 'n' legs. In English, that would be:

"Select RX,RY,RZ for the next five legs starting on MAN1"

So the query would go to MAN1, retrieve RX, RY, RZ, then read the parameter NEXT and go to that Id, retrieve RX, RY, RZ; read the parameter NEXT; go to that one ... like this five times.

How can I pass such query with "dynamic parameters"?

Thank you.

Answer 1

Following OMG Ponies's comment, and http://sqllessons.com/categories.html, perhaps try something like this:

select 
       MAN1.RX as MAN1_RX,
       MAN1.RY as MAN1_RY,
       MAN1.RZ  as MAN1_RZ,
       MAN2.RX as MAN2_RX,
       MAN2.RY as MAN2_RY,
       MAN2.RZ  as MAN2_RZ,
       MAN3.RX as MAN3_RX,
       MAN3.RY as MAN3_RY,
       MAN3.RZ  as MAN3_RZ,
  from table as MAN1
left outer
  join table as MAN2
    on MAN1.NEXT = MAN2.Id
left outer
  join table as MAN3
    on MAN3.NEXT = MAN2.Id
where MAN1.Id = 'MAN1'

PS. I'm not terribly familiar with sqlite, but assuming it does not have direct hierarchical query syntax, then this link (http://www.dbforums.com/mysql/1638233-equivalent-start-connect-mysql.html) points to the above work-around.

Answer 2

I found the answer to my own question in the SQLAlchemy website. From the documentation:

The adjacency list pattern is a common relational pattern whereby a table contains a foreign key reference to itself. This is the most common and simple way to represent hierarchical data in flat tables. The other way is the “nested sets” model, sometimes called “modified preorder”. Despite what many online articles say about modified preorder, the adjacency list model is probably the most appropriate pattern for the large majority of hierarchical storage needs, for reasons of concurrency, reduced complexity, and that modified preorder has little advantage over an application which can fully load subtrees into the application space.

SQLAlchemy commonly refers to an adjacency list relationship as a self-referential mapper. In this example, we’ll work with a single table called treenodes to represent a tree structure:

A graph such as the following:

root --+---> child1
       +---> child2 --+--> subchild1
       |              +--> subchild2
       +---> child3

Would be represented with data such as:

id       parent_id     data
---      -------       ----
1        NULL          root
2        1             child1
3        1             child2
4        3             subchild1
5        3             subchild2
6        1             child3