+++ /dev/null
-/*
- * Copyright (C) 2011, Jamie Thompson
- *
- * This program 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.
- *
- * This program 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 this program; If not, see
- * <http://www.gnu.org/licenses/>.
- */
-
-#include <QDebug>
-#include <QDir>
-#include <QPair>
-#include <QStringList>
-#include <QtSql/QSqlDatabase>
-#include <QtSql/QSqlQuery>
-#include <QVariant>
-
-#include <stdexcept>
-
-#include "EventLogReindexer.h"
-
-#define DB_LOC "/.rtcom-eventlogger/el-v1.db"
-
-EventLogReindexer::EventLogReindexer()
-{
-}
-
-void EventLogReindexer::Reindex()
-{
- // Set up the database connection...
- QSqlDatabase db(QSqlDatabase::addDatabase( "QSQLITE" ));
-
- db.setDatabaseName( QDir::homePath() + DB_LOC );
- if ( ! db.open() )
- {
- throw std::runtime_error("Cannot open database: Unable to establish database connection");
- }
- else
- {
- // Reorder the evnts by their start time
- uint changesRequired(0);
- do
- {
- // Note the smallest event ID found, so we have a place to start.
- int min(0);
-
- // The required ID changes ( current, correct );
- QHash<int, int> mapping;
-
- // Grab the current records, and determine what changes need to
- // happen to get to the sorted results
- {
- qDebug() << "DB Opened";
-
- QSqlQuery * dbq1(new QSqlQuery( db )), * dbq2(new QSqlQuery( db ));
-
- dbq1->setForwardOnly( true );
- dbq2->setForwardOnly( true );
-
- QString s1("SELECT id, event_type_id, start_time, end_time "
- " FROM Events");
- QString s2("SELECT id, event_type_id, start_time, end_time "
- " FROM Events ORDER BY start_time ASC");
-
- if ( dbq1->exec( s1 ) && dbq2->exec( s2 ))
- {
- qDebug() << "Query OK, " << dbq1->numRowsAffected() << " & " << dbq2->numRowsAffected() << " rows affected.";
-
- while( dbq1->next() && dbq2->next())
- {
- int one (dbq1->value( 0 ).value< int >());
- int two (dbq2->value( 0 ).value< int >());
- //uint startTime( m_dbq->value( 1 ).value< uint >() );
- //uint endTime( m_dbq->value( 2 ).value< uint >() );
-
- //qDebug() << "Event: " << type << ", " << startTime << ", " << endTime << "";
- //qDebug() << "( " << one << ", " << two << " )";
-
- if(two != one)
- {
- if(min == 0)
- min = one;
-
- qDebug() << "( " << one << ", " << two << " )";
- mapping.insert(one, two);
- }
- }
- }
- else
- {
- qDebug() << "SQL EXEC Error: "<< "EXEC query failed";
- qDebug() << "Query1: " << s1;
- qDebug() << "Query2: " << s1;
- }
-
- // Clear up database connections
- if ( dbq1 != NULL )
- {
- qDebug() << "Cleaning up connection 1";
-
- dbq1->finish();
-
- delete dbq1;
- dbq1 = NULL;
- }
-
- if ( dbq2 != NULL )
- {
- qDebug() << "Cleaning up connection 2";
-
- dbq2->finish();
-
- delete dbq2;
- dbq2 = NULL;
- }
- }
-
- QList<int> sequence;
- int val(min);
- sequence.append(0);
- sequence.append(val);
- qDebug().nospace() << "val1: " << val << ", ";
-
- while((val = mapping[val]) && val != min)
- {
- sequence.append(val);
- qDebug().nospace() << val << ", ";
- }
- sequence.append(0);
-
- qDebug().nospace() << "seq: ";
- QList<QPair<int,int> > updates;
- int last(sequence.first());
- foreach(int seq, sequence)
- {
- if(seq != last)
- {
- qDebug().nospace() << seq << ", " << last << ", ";
- updates.append(QPair<int,int>(seq, last));
- }
-
- last = seq;
- }
-
- // Used to keep iterating until no changes are required.
- // TODO: Shouldn't be required, but is. One to revisit later.
- changesRequired = updates.count();
-
- for( QList<QPair<int,int> >::const_iterator it(updates.constBegin()); it != updates.constEnd(); ++it)
- {
- //qDebug().nospace() << (*it).first << ", " << (*it).second;
- }
-
- QList<QString> tables = QList<QString>() << "Events" << "Attachments" << "Headers" << "GroupCache";
- QString query;
- for( QList<QString>::const_iterator currentTable(tables.constBegin()); currentTable != tables.constEnd(); ++currentTable)
- {
- QString curquery = "UPDATE %3 set %4 = %1 WHERE %4 = %2;";
- for( QList<QPair<int,int> >::const_iterator currentUpdate(updates.constBegin()); currentUpdate != updates.constEnd(); ++currentUpdate)
- {
- query.append(
- curquery
- .arg((*currentUpdate).second)
- .arg((*currentUpdate).first)
- .arg((*currentTable))
- .arg((*currentTable) == "Events" ? "id" : "event_id")
- ).append("\n");
-
- //qDebug().nospace() << (*it).first << ", " << (*it).second;
- }
- }
-
- qDebug() << query;
-
- QSqlQuery * UpdateQuery(new QSqlQuery( db ));
- if(UpdateQuery != NULL)
- {
- UpdateQuery->setForwardOnly( true );
-
- if(db.transaction())
- {
- QStringList statements = query.trimmed().split(";", QString::SkipEmptyParts);
- try
- {
- for( QStringList::const_iterator currentStatement(statements.constBegin()); currentStatement != statements.constEnd(); ++currentStatement)
- {
- if ( UpdateQuery->exec( *currentStatement ))
- qDebug() << "Query OK, " << UpdateQuery->numRowsAffected() << " rows affected.";
- else
- {
- qDebug() << "Query Failed: " << *currentStatement;
- throw std::exception();
- }
- }
-
- qDebug() << "Committing.";
- db.commit();
- }
- catch(...)
- {
- qDebug() << "Rolling back.";
- db.rollback();
- }
- }
- else
- qDebug() << "Unable to start transaction.";
- }
- }while(changesRequired > 0);
-
- // Update the group cache so the last events are correct
- {
- qDebug() << "Updating most recent events.";
-
- // Grab group UIDs from group cache
- QSqlQuery * dbq(new QSqlQuery( db ));
- dbq->setForwardOnly( true );
-
- const char * groupUIDListSQL("SELECT group_uid FROM GroupCache");
- if (dbq->exec(groupUIDListSQL))
- {
- qDebug() << "Query OK, " << dbq->numRowsAffected() << " rows affected.";
- qDebug() << "GroupUIDs:";
-
- QSet<QString> groupUIDs;
- while( dbq->next() )
- {
- QString groupUID(dbq->value(0).value<QString>());
-
- qDebug() << groupUID;
- groupUIDs.insert(groupUID);
- }
-
- // Iterate over group UIDS
- if(groupUIDs.count() > 0)
- {
- // Build a batch statement to update every group with
- // the most recent event
-
- // Ignore 'data' failures (i.e. no events but present in the
- // cache)- something else's been monkeying with the DB, and
- // we can't account for everything.
- QString updateGroupCacheWithLatestEventsSQL(
- "UPDATE OR IGNORE GroupCache SET event_id = "
- "(SELECT id FROM events WHERE group_uid = \"%1\" "
- " ORDER BY id DESC LIMIT 1)"
- " WHERE group_uid = \"%1\";");
- QString updateGroupCacheWithLatestEventsBatchSQL;
- foreach(QString groupUID, groupUIDs)
- {
- updateGroupCacheWithLatestEventsBatchSQL.append(
- updateGroupCacheWithLatestEventsSQL
- .arg(groupUID)
- ).append("\n");
- }
-
- // Execute the statement in single-statement chunks thanks
- // to QT's inability to call the SQLite function supporting
- // multiple statements
-
- QSqlQuery * setLatestEventInGroupCacheSQL(new QSqlQuery( db ));
- if(NULL != setLatestEventInGroupCacheSQL)
- {
- setLatestEventInGroupCacheSQL->setForwardOnly( true );
-
- if(db.transaction())
- {
- QStringList statements = updateGroupCacheWithLatestEventsBatchSQL.trimmed().split(";", QString::SkipEmptyParts);
- try
- {
- for( QStringList::const_iterator currentStatement(statements.constBegin()); currentStatement != statements.constEnd(); ++currentStatement)
- {
- if ( setLatestEventInGroupCacheSQL->exec( *currentStatement ))
- qDebug() << "Query OK, " << setLatestEventInGroupCacheSQL->numRowsAffected() << " rows affected.";
- else
- {
- qDebug() << "Query Failed: " << *currentStatement;
- throw std::exception();
- }
- }
-
- qDebug() << "Committing.";
- db.commit();
- }
- catch(...)
- {
- qDebug() << "Rolling back.";
- db.rollback();
- }
- }
- else
- qDebug() << "Unable to start transaction.";
- }
- }
- }
- else
- {
- qDebug() << "SQL EXEC Error: "<< "EXEC query failed";
- qDebug() << "Query: " << groupUIDListSQL;
- }
- }
-
- qDebug() << "Closing.";
- db.close();
- QSqlDatabase::removeDatabase( "QSQLITE" );
- }
-
- return;
-}