Introduction

In this tutorial will demonstrate how to integrate bSQL into an example Daml application. Once set up, data will be sent from the Daml quick-start application to a bSQL instance in a cloud environment. By querying data from our bSQL instance we can observe contract flow and execution.

Background

The Daml environment ensures immutability, but once data exits this environment to, let's say, an analytics database, how do we extend these guarantees?
bSQL stores data immutably. This means that existing data cannot be deleted or updated, instead only new versions are added to the system. This logic aligns with the Daml ledger model. When an action is performed on a contract, the old contract persists, and a new contract takes its place. The bSQL programming language allows us to:

• Prove that the data hasn't been changed.
• Run optimized queries from structured, immutable data.
• Easily compare database versions and move through time effortlessly.

Application Overview

We will be using the Daml quick-start tutorial to set up a basic application through the following steps:

1. Deploy the sample IOU Daml application
2. Configure a bSQL instance
3. Connect the database to the application using JDBC 4. Work in the UI to populate our database
4. Run queries on immutable data

Deploying the DAML quick-start application

In order to complete this tutorial there are a few prerequisites:

• Apache Maven and the Java JDK.
• A good JAVA IDEA, I used IntelliJ.
• Knowledge of the Daml model and application structure.
• The Daml SDK.

After installing, set up the quick-start application by reading the tutorial or by running the following in your command line: daml new quickstart --template quickstart-java. This command generates a new quick-start application. As a third option, pull the source code, however you will still need to deploy a bSQL instance and modify the connection parameters appropriately.

We will be mostly working in IouMain.java and make changes to pom.xml to resolve conflicts.

Deploying a bSQL instance

The next step is to deploy and set up our bSQL instance by:

• Deploying a database using the Blockpoint Portal
• Opening the instance in the IDE
• Creating a database and a blockchain

1.) The tutorial for deploying your first instance can be found here. Once completed, a new instance should appear on the blockpoint portal home page.

2.) Once created, navigate to the home page. To open the IDE, click "Open in IDE" and, when prompted, provide your database credentials.

3.) Finally we are going to run a few bSQL commands to finish our set up.

a. Create a new database called "iou" by running CREATE
DATABASE iou; 

b. Interact with the newly created database by running USE
iou;

Next, we are going to want to configure a single blockchain for capturing contract data. A blockchain is a structured container for storing data in bSQL. Once data has been added to the system, it cannot be removed. For a more comprehensive overview on the blockchain structure read the documentation here.

For the sake of keeping this tutorial simple, we are going to use a single blockchain called contracts to track the flow of contracts in the ledger. Using a traditional blockchain we can track contract versions. Deploy the blockchain by running the following command in the IDE.

CREATE BLOCKCHAIN contracts TRADITIONAL (
     id UINT64 PRIMARY,
     unique_identifier STRING PACKED,
     issuer STRING PACKED,
     owner STRING PACKED,
     currency STRING PACKED,
     amount FLOAT32
);

Connecting the database via JDBC

We are going to use the MDB JDBC to connect to the database created in the previous step. In order to connect, we must add the JDBC dependency to the pom.xml file and resolve any conflicting dependencies.

Only adding the JDBC dependency to the pom.xml will not work. Because both the Daml Application and the JDBC use different versions of protocol buffers, they must be resolved via the <dependency management> field in the pom.xml file. I highly recommend replacing the current pom.xml file with the example provided instead of doing this manually.

The current version of the JDBC is 1.0.7, I recommend using the latest release possible.

Connecting to the bSQL client

The next step is defining our connection URL and logic. Define a class Utils by creating a Utils.java file in the com.daml.quickstart.iou directory and copying the following code. This class has a single method called connect().

package com.daml.quickstart.iou;
import java.sql.Connection;
import java.sql.DriverManager;

public class Utils {
    public Connection connect() {
        Connection c;
        try {
            // Remove brackets when specifying info
            c = DriverManager.getConnection("jdbc:mdb://{your public bSQL IP address}:5461/iou?user={your bSQL username}&password={your bSQL password}");
        } catch (Exception e) {
            e.printStackTrace();
            System.exit(1);
            return null;
        }
        System.out.println("succesfully connected to bSQL!");
        return  c;
    }
}

The connection string on line 9 will be unique to your instance. The following information is needed:

• your public bSQL IP address (this can be found under the "essentials" dropdown above your instance metrics on the home page)
• your username and password

After calling the connection method Utils.connect(); a connection to the database in the instance is returned. This connection can then be used to send data to the contracts blockchain. The rest of the work is done in iouMain.java.

Adding bSQL logic

We will be adding two methods to the IouMain class, the first will be the database logic for adding a contract, the second will be logic for archiving a contract.

Adding a new contract converts the contract to a simple record, and adds it to the contracts blockchain.

static void addContract(Connection c, Iou.Contract contract, Long id) throws SQLException {
        try {
            Statement stmt = c.createStatement();
            String sql = String.format(
                    "INSERT iou.contracts VALUES(%d, \"%s\", \"%s\", \"%s\", \"%s\", %f);",
                    id,
                    contract.id.contractId,
                    contract.data.issuer,
                    contract.data.owner,
                    contract.data.currency,
                    contract.data.amount);
            stmt.execute(sql);
            c.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
    }

Lines 195–212 in the repo.

Archiving a contract is simple, in order to archive all we have to do is discontinue the record from the contracts blockchain.

static void archiveContract(Connection c, ArchivedEvent archivedEvent, Long id) throws SQLException {
    try {
        Statement stmt = c.createStatement();
        String sql = String.format(
            "DISCONTINUE iou.contracts (id) VALUES (%d)",
            id);
        stmt.execute(sql);
    } catch (SQLException e) {
        e.printStackTrace();
        System.exit(1);
    }
}

Lines 214–226 in the in the repo.

An important thing to note:

• Instead of the traditional SQL statements update and delete, bSQL uses the pseudo-mutations amend and discontinue. 
• Discontinuing a contract in bSQL adds a tombstone record to mark the record as no longer existing. This updates the current state of the contracts blockchain to no longer include the original record, yet the old record will always be accessible in previous states. If this doesn't make sense, move on and see it in action.

Connecting bSQL logic to the application

Now we can use the Utils package we wrote earlier to connect to our bSQL instance. In iouMain.java at the beginning of the main method, the DamlLedgerClient is built and the connection is established. The code you need to add is on lines 8–9 in the snippet below. This establishes a connection to the bSQL instance.

// Create a client object to access services on the ledger.
DamlLedgerClient client = DamlLedgerClient.newBuilder(ledgerhost, ledgerport).build();

// Connects to the ledger and runs initial validation.
client.connect();

// Establishes a connection to the bSQL instance
Utils u = new Utils();
Connection c = u.connect();

Lines 61–67 in the repo.

The next step is to call the addContract and archiveContract methods in our application stream, effectively sending information to the bSQL instance every time a contract is added or archived.

• If the contract is a created event, we call addContract after the in-memory maps are updated.
• If the contract is an archived event, we call archiveContract after the maps are updated.

 Disposable ignore =
        client
            .getTransactionsClient()
            .getTransactions(acsOffset.get(), iouFilter, true)
            .forEach(
                t -> {
                  for (Event event : t.getEvents()) {
                    if (event instanceof CreatedEvent) {
                      CreatedEvent createdEvent = (CreatedEvent) event;
                      long id = idCounter.getAndIncrement();
                      Iou.Contract contract = Iou.Contract.fromCreatedEvent(createdEvent);
                      contracts.put(id, contract.data);
                      idMap.put(id, contract.id);
                      try {
                          addContract(c, contract, id);
                      } catch (Exception e) {
                          e.printStackTrace();
                          System.exit(1);
                      }
                    } else if (event instanceof ArchivedEvent) {
                      ArchivedEvent archivedEvent = (ArchivedEvent) event;
                      long id =
                          idMap.inverse().get(new Iou.ContractId(archivedEvent.getContractId()));
                      contracts.remove(id);
                      idMap.remove(id);
                      try {
                          archiveContract(c, archivedEvent, id);
                      } catch (SQLException e) {
                          e.printStackTrace();
                          System.exit(1);
                      }
                    }
                  }
                });

Lines 99–132 in the repo.

Running the Application

Now that all the logic has been added, we will be running our application and generating data. 
Below are the steps for running the application locally as well as the successful responses for reference.

1. Navigate to the quickstart directory
2. Run daml build in terminal to generate the .dar file.
3. Generate java code by running daml codegen java
4. Compile the code using mvn compile
5. In a separate terminal run daml sandbox .daml/dist/quickstart-0.0.1.dar to start the the sandbox
6. In a separate terminal start the java code by running mvn exec:java@run-quickstart
7. If there was an error and the connection didn't print successfully, check your connection URL in Utils.java
8. In another terminal initialize some contracts by running daml script - dar .daml/dist/quickstart-0.0.1.dar - script-name Main:initialize - ledger-host localhost - ledger-port 6865 - static-time
9. You can then launch the UI by running daml navigator server

Using the Application

Once the application is running, we will be using the UI on localhost to generate data. Additionally, we will be using the bSQL IDE to look at the data we generated. To help distinguish between platforms, steps with N refer to using the navigator and steps with B refer to using the bSQL portal.
Create an IOU and begin a transfer.
1. N: Select Alice from the drop down menu

2. N: Navigate to the Templates page and select the first option Iou:Iou. Issue yourself one AliceCoin by filling out the template like below and hitting "Submit".

3. B: Once we have chosen to transfer this IOU to Bob, the contract will be archived. When the above contract is discontinued from contracts it no longer appears in the current state. This can be shown by running SELECT * FROM contracts WHERE id = 1; when no records are returned.

We can easily access this contract by querying from the lifetime of the contracts blockchain by running SELECT *, sys_timestamp FROM LIFETIME contracts WHERE id = 1; this provides us with the following records:

The first entry is the original contract. The second entry is called a tombstone record, it's used to mark the primary key as no longer existing in the current state. Additionally, since we selected the sys_timestamp column, a built in column for all blockchains, we can note the time this contract was archived.

8. N: You are now going to switch user to Bob, so you can accept the trades you have just proposed. Start by clicking on the "logout" button next to the username, at the top of the screen. On the login page, select Bob from the dropdown.

9. N: First, accept the transfer of the AliceCoin. Go to the Iou Transfers page, click on the row of the transfer, and click "IouTransfer_Accept", then "Submit".

10. B: After logging in as Bob and accepting the IOU transfer for AliceCoin we can observe the new contract that replaced the archived contract. We can find this contract by reading from the current state by running SELECT * FROM contracts WHERE currency = "AliceCoin"; .

What else to run?

Validating Your Data Hasn't Changed

In bSQL data pages are stored in a blockchain format. They are hashed and linked together. As a database user you can check that the data hasn't been illicitly modified by a bad actor. This can be easily done by checking all data digests - a unique numerical representation of a data page - in the system by running a check validity command.

USE master;
CHECK VALIDITY;

Additional security is added when we export, distribute, and let others validate the database. This prevents authority illicit changes, where entire chains of data are swapped for seemingly valid ones. To read all digests from the iou database, run a read digest command.

USE iou;
READ DIGEST iou;

We can download the digest as a CSV and distribute it across technologies for validation later.

Lifetime Queries

We can use the lifetime of contracts in more complex queries. For example, I could find the number of contracts ever owned by each user by running the following query.

SELECT owner, COUNT(*) FROM LIFETIME contracts
    WHERE NOT ISNULL(owner)
  GROUP BY owner;

Selecting from a Specific Version

bSQL allows you to interact with different states of the ledger by setting the transaction query time. For example, I can set my transaction query time to when I issued Alice the AliceCoin by running SET TRANSACTION QUERY TIME "2021–07–28 19:27:51.131868043"; . This sets the scope of the current state back to the time specified, all queries I run after this transaction will interact with the current state as if it was at this time period.

Final Thoughts

In this article I showed you how to set up the Daml quick-start application and a bSQL instance. We then added bSQL logic to the application and observed a the lifetime of a contract using bSQL.

Here are some resources used to write this article:

• The Daml quick-start tutorial
• The bSQL docs and video tutorials

This was a very simple connection to demonstrate compatibility in logic. There are many ways to use these technologies together.

Full code is on github.