-
Notifications
You must be signed in to change notification settings - Fork 0
DB Diff
All Basic steps are completely same as for XML comparison.
Probably, it worse to mention, that basic comparison, without using [Included Queries][#includedDb] elements can be used
with any Map interface
def xml = new XmlSlurper().parseText(new File('/path/to/file').text).toList();
Sql sql = Sql.newInstance(
url: 'localhost',
user: 'admin',
password: 'admin',
driver: 'com.mysql.jdbc.Driver'
);
String query = """
SELECT *
FROM Somthething
WHERE id = 1;
""";
List<GroovyRowResult> rows = sql.rows();
AbstractDiffHelper diffHelper = new DbDiffHelper(xml, rows);
diffHelper.calcDiff();
assert diffHelper.isSimilar();
When it is necessary to convert XML to
List<NodeChild> use XmlUtil.walkXmlByPath() function.
For more information, refer to [Transform XMl][#transformXml] section.
For DB Comparison ouputList1 contains XML list, ouputList2 - List of DB rows.
There is nothing to add more, as generally logic is same:
- Feed with inputs
- Call
calcDiff()method - Review results from outputLists.
That is up to you how to represent or analyse provided results. Probably, best way is manual analysis of found differences. Probably, in future function for XML to DB or DB to XML transform will be provided.
In general, you just have to feed your XML structure to provided function for automated XML to DB conversion
DbDiffHelper.mapXml((NodeChild)new XmlSlurper().parseText(xmlSource));
This function will create DB_result naming according to selected for project naming convention.
<result>
<id>getIncentive</id>
<data>
<lease>
<creditrating tier="STD">
<mileage code="20">
<term length="24">
<residualpct>52</residualpct>
<acquisition>0</acquisition>
<specialApr>0.0469</specialApr>
</term>
<term length="36">
<residualpct>44</residualpct>
<acquisition>0</acquisition>
<specialApr>0.0469</specialApr>
</term>
<term length="48">
<residualpct>38</residualpct>
<acquisition>0</acquisition>
<specialApr>0.0469</specialApr>
</term>
<term length="60">
<residualpct>33</residualpct>
<acquisition>0</acquisition>
<specialApr>0.0469</specialApr>
</term>
</mileage>
<mileage code="24">
<term length="24">
<residualpct>50</residualpct>
<acquisition>0</acquisition>
<specialApr>0.0469</specialApr>
</term>
<term length="36">
<residualpct>42</residualpct>
<acquisition>0</acquisition>
<specialApr>0.0469</specialApr>
</term>
<term length="48">
<residualpct>36</residualpct>
<acquisition>0</acquisition>
<specialApr>0.0469</specialApr>
</term>
<term length="60">
<residualpct>31</residualpct>
<acquisition>0</acquisition>
<specialApr>0.0469</specialApr>
</term>
</mileage>
<tradingDollars>8250</tradingDollars>
</creditrating>
<semiMonthlyQualified>true</semiMonthlyQualified>
</lease>
<purchase>
<creditrating tier="STD">
<term length="24">
<standardApr>0.0499</standardApr>
<specialApr>0.0099</specialApr>
</term>
<term length="36">
<standardApr>0.0499</standardApr>
<specialApr>0.0099</specialApr>
</term>
<term length="48">
<standardApr>0.0499</standardApr>
<specialApr>0.0099</specialApr>
</term>
<term length="60">
<standardApr>0.0499</standardApr>
<specialApr>0.0199</specialApr>
</term>
<term length="72">
<standardApr>0.0499</standardApr>
<specialApr>0.0199</specialApr>
</term>
<term length="84">
<standardApr>0.0499</standardApr>
<specialApr>0.0299</specialApr>
</term>
<tradingDollars>2500</tradingDollars>
</creditrating>
<semiMonthlyQualified>true</semiMonthlyQualified>
</purchase>
<cashDiscountNonStack>
<amount>9000</amount>
</cashDiscountNonStack>
<loyaltyAmount/>
<shortTermCampaign>
<campaign/>
</shortTermCampaign>
<defaultTerms>
<financeTerm>84</financeTerm>
<leaseTerm>60</leaseTerm>
<mileage>24</mileage>
</defaultTerms>
</data>
<errors/>
</result>
Will be transformed to
id
data = ''
data.lease = ''
data.lease.creditrating = ''
data.lease.creditrating.@tier
data.lease.creditrating.mileage.>0 = ''
data.lease.creditrating.mileage.>0.@code
data.lease.creditrating.mileage.>0.term.>0 = ''
data.lease.creditrating.mileage.>0.term.>0.@length
data.lease.creditrating.mileage.>0.term.>0.residualpct
data.lease.creditrating.mileage.>0.term.>0.acquisition
data.lease.creditrating.mileage.>0.term.>0.specialApr
data.lease.creditrating.mileage.>0.term.>1 = ''
data.lease.creditrating.mileage.>0.term.>1.@length
data.lease.creditrating.mileage.>0.term.>1.residualpct
data.lease.creditrating.mileage.>0.term.>1.acquisition
data.lease.creditrating.mileage.>0.term.>1.specialApr
data.lease.creditrating.mileage.>0.term.>2 = ''
data.lease.creditrating.mileage.>0.term.>2.@length
data.lease.creditrating.mileage.>0.term.>2.residualpct
data.lease.creditrating.mileage.>0.term.>2.acquisition
data.lease.creditrating.mileage.>0.term.>2.specialApr
data.lease.creditrating.mileage.>0.term.>3 = ''
data.lease.creditrating.mileage.>0.term.>3.@length
data.lease.creditrating.mileage.>0.term.>3.residualpct
data.lease.creditrating.mileage.>0.term.>3.acquisition
data.lease.creditrating.mileage.>0.term.>3.specialApr
data.lease.creditrating.mileage.>1 = ''
data.lease.creditrating.mileage.>1.@code
data.lease.creditrating.mileage.>1.term.>0 = ''
data.lease.creditrating.mileage.>1.term.>0.@length
data.lease.creditrating.mileage.>1.term.>0.residualpct
data.lease.creditrating.mileage.>1.term.>0.acquisition
data.lease.creditrating.mileage.>1.term.>0.specialApr
data.lease.creditrating.mileage.>1.term.>1 = ''
data.lease.creditrating.mileage.>1.term.>1.@length
data.lease.creditrating.mileage.>1.term.>1.residualpct
data.lease.creditrating.mileage.>1.term.>1.acquisition
data.lease.creditrating.mileage.>1.term.>1.specialApr
data.lease.creditrating.mileage.>1.term.>2 = ''
data.lease.creditrating.mileage.>1.term.>2.@length
data.lease.creditrating.mileage.>1.term.>2.residualpct
data.lease.creditrating.mileage.>1.term.>2.acquisition
data.lease.creditrating.mileage.>1.term.>2.specialApr
data.lease.creditrating.mileage.>1.term.>3 = ''
data.lease.creditrating.mileage.>1.term.>3.@length
data.lease.creditrating.mileage.>1.term.>3.residualpct
data.lease.creditrating.mileage.>1.term.>3.acquisition
data.lease.creditrating.mileage.>1.term.>3.specialApr
data.lease.creditrating.tradingDollars
data.lease.semiMonthlyQualified
data.purchase = ''
data.purchase.creditrating = ''
data.purchase.creditrating.@tier
data.purchase.creditrating.term.>0 = ''
data.purchase.creditrating.term.>0.@length
data.purchase.creditrating.term.>0.standardApr
data.purchase.creditrating.term.>0.specialApr
data.purchase.creditrating.term.>1 = ''
data.purchase.creditrating.term.>1.@length
data.purchase.creditrating.term.>1.standardApr
data.purchase.creditrating.term.>1.specialApr
data.purchase.creditrating.term.>2 = ''
data.purchase.creditrating.term.>2.@length
data.purchase.creditrating.term.>2.standardApr
data.purchase.creditrating.term.>2.specialApr
data.purchase.creditrating.term.>3 = ''
data.purchase.creditrating.term.>3.@length
data.purchase.creditrating.term.>3.standardApr
data.purchase.creditrating.term.>3.specialApr
data.purchase.creditrating.term.>4 = ''
data.purchase.creditrating.term.>4.@length
data.purchase.creditrating.term.>4.standardApr
data.purchase.creditrating.term.>4.specialApr
data.purchase.creditrating.term.>5 = ''
data.purchase.creditrating.term.>5.@length
data.purchase.creditrating.term.>5.standardApr
data.purchase.creditrating.term.>5.specialApr
data.purchase.creditrating.tradingDollars
data.purchase.semiMonthlyQualified
data.cashDiscountNonStack = ''
data.cashDiscountNonStack.amount
data.loyaltyAmount
data.shortTermCampaign = ''
data.shortTermCampaign.campaign
data.defaultTerms = ''
data.defaultTerms.financeTerm
data.defaultTerms.leaseTerm
data.defaultTerms.mileage
errors
When XML structure is simple DB_result field names should contain XmlNode names. For example:
<car>
<engine>Diesel</engine>
<transmission>Automatic</automatic>
</car>
---
String query = """
SELECT
db_engine AS "engine",
db_transmission AS "transmission"
FROM db_cars;
""";
When XML structure is bit simple, use "." symbol to separate elements in DB naming:
<car>
<engine>Diesel</engine>
<transmission>Automatic</transmission>
<colors is_primary="3">
<color1>black</color1>
<color2>red</color2>
<color3>green</color3>
</colors>
</car>
---
String query = """
SELECT
db_engine AS "engine",
db_transmission AS "transmission",
db_is_primary AS "is_primary",
db_color1 AS "colors.color1",
db_color2 AS "colors.color2",
db_color3 AS "colors.color3"
FROM db_cars;
""";
When XML structure contains elements with same name, use ">[X]" notation to distinguish elements:
<car>
<engine>Diesel</engine>
<transmission>Automatic</transmission>
<colors is_primary="3">
<color val="black"/>
<color val="red"/>
<color val="green"/>
</colors>
</car>
---
String query = """
SELECT
db_engine AS "engine",
db_transmission AS "transmission",
db_is_primary AS "is_primary",
db_color1 AS "colors.color.>0.val",
db_color2 AS "colors.color.>1.val",
db_color3 AS "colors.color.>2.val"
FROM db_cars;
""";
Sometimes it is impossible (DB doesn't have all fields) or even unnecessary to compare all XML to DB. In this case some fields has to be omitted during comparison. There are couple ways to do that.
- All fields that starts with underscore "_" character are ignored from comparison. This has been done for passing parameters from DB result to [Included Queries][#includedDb]. Probably later, property to control ignorable prefix will be added.
- All fields that return NULL, empty string or 1 space character.
This is necessary for handling nodes that can be empty in some cases. For example:
- subNode1 - expected to have value always - leave as is
- subNode2 - node must appear in structure even it doesn't have value.
NOTE! OracleDB doesn't allow to return empty string. It is automatically converted to
NULL. That's why we must have specific case for OracleDB. Probably later, property to control empty string will be added. - subNode3 - must be deleted from XML structure if value is
fake_value
<node>
<subNode1>subVal1</subNode1>
<subNode2>subVal2</subNode2>
<subNode3>subVal3</subNode3>
</node>
<node>
<subNode1>subVal1</subNode1>
<subNode2></subNode2>
</node>
As you can see, we have 2 node elements, with subset of internal nodes.
For second node, subNode2 and subNode3 doesn't have values. But XML handle this situation differently.
subNode2 exists but is empty. subNode3 doesn't exist. To handle that case with SQL:
SELECT
db_val1 AS "subNode1",
IFNULL(db_val2, ' ') AS "subNode2",
IF(db_val3 = 'fake_value', ' ', db_val3) as "subNode3"
FROM Table;
To do that, you have to:
- Set ignorable value, or
RegExp(default value is "ignored")
AbstractDiffHelper diffHelper = new DbDiffHelper(xml, rows);
diffHelper.ignoredValue = "ignored";
- Return ignorable value from DB:
SELECT
'ignored' AS SOME_COMPLICATE_FIELD
FROM Table;
It is possible to use DB as reference to fields/XML elements that has to be compared
AbstractDiffHelper diffHelper = new DbDiffHelper(xml, rows);
diffHelper.skipMissedDb = true;
For example (comparison will pass, as "colors" nodes are ignored):
<car>
<engine>Diesel</engine>
<transmission>Automatic</automatic>
<colors is_primary="3">
<color val="black"/>
<color val="red"/>
<color val="green"/>
</colors>
</car>
---
String query = """
SELECT
db_engine AS "engine",
db_transmission AS "transmission"
FROM db_cars;
""";
It is possible to use XML as reference to fields/XML elements that has to be compared
AbstractDiffHelper diffHelper = new DbDiffHelper(xml, rows);
diffHelper.skipMissedXml = true;
For example (comparison will pass, as "trash" DB field is ignored):
<car>
<engine>Diesel</engine>
<transmission>Automatic</automatic>
</car>
---
String query = """
SELECT
db_engine AS "engine",
db_transmission AS "transmission",
db_id AS "trash"
FROM db_cars;
""";
Sometimes it is hard to obtain all results from DB in same query. Especially, when number of elements differs from XML to XML in provided list. For example:
<car id="1">
<engine>Diesel</engine>
<transmission>Automatic</automatic>
<colors is_primary="3">
<color val="black"/>
<color val="red"/>
<color val="green"/>
</colors>
</car>
<car id="2">
<engine>Gas</engine>
<transmission>Manual</automatic>
<colors is_primary="4">
<color val="puprple"/>
<color val="pink"/>
<color val="blue"/>
<color val="grey"/>
</colors>
</car>
In real life, you will query DB something like:
SELECT
db_engine AS "engine",
db_transmition AS "transmition",
db_color AS "color"
FROM cars
INNER JOIN colors ON cars.car_id = colors.color_id
As result, you will have list of car-color pairs. Same query can be replaced with another 2 queries:
SELECT
db_engine AS "engine",
db_transmition AS "transmition"
FROM cars;
---
SELECT db_color
FROM colors
WHERE car_id = :car_id;
Same thins is proposed to do automatically:
def xml = new XmlSlurper().parseText(new File('/path/to/file').text).toList();
Sql sql = Sql.newInstance(connectionProperties);
String query = """
SELECT
db_engine AS "engine",
db_transmition AS "transmition",
db_car_id AS "_car_id"
FROM cars;
""";
List<GroovyRowResult> rows = sql.rows();
AbstractDiffHelper diffHelper = new DbDiffHelper(xml, rows);
diffHelper.sql = sql;
diffHelper.subQueryFromFile = false;
diffHelper.includedNodes = [
'colors' : 'SELECT db_color FROM colors WHERE car_id = :_car_id;'
];
As result, system will search colors in DB for every upper level DB_result automatically.
Rows will be matched by _car_id field. Even so you can match SQLs by any field returned by Primary query,
it might be necessary to have additional fields in result. Mark such fields with underscore "_" prefix.
System will skip such field during comparison, but you can use it for SQL linking.
Also, you can provide filePath to file on disk that contains SQL query (this is default behavior).
diffHelper.sql = sql;
diffHelper.subQueryFromFile = true; // can be ommited
diffHelper.includedNodes = [
'colors' : 'path/to/file'
];