Oracle Database View Definitions for Wholesale CBDC

You can use the rich history database to retrieve account transaction history and resolve incorrect balances in multiple transactions that occur in the same block.

You can use the GetAccountTransactionHistoryWithFiltersFromRichHistDB, getOrgAccountTransactionHistoryWithFiltersFromRichHistDB, and getAllAccountTransactionHistoryWithFiltersFromRichHistDB APIs to fetch account transaction history from the rich history database. When you pass the custom_endpoint and bearer_token parameters to the method, the account transaction history is retrieved from the rich history database or the state database. There are multiple custom endpoints, as shown in the following list. For more information, see Create Rich History Database Views.

Accounts: https://<base_URL>ords/ALIAS_NAME/BASE_PATH/PATTERN; Available in generic mode and confidential mode.
OrgAccounts: https://<base_URL>ords/ALIAS_NAME/BASE_PATH/PATTERNOrgAccount; Available in generic mode and confidential mode.
AllAccounts: https://<base_URL>ords/ALIAS_NAME/BASE_PATH/PATTERNAllAccounts; Available in generic mode. In confidential mode, available to system owners only.

To retrieve transaction history from the rich history database, you must be running Oracle Autonomous Database with Oracle REST Data Services (ORDS) and OAuth enabled.

Enable and configure the rich history database.
For more information, see Enable and Configure the Rich History Database in Using Oracle Blockchain Platform.
Enable rich history on the channels that contain the chaincode data that you want to write to the rich history database. For more information, see Configure the Channels that Write Data to the Rich History Database in Using Oracle Blockchain Platform.
Download and install Node.js version 18 or later.
On the Digital Assets page in Oracle Blockchain Platform, select Wholesale CBDC Application.
Click Download the Database View Definitions package.
Extract the WholesaleCBDCViewsPackage.zip file.
Navigate to the ORDSscript folder and install the required dependencies by running the following command.
```
npm install
```

Edit the .env file that is supplied with the script to configure it for your environment.

Oracle REST Data Services endpoints use the following general format.

<base_URL>/<user_name>/<resource_link>

Environment / Configuration Type	Environment / Configuration Variables	Description	Example
`DB Connection`	`CONNECTION_STRING`	The connection string for the database.	`CONNECTION_STRING="(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522) (host=adg.ap-sydney-1.example.com))(connect_data= (service_name=g536390e55ee33f4_db_high.adg.example.com))(security=(ssl_server_dn_match=yes)))"`
`View Configuration`	`CHAINCODE_NAME`	The name of the chaincode to fetch transaction details from in the rich history database.	`CHAINCODE_NAME="BondMarketplace"`
`View Configuration`	`INSTANCE_NAME`	The name of the instance where the chaincode is deployed.	`INSTANCE_NAME="BondMarketplace"`
`View Configuration`	`CHANNEL_NAME`	The name of the channel where the chaincode is deployed.	`CHANNEL_NAME="default"`
`ORDS Endpoint Setup`	`MODULE_NAME`	The name of the ORDS module to use. This can be any value that does not conflict with existing assets in the database.	`MODULE_NAME="demotest"`
`ORDS Endpoint Setup`	`BASE_PATH`	The base path of the ORDS URL. This can be any value that does not conflict with existing assets in the database.	`BASE_PATH="demotest"`
`ORDS Endpoint Setup`	`PATTERN`	The pattern name of the ORDS URL. This can be any value that does not conflict with existing assets in the database.	`PATTERN="accountTransactionDetails"`
`ORDS REST Endpoint`	`ORDS_REST_BASE_URL`	The base URL of the ORDS REST endpoint of the database.	`ORDS_REST_BASE_URL="https://g536390e55ee33f4_db_high.adg.ap-sydney-1.example.com"`
`Alias Configuration`	`ALIAS_NAME`	The alias to use in place of a user name in the REST endpoint URL. This can be any value that does not conflict with existing assets in the database.	`ALIAS_NAME="demotestAlias"`
`ORDS Role`	`ROLE_NAME`	The ORDS role that is assigned to the user. This can be any value that does not conflict with existing assets in the database.	`ROLE_NAME="demotest_role"`
`ORDS Privilege`	`PRIVILEGE_NAME`	The ORDS privilege that is assigned to the user. This can be any value that does not conflict with existing assets in the database.	`PRIVILEGE_NAME="demotest_priv"`
`ORDS Privilege`	`LABEL`	A label for the ORDS privilege. This can be any value that does not conflict with existing assets in the database.	`LABEL="demotest_label"`
`ORDS Privilege`	`DESCRIPTION`	A description of the ORDS privilege. This can be any value that does not conflict with existing assets in the database.	`DESCRIPTION="demotest_description"`
`OAuth Configuration`	`CLIENT_NAME`	The client name to use for OAuth authentication with the ORDS REST endpoint. This can be any value that does not conflict with existing assets in the database.	`CLIENT_NAME="demotest_client"`
`OAuth Configuration`	`OWNER`	The owner name to use for OAuth authentication with the ORDS REST endpoint. This can be any value that does not conflict with existing assets in the database.	`OWNER="demotest"`
`OAuth Configuration`	`DESCRIPTION`	A description of the OAuth configuration. This can be any value that does not conflict with existing assets in the database.	`DESCRIPTION="demotest_description"`
`OAuth Configuration`	`SUPPORT_EMAIL`	The support email address for the OAuth configuration. This can be any value that does not conflict with existing assets in the database.	`SUPPORT_EMAIL="test@example.com"`

Analytics and other view names are included in the software code. The accounts view is ACCOUNTS_VIEW. The transaction private data collection view is TRANSACTION_PDC_VIEW. The account transaction view is ACCOUNT_TRANSACTION_VIEW.

System owners (central banks) only: edit the Participant_orgs_list.yml file to create database links between the central bank and each participant bank. Do not edit or delete this file for participant bank nodes. If you select confidential mode when the script runs, you are prompted for the database link user name and password for each participant organization listed in the Participant_orgs_list.yml file. Unlike database user names, database link user names are case-sensitive.
The Participant_orgs_list.yml file is a YAML array that contains configuration details used to create database links between the central bank and each participant bank, in the following format.

orgName

The instance name of the participant organization.

hostName

The host name or IP address of the target database server.

port

The network port for the database service.

serviceName

The database service name or system identifier (SID).
The following text is an example Participant_orgs_list.yml file.
```
- orgName: "Participating_Org1"
  hostName: "dbserver.example.com"
  port: 1521
  serviceName: "ORCLPDB1"
- orgName: "Participating_Org2"
  hostName: "dbserver2.example.com"
  port: 1522
  serviceName: "ORCLPDB2"
```
Run the ORDS script by using the following command.
```
npm run start
```
In confidential case user will prompted with select your organization type. If user opted System owners, then Participant_orgs_list.yml file comes in the action. and please do mention for confidential case, all the participant bank need to complete their ORDS setup before System Owner, else it will result in failure.

When the command runs, the following prompts are displayed.

Enter your DB User Name

Enter the user name for the rich history database. The account must have the necessary permissions to create views and ORDS endpoints.

Enter your DB Password

Enter the password for the rich history database.

Please select a chaincode type

Select CONFIDENTIAL for chaincodes that use confidential mode. Otherwise, select WCBDC for chaincodes that use generic mode. Use the arrow keys to navigate and then press the Enter key to confirm your selection. If you select confidential mode, you are prompted to select your organization type: SYSTEM OWNER (which represents the central bank) or PARTICIPATING ORG (which represents participating banks).

Note:
In confidential mode, all participating organizations must run this script and complete ORDS setup before the system owner. If you run the script for the system owner first, it will fail.

Do you want to create the View and ORDS Endpoint? (y/n)

Enter y to create the view and endpoint. Enter n if you have already created the view and endpoint.

Please select the language of your chaincode? (TS/GO)

If you entered y previously, enter TS for TypeScript or GO for Go.

Do you want to generate ORDS Endpoint URL and Bearer Token? (y/n)

Enter y to generate the endpoint credentials. Otherwise, enter n.

If you delete the Participant_orgs_list.yml file by mistake, run the following command to regenerate the file.

npm run refresh

If a view or database link already exists in the system, the script does not recreate it. Instead, a message similar to the following text is logged:

createView already exists

The script continues with the next step. This ensures that views, tables, database links, and other database entities are not deleted or overwritten by the script.

Additionally, you might see the following error.

Error: ORA-20049: Cannot alter the URL mapping while the schema is enabled. Try disabling the schema first.

This error occurs because the database schema is enabled and thus cannot be mapped to the different alias that is specified in the .env file.

To work around this behavior, complete the following steps.

Use the same alias name that was used previously, or check the REST services in the database for the schema alias.
Disable the database schema and run the script again. For more information, see Oracle REST Data Services (ORDS) : Using SQL Developer.