-
Notifications
You must be signed in to change notification settings - Fork 3
Open
Description
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
}
TheCedarPrince and quffaro
Metadata
Metadata
Assignees
Labels
No labels