With the 2204 release of Oracle Visual Builder, we are adding a major new feature – Bring Your Own Schema. With this feature developers can point Visual Builder to an existing database schema, and use the declarative business objects functionality to expose the tables and views as REST endpoints, add business logic, and create user interfaces with ease. In this blog we'll show how this works.
Setup
To leverage this functionality your Visual Builder instance will need to leverage an external Oracle database instead of the built-in database. It's quite easy to switch your instance setting to a separate DB. With the new functionality, in addition to managing its own set of schemas for BOs that you create, VB can access the tables that are already defined in the DB in other schemas.
To enable you to leverage another schema's table in your app, your instance administration needs to add that schema to the list of "Available Schemas" in the tenant settings.
Once this is done, you'll be able to choose the schema in your application settings->business objects to replace the default schema. Note that an application can either use an external schema or use the default one – so you either use existing DB objects or use the ability of VB to create and manage DB objects. A key difference here is who is responsible for the life cycle of the tables – with existing tables/views you manage the lifecycle outside of VB.
Mapping tables and views
Once your app is set to map to an external schema, when you click to create a new business object, you'll be prompted to choose which DB table or view you want to base your BO on. When you pick the DB object you can modify the names and titles of fields in the BO. When you click create, VB creates all the REST APIs needed to preform CRUD operations against the table/view. When two objects have a relationship defined in the database (foreign keys), the relationship will also be created between the business objects that represents those DB objects.
You can now add your own business logic triggers, validations, and functions on the business object.
A key advantage of being able to create BOs on top of database views is that you can leverage the full power of SQL. For example, your BOs can contain aggregated data using DB functions and distinct values for tables too.
The business objects you create this way behave just like any other business object in terms of the way you bind them to your user interface. VB will automatically leverage advanced functionality such as pagination, filterCriterion, sorting, and relationship management when work with the data.
Here is a quick demo of these capabiltieis:
Lifecycle
[Update May 2022 – Visual Builder now supports switching schemas for various lifecycle stages.]
With the 2204 release the BOs are mapped to a specific schema, that is used for all the lifecycle stages of the app (dev/stage/live). If you want to switch to different schemas before you go to the next stage of the lifecycle you'll need to modify that in the businessObjects.json file of your app. Note that you are in charge of managing the lifecycle of the database objects as well as doing operations such as backup and recovery when working with the external tables.
Another advantage of using external tables is that you have full access to the schema with any other tool that can connect to the database.
You can learn more about the new features in our documentation on our doc chapter "Switch to Your Own Database Schema for Business Objects".