...
...
| Panel | ||
|---|---|---|
| ||
This section describes how custom database changes can be made, defining those changes in XML files rather that than writing SQL directly against the database.
What does this section cover?
|
Introduction
CRM.COM uses LiquiBase to implement this its database changes management mechanism and uses changelog XML files to define the desired database changes.
All supported database changes can be found here.
...
Each change runs only one time and this is ensured by DATABASECHANGELOGLOCK table.
Changelog
...
for database object changes
New tables, columns, indexes and constraints are defined in <projectname>.changelog.xml which is located under ProjectNameCrmEJB/ejbModuleplugin-project/src/main/resources.
Examples
Create New Table
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
<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> |
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
<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> |
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
<databaseChangeLog>
...
<changeSet author="p_kestora" id="custom-db-change-3">
<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> |
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
<databaseChangeLog>
...
<changeSet author="custom_developer" id="custom-db-change-4">
<createIndex indexName="IX_CUSTOMTABLE01" tableName="CUSTOMTABLE">
<column name="RENTALID"/>
</createIndex>
</changeSet>
...
</databaseChangeLog> |
Changelog
...
for data changes
data.xml, which is located under ProjectNameCrmEJB/ejbModuleplugin-project/src/main/resources, is used for inserting and updating data in existing tables
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
<databaseChangeLog>
...
<changeSet author="custom_developer" id="custom-data-db-change-5">
<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> |
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
<databaseChangeLog>
...
<changeSet author="custom_developer" id="custom-data-db-change-6">
<update tableName="CUSTOMTABLE">
<column name="CUSTOMDESC" value="This record is related to a rental" type="varchar(512)"/>
<where>RENTALID IS NOT NULL</where>
</update>
</changeSet>
...
</databaseChangeLog> |
...
Note: Make sure that you use a globally unique changeSet id for each changeSet. A changeSet id used in projectname.changelog.xml cannot be used in projectname.data.xml.