Skip to end of banner
Go to start of banner

Manage Database Changes

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 13 Next »

This section describes how custom database changes can be made, defining those changes in XML files rather that writing SQL directly against the database.

 

What does this section cover?

 

Introduction

CRM.COM uses LiquiBase to implement this database changes management mechanism and uses changelog XML files to define the desired database changes. 

DATABASECHANGELOG table is used to keep track of the changes that ran.

Each change, runs only one time and this is ensured by DATABASECHANGELOGLOCK table.

Changelog For Database Object Changes

New tables, columns and constraints are defined in <projectname>.changelog.xml which is located under ProjectNameCrmEJB/ejbModule.

Create New Table

projectname.changelog.xml 
<databaseChangeLog>
	...
	<changeSet author="custom_developer" id="custom-db-change-1">
		<createTable tableName="CUSTOMTABLE">
            <column name="CUSTOMID" remarks="Primary Key" type="VARCHAR(32)">
                <constraints nullable="false"/>
            </column>
            <column name="CUSTOMNAME" remarks="The name of the custom entity" type="VARCHAR(256)"/>
            <column name="CUSTOMALTCODE" type="VARCHAR(32)"/>
            <column name="CUSTOMDESC" remarks="The description of the custom entity" type="VARCHAR(512)"/>
            <column name="CUSTOMCREATEDDATE" type="TIMESTAMP"/>
            <column name="CUSTOMUPDATEDDATE" type="TIMESTAMP"/>
            <column name="CUSTOMDELETED" type="INTEGER"/>
            <column name="CUSTOMCREATEDBYUSERID" type="VARCHAR(32)"/>
            <column name="CUSTOMUPDATEDBYUSERID" type="VARCHAR(32)"/>
            <column name="CUSTOMCREATEDBYOUUID" type="VARCHAR(32)"/>
            <column name="CUSTOMUPDATEDBYOUUID" type="VARCHAR(32)"/>
            <column defaultValueNumeric="0" name="RECVERSION" type="INTEGER">
                <constraints nullable="false"/>
            </column>
        </createTable>
	</changeSet>
	...
</databaseChangeLog>

Add New Column To An Existing Table

New columns can be added to both release and custom tables.

projectname.changelog.xml 
<databaseChangeLog>
	...
	<changeSet author="custom_developer" id="custom-db-change-2">
        <addColumn tableName="CUSTOMTABLE">
            <column name="RENTALID" remarks="The rental related with the custom entity - FK to TRN_RENTALS" type="VARCHAR(32)"/>
        </addColumn>
	</changeSet>
	...
</databaseChangeLog>

Add a Foreign Key Constraint

projectname.changelog.xml 
<databaseChangeLog>
	...
	<changeSet author="p_kestora" id="CRMCOM-5483-05">
        <addForeignKeyConstraint baseColumnNames="RENTALID" baseTableName="CUSTOMTABLE" constraintName="FK_CUSTOMTABLE_01" deferrable="false" initiallyDeferred="false" onDelete="NO ACTION" onUpdate="NO ACTION" referencedColumnNames="RENTALID" referencedTableName="TRN_RENTALS"/>
    </changeSet>
	...
</databaseChangeLog>

Changelog For Data Changes

<projectname>.data.xml, which is located under ProjectNameCrmEJB/ejbModule, is used for inserting and updating data in existing tables

Insert Data In Existing Table

projectname.data.xml 
<databaseChangeLog>
	...
	<changeSet author="custom_developer" id="custom-data-db-change-3">
        <insert tableName="CUSTOMTABLE">
            <column name="CUSTOMID" value="63E096DF859A44ED958007C71F0389E5"/>
            <column name="CUSTOMNAME" value="Standard Customer"/>
            <column name="CUSTOMALTCODE" value="SC"/>
            <column name="CUSTOMDESC"/>
            <column name="CUSTOMCREATEDDATE" valueDate="2016-03-11 10:03:21.0"/>
            <column name="CUSTOMUPDATEDDATE" valueDate="2016-03-11 10:03:56.0"/>
            <column name="CUSTOMDELETED" valueNumeric="0"/>
            <column name="CUSTOMCREATEDBYUSERID" value="1"/>
            <column name="CUSTOMUPDATEDBYUSERID" value="1"/>
            <column name="CUSTOMCREATEDBYOUUID" value="1"/>
            <column name="CUSTOMUPDATEDBYOUUID" value="1"/>
            <column name="RECVERSION" valueNumeric="0"/>
        </insert>
    </changeSet>
	...
</databaseChangeLog>

Update Data In Existing Table

projectname.data.xml 
<databaseChangeLog>
	...
	<changeSet author="custom_developer" id="custom-data-db-change-3">
        <insert tableName="CUSTOMTABLE">
            <column name="CUSTOMID" value="63E096DF859A44ED958007C71F0389E5"/>
            <column name="CUSTOMNAME" value="Standard Customer"/>
            <column name="CUSTOMALTCODE" value="SC"/>
            <column name="CUSTOMDESC"/>
            <column name="CUSTOMCREATEDDATE" valueDate="2016-03-11 10:03:21.0"/>
            <column name="CUSTOMUPDATEDDATE" valueDate="2016-03-11 10:03:56.0"/>
            <column name="CUSTOMDELETED" valueNumeric="0"/>
            <column name="CUSTOMCREATEDBYUSERID" value="1"/>
            <column name="CUSTOMUPDATEDBYUSERID" value="1"/>
            <column name="CUSTOMCREATEDBYOUUID" value="1"/>
            <column name="CUSTOMUPDATEDBYOUUID" value="1"/>
            <column name="RECVERSION" valueNumeric="0"/>
        </insert>
    </changeSet>
	...
</databaseChangeLog>
  • No labels