forked from edman007/chiton
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdatabase_manager.cpp
277 lines (255 loc) · 9.81 KB
/
database_manager.cpp
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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
/**************************************************************************
*
* This file is part of Chiton.
*
* Chiton is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* Chiton is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with Chiton. If not, see <https://www.gnu.org/licenses/>.
*
* Copyright 2020 Ed Martin <[email protected]>
*
**************************************************************************
*/
#include "database_manager.hpp"
#include "util.hpp"
//we increment Major when we break backwards compatibility
static const int CURRENT_DB_VERSION_MAJOR = 1;
//we increment minor when we add a backwards compatible version
static const int CURRENT_DB_VERSION_MINOR = 2;
static const std::string CURRENT_DB_VERSION = std::to_string(CURRENT_DB_VERSION_MAJOR) + "." + std::to_string(CURRENT_DB_VERSION_MINOR);
DatabaseManager::DatabaseManager(Database &db) : db(db) {
}
bool DatabaseManager::initilize_db(){
const std::string config_tbl = "CREATE TABLE config ( "
"name varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL, "
"value varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, "
"camera int(11) NOT NULL, "
"UNIQUE KEY camera (camera,name) "
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
const std::string videos_tbl = "CREATE TABLE videos ( "
"id int(11) unsigned NOT NULL AUTO_INCREMENT, "
"path varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, "
"starttime bigint(20) NOT NULL, "
"endtime bigint(20) DEFAULT NULL, "
"camera int(11) NOT NULL, "
"`locked` tinyint(1) NOT NULL DEFAULT 0, "
"PRIMARY KEY (id,camera,starttime), "
"KEY endtime (endtime), "
"KEY starttime (starttime), "
"KEY camera (camera) "
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
const std::string exports_tbl = "CREATE TABLE `exports` ( "
"`id` int(10) unsigned NOT NULL AUTO_INCREMENT, "
"`camera` int(11) NOT NULL, "
"`path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', "
"`starttime` bigint(20) NOT NULL, "
"`endtime` bigint(20) NOT NULL, "
"`progress` int(11) NOT NULL DEFAULT 0, "
"PRIMARY KEY (`id`), "
"KEY `camera` (`camera`,`starttime`,`progress`) "
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
const std::string images_tbl = "CREATE TABLE `images` ( "
"`id` int(11) NOT NULL AUTO_INCREMENT, "
" camera int(11) NOT NULL, "
"`path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, "
"`prefix` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, "
"`extension` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, "
"`starttime` bigint(20) NOT NULL, "
"PRIMARY KEY (`id`), "
"KEY starttime (starttime) "
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
//create the config table
DatabaseResult *res = db.query(config_tbl);
bool ret = true;
if (res){
delete res;
} else {
LFATAL("Could not create config table");
ret = false;
}
//create the videos table
if (ret){
res = db.query(videos_tbl);
if (res){
delete res;
} else {
LFATAL("Could not create videos table");
ret = false;
}
}
//create the exports table
if (ret){
res = db.query(exports_tbl);
if (res){
delete res;
} else {
LFATAL("Could not create exports table");
ret = false;
}
}
//create the images table
if (ret){
res = db.query(images_tbl);
if (res){
delete res;
} else {
LFATAL("Could not create images table");
ret = false;
}
}
if (ret){
return set_latest_version();
}
return ret;
}
bool DatabaseManager::check_database(void){
std::string sql = "SELECT value FROM config WHERE name = 'database-version' AND camera = -1";
DatabaseResult *res = db.query(sql);
bool ret = false;
if (res && res->next_row()){
std::string cur_version = res->get_field(0);
int cur_major;
int cur_minor;
try {
cur_major = std::stoi(cur_version.substr(0, cur_version.find('.')));
cur_minor = std::stoi(cur_version.substr(cur_version.find('.') + 1));
} catch (std::invalid_argument &e){
LDEBUG("Database Version is invalid");
cur_major = INT_MAX;
cur_minor = INT_MAX;
} catch (std::out_of_range &e) {
LDEBUG("Database Version is out of range");
cur_major = INT_MAX;
cur_minor = INT_MAX;
}
if (cur_major > CURRENT_DB_VERSION_MAJOR){
LFATAL("Error, current database version " + cur_version + " is incompible with this version of chiton");
} else if (cur_major < CURRENT_DB_VERSION_MAJOR){
if (!upgrade_database(cur_major, cur_minor)){
LFATAL("The database needs an upgrade, but the upgrade from " + cur_version + " to " + CURRENT_DB_VERSION);//shouldn't happen
} else {
ret = true;
}
//normally we would just upgrade...but no such upgrade is possible
} else {//equal
if (cur_minor > CURRENT_DB_VERSION_MINOR){
LWARN("Current database version is newer than expected (" + cur_version + " -> " + CURRENT_DB_VERSION
+ "), continuing without changing the database version");
ret = true;//this is not an issue
} else if (cur_minor < CURRENT_DB_VERSION_MINOR){
if (!upgrade_database(cur_major, cur_minor)){
LFATAL("The database needs an upgrade, but no other version is known, is " + cur_version + " but expected " + CURRENT_DB_VERSION);//shouldn't happen
} else {
ret = true;
}
} else {//equal
LINFO("Detected latest database version " + CURRENT_DB_VERSION);
ret = true;
}
}
} else {
//no database version? then we try initilizing it
ret = initilize_db();
}
delete res;
return ret;
}
bool DatabaseManager::set_latest_version(void){
std::string sql = "INSERT INTO config (camera, name, value) VALUES (-1, 'database-version', '"
+ CURRENT_DB_VERSION + "') ON DUPLICATE KEY UPDATE "
+ " value = '" + CURRENT_DB_VERSION + "'";
long affected_rows = 0;
DatabaseResult *res = db.query(sql, &affected_rows, NULL);
bool ret = false;
if (res && affected_rows){
LINFO("Database Upgraded to " + CURRENT_DB_VERSION);
ret = true;
} else {
LFATAL("Failed to update database version");
}
delete res;
return ret;
}
bool DatabaseManager::upgrade_database(int major, int minor){
if (1 == major){
switch (minor){
case 0:
return upgrade_from_1_0();
case 1:
return upgrade_from_1_1();
default:
return false;
}
}
return false;
}
bool DatabaseManager::upgrade_from_1_0(void){
const std::string alter_query = "ALTER TABLE `videos` ADD `locked` BOOLEAN NOT NULL DEFAULT FALSE AFTER `camera`";
const std::string exports_tbl = "CREATE TABLE `exports` ( "
"`id` int(10) unsigned NOT NULL AUTO_INCREMENT, "
"`camera` int(11) NOT NULL, "
"`path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', "
"`starttime` bigint(20) NOT NULL, "
"`endtime` bigint(20) NOT NULL, "
"`progress` int(11) NOT NULL DEFAULT 0, "
"PRIMARY KEY (`id`), "
"KEY `camera` (`camera`,`starttime`,`progress`) "
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
DatabaseResult *res = db.query(alter_query);
bool ret = true;
if (res){
delete res;
} else {
LWARN("Database Upgrade Failed - " + alter_query);
ret = false;
}
//create the exports table
if (ret){
res = db.query(exports_tbl);
if (res){
delete res;
} else {
LFATAL("Could not create exports table");
ret = false;
}
}
if (ret){
return upgrade_from_1_1();
} else {
return ret;
}
}
bool DatabaseManager::upgrade_from_1_1(void){
const std::string images_tbl = "CREATE TABLE `images` ( "
"`id` int(11) NOT NULL AUTO_INCREMENT, "
"camera int(11) NOT NULL, "
"`path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, "
"`prefix` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, "
"`extension` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, "
"`starttime` bigint(20) NOT NULL, "
"PRIMARY KEY (`id`), "
"KEY starttime (starttime) "
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
bool ret = true;
DatabaseResult *res = db.query(images_tbl);
if (res){
delete res;
} else {
LFATAL("Could not create images table");
ret = false;
}
if (ret){
return set_latest_version();
} else {
return ret;
}
}