Building a Smart Contract Application Using bSQL and Daml
Tags: blockchain, database, sql, datascience
Published: 2021-10-11 16:42:41

##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](https://docs.daml.com/app-dev/bindings-java/quickstart.html) 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](https://bsql.org/) 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
5. Run queries on immutable data

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/805nchyinv7g49eylu2n.png)

##Deploying the DAML quick-start application
In order to complete this tutorial there are a few prerequisites:
- [Apache Maven](https://maven.apache.org/download.cgi) and the [Java JDK](https://www.oracle.com/java/technologies/javase/javase-jdk8-downloads.html).
- A good JAVA IDEA, I used [IntelliJ](https://www.jetbrains.com/idea/).
- Knowledge of the Daml model and application structure.
- The Daml [SDK](https://docs.daml.com/getting-started/installation.html).

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](https://github.com/blockpointSystems/daml-quickstart-bsql), 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](https://www.youtube.com/watch?v=Em8b5qXcQew&list=PLdRS5wHN77gWuG71IvaH4pX7IdfZYGd06). Once completed, a new instance should appear on the blockpoint portal home page.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r0bqpmlohjwq03e9ruz2.png)

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](https://bsql.org/docs/blockchain-overview).

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](https://search.maven.org/artifact/com.blockpointdb/mdb-jdbc/1.0.7/jar) 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()`.

```java
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.

```java
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](https://github.com/blockpointSystems/daml-quickstart-bsql/blob/master/src/main/java/com/daml/quickstart/iou/IouMain.java).*

Archiving a contract is simple, in order to archive all we have to do is [discontinue](https://bsql.org/docs/mutations/discontinue) the record from the **contracts** blockchain.

```java
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](https://github.com/blockpointSystems/daml-quickstart-bsql/blob/master/src/main/java/com/daml/quickstart/iou/IouMain.java).*

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.

```java
// 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](https://github.com/blockpointSystems/daml-quickstart-bsql/blob/master/src/main/java/com/daml/quickstart/iou/IouMain.java).*

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.

```java
 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](https://github.com/blockpointSystems/daml-quickstart-bsql/blob/master/src/main/java/com/daml/quickstart/iou/IouMain.java).*

---

##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.
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/61m5o7lcfdo52ukiq3i0.png)
3. Generate java code by running `daml codegen java`
 ![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rkrxsw0whvt6eglq91zu.png)
4. Compile the code using `mvn compile`
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/osuy57wt5fi9zhvxmnve.png)
5. In a separate terminal run `daml sandbox .daml/dist/quickstart-0.0.1.dar` to start the the sandbox 
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bvh1dzda6wi63f7ss6ud.png)
6. In a separate terminal start the java code by running `mvn exec:java@run-quickstart`
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/28ty6apv6wnqvfvz09ch.png)
- If there was an error and the connection didn't print successfully, check your connection URL in `Utils.java`
7. 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`
8. 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](http://localhost:4000/) 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
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b1xh2rmnyhvk5vbgxd68.png)
**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".
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hdqw9vmu3ywuzv1g4c7e.png)
**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:
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vr5815fj2wef6maw8dcu.png)
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*](https://bsql.org/docs/database/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*](https://bsql.org/docs/security/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*](https://bsql.org/docs/transaction/set-transaction). 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](https://docs.daml.com/app-dev/bindings-java/quickstart.html)
- The bSQL [docs](https://bsql.org/docs/overview) and [video tutorials](https://www.youtube.com/watch?v=Em8b5qXcQew&list=PLdRS5wHN77gWuG71IvaH4pX7IdfZYGd06)

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](https://github.com/blockpointSystems/daml-quickstart-bsql).