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 |
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.