Convert SQL schemas to presentations

[epatters]

The Presentations module can convert a presentation of a Catlab schema into a SQL schema. Being able to go in the other direction would also be helpful. The main use case I have is loading data from a SQL database into an attributed C-set.

[kskyten]

Here is a package for Postgres introspection: PostgresCatalog.jl. Is it also possible to automatically construct a query for loading data into Julia objects using Strapping.jl?

[epatters]

Thanks for the pointer to PostgresCatalog. That seems very useful for the purpose of this issue.

What role do you have in mind for Strapping here? I haven't used the package before.

[kskyten]

Mainly I'm interested in instantiating Julia objects from databases (sort of like an ORM). I'm wondering if the C-set presentation would help automatically generate code like in this example. Strapping unrolls nested objects into 2d arrays, which can be inserted into a database. Conversely, you can also construct the objects back from the 2d representation. Is it possible to use a Catlab schema and data to construct objects directly?

[epatters]

You could certainly use an attributed C-set as a data structure for this kind of relational data.

using Catlab, Catlab.CategoricalAlgebra

@present MusicSchema(FreeSchema) begin
  (Text, Year)::Data

  Album::Ob
  (album_name, artist)::Attr(Album, Text)
  year::Attr(Album, Year)
   
  Song::Ob
  album::Hom(Song, Album)
  song_name::Attr(Song, Text)
end

const Music = ACSetType(MusicSchema, index=[:album])

music = Music{String,Int}()

IMO, this could replace the structs in Models.jl. You can manipulate a Music object as a regular Julia object and also make SQL-style queries against it. You won't have nested objects (a struct Album containing a list of Song struct) but you can easily get the list of songs in an album by calling incident(music, album_id, :album).

We should implement more automated interop with SQL (hence this issue), but if you wanted to roll something yourself, you can introspect the presentation for info about the schema. E.g, the list of tables is:

julia> generators(MusicSchema, :Ob)
2-element Vector{Catlab.Theories.FreeSchema.Ob{:generator}}:
 Album
 Song

The list of "foreign keys" for the Song table is:

julia> filter(f -> dom(f) == MusicSchema[:Song], generators(MusicSchema, :Hom))
1-element Vector{Catlab.Theories.FreeSchema.Hom{:generator}}:
 album