Building a SQL statement test case This probably requires that you're using the "autoconf" build system at the moment. This is the case if you're running on Linux or some other Unix variant, including Mac OS X. It is the case for some kinds of Windows builds too (using MinGW / MSYS). I assume that you're mostly OK with building from fossil. If not, start at http://www.gaia-gis.it/gaia-sins/about-fossil.html. There are detailed build instructions linked off http://www.gaia-gis.it/gaia-sins/ for each major platform. Make sure you have all the dependencies. It is a bit of work but it will pay off in the long run. Once you've got that far, its time to start with the testing. Step 1. Just get the tests building. If you're in the build directory (i.e. where you ran the "./configure" and "make" steps), you should be able to run "make check" and have the tests build and run. Towards the end, it should report something like: ============================================================================ Testsuite summary for libspatialite 4.3.0-devel ============================================================================ # TOTAL: 83 # PASS: 83 # SKIP: 0 # XFAIL: 0 # FAIL: 0 # XPASS: 0 # ERROR: 0 ============================================================================ If any of them fail, please let us know ASAP. Step 2. After that works, its time to set up the coverage testing. Obviously we want to understand which code isn't being tested, then write a test to check that code, and finally make sure that the code is being tested. Fortunately, there are nice tools to check which code is being run. On Unix / Linux, you should have no problem obtaining "gcov" and "lcov" - they're probably available using your package / build system. Clean up the build: make distclean and then run configure with the --enable-gcov=yes option: ./configure --enable-gcov=yes You can add in any other options for configure at the same time (just as you did for the initial build). Then build as normal: make check You don't need to install (and probably don't want to install this version). Then zero out the coverage numbers: make coverage-init and build the tests: make check and finally do the coverage summary: make coverage At the end, you should see something like: Writing directory view page. Overall coverage rate: lines......: 64.9% (64946 of 100079 lines) functions..: 16.4% (218 of 1329 functions) branches...: 8.2% (1929 of 23468 branches) Step 3. Find some untested code. The results of the testing are converted into nice HTML, which is in the covresults/ directory. Point your web browser at covresults/index.html. Untested functions are the most important, then untested branches. Don't worry too much about untested lines. The colour coding should help. Step 4. Decide to do something about it. This is really the only hard part. Step 5. Think about how to test the code using SQL. Lets say you notice that the fnct_math_floor() function isn't being tested. [It is in src/spatialite/spatialite.c if you're looking for it]. Now further inspection shows its being mapped to the SQL floor function using: sqlite3_create_function (db, "floor", 1, SQLITE_ANY, 0, fnct_math_floor, 0, 0); So something like "SELECT floor(3.2);" would give us a test case. However libspatialite doesn't include the command line tools, so we have a simple test harness in the test directory called "check_sql_stmt". It basically loads test cases from a directory (test/sql_stmt_tests/). Any file ending with .testcase will be checked. A quick overview of the format: * anything after a # character is a comment. Line 1: the name of the test Line 2: the database file to use. ":memory:" is usually the best option, but you can load a pre-existing database if you need source data. Line 3: the SQL to run. Line 4: the number of rows of results you're expecting (not including the header row) Line 5: the number of columns of results you're expecting. remaining lines: the results, one line per cell. So lets write such a file: floor(3.2) # you can use the SQL if you're not feeling imaginative. :memory: SELECT floor(3.2); 1 # rows 1 # columns floor(3.2) # this is the header row 3.0 # this is really the result and save it (in test/sql_stmt_tests/ - I called mine floor32.testcase, but you can use anything that doesn't already exist and ends with .testcase). Now when you run "make check" you should see your new test being run, marked by an entry just above the PASS: check_sql_stmt that looks like: Test case: floor(3.2) [It won't change the total number of test cases reported by "make check" because that is just the number of unique programs that were run, and this is an extra test file within an existing program] Step 6. Re-run the coverage programs: make coverage-init && make check && make coverage and check the results with the browser. You should now see some green lines in fnct_math_floor. However you might notice that there are still some red lines (and red - and # marks on the left side, which indicate branches not taken). We can see that we're testing the case where we have SQLITE_FLOAT input, but not integer or some other inputs. Step 7. Write another test file: floor(integer) # integer input - this is just the name remember :memory: SELECT floor(3); # the SQL to run 1 # rows 1 # columns floor(3) # this is the header row 3.0 # this is really the result Save it, again in test/sql_stmt_tests/ - I called mine floorint.testcase, but anything will do. Then run the tests again: make check and you should see your new test case as well: Test case: floor(3.2) Test case: floor(integer) Step 8. Repeat the coverage program runs: make coverage-init && make check && make coverage and check the results with the browser. You should now see even more green lines in fnct_math_floor. However there is still the case for what happens if the input is neither integer nor float. For example, what happens if we put in a test entry. Step 9. Write another test file. floor(null) # text input, expecting null output :memory: SELECT floor("some text"); # the SQL to run 1 # rows 1 # columns floor("some text") # this is the header row (NULL) # this is really the result - special case for null value and do the save / check / check coverage things again. You should see pretty much all of that function is now being tested. Step 10. Send the results to be added to libspatialite! You can make a svn diff output if you're familiar with SVN, but you can just send the new files that you're written (e.g. as a tar.gz or zip file) to me (bradh@frogmouth.net) or to the spatialite-users mailing list. Hints: 1. If your test results in a null value, use "(NULL)" as the expected cell value. 2. If your test result is a floating point value, and it might vary at the least significant values, then you can make the test more reliable by limiting how close things need to be. You do this by adding a : character, then the number of characters to compare. So if you're looking for 2.7182818, but the result could vary a bit, then write the result as 2.7182818:8. If you need to put a colon (:) character in the value, you can use :0 at the end, to indicate that the precision should be "absolute match". 3. If you want to run the tests under valgrind, change into the test/ directory and use: libtool --mode=execute valgrind --track-origins=yes --tool=memcheck --num-callers=20 --leak-check=full --show-reachable=yes ./check_sql_stmt 4. Don't worry about the not-taken branch for GAIA_UNUSED(). This is just to avoid a warning, and isn't a problem. 5. Don't worry about not being able to write a test for every possible scenario. Even a little extra coverage helps. 6. If your test shows a problem, think twice before fixing the test to match the code. It could be that you've just found a bug, and that is really important news - we have a reliable way to reproduce the problem, and a way to make sure it doesn't sneak back in later. Please raise these issues separately. 7. You can "hold" failing test cases by renaming the file (I usually add ".on-hold", so pow00.testcase becomes pow00.testcase.on-hold). This allows you to keep testing without loosing the problem test cases. You can use that to "remove" the test cases for fnct_math_floor() if you want to reproduce this.