CTK/Libs/Core/PluginFramework/ctkPluginDatabase.cxx

/*=============================================================================

  Library: CTK

  Copyright (c) 2010 German Cancer Research Center,
    Division of Medical and Biological Informatics

  Licensed under the Apache License, Version 2.0 (the "License");
  you may not use this file except in compliance with the License.
  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.

=============================================================================*/

#include "ctkPluginDatabase_p.h"
#include "ctkPluginDatabaseException.h"
#include "ctkPlugin.h"
#include "ctkPluginException.h"
#include "ctkPluginArchive_p.h"
#include "ctkPluginStorage_p.h"

#include <QApplication>
#include <QFileInfo>
#include <QUrl>

#include <QDebug>

//database name
#define PLUGINDATABASE "pluginfw.db"

//database table names
#define PLUGINS_TABLE "Plugins"
#define PLUGIN_RESOURCES_TABLE "PluginResources"

//separator
#define PLUGINDATABASE_PATH_SEPARATOR "//"


namespace ctk {

  enum TBindIndexes
  {
    EBindIndex=0,
    EBindIndex1,
    EBindIndex2,
    EBindIndex3,
    EBindIndex4,
    EBindIndex5,
    EBindIndex6,
    EBindIndex7
  };



PluginDatabase::PluginDatabase(PluginStorage* storage)
:m_isDatabaseOpen(false), m_inTransaction(false), m_PluginStorage(storage)
{
}

PluginDatabase::~PluginDatabase()
{
    close();
}

void PluginDatabase::open()
{
  if (m_isDatabaseOpen)
    return;

  QString path;

  //Create full path to database
  if(m_databasePath.isEmpty ())
      m_databasePath = getDatabasePath();

  path = m_databasePath;
  QFileInfo dbFileInfo(path);
  if (!dbFileInfo.dir().exists())
  {
    if(!QDir::root().mkpath(dbFileInfo.path()))
    {
      close();
      QString errorText("Could not create database directory: %1");
      throw PluginDatabaseException(errorText.arg(dbFileInfo.path()), PluginDatabaseException::DB_CREATE_DIR_ERROR);
    }
  }

  m_connectionName = dbFileInfo.completeBaseName();
  QSqlDatabase database;
  if (QSqlDatabase::contains(m_connectionName))
  {
    database = QSqlDatabase::database(m_connectionName);
  }
  else
  {
    database = QSqlDatabase::addDatabase("QSQLITE", m_connectionName);
    database.setDatabaseName(path);
  }

  if (!database.isValid())
  {
    close();
    throw PluginDatabaseException(QString("Invalid database connection: %1").arg(m_connectionName),
                                  PluginDatabaseException::DB_CONNECTION_INVALID);
  }

  //Create or open database
  if (!database.isOpen())
  {
    if (!database.open())
    {
      close();
      throw PluginDatabaseException(QString("Could not open database. ") + database.lastError().text(),
                                    PluginDatabaseException::DB_SQL_ERROR);
    }
  }
  m_isDatabaseOpen = true;

  //Check if the sqlite version supports foreign key constraints
  QSqlQuery query(database);
  if (!query.exec("PRAGMA foreign_keys"))
  {
    close();
    throw PluginDatabaseException(QString("Check for foreign key support failed."),
                                  PluginDatabaseException::DB_SQL_ERROR);
  }

  if (!query.next())
  {
    close();
    throw PluginDatabaseException(QString("SQLite db does not support foreign keys. It is either older than 3.6.19 or was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER"),
                                  PluginDatabaseException::DB_SQL_ERROR);
  }
  query.finish();
  query.clear();

  //Enable foreign key support
  if (!query.exec("PRAGMA foreign_keys = ON"))
  {
    close();
    throw PluginDatabaseException(QString("Enabling foreign key support failed."),
                                  PluginDatabaseException::DB_SQL_ERROR);
  }
  query.finish();


  //Check database structure (tables) and recreate tables if neccessary
  //If one of the tables is missing remove all tables and recreate them
  //This operation is required in order to avoid data coruption
  if (!checkTables())
  {
    if (dropTables())
    {
      createTables();
    }
    else
    {
      //dropTables() should've handled error message
      //and warning
      close();
    }
  }

  //Update database based on the recorded timestamps
  updateDB();
}

void PluginDatabase::updateDB()
{
  checkConnection();

  QSqlDatabase database = QSqlDatabase::database(m_connectionName);
  QSqlQuery query(database);

  beginTransaction(&query, Write);

  QString statement = "SELECT ID, Location, LocalPath, Timestamp FROM Plugins WHERE State != ?";
  QList<QVariant> bindValues;
  bindValues.append(Plugin::UNINSTALLED);

  QList<qlonglong> outdatedIds;
  QList<QPair<QString,QString> > outdatedPlugins;
  try
  {
    executeQuery(&query, statement, bindValues);

    while (query.next())
    {
      QFileInfo pluginInfo(query.value(EBindIndex2).toString());
      if (pluginInfo.lastModified() > QDateTime::fromString(query.value(EBindIndex3).toString(), Qt::ISODate))
      {
        outdatedIds.append(query.value(EBindIndex).toLongLong());
        outdatedPlugins.append(qMakePair(query.value(EBindIndex1).toString(), query.value(EBindIndex2).toString()));
      }
    }
  }
  catch (...)
  {
    rollbackTransaction(&query);
    throw;
  }

  query.finish();
  query.clear();

  try
  {
    statement = "DELETE FROM Plugins WHERE ID=?";
    QListIterator<qlonglong> idIter(outdatedIds);
    while (idIter.hasNext())
    {
      bindValues.clear();
      bindValues.append(idIter.next());
      executeQuery(&query, statement, bindValues);
    }
  }
  catch (...)
  {
    rollbackTransaction(&query);
    throw;
  }

  commitTransaction(&query);

  QListIterator<QPair<QString,QString> > locationIter(outdatedPlugins);
  while (locationIter.hasNext())
  {
    const QPair<QString,QString>& locations = locationIter.next();
    insertPlugin(QUrl(locations.first), locations.second, false);
  }
}


PluginArchive* PluginDatabase::insertPlugin(const QUrl& location, const QString& localPath, bool createArchive)
{
  checkConnection();

  // Assemble the data for the sql record
  QFileInfo fileInfo(localPath);
  const QString lastModified = fileInfo.lastModified().toString(Qt::ISODate);

  QString resourcePrefix = fileInfo.baseName();
  if (resourcePrefix.startsWith("lib"))
  {
    resourcePrefix = resourcePrefix.mid(3);
  }
  resourcePrefix.replace("_", ".");

  resourcePrefix = QString(":/") + resourcePrefix + "/";

  QSqlDatabase database = QSqlDatabase::database(m_connectionName);
  QSqlQuery query(database);

  beginTransaction(&query, Write);

  QString statement = "INSERT INTO Plugins(Location,LocalPath,State,Timestamp) VALUES(?,?,?,?)";

  QList<QVariant> bindValues;
  bindValues.append(location.toString());
  bindValues.append(localPath);
  bindValues.append(Plugin::INSTALLED);
  bindValues.append(lastModified);

  long pluginId = -1;
  try
  {
    executeQuery(&query, statement, bindValues);
    QVariant lastId = query.lastInsertId();
    if (lastId.isValid())
    {
      pluginId = lastId.toLongLong();
    }
  }
  catch (...)
  {
    rollbackTransaction(&query);
    throw;
  }

  // Load the plugin and cache the resources
  QPluginLoader pluginLoader;
  pluginLoader.setFileName(localPath);
  if (!pluginLoader.load())
  {
    rollbackTransaction(&query);
    throw PluginException(QString("The plugin could not be loaded: %1").arg(localPath));
  }

  QDirIterator dirIter(resourcePrefix, QDirIterator::Subdirectories);
  while (dirIter.hasNext())
  {
    QString resourcePath = dirIter.next();
    if (QFileInfo(resourcePath).isDir()) continue;

    QFile resourceFile(resourcePath);
    resourceFile.open(QIODevice::ReadOnly);
    QByteArray resourceData = resourceFile.readAll();
    resourceFile.close();

    statement = "INSERT INTO PluginResources(PluginID, ResourcePath, Resource) VALUES(?,?,?)";
    bindValues.clear();
    bindValues.append(QVariant::fromValue<qlonglong>(pluginId));
    bindValues.append(resourcePath.mid(resourcePrefix.size()-1));
    bindValues.append(resourceData);

    try
    {
      executeQuery(&query, statement, bindValues);
    }
    catch (...)
    {
      rollbackTransaction(&query);
      throw;
    }
  }

  pluginLoader.unload();

  try
  {
    commitTransaction(&query);

    if (createArchive)
    {
      PluginArchive* archive = new PluginArchive(m_PluginStorage, location, localPath,
                                               pluginId);
      return archive;
    }
    else return 0;
  }
  catch (...)
  {
      rollbackTransaction(&query);
      throw;
  }

}

QStringList PluginDatabase::findResourcesPath(long pluginId, const QString& path) const
{
  checkConnection();

  QString statement = "SELECT SUBSTR(ResourcePath,?) FROM PluginResources WHERE PluginID=? AND SUBSTR(ResourcePath,1,?)=?";

  QString resourcePath = path.startsWith('/') ? path : QString("/") + path;
  if (!resourcePath.endsWith('/'))
    resourcePath += "/";

  QList<QVariant> bindValues;
  bindValues.append(resourcePath.size()+1);
  bindValues.append(qlonglong(pluginId));
  bindValues.append(resourcePath.size());
  bindValues.append(resourcePath);

  QSqlDatabase database = QSqlDatabase::database(m_connectionName);
  QSqlQuery query(database);

  executeQuery(&query, statement, bindValues);

  QStringList paths;
  while (query.next())
  {
    QString currPath = query.value(EBindIndex).toString();
    int slashIndex = currPath.indexOf('/');
    if (slashIndex > 0)
    {
      currPath = currPath.left(slashIndex+1);
    }

    paths << currPath;
  }

  return paths;
}

void PluginDatabase::removeArchive(const PluginArchive *pa)
{
  checkConnection();

  QSqlDatabase database = QSqlDatabase::database(m_connectionName);
  QSqlQuery query(database);

  QString statement = "DELETE FROM Plugins WHERE ID=?";

  QList<QVariant> bindValues;
  bindValues.append(pa->getPluginId());

  executeQuery(&query, statement, bindValues);
}

void PluginDatabase::executeQuery(QSqlQuery *query, const QString &statement, const QList<QVariant> &bindValues) const
{
  Q_ASSERT(query != 0);

  bool success = false;
  enum {Prepare =0 , Execute=1};

  for (int stage=Prepare; stage <= Execute; ++stage)
  {
    if ( stage == Prepare)
      success = query->prepare(statement);
    else // stage == Execute
      success = query->exec();

    if (!success)
    {
      QString errorText = "Problem: Could not %1 statement: %2\n"
              "Reason: %3\n"
              "Parameters: %4\n";
      QString parameters;
      if (bindValues.count() > 0)
      {
        for (int i = 0; i < bindValues.count(); ++i)
        {
          parameters.append(QString("\n\t[") + QString::number(i) + "]: " + bindValues.at(i).toString());
        }
      }
      else
      {
        parameters = "None";
      }

      PluginDatabaseException::Type errorType;
      int result = query->lastError().number();
      if (result == 26 || result == 11) //SQLILTE_NOTADB || SQLITE_CORRUPT
      {
        qWarning() << "PluginFramework:- Database file is corrupt or invalid:" << getDatabasePath();
        errorType = PluginDatabaseException::DB_FILE_INVALID;
      }
      else if (result == 8) //SQLITE_READONLY
        errorType = PluginDatabaseException::DB_WRITE_ERROR;
      else
        errorType = PluginDatabaseException::DB_SQL_ERROR;

      query->finish();
      query->clear();

      throw PluginDatabaseException(errorText.arg(stage == Prepare ? "prepare":"execute")
                  .arg(statement).arg(query->lastError().text()).arg(parameters), errorType);
    }

    if (stage == Prepare)
    {
      foreach(const QVariant &bindValue, bindValues)
        query->addBindValue(bindValue);
    }
  }
}


void PluginDatabase::close()
{
  if (m_isDatabaseOpen)
  {
    QSqlDatabase database = QSqlDatabase::database(m_connectionName, false);
    if (database.isValid())
    {
      if(database.isOpen())
      {
        database.close();
        m_isDatabaseOpen = false;
        return;
      }
    }
    else
    {
      throw PluginDatabaseException(QString("Problem closing database: Invalid connection %1").arg(m_connectionName));
    }
  }
}


void PluginDatabase::setDatabasePath(const QString &databasePath)
{
    m_databasePath = QDir::toNativeSeparators(databasePath);
}


QString PluginDatabase::getDatabasePath() const
{
    QString path;
    if(m_databasePath.isEmpty())
    {
      QSettings settings(QSettings::UserScope, "commontk", QApplication::applicationName());
      path = settings.value("PluginDB/Path").toString();
      if (path.isEmpty())
      {
        path = QDir::currentPath();
        if (path.lastIndexOf(PLUGINDATABASE_PATH_SEPARATOR) != path.length() -1)
        {
          path.append(PLUGINDATABASE_PATH_SEPARATOR);
        }
        path.append(PLUGINDATABASE);
      }
      path = QDir::toNativeSeparators(path);
    }
    else
    {
      path = m_databasePath;
    }

    return path;
}


QByteArray PluginDatabase::getPluginResource(long pluginId, const QString& res) const
{
  checkConnection();

  QSqlDatabase database = QSqlDatabase::database(m_connectionName);
  QSqlQuery query(database);

  QString statement = "SELECT Resource FROM PluginResources WHERE PluginID=? AND ResourcePath=?";

  QString resourcePath = res.startsWith('/') ? res : QString("/") + res;
  QList<QVariant> bindValues;
  bindValues.append(qlonglong(pluginId));
  bindValues.append(resourcePath);

  executeQuery(&query, statement, bindValues);

  if (query.next())
  {
    return query.value(EBindIndex).toByteArray();
  }

  return QByteArray();
}


void PluginDatabase::createTables()
{
    QSqlDatabase database = QSqlDatabase::database(m_connectionName);
    QSqlQuery query(database);

    //Begin Transaction
    beginTransaction(&query, Write);

    QString statement("CREATE TABLE Plugins("
                      "ID INTEGER PRIMARY KEY,"
                      "Location TEXT NOT NULL UNIQUE,"
                      "LocalPath TEXT NOT NULL UNIQUE,"
                      "State INTEGER NOT NULL,"
                      "Timestamp TEXT NOT NULL)");
    try
    {
      executeQuery(&query, statement);
    }
    catch (...)
    {
      rollbackTransaction(&query);
      throw;
    }

    statement = "CREATE TABLE PluginResources("
                "PluginID INTEGER NOT NULL,"
                "ResourcePath TEXT NOT NULL, "
                "Resource BLOB NOT NULL,"
                "FOREIGN KEY(PluginID) REFERENCES Plugins(ID) ON DELETE CASCADE)";
    try
    {
      executeQuery(&query, statement);
    }
    catch (...)
    {
      rollbackTransaction(&query);
      throw;
    }

    try
    {
      commitTransaction(&query);
    }
    catch (...)
    {
      rollbackTransaction(&query);
      throw;
    }

}


bool PluginDatabase::checkTables() const
{
  bool bTables(false);
  QStringList tables = QSqlDatabase::database(m_connectionName).tables();
  if (tables.contains(PLUGINS_TABLE)
      && tables.contains(PLUGIN_RESOURCES_TABLE))
  {
    bTables = true;
  }
  return bTables;
}


bool PluginDatabase::dropTables()
{
  //Execute transaction for deleting the database tables
  QSqlDatabase database = QSqlDatabase::database(m_connectionName);
  QSqlQuery query(database);
  QStringList expectedTables;
  expectedTables << PLUGINS_TABLE << PLUGIN_RESOURCES_TABLE;

  if (database.tables().count() > 0)
  {
    beginTransaction(&query, Write);
    QStringList actualTables = database.tables();

    foreach(const QString expectedTable, expectedTables)
    {
      if (actualTables.contains(expectedTable))
      {
        try
        {
          executeQuery(&query, QString("DROP TABLE ") + expectedTable);
        }
        catch (...)
        {
          rollbackTransaction(&query);
          throw;
        }
      }
      try
      {
        commitTransaction(&query);
      }
      catch (...)
      {
        rollbackTransaction(&query);
        throw;
      }
    }
  }
}


bool PluginDatabase::isOpen() const
{
  return m_isDatabaseOpen;
}


void PluginDatabase::checkConnection() const
{
  if(!m_isDatabaseOpen)
  {
    throw PluginDatabaseException("Database not open.", PluginDatabaseException::DB_NOT_OPEN_ERROR);
  }

  if (!QSqlDatabase::database(m_connectionName).isValid())
  {
    throw PluginDatabaseException(QString("Database connection invalid: %1").arg(m_connectionName),
                                  PluginDatabaseException::DB_CONNECTION_INVALID);
  }
}


void PluginDatabase::beginTransaction(QSqlQuery *query, TransactionType type)
{
  bool success;
  if (type == Read)
      success = query->exec(QLatin1String("BEGIN"));
  else
      success = query->exec(QLatin1String("BEGIN IMMEDIATE"));

  if (!success) {
      int result = query->lastError().number();
      if (result == 26 || result == 11) //SQLITE_NOTADB || SQLITE_CORRUPT
      {
        throw PluginDatabaseException(QString("PluginFramework: Database file is corrupt or invalid: %1").arg(getDatabasePath()),
                                      PluginDatabaseException::DB_FILE_INVALID);
      }
      else if (result == 8) //SQLITE_READONLY
      {
        throw PluginDatabaseException(QString("PluginFramework: Insufficient permissions to write to database: %1").arg(getDatabasePath()),
                                      PluginDatabaseException::DB_WRITE_ERROR);
      }
      else
        throw PluginDatabaseException(QString("PluginFramework: ") + query->lastError().text(),
                                      PluginDatabaseException::DB_SQL_ERROR);
  }

}


void PluginDatabase::commitTransaction(QSqlQuery *query)
{
  Q_ASSERT(query != 0);
  query->finish();
  query->clear();
  if (!query->exec(QLatin1String("COMMIT")))
  {
    throw PluginDatabaseException(QString("PluginFramework: ") + query->lastError().text(),
                                  PluginDatabaseException::DB_SQL_ERROR);
  }
}


void PluginDatabase::rollbackTransaction(QSqlQuery *query)
{
  Q_ASSERT(query !=0);
  query->finish();
  query->clear();

  if (!query->exec(QLatin1String("ROLLBACK")))
  {
    throw PluginDatabaseException(QString("PluginFramework: ") + query->lastError().text(),
                                  PluginDatabaseException::DB_SQL_ERROR);
  }
}

QList<PluginArchive*> PluginDatabase::getPluginArchives() const
{
  checkConnection();

  QSqlQuery query(QSqlDatabase::database(m_connectionName));
  QString statement("SELECT ID, Location, LocalPath FROM Plugins WHERE State != ?");
  QList<QVariant> bindValues;
  bindValues.append(Plugin::UNINSTALLED);

  executeQuery(&query, statement, bindValues);

  QList<PluginArchive*> archives;
  while (query.next())
  {
    const long id = query.value(EBindIndex).toLongLong();
    const QUrl location(query.value(EBindIndex1).toString());
    const QString localPath(query.value(EBindIndex2).toString());

    if (id <= 0 || location.isEmpty() || localPath.isEmpty())
    {
      throw PluginDatabaseException(QString("Database integrity corrupted, row %1 contains empty values.").arg(id),
                                    PluginDatabaseException::DB_FILE_INVALID);
    }

    try
    {
      PluginArchive* pa = new PluginArchive(m_PluginStorage, location, localPath, id);
      archives.append(pa);
    }
    catch (const PluginException& exc)
    {
      qWarning() << exc;
    }
  }

  return archives;
}

}