A customer maintenance form in Uniface 10, part 6 - the first 1:n relation (addresses)

Until now the application had exactly one table. Every screen, every test and every lesson in parts 1-5 was about a single CUSTOMER row. Real customers have more than one address - one for invoices, one for deliveries, maybe a PO box - and that makes this step the first real 1:n relation in the project.

It touched every layer: a new table, a second number range, a new modeled entity, a validation service with its own test service, a new form with an editable grid, and a change to the delete logic of the existing form. This post goes through them in that order, including the two places where the IDE quietly did something other than what I asked.

Decision first: a separate form, not a second grid

The obvious place for addresses would have been a second grid inside the customer form. Two reasons against it:

1. The layout had no room left. Part 5 already showed how rigid the form painter is: frames cannot overlap, frame heights could not be changed, and there was no free block large enough for a grid.
2. A separate form is a module. An ADDRESS_FRM that takes a customer ID as a parameter can be opened from the customer form today and from a main menu tomorrow (part 7) without knowing who called it.

So: ADDRESS_FRM as a modal form with the signature exec(pCustomerId, pCustomerName), and a button "Addresses" in the customer form.

The table

CREATE TABLE CUSTOMER_ADDRESS (
  ADDRESS_ID   INTEGER     NOT NULL PRIMARY KEY,
  CUSTOMER_ID  INTEGER     NOT NULL,
  ADDRESS_TYPE VARCHAR(10) NOT NULL,
  STREET       VARCHAR(60) NOT NULL,
  POSTAL_CODE  VARCHAR(10) NOT NULL,
  CITY         VARCHAR(60) NOT NULL,
  COUNTRY      VARCHAR(2),
  CREATED_AT   DATETIME    NOT NULL,
  CHANGED_AT   DATETIME,
  CHANGED_BY   VARCHAR(30),
  VERSION_NO   INTEGER
);
CREATE INDEX IX_CUSTOMER_ADDRESS_CUSTOMER ON CUSTOMER_ADDRESS (CUSTOMER_ID);
INSERT INTO CUSTOMER_SEQ (SEQ_NAME, LAST_ID) VALUES ('ADDRESS', 0);

Three statements, three executions in the EDIT SQL dialog, each followed by COMMIT. The address table gets the same audit columns and the same VERSION_NO as the customer table from part 4, so the optimistic locking works the same way. No foreign key constraint: SQLite only enforces foreign keys when a pragma is switched on per connection, and I did not want correctness to depend on a connection setting I do not control through the Uniface driver. The service owns the relationship instead - more on that below.

One number-range service for everything

Customers got their IDs from a table CUSTOMER_SEQ since part 1, through an operation CUSTOMER_SVC.NEXT_ID. Addresses need the same mechanism with a different counter. Instead of copying it, the customer service got a general operation, and the old one became a thin wrapper so that no caller and no test had to change:

public operation NEXT_RANGE_ID
params
    string pRange : IN
    numeric pId : OUT
    string pError : OUT
endparams
    call TAKE_NEXT(pRange, pId, pError)
    return $status
end

public operation NEXT_ID
params
    numeric pId : OUT
    string pError : OUT
endparams
    call TAKE_NEXT("CUSTOMER", pId, pError)
    return $status
end

entry TAKE_NEXT
params
    string pRange : IN
    numeric pId : OUT
    string pError : OUT
endparams
variables
    string vRange, vSql
endvariables
    pError = ""
    pId = 0
    vRange = pRange
    call SQL_ESCAPE(vRange)
    vSql = "UPDATE CUSTOMER_SEQ SET LAST_ID = LAST_ID + 1 WHERE SEQ_NAME = '%%(vRange)'"
    sql vSql, "CUSTOMERS"
    if ($status < 0)
        pError = $concat("The number range ", pRange, " could not be updated (error ", $procerror, ").")
        return -1
    endif
    vSql = "SELECT LAST_ID FROM CUSTOMER_SEQ WHERE SEQ_NAME = '%%(vRange)'"
    sql vSql, "CUSTOMERS"
    if ($status < 1)
        pError = $concat("The number range ", pRange, " is missing in table CUSTOMER_SEQ.")
        return -1
    endif
    pId = $result
    return 0
end

Update first, then read: the UPDATE takes the write lock, so two sessions cannot read the same value and both increment it. The range name is escaped even though today it is always a constant - the test for it costs one line and removes a whole category of "that will never happen".

After replacing the operation, the existing customer tests ran unchanged: 35 tests, 0 failures. That is the point of keeping NEXT_ID as a wrapper.

The modeled entity - and why the template fields had to go

A new modeled entity is created by dragging the template "Modeled Entity: DBMS Entity" from the project palette onto the project row and renaming it (CUSTOMER_ADDRESS.CUSTOMER_MDL). The template comes with two fields, KEYFIELD and FIELD. Renaming them to ADDRESS_ID and CUSTOMER_ID looked like a shortcut.

It was not. Both template fields have data type String, and the data type could not be changed in the property grid at the time; setting only the interface to I4 produced a conflict marker. The clean way was to delete the two template fields and drag the fields from the typed templates (Numeric field, String field, Date-Time field) onto the entity row, one per column, then rename each.

Two lessons from earlier parts were applied up front this time:

• No MAN and no LEN(1-..) in the model. In part 3 a mandatory field syntax in the model fought with the validation in the service, and the model won at the worst possible moment (error 0129 when leaving an occurrence). Required fields are checked by the service, full stop.
• Remove throws from the generated read, write and delete triggers. In part 4 the throws on the generated write trigger turned a locking conflict into an uncaught exception that closed the application. A new entity gets the same generated triggers, so it gets the same edit.

The address service

ADDRESS_SVC does four things: validate one address, hand out address IDs, count the addresses of a customer, and delete them.

public operation VALIDATE
params
    string pType : INOUT
    string pStreet : INOUT
    string pPostalCode : INOUT
    string pCity : INOUT
    string pCountry : INOUT
    string pField : OUT
    string pError : OUT
endparams
    pField = ""
    pError = ""
    call TRIM_TEXT(pType)
    call TRIM_TEXT(pStreet)
    call TRIM_TEXT(pPostalCode)
    call TRIM_TEXT(pCity)
    call TRIM_TEXT(pCountry)
    pType = $uppercase(pType)
    pCountry = $uppercase(pCountry)
    if (pCountry = "")
        pCountry = "DE"
    endif
    if (pType != "BILLING" & pType != "DELIVERY" & pType != "POSTAL")
        pField = "ADDRESS_TYPE"
        pError = "Please choose an address type: BILLING, DELIVERY or POSTAL."
        return -1
    endif
    if (pStreet = "")
        pField = "STREET"
        pError = "Please enter a street."
        return -1
    endif
    ...
    if (pCountry = "DE")
        call IS_DIGITS(pPostalCode)
        if ($status < 0 | $length(pPostalCode) != 5)
            pField = "POSTAL_CODE"
            pError = "A German postal code has exactly five digits."
            return -1
        endif
    endif
    return 0
end

The same pattern as the customer validation from part 2, and it keeps paying off:

• INOUT parameters normalise the input. The caller passes what the user typed; it gets back trimmed, upper-cased values with a default country. The form simply writes them back into its fields.
• pField names the culprit. The form uses it to put the cursor into the field that is wrong, without knowing the rules.
• Country-specific rules are explicit. Five digits for German postal codes, anything goes for GB (SW1A 1AA is a valid test case). An empty country means DE, and the test proves that an empty country gets the German rule.

COUNT_FOR_CUSTOMER and DELETE_FOR_CUSTOMER are one SQL statement each. They exist so that the customer form can cascade a delete without containing SQL.

21 tests before the first pixel

The test service follows the pattern from part 3: an exec that runs everything, a CHECK entry that logs PASS/FAIL with putmess, a rollback at the end so the database is untouched. A helper entry runs one validation case per line, which makes the table of cases easy to read and to extend:

call VALIDATE_CASE("ADDRESS German postal code too short", "BILLING", "Musterstrasse 1", "1234", "Musterstadt", "DE", -1, "POSTAL_CODE", pTests, pFailures)
call VALIDATE_CASE("ADDRESS foreign postal code with letters", "POSTAL", "Test Street 1", "SW1A 1AA", "London", "GB", 0, "", pTests, pFailures)
call VALIDATE_CASE("ADDRESS empty country defaults to DE rules", "BILLING", "Musterstrasse 1", "1234", "Musterstadt", "", -1, "POSTAL_CODE", pTests, pFailures)

Beyond validation, the tests cover the number range (two IDs in a row, own counter, unknown range name, a range name containing a quote) and the per-customer operations - three test addresses for two customers, then "count gives 2" and "delete removes only this customer's addresses".

The log after the first run:

Address service tests started
PASS: ADDRESS valid German address
PASS: ADDRESS valid French address
PASS: ADDRESS foreign postal code with letters
PASS: ADDRESS lower case type accepted
...
PASS: ADDRESS trims, upper-cases and defaults the country
PASS: ADDRESS NEXT_ID uses its own number range
PASS: RANGE unknown range is an error
PASS: RANGE name with quote is escaped
PASS: ADDRESS counts the addresses of one customer
PASS: ADDRESS deletes only the addresses of that customer
Address service: 21 tests, 0 failures

One small detail: $uppercase had never been compiled in this project before. Rather than trusting memory, the first compile of the service was the check - 0 errors, only the expected warning 1016 about the dummy entity every service needs.

The form: an editable grid

ADDRESS_FRM has three frames:

• HEADER_DMY.NOMODEL with one display field, CUSTOMER_INFO ("Addresses of customer 11 - Anna Addresstest"),
• CUSTOMER_ADDRESS.CUSTOMER_MDL as a grid with five editable fields,
• ACTION_DMY.NOMODEL with Add, Remove and Save.

Loading is a retrieve with a WHERE clause passed through the entity properties - the same mechanism the customer list uses:

entry LOAD_ADDRESSES
variables
    string vProps, vWhere
    numeric vId
endvariables
    clear/e "CUSTOMER_ADDRESS"
    vId = $customerId$
    vProps = ""
    vWhere = "CUSTOMER_ID = %%(vId)"
    putitem/id vProps, "WHERE", vWhere
    $entityproperties(CUSTOMER_ADDRESS) = vProps
    retrieve/e "CUSTOMER_ADDRESS"
    if ($status < 0 & $status != -1 & $status != -2)
        message/error $concat("The addresses could not be read (error ", $procerror, ").")
        return -1
    endif
    sort "CUSTOMER_ADDRESS", "ADDRESS_TYPE:a ci"
    return 0
end

$customerId$ is a component variable, and it has to be declared in the Declarations section of the script, not in the script body. Pasting the whole script into the script section compiles, but the variable block belongs above it; moving four lines fixed it.

Saving walks through the grid and only touches rows that were actually changed:

forentity "CUSTOMER_ADDRESS"
    if ($occdbmod(CUSTOMER_ADDRESS) = 1)
        vType = ADDRESS_TYPE.CUSTOMER_ADDRESS
        vStreet = STREET.CUSTOMER_ADDRESS
        ...
        activate "ADDRESS_SVC".VALIDATE(vType, vStreet, vPostalCode, vCity, vCountry, vField, vError)
        if ($status < 0)
            message/error $concat(vError, " The addresses were not saved.")
            call PROMPT_FIELD(vField)
            return -1
        endif
        ADDRESS_TYPE.CUSTOMER_ADDRESS = vType
        ...
        VERSION_NO.CUSTOMER_ADDRESS = vVersion + 1
        CHANGED_AT.CUSTOMER_ADDRESS = $datim
        CHANGED_BY.CUSTOMER_ADDRESS = $user
    endif
endfor
store/e "CUSTOMER_ADDRESS"

$occdbmod is the important function here: it is 1 only for occurrences whose database fields changed. Unchanged rows keep their version and their CHANGED_AT. After store/e the same -10 handling as in part 4 kicks in - on a conflict the user gets "Reload / Keep editing" instead of a crash.

New rows get their ID immediately in DO_ADD (from the ADDRESS range) rather than at save time. That is another lesson from part 3: an occurrence with an empty primary key is exactly what produced error 0129 when the user clicked elsewhere.

Deleting a customer now has consequences

With addresses in the database, "Delete permanently" from part 5 must not leave orphans behind. The delete routine asks the address service first and deletes in the same transaction:

activate "ADDRESS_SVC".COUNT_FOR_CUSTOMER(CUSTOMER_ID.CUSTOMER, vCount, vErrText)
if ($status < 0)
    message/error vErrText
    return -1
endif
if (vCount > 0)
    askmess/warning "This customer has %%(vCount) address(es). They will be deleted as well. Continue?~Delete customer", "Yes,No"
    if ($status != 1)
        return 0
    endif
    activate "ADDRESS_SVC".DELETE_FOR_CUSTOMER(CUSTOMER_ID.CUSTOMER, vErrText)
    if ($status < 0)
        rollback
        message/error vErrText
        return -1
    endif
endif
remocc "CUSTOMER"
store/e "CUSTOMER"

If store/e for the customer fails afterwards (for example with -10 because someone else changed the customer), the rollback also restores the addresses. The address delete is not committed on its own.

Two IDE surprises

A drag that moved instead of painting. To paint a field into a frame you select a template in the palette and drag a rectangle inside the frame. If the frame itself is selected at that moment, the same drag moves the frame instead. On the first try, the grid frame travelled a few hundred pixels to the right, and the following "paints" landed outside any entity. Those objects stayed on the canvas as grey rectangles that could not be selected, deleted or right-clicked ("No options are applicable"), and there is no undo. What worked: close the tab (it saves automatically), reopen it - the ghosts were gone - and drag the frame back. Since then the routine is: click an empty area of the canvas, select the template, then drag.

A script change that did not stick. After replacing DO_DELETE in the customer form, the compile was clean. A later compile suddenly reported

warning: 1000 - Module 'DO_ADDRESSES' not found.

The new code was simply gone; the editor showed the old DO_DELETE again. The cause was an inline rename box in the component tree that had been opened by an accidental click on the component name and was still open while the script was pasted. When it closed, the component was reloaded from its saved state. The warning was the only hint. Since then: after every paste, compile and check the module list on the right for the new entries.

The grid, finished

Three things turned the grid from "technically works" into "usable":

• Grid widget: paint the entity frame, then set its property FRM Widget Type to EGRID.
• Column headers: paint a label one row above each field and give it the same name as the field. The painter then associates it with the field, and the grid shows the label text as column header. Leave the row above empty and the headers are blank.
• Unsaved changes: the form's quit trigger asks "There are unsaved changes. Do you want to close without saving?" when $instancedbmod is set.

The compile left one new warning that is expected for this design:

warning: 1076 - Path to painted entity CUSTOMER_ADDRESS.CUSTOMER_MDL not found.

The entity is painted without an outer entity it relates to - the form loads it with its own WHERE. The customer form shows the same warning for its customer entity.

End-to-end run

With the database empty:

1. New customer "Anna Addresstest" → ID 11 → button Addresses → the header reads "Addresses of customer 11 - Anna Addresstest".
2. Add → the new row already has type BILLING and country DE; street "Hauptstrasse 5", postal code 1234 → Save → "A German postal code has exactly five digits. The addresses were not saved.", cursor in the postal code.
3. 12345 → Save → saved; in the database: ADDRESS_ID 1, VERSION_NO 1.
4. Second address, type typed as delivery → saved as DELIVERY; the first row was not written again (its VERSION_NO stayed at 1).
5. Remove the second row → Save → gone from the table.
6. Back in the customer form: Delete → Delete permanently → "This customer has 1 address(es). They will be deleted as well. Continue?" → Yes → customer and address removed.

Takeaways

Generalise the second time, not the first. The number range existed for a year-equivalent of this project as a customer-only operation. The moment a second range was needed, it became NEXT_RANGE_ID - with the old name kept as a wrapper, so the change was invisible to every caller and every test.

Let the service own the relationship. No foreign key constraint, but COUNT_FOR_CUSTOMER/DELETE_FOR_CUSTOMER in the service and one transaction in the form. The rule "no orphan addresses" is in one tested place.

Lessons from earlier parts are checklists. No MAN in the model, no throws in generated triggers, IDs assigned on Add - each one cost an afternoon the first time and nothing the second.

Compile is your only witness. In an IDE where a click in the wrong place can silently discard your edit, a clean compile plus a glance at the module list is the cheapest possible check.

Next: the address form currently opens only from the customer form. A reusable customer lookup and a small main menu make it - and every future module - reachable on its own.