Original signature missing in test data when using v2 quirks

[TheJulianJES]

Issue

We do not seem to keep the original_signature in diagnostics for v2 quirks.
We have this logic: zha/zigbee/device.py#L1401-L1404, where we use replacement for v2 quirks, but that'll always be empty from what I can see, due to this: zigpy/quirks/v2/__init__.py#L80-L83. It also doesn't seem to make sense that we'd wanna use the (generated) replacement anyway. Both sections seem improvable in general.

Why this is bad

It's important to always include the original signature in diagnostics as well, as quirk changes will persist through regenerated diagnostics otherwise. See the following example:

So, when changing or developing new quirks, it looks they'll always be added on top of the modified data from last time when regenerating diagnostics. Let's say we generate diagnostics from a device with a v2 quirk that adds endpoint 10 with the LevelControl cluster.
If we later update the v2 quirk to not touch endpoint 10 (and clusters on that ep) at all, then regenerate diagnostics, we'll still have ep 10 with LevelControl present from what I can see.

On a real setup, this likely won't happen, as the original ep/cluster information is saved in the zigpy DB and not touched*.

*: Unfortunately, there were also some zigpy versions that committed quirked clusters/signatures in the DB, so we'll have to keep that in mind if we get diagnostics from devices added over the last years.

Due to how zigpy currently applies quirks (i.e. adding endpoints and clusters), it might be hard to get the original signature for device using v2 quirks, without changing zigpy or directly accessing the zigpy database.

Additional

(Original message posted on Discord: https://discord.com/channels/330944238910963714/1346942546714296351/1392692772691120158)

[puddly]

This is definitely something that needs to be addressed, this type of device modification prevents us from fully utilizing the zigpy database while also making the device diagnostics unreliable, as you said.

This is complicated by a few things:

• We save information to the database by reading it from the device objects.
• Quirks modify runtime data of these objects (e.g. profile_id and device_type), meaning we cannot save any data once a device has been "modified".
• The database uses foreign keys and such for all of the relationships, making it impossible to store "floating" data in the database without also adding fake endpoint and cluster rows.
• After OTA, we cannot re-interview the device easily without basically removing and fully re-adding the device, to "reset" quirks.

This isn't a great situation 😄. I need to think of some ways to progress.

[puddly]

Perhaps we should strip down the table-based approach in SQLite and simplify things:

• We have a IEEE -> NWK table. This keeps track of the devices that may exist on the network but no information about them.
• A signature table that ties an IEEE to on-network metadata: node descriptor, endpoints, and simple descriptors. This is immutable and saved only as we read the data via ZDO, never from a live device object.
• There is a ZCL attribute cache. This is populated whenever we read attributes or receive reports and again, only persists real ZCL data. It stores the serialized ZCL data for the attribute (no SQLite data types) and the other metadata. These attributes aren't linked to endpoint or cluster rows, though they store that information, to allow weird unregistered endpoints and clusters to keep working.
• There is a "data store" or something similar. This is where virtual attributes and anything touched by a quirk go.

The intent is to have the first three tables be merely a cache: we should be able to, at any time, wipe them and re-read them straight from the device (assuming perfect connectivity 😄).

[puddly]

Maybe something like this:

-- Address cache: rudimentary information about nodes on the network, nothing more
DROP TABLE IF EXISTS address_cache_v14;
CREATE TABLE address_cache_v14 (
    ieee ieee NOT NULL,
    nwk INTEGER NOT NULL,
    last_relays BLOB,
    last_seen REAL NOT NULL,
    last_hop_lqi INTEGER,
    last_hop_rssi INTEGER,
);

CREATE UNIQUE INDEX address_cache_idx_v14
    ON address_cache_v14(ieee);


-- ZDO signatures: immutable ZDO metadata about the device
DROP TABLE IF EXISTS zdo_signatures_v14;
CREATE TABLE zdo_signatures_v14 (
    ieee ieee NOT NULL,
    node_descriptor BLOB NOT NULL,
    active_endpoints BLOB NOT NULL,
    simple_descriptors BLOB NOT NULL,
);

CREATE UNIQUE INDEX zdo_signatures_idx_v14
    ON zdo_signatures_v14(ieee);


-- ZCL attribute cache: read and reported ZCL attributes
DROP TABLE IF EXISTS zcl_attribute_cache_v14;
CREATE TABLE zcl_attribute_cache_v14 (
    ieee ieee NOT NULL,
    endpoint_id INTEGER NOT NULL,
    cluster_type INTEGER NOT NULL,
    cluster_id INTEGER NOT NULL,
    attr_id INTEGER NOT NULL,

    -- The attribute can be unsupported
    status INTEGER NOT NULL,
    value BLOB,

    last_updated REAL NOT NULL,

    -- For migrated attributes, we cannot be sure if the value is processed or real
    tainted BOOL NOT NULL,

    PRIMARY KEY(ieee, endpoint_id, cluster_type, cluster_id, attr_id)
);

CREATE UNIQUE INDEX zcl_attributes_cache_idx_v14
    ON zcl_attribute_cache_v14(ieee, endpoint_id, cluster_type, cluster_id, attr_id);


-- Device KV store: key-value store for quirk data
DROP TABLE IF EXISTS device_kv_store_v14;
CREATE TABLE device_kv_store_v14 (
    ieee ieee NOT NULL,
    key TEXT NOT NULL,
    value BLOB NOT NULL,
    last_updated REAL NOT NULL,

    PRIMARY KEY(ieee, key)
);

CREATE UNIQUE INDEX device_kv_store_idx_v14
    ON device_kv_store_v14(ieee, key);