git.maemo.org Git - qwerkisync/blob - EventLogReindexer.cpp

   1 /*
   2  * Copyright (C) 2011, Jamie Thompson
   3  *
   4  * This program is free software; you can redistribute it and/or
   5  * modify it under the terms of the GNU General Public
   6  * License as published by the Free Software Foundation; either
   7  * version 3 of the License, or (at your option) any later version.
   8  *
   9  * This program is distributed in the hope that it will be useful,
  10  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  12  * General Public License for more details.
  13  *
  14  * You should have received a copy of the GNU General Public
  15  * License along with this program; If not, see
  16  * <http://www.gnu.org/licenses/>.
  17  */
  18
  19 #include <QDebug>
  20 #include <QDir>
  21 #include <QPair>
  22 #include <QStringList>
  23 #include <QtSql/QSqlDatabase>
  24 #include <QtSql/QSqlQuery>
  25 #include <QVariant>
  26
  27 #include <stdexcept>
  28
  29 #include "EventLogReindexer.h"
  30
  31 #define DB_LOC "/.rtcom-eventlogger/el-v1.db"
  32
  33 EventLogReindexer::EventLogReindexer()
  34 {
  35 }
  36
  37 void EventLogReindexer::Reindex()
  38 {
  39         // Set up the database connection...
  40         QSqlDatabase db(QSqlDatabase::addDatabase( "QSQLITE" ));
  41
  42         db.setDatabaseName( QDir::homePath() + DB_LOC );
  43         if ( ! db.open() )
  44         {
  45                 throw std::runtime_error("Cannot open database: Unable to establish database connection");
  46         }
  47         else
  48         {
  49                 // Reorder the evnts by their start time
  50                 uint changesRequired(0);
  51                 do
  52                 {
  53                         // Note the smallest event ID found, so we have a place to start.
  54                         int min(0);
  55
  56                         // The required ID changes ( current, correct );
  57                         QHash<int, int> mapping;
  58
  59                         // Grab the current records, and determine what changes need to
  60                         // happen to get to the sorted results
  61                         {
  62                                 qDebug() << "DB Opened";
  63
  64                                 QSqlQuery * dbq1(new QSqlQuery( db )), * dbq2(new QSqlQuery( db ));
  65
  66                                 dbq1->setForwardOnly( true );
  67                                 dbq2->setForwardOnly( true );
  68
  69                                 QString s1("SELECT id, event_type_id, start_time, end_time "
  70                                                    " FROM Events");
  71                                 QString s2("SELECT id, event_type_id, start_time, end_time "
  72                                                    " FROM Events ORDER BY start_time ASC");
  73
  74                                 if ( dbq1->exec( s1 ) && dbq2->exec( s2 ))
  75                                 {
  76                                         qDebug() << "Query OK, " << dbq1->numRowsAffected() << " & " << dbq2->numRowsAffected() << " rows affected.";
  77
  78                                         while( dbq1->next() && dbq2->next())
  79                                         {
  80                                                 int one (dbq1->value( 0 ).value< int >());
  81                                                 int two (dbq2->value( 0 ).value< int >());
  82                                                 //uint startTime( m_dbq->value( 1 ).value< uint >() );
  83                                                 //uint endTime( m_dbq->value( 2 ).value< uint >() );
  84
  85                                                 //qDebug() << "Event: " << type << ", " << startTime << ", " << endTime << "";
  86                                                 //qDebug() << "( " << one << ", " << two << " )";
  87
  88                                                 if(two != one)
  89                                                 {
  90                                                         if(min == 0)
  91                                                                 min = one;
  92
  93                                                         qDebug() << "( " << one << ", " << two << " )";
  94                                                         mapping.insert(one, two);
  95                                                 }
  96                                         }
  97                                 }
  98                                 else
  99                                 {
 100                                         qDebug() << "SQL EXEC Error: "<< "EXEC query failed";
 101                                         qDebug() << "Query1: " << s1;
 102                                         qDebug() << "Query2: " << s1;
 103                                 }
 104
 105                                 // Clear up database connections
 106                                 if ( dbq1 != NULL )
 107                                 {
 108                                         qDebug() << "Cleaning up connection 1";
 109
 110                                         dbq1->finish();
 111
 112                                         delete dbq1;
 113                                         dbq1 = NULL;
 114                                 }
 115
 116                                 if ( dbq2 != NULL )
 117                                 {
 118                                         qDebug() << "Cleaning up connection 2";
 119
 120                                         dbq2->finish();
 121
 122                                         delete dbq2;
 123                                         dbq2 = NULL;
 124                                 }
 125                         }
 126
 127                         QList<int> sequence;
 128                         int val(min);
 129                         sequence.append(0);
 130                         sequence.append(val);
 131                         qDebug().nospace() << "val1: " << val << ", ";
 132
 133                         while((val = mapping[val]) && val != min)
 134                         {
 135                                 sequence.append(val);
 136                                 qDebug().nospace() << val << ", ";
 137                         }
 138                         sequence.append(0);
 139
 140                         qDebug().nospace() << "seq: ";
 141                         QList<QPair<int,int> > updates;
 142                         int last(sequence.first());
 143                         foreach(int seq, sequence)
 144                         {
 145                                 if(seq != last)
 146                                 {
 147                                         qDebug().nospace() << seq << ", " << last << ", ";
 148                                         updates.append(QPair<int,int>(seq, last));
 149                                 }
 150
 151                                 last = seq;
 152                         }
 153
 154                         // Used to keep iterating until no changes are required.
 155                         // TODO: Shouldn't be required, but is. One to revisit later.
 156                         changesRequired = updates.count();
 157
 158                         for( QList<QPair<int,int> >::const_iterator it(updates.constBegin()); it != updates.constEnd(); ++it)
 159                         {
 160                                 //qDebug().nospace() << (*it).first << ", " << (*it).second;
 161                         }
 162
 163                         QList<QString> tables = QList<QString>() << "Events" << "Attachments" << "Headers" << "GroupCache";
 164                         QString query;
 165                         for( QList<QString>::const_iterator currentTable(tables.constBegin()); currentTable != tables.constEnd(); ++currentTable)
 166                         {
 167                                 QString curquery = "UPDATE %3 set %4 = %1 WHERE %4 = %2;";
 168                                 for( QList<QPair<int,int> >::const_iterator currentUpdate(updates.constBegin()); currentUpdate != updates.constEnd(); ++currentUpdate)
 169                                 {
 170                                         query.append(
 171                                                 curquery
 172                                                         .arg((*currentUpdate).second)
 173                                                         .arg((*currentUpdate).first)
 174                                                         .arg((*currentTable))
 175                                                         .arg((*currentTable) == "Events" ? "id" : "event_id")
 176                                                 ).append("\n");
 177
 178                                         //qDebug().nospace() << (*it).first << ", " << (*it).second;
 179                                 }
 180                         }
 181
 182                         qDebug() << query;
 183
 184                         QSqlQuery * UpdateQuery(new QSqlQuery( db ));
 185                         if(UpdateQuery != NULL)
 186                         {
 187                                 UpdateQuery->setForwardOnly( true );
 188
 189                                 if(db.transaction())
 190                                 {
 191                                         QStringList statements = query.trimmed().split(";", QString::SkipEmptyParts);
 192                                         try
 193                                         {
 194                                                 for( QStringList::const_iterator currentStatement(statements.constBegin()); currentStatement != statements.constEnd(); ++currentStatement)
 195                                                 {
 196                                                         if ( UpdateQuery->exec( *currentStatement ))
 197                                                                 qDebug() << "Query OK, " << UpdateQuery->numRowsAffected() << " rows affected.";
 198                                                         else
 199                                                         {
 200                                                                 qDebug() << "Query Failed: " << *currentStatement;
 201                                                                 throw std::exception();
 202                                                         }
 203                                                 }
 204
 205                                                 qDebug() << "Committing.";
 206                                                 db.commit();
 207                                         }
 208                                         catch(...)
 209                                         {
 210                                                 qDebug() << "Rolling back.";
 211                                                 db.rollback();
 212                                         }
 213                                 }
 214                                 else
 215                                         qDebug() << "Unable to start transaction.";
 216                         }
 217                 }while(changesRequired > 0);
 218
 219                 // Update the group cache so the last events are correct
 220                 {
 221                         qDebug() << "Updating most recent events.";
 222
 223                         // Grab group UIDs from group cache
 224                         QSqlQuery * dbq(new QSqlQuery( db ));
 225                         dbq->setForwardOnly( true );
 226
 227                         const char * groupUIDListSQL("SELECT group_uid FROM GroupCache");
 228                         if (dbq->exec(groupUIDListSQL))
 229                         {
 230                                 qDebug() << "Query OK, " << dbq->numRowsAffected() << " rows affected.";
 231                                 qDebug() << "GroupUIDs:";
 232
 233                                 QSet<QString> groupUIDs;
 234                                 while( dbq->next() )
 235                                 {
 236                                         QString groupUID(dbq->value(0).value<QString>());
 237
 238                                         qDebug() << groupUID;
 239                                         groupUIDs.insert(groupUID);
 240                                 }
 241
 242                                 // Iterate over group UIDS
 243                                 if(groupUIDs.count() > 0)
 244                                 {
 245                                         // Build a batch statement to update every group with
 246                                         // the most recent event
 247
 248                                         // Ignore 'data' failures (i.e. no events but present in the
 249                                         // cache)- something else's been monkeying with the DB, and
 250                                         // we can't account for everything.
 251                                         QString updateGroupCacheWithLatestEventsSQL(
 252                                                 "UPDATE OR IGNORE GroupCache SET event_id = "
 253                                                         "(SELECT id FROM events WHERE group_uid = \"%1\" "
 254                                                         " ORDER BY id DESC LIMIT 1)"
 255                                                 " WHERE group_uid = \"%1\";");
 256                                         QString updateGroupCacheWithLatestEventsBatchSQL;
 257                                         foreach(QString groupUID, groupUIDs)
 258                                         {
 259                                                 updateGroupCacheWithLatestEventsBatchSQL.append(
 260                                                         updateGroupCacheWithLatestEventsSQL
 261                                                         .arg(groupUID)
 262                                                         ).append("\n");
 263                                         }
 264
 265                                         // Execute the statement in single-statement chunks thanks
 266                                         // to QT's inability to call the SQLite function supporting
 267                                         // multiple statements
 268
 269                                         QSqlQuery * setLatestEventInGroupCacheSQL(new QSqlQuery( db ));
 270                                         if(NULL != setLatestEventInGroupCacheSQL)
 271                                         {
 272                                                 setLatestEventInGroupCacheSQL->setForwardOnly( true );
 273
 274                                                 if(db.transaction())
 275                                                 {
 276                                                         QStringList statements = updateGroupCacheWithLatestEventsBatchSQL.trimmed().split(";", QString::SkipEmptyParts);
 277                                                         try
 278                                                         {
 279                                                                 for( QStringList::const_iterator currentStatement(statements.constBegin()); currentStatement != statements.constEnd(); ++currentStatement)
 280                                                                 {
 281                                                                         if ( setLatestEventInGroupCacheSQL->exec( *currentStatement ))
 282                                                                                 qDebug() << "Query OK, " << setLatestEventInGroupCacheSQL->numRowsAffected() << " rows affected.";
 283                                                                         else
 284                                                                         {
 285                                                                                 qDebug() << "Query Failed: " << *currentStatement;
 286                                                                                 throw std::exception();
 287                                                                         }
 288                                                                 }
 289
 290                                                                 qDebug() << "Committing.";
 291                                                                 db.commit();
 292                                                         }
 293                                                         catch(...)
 294                                                         {
 295                                                                 qDebug() << "Rolling back.";
 296                                                                 db.rollback();
 297                                                         }
 298                                                 }
 299                                                 else
 300                                                         qDebug() << "Unable to start transaction.";
 301                                         }
 302                                 }
 303                         }
 304                         else
 305                         {
 306                                 qDebug() << "SQL EXEC Error: "<< "EXEC query failed";
 307                                 qDebug() << "Query: " << groupUIDListSQL;
 308                         }
 309                 }
 310
 311                 qDebug() << "Closing.";
 312                 db.close();
 313                 QSqlDatabase::removeDatabase( "QSQLITE" );
 314         }
 315
 316         return;
 317 }