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

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

    In[]:=
    schema=RelationalDatabase[FindFile["ExampleData/ecommerce-database.sqlite"]]
    Out[]=
    RelationalDatabase
    Table count: 8
    Backend: SQLite
    Tables:
    productlines
    payments
    offices
    products
    orderdetails
    employees
    orders
    customers
    

    Top-level properties

    In[]:=
    schema["Properties"]
    Out[]=
    {Tables,Connection}
    In[]:=
    schema["Tables"]
    Out[]=
    {productlines,payments,offices,products,orderdetails,employees,orders,customers}

    Table-level properties

    In[]:=
    schema["orders","Properties"]
    Out[]=
    {Columns,ForeignKeys,Name,PrimaryKey,UniquenessConstraints,Indexes}
    In[]:=
    schema["orders","Columns"]
    Out[]=
    {orderNumber,orderDate,requiredDate,shippedDate,status,comments,customerNumber}

    Column-level properties

    In[]:=
    schema["orders","orderNumber","Properties"]
    Out[]=
    {NativeTypeString,Default,Indexed,Name,Nullable}
    In[]:=
    schema["orders","orderNumber","NativeTypeString"]
    Out[]=
    INTEGER

    Inspection: EntityStore

    In[]:=
    store=EntityStore[schema]
    Out[]=
    ◼
  • 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)
    • In[]:=
    EntityRegister[store]
    Out[]=
    {productlines,payments,offices,products,orderdetails,employees,orders,customers}

    Querying

    In[]:=
    EntityValue["offices","officeCode"]
    Out[]=
    {1,2,3,4,5,6,7}
    In[]:=
    EntityValue["offices",{"city","country"},"PropertyAssociation"]
    Out[]=
    {citySan Francisco,countryUSA,cityBoston,countryUSA,cityNYC,countryUSA,cityParis,countryFrance,cityTokyo,countryJapan,citySydney,countryAustralia,cityLondon,countryUK}
    In[]:=
    EntityValue[FilteredEntityClass["offices",EntityFunction[x,x["country"]"USA"]],"city"]
    Out[]=
    {San Francisco,Boston,NYC}
    In[]:=
    EntityValue["offices",{"state",EntityFunction[o,MissingQ[o["state"]]]}]
    Out[]=
    {{CA,False},{MA,False},{NY,False},{Missing[NotAvailable],True},{Chiyoda-Ku,False},{Missing[NotAvailable],True},{Missing[NotAvailable],True}}

    Querying (composition)

    Count how many customers per city and country we have
    In[]:=
    aggr=AggregatedEntityClass[​​"customers","customerCount"EntityFunction[c,Length[c["customerNumber"]]],​​{"city","country"}​​];
    In[]:=
    EntityValue[aggr,"PropertyAssociation"]
    Well, this is unsorted, let’s sort it:

    Querying (what happens under the hood?)

    Who is making orders on Sundays?

    Relations

    What’s the overall structure of the database?
    Here is a naked column:
    Here is the same column expressed as an Entity:
    And the reverse relation:
    A more complicated example:
    The same thing without relations
    Aggregation-less aggregations

    EmbeddedSQL and ExternalEvaluate

    Useful for things we don’t or can’t support
    Supports binding things from the outer query
    What if I want to have query-level EmbeddedSQL?
    Really arbitrary stuff:

    CITE THIS NOTEBOOK

    Wolfram R&D LIVE: Relational Databases in the Wolfram Language​
    by Carlo Barbieri​
    Wolfram Community, STAFF PICKS, April 25 2023
    ​https://community.wolfram.com/groups/-/m/t/2907390