API: Vault Query¶

Overview¶

Corda has been architected from the ground up to encourage usage of industry standard, proven query frameworks and libraries for accessing RDBMS backed transactional stores (including the Vault).

Corda provides a number of flexible query mechanisms for accessing the Vault:

• Vault Query API
• Using a JDBC session (as described in Persistence)
• Custom JPA/JPQL queries
• Custom 3rd party Data Access frameworks such as Spring Data

The majority of query requirements can be satisfied by using the Vault Query API, which is exposed via the VaultService for use directly by flows:

/**
 * Generic vault query function which takes a [QueryCriteria] object to define filters,
 * optional [PageSpecification] and optional [Sort] modification criteria (default unsorted),
 * and returns a [Vault.Page] object containing the following:
 *  1. states as a List of <StateAndRef> (page number and size defined by [PageSpecification])
 *  2. states metadata as a List of [Vault.StateMetadata] held in the Vault States table.
 *  3. total number of results available if [PageSpecification] supplied (otherwise returns -1)
 *  4. status types used in this query: UNCONSUMED, CONSUMED, ALL
 *  5. other results (aggregate functions with/without using value groups)
 *
 * @throws VaultQueryException if the query cannot be executed for any reason
 *        (missing criteria or parsing error, paging errors, unsupported query, underlying database error)
 *
 * Notes
 *   If no [PageSpecification] is provided, a maximum of [DEFAULT_PAGE_SIZE] results will be returned.
 *   API users must specify a [PageSpecification] if they are expecting more than [DEFAULT_PAGE_SIZE] results,
 *   otherwise a [VaultQueryException] will be thrown alerting to this condition.
 *   It is the responsibility of the API user to request further pages and/or specify a more suitable [PageSpecification].
 */
@Throws(VaultQueryException::class)
fun <T : ContractState> _queryBy(criteria: QueryCriteria,
                                 paging: PageSpecification,
                                 sorting: Sort,
                                 contractStateType: Class<out T>): Vault.Page<T>

/**
 * Generic vault query function which takes a [QueryCriteria] object to define filters,
 * optional [PageSpecification] and optional [Sort] modification criteria (default unsorted),
 * and returns a [Vault.PageAndUpdates] object containing
 * 1) a snapshot as a [Vault.Page] (described previously in [queryBy])
 * 2) an [Observable] of [Vault.Update]
 *
 * @throws VaultQueryException if the query cannot be executed for any reason
 *
 * Notes: the snapshot part of the query adheres to the same behaviour as the [queryBy] function.
 *        the [QueryCriteria] applies to both snapshot and deltas (streaming updates).
 */
@Throws(VaultQueryException::class)
fun <T : ContractState> _trackBy(criteria: QueryCriteria,
                                 paging: PageSpecification,
                                 sorting: Sort,
                                 contractStateType: Class<out T>): DataFeed<Vault.Page<T>, Vault.Update<T>>

And via CordaRPCOps for use by RPC client applications:

@RPCReturnsObservables
fun <T : ContractState> vaultQueryBy(criteria: QueryCriteria,
                                     paging: PageSpecification,
                                     sorting: Sort,
                                     contractStateType: Class<out T>): Vault.Page<T>

@RPCReturnsObservables
fun <T : ContractState> vaultTrackBy(criteria: QueryCriteria,
                                     paging: PageSpecification,
                                     sorting: Sort,
                                     contractStateType: Class<out T>): DataFeed<Vault.Page<T>, Vault.Update<T>>

Helper methods are also provided with default values for arguments:

fun <T : ContractState> vaultQuery(contractStateType: Class<out T>): Vault.Page<T> {
    return vaultQueryBy(QueryCriteria.VaultQueryCriteria(), PageSpecification(), Sort(emptySet()), contractStateType)
}
fun <T : ContractState> vaultQueryByCriteria(criteria: QueryCriteria, contractStateType: Class<out T>): Vault.Page<T> {
    return vaultQueryBy(criteria, PageSpecification(), Sort(emptySet()), contractStateType)
}
fun <T : ContractState> vaultQueryByWithPagingSpec(contractStateType: Class<out T>, criteria: QueryCriteria, paging: PageSpecification): Vault.Page<T> {
    return vaultQueryBy(criteria, paging, Sort(emptySet()), contractStateType)
}
fun <T : ContractState> vaultQueryByWithSorting(contractStateType: Class<out T>, criteria: QueryCriteria, sorting: Sort): Vault.Page<T> {
    return vaultQueryBy(criteria, PageSpecification(), sorting, contractStateType)
}

fun <T : ContractState> vaultTrack(contractStateType: Class<out T>): DataFeed<Vault.Page<T>, Vault.Update<T>> {
    return vaultTrackBy(QueryCriteria.VaultQueryCriteria(), PageSpecification(), Sort(emptySet()), contractStateType)
}
fun <T : ContractState> vaultTrackByCriteria(contractStateType: Class<out T>, criteria: QueryCriteria): DataFeed<Vault.Page<T>, Vault.Update<T>> {
    return vaultTrackBy(criteria, PageSpecification(), Sort(emptySet()), contractStateType)
}
fun <T : ContractState> vaultTrackByWithPagingSpec(contractStateType: Class<out T>, criteria: QueryCriteria, paging: PageSpecification): DataFeed<Vault.Page<T>, Vault.Update<T>> {
    return vaultTrackBy(criteria, paging, Sort(emptySet()), contractStateType)
}
fun <T : ContractState> vaultTrackByWithSorting(contractStateType: Class<out T>, criteria: QueryCriteria, sorting: Sort): DataFeed<Vault.Page<T>, Vault.Update<T>> {
    return vaultTrackBy(criteria, PageSpecification(), sorting, contractStateType)
}

The API provides both static (snapshot) and dynamic (snapshot with streaming updates) methods for a defined set of filter criteria:

• Use queryBy to obtain a only current snapshot of data (for a given QueryCriteria)
• Use trackBy to obtain a both a current snapshot and a future stream of updates (for a given QueryCriteria)

Simple pagination (page number and size) and sorting (directional ordering using standard or custom property attributes) is also specifiable. Defaults are defined for paging (pageNumber = 1, pageSize = 200) and sorting (direction = ASC).

The QueryCriteria interface provides a flexible mechanism for specifying different filtering criteria, including and/or composition and a rich set of operators to include:

• Binary logical (AND, OR)
• Comparison (LESS_THAN, LESS_THAN_OR_EQUAL, GREATER_THAN, GREATER_THAN_OR_EQUAL)
• Equality (EQUAL, NOT_EQUAL)
• Likeness (LIKE, NOT_LIKE)
• Nullability (IS_NULL, NOT_NULL)
• Collection based (IN, NOT_IN)
• Standard SQL-92 aggregate functions (SUM, AVG, MIN, MAX, COUNT)

There are four implementations of this interface which can be chained together to define advanced filters.

1. VaultQueryCriteria provides filterable criteria on attributes within the Vault states table: status (UNCONSUMED, CONSUMED), state reference(s), contract state type(s), notaries, soft locked states, timestamps (RECORDED, CONSUMED).

   Note

   Sensible defaults are defined for frequently used attributes (status = UNCONSUMED, always include soft locked states).

2. FungibleAssetQueryCriteria provides filterable criteria on attributes defined in the Corda Core FungibleAsset contract state interface, used to represent assets that are fungible, countable and issued by a specific party (eg. Cash.State and CommodityContract.State in the Corda finance module). Filterable attributes include: participants(s), owner(s), quantity, issuer party(s) and issuer reference(s).

   Note

   All contract states that extend the FungibleAsset now automatically persist that interfaces common state attributes to the vault_fungible_states table.

3. LinearStateQueryCriteria provides filterable criteria on attributes defined in the Corda Core LinearState and DealState contract state interfaces, used to represent entities that continuously supersede themselves, all of which share the same linearId (e.g. trade entity states such as the IRSState defined in the SIMM valuation demo). Filterable attributes include: participant(s), linearId(s), uuid(s), and externalId(s).

   Note

   All contract states that extend LinearState or DealState now automatically persist those interfaces common state attributes to the vault_linear_states table.

4. VaultCustomQueryCriteria provides the means to specify one or many arbitrary expressions on attributes defined by a custom contract state that implements its own schema as described in the Persistence documentation and associated examples. Custom criteria expressions are expressed using one of several type-safe CriteriaExpression: BinaryLogical, Not, ColumnPredicateExpression, AggregateFunctionExpression. The ColumnPredicateExpression allows for specification arbitrary criteria using the previously enumerated operator types. The AggregateFunctionExpression allows for the specification of an aggregate function type (sum, avg, max, min, count) with optional grouping and sorting. Furthermore, a rich DSL is provided to enable simple construction of custom criteria using any combination of ColumnPredicate. See the Builder object in QueryCriteriaUtils for a complete specification of the DSL.

   Note

   Custom contract schemas are automatically registered upon node startup for CorDapps. Please refer to Persistence for mechanisms of registering custom schemas for different testing purposes.

All QueryCriteria implementations are composable using and and or operators.

All QueryCriteria implementations provide an explicitly specifiable set of common attributes:

1. State status attribute (Vault.StateStatus), which defaults to filtering on UNCONSUMED states. When chaining several criterias using AND / OR, the last value of this attribute will override any previous
2. Contract state types (<Set<Class<out ContractState>>), which will contain at minimum one type (by default this will be ContractState which resolves to all state types). When chaining several criteria using and and or operators, all specified contract state types are combined into a single set

An example of a custom query is illustrated here:

val generalCriteria = VaultQueryCriteria(Vault.StateStatus.ALL)

val results = builder {
    val currencyIndex = PersistentCashState::currency.equal(USD.currencyCode)
    val quantityIndex = PersistentCashState::pennies.greaterThanOrEqual(10L)

    val customCriteria1 = VaultCustomQueryCriteria(currencyIndex)
    val customCriteria2 = VaultCustomQueryCriteria(quantityIndex)

    val criteria = generalCriteria.and(customCriteria1.and(customCriteria2))
    vaultService.queryBy<Cash.State>(criteria)
}

Examples of these QueryCriteria objects are presented below for Kotlin and Java.

The Vault Query API leverages the rich semantics of the underlying JPA Hibernate based Persistence framework adopted by Corda.

An example of a custom query in Java is illustrated here:

QueryCriteria generalCriteria = new VaultQueryCriteria(Vault.StateStatus.ALL);

Field attributeCurrency = CashSchemaV1.PersistentCashState.class.getDeclaredField("currency");
Field attributeQuantity = CashSchemaV1.PersistentCashState.class.getDeclaredField("pennies");

CriteriaExpression currencyIndex = Builder.equal(attributeCurrency, "USD");
CriteriaExpression quantityIndex = Builder.greaterThanOrEqual(attributeQuantity, 10L);

QueryCriteria customCriteria2 = new VaultCustomQueryCriteria(quantityIndex);
QueryCriteria customCriteria1 = new VaultCustomQueryCriteria(currencyIndex);


QueryCriteria criteria = generalCriteria.and(customCriteria1).and(customCriteria2);
Vault.Page<ContractState> results = vaultService.queryBy(Cash.State.class, criteria);

Pagination¶

The API provides support for paging where large numbers of results are expected (by default, a page size is set to 200 results). Defining a sensible default page size enables efficient checkpointing within flows, and frees the developer from worrying about pagination where result sets are expected to be constrained to 200 or fewer entries. Where large result sets are expected (such as using the RPC API for reporting and/or UI display), it is strongly recommended to define a PageSpecification to correctly process results with efficient memory utilisation. A fail-fast mode is in place to alert API users to the need for pagination where a single query returns more than 200 results and no PageSpecification has been supplied.

Example usage¶

val result = vaultService.queryBy<ContractState>()

/**
 * Query result returns a [Vault.Page] which contains:
 *  1) actual states as a list of [StateAndRef]
 *  2) state reference and associated vault metadata as a list of [Vault.StateMetadata]
 *  3) [PageSpecification] used to delimit the size of items returned in the result set (defaults to [DEFAULT_PAGE_SIZE])
 *  4) Total number of items available (to aid further pagination if required)
 */
val states = result.states
val metadata = result.statesMetadata

val sortAttribute = SortAttribute.Standard(Sort.CommonStateAttribute.STATE_REF_TXN_ID)
val criteria = VaultQueryCriteria(stateRefs = listOf(stateRefs.first(), stateRefs.last()))
val results = vaultService.queryBy<DummyLinearContract.State>(criteria, Sort(setOf(Sort.SortColumn(sortAttribute, Sort.Direction.ASC))))

val criteria = VaultQueryCriteria(contractStateTypes = setOf(Cash.State::class.java, DealState::class.java))
val results = vaultService.queryBy<ContractState>(criteria)

val criteria = VaultQueryCriteria(notary = listOf(CASH_NOTARY))
val results = vaultService.queryBy<ContractState>(criteria)

val criteria = LinearStateQueryCriteria(participants = listOf(BIG_CORP, MINI_CORP))
val results = vaultService.queryBy<ContractState>(criteria)

val start = TODAY
val end = TODAY.plus(30, ChronoUnit.DAYS)
val recordedBetweenExpression = TimeCondition(
        QueryCriteria.TimeInstantType.RECORDED,
        ColumnPredicate.Between(start, end))
val criteria = VaultQueryCriteria(timeCondition = recordedBetweenExpression)
val results = vaultService.queryBy<ContractState>(criteria)

val pagingSpec = PageSpecification(DEFAULT_PAGE_NUM, 10)
val criteria = VaultQueryCriteria(status = Vault.StateStatus.ALL)
val results = vaultService.queryBy<ContractState>(criteria, paging = pagingSpec)

var pageNumber = DEFAULT_PAGE_NUM
val states = mutableListOf<StateAndRef<ScheduledState>>()
do {
    val pageSpec = PageSpecification(pageSize = PAGE_SIZE, pageNumber = pageNumber)
    val results = vaultService.queryBy<ScheduledState>(VaultQueryCriteria(), pageSpec, SORTING)
    states.addAll(results.states)
    pageNumber++
} while ((pageSpec.pageSize * (pageNumber)) <= results.totalStatesAvailable)

val linearIds = issuedStates.states.map { it.state.data.linearId }.toList()
val criteria = LinearStateQueryCriteria(linearId = listOf(linearIds.first(), linearIds.last()))
val results = vaultService.queryBy<LinearState>(criteria)

val linearStateCriteria = LinearStateQueryCriteria(linearId = listOf(linearId), status = Vault.StateStatus.ALL)
val vaultCriteria = VaultQueryCriteria(status = Vault.StateStatus.ALL)
val results = vaultService.queryBy<LinearState>(linearStateCriteria and vaultCriteria)

val criteria = LinearStateQueryCriteria(externalId = listOf("456", "789"))
val results = vaultService.queryBy<DealState>(criteria)

val criteria = LinearStateQueryCriteria(participants = parties)
val results = vaultService.queryBy<DealState>(criteria)

val ccyIndex = builder { CashSchemaV1.PersistentCashState::currency.equal(USD.currencyCode) }
val criteria = VaultCustomQueryCriteria(ccyIndex)
val results = vaultService.queryBy<FungibleAsset<*>>(criteria)

val fungibleAssetCriteria = FungibleAssetQueryCriteria(quantity = builder { greaterThan(2500L) })
val results = vaultService.queryBy<Cash.State>(fungibleAssetCriteria)

val criteria = FungibleAssetQueryCriteria(issuer = listOf(BOC))
val results = vaultService.queryBy<FungibleAsset<*>>(criteria)

val sum = builder { CashSchemaV1.PersistentCashState::pennies.sum() }
val sumCriteria = VaultCustomQueryCriteria(sum)

val count = builder { CashSchemaV1.PersistentCashState::pennies.count() }
val countCriteria = VaultCustomQueryCriteria(count)

val max = builder { CashSchemaV1.PersistentCashState::pennies.max() }
val maxCriteria = VaultCustomQueryCriteria(max)

val min = builder { CashSchemaV1.PersistentCashState::pennies.min() }
val minCriteria = VaultCustomQueryCriteria(min)

val avg = builder { CashSchemaV1.PersistentCashState::pennies.avg() }
val avgCriteria = VaultCustomQueryCriteria(avg)

val results = vaultService.queryBy<FungibleAsset<*>>(sumCriteria
        .and(countCriteria)
        .and(maxCriteria)
        .and(minCriteria)
        .and(avgCriteria))

val sum = builder { CashSchemaV1.PersistentCashState::pennies.sum(groupByColumns = listOf(CashSchemaV1.PersistentCashState::currency)) }
val sumCriteria = VaultCustomQueryCriteria(sum)

val max = builder { CashSchemaV1.PersistentCashState::pennies.max(groupByColumns = listOf(CashSchemaV1.PersistentCashState::currency)) }
val maxCriteria = VaultCustomQueryCriteria(max)

val min = builder { CashSchemaV1.PersistentCashState::pennies.min(groupByColumns = listOf(CashSchemaV1.PersistentCashState::currency)) }
val minCriteria = VaultCustomQueryCriteria(min)

val avg = builder { CashSchemaV1.PersistentCashState::pennies.avg(groupByColumns = listOf(CashSchemaV1.PersistentCashState::currency)) }
val avgCriteria = VaultCustomQueryCriteria(avg)

val results = vaultService.queryBy<FungibleAsset<*>>(sumCriteria
        .and(maxCriteria)
        .and(minCriteria)
        .and(avgCriteria))

val sum = builder {
    CashSchemaV1.PersistentCashState::pennies.sum(groupByColumns = listOf(CashSchemaV1.PersistentCashState::issuerParty,
            CashSchemaV1.PersistentCashState::currency),
            orderBy = Sort.Direction.DESC)
}

val results = vaultService.queryBy<FungibleAsset<*>>(VaultCustomQueryCriteria(sum))

vaultService.trackBy<Cash.State>().updates     // UNCONSUMED default

val (snapshot, updates) = vaultService.trackBy<LinearState>()

val (snapshot, updates) = vaultService.trackBy<DealState>()

Vault.Page<LinearState> results = vaultService.queryBy(LinearState.class);

VaultQueryCriteria criteria = new VaultQueryCriteria(Vault.StateStatus.CONSUMED);
Vault.Page<Cash.State> results = vaultService.queryBy(Cash.State.class, criteria);

Vault.StateStatus status = Vault.StateStatus.CONSUMED;
@SuppressWarnings("unchecked")
Set<Class<LinearState>> contractStateTypes = new HashSet(Collections.singletonList(LinearState.class));

QueryCriteria vaultCriteria = new VaultQueryCriteria(status, contractStateTypes);

List<UniqueIdentifier> linearIds = Collections.singletonList(ids.getSecond());
QueryCriteria linearCriteriaAll = new LinearStateQueryCriteria(null, linearIds, Vault.StateStatus.UNCONSUMED, null);
QueryCriteria dealCriteriaAll = new LinearStateQueryCriteria(null, null, dealIds);

QueryCriteria compositeCriteria1 = dealCriteriaAll.or(linearCriteriaAll);
QueryCriteria compositeCriteria2 = compositeCriteria1.and(vaultCriteria);

PageSpecification pageSpec = new PageSpecification(DEFAULT_PAGE_NUM, MAX_PAGE_SIZE);
Sort.SortColumn sortByUid = new Sort.SortColumn(new SortAttribute.Standard(Sort.LinearStateAttribute.UUID), Sort.Direction.DESC);
Sort sorting = new Sort(ImmutableSet.of(sortByUid));
Vault.Page<LinearState> results = vaultService.queryBy(LinearState.class, compositeCriteria2, pageSpec, sorting);

Field pennies = CashSchemaV1.PersistentCashState.class.getDeclaredField("pennies");

QueryCriteria sumCriteria = new VaultCustomQueryCriteria(Builder.sum(pennies));
QueryCriteria countCriteria = new VaultCustomQueryCriteria(Builder.count(pennies));
QueryCriteria maxCriteria = new VaultCustomQueryCriteria(Builder.max(pennies));
QueryCriteria minCriteria = new VaultCustomQueryCriteria(Builder.min(pennies));
QueryCriteria avgCriteria = new VaultCustomQueryCriteria(Builder.avg(pennies));

QueryCriteria criteria = sumCriteria.and(countCriteria).and(maxCriteria).and(minCriteria).and(avgCriteria);
Vault.Page<Cash.State> results = vaultService.queryBy(Cash.State.class, criteria);

Field pennies = CashSchemaV1.PersistentCashState.class.getDeclaredField("pennies");
Field currency = CashSchemaV1.PersistentCashState.class.getDeclaredField("currency");

QueryCriteria sumCriteria = new VaultCustomQueryCriteria(Builder.sum(pennies, Collections.singletonList(currency)));
QueryCriteria countCriteria = new VaultCustomQueryCriteria(Builder.count(pennies));
QueryCriteria maxCriteria = new VaultCustomQueryCriteria(Builder.max(pennies, Collections.singletonList(currency)));
QueryCriteria minCriteria = new VaultCustomQueryCriteria(Builder.min(pennies, Collections.singletonList(currency)));
QueryCriteria avgCriteria = new VaultCustomQueryCriteria(Builder.avg(pennies, Collections.singletonList(currency)));

QueryCriteria criteria = sumCriteria.and(countCriteria).and(maxCriteria).and(minCriteria).and(avgCriteria);
Vault.Page<Cash.State> results = vaultService.queryBy(Cash.State.class, criteria);

Field pennies = CashSchemaV1.PersistentCashState.class.getDeclaredField("pennies");
Field currency = CashSchemaV1.PersistentCashState.class.getDeclaredField("currency");
Field issuerParty = CashSchemaV1.PersistentCashState.class.getDeclaredField("issuerParty");

QueryCriteria sumCriteria = new VaultCustomQueryCriteria(Builder.sum(pennies, Arrays.asList(issuerParty, currency), Sort.Direction.DESC));

Vault.Page<Cash.State> results = vaultService.queryBy(Cash.State.class, sumCriteria);

@SuppressWarnings("unchecked")
Set<Class<ContractState>> contractStateTypes = new HashSet(Collections.singletonList(Cash.State.class));

VaultQueryCriteria criteria = new VaultQueryCriteria(Vault.StateStatus.UNCONSUMED, contractStateTypes);
DataFeed<Vault.Page<ContractState>, Vault.Update<ContractState>> results = vaultService.trackBy(ContractState.class, criteria);

Vault.Page<ContractState> snapshot = results.getSnapshot();
Observable<Vault.Update<ContractState>> updates = results.getUpdates();

@SuppressWarnings("unchecked")
Set<Class<ContractState>> contractStateTypes = new HashSet(Collections.singletonList(Cash.State.class));

VaultQueryCriteria criteria = new VaultQueryCriteria(Vault.StateStatus.UNCONSUMED, contractStateTypes);
DataFeed<Vault.Page<ContractState>, Vault.Update<ContractState>> results = vaultService.trackBy(ContractState.class, criteria);

Vault.Page<ContractState> snapshot = results.getSnapshot();
Observable<Vault.Update<ContractState>> updates = results.getUpdates();

Troubleshooting¶

If the results your were expecting do not match actual returned query results we recommend you add an entry to your log4j2.xml configuration file to enable display of executed SQL statements:

<Logger name="org.hibernate.SQL" level="debug" additivity="false">

    <AppenderRef ref="Console-Appender"/>

</Logger>

Behavioural notes¶

1. TrackBy updates do not take into account the full criteria specification due to different and more restrictive syntax in observables filtering (vs full SQL-92 JDBC filtering as used in snapshot views). Specifically, dynamic updates are filtered by contractStateType and stateType (UNCONSUMED, CONSUMED, ALL) only
2. QueryBy and TrackBy snapshot views using pagination may return different result sets as each paging request is a separate SQL query on the underlying database, and it is entirely conceivable that state modifications are taking place in between and/or in parallel to paging requests. When using pagination, always check the value of the totalStatesAvailable (from the Vault.Page result) and adjust further paging requests appropriately.

Other use case scenarios¶

For advanced use cases that require sophisticated pagination, sorting, grouping, and aggregation functions, it is recommended that the CorDapp developer utilise one of the many proven frameworks that ship with this capability out of the box. Namely, implementations of JPQL (JPA Query Language) such as Hibernate for advanced SQL access, and Spring Data for advanced pagination and ordering constructs.

The Corda Tutorials provide examples satisfying these additional Use Cases:

1. Example CorDapp service using Vault API Custom Query to access attributes of IOU State
2. Example CorDapp service query extension executing Named Queries via JPQL
3. Advanced pagination queries using Spring Data JPA