3 ms·

The schema aspects of this idea are hard for me to grasp. How would the database engine efficiently resolve any references to the embedded relation ? I'm also

The schema aspects of this idea are hard for me to grasp. How would the database engine efficiently resolve any references to the embedded relation ? I'm also having trouble understanding how your INSERT statement would even work - is the firstQuery being inserted being treated as hierarchical data where every single relation attached to every single row has its own schema/structure ? Or is the structure fixed in the CREATE TABLE statement so that you can't use just any relation, but rather have to use a relation that conforms to the structure defined in the CREATE TABLE statement ?

naasking 8y ago
I played a little fast and loose to convey the basic idea. To elaborate since you asked, introduce typed tuples/records as first-class entities, then "tables" are just a sequence of records declared with a particular lifetime. Relations are functions over these sequences. The embedded relation is then a stored function over a set of nested sequences. So to add the concrete types: var firstQuery = select Id, Name, Payment from Foo where ... var compositeQuery = select Id, Name, Total from firstQuery where ... create table StoredQuery(id int not null primary key, query relation{Id int, Name Text, Payment decimal}) insert into StoredQuery values (0, firstQuery) Again eliding a few details, but hopefully you get the basic idea.
- Izkata 8y ago
  This particular use case sounds like a MATERIALIZED VIEW, and views in general for doing what you're asking for...
  - naasking 8y ago
    A materialized view would be an optimization technique used when compiling a schema containing first-class relations (essentially, memoization). First-class relations are more general though. But first-class relations would also present some optimization challenges, so a subset of a relational system with a restricted form of first-class relations corresponding to materialized views that can be stored in table columns and used in queries would get pretty close.
- tomnipotent 8y ago
  What you're asking for is an independently evolving logical schema on top of the physical schema - this is a great idea, but performance guarantees are highly tied to the physical layout of the data. Apache Spark is the best example I've seen of this in that you can compose schemas on the fly and it will unroll them at the end based on physical layout.
- TimJYoung 8y ago
  I think what you're describing is already present as of SQL 2003 in the form of multisets: http://farrago.sourceforge.net/design/CollectionTypes.html http://farrago.sourceforge.net/design/CollectionTypes.html However, good luck finding a database engine that necessarily supports such features. Some support array types, but I haven't seen too many that support multisets.

naasking 8y ago

I played a little fast and loose to convey the basic idea. To elaborate since you asked, introduce typed tuples/records as first-class entities, then "tables" are just a sequence of records declared with a particular lifetime. Relations are functions over these sequences. The embedded relation is then a stored function over a set of nested sequences. So to add the concrete types: var firstQuery = select Id, Name, Payment from Foo where ... var compositeQuery = select Id, Name, Total from firstQuery where ... create table StoredQuery(id int not null primary key, query relation{Id int, Name Text, Payment decimal}) insert into StoredQuery values (0, firstQuery) Again eliding a few details, but hopefully you get the basic idea.

Izkata 8y ago
This particular use case sounds like a MATERIALIZED VIEW, and views in general for doing what you're asking for...
- naasking 8y ago
  A materialized view would be an optimization technique used when compiling a schema containing first-class relations (essentially, memoization). First-class relations are more general though. But first-class relations would also present some optimization challenges, so a subset of a relational system with a restricted form of first-class relations corresponding to materialized views that can be stored in table columns and used in queries would get pretty close.
tomnipotent 8y ago
What you're asking for is an independently evolving logical schema on top of the physical schema - this is a great idea, but performance guarantees are highly tied to the physical layout of the data. Apache Spark is the best example I've seen of this in that you can compose schemas on the fly and it will unroll them at the end based on physical layout.
TimJYoung 8y ago
I think what you're describing is already present as of SQL 2003 in the form of multisets: http://farrago.sourceforge.net/design/CollectionTypes.html http://farrago.sourceforge.net/design/CollectionTypes.html However, good luck finding a database engine that necessarily supports such features. Some support array types, but I haven't seen too many that support multisets.