How To: Configure a Project Using Oracle Schemas for DBdefs
There may be situations where Oracle schemas are used to represent the various environments in a project pipeline. For example, the Dev and Test environments could be represented by the database schemas APPDEV and APPTEST which coexist on the same database. This is often done to reduce the license costs of supporting many Oracle databases. This article will show how to configure a Liquibase Business or Enterprise project for this situation.
Database Configuration
Each DBdef in a Liquibase pipeline requires its own database tracking table so that Liquibase knows which changes have been applied to that environment. Create a unique TRACKING schema for each DBdef in the pipeline. In this example, there are two Oracle databases: one for production and another for the RefDB, Dev, and Test environments. The managed schemas are APPREF, APPDEV, APPTEST, and APPPROD representing the steps in the pipeline. Each Dbdef has its own tracking schema shown here as TRACKREF, TRACKDEV, TRACKTEST, and TRACKPROD. Only one DATICAL_USER account is needed for each database.
Liquibase Project Configuration
In this example, the Liquibase project will have one pipeline as shown below. However, the same principles apply to multi-pipeline projects.
When defining the project, use a property substitution variable in the form ${variable_name} to specify the Managed and Tracking schemas. In the GUI, type the variable name instead of selecting from the list of schemas. If there are multiple managed schemas, use a comma separated list of variables.
The datical.project file will contain the variable names instead of the actual schema names.
<?xml version="1.0" encoding="ASCII"?>
<dbproject:Project xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dbproject="http://datical.com/db/project/1.0" name="onedb" storedLogicExternal="true" deployThreshold="stopOnError" deployMode="quick" projectsId="5742f46b-febc-4569-8465-dea5e356c7df" runtimeCredentials="false" multiSchema="true" dbSchemaDefinition="project" schemaSelectionStep="ref" trackingSchema="${tracking_schema}" enableRowCount="disabled" multiCatalog="false" lastEdited="2020-07-07T14:33:41.957Z">
<dbDefs xsi:type="dbproject:OracleDbDef" name="ref" driver="oracle.jdbc.OracleDriver" hostname="demo-db1-rhel6.datical.net" port="1521" username="datical_user" password="ZGF0aWNhbF91c2Vy" contexts="ref" storageOptionCollectedAtSnapshot="true" labels="current" dbDefsId="1e1802f2-5368-3e97-b81e-da06c318d2af" serviceName="mmb_onedb.datical.net" enableCompression="false" rowsPerBatch="10000" useWallet="false"/>
<dbDefs xsi:type="dbproject:OracleDbDef" name="dev" driver="oracle.jdbc.OracleDriver" hostname="demo-db1-rhel6.datical.net" port="1521" username="datical_user" password="ZGF0aWNhbF91c2Vy" contexts="dev" storageOptionCollectedAtSnapshot="true" labels="current" dbDefsId="ecb2952f-99bd-3c5c-9a72-500c6c35f9a4" serviceName="mmb_onedb.datical.net" enableCompression="false" rowsPerBatch="10000" useWallet="false"/>
<plans name="current" databaseDefs="//@dbDefs[name='ref'] //@dbDefs[name='dev']" plansId="a5b261d9-5986-3d58-bbdd-e73bbe8035f4"/>
<changelog href="Changelog/changelog.xml#//@databaseChangeLog"/>
<schemas name="${managed_schema}"/>
</dbproject:Project>
Next, create changelog properties that will map the variables to the correct schema name for each environment.
On the Settings Tab for the project, click on Manage Properties.
Create properties for each DBdef in the pipeline. The Property Key is the name of the substitution variable that was used in the project definition. The Property Value is the schema name. Be sure to enter the schema name in UPPERCASE. The Context is used to map the property value to a DBdef. Be sure that case and spelling match the Context that is defined for the DBdef. Leave all other fields as the defaults.
The changelog.xml will now contain the variable mapping. At runtime Liquibase will use the changelog properties to map the correct Managed and Tracking schemas for each step in the pipeline. You can also edit the changelog.xml using a text editor if that is more convenient that using the GUI.
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:appdba="http://www.datical.net/xml/ns/appdba" xmlns:datical="http://www.datical.net/xml/ns/datical" xmlns:storedlogic="http://www.datical.net/xml/ns/storedlogic" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" datical:lastEdited="2020-07-07T15:09:46.379Z" logicalFilePath="Changelog/changelog.xml" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
<property appdba:substitutionMode="none" appdba:target="" context="ref" labels="" name="managed_schema" value="APPREF"/>
<property appdba:substitutionMode="none" appdba:target="" context="dev" labels="" name="managed_schema" value="APPDEV"/>
<property appdba:substitutionMode="none" appdba:target="" context="test" labels="" name="managed_schema" value="APPTEST"/>
<property appdba:substitutionMode="none" appdba:target="" context="prod" labels="" name="managed_schema" value="APPPROD"/>
<property appdba:substitutionMode="none" appdba:target="" context="ref" labels="" name="tracking_schema" value="TRACKREF"/>
<property appdba:substitutionMode="none" appdba:target="" context="dev" labels="" name="tracking_schema" value="TRACKDEV"/>
<property appdba:substitutionMode="none" appdba:target="" context="test" labels="" name="tracking_schema" value="TRACKTEST"/>
<property appdba:substitutionMode="none" appdba:target="" context="prod" labels="" name="tracking_schema" value="TRACKPROD"/>
</databaseChangeLog>SQL Repo Configuration
The managed schemas change from one environment to another, so the schema names cannot be hard-coded. To provide the schema names at runtime, create a metadata.properties file in the SQL Repo if one does not already exist.
In the metadata.properties file specify the substitution variable for the managed schema.
schemaName=${managed_schema}Special Note
If the RefDB is located in the same database as other environments, Flashback database cannot be used for backup/restore. A flashback operation would disrupt work being done in all environments hosted on that database. The default schema level backup/restore will not interfere with other environments on the same database.
See also
Copyright © Datical 2012-2020 - Proprietary and Confidential