vcs.maemo.org Git - qwerkisync/blob - DBBackends/RtcomEventLoggerComponents/TriggerDisabler.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 "TriggerDisabler.h"
  20
  21 #include "Settings.h"
  22
  23 #include <QtSql>
  24
  25 #include <sqlite3.h>
  26
  27 #include <stdexcept>
  28
  29 static void _sqlite3ORStep(sqlite3_context *context, int numArgs, sqlite3_value **value);
  30 static void _sqlite3ORFinal(sqlite3_context *context);
  31 bool createSQLiteFunctions(const QSqlDatabase &db);
  32
  33 using namespace DBBackends::RtcomEventLoggerComponents;
  34
  35 TriggerDisabler::TriggerDisabler(const Settings &settings)
  36         : m_Settings(settings), m_Reenabled(false)
  37 {
  38         // Preserve default cache triggers
  39         // Set up the database connection...
  40         QSqlDatabase db(QSqlDatabase::addDatabase( "QSQLITE" ));
  41
  42         db.setDatabaseName(CurrentSettings().DBPath());
  43         if ( ! db.open() )
  44                 throw std::runtime_error("Cannot open database: Unable to establish database connection");
  45         else
  46         {
  47                 qDebug() << "DB Opened";
  48
  49                 QSqlQuery dbqGetTriggers(db);
  50                 dbqGetTriggers.setForwardOnly( true );
  51
  52                 const char * sqlGetTriggers("SELECT name, sql FROM sqlite_master WHERE type = \"trigger\" AND name LIKE \"gc_update_ev_%\"");
  53                 if(dbqGetTriggers.exec(sqlGetTriggers))
  54                 {
  55                         qDebug() << "Query OK, " << dbqGetTriggers.numRowsAffected() << " rows affected.";
  56
  57                         while( dbqGetTriggers.next())
  58                         {
  59                                 QString name(dbqGetTriggers.value(0).value<QString>());
  60                                 QString sqlTrigger(dbqGetTriggers.value(1).value<QString>());
  61
  62                                 qDebug() << "( " << name << ", " << sqlTrigger << " )";
  63
  64                                 Triggers().insert(name, sqlTrigger);
  65                         }
  66
  67                         qDebug() << Triggers().count() << " triggers found.";
  68
  69                         QSqlQuery dbqRemoveTriggers(db);
  70                         dbqRemoveTriggers.setForwardOnly( true );
  71
  72                         const QString sqlRemoveTriggers("DROP TRIGGER ");
  73                         foreach(QString triggerName, Triggers().keys())
  74                         {
  75                                 qDebug() << "Executing: " << sqlRemoveTriggers + triggerName;
  76                                 if(dbqRemoveTriggers.exec(sqlRemoveTriggers + triggerName))
  77                                         qDebug() << "Query OK, " << dbqGetTriggers.numRowsAffected() << " rows affected.";
  78                                 else
  79                                         qDebug() << "Query failed, " << dbqGetTriggers.numRowsAffected() << " rows affected. Error: " << dbqRemoveTriggers.lastError().text();
  80                         }
  81                 }
  82                 else
  83                 {
  84                         qDebug() << "SQL EXEC Error: " << "EXEC query failed";
  85                         qDebug() << "Query: " << sqlGetTriggers;
  86                 }
  87
  88                 qDebug() << "Closing.";
  89                 db.close();
  90         }
  91
  92         QSqlDatabase::removeDatabase("QSQLITE");
  93
  94         return;
  95 }
  96
  97 TriggerDisabler::~TriggerDisabler()
  98 {
  99         // Restore default cache triggers
 100         if(!Reenabled())
 101                 Reenable();
 102 }
 103
 104 void TriggerDisabler::Reenable()
 105 {
 106         // Set up the database connection...
 107         QSqlDatabase db(QSqlDatabase::addDatabase( "QSQLITE" ));
 108
 109         db.setDatabaseName(CurrentSettings().DBPath());
 110         if (!db.open())
 111                 throw std::runtime_error("Cannot open database: Unable to establish database connection");
 112         else
 113         {
 114                 qDebug() << "DB Opened";
 115
 116                 RestoreTriggers(db);
 117
 118                 UpdateGroupCache(db);
 119
 120                 qDebug() << "Closing DB.";
 121                 db.close();
 122         }
 123
 124         QSqlDatabase::removeDatabase( "QSQLITE" );
 125
 126         Reenabled(true);
 127 }
 128
 129 void TriggerDisabler::RestoreTriggers(QSqlDatabase &db)
 130 {
 131         QSqlQuery dbqRestoreTriggers(db);
 132         dbqRestoreTriggers.setForwardOnly( true );
 133
 134         foreach(QString triggerName, Triggers().keys())
 135         {
 136                 qDebug() << "Restoring trigger " << triggerName;
 137                 qDebug() << "\tSQL: " << Triggers().value(triggerName);
 138                 if(dbqRestoreTriggers.exec(Triggers().value(triggerName)))
 139                         qDebug() << "Query OK, " << dbqRestoreTriggers.numRowsAffected() << " rows affected.";
 140                 else
 141                         qDebug() << "Query failed, " << dbqRestoreTriggers.numRowsAffected() << " rows affected. Error: " << dbqRestoreTriggers.lastError().text();
 142         }
 143 }
 144
 145 void TriggerDisabler::UpdateGroupCache(QSqlDatabase &db)
 146 {
 147         const char * sqlUpdateGroupCache(
 148                 "INSERT OR REPLACE INTO groupcache "
 149                 "SELECT id, service_id, group_uid, total, readcount, mergedflags "
 150                 "FROM events e, "
 151                         "(SELECT max(start_time) as maxdate, group_uid as maxgid, count(group_uid) as total, total(is_read) as readcount, _OR(flags) as mergedflags "
 152                         "FROM events "
 153                         "GROUP BY maxgid) maxresults "
 154                 "WHERE group_uid = maxgid AND start_time=maxresults.maxdate "
 155                 "GROUP BY group_uid");
 156
 157         QSqlQuery dbqRecalculateGroupCache(db);
 158         dbqRecalculateGroupCache.setForwardOnly(true);
 159
 160         createSQLiteFunctions(db);
 161
 162         qDebug() << "Updating group cache...";
 163         if(dbqRecalculateGroupCache.exec(sqlUpdateGroupCache))
 164         {
 165                 qDebug() << "Query OK, " << dbqRecalculateGroupCache.numRowsAffected() << " rows affected.";
 166
 167                 while(dbqRecalculateGroupCache.next())
 168                 {
 169                         int id(dbqRecalculateGroupCache.value(0).value<int>());
 170                         QString group_uid(dbqRecalculateGroupCache.value(1).value<QString>());
 171                         int total(dbqRecalculateGroupCache.value(2).value<int>());
 172                         int readcount(dbqRecalculateGroupCache.value(3).value<int>());
 173                         int flags(dbqRecalculateGroupCache.value(4).value<int>());
 174
 175                         qDebug() << "( " << id << ", " << group_uid << ", " << total << ", " << readcount << ", " << flags << " )";
 176                 }
 177         }
 178         else
 179         {
 180                 qDebug() << "SQL EXEC Error: " << "EXEC query failed. Error: " << dbqRecalculateGroupCache.lastError().text();
 181                 qDebug() << "Query: " << sqlUpdateGroupCache;
 182         }
 183 }
 184
 185 bool createSQLiteFunctions(const QSqlDatabase &db)
 186 {
 187         // Get handle to the driver and check it is both valid and refers to SQLite3.
 188         QVariant v(db.driver()->handle());
 189         if (!v.isValid() || qstrcmp(v.typeName(), "sqlite3*") != 0)
 190         {
 191                 qDebug() << "Cannot get a sqlite3 handle to the driver.";
 192                 return false;
 193         }
 194
 195         // Create a handler and attach functions.
 196         sqlite3 *handler(*static_cast<sqlite3**>(v.data()));
 197         if (!handler)
 198         {
 199                 qDebug() << "Cannot get a sqlite3 handler.";
 200                 return false;
 201         }
 202
 203         // Check validity of the state.
 204         if (!db.isValid())
 205         {
 206                 qDebug() << "Cannot create SQLite custom functions: db object is not valid.";
 207                 return false;
 208         }
 209
 210         if (!db.isOpen())
 211         {
 212                 qDebug() << "Cannot create SQLite custom functions: db object is not open.";
 213                 return false;
 214         }
 215
 216         if (sqlite3_create_function(handler, "_OR", 1, SQLITE_ANY, NULL, NULL, _sqlite3ORStep, _sqlite3ORFinal))
 217                 qDebug() << "Cannot create SQLite functions: sqlite3_create_function failed.";
 218
 219         return true;
 220 }
 221
 222 static void _sqlite3ORStep(sqlite3_context *context, int, sqlite3_value **value)
 223 {
 224         int *result((int *)sqlite3_aggregate_context(context, sizeof(int)));
 225         *result |= sqlite3_value_int(*value);
 226 }
 227
 228 static void _sqlite3ORFinal(sqlite3_context *context)
 229 {
 230         sqlite3_result_int(context, *(int *)sqlite3_aggregate_context(context, sizeof(int)));
 231 }