6 Routines to Delete/Rename/Move Devices & Entities and their corresponding registry entries, data, and metadata

[puterboy]

I want to share the following 6 routines that I created to help manage Devices and Entities at their source level by allowing complete flexibility to Rename, Delete, and Move both Entities and Devices along with their underlying registry entries (in .storage) and state, statistics, and metadata in home-assistant_v2.db

This allows for cleaner, more flexible, and more complete editing of devices and entities than is possible through the GUI. This also saves manual and error-prone messing with JSON registries and SQL databases.

All routines by default (optionally) backup any changed files or registries before writing to them. Also, they by default (optionally) stop ha core before accessing or changing files. I also added a fair bit of error checking to prevent db and registry corruption and to avoid bone-headed mistakes.

CAUTION: These routines should only be used by “power users” who know what they are doing, are comfortable taking risks in manipulating core registry and database code, and want to make changes beyond what is possible with the non-technical GUI interface

Despite all the error checking, backups, and flow embedded in my code, there are certain to be bugs in my code and ways to cause damage to your databases that may require you in the worst case to restore from a full backup.

Please look through the code before using it so you know what you are doing. And of course use at your own risk! (though bug reports and suggestions are always welcome)

NOTE: Several use cases for this code are shared in subsequent postings

delete_device+entity
Delete devices and/or their corresponding entities completely from homeassistant registries & databases

• Delete devices ‘.storage’ info from:
  core.device_registry
• Delete entity ‘.storage’ info from:
  core.entity_registry
  core.restore_state
  homeassistant.exposed_entities
• Delete corresponding entity data from the following home-assistant_v2.db tables:
  states_meta
  states
  statistics_meta
  statistics
  statistics_short_term
  state_attributes

delete_device+entity - Pastebin.com

rename_device
Rename device from OLD_DEVICE_NAME to NEW_DEVICE_NAME and optionally rename name_by_user to NEW_NAME_BY_USER

• Makes changes to device ‘.storage’ info in:
  core.device_registry

rename_device - Pastebin.com

rename_entity

Rename entity_id name from OLD_ENTITY_ID to NEW_ENTITY_ID and optionally:
- Renames the corresponding ‘unique_id’ to NEW_UNIQUE_ID (in core.entity_register)
- Renames ‘friendly_name’ to NEW_FRIENDLY_NAME (in core.restore_state and if present in HA db)
- Renames designated entities mentioned in configurations, automations, scripts (See variable: YAML_FILES_BASIC and YAML_FILES_ADDED) [USE carefully]
- Updates or adds a new attribute for the NEW_FRIENDLY_NAME (if given) and assigns to relevant states

• Changes ‘entity_id’ name in the following registries:
  core.entity_register (also optionally changes ‘unique_id’)
  core.restore_state (also optionally changes ‘friendly_name’)
  homeassistant.exposed_entities
  lovelace.dashboard_*
  (plus designated yaml config files)

• Changes the following tables in home-assistant_v2.db:
  states_meta (entity_id column)
  statistics_meta (statistic_id column)
  state_attributes (if NEW_FRIENDLY_NAME given)
  states (if NEW_FRIENDLY_NAME given)

rename_entity - Pastebin.com

rename_device+entities
Rename device and associated entities from OLD_DEVICE_NAME to NEW_DEVICE_NAME
and optionally:
- Rename: name_by_user (for device – see script ‘rename_device’ for details)
- Rename: friendly_name (for entity – see script ‘rename_entity’ for details)
- Republish: MQTT discovery topic (and delete old one)

Note: Calls the scripts ‘rename_device’ and ‘rename_entity’ that are assumed to be in the same directory as this script (unless otherwise configured)

Note: There are a fair number of heuristics used here to make the MQTT publishing work and to update various derived device and entity entries based on the old and new device names. I wrote the heuristics based on the common MQTT sensors I have but YMMV and the heuristics may not work for you. If so, either modify the code or make any missing changes manually.

rename_device+entities - Pastebin.com

move_states
Move state metadata_ids from: (X1, X2,…, Xn) → (Y1, Y2,…, Yn)

• Changes metadata_ids in tables:
  states_meta
  states

move_states - Pastebin.com

move_statistics
Move statistic metadata_id’s from: (X1, X2,…, Xn) → (Y1, Y2,…, Yn)

• Moves statistic metadata_ids in tables:
  statistics_meta (id)
  statistics (metadata_id)
  statistics_short_term (metadata_id)

move_statistics - Pastebin.com

[puterboy]

Here are some use cases:
delete_device+entity

• Completely delete existing devices +/- their associated entities to clean up HA from inadvertently added devices or devices no longer needed that cannot be removed fully or at all using their integration GUI
• Delete devices and entities that have been partially deleted (and hidden) via the GUI

rename_device+entities

• Flexibly rename all the different naming components of a device and its associated entities (plus attributes) when the default name is non-helpful or needs to be changed
• Republish MQTT discovery message based on new naming (and unpublish old one)
• Useful for auto-discovered rtl_433 devices that have a channel number in their device & entity names that change every time the battery is changed, resulting in the endless creation of new devices and entities. Next time you need to change the battery, all you need to do is to change a single field in the MQTT discovery message (state_topic or stat_t) to redirect the messages to your now stably and friendly-named device and entities

move_states
move_statistics
Renumbering the metadata_id for the states and statistics tables for a given set of sensors if you want to group similar devices and entities together.

[puterboy]

Note that I will update the pastebin source code from time to time.
Please let me know if you find any bugs or other issues.

[Mariusthvdb (Marius)]

Hi,

Is there a WHY to all of this? Have you run into trouble that could not be fixed by manipulating the Home Assistant devices/entities in the general UI / Yaml files?

Asking, because even though you seem to be very diligent, this is all very dangerous, especially to the fresh Home Assistant user, and possibly also for the more seasoned user.

In fact, one should not have th edit the hidden files at all. They are hidden for a reason… It is for that reason manually manipulating the .storage files is discouraged/advised against under most if not all circumstances.

Could I ask yo to write some preliminary intro at the top of the opening post explaining why you wrote this, and with some bold warning not to do this as a generic approach but only after all other options have not helped?

[puterboy]

I gave a list of several use cases above.
I use these routines for the following reasons (and more)

1. Various integrations (e.g., MQTT, Alexa Media Player) can lead to the creation of unwanted sensors – and often even the recreation of such sensors multiple times – that needlessly clutter the database
   For example:
   a. rtl_433_ESP by default listens to and discovers all my neighbors sensors that often have the same general name (other than the final channel designation) as my own sensors. This creates clutter and confusion
   b. Alexa Media Player likes to recreate and duplicate sensor names
   c. One of the calendar functions had a temporary bug that duplicated all calendar sensors which were persisted long after the bug was fixed, so I wanted to remove the persistent duplication and resolve the confusion in sensor naming
   d. Many rtl_433 humidity and temperature sensors change their name every time you change the battery, resulting in the creation of new sensors with new random names that mess up any automation, script, or card that references them

2. Sensors are often created automatically with meaningless or hard to remember names. Given that many if not most manual configurations require you to reference the actual entity_id sensor name, this becomes challenging to know what name to use in the first place and then to remember what it all refers to if you ever come back to change or review the YAML configurations. For examples see the above plus:
   a. Specifically, many rtl_433 sensors have names like sensor.lacrosse_tx141thbv2_0_233_temperature or sensor.ambientweather_f007th_1_13_temperature that give no indication where they are or even if they are mine
   b. Alexa Media Player creates multiple sensors with names like sensor.this_device or sensor.this_device2, etc.
   c. Nest uses different naming conventions for thermostats vs. remote thermostats

3. Sometimes after the fact I want to change the names of various sensors because either they move, change, or I just want to change my naming scheme.
   a. For example, I have created some custom sensors using custom hardware. My initial MQTT configuration for those sensors was lacking and I wanted to change various configuration options without having to delete and reinstall the sensor (thereby losing data)

4. Conversely, if I swap out the sensor measuring a specific quantity (e.g., bedroom temperature) or if I change the battery and a new sensor is created for the same function, I want to use the same sensor name name and metadata_id so that I don’t have to change any yaml or configurations and so that if I export the data from home-assistant_v2.db it will all have the same metadata_id across time for easy and consistent import into other software

5. Sometimes one makes “mistakes” in configurations, in naming, in manipulations etc. and you don’t want to restore from a backup so as not to lose data. In that case, these routines can be a lifesaver in undoing changes cleanly and (relatively) safely.

6. Sometimes you want to go “back in time” and change historical data like fixing the spelling for the friendly_name in the attribute data so that it is right historically for all time and not just going forward

7. I believe that the built-in tools only let one change the display names (corresponding roughly to ‘name_by_user’ for devices and ‘friendly_name’ for entities) but not the sensor names themselves. But most scripts and automation yaml code requires you to reference the sensor entity_id which is hard to remember and later understand if the name is sensor.ambientweather_f007th_1_13_temperature

8. Similarly, the build-in tools for deleting devices and entities doesn’t fully delete devices and entities and all their data.

9. Similarly, some integrations make it challenging if not impossible to delete or even modify existing devices and entities. The Forum is filled with requests from people asking how to do so. Sometimes there is an easy answer, but often I have seen the suggested solution being to completely uninstall and reinstall the entire integration along with all its associated devices and entities which presumably would lead to data loss and the need to reassociate the newly installed sensors with their referenced automations and scripts This to me is a non-starter solution, so short of fixing or re-writing the integration, I can use my code to solve the problem directly.

10. Most importantly, I am anal and OCD when it comes to the organization of my data and just HATE having clutter and crud in the database and registers – even if hidden from the GUI.

I could go on with many more reasons as I find these routines really useful in keeping my system streamlined – especially as I climb the HA learning curve and realize that some of my initial choices and naming conventions were suboptimal. These routines let me get things right at the foundational level!

Personally, I much prefer manual configurations with YAML or JSON than GUI abstractions that hide the full power (and complexity) and limit what you can do.
I understand this may not be true for the average non-technical user and per your suggestion, I will add a caution at the beginning of my initial post.

I should also add that writing this code has given me unparalleled understanding of how HA and its various integrations ingest, organize, transform, and store data at the most granular level. This is really helpful to understand how everything works more generally as you abstract up to the GUI. Plus it’s fun

[Mariusthvdb (Marius)]

@puterboy

I understand this may not be true for the average non-technical user and per your suggestion, I will add a caution at the beginning of my initial post.

thank you very much for adding that, appreciated!

[puterboy]

Updates to v8.1 posted to pastebin site referenced above

[Umby (Umberto)]

Great collection of scripts!
I found your work very interesting, are you also going to release a version of the scripts that works on MariaDB?
It would be really nice to be able to run your scripts on DBs that are not managed by HA’s default internal DB.

[puterboy]

Thanks!
Unfortunately, I know nothing of MariaDB - don’t use it, don’t have it, and frankly have no plans or need to currently use it.
But you are more than welcome to extend the scripts… If MariaDB is sql-based, I imagine most commands should be similar…

[Umby (Umberto)]

Unfortunately, I do not have the skills to write scripts. An additional complication is the way my setup installation is done with dockers. The database and configuration files are on two different containers, so modifying both with a single script would be even more complex.
I took a cue from your questions and proceeded to manually operate on the database to transfer the statistical data of the entities I modified in HA.
Thank you very much for sharing your work and helping and inspiring me in a positive way.

[AJolly]

Thanks! I appreciate you taking the time to release these.
But wouldn’t it make more sense to share them on github for easy updating?

Also - any idea how to get sqlite3 installed on HA? apt get seems missing.

ps - you may appreciate hass-addons/dbstats/README.MD at 01b207802840b2eb93ce1a72ccd4e6c565d21698 · jehy/hass-addons · GitHub to show what is taking up so much DB space

[puterboy]

@AJolly

Thanks! I appreciate you taking the time to release these.
But wouldn’t it make more sense to share them on github for easy updating?

I guess I could use github but when I started this I was just creating rough scripts and it grew from there.
Personally, I am not a big user of github. So, for now, I will just update on pastebin unless it starts getting too cumbersome

Also - any idea how to get sqlite3 installed on HA? apt get seems missing.

Very easy!
Install the SQLite Web add-on which includes sqlite3, then access via ssh.

ps - you may appreciate hass-addons/dbstats/README.MD at 01b207802840b2eb93ce1a72ccd4e6c565d21698 · jehy/hass-addons · GitHub to show what is taking up so much DB space

Cool!

[AJolly]

Github: would be more useful to let someone just update their copy of your scripts from github / propose updates/forks. Like I just realized I’m still using your 0.5 version, not .81.
Comments are for 0.5:

I Had the Sqlite Web add on already, however it seems to just use the python libraries not the binary so that didn’t work. however either apk add sqlite or using the community advanced ssh terminal and adding sqlite to the package list did work.

I was a bit surprised that the NAME meant the entity ID as it shows up in HA, and the unique id was the human readable name.

Would love if it had wildcard support.
Would love if it could use a tag or area as a type.
Would love if you could force it not to ask for confirmations.
It be nice for an option to delete all of the records associated and not the entity/device itself

I’m also running into this error:

./delete_device_entity.sh: line 490: /usr/bin/sqlite3: Argument list too long

[puterboy]

@AJolly

Github: would be more useful to let someone just update their copy of your scripts from github / propose updates/forks. Like I just realized I’m still using your 0.5 version, not .81.
Comments are for 0.5:

TBH, I don’t think I will be changing it much other than minor bug fixes as it already does more than I need to do. These are meant to be simple scripts not complex programs that people can either use as-is or modify for their own needs.

I Had the Sqlite Web add on already, however it seems to just use the python libraries not the binary so that didn’t work. however either apk add sqlite or using the community advanced ssh terminal and adding sqlite to the package list did work.

I didn’t do either method and yet I have a sqlite3 executable. Not sure what caused it to be included in my setup…

I was a bit surprised that the NAME meant the entity ID as it shows up in HA, and the unique id was the human readable name.

If I recall, this is consistent with the naming of the device and entity elements in the registry files.
These routines are targeted mostly at people who are familiar already with the general structure of those files but wanted a way to change them in a safer, more comprehensive, and more automated fashion (I did include a warning )

Would love if it had wildcard support.
Would love if it could use a tag or area as a type.

You can get that by wrapping it in a bash script that does the wildcard lookup or looks up the tag… Again this is not meant to be a full-featured standalone program but a script that you can combine or extend with other bash scripts and code.
Plus this code is “dangerous” enough as is and I would fear that a wildcard if not accompanied by additional warnings and previews of what is changed/deleted could lead to unpredicted and even catastrophic deletions or corruptions.

Would love if you could force it not to ask for confirmations.

For safety, I think it’s important to know what is happening.
But you can override such safety by feeding it ‘y’ from a bash script.

It be nice for an option to delete all of the records associated and not the entity/device itself

That doesn’t sound like a general use case. The purpose of this script is to cleanly and completely remove or modify entities.
Assuming by records, you just want to delete the database records for any given entity or device, it’s pretty trivial to do that with a few sqlite3 commands.

Beyond that, the code is all Bash and pretty extensively commented, so for your own personal use cases, feel free to comment out those lines that delete the device/entity itself.

More generally, as the code is all there, feel free to submit patches if you want to offer extensions or improvements to the code…

I’m also running into this error:

./delete_device_entity.sh: line 490: /usr/bin/sqlite3: Argument list too long

Please try latest version and let me know if error exists and the line number - I will take a look and see. (Newer version also has better error reporting)
I am guessing the error relates to deleting attributes and potentially there are too many attributes for a given entity to delete in a single sqlite3 command.

To help with the debugging, you could add an ‘echo’ before the sqlite3 command to see what is actually being sent to sqlite.

If that is the case, it could be fixed by breaking the delete into multiple sql delete commands.