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
...
| 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 | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
<?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> |
...
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 | ||||||
|---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="UTF-8"?>
<DataFix xmlns="http://flexagon.com/database/datafix">
<Description>Fix total_price on order_header</Description>
<FixSQL>update order_header set total_price = 250 where order_id = 1;</FixSQL>
<BackupSQL>create table order_header_bkp as (select * from 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 order_header where order_id = 1) o,
(select order_id, sum(unit_price * quantity) as line_item_total
from 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> |
...