DatabaseLink

Chris Williamson

Wolfram Research

What is DatabaseLink?

An interface for manipulating and querying databases.

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

Works cross-platform

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

Works cross-platform

Supports many different database through JDBC drivers

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

Works cross-platform

Supports many different database through JDBC drivers

Manipulate and query the database using SQL or Mathematica commands

What is DatabaseLink?

An interface for manipulating and querying databases.

Implemented using JDBC.

Java independent

Works cross-platform

Supports many different database through JDBC drivers

Manipulate and query the database using SQL or Mathematica commands

Includes a lightweight database

How does DatabaseLink work?

Establish a connection to the server.

How does DatabaseLink work?

Establish a connection to the server.

Manipulate and/or query data in the database using the connection.

How does DatabaseLink work?

Establish a connection to the server.

Manipulate and/or query data in the database using the connection.

Close connection.

Load the Package

The package needs to be loaded before we can use DatabaseLink.

<< DatabaseLink`

Establish a connection

A connection can be opened to an existing data source bying passing the data source's name into OpenSQLConnection.

conn = OpenSQLConnection["demo"]

Data Sources

Existing data source names can be found using DataSourceNames.

DataSourceNames[]

Establish a connection (con't)

If no parameters are passed to OpenSQLConnection, a GUI will present itself and allow the user to choose an existing data source or configure a new data source that is saved to disk.

conn1  =  OpenSQLConnection[]

Establish a connection (con't)

JDBC may be passed to OpenSQLConnection as a parameter to specify a specific JDBC connection.  JDBC may contain the JDBC driver class name and URL used to connect to the database.  Options such as Username and Password may be passed to OpenSQLConnection as well.

conn2  =  
      OpenSQLConnection[
        JDBC["org.hsqldb.jdbcDriver",  "jdbc:hsqldb:/temp/test2"],  
        Username -> "sa"]

Establish a connection (con't)

To make things a bit easier to use, JDBC information may be stored and reused.  In this example, we can specify hsqldb rather than the JDBC driver class name and we can leave off the protocol from the URL.

\(conn3\  = \ 
      OpenSQLConnection[
        JDBC["hsqldb", \ 
          ToFileName[{$UserBaseDirectory, \ "DatabaseResources", \ "Examples"}, \ "test"]], \ Username -> "sa"]\)

JDBC Drivers

Existing JDBC driver names can be found using JDBCDriverNames.

JDBCDriverNames[]

Establish a connection (con't)

Passwords are stored in plain text for existing data sources.  The password may also be set to "$Prompt", so the user will be prompted for the password when trying to connect to data source.

\(conn4\  = \ 
      OpenSQLConnection[
        JDBC["hsqldb", \ 
          ToFileName[{$UserBaseDirectory, \ "DatabaseResources", \ "Examples"}, \ "test"]], \ Username -> "sa", \ 
        Password -> "$Prompt"]\)

Listing open SQLConnections

SQLConnections is used to list open connections to databases.

SQLConnections[]

Closing a connection

CloseSQLConnection is used to close an open connection to the database.  This prevents resources from being wasted on a connection not being used.

CloseSQLConnection[conn];
CloseSQLConnection  /@  SQLConnections[];

Executing SQL

SQL commands may be executed using SQLExecute.

conn = OpenSQLConnection["demo"]; 
    SQLExecute[conn,  "SELECT * FROM SAMPLETABLE1"]

Querying table information

SQLTableNames may be used to retrieve the names of tables in the database.

SQLTableNames[conn]

Querying table information (con't)

SQLTables may be used to retrieve the tables in the database.  A list of SQLTable expressions are returned.

SQLTables[conn]

Querying table information (con't)

SQLTableInformation may be used to retrieve more detailed information about tables in the database.

SQLTableInformation[conn,  ShowColumnHeadings -> True] // 
      TableForm

Querying column information

SQLColumnNames may be used to retrieve the names of columns in the database.  The result is a paired list of table and column names.

SQLColumnNames[conn,  "SAMPLETABLE1"]

Querying column information (con't)

SQLColumns may be used to retrieve the columns in the database.  A list of SQLColumn expressions are returned.

SQLColumns[conn,  "SAMPLETABLE1"]

Querying column information (con't)

SQLColumnInformation may be used to retrieve more detailed information about columns in the database.

SQLColumnInformation[conn,  "SAMPLETABLE1",  
        ShowColumnHeadings -> True] // TableForm

Querying data

SQLSelect is used retrieve data from a database.  The second parameter is used specify which table to retrieve data from.

SQLSelect[conn,  "SAMPLETABLE1"]

Querying data (con't)

The option ShowColumnHeadings may be used to retrieve the column names along with the data from a table.

SQLSelect[conn,  "SAMPLETABLE1",  
        ShowColumnHeadings -> True] // TableForm

Querying data (con't)

The third parameter of SQLSelect may be used to specify which columns of data to retrieve from a table.

SQLSelect[conn,  "SAMPLETABLE1",  {"NAME",  "VALUE"},  
        ShowColumnHeadings -> True] // TableForm

Querying data (con't)

The fourth parameter of SQLSelect may be used to specify a condition that limits the amount of data retrieved from a table.

SQLSelect[conn,  "SAMPLETABLE1",  {"NAME",  "VALUE"},  
        SQLColumn["VALUE"]  <  6,  ShowColumnHeadings -> True] // 
      TableForm

Querying data type information

SQLDataTypeNames may be used to retrieve the names of data types supported by a database.

conn = OpenSQLConnection["demo"];
SQLDataTypeNames[conn]

Creating a table

SQLCreateTable is used to create a table. The second parameter is used to specify the table name. The third parameter specifies a list SQLColumn expressions defining columns of the table.

\(\(SQLCreateTable[
        conn, \ "TEST", \ {
SQLColumn["X", \ 
            DataTypeName -> "REAL"], \ 
SQLColumn["Y", \ 
            DataTypeName -> "REAL"], \ 
SQLColumn["Z", \ 
            DataTypeName -> "REAL"]
}];\)\)
SQLTableNames[conn]

Inserting data

SQLInsert is used to store data in a table.  The second parameter specifies is the name of the table to insert data into.  The third parameter specifies the column names to insert data into.  The fourth parameter specifies the values of the data to be inserted into the table.

SQLInsert[
        conn,  "TEST",  {"X", "Y", "Z"},  {0, 1, 
          0}];
SQLSelect[conn,  "TEST"]

Inserting data (con't)

A table of data can be passed as the fourth parameter of SQLInsert to store in a table in the database.

SQLInsert[
      conn,  "TEST",  {"X", "Y", "Z"},  {Random[],  
            Random[], Random[]}  &  /@  Range[3]]; 
    ArrayPlot[
      SQLSelect[conn,  "TEST",  {"X", "Y",  "Z"}, 
        MaxRows -> 3]]

Updating data

SQLUpdate is used to update the contents of a table.  The second parameter specifies the table that is being updated.  The third parameter specifies the column names of the data being updated.  The fourth parameter specifies the values of the data being updated.

ArrayPlot[
        SQLSelect[conn,  "TEST",  {"X", "Y",  "Z"}, 
          MaxRows -> 3]];
\(SQLUpdate[
      conn, \ "TEST", \ {"X", "Y", "Z"}, \ {SQLColumn["X"]\  + \  .2, \ SQLColumn["Y"]\ \  + \  .2, \ 
        SQLColumn["Z"]\  + \  .2}]\)

ArrayPlot[
      SQLSelect[conn,  "TEST",  {"X", "Y",  "Z"}, 
        MaxRows -> 3]]

Deleting data

SQLDelete is used to delete the contents of a table.  The second parameter specifies the table of the data being deleted.  A condition may be specified as the third parameter that limits the data deleted.  If a condition is not specified, all data in a table will be deleted.

SQLDelete[conn,  "TEST"]

Dropping a table

SQLDropTable is used to remove a table from the database.  The second parameter specifies the table to drop from the database.

SQLDropTable[conn,  "TEST"];
CloseSQLConnection[conn]

Advanced Features

Prepared Statements

Advanced queries featuring functions, joins, and more.

Binary support

Mathematica Expression support

Batch support

Transactions

Prepared Statements

Allows a user to substitute values into a SQL statement without using a bunch of calls to StringJoin.

conn  =  
        OpenSQLConnection["demo"];
\(SQLExecute[
      conn, \ "SELECT * FROM SAMPLETABLE1 WHERE NAME = `1`", \ {"Day1"}]\)

Prepared Statements (con't)

Prepared statements can used to substitute table and column names as well as values into SQL statements.

\(SQLExecute[
      conn, \ "SELECT `1` FROM SAMPLETABLE1", \ {SQLColumn["VALUE"]}]\)

Prepared Statements (con't)

Multiple values can be substituted into one place of a SQL statement using SQLArugment.

SQLExecute[
      conn,  "SELECT `1` FROM SAMPLETABLE1",  {SQLArgument[
          SQLColumn["NAME"],  SQLColumn["VALUE"]]}]

CloseSQLConnection[conn]

Advanced Queries - Joins

Data is organized in tables.  Using joins it is possible to combine this data into one data set.

conn  =  
        OpenSQLConnection["publisher"];
\(SQLSelect[
        conn, \ 
 (*\ 
          Table\ to\ use\ *) 
{"TITLES", \ "TITLEAUTHORS", \ "AUTHORS"}, \ 

 (*\ 
          Columns\ to\ select\ *) 
{{"TITLES", \ "TITLE"}, \ {"AUTHORS", \ "AU_LNAME"}, \ {"AUTHORS", \ "AU_FNAME"}}, \ 

 (*\ 
          Conditions\ to\ meet\ *) 
SQLColumn[{"TITLES", "TITLE_ID"}]\  == \ 
            SQLColumn[{"TITLEAUTHORS", \ "TITLE_ID"}]\  && \ 
          SQLColumn[{"TITLEAUTHORS", "AU_ID"}]\  == \ 
            SQLColumn[{"AUTHORS", \ "AU_ID"}], \ 

SortingColumns -> {SQLColumn[{"TITLES", \ "TITLE"}] -> "Ascending"}, \ ShowColumnHeadings -> True] // 
      TableForm\)

Advanced Queries - Functions

Using functions it is possible to aggregate results

SQLExecute[conn,  "SELECT COUNT(*) FROM SALESDETAILS"]

CloseSQLConnection[conn]

Binary data

It is possible to store binary data into a table and retrieve it.  Binary data is wrapped in SQLBinary to identify it as binary data with the database.  The data passed into SQLBinary should be a list of bytes.

Needs["DatabaseLink`"];
conn  =  
        OpenSQLConnection[ "demo"];
gif  = 
      SQLBinary[
        ToCharacterCode[
          ExportString[
            Plot[Sin[x],  {x, 0, 2  Pi}],  "GIF"]]]

Binary data (con't)

The datatype BINARY should be used for storing binary data.  Some databases do not have a BINARY datatype, but use BLOB instead.

SQLCreateTable[
        conn,  "BINTABLE",  {SQLColumn["BINCOL",  
            DataTypeName -> "BINARY"]}];
SQLInsert[
        conn,  "BINTABLE",  {"BINCOL"},  {gif}];
data =  SQLSelect[conn,  "BINTABLE"]

Binary data (con't)

FromCharacterCode can be used to convert the binary data into ASCII.  ImportString and Show can be used to display the data in Mathematica.

gifData  = 
        FromCharacterCode[ 
          data[[1, 1, 1]] ];
Show[ImportString[gifData, "GIF"]];
SQLDropTable[conn,  "BINTABLE"];
CloseSQLConnection[conn];

Mathematica Expressions

It is possible to store a Mathematica expression in a table and retrieve it.  Mathematica expressions are wrapped in SQLExpr to identify it as a Mathematica expression with the database.  The datatype VARCHAR (or the equivalent to a string datatype for the database) should be used for storing Mathematica expressions.

Needs["DatabaseLink`"];
conn  =  
        OpenSQLConnection[ "demo"];
SQLCreateTable[
        conn,  "EXPRTABLE",  {SQLColumn["EXPRCOL",  
            DataTypeName -> "VARCHAR"]}];
SQLInsert[
        conn, "EXPRTABLE",  {"EXPRCOL"}, {SQLExpr[
            Sin[x^2]]}];
data  =  SQLSelect[conn,  "EXPRTABLE"]

SQLDropTable[conn,  "EXPRTABLE"];
CloseSQLConnection[conn];

Batch support

Map can be used to execute several database commands with one Mathematica command.  However, there is overhead associated with calling database commands.  It is more efficient to call a database command once and insert all the data in one call.

Needs["DatabaseLink`"];
conn  =  
        OpenSQLConnection[ "demo"];
table  =  SQLTable["BATCH"];
\(\(cols\  = \ 
{
SQLColumn["X", \ DataTypeName -> "Integer"], 
SQLColumn["Y", \ 
            DataTypeName -> "Integer"]
};\)\)
SQLCreateTable[conn,  table,  cols];
data1  =  {#,  #^2}  &  /@  
          Range[1000];
\(AbsoluteTiming[\(\(SQLExecute[
              conn, \ "INSERT INTO BATCH (X, Y) VALUES (`1`,`2`)", \ #]\  &\) /@ \ data1\ ;\)]\)

Batch support (con't)

Batch support can be used to send a batch of database calls to the database in one Mathematica call.  This allows data to be inserted at a much faster rate.

AbsoluteTiming[SQLExecute[
          conn,  "INSERT INTO BATCH (X, Y) VALUES (`1`,`2`)",  
          data1];]

Transactions

Transactions are important for ensuring an entire batch of commands are executed correctly.  If there is a failure in one of these commands, transactions can be undone or rolled back.  SQLBeginTransaction can be used to start a transaction.

SQLDelete[conn,  "BATCH"];
SQLBeginTransaction[conn];
SQLSelect[conn,  "BATCH"]

Transactions (con't)

Once a transaction has begun, data may be inserted or updated within the database.

SQLInsert[
        conn,  "BATCH",  {"X",  "Y"},  {61, 80}];
SQLInsert[
        conn,  "BATCH",  {"X",  "Y"},  {72, 
          5}];
SQLSelect[conn,  "BATCH"]

Transactions (con't)

At any time the transaction may be rolled back to the point where the transaction started by using SQLRollbackTransaction.

SQLRollbackTransaction[conn];
SQLSelect[conn,  "BATCH"]

Transactions (con't)

Data may be committed permantently to the database using SQLCommitTransaction.

SQLBeginTransaction[conn];
SQLInsert[
        conn,  "BATCH",  {"X",  "Y"},  {61, 80}];
SQLInsert[
        conn,  "BATCH",  {"X",  "Y"},  {72, 
          5}];
SQLCommitTransaction[conn];
SQLSelect[conn,  "BATCH"]

Graph database example

The graph database example demonstrates querying graph properties stored in a database.  This database allows users to search many graphs quickly and find graphs consisting of specific properties.

Needs["DatabaseLink`Examples`Graphs`"];
GraphProperties[]

GraphQuery[{"COMPLETEQ" -> True}]

GraphQuery[{"REGULARQ" -> True}, "MaxHits" -> 
        3]

GraphQuery[{"HAMILTONIANQ" -> 
          True},  {"diameter",  "girth",  "edges"}]

Command Cache Example

The command cache example demonstrates how easy and useful it is to use Mathematica Expressions with a database.  A Mathematica function can be used to simplify storing data.

Needs["DatabaseLink`Examples`CommandCache`"];
StoreCommand[MakeBoxes[2 + 2]];
StoreCommand[MakeBoxes[2^2]];

Command Cache Example (con't)

A Mathematica function can also be used to customize and simplify retrieving data.

CommandCache[]

CommandCache[{"ID", "FULLFORM"}]

\(\(\(CommandCache["Power", \ {"ID", "FULLFORM"}]\)\(\ \)\)\)

CommandCache[1]

Command Cache Example (con't)

A GUI can also be used to browse the commands.

CommandBrowser[]

Do[StoreCommand[
        ToBoxes[Expand[(α + (β/((🐺 + μ))))^
              i]]] ,  {i, 0, 10}]

ClearCommandCache[]

Conclusion

Questions.

My email is chrisw@wolfram.com.