DEV Community: Cassidy Mountjoy

Immutable File Storage

Cassidy Mountjoy — Thu, 07 Apr 2022 19:07:13 +0000

Sensitive files should be stored immutably. This seems like a no brainer, but why isn't it being implemented in modern applications?

I think that many developers are turning to Blockchain technology to solve security problems. Momentum for projects is then soon slowed by the steep learning curve and cost of operating blockchain transactions.

By improving the security of traditional technologies, such as databases, developers can continue using a familiar stack. Immutable databases are great for storing sensitive files. Using the bSQL language you can easily store immutable files.

Immutable data is stored in "pen" not "pencil" and you wouldn't sign a contract in pencil.

Demonstration

This post will demonstrate how to set up immutable file storage in bSQL using Node. The full code can be found on GitHub.

Let’s define the basic workflow of our example application.

Store a file with a description in the database.
Confirm the file hasn't been illicitly modified.
Export the file from the DB and read the file and its corresponding description.

Disclamer: I am relatively new to Node feel free to make comments and suggestions to help me improve the code.

Setting Up the Database

In order to store files immutably, you'll need an immutable database. The bSQL community slack channel gives you direct access to engineers to help you get set up, by joining the slack you'll receive an access code for a free bSQL instance.

Establishing the connection

We will be working with the database directly. In order to do so we will need the node connector. The current version is 3.20.0, but you should install the latest. The package requires protobuf, so if you are installing manually you will need to run npm install google-protobuf grpc AND npm install mdb-server.

Now you can import the package.

import * as mdb from "mdb-server";

And establish the connection:

let conn = await mdb.CreateConnection(
    {
        username: "your_username",
        password: "your_password",
        serverAddress: "0.0.0.0",
        serverPort: 5461,
        databaseName: "master",
        parameters: new Map([["interpolateParams", true]])
    }
)

// Connect to the database
await conn.connect()

Building the Containers

In order to store a description and its corresponding file, we create a new database and blockchain to link our descriptions to our files.

await conn.exec(`CREATE DATABASE ${dbName}`)

In bSQL blockchains are like sql tables. There are many different blockchain types, we will be using a HISTORICAL PLUS. The blockchain created by the CREATE BLOCKHAIN command will have the following columns:

id is a primary key that is incremented after each insertion.
file_id references the corresponding file stored in the file store.
description is a packed string that describes the file.

 await conn.exec(`CREATE BLOCKCHAIN ${dbName}.${blockchainName} HISTORICAL PLUS (
        id UINT64 AUTO INCREMENT PRIMARY,
        file_id UINT64 FOREIGN KEY [document_store.sys_file, sys_file_id],
        description STRING PACKED)`)

Store the local file in the database

bSQL has built in file storage. This makes it very easy to store files. There is no way to delete or update an existing file once it's stored. This ensures that centralized authorities can't illicitly modify sensitive documents.

This command stores the file blockpoint.png located in the app directory. It uses the response to store the file_id and a despcription in the reference table.

let resp = await conn.storeFile(imageName, "app/blockpoint.png", "png")

await conn.exec(`INSERT ${dbName}.${blockchainName} (file_id, description) VALUES 
    (?, ?)`,
            [resp.getLastInsertId(), description])

Check the validity of the database

Data in bSQL is hashed and linked together. We can check the recompute hashes and compare them to the hashes stored on insertion by running CHECK VALIDITY.

resp = await conn.exec("CHECK VALIDITY")

Export the file and save it to a local directory

Exporting the file is a two-step process. The file will be saved to app/exports/out_image.png when resp.saveFile() is called.

resp = await conn.exportFile(imageName, "app/exports/out_image.png", "png")
    await resp.saveFile()

File Versioning

If there was an amendment to a document, how could we extend this application to update the file?

Use cases:

Building Codes
KYC documents
Legal Contracts

You could simply store the new file, run an AMEND statement of the reference blockchain. This would give you access to the new version and the previous version as well.

How would I access the previous version?
We can set a database session to work with a previous version of the database. I recommend checking out SET TRANSACTION QUERY TIME.

Connecting bSQL to Tableau!

Cassidy Mountjoy — Wed, 30 Mar 2022 15:17:49 +0000

One of the advantages of the bSQL language is that it's both immutable relational. Other ledger database technologies are modifications on document storage. While they may be "SQL compliant" to some degree, these implementations are non-native and lack complex query features and optimization.

SQL-based technologies, such as Tableau, use SQL to query data. There is immense value in using live database connections. I'll be going over how to connect a bSQL instance to Tableau.

Pre-requisites:

Tableau Desktop > 2020.4
A bSQL instance (instructions here)

Download the JDB Driver

The bSQL JDBC driver can be found on the Maven Central Repository here. You will need to download the latest version as a .jar file.

Download the `.taco` custom connector

In order to connect your bSQL instance to Tableau you need the bsql_jdbc connector. This connector allows for live communication between your bSQL instance and Tableau Desktop. It can be downloaded from the bSQL documentation.

Configuring Tableau Desktop

Tableau desktop can be configured to use custom connectors.

Copy the connector you downloaded into your My Tableau Repository/Connectors directory.
Place the .jar file in the folder for your operating system.
- Windows: C:\Program Files\Tableau\Drivers
- Mac: ~/Library/Tableau/Drivers
- Linux: /opt/tableau/tableau_driver/jdbc

Ready to connect

Now when you start Tableau Desktop there should be an option for bSQL: bsql_jdbc. If the option doesn't show up, double check the file path in which you placed the connector.

After choosing the connector, this should open the connection dialogue. From here you can specify the bSQL instance you wish to connect to.

Continuations

In the run initial SQL pane you can add bSQL specific features such as setting the state of the database you want to work by using SET TRANSACTION QUERY TIME
Using the New Custom SQL we can generate views on lifetime attributes of tables. For instance, creating.

Streaming Ethereum Blocks Into bSQL Using Infura and Python

Cassidy Mountjoy — Wed, 10 Nov 2021 02:05:54 +0000

Overview

Blockchain data is secure and tamper proof, but when working with blockchain data in a more traditional environment, let's say a conventional database, it becomes harder to extend these guarantees. Can data be trusted after it has left the blockchain?

By using a less traditional form of DBMS, an immutable database, we can:

Verify that data hasn't been illicitly changed
Track all changes made to the system
Easily access old versions of the system

In this tutorial I will be using Blockpoint's bSQL, because it's immutable, relational, structured, and has a rich language. The bSQL storage structure is actually very similar to that of a blockchain in that data pages are hashed and linked together. Data is added, never deleted:

Let's Stream Some Ethereum Blocks

We will be using an Infura free trial to access the Ethereum network, Python to filter for new blocks, and bSQL to store blockchain data. In order to do so we will:

Sign up for a free Infura account and obtain an Ethereum endpoint.
Create a bSQL account and deploy a free instance.
Write a python script and start streaming Ethereum blocks to the database.

At any time you can reference the public repo

Setting up Infura

Register for a free Infura account here. This will give you access to 100,000 Requests/Day, which is plenty given that this is just a demonstration.

Once you've set up your Infura account, you can access your project ID, it will be needed to connect to Infura using an endpoint and can be found under your project name.

The endpoint for accessing the data will resemble the following:

https://mainnet.infura.io/v3/your_project_id

We will use this endpoint when we set up our python application.

Deploying a bSQL instance

The next step is to 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

In order to create a bSQL account you will need an a unique access token, you can get your access token by messaging me directly or joining the slack, it's free!

1.) The tutorial for creating an account and 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 "eth" by running CREATE DATABASE eth;

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

Next, we are going to want to configure a single blockchain for capturing Ethereum 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 blocks to track new blocks added to the Ethereum network. Using a historical blockchain we can enforce immutability and check data integrity. Deploy the blockchain by running the following command in the IDE.

CREATE BLOCKCHAIN blocks HISTORICAL (
     time TIMESTAMP,
     number UINT64,
     hash STRING SIZE=66,
     parent_hash STRING SIZE=66,
     nonce STRING SIZE=42,
     sha3_uncles STRING SIZE=66,
     logs_bloom STRING PACKED SIZE=18000,
     transactions_root STRING SIZE=66,
     state_root STRING SIZE=66,
     receipts_root STRING SIZE=66,
     miner STRING SIZE=42,
     difficulty FLOAT64,
     size_of_block INT64,
     extra_data STRING PACKED,
     gas_limit INT64,
     transaction_count INT64,
     base_fee_per_gas INT64
);

Congrats on building your first bSQL blockchain! Now let's start adding data.

Setting up your python application.

In order to set up your python applet you will need the following:

Python downloaded and installed on your computer, I'm using python 3.9.
A python IDE, I'm using Pycharm
web3 for python
The bSQL python bSQL database driver

Once the follow criteria are met, you can set up a main.py file in your project directory. The code is on Github although it will not work until the above criteria is met and you have deployed an instance.

Connecting to Infura and bSQL

The first step in our code is to define our connections. You will need to fill out the following fields:

your Infura project id
your bSQL username, password and public IP address

infru = "https://mainnet.infura.io/v3/your_project_id" #change me
web3 = Web3(Web3.HTTPProvider(infru))
conn = mdb_bp.driver.connect(
    username="your username", #change me
    password="your password", #change me
    connection_protocol="tcp",
    server_address="server address", #change me
    server_port=5461,
    database_name="eth",
    parameters={"interpolateParams": True},
)

Defining our main method

The main method defines a filter for the latest Ethereum block and passes it into our loop that we will define next.

def main():
    block_filter = web3.eth.filter('latest')
    loop = asyncio.get_event_loop()
    try:
        loop.run_until_complete(
            asyncio.gather(
                log_loop(block_filter, 2)))
    finally:
        # close loop to free up system resources
        loop.close()

if __name__ == '__main__':
    main()

Defining our Loop

Our loop sleeps for a desired interval, then attempts to pull new entries from the event filter. Every time a new entry is received the event is handled in the handle_event function.

async def log_loop(event_filter, poll_interval):
    while True:
        for PairCreated in event_filter.get_new_entries():
            handle_event(web3.eth.get_block(PairCreated))
        await asyncio.sleep(poll_interval)

Defining our Event Handling

Time for the database call. Every time a new block is added to the chain, we print to the console and send an insertion statement to the database, inserting block data into blocks.

def handle_event(block):
    print(block['number'])
    conn.exec("INSERT blocks VALUES (" +
              "\"" + str(datetime.datetime.utcfromtimestamp(block['timestamp'])) + "\"," +
              str(block['number']) + "," +
              "\"" + str(block['hash'].hex()) + "\"," +
              "\"" + str(block['parentHash'].hex()) + "\"," +
              "\"" + str(block['nonce'].hex()) + "\"," +
              "\"" + str(block['sha3Uncles'].hex()) + "\"," +
              "\"" + str(block['logsBloom'].hex()) + "\"," +
              "\"" + str(block['transactionsRoot'].hex()) + "\"," +
              "\"" + str(block['stateRoot'].hex()) + "\"," +
              "\"" + str(block['receiptsRoot'].hex()) + "\"," +
              "\"" + str(block['miner']) + "\"," +
              str(block['difficulty']) + "," +
              str(block['size']) + "," +
              "\"" + str(block['extraData'].hex()) + "\"," +
              str(block['gasLimit']) + "," +
              str(len(block['transactions'])) + "," +
              str(block['baseFeePerGas']) + ")")

And that's all the code needed, so give that baby a run.

Putting it all together

After letting my program run for about an hour, I stopped my script and started to do a little data exploration.

I ran a few queries in the bSQL portal and included them in the repo. You can load this file into the bSQL IDE or write your own queries.

Here's what I came up with:

Conclusion

There you have it. A fun little script for Ethereum data. There is definitely more to explore when it comes to how the data is pulled and even more queries to write.

Like always, please comment your feedback or any questions you may have.

Building a Smart Contract Application Using bSQL and Daml

Cassidy Mountjoy — Mon, 11 Oct 2021 16:42:41 +0000

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:

Deploy the sample IOU Daml application
Configure a bSQL instance
Connect the database to the application using JDBC 4. Work in the UI to populate our database
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.

Navigate to the quickstart directory
Run daml build in terminal to generate the .dar file.
Generate java code by running daml codegen java
Compile the code using mvn compile
In a separate terminal run daml sandbox .daml/dist/quickstart-0.0.1.dar to start the the sandbox
In a separate terminal start the java code by running mvn exec:java@run-quickstart
If there was an error and the connection didn't print successfully, check your connection URL in Utils.java
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
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.

How do I imbed gifs in my Dev Community comments?

Cassidy Mountjoy — Fri, 10 Sep 2021 15:35:20 +0000

Show me the way...

FAQ: blockchain technology in bSQL

Cassidy Mountjoy — Tue, 07 Sep 2021 17:24:15 +0000

Intro

The bSQL language by Blockpoint Systems is a SQL for interfacing with immutable data. bSQL is not a decentralized blockchain, neither is it a traditional database management system. This resource is meant to provide and explanation of "blockchain" in bSQL.

How is this blockchain?

The blockchain storage structure is the foundation of data storage in bSQL. This doesn't change the fact that bSQL is a centralized database management system. All data in bSQL is stored in centralized blockchains within a single machine.

Built-in blockchain storage provides an additional layer of security without compromising data privacy. A blockchain implementation is more secure than traditional systems and provides the following benefits.

Prevents Illicit Changes from Inside Employees: Users cannot update or delete physical data.
Detects Illicit Hacker Changes: Blockchain detects changes to data by storing a cryptographic digest.
Detects Authority Ordered Illicit Changes: MDB digests can be freely distributed publicly. Cover-ups can be detected by comparing the published digest to the current contents.

How do I use the blockchain-ness?

The blockchain aspect of MDB is abstracted from the database user. Every time you read or write blockchain hashes are verified. You can manually validate all hashes by running a CHECK VALIDITY command on the master database.

What are the different kinds of blockchains?

Different bSQL blockchains provide an interface-like method of interfacing with immutable data.
They optimize applications based-off of table / blockchain usage patterns.

HISTORICAL → Optimized for historical data collection and logging. The historical blockchain is a bare-bone
ledger optimized for fast insert speeds, large datasets, and non-primary key dependent data.
HISTORICAL PLUS → Handles primary key dependent data and is optimized for infrequently updated data with inter-blockchain dependencies.
TRADITIONAL → Provides traditional features such as primary and foreign keys as well as blockchain-specific attributes such as time series selection, validity, and lifetime tracking. This container was designed to manage relational data while providing fast access to mutation history.
SPARSE → Provides the same functionality as a traditional blockchain but optimizes storage by only storing changed values.

How do I determine which blockchain to use?

The table below illustrates the basic methodology for choosing a blockchain to fit your database needs.

Blockchain	FREQUENT INSERTIONS	INFREQUENT INSERTIONS	FREQUENT UPDATES	INFREQUENT UPDATES
`historical`	✔️		✕	✕
`historical(+)`	✔️			✔️
`traditional`	✔️	✔️		✔️
`sparse`		✔️	✔️

Decentralized Security From a Centralized Database

Cassidy Mountjoy — Thu, 02 Sep 2021 18:41:41 +0000

Blockchain technology is great don't get me wrong but does traditional peer-to-peer blockchain fit most business cases? Most businesses would not benefit from publicly distributed data and private blockchain services are very expensive.

This begs the question... How can I get similar security features from my centralized database?

Blockpoint is able to prove the authenticity of records by exporting cryptographic digests: a unique, mathematical representation of the data, which allows external sources to validate data without having access to it. In this article we will examine the lifecycle of this validation technique.

Exporting Digests

The EXPORT DIGEST command reads data digests from the specified database object.

Syntax

READ DIGEST { <datbase_object> }

database_object ::=  
{   
   database_name.blockchain_name   
   | datbase_name    
}

Arguments

database_name
The target database to read the digests.

blockchain_name
The target blockchain to read the digests from.

Remarks

The READ DIGEST command reads page hashes, timestamps, and previous hashes and outputs the data in query format. The command returns a response with the following
columns:
- blockchain_id: The internal id of the blockchain.
- page_id: The page id of the blockchain.
- timestamp: The time that the page was sealed at.
- current_hash: The hash of that page as a UINT64.
- previous_hash: The hash of the previous page; stored as 0 for the first page in the blockchain.
Only blockchain pages in the archive are read. Blockchain pages are pushed to the archive when the page is full and no currently open transaction have written to that page. This ensures a ROLLBACK can't change archived data, resealing the page, and changing the hash.
Running a CHECK VALIDITY command before reading a digest ensures that digest is valid within the database and values haven't been illicitly changed.

A. Reading the digest from a blockchain

The following example reads the digest from the pricing blockchain.

READ DIGEST financial.pricing;

Output

The output returned reads the two archived pages in pricing, if there were no archived pages, no records would be returned. Notice that the previous hash of the first page is 0, because there is no previous page.

BLOCKCHAIN_ID [UINT16]  PAGE_ID [UINT64]    TIMESTAMP [TIMESTAMP]   CURRENT_HASH [UINT64]   PREVIOUS_HASH [UINT64]
19                      0              2021-05-24 16:46:05.991142800    6006725463818420546  0
19                      1              2021-05-24 16:46:05.992201900    3175351712536806224  6006725463818420546

Distributing a Digest

Distributing a digest is really up to an organizations digression. It safer to distribute digest files over multiple technologies. This can be through email and other messaging or file sharing services.

Validating a Digest

VALIDATE DIGEST checks the current digest against the provided.

Syntax

VALIDTATE DIGEST { <datbase_object> } PATH "file_path";

database_object ::=  
{   
   database_name.blockchain_name   
   | datbase_name    
}

Arguments

database_name
The target database to be checked against the provided digest.

blockchain_name
The target blockchain to be checked against the provided digest.

PATH "file_path
The filepath to the provided digests. The provided digests must be in CSV format and follow the column order produced by
the READ DIGEST command.

Remarks

Only blockchain pages in the archive are validated. Blockchain pages are pushed to the archive when the page is full and no currently open transaction have written to that page.
If data was added to the blockchain archive after the digest was exported. New data won't be validated.
Running a CHECK VALIDITY command before validating a digest ensures that digest is valid within the database and values haven't been illicitly changed.
A database or blockchain digest can be used to validate a blockchain.
Only a database digest can be used to validate a database.
DISCONNECTED blockchains are still validated as they can be reconnected to the database.

A. Validating a blockchain digest

The following example validates the digest of the pricing blockchain. The provided digest can be a blockchain or database digest.

VALIDATE DIGEST financial.pricing PATH "digests/may_5_digest.csv";

Output

If valid, the output provides the time at which the digest is valid given the last archived page's sealed time of the uploaded digest.

Database object financial_1.pricing is valid against the stored digests for archived pages before 2021-05-24 16:46:05.992201900

B. Validating a database digest

The following example reads all blockchain digests in the entire database.

VALIDATE DIGEST financial PATH "digests/may_5_digest.csv";

C. Invalid digest

The following example illustrates an invalid digest being compared to a valid database.

VALIDATE DIGEST financial PATH "digests/may_5_bad_digest.csv";

Output

An invalid digest returns specifics on the failed data. In this case, the timestamp on the first page of the system blockchain syscolumns has been modified in either the database or the imported digest.

Blockchain syscolumns: timestamp mismatch when verifying digest page 0, digest timestamp 2021-05-21 19:14:14.9512269 +0000 UTC not equal to system timestamp 2021-05-24 16:46:05.9175726 +0000 UTC

Conclusion

The digest lifecycle described above allows outside sources to validate that data within a centralized database hasn't been illicitly changed. Nifty!

Hindsight 20/20: Querying Any Point in Time

Cassidy Mountjoy — Wed, 01 Sep 2021 14:52:15 +0000

Today I'll be showing you how to use transactions to simulate snapshots in bSQL. Because of its multi-version protocols users can access old data by running queries with a set query time. By setting a query time all subsequent queries run as if they had been run at that time. This allows for analysis of old versions of the database, giving you rich data insights. The following diagram illustrates snapshotting in traditional databases and setting the query time with blockpoint's immutable database.

Syntax

SET TRANSACTION 
    QUERY TIME <query_time>

<query_time> ::=
    timestamp
    | STATE

QUERY TIME
Sets queries to run in a snapshot-like environment at the time specified. Values that were AMENDED or DISCONTINUED after the specified time will be restored and relational entities are guaranteed to hold.

[!NOTE]
The query time is currently specified in UTC time format.

STATE
Specifies that queries should operate normally, on the current state of the system.

Limitations
- LIFETIME queries and QUERY TIME are mutually exclusive. An error is thrown if a lifetime query is run on any version other than STATE.
- Queryies run on a previous database version always use the READ UNCOMMITTED isolation level.

timestamp
The time to read from. timestamp can be specified as a timestamp in quotations or an expression that computes to a valid timestamp.

Setting the query time.

We can use the QUERY TIME keyword to simulation a snapshot at the time specified. Because the system continuously tracks changes setting the transaction time should have only a small effect on performance. Although all the statements below were technically committed, we can still view previous states.

CREATE BLOCKCHAIN users TRADITIONAL (id UINT64 AUTO INCREMENT PRIMARY, name STRING PACKED);

INSERT users (name) VALUES ("john"), ("jimmy"), ("jeff");

--Assume Time = "2021-02-26 00:07:10.000000000"

DISCONTINUE users (id, name) VALUES (1, "jimmy");

SET TRANSACTION QUERY TIME "2021-02-26 00:07:10.000000000";

SELECT * FROM users;

OUTPUT

Since "jimmy" was inserted into the blockchain before the specified transaction time. The record persists in that state of the database.

ID	NAME
0	john
1	jimmy
2	jeff

Conclusion

Although this example is trivial, the same logic is used for audits and data analysis. It uses the database structure in a unique way to give users access to data evolution over time.

How to Create a Database and Blockchain in bSQL

Cassidy Mountjoy — Mon, 23 Aug 2021 20:52:01 +0000

In this tutorial we will be introducing how to set up a new database, blockchain, and perform your first mutation. If you
prefer to learn in a video format, you can watch the tutorial online.

You can create a bSQL account through the Blockpoint website or join the slack for direct assistance from an engineer.

We will be:

Creating a new database
Starting a session on that database
Creating a blockchain
Populating your blockchain
Reading from your blockchain

Opening the IDE

After selecting the "open IDE button" you will automatically navigate to the IDE. A few things are important to note:

You will need to provide your username and password to connect to bSQL
Once you connect, a session will be started on the master database, a hub for system metadata where work and application logic should not be performed

Creating a database

Once you have the master database open in the IDE we can run a CREATE DATABASE command to build a database.

I will be calling mine marketplace, and will create a new database by running the following statement:

CREATE DATABASE marketplace;

Start a session on the database

After we have created a database, it is bSQL convention to begin a session on our working database. To do this we run a
USE statement followed by the name of our database. Now marketplace is used as the default database
for all subsequent statements.

USE marketplace;

Create a blockchain

Creating an immutable Blockchain is as simple as creating a table in other SQL databases. There are four types
of blockchain in bSQL. Before defining the schema, we specified that the blockchian would be of type TRADITIONAL.
Reference the CREATE BLOCKCHAIN documentation for full syntax and keywords.

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

Data types are explained in data types. You can read the columns from the database by writing a
DESCRIBE statement.

DESCRIBE marketplace.contracts SCHEMA;

We can use an INSERT statement to add immutable data to our blockchain. Insert a new contract into the blockchain we
created by running the following statement. Once data is added, it can't be modified or illicitly changed.

INSERT contracts VALUES (
    0,
    "25c3d24f-0fc3-4a99-a292-98302dc0b5d0",
    "bank",
    "joe",
    "USD",
    120.0
);

If we want to change values in bSQL we can write an AMEND statement. It is important to note that
this statement doesn't modify existing values, only updates the current version relative to the primary key.

AMEND contracts VALUES (
    0,
    "36e55e77-3ef2-4d8c-9f74-58895f4193b5",
    "joe",
    "kate",
    "USD",
    120.0
);

To prove that data hasn't been illicitly modified we can run a CHECK VALIDITY command to
validate our data digests.

CHECK VALIDITY;

To query from the current state of the database, use a SELECT statement.

SELECT * FROM contracts;

Output:

Additionally, we can access the record history of the blockchain by applying the LIFETIME keyword to the contracts blockchain.

SELECT * FROM LIFETIME contracts;

Output:

Wondering How Your Data is Evolving? Time Travel Queries in bSQL Can Help

Cassidy Mountjoy — Thu, 19 Aug 2021 15:20:43 +0000

What is a Time-Travel Query?

Imagine a database that never forgets, no matter what you throw at it. A place for unbiased facts where all changes are tracked. Now imagine you are roped into an adventure with a crazy data science robot. He puts you in the bSQL time-travel mobile and takes you on a trip down data lane.

You learn that application data is always evolving as values are added and removed from the database. Through rich record histories, you find yourself accessing a new dimension of data by exploring its evolution over time.

Enhanced visibility is perhaps one of the most powerful features of bSQL and allows us to answer important questions like: What was this value before I updated it? When did this value get deleted? How does the history of my data affect the current state?

bSQL allows you to access the history of your data by writing special “time-travel” queries.

Financial Data Example

In the financial demo database there are two containers.

companies keeps track of the company metadata such as name, sector, and symbol.
pricing keeps track of the current stock prices.

Ever row in pricing references a row in companies. Every time the share price changes an AMEND statement is sent to the pricing container to make the corresponding updates. Logical right.

Querying from the pricing container using a basic SELECT statement reads from the current state of the system. Using our Multi-Version Database, we can run analytics on previous events. In the bSQL language this involves using the LIFETIME keyword to query from the lifetime of the container.

For a full description of the financial database check out the bSQL docs here.

Understanding the Lifetime Query

SELECT symbol, price, timestamp
   FROM LIFETIME financial.pricing;

The above query uses the LIFETIME keyword to query from the entire record history of the pricing container. The following output is produced:

It is important to notice a couple of things here:

The output is sorted by the primary key of the container, the symbol column.
The records associated with the primary key are ordered by the time the mutation was made in ascending order.
The first entry of the group represents when the record was inserted into the container.
Subsequent entries represent when values were either updated using the AMMEND command or removed from the current state using the DISCONTINUE command.
When a record is removed from the current state of the container using a DISCONTINUE command, a tombstone record is added to the data.

Let’s look at the record history of the symbol A. When the record was introduced into the container the price was 58.42. The records that follow it show how the price was updated. The final record with a NULL price value, represents a tombstone record. This means that A was removed with the DISCONTINUE command from the current state of the database at 2020–11–13 07:26:03.650678200.

Discontinued Data

Although it sounds paradoxical, let’s search for deleted data. Here we will use the DISCONTINUED keyword to filter our previous query.

SELECT * 
     FROM LIFETIME financial.pricing
  WHERE DISCONTINUED(pricing);

The corresponding output is:

SYMBOL    PRICE   ...    52_WEEK_HIGH       TIMESTAMP
A         NULL    ...    NULL       2020–11–13 07:26:03.650678200
MMM       NULL    ...    NULL       2020–11–13 07:26:03.670677500

When a DISCONTINUE statement is run, a tombstone record is inserted into the target container. Time-travel queries allow us to access the tombstones displayed above. As you can see, the primary key, in this case both MMM and A, as well as any timestamp column is preserved in the tombstone. This allows us to embed such statements in more complex queries and preserve discontinued data.

Joining and Aggregating Histories

Now let’s look at how we can use the LIFETIME keyword to gain insight into the history of our data.

SELECT c.name, COUNT(*) AS number_of_versions, AVG(p.price)
    FROM LIFETIME financial.pricing AS p 
       JOIN financial.companies AS c
       ON c.symbol = p.symbol 
    GROUP BY name
FILTER 10;

Let’s break down this query:

The complete history of pricing is joined with the current state of companies to retrieve the name metadata.
The records are then grouped by the name column and the COUNT and AVG functions are applied. This will return the number of versions of each primary key, as well as the average price over these versions respectively.
The output is limited to be the first 10 records.

This query returns:

C.NAME           NUMBER_OF_VERSIONS             AVG(P.PRICE) 
3M Co.           22                             133.92297224564985
ACE Limited      21                             103.51996685209728
AES Corp         21                             18.495395614987327
AFLAC Inc        21                             70.05538577125186
AGL Resources Inc. 21                           56.701499938964844
AMETEK Inc       21                             59.99528685070219
AT&T Inc         21                             25.695445378621418
AbbVie Inc.      21                             57.20997020176479
Abbott Laboratories 21                          45.31997081211635
Accenture        21                             89.67997051420666

The number of versions tells us the number of INSERT, AMEND, and DISCONTINUE statements that were run on each record. While other companies where changed 21 times, 3M Co. was changed 22 times, this makes sense because 3M Co. was discontinued from the data set, adding the “discontinued” version. We were able to compute the average price across all versions regardless of whether or not the record existed in the current state.

Using the Timestamp Column

Let’s see what we can uncover using the timestamp column.

SELECT symbol, MAX(p.price) AS max_price, MIN(p.price) AS min_price,
   MAX(p.timestamp) - MIN(p.timestamp) AS life_span
     FROM LIFETIME financial.pricing AS p
   GROUP BY p.symbol
  ORDER BY life_span DESC
FILTER 10;

Let’s take a deeper look at this query.

The complete history of pricing is grouped by the symbol column. We compute the min_price, max_price, and the time since the record was inserted and when it was last amended or discontinued as life_span.
We ordered the output by the life_span.
We limited the number of outputs to be the first 10 records.

SYMBOL          MAX_PRICE         MIN_PRICE           LIFE_SPAN
VLO             52.99             40.63915            182 
VMC             68.85             56.499134           182 
ZMH             98.06             84.10976            182 
VIAB            88.27             75.919136           182 
YUM             77.16             63.209763           182 
XYL             38.64             24.689758           182 
VFC             61.71059          51.781025           182 
XRAY            46.86             32.909756           182 
XOM             94.99             81.03975            182 
V               225.89061         215.96103           182

Our output produces an interesting dataset that gives us the amount of time between the first insertion and the last mutation in seconds. The dataset we produced allows us to analyze how the life span of a stock affects other target variables. As you can see, the bSQL language allows you to compute rich datasets that leverage the power of an immutable database.

Conclusion

If you made it this far, hats off to you Sherlock Codes! We are always working on more bSQL features and will continue to post. Our goal at blockpoint is to provide you with with insightful tools to get the most out of immutable databases. Please leave any comments or suggestions down below.

How immutable data can benefit your data-driven application.

Cassidy Mountjoy — Wed, 18 Aug 2021 18:32:35 +0000

The promise of blockchain technology set out to change data systems and revolutionize networks. Blockchain advocates pledged to decentralize voting, registration, commerce, and currency. Although various changes have been made to many of these fields, there is still a lot more work to be done. Don’t get me wrong, CryptoKitties is pretty cool and the cats are so darn cute, but I think blockchain technology is far from reaching its full potential.

One of the fundamental ideas behind blockchain systems is that data is immutable. Immutable data prevents adversaries from changing existing values within the database. This establishes trust and greatly enhances the security of the overall system, preventing data discrepancies that have potentially catastrophic results.

The Cabinet Dilemma

Traditional industries are becoming digitized, moving their pen and paper operations to applications and electronic storage. This entails trading locked file cabinets for database management systems. While the lock on the cabinet provides the primary layer of security, the documents inside have an additional layer of security, their wet signatures.

Immutable data is the equivalent of a wet signature. The paper has been signed, there is no going back or “modifying” the signature. On the contrary mutable systems are equivalent to signing the document in pencil and attempting to hide the eraser. If the cabinet is opened, the freedom to re-write data is granted to the adversary.

With over 80,000 cyber attacks per day, our virtual file cabinets need to be as secure as possible. You wouldn’t sign your documents in pencil would you?

Data breaches are dangerous to your customers, a headache for your engineers, and embarrassing. We should be doing as much as possible to stop such violations of privacy. Blockchain fundamentals, such as immutability, must be applied to traditional systems to add much needed security and create an overall safer environment.

Providing Additional Value to Immutable Data

On face value, immutable data deepens insight and enhances the security of your system. When applied to relational databases, immutable data provides other solutions to application demands, here’s why.

Speed

While developing an immutable relational database, we learned that storage techniques associated with write-only system are greatly simplified. When adding data to the system becomes a simple procedure, database resources are made more accessible to users.

Auditing and Analytics

Immutable data can be used as a trusted resource for auditing. Additionally, analytics teams have access to the complete history, allowing them to run more in-depth queries for analyzing changes over time.

Conclusion

When we explore blockchain fundamentals a bit deeper, its apparent that immutable data provides many needed benefits to our applications. As traditional industries move their services online, sensitive data should be secured at the lowest level possible. With that being said, immutable systems are a necessity for us to protect our businesses, sensitive information, and quality of life.

Blockpoint

At blockpoint Systems we value immutability and think you should too. We developed a multi-version database that brings together blockchain immutability with traditional relational database infrastructure. Check out our website or feel free to reach out to us at contact@blockpoint.systems.

Immutable Databases Are Here to Stay... pun intended

Cassidy Mountjoy — Tue, 17 Aug 2021 17:55:49 +0000

If understanding the inner workings of SQL and NoSQL databases didn’t leave your head spinning, I’d like to dust off a database relic, the Multi-Version database. These systems are actually a fairly simple concept and have been around for decades. Why haven’t you heard of them? Unless you spend your time reading academic papers it’s probably because they never got traction because of hardware constraints. When Multi-Version databases were introduced, storage was for the 1%… Now that storage is a lot cheaper, Multi-Version systems can be revisited as an applicable and cost-effective database solution.

Multi-Version databases are important because:

immutable datastores enhance security
accessible versioning provides in-depth analytics and auditing
updates to your data are faster

On a more technical note they:

reduce locking overhead
don’t require pages to be re-packed
eliminate the need for manual snapshots

Multi-Version and Data Storage

While Uni-Version Systems (SQL and NoSQL) only keep track of the current state of the database, Multi-Version systems track every change that has occurred in the system. Data is never truly altered or removed, updated data is added to the data files; data is immutable.

As a result, an append-only system increases the amount of storage used by an application. Initially, this made Multi-Version systems an expensive data management method.

Historic prices of hard drive space, from Matt Komorowski.

In the 1980s, shortly after the Multi-Version database was conceived, storage cost was upwards of $100,000 per Gb…. today storage costs less than $.05 per Gb. The major decrease in storage costs lets us reconsider the Multi-Version database.

Updating Your Data

In order to maintain the state of a Uni-Version system, the database management system has to use complicated control measures to coordinate access to recourses and prevent data corruption.

(Figure 1)

When access is granted to the resource, the data is said to have a “lock” on it. When User 1 requests to update a value (figure 1), a lock is added to the system. Data can’t be accessed by another source until the lock is released, hence, User 2 has to wait until the lock is released to perform an update. Locking makes resources inaccessible for an undefined period of time. Locking systems require downtime and compute, increasing the overhead associated with large transaction loads.

Multi-version systems don’t require complex locking mechanisms because data can only be added to the end of the file. In this fashion, requests for updates can be handled concurrently. By eliminating traditional locking, removing and updating data is inherently faster, allowing applications to run at higher speeds and certain data cleaning processes become obsolete.

Data Cleaning

When packed data is deleted or updated in a Uni-Version system, the space allotted to the original data is no longer in use and deemed inaccessible.

(figure 2) Red scribbles represent empty space in a data file.

Inaccessible space provides no value until it’s eventually recycled and made usable by the database management system during a process commonly called database “vacuuming”. For example, when updating a specific value, the system deems the old value unusable, appends the updated data to the end of the file, and cleans the empty space during the next vacuum cycle.

When data is “updated” or “deleted” in a multi-version system, the updated record is simply added to the end of the file. Previous data is never modified, instead, all changes are written to the next free slot. Multi-Version data mutations require only fetching the next free slot, there is no need to find the original tuple. This method decreases the number of data pages that are potentially brought into memory, increasing mutation speed, and reducing the amount of time that resources are locked.

Analytics

In traditional systems, snapshots are used to track changes to the database after the snapshot was created. Snapshots are commonly used for time-based analytics and auditing. As an application grows and analytics begin to develop, snapshots become a daily routine for teams.

Since Multi-Version databases track full database history, fine-grain snapshotting can be easily integrated into the system. Snapshots don’t rely on user commands as all versions of the database are accessible at all times. Fine-grained snapshots provide additional dimensions to your data. For example, in a Multi-Version database, it would be possible to get the state of a table before a specific value was updated. A rich data-history provides the analytics team access to insightful queries that span database versions.

(figure 3) A time travel query.

Conclusion

In conclusion, database implementations should adapt to the hardware that supports them. We have been relying on the same Uni-Version systems for the past 30 years. Is this the best implementation? How can we adapt our management systems to capitalize on the efficiency of cloud computing? Multi-Version databases were once an unrealistic and expensive solution, yet revisiting them uncovers a lot of programmatic benefits that are no longer constrained by costs. As the emphasis shifts from storage size to speed and analytics, a Multi-Version implementation becomes an effective choice for data-driven applications.

DEV Community: Cassidy Mountjoy

Immutable File Storage

Demonstration

Setting Up the Database

Establishing the connection

Building the Containers

Store the local file in the database

Check the validity of the database

Export the file and save it to a local directory

File Versioning

Connecting bSQL to Tableau!

Download the JDB Driver

Download the .taco custom connector

Configuring Tableau Desktop

Ready to connect

Continuations

Streaming Ethereum Blocks Into bSQL Using Infura and Python

Overview

Let's Stream Some Ethereum Blocks

Setting up Infura

Deploying a bSQL instance

Setting up your python application.

Connecting to Infura and bSQL

Defining our main method

Defining our Loop

Defining our Event Handling

Putting it all together

Conclusion

Building a Smart Contract Application Using bSQL and Daml

Introduction

Background

Application Overview

Deploying the DAML quick-start application

Deploying a bSQL instance

Connecting the database via JDBC

Connecting to the bSQL client

Adding bSQL logic

Connecting bSQL logic to the application

Running the Application

Using the Application

What else to run?

Validating Your Data Hasn't Changed

Lifetime Queries

Selecting from a Specific Version

Final Thoughts

How do I imbed gifs in my Dev Community comments?

FAQ: blockchain technology in bSQL

Intro

How is this blockchain?

How do I use the blockchain-ness?

What are the different kinds of blockchains?

How do I determine which blockchain to use?

Decentralized Security From a Centralized Database

Exporting Digests

Syntax

Arguments

Remarks

A. Reading the digest from a blockchain

Output

Distributing a Digest

Validating a Digest

Syntax

Arguments

Remarks

A. Validating a blockchain digest

Output

B. Validating a database digest

C. Invalid digest

Output

Conclusion

Hindsight 20/20: Querying Any Point in Time

Syntax

Setting the query time.

OUTPUT

Conclusion

How to Create a Database and Blockchain in bSQL

Opening the IDE

Creating a database

Start a session on the database

Create a blockchain

Download the `.taco` custom connector