Sort option in compare resultset?

[Unforgettable631]

Before I'll make something for our project I just want to make sure it isn't covered already in the existing jdbcSlim code :). I read the manual and scanned the sources.

Situation
We're using two different databases (Oracle & Sap Hana). We have a testcase querying both of them and the result has to be compared. Both databases have a different algorithm implemented for order by sorting (one binary sort and the other I forgot). Unfortunately adjusting this implementation is not an option (at the moment).

The problem here is the outcome of the result sheets. Because the ordering differs from each other, it will be marked as differences (while in fact there are no differences in this case).

Grep resultsheets:

$RSSource<-[[[sourcetable<, targettable<], .....[CRG_EXG_TEXT, A1_RANDOM], [CRG_EXG_Z, A2_RANDOM], [CRG_0AC_TEXT, A1_RANDOM], [CRG_0ASSET_TEXT, A2_RANDOM], .....]]

$RSTarget<-[[[SOURCETABLE, TARGETTABLE], .....[CRG_0AC_TEXT, A1_RANDOM], [CRG_0ASSET_TEXT, A2_RANDOM], [CRG_EXG_TEXT, A1_RANDOM], [CRG_EXG_Z, A2_RANDOM], .....]]


|Table:SheetEcho| $RSDiff|
|sourcetable<|targettable<|
|.....many records above|.....many records above|
|[+]CRG_0AC_TEXT|[+]A1_RANDOM|
|[+]CRG_0ASSET_TEXT|[+]A2_RANDOM|
|CRG_EXG_TEXT|A1_RANDOM|
|CRG_EXG_Z|A2_RANDOM|
|[-]CRG_0AC_TEXT|[-]A1_RANDOM|
|[-]CRG_0ASSET_TEXT|[-]A2_RANDOM|
|many records below...|many records below...|

I would like to see there is no difference, because all 4 rows are present in the resultsheet.
Testpage looks something like:

!define source_query {select a.table as "sourcetable<", b.table as "targettable<" from db 1 order by 1,2}
!define target_query {select c.table as sourcetable, d.table as targettable  from db 9 order by 1,2}

!|Define Properties|Source                     |
|key               |value                      |
|jdbcDriver        |SQLDriver                  |
|DBURL             |${connection_url}          |
|DBUSER            |user                       |
|.keyStoreLocation |keystore                   |
|DBPASSWORD        |password                   |
|#DBAUTOCOMMIT     |true                       |
|CMD               |${source_query}            |
|query             |true                       |

!|Define Properties|Target                     |
|key               |value                      |
|jdbcDriver        |SQLDriver                  |
|DBURL             |${connection_url}          |
|DBUSER            |user                       |
|.keyStoreLocation |keystore                   |
|DBPASSWORD        |password                   |
|#DBAUTOCOMMIT     |true                       |
|CMD               |${target_query}            |
|query             |true                       |

!|Script   |SQLCommand|Source|
|open connection             |
|execute                     |
|$RSSource=|resultSheet      |
|close connection            |

!|Script   |SQLCommand  |Target   |
|open connection                  |
|execute                          |
|$RSTarget=|resultSheet           |
|$RSDiff=  |compareSheet|$RSSource|
|close connection                 |


!|Table:SheetEcho|$RSDiff|

I know if we could use 'SQLCommand | db | query| outputparameter SORT|' outcome of the sorting is for both results the same.

Workaround could be defining the sql statement better, but do you have any advice in this?

[six42]

Hi unforgettable631, Thanks for using jdbcSlim. It might work that you add the SORT option to the target properties. If this doesn't works the sort option must be refactored to be able to be called directly. It is this code: if(sort ){ // Sort the actual values except the header line //Create a copy to not change the data of the calling function List<List<String>> tmp = new ArrayList<List<String>>( actual); tmp.remove(0); StringListComparator sortComparator = new StringListComparator(actualSortKeys); Collections.sort( tmp, sortComparator); tmp.add(0,actualHeader); actual = tmp; } It is currently embedded in the function compareTableWithSort In the file: https://github.com/six42/jdbcslim/blob/master/src/main/java/six42/fitnesse/jdbcslim/SheetFixture.java

[Unforgettable631]

Hi six42,

Thanks for your reply. I forgot to mention I tried the sort option in both (source & target) properties but unfortunately it had not the result I was hoping for.

Fyi, we're very happy that jdbcSlim exists in the way it's already implemented. We've added some more plugins and other fixtures to use it in our BI-environment and tests are running via Jenkins every code commit (or scheduled at night for some environments). Now the testers are adding content (more tests) for QA.

I will look into the SheetFixture code tomorrow and let you now! Thanks so far.

[six42]

Thanks for the positive feedback and the details how you use it. Sounds you are mature in test automation and continuous delivery. I am happy to add any of your additions to jdbcSlim. I am sure others could benefit from it as well.

[Unforgettable631]

We are trying to, but we still have some challenges :).

I've done some debugging with a few scenario's and (I'm not sure!) it looks like if |sort|true| in the property file is defined in combination with keys defined in the query this code doesn't do the same in the scenario when giving this ouputparameter in the !|SQLCommand|propfile|query|SORT| is set? I could be very wrong for this.

[Unforgettable631]

After some more debugging I think I can pinpoint what is happening. The code for sorting isn't triggered (yet). This is the code which determines to get into compareTableWithSort or not:

        if (this.commandExecuter.success()) {
            if (!hasHeader) {
                result = this.commandExecuter.resultSheet();
            } else {
                // TODO If expected has a Header than check the order and values of the same
                result = this.compareTableWithSort(ParameterTable, this.commandExecuter.resultSheet(), this.commandExecuter.Properties().getProperty("sort") != null);
            }
In SheetFixture.java

If I provide the table header AND define the sort parameter it will hit the compareTableWithSort, but it can't find any SortKeys (because I didn't defined them) and sorting is not happening. F.e.:

!|Define Properties|testdb                  |
|key               |value                   |
|jdbcDriver        |some.Driver             |
|DBURL             |${var_connection_url}   |
|DBUSER            |${var_db_user}          |
|.keyStoreLocation |${var_keystore_location}|
|DBPASSWORD        |${var_db_passwd}        |
|#DBAUTOCOMMIT     |true                    |
|sort              |                        |
|CMD               |${query}                |
|query             |true                    |

!|SQLCommand|testdb|${query}|
|headerexample              |

We use it like the SideEffectCheck of jdbcSlim on http://rawgit.com/six42/jdbcslim/master/JdbcSlim.htm#JdbcSlim.UserGuide.2KeyBenefits.2TestingOnSideEffects and then you don't provide 'headers'.

Any suggestions how to approach this? Write some new code?