1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
| -- ============================================
-- BEGIN MIGRATION SCRIPT
-- This script migrates historical data from old devices to new devices
-- using the new device's earliest recorded Date as the cutoff.
-- Run this on a backup copy of your database with Domoticz stopped.
-- ============================================
-- ==================================================
-- Migrate Percentage (Custom) Devices
-- ==================================================
-- 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);
-- Migrate from old IDX 524 to new IDX 4233
INSERT INTO Percentage (DeviceRowID, Percentage, Date)
SELECT 4233, Percentage, Date
FROM Percentage
WHERE DeviceRowID = 524
AND Date < (SELECT MIN(Date) FROM Percentage WHERE DeviceRowID = 4233);
-- Migrate from old IDX 525 to new IDX 4234
INSERT INTO Percentage (DeviceRowID, Percentage, Date)
SELECT 4234, Percentage, Date
FROM Percentage
WHERE DeviceRowID = 525
AND Date < (SELECT MIN(Date) FROM Percentage WHERE DeviceRowID = 4234);
-- 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);
-- Migrate from old IDX 524 to new IDX 4233
INSERT INTO Percentage_Calendar (DeviceRowID, Percentage_Min, Percentage_Max, Percentage_Avg, Date)
SELECT 4233, Percentage_Min, Percentage_Max, Percentage_Avg, Date
FROM Percentage_Calendar
WHERE DeviceRowID = 524
AND Date < (SELECT MIN(Date) FROM Percentage_Calendar WHERE DeviceRowID = 4233);
-- Migrate from old IDX 525 to new IDX 4234
INSERT INTO Percentage_Calendar (DeviceRowID, Percentage_Min, Percentage_Max, Percentage_Avg, Date)
SELECT 4234, Percentage_Min, Percentage_Max, Percentage_Avg, Date
FROM Percentage_Calendar
WHERE DeviceRowID = 525
AND Date < (SELECT MIN(Date) FROM Percentage_Calendar WHERE DeviceRowID = 4234);
-- ==================================================
-- Migrate Temperature Devices
-- ==================================================
-- 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);
-- Migrate from old IDX 505 to new IDX 4215
INSERT INTO Temperature (DeviceRowID, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date)
SELECT 4215, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date
FROM Temperature
WHERE DeviceRowID = 505
AND Date < (SELECT MIN(Date) FROM Temperature WHERE DeviceRowID = 4215);
-- Migrate from old IDX 506 to new IDX 4216
INSERT INTO Temperature (DeviceRowID, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date)
SELECT 4216, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date
FROM Temperature
WHERE DeviceRowID = 506
AND Date < (SELECT MIN(Date) FROM Temperature WHERE DeviceRowID = 4216);
-- Migrate from old IDX 507 to new IDX 4217
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
AND Date < (SELECT MIN(Date) FROM Temperature WHERE DeviceRowID = 4217);
-- Migrate from old IDX 508 to new IDX 4218
INSERT INTO Temperature (DeviceRowID, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date)
SELECT 4218, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date
FROM Temperature
WHERE DeviceRowID = 508
AND Date < (SELECT MIN(Date) FROM Temperature WHERE DeviceRowID = 4218);
-- Migrate from old IDX 509 to new IDX 4219
INSERT INTO Temperature (DeviceRowID, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date)
SELECT 4219, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date
FROM Temperature
WHERE DeviceRowID = 509
AND Date < (SELECT MIN(Date) FROM Temperature WHERE DeviceRowID = 4219);
-- Migrate from old IDX 510 to new IDX 4221
INSERT INTO Temperature (DeviceRowID, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date)
SELECT 4221, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date
FROM Temperature
WHERE DeviceRowID = 510
AND Date < (SELECT MIN(Date) FROM Temperature WHERE DeviceRowID = 4221);
-- Migrate from old IDX 511 to new IDX 4222
INSERT INTO Temperature (DeviceRowID, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date)
SELECT 4222, Temperature, Chill, Humidity, Barometer, DewPoint, SetPoint, Date
FROM Temperature
WHERE DeviceRowID = 511
AND Date < (SELECT MIN(Date) FROM Temperature WHERE DeviceRowID = 4222);
-- 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);
-- Migrate from old IDX 505 to new IDX 4215
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 4215, 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 = 505
AND Date < (SELECT MIN(Date) FROM Temperature_Calendar WHERE DeviceRowID = 4215);
-- Migrate from old IDX 506 to new IDX 4216
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 4216, 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 = 506
AND Date < (SELECT MIN(Date) FROM Temperature_Calendar WHERE DeviceRowID = 4216);
-- Migrate from old IDX 507 to new IDX 4217
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
AND Date < (SELECT MIN(Date) FROM Temperature_Calendar WHERE DeviceRowID = 4217);
-- Migrate from old IDX 508 to new IDX 4218
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 4218, 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 = 508
AND Date < (SELECT MIN(Date) FROM Temperature_Calendar WHERE DeviceRowID = 4218);
-- Migrate from old IDX 509 to new IDX 4219
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 4219, 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 = 509
AND Date < (SELECT MIN(Date) FROM Temperature_Calendar WHERE DeviceRowID = 4219);
-- Migrate from old IDX 510 to new IDX 4221
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 4221, 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 = 510
AND Date < (SELECT MIN(Date) FROM Temperature_Calendar WHERE DeviceRowID = 4221);
-- Migrate from old IDX 511 to new IDX 4222
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 4222, 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 = 511
AND Date < (SELECT MIN(Date) FROM Temperature_Calendar WHERE DeviceRowID = 4222);
-- ==================================================
-- Post-Migration Optimizations
-- ==================================================
PRAGMA integrity_check;
PRAGMA optimize;
VACUUM;
PRAGMA shrink_memory;
-- ============================================
-- END OF MIGRATION SCRIPT |
:strip_icc():strip_exif()/u/396258/crop5c58365079ffa_cropped.jpeg?f=community)
:strip_exif()/u/72870/crop5db2a0081b965.gif?f=community)
:no_upscale():strip_icc():strip_exif()/f/image/X07HHn4kNRVHDCQr3VMMOwmm.jpg?f=user_large)
:no_upscale():strip_icc():strip_exif()/f/image/tQNCW0Z3oaRS3eOQS0POqys4.jpg?f=user_large)
:strip_icc():strip_exif()/u/4202/crop59500930db0d0_cropped.jpeg?f=community)
.
:strip_exif()/f/image/1ZMyWVnFM0eWCrCwwSgJrI8j.png?f=user_large)
/u/290474/crop65ba10733d774_cropped.png?f=community)
:strip_icc():strip_exif()/u/2266320/crop67bdb6a6a50fc_cropped.jpg?f=community)
:strip_icc():strip_exif()/u/2761/crop5d863c57a7d5d.jpeg?f=community)
/u/269220/crop64e8b52e29bd2_cropped.png?f=community)
:no_upscale():strip_icc():strip_exif()/f/image/KWlXYFrP6vv5u4vpHCnHR9q6.jpg?f=user_large)
:no_upscale():strip_icc():strip_exif()/f/image/Ii6IvIMIdcyOjbDxQ5Z3kXN3.jpg?f=user_large){kind=small}
:strip_icc():strip_exif()/u/206968/325134.jpg?f=community)
:strip_exif()/f/image/7G3ZkGUy6icIqvqHslIrNZaQ.png?f=user_large)
:strip_exif()/f/image/nZepisLp3B5XvK9FN6OP7XjJ.png?f=user_large)
/u/484268/crop61e4788571a3c_cropped.png?f=community)
:strip_exif()/f/image/YrNtb0Vn8JRl6quLNvq5UaqE.png?f=user_large)
/u/1595/crop6564b5fdadb94_cropped.png?f=community)