In this blog, we will look at how we can integrate existing database into Progress Rollbase Applications. Rollbase is a low-code platform that helps create all sorts of applications at an unprecedented speed and efficiency. In most cases, you intend to modernize your existing applications to an all new User interface and experience. This can be easily achieved with Rollbase External database support. This feature is only available on private cloud.
Purpose and Prerequisite
We will build an Application in Rollbase which will have
- Employee and Department as objects
- 1:M relationship between Department and Employee.
As a prerequisite, we need to have a database schema with two tables ‘Employee’ & ‘Department’ as:
- Employee table with employee id, name & department id columns.
- Department table with department id & name column.
- Both these tables have some data.
Setting up Rollbase on the existing Database schema
Rollbase installation comes with sql files for 4 databases:
- Oracle (create_ora.sql)
- MySQL (create_mysql.sql)
- MSSQL (create_ms.sql)
- OpenEdge (create_oe.sql)
For the purpose of this blog, we will use the MySQL version and MySQL workbench. We will now run ‘create_mysql.sql’ script to install Rollbase tables on the existing schema.
Your database schema (rb_dbo) will now have existing tables (department, employee) and Rollbase tables (tables that start with ‘rb_’).
Let’s take a quick look at the data from the ’employee’ and ‘department’ tables.
Configuring database in Rollbase
Creating Application in Rollbase
Let’s start building our application in Rollbase with objects accessing data from the existing tables.
As a first step, we will create an Application ‘External Table Demo’ in Rollbase by launching Application create wizard and click on ‘Let me build it my way’.
Creating External Objects in Rollbase
1. We will now create the Department object that points to the ‘department’ table in the schema.
- In order to this, we should choose ‘A new Object (with Tab) from an External Metadata’ option from the object create popup.
- Choose ‘External Tables’ option in the ‘Import Object Metadata’ page and click on ‘Next’.
- Choose the department table from the dropdown and a name column. Now, provide a name for the Rollbase object that will point to this database table and click on ‘Create Object’.
- Choose a primary key for the ‘department’ table viz. ‘id’ and click on ‘Create Fields’. This will now create fields in the Department object and associate them with the respective database columns. In this page, you can set Rollbase field details such as their label, field type, uniqueness constraint etc.
- In the ‘Adjust SQL’ page, you will be see options to configure your sql queries for the CRUD operations on the Department object.
2. Follow the same steps to create the Employee object that points to the ’employee’ table in the schema. During this configuration, make sure you select the ‘department_id’ column as a foreign key in ‘Create Fields’ page. This will automatically create a 1:M relationship in Rollbase between Employee and Department object.
3. The ‘Department’ and ‘Employee’ Tabs in the Application now show the data from their respective database tables.
4. Let’s now create an Employee for the Finance Department. This will also create an entry in the ’employee’ table.
Let’s now query the ’employee’ table and verify the new Employee entry.
To conclude, we learnt how easy it is to develop applications in Rollbase that can reuse your existing data. There is comprehensive documentation on this subject here.