Best Practices for Adding Fields to Persistent Records

Module Development
1

When making a persistent record with lets say 10 fields, when that module is first installed on the gateway it will create a SQLITE table that is 10 columns. If someone else who uses the module and then says hey, it’d be nice to have these additional config fields too, can you please add them?

So then you add 1 field (for example) and the new module now has 11 fields. If you then try to install that new version of the module on the same gateway it will throw an error because the column for the new field doesn’t exist in the SQLITE table.

What seems like it needs to happen is this:

  1. check if the table with that name already exists, if not make it
  2. if the table exists, read in its values then drop the table
  3. Create the table and pass in the old values plus any for the new field

I’d like to hear what everyone’s best practices are for handling this and if possible see some code examples.

This sort of functionality is not in the SDK examples so if we can understand how best to handle this, I’d like to make a pull request to add it since it seems like a pretty important feature to have assuming that modules will change over time.

Thanks,

Nick

2

When your gateway hook’s .setup() method is called, it should be calling:

context.getSchemaUpdater().updatePersistentRecords(SomePersistentRecord.META);

That will add new columns to your tables. I don’t think it will change any existing columns.

1 Like
3

What @pturmel said

1 Like
4

OK, I do see that but I did see an error raised when adding a new field. I will re-verify:

  private void verifySchema(GatewayContext context) {
      try {
          context.getSchemaUpdater().updatePersistentRecords(KafkaSettingsRecord.META);
      } catch (SQLException e) {
          log.error("Error verifying persistent record schemas for KafkaConnect records.", e);
      }
  }

The behavior I saw was after adding a new field, the module would install in a faulted state.

Nick

5

Are you sure you just added the one column? Did you assign an appropriate default value to that new column? When the module faulted, what showed up in the wrapper log?

1 Like
6

What was added was one field to specify minimum alarm priority:

  public static final EnumField<alarmPriorities> MinimumPriority =
          new EnumField<>(META, "MinimumPriority", alarmPriorities.class, SFieldFlags.SMANDATORY).
                  setDefault(alarmPriorities.Medium);

I will try to find the error, need to go back in the log a bit

Nick

7

@pturmel yesterday when this topic came back to mind (still having issues with it) I realized I never posted what the error was. Here I am just doing a test to show what I am seeing:

Current Persistent Record Columns

image
image1074×712 48.8 KB

Add one dummy field

image
image1648×723 127 KB

Verify schema and maybe create are defined and called from gateway hook setup:

    private void verifySchema(GatewayContext context) {
        try {
            context.getSchemaUpdater().updatePersistentRecords(KafkaSettingsRecord.META);
        } catch (SQLException e) {
            log.error("Error verifying persistent record schemas for KafkaConnect records.", e);
        }
    }

    public void maybeCreateKafkaSettings(GatewayContext context) {
        log.trace("Attempting to create Kafka Settings Record");

        try {
            KafkaSettingsRecord settingsRecord = context.getLocalPersistenceInterface().createNew(KafkaSettingsRecord.META);
            settingsRecord.setId(0L);
            settingsRecord.setBrokerList("127.0.0.1:9092");
            settingsRecord.setTagHistoryTopic("ignition-PROD-tag-history");
            settingsRecord.setEnabled(false);
            settingsRecord.setUseStoreAndFwd(false);
            settingsRecord.setUseSSL(false);
            settingsRecord.setAlarmsTopic("ignition-PROD-alarm-event");
            settingsRecord.setDefaultAlarmPriority();
            settingsRecord.setSource("");
            settingsRecord.setAlarmsEnabled(false);
            settingsRecord.setAuditTopic("ignition-PROD-audit-event");
            settingsRecord.setAuditEnabled(false);
            settingsRecord.setScriptingEnabled(false);
            settingsRecord.setEquipmentStateTopic("ignition-PROD-equipment-state");
            settingsRecord.setDispositionTopic("ignition-PROD-disposition");
            settingsRecord.setTest("Hey hey");

            // This doesn't override existing settings, it only sets the above if there is no existing settings

            context.getSchemaUpdater().ensureRecordExists(settingsRecord);
        } catch (Exception e) {
            log.error("Failed to establish Kafka Record exists", e);
        }
        log.trace("Kafka Settings Record Established");
    }

After installing the updated module with the added field, the module status goes to faulted:

image

There are 3 messages about it in the log:

image
image1783×282 50.6 KB

The 1st one about InternalDatabaseManager is missing the test field.

The 2nd is is about non null constraint failed. I believe this may be the issue but not sure what is the correct action to make it non-null

org.sqlite.SQLiteException: [SQLITE_CONSTRAINT_NOTNULL] A NOT NULL constraint failed (NOT NULL constraint failed: KAFKASETTINGSRECORD.TEST)

Finally, the 3rd message is about the fact that it failed to update the record:

simpleorm.utils.SException$Jdbc: Preparing 'SELECT KafkaSettingsRecord.KafkaSettingsRecord_ID, KafkaSettingsRecord.Brokers, KafkaSettingsRecord.TagHistoryTopic, KafkaSettingsRecord.Enabled, KafkaSettingsRecord.UseStoreAndFwd, KafkaSettingsRecord.UseSSL, KafkaSettingsRecord.AlarmsTopic, KafkaSettingsRecord.MinimumPriority, KafkaSettingsRecord.Source, KafkaSettingsRecord.DispPath, KafkaSettingsRecord.SrcPath, KafkaSettingsRecord.AlarmsEnabled, KafkaSettingsRecord.AuditTopic, KafkaSettingsRecord.AuditEnabled, KafkaSettingsRecord.ScriptingEnabled, KafkaSettingsRecord.DispositionTopic, KafkaSettingsRecord.EquipmentStateTopic, KafkaSettingsRecord.Test FROM KafkaSettingsRecord WHERE KafkaSettingsRecord_ID = ? '

So the issue seems to be about NOT NULL.

Nick

8

That makes sense, as any existing records would have nulls for the new column. The default only applies to new rows, not existing rows.

Basically, you cannot put a "not null" constraint on a new column.

{ This is pretty typical behavior for databases. }

9

https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.25/com/inductiveautomation/ignition/gateway/localdb/SchemaUpdater.html#updatePersistentRecords(com.inductiveautomation.ignition.gateway.localdb.persistence.RecordMeta...)

This will update the internal database's underlying database schema to support the given records. If this is the first time a record has been registered, then the table representing it will be created. If the record has been changed from the last time it was seen, the table will be modified to support any new columns. Note that new fields added to existing records that have the SFieldFlags.SMANDATORY flag (a.k.a. "NOT NULL") must have a default value, as existing records will be unable to set the new column to NULL.

2 Likes
10

Are you sure that ever worked?

11

I will modify to the following and re-test:

public static final StringField testTest = new StringField(META, "Test", SFieldFlags.SMANDATORY).setDefault("Hey hey");

Thanks,

Nick

12

@PGriffith you are right, thanks for the advice. Setting the default made it work:

image
image1082×750 51.6 KB

This is a big deal for modules that evolve over time and need to be installed smoothly. Much appreciated!

For anyone else who may see this in the future, the confusion I had is believing the default was set by this:

image
image1447×996 128 KB

It had to be set on the actual settings record.

Thanks,

Nick

2 Likes