Missing remove column implementation for sqlite upon QSqlQuery.

qt-sqlite-removecolumn.cpp

#include <QLoggingCategory>                                                                                                
#include <QSqlQuery>
#include <QSqlError>

Q_LOGGING_CATEGORY(hfCoreM, "hf.core.migration");

QString getOneTableDbSchema(QString table_name) {
  auto q = QString(
          "SELECT * FROM `sqlite_master` WHERE `type` = 'table' AND `name` = '%1'").arg(table_name);

  QSqlQuery query;
  if (!query.exec(q)) {
    qCCritical(hfCoreM) << query.lastQuery() << query.lastError().text();
    return "";
  }

  if (query.next()) {
      return query.value("sql").toString();
  }

  return "";
}

QSet<QString> getTableColumns(QString table_name) {
  const auto q = "pragma table_info(" + table_name + ");";
  QSet<QString> columns;

  QSqlQuery query;
  if (!query.exec(q)) {
    qCCritical(hfCoreM) << query.lastQuery() << query.lastError().text();
    return columns;
  }

  while (query.next()) {
      columns.insert(query.value("name").toString());
  }

  qCDebug(hfCoreM) << columns;
  return columns;
}

QString getSqlWithoutRemovedColumns(QString oldColumnsSql, QSet<QString> columnsToRemove) {
  qCDebug(hfCoreM) << oldColumnsSql;
  QStringList splitSql = oldColumnsSql.split(", ");
  for (int i=0; i < splitSql.size(); i++) {
    for (const auto remove : columnsToRemove) {
      if (splitSql[i].contains(remove) ) {
        splitSql.removeAt(i--);
      }
    }
  }
  return splitSql.join(", ");
}

bool safeQueryExec(QSqlQuery &query, QString q ) {
  qCDebug(hfCoreM) << q;
  if (!query.exec(q)) {
    qCCritical(hfCoreM) << query.lastQuery() << query.lastError().text();
    return false;
  }
  return true;
}

bool removeColumn(const QString &table_name, const QString &columnToRemove) {
    QSet<QString> columns;
    columns << columnToRemove;
    return removeColumns(table_name, columns);
}

bool removeColumns(const QString &table_name, const QSet<QString> &columnsToRemove) {
  qCDebug(hfCoreM) << table_name << columnsToRemove;
  QSqlQuery query;

  QSet<QString> columnNames = getTableColumns(table_name);
  // Remove the columns we don't want anymore from the table's list of columns
  QSet<QString> newColumnNames = columnNames.subtract(columnsToRemove);
  QStringList newColumnNamesList = QStringList::fromSet(newColumnNames);
  QString newColumnNamesSeparated = newColumnNamesList.join(", ");
  qCDebug(hfCoreM) << newColumnNamesSeparated;

  QString sql = getOneTableDbSchema(table_name);
  // Extract the SQL query that contains only columns
  int from = sql.indexOf("(")+1;
  int n = sql.lastIndexOf(")") - from;
  QString oldColumnsSql = sql.mid(from, n);

  QString newColumnsSql = getSqlWithoutRemovedColumns(oldColumnsSql, columnsToRemove);


  bool res = safeQueryExec(query, QString("CREATE TABLE `%1_old` (%2);")
                                .arg(table_name).arg(newColumnsSql))
    && safeQueryExec(query, QString("INSERT INTO %1_old SELECT %2 FROM %1;")
                                .arg(table_name).arg(newColumnNamesSeparated))
    && safeQueryExec(query, QString("DROP TABLE %1;")
                                .arg(table_name))
    && safeQueryExec(query, QString("CREATE TABLE `%1` (%2);")
                                .arg(table_name).arg(newColumnsSql))
    && safeQueryExec(query, QString("INSERT INTO %1 SELECT %2 FROM %1_old;")
                                .arg(table_name).arg(newColumnNamesSeparated))
    && safeQueryExec(query, QString("DROP TABLE %1_old;")
                                .arg(table_name));

  return res;
}