Skip to content

schema for schemas #47

@slwu89

Description

@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"))

Image

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

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions