AlgebraicJulia / AlgebraicJulia/AlgebraicRelations.jl

schema for schemas

Đang mở
#47 2 bình luận 2 reaction 0 người được giao Xem trên GitHub
Ngôn ngữ chính
Julia
Star
56
Fork
4
Chỉ số merge pull request
Không có pull request nào được merge trong 30 ngày

Mô tả

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:

```julia
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](https://github.com/user-attachments/assets/5a8ba116-000b-47ea-9671-6b197e9169fe)

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=<

TableA
🔑id
🔗tab_b

>];

TableB [label=<

TableB
🔑name

>];

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

Hướng dẫn đóng góp

Chưa lập chỉ mục được hướng dẫn đóng góp cho kho mã nguồn này

Đánh giá

Issue này chưa được đánh giá.

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.