Integrating SQL in the
Wolfram Language

Riccardo Di Virgilio and Carlo Barbieri

Join the Conversation #WolframTechConf

The missing speaker (he’s not dead, he just didn’t get a visa)

Leonid Shifrin

Interacting with an SQL database

V12 will allow users to connect to an SQL database using EntityStore.

Sample workflow: Inspection and connection

Mapping databases to entities: EntityStore

Sample queries

Relations

Metaprogramming

Generated SQL

Future directions

DatabaseConnection

Connections can be specified using DatabaseConnection

sqlite = DatabaseConnection[File["/Users/rdv/Desktop/economy.sqlite"]]

We support several popular backends: SQLite, MySQL, Postgresql, Microsoft SQL Server, Oracle + all cloud versions.

mysql = DatabaseConnection[URL["mysql://root@localhost/wolfram_test"]]

postgres = DatabaseConnection[<|
"Backend"->"postgres",
"Name"->"wolfram_test",
"Port"->None,
"Host"->"localhost",
"Username"->"rdv",
"Password"->""
|>]

postgres["Connect"];

postgres["Disconnect"];

DatabaseInspect

DatabaseInspect is used to read the schema from a database

db = DatabaseInspect[postgres]

This operation is backend independent, can be done on local or remote databases

DatabaseInspect[mysql]

Mapping databases to entities

After database inspection, the schema can be programmatically inspected.

db = DatabaseInspect[postgres]

db["Tables"]

db["transaction", "Columns"]

db["transaction", "ForeignKeys"]

es = EntityStore[db]

Each table is an Entity type

Each column is an EntityProperty

Foreign keys create Entity-valued properties (relations)

Construct an EntityStore with a subset of the tables:

EntityStore[{"person"},db]

Rename types or properties:

EntityStore[{"Persona" -> "person", "transaction" -> {"transaction_id" -> "id"}}, db]

Computed properties:

EntityStore[{"person" -> {"id", "AgeSquared" -> EntityFunction[x,x["age"]^2]}}, db]

Computed types:

EntityStore[{"male" -> EntityClass["person", "sex" -> "M"],"female" -> EntityClass["person", "sex" -> "F"]},db]

Example queries

In order to start reading data from a database we need to register the entity store

EntityRegister[EntityStore[db]]

EntityValue["transaction", "Properties"]

EntityValue["person", {"fullname", "age"}]

Rich Wolfram Language types are supported (when they exist on the database)

Entity["transaction", 1]["date"]

Relations

When the database contains foreign keys, both the original value and an Entity-valued property are created:

EntityValue["person", "nation_id"]

EntityValue["person", "nation"]

For each foreign key a reverse relation is automatically created:

ec=EntityValue[Entity["nation", "it"], "person"]

EntityList[ec]

The advantage of returning EntityClass instead of a list of Entity is that they can be chained:

ec["age"]

It is also possible to perform inlined aggregations on relations:d

EntityValue["person", EntityFunction[p, Mean[p["transaction-from"]["person-to"]["age"]]/p["age"]]]

Generating symbolic queries

ef=EntityFunction@@{x,Which @@(Join @@MapIndexed[{#1[x["date"]],First[#2]}&,Between/@Partition[Table[DateObject[{2018,1,1}]+ Quantity[i*6, "Months"],{i,0,6}],2,1]])}

ext=ExtendedEntityClass["transaction", "semester" -> ef];

ext[{"date", "semester"}]

aggr=AggregatedEntityClass[ext, "count" -> EntityFunction[y, Length[y["id"]]], "semester"];

aggr[{"semester", "count"}]

sort=SortedEntityClass[aggr, "semester"];

sort[{"semester", "count"}]

sort["count"]//BarChart

Generated SQL

⚠ This is not the final syntax

Block[{$QueryLogger = EchoFunction[SQLFormat]},
	EntityValue["transaction", "id"]
]

Block[{$QueryLogger = EchoFunction[SQLFormat]},
	EntityValue["person", EntityFunction[x,x["id"]/5
]]]

EntityRegister[EntityStore[postgres]]

Block[{$QueryLogger = EchoFunction[SQLFormat]},
	FilteredEntityClass["person", EntityFunction[x, StringContainsQ[x["fullname"], "John", IgnoreCase->True]]]["fullname"]]

Block[{$QueryLogger = EchoFunction[SQLFormat]},
	FilteredEntityClass["person", EntityFunction[x, StringContainsQ[x["fullname"], "John", IgnoreCase->True]]]["fullname"]]

Block[{$QueryLogger = EchoFunction[SQLFormat]},
	EntityValue["person", EntityFunction[p, Mean[p["transaction-from"]["person-to"]["age"]]/p["age"]]]
]

Future directions

Upcoming features:

Writing to a database

Update statement

Insert statement

Transactions

Custom data types

Support for DB specific data types

Postgres Array / HStore /JSON fields

MySQL JSON / Array fields

Database Schema creation

<|
    "nation" -> <|
        "PrimaryKey" -> <|"Columns" -> {"id"}|>,
        "Columns" -> <|
            "id"             -> <|"BackendType" -> {"String", 2}|>,
            "currency"       -> <|"BackendType" -> {"String", 3}|>,
            "name"           -> <|"BackendType" -> {"String", 20}|>
        |>
    |>,
    "person" -> <|
        "PrimaryKey" -> <|"Columns" -> {"id"}|>,
        "ForeignKeys" -> {
            <|
                "ToTable" -> "person",
                "FromColumns" -> {"friend"},
                "ToColumns" -> {"id"}
            |>,
            <|
                "ToTable" -> "person",
                "FromColumns" -> {"enemy"},
                "ToColumns" -> {"id"}
            |>,
            <|
                "ToTable" -> "nation",
                "FromColumns" -> {"nation"},
                "ToColumns" -> {"id"}
            |>
        },
        "Columns" -> <|
            "id"            -> <|"BackendType" -> "Integer"|>,
            "friend"        -> <|"BackendType" -> "Integer"|>,
            "enemy"         -> <|"BackendType" -> "Integer"|>,
            "fullname"      -> <|"BackendType" -> {"String", 40}|>,
            "address"       -> <|"BackendType" -> {"String", 80}|>,
            "password"      -> <|"BackendType" -> {"ByteArray", 40}|>,
            "age"           -> <|"BackendType" -> "Integer"|>,
            "height"        -> <|"BackendType" -> "Real"|>,
            "weight"        -> <|"BackendType" -> "Real"|>,
            "sex"           -> <|"BackendType" -> {"String", 1}|>,
            "savings"       -> <|"BackendType" -> {"Decimal", 10, 2}|>,
            "licenseplate"  -> <|"BackendType" -> {"String", 15}|>,
            "unemployed"    -> <|"BackendType" -> "Boolean"|>,
            "date"          -> <|"BackendType" -> "Date"|>,
            "datetime"      -> <|"BackendType" -> "DateTime"|>,
            "time"          -> <|"BackendType" -> "Time"|>,
            "unixtm"        -> <|"BackendType" -> "Real"|>,
            "juliantm"      -> <|"BackendType" -> "Real"|>,  
            "nation"        -> <|"BackendType" -> {"String", 2}|>
        |>
    |>,
    "transaction" -> <|
        "PrimaryKey" -> <|"Columns" -> {"id"}|>,
        "ForeignKeys" -> {
            <|
                "ToTable" -> "person", 
                "FromColumns"  -> {"from"}, 
                "ToColumns"    -> {"id"}
            |>,
            <|
                "ToTable" -> "person", 
                "FromColumns"  -> {"to"}, 
                "ToColumns"    -> {"id"}
            |>,
            <|
                "ToTable" -> "nation",
                "FromColumns" -> {"nation"},
                "ToColumns" -> {"id"}
            |>
        }, 
        "Columns" -> <|
            "id"            -> <|"BackendType" -> "Integer"|>,
            "from"          -> <|"BackendType" -> "Integer"|>,
            "to"            -> <|"BackendType" -> "Integer"|>,
            "amount"        -> <|"BackendType" -> {"Decimal", 10, 2}|>,
            "nation"        -> <|"BackendType" -> {"String", 2}|>,
            "date"          -> <|"BackendType" -> "Date"|>,
            "datetime"      -> <|"BackendType" -> "DateTime"|>,
            "time"          -> <|"BackendType" -> "Time"|>,
            "reason"        -> <|"BackendType" -> {"String", 100}|>
        |>
    |>
|>

Database Schema migrations