[COPY] EBS Object Types - Data Fixes

XML files containing one or more data fix sql statements, a sql statement to back up data, and validation statements used to determine whether the data fix should be committed or rolled back.

Object Type Identification

  • If a file with .xml extension is in the folder specified by the Data Fix Root Source Directory project property, then it is considered a data fix file

File Extensions 

NameDescription
.xmlExtensible Markup Language file format used to create common information formats and share both the format and the data using standard ASCII text.

Object Type

NameCode
Data FixDATA_FIX

Object Type Attributes

NameCodeDescriptionRequiredDefault ValueSupported Values
SourceSOURCEObject source location typeYes

SCM

SCM
Target LocationTARGET_LOCATIONPath to where the file should be deployed toNo



Database User PropertyDB_USERProperty name for database user nameNoFDEBS_DB_USER
Database Password PropertyDB_PASSWORDProperty name for database passwordNoFDEBS_DB_PASSWORD

Related Project Properties

NameCodeDescriptionDefault Value
Data Fix Root Source DirectoryFDEBS_DATA_FIX_ROOT_SOURCE_DIRThe source directory to recognize files as data fixesdatafix
Data Fix Root Destination DirectoryFDEBS_DATA_FIX_DESTINATION_DIRThe destination directory to copy file to.  Leave blank to not copy file anywhere

Related Environment Instance Properties

NameCodeRequiredDescription
JDBC Driver PathFDEBS_DRIVER_PATHYesLocation of JDBC driver file required only for running data fix files. For example, /u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc6.jar

Sample Build Commands 

N/A - Build commands not supported for this type.

Sample Deploy Commands 

N/A - Deploy commands not supported for this type.

XML Schema

Elements without min/max occurrences listed have the default values for cardinality, which are minOccurs="1" and maxOccurs="1"

Data Fix XML Schema (XSD)
<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://flexagon.com/database/datafix" targetNamespace="http://flexagon.com/database/datafix" elementFormDefault="qualified">
  <xsd:element name="DataFix">
    <xsd:annotation>
      <xsd:documentation>Data fix object</xsd:documentation>
    </xsd:annotation>
    <xsd:complexType>
      <xsd:all>
        <xsd:element type="xsd:string" name="Description"/>
        <xsd:element type="xsd:string" name="FixSQL"/>
        <xsd:element type="xsd:string" name="BackupSQL"/>
        <xsd:element type="xsd:string" name="ConnectStringProperty" minOccurs="0" maxOccurs="1"/>
        <xsd:element type="xsd:string" name="UserProperty" minOccurs="0" maxOccurs="1"/>
        <xsd:element type="xsd:string" name="PasswordProperty" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="Validations">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="Validation" minOccurs="1" maxOccurs="unbounded">
                <xsd:complexType>
                  <xsd:sequence>
                    <xsd:element type="xsd:string" name="Description"/>
                    <xsd:element type="xsd:string" name="ValidationSQL"/>
                    <xsd:element name="Environment" minOccurs="1" maxOccurs="unbounded">
                      <xsd:complexType>
                        <xsd:all>
                          <xsd:element type="xsd:string" name="EnvironmentCode"/>
                          <xsd:element name="Before" type="Operators" minOccurs="0" maxOccurs="1"/>
                          <xsd:element name="After" type="Operators" minOccurs="0" maxOccurs="1"/>
                          <xsd:element name="UpdateCount" type="Operators" minOccurs="0" maxOccurs="1"/>
                        </xsd:all>
                      </xsd:complexType>
                    </xsd:element>
                  </xsd:sequence>
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:all>
    </xsd:complexType>
  </xsd:element>
  <xsd:complexType name="Operators">
    <xsd:all>
      <xsd:element name="Equals" type="xsd:int" minOccurs="0" maxOccurs="1"/>
      <xsd:element name="NotEquals" type="xsd:int" minOccurs="0" maxOccurs="1"/>
      <xsd:element name="LessThan" type="xsd:int" minOccurs="0" maxOccurs="1"/>
      <xsd:element name="LessThanEqual" type="xsd:int" minOccurs="0" maxOccurs="1"/>
      <xsd:element name="GreaterThan" type="xsd:int" minOccurs="0" maxOccurs="1"/>
      <xsd:element name="GreaterThanEqual" type="xsd:int" minOccurs="0" maxOccurs="1"/>
    </xsd:all>
  </xsd:complexType>
</xsd:schema>

Details of data fix elements are described in the table below. 

ElementPathXSD Line NumberDescription
DataFix/3The root element for the data fix
Description/DataFix9A description for the data fix being run
FixSQL/DataFix10The SQL which implements the data fix. It can be multiple statements delimited by a semicolon or slash
BackupSQL/DataFix11

SQL to perform necessary backup of data before executing FixSQL. This must be one valid SQL statement, not followed by a semicolon

Include ${{FD_PROJECT_VERSION}} in the title of your backup table to easily identify it after execution

ConnectStringProperty/DataFix12The JDBC connection string for the database. The APPS_JDBC_URL environment variable will be used if not specified in file
UserProperty/DataFix13User to connect to database with. The object attribute will be used if property is not specified in file
PasswordProperty/DataFix14Password for the database user. The object attribute will be used if property is not specified in file
Validations/DataFix15The rule(s) used to validate the data fix before committing changes
Validation/DataFix/Validations18A single validation rule
Description/DataFix/Validations/Validation21A description for the validation rule
ValidationSQL/DataFix/Validations/Validation22Select query which is executed before and after the FixSQL is executed, and result is compared with before and after values. This must be one valid SQL statement, not followed by a semicolon
Environment/DataFix/Validations/Validation23The expected results defined by an environment
EnvironmentCode/DataFix/Validations/Validation/Environment26FlexDeploy environment code, or DEFAULT to apply to any deploy environment not explicitly specified
Before/DataFix/Validations/Validation/Environment27Contains results to check before FixSQL is executed
After/DataFix/Validations/Validation/Environment28Contains results to check after FixSQL is executed
UpdateCount/DataFix/Validations/Validation/Environment29Results to compare return of JDBC executeUpdate() of FixSQL. If the FixSQL contains more than one statement, it is the sum of the updated rows
OperatorsN/A42Complex type containing list of accepted operators to be used in validations

Data Fix Source Template

Basic Data Fix Template
<?xml version="1.0" encoding="UTF-8"?>
<DataFix xmlns="http://flexagon.com/database/datafix">
    <Description></Description>
    <FixSQL></FixSQL>
    <BackupSQL></BackupSQL>
    <ConnectStringProperty></ConnectStringProperty>
    <UserProperty></UserProperty>
    <PasswordProperty></PasswordProperty>
    <Validations>
		<!-- Can be one or more -->
        <Validation>
            <Description></Description>
            <ValidationSQL></ValidationSQL>
			<!-- Can be one or more -->
            <Environment> 
                <EnvironmentCode></EnvironmentCode>
                <Before>
                    <Equals></Equals>
                    <NotEquals></NotEquals>
                    <GreaterThan></GreaterThan>
                    <GreaterThanEqual></GreaterThanEqual>
                    <LessThan></LessThan>
                    <LessThanEqual></LessThanEqual>
                </Before>
                <After>
                    <Equals></Equals>
                    <NotEquals></NotEquals>
                    <GreaterThan></GreaterThan>
                    <GreaterThanEqual></GreaterThanEqual>
                    <LessThan></LessThan>
                    <LessThanEqual></LessThanEqual>
                </After>
                <UpdateCount>
                    <Equals></Equals>
                    <NotEquals></NotEquals>
                    <GreaterThan></GreaterThan>
                    <GreaterThanEqual></GreaterThanEqual>
                    <LessThan></LessThan>
                    <LessThanEqual></LessThanEqual>
                </UpdateCount>
            </Environment>
        </Validation>
    </Validations>
</DataFix>

Data Fix Source Examples

The first example is a simple data fix that updates one row of a table, then verifies only the expected row is updated.

Product Price Fix
<?xml version="1.0" encoding="UTF-8"?>
<DataFix xmlns="http://flexagon.com/database/datafix">
    <Description>Fix list price of product ID 47809</Description>
    <FixSQL>update xxhr.XXHR_PRODUCT_LIST set list_price = 150 where product_id = 47809;</FixSQL>
    <BackupSQL>create table xxhr.xxhr_product_list_bkp as (select * from xxhr.xxhr_product_list)</BackupSQL>
    <ConnectStringProperty>CONNECT_STRING_PROPERTY</ConnectStringProperty>
    <UserProperty>DB_USER_PROPERTY</UserProperty>
    <PasswordProperty>DB_PASSWORD_PROPERTY</PasswordProperty>
    <Validations>
        <Validation>
            <Description>Make sure product_id = 47809 has a list_price of 150</Description>
            <ValidationSQL>select count(*) from xxhr.xxhr_product_list where list_price = 150 and product_id = 47809</ValidationSQL>
            <Environment>
                <EnvironmentCode>DEV</EnvironmentCode>
                <Before>
                    <Equals>0</Equals>
                </Before>
                <After>
                    <Equals>1</Equals>
                </After>
                <UpdateCount>
                    <Equals>1</Equals>
                </UpdateCount>
            </Environment>
        </Validation>
    </Validations>
</DataFix>

This example updates a row on the order_header table, then validates that the order_item total matches the updated order_header total.

Order Header Fix
<?xml version="1.0" encoding="UTF-8"?>
<DataFix xmlns="http://flexagon.com/database/datafix">
    <Description>Fix total_price on order_header</Description>
    <FixSQL>update XXHR.xxhr_order_header set total_price = 250 where order_id = 1;</FixSQL>
    <BackupSQL>create table xxhr.xxhr_order_header_bkp as (select * from xxhr.xxhr_order_header)</BackupSQL>
    <Validations>
        <Validation>
            <Description>Make sure order_header total_price is equal to the line_item total</Description>
            <ValidationSQL>select case when o.order_total = l.line_item_total then 1 when o.order_total <> l.line_item_total then 0 end 
			               as validation from
                           (select order_id, order_total from xxhr.xxhr_order_header where order_id = 1) o, 
                           (select order_id, sum(unit_price * quantity) as line_item_total 
                           from XXHR.xxhr_order_items where order_id = 1 group by order_id) l
                           where o.order_id = l.order_id</ValidationSQL>
            <Environment>
                <EnvironmentCode>DEFAULT</EnvironmentCode>
                <Before>
                    <Equals>0</Equals>
                </Before>
                <After>
                    <Equals>1</Equals>
                </After>
                <UpdateCount>
                    <Equals>1</Equals>
                </UpdateCount>
            </Environment>
            </Environment>
        </Validation>
    </Validations>
</DataFix>

This example is a data fix that updates the price of multiple products, then verifies the amount of items affected by this change.

Line Item Fix
<?xml version="1.0" encoding="UTF-8"?>
<DataFix xmlns="http://flexagon.com/database/datafix">
    <Description>Fix line items for product IDs 47809 and 45203</Description>
    <FixSQL>update XXHR.xxhr_order_items set unit_price = 100 where product_id = 47809;
            update XXHR.xxhr_order_items set unit_price = 75 where product_id = 45203;</FixSQL>
    <BackupSQL>create table xxhr.xxhr_order_items_bkp as (select * from xxhr.xxhr_order_items)</BackupSQL>
    <Validations>
        <Validation>
            <Description>Verify number of rows modified for product with id 47809</Description>
            <ValidationSQL>select count(*) from xxhr.xxhr_order_items where list_price = 100 and product_id = 47809</ValidationSQL>
            <Environment>
                <EnvironmentCode>DEV</EnvironmentCode>
                <UpdateCount>
                    <GreaterThan>80</GreaterThan>
                    <LessThan>100</LessThan>
                </UpdateCount>
            </Environment>
            <Environment>
                <EnvironmentCode>default</EnvironmentCode>
                <UpdateCount>
                    <GreaterThan>87</GreaterThan>
                    <LessThan>93</LessThan>
                </UpdateCount>
            </Environment>
        </Validation>
        <Validation>
            <Description>Verify number of rows modified for product with id 45203</Description>
            <ValidationSQL>select count(*) from xxhr.xxhr_order_items where list_price = 75 and product_id = 45203</ValidationSQL>
            <Environment>
                <EnvironmentCode>default</EnvironmentCode>
                <UpdateCount>
                    <GreaterThan>120</GreaterThan>
                    <LessThan>125</LessThan>
                </UpdateCount>
            </Environment>
        </Validation>
    </Validations>
</DataFix>
The following macros are not currently supported in the footer:
  • style