wirenboard / wb-mqtt-db / 96

Committed 31 Jul 2026 10:43AM UTC coverage: 78.543% (-1.8%) from 80.337%

Build # 96

Build Type

push

github

Committed by

web-flow

Commit Message

Use exported vars instead of MAKEFLAGS for coverage options (#66)

Coverage Stats

938 of 1102 branches covered (85.12%)

1563 of 1990 relevant lines covered (78.54%)

13.11 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

2.3

/src/db_migrations.cpp

#include "db_migrations.h"

#include <iostream>
#include <sqlite3.h>

namespace
{
    void ConvertDb0To1(SQLite::Database& db)
    {
        db.exec("ALTER TABLE data RENAME TO tmp");

        // drop existing indexes
        db.exec("DROP INDEX data_topic");
        db.exec("DROP INDEX data_topic_timestamp");
        db.exec("DROP INDEX data_gid");
        db.exec("DROP INDEX data_gid_timestamp");

        // create tables
        db.exec("CREATE TABLE IF NOT EXISTS devices ( "
                "int_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "device VARCHAR(255) UNIQUE "
                " )  ");

        db.exec("CREATE TABLE IF NOT EXISTS channels ( "
                "int_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "device VARCHAR(255), "
                "control VARCHAR(255) "
                ")  ");

        db.exec("CREATE TABLE IF NOT EXISTS groups ( "
                "int_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "group_id VARCHAR(255) "
                ")  ");

        db.exec("CREATE TABLE IF NOT EXISTS data ("
                "uid INTEGER PRIMARY KEY AUTOINCREMENT, "
                "device INTEGER,"
                "channel INTEGER,"
                "value VARCHAR(255),"
                "timestamp REAL DEFAULT(julianday('now')),"
                "group_id INTEGER"
                ")");

        db.exec("CREATE TABLE IF NOT EXISTS variables ("
                "name VARCHAR(255) PRIMARY KEY, "
                "value VARCHAR(255) )");

        // generate internal integer ids from old data table
        db.exec("INSERT OR IGNORE INTO devices (device) SELECT device FROM tmp GROUP "
                "BY device");
        db.exec("INSERT OR IGNORE INTO channels (device, control) SELECT device, "
                "control FROM tmp "
                "GROUP BY device, control");
        db.exec("INSERT OR IGNORE INTO groups (group_id) SELECT group_id FROM tmp "
                "GROUP BY group_id");

        // populate data table using values from old data table
        db.exec("INSERT INTO data(uid, device, channel,value,timestamp,group_id) "
                "SELECT uid, devices.int_id, channels.int_id, value, "
                "julianday(timestamp), groups.int_id "
                "FROM tmp "
                "LEFT JOIN devices ON tmp.device = devices.device "
                "LEFT JOIN channels ON tmp.device = channels.device AND tmp.control "
                "= channels.control "
                "LEFT JOIN groups ON tmp.group_id = groups.group_id ");

        db.exec("DROP TABLE tmp");

        db.exec("UPDATE variables SET value=\"1\" WHERE name=\"db_version\"");
    }

    void ConvertDb1To2(SQLite::Database& db)
    {
        // In versions >= 2, there is a difference in 'data' table:
        // add data.max, data.min columns
        db.exec("ALTER TABLE data ADD COLUMN max VARCHAR(255)");
        db.exec("ALTER TABLE data ADD COLUMN min VARCHAR(255)");
        db.exec("ALTER TABLE data ADD COLUMN retained INTEGER");

        db.exec("UPDATE data SET max = value");
        db.exec("UPDATE data SET min = value");
        db.exec("UPDATE data SET retained = 0");

        db.exec("UPDATE variables SET value=\"2\" WHERE name=\"db_version\"");
    }

    void ConvertDb2To3(SQLite::Database& db)
    {
        // save old data table
        db.exec("ALTER TABLE data RENAME TO data_old");

        // create new data table
        db.exec("CREATE TABLE data ("
                "uid INTEGER PRIMARY KEY AUTOINCREMENT,"
                "device INTEGER,"
                "channel INTEGER,"
                "value VARCHAR(255),"
                "timestamp INTEGER DEFAULT(0),"
                "group_id INTEGER,"
                "max VARCHAR(255),"
                "min VARCHAR(255),"
                "retained INTEGER"
                ")");

        // copy all data casting timestamps
        db.exec("INSERT INTO data "
                "SELECT uid, device, channel, value, "
                "CAST((timestamp - 2440587.5) * 86400000 AS INTEGER), group_id, max, "
                "min, retained "
                "FROM data_old");

        // drop old data table
        db.exec("DROP TABLE data_old");

        db.exec("UPDATE variables SET value=\"3\" WHERE name=\"db_version\"");
    }

    void ConvertDb3To4(SQLite::Database& db)
    {
        // save old channels table
        db.exec("ALTER TABLE channels RENAME TO channels_old");

        // save new channels table
        db.exec("CREATE TABLE channels ( "
                "int_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "device VARCHAR(255), "
                "control VARCHAR(255), "
                "UNIQUE(device,control) "
                ")");

        // save unique channels to new channels table and update keys in data table
        {
            SQLite::Statement query(db,
                                    "SELECT int_id, device, control FROM "
                                    "channels_old ORDER BY device, control");
            int prevId = -1;
            std::string prevDevice;
            std::string prevControl;
            while (query.executeStep()) {
                std::string curDevice(query.getColumn(1).getString());
                std::string curControl(query.getColumn(2).getString());
                if ((curDevice == prevDevice) && (curControl == prevControl)) {
                    SQLite::Statement updateQuery(db, "UPDATE data SET channel=? WHERE channel=?");
                    updateQuery.bind(1, prevId);
                    updateQuery.bind(2, query.getColumn(0).getInt());
                    updateQuery.exec();
                } else {
                    prevDevice = curDevice;
                    prevControl = curControl;
                    prevId = query.getColumn(0).getInt();
                    SQLite::Statement insertQuery(db, "INSERT INTO channels(int_id, device, control) VALUES(?, ?, ?)");
                    insertQuery.bind(1, prevId);
                    insertQuery.bind(2, curDevice);
                    insertQuery.bind(3, curControl);
                    insertQuery.exec();
                }
            }
        }

        // drop old channels table
        db.exec("DROP TABLE channels_old");

        // save old data table
        db.exec("ALTER TABLE data RENAME TO data_old");

        // create new data table
        db.exec("CREATE TABLE data ("
                "uid INTEGER PRIMARY KEY AUTOINCREMENT,"
                "channel INTEGER,"
                "value VARCHAR(255),"
                "timestamp INTEGER DEFAULT(0),"
                "group_id INTEGER,"
                "max VARCHAR(255),"
                "min VARCHAR(255),"
                "retained INTEGER"
                ")");

        // copy all data without device field
        db.exec("INSERT INTO data "
                "SELECT uid, channel, value, timestamp, group_id, max, min, retained "
                "FROM data_old");

        // drop old data table
        db.exec("DROP TABLE data_old");

        // drop old devices table
        db.exec("DROP TABLE devices");

        db.exec("UPDATE variables SET value=\"4\" WHERE name=\"db_version\"");
    }

    void ConvertDb4To5(SQLite::Database& db)
    {
        // In version 5 groups table and group_id field in data table are removed

        // save old data table
        db.exec("ALTER TABLE data RENAME TO data_old");

        // create new data table
        db.exec("CREATE TABLE data ("
                "uid INTEGER PRIMARY KEY AUTOINCREMENT,"
                "channel INTEGER,"
                "value VARCHAR(255),"
                "timestamp INTEGER DEFAULT(0),"
                "max VARCHAR(255),"
                "min VARCHAR(255),"
                "retained INTEGER"
                ")");

        // copy all data without device field
        db.exec("INSERT INTO data "
                "SELECT uid, channel, value, timestamp, max, min, retained "
                "FROM data_old");

        // drop old data table
        db.exec("DROP TABLE data_old");

        // drop groups table
        db.exec("DROP TABLE groups");

        db.exec("UPDATE variables SET value=\"5\" WHERE name=\"db_version\"");
    }

    void ConvertDb5To6(SQLite::Database& db)
    {
        // add precision column to channel
        try {
            db.exec("ALTER TABLE channels ADD COLUMN precision REAL");
        } catch (const SQLite::Exception& e) { // The column could de already added
            if (e.getErrorCode() != SQLITE_ERROR) {
                throw;
            }
        }

        db.exec("UPDATE variables SET value=\"6\" WHERE name=\"db_version\"");
    }
} // namespace

std::vector<ConvertDbFnType> GetMigrations()
{
    return {ConvertDb0To1, ConvertDb1To2, ConvertDb2To3, ConvertDb3To4, ConvertDb4To5, ConvertDb5To6};
}

1	#include "db_migrations.h"
2
3	#include <iostream>
4	#include <sqlite3.h>
5
6	namespace
7	{
8	void ConvertDb0To1(SQLite::Database& db)	×
9	{
10	db.exec("ALTER TABLE data RENAME TO tmp");	×
11
12	// drop existing indexes
13	db.exec("DROP INDEX data_topic");	×
14	db.exec("DROP INDEX data_topic_timestamp");	×
15	db.exec("DROP INDEX data_gid");	×
16	db.exec("DROP INDEX data_gid_timestamp");	×
17
18	// create tables
19	db.exec("CREATE TABLE IF NOT EXISTS devices ( "	×
20	"int_id INTEGER PRIMARY KEY AUTOINCREMENT, "
21	"device VARCHAR(255) UNIQUE "
22	" ) ");
23
24	db.exec("CREATE TABLE IF NOT EXISTS channels ( "	×
25	"int_id INTEGER PRIMARY KEY AUTOINCREMENT, "
26	"device VARCHAR(255), "
27	"control VARCHAR(255) "
28	") ");
29
30	db.exec("CREATE TABLE IF NOT EXISTS groups ( "	×
31	"int_id INTEGER PRIMARY KEY AUTOINCREMENT, "
32	"group_id VARCHAR(255) "
33	") ");
34
35	db.exec("CREATE TABLE IF NOT EXISTS data ("	×
36	"uid INTEGER PRIMARY KEY AUTOINCREMENT, "
37	"device INTEGER,"
38	"channel INTEGER,"
39	"value VARCHAR(255),"
40	"timestamp REAL DEFAULT(julianday('now')),"
41	"group_id INTEGER"
42	")");
43
44	db.exec("CREATE TABLE IF NOT EXISTS variables ("	×
45	"name VARCHAR(255) PRIMARY KEY, "
46	"value VARCHAR(255) )");
47
48	// generate internal integer ids from old data table
49	db.exec("INSERT OR IGNORE INTO devices (device) SELECT device FROM tmp GROUP "	×
50	"BY device");
51	db.exec("INSERT OR IGNORE INTO channels (device, control) SELECT device, "	×
52	"control FROM tmp "
53	"GROUP BY device, control");
54	db.exec("INSERT OR IGNORE INTO groups (group_id) SELECT group_id FROM tmp "	×
55	"GROUP BY group_id");
56
57	// populate data table using values from old data table
58	db.exec("INSERT INTO data(uid, device, channel,value,timestamp,group_id) "	×
59	"SELECT uid, devices.int_id, channels.int_id, value, "
60	"julianday(timestamp), groups.int_id "
61	"FROM tmp "
62	"LEFT JOIN devices ON tmp.device = devices.device "
63	"LEFT JOIN channels ON tmp.device = channels.device AND tmp.control "
64	"= channels.control "
65	"LEFT JOIN groups ON tmp.group_id = groups.group_id ");
66
67	db.exec("DROP TABLE tmp");	×
68
69	db.exec("UPDATE variables SET value=\"1\" WHERE name=\"db_version\"");	×
70	}	×
71
72	void ConvertDb1To2(SQLite::Database& db)	×
73	{
74	// In versions >= 2, there is a difference in 'data' table:
75	// add data.max, data.min columns
76	db.exec("ALTER TABLE data ADD COLUMN max VARCHAR(255)");	×
77	db.exec("ALTER TABLE data ADD COLUMN min VARCHAR(255)");	×
78	db.exec("ALTER TABLE data ADD COLUMN retained INTEGER");	×
79
80	db.exec("UPDATE data SET max = value");	×
81	db.exec("UPDATE data SET min = value");	×
82	db.exec("UPDATE data SET retained = 0");	×
83
84	db.exec("UPDATE variables SET value=\"2\" WHERE name=\"db_version\"");	×
85	}	×
86
87	void ConvertDb2To3(SQLite::Database& db)	×
88	{
89	// save old data table
90	db.exec("ALTER TABLE data RENAME TO data_old");	×
91
92	// create new data table
93	db.exec("CREATE TABLE data ("	×
94	"uid INTEGER PRIMARY KEY AUTOINCREMENT,"
95	"device INTEGER,"
96	"channel INTEGER,"
97	"value VARCHAR(255),"
98	"timestamp INTEGER DEFAULT(0),"
99	"group_id INTEGER,"
100	"max VARCHAR(255),"
101	"min VARCHAR(255),"
102	"retained INTEGER"
103	")");
104
105	// copy all data casting timestamps
106	db.exec("INSERT INTO data "	×
107	"SELECT uid, device, channel, value, "
108	"CAST((timestamp - 2440587.5) * 86400000 AS INTEGER), group_id, max, "
109	"min, retained "
110	"FROM data_old");
111
112	// drop old data table
113	db.exec("DROP TABLE data_old");	×
114
115	db.exec("UPDATE variables SET value=\"3\" WHERE name=\"db_version\"");	×
116	}	×
117
118	void ConvertDb3To4(SQLite::Database& db)	×
119	{
120	// save old channels table
121	db.exec("ALTER TABLE channels RENAME TO channels_old");	×
122
123	// save new channels table
124	db.exec("CREATE TABLE channels ( "	×
125	"int_id INTEGER PRIMARY KEY AUTOINCREMENT, "
126	"device VARCHAR(255), "
127	"control VARCHAR(255), "
128	"UNIQUE(device,control) "
129	")");
130
131	// save unique channels to new channels table and update keys in data table
132	{
133	SQLite::Statement query(db,
134	"SELECT int_id, device, control FROM "
135	"channels_old ORDER BY device, control");	×
136	int prevId = -1;	×
137	std::string prevDevice;	×
138	std::string prevControl;	×
139	while (query.executeStep()) {	×
140	std::string curDevice(query.getColumn(1).getString());	×
141	std::string curControl(query.getColumn(2).getString());	×
142	if ((curDevice == prevDevice) && (curControl == prevControl)) {	×
143	SQLite::Statement updateQuery(db, "UPDATE data SET channel=? WHERE channel=?");	×
144	updateQuery.bind(1, prevId);	×
145	updateQuery.bind(2, query.getColumn(0).getInt());	×
146	updateQuery.exec();	×
147	} else {	×
148	prevDevice = curDevice;	×
149	prevControl = curControl;	×
150	prevId = query.getColumn(0).getInt();	×
151	SQLite::Statement insertQuery(db, "INSERT INTO channels(int_id, device, control) VALUES(?, ?, ?)");	×
152	insertQuery.bind(1, prevId);	×
153	insertQuery.bind(2, curDevice);	×
154	insertQuery.bind(3, curControl);	×
155	insertQuery.exec();	×
156	}	×
157	}	×
158	}	×
159
160	// drop old channels table
161	db.exec("DROP TABLE channels_old");	×
162
163	// save old data table
164	db.exec("ALTER TABLE data RENAME TO data_old");	×
165
166	// create new data table
167	db.exec("CREATE TABLE data ("	×
168	"uid INTEGER PRIMARY KEY AUTOINCREMENT,"
169	"channel INTEGER,"
170	"value VARCHAR(255),"
171	"timestamp INTEGER DEFAULT(0),"
172	"group_id INTEGER,"
173	"max VARCHAR(255),"
174	"min VARCHAR(255),"
175	"retained INTEGER"
176	")");
177
178	// copy all data without device field
179	db.exec("INSERT INTO data "	×
180	"SELECT uid, channel, value, timestamp, group_id, max, min, retained "
181	"FROM data_old");
182
183	// drop old data table
184	db.exec("DROP TABLE data_old");	×
185
186	// drop old devices table
187	db.exec("DROP TABLE devices");	×
188
189	db.exec("UPDATE variables SET value=\"4\" WHERE name=\"db_version\"");	×
190	}	×
191
192	void ConvertDb4To5(SQLite::Database& db)	×
193	{
194	// In version 5 groups table and group_id field in data table are removed
195
196	// save old data table
197	db.exec("ALTER TABLE data RENAME TO data_old");	×
198
199	// create new data table
200	db.exec("CREATE TABLE data ("	×
201	"uid INTEGER PRIMARY KEY AUTOINCREMENT,"
202	"channel INTEGER,"
203	"value VARCHAR(255),"
204	"timestamp INTEGER DEFAULT(0),"
205	"max VARCHAR(255),"
206	"min VARCHAR(255),"
207	"retained INTEGER"
208	")");
209
210	// copy all data without device field
211	db.exec("INSERT INTO data "	×
212	"SELECT uid, channel, value, timestamp, max, min, retained "
213	"FROM data_old");
214
215	// drop old data table
216	db.exec("DROP TABLE data_old");	×
217
218	// drop groups table
219	db.exec("DROP TABLE groups");	×
220
221	db.exec("UPDATE variables SET value=\"5\" WHERE name=\"db_version\"");	×
222	}	×
223
224	void ConvertDb5To6(SQLite::Database& db)	×
225	{
226	// add precision column to channel
227	try {
228	db.exec("ALTER TABLE channels ADD COLUMN precision REAL");	×
229	} catch (const SQLite::Exception& e) { // The column could de already added	×
230	if (e.getErrorCode() != SQLITE_ERROR) {	×
231	throw;	×
232	}
233	}	×
234
235	db.exec("UPDATE variables SET value=\"6\" WHERE name=\"db_version\"");	×
236	}	×
237	} // namespace
238
239	std::vector<ConvertDbFnType> GetMigrations()	1✔
240	{
241	return {ConvertDb0To1, ConvertDb1To2, ConvertDb2To3, ConvertDb3To4, ConvertDb4To5, ConvertDb5To6};	3✔
242	}

wirenboard / wb-mqtt-db / 96

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous