Page Comparison

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

Object Type Identification

...

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

must

can be one

valid SQL statement, not followed

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 object attribute will be used if property is 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

...

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 orderproduct_itemslist set unitlist_price = 100 where product_id = 47809;
			update product_list set list_price = 75 where product_id =    45203;
			update order_items set unit_price = 75100 where product_id = 4520347809;</FixSQL>
     <BackupSQL>create table order_items_bkp as (select * from update order_items)</BackupSQL> set unit_price = 75 where product_id = 45203;</FixSQL>
    <BackupSQL>create table order_items_bkp as (select * from order_items);
			   create table product_list_bkp as (select * from product_list);</BackupSQL>
    <ConnectStringProperty>DATA_FIX_URL_PROPERTY</ConnectStringProperty>
    <UserProperty>DATA_FIX_USER_PROPERTY</UserProperty>
    <PasswordProperty>DATA_FIX_PASSWORD_PROPERTY</PasswordProperty>
    <Validations>
        <Validation>
            <Description>Verify number of rows modified for product with id 47809</Description>
            <ValidationSQL>select count(*) from order_items where list_price = 100 and product_id = 47809</ValidationSQL>
            <Environment>
                <EnvironmentCode>DEV</EnvironmentCode>
                <After>
                    <GreaterThan>80</GreaterThan>
                    <LessThan>100</LessThan>
           <ConnectStringProperty>DATA_FIX_URL_PROPERTY</ConnectStringProperty>     <UserProperty>DATA_FIX_USER_PROPERTY</UserProperty></After>
    <PasswordProperty>DATA_FIX_PASSWORD_PROPERTY</PasswordProperty>     <Validations>   </Environment>
     <Validation>       <Environment>
     <Description>Verify   number of rows modified for product with id 47809</Description><EnvironmentCode>default</EnvironmentCode>
                <After>
<ValidationSQL>select count(*) from order_items where list_price = 100 and product_id = 47809</ValidationSQL>         <GreaterThan>87</GreaterThan>
   <Environment>                 <EnvironmentCode>DEV<<LessThan>93</EnvironmentCode>LessThan>
                <UpdateCount></After>
            </Environment>
        <GreaterThan>80<</GreaterThan>Validation>
        <Validation>
           <LessThan>100</LessThan>
      <Description>Verify number of rows modified for product with id 45203</Description>
          </UpdateCount>  <ValidationSQL>select count(*) from order_items where list_price = 75 and product_id = <45203</Environment>ValidationSQL>
            <Environment>
                <EnvironmentCode>default</EnvironmentCode>
                <UpdateCount><After>
                    <GreaterThan>87<<GreaterThan>120</GreaterThan>
                    <LessThan>93<<LessThan>125</LessThan>
                </UpdateCount>After>
            </Environment>
        </Validation>
        		<Validation>
            <Description>Verify total number of rows modified for product with id 45203<modified</Description>
            <ValidationSQL>select count(*) from order_items where list_price = 75 and product_id = 45203<<ValidationSQL></ValidationSQL>
            <Environment>
                <EnvironmentCode>default</EnvironmentCode>
                <UpdateCount>
                    <GreaterThan>120<<GreaterThan>200</GreaterThan>
                    <LessThan>125<<LessThan>220</LessThan>
                </UpdateCount>
            </Environment>
        </Validation>
    </Validations>
</DataFix>

...

Versions Compared

Old Version 12

New Version Current

Key

Object Type Identification