Extracting Party data from Ledger in DAML

Table of contents

Reading Time: 5 minutes

In this blog, we will see what is extractor in DAML , why it is used and how to use it with a simple demo application. Before moving further I assume that you know basic of DAML if not then please read this blog.

What is Extractor ?

Extractor in DAML is use to extract contract data for a single party from a Ledger node into a PostgreSQL database. We can use it to extract all the data related to a party for a specific time interval or we can use it in stream mode to get a continuous flow of changes that are made to the party data extractor.

By using Extractor we can:

Take a full snapshot of the ledger (from the start of the ledger to the current latest transaction)
Take a partial snapshot of the ledger (between specific offset)
Extract historical data and then stream indefinitely (either from the start of the ledger or from a specific offset)

Use cases of extracting contracts from the Ledger

Application developers to access data on the ledger, observe the evolution of data, and debug their applications.
Business analyst to analyze ledger data and create report.
Teams to debug any problem that happen in production.

Process Flow

process flow

Setting up

Before we create our demo application, we need to setup our environment.

Download PostgreSQL. Use PostgreSQL version 9.4 or later to have JSONB type support that is used in the Extractor.
Now open PostgreSQL using following command.

sudo -u postgres psql

3. Now create a user of PostgreSQL using the following command and
grant all privilege to a new user.

create user myuser with encrypted password 'mypass';
grant all privileges on database mydb to myuser;

4. Create a database for each user in your application by using
the following command.

CREATE DATABASE DatabaseName;

Now we will see with an example of how can we use an extractor to extract single party data and how can we analyse it in a more sophisticated manner. I am using simple DAML model where a stock buyer requests stock purchase from Broker and in return broker give a share to the buyer and charge a fix commission.

Process Flow of Our Application

Image showing how client data transfer from ledger to PSQL

Main.daml

It contain main business logic.


module Main where

data Company
  = Tata
  | Reliance
  | Cipla
  | Maruti
  | Sunpharma
  | SBI
    deriving(Eq,Show)

template StockPurchase
  with 
    owner:Party
    shareCount: Int
    companyName : Company
    broker :Party
  where
    signatory owner
    observer broker
    
    choice Accept:(ContractId BrokerCommission,ContractId StockAllocate)
      controller broker 
        do
          commission <-create BrokerCommission with
            requester = this
            requesterParty=owner
            amount = 1000

          stockAllocate <- create StockAllocate with
            company = companyName
            numberOfShare = shareCount
            owner = owner
            broker = broker

          return (commission,stockAllocate)  


template BrokerCommission
  with
    requester :StockPurchase
    requesterParty:Party
    amount :Int
  where
   signatory requester.broker  
  

template StockAllocate 
  with 
    company : Company
    numberOfShare : Int
    owner : Party
    broker : Party
  where 
    signatory broker
    observer owner

Setup.daml

Script file is for initialising buyer request and Broker acceptance when we deploy our application.

module Setup where

import Daml.Script

import Main

initialize : Script ()
initialize = do
  alice <- allocatePartyWithHint "Alice" (PartyIdHint "Alice")
  shareBroker <- allocatePartyWithHint "ShareKhan" (PartyIdHint "ShareKhan")
  bob <- allocatePartyWithHint "Bob" (PartyIdHint "Bob")
  
  aliceTV <- submit alice do
    createCmd StockPurchase with
      owner = alice
      shareCount = 10
      companyName = Tata
      broker = shareBroker

  submit shareBroker do 
    exerciseCmd aliceTV Accept    

  bobTV <- submit bob do
    createCmd StockPurchase with
      owner = bob
      shareCount = 10
      companyName = SBI
      broker = shareBroker
  
  submit shareBroker do 
    exerciseCmd bobTV Accept

  aliceSecond <- submit alice do
    createCmd StockPurchase with
      owner = alice
      shareCount = 10
      companyName = Tata
      broker = shareBroker      
  
  submit shareBroker do 
    exerciseCmd aliceSecond Accept    
  
  aliceThird <- submit alice do
    createCmd StockPurchase with
      owner = alice
      shareCount = 10
      companyName = SBI
      broker = shareBroker      
  
  submit shareBroker do 
    exerciseCmd aliceThird Accept    


  pure ()

Now Start the application on Sandbox.

Extraction in non-streaming mode

In this mode, Extractor will fetch all party data up to current time and store it in specified database. As we already made a request on customer(upanshu) behalf in script. Let extract it by using following command.

daml extractor postgresql --user myuser --password mypass --connecturl jdbc:postgresql:test --party Alice -h localhost -p 6865  --to head

You can check the data of each table using following command.

select * from TableName;

Extraction in Stream mode

In this mode extractor will fetch party information in real time, as soon as any change is made in party data, extractor will fetch it and store in specified database.

for this we will start sandbox with navigator so our dealer(Apple) can reply customer query.

daml extractor postgresql --user myuser --password mypass --connecturl jdbc:postgresql:test --party Alice -h localhost -p 6865  --to follow

You can check in test database that four table are created. Each table contain party data up to this point.

for more information on Extractor you can visit here.

Extractor creates four tables with following name in extracting party database.

contract
exercise
state
transaction

Analysis

Let see an example of how can we use extractor for analysis purpose. We will extract share broker data and party data and use some cross-database query to extract some reasonable information.

Steps required before we do any perform any Cross DB related analysis.

Start extractor for a broker and any party in stream mode by using the following commands.

daml extractor postgresql --user myuser --password mypass --connecturl jdbc:postgresql:alicedb --party Alice -h localhost -p 6865  --to follow

daml extractor postgresql --user myuser --password mypass --connecturl jdbc:postgresql:brokerdb --party Broker -h localhost -p 6865 --to follow

Create a view of Contract table in both Broker Database and Alice Database.

// In AliceDb

CREATE VIEW account_view AS
SELECT
   create_arguments->>'owner' AS owner
  ,create_arguments->>'numberOfShare' AS shareCount
  ,create_arguments->>'companyName' AS companyName
FROM
  contract
WHERE
  package_id = '7897aa6766ed88ddc38623af0eaa461485382a85190050562a58721515380cdb'
  AND
  template = 'Main:StockAllocate';

// In BrokerDB
CREATE VIEW commission_view AS
SELECT
   create_arguments->>'amount' AS amount
  ,create_arguments->>'requesterParty' AS client
FROM
  contract
WHERE
  package_id = '00639631cb811ffd2c623f6bee4cfc25d1d031da2ed30acd56df41e15d1e7c90'
  AND
  template = 'Main:BrokerCommission';

Environment setup for accessing brokerdb table in alicedb visit here.

Now we have set up our environment we can access table data from other database and extract useful information. Let extract party share count and total commission charge by Broker on share purchase.

First fetch sum of all commission charge at all user from brokerdb.

//In Alicedb store crossdb query result into new table

create TABLE BrokerData AS( SELECT client,SUM(CAST(amount AS INTEGER)) as TotalAmount from (SELECT * FROM public.dblink
('demodbrnd','select * from public.commission_view')
AS DATA(amount TEXT
,client TEXT
)) as Test GROUP BY client);

Now make a join operation with account_view to show how much commission is charged on shares.

select m.owner,m.sharecount,t.totalamount from ( select owner,SUM(CAST(sharecount as INTEGER)) as sharecount from account_view GROUP BY owner) m JOIN (select * from BrokerData) t ON t.client=m.owner;

Finally, we will get a total share count and total commission charge over the buyer.

image showing output of previous query