2 * Copyright (C) 2011, Jamie Thompson
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.
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.
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/>.
22 #include <QStringList>
23 #include <QtSql/QSqlDatabase>
24 #include <QtSql/QSqlQuery>
29 #include "EventLogReindexer.h"
31 #define DB_LOC "/.rtcom-eventlogger/el-v1.db"
33 EventLogReindexer::EventLogReindexer()
37 void EventLogReindexer::Reindex()
39 // Set up the database connection...
40 QSqlDatabase db(QSqlDatabase::addDatabase( "QSQLITE" ));
42 db.setDatabaseName( QDir::homePath() + DB_LOC );
45 throw std::runtime_error("Cannot open database: Unable to establish database connection");
49 // Reorder the evnts by their start time
50 uint changesRequired(0);
53 // Note the smallest event ID found, so we have a place to start.
56 // The required ID changes ( current, correct );
57 QHash<int, int> mapping;
59 // Grab the current records, and determine what changes need to
60 // happen to get to the sorted results
62 qDebug() << "DB Opened";
64 QSqlQuery * dbq1(new QSqlQuery( db )), * dbq2(new QSqlQuery( db ));
66 dbq1->setForwardOnly( true );
67 dbq2->setForwardOnly( true );
69 QString s1("SELECT id, event_type_id, start_time, end_time "
71 QString s2("SELECT id, event_type_id, start_time, end_time "
72 " FROM Events ORDER BY start_time ASC");
74 if ( dbq1->exec( s1 ) && dbq2->exec( s2 ))
76 qDebug() << "Query OK, " << dbq1->numRowsAffected() << " & " << dbq2->numRowsAffected() << " rows affected.";
78 while( dbq1->next() && dbq2->next())
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 >() );
85 //qDebug() << "Event: " << type << ", " << startTime << ", " << endTime << "";
86 //qDebug() << "( " << one << ", " << two << " )";
93 qDebug() << "( " << one << ", " << two << " )";
94 mapping.insert(one, two);
100 qDebug() << "SQL EXEC Error: "<< "EXEC query failed";
101 qDebug() << "Query1: " << s1;
102 qDebug() << "Query2: " << s1;
105 // Clear up database connections
108 qDebug() << "Cleaning up connection 1";
118 qDebug() << "Cleaning up connection 2";
130 sequence.append(val);
131 qDebug().nospace() << "val1: " << val << ", ";
133 while((val = mapping[val]) && val != min)
135 sequence.append(val);
136 qDebug().nospace() << val << ", ";
140 qDebug().nospace() << "seq: ";
141 QList<QPair<int,int> > updates;
142 int last(sequence.first());
143 foreach(int seq, sequence)
147 qDebug().nospace() << seq << ", " << last << ", ";
148 updates.append(QPair<int,int>(seq, last));
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();
158 for( QList<QPair<int,int> >::const_iterator it(updates.constBegin()); it != updates.constEnd(); ++it)
160 //qDebug().nospace() << (*it).first << ", " << (*it).second;
163 QList<QString> tables = QList<QString>() << "Events" << "Attachments" << "Headers" << "GroupCache";
165 for( QList<QString>::const_iterator currentTable(tables.constBegin()); currentTable != tables.constEnd(); ++currentTable)
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)
172 .arg((*currentUpdate).second)
173 .arg((*currentUpdate).first)
174 .arg((*currentTable))
175 .arg((*currentTable) == "Events" ? "id" : "event_id")
178 //qDebug().nospace() << (*it).first << ", " << (*it).second;
184 QSqlQuery * UpdateQuery(new QSqlQuery( db ));
185 if(UpdateQuery != NULL)
187 UpdateQuery->setForwardOnly( true );
191 QStringList statements = query.trimmed().split(";", QString::SkipEmptyParts);
194 for( QStringList::const_iterator currentStatement(statements.constBegin()); currentStatement != statements.constEnd(); ++currentStatement)
196 if ( UpdateQuery->exec( *currentStatement ))
197 qDebug() << "Query OK, " << UpdateQuery->numRowsAffected() << " rows affected.";
200 qDebug() << "Query Failed: " << *currentStatement;
201 throw std::exception();
205 qDebug() << "Committing.";
210 qDebug() << "Rolling back.";
215 qDebug() << "Unable to start transaction.";
217 }while(changesRequired > 0);
219 // Update the group cache so the last events are correct
221 qDebug() << "Updating most recent events.";
223 // Grab group UIDs from group cache
224 QSqlQuery * dbq(new QSqlQuery( db ));
225 dbq->setForwardOnly( true );
227 const char * groupUIDListSQL("SELECT group_uid FROM GroupCache");
228 if (dbq->exec(groupUIDListSQL))
230 qDebug() << "Query OK, " << dbq->numRowsAffected() << " rows affected.";
231 qDebug() << "GroupUIDs:";
233 QSet<QString> groupUIDs;
236 QString groupUID(dbq->value(0).value<QString>());
238 qDebug() << groupUID;
239 groupUIDs.insert(groupUID);
242 // Iterate over group UIDS
243 if(groupUIDs.count() > 0)
245 // Build a batch statement to update every group with
246 // the most recent event
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)
259 updateGroupCacheWithLatestEventsBatchSQL.append(
260 updateGroupCacheWithLatestEventsSQL
265 // Execute the statement in single-statement chunks thanks
266 // to QT's inability to call the SQLite function supporting
267 // multiple statements
269 QSqlQuery * setLatestEventInGroupCacheSQL(new QSqlQuery( db ));
270 if(NULL != setLatestEventInGroupCacheSQL)
272 setLatestEventInGroupCacheSQL->setForwardOnly( true );
276 QStringList statements = updateGroupCacheWithLatestEventsBatchSQL.trimmed().split(";", QString::SkipEmptyParts);
279 for( QStringList::const_iterator currentStatement(statements.constBegin()); currentStatement != statements.constEnd(); ++currentStatement)
281 if ( setLatestEventInGroupCacheSQL->exec( *currentStatement ))
282 qDebug() << "Query OK, " << setLatestEventInGroupCacheSQL->numRowsAffected() << " rows affected.";
285 qDebug() << "Query Failed: " << *currentStatement;
286 throw std::exception();
290 qDebug() << "Committing.";
295 qDebug() << "Rolling back.";
300 qDebug() << "Unable to start transaction.";
306 qDebug() << "SQL EXEC Error: "<< "EXEC query failed";
307 qDebug() << "Query: " << groupUIDListSQL;
311 qDebug() << "Closing.";
313 QSqlDatabase::removeDatabase( "QSQLITE" );