1. Using Oracle Blockchain Platform
  2. Work With Databases
  3. Query the State Database

Query the State Database

This topic contains information to help you understand how to query the state database where the blockchain ledger’s current state data is stored.

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

Rich Queries in the Console

Administrators can run and analyze rich queries from the console.

  1. Go to the console and select the Channels tab.
  2. In the channels table, locate the channel where you want to run a query, click the channels More Actions button, and then click Analyze Rich Queries. The Analyze Rich Queries dialog box is displayed.
  3. To run a rich query against the state database, select Query Execution.
    1. For Chaincode, select the chaincode that is deployed to the channel that you want to query.
    2. For Peer, select the peer to query.
      Only peers in the current organization that are running the selected chaincode are available.
    3. For Rich Query, enter the rich query to run and analyze.
      The query format must follow the rich query syntax. For more information about rich query syntax, see Supported Rich Query Syntax.
    4. For Result Rows Limit, move the slider to the maximum number of result rows to fetch. You can fetch up to 50 rows of results.
  4. To get the execution plan for a query, select Query Plan Explain. A query execution plan is the sequence of operations that was performed to run the query.
    1. For Chaincode, select the chaincode that is deployed to the channel that you want to query.
    2. For Peer, select the peer to query.
    3. For Collection, select the state database or private data collection.
    4. For Rich Query, enter the rich query.
      The explain keyword is not needed for this query.
      For example: select * from <state>
  5. Click Execute. The Results field shows the query result table or the execution plan. To export the results table as a .csv file, click Export.
    The results table size is limited to 1 MB. You might need to refine your query to avoid exceeding this limit.

Supported Rich Query Syntax

Oracle Blockchain Platform supports two types of rich query syntax that you can use to query the state database: SQL rich query and CouchDB rich query.

SQL Rich Query Syntax

The Berkeley DB JSON extensions are in the form of SQL functions.

Before You Begin

Note the following information:

  • You can only access the channel chaincode (<STATE>) that you’re executing your query from.
  • Only the SELECT statement is supported.
  • You can’t modify the state database table.
  • A rich query expression can have only one SELECT statement.
  • The examples in this topic are just a few ways that you can write your rich query. You've access to the usual full SQL syntax to query a SQL database.
  • You've access to the JSON1 Extension (SQLite extension). See JSON1 Extension and SQL As Understood by SQLite.

If you need more information about writing and testing chaincodes, see Develop Chaincodes.

How to Refer to the State Database in Queries

The state database table name is internally managed by Oracle Blockchain Platform, so you don't need to know the state database’s physical name when you write a chaincode.

Instead, you must use the <STATE> alias to refer to the table name. For example: select key, value from <STATE>.

Note that the <STATE> alias is not case-sensitive, so you can use either <state>, <STATE>, or something like <StAtE>.

Retrieve All Keys

Use this syntax:

SELECT key FROM <STATE>

For example, if you use this syntax to query the Car Dealer sample, then you’ll get the following list of keys:

key

abg1234

abg1235

ser1236

bra1238

dtrt10001

Retrieve All Keys and Values Ordered Alphabetically by Key

Use this syntax:

SELECT key AS serialNumber, valueJson AS details FROM  <state> ORDER BY key

For example, if you use this syntax to query the Car Dealer sample, then you’ll get the following results:

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

Retrieve All Keys and Values Starting with “abg”

Use this syntax:

SELECT key AS serialNumber, valueJson AS details FROM <state> WHERE key LIKE 'abg%'SELECT key, value FROM <STATE>

For example, if you use this syntax to query the Car Dealer sample, then you’ll get the following results:

serialNumber details
abg1234 {"docType": "vehiclePart", "serialNumber": "abg1234", "assembler": "panama-parts", "assemblyDate": "1502688979", "name": "airbag 2020", "owner": "Detroit Auto", "recall": "false", "recallDate": "1502688979"}
abg1235 {"docType": "vehiclePart", "serialNumber": "abg1235", "assembler": "panama-parts", "assemblyDate": "1502688979", "name": "airbag 4050", "owner": "Detroit Auto", "recall": "false", "recallDate": "1502688979"}

Retrieve All Keys with Values Containing a Vehicle Part Owned by "Detroit Auto"

Use this syntax:

SELECT key FROM <state> WHERE json_extract(valueJson, '$.docType') = 'vehiclePart' AND json_extract(valueJson, '$.owner') = 'Detroit Auto'

For example, if you use this syntax to query the Car Dealer sample, then you’ll get the following list of keys:

key

abg1234

abg1235

ser1236

bra1238

Retrieve Model and Manufacturer for all Cars Owned by "Sam Dealer"

Use this syntax:

SELECT json_extract(valueJson, '$.model') AS model, json_extract(valueJson, '$.manufacturer') AS manufacturer FROM <state> WHERE json_extract(valueJson, '$.docType') = 'vehicle' AND json_extract(valueJson, '$.owner') = 'Sam Dealer'

For example, if you use this syntax to query the Car Dealer sample, then you’ll get the following results:

model manufacturer
a coupe Detroit Auto

If the state value is JSON array, you may use this syntax to retrieve model and manufacturer for all cars owned by "Sam Dealer":

SELECT json_extract(j.value, '$.model') AS model, json_extract(j.value, '$.manufacturer') AS manufacturer FROM <state> s, json_each(json_extract(s.valueJson,'$')) j WHERE json_valid(j.value) AND json_extract(j.value, '$.owner') = 'Sam Dealer'

CouchDB Rich Query Syntax

Use the information in this topic if you’re migrating your chaincodes containing CouchDB syntax to Oracle Blockchain Platform, or if you need to write chaincodes to install on Hyperledger Fabric peers participating in an Oracle Blockchain Platform network.

If you’re writing a new chaincode, then Oracle recommends that you use SQL rich queries to take advantage of the performance benefits that Oracle Blockchain Platform with Berkeley DB provides.

If you need more information about writing and testing chaincodes, see Develop Chaincodes.

Unsupported Query Parameters and Selector Syntax

Oracle Blockchain Platform doesn’t support the use_index parameter. If used, Oracle Blockchain Platform ignores this parameter, and it will automatically pick the indexes defined on the StateDB in question.

Parameter Type Description
use_index json Instructs a query to use a specific index.

Retrieve All Models, Manufacturers, and Owners of Cars, and Order Them by Owner

Use this expression:

{ 
  "fields": ["model", "manufacturer", "owner"], 
  "sort": [   
    "owner" 
   ]
}

Retrieve Model and Manufacturer for All Cars Owned by “Sam Dealer”

Use this expression:

{ 
  "fields": ["model", "manufacturer"], 
  "selector": {   
    "docType"  : "vehicle",
     "owner" : "Sam Dealer" 
  }
}

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.