I have some hierarchical data (representing musical instruments and playing techniques), where each leaf in the hierarchy is an audio file.
The nesting depth of leaf nodes will vary because some playing techniques will have sub-articulations.
Example paths to leaf nodes could be
/philharmonia/trumpet/mute/muted_trumpet1.wav
/philharmonia/trumpet/legato/attack/legato_trumpet1.wav
/ircam/violin/pizz/violin_pizz1.wav
I have the data stored using a master table and closure table:
sqlite> PRAGMA table_info(as_node);
0|id|INTEGER|0||1
1|name|TEXT(1024)|1||0
2|file|INTEGER|0||0
3|parent|INTEGER|0||0
sqlite> PRAGMA table_info(as_node_closure);
0|id|INTEGER|0||1
1|ancestor|INTEGER|0||0
2|descendant|INTEGER|0||0
3|depth|INTEGER|0||0
How can I query the database to select all leaf nodes where an ancestor node matches a given string at given nesting level?
For example in plain English: "give me all leaf nodes where ancestor node is 'trumpet' at a nesting level of 2"
With the above data, this should return muted_trumpet1.wav
and legato_trumpet1.wav
.
UPDATE 1
Some example data:
sqlite> select * from as_node
1|BrassP||
2|4Horns||1
3|FT||2
4|DYN_02||3
5|RR_01||4
6|Mic_CL||5
7|4H_CL_DYN2_FT_01.wav|1|6
8|Mic_FM||5
9|4H_FM_DYN2_FT_01.wav|2|8
10|Mic_RM||5
11|4H_RM_DYN2_FT_01.wav|3|10
12|Mic_SUR||5
13|4H_SURR_DYN2_FT_01.wav|4|12
14|DYN_03||3
15|RR_01||14
16|Mic_CL||15
17|4H_CL_DYN3_FT_01.wav|5|16
...
sqlite> select * from as_node_closure limit 10;
1|1|1|0
2|1|2|1
3|2|2|0
4|1|3|2
5|2|3|1
6|3|3|0
7|1|4|3
8|2|4|2
9|3|4|1
10|4|4|0
UPDATE 2:
This gives me pretty much what I want, but I'm sure it it's not the right way:
SELECT name from as_node WHERE id IN (SELECT descendant FROM as_node_closure WHERE ancestor IN (SELECT id FROM as_node WHERE name = "trumpet") AND descendant IN (SELECT id FROM as_node WHERE name LIKE "%.wav"));
This uses the inner SELECT
to get all of the leaf nodes and return only descendants in that list.
depth
represent? The absolute depth, distance from the root? Or the difference between the ancestor and descendant levels?depth
is the distance between ancestor and descendant. Not the absolute level.