1330 Uniface tests against the real database - and the file doesn't change by a single byte

The customer app from the series Building a customer app in Uniface 10 has one SQLite file, customers.db. There is no separate test database. All 34 test services - 1330 checks at the moment - run against exactly that file.

That sounds reckless, so here is how it is kept safe, and the one number that tells me after every run that it was: the MD5 checksum of the database file is the same before and after the suite.

Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) and the SQLite version of its SLE driver (3.50.2), October 2026.

Why not a separate test database?

Because the interesting bugs were in the schema, not in the data. Every stage of the app added tables, indexes and settings to customers.db. A second database would have to follow every migration, and the day it doesn't, the tests are green against a schema nobody uses. Testing against the real file means the tests also check the migration.

The price is that a test must never leave anything behind. Three rules take care of that.

Rule 1: Test data lives in reserved ranges

Every test service gets its own block of customer numbers and its own markers:

Marker	Used for
Customer IDs 998600–998699	customers, documents, tickets, opportunities of the current stage
SKU prefix TS23-	products
user names tst_…	logins, and the CHANGED_BY/CREATED_BY columns of everything the tests write
warehouse codes TW…	warehouses

Real customer numbers come from the app's number range, which is far away from 998xxx, and by convention no real user name starts with tst_. That makes the cleanup a set of plain DELETE statements without any guessing.

Rule 2: Clean up before and after

Every test service starts and ends with the same entry:

public operation RUN_ALL
variables
    numeric vTests, vFailures
    string vError
endvariables
    vTests = 0
    vFailures = 0
    putmess "Feature 30 tests started"
    call CLEANUP
    activate "USER_SVC".LOGOUT()
    call AS_RUNNER
    call SETUP_DATA
    call AS_RUNNER
    call TEST_DQ(vTests, vFailures)
    ...
    activate "USER_SVC".LOGOUT()
    call CLEANUP
    rollback
    putmess $concat("Feature 30 tests: ", vTests, " tests, ", vFailures, " failures")
    if (vFailures > 0)
        return -1
    endif
    return 0
end

CLEANUP at the start removes leftovers of a run that was interrupted. CLEANUP at the end removes what this run created. It deletes children before parents, so it also works on a database with foreign keys switched on:

entry CLEANUP
    sql "DELETE FROM TICKET_COMMENT WHERE TICKET_ID IN (SELECT TICKET_ID FROM TICKET WHERE CUSTOMER_ID BETWEEN 998600 AND 998699)", "CUSTOMERS"
    sql "DELETE FROM TICKET WHERE CUSTOMER_ID BETWEEN 998600 AND 998699", "CUSTOMERS"
    ...
    sql "DELETE FROM PAYMENT WHERE DOC_ID IN (SELECT DOC_ID FROM SALES_DOC WHERE CUSTOMER_ID BETWEEN 998600 AND 998699)", "CUSTOMERS"
    sql "DELETE FROM SALES_LINE WHERE DOC_ID IN (SELECT DOC_ID FROM SALES_DOC WHERE CUSTOMER_ID BETWEEN 998600 AND 998699)", "CUSTOMERS"
    sql "DELETE FROM SALES_DOC WHERE CUSTOMER_ID BETWEEN 998600 AND 998699", "CUSTOMERS"
    sql "DELETE FROM PRODUCT WHERE SKU LIKE 'TS23-%'", "CUSTOMERS"
    sql "DELETE FROM CUSTOMER WHERE CUSTOMER_ID BETWEEN 998600 AND 998699", "CUSTOMERS"
    sql "DELETE FROM APP_EXPORT_LOG WHERE USER_NAME LIKE 'tst_%'", "CUSTOMERS"
    sql "DELETE FROM APP_USER WHERE USER_NAME LIKE 'tst_%'", "CUSTOMERS"
    call AS_RUNNER
    return 0
end

The real entry has about 80 statements, one per table the tests touch. It grew with every stage, and that is the main maintenance cost of this approach: a new table that the tests write to needs a new line here, or the next run fails on a duplicate key.

Rule 3: Nothing is committed

The last statement before the summary is rollback. The whole test run is one transaction that never gets committed. If everything works, the DELETEs of the final cleanup are not even necessary - the rollback undoes the inserts anyway.

So why clean up at all? Because a rollback only undoes what has not been committed yet, and the app has operations that commit on purpose. One of them is the safe backup: before it copies the database file, it writes all pending changes so the copy is complete.

public operation SAFE_BACKUP
    ...
    commit
    vStatus = $status
    if (vStatus < 0)
        pError = $concat("Open changes could not be saved before the backup (error ", $procerror, ").")
        return -1
    endif

If a test calls such an operation, everything up to that point is in the file for good, and only the cleanup gets rid of it. The two safety nets cover each other.

That backup is also the one place where I gave up on testing the real thing: the new job scheduler can run a BACKUP job, but the test only creates that job inactive and checks that it is listed. Running it would commit in the middle of the test run.

The proof: one checksum

After a full run I compare the checksum of the database file with the value from before:

md5sum customers.db
f0da0851db8075c05d7a3edef94e7b20  customers.db

On Windows, Get-FileHash customers.db -Algorithm MD5 gives the same value. It changes once per stage - when the schema is extended - and the new value goes into the project notes. If a test run ever changes it, something was committed during the run - even if the cleanup removed the rows again, the file would not be byte-identical any more. Either way I want to know.

A checksum is a blunt tool. It can't tell me what changed. But it is cheap and it covers every table, including the ones nobody thought of when writing the cleanup.

Files are data too

The database was clean, but a test still failed for a reason outside it. One import test wrote products28.csv with a deliberately wrong header (Code;Name) to check that the import rejects it. A later rights test reused the same file name and expected a successful dry run - and got the "wrong header" error.

The fix was to give that test its own valid file. The lesson: test files in the export folder need the same discipline as test rows. One file name per purpose, and never rely on what another test left in a file.

What it costs

• A long cleanup entry that must grow with the schema.
• Operations that commit internally can't be tested end-to-end in the same run.
• A real database means real data could be in the way. Global figures in the tests are therefore measured as differences: read the cockpit, insert the test data, read it again, compare the delta.

    activate "KPI2_SVC".COCKPIT("", vBefore, vError)
    ... insert one invoice, one ticket, one opportunity ...
    activate "KPI2_SVC".COCKPIT("", vAfter, vError)
    call DIFF(vBefore, vAfter, "OPEN_AR", vAr)
    call CHECK("COCKPIT sums sales, purchasing, stock, service and CRM", (vAr = 10000 & ...), ...)

What it gives

Every stage ends with one command that runs all 34 test services:

===== FEAT29_TST_SVC: OK =====
===== FEAT30_TST_SVC: OK =====
===== All test services: 34 suites, all passed =====

and one checksum that is the same as before. Together they say: the code works with the schema that is actually in the file, and the file is exactly as it was.

Takeaways

Reserve ranges for test data that real data can never reach, and make every cleanup a plain DELETE on those ranges.

Clean up before and after, and still end with rollback - the two nets catch different failures.

Know which operations commit. They can't be tested in the same transaction, and they are the reason the cleanup exists.

Check the database file's checksum after the run. It is crude, and that is the point.

Treat test files like test rows: one name per purpose.