Nested Data Example

[Peter Klosowski]

Hi all,

Is there any chance you could share the code (schema and instance) behind the nested data example on the screenshots page?

https://www.categoricaldata.net/pics/nested1.png

https://www.categoricaldata.net/pics/nested2.png

That would be much appreciated, thank you in advance!

Best,

Peter

[Ryan Wisnesky]

Hi Peter,

The nested data screen shots are from one of CQL's predecessors, OPL.  They are built-in in the defunct old version of the IDE here: https://github.com/CategoricalData/FQL as the examples Nested and Nested 2.  In CQL, it is possible to place arbitrary java objects, including CQL instances, inside CQL rows in a similar way.  However, it is not recommended to do so any more than it is recommended to store SQL databases inside of SQL rows -  in both CQL and SQL, hierarchical documents such as JSON or XML are recommended to be shredded into systems of flat foreign keys and simple compound datatypes (lists, finite maps, etc).  I'd be happy to chat further if you have a specific use case in mind.

Ryan

--
You received this message because you are subscribed to the Google Groups "Categorical Data" group.
To unsubscribe from this group and stop receiving emails from it, send an email to categoricalda...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/categoricaldata/16cfaeb7-6bff-4059-85dc-c3fe965dab13%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Peter Klosowski]

Hi Ryan,

Thank you very much for the quick and enlightening response. You deduced correctly what I was intending to use nested data for, namely to model hierarchical data. My real-world schema is quite complex, but as an approximate example we can consider cycling data, following the GPX specification:

https://en.wikipedia.org/wiki/GPS_Exchange_Format

In this format, a "Track" has many "Segments", and a "Segment" has many "Waypoints". Each of these different entities can contain various attributes, e.g. the "name" of the "Track", the "fastest_time" for a "Segment", or the "longitude" and "latitude" for a "Waypoint". Is there already an example that shows how to model such hierarchical data? If yes, can you point me to it, and if no, can you maybe sketch how one might do it for this cycling data example?

Many thanks again for your time!

Best,

Peter

On Wednesday, May 29, 2019 at 4:02:02 AM UTC+10, Ryan Wisnesky wrote:

Hi Peter,

The nested data screen shots are from one of CQL's predecessors, OPL.  They are built-in in the defunct old version of the IDE here: https://github.com/CategoricalData/FQL as the examples Nested and Nested 2.  In CQL, it is possible to place arbitrary java objects, including CQL instances, inside CQL rows in a similar way.  However, it is not recommended to do so any more than it is recommended to store SQL databases inside of SQL rows -  in both CQL and SQL, hierarchical documents such as JSON or XML are recommended to be shredded into systems of flat foreign keys and simple compound datatypes (lists, finite maps, etc).  I'd be happy to chat further if you have a specific use case in mind.

Ryan

On Tue, May 28, 2019 at 3:27 AM Peter Klosowski <klo...@gmail.com> wrote:

Hi all,

Is there any chance you could share the code (schema and instance) behind the nested data example on the screenshots page?

https://www.categoricaldata.net/pics/nested1.png

https://www.categoricaldata.net/pics/nested2.png

That would be much appreciated, thank you in advance!

Best,

Peter

--
You received this message because you are subscribed to the Google Groups "Categorical Data" group.

To unsubscribe from this group and stop receiving emails from it, send an email to categor...@googlegroups.com.

[David Spivak]

How's this?

schema peterschema = literal : sql {

  entities

    track segment waypoint

  foreign_keys

    in: segment->track

    in: waypoint->segment

  attributes

    name: track->String

    fastest_time: segment->Integer

    longitude: waypoint->Integer

    latitude: waypoint->Integer

}

To unsubscribe from this group and stop receiving emails from it, send an email to categoricalda...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/categoricaldata/9877b830-089c-40dc-89d9-7fc59b4e9c97%40googlegroups.com.

[Peter Klosowski]

Hi David,

Thank you very much for the response. Yes, I believe this would be an idiomatic translation of the equivalent normalized relational schema. However, perhaps I have oversimplified things a bit. In a hierarchical JSON document, there would also be attributes "segments" of type "track->List(segment)" and "waypoints" of type "segment->List(waypoint)". Of course, in SQL we could obtain this using relational joins, but one of the key features I like about CQL is the ability to make joins transparent. Is there any way to extend the schema to include such attributes? What exactly did Ryan mean when he referred to "flat foreign keys and simple compound datatypes (lists, finite maps, etc)"?

Best,

Peter

[Ryan Wisnesky]

Hi Peter,

I think your intuition for handling nested data in SQL should extend to CQL.  By 'simple compound' data types, I mean that in your CQL typeside you can write types like 'List', bind 'List' to java's List class, and the use 'nil' and 'cons' to construct lists (I'd be happy to demonstrate).  CQL requires fewer joins than SQL because a foreign key f can be de-referenced by writing '.f' instead of needing a FROM clause. Although CQL equations in schemas can make each row's fields derived from the other fields; however, they cannot make a row field depend on anything besides that row, so your join query cannot be reified as an equation in a schema.  However, you can reify joins using CQL constraints, which are additional restrictions placed on top of a schema.  I'm not 100% sure how it would work in your case, but here's an example:

schema peterschema = literal : sql {
  entities

    track segment waypoint R

  foreign_keys
    in: segment->track
    in: waypoint->segment

    seg : R -> segment
    tr : R -> track

  attributes
    name: track->String
    fastest_time: segment->Integer
    longitude: waypoint->Integer
    latitude: waypoint->Integer
}

constraints C = literal : peterschema {
forall s:segment t:track where s.in = t -> exists r:R where r.seg = s  r.tr = t
}

  

 

To unsubscribe from this group and stop receiving emails from it, send an email to categoricalda...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/categoricaldata/78de0b2a-530f-45cd-81fd-636f24303fb8%40googlegroups.com.