Upgrading the Database

Overview

The ui-backend service runs its Liquibase changelogs automatically on startup against each configured datasource (acs, iotw, qoe). Changelogs are append-only: once a changeSet has been applied and recorded in DATABASECHANGELOG, it is never edited in place, because Liquibase tracks each changeSet by an MD5 checksum of its content and would refuse to re-run (or flag as modified) a changeSet whose content changed after release.

For the mechanics of authoring, structuring, and reviewing changeSets, see Database Change Management Guide. This page instead tracks the data-impacting migrations that operators upgrading from an older release need to be aware of — renamed identifiers, backfilled columns, and similar changes that affect values already stored in an existing database.

A changeSet that only affects the schema shape of a brand-new install (e.g. createTable) is usually self-explanatory and is not repeated here. This page focuses on changes that transform or relocate existing data, or that change a stable identifier that API clients depend on.

Notable changes by version

7.1.1

Reports interface-item IDs renamed from *Url to *Id

The 14 interface configuration items in the reports group that describe individual embedded report views were originally named with a *Url suffix (e.g. CurrentInventoryStatusUrl), even though the value they hold is a report identifier, not a URL. These items have been renamed to use an *Id suffix.

Old ID (*Url) New ID (*Id)

CurrentInventoryStatusUrl

CurrentInventoryStatusId

DeviceTrendsUrl

DeviceTrendsId

InternetConnectivityUrl

InternetConnectivityId

DevicePerformanceUrl

DevicePerformanceId

UserExperienceUrl

UserExperienceId

CustomerQoeAnalysisUrl

CustomerQoeAnalysisId

SpeedAndLatencyAnalysisUrl

SpeedAndLatencyAnalysisId

WifiDeviceAttributesUrl

WifiDeviceAttributesId

WifiNetworkPerformanceUrl

WifiNetworkPerformanceId

WifiClientAnalysisUrl

WifiClientAnalysisId

WifiStandardsUrl

WifiStandardsId

WifiBandsUrl

WifiBandsId

WifiSecurityUrl

WifiSecurityId

HomeScoreUrl

HomeScoreId
CoreReportsUrl and PersonalizedReportsUrl (also in the reports group) were not renamed, and neither were the grafana group items BasicViewUrl, BusinessMetricsUrl, APIMetricsUrl. Those five items genuinely hold URLs and keep their existing IDs unchanged.
How existing values are preserved

Because SettingInitializer.setInterfaceItemsFromFile() only inserts an interface item from conf/interfaceItems.json when no row for that ID already exists (it filters out any ID already present in iotw_interface_item), simply shipping the renamed IDs in the seed file is not enough on an upgrade: an existing installation already has rows under the old *Url IDs, so the seed would only add rows under the new *Id IDs alongside the untouched legacy rows, rather than migrating operator-configured values.

SettingInitializer only ever seeds iotw_interface_item and its cascaded iotw_interface_description. Rows in iotw_client_interface and iotw_interface_domain_specific are written only when an operator saves a value through the settings API, so those tables can hold real configuration under the new *Id value before this migration ever runs (see the NOTE below). The migration must never delete that operator-configured data.

To carry existing values across the rename, changelog db.changelog/ui/changelog-rename-report-interface-items.xml (iot-web-app-build/liquibase/db.changelog/ui/) adds one changeSet per renamed ID. Each changeSet runs the following statements in order, applying "newer wins" semantics:

  1. Is gated by a sqlCheck precondition with onFail="MARK_RAN", so it is a no-op (recorded as run, nothing executed) on a fresh install where the legacy *Url row never existed.

  2. INSERT .. SELECT, guarded by NOT EXISTS, clones the legacy iotw_interface_item row under the new ID only when the new ID is not already present — a row already seeded by SettingInitializer is kept as is, not overwritten. The rename cannot be done as an in-place primary-key UPDATE because the foreign keys from the child tables below have no ON UPDATE CASCADE. After this statement the new ID always exists, either just cloned or already present.

  3. iotw_interface_description holds seeded metadata only: the rows under the new ID are deleted, then the legacy rows are re-pointed to the new ID. That keeps exactly one description set.

  4. iotw_client_interface holds operator values. The legacy row is deleted only where a value already exists under the new ID; the remaining legacy rows are then re-pointed to the new ID. Nothing configured under the new ID is ever deleted, and no duplicate row survives — a duplicate would break ClientInterfaceRepository.getClientInterfaceItem(…​), which returns an Optional and fails on a non-unique result.

  5. iotw_interface_domain_specific is handled the same way as step 4, matched on the (client_type, domain_id) pair.

  6. The legacy iotw_interface_item row is deleted last, once every child row has moved off it.

The changeSet SQL is written to run on both MySQL and Oracle: self-referencing subqueries are wrapped in derived tables (bare correlated subqueries on the table being written trigger MySQL error 1093), and table aliases are written without AS (Oracle rejects AS for table aliases). Net effect: any value an operator previously configured for one of the 14 renamed items is preserved, now stored under the new *Id primary key.

If the same report was configured under both the legacy *Url ID and the new *Id ID, the value stored under the new ID wins and the legacy value is discarded. This can happen during a mixed-version rollout when interfaceItems.json and ${DB_LIQUIBASE_DIR} (${APP_HOME}/liquibase) come from different release artifacts: the new seed IDs can be created before the matching migration runs, and an operator can configure them in that interval.

 
<!-- iot-web-app-build/liquibase/db.changelog/ui/changelog-rename-report-interface-items.xml -->
<changeSet id="rename-report-interface-item-current-inventory-status" author="kirillbushtets">
    <preConditions onFail="MARK_RAN" onError="HALT">
        <sqlCheck expectedResult="1">
            SELECT COUNT(*) FROM iotw_interface_item WHERE id = 'CurrentInventoryStatusUrl'
        </sqlCheck>
    </preConditions>
    <comment>Rename CurrentInventoryStatusUrl to CurrentInventoryStatusId, keeping the configured values.</comment>
    <sql>
        INSERT INTO iotw_interface_item (id, default_value, value_type, is_required, group_id, domainSpecificValue)
        SELECT 'CurrentInventoryStatusId', default_value, value_type, is_required, group_id, domainSpecificValue
        FROM iotw_interface_item
        WHERE id = 'CurrentInventoryStatusUrl'
          AND NOT EXISTS (SELECT 1 FROM (SELECT id FROM iotw_interface_item) existing WHERE existing.id = 'CurrentInventoryStatusId');
        DELETE FROM iotw_interface_description WHERE interface_description_id = 'CurrentInventoryStatusId';
        UPDATE iotw_interface_description SET interface_description_id = 'CurrentInventoryStatusId' WHERE interface_description_id = 'CurrentInventoryStatusUrl';
        DELETE FROM iotw_client_interface
        WHERE interface_id = 'CurrentInventoryStatusUrl'
          AND client_type IN (SELECT client_type FROM (SELECT client_type FROM iotw_client_interface WHERE interface_id = 'CurrentInventoryStatusId') existing);
        UPDATE iotw_client_interface SET interface_id = 'CurrentInventoryStatusId' WHERE interface_id = 'CurrentInventoryStatusUrl';
        DELETE FROM iotw_interface_domain_specific
        WHERE interface_id = 'CurrentInventoryStatusUrl'
          AND (client_type, domain_id) IN (SELECT client_type, domain_id FROM (SELECT client_type, domain_id FROM iotw_interface_domain_specific WHERE interface_id = 'CurrentInventoryStatusId') existing);
        UPDATE iotw_interface_domain_specific SET interface_id = 'CurrentInventoryStatusId' WHERE interface_id = 'CurrentInventoryStatusUrl';
        DELETE FROM iotw_interface_item WHERE id = 'CurrentInventoryStatusUrl';
    </sql>
</changeSet>
<!-- ... one changeSet per renamed id ... -->

The include for this changelog is appended at the end of iot-web-app-build/liquibase/db.changelog/ui/db.changelog-master.yaml, following the project convention of only ever appending new includes (see Step 3: Update Master ChangeLog).

The already-released changelog changelog-update-interface-report-items-defaultvalue.xml is not edited as part of this change — editing a changeSet that has already been applied on installed systems would change its checksum and break Liquibase validation on upgrade. That changelog still references the legacy *Url IDs (CurrentInventoryStatusUrl, CustomerQoeAnalysisUrl, DevicePerformanceUrl, and so on) in its <where> clauses. This is expected: it ran once, against the IDs that existed at the time, and is left untouched.
Impact on API clients

The interface item IDs are the wire contract of POST and PUT iot-webservice/iotw/Setting/interface (SettingController / InterfaceService). POST reads all interface configuration items grouped by group_id; PUT updates them. Any client that reads or writes these 14 items by ID — portal frontends, integrations, or scripts calling the API directly — must switch from the *Url names to the corresponding *Id names listed in the table above. Values are preserved across the upgrade, but requests that still reference the old *Url IDs after upgrading will no longer match an existing item.