Page Comparison

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

Object Type Identification

...

Name	Code	Description	Default Value
Data Fix Root Source Directory	FDEBS_DATA_FIX_ROOT_SOURCE_DIR	The source directory to recognize files as data fixes	datafix
Data Fix Root Destination Directory	FDEBS_DATA_FIX_DESTINATION_DIR	The destination directory to copy file to. Leave blank to not copy file anywhere

Related Environment Instance Properties

Name	Code	Required	Description
JDBC Driver Path	FDEBS_DRIVER_PATH	Yes	Location 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.

...

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

Data Fix Source Formatting

...

A single validation contains the following:

...

An environment contains the following:

...

The Before, After, and UpdateCount elements may contain one or more of the following operators to compare the result:

Equals
NotEquals
LessThan
LessThanEqual
GreaterThan
GreaterThanEqual

If more than one of these elements are present, all must evaluate to true for the validation to pass.

Data Fix Source Template

...

language	xml
theme	RDark
title	Basic Data Fix Template
linenumbers	true

...

XML Schema

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

Code Block

language	xml
theme	RDark
title	Data Fix XML Schema (XSD)
linenumbers	true

<?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" minOccurs="0" maxOccurs="1"/>
                    <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.

Element

Path

XSD Line Number

Description

DataFix

/

3

The root element for the data fix

Description

/DataFix

9

A description for the data fix being run

FixSQL

/DataFix

10

The SQL which implements the data fix. It can be multiple statements delimited by a semicolon or slash

BackupSQL

/DataFix

11

SQL to perform necessary backup of data before executing FixSQL. This can be one or more statements delimited by a semicolon or slash

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

ConnectStringProperty

/DataFix

12

The JDBC connection string for the database. The APPS_JDBC_URL environment variable will be used if not specified in file

UserProperty

/DataFix

13

User to connect to database with. The object attribute will be used if property is not specified in file

PasswordProperty

/DataFix

14

Password for the database user. The object attribute will be used if property is not specified in file

Validations

/DataFix

15

The rule(s) used to validate the data fix before committing changes

Validation

/DataFix/Validations

18

A single validation rule

Description

/DataFix/Validations/Validation

21

A description for the validation rule

ValidationSQL

/DataFix/Validations/Validation

22

Select 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/Validation

23

The expected results defined by an environment

EnvironmentCode

/DataFix/Validations/Validation/Environment

26

FlexDeploy environment code, or DEFAULT to apply to any deploy environment not explicitly specified

Before

/DataFix/Validations/Validation/Environment

27

Contains results to check before FixSQL is executed

After

/DataFix/Validations/Validation/Environment

28

Contains results to check after FixSQL is executed

UpdateCount

/DataFix/Validations/Validation/Environment

29

Results to compare return of JDBC executeUpdate() of FixSQL. If the FixSQL contains more than one statement, it is the sum of the updated rows

Operators

N/A

42

Complex type containing list of accepted operators to be used in validations

Data Fix Source Template

Code Block

language	xml
theme	RDark
title	Basic Data Fix Template
linenumbers	true

<?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>
               <Validation>     <Equals></Equals>
       <Description></Description>             <ValidationSQL><<NotEquals></ValidationSQL>
			<!-- Can be one or more -->NotEquals>
                   <Environment> <GreaterThan></GreaterThan>
                <EnvironmentCode></EnvironmentCode>    <GreaterThanEqual></GreaterThanEqual>
            <Before>        <LessThan></LessThan>
            <Equals></Equals>        <LessThanEqual></LessThanEqual>
            <NotEquals></NotEquals>    </Before>
                <GreaterThan></GreaterThan><After>
                    <GreaterThanEqual><<Equals></GreaterThanEqual>Equals>
                    <LessThan><<NotEquals></LessThan>NotEquals>
                    <LessThanEqual><<GreaterThan></LessThanEqual>GreaterThan>
                </Before>    <GreaterThanEqual></GreaterThanEqual>
            <After>        <LessThan></LessThan>
            <Equals></Equals>        <LessThanEqual></LessThanEqual>
            <NotEquals></NotEquals>    </After>
                <GreaterThan></GreaterThan><UpdateCount>
                    <GreaterThanEqual><<Equals></GreaterThanEqual>Equals>
                    <LessThan><<NotEquals></LessThan>NotEquals>
                    <LessThanEqual><<GreaterThan></LessThanEqual>GreaterThan>
                </After>    <GreaterThanEqual></GreaterThanEqual>
            <UpdateCount>            <LessThan></LessThan>
        <Equals></Equals>            <LessThanEqual></LessThanEqual>
        <NotEquals></NotEquals>        </UpdateCount>
            <GreaterThan></GreaterThan></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.

Code Block

language	xml
theme	RDark
title	Product Price Fix

<?xml version="1.0" encoding="UTF-8"?>
      <GreaterThanEqual></GreaterThanEqual><DataFix xmlns="http://flexagon.com/database/datafix">
    <Description>Fix list price of product            <LessThan></LessThan>
      ID 47809</Description>
    <FixSQL>update xxhr.XXHR_PRODUCT_LIST set list_price = 150 where product_id = <LessThanEqual><47809;</LessThanEqual>FixSQL>
    <BackupSQL>create table xxhr.xxhr_product_list_bkp as (select * from xxhr.xxhr_product_list)</BackupSQL>
    </UpdateCount><ConnectStringProperty>CONNECT_STRING_PROPERTY</ConnectStringProperty>
    <UserProperty>DB_USER_PROPERTY</UserProperty>
    <PasswordProperty>DB_PASSWORD_PROPERTY</PasswordProperty>
  </Environment>   <Validations>
     </Validation>   <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.

Code Block

language	xml
theme	RDark
title	Product Price Fix

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

Code Block

language	xml
theme	RDark
title	Order Header Fix

<?xml version="1.0" encoding="UTF-8"?>
        <Equals>1</Equals><DataFix xmlns="http://flexagon.com/database/datafix">
    <Description>Fix total_price           </After>
    on order_header</Description>
    <FixSQL>update XXHR.xxhr_order_header set total_price = 250 where <UpdateCount>order_id = 1;</FixSQL>
    <BackupSQL>create table xxhr.xxhr_order_header_bkp as (select * from xxhr.xxhr_order_header)</BackupSQL>
    <Validations>
 <Equals>1</Equals>       <Validation>
         </UpdateCount>   <Description>Make sure order_header total_price  is equal to the line_item <total</Environment>Description>
        </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.

Code Block

language	xml
theme	RDark
title	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 *<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_header)</BackupSQL>id = 1) o, 
    <Validations>         <Validation>             <Description>Make sure(select order_header totalid, sum(unit_price is* equal to thequantity) as line_item total</Description>_total 
                         <ValidationSQL>select case whenfrom oXXHR.xxhr_order_totalitems = l.line_item_total thenwhere order_id = 1 whengroup by o.order_totalid) <> l.line_item_total
then 0 end  			               as validation from      where o.order_id = l.order_id</ValidationSQL>
            <Environment>
     (select order_id, order_total from xxhr.xxhr_order_header where order_id = 1) o,    <EnvironmentCode>DEFAULT</EnvironmentCode>
                <Before>
       (select order_id, sum(unit_price * quantity) as line_item_total       <Equals>0</Equals>
                </Before>
    from XXHR.xxhr_order_items where order_id = 1 group by order_id) l   <After>
                    <Equals>1</Equals>
   where o.order_id = l.order_id</ValidationSQL>          </After>
  <Environment>              <UpdateCount>
  <EnvironmentCode>DEFAULT</EnvironmentCode>                 <Before> <Equals>1</Equals>
                </UpdateCount>
  <Equals>0</Equals>          </Environment>
      </Before>      </Environment>
        </Validation>
 <After>   </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.

Code Block

language	xml
theme	RDark
title	Line Item Fix

<?xml version="1.0" encoding="UTF-8"?>
              <Equals>1</Equals><DataFix xmlns="http://flexagon.com/database/datafix">
    <Description>Fix line items for product IDs 47809      </After>and 45203</Description>
    <FixSQL>update XXHR.xxhr_order_items set unit_price = 100 where product_id = 47809;
  <UpdateCount>          update XXHR.xxhr_order_items set unit_price = 75 where product_id = 45203;
 <Equals>1</Equals>
       			update XXHR.xxhr_product_list set list_price = 100 where product_id = 47809;
        </UpdateCount>    update XXHR.xxhr_product_list set list_price = 75 where product_id = 45203;</Environment>FixSQL>
    <BackupSQL>create table xxhr.xxhr_order_items_bkp as (select * from  </Environment>xxhr.xxhr_order_items);
			   create      </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.

Code Block

language	xml
theme	RDark
title	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 XXHRtable xxhr.xxhr_product_list_bkp as (select * from xxhr.xxhr_product_list);
	</BackupSQL>
    <Validations>
        <Validation>
            <Description>Verify number of rows modified for product with id 47809</Description>
            <ValidationSQL>select count(*) from xxhr.xxhr_order_items setwhere unitlist_price = 100 whereand product_id = 47809;47809</ValidationSQL>
            <Environment>
      update XXHR.xxhr_order_items set unit_price = 75 where product_id = 45203;</FixSQL>          <EnvironmentCode>DEV</EnvironmentCode>
      <BackupSQL>create table xxhr.xxhr_order_items_bkp as (select * from xxhr.xxhr_order_items)</BackupSQL>   <UpdateCount>
 <ConnectStringProperty></ConnectStringProperty>     <UserProperty></UserProperty>     <PasswordProperty></PasswordProperty>     <Validations>    <GreaterThan>80</GreaterThan>
    <Validation>             <Description>Verify number of rows<LessThan>100</LessThan>
modified for product with id 47809</Description>           </UpdateCount>
 <ValidationSQL>select count(*) from xxhr.xxhr_order_items where list_price = 100 and product_id = 47809<</ValidationSQL>Environment>
            <Environment>
                <EnvironmentCode>DEV<<EnvironmentCode>default</EnvironmentCode>
                <UpdateCount>
                    <GreaterThan>80<<GreaterThan>87</GreaterThan>
                    <LessThan>100<<LessThan>93</LessThan>
                </UpdateCount>
            </Environment>
        </Validation>
   <Environment>
     <Validation>
            <Description>Verify number of rows modified for product with id 45203</Description>
            <ValidationSQL>select count(*)  <EnvironmentCode>default</EnvironmentCode>
       from xxhr.xxhr_order_items where list_price = 75 and product_id = 45203</ValidationSQL>
        <UpdateCount>    <Environment>
                <GreaterThan>87<<EnvironmentCode>default</GreaterThan>EnvironmentCode>
                <After>
   <LessThan>93</LessThan>                 <<GreaterThan>120</UpdateCount>
GreaterThan>
           </Environment>         <<LessThan>125</Validation>LessThan>
        <Validation>        </After>
    <Description>Verify number of rows modified for product with id 45203</Description>
 </Environment>
        </Validation>
		<Validation>
<ValidationSQL>select count(*) from xxhr.xxhr_order_items where list_price = 75 and product_id = 45203</ValidationSQL> <Description>Verify total number of row updates</Description>
      <Environment>      <ValidationSQL></ValidationSQL>
          <EnvironmentCode>default</EnvironmentCode>  <Environment>
              <UpdateCount>  <EnvironmentCode>default</EnvironmentCode>
                <After>
 <GreaterThan>120</GreaterThan>                     <LessThan>125<<GreaterThan>200</GreaterThan>
					<LessThan>220</LessThan>
                </UpdateCount>After>
            </Environment>
        </Validation>
    </Validations>
</DataFix>

...

Versions Compared

Old Version 10

New Version Current

Key

Object Type Identification

Related Environment Instance Properties

Sample Build Commands

Data Fix Source Formatting

Data Fix Source Template

XML Schema

Data Fix Source Template

Data Fix Source Examples

Data Fix Source Examples