According to their developer docs, it seemed fit this use case exactly:
Manage a collection of updates and insertions as a unit before incorporating them into production data
Workspace Manager lets you review changes and roll back undesirable ones before making the changes public. Until you make the changes public, they are invisible to other users of the database, who will access only the regular production data. You can organize the changes in a simple set of workspaces or in a complex workspace hierarchy. A typical example might be a life sciences application in which Workspace Manager supports the discovery and quality assurance (QA) processes by managing a collection of updates before they are merged with the production data.
You could think of Oracle Workspace Manager as light "git"-like db versioning.
Simply put:
- You create a workspace (branch)...
- You make your changes there.. other people can connect to your workspace .. also make changes... or alternatively create their own branch from yours.
- You can refresh (fetch / merge) your workspace and resolve any conflicts that arise.
- Once everything is sorted and all is well with your changes you merge it back into the "LIVE" (origin: develop / master) workspace.
I used an existing Docker image of a oracle standard edition version. Oracle Workspace manager is not available on XE unfortunately. This is a rather large image, and it does take a couple minutes to initialise. The image is available on here on Docker Hub.
To setup the initial DB I tried out Flyway. All quite simple and easy to implement.
Under resources/db/migration there are a number of sql files that do the initial database setup.
When you enable versioning the following happens, the table is renamed and a view is created allowing the "recording" of changes that occur.
Reference: (oracle presentation available here)
So after the initial setup you will seen a number of tables and views:
To try this out...
To check the current workspace:
GET: http://localhost:9119/poc/workspace
To select all the information from the code table for the current workspace:
GET: http://localhost:9119/poc/workspace/data/code
Create code in the current workspace:
POST: http://localhost:9119/poc/workspace/data/code
BODY:
{
"id":2,
"descr": "some code",
"type": 1
}
To change workspace (LIVE is default and available):
PUT: http://localhost:9119/poc/workspace/{workspaceName}
To create a workspace:
POST: http://localhost:9119/poc/workspace/{workspaceName}
To remove a workspace:
DELETE: http://localhost:9119/poc/workspace/{workspaceName}
These REST resources just wrap some of the functions from the DBMS_WM Package.
This is maybe just the tip of a iceberg, as there is a ton of functionality available from this package.
All the code is available here.
Connect database with following setting:
hostname: localhost
port: 1521
sid: xe
service name: xe.oracle.docker
username: system
password: oracle
To
connect using sqlplus:
sqlplus
system/oracle@//localhost:1521/xe.oracle.docker
To connect on mac os - install instantclient and run from there:
./instantclient_12_1/sqlplus system/oracle@local
To setup the initial DB I tried out Flyway. All quite simple and easy to implement.
Under resources/db/migration there are a number of sql files that do the initial database setup.
When you enable versioning the following happens, the table is renamed and a view is created allowing the "recording" of changes that occur.
Reference: (oracle presentation available here)
So after the initial setup you will seen a number of tables and views:
To try this out...
- Get the Oracle Docker image
- Once the DB is started... run the boot DataApplication.
- Use Postman on the REST resources below
To check the current workspace:
GET: http://localhost:9119/poc/workspace
To select all the information from the code table for the current workspace:
GET: http://localhost:9119/poc/workspace/data/code
Create code in the current workspace:
POST: http://localhost:9119/poc/workspace/data/code
BODY:
{
"id":2,
"descr": "some code",
"type": 1
}
To change workspace (LIVE is default and available):
PUT: http://localhost:9119/poc/workspace/{workspaceName}
To create a workspace:
POST: http://localhost:9119/poc/workspace/{workspaceName}
To remove a workspace:
DELETE: http://localhost:9119/poc/workspace/{workspaceName}
To merge a workspace:
PUT: http://localhost:9119/poc/workspace/merge/{workspaceName}
PUT: http://localhost:9119/poc/workspace/merge/{workspaceName}
These REST resources just wrap some of the functions from the DBMS_WM Package.
This is maybe just the tip of a iceberg, as there is a ton of functionality available from this package.
All the code is available here.
