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
...
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.
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="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> |
...