Open jmatsushita opened 1 year ago
Also unfortunately:
TutorialD (master/main): foreign key src_tgt rec{src} in rec{tgt}
ERR: RelationTypeMismatchError attributesFromList [(Attribute "src" IntegerAtomType)] attributesFromList [(Attribute "tgt" IntegerAtomType)]
UPDATE: I think this is non-sensical though π
Ok what I was really trying to do was:
TutorialD (master/main): nod := relation{tuple{nid 0, lbl "a"}}
TutorialD (master/main): insert nod relation{tuple{nid 1, lbl "b"}}
TutorialD (master/main): :showexpr nod
βββββββββββ¬βββββββββββββ
βlbl::Textβnid::Integerβ
βββββββββββΌβββββββββββββ€
β"b" β1 β
β"a" β0 β
βββββββββββ΄βββββββββββββ
TutorialD (master/main): foreign key src_nod (rec rename {src as nid}){nid} in nod{nid}
ERR: InclusionDependencyCheckError "src_nod" Nothing
TutorialD (master/main): insert nod relation{tuple{nid 2, lbl "c"}}
TutorialD (master/main): insert nod relation{tuple{nid 3, lbl "d"}}
TutorialD (master/main): insert nod relation{tuple{nid 4, lbl "e"}}
TutorialD (master/main): foreign key src_nod (rec rename {src as nid}){nid} in nod{nid}
TutorialD (master/main): foreign key tgt_nod (rec rename {tgt as nid}){nid} in nod{nid}
Works nicely
Oh wow, actually this does work if I make the root loop on itself (tuple {src 0, tgt 0}
) and map src
and tgt
attributes to another name x
:
TutorialD (master/main): rec := relation{tuple{src 0, tgt 0},tuple{src 0, tgt 1}, tuple{src 1, tgt 2}, tuple{src 2, tgt 3}}
TutorialD (master/main): foreign key src_tgt (rec rename {src as x}){x} in (rec rename {tgt as x}){x}
That's a clever workaround. I suppose it wouldn't hurt to make a utility wrapper command to make this pattern more convenient. Do you have some syntax in mind?
By the way, the foreign key syntax is itself a convenience wrapper around inclusion dependencies which have been proven to be able to represent any database constraint.
I've also been thinking about how to represent n-ary, nested relationships within a relation outside the scope of adjacency lists since we could leverage nested relations. However, the top-level relation type does not seem to be able to represent arbitrarily-nested relation types, so I don't see off-hand how to represent arbitrary levels of nesting.
Thanks for the feedback π I'll try to think about a syntax for traversing recursive relationships. Datalog might be an inspiration ? I'm very interested in modeling n-ary relations but I don't quite visualise how to do it with a nested relation even for the arity 2 case. Could you give an example?
Sort of broadening the scope quite a bit here, but I was wondering what are principled approaches to model graphs in a relation database. This seems like a very interesting paper on implementing graph algorithms in RDBMSes using new operations and a while syntax.
https://dsl.cds.iisc.ac.in/~course/TIDS/papers/allinone.pdf
To support graph processing, in this work, we propose 4 new relational algebra operations, MM- join, MV-join, anti-join, and union-by-update.
Thanks for linking to that paper- it proposes new SQL syntax to support said traversals. However, it is still limited by the capabilities of SQL (which Project:M36 is not). For example, Project:M36 supports algebraic data types and relation-valued attributes, both of which can be leveraged to represent graph more effectively.
For example, relation-valued attributes would be a great way to query a database to service an ORM. Here's a sample dashboard using the C.J. Date example. This would typically done by an outer join in SQL, but the Project:M36 representation is quite natural:
:showexpr (s join sp){p#,qty,s#,sname} group ({p#,qty} as parts)
βββββββββββββββββββββββββββββββββββββββββ¬βββββββββ¬ββββββββββββ
βparts::relation {p#::Text,qty::Integer}βs#::Textβsname::Textβ
βββββββββββββββββββββββββββββββββββββββββΌβββββββββΌββββββββββββ€
βββββββββββ¬βββββββββββββ β"S4" β"Clark" β
ββp#::Textβqty::Integerβ β β β
βββββββββββΌβββββββββββββ€ β β β
ββ"P4" β300 β β β β
ββ"P5" β400 β β β β
ββ"P2" β200 β β β β
βββββββββββ΄βββββββββββββ β β β
βββββββββββ¬βββββββββββββ β"S1" β"Smith" β
ββp#::Textβqty::Integerβ β β β
βββββββββββΌβββββββββββββ€ β β β
ββ"P4" β200 β β β β
ββ"P5" β100 β β β β
ββ"P6" β100 β β β β
ββ"P3" β400 β β β β
ββ"P1" β300 β β β β
ββ"P2" β200 β β β β
βββββββββββ΄βββββββββββββ β β β
βββββββββββ¬βββββββββββββ β"S3" β"Blake" β
ββp#::Textβqty::Integerβ β β β
βββββββββββΌβββββββββββββ€ β β β
ββ"P2" β200 β β β β
βββββββββββ΄βββββββββββββ β β β
βββββββββββ¬βββββββββββββ β"S2" β"Jones" β
ββp#::Textβqty::Integerβ β β β
βββββββββββΌβββββββββββββ€ β β β
ββ"P2" β400 β β β β
ββ"P1" β300 β β β β
βββββββββββ΄βββββββββββββ β β β
βββββββββββββββββββββββββββββββββββββββββ΄βββββββββ΄ββββββββββββ
We could consider some syntax addition to TutorialD which would generate graph traversals using RVAs, but I'm not yet sure what that would look like.
I encounter this issue too when I want to record tree-like comments.
I found an article talking about tree structure in sql (in Chinese). Tree structure in SQL
Basically, it says there are four ways to do that:
Adjacency List: easiest to implement and understand Path Enumeration: convenient when paths are needed Nested Sets: good for query frequently but modify occasionally. Closure Table: flexible, needs extra space.
There's actually a more natural way to represent graphs in the relational algebra which I have not seen before. It's not quite possible in Project:M36 because we don't support recursive data types, but it should be possible to support in the future.
Here's an example that works today that hints at what could be possible:
TutorialD (master/main): :showexpr relation{tuple{val 4, children relation{tuple{val 6,children relation{tuple{}}}}}, tuple{val 10, children relation{tuple{val 1, children relation{tuple{}}}, tuple{val 2, children relation{tuple{}}}}}}
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββ¬βββββββββββββ
βchildren::relation {children::relation {},val::Integer}βval::Integerβ
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββΌβββββββββββββ€
ββββββββββββββββββββββββ¬βββββββββββββ β4 β
ββchildren::relation {}βval::Integerβ β β
ββββββββββββββββββββββββΌβββββββββββββ€ β β
ββββ β6 β β β
ββββ β β β β
ββββ€ β β β β
ββββ β β β β
ββββββββββββββββββββββββ΄βββββββββββββ β β
ββββββββββββββββββββββββ¬βββββββββββββ β10 β
ββchildren::relation {}βval::Integerβ β β
ββββββββββββββββββββββββΌβββββββββββββ€ β β
ββββ β2 β β β
ββββ β β β β
ββββ€ β β β β
ββββ β β β β
ββββ β1 β β β
ββββ β β β β
ββββ€ β β β β
ββββ β β β β
ββββββββββββββββββββββββ΄βββββββββββββ β β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββ΄βββββββββββββ
If we could define a recursive type for the top-level relation, then we could conceivably enable unlimited levels of nested structures using nested relations. This would allow us, for example, to enable fine-grained constraints on the graph use normal database constraints.
This is certainly more natural than adjanceny lists and less error-prone than number range-based graphs structures.
Hi there π
I'm trying to work with recursive relationships.
It seems to work ok for a depth 1 traversal:
Also if I add an edge:
I can work out a way to do a depth 2 traversal:
Can this be generalised to a depth
n
traversal? Also would it be possible to group the result by depth?Thanks for your time!
Jun