Replacing Device question

[Ragdag]

So I found the Wiki about replacing a device.
https://wiki.domoticz.com/Managing_Devi ... ace_device

I'm planning to migrate values from an old Heat pump plugin to the one I created, not sure if that is possible.

So I have 2 Hardware running, WP and WPOLD

WPOLD has all the history I want to keep.

I'm thinking that I can point the WPOLD to a nonexisting IP for the heatpump so it will not fetch new information.
Then use the Replace device procedure from the wiki.

As an example, the Outside Temp avg. is Device idx 508 (old) and 4218 (new)
When initiating the Replace from 508. The new device will be deleted.
WHat will the Hardware Name, ID and Unit be of the remaining device?

Since the plugin is using Unit as a look up to handle device renames, I think devices like 511 to 4222 will make my life difficult.
So I think if those do not match up anymore things will go bonkers.

[waltervl]

Disable the old plugin (not delete). Disable in Menu Setup - Settings the option to hide devices of disabled hardware gateways. The old devices will be visible but greyed out.
Enable the new plugin, new devices will be created and updated.
Use the replace function on the new devices to tell they are replacing the old devices.
Old devices will be removed, new devices will get IDX of old devices (nice for existing scripting).
Remove the old hardware gateway (plugin)
Enable in Menu Setup Settings the option to hide devices of disabled hardware gateways.

[Ragdag]

Thank you
I tested with disable but that indeed did hide my existing device, did not know there was an option for that.

What would happen with the Unit IDs?
I understand the idx will move over.

[waltervl]

What would happen with the Unit IDs?
I understand the idx will move over.

I dont know.

[Kedi]

Why not create 2 dummy devices, which you can delete later on, and test what happens when you replace one by the other. So simple.

[Ragdag]

Why not create 2 dummy devices, which you can delete later on, and test what happens when you replace one by the other. So simple.

I did that, and some other test and the plugin will look up existing devices based on the Unit ID, so since my new plugin has more devices they will not match up anymore and break the look up.

Wanted to test by copying data directly in the database.
Tested with 1 device

Copy Temperature History

Code: Select all

INSERT INTO Temperature (DeviceRowID, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date)
SELECT 4217, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date
FROM Temperature
WHERE DeviceRowID = 507;

Copy Aggregated Temperature Calendar Data

Code: Select all

INSERT INTO Temperature_Calendar (DeviceRowID, Temp_Min, Temp_Max, Temp_Avg, Chill_Min, Chill_Max, Humidity, Barometer, DewPoint, SetPoint_Min, SetPoint_Max, SetPoint_Avg, Date)
SELECT 4217, Temp_Min, Temp_Max, Temp_Avg, Chill_Min, Chill_Max, Humidity, Barometer, DewPoint, SetPoint_Min, SetPoint_Max, SetPoint_Avg, Date
FROM Temperature_Calendar
WHERE DeviceRowID = 507;

This works perfectly and I now see the temperature history in the new device.
But my Domoticz Log is now throwing errors

Code: Select all

2025-02-11 09:31:28.244 Error: SQL Query("SELECT strftime('%Y', Date) as y,strftime('%m', Date) as m, AVG()/1.000000 as s FROM Temperature_Calendar WHERE DeviceRowID == 4217 GROUP BY strftime('%Y', Date), strftime('%m', Date)") : wrong number of arguments to function AVG() 

As far as I can see the data in the table is correct and normal

[Ragdag]

Tested the same thing again but now with

Code: Select all

PRAGMA optimize;
PRAGMA cache_size = -2000;
PRAGMA shrink_memory;

After the inserts and it seems to be going okay now.

Can anyone verify that I'm not missing anything critical?

[Kedi]

What happens to the error if you select something else then 'average' in the compare graph.

[Ragdag]

What happens to the error if you select something else then 'average' in the compare graph.

I did not select anything (I think )
Will test and try some other stuff later. Reverted to my backup of the database, so it is orginal again.

[Ragdag]

If it helps anybody, I did it directly in the DB.


For Temperature devices, you need to run 2 queries:

Code: Select all

-- Detailed Data in Temperature table
-- Migrate from old IDX 504 to new IDX 4214
INSERT INTO Temperature (DeviceRowID, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date)
SELECT 4214, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date
FROM Temperature
WHERE DeviceRowID = 504
  AND Date < (SELECT MIN(Date) FROM Temperature WHERE DeviceRowID = 4214);

-- Aggregated Data in Temperature_Calendar table
-- Migrate from old IDX 504 to new IDX 4214
INSERT INTO Temperature_Calendar (DeviceRowID, Temp_Min, Temp_Max, Temp_Avg, Chill_Min, Chill_Max, Humidity, Barometer, DewPoint, SetPoint_Min, SetPoint_Max, SetPoint_Avg, Date)
SELECT 4214, Temp_Min, Temp_Max, Temp_Avg, Chill_Min, Chill_Max, Humidity, Barometer, DewPoint, SetPoint_Min, SetPoint_Max, SetPoint_Avg, Date
FROM Temperature_Calendar
WHERE DeviceRowID = 504
  AND Date < (SELECT MIN(Date) FROM Temperature_Calendar WHERE DeviceRowID = 4214);

For custom devices

Code: Select all

-- Detailed Data in Percentage table
-- Migrate from old IDX 523 to new IDX 4232
INSERT INTO Percentage (DeviceRowID, Percentage, Date)
SELECT 4232, Percentage, Date
FROM Percentage
WHERE DeviceRowID = 523
  AND Date < (SELECT MIN(Date) FROM Percentage WHERE DeviceRowID = 4232);

-- Aggregated Data in Percentage_Calendar table
-- Migrate from old IDX 523 to new IDX 4232
INSERT INTO Percentage_Calendar (DeviceRowID, Percentage_Min, Percentage_Max, Percentage_Avg, Date)
SELECT 4232, Percentage_Min, Percentage_Max, Percentage_Avg, Date
FROM Percentage_Calendar
WHERE DeviceRowID = 523
  AND Date < (SELECT MIN(Date) FROM Percentage_Calendar WHERE DeviceRowID = 4232);

Finally, some DB housekeeping

Code: Select all

PRAGMA integrity_check;
PRAGMA optimize;
VACUUM;
PRAGMA shrink_memory;