PageRenderTime 224ms CodeModel.GetById 40ms app.highlight 151ms RepoModel.GetById 20ms app.codeStats 1ms

/src/DBAccess.cpp

https://github.com/olympum/GoldenCheetah
C++ | 1111 lines | 735 code | 168 blank | 208 comment | 232 complexity | 076ed70ddd7a908be74a4cd8ff0b7315 MD5 | raw file
   1/*
   2 * Copyright (c) 2006 Justin Knotzke (jknotzke@shampoo.ca)
   3 * Copyright (c) 2009 Mark Liversedge (liversedge@gmail.com)
   4 *
   5 * This program is free software; you can redistribute it and/or modify it
   6 * under the terms of the GNU General Public License as published by the Free
   7 * Software Foundation; either version 2 of the License, or (at your option)
   8 * any later version.
   9 *
  10 * This program is distributed in the hope that it will be useful, but WITHOUT
  11 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
  12 * FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for
  13 * more details.
  14 *
  15 * You should have received a copy of the GNU General Public License along
  16 * with this program; if not, write to the Free Software Foundation, Inc., 51
  17 * Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
  18 */
  19
  20#include "MainWindow.h"
  21#include "Athlete.h"
  22#include "DBAccess.h"
  23#include <QtSql>
  24#include <QtGui>
  25#include "RideFile.h"
  26#include "Zones.h"
  27#include "Settings.h"
  28#include "RideItem.h"
  29#include "IntervalItem.h"
  30#include "RideMetric.h"
  31#include "TimeUtils.h"
  32#include <math.h>
  33#include <QtXml/QtXml>
  34#include <QFile>
  35#include <QFileInfo>
  36#include "SummaryMetrics.h"
  37#include "RideMetadata.h"
  38#include "SpecialFields.h"
  39
  40// DB Schema Version - YOU MUST UPDATE THIS IF THE SCHEMA VERSION CHANGES!!!
  41// Schema version will change if a) the default metadata.xml is updated
  42//                            or b) new metrics are added / old changed
  43//                            or c) the metricDB tables structures change
  44
  45// Revision History
  46// Rev Date         Who                What Changed
  47//-----------------------------------------------------------------------
  48//
  49// ******* Prior to version 29 no revision history was maintained *******
  50//
  51// 29  5th Sep 2011 Mark Liversedge    Added color to the ride fields
  52// 30  8th Sep 2011 Mark Liversedge    Metadata 'data' field for data present string
  53// 31  22  Nov 2011 Mark Liversedge    Added Average WPK metric
  54// 32  9th Dec 2011 Damien Grauser     Temperature data flag (metadata field 'Data')
  55// 33  17  Dec 2011 Damien Grauser     Added ResponseIndex and EfficiencyFactor
  56// 34  15  Jan 2012 Mark Liversedge    Added Average and Max Temperature and Metric->conversionSum()
  57// 35  13  Feb 2012 Mark Liversedge    Max/Avg Cadence adjusted conversion
  58// 36  18  Feb 2012 Mark Liversedge    Added Pace (min/mile) and 250m, 500m Pace metrics
  59// 37  06  Apr 2012 Rainer Clasen      Added non-zero average Power (watts)
  60// 38  8th Jul 2012 Mark Liversedge    Computes metrics for manual files now
  61// 39  18  Aug 2012 Mark Liversedge    New metric LRBalance
  62// 40  20  Oct 2012 Mark Liversedge    Lucene search/filter and checkbox metadata field
  63// 41  27  Oct 2012 Mark Liversedge    Lucene switched to StandardAnalyzer and search all texts by default
  64// 42  03  Dec 2012 Mark Liversedge    W/KG ridefilecache changes - force a rebuild.
  65// 43  24  Jan 2012 Mark Liversedge    TRIMP update
  66// 44  19  Apr 2013 Mark Liversedge    Aerobic Decoupling precision reduced to 1pt
  67// 45  09  May 2013 Mark Liversedge    Added 2,3,8 and 90m peak power for fatigue profiling
  68// 46  13  May 2013 Mark Liversedge    Handle absence of speed in metric calculations
  69// 47  17  May 2013 Mark Liversedge    Reimplementation of w/kg and ride->getWeight()
  70// 48  22  May 2013 Mark Liversedge    Removing local measures.xml, till v3.1
  71// 49  29  Oct 2013 Mark Liversedge    Added percentage time in zone
  72// 50  29  Oct 2013 Mark Liversedge    Added percentage time in heartrate zone
  73// 51  05  Nov 2013 Mark Liversedge    Added average aPower
  74// 52  05  Nov 2013 Mark Liversedge    Added EOA - Effect of Altitude
  75// 53  18  Dec 2013 Mark Liversedge    Added Fatigue Index (for power)
  76// 54  07  Jan 2014 Mark Liversedge    Revised Estimated VO2MAX metric formula
  77// 55  20  Jan 2014 Mark Liversedge    Added back Minimum W'bal metric and MaxMatch
  78// 56  20  Jan 2014 Mark Liversedge    Added W' TAU to be able to track it
  79// 57  20  Jan 2014 Mark Liversedge    Added W' Expenditure for total energy spent above CP
  80// 58  23  Jan 2014 Mark Liversedge    W' work rename and calculate without reference to WPrime class (speed)
  81// 59  24  Jan 2014 Mark Liversedge    Added Maximum W' exp which is same as W'bal bur expressed as used not left
  82// 60  05  Feb 2014 Mark Liversedge    Added Critical Power as a metric -- retrieves from settings for now
  83// 61  15  Feb 2014 Mark Liversedge    Fixed W' Work (for recintsecs not 1s!).
  84// 62  06  Mar 2014 Mark Liversedge    Fixed Fatigue Index to find peak then watch for decay, primarily useful in sprint intervals
  85// 63  06  Mar 2014 Mark Liversedge    Added Pacing Index AP as %age of Max Power
  86// 64  17  Mar 2014 Mark Liversedge    Added W' and CP work to PMC metrics
  87// 65  17  Mar 2014 Mark Liversedge    Added Aerobic TISS prototype
  88// 66  18  Mar 2014 Mark Liversedge    Updated aPower calculation
  89// 67  22  Mar 2014 Mark Liversedge    Added Anaerobic TISS prototype
  90// 68  22  Mar 2014 Mark Liversedge    Added dTISS prototype
  91// 69  23  Mar 2014 Mark Liversedge    Updated Gompertz constansts for An-TISS sigmoid
  92// 70  27  Mar 2014 Mark Liversedge    Add file CRC to refresh only if contents change (not just timestamps)
  93// 71  14  Apr 2014 Mark Liversedge    Added average lef/right vector metrics (Pedal Smoothness / Torque Effectiveness)
  94// 72  24  Apr 2014 Mark Liversedge    Andy Froncioni's faster algorithm for W' bal
  95// 73  11  May 2014 Mark Liversedge    Default color of 1,1,1 now uses CPLOTMARKER for ride color, change version to force rebuild
  96// 74  20  May 2014 Mark Liversedge    Added Athlete Weight
  97// 75  25  May 2014 Mark Liversedge    W' work calculation changed to only include energy above CP
  98// 76  14  Jun 2014 Mark Liversedge    Add new 'present' field that uses Data tag data
  99// 77  18  Jun 2014 Mark Liversedge    Add TSS per hour metric
 100// 78  19  Jun 2014 Mark Liversedge    Do not include zeroes in average L/R pedal smoothness/torque effectiveness
 101// 79  20  Jun 2014 Mark Liversedge    Change the way average temperature is handled
 102// 80  13  Jul 2014 Mark Liversedge    W' work + Below CP work = Work
 103// 81  16  Aug 2014 Joern Rischmueller Added 'Elevation Loss'
 104// 82  23  Aug 2014 Mark Liversedge    Added W'bal Matches
 105// 83  05  Sep 2014 Joern Rischmueller Added 'Time Carrying' and 'Elevation Gain Carrying'
 106// 84  08  Sep 2014 Mark Liversedge    Added HrPw Ratio
 107// 85  09  Sep 2014 Mark Liversedge    Added HrNp Ratio
 108// 86  26  Sep 2014 Mark Liversedge    Added isRun first class var
 109// 87  11  Oct 2014 Mark Liversedge    W'bal inegrator fixed up by Dave Waterworth
 110// 88  14  Oct 2014 Mark Liversedge    Pace Zone Metrics
 111// 89  07  Nov 2014 Ale Martinez       GOVSS
 112// 90  08  Nov 2014 Mark Liversedge    Update data flags for Moxy and Garmin Running Dynamics
 113// 91  16  Nov 2014 Damien Grauser     Do not include values if data not present in TimeInZone and HRTimeInZone
 114// 92  21  Nov 2014 Mark Liversedge    Added Watts:RPE ratio
 115// 93  26  Nov 2014 Mark Liversedge    Added Min, Max, Avg SmO2
 116// 94  02  Dic 2014 Ale Martinez       Added xPace
 117
 118int DBSchemaVersion = 94;
 119
 120DBAccess::DBAccess(Context* context) : context(context), db(NULL)
 121{
 122    // check we have one and use built in if not there
 123    RideMetadata::readXML(":/xml/measures.xml", mkeywordDefinitions, mfieldDefinitions, mcolorfield);
 124    initDatabase(context->athlete->home->cache());
 125}
 126
 127DBAccess::~DBAccess()
 128{
 129    if (db) {
 130        db->close();
 131        delete db;
 132        QSqlDatabase::removeDatabase(sessionid);
 133    }
 134}
 135
 136void
 137DBAccess::initDatabase(QDir home)
 138{
 139
 140    sessionid = QString("%1").arg(context->athlete->cyclist);
 141
 142    if(db && db->database(sessionid).isOpen()) return;
 143
 144    // use different name for v3 metricDB to avoid constant rebuilding
 145    // when switching between v2 stable and v3 development builds
 146    db = new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE", sessionid));
 147    db->setDatabaseName(home.canonicalPath() + "/metricDBv3");
 148
 149    if (!db->database(sessionid).isOpen()) {
 150
 151        QMessageBox::critical(0, qApp->translate("DBAccess","Cannot open database"),
 152                       qApp->translate("DBAccess","Unable to establish a database connection.\n"
 153                                       "This feature requires SQLite support. Please read "
 154                                       "the Qt SQL driver documentation for information how "
 155                                       "to build it.\n\n"
 156                                       "Click Cancel to exit."), QMessageBox::Cancel);
 157    } else {
 158
 159        // create database - does nothing if its already there
 160        createDatabase();
 161    }
 162}
 163
 164unsigned int
 165DBAccess::computeFileCRC(QString filename)
 166{
 167    QFile file(filename);
 168    QFileInfo fileinfo(file);
 169
 170    // open file
 171    if (!file.open(QFile::ReadOnly)) return 0;
 172
 173    // allocate space
 174    QScopedArrayPointer<char> data(new char[file.size()]);
 175
 176    // read entire file into memory
 177    QDataStream *rawstream(new QDataStream(&file));
 178    rawstream->readRawData(&data[0], file.size());
 179    file.close();
 180
 181    return qChecksum(&data[0], file.size());
 182}
 183
 184bool DBAccess::createMetricsTable()
 185{
 186    QSqlQuery query(db->database(sessionid));
 187    bool rc;
 188    bool createTables = true;
 189
 190    // does the table exist?
 191    rc = query.exec("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;");
 192    if (rc) {
 193        while (query.next()) {
 194
 195            QString table = query.value(0).toString();
 196            if (table == "metrics") {
 197                createTables = false;
 198                break;
 199            }
 200        }
 201    }
 202
 203    // we need to create it!
 204    if (rc && createTables) {
 205        QString createMetricTable = "create table metrics (filename varchar primary key,"
 206                                    "identifier varchar,"
 207                                    "timestamp integer,"
 208                                    "crc integer,"
 209                                    "ride_date date,"
 210                                    "isRun integer,"
 211                                    "present varchar,"
 212                                    "color varchar,"
 213                                    "fingerprint integer";
 214
 215        // Add columns for all the metric factory metrics
 216        const RideMetricFactory &factory = RideMetricFactory::instance();
 217        for (int i=0; i<factory.metricCount(); i++)
 218            createMetricTable += QString(", X%1 double").arg(factory.metricName(i));
 219
 220        // And all the metadata texts
 221        foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 222            if (!context->specialFields.isMetric(field.name) && (field.type < 3 || field.type == 7)) {
 223                createMetricTable += QString(", Z%1 varchar").arg(context->specialFields.makeTechName(field.name));
 224            }
 225        }
 226
 227        // And all the metadata metrics
 228        foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 229            if (!context->specialFields.isMetric(field.name) && (field.type == 3 || field.type == 4)) {
 230                createMetricTable += QString(", Z%1 double").arg(context->specialFields.makeTechName(field.name));
 231            }
 232        }
 233        createMetricTable += " )";
 234
 235        rc = query.exec(createMetricTable);
 236        //if (!rc) qDebug()<<"create table failed!"  << query.lastError();
 237
 238        // add row to version database
 239        QString metadataXML =  QString(context->athlete->home->config().absolutePath()) + "/metadata.xml";
 240        int metadatacrcnow = computeFileCRC(metadataXML);
 241        QDateTime timestamp = QDateTime::currentDateTime();
 242
 243        // wipe current version row
 244        query.exec("DELETE FROM version where table_name = \"metrics\"");
 245
 246        query.prepare("INSERT INTO version (table_name, schema_version, creation_date, metadata_crc ) values (?,?,?,?)");
 247        query.addBindValue("metrics");
 248	    query.addBindValue(DBSchemaVersion);
 249	    query.addBindValue(timestamp.toTime_t());
 250	    query.addBindValue(metadatacrcnow);
 251        rc = query.exec();
 252    }
 253    return rc;
 254}
 255
 256bool DBAccess::dropMetricTable()
 257{
 258    QSqlQuery query("DROP TABLE metrics", db->database(sessionid));
 259    bool rc = query.exec();
 260    return rc;
 261}
 262
 263bool DBAccess::createIntervalMetricsTable()
 264{
 265    QSqlQuery query(db->database(sessionid));
 266    bool rc;
 267    bool createTables = true;
 268
 269    // does the table exist?
 270    rc = query.exec("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;");
 271    if (rc) {
 272        while (query.next()) {
 273
 274            QString table = query.value(0).toString();
 275            if (table == "interval_metrics") {
 276                createTables = false;
 277                break;
 278            }
 279        }
 280    }
 281
 282    // we need to create it!
 283    if (rc && createTables) {
 284        QString createIntervalMetricTable = "create table interval_metrics (identifier varchar,"
 285                                    "filename varchar,"
 286                                    "timestamp integer,"
 287                                    "crc integer,"
 288                                    "ride_date date,"
 289                                    "type varchar,"
 290                                    "groupName varchar,"
 291                                    "name varchar,"
 292                                    "start integer,"
 293                                    "stop integer,"
 294                                    "color varchar,"
 295                                    "fingerprint integer";
 296
 297        // Add columns for all the metric factory metrics
 298        const RideMetricFactory &factory = RideMetricFactory::instance();
 299        for (int i=0; i<factory.metricCount(); i++)
 300            createIntervalMetricTable += QString(", X%1 double").arg(factory.metricName(i));
 301
 302        // And all the metadata texts from ride of the intervals
 303        foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 304            if (!context->specialFields.isMetric(field.name) && (field.type < 3 || field.type == 7)) {
 305                createIntervalMetricTable += QString(", Z%1 varchar").arg(context->specialFields.makeTechName(field.name));
 306            }
 307        }
 308
 309        // And all the metadata metrics from ride of the intervals
 310        foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 311            if (!context->specialFields.isMetric(field.name) && (field.type == 3 || field.type == 4)) {
 312                createIntervalMetricTable += QString(", Z%1 double").arg(context->specialFields.makeTechName(field.name));
 313            }
 314        }
 315        createIntervalMetricTable += " )";
 316
 317        rc = query.exec(createIntervalMetricTable);
 318        if (!rc) qDebug()<<"create table interval_metrics failed!"  << query.lastError();
 319
 320        // add row to version database
 321        QString metadataXML =  QString(context->athlete->home->config().absolutePath()) + "/metadata.xml";
 322        int metadatacrcnow = computeFileCRC(metadataXML);
 323        QDateTime timestamp = QDateTime::currentDateTime();
 324
 325        // wipe current version row
 326        query.exec("DELETE FROM version where table_name = \"interval_metrics\"");
 327
 328        query.prepare("INSERT INTO version (table_name, schema_version, creation_date, metadata_crc ) values (?,?,?,?)");
 329        query.addBindValue("interval_metrics");
 330        query.addBindValue(DBSchemaVersion);
 331        query.addBindValue(timestamp.toTime_t());
 332        query.addBindValue(metadatacrcnow);
 333        rc = query.exec();
 334    }
 335    return rc;
 336}
 337
 338bool DBAccess::dropIntervalMetricTable()
 339{
 340    QSqlQuery query("DROP TABLE interval_metrics", db->database(sessionid));
 341    bool rc = query.exec();
 342    return rc;
 343}
 344
 345bool DBAccess::createMeasuresTable()
 346{
 347    QSqlQuery query(db->database(sessionid));
 348    bool rc;
 349    bool createTables = true;
 350
 351    // does the table exist?
 352    rc = query.exec("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;");
 353    if (rc) {
 354        while (query.next()) {
 355
 356            QString table = query.value(0).toString();
 357            if (table == "measures") {
 358                createTables = false;
 359                break;
 360            }
 361        }
 362    }
 363    // we need to create it!
 364    if (rc && createTables) {
 365
 366        QString createMeasuresTable = "create table measures (timestamp integer primary key,"
 367                                      "measure_date date";
 368
 369        // And all the metadata texts
 370        foreach(FieldDefinition field, mfieldDefinitions)
 371            if (field.type < 3 || field.type == 7) createMeasuresTable += QString(", Z%1 varchar").arg(context->specialFields.makeTechName(field.name));
 372
 373        // And all the metadata measures
 374        foreach(FieldDefinition field, mfieldDefinitions)
 375            if (field.type == 3 || field.type == 4)
 376                createMeasuresTable += QString(", Z%1 double").arg(context->specialFields.makeTechName(field.name));
 377
 378        createMeasuresTable += " )";
 379
 380        rc = query.exec(createMeasuresTable);
 381        //if (!rc) qDebug()<<"create table failed!"  << query.lastError();
 382
 383        // add row to version database
 384        //QString measuresXML =  QString(home.absolutePath()) + "/measures.xml";
 385        int measurescrcnow = 0; //computeFileCRC(measuresXML); //no crc since we don't allow definition
 386        QDateTime timestamp = QDateTime::currentDateTime();
 387
 388        // wipe current version row
 389        rc = query.exec("DELETE FROM version where table_name = \"measures\";");
 390
 391        // add row to version table
 392        query.prepare("INSERT INTO version (table_name, schema_version, creation_date, metadata_crc ) values (?,?,?,?)");
 393        query.addBindValue("measures");
 394	    query.addBindValue(DBSchemaVersion);
 395	    query.addBindValue(timestamp.toTime_t());
 396	    query.addBindValue(measurescrcnow);
 397        rc = query.exec();
 398    }
 399    return rc;
 400}
 401
 402bool DBAccess::dropMeasuresTable()
 403{
 404    QSqlQuery query("DROP TABLE measures", db->database(sessionid));
 405    bool rc = query.exec();
 406    return rc;
 407}
 408
 409bool DBAccess::createDatabase()
 410{
 411    // check schema version and if missing recreate database
 412    checkDBVersion();
 413
 414    // Ride metrics
 415	createMetricsTable();
 416
 417    // Athlete measures
 418    createMeasuresTable();
 419
 420    return true;
 421}
 422
 423void DBAccess::checkDBVersion()
 424{
 425
 426    // get a CRC for metadata.xml
 427    QString metadataXML =  QString(context->athlete->home->config().absolutePath()) + "/metadata.xml";
 428    int metadatacrcnow = computeFileCRC(metadataXML);
 429
 430    // get a CRC for measures.xml
 431    //QString measuresXML =  QString(home.absolutePath()) + "/measures.xml";
 432    int measurescrcnow = 0; //computeFileCRC(measuresXML);// we don't allow user to edit
 433
 434    // can we get a version number?
 435    QSqlQuery query("SELECT table_name, schema_version, creation_date, metadata_crc from version;", db->database(sessionid));
 436
 437    bool rc = query.exec();
 438
 439    if (!rc) {
 440        // we couldn't read the version table properly
 441        // it must be out of date!!
 442
 443        QSqlQuery dropM("DROP TABLE version", db->database(sessionid));
 444        dropM.exec();
 445
 446        // recreate version table and add one entry
 447        QSqlQuery version("CREATE TABLE version ( table_name varchar primary key, schema_version integer, creation_date date, metadata_crc integer );", db->database(sessionid));
 448        version.exec();
 449
 450        // wipe away whatever (if anything is there)
 451        dropMetricTable();
 452        dropIntervalMetricTable();
 453        dropMeasuresTable();
 454
 455        // create afresh
 456        createMetricsTable();
 457        createIntervalMetricsTable();
 458        createMeasuresTable();
 459
 460        return;
 461    }
 462
 463    // ok we checked out ok, so lets adjust db schema to reflect
 464    // tne current version / crc
 465    bool dropMetric = true;
 466    bool dropIntervalMetric = true;
 467    bool dropMeasures = true;
 468
 469    while (query.next()) {
 470
 471        QString table_name = query.value(0).toString();
 472        int currentversion = query.value(1).toInt();
 473        //int creationdate = query.value(2).toInt(); // not relevant anymore, we use version/crc
 474        int crc = query.value(3).toInt();
 475
 476        if (table_name == "metrics") {
 477            if (currentversion == DBSchemaVersion && crc == metadatacrcnow) {
 478                dropMetric = false;
 479            }
 480        }
 481
 482        if (table_name == "interval_metrics") {
 483            if (currentversion == DBSchemaVersion && crc == metadatacrcnow) {
 484                dropIntervalMetric = false;
 485            }
 486        }
 487
 488        if (table_name == "measures") {
 489            if (crc == measurescrcnow) {
 490                dropMeasures = false;
 491            }
 492        }
 493    }
 494    query.finish();
 495
 496
 497    // "metrics" table, is it up-to-date?
 498    if (dropMetric) {
 499        dropMetricTable();
 500        createMetricsTable();
 501    }
 502
 503    // "interval_metrics" table, is it up-to-date?
 504    if (dropIntervalMetric) {
 505        dropIntervalMetricTable();
 506        createIntervalMetricsTable();
 507    }
 508
 509    // "measures" table, is it up-to-date? - export - recreate - import ....
 510    // gets wiped away for now, will fix as part of v3.1
 511    if (dropMeasures) {
 512        dropMeasuresTable();
 513        createMeasuresTable();
 514    }
 515}
 516
 517int DBAccess::getDBVersion()
 518{
 519    int schema_version = -1;
 520    // can we get a version number?
 521    QSqlQuery query("SELECT schema_version from version;", db->database(sessionid));
 522
 523    bool rc = query.exec();
 524
 525    if (rc) {
 526        while (query.next()) {
 527            if (query.value(0).toInt() > schema_version)
 528                schema_version = query.value(0).toInt();
 529        }
 530    }
 531    query.finish();
 532    return schema_version;
 533}
 534
 535/*----------------------------------------------------------------------
 536 * CRUD routines for Metrics table
 537 *----------------------------------------------------------------------*/
 538bool DBAccess::importRide(SummaryMetrics *summaryMetrics, RideFile *ride, QColor color, unsigned long fingerprint, bool modify)
 539{
 540	QSqlQuery query(db->database(sessionid));
 541    QDateTime timestamp = QDateTime::currentDateTime();
 542
 543    if (modify) {
 544        // zap the current row
 545	    query.prepare("DELETE FROM metrics WHERE filename = ?;");
 546	    query.addBindValue(summaryMetrics->getFileName());
 547        query.exec();
 548    }
 549
 550    // construct an insert statement
 551    QString insertStatement = "insert into metrics ( filename, identifier, crc, timestamp, ride_date, isRun, present, color, fingerprint ";
 552    const RideMetricFactory &factory = RideMetricFactory::instance();
 553    for (int i=0; i<factory.metricCount(); i++)
 554        insertStatement += QString(", X%1 ").arg(factory.metricName(i));
 555
 556    // And all the metadata texts
 557    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 558        if (!context->specialFields.isMetric(field.name) && (field.type < 3 || field.type == 7)) {
 559            insertStatement += QString(", Z%1 ").arg(context->specialFields.makeTechName(field.name));
 560        }
 561    }
 562        // And all the metadata metrics
 563    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 564        if (!context->specialFields.isMetric(field.name) && (field.type == 3 || field.type == 4)) {
 565            insertStatement += QString(", Z%1 ").arg(context->specialFields.makeTechName(field.name));
 566        }
 567    }
 568
 569    insertStatement += " ) values (?,?,?,?,?,?,?,?,?"; // filename, identifier, crc, timestamp, ride_date, isRun, present, color, fingerprint
 570    for (int i=0; i<factory.metricCount(); i++)
 571        insertStatement += ",?";
 572    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 573        if (!context->specialFields.isMetric(field.name) && (field.type < 5 || field.type == 7)) {
 574            insertStatement += ",?";
 575        }
 576    }
 577    insertStatement += ")";
 578
 579	query.prepare(insertStatement);
 580
 581    // filename, crc, timestamp, ride date
 582    QString fullPath =  QString(context->athlete->home->activities().absolutePath()) + "/" + summaryMetrics->getFileName();
 583	query.addBindValue(summaryMetrics->getFileName());
 584	query.addBindValue(summaryMetrics->getId());
 585	query.addBindValue((int)computeFileCRC(fullPath));
 586	query.addBindValue(timestamp.toTime_t());
 587    query.addBindValue(summaryMetrics->getRideDate());
 588    query.addBindValue(summaryMetrics->isRun());
 589    query.addBindValue(ride->getTag("Data", ""));
 590    query.addBindValue(color.name());
 591    query.addBindValue((int)fingerprint);
 592
 593    // values
 594    for (int i=0; i<factory.metricCount(); i++) {
 595	    query.addBindValue(summaryMetrics->getForSymbol(factory.metricName(i)));
 596    }
 597
 598    // And all the metadata texts
 599    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 600
 601        if (!context->specialFields.isMetric(field.name) && (field.type < 3 || field.type ==7)) {
 602            query.addBindValue(ride->getTag(field.name, ""));
 603        }
 604    }
 605    // And all the metadata metrics
 606    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 607
 608        if (!context->specialFields.isMetric(field.name) && (field.type == 3 || field.type == 4)) {
 609            query.addBindValue(ride->getTag(field.name, "0.0").toDouble());
 610        } else if (!context->specialFields.isMetric(field.name)) {
 611            if (field.name == "Recording Interval") 
 612                query.addBindValue(ride->recIntSecs());
 613        }
 614    }
 615
 616    // go do it!
 617	bool rc = query.exec();
 618
 619	//if(!rc) qDebug() << query.lastError();
 620
 621	return rc;
 622}
 623
 624bool
 625DBAccess::deleteRide(QString name)
 626{
 627    QSqlQuery query(db->database(sessionid));
 628
 629    query.prepare("DELETE FROM metrics WHERE filename = ?;");
 630    query.addBindValue(name);
 631    return query.exec();
 632}
 633
 634QList<QDateTime> DBAccess::getAllDates()
 635{
 636    QSqlQuery query("SELECT ride_date from metrics ORDER BY ride_date;", db->database(sessionid));
 637    QList<QDateTime> dates;
 638
 639    query.exec();
 640    while(query.next())
 641    {
 642        QDateTime date = query.value(0).toDateTime();
 643        dates << date;
 644    }
 645    return dates;
 646}
 647
 648bool
 649DBAccess::getRide(QString filename, SummaryMetrics &summaryMetrics, QColor&color)
 650{
 651    // lookup a ride by filename returning true/false if found
 652    bool found = false;
 653
 654    // construct the select statement
 655    QString selectStatement = "SELECT filename, identifier, ride_date, isRun, color";
 656    const RideMetricFactory &factory = RideMetricFactory::instance();
 657    for (int i=0; i<factory.metricCount(); i++)
 658        selectStatement += QString(", X%1 ").arg(factory.metricName(i));
 659    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 660        if (!context->specialFields.isMetric(field.name) && (field.type < 5 || field.type == 7)) {
 661            selectStatement += QString(", Z%1 ").arg(context->specialFields.makeTechName(field.name));
 662        }
 663    }
 664    selectStatement += " FROM metrics where filename = :name;";
 665
 666    // execute the select statement
 667    QSqlQuery query(selectStatement, db->database(sessionid));
 668    query.bindValue(":start", filename);
 669    query.exec();
 670
 671    while(query.next())
 672    {
 673        found = true;
 674
 675        // filename and date
 676        summaryMetrics.setFileName(query.value(0).toString());
 677        summaryMetrics.setId(query.value(1).toString());
 678        summaryMetrics.setRideDate(query.value(2).toDateTime());
 679        summaryMetrics.setIsRun(query.value(3).toInt());
 680        color = QColor(query.value(4).toString());
 681
 682        // the values
 683        int i=0;
 684        for (; i<factory.metricCount(); i++)
 685            summaryMetrics.setForSymbol(factory.metricName(i), query.value(i+5).toDouble());
 686
 687        foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 688            if (!context->specialFields.isMetric(field.name) && (field.type == 3 || field.type == 4)) {
 689                QString underscored = field.name;
 690                summaryMetrics.setForSymbol(underscored.replace("_"," "), query.value(i+4).toDouble());
 691                i++;
 692            } else if (!context->specialFields.isMetric(field.name) && field.type < 3) {
 693                QString underscored = field.name;
 694                summaryMetrics.setText(underscored.replace("_"," "), query.value(i+4).toString());
 695                i++;
 696            }
 697        }
 698    }
 699    query.finish();
 700    return found;
 701}
 702
 703QList<QString> DBAccess::getDistinctValues(FieldDefinition field)
 704{
 705    QStringList returning;
 706
 707    // what are we querying?
 708    QString fieldname = QString("Z%1").arg(context->specialFields.makeTechName(field.name));
 709
 710    QString selectStatement = QString("SELECT DISTINCT(%1) FROM METRICS ORDER BY %1;").arg(fieldname);
 711
 712    QSqlQuery query(db->database(sessionid));
 713    query.prepare(selectStatement);
 714    query.exec();
 715
 716    while(query.next()) {
 717        returning << query.value(0).toString();
 718    }
 719    return returning;
 720}
 721
 722QList<SummaryMetrics> DBAccess::getAllMetricsFor(QDateTime start, QDateTime end)
 723{
 724    QList<SummaryMetrics> metrics;
 725
 726    // null date range fetches all, but not currently used by application code
 727    // since it relies too heavily on the results of the QDateTime constructor
 728    if (start == QDateTime()) start = QDateTime::currentDateTime().addYears(-10);
 729    if (end == QDateTime()) end = QDateTime::currentDateTime().addYears(+10);
 730
 731    // construct the select statement
 732    QString selectStatement = "SELECT filename, identifier, ride_date, isRun";
 733    const RideMetricFactory &factory = RideMetricFactory::instance();
 734    for (int i=0; i<factory.metricCount(); i++)
 735        selectStatement += QString(", X%1 ").arg(factory.metricName(i));
 736    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 737        if (!context->specialFields.isMetric(field.name) && (field.type < 5 || field.type == 7)) {
 738            selectStatement += QString(", Z%1 ").arg(context->specialFields.makeTechName(field.name));
 739        }
 740    }
 741    selectStatement += " FROM metrics where DATE(ride_date) >=DATE(:start) AND DATE(ride_date) <=DATE(:end) "
 742                       " ORDER BY ride_date;";
 743
 744    // execute the select statement
 745    QSqlQuery query(db->database(sessionid));
 746    query.prepare(selectStatement);
 747    query.bindValue(":start", start.date());
 748    query.bindValue(":end", end.date());
 749    query.exec();
 750
 751    while(query.next())
 752    {
 753        SummaryMetrics summaryMetrics;
 754
 755        // filename and date
 756        summaryMetrics.setFileName(query.value(0).toString());
 757        summaryMetrics.setId(query.value(1).toString());
 758        summaryMetrics.setRideDate(query.value(2).toDateTime());
 759        summaryMetrics.setIsRun(query.value(3).toInt());
 760        // the values
 761        int i=0;
 762        for (; i<factory.metricCount(); i++)
 763            summaryMetrics.setForSymbol(factory.metricName(i), query.value(i+4).toDouble());
 764        foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 765            if (!context->specialFields.isMetric(field.name) && (field.type == 3 || field.type == 4)) {
 766                QString underscored = field.name;
 767                summaryMetrics.setForSymbol(underscored.replace("_"," "), query.value(i+4).toDouble());
 768                i++;
 769            } else if (!context->specialFields.isMetric(field.name) && (field.type < 3 || field.type == 7)) {
 770                QString underscored = field.name;
 771                summaryMetrics.setText(underscored.replace("_"," "), query.value(i+4).toString());
 772                i++;
 773            }
 774        }
 775        metrics << summaryMetrics;
 776    }
 777    return metrics;
 778}
 779
 780SummaryMetrics DBAccess::getRideMetrics(QString filename)
 781{
 782    SummaryMetrics summaryMetrics;
 783
 784    // construct the select statement
 785    QString selectStatement = "SELECT filename, identifier, isRun";
 786    const RideMetricFactory &factory = RideMetricFactory::instance();
 787    for (int i=0; i<factory.metricCount(); i++)
 788        selectStatement += QString(", X%1 ").arg(factory.metricName(i));
 789    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 790        if (!context->specialFields.isMetric(field.name) && (field.type < 5 || field.type == 7)) {
 791            selectStatement += QString(", Z%1 ").arg(context->specialFields.makeTechName(field.name));
 792        }
 793    }
 794    selectStatement += " FROM metrics WHERE filename = :filename ;";
 795
 796    // execute the select statement
 797    QSqlQuery query(db->database(sessionid));
 798    query.prepare(selectStatement);
 799    query.bindValue(":filename", QVariant(filename));
 800
 801    query.exec();
 802    while(query.next())
 803    {
 804        // filename and date
 805        summaryMetrics.setFileName(query.value(0).toString());
 806        summaryMetrics.setId(query.value(1).toString());
 807        summaryMetrics.setIsRun(query.value(2).toInt());
 808        // the values
 809        int i=0;
 810        for (; i<factory.metricCount(); i++)
 811            summaryMetrics.setForSymbol(factory.metricName(i), query.value(i+3).toDouble());
 812        foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 813            if (!context->specialFields.isMetric(field.name) && (field.type == 3 || field.type == 4)) {
 814                QString underscored = field.name;
 815                summaryMetrics.setForSymbol(underscored.replace(" ","_"), query.value(i+3).toDouble());
 816                i++;
 817            } else if (!context->specialFields.isMetric(field.name) && (field.type < 3 || field.type == 7)) {
 818                QString underscored = field.name;
 819                summaryMetrics.setText(underscored.replace("_"," "), query.value(i+3).toString());
 820                i++;
 821            }
 822        }
 823    }
 824    return summaryMetrics;
 825}
 826
 827/*----------------------------------------------------------------------
 828 * CRUD routines for Measures table
 829 *----------------------------------------------------------------------*/
 830bool DBAccess::importMeasure(SummaryMetrics *summaryMetrics)
 831{
 832	QSqlQuery query(db->database(sessionid));
 833
 834    // construct an insert statement
 835    QString insertStatement = "insert into measures (timestamp, measure_date";
 836
 837    // And all the metadata texts
 838    foreach(FieldDefinition field, mfieldDefinitions) {
 839        if (field.type < 3 || field.type == 7) {
 840            insertStatement += QString(", Z%1 ").arg(msp.makeTechName(field.name));
 841        }
 842    }
 843        // And all the metadata metrics
 844    foreach(FieldDefinition field, mfieldDefinitions) {
 845        if (field.type == 3 || field.type == 4) {
 846            insertStatement += QString(", Z%1 ").arg(msp.makeTechName(field.name));
 847        }
 848    }
 849
 850    insertStatement += " ) values (?,?"; // timestamp, measure_date
 851
 852    foreach(FieldDefinition field, mfieldDefinitions) {
 853        if (field.type < 5 || field.type == 7) {
 854            insertStatement += ",?";
 855        }
 856    }
 857    insertStatement += ")";
 858
 859	query.prepare(insertStatement);
 860
 861    // timestamp and date
 862    query.addBindValue(summaryMetrics->getDateTime().toTime_t());
 863    query.addBindValue(summaryMetrics->getDateTime().date());
 864
 865    // And all the text measures
 866    foreach(FieldDefinition field, mfieldDefinitions) {
 867        if (field.type < 3 || field.type == 7) {
 868            query.addBindValue(summaryMetrics->getText(field.name, ""));
 869        }
 870    }
 871    // And all the numeric measures
 872    foreach(FieldDefinition field, mfieldDefinitions) {
 873        if (field.type == 3 || field.type == 4) {
 874            query.addBindValue(summaryMetrics->getText(field.name, "nan").toDouble());
 875        }
 876    }
 877    // go do it!
 878	bool rc = query.exec();
 879
 880    //if(!rc) qDebug() << query.lastError();
 881
 882	return rc;
 883}
 884
 885QList<SummaryMetrics> DBAccess::getAllMeasuresFor(QDateTime start, QDateTime end)
 886{
 887    QList<SummaryMetrics> measures;
 888
 889    // null date range fetches all, but not currently used by application code
 890    // since it relies too heavily on the results of the QDateTime constructor
 891    if (start == QDateTime()) start = QDateTime::currentDateTime().addYears(-10);
 892    if (end == QDateTime()) end = QDateTime::currentDateTime().addYears(+10);
 893
 894    // construct the select statement
 895    QString selectStatement = "SELECT timestamp, measure_date";
 896    foreach(FieldDefinition field, mfieldDefinitions) {
 897        if (!context->specialFields.isMetric(field.name) && (field.type < 5 || field.type == 7)) {
 898            selectStatement += QString(", Z%1 ").arg(context->specialFields.makeTechName(field.name));
 899        }
 900    }
 901    selectStatement += " FROM measures where DATE(measure_date) >=DATE(:start) AND DATE(measure_date) <=DATE(:end) "
 902                       " ORDER BY measure_date;";
 903
 904    // execute the select statement
 905    QSqlQuery query(selectStatement, db->database(sessionid));
 906    query.prepare(selectStatement);
 907    query.bindValue(":start", start.date());
 908    query.bindValue(":end", end.date());
 909    query.exec();
 910    while(query.next())
 911    {
 912        SummaryMetrics add;
 913
 914        // filename and date
 915        add.setDateTime(query.value(1).toDateTime());
 916        // the values
 917        int i=2;
 918        foreach(FieldDefinition field, mfieldDefinitions) {
 919            QString symbol = QString("%1_m").arg(field.name);
 920            if (field.type == 3 || field.type == 4) {
 921                add.setText(symbol, query.value(i).toString());
 922                i++;
 923            } else if (field.type < 3 || field.type == 7) {
 924                add.setText(symbol, query.value(i).toString());
 925                i++;
 926            }
 927        }
 928        measures << add;
 929    }
 930    return measures;
 931}
 932
 933/*----------------------------------------------------------------------
 934 * CRUD routines for Interval Metrics table
 935 *----------------------------------------------------------------------*/
 936bool DBAccess::importInterval(SummaryMetrics *summaryMetrics, IntervalItem *interval, QString type, QString groupName, QColor color, unsigned long fingerprint, bool modify)
 937{
 938    QSqlQuery query(db->database(sessionid));
 939    QDateTime timestamp = QDateTime::currentDateTime();
 940
 941    if (modify) {
 942        // zap the current row
 943        query.prepare("DELETE FROM interval_metrics WHERE filename = :filename AND type = :type AND groupName = :groupName AND start = :start;");
 944
 945        query.bindValue(":filename", summaryMetrics->getFileName());
 946        query.bindValue(":type", type);
 947        query.bindValue(":groupName", groupName);
 948        query.bindValue(":start", interval->start);
 949        query.exec();
 950    }
 951
 952    // construct an insert statement
 953    QString insertStatement = "insert into interval_metrics ( identifier, filename, crc, timestamp, ride_date, type, groupName, name, start, stop, color, fingerprint ";
 954
 955    const RideMetricFactory &factory = RideMetricFactory::instance();
 956    for (int i=0; i<factory.metricCount(); i++)
 957        insertStatement += QString(", X%1 ").arg(factory.metricName(i));
 958
 959    // And all the metadata texts
 960    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 961        if (!context->specialFields.isMetric(field.name) && (field.type < 3 || field.type == 7)) {
 962            insertStatement += QString(", Z%1 ").arg(context->specialFields.makeTechName(field.name));
 963        }
 964    }
 965        // And all the metadata metrics
 966    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 967        if (!context->specialFields.isMetric(field.name) && (field.type == 3 || field.type == 4)) {
 968            insertStatement += QString(", Z%1 ").arg(context->specialFields.makeTechName(field.name));
 969        }
 970    }
 971
 972
 973    insertStatement += " ) values (?,?,?,?,?,?,?,?,?,?,?,?"; // identifier, filename, crc, timestamp, ride_date, type, groupName, name, start, stop, color, fingerprint
 974
 975    for (int i=0; i<factory.metricCount(); i++)
 976        insertStatement += ",?";
 977    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
 978        if (!context->specialFields.isMetric(field.name) && (field.type < 5 || field.type == 7)) {
 979            insertStatement += ",?";
 980        }
 981    }
 982    insertStatement += ");";
 983
 984    query.prepare(insertStatement);
 985
 986    // filename, crc, timestamp, ride date
 987    QString fullPath =  QString(context->athlete->home->activities().absolutePath()) + "/" + summaryMetrics->getFileName();
 988    query.addBindValue(summaryMetrics->getFileName()+"-"+interval->displaySequence);
 989    query.addBindValue(summaryMetrics->getFileName());
 990    query.addBindValue((int)computeFileCRC(fullPath));
 991    query.addBindValue(timestamp.toTime_t());
 992    query.addBindValue(summaryMetrics->getRideDate());
 993    query.addBindValue(type); // type
 994    query.addBindValue(groupName); // groupName,
 995    query.addBindValue(interval->text(0)); // name,
 996    query.addBindValue(interval->start); // start,
 997    query.addBindValue(interval->stop); // stop,
 998    query.addBindValue(color.name());
 999    query.addBindValue((int)fingerprint);
1000
1001    // values
1002    for (int i=0; i<factory.metricCount(); i++) {
1003        query.addBindValue(summaryMetrics->getForSymbol(factory.metricName(i)));
1004    }
1005
1006    // And all the metadata texts
1007    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
1008
1009        if (!context->specialFields.isMetric(field.name) && (field.type < 3 || field.type ==7)) {
1010            query.addBindValue(interval->ride->getTag(field.name, ""));
1011        }
1012    }
1013    // And all the metadata metrics
1014    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
1015
1016        if (!context->specialFields.isMetric(field.name) && (field.type == 3 || field.type == 4)) {
1017            query.addBindValue(interval->ride->getTag(field.name, "0.0").toDouble());
1018        } else if (!context->specialFields.isMetric(field.name)) {
1019            if (field.name == "Recording Interval")
1020                query.addBindValue(interval->ride->recIntSecs());
1021        }
1022    }
1023
1024    // go do it!
1025    bool rc = query.exec();
1026
1027    if(!rc) qDebug() << query.lastError();
1028
1029    return rc;
1030}
1031
1032bool
1033DBAccess::deleteIntervalsForRide(QString filename)
1034{
1035    QSqlQuery query(db->database(sessionid));
1036
1037    query.prepare("DELETE FROM interval_metrics WHERE filename = ?;");
1038    query.addBindValue(filename);
1039    return query.exec();
1040}
1041
1042bool
1043DBAccess::deleteIntervalsForTypeAndGroupName(QString type, QString groupName)
1044{
1045    QSqlQuery query(db->database(sessionid));
1046
1047    query.prepare("DELETE FROM interval_metrics WHERE type = :type AND groupName = :groupName;");
1048    query.addBindValue(type); // type
1049    query.addBindValue(groupName); // groupName,
1050    return query.exec();
1051}
1052
1053bool
1054DBAccess::getInterval(QString filename, QString type, QString groupName, int start, SummaryMetrics &summaryMetrics, QColor&color)
1055{
1056    // lookup a ride by filename returning true/false if found
1057    bool found = false;
1058
1059    // construct the select statement
1060    QString selectStatement = "SELECT identifier, filename, crc, type, groupName, color"; //identifier, filename, crc, timestamp, ride_date, type, groupName, name, start, stop
1061
1062    const RideMetricFactory &factory = RideMetricFactory::instance();
1063    for (int i=0; i<factory.metricCount(); i++)
1064        selectStatement += QString(", X%1 ").arg(factory.metricName(i));
1065    foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
1066        if (!context->specialFields.isMetric(field.name) && (field.type < 5 || field.type == 7)) {
1067            selectStatement += QString(", Z%1 ").arg(context->specialFields.makeTechName(field.name));
1068        }
1069    }
1070    selectStatement += " FROM interval_metrics where filename = :filename AND type = :type AND groupName = :groupName AND start = :start;";
1071
1072    // execute the select statement
1073    QSqlQuery query(selectStatement, db->database(sessionid));
1074    query.bindValue(":filename", filename);
1075    query.bindValue(":type", type);
1076    query.bindValue(":groupName", groupName);
1077    query.bindValue(":start", start);
1078    query.exec();
1079
1080    while(query.next())
1081    {
1082        found = true;
1083
1084        // filename and date
1085        summaryMetrics.setFileName(query.value(0).toString());
1086        summaryMetrics.setId(query.value(1).toString());
1087        summaryMetrics.setRideDate(query.value(2).toDateTime());
1088        summaryMetrics.setIsRun(query.value(3).toInt());
1089        color = QColor(query.value(4).toString());
1090
1091        // the values
1092        int i=0;
1093        for (; i<factory.metricCount(); i++)
1094            summaryMetrics.setForSymbol(factory.metricName(i), query.value(i+5).toDouble());
1095
1096        foreach(FieldDefinition field, context->athlete->rideMetadata()->getFields()) {
1097            if (!context->specialFields.isMetric(field.name) && (field.type == 3 || field.type == 4)) {
1098                QString underscored = field.name;
1099                summaryMetrics.setForSymbol(underscored.replace("_"," "), query.value(i+4).toDouble());
1100                i++;
1101            } else if (!context->specialFields.isMetric(field.name) && field.type < 3) {
1102                QString underscored = field.name;
1103                summaryMetrics.setText(underscored.replace("_"," "), query.value(i+4).toString());
1104                i++;
1105            }
1106        }
1107    }
1108    query.finish();
1109    return found;
1110}
1111