What’s New and Coming in Relational Databases

Carlo Barbieri, Wolfram Research

Outline

What’s old

Entity relational model

New (old) *EntityClass heads

EntityFunction

What’s new

Set operations on entities

EmbeddedSQLExpression

What’s coming

EmbeddedSQLQuery

Writing to databases

Integration with forms

Entity relational model

EntityRegister[EntityStore[RelationalDatabase[FindFile["ExampleData/ecommerce-database.sqlite"]]]]

One main resolver: EntityValue

EntityValue[
FilteredEntityClass[
"employees" ,
EntityFunction[x, StringStartsQ[x["firstName"],"L"]]
],
{"firstName", "lastName"}
]

For the relational experts: projection is always delayed until the very last moment, that’s the only thing EntityValue does.

Classes of entities as a symbolic representation of queries

Disclaimer: The following examples are meant to show the symbolic representation (I do not guarantee they will yield something sensible)

EntityClass

Forefather of all the other representations, named and implicit:

EntityClass["Country", "GroupOf8"]

EntityClass["Country", "Population" -> GreaterThan[10^9]]

ExtendedEntityClass

Adds one or more properties:

ExtendedEntityClass[
"Country",
"ArrivalsDivorceRatio" -> EntityFunction[c, c["AirArrivals"]/c["AnnualDivorces"]]
]

FilteredEntityClass

Filter by an arbitrary predicate:

FilteredEntityClass[
"Country",
EntityFunction[c, c["FemalePopulation"] < c["MalePopulation"] || c["CapitalCity"] ==(Entity["Rome", Entity["City",{"Rome","Lazio","Italy"}], "Entity[\"City\", {\"Rome\", \"Lazio\", \"Italy\"}]", "city"])]
]

SortedEntityClass

Sorts by a property and limits the output:

SortedEntityClass[
"Country",
"Population" -> "Descending",
{2,5}
]

SampledEntityClass

Limits the output:

SampledEntityClass[
"Country",
3
]

AggregatedEntityClass

Groups entities and aggregates properties:

AggregatedEntityClass[
"Country",
"Population" -> Mean,
"Continent"
]

CombinedEntityClass

Combine two entity types into a single one

CombinedEntityClass[
"Country",
"City",
"CapitalCity" -> "Entity"
]

EntityFunction

A property that is computed on the fly:

(Entity["Italy", Entity["Country","Italy"], "Entity[\"Country\", \"Italy\"]", "country"])[
EntityFunction[
c,
c["VehiclesInUsePassengerCars"]/c["VehiclesInUseBuses"]
]
]

Can be used anywhere a property can be used:

SortedEntityClass[
"Country",
EntityFunction[
c,
c["VehiclesInUsePassengerCars"]/c["VehiclesInUseBuses"]
] ,
1
]//EntityList

Set operations on entities

Three main set operations

UnionedEntityClass
IntersectedEntityClass
ComplementedEntityClass

Corresponding to

Union
Intersection
Complement

Equality is defined in the sense of (simplified) EntityList:

EntityList[IntersectedEntityClass[(EntityClass["Group of 8", EntityClass["Country","GroupOf8"], "EntityClass[\"Country\", \"GroupOf8\"]", "countries"]), (EntityClass["European Union", EntityClass["Country","EuropeanUnion"], "EntityClass[\"Country\", \"EuropeanUnion\"]", "countries"])]]===Intersection[EntityList[(EntityClass["Group of 8", EntityClass["Country","GroupOf8"], "EntityClass[\"Country\", \"GroupOf8\"]", "countries"])],EntityList[(EntityClass["European Union", EntityClass["Country","EuropeanUnion"], "EntityClass[\"Country\", \"EuropeanUnion\"]", "countries"])]]

Parametrizing equality

Just like set operations take a SameTest option, we have SameTestProperties, which allows us to do very powerful things.

UnionedEntityClass[
"Movie",
"Book",
SameTestProperties -> "Name"
]

UnionedEntityClass[EntityClass["Movie","Name"->"The Godfather"],EntityClass["Book","Name"->"The Godfather"], SameTestProperties-> "Name"][{"FirstPublished", "ReleaseDate"}]

Parametrizing equality (continued in SQL)

SameTestProperties can be set to False, which is equivalent to setting SameTest to Function[False] to get multiple copies (UNION ALL)

UnionedEntityClass[
"offices",
"offices",
SameTestProperties-> False
]["officeCode"]

SameTestProperties and a single argument UnionedEntityClass can be used to effectively perform DeleteDuplicatesBy (DISTINCT ON):

UnionedEntityClass[
"customers",
SameTestProperties->{"country"}
][{"customerName", "country"}]

EntityProperties of set operations

Properties that have the same string name get combined, but the old properties are still available:

EntityValue[
UnionedEntityClass["offices","employees"],{
EntityProperty["offices","officeCode"],EntityProperty["employees","officeCode"],
EntityProperty[
UnionedEntityClass["offices","employees"],
"officeCode"
]}
]//TableForm

EmbeddedSQLExpression

A wrapper for verbatim SQL in an EntityFunction

Let’s play with SQLite date functionality:

Entity["offices", "1"][EntityFunction[x, EmbeddedSQLExpression["date('now', '+10000000 seconds', 'start of month', 'weekday 0')"]]]

TemplateObject-like bindings are supported:

FilteredEntityClass[
"employees",
EntityFunction[x, EmbeddedSQLExpression[
"`` LIKE 'Ge%'",
{x["firstName"]}
]]
]["firstName"]

Arbitrarily complex expressions can be bound to the template slots:

EntityValue[
"employees",{
EntityFunction[x,EmbeddedSQLExpression["row_number() OVER (partition by `x` order by `y`)",
<|"x"->StringTake[x["lastName"],1],"y"->x["firstName"]|>
]],
"firstName",
"lastName"
}]

Bonus example (PostGIS)

Let’s get a random street that has Barbieri in its name and find out its length in km.

SampledEntityClass[
FilteredEntityClass["planet_osm_line", EntityFunction[w, StringContainsQ[w["name"], "Barbieri"]]],
1
][{
 "name",
EntityFunction[x, EmbeddedSQLExpression["ST_Length(Geography(ST_Transform(``,4326)))/1000",{x["way"]}]],EntityFunction[x, EmbeddedSQLExpression["ST_AsGeoJSON(ST_Transform(``,4326))",{x["way"]}]]
}]

ImportString[%[[1,3]], "GeoJSON"]

EmbeddedSQLQuery

EntityClass-level verbatim SQL:

EmbeddedSQLQuery[
"SELECT `x`, 1",
{"prop1", "prop2"},
<|"x" -> 0|>
]

Arguments:

String template

Property names (we can’t figure it out from a string)

Template bindings for slots

We can use this just like any class of entities, and we can pass an EntityClass as one of the bound slots.

This allows you to mix and match textual and generated SQL at will.

Future work

Everything you’ve seen up to here should make it in V12.1

We are currently designing the tools to write to databases

We are mapping database types to interpreter types

FormObject[Entity[type]] to get a data-entry form

SQLite databases will be writable on the public cloud