[sqlite] using cross-link table

[Jason Jobe]

I'm a big fan of  EOF (Apple's relational to OO tool) and I've been
trying to figure out the easiest/best way to support queries that span
several links through a many-to-many cross-link table and am hoping for
some suggestions and pointers.

Here is the kind of query I would like to write (or something like it):

        // to find people whose great-grandfather's name is Fred

        select p1.* from person p1, person p2 where p2.name='Fred'  AND
related-to (p1.rowid, 'parent.parent.parent', p2.rowid);

Should I auto-generate the link part of the query to be something like

        select p1.* from person p1, person p2,
                link l1, link l2, link l3
        where
                p2.name = 'Fred'
        and
                        (p1.rowid = l1.src_rowid and l1.type = 'parent')
                and
                        (l1.dest_rowid = l2.src_rowid and l2.type = 'parent')   
        and
                        (l2.dest_rowid = l3.src_rowid and l3.type = 'parent')

Well, this is probably correct sql but hopefully you get the idea. So
part of the question is:

        1. How efficient is this?
        2. Is there an ordering that would make it much more efficient?
        3. Would it be better to write the custom function "related-to" to use
its own index?
        4. Can a custom function leverage the sqlite data structures is some
low level ways to gain efficiency?

I'm a pretty good coder and am willing to dive into the sqlite source
as required but not very adept at sql.

Thanks,
Jason