schema for schemas

[slwu89]

Following the chat today with @TheCedarPrince and @quffaro, we may like to update the schema for schemas to handle compound PKs (and FKs). I have been thinking about the following one:

using Catlab

"""
A schema to describe SQL schemas.
Tables are what you would expect. Cols are the columns of a table, so they always know who they belong to col_of.
PK is the primary key of a table pk_of. It needs a junction table PK_Cols because composite primary keys need
to reference multiple columns of a table. FK are foreign keys, they take you from one table to a PK (of another table).
Because the referenced primary key may be composite, the foreign key may also need to be composite, so there is a junction
table FK_Cols to relate a foreign key to its columns.

There are 2 commuting squares. The first is defined by pk_col; col_of == pk; pk_of, saying that a primary key's columns
must belong to the table that it is a primary key for. The second is analogous for foreign keys and is defined by fk; from == fk_col; col_of.
We would also like to say that from != to; pk_of so that foreign keys have to go between different tables but I do not know
how to enforce that.
"""
@present SchSqlTables(FreeSchema) begin
    (Tables,Cols,PK,PK_Cols,FK,FK_Cols)::Ob
    col_of::Hom(Cols,Tables)
    pk_col::Hom(PK_Cols,Cols)
    pk::Hom(PK_Cols,PK)
    pk_of::Hom(PK,Tables)
    to::Hom(FK,PK)
    from::Hom(FK,Tables)
    fk::Hom(FK_Cols,FK)
    fk_col::Hom(FK_Cols,Cols)
    StrType::AttrType
    tab_name::Attr(Tables,StrType)
    col_name::Attr(Cols,StrType)
    col_type::Attr(Cols,StrType)
    compose(pk, pk_of) == compose(pk_col, col_of)
    compose(fk_col, col_of) == compose(fk, from)
    # compose(to, pk_of) != from
end

to_graphviz(SchSqlTables, graph_attrs=Dict(:size=>"5", :ratio=>"expand"))

Which would feed into a nice visualization, which could at first be made just by directly building up the Graphviz syntax:

strict digraph {
rankdir="LR"
node [shape="plain"]

TableA [label=<
    <TABLE BORDER="0" CELLBORDER="1" CELLSPACING="0">
        <TR><TD COLSPAN="2">TableA</TD></TR>
        <TR><TD>🔑</TD><TD>id</TD></TR>
        <TR><TD>🔗</TD><TD PORT="tab_b">tab_b</TD></TR>
    </TABLE>
>];

TableB [label=<
    <TABLE BORDER="0" CELLBORDER="1" CELLSPACING="0">
        <TR><TD COLSPAN="2">TableB</TD></TR>
        <TR><TD PORT="name">🔑</TD><TD>name</TD></TR>
    </TABLE>
>];

TableA:tab_b:e -> TableB:name:w
}

See visualization at https://magjac.com/graphviz-visual-editor/?dot=strict%20digraph%20%7B%0Arankdir%3D%22LR%22%0Anode%20%5Bshape%3D%22plain%22%5D%0A%0ATableA%20%5Blabel%3D%3C%0A%20%20%20%20%3CTABLE%20BORDER%3D%220%22%20CELLBORDER%3D%221%22%20CELLSPACING%3D%220%22%3E%0A%20%20%20%20%20%20%20%20%3CTR%3E%3CTD%20COLSPAN%3D%222%22%3ETableA%3C%2FTD%3E%3C%2FTR%3E%0A%20%20%20%20%20%20%20%20%3CTR%3E%3CTD%3E%F0%9F%94%91%3C%2FTD%3E%3CTD%3Eid%3C%2FTD%3E%3C%2FTR%3E%0A%20%20%20%20%20%20%20%20%3CTR%3E%3CTD%3E%F0%9F%94%97%3C%2FTD%3E%3CTD%20PORT%3D%22tab_b%22%3Etab_b%3C%2FTD%3E%3C%2FTR%3E%0A%20%20%20%20%3C%2FTABLE%3E%0A%3E%5D%3B%0A%0ATableB%20%5Blabel%3D%3C%0A%20%20%20%20%3CTABLE%20BORDER%3D%220%22%20CELLBORDER%3D%221%22%20CELLSPACING%3D%220%22%3E%0A%20%20%20%20%20%20%20%20%3CTR%3E%3CTD%20COLSPAN%3D%222%22%3ETableB%3C%2FTD%3E%3C%2FTR%3E%0A%20%20%20%20%20%20%20%20%3CTR%3E%3CTD%20PORT%3D%22name%22%3E%F0%9F%94%91%3C%2FTD%3E%3CTD%3Ename%3C%2FTD%3E%3C%2FTR%3E%0A%20%20%20%20%3C%2FTABLE%3E%0A%3E%5D%3B%0A%0ATableA%3Atab_b%3Ae%20-%3E%20TableB%3Aname%3Aw%0A%7D

[TheCedarPrince]

(joking) The everlasting journey to turn Catlab.jl into the best graph visualization library continues!

In all honesty, this is really cool and thanks for putting this together @slwu89 !

[slwu89]

This will also resolve SQLACSets