The GRAPH engine allows you to deal with hierarchies and graphs in a purely relational way. So, we can find all children of an item, path from an item to a root node, shortest path between two items, and so on, each with a simple basic query structure using standard SQL grammar.
The engine is implemented as a MySQL/MariaDB 5.1 plugin (we’re working on a 5.0 backport for some clients) and thus runs with an unmodified server.
Demo time! I’ll simplify/strip a little bit here for space reasons, but what’s here is plain cut/paste from a running server, no edits
-- insert a few entries with connections (and multiple paths)
insert into foo (origid, destid) values (1,2), (2,3), (2,4), (4,5), (3,6), (5,6);
-- a regular table to join on to
insert into people values (1,"pearce"),(2,"hunnicut"),(3,"potter"),
(4,"hoolihan"),(5,"winchester"),(6,"mulcahy");
-- find us the shortest path from pearce (1) to mulcahy (6) please
select group_concat(people.name order by seq) as path
from foo join people on (foo.linkid=people.id)
where latch=1 and origid=1 and destid=6;
+--------+--------+--------------------------------+
| origid | destid | path |
+--------+--------+--------------------------------+
| 1 | 6 | pearce,hunnicut,potter,mulcahy |
+--------+--------+--------------------------------+
-- find us all people we can get to from potter (3)
select origid,group_concat(people.name order by seq) as destinations
from foo join people on (foo.linkid=people.id)
where latch=1 and origid=3;
+--------+----------------+
| origid | destinations |
+--------+----------------+
| 3 | mulcahy,potter |
+--------+----------------+
-- find us all people from where we can get to hoolihan (4)
select origid,group_concat(people.name order by seq) as origins
from foo join people on (foo.linkid=people.id)
where latch=1 and destid=4;
+--------+--------------------------+
| origid | origins |
+--------+--------------------------+
| 4 | hoolihan,hunnicut,pearce |
+--------+--------------------------+
So, there you have it. A graph (in this case a simple unidirectional tree, aka hierarchy) that looks like a table to us, as do the resultsets that have been computed.
This is still a early implementation, we’re still enhancing the storage efficiency (in memory) and speed, and adding persistence. We’re also looking for a suitable large dataset that would allow us to seriously test the system, find bugs and assess speed. If you happen to have a large hierarchical structure, but especially a social graph you could obfuscate and give to us, that would be great!
Also, if you’re interested in deploying the GRAPH engine or have questions or additional needs, we’d be happy to talk with you.
select origid,group_concat(people.name order by seq) as destinations from foo join people on (foo.linkid=people.id) where latch=1 and origid=4;
+——–+—————————–+
| origid | destinations |
+——–+—————————–+
| 4 | mulcahy,winchester,hoolihan |
+——–+—————————–+
