Query the State Database

What's the State Database?

The blockchain ledger’s current state data is stored in the state database.

When you develop Oracle Blockchain Platform chaincodes, you can extract data from the state database by executing rich queries. Oracle Blockchain Platform supports rich queries by using the SQL rich query syntax and the CouchDB find expressions. See SQL Rich Query Syntax and CouchDB Rich Query Syntax.

Hyperledger Fabric doesn’t support SQL rich queries. If your Oracle Blockchain Platform network contains Hyperledger Fabric participants, then you need to make sure to do the following:

If your chaincodes contain SQL rich query syntax, then those chaincodes are installed only on member peers using Oracle Blockchain Platform.
If a chaincode needs to be installed on Oracle Blockchain Platform and Hyperledger Fabric peers, then use CouchDB syntax in the chaincodes and confirm that the Hyperledger Fabric peers are set up to use CouchDB as their state database repository. Oracle Blockchain Platform can process CouchDB.

How Does Oracle Blockchain Platform Work with Berkeley DB?

Oracle Blockchain Platform uses Oracle Berkeley DB as the state database. Oracle Blockchain Platform creates relational tables in Berkeley DB based on the SQLite extension. This architecture provides a robust and performant way to validate SQL rich queries.

For each channel chaincode, Oracle Blockchain Platform creates a Berkeley DB table. This table stores state information data, and contains at least a key column named key, and a value column named value or valueJson, depending on whether you’re using JSON format data.

Column Name	Type	Description
`key`	TEXT	Key column of the state table.
`value`	TEXT	Value column of the state table.
`valueJson`	TEXT	JSON format value column of the state table.

Note that the valueJson and value columns are mutually-exclusive. So, if the chaincode assigns a JSON value to a key, then the valueJson column will hold that value, and the value column will be set to null. If the chaincode assigns a non-JSON value to a key, then the valueJson column will be set to null, and the value column will hold the value.

Example of a State Database

These are examples of keys and their values from the Car Dealer sample’s state database:

key	value	valueJson
abg1234	null	{"docType": "vehiclePart", "serialNumber": "abg1234", "assembler": "panama-parts", "assemblyDate": 1502688979, "name": "airbag 2020", "owner": "Detroit Auto", "recall": false, "recallDate": 1502688979}
abg1235	null	{"docType": "vehiclePart", "serialNumber": "abg1235", "assembler": "panama-parts", "assemblyDate": 1502688979, "name": "airbag 4050", "owner": "Detroit Auto", "recall": false, "recallDate": 1502688979}
ser1236	null	{"docType": "vehiclePart", "serialNumber": "ser1236", "assembler": "panama-parts", "assemblyDate": 1502688979, "name": "seatbelt 10020", "owner": "Detroit Auto", "recall": false, "recallDate": 1502688979}
bra1238	null	{"docType": "vehiclePart", "serialNumber": "bra1238", "assembler": "bobs-bits", "assemblyDate": 1502688979, "name": "brakepad 4200", "owner": "Detroit Auto", "recall": false, "recallDate": 1502688979}
dtrt10001	null	{"docType": "vehicle", "chassisNumber": "dtrt10001", "manufacturer": "Detroit Auto", "model": "a coupe", "assemblyDate": 1502688979, "airbagSerialNumber": "abg1235", "owner": "Sam Dealer", "recall": false, "recallDate": 1502688979

State Database Indexes

The state database can contain a large amount of data. In such cases Oracle Blockchain Platform uses indexes to improve data access.

Default Indexes

When a chaincode is deployed, Oracle Blockchain Platform creates two indexes.

Key index — Created on the key column.
Value index — Created on the value column.

Custom Indexes

In some cases, you might need to create custom indexes. You define these indexes using any expression that can be resolved in the context of the state table. Custom indexes created against Berkeley DB rely on the SQLite syntax, but they otherwise follow the same CouchDB implementation provided by Hyperledger Fabric.

Note that you can use custom indexes to dramatically improve the performance of WHERE and ORDER BY statements on large data sets. Because using custom indexes slows down data insertions, you should use them judiciously.

Each custom index is defined as an array of expressions, which support compound indexes, expressed as a JSON document inside one file (note that there's one index per file). You must package this file with the chaincode in a folder named “indexes” in the following directory structure: statedb/relationaldb/indexes. See How to add CouchDB indexes during chaincode installation.

Example Custom Indexes

The custom index examples in this section use the Car Dealer sample.

Example 1 —This example indexes the use of the json_extract expression in the context of WHERE and ORDER BY expressions.

{"indexExpressions": ["json_extract(valueJson, '$.owner')"]}

For example:

SELECT … FROM … ORDER BY json_extract(valueJson, '$.owner')

Example 2 — This example indexes the compound use of the two json_extract expressions in the context of WHERE and ORDER BY expressions.

{"indexExpressions": ["json_extract(valueJson, '$.docType')", "json_extract(valueJson, '$.owner')"]}

For example:

SELECT … FROM … WHERE json_extract(valueJson, '$.docType') = 'vehiclePart' AND json_extract(valueJson, '$.owner') = 'Detroit Auto'

Example 3 — This example creates two indexes: the index described in Example 1 and the index described in Example 2. Note that each JSON structure needs to be included in a separate file. Each file describes a single index: a simple index like Example 1, or a compound index like Example 2.

Index 1: {"indexExpressions": ["json_extract(valueJson, '$.owner')"]}

Index 2: {"indexExpressions": ["json_extract(valueJson, '$owner')", "json_extract(valueJson, '$.docType')"]}

In the following example, Index 2 is applied to the AND expression in the WHERE portion of the query, while Index 1 is applied to the ORDER BY expression:

SELECT … FROM … WHERE json_extract(valueJson, '$.docType') = 'vehiclePart' AND json_extract(valueJson, '$.owner') = 'Detroit Auto' ORDER BY json_extract(valueJson, '$.owner')

JSON Document Format

The JSON document must be in the following format:

{"indexExpressions": [expr1, ..., exprN]}

For example:

{"indexExpressions": ["json_extract(valueJson, '$.owner')"]}

Differences in the Validation of Rich Queries

In some cases, the standard Hyperledger Fabric with CouchDB rich query and the Oracle Berkeley DB rich query behave differently.

In standard Hyperledger Fabric with CouchDB, each key and value pair returned by the query is added to the transaction's read-set and is validated at validation time and without re-executing the query. In Berkeley DB, the returned key and value pair isn’t added to the read-set, but the rich query's result is hashed in a Merkle tree and validated against the re-execution of the query at validation time.

Native Hyperledger Fabric doesn’t provide data protection for rich query. However, Berkeley DB contains functionality that protects and validates the rich query by adding the Merkle tree hash value into the read-set, re-executing the rich query, and at the validation stage re-calculating the Merkle tree value. Note that because validation is more accurate in Oracle Blockchain Platform with Berkeley DB, chaincode invocations are sometimes flagged for more frequent phantom reads.