SDB Mid Tier Routing Demo App
Sharded Database (SDB) Mid-Tier Routing App is a demo app which customers can deploy to understand the benefits of using SDB Mid-Tier routing services for their application.
The demo app, an Invoice application which uses or wants to use Oracle sharded database, allows its users to view the vendor invoices for a customer. Invoice application uses mid-tier routing services to define and route user requests on different swimlanes for its application stack instances.
- Client Side UI component (Invoice App frontend)
- Oracle SDB Mid-Tier routing component (Mid-Tier routing service)
- Server side component (Invoice App backend)
- Sharded database setup and schema definitions for sharded tables (Customer & Invoice sharded tables)
The demo app architecture is based on the swim lane option 1 architecture. Please refer to Sharded Database Mid-Tier Routing for more details for the basics of swim lane architectures.
The architecture diagram shows the control flow of the Invoice demo app. User requests from various sources or form factors including mobile, desktop or a service makes the invoice app request along with credentials for authentication to a load balancer (not part of the demo setup. One can choose to add a load balancer like nginx or haproxy for load balancing user requests across the web server pool). The load balancer then forwards the request to a web sever which hosts the Invoice app UI. The Invoice app UI first authenticates the request and then upon successful authentication, communicates with Oracle SDB Mid-Tier routing component to retrieve the swim lane details by passing the shard Key as part of the request. Once the swimLane details are fetched, the app UI routes the request to the appropriate pre-defined swimlane. The swimlane chosen has the application stack including the shard that contains the data, that the user requested for. The benefits of using Oracle Mid-Tier routing with Oracle shared database is described in detail in this article : https://github.com/oracle/db-sharding/wiki#advantages-of-sdb-mid-tier-routing
The installation & setup deals with the following steps in that order. It is imperative to check/test the success of each step to ensure that the Invoice app demo works correctly.
- Installation & setup of Oracle sharded database and schema for Invoice App Demo.
- Installation & setup of Oracle SDB Mid-Tier routing component.
- Installation & setup of Invoice App Backend.
- Installation & setup of Invoice App Frontend.
-
Download & Run Oracle docker image using the following steps or use your existing Oracle database and skip to step https://github.com/oracle/docker-images/tree/master/OracleDatabase
-
Use the cook book for add shard or create shard depending upon whether you already have a Oracle database setup or install the Oracle database from scratch.
-
Add shard cook book : https://www.oracle.com/technetwork/database/availability/sharding-adg-addshard-cookbook-3610618.pdf
OR
-
Create shard cook book : https://www.oracle.com/technetwork/database/availability/sharding-adg-createshard-cookbook-3610619.pdf
In either case, for the Mid-Tier routing demo, we need to setup atleast 4 shards.
Note : Follow the steps until Step 5 in Lab1 in either cookbooks. Go to the LabLaunch GDSCTL and create the shard catalog using the following parameters.
Create shard catalog with following parameters
GDSCTL> create shardcatalog -database shard0:1521:shardcat -chunks 4 -user mygdsadmin/passwd_mygdsadmin -sdb
cust_sdb -repl DG -sharding composite -region region1 -protectmode maxprotection -agent_port 8080 -
agent_password welcome
Note : Define two shardspaces
Add shardpaces
GDSCTL> add shardspace -shardspace shd1
GDSCTL> add shardspace -shardspace shd2
Note : Follow the steps until Step 5 in Lab1 in either cookbooks. Go to the LabLaunch GDSCTL and to add / define the shardgroups using the following parameters.
To add the two shard groups use the following GDSCTL commands
GDSCTL> add shardgroup -shardgroup shgrp1 -shardspace shd1 -deploy_as primary -region region1
GDSCTL> add shardgroup -shardgroup shgrp2 -shardspace shd2 -deploy_as primary -region region1
Note : If you are following Add Shard CookBook, use the following parameters for GDSTCL commands in Step 7 of Lab 2, add the four shards.
Add 4 shards
GDSCTL> add invitednode shard1
GDSCTL> add shard -shardgroup shgrp1 -connect shard1:1521/sh1 -pwd <GSMUSER_password>
GDSCTL> add invitednode shard2
GDSCTL> add shard -shardgroup shgrp1 -connect shard2:1521/sh2 -pwd <GSMUSER_password>
GDSCTL> add invitednode shard3
GDSCTL> add shard -shardgroup shgrp2 -connect shard3:1521/sh3 -pwd <GSMUSER_password>
GDSCTL> add invitednode shard4
GDSCTL> add shard -shardgroup shgrp2 -connect shard4:1521/sh4 -pwd <GSMUSER_password>
Note : If you are following Create Shard CookBook, use the following parameters for GDSTCL commands in Step 8 of Lab 2, to create the four shards.
GDSCTL> add invitednode shard1
GDSCTL> create shard -shardgroup shgrp1 -destination shard1 -credential oracle_cred
GDSCTL> add invitednode shard2
GDSCTL> create shard -shardgroup shgrp1 -destination shard2 -credential oracle_cred
GDSCTL> add invitednode shard3
GDSCTL> create shard -shardgroup shgrp2 -destination shard3 -credential oracle_cred
GDSCTL> add invitednode shard4
GDSCTL> create shard -shardgroup shgrp2 -destination shard4 -credential oracle_cred
-
Download & Run the following schema file that contains schema definitions for Invoice App to work using sqlplus or sqldeveloper as sys
Invoice App Schema : https://github.com/oracle/db-sharding/blob/master/Mid-Tier-Routing/sdb-mid-tier-routing-demo-app-backend/sql/shardTables.sql
-
You have now setup the sharded database tables with composite sharding.
For installation and setup of Oracle SDB Mid-Tier routing component, please refer to SDB Mid Tier Routing Services wiki page page.
Note : Once the SDB Mid-Tier routing component install & setup is done, please execute all the mid-tier routing demo APIs in the collection https://documenter.getpostman.com/view/754319/RztiuWQR , to ensure that the swim lanes are defined and to verify that the shard catalog is setup properly.
Invoice app backend is a node.js app. So install the latest npm and node.js on the target machine, if you haven't already.
There are 4 instances of the Invoice app backend created for the purpose of demonstrating application stack swimlanes.
Please follow the installation & setup instructions provided in the README .
Start all the 4 backends by running the following commands.
SDB MTR demo app backend
npm start
OR
npm restart
Note : Once the backend setup is done, please execute the backend demo APIs in the collection https://documenter.getpostman.com/view/754319/RztsnRCC , to verify that the backends are setup properly and are able to connect to the shards.
Invoice app frontend is a react app. So install the latest npm and react.js on the target machine, if you haven't already.
- Download/fork/clone the source of front end at :
- Follow the instructions at the following README
sdb-mtr-front-end
cd sdb-mid-tier-routing-demo-app-ui/
npm install
npm start
- Once the npm start has completed, the Invoice app login page must be now be loaded on your default browser.
- Now enter the customer Id & password for user name and password fields, select individual or customer type and hit login.
- Once the customer logs in, the customer specific data is loaded from the backend instance which is returned from the shardDetails and swimlane mid-tier routing service api calls that the Invoice app makes to the mid-tier routing component.
