Renaming HA entity IDs

[proddy]

from @sciurius

1. Create a rename script for the database entries (let me know if you're interested).
2. Modified EMS-ESP to not use MQTT (actually, I routed it to a test broker).
3. In the MQTT broker, I removed all autodiscovery items for EMS-ESP.
4. In HA, removed the EMS-ESP, boiler and thermostat from the MQTT integration.
5. Run the rename sql to update all database entries.
6. Reflashed the gateway with v3.5b11.
7. Re-attached to the boiler and verify it is working.
8. Modify EMS-ESP to use the real MQTT broker and enable autodiscovery.
9. Hunt down YAML files and change entity names.

Maybe some of the steps are unneccessary, but this worked for me.
Unfortunately, due to a recent change in HA the 'more info' popup will only show new data. 'show more' will show all data.

Originally posted by @sciurius in #804 (comment)

[proddy]

From @sciurius

Sure. I made a markdown document with all the entities and a small Perl program that reads the md file to produce a SQL rename script. I use PostgreSQL but I think the script will work with other SQL databases as well.

rename.zip

Note that the program generates a rename sql that groups everything in a single transaction, and rolls back at the end. This is for testing. If testing succeeds, or if you are reckless, change ROLLBACK to COMMIT and go.

[proddy]

@mtc716 said

I did more or less the same by stopping recorder, modifying SQL DB and changing dashboard and yaml files. And it worked like charm. In the DB there are roughly 6 or 7 table columns at which you need to change the old to new name. And if you do not see the short term statistics, you need to check under developer settings and statistics. There might be the problem reported and by solving it there your statistics will be back.

[mtc716]

Step 1: Stop Recorder at HA
Step 2: Publish the SQL statements (see below)
Step 3: Rename the recorded entities in EMS_ESP_b10 with the new "short_name"
Step 4: Check in HA if the new names are visible
Step 5: Restart HA and flash EMS_ESP_b11

My way was first looking through the tables and find the columns which consists of the "entity_id" names.
By this I created the following SQL REPLACE statement as an example for boiler_outdoortemp

update event_data set shared_data = replace(shared_data, 'sensor.boiler_outside_temperature', 'sensor.boiler_outdoortemp');
update events set event_data = replace(event_data, 'sensor.boiler_outside_temperature', 'sensor.boiler_outdoortemp');
update state_attributes set shared_attrs = replace(shared_attrs, 'sensor.boiler_outside_temperature', 'sensor.boiler_outdoortemp');
update states set entity_id = replace(entity_id, 'sensor.boiler_outside_temperature', 'sensor.boiler_outdoortemp');
update states set attributes = replace(attributes, 'sensor.boiler_outside_temperature', 'sensor.boiler_outdoortemp');
update statistics_meta set statistic_id = replace(statistic_id, 'sensor.boiler_outside_temperature', 'sensor.boiler_outdoortemp');

Table Names are marked in BOLD
Column Names are marked in Italic

This I repeated for all my recorded entity_id's and after restart everything including my history for more than a year is still alive and working...

I guess a combined attemp of your perl and the extended SQL to cover everything would work like charm.

[sciurius]

It would be very nice if ha cli rename sensor.boiler_outside_temperature sensor.boiler_outdoortemp did just that...

[mtc716]

Oh yeah, but I would assume it is not like that, at least not for recorded history.
Or I am just to stupid to use it as it says for me, unknown command. :-)

[sciurius]

I've modified the perl program to generate renames for all the necessary tables.

rename.zip