Workshop: Working with Relational Databases in the Wolfram Language

Carlo Barbieri, ECE

(RowDefault[, (Spacer1), , (Spacer1), ])  #SocialMediaAreBadForYou

ABSTRACT

The objective of this workshop is getting you started with the main concepts of working with RelationalDatabases through the Entity framework in the Wolfram Language.

https://wolfr.am/dbwork

Outline

Philosophy

Classes of entities

Inspection

Querying

Relations

EmbeddedSQL and ExternalEvaluate

Q&A

Philosophy

EntityValue[class, properties]

First argument: EntityClass (and related heads)

Fully inert and symbolic

Represents all the data

Rows

Second argument: EntityProperty (usually plural)

Projection

Columns

Principles

Resolution only happens when EntityValue is called

EntityList considered dangerous (/s)

1 EntityValue = 1 SQL query

Classes of entities

EntityClass

Legacy, can do basic filtering and sorting

FilteredEntityClass

Full-fledged filtering (WHERE)

ExtendedEntityClass

Annotations, renamings

SampledEntityClass

Roughly follows the Take syntax (LIMIT, OFFSET)

SortedEntityClass

Like SortBy (ORDER BY)

AggregatedEntityClass

2-ary version performs aggregations, 3-argument version groups and aggregates (GROUP BY)

CombinedEntityClass

Similar to JoinAcross, but supports more complex statements (JOIN)

UnionedEntityClass

Most complex set operation, supports SameTestProperties (UNION, UNION ALL, DISTINCT ON)

IntersectedEntityClass

Also supports SameTestProperties (INTERSECT)

ComplementedEntityClass

Also supports SameTestProperties (EXCEPT)

Classes of entities (cont.d)

Like Lego: each class of entities is takes one (or more) classes of entities as arguments

Completely inert and symbolic

You can reason about their EntityProperties without executing

Inspection

schema = RelationalDatabase[FindFile["ExampleData/ecommerce-database.sqlite"]]

Top-level properties

schema["Properties"]

schema["Tables"]

Table-level properties

schema["orders", "Properties"]

schema["orders", "Columns"]

Column-level properties

schema["orders", "orderNumber", "Properties"]

Inspection: EntityStore

store = EntityStore[schema]

Bold properties are primary keys ⇒ CanonicalName

Underlined properties are relations ⇒ Entity- or EntityClass-valued properties

→ means one-to-one (outgoing foreign key)

← means one-to-many (incoming foreign key)

EntityRegister[store]

Querying

EntityValue["offices", "officeCode"]

EntityValue["offices", {"city", "country"},"PropertyAssociation"]

EntityValue[FilteredEntityClass["offices", EntityFunction[x, x["country"]=="USA"]], "city"]

EntityValue["offices", {"state", EntityFunction[o, MissingQ[o["state"]]]}]

Querying (composition)

Count how many customers per city and country we have

aggr=AggregatedEntityClass[
"customers","customerCount"->EntityFunction[c,Length[c["customerNumber"]]],
{"city","country"}
];

EntityValue[aggr,"PropertyAssociation"]

Well, this is unsorted, let’s sort it:

sort = SortedEntityClass[
aggr,
"customerCount"->"Descending"
];

EntityValue[
sort,
"PropertyAssociation"
]

Querying (what happens under the hood?)

Databases`Database`$DBQueryLogger= Echo;

EntityValue["offices", {"state", EntityFunction[o, MissingQ[o["state"]]]}]

Who is making orders on Sundays?

EntityValue[FilteredEntityClass["orders", EntityFunction[o, DateValue[FromUnixTime[o["orderDate"]], "DayName"] ==="Sunday"]], EntityFunction[o, o["customers"]["customerName"]]]

Databases`Database`$DBQueryLogger= .

Relations

What’s the overall structure of the database?

Graph[Join@@(Thread[#->schema[#, "ForeignKeys", "ToTable"]]&/@schema["Tables"]), VertexLabels -> "Name"]

EntityStores[][[1]]

Here is a naked column:

EntityValue["employees", "officeCode"]

Here is the same column expressed as an Entity:

EntityValue["employees", "offices"]

And the reverse relation:

EntityValue["offices", "employees"]

A more complicated example:

EntityFunction[c,c["employees"]["firstName"]<>" "<>c["employees"]["lastName"]<>" is the sales representative for "<>c["customerName"]<>". Their manager is "<>c["employees"]["employees-reportsTo"]["firstName"]<>" "<>c["employees"]["employees-reportsTo"]["lastName"]][Entity["customers",103]]

Databases`Database`$DBQueryLogger=Echo;

The same thing without relations

CombinedEntityClass[CombinedEntityClass["customers","employees","salesRepEmployeeNumber"->"employeeNumber"],"manager"->"employees",{"reportsTo"->"employeeNumber"}][EntityFunction[c,c[EntityProperty["employees","firstName"]]<>" "<>c[EntityProperty["employees","lastName"]]<>" is the sales representative for "<>c["customerName"]<>". Their manager is "<>c[EntityProperty["manager"->"employees","firstName"]]<>" "<>c[EntityProperty["manager"->"employees","lastName"]]]]//First

Aggregation-less aggregations

EntityFunction[o,Total[o["orderdetails"]["priceEach"]*o["orderdetails"]["quantityOrdered"]]][Entity["orders",10100]]

EmbeddedSQL and ExternalEvaluate

Useful for things we don’t or can’t support

EntityValue[Entity["offices", "1"], EntityFunction[x, EmbeddedSQLExpression["date('now','start of year','+6 months','weekday 0')"]]]

Supports binding things from the outer query

EntityValue["offices", {EntityFunction[x, EmbeddedSQLExpression["glob('U*',``)", {x["country"]}]],"country"}]

What if I want to have query-level EmbeddedSQL?

EmbeddedSQLEntityClass["WITH RECURSIVE fact_i (n, fct) AS (
     VALUES (0, 1)
     UNION ALL
     SELECT n+1, fct * (n+1) FROM fact_i
     WHERE n < 10)
SELECT * FROM fact_i", {"n", "fct"}]["fct"]

Really arbitrary stuff:

ExternalEvaluate[DatabaseReference["sqlite://"], {"CREATE TABLE developers (name TEXT NON NULL, awesome INTEGER)", "INSERT INTO developers (name, awesome) VALUES (\"carlo barbieri\", 1), (\"donald knuth\", 0)", "SELECT name FROM developers where awesome"}]

Questions?