SQL Logger Duplicate

I am getting an error in SQL Logger, along the lines of:

Code: Select all

2021-03-02 07:37:42.427   SQL Logger Error   exception trying to insert row into table device_history_1922093003 ("HE Kitchen - Motion Fridge") -- unable to modify table
2021-03-02 07:37:42.427   SQL Logger Error   ERROR:  column "illuminance_ui" specified more than once at character 89

2021-03-02 07:37:42.427   SQL Logger Error   INSERT INTO device_history_1922093003 ("batterylevel", "illuminance", "illuminance_ui", "illuminance_ui", "onoffstate") VALUES (%s, %s, %s, %s, %s);


I am creating the states illuminance and illuminance_ui in a getDeviceStateList method.

The code snippet is as follows:

Code: Select all

        # Illuminance State
        if bool(dev.pluginProps.get("uspIlluminance", False)):
            illuminance_state = self.getDeviceStateDictForNumberType(u"illuminance", u"Illuminance Changed", u"Illuminance")
            if illuminance_state not in state_list:
                state_list.append(illuminance_state)
            illuminance_ui_state = self.getDeviceStateDictForStringType(u"illuminance_ui", u"Illuminance Changed", u"Illuminance")
            if illuminance_ui_state not in state_list:
                state_list.append(illuminance_ui_state)


To investigate further, I stopped the Indigo Server and then viewed the Indigo database in my text editor. I noted that there were three fields: illuminance, illuminance_ui and illuminance.ui. As a first step, I deleted the illuminance.ui field and saved the database. Indigo (I think) then recreated it on the restart. The documentation says that you can't have a full stop in a state name as it is for internal use. I haven't used a full stop.

I suspect the SQL Logger error is caused by the illuminance.ui being renamed to illuminance_ui prior to inserting the record, which causes a duplicate error as the illuminance_ui already exists.

So, I am not sure why the illuminance.ui is being created and/or what Indigo uses a full stop for in a state name.

I have worked around the issue by renaming the illuminance_ui to illuminanceUi which actually is probably more akin to the Indigo standard of using Title case.

Why are you creating a separate state for illuminance_ui? Can't you just use the normal method for updating the UI value at the same time you update the state value?

I am showing the illuminance as custom states in a temperature sensor:

illuminance.png (46.47 KiB) Viewed 1757 times

So I was creating two custom states for the user to choose from.

[matt (support)]

So, I am not sure why the illuminance.ui is being created and/or what Indigo uses a full stop for in a state name.

When you update a device state you can optionally provide a UI version of the state:

Code: Select all

dev.updateStateOnServer("someState", 72.45, uiValue=u"72.45°C")

The raw value being useful for <, >, = compares in Triggers and Conditions, and the UI state being useful for showing on Control Pages or UI. When choosing to show a state on a Control Page control Indigo will use the ".ui" version of the state if it exists. It sounds like you are trying to duplicate this logic and should instead just use the optional parameter to let Indigo handle it.

And I think you are correct about the SQL Logger error being because of the substitution Indigo is performing on the state name causing it to conflict. That would be an Indigo buglet.