A customer maintenance form in Uniface 10, part 10 - a CSV import with a dry run, and backing up a SQLite file Uniface keeps o

Part 8 wrote customers to a CSV file. The obvious next request is the reverse: "we have a list from the trade fair, can you load it?". And once an import can write a hundred customers in one click, the next question follows immediately: "and how do I get back to yesterday if it went wrong?".

So this part has two services:

• IMPORT_CUSTOMERS reads the export format back, validates every line with the existing business rules, and writes a log that says per line what happened. A dry run shows that log without changing anything.
• BACKUP copies the database into a folder with a timestamp and keeps the newest n copies. BACKUP_IF_DUE does that at most once per day.

The backup part contains the most interesting finding of this post: the SQLite feature I planned to use did not work inside Uniface, and I only noticed because the test log said so.

Import: the format is the export format

There is exactly one accepted format: the ten columns of the export, separated by ;, with the header line.

ID;Last name;First name;E-mail;Phone;Active;Street;Postal code;City;Country

That decision removes a whole class of questions (column mapping, other separators, other encodings) and it gives a free round-trip test: whatever the export writes, the import must read.

• ID may be empty - then the customer gets the next number from CUSTOMER_SEQ.
• Active is Y, N or empty (= Y).
• Address columns are optional; if street, postal code or city is filled, a billing address is created.

Reading the file

    fileload pFileName, vText, "UTF-8"
    ...
    vFirstCh = $string("")
    if (vText[1:1] = vFirstCh)
        vCount = $length(vText) - 1
        vText = vText[2:vCount]
    endif
    vSep = $string("&uSEP;")
    if ($scan(vText, $string("
")) > 0)
        vText = $replace(vText, 1, $string("
"), vSep, -1)
    endif
    if ($scan(vText, $string("
")) > 0)
        vText = $replace(vText, 1, $string("
"), vSep, -1)
    endif
    if ($scan(vText, $string("
")) > 0)
        vText = $replace(vText, 1, $string("
"), vSep, -1)
    endif
    vLines = vText

Three things happen here:

1. The BOM is removed. Part 8 showed that lfiledump writes one. fileload does not strip it, so the first header field would otherwise be \uFEFFID and the header check would fail on our own export.
2. All line endings become the Uniface list separator (&uSEP;, the GOLD ;). CRLF first, then lone CR, then lone LF - the order matters, otherwise CRLF would produce empty lines.
3. After that, the text is a Uniface list and forlist vLine in vLines walks the lines. No manual line splitting.

A CSV parser in 40 lines

$replace on ; would break on "Main St; Apt 1", which the export deliberately produces. So the split is a small state machine: inside or outside quotes, and a doubled quote inside quotes is a literal quote.

entry SPLIT_CSV
params
    string pLine : IN
    string pFields : OUT
    numeric pCount : OUT
endparams
variables
    string vQ, vCh, vNext, vCur, vSep
    numeric vPos, vLen, vNextPos
    boolean vInQuotes
endvariables
    pFields = ""
    pCount = 0
    vQ = $string(""")
    vSep = $string("&uSEP;")
    vLen = $length(pLine)
    vCur = ""
    vInQuotes = 0
    vPos = 1
    while (vPos <= vLen)
        vCh = pLine[vPos:1]
        if (vInQuotes)
            if (vCh = vQ)
                vNextPos = vPos + 1
                vNext = ""
                if (vNextPos <= vLen)
                    vNext = pLine[vNextPos:1]
                endif
                if (vNext = vQ)
                    vCur = $concat(vCur, vQ)
                    vPos = vPos + 1
                else
                    vInQuotes = 0
                endif
            else
                vCur = $concat(vCur, vCh)
            endif
        else
            if (vCh = vQ)
                vInQuotes = 1
            elseif (vCh = ";")
                call ADD_FIELD(pFields, pCount, vCur, vSep)
                vCur = ""
            else
                vCur = $concat(vCur, vCh)
            endif
        endif
        vPos = vPos + 1
    endwhile
    call ADD_FIELD(pFields, pCount, vCur, vSep)
    return 0
end

ADD_FIELD appends with the list separator and counts, so the caller gets a Uniface list plus the number of columns. SPLIT_LINE is a public wrapper so the test service can call the parser directly.

A character-by-character loop in ProcScript is not fast. For files with a few thousand lines it does not matter; for a hundred thousand it would.

Every line goes through the same rules as the form

IMPORT_LINE does not have its own validation. It calls the services the form uses:

    activate "CUSTOMER_SVC".VALIDATE(vLast, vFirst, vEmail, vPhone, vField, vError)
    if ($status < 0)
        pMsg = vError
        return -1
    endif
    ...
    activate "CUSTOMER_SVC".COUNT_DUPLICATES_EX(0, vLast, vFirst, vEmail, vPhone, vCount, vReason, vError)
    ...
    if (vCount > 0)
        pMsg = "possible duplicate of an existing customer (same %%(vReason))"
        return -1
    endif
    ...
    if (vStreet != "" | vPostal != "" | vCity != "")
        activate "ADDRESS_SVC".VALIDATE(vType, vStreet, vPostal, vCity, vCountry, vField, vError)
        if ($status < 0)
            pMsg = $concat("address: ", vError)
            return -1
        endif
    endif

This is where part 3 pays off. Because the rules were moved out of the form into services back then, the import gets mandatory last name, the e-mail check, duplicate detection (same name, same e-mail ignoring case, same phone in another format) and the German postal code rule for free - with exactly the same messages the user knows from the form.

A given ID is checked for digits and for existence. If it is higher than the current counter, the counter is moved behind it, so the next customer created in the form does not collide:

        vSql = "UPDATE CUSTOMER_SEQ SET LAST_ID = %%(pNewId) WHERE SEQ_NAME = 'CUSTOMER' AND LAST_ID < %%(pNewId)"

Each imported customer gets a history entry IMPORTED (see part 9, so later nobody wonders where a customer came from.

The log is the user interface

IMPORT_CUSTOMERS returns two counters and a log text. This is the log of the test file:

Line 2: imported as customer 15
Line 3: imported as customer 999982
Line 4: skipped - customer 999981 already exists
Line 5: skipped - The e-mail address must contain an @ character.
Line 6: skipped - possible duplicate of an existing customer (same name)
Line 7: skipped - expected 10 columns, found 3
Line 8: skipped - address: A German postal code has exactly five digits.
Line 9: imported as customer 999983

Line numbers refer to the file, header included, so the user can open the file in an editor and jump to the line. A bad line is skipped, the rest is imported - one typo does not cancel 499 good customers.

The dry run is a rollback

The dry run does not simulate anything. It runs the real import - inserts, counter updates, history rows - and then rolls back:

    if (pDryRun)
        rollback
    endif
    return 0

That makes the dry run exact by construction. It cannot disagree with the real run, because it is the real run. Duplicates within the file are found too, because line 6 sees the customer that line 2 has just inserted.

The caller decides what happens after a real run: commit, or rollback if the log looks wrong.

The catch - and a reader will point it out - is that rollback rolls back everything in the current transaction, not only the import. If the calling form has unsaved changes in the same transaction, a dry run throws them away. The import must therefore be started from a place where nothing else is pending. For the tools form that is the case; it would not be the case if someone called the import from inside the customer form.

Backup

The plan: VACUUM INTO

SQLite 3.27+ has VACUUM INTO 'file', which writes a consistent, compacted copy of the open database. That is the textbook way to back up a live SQLite database, and it is one SQL statement:

    sql "SELECT strftime('%Y%m%d_%H%M%S', 'now', 'localtime')", "CUSTOMERS"
    vStamp = $result
    vTarget = $concat(vDir, "customers_", vStamp, ".db")
    ...
    vSql = "VACUUM INTO '%%(vEsc)'"
    sql vSql, "CUSTOMERS"
    vStatus = $status
    if (vStatus >= 0 & $lfileexists(vTarget) = 1)
        pMethod = "VACUUM INTO"
    else
        lfilecopy pDbFile, vTarget
        if ($procerror < 0 | $lfileexists(vTarget) != 1)
            pError = $concat("The backup could not be written (error ", $procerror, ").")
            return -1
        endif
        pMethod = "file copy"
    endif

The fallback to a plain file copy was meant for old SQLite versions. The service reports which method it used in pMethod.

What the test log said

I/O function: Q, mode: 0, on driver: SLE
cannot VACUUM from within a transaction
cannot VACUUM from within a transaction
Backup method: file copy, file C:/Uniface_Projekte/Kundenverwaltung/export/test/backup/customers_20260924_204313.db
PASS: BACKUP file is written

All tests passed. But the method was file copy, not VACUUM INTO. SQLite refuses VACUUM inside an open transaction, and in the test there is always one, because the tests insert data first and roll back at the end.

That raises two questions I cannot fully answer yet:

1. Is there an open transaction in normal use too? Uniface's SLE driver works in transactions; after any data access in the session there may be one open until the next commit/rollback. The practical rule is: call the backup directly after a commit, and check pMethod.
2. Is the file copy consistent? A copy of the .db file while another connection - here the same Uniface process - has uncommitted changes can be inconsistent. In rollback-journal mode SQLite may already have written changed pages into the main file, with the originals in the -journal. A copy of only the .db at that moment is not guaranteed to be a valid database. The test checks that the file exists, not that it opens.

So the honest status is: the backup works, but in the test situation it took the weaker path. The missing test is "open the backup and run PRAGMA integrity_check". That is on the list, together with a commit before VACUUM INTO in the tools form.

I left this in the article on purpose. Without the putmess of the method, the test would just have said "PASS" and I would have believed that VACUUM INTO was in use.

Rotation without a date parser

Backups are called customers_YYYYMMDD_HHMMSS.db. That format sorts lexically in time order, so "the oldest backup" is simply the smallest file name:

    call BACKUP_FILES(pDir, vFiles)
    vCount = $itemcount(vFiles)
    while (vCount > pKeep)
        vOldest = ""
        vOldIdx = 0
        vIdx = 1
        while (vIdx <= vCount)
            getitem vFile, vFiles, vIdx
            if (vOldIdx = 0 | vFile < vOldest)
                vOldest = vFile
                vOldIdx = vIdx
            endif
            vIdx = vIdx + 1
        endwhile
        lfiledelete $concat(pDir, vOldest)
        ...
        delitem vFiles, vOldIdx
        vCount = $itemcount(vFiles)
    endwhile

BACKUP_FILES uses $ldirlist(dir, "FILE") and keeps only names that start with customers_ and end with .db. Other files in the folder are never touched - one test puts an unrelated file there and checks that it survives the rotation.

Two backups in the same second would collide; the service refuses the second one with "try again in a second" instead of overwriting.

Once per day

BACKUP_IF_DUE looks for a file with today's prefix and does nothing if it finds one:

    sql "SELECT strftime('%Y%m%d', 'now', 'localtime')", "CUSTOMERS"
    vToday = $result
    vPrefix = "customers_%%(vToday)_"
    vLen = $length(vPrefix)
    activate $instancename.LIST_BACKUPS(pTargetDir, vFiles)
    forlist vFile in vFiles
        if (vFile[1:vLen] = vPrefix)
            return 0
        endif
    endfor

The file system is the state. There is no "last backup" setting that could disagree with what is actually in the folder.

A note on % inside SQL strings: in ProcScript %% starts a substitution (%%(var), %%^), but a single % is a normal character. strftime('%Y%m%d', ...) works as written.

Settings instead of hard-coded paths

Folder, number of copies and the auto-backup switch come from a small key/value table:

CREATE TABLE APP_SETTING (SETTING_KEY VARCHAR(30) NOT NULL PRIMARY KEY, SETTING_VALUE VARCHAR(200));

SETTINGS_SVC.GET_SETTING(key, default, value) always returns something: the stored value, or the default if the key is missing, empty or the query fails. Callers never need an error branch just to read a folder name. SET_SETTING upper-cases and validates the key and uses INSERT OR REPLACE.

Tests

PASS: IMPORT CSV line with quotes and empty fields is split
PASS: IMPORT dry run counts imported and skipped lines
PASS: IMPORT dry run leaves the database unchanged
PASS: IMPORT real run imports three customers
PASS: IMPORT log names the existing ID
PASS: IMPORT log names the duplicate
PASS: IMPORT given ID and inactive flag are used
PASS: IMPORT number range is moved behind the given ID
PASS: IMPORT billing address is created
PASS: IMPORT quoted value with quotes and separator
PASS: IMPORT writes a history entry
PASS: IMPORT missing file is reported
PASS: IMPORT wrong header is reported
PASS: BACKUP missing folder is reported
PASS: BACKUP missing database file is reported
PASS: BACKUP file is written
PASS: BACKUP two oldest backups are deleted
PASS: BACKUP two backups remain, other files are kept
PASS: SETTING unknown key returns the default
PASS: SETTING is saved trimmed with an upper-case key
PASS: SETTING is overwritten
PASS: SETTING name with a space is rejected
PASS: SETTING value with a quote
PASS: SETTING list contains the new key
PASS: AUTO BACKUP first call of the day writes a backup
PASS: AUTO BACKUP second call of the day does nothing

"dry run leaves the database unchanged" checks that none of the test customers exist after the dry run. It does not yet check the number-range counter, which the dry run also moves and then rolls back - an obvious additional assertion.

The test backups go to export/test/backup/, never to the real backup folder.

Known limits

• Line breaks inside quoted fields are not supported: the file is split into lines before the CSV parser sees it. The export never writes them (there is no multi-line customer field), so round trips work, but a CSV from Excel with a line break in a cell would be split wrongly.
• No savepoint per line. Everything that could fail is validated before the first insert of a line, so a half-imported line needs a database error in the middle. If that happens, the customer row stays and the address is missing. A SAVEPOINT per line would fix it.
• The dry run discards other pending changes (see above).
• Backup consistency is not yet verified by opening the copy (see above).

Takeaways

Accept one format - your own. Reading exactly what you write gives you a round-trip test for free and avoids a mapping UI nobody asked for.

Reuse the rules. Because validation lives in services, the import has the same checks and messages as the form, in about ten lines.

A dry run can be the real run plus a rollback. It cannot lie, but it rolls back more than just itself.

Log the path your code took, not only the result. "PASS: BACKUP file is written" was true; "Backup method: file copy" was the useful information.

Next part: finding duplicates, merging two customers into one, and answering a GDPR request.