Hello:
I am a very new user of sqlunit, but experienced with junit.
I want to do something like the following
select count(*) from table where foo=bar
and I want to assert that the count must be greater than zero.
Is there a simple way to do this?
thanks in advance!
--Craeg
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Since the assert works at the result level, an assertGreaterThan is kind of meaningless there, since we are essentially asserting something at the column level.
However, you can do this with matchers. Such a matcher does not exist yet, but it is quite simple to write a LessThanMatcher and a GreaterThanMatcher which will return true if the generated value is less or greater than the specified value.
I will build these Matchers for you and check them in sometime by Monday evening, and update the thread.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sounds good!
I assume usage would be sthing like the following, for example:
create a diff that compares the result of my sql statement to
a column value of 0 with a matcher of assertGreaterThan.
Is that right?
I think having greater than and less than matchers is a good idea, and thanks very much for adding them! I will certainly be using them both quite a bit.
However I am wondering if there is a more direct way of handling a particular case of a zero to one relationship in the database, where you want to assert that the associated thing does exist.
Basically what I am asking for is an existential quantifier: "there exists a foo, such that [complex SQL follows]"
If the foo does not exist, there will be zero rows returned. Is there a quick and easy way in SQLUnit to assert that there must be at least one row returned, without caring anything about *what* is returned?
BTW, providing the converse capability would probably take care of the universal quantifier: "for all foo, make sure [complex SQL condition]" You could structure the SQL to return only those foos for which the [complex SQL condition] does not hold, and then use the "assert we didn't get any rows returned" to display a failure message with the offending foos.
**aside**
In case you are wondering, I am using SQL Unit to create a validation suite to automatically validate that all of the data within a particular database instance is consistent with a set of business rules. The business rules are captured in the middle tier and are extremely complex. Unfortunately, our deployment uses bulk copy tools and combines data streams from multiple sources plus patches, etc. which often results in dirty data. I plan to implement a "data doctor" in sqlunit to "diagnose" unhealthy data conditions. Once I have some rules captured, the next step for me will be to put together some nice XML/XSLT based reporting.
I wonder if anyone else is using SQLunit in this way...?
**end aside**
Thanks for answering the questions of a sqlunit newbie, and especially for creating such a useful tool!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Rather than adding separate LessThan and GreaterThan matchers, I decided to write an ExpressionMatcher that could take any JEXL expression. The nice thing about this is that we dont have to write matchers for each and every little operation. We can also do regex matches with this, although its not a directly supported operation.
I have some examples I used to test this matcher with my mock testing framework here (last 3 tests):
test/mock/matchertests.xml
You will need to check out the following files (note the version number, its available in public cvs servers about a day after I actually check it in, which is going to be now):
Checking in docs/sqlunit-book.xml;
/cvsroot/sqlunit/sqlunit/docs/sqlunit-book.xml,v <-- sqlunit-book.xml
new revision: 1.94; previous revision: 1.93
done
RCS file: /cvsroot/sqlunit/sqlunit/lib/commons-jexl-1.0.jar,v
done
Checking in lib/commons-jexl-1.0.jar;
/cvsroot/sqlunit/sqlunit/lib/commons-jexl-1.0.jar,v <-- commons-jexl-1.0.jar
initial revision: 1.1
done
RCS file: /cvsroot/sqlunit/sqlunit/lib/commons-logging.jar,v
done
Checking in lib/commons-logging.jar;
/cvsroot/sqlunit/sqlunit/lib/commons-logging.jar,v <-- commons-logging.jar
initial revision: 1.1
done
RCS file: /cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/matchers/ExpressionMatcher.java,v
done
Checking in src/net/sourceforge/sqlunit/matchers/ExpressionMatcher.java;
/cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/matchers/ExpressionMatcher.java,v <-- ExpressionMatcher.java
initial revision: 1.1
done
Checking in test/mock/coretests.xml;
/cvsroot/sqlunit/sqlunit/test/mock/coretests.xml,v <-- coretests.xml
new revision: 1.21; previous revision: 1.20
done
RCS file: /cvsroot/sqlunit/sqlunit/test/mock/matchertests.xml,v
done
Checking in test/mock/matchertests.xml;
/cvsroot/sqlunit/sqlunit/test/mock/matchertests.xml,v <-- matchertests.xml
initial revision: 1.1
done
> Is there a quick and easy way in SQLUnit to assert that there must be at least one row returned, without caring anything about *what* is returned?
You can set the rowcount attribute of the resultset to a value, say 1 or 0, and not specify the actual result, to indicate that only the rowcount should be matched. For examples, look at test/mock/*.xml and look for rowcount. Would this serve your purpose?
> In case you are wondering...
Given what you are doing, you may also want to take a look at the diff tag, it compares resultsets from different sql statements.
> Thanks for answering the questions of a sqlunit newbie, and especially for creating such a useful tool!
I think the usefulness of the SQLUnit tool and its many features are a testament to the power of the open source process. Had it not been for the many suggestions, feature requests and bug fixes that I have gotten from people such as yourself over the last couple of years, SQLUnit would still have been stuck in version 1.0 :-).
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello:
I am a very new user of sqlunit, but experienced with junit.
I want to do something like the following
select count(*) from table where foo=bar
and I want to assert that the count must be greater than zero.
Is there a simple way to do this?
thanks in advance!
--Craeg
Hi Craeg,
Since the assert works at the result level, an assertGreaterThan is kind of meaningless there, since we are essentially asserting something at the column level.
However, you can do this with matchers. Such a matcher does not exist yet, but it is quite simple to write a LessThanMatcher and a GreaterThanMatcher which will return true if the generated value is less or greater than the specified value.
I will build these Matchers for you and check them in sometime by Monday evening, and update the thread.
-sujit
Sounds good!
I assume usage would be sthing like the following, for example:
create a diff that compares the result of my sql statement to
a column value of 0 with a matcher of assertGreaterThan.
Is that right?
I think having greater than and less than matchers is a good idea, and thanks very much for adding them! I will certainly be using them both quite a bit.
However I am wondering if there is a more direct way of handling a particular case of a zero to one relationship in the database, where you want to assert that the associated thing does exist.
Basically what I am asking for is an existential quantifier: "there exists a foo, such that [complex SQL follows]"
If the foo does not exist, there will be zero rows returned. Is there a quick and easy way in SQLUnit to assert that there must be at least one row returned, without caring anything about *what* is returned?
BTW, providing the converse capability would probably take care of the universal quantifier: "for all foo, make sure [complex SQL condition]" You could structure the SQL to return only those foos for which the [complex SQL condition] does not hold, and then use the "assert we didn't get any rows returned" to display a failure message with the offending foos.
**aside**
In case you are wondering, I am using SQL Unit to create a validation suite to automatically validate that all of the data within a particular database instance is consistent with a set of business rules. The business rules are captured in the middle tier and are extremely complex. Unfortunately, our deployment uses bulk copy tools and combines data streams from multiple sources plus patches, etc. which often results in dirty data. I plan to implement a "data doctor" in sqlunit to "diagnose" unhealthy data conditions. Once I have some rules captured, the next step for me will be to put together some nice XML/XSLT based reporting.
I wonder if anyone else is using SQLunit in this way...?
**end aside**
Thanks for answering the questions of a sqlunit newbie, and especially for creating such a useful tool!
Hi Craeg,
Rather than adding separate LessThan and GreaterThan matchers, I decided to write an ExpressionMatcher that could take any JEXL expression. The nice thing about this is that we dont have to write matchers for each and every little operation. We can also do regex matches with this, although its not a directly supported operation.
I have some examples I used to test this matcher with my mock testing framework here (last 3 tests):
test/mock/matchertests.xml
The Java Extended expression language (JEXL) syntax is described here:
http://jakarta.apache.org/commons/jexl/reference/index.html
You will need to check out the following files (note the version number, its available in public cvs servers about a day after I actually check it in, which is going to be now):
Checking in docs/sqlunit-book.xml;
/cvsroot/sqlunit/sqlunit/docs/sqlunit-book.xml,v <-- sqlunit-book.xml
new revision: 1.94; previous revision: 1.93
done
RCS file: /cvsroot/sqlunit/sqlunit/lib/commons-jexl-1.0.jar,v
done
Checking in lib/commons-jexl-1.0.jar;
/cvsroot/sqlunit/sqlunit/lib/commons-jexl-1.0.jar,v <-- commons-jexl-1.0.jar
initial revision: 1.1
done
RCS file: /cvsroot/sqlunit/sqlunit/lib/commons-logging.jar,v
done
Checking in lib/commons-logging.jar;
/cvsroot/sqlunit/sqlunit/lib/commons-logging.jar,v <-- commons-logging.jar
initial revision: 1.1
done
RCS file: /cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/matchers/ExpressionMatcher.java,v
done
Checking in src/net/sourceforge/sqlunit/matchers/ExpressionMatcher.java;
/cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/matchers/ExpressionMatcher.java,v <-- ExpressionMatcher.java
initial revision: 1.1
done
Checking in test/mock/coretests.xml;
/cvsroot/sqlunit/sqlunit/test/mock/coretests.xml,v <-- coretests.xml
new revision: 1.21; previous revision: 1.20
done
RCS file: /cvsroot/sqlunit/sqlunit/test/mock/matchertests.xml,v
done
Checking in test/mock/matchertests.xml;
/cvsroot/sqlunit/sqlunit/test/mock/matchertests.xml,v <-- matchertests.xml
initial revision: 1.1
done
> Is there a quick and easy way in SQLUnit to assert that there must be at least one row returned, without caring anything about *what* is returned?
You can set the rowcount attribute of the resultset to a value, say 1 or 0, and not specify the actual result, to indicate that only the rowcount should be matched. For examples, look at test/mock/*.xml and look for rowcount. Would this serve your purpose?
> In case you are wondering...
Given what you are doing, you may also want to take a look at the diff tag, it compares resultsets from different sql statements.
> Thanks for answering the questions of a sqlunit newbie, and especially for creating such a useful tool!
I think the usefulness of the SQLUnit tool and its many features are a testament to the power of the open source process. Had it not been for the many suggestions, feature requests and bug fixes that I have gotten from people such as yourself over the last couple of years, SQLUnit would still have been stuck in version 1.0 :-).
-sujit