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
...
Name | Code | Description | Required | Default Value | Supported Values |
---|
Source | SOURCE | Object source location type | Yes | SCM | SCM |
Target Location | TARGET_LOCATION | Path to where the file should be deployed to | No |
|
|
Database URL Property | DB_URL | Property name for database URL | No | FDJDBC_URL |
|
Database User Property | DB_USER | Property name for database user name | No | FDJDBC_USER |
|
Database Password Property | DB_PASSWORD | Property name for database password | No | FDJDBC_PASSWORD |
|
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 |
must valid SQL statement, not followed or more statements delimited by a semicolon or slash |
ConnectStringProperty | /DataFix | 12 | 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 |
Data Fix Source Template
Code Block |
---|
|
<?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>
|
...
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 order_items)</BackupSQL>
<ConnectStringProperty>DATA_FIX_URL_PROPERTY</ConnectStringProperty>
<UserProperty>DATA_FIX_USER_PROPERTY</UserProperty>
<PasswordProperty>DATA_FIX_PASSWORD_PROPERTY</PasswordProperty>
<Validations> update order_items 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>
</After>
</Environment>
<Environment>
<Validation>
<Description>Verify number of rows modified<EnvironmentCode>default</EnvironmentCode>
for product with id 47809</Description> <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> |
...