DEV Community: Franz

The Uniface test that "hung" had died - finding a silent crash with SQLite

Franz — Sat, 17 Oct 2026 09:25:17 +0000

This is a side story from the series Building a customer app in Uniface 10. It stands on its own: you only need to know that the app is a Uniface 10 desktop application on a SQLite database, and that every service has a test service written in ProcScript.

The short version: a test run stopped somewhere in the middle. It looked like a hang. It was a crash, and the log file pointed at the wrong place. The cause was a single SELECT without a FROM clause.

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.

The symptom

The feature in question is an ABC classification of customers: points for recent activities and complete data, and two limits from the settings table that decide between class A, B and C. The limits are configurable, so they have to be validated - a setting ABC_B = "abc" must not break the report.

The test suite for it is a normal ProcScript test service. It logs one PASS or FAIL line per check with putmess, prints a summary at the end and rolls back. Started with Compile & Test in the IDE, the run never printed its summary. The log stopped after a PASS line of the previous test group, and nothing came after it.

Two more things were wrong afterwards:

a test process was still listed by tasklist /FI "IMAGENAME eq ide.exe" and had to be ended with taskkill /PID <pid> /F,
next to the database there was a customers.db-journal file. SQLite rolls it back the next time the database is opened, so no data was lost - the tests always end with rollback anyway - but it shows that the process stopped in the middle of a transaction.

The wrong suspects

Because it looked like a hang, I first looked for something slow or blocking. Two candidates were in the test data setup of that group:

the test activities were inserted with one INSERT statement built on a common table expression (WITH ...),
the classification query had grown into one large statement.

I replaced the CTE insert with a loop over a small helper entry and rewrote the query with LEFT JOINs on grouped subqueries. Both changes were fine on their own. Neither changed anything: the run still stopped at the same PASS line.

That was the moment to stop guessing.

Why the log pointed at the wrong place

The log in the IDE's log folder is written in blocks, not line by line. When the process ends normally, everything is flushed. When it dies, the last block is lost. So the last line in the log was not the last line that ran - it was the last line that happened to be written before the crash. Everything after it, possibly several test groups, was simply missing.

The lesson is general: if a process dies, its buffered output lies by omission. The last thing you see is not the last thing that happened.

A trace that cannot be lost

The fix for the diagnosis was a trace that writes every line to disk immediately. In ProcScript, lfiledump/append does exactly that - it opens the file, appends, and closes it again:

entry TRACE
params
    string pText : IN
endparams
    lfiledump/append "%%(pText)%%^", "C:/Projects/customers/export/test/trace.txt"
    return 0
end

(The path is shortened here.) Then call TRACE("...") between the steps of the test - and, once the test steps were narrowed down, inside the service under test as well:

    call TRACE("ABC step 3 activities")
    activate "REPORT3_SVC".ABC_CLASS(999831, vClass, vPoints, vError)
    call TRACE("ABC step 4 first class")

This is slow and ugly, and it is only meant to live for a few runs. But it survives a crash. The trace of the second run ended like this:

ABC step 3 activities
R3 class enter
R3 thresholds enter
R3 thresholds settings 70 40
R3 thresholds before sql

So the process died inside the entry that reads and checks the two limits, right at its sql statement - and with the default values 70 and 40, not with a broken setting.

The statement

Here is the entry as it was:

entry THRESHOLDS
params
    string pA : OUT
    string pB : OUT
endparams
variables
    string vA, vB, vSql
endvariables
    activate "SETTINGS_SVC".GET_SETTING("ABC_A", "70", vA)
    activate "SETTINGS_SVC".GET_SETTING("ABC_B", "40", vB)
    call SQL_ESCAPE(vA)
    call SQL_ESCAPE(vB)
    vSql = "SELECT CASE WHEN '%%(vA)' <> '' AND '%%(vA)' NOT GLOB '*[^0-9]*' AND '%%(vB)' <> '' AND '%%(vB)' NOT GLOB '*[^0-9]*' AND CAST('%%(vA)' AS INTEGER) BETWEEN 2 AND 100 AND CAST('%%(vB)' AS INTEGER) BETWEEN 1 AND CAST('%%(vA)' AS INTEGER) - 1 THEN 1 ELSE 0 END"
    sql vSql, "CUSTOMERS"
    if ($status >= 0 & $result = 1)
        pA = vA
        pB = vB
    else
        pA = "70"
        pB = "40"
    endif
    return 0
end

The idea was to let SQLite do the validation in one go: digits only, A between 2 and 100, B below A. As plain SQL, the statement is valid - run through Python's sqlite3 module, it returns 1 for "70"/"40" and 0 for "70"/"abc", as intended. Through the sql statement of ProcScript and the SLE driver, it ended the process. No error status, no message, no log line.

What I did not do is isolate which part of it is responsible. The application runs other SELECTs without a FROM all the time, for example this one for the current time in milliseconds:

    sql "SELECT CAST((julianday('now') - 2440587.5) * 86400000 AS INTEGER)", "CUSTOMERS"

So "no FROM" alone is not the problem, and neither is CAST alone. The candidates left are the CASE expression, the GLOB with a character class, or the combination. If you know the SLE driver well enough to explain it, I would like to read that in the comments.

The fix: validation belongs in ProcScript

Instead of hunting for the exact trigger, I moved the validation to where it belongs anyway - into the code:

entry THRESHOLDS
params
    string pA : OUT
    string pB : OUT
endparams
variables
    string vA, vB
    numeric vNumA, vNumB
    boolean vOkA, vOkB
endvariables
    pA = "70"
    pB = "40"
    activate "SETTINGS_SVC".GET_SETTING("ABC_A", "70", vA)
    activate "SETTINGS_SVC".GET_SETTING("ABC_B", "40", vB)
    call TRIM_TEXT(vA)
    call TRIM_TEXT(vB)
    call IS_INT(vA, vOkA)
    call IS_INT(vB, vOkB)
    if (vOkA & vOkB)
        vNumA = vA
        vNumB = vB
        if (vNumA >= 2 & vNumA <= 100 & vNumB >= 1 & vNumB < vNumA)
            pA = vA
            pB = vB
        endif
    endif
    return 0
end

entry IS_INT
params
    string pText : IN
    boolean pOk : OUT
endparams
variables
    numeric vI, vLen
endvariables
    pOk = 0
    vLen = $length(pText)
    if (vLen < 1 | vLen > 3)
        return 0
    endif
    vI = 1
    while (vI <= vLen)
        if ($scan("0123456789", pText[vI:1]) = 0)
            return 0
        endif
        vI = vI + 1
    endwhile
    pOk = 1
    return 0
end

It is longer, but every line can be read and tested on its own. The defaults are set first, so every path that does not end in "valid" keeps 70 and 40. With this version the suite ran through - 61 tests - and the full run with all suites passed, with the database file unchanged afterwards.

The trace calls were removed again. They had done their job.

Takeaways

A run that stops is not necessarily a hang. Check whether the process is still alive before you look for something slow.

Buffered logs lose their tail when a process dies. Don't trust the last line. For a crash, write a trace with lfiledump/append, which appends and closes on every call.

Narrow down with the trace, then fix the cause you can prove. I could prove "this statement ends the process". I could not prove which part of it. That was enough to fix it.

Keep validation in ProcScript and use SQL for data. A clever one-line check in SQL saves ten lines of code - until the driver disagrees with it.

Look for leftovers. A journal file next to the database and a lingering process are both signs of a crash, not of a slow query.

Six ProcScript surprises from 60 Uniface services

Franz — Sat, 17 Oct 2026 09:23:28 +0000

Over the last two rounds of the series Building a customer app in Uniface 10, the app grew by about 60 service operations: pipelines, owners, contact rhythms, text templates, CSV import and export, statistics, notices, settings snapshots. Most of it was routine. These six things were not - each one cost a failed test or a wrong result before I understood it.

None of them is in the obvious places of the documentation, and none of them is specific to this app. If you write ProcScript against SQLite, you will probably meet some of them.

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.

1. One variable as input and output

The follow-up service creates the next follow-up of a repeating task and moves its due date to the next working day. The helper entry for that has an input and an output parameter:

entry NEXT_WORKDAY_ISO
params
    string pIso : IN
    string pNext : OUT
endparams
...
    pNext = ""
    call REAL_DATE(pIso, vDay)
    if (vDay = "" | vDay != pIso)
        return 0
    endif

The first version called it with the same variable on both sides, because "replace the date with the next working day" felt natural:

    call NEXT_WORKDAY_ISO(vNext, vNext)

The result was empty. The entry starts by clearing its output - a habit that makes every early return safe - and with one variable on both sides, that also cleared the input. As far as I can tell, the parameters of a call behave like references to the caller's variable here. A separate variable fixed it:

    call NEXT_WORKDAY_ISO(vNext, vWork)
    vNext = vWork

The same project has call SQL_LIT(vText, vText) in several places, and that one works - because SQL_LIT copies its input into a local variable before it writes the output. Whether such a call works depends on the order inside the entry, which is a bad thing to depend on. Use separate variables for IN and OUT.

2. Blanks at the end don't survive the round trip

A test for the e-mail normalization inserted a customer with the address " Ann@Example.COM " - blanks on both sides on purpose. The dry run must not change it, so the test read it back and compared:

    call GET_TEXT("SELECT EMAIL FROM CUSTOMER WHERE CUSTOMER_ID = 999876", vValue)
    call CHECK("...", (... & vValue = " Ann@Example.COM "), ...)

The check failed. The value that came back was " Ann@Example.COM" - the leading blank was still there, the trailing one was gone. I did not dig into whether it gets lost on the way in or on the way out; for the test it does not matter. The fixed check looks for what the dry run must preserve, the upper-case letters:

    call CHECK("EMAILS the dry run counts customers and contacts", (vStatus = 0 & vCount = 2 & $scan(vValue, "Ann@Example.COM") > 0), ...)

If trailing blanks matter to you - for example because you want to prove that a clean-up removes them - check them in SQL, where they are still there: SELECT COUNT(*) FROM CUSTOMER WHERE EMAIL <> TRIM(EMAIL). Don't compare trailing blanks after reading a value into ProcScript.

3. Two timestamps in a row are often the same

The "recently opened customers" list keeps the last ten customers per user, ordered by the time they were opened. Opening two customers directly after each other - which a test does - gave both the same OPENED_AT, down to the millisecond. The Windows system clock that SQLite's 'now' reads typically advances in steps of about 15 milliseconds, so "later" is not guaranteed to be "greater".

The fix makes the database guarantee the order: the new timestamp is the current time, or one millisecond after the newest entry of that user, whichever is greater:

    vSql = "INSERT OR REPLACE INTO APP_RECENT (USER_NAME, CUSTOMER_ID, OPENED_AT) SELECT '%%(vUser)', %%(pCustomerId), CASE WHEN m IS NULL OR n > m THEN n ELSE strftime('%Y-%m-%d %H:%M:%f', m, '+0.001 seconds') END FROM (SELECT strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime') AS n, (SELECT MAX(OPENED_AT) FROM APP_RECENT WHERE USER_NAME = '%%(vUser)') AS m)"
    sql vSql, "CUSTOMERS"

And every query that sorts by the timestamp gets a second sort key (ORDER BY OPENED_AT DESC, CUSTOMER_ID DESC), so that even equal values have a fixed order. Never let the order of events depend on the clock alone.

4. `lfiledump` with "UTF-8" writes a BOM

The CSV export writes its file with lfiledump vText, pFileName, "UTF-8". The first bytes of the file are EF BB BF - a byte order mark. Excel likes that for CSV. Many other consumers don't: a JSON parser may refuse it, and a checksum file with a BOM in front of the hash is no longer in the format sha256sum -c expects.

There is a switch for it:

    lfiledump/nobom pText, pFileName, "UTF-8"

The JSON export and the checksum files use /nobom, the CSV exports keep the BOM. Decide per file type, not once for the project.

5. Hashing a file the way `sha256sum` does

The data export writes a checksum file next to each export, so that a copy can be verified later - also without Uniface. That only works if the hash is computed over the bytes of the file, not over a decoded text. Loading the file as raw data does that:

entry FILE_HASH
params
    string pFileName : IN
    string pHash : OUT
    string pError : OUT
endparams
variables
    raw vData
endvariables
    pHash = ""
    pError = ""
    if ($lfileexists(pFileName) != 1)
        pError = $concat("The file ", pFileName, " does not exist.")
        return -1
    endif
    fileload/raw pFileName, vData
    if ($status < 0)
        pError = $concat("The file ", pFileName, " could not be read (error ", $procerror, ").")
        return -1
    endif
    pHash = $lowercase($encode("HEX", $encode("SHA256", vData)))
    return 0
end

The checksum file then gets one line in the usual format - hash, two blanks, file name - written with /nobom. I checked the result against sha256sum with a test file that contains umlauts, which is where a text-based hash would differ: both gave the same value. $lowercase is there because sha256sum prints lower case. For hashes, load /raw into a raw variable.

6. One SQL statement can end the process

This one gets its own article, because finding it took the longest: a SELECT CASE WHEN ... GLOB ... CAST(...) without a FROM, sent through the sql statement, ended the test process without an error status and without a log line. Simpler SELECTs without a FROM - SELECT date('now', 'localtime') and the like - work fine and are used all over the app. I moved the validation into ProcScript and haven't looked back.

The longer story, including how to find a crash when the log loses its last lines, is in The Uniface test that "hung" had died. Keep validation in ProcScript and use SQL for data.

Takeaways

Use separate variables for IN and OUT, even when it looks redundant.

Check whitespace in SQL, not after reading the value into ProcScript.

Give every timestamp-ordered list a second sort key and, where order matters, make the database guarantee it.

Choose the BOM per file type. lfiledump adds one for "UTF-8" unless you say /nobom.

Hash bytes, not text: fileload/raw into a raw variable, then $encode("SHA256", ...).

When a statement can take the process down, don't be clever in SQL.

40 features, 872 tests, one SQLite file - how the Uniface test suite stayed honest

Franz — Sat, 17 Oct 2026 09:20:39 +0000

The series Building a customer app in Uniface 10 ended its last round with 688 tests in 13 test services. This round added 40 features in one go - twelve new services, from a sales pipeline to settings snapshots - and two new test services. At the end, the full run had 15 suites and 872 tests, all passing, with the database file byte-identical before and after.

Part 17 of the series already covered the basics: every suite logs in on its own, every suite ends with rollback, and an MD5 of the database file before and after the run shows anything that slipped through. This article is about what came on top when the amount of work grew: how to keep 40 features in order, how to write tests that don't depend on what else is in the database, and how to make sure the IDE actually contains the code you tested.

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.

1. A work list in a file

Forty features don't fit into anyone's head, including mine. Before writing any code, I put them into a Markdown file in the project folder: one table per stage, one row per feature, with the service operation, the required role and a status column.

| # | Status | Feature | Service.Operation | Right |
|---|---|---|---|---|
| 1 | [x] | Set the customer stage (LEAD, PROSPECT, CUSTOMER, LOST) with a history entry | PIPELINE_SVC.SET_STAGE | EDIT |
| 2 | [x] | Read the stage (stage, since when) | PIPELINE_SVC.GET_STAGE | READ |
...

Below the tables, the file has the rules learned in earlier rounds and a progress log with one line per finished stage: what was added, how many tests, which failures and why. The four stages - sales, data quality, statistics, operations - were done strictly one after the other: write the services, compile, show the compiler output before fixing anything, run the stage's tests, update the file. Only then the next stage.

It sounds bureaucratic. In practice it meant that after an interruption - and there were several - the next step was always clear, and at the end the documentation was mostly written already.

2. Each suite owns a range

All test services work on the same database file. To keep them from stepping on each other, each one owns a range of customer numbers, and its clean-up only touches that range:

Suite	Customer numbers
FEAT9_TST_SVC	999830 - 999859
FEAT10_TST_SVC	999860 - 999909
FEAT11_TST_SVC	999910 - 999959

Users, settings, templates and snapshots that tests create get a prefix (tst_ for users, TST_ for keys). The clean-up runs at the start - in case a previous run stopped half-way - and again at the end:

entry CLEANUP
    sql "DELETE FROM CUSTOMER_STAGE WHERE CUSTOMER_ID BETWEEN 999860 AND 999909", "CUSTOMERS"
    sql "DELETE FROM CUSTOMER_OWNER WHERE CUSTOMER_ID BETWEEN 999860 AND 999909", "CUSTOMERS"
    ...
    sql "DELETE FROM CUSTOMER WHERE CUSTOMER_ID BETWEEN 999860 AND 999909", "CUSTOMERS"
    sql "DELETE FROM APP_SETTING WHERE SETTING_KEY LIKE 'TST!_%' ESCAPE '!'", "CUSTOMERS"
    sql "DELETE FROM TEXT_TEMPLATE WHERE TEMPLATE_KEY LIKE 'TST!_%' ESCAPE '!'", "CUSTOMERS"
    call AS_RUNNER
    return 0
end

Note the ESCAPE '!': in LIKE, the underscore is a wildcard for one character, so 'TST_%' would also match a real setting called TSTX.... The child tables come first and the customers last, so the order would still be right if foreign keys were enforced.

3. Measure first, then compare the difference

The database of this app is empty in normal operation, so a test could simply expect "the pipeline has 2 leads". That test would break the day someone runs the suite against a database with real data - or the day another suite leaves something behind. So the tests that count things measure first and compare the difference:

    activate "PIPELINE_SVC".PIPELINE_SUMMARY(vData, vCount, vError)
    call ROW_VALUE(vData, "LEAD", 2, vN0)
    ...
    activate "PIPELINE_SVC".SET_STAGE(999861, "LEAD", vError)
    activate "PIPELINE_SVC".SET_STAGE(999862, "lead", vError)
    ...
    activate "PIPELINE_SVC".PIPELINE_SUMMARY(vData, vCount, vError)
    call ROW_VALUE(vData, "LEAD", 2, vN)
    vCount = vN - vN0
    call CHECK("PIPELINE the summary counts the customers per stage", (vCount = 2), "lead before %%(vN0), after %%(vN)", pTests, pFailures)

The same pattern is used for statistics per month, follow-up statistics, consent rates, the export log, the period comparison and the clean-up of old sessions. Two details matter:

If a group may not exist yet, the "before" value is empty, not 0. The tests set it to "0" explicitly before they calculate with it.
Where a value can only be checked on an empty database - an average, for example - the check says so: (vN0 != "0" | vAvg = "5" | vAvg = "5.0"). It is weaker, but it does not lie.

4. A setting that leaked between tests

rollback runs once at the end of a suite, not after every test. Within a suite, every test sees what the tests before it left behind. That bit me with the ABC classification, whose limits come from two settings:

the test for the settings check set ABC_A to 50 to provoke a warning,
the ABC test, which ran later in the same suite, only set ABC_B and relied on ABC_A having its default of 70.

Run on its own, the ABC test passed. In the full suite it failed with "the limits come from the settings". The fix was one line - the ABC test now sets ABC_A as well - and a rule that went into the work list file: a test sets every setting it depends on, even if the value is the default.

5. Make sure the IDE has the code you tested

In this project, code is not typed into the IDE; it is written in files, pasted into the IDE's script editor and compiled. That leaves room for a quiet kind of error: a paste that landed one line too low, a selection that missed a line, a fix that went into the file but not into the IDE. The tests would then test the IDE's version, and the files - from which the documentation and these articles are written - would describe something else.

The Community Edition keeps its repository in a local SQLite file, dbms\usys.db. An earlier article, about building a calculator in the same IDE, describes what happens when you write to it while the IDE is running: the IDE stopped and would not start again. Reading a copy is harmless. So after every compile, a small script copies the file, extracts the script of each component and compares it with the file it came from:

mkdir -p ~/scratch/repo2 && cd ~/scratch/repo2 && cp "<ide project folder>/dbms/usys.db" usys.db && rm -rf src && python3 ../repo/extract.py usys.db
for n in "$@"; do python3 - "$n" <<'PY'
import sys,re,os,difflib
n=sys.argv[1]
t=open(os.path.expanduser('~/scratch/repo2/src/%s.txt'%n)).read()
d=re.split(r'^##### (\w+)\n',t,flags=re.M); dd=dict((d[i],d[i+1]) for i in range(1,len(d),2))
a=dd['USCRIPT'].rstrip('\n').split('\n'); b=open(os.path.expanduser('~/scratch/s18/out/%s.txt'%n)).read().rstrip('\n').split('\n')
a=[x.rstrip() for x in a]; b=[x.rstrip() for x in b]
diff=list(difflib.unified_diff(b,a,'expected','ide',lineterm='',n=0))
print(n,'IDENTICAL' if not diff else 'DIFF:'); print('\n'.join(diff[:40]))
PY
done

(The folder of the IDE project is replaced by a placeholder.) The extraction itself reads the component tables UFORM and OUFORM and unescapes the XML in which the scripts are stored. The output for this round:

PIPELINE_SVC IDENTICAL
OWNER_SVC IDENTICAL
...
FEAT11_TST_SVC IDENTICAL
PRIVACY_SVC IDENTICAL

It also works the other way round: before changing an existing component, the comparison shows whether the file is still the current version. For PRIVACY_SVC it showed exactly the twelve lines that were about to be inserted, and nothing else.

6. When a test fails, check the expectation first

Five things went red during this round. Three were compiler errors and entirely mine ($concat with six arguments - the limit is five). The two test failures were not bugs in the services at all:

Trailing blanks. A test compared an e-mail address with blanks on both sides after reading it back into ProcScript. The trailing blank does not survive that trip. The service was right; the comparison was not.
No login is not no role. A test expected that adding a personal task fails after logout. In this app, a user who is not logged in has the role READ - a deliberate decision from part 16 of the series - and a personal task only needs READ. So the call succeeded, as designed. The test now checks what actually matters: after logout, the tasks of the previous user are not visible.

In both cases, "fix the service until the test is green" would have been the wrong reaction. The first question for a red test is whether it expects the right thing.

Results

12 new services, 7 new tables, one changed service (anonymizing a customer also removes stage, owner and contact rhythm).
Two new test services with 114 and 70 tests. Full run: 15 suites, 872 tests, all passing.
MD5 of the database file identical before and after the full run, no journal file left.
All 16 new or changed components identical in the IDE and in the files.

Takeaways

Write the plan into a file and work through it. Status per feature, a progress log per stage, rules from earlier rounds at the top.

Give each test suite its own range of IDs and names, and clean up before and after.

Compare differences, not absolute numbers, unless the test creates everything it counts.

A test sets every setting it depends on. The suite's rollback comes too late to protect the next test.

Check that the tool contains what you tested. If the IDE's repository is readable, diff against it - read-only, on a copy.

A red test is a question, not an order. Check the expectation before you change the code.

An invoice archive with SHA-256 checksums in Uniface - and where it is weaker than it looks

Franz — Sat, 17 Oct 2026 09:07:29 +0000

Issued invoices must be kept, and kept unchanged. The customer app from the series Building a customer app in Uniface 10 can already render an invoice as HTML. The last stage added an archive: all issued invoices and credit notes of a year are written to files, each file gets a SHA-256 checksum in the database, and a second operation checks later whether every file still exists and still has the same checksum.

The code is short. The interesting part is what the checksum can and can't tell you.

Tested with Rocket Uniface 10.4.03 Community Edition and SQLite 3.50.2, October 2026.

The table

CREATE TABLE ARCHIVE_ENTRY (
  ARCHIVE_ID   INTEGER NOT NULL PRIMARY KEY,
  DOC_ID       INTEGER NOT NULL,
  ARCHIVE_YEAR INTEGER NOT NULL,
  FILE_NAME    VARCHAR(260) NOT NULL,
  CHECKSUM     VARCHAR(64) NOT NULL,
  ARCHIVED_AT  DATETIME NOT NULL,
  ARCHIVED_BY  VARCHAR(30)
);
CREATE UNIQUE INDEX UX_ARCHIVE_DOC ON ARCHIVE_ENTRY (DOC_ID);
CREATE INDEX IX_ARCHIVE_YEAR ON ARCHIVE_ENTRY (ARCHIVE_YEAR);

One row per document, enforced by the unique index. A SHA-256 hash in hex has 64 characters.

Archiving a year

ARCHIVE_DOCS(pYear, pDir) selects every issued invoice and credit note of the year - drafts never get archived - and writes one file per document, named after the document number:

    sql/data "SELECT DOC_ID, COALESCE(DOC_NO, '') FROM SALES_DOC WHERE DOC_TYPE IN ('INVOICE', 'CREDIT') AND STATUS <> 'DRAFT' AND substr(DOC_DATE, 1, 4) = '%%(pYear)' ORDER BY DOC_NO, DOC_ID", "CUSTOMERS"
    ...
    forlist vRow in vData
        getitem vDocId, vRow, 1
        getitem vNo, vRow, 2
        if (vNo = "")
            vNo = "DOC-%%(vDocId)"
        endif
        vNo = $replace(vNo, 1, "/", "-", -1)
        vFile = "%%(vDir)%%(vNo).html"
        activate "INVOICE_SVC".INVOICE_HTML(vDocId, vFile, vLines, pError)
        if ($status < 0)
            return -1
        endif
        call FILE_SUM(vFile, vSum)
        ...
        sql "INSERT INTO ARCHIVE_ENTRY (ARCHIVE_ID, DOC_ID, ARCHIVE_YEAR, FILE_NAME, CHECKSUM, ARCHIVED_AT, ARCHIVED_BY) VALUES (%%(vId), %%(vDocId), %%(pYear), %%(vFileLit), '%%(vSum)', datetime('now', 'localtime'), %%(vUserLit))", "CUSTOMERS"

Two small defensive steps: a slash in a document number would become a directory separator, so it is replaced; and a document without number (which shouldn't exist once issued) still gets a unique file name.

The rendering is not new code. The archive calls the same INVOICE_HTML operation the user calls when printing an invoice, so the archived file looks exactly like the printed one.

Cancelled invoices are archived too. A cancelled invoice was issued and sent; it stays part of the record, next to the credit note that reverses it.

The checksum

entry FILE_SUM
params
    string pFile : IN
    string pSum : OUT
endparams
variables
    string vText
endvariables
    pSum = ""
    vText = ""
    fileload pFile, vText, "UTF-8"
    if ($status < 0)
        return -1
    endif
    pSum = $encode("HEX", $encode("SHA256", vText))
    return 0
end

$encode("SHA256", …) returns the raw hash; $encode("HEX", …) turns it into the familiar 64-character string.

Checking the archive

ARCHIVE_INDEX(pYear, pFileName) goes through all entries of a year, recomputes each checksum and writes an index file with one line per document:

        vState = "OK"
        if ($lfileexists(vFile) != 1)
            vState = "MISSING"
        else
            call FILE_SUM(vFile, vNow)
            if (vNow != vSum)
                vState = "CHANGED"
            endif
        endif

The result looks like this:

DocNo;File;Checksum;State
I-2025-00001;C:/Projects/CustomerApp/archive/2025/I-2025-00001.html;3f0c…a91e;OK
I-2025-00002;C:/Projects/CustomerApp/archive/2025/I-2025-00002.html;77b2…04cd;CHANGED

(checksums shortened here), and the operation returns the number of bad entries. The test archives a year with one issued invoice and one draft, expects exactly one entry with a 64-character checksum, verifies (OK), overwrites the file with other text and verifies again (CHANGED). The MISSING case is not covered by a test yet.

Limit 1: this hashes text, not bytes

fileload … "UTF-8" decodes the file into a string before hashing. The hash is therefore a hash of the text, not of the bytes on disk. Two consequences:

The value is not what sha256sum or Get-FileHash print for the same file. Someone checking the archive with standard tools would see different numbers. (A byte order mark at the start of the file is one obvious difference.)
A change that keeps the decoded text identical - re-saving the file with a different encoding that represents the same characters - would not be detected.

For catching edits to the content, that's enough. For checksums that outside tools can reproduce, hash the bytes: load the file with fileload/raw into a variable of type raw and hash that. I described this in an earlier article (Six ProcScript surprises from 60 Uniface services). The archive should use it too; that's the first change on my list.

Limit 2: the checksums live next to the files

The files are in a folder; the checksums are in the database of the same application. Anyone who can change an archived file and also write to the database can update the checksum as well, and the check reports OK.

What the checksum does protect against is accidental change and change by someone who only has access to the file share. For more, the checksums must leave the system: print the index file, send it to someone, store it where the archive's writers can't write - or sign it.

Limit 3: archiving again replaces the old entry

ARCHIVE_DOCS removes an existing entry for a document before inserting the new one:

        sql "DELETE FROM ARCHIVE_ENTRY WHERE DOC_ID = %%(vDocId)", "CUSTOMERS"

That makes the operation repeatable - archive a year, notice the folder was wrong, archive again. But it also means: if someone changes a file and then runs the archive for that year again, the new file is written from the database and gets a fresh checksum. The tampered file is overwritten, not detected.

For an archive that means "unchanged since the first time", the second run should refuse to touch documents that are already archived, or at least compare first and report differences. Both are small changes; neither is in this version.

So is it useful?

Yes, for what it is: a complete, readable copy of every issued document of a year, produced by the same code that prints invoices, with a cheap check that detects missing and edited files. It is not an audit-proof archive system, and the article title says "weaker than it looks" because the word checksum tends to make it sound like one.

Takeaways

Archive with the code that prints, so the archived file is the document the customer got.

Store one checksum per file and check for missing and changed files.

Hash bytes if anyone else should verify the checksums. fileload with an encoding hashes text.

Get the checksums out of the system if the people who can edit files can also edit the database.

Make re-archiving explicit. Repeating the run should not silently replace the evidence.

A job scheduler in two tables - recurring tasks inside a Uniface app

Franz — Sat, 17 Oct 2026 09:06:08 +0000

The customer app from the series Building a customer app in Uniface 10 had accumulated several operations that someone has to start regularly: the subscription billing run, the dunning run, closing solved tickets after two weeks, the backup, capturing price changes. Each of them worked; none of them ran on its own.

The last stage added a small scheduler. It does not replace the Windows Task Scheduler - something still has to start it - but it moves the knowledge of what is due and what happened into the database, where the app can show it.

Tested with Rocket Uniface 10.4.03 Community Edition and SQLite 3.50.2, October 2026.

Two tables

CREATE TABLE JOB_DEF (
  JOB_CODE      VARCHAR(20) NOT NULL PRIMARY KEY,
  JOB_NAME      VARCHAR(60) NOT NULL,
  JOB_TYPE      VARCHAR(20) NOT NULL,   -- SUBSCRIPTIONS, DUNNING, TICKET_CLOSE, BACKUP, PRICE_CAPTURE
  INTERVAL_DAYS INTEGER NOT NULL,
  NEXT_RUN      DATE NOT NULL,
  IS_ACTIVE     INTEGER NOT NULL,
  CHANGED_AT    DATETIME NOT NULL,
  CHANGED_BY    VARCHAR(30)
);

CREATE TABLE JOB_RUN (
  RUN_ID      INTEGER NOT NULL PRIMARY KEY,
  JOB_CODE    VARCHAR(20) NOT NULL,
  STARTED_AT  DATETIME NOT NULL,
  FINISHED_AT DATETIME,
  STATUS      VARCHAR(10) NOT NULL,     -- RUNNING, OK, ERROR
  MESSAGE     VARCHAR(500),
  RUN_BY      VARCHAR(30)
);
CREATE INDEX IX_JOB_RUN_JOB ON JOB_RUN (JOB_CODE, STARTED_AT);

The schedule is deliberately simple: an interval in days and the date of the next run. No cron expressions, no "first Monday of the month". Everything in this app runs daily, weekly or monthly-ish, and a day interval covers that well enough. A job type is a fixed list; it maps to code, not to a configurable command line.

Defining a job

SAVE_JOB creates or updates a job. Everything is validated before it is stored:

the code has 1 to 20 characters from A-Z, 0-9 and _,
the name 1 to 60 characters,
the type is one of the five known types,
the interval is 1 to 365 days,
active is 0 or 1,
the next run is a valid date (empty means today).

The type check uses a little trick for "is this word in the list":

    if ($scan(",SUBSCRIPTIONS,DUNNING,TICKET_CLOSE,BACKUP,PRICE_CAPTURE,", ",%%(pType),") = 0 | pType = "")
        pError = "Unknown job type '%%(pType)'. Allowed: SUBSCRIPTIONS, DUNNING, TICKET_CLOSE, BACKUP, PRICE_CAPTURE."
        return -1
    endif

The commas on both sides prevent partial matches - CLOSE alone is not found, because ,CLOSE, doesn't occur in the list.

Running what is due

RUN_DUE takes a date (normally today), selects all active jobs whose next run is on or before it, and runs them one after another:

    sql/data "SELECT JOB_CODE, JOB_TYPE, INTERVAL_DAYS, NEXT_RUN FROM JOB_DEF WHERE IS_ACTIVE = 1 AND NEXT_RUN <= '%%(vDay)' ORDER BY NEXT_RUN, JOB_CODE", "CUSTOMERS"
    ...
    forlist vRow in vJobs
        ...
        call NEXT_ID("JOB_RUN", "RUN_ID", vRunId)
        sql "INSERT INTO JOB_RUN (RUN_ID, JOB_CODE, STARTED_AT, STATUS, RUN_BY) VALUES (%%(vRunId), '%%(vCode)', datetime('now', 'localtime'), 'RUNNING', %%(vUserLit))", "CUSTOMERS"
        call RUN_ONE(vType, vDay, vMsg, vErr)
        vRc = $status
        if (vRc < 0)
            vStatus = "ERROR"
            vMsg = vErr
            pErrors = pErrors + 1
        else
            vStatus = "OK"
        endif
        ...
        sql "UPDATE JOB_RUN SET FINISHED_AT = datetime('now', 'localtime'), STATUS = '%%(vStatus)', MESSAGE = %%(vMsgLit) WHERE RUN_ID = %%(vRunId)", "CUSTOMERS"

Three decisions are in these lines:

The log row is written before the job runs. If the job crashes the process, the log shows a run with status RUNNING and no end time. That is much more useful than no row at all.

One failing job does not stop the others. The error goes into the log and into an error counter, and the loop continues. The dunning run should not be skipped because the backup folder was full.

Every job type calls an existing operation. The scheduler contains no business logic of its own:

    if (pType = "SUBSCRIPTIONS")
        call GET_TEXT("SELECT strftime('%d.%m.%Y', '%%(pDay)')", vDate)
        activate "BILLING_SVC".BILLING_RUN(vDate, 0, vRunId, vData, vCount, pError)
        ...
        pMessage = "%%(vCount) invoice drafts in billing run %%(vRunId)"
    elseif (pType = "DUNNING")
        activate "DUNNING_SVC".DUNNING_RUN(0, 0, vData, vCount, pError)
        ...
    elseif (pType = "TICKET_CLOSE")
        call SETTING_NUM("TICKET_CLOSE_DAYS", "14", vDays)
        activate "TICKET_SVC".AUTO_CLOSE(vDays, 0, vData, vCount, pError)
        ...
    elseif (pType = "BACKUP")
        activate "BACKUP2_SVC".SAFE_BACKUP(vFile, vMethod, vCheck, pError)
        ...
    elseif (pType = "PRICE_CAPTURE")
        activate "PRICEHIST_SVC".CAPTURE(vCount, pError)
        ...

The billing run gets the job date in the display format. Its date parameter accepts ISO dates as well, so the conversion is only defensive. The message is short and human-readable: "1 tickets closed", "3 invoice drafts in billing run 12".

Moving the next run

After a run, NEXT_RUN moves forward by the interval. The interesting case is a job that hasn't run for a while - the PC was off for a week, and a daily job is seven days overdue. There are two options:

run it seven times to catch up, or
run it once and move the next run past today.

For every job in this app, option 2 is right. Each of the called operations already processes everything that is due in one call - the billing run all due periods, the ticket closer all old solved tickets, the dunning run all overdue invoices with its own minimum interval between reminder levels. Replaying them would add nothing but log rows. So the next run moves by as many intervals as needed to land after the run date:

        call COUNT_SQL("SELECT CAST((julianday('%%(vDay)') - julianday('%%(vNext)')) / %%(vInterval) AS INTEGER) + 1 FROM (SELECT 1 AS X)", vSteps)
        vDays = vSteps * vInterval
        call GET_TEXT("SELECT date('%%(vNext)', '+%%(vDays) days')", vNewNext)
        sql "UPDATE JOB_DEF SET NEXT_RUN = '%%(vNewNext)' WHERE JOB_CODE = '%%(vCode)'", "CUSTOMERS"

Example: a weekly job with next run 10 days ago. 10 / 7 is 1.43, truncated to 1, plus 1 is 2 steps, so the next run is the old date plus 14 days - four days from now. The rhythm stays on the original weekday instead of drifting to "seven days after whenever it last ran". A daily job due today moves to tomorrow.

The next run also moves after an error. A job that fails every time would otherwise run again on every call of RUN_DUE; this way the error shows up once per interval in the log, and a system check reports jobs whose last run failed.

The job that can't be tested like the others

All test services in this app run inside one transaction that is rolled back at the end, so they never leave data behind. The backup job breaks that: before the backup copies the database file, it commits all pending changes so the copy is complete.

public operation SAFE_BACKUP
    ...
    commit

Running it inside a test would commit everything the test had written so far. So the test creates a BACKUP job, checks that it is stored and listed - and creates it inactive, so RUN_DUE doesn't pick it up.

The scheduler test covers the rest:

four jobs: one overdue and active, one due today, one inactive, one in the future,
invalid code, unknown type and interval 0 are rejected,
saving an existing code updates it instead of creating a second job,
RUN_DUE runs exactly the two due active jobs: a solved ticket from 30 days ago is closed, a price snapshot is written,
the next runs are today + 4 days (weekly, 10 days overdue) and tomorrow (daily, due today),
the log has one OK row per job with a readable message.

What still starts the scheduler

The scheduler decides what runs; it doesn't run by itself. Something has to call RUN_DUE. In this app that part doesn't exist yet - the operation is there and tested, but no form or startup command calls it. The obvious options are a small batch start of the app that calls RUN_DUE and exits, triggered by the Windows Task Scheduler, or a button in the admin menu. The job log would be the same either way.

Takeaways

Keep schedules simple: an interval in days and a next-run date cover most business jobs.

Write the log row before the job runs, and finish it afterwards.

Let one failing job not stop the others.

Skip missed runs instead of replaying them, unless the job really needs every single run - and move the date even after an error.

Know which jobs commit, and keep them out of rolled-back test runs.

A four-eyes approval in a Uniface service - one table, two rules, and the gaps it leaves

Franz — Sat, 17 Oct 2026 09:03:45 +0000

Some actions in the customer app from the series Building a customer app in Uniface 10 should not be done by one person alone: writing off an open invoice, changing a price by more than a few percent, moving a customer to another account owner, paying a large supplier bill. The app got a four-eyes approval for these four cases.

It is a small service - one table, five operations - but the details decide whether it actually helps. This article walks through them, including the parts it does not cover.

Tested with Rocket Uniface 10.4.03 Community Edition and SQLite 3.50.2, October 2026.

The table

CREATE TABLE APPROVAL (
  APPROVAL_ID    INTEGER NOT NULL PRIMARY KEY,
  APPROVAL_TYPE  VARCHAR(12) NOT NULL,   -- WRITEOFF, PRICE, TRANSFER, BILL
  REF_ID         INTEGER NOT NULL,       -- invoice, product, customer or bill
  AMOUNT_CENTS   INTEGER,                -- amount or new price
  NEW_VALUE      VARCHAR(100),           -- new owner for TRANSFER
  REQUEST_NOTE   VARCHAR(200),
  STATUS         VARCHAR(10) NOT NULL,   -- PENDING, APPROVED, REJECTED
  REQUESTED_AT   DATETIME NOT NULL,
  REQUESTED_BY   VARCHAR(30) NOT NULL,
  DECIDED_AT     DATETIME,
  DECIDED_BY     VARCHAR(30),
  DECISION_NOTE  VARCHAR(200)
);
CREATE INDEX IX_APPROVAL_STATUS ON APPROVAL (STATUS, REQUESTED_AT);

One generic table instead of one per action type. The reference is a plain ID whose meaning depends on the type; the payload is either an amount or a short text. That is less strict than four specific tables, but the list of open requests is one query and the history of all decisions is one table.

When is an approval needed?

The thresholds are settings, so they can change without a release:

Type	Needs approval when	Setting (default)
`WRITEOFF`	amount above the limit	`APPROVAL_WRITEOFF_CENTS` (5000)
`PRICE`	change above a percentage of the old price	`APPROVAL_PRICE_PCT` (10)
`BILL`	gross amount above the limit	`APPROVAL_BILL_CENTS` (100000)
`TRANSFER`	always	-

The check is its own read-only operation, so the user interface can ask before it offers the button:

    call SETTING_NUM("APPROVAL_PRICE_PCT", "10", vLimit)
    if (vOld = 0)
        if (vAmount > 0)
            pNeeded = 1
        endif
        return 0
    endif
    call COUNT_SQL("SELECT ABS(%%(vAmount) - %%(vOld)) * 100.0 / %%(vOld) FROM (SELECT 1 AS X)", vPct)
    if (vPct > vLimit)
        pNeeded = 1
    endif

The special case vOld = 0 is not cosmetic: a product without a price would otherwise divide by zero, and any first price is a 100 % change anyway.

The request validates everything up front

REQUEST checks that the action could be executed right now: the invoice exists and is still open, the product exists, the new owner is an active user, the bill is still open. A write-off also needs a reason.

    if (vType = "WRITEOFF")
        call GET_TEXT("SELECT STATUS FROM SALES_DOC WHERE DOC_ID = %%(pRefId) AND DOC_TYPE = 'INVOICE'", vStatus)
        if (vStatus != "ISSUED" & vStatus != "PARTLY_PAID")
            pError = "The invoice %%(pRefId) does not exist or is not open."
            return -1
        endif
        ...
        if (vNote = "" | $length(vNote) > 60)
            pError = "A write-off needs a reason with at most 60 characters."
            return -1
        endif

And there can only be one open request per object and type:

    call COUNT_SQL("SELECT COUNT(*) FROM APPROVAL WHERE APPROVAL_TYPE = '%%(vType)' AND REF_ID = %%(pRefId) AND STATUS = 'PENDING'", vCount)
    if (vCount > 0)
        pError = "There is already a pending request for this object."
        return -1
    endif

Without that rule, two people can request two different write-offs for the same invoice, and the approver sees two plausible requests without knowing they compete.

Rule 1: someone else decides

Approving and rejecting require the administrator role - and the decider must not be the person who asked:

entry OTHER_ADMIN
params
    string pRequestedBy : IN
    string pUser : OUT
    string pError : OUT
endparams
variables
    string vRole
endvariables
    pError = ""
    activate "USER_SVC".CURRENT_USER(pUser, vRole)
    if (pUser = pRequestedBy)
        pError = "A request must be decided by another administrator."
        return -1
    endif
    return 0
end

This is checked on the server side, in the service, not by hiding a button. A test logs in as the requesting administrator, tries to decide the request and expects this error.

Rule 2: the approver executes

The approval is not a flag that the requester checks later. The APPROVE operation performs the action itself, by calling the same service operation that would otherwise be called directly:

    if (vType = "WRITEOFF")
        activate "SETTLE_SVC".WRITE_OFF(vRefId, vAmountText, vNote, vPaymentId, pError)
    elseif (vType = "PRICE")
        activate "PRODUCT_SVC".UPDATE_PRODUCT(vRefId, "", "", vAmountText, -1, 0, pError)
    elseif (vType = "TRANSFER")
        activate "OWNER_SVC".SET_OWNER(vRefId, vValue, pError)
    else
        activate "PAYABLE_SVC".PAY_BILL(vRefId, "", pError)
    endif
    if ($status < 0)
        return -1
    endif
    ...
    sql "UPDATE APPROVAL SET STATUS = 'APPROVED', DECIDED_AT = datetime('now', 'localtime'), DECIDED_BY = %%(vUserLit), DECISION_NOTE = %%(vDecisionLit) WHERE APPROVAL_ID = %%(pApprovalId)", "CUSTOMERS"

Two reasons for this order:

What was approved is what happens. The amount and the new price come from the request row, not from a second input. Nobody can approve 40.00 and then book 400.00.
The existing rules still apply. The write-off goes through the settlement service with all its checks and its own audit trail. If the invoice was paid in the meantime, the write-off fails, the operation returns the error, and the request stays PENDING for a decision.

The status is only set after the action succeeded. Action and status update run in the same transaction, so a failure in between doesn't leave an executed action with a pending request - as long as the caller rolls back on an error.

Rejecting needs a reason (1 to 200 characters) and only sets the status.

What this does not protect against

This is the part readers will ask about, so here it is up front.

The direct path still exists. SETTLE_SVC.WRITE_OFF and the other operations were there before the approval service, and they still work when called directly by a user with the right role. The four-eyes rule holds only as long as the user interface routes these actions through REQUEST. A hard guarantee would need each of the four operations to check for an approved request itself - and then the approver would need a way past that check. I chose the softer version and wrote it down.

One person with two accounts. The check compares user names. An administrator who can create users can create a second administrator account. The usual answer is organisational: user administration in different hands.

Time between request and decision. The request validates the state at request time; the approval re-validates by executing the action. A price change requested at 10 % but approved after someone else changed the price will apply the requested new price anyway. Whether that is right depends on the business; here it is accepted.

What the tests cover

The limits per type, including the price of 0.
Requests of all four types are stored as pending; a second request for the same object is refused; requests for objects that don't exist or aren't open are refused.
Approving executes the price change, the write-off and the customer transfer.
Rejecting without a reason fails and leaves the object unchanged.
The requester cannot decide their own request.
A user with only the edit role can request and list, but not decide.

One case is not automated yet: an action that fails at approval time (for example an invoice paid in the meantime) must leave the request pending. It is the next test I would add.

Takeaways

Check "who decides" in the service, not in the user interface.

Let the approval execute the action with the stored values, through the existing operations.

Validate on request and again on execution; set the status only after the action succeeded.

One pending request per object.

Write down what the design doesn't cover. Someone will ask.

The Uniface CSV import that never found its header line

Franz — Sat, 17 Oct 2026 08:58:11 +0000

The customer app from the series Building a customer app in Uniface 10 got a CSV import for products, prices, suppliers, contacts and customer changes. The service compiled without errors. The first test run reported 16 tests, 12 failures - and the failing tests all got the same kind of error from the service, here for the product file:

The first line must be the header SKU;NAME;UNIT;PRICE;TAXRATE.

The test file started with exactly that header. This article is about why the import didn't see it, and how to split text into lines in ProcScript so that it works for files from Windows, Linux and old Macs.

Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) on Windows, October 2026.

The import in short

Every import type goes through one entry, RUN_IMPORT. It loads the file, checks the header, then hands every line to a type-specific entry that validates and saves it:

    fileload pFileName, vText, "UTF-8"
    ...
    getitem vHead, vLines, 1
    vHead = $uppercase(vHead)
    call TRIM_TEXT(vHead)
    if (vHead != pHeader)
        pError = "The first line must be the header %%(pHeader)."
        return -1
    endif

The idea: turn the text into a Uniface list - one item per line - and then use getitem and forlist. A Uniface list uses a special separator character, the GOLD semicolon. $string("&uSEP;") returns it.

What went wrong

The first version replaced the line break with the list separator like this, roughly:

    vSep = $string("&uSEP;")
    vLines = $replace(vText, 1, "%%^", vSep, -1)

%%^ is how you write a line break in a ProcScript string literal, and when you write a file it does what you expect: lfiledump with "%%^" in the text produces CR LF on Windows. The test files were written that way. So the test file contained CR LF after every line.

Reading it back, the $replace did not split anything. The whole file stayed one list item, so "line 1" was the complete file, and the header comparison failed. I did not find out what character %%^ stands for inside the string at runtime; what I could verify is that it does not match the CR LF pair that fileload returns.

The fix: name the characters explicitly

$string accepts character references, so the three possible line endings can be written unambiguously:

    vSep = $string("&uSEP;")
    if ($scan(vText, $string("&#13;&#10;")) > 0)
        vText = $replace(vText, 1, $string("&#13;&#10;"), vSep, -1)
    endif
    if ($scan(vText, $string("&#13;")) > 0)
        vText = $replace(vText, 1, $string("&#13;"), vSep, -1)
    endif
    if ($scan(vText, $string("&#10;")) > 0)
        vText = $replace(vText, 1, $string("&#10;"), vSep, -1)
    endif
    vLines = vText

The order matters. CR LF must be replaced first; otherwise replacing CR alone would leave a lone LF behind and every line break would turn into two list items with an empty one in between. After the first replacement, any remaining CR is an old-Mac line ending and any remaining LF is a Linux one.

The $scan checks are not needed for correctness. They just skip a full pass over a large text when the character doesn't occur.

The same problem appeared in a second place: the JSON export escapes text values, and it had to turn line breaks inside a customer note into \n. Same fix - drop CR, replace LF:

    if ($scan(pText, $string("&#13;")) > 0)
        pText = $replace(pText, 1, $string("&#13;"), "", -1)
    endif
    if ($scan(pText, $string("&#10;")) > 0)
        pText = $replace(pText, 1, $string("&#10;"), "\n", -1)
    endif

After both changes, all 16 tests of the import and export service passed.

Two more things a CSV loader needs

A byte order mark. Files saved by Excel or by lfiledump with "UTF-8" start with a BOM. fileload keeps it as the first character of the text, and then the header is SKU;NAME;… - which also doesn't match. The import removes it:

    vBom = $string("&#xFEFF;")
    if (vText[1:1] = vBom)
        vLen = $length(vText) - 1
        vText = vText[2:vLen]
    endif

Compute the length into a variable first. Expressions inside the index brackets (vText[2:$length(vText)-1]) gave me trouble earlier.

The trailing line break. A file that ends with a line break produces an empty last item. The loop skips empty lines instead of reporting them as errors:

    forlist vLine in vLines
        vNo = vNo + 1
        if (vNo > 1 & vLine != "")
            vTotal = vTotal + 1
            vFields = $replace(vLine, 1, ";", vSep, -1)
            call IMPORT_ROW(pType, vFields, pDryRun, vMsg)
            ...

vNo still counts every physical line, so an error message says "line 6" for the sixth line of the file, even if line 4 was empty. That is what a user looking at the file in an editor expects.

Errors per line, not per file

A broken row should not stop the import. Every row is validated on its own, valid rows are saved, and the messages are collected:

FAIL ... line 4: name must have 1 to 100 characters | line 5: price is not valid | line 6: tax rate must be 0, 7 or 19

Every run - including dry runs - is written to an import log table with the number of rows, the number of errors and the error text. With pDryRun = 1 the import validates everything and saves nothing, which is the mode you want first with a file from someone else.

What it still doesn't do

Quoted fields. The import splits each line at every semicolon. A product name like Paper; A4 exported as "Paper; A4" would be split into two fields on the way back in. The export quotes such values correctly; the import doesn't unquote them. For the data in this app - product numbers, names, prices - that was acceptable, but it means export and import are not a guaranteed round trip. If your data can contain the separator, you need a small state machine that walks the line character by character and tracks whether it is inside quotes.

Line breaks inside quoted fields. Same limitation, same fix.

Encodings other than UTF-8. A file saved as Windows-1252 will load, but umlauts come out wrong. The import doesn't try to guess.

Takeaways

Write CR and LF as character references ($string(""), $string("
")) when you take text apart. Don't rely on %%^ for that.

Replace CR LF first, then CR, then LF.

Strip the BOM before you compare the first line with anything.

Count physical lines for messages, skip empty ones for processing.

Validate per row and log every run, including dry runs.

Be honest about quoting. If the import splits on the separator, say so - or write the state machine.

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

Franz — Sat, 17 Oct 2026 08:55:27 +0000

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.

Schema migrations for a Uniface app on SQLite - without a migration tool

Franz — Sat, 17 Oct 2026 08:53:49 +0000

Over five stages, the database of the customer app from the series Building a customer app in Uniface 10 grew from about 35 to 95 tables. There is no migration framework. Uniface has no Flyway, and adding a Java or .NET tool just to run a few CREATE TABLE statements against a single SQLite file felt like the wrong trade.

What replaced it is a short, boring procedure and two checks inside the app. Both are below, including the parts that are specific to the Uniface IDE.

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.

The rule: only add, never change

The last five stages only added tables, indexes and settings. No column was renamed, no table rebuilt, no data moved. New information about an existing record goes into a new table with the same key - for example company data for a customer:

CREATE TABLE CUSTOMER_PARTY (
  CUSTOMER_ID  INTEGER NOT NULL PRIMARY KEY,
  PARTY_TYPE   VARCHAR(10) NOT NULL,
  COMPANY_NAME VARCHAR(100),
  ...
);

instead of ALTER TABLE CUSTOMER ADD COLUMN …. That keeps every existing form, entity and service untouched, and it makes every migration safe to reason about: a stage either created its objects or it didn't. There is no half-migrated table.

It is not free. Reading a customer with all its details now means joins over several 1:1 tables, and the "customer" exists in more places than one. For an app that grows in stages, that was the better trade; for a schema designed in one go, it would not be.

New tables, no new entities

In Uniface, a table is usually also an entity in an application model, with fields, keys and relationships, so forms can paint it. The 60 new tables have none of that. They are only ever read and written by services, and the services use sql and sql/data with the path from the assignment file:

[PATHS]
$CUSTOMERS   SLE:C:\Projects\CustomerApp\customers.db

    sql/data "SELECT JOB_CODE, JOB_TYPE, INTERVAL_DAYS, NEXT_RUN FROM JOB_DEF WHERE IS_ACTIVE = 1 AND NEXT_RUN <= '%%(vDay)' ORDER BY NEXT_RUN, JOB_CODE", "CUSTOMERS"

Like every service in this app, each new service component has exactly one entity painted on it: a non-database dummy, SVC_DMY.NOMODEL. The compiler answers with warning 1016 ("entity not found in application model, generating now"), which is expected and the only warning these services produce.

This decision makes a migration a pure database change. There is nothing to import into the repository, no model to keep in sync, and the next compile doesn't care. The price: forms can't paint these tables directly; a form that needs them calls a service operation.

Step 1: one script per stage

Each stage has one SQL file with plain statements, one per line:

CREATE TABLE APPROVAL (APPROVAL_ID INTEGER NOT NULL PRIMARY KEY, APPROVAL_TYPE VARCHAR(12) NOT NULL, ...);
CREATE INDEX IX_APPROVAL_STATUS ON APPROVAL (STATUS, REQUESTED_AT);
CREATE TABLE PRICE_HISTORY (...);
CREATE INDEX IX_PRICE_HISTORY_PRODUCT ON PRICE_HISTORY (PRODUCT_ID, VALID_FROM);
...
INSERT OR REPLACE INTO APP_SETTING (SETTING_KEY, SETTING_VALUE) VALUES ('SCHEMA_VERSION', '23');

The last line records the version in a settings table. That one row is what the app later checks.

The script and a short model description go into the folder of the stage, and the script is the documentation of what changed. Nobody has to reconstruct it from sqlite_master later.

Step 2: back up the file

backup\customers_before_stage23_20261016.db

A plain copy of the database file, named after the stage and the date. With SQLite, the whole database is one file - this is the cheapest rollback plan there is. Take the copy when nothing is writing to the file; a copy of a file in the middle of a write is not a backup.

Step 3: dry run on a copy

The script runs first against a second copy of the database, with a SQLite client outside the app. Then three checks on that copy:

PRAGMA integrity_check;
SELECT type, name, sql FROM sqlite_master ORDER BY type, name;
SELECT SETTING_VALUE FROM APP_SETTING WHERE SETTING_KEY = 'SCHEMA_VERSION';

The sqlite_master output of the copy is saved. After the real migration, the same query on the real file must return exactly the same rows. That comparison catches the typical mistakes of a manual migration: a statement that was skipped, a statement executed twice (which fails for CREATE TABLE, but not for INSERT OR REPLACE), or a statement run against the wrong file.

Step 4: apply it, one statement at a time

The real migration runs inside the Uniface IDE, because that is where the connection to the database is configured. The IDE has an EDIT SQL dialog: a text field, an SQL button, a result area. Two things about it are worth knowing:

Commit explicitly. Statements executed there are not committed automatically. Each statement is followed by COMMIT in the same dialog. Until then the change is not in the file.
Don't use Ctrl+A in the dialog. In the Uniface IDE, Ctrl+A is the ^ACCEPT key and closes the dialog. To replace the text in the field, use Ctrl+Home, then Ctrl+Shift+End, then type.

One statement at a time sounds slow. For 15 statements it takes a few minutes, and every statement gets its own result. A script with one failed line in the middle would leave you guessing which statements went through.

The backup and the dry run were the work. The real run should be uneventful.

Step 5: let the app check itself

Two operations check the schema from inside the app.

A schema check compares the tables that should exist with the tables that do, and reads the version:

    call GET_TEXT("SELECT SETTING_VALUE FROM APP_SETTING WHERE SETTING_KEY = 'SCHEMA_VERSION'", pVersion)
    sql/data "SELECT name FROM sqlite_master WHERE type = 'table'", "CUSTOMERS"
    ...
    if (pVersion != "23")
        pError = "The schema version is %%(pVersion), expected 23."
        return -1
    endif

A broader system check does the same for the tables and indexes of the newer stages, with one query each. SQLite's VALUES clause makes the expected list a table, and NOT IN against sqlite_master returns what is missing:

entry MISSING_OBJECTS
params
    string pType : IN
    string pValues : IN
    string pMissing : OUT
endparams
    call GET_TEXT("SELECT COALESCE(GROUP_CONCAT(v.column1, ', '), '') FROM (VALUES %%(pValues)) v WHERE v.column1 NOT IN (SELECT name FROM sqlite_master WHERE type = '%%(pType)')", pMissing)
    return 0
end

called with

    vIndexes = "('UX_PRODUCT_SKU'), ('UX_SALES_DOC_NO'), ('IX_SALES_DOC_CUSTOMER'), ..."
    call MISSING_OBJECTS("index", vIndexes, vMissing)

An empty result means everything is there. A non-empty one names exactly what is missing - which is the information you need at the moment the app starts behaving strangely after someone restored an old backup.

The same system check also validates that numeric settings are numbers:

SELECT COALESCE(GROUP_CONCAT(SETTING_KEY, ', '), '')
  FROM APP_SETTING
 WHERE SETTING_KEY IN ('SLA_HIGH', 'DUNNING_FEE_1', ...)
   AND (COALESCE(SETTING_VALUE, '') = '' OR SETTING_VALUE GLOB '*[^0-9]*');

GLOB '*[^0-9]*' matches any value that contains a character other than a digit.

Step 6: run all tests and note the checksum

The full test suite runs against the migrated file. All tests run inside one transaction that is rolled back, so the file must be byte-identical afterwards. Its MD5 checksum goes into the project notes - once per stage, after the migration. From then on, any change of that checksum outside a planned migration is a finding.

What I would do differently with a bigger team

A version table instead of a setting. One row per applied script with a timestamp and a checksum of the script. That is what migration tools do, and it answers "was script 21 applied twice?" without detective work.
Statements that are safe to repeat. CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS would make a re-run harmless. I left them out on purpose, so that an accidental second run fails loudly - with one person and a dry run, loud failure was more useful than silent success.
Real ALTER TABLE migrations would need SQLite's "create new table, copy, drop, rename" dance for anything beyond adding a column. The add-only rule avoided that entirely.

Takeaways

Only add while you can: new tables with the same key instead of changed tables.

One script per stage, ending with the version number.

Back up, dry run on a copy, compare sqlite_master.

Apply one statement at a time and commit explicitly - in the Uniface IDE, without Ctrl+A.

Let the app check its own schema: version, tables, indexes, settings.

Business reports in a Uniface service - year comparison, cash forecast and sales funnel with plain SQL

Franz — Sat, 17 Oct 2026 08:51:08 +0000

The last stage of the customer app from the series Building a customer app in Uniface 10 added a reporting service: a cockpit, a month-end report, a year comparison, margins, a cash forecast, stock turnover, service figures and a sales funnel. All of it lives in one Uniface service, KPI2_SVC: ProcScript operations that build plain SQL, run it against SQLite and return Uniface lists or write HTML files.

This article shows how the service hands figures to its callers, how it writes HTML without a report painter, four of the queries that needed a trick, and one deliberate restriction: no window functions, no CTEs, and never more than one scalar subquery in a select list.

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.

A cockpit as an associative list

The cockpit returns 18 figures. Instead of 18 OUT parameters, the operation returns one Uniface list with key=value items, built with putitem/id:

    call COUNT_SQL("SELECT COALESCE(SUM(d.NET_CENTS), 0) FROM SALES_DOC d WHERE %%(vCond) AND substr(d.DOC_DATE, 1, 7) = substr('%%(pDay)', 1, 7) AND d.DOC_DATE <= '%%(pDay)'", vN)
    putitem/id pData, "SALES_MONTH", vN
    call COUNT_SQL("SELECT COUNT(*) FROM SALES_DOC WHERE DOC_TYPE = 'ORDER' AND STATUS IN ('OPEN', 'CONFIRMED')", vN)
    putitem/id pData, "OPEN_ORDERS", vN
    ...

A caller picks what it needs with getitem/id vN, vData, "OPEN_AR". Adding a figure later doesn't change the signature, and every existing caller keeps working.

Each figure is its own small statement. That is 18 round trips instead of one big query - for a cockpit opened a few times a day on a local SQLite file, simplicity wins.

HTML without a report painter

The reports are HTML files written with lfiledump. A helper entry turns one cockpit figure into a table row, formats cents as currency and appends the row to the page:

entry KV_ROW
params
    string pLabel : IN
    string pData : IN
    string pKey : IN
    numeric pCents : IN
    string pHtml : INOUT
endparams
variables
    string vText, vLine
    numeric vN
endvariables
    getitem/id vN, pData, pKey
    if (pCents = 1)
        call FMT_CENTS(vN, vText)
    else
        vText = "%%(vN)"
    endif
    vLine = "<tr><td>%%(pLabel)</td><td style='text-align:right'>%%(vText)</td></tr>"
    pHtml = $concat(pHtml, vLine, "%%^")
    return 0
end

and the page is a list of such calls:

    call KV_ROW("Open receivables", vData, "OPEN_AR", 1, vRows)
    call KV_ROW("Overdue receivables", vData, "OVERDUE_AR", 1, vRows)
    call KV_ROW("Open tickets", vData, "OPEN_TICKETS", 0, vRows)

Two ProcScript details: $concat takes at most five arguments, so longer pages are built in steps; and %%^ in a string literal writes a line break when the file is saved. Every operation that writes a file also logs the export with user, type, row count and file name.

The restriction first

SQLite 3.50 supports common table expressions and window functions. A running total would be one SUM(…) OVER (ORDER BY …). I still don't use them here.

The reason is not SQLite, it's the path between ProcScript and SQLite. Earlier in the project, a single SELECT without a FROM clause ended the whole Uniface process - no error, no log line, just gone (the story is in The Uniface test that "hung" had died). Statements with several scalar subqueries in the select list also caused trouble. Since then the rules for SQL in this app are boring on purpose:

derived tables in FROM instead of scalar subqueries in the select list,
a calculation without a table still gets one: SELECT … FROM (SELECT 1 AS X),
no window functions or WITH, because I haven't tested them through this driver and I don't want to find out in production.

If your SQLite access goes through a mature driver, use window functions. If it goes through something you can't debug, the patterns below still work.

1. Twelve months without a calendar table

A year comparison needs one row per month - including months without revenue. Without a calendar table and without a recursive CTE, a derived table of twelve literal rows does it:

SELECT x.N, x.C, x.P, x.C - x.P,
       CASE WHEN x.P <> 0 THEN ROUND((x.C - x.P) * 100.0 / x.P, 1) ELSE 0 END
FROM (
  SELECT m.N AS N, COALESCE(a.S, 0) AS C, COALESCE(b.S, 0) AS P
  FROM (SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
        UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) m
  LEFT JOIN (SELECT CAST(substr(d.DOC_DATE, 6, 2) AS INTEGER) AS M, SUM(d.NET_CENTS) AS S
               FROM SALES_DOC d
              WHERE d.DOC_TYPE IN ('INVOICE', 'CREDIT') AND d.STATUS <> 'DRAFT'
                AND substr(d.DOC_DATE, 1, 4) = '2025'
              GROUP BY 1) a ON a.M = m.N
  LEFT JOIN (/* same for 2024 */) b ON b.M = m.N
) x
ORDER BY x.N;

Three details:

Aggregate first, join second. Each year is grouped into at most twelve rows before the join. Joining raw documents to months and grouping afterwards would multiply rows when both years have data.
* 100.0 makes the percentage a real division. (C - P) * 100 / P with integers truncates.
Revenue includes credit notes with their negative amounts, and cancelled invoices stay in. An invoice and its cancelling credit note add up to zero over the year; if they fall into different months, the first month shows the revenue and the second the reversal.

2. A cash forecast in weekly buckets

The forecast answers: how much money comes in and goes out per week, starting today? Incoming is the open amount of issued invoices by due date; outgoing is open supplier bills by due date. Anything already overdue belongs into the first week - it is due now.

The number of weeks is a parameter (1 to 26), so the week list is built in ProcScript:

    vWeeks = "SELECT 0 AS W"
    vI = 1
    while (vI < pWeeks)
        vWeeks = "%%(vWeeks) UNION ALL SELECT %%(vI)"
        vI = vI + 1
    endwhile

Each side computes its bucket with a CASE:

SELECT CASE WHEN COALESCE(o.DUE_DATE, '2026-10-17') <= '2026-10-17' THEN 0
            ELSE CAST((julianday(o.DUE_DATE) - julianday('2026-10-17')) / 7 AS INTEGER)
       END AS W,
       SUM(o.OPEN_CENTS) AS S
  FROM (/* open invoices with open amount */) o
 GROUP BY 1

CAST(… AS INTEGER) truncates towards zero. For positive day differences that is the floor, which is what we want; negative differences are already caught by the CASE. Without it, an invoice 3 days overdue would land in week 0 by luck and one 10 days overdue in week -1, which doesn't exist in the week list and would silently disappear.

The week dates come from julianday arithmetic, not from string concatenation:

SELECT date(julianday('2026-10-17') + w.W * 7),
       date(julianday('2026-10-17') + w.W * 7 + 6),
       COALESCE(a.S, 0), COALESCE(b.S, 0), COALESCE(a.S, 0) - COALESCE(b.S, 0)
  FROM (/* weeks */) w
  LEFT JOIN (/* incoming */) a ON a.W = w.W
  LEFT JOIN (/* outgoing */) b ON b.W = w.W
 ORDER BY w.W

date() accepts a Julian day number, so date(julianday(x) + 7) is "one week after x" without building a modifier string like '+' || n || ' days'.

The open amount itself is a derived table that is reused by four reports, so it lives in one entry:

(SELECT d.DOC_ID, d.CUSTOMER_ID, d.DOC_DATE, d.DUE_DATE,
        d.GROSS_CENTS - COALESCE(p.PAID, 0) AS OPEN_CENTS
   FROM SALES_DOC d
   LEFT JOIN (SELECT DOC_ID, SUM(AMOUNT_CENTS) AS PAID
                FROM PAYMENT WHERE IS_REVERSED = 0 GROUP BY DOC_ID) p
          ON p.DOC_ID = d.DOC_ID
  WHERE d.DOC_TYPE = 'INVOICE' AND d.STATUS IN ('ISSUED', 'PARTLY_PAID'))

3. A sales funnel that follows the documents

A funnel counts how many opportunities reached each stage. The naive version counts quotes, orders and invoices in the period - but those numbers are unrelated. Ten quotes and ten invoices don't mean the quotes turned into the invoices.

This funnel follows the chain: opportunity → its quote → an order created from that quote → an invoice created from that order. Each step is a join on the stored reference:

-- quotes
SELECT COUNT(*)
  FROM OPPORTUNITY o
  JOIN SALES_DOC q ON q.DOC_ID = o.QUOTE_ID AND q.DOC_TYPE = 'QUOTE'
 WHERE substr(o.CREATED_AT, 1, 10) BETWEEN '2024-06-01' AND '2024-06-30';

-- invoices
SELECT COUNT(DISTINCT o.OPP_ID)
  FROM OPPORTUNITY o
  JOIN SALES_DOC r ON r.SOURCE_DOC_ID = o.QUOTE_ID AND r.DOC_TYPE = 'ORDER' AND r.STATUS <> 'CANCELLED'
  JOIN SALES_DOC i ON i.SOURCE_DOC_ID = r.DOC_ID AND i.DOC_TYPE = 'INVOICE'
                  AND i.STATUS NOT IN ('DRAFT', 'CANCELLED')
 WHERE substr(o.CREATED_AT, 1, 10) BETWEEN '2024-06-01' AND '2024-06-30';

COUNT(DISTINCT o.OPP_ID) matters: one order can be invoiced in parts, and three partial invoices must still count as one converted opportunity.

The test builds four opportunities: one with quote, order and invoice; one with only a quote; one without anything; one whose order was cancelled. Expected: 4 → 3 → 1 → 1, conversion 25 %.

4. Margin with a purchase price that may not exist

Gross margin per product is revenue minus quantity × purchase price. The purchase price is the one of the product's main supplier - and a product may have none. The rule is: no main supplier, cost 0. That is a visible choice, not an accident:

SELECT p.PRODUCT_ID, p.SKU, s.QTY, s.REV,
       CAST(ROUND(s.QTY * COALESCE(c.PC, 0) / 1000.0) AS INTEGER) AS COST
  FROM (SELECT l.PRODUCT_ID, SUM(l.QTY_MILLI) AS QTY, SUM(l.NET_CENTS) AS REV
          FROM SALES_LINE l JOIN SALES_DOC d ON d.DOC_ID = l.DOC_ID
         WHERE d.DOC_TYPE IN ('INVOICE', 'CREDIT') AND d.STATUS <> 'DRAFT'
           AND l.PRODUCT_ID IS NOT NULL
         GROUP BY l.PRODUCT_ID) s
  JOIN PRODUCT p ON p.PRODUCT_ID = s.PRODUCT_ID
  LEFT JOIN (SELECT PRODUCT_ID, MIN(PURCHASE_CENTS) AS PC
               FROM SUPPLIER_PRODUCT WHERE IS_MAIN = 1 GROUP BY PRODUCT_ID) c
         ON c.PRODUCT_ID = s.PRODUCT_ID

Quantities are stored in thousandths (QTY_MILLI), so the cost divides by 1000. Margin and percentage are computed one level up, on the named columns of this derived table, because SQLite doesn't let a select list refer to an alias defined in the same select list.

A product without main supplier now shows a 100 % margin, which is obviously wrong - and that's better than hiding it. The data quality report lists exactly these products as NO_MAIN_SUPPLIER.

Testing reports against a live database

Reports sum over everything, so a test can't simply expect "revenue = 10000" when other data exists. Two approaches cover it:

Past periods only the tests use. The month-end and year-comparison tests put their documents into March 2024 and March 2023.
Differences. The cockpit test reads all figures, inserts one invoice, one ticket, one opportunity and one stock movement, reads again and checks the deltas.

Takeaways

Aggregate in a derived table, then join - never join raw rows to a list of periods and group afterwards.

Generate small series with UNION ALL when you can't or won't use recursive CTEs.

Bucket dates with julianday, and catch negative differences before you cast.

Follow stored references for funnels, and count distinct starting objects.

Make defaults for missing data visible, then report them elsewhere.

Data quality checks in a Uniface app - orphans and bad data in SQLite without foreign keys

Franz — Sat, 17 Oct 2026 08:48:40 +0000

The database behind the customer app from the series Building a customer app in Uniface 10 has 95 tables. Not one of them declares a foreign key. The app keeps the relations consistent in its services, but that means SQLite will happily store an invoice line whose invoice no longer exists.

This article shows the Uniface service DQ3_SVC that finds such rows, and a second kind of check that looks for data that is technically valid but wrong for the business. The checks are ProcScript entries that build plain SQL, run it with sql or sql/data, and turn the result into Uniface lists and an HTML report. The SQL itself would work in any SQLite database.

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 SQLite doesn't catch it for you

Two reasons, and both are easy to miss:

Foreign key enforcement is off by default in SQLite. Even with REFERENCES clauses in the schema, nothing is checked unless the connection runs PRAGMA foreign_keys = ON. Every connection, every time.
PRAGMA foreign_key_check only knows declared keys. It is a great tool - if the schema declares the relations. Here it doesn't, so it finds nothing.

Without declared keys, the relations exist only in the code and in your head. So the first step is to write them down.

One query per relation

An orphan is a child row whose parent doesn't exist. For a single relation:

SELECT COUNT(*)
FROM SALES_LINE c
WHERE c.DOC_ID IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM SALES_DOC p WHERE p.DOC_ID = c.DOC_ID);

IS NOT NULL matters for optional relations: a sales line without a product (a free-text line) is fine, a sales line with a product ID that doesn't exist is not.

NOT EXISTS is usually the better choice over LEFT JOIN … WHERE p.DOC_ID IS NULL or NOT IN (SELECT …). NOT IN returns no rows at all if the subquery contains a NULL, which is a classic trap. NOT EXISTS has no such surprise, and SQLite can use the parent's primary key for the lookup.

In the app, one small entry runs this query for any child/parent pair and puts the result into a list keyed by TABLE.COLUMN:

entry ORPHAN
params
    string pChild : IN
    string pColumn : IN
    string pParent : IN
    string pKey : IN
    string pData : INOUT
    numeric pTotal : INOUT
endparams
variables
    numeric vN
    string vId
endvariables
    call COUNT_SQL("SELECT COUNT(*) FROM %%(pChild) c WHERE c.%%(pColumn) IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %%(pParent) p WHERE p.%%(pKey) = c.%%(pColumn))", vN)
    vId = "%%(pChild).%%(pColumn)"
    putitem/id pData, vId, vN
    if (vN > 0)
        pTotal = pTotal + vN
    endif
    return 0
end

The check itself is then just the list of relations - 48 of them for the newer half of the schema:

    call ORPHAN("SALES_DOC", "CUSTOMER_ID", "CUSTOMER", "CUSTOMER_ID", pData, pTotal)
    call ORPHAN("SALES_LINE", "DOC_ID", "SALES_DOC", "DOC_ID", pData, pTotal)
    call ORPHAN("SALES_LINE", "PRODUCT_ID", "PRODUCT", "PRODUCT_ID", pData, pTotal)
    call ORPHAN("PAYMENT", "DOC_ID", "SALES_DOC", "DOC_ID", pData, pTotal)
    call ORPHAN("DELIVERY_LINE", "DELIVERY_ID", "DELIVERY_DOC", "DELIVERY_ID", pData, pTotal)
    call ORPHAN("STOCK_MOVE", "WAREHOUSE_ID", "WAREHOUSE", "WAREHOUSE_ID", pData, pTotal)
    call ORPHAN("TICKET_COMMENT", "TICKET_ID", "TICKET", "TICKET_ID", pData, pTotal)
    call ORPHAN("JOB_RUN", "JOB_CODE", "JOB_DEF", "JOB_CODE", pData, pTotal)
    ...

The result lists every relation with its count - zeros included. A list that only shows problems looks the same whether everything is fine or the check didn't run. A list of 48 zeros is evidence.

This list is the documentation of the relations I never wrote anywhere else. If you only take one thing from this article: write the list, even if you never run it automatically.

Rows that are valid but wrong

Orphans are a structural problem. The more useful checks turned out to be business rules that no constraint could express. They all follow one pattern: one SELECT per rule, each returning the same columns plus a fixed issue code, glued together with UNION ALL:

SELECT p.PRODUCT_ID, p.SKU, p.PRODUCT_NAME, 'NO_PRICE'
  FROM PRODUCT p
 WHERE p.IS_ACTIVE = 1 AND p.PRICE_CENTS <= 0
UNION ALL
SELECT p.PRODUCT_ID, p.SKU, p.PRODUCT_NAME, 'NO_SUPPLIER'
  FROM PRODUCT p
 WHERE p.IS_ACTIVE = 1
   AND NOT EXISTS (SELECT 1 FROM PRODUCT_BUNDLE b WHERE b.BUNDLE_ID = p.PRODUCT_ID)
   AND NOT EXISTS (SELECT 1 FROM SUPPLIER_PRODUCT s WHERE s.PRODUCT_ID = p.PRODUCT_ID)
UNION ALL
SELECT p.PRODUCT_ID, p.SKU, p.PRODUCT_NAME, 'NO_MAIN_SUPPLIER'
  FROM PRODUCT p
 WHERE p.IS_ACTIVE = 1
   AND EXISTS (SELECT 1 FROM SUPPLIER_PRODUCT s WHERE s.PRODUCT_ID = p.PRODUCT_ID)
   AND NOT EXISTS (SELECT 1 FROM SUPPLIER_PRODUCT s WHERE s.PRODUCT_ID = p.PRODUCT_ID AND s.IS_MAIN = 1)
UNION ALL
SELECT p.PRODUCT_ID, p.SKU, p.PRODUCT_NAME, 'TAX_RATE'
  FROM PRODUCT p
 WHERE p.IS_ACTIVE = 1 AND p.TAX_RATE NOT IN (0, 7, 19)
ORDER BY 1, 4;

A few details that make this pattern work:

UNION ALL, not UNION. UNION removes duplicates, which costs a sort and can hide two different findings with identical columns.
ORDER BY with column numbers at the very end. In a compound select it applies to the whole result, and positions avoid alias trouble.
One product can appear several times, once per issue. That is intended: the report says what is wrong, not just that something is wrong.
Exceptions belong in the rule. Bundles are made of other products and have no supplier of their own, so NO_SUPPLIER excludes them. Without that exception every bundle would show up as a finding - and a report full of known false alarms teaches people to ignore it.

The same pattern covers documents and other areas:

Area	Issue	Rule
Documents	`OLD_DRAFT`	invoice draft older than N days
	`OLD_QUOTE`	quote still open after its validity date
	`STALE_ORDER`	open order older than N days with neither an invoice nor a delivery note
	`NO_LINES`	issued document without a single line
Suppliers	`NO_EMAIL`, `NO_ADDRESS`, `NO_PRODUCTS`	active supplier missing contact data or products
Tickets	`UNASSIGNED`, `STALE_TICKET`	open ticket without owner, or not touched for N days
Purchasing	`OVERDUE_PO`	sent purchase order past its due date

"N days" comes from a setting with a default (30 days for drafts and orders, 14 for tickets), so the thresholds can change without touching the SQL.

How the service hands results back

Every check is a public operation with the same shape: rights check first, then a private entry that does the work, and the result as a Uniface list in an OUT parameter.

public operation PRODUCT_DQ
params
    string pData : OUT
    numeric pCount : OUT
    string pError : OUT
endparams
    pData = ""
    pCount = 0
    pError = ""
    activate "RIGHTS_SVC".REQUIRE("READ", pError)
    if ($status < 0)
        return -1
    endif
    call PRODUCT_ROWS(pData, pCount, pError)
    return $status
end

PRODUCT_ROWS builds the UNION ALL statement step by step with %%(…) substitution and runs it with sql/data:

    vNoBundle = "NOT EXISTS (SELECT 1 FROM PRODUCT_BUNDLE b WHERE b.BUNDLE_ID = p.PRODUCT_ID)"
    vSql = "SELECT p.PRODUCT_ID, p.SKU, p.PRODUCT_NAME, 'NO_PRICE' FROM PRODUCT p WHERE p.IS_ACTIVE = 1 AND p.PRICE_CENTS <= 0"
    vSql = "%%(vSql) UNION ALL SELECT p.PRODUCT_ID, p.SKU, p.PRODUCT_NAME, 'NO_SUPPLIER' FROM PRODUCT p WHERE p.IS_ACTIVE = 1 AND %%(vNoBundle) AND NOT EXISTS (SELECT 1 FROM SUPPLIER_PRODUCT s WHERE s.PRODUCT_ID = p.PRODUCT_ID)"
    ...
    vSql = "%%(vSql) ORDER BY 1, 4"
    sql/data vSql, "CUSTOMERS"
    if ($status < 0)
        pError = $concat("The products could not be checked (error ", $procerror, ").")
        return -1
    endif
    pData = $result
    pCount = $itemcount(pData)

With /data, $result holds the whole result set as a Uniface list: one item per row, and each row is again a list of columns. A caller reads it with forlist and getitem - no entity, no form, no painted fields. The checks are reusable from a form, from a report and from a test in exactly the same way.

The orphan check returns a different kind of list: an associative one, built with putitem/id. The key is TABLE.COLUMN, the value the count, and a caller asks for one relation directly:

    getitem/id vDl, vData, "DELIVERY_LINE.DELIVERY_ID"

From rows to a report

All three rule groups go into one HTML file. A small entry turns any result list into a table, escaping every cell:

    forlist vRow in pData
        vLine = "<tr>"
        vI = 1
        while (vI <= pColumns)
            getitem vCell, vRow, vI
            call HTML_ESC(vCell)
            vLine = "%%(vLine)<td>%%(vCell)</td>"
            vI = vI + 1
        endwhile
        pHtml = $concat(pHtml, vLine, "</tr>", vNl)
    endfor

Escaping is not optional here. The report shows customer and supplier names, and the one with & or < in it will arrive eventually.

Testing the checks

A data quality check that never finds anything is indistinguishable from a broken one. The tests therefore create exactly one bad row per rule and assert the exact issue list per object:

    call ISSUES_OF(vData, "", "998631", vQ1)
    call ISSUES_OF(vData, "", "998632", vQ2)
    call ISSUES_OF(vData, "", "998633", vQ3)
    call CHECK("PRODUCT_DQ finds missing price, supplier, main supplier and odd tax rates", (vStatus = 0 & vQ1 = "NO_PRICE,NO_SUPPLIER" & vQ2 = "NO_MAIN_SUPPLIER,TAX_RATE" & vQ3 = ""), ...)

The third product is the important one: it is correct and must produce no finding. Without such a "clean" case, a rule that flags everything would pass.

For orphans, the test counts before, inserts one orphan delivery line and one orphan ticket comment, counts again and expects exactly two more. It also checks that the result has 48 entries, so a relation that silently drops out of the list fails the test.

Takeaways

Foreign keys in SQLite are off unless you switch them on, per connection - and foreign_key_check only knows declared keys.

Write down every relation and check it with NOT EXISTS. Report zeros too.

One SELECT per business rule, combined with UNION ALL, each with a fixed issue code.

Put the exceptions into the rule before the report teaches people to ignore it.

Test every rule with one bad and one good row.

A customer maintenance form in Uniface 10, part 13 - salted password hashes, rights checks in the services, and a session timeout

Franz — Sun, 04 Oct 2026 16:40:08 +0000

Part 9 added users, three roles and a login lockout - as services, without a login dialog. The password hash from part 9 was one HMAC-SHA256 per password, keyed with the application name and the user name. That is better than plain text and worse than anything you would choose today: no random salt, one round, fast to brute-force.

This part closes three gaps:

a v2 password hash with a random salt and a configurable number of rounds, plus an upgrade path for existing v1 hashes,
rights checks inside the services that change data, not only in the forms,
a session timeout that logs a user out after a period of inactivity.

And it finally adds the forms that make all of this usable: a login dialog and a change-password dialog.

The v2 hash

The stored value carries everything needed to check it later:

v2:<salt as hex>:<rounds>:<hash as hex>

The version prefix is the important part. It lets old and new hashes live in the same column, and it leaves room for a v3.

Generating a hash:

entry HASH_V2
params
    string pPassword : IN
    string pHash : OUT
endparams
variables
    string vSalt, vHex, vRoundsText
    numeric vRounds
endvariables
    pHash = ""
    activate "SETTINGS_SVC".GET_SETTING("PASSWORD_ROUNDS", "10000", vRoundsText)
    vRounds = vRoundsText
    if (vRounds < 1)
        vRounds = 10000
    endif
    vSalt = $encode("HEX", $random("raw", 16))
    if (vSalt = "")
        return -1
    endif
    call DERIVE(pPassword, vSalt, vRounds, vHex)
    pHash = "v2:%%(vSalt):%%(vRounds):%%(vHex)"
    return 0
end

$random("raw", 16) returns 16 random bytes; $encode("HEX", ...) makes them storable. The derivation itself is a plain loop:

entry DERIVE
params
    string pPassword : IN
    string pSalt : IN
    numeric pRounds : IN
    string pHex : OUT
endparams
variables
    numeric vRound
endvariables
    pHex = pSalt
    vRound = 0
    while (vRound < pRounds)
        pHex = $encode("HEX", $encode("HMAC_SHA256", pHex, pPassword))
        vRound = vRound + 1
    endwhile
    return 0
end

Each round feeds the previous result back into HMAC-SHA256 with the password as the key. The salt is the starting value, so the same password gives a different hash for every user.

To be clear about what this is: an iterated HMAC, not PBKDF2, bcrypt or Argon2. $encode offers HMAC-SHA256 but none of the standard key-derivation functions, so this is the closest thing that can be built from what is there. It makes brute force slower by the round count and defeats precomputed tables because of the salt. It is not a reviewed standard construction, and if the platform ever offers PBKDF2, the version prefix makes a switch possible.

Two more weak spots I would point out in a review myself:

The comparison is a plain string comparison (vCalc = vHash), not a constant-time one. For a desktop application that checks passwords locally, timing attacks are not a realistic threat; for a network service it would matter.
A loop of HMAC calls in an interpreted language is slow per round. That means fewer rounds for the same waiting time than a native implementation would get, which is why the round count has to be measured (see below).

Checking a password, and upgrading on the fly

Checking reads the stored value, splits it, and re-runs the derivation:

    if (pStored[1:3] = "v2:")
        vList = $replace(pStored, 1, ":", $string("&uSEP;"), -1)
        getitem vVersion, vList, 1
        getitem vSalt, vList, 2
        getitem vRounds, vList, 3
        getitem vHash, vList, 4
        if (vSalt = "" | vHash = "" | vRounds < 1)
            return 0
        endif
        call DERIVE(pPassword, vSalt, vRounds, vCalc)
        if (vCalc = vHash)
            pOk = 1
        endif
        return 0
    endif
    vKey = $concat("CUSTOMER_MANAGEMENT:", $uppercase(pUser))
    vCalc = $encode("HEX", $encode("HMAC_SHA256", pPassword, vKey))
    if (vCalc = pStored)
        pOk = 1
        pOld = 1
    endif

Replacing : with the Uniface item separator turns the stored string into a list that getitem can read. Anything without the v2: prefix is treated as a v1 hash and checked the old way. If that succeeds, pOld is set.

The login uses that flag: after a successful v1 check it computes a v2 hash from the password it has just been given and stores it. Users never notice the migration, and after everyone has logged in once there are no v1 hashes left. This is the only moment the plain-text password is available, so it is the only moment the upgrade can happen.

The login operation also returns more than ok/not ok:

Status	Meaning
0	logged in
1	no users existed; this user was created as administrator
2	logged in, but the password was reset and must be changed now
-1	refused (text in `pError`)

Status 2 comes from an administrator's password reset. The login dialog reacts to it by opening the change-password dialog straight away and logging the user out again if they cancel.

How many rounds?

"10000 rounds" is a guess. Whether that takes 20 milliseconds or two seconds depends on the machine. So the service measures it:

public operation CALIBRATE
params
    numeric pTargetMs : IN
    numeric pRounds : OUT
    numeric pMs : OUT
    string pError : OUT
endparams
    ...
    call NOW_MS(vStart)
    call DERIVE("calibration", "00", 2000, vHex)
    call NOW_MS(vEnd)
    pMs = vEnd - vStart
    if (pMs < 1)
        pMs = 1
    endif
    sql "SELECT MAX(1000, MIN(500000, CAST(%%(vTarget) * 2.0 / %%(pMs) AS INTEGER) * 1000))", "CUSTOMERS"
    ...
    pRounds = $result
    vRounds = "%%(pRounds)"
    activate "SETTINGS_SVC".SET_SETTING("PASSWORD_ROUNDS", vRounds, pError)
    return $status
end

It runs 2000 rounds, measures the time, and scales up to the target time (for example 300 ms), rounded down to whole thousands and kept between 1000 and 500000. Existing hashes keep their own round count, because it is stored in the hash. Only new hashes use the new setting.

The millisecond clock comes from SQLite, because a connection is open anyway:

entry NOW_MS
params
    numeric pMs : OUT
endparams
    pMs = 0
    sql "SELECT CAST((julianday('now') - 2440587.5) * 86400000 AS INTEGER)", "CUSTOMERS"
    if ($status >= 0)
        pMs = $result
    endif
    return 0
end

julianday('now') - 2440587.5 is days since 1970; times 86400000 gives milliseconds. The settings form has a "Calibrate" button that calls this operation.

Rights checks belong in the services

Part 9 defined the roles READ < EDIT < ADMIN and a HAS_RIGHT check. What was missing was a place that enforces it. Hiding buttons in the menu is not enforcement: every service can be called from any other component.

So there is a small service whose only job is to refuse with a readable sentence:

public operation REQUIRE
params
    string pRight : IN
    string pError : OUT
endparams
variables
    string vUser, vRole
    boolean vAllowed
endvariables
    pError = ""
    activate "USER_SVC".HAS_RIGHT(pRight, vAllowed)
    if (vAllowed)
        return 0
    endif
    activate "USER_SVC".CURRENT_USER(vUser, vRole)
    pError = "This function needs the role %%(pRight). You are logged in as %%(vUser) with the role %%(vRole)."
    return -1
end

and one line at the top of every service operation that changes data in bulk or for good - merge, anonymize, import, the bulk clean-ups:

    activate "RIGHTS_SVC".REQUIRE("EDIT", pError)
    if ($status < 0)
        return -1
    endif

The clean-up service has a dry-run mode that only counts. The check sits inside if (!pDryRun), so a reader may still see what would change.

One design decision needs stating honestly. Without a login, CURRENT_USER returns the operating system user with the role EDIT. That keeps all older tests and the developer's own Compile & Test runs working without logging in first. It also means that anyone who can start the components directly, without the main menu, works with EDIT rights. The main menu now always asks for a login first, but the default is a convenience for development, not a security boundary.

The tests for this part log in a READ user and check that merge, anonymize, import and the real clean-up are all refused with a text that names the role, that the clean-up dry run is still allowed, and that everything works again after logout.

A session timeout without a timer

The logged-in user lives in Uniface's numbered general variables ($91 user, $92 role), which keep their value as long as the application runs. A third one, $93, holds the time of the last activity in milliseconds:

public operation CHECK
params
    string pError : OUT
endparams
variables
    string vText
    numeric vNow, vLast, vLimit, vIdle
endvariables
    pError = ""
    call NOW_MS(vNow)
    if ($91 = "")
        $93 = vNow
        return 0
    endif
    activate "SETTINGS_SVC".GET_SETTING("SESSION_TIMEOUT", "30", vText)
    vLimit = vText
    vLast = $93
    if (vLimit > 0 & vLast > 0)
        vIdle = (vNow - vLast) / 60000
        if (vIdle >= vLimit)
            activate "USER_SVC".LOGOUT()
            $93 = ""
            pError = "Your session has expired after %%(vLimit) minutes without activity. Please log in again."
            return -1
        endif
    endif
    $93 = vNow
    return 0
end

There is no background timer. The check runs whenever the user picks a function in the main menu or the "More functions" menu:

entry SESSION_OK
variables
    string vError
    numeric vOk
endvariables
    activate "SESSION_SVC".CHECK(vError)
    if ($status < 0)
        message/info vError
        activate "LOGIN_FRM".exec(vOk)
        if (vOk != 1)
            return -1
        endif
    endif
    return 0
end

If the session has expired, the user sees why and gets the login dialog. Logging in again continues with the function they picked.

The limitation is obvious from the code: someone who leaves a form open is only caught at their next menu choice, not while the form is on screen. A timeout of 0 switches the check off.

Testing time-based behaviour without waiting 30 minutes needs a back door, and the service has one: SET_LAST_ACTIVITY(pMinutesAgo) moves the last activity into the past. The tests use it to check "3 idle minutes are fine", "after the limit the user is logged out" and "timeout 0 never expires".

The two dialogs

The login dialog is small: user name, password, two buttons and an info line. Two details are worth copying:

The password field is cleared right after its value has been read, before the service call. Whatever happens next, the password is not left on screen.
When the user table is empty, the info line says "No users yet. The first login creates the administrator." Without that hint, the first start of a fresh installation looks like a login with no valid account.

The result is passed back through a component variable, because edit returns before the form closes:

operation exec
params
    numeric pResult : OUT
endparams
    ...
    $cResult$ = 0
    edit
    pResult = $cResult$
end

Component variables are declared in the form's Declarations section and must be written with dollar signs in the code ( $cResult$ ). Writing cResult instead compiles, but with a warning "Field 'CRESULT' not found", and the value goes nowhere.

The change-password dialog asks for the old password and the new one twice, and shows the password rule (at least 8 characters with a letter and a digit) before the user types anything.

Results

New services: RIGHTS_SVC, SESSION_SVC. Additions to AUTH_SVC (CALIBRATE). Rights checks in four existing services.
The test service for this stage has 34 tests; together with the older suites, 8 test services and 371 tests run in one go, all passing, and the database is unchanged afterwards.
A click test against the real database: the first login created the administrator, the main menu, the new sub-menu and the settings, user, password and other dialogs opened, and the test user was removed again afterwards.

Takeaways

Version your hashes. A prefix like v2: costs three characters and makes every future change a migration instead of a reset.

Upgrade at login. It is the only time you have the plain password, so it is the only time you can re-hash it.

Measure the cost, don't guess it. A calibration that writes the round count into a setting is twenty lines.

Say what your construction is. An iterated HMAC is not PBKDF2. Writing that down keeps the next reader from trusting it more than it deserves.

Enforce rights where the data changes. Menus decide what users see; services decide what happens.

A timeout does not need a timer. Checking at every menu action is simple, testable, and good enough for a desktop application - as long as you know where it stops.

DEV Community: Franz

The Uniface test that "hung" had died - finding a silent crash with SQLite

The symptom

The wrong suspects

Why the log pointed at the wrong place

A trace that cannot be lost

The statement

The fix: validation belongs in ProcScript

Takeaways

Six ProcScript surprises from 60 Uniface services

1. One variable as input and output

2. Blanks at the end don't survive the round trip

3. Two timestamps in a row are often the same

4. lfiledump with "UTF-8" writes a BOM

5. Hashing a file the way sha256sum does

6. One SQL statement can end the process

Takeaways

40 features, 872 tests, one SQLite file - how the Uniface test suite stayed honest

1. A work list in a file

2. Each suite owns a range

3. Measure first, then compare the difference

4. A setting that leaked between tests

5. Make sure the IDE has the code you tested

6. When a test fails, check the expectation first

Results

Takeaways

An invoice archive with SHA-256 checksums in Uniface - and where it is weaker than it looks

The table

Archiving a year

The checksum

Checking the archive

Limit 1: this hashes text, not bytes

Limit 2: the checksums live next to the files

Limit 3: archiving again replaces the old entry

So is it useful?

Takeaways

A job scheduler in two tables - recurring tasks inside a Uniface app

Two tables

Defining a job

Running what is due

Moving the next run

The job that can't be tested like the others

What still starts the scheduler

Takeaways

A four-eyes approval in a Uniface service - one table, two rules, and the gaps it leaves

The table

When is an approval needed?

The request validates everything up front

Rule 1: someone else decides

Rule 2: the approver executes

What this does not protect against

What the tests cover

Takeaways

The Uniface CSV import that never found its header line

The import in short

What went wrong

The fix: name the characters explicitly

Two more things a CSV loader needs

Errors per line, not per file

What it still doesn't do

Takeaways

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

Why not a separate test database?

Rule 1: Test data lives in reserved ranges

Rule 2: Clean up before and after

Rule 3: Nothing is committed

The proof: one checksum

Files are data too

What it costs

What it gives

Takeaways

Schema migrations for a Uniface app on SQLite - without a migration tool

The rule: only add, never change

New tables, no new entities

Step 1: one script per stage

Step 2: back up the file

Step 3: dry run on a copy

Step 4: apply it, one statement at a time

Step 5: let the app check itself

Step 6: run all tests and note the checksum

4. `lfiledump` with "UTF-8" writes a BOM

5. Hashing a file the way `sha256sum` does