Storing provenance in a relational database

Various SQL storages can be added to SPADE using the controller, allowing provenance records to be sent to a relational database. Note that 3 arguments must be provided when adding a storage: <database path>, <username>, and <password>. When adding the storage using the controller, these arguments are separated by spaces. If either the username or password is unused, the string literal null should be used. If the path is absolute, it should start with a / and will be treated literally. However, if its a relative path, it will be treated relative to the db directory in SPADE root directory.

In addition, each SQL storage has its own config file containing database specific configurations. These configurations are in the form of key-value pairs and get loaded when the storage is initialized. They contain two mandatory entries: databaseDriver and databaseURLPrefix. It is essential that this file is populated for a storage before adding it to SPADE.

H2

H2 is an embedded SQL library. SPADE comes pre-packaged with H2. Following is an example of its config file and the commands to initialize it through the SPADE control client:

config file

databaseDriver = org.h2.Driver
databaseURLPrefix = jdbc:h2:

control commands

-> add storage H2 /tmp/sql/spade.sql_db sa null
Adding storage H2... done

This will create a database in the filesystem at /tmp/sql/spade.sql_db if it does not exist, or use the existing database if it has previously been created. The username for connecting to this database will be sa and no password will be used.

---

The following will cause SPADE to stop sending provenance records to the SQL database:

-> remove storage H2
Shutting down storage H2... done

PostgreSQL

To add a PostgreSQL storage to SPADE, it needs to be installed as a separate application. This page has PostgreSQL available to download for different operating systems. Once downloaded, it needs to run as an independent process for SPADE to communicate with it. The following example presents config file and the commands to initialize PostgreSQL through the SPADE control client:

config file

databaseDriver = org.postgresql.Driver
databaseURLPrefix = jdbc:postgresql:

control commands

-> add storage PostgreSQL spade_pg john_doe 12345
Adding storage PostgreSQL... done

This will create a database in the filesystem at SPADE_ROOT/db/spade_pg if it does not exist, or use the existing database if it has previously been created. The username and password for connecting to this database will be john_doe and 12345.