2
* Copyright (c) 2012 Mark Liversedge (liversedge@gmail.com)
4
* This program is free software; you can redistribute it and/or modify it
5
* under the terms of the GNU General Public License as published by the Free
6
* Software Foundation; either version 2 of the License, or (at your option)
9
* This program is distributed in the hope that it will be useful, but WITHOUT
10
* ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
11
* FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
14
* You should have received a copy of the GNU General Public License along
15
* with this program; if not, write to the Free Software Foundation, Inc., 51
16
* Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
22
// DB Schema Version - YOU MUST UPDATE THIS IF THE TRAIN DB SCHEMA CHANGES
25
// Rev Date Who What Changed
26
// 01 21 Dec 2012 Mark Liversedge Initial Build
28
static int TrainDBSchemaVersion = 1;
31
TrainDB::TrainDB(QDir home) : home(home)
33
// we live above the rider directory
37
void TrainDB::closeConnection()
48
TrainDB::initDatabase(QDir home)
52
if(dbconn.isOpen()) return;
55
db = QSqlDatabase::addDatabase("QSQLITE", "1");
56
db.setDatabaseName(home.absolutePath() + "/trainDB");
57
dbconn = db.database("1");
59
if (!dbconn.isOpen()) {
60
QMessageBox::critical(0, qApp->translate("TrainDB","Cannot open database"),
61
qApp->translate("TrainDB","Unable to establish a database connection.\n"
62
"This feature requires SQLite support. Please read "
63
"the Qt SQL driver documentation for information how "
65
"Click Cancel to exit."), QMessageBox::Cancel);
68
// create database - does nothing if its already there
73
// rebuild effectively drops and recreates all tables
74
// but not the version table, since its about deleting
75
// user data (e.g. when rescanning their hard disk)
85
bool TrainDB::createVideoTable()
87
QSqlQuery query(dbconn);
89
bool createTables = true;
91
// does the table exist?
92
rc = query.exec("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;");
94
while (query.next()) {
96
QString table = query.value(0).toString();
97
if (table == "videos") {
103
// we need to create it!
104
if (rc && createTables) {
106
QString createVideoTable = "create table videos (filepath varchar primary key,"
111
rc = query.exec(createVideoTable);
113
// insert the 'DVD' record for playing currently loaded DVD
114
// need to resolve DVD playback in v3.1, there is an open feature request for this.
115
// rc = query.exec("INSERT INTO videos (filepath, filename) values (\"\", \"DVD\");");
117
// add row to version database
118
query.exec("DELETE FROM version where table_name = \"videos\"");
121
query.prepare("INSERT INTO version (table_name, schema_version, creation_date) values (?,?,?);");
122
query.addBindValue("videos");
123
query.addBindValue(TrainDBSchemaVersion);
124
query.addBindValue(QDateTime::currentDateTime().toTime_t());
130
bool TrainDB::createWorkoutTable()
132
QSqlQuery query(dbconn);
134
bool createTables = true;
136
// does the table exist?
137
rc = query.exec("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;");
139
while (query.next()) {
141
QString table = query.value(0).toString();
142
if (table == "workouts") {
143
createTables = false;
148
// we need to create it!
149
if (rc && createTables) {
151
QString createMetricTable = "create table workouts (filepath varchar primary key,"
154
"description varchar,"
158
"coggan_tss integer,"
163
rc = query.exec(createMetricTable);
165
// adding a space at the front of string to make manual mode always
166
// appear first in a sorted list is a bit of a hack, but works ok
167
QString manualErg = QString("INSERT INTO workouts (filepath, filename) values (\"//1\", \"%1\");")
168
.arg(tr(" Manual Erg Mode"));
169
rc = query.exec(manualErg);
171
QString manualCrs = QString("INSERT INTO workouts (filepath, filename) values (\"//2\", \"%1\");")
172
.arg(tr(" Manual Slope Mode"));
173
rc = query.exec(manualCrs);
175
// add row to version database
176
query.exec("DELETE FROM version where table_name = \"workouts\"");
179
query.prepare("INSERT INTO version (table_name, schema_version, creation_date) values (?,?,?);");
180
query.addBindValue("workouts");
181
query.addBindValue(TrainDBSchemaVersion);
182
query.addBindValue(QDateTime::currentDateTime().toTime_t());
188
bool TrainDB::dropVideoTable()
190
QSqlQuery query("DROP TABLE videos", dbconn);
191
bool rc = query.exec();
195
bool TrainDB::dropWorkoutTable()
197
QSqlQuery query("DROP TABLE workouts", dbconn);
198
bool rc = query.exec();
202
bool TrainDB::createDatabase()
204
// check schema version and if missing recreate database
208
createWorkoutTable();
214
void TrainDB::checkDBVersion()
216
// can we get a version number?
217
QSqlQuery query("SELECT table_name, schema_version, creation_date from version;", dbconn);
219
bool rc = query.exec();
222
// we couldn't read the version table properly
223
// it must be out of date!!
225
QSqlQuery dropM("DROP TABLE version", dbconn);
228
// recreate version table and add one entry
229
QSqlQuery version("CREATE TABLE version ( table_name varchar primary key, schema_version integer, creation_date date );", dbconn);
232
// wipe away whatever (if anything is there)
234
createWorkoutTable();
241
// ok we checked out ok, so lets adjust db schema to reflect
242
// tne current version / crc
243
bool dropWorkout = false;
244
bool dropVideo = false;
245
while (query.next()) {
247
QString table_name = query.value(0).toString();
248
int currentversion = query.value(1).toInt();
250
if (table_name == "workouts" && currentversion != TrainDBSchemaVersion) dropWorkout = true;
251
if (table_name == "videos" && currentversion != TrainDBSchemaVersion) dropVideo = true;
255
// "workouts" table, is it up-to-date?
256
if (dropWorkout) dropWorkoutTable();
257
if (dropVideo) dropVideoTable();
260
int TrainDB::getCount()
262
// how many workouts are there?
263
QSqlQuery query("SELECT count(*) from workouts;", dbconn);
264
bool rc = query.exec();
267
while (query.next()) {
268
return query.value(0).toInt();
275
int TrainDB::getDBVersion()
277
int schema_version = -1;
279
// can we get a version number?
280
QSqlQuery query("SELECT schema_version from version;", dbconn);
282
bool rc = query.exec();
285
while (query.next()) {
286
if (query.value(0).toInt() > schema_version)
287
schema_version = query.value(0).toInt();
291
return schema_version;
294
/*----------------------------------------------------------------------
296
*----------------------------------------------------------------------*/
298
bool TrainDB::deleteWorkout(QString pathname)
300
QSqlQuery query(dbconn);
301
QDateTime timestamp = QDateTime::currentDateTime();
303
// zap the current row - if there is one
304
query.prepare("DELETE FROM workouts WHERE filepath = ?;");
305
query.addBindValue(pathname);
310
bool TrainDB::importWorkout(QString pathname, ErgFile *ergFile)
312
QSqlQuery query(dbconn);
313
QDateTime timestamp = QDateTime::currentDateTime();
315
// zap the current row - if there is one
316
query.prepare("DELETE FROM workouts WHERE filepath = ?;");
317
query.addBindValue(pathname);
320
// construct an insert statement
321
QString insertStatement = "insert into workouts ( filepath, "
331
"grade ) values ( ?,?,?,?,?,?,?,?,?,?,? );";
332
query.prepare(insertStatement);
334
// filename, timestamp, ride date
335
query.addBindValue(pathname);
336
query.addBindValue(QFileInfo(pathname).fileName());
337
query.addBindValue(timestamp);
338
query.addBindValue(ergFile->Name);
339
query.addBindValue(ergFile->Source);
340
query.addBindValue(ergFile->Ftp);
341
query.addBindValue((int)ergFile->Duration);
342
query.addBindValue(ergFile->TSS);
343
query.addBindValue(ergFile->IF);
344
query.addBindValue(ergFile->ELE);
345
query.addBindValue(ergFile->GRADE);
348
bool rc = query.exec();
353
bool TrainDB::deleteVideo(QString pathname)
355
QSqlQuery query(dbconn);
356
QDateTime timestamp = QDateTime::currentDateTime();
358
// zap the current row - if there is one
359
query.prepare("DELETE FROM videos WHERE filepath = ?;");
360
query.addBindValue(pathname);
364
bool TrainDB::importVideo(QString pathname)
366
QSqlQuery query(dbconn);
367
QDateTime timestamp = QDateTime::currentDateTime();
369
// zap the current row - if there is one
370
query.prepare("DELETE FROM videos WHERE filepath = ?;");
371
query.addBindValue(pathname);
374
// construct an insert statement
375
QString insertStatement = "insert into videos ( filepath,filename ) values ( ?,? );";
376
query.prepare(insertStatement);
378
// filename, timestamp, ride date
379
query.addBindValue(pathname);
380
query.addBindValue(QFileInfo(pathname).fileName());
383
bool rc = query.exec();