catalogsdb.cpp

/*
    SPDX-FileCopyrightText: 2021 Valentin Boettcher <hiro at protagon.space; @hiro98:tchncs.de>
 
    SPDX-License-Identifier: GPL-2.0-or-later
*/
 
#include <limits>
#include <cmath>
#include <QSqlDriver>
#include <QSqlRecord>
#include <QMutexLocker>
#include <QTemporaryDir>
#include <qsqldatabase.h>
#include "cachingdms.h"
#include "catalogsdb.h"
#include "kspaths.h"
#include "skymesh.h"
#include "Options.h"
#include "final_action.h"
#include "sqlstatements.cpp"
 
using namespace CatalogsDB;
 
/**
 * Get an increasing index for new connections.
 */
int get_connection_index()
{
    static int connection_index = 0;
    return connection_index++;
}
 
QSqlQuery make_query(QSqlDatabase &db, const QString &statement, const bool forward_only)
{
    QSqlQuery query{ db };
 
    query.setForwardOnly(forward_only);
    if (!query.prepare(statement))
    {
        throw DatabaseError("Can't prepare query!", DatabaseError::ErrorType::PREPARE,
                            query.lastError());
    };
 
    return query;
}
 
/**
 * Migrate the database from \p version to the current version.
 */
std::pair<bool, QString> migrate_db(const int version, QSqlDatabase &db,
                                    QString prefix = "")
{
    if (prefix.size() > 0)
        prefix += ".";
 
    // we have to add the timestamp collumn to the catalogs
    if (version < 2)
    {
        QSqlQuery add_ts{ db };
        const auto success = add_ts.exec(QString("ALTER TABLE %1catalogs ADD COLUMN "
                                         "timestamp DEFAULT NULL")
                                         .arg(prefix));
        if (!success)
            return { false, add_ts.lastError().text() };
    }
 
    // adding the color selector table; this only applies for the
    // master database
    if (version < 3 && prefix == "")
    {
        QSqlQuery add_colors{ db };
        const auto success = add_colors.exec(SqlStatements::create_colors_table);
        if (!success)
            return { false, add_colors.lastError().text() };
    }
 
    return { true, "" };
}
 
DBManager::DBManager(const QString &filename) : m_db_file(filename)
{
    m_db = QSqlDatabase::addDatabase("QSQLITE", QUuid::createUuid().toString());
    m_db.setDatabaseName(m_db_file);
 
    // we are throwing here, because errors at this stage should be fatal
    if (!m_db.open())
    {
        throw DatabaseError(QString("Cannot open CatalogDatabase '%1'!").arg(m_db_file),
                            DatabaseError::ErrorType::OPEN, m_db.lastError());
    }
 
    bool init                                    = false;
    std::tie(m_db_version, m_htmesh_level, init) = get_db_meta();
 
    if (!init && m_db_version > 0 && m_db_version < SqlStatements::current_db_version)
    {
        const auto &backup_path = QString("%1.%2").arg(m_db_file).arg(
                                      QDateTime::currentDateTime().toString("dd_MMMM_yy_hh_mm_sss_zzz"));
 
        if (!QFile::copy(m_db_file, backup_path))
        {
            throw DatabaseError(
                QString("Could not backup dso database before upgrading."),
                DatabaseError::ErrorType::VERSION, QSqlError{});
        }
 
        const auto &success = migrate_db(m_db_version, m_db);
        if (success.first)
        {
            m_db_version = SqlStatements::current_db_version;
            QSqlQuery version_query{ m_db };
            version_query.prepare(SqlStatements::update_version);
            version_query.bindValue(":version", m_db_version);
 
            if (!version_query.exec())
            {
                throw DatabaseError(QString("Could not update the database version."),
                                    DatabaseError::ErrorType::VERSION,
                                    version_query.lastError());
            }
        }
        else
            throw DatabaseError(
                QString("Wrong database version. Expected %1 and got %2 and "
                        "migration is not possible.")
                .arg(SqlStatements::current_db_version)
                .arg(m_db_version),
                DatabaseError::ErrorType::VERSION, success.second);
    }
 
    QSqlQuery master_exists{ m_db };
    master_exists.exec(SqlStatements::exists_master);
    const bool master_does_exist = master_exists.next();
    master_exists.finish();
 
    if (init || !master_does_exist)
    {
        if (!initialize_db())
        {
            throw DatabaseError(QString("Could not initialize database."),
                                DatabaseError::ErrorType::INIT, m_db.lastError());
        }
 
        if (!catalog_exists(SqlStatements::user_catalog_id))
        {
            const auto &res =
                register_catalog(SqlStatements::user_catalog_id,
                                 SqlStatements::user_catalog_name, true, true, 1);
            if (!res.first)
            {
                throw DatabaseError(QString("Could not create user database."),
                                    DatabaseError::ErrorType::CREATE_CATALOG, res.second);
            }
        }
 
        if (!update_catalog_views())
        {
            throw DatabaseError(QString("Unable to create combined catalog view!"),
                                DatabaseError::ErrorType::CREATE_CATALOG,
                                m_db.lastError());
        }
 
        if (!compile_master_catalog())
        {
            throw DatabaseError(QString("Unable to create master catalog!"),
                                DatabaseError::ErrorType::CREATE_MASTER,
                                m_db.lastError());
        }
    }
 
    m_q_cat_by_id         = make_query(m_db, SqlStatements::get_catalog_by_id, true);
    m_q_obj_by_trixel     = make_query(m_db, SqlStatements::dso_by_trixel, false);
    m_q_obj_by_trixel_no_nulls = make_query(m_db, SqlStatements::dso_by_trixel_no_nulls, false);
    m_q_obj_by_trixel_null_mag = make_query(m_db, SqlStatements::dso_by_trixel_null_mag, false);
    m_q_obj_by_name       = make_query(m_db, SqlStatements::dso_by_name, true);
    m_q_obj_by_name_exact = make_query(m_db, SqlStatements::dso_by_name_exact, true);
    m_q_obj_by_lim        = make_query(m_db, SqlStatements::dso_by_lim, true);
    m_q_obj_by_maglim     = make_query(m_db, SqlStatements::dso_by_maglim, true);
    m_q_obj_by_maglim_and_type =
        make_query(m_db, SqlStatements::dso_by_maglim_and_type, true);
    m_q_obj_by_oid = make_query(m_db, SqlStatements::dso_by_oid, true);
};
 
DBManager::DBManager(const DBManager &other) : DBManager::DBManager{ other.m_db_file } {};
 
bool DBManager::initialize_db()
{
    if (m_db_version < 0 || m_htmesh_level < 1)
        throw std::runtime_error("DBManager not initialized properly, m_db_vesion and "
                                 "m_htmesh_level have to be set.");
 
    if (!m_db.exec(SqlStatements::create_meta_table).isActive())
        return false;
 
    if (!m_db.exec(SqlStatements::create_colors_table).isActive())
        return false;
 
    QSqlQuery meta_query{ m_db };
    meta_query.prepare(SqlStatements::set_meta);
    meta_query.bindValue(0, m_db_version);
    meta_query.bindValue(1, m_htmesh_level);
    meta_query.bindValue(2, false);
 
    if (!meta_query.exec())
        return false;
 
    return m_db.exec(SqlStatements::create_catalog_list_table).isActive();
}
 
std::tuple<int, int, bool> DBManager::get_db_meta()
{
    auto query = m_db.exec(SqlStatements::get_meta);
 
    if (query.first())
        return { query.value(0).toInt(), query.value(1).toInt(),
                 query.value(2).toBool() };
    else
        return { SqlStatements::current_db_version, SqlStatements::default_htmesh_level,
                 true };
}
 
std::vector<int> DBManager::get_catalog_ids(bool include_disabled)
{
    auto query = m_db.exec(include_disabled ? SqlStatements::get_all_catalog_ids :
                           SqlStatements::get_catalog_ids);
 
    std::vector<int> ids;
 
    while (query.next())
    {
        int id = query.value(0).toInt();
        ids.push_back(id);
    }
 
    return ids;
}
 
bool DBManager::update_catalog_views()
{
    const auto &ids = get_catalog_ids();
    bool result     = true;
    auto _          = gsl::finally([&]()
    {
        m_db.commit();
    });
 
    m_db.transaction();
    QSqlQuery query{ m_db };
    result &=
        query.exec(QString("DROP VIEW IF EXISTS ") + SqlStatements::all_catalog_view);
 
    if (!result)
    {
        return result;
    }
 
    QString view
    {
        "CREATE VIEW  "
    }; // small enough to be included here and not in sqlstatements
 
    view += SqlStatements::all_catalog_view;
    view += " AS\n";
 
    QStringList prefixed{};
    for (auto *field : SqlStatements::catalog_collumns)
    {
        prefixed << QString("c.") + field;
    }
 
    QString prefixed_joined = prefixed.join(",");
 
    QStringList catalog_queries{};
    for (auto id : ids)
    {
        catalog_queries << SqlStatements::all_catalog_view_body(
                            prefixed_joined, SqlStatements::catalog_prefix, id);
    }
 
    if (ids.size() == 0)
    {
        catalog_queries << SqlStatements::all_catalog_view_body(
                            prefixed_joined, SqlStatements::catalog_prefix, 0) +
                        " WHERE FALSE"; // we blackhole the query
    }
 
    view += catalog_queries.join("\nUNION ALL\n");
    result &= query.exec(view);
    return result;
}
 
void bind_catalog(QSqlQuery &query, const Catalog &cat)
{
    query.bindValue(":id", cat.id);
    query.bindValue(":name", cat.name);
    query.bindValue(":mut", cat.mut);
    query.bindValue(":enabled", cat.enabled);
    query.bindValue(":precedence", cat.precedence);
    query.bindValue(":author", cat.author);
    query.bindValue(":source", cat.source);
    query.bindValue(":description", cat.description);
    query.bindValue(":version", cat.version);
    query.bindValue(":color", cat.color);
    query.bindValue(":license", cat.license);
    query.bindValue(":maintainer", cat.maintainer);
    query.bindValue(":timestamp", cat.timestamp);
}
 
std::pair<bool, QString> DBManager::register_catalog(
    const int id, const QString &name, const bool mut, const bool enabled,
    const double precedence, const QString &author, const QString &source,
    const QString &description, const int version, const QString &color,
    const QString &license, const QString &maintainer, const QDateTime &timestamp)
{
    return register_catalog({ id, name, precedence, author, source, description, mut,
                              enabled, version, color, license, maintainer, timestamp });
}
 
std::pair<bool, QString> DBManager::register_catalog(const Catalog &cat)
{
    if (catalog_exists(cat.id))
        return { false, i18n("Catalog with that ID already exists.") };
 
    QSqlQuery query{ m_db };
 
    if (!query.exec(SqlStatements::create_catalog_table(cat.id)))
    {
        return { false, query.lastError().text() };
    }
 
    query.prepare(SqlStatements::insert_catalog);
    bind_catalog(query, cat);
 
    return { query.exec(), query.lastError().text() };
};
 
bool DBManager::compile_master_catalog()
{
    auto _ = gsl::finally([&]()
    {
        m_db.commit();
    });
    QSqlQuery query{ m_db };
    m_db.transaction();
 
    if (!query.exec(SqlStatements::drop_master))
    {
        return false;
    }
 
    if (!query.exec(SqlStatements::create_master))
    {
        return false;
    }
 
    bool success = true;
    success &= query.exec(SqlStatements::create_master_trixel_index);
    success &= query.exec(SqlStatements::create_master_mag_index);
    success &= query.exec(SqlStatements::create_master_type_index);
    success &= query.exec(SqlStatements::create_master_name_index);
    return success;
};
 
const Catalog read_catalog(const QSqlQuery &query)
{
    return { query.value("id").toInt(),
             query.value("name").toString(),
             query.value("precedence").toDouble(),
             query.value("author").toString(),
             query.value("source").toString(),
             query.value("description").toString(),
             query.value("mut").toBool(),
             query.value("enabled").toBool(),
             query.value("version").toInt(),
             query.value("color").toString(),
             query.value("license").toString(),
             query.value("maintainer").toString(),
             query.value("timestamp").toDateTime() };
}
 
const std::pair<bool, Catalog> DBManager::get_catalog(const int id)
{
    QMutexLocker _{ &m_mutex };
    m_q_cat_by_id.bindValue(0, id);
 
    if (!m_q_cat_by_id.exec())
        return { false, {} };
 
    if (!m_q_cat_by_id.next())
        return { false, {} };
 
    Catalog cat{ read_catalog(m_q_cat_by_id) };
 
    m_q_cat_by_id.finish();
    return { true, cat };
}
 
bool DBManager::catalog_exists(const int id)
{
    QMutexLocker _{ &m_mutex };
    m_q_cat_by_id.bindValue(0, id);
    auto end = gsl::finally([&]()
    {
        m_q_cat_by_id.finish();
    });
 
    if (!m_q_cat_by_id.exec())
        return false;
 
    return m_q_cat_by_id.next();
}
 
size_t count_rows(QSqlQuery &query)
{
    size_t count{ 0 };
    while (query.next())
    {
        count++;
    }
 
    return count;
}
 
CatalogObject DBManager::read_catalogobject(const QSqlQuery &query) const
{
    const CatalogObject::oid id = query.value(0).toByteArray();
    const SkyObject::TYPE type  = static_cast<SkyObject::TYPE>(query.value(1).toInt());
 
    const double ra         = query.value(2).toDouble();
    const double dec        = query.value(3).toDouble();
    const float mag         = query.isNull(4) ? NaN::f : query.value(4).toFloat();
    const QString name      = query.value(5).toString();
    const QString long_name = query.value(6).toString();
    const QString catalog_identifier = query.value(7).toString();
    const float major                = query.value(8).toFloat();
    const float minor                = query.value(9).toFloat();
    const double position_angle      = query.value(10).toDouble();
    const float flux                 = query.value(11).toFloat();
    const int catalog_id             = query.value(12).toInt();
 
    return { id,         type,     dms(ra),   dms(dec),
             mag,        name,     long_name, catalog_identifier,
             catalog_id, major,    minor,     position_angle,
             flux,       m_db_file };
}
 
CatalogObjectVector DBManager::_get_objects_in_trixel_generic(QSqlQuery &query, const int trixel)
{
    QMutexLocker _{ &m_mutex }; // this costs ~ .1ms which is ok
    query.bindValue(0, trixel);
 
    if (!query.exec()) // we throw because this is not recoverable
        throw DatabaseError(
            QString("The by-trixel query for objects in trixel=%1 failed.")
            .arg(trixel),
            DatabaseError::ErrorType::UNKNOWN, query.lastError());
 
    CatalogObjectVector objects;
    size_t count =
        count_rows(query); // this also moves the query head to the end
 
    if (count == 0)
    {
        query.finish();
        return objects;
    }
 
    objects.reserve(count);
 
    while (query.previous())
    {
        objects.push_back(read_catalogobject(query));
    }
 
    query.finish();
 
    // move semantics baby!
    return objects;
}
 
CatalogObjectList DBManager::fetch_objects(QSqlQuery &query) const
{
    CatalogObjectList objects;
    auto _ = gsl::finally([&]()
    {
        query.finish();
    });
 
    query.exec();
 
    if (!query.isActive())
        return {};
    while (query.next())
        objects.push_back(read_catalogobject(query));
 
    return objects;
}
 
CatalogObjectList DBManager::find_objects_by_name(const QString &name, const int limit,
        const bool exactMatchOnly)
{
    QMutexLocker _{ &m_mutex };
 
    // limit < 0 is a sentinel value for unlimited
    if (limit == 0)
        return CatalogObjectList();
 
    // search for an exact match first
    m_q_obj_by_name_exact.bindValue(":name", name);
    CatalogObjectList objs { fetch_objects(m_q_obj_by_name_exact) };
 
    if ((limit == 1 && objs.size() > 0) || exactMatchOnly)
        return objs;
 
    Q_ASSERT(objs.size() <= 1);
 
    m_q_obj_by_name.bindValue(":name", name);
    m_q_obj_by_name.bindValue(":limit", int(limit - objs.size()));
 
    CatalogObjectList moreObjects = fetch_objects(m_q_obj_by_name);
    moreObjects.splice(moreObjects.begin(), objs);
    return moreObjects;
 
}
 
CatalogObjectList DBManager::find_objects_by_name(const int catalog_id,
        const QString &name, const int limit)
{
    QSqlQuery query{ m_db };
 
    query.prepare(SqlStatements::dso_by_name_and_catalog(catalog_id));
    query.bindValue(":name", name);
    query.bindValue(":limit", limit);
    query.bindValue(":catalog", catalog_id);
 
    return fetch_objects(query);
}
 
std::pair<bool, CatalogObject> DBManager::read_first_object(QSqlQuery &query) const
{
    if (!query.exec() || !query.first())
        return { false, {} };
 
    return { true, read_catalogobject(query) };
}
 
std::pair<bool, CatalogObject> DBManager::get_object(const CatalogObject::oid &oid)
{
    QMutexLocker _{ &m_mutex };
    m_q_obj_by_oid.bindValue(0, oid);
 
    auto f = gsl::finally([&]()   // taken from the GSL, runs when it goes out of scope
    {
        m_q_obj_by_oid.finish();
    });
 
    return read_first_object(m_q_obj_by_oid);
};
 
std::pair<bool, CatalogObject> DBManager::get_object(const CatalogObject::oid &oid,
        const int catalog_id)
{
    QMutexLocker _{ &m_mutex };
    QSqlQuery query{ m_db };
 
    query.prepare(SqlStatements::dso_by_oid_and_catalog(catalog_id));
    query.bindValue(0, oid);
 
    return read_first_object(query);
};
 
CatalogObjectList DBManager::get_objects(float maglim, int limit)
{
    QMutexLocker _{ &m_mutex };
    m_q_obj_by_maglim.bindValue(":maglim", maglim);
    m_q_obj_by_maglim.bindValue(":limit", limit);
 
    return fetch_objects(m_q_obj_by_maglim);
}
 
CatalogObjectList DBManager::get_objects_all()
{
    QMutexLocker _{ &m_mutex };
    m_q_obj_by_lim.bindValue(":limit", -1);
 
    return fetch_objects(m_q_obj_by_lim);
}
 
CatalogObjectList DBManager::get_objects(SkyObject::TYPE type, float maglim, int limit)
{
    QMutexLocker _{ &m_mutex };
    m_q_obj_by_maglim_and_type.bindValue(":type", type);
    m_q_obj_by_maglim_and_type.bindValue(":limit", limit);
    m_q_obj_by_maglim_and_type.bindValue(":maglim", maglim);
 
    return fetch_objects(m_q_obj_by_maglim_and_type);
}
 
CatalogObjectList DBManager::get_objects_in_catalog(SkyObject::TYPE type,
        const int catalog_id, float maglim,
        int limit)
{
    QSqlQuery query{ m_db };
 
    query.prepare(SqlStatements::dso_in_catalog_by_maglim(catalog_id));
    query.bindValue(":type", type);
    query.bindValue(":limit", limit);
    query.bindValue(":maglim", maglim);
    return fetch_objects(query);
}
 
std::pair<bool, QString> DBManager::set_catalog_enabled(const int id, const bool enabled)
{
    const auto &success = get_catalog(id);
    if (!success.first)
        return { false, i18n("Catalog could not be found.") };
 
    const auto &cat = success.second;
    if (cat.enabled == enabled)
        return { true, "" };
 
    QSqlQuery query{ m_db };
    query.prepare(SqlStatements::enable_disable_catalog);
    query.bindValue(":enabled", enabled);
    query.bindValue(":id", id);
 
    return { query.exec() &&update_catalog_views() &&compile_master_catalog(),
             query.lastError().text() + m_db.lastError().text() };
}
 
const std::vector<Catalog> DBManager::get_catalogs(bool include_disabled)
{
    auto ids = get_catalog_ids(include_disabled);
    std::vector<Catalog> catalogs;
    catalogs.reserve(ids.size());
 
    std::transform(ids.cbegin(), ids.cend(), std::back_inserter(catalogs),
                   [&](const int id)
    {
        const auto &found = get_catalog(id);
        if (found.first)
            return found.second;
 
        // This really should **not** happen
        throw DatabaseError(
            QString("Could not retrieve the catalog with id=%1").arg(id));
    });
 
    return catalogs;
}
 
inline void bind_catalogobject(QSqlQuery &query, const int catalog_id,
                               const SkyObject::TYPE t, const CachingDms &r,
                               const CachingDms &d, const QString &n, const float m,
                               const QString &lname, const QString &catalog_identifier,
                               const float a, const float b, const double pa,
                               const float flux, Trixel trixel,
                               const CatalogObject::oid &new_id)
{
    query.prepare(SqlStatements::insert_dso(catalog_id));
 
    query.bindValue(":hash", new_id); // no dedupe, maybe in the future
    query.bindValue(":oid", new_id);
    query.bindValue(":type", static_cast<int>(t));
    query.bindValue(":ra", r.Degrees());
    query.bindValue(":dec", d.Degrees());
    query.bindValue(":magnitude", (m < 99 && !std::isnan(m)) ? m : QVariant{});
    query.bindValue(":name", n);
    query.bindValue(":long_name", lname.length() > 0 ? lname : QVariant{});
    query.bindValue(":catalog_identifier",
                    catalog_identifier.length() > 0 ? catalog_identifier : QVariant{});
    query.bindValue(":major_axis", a > 0 ? a : QVariant{});
    query.bindValue(":minor_axis", b > 0 ? b : QVariant{});
    query.bindValue(":position_angle", pa > 0 ? pa : QVariant{});
    query.bindValue(":flux", flux > 0 ? flux : QVariant{});
    query.bindValue(":trixel", trixel);
    query.bindValue(":catalog", catalog_id);
}
 
inline void bind_catalogobject(QSqlQuery &query, const int catalog_id,
                               const CatalogObject &obj, Trixel trixel)
{
    bind_catalogobject(query, catalog_id, static_cast<SkyObject::TYPE>(obj.type()),
                       obj.ra0(), obj.dec0(), obj.name(), obj.mag(), obj.longname(),
                       obj.catalogIdentifier(), obj.a(), obj.b(), obj.pa(), obj.flux(),
                       trixel, obj.getObjectId());
};
 
std::pair<bool, QString> DBManager::add_object(const int catalog_id,
        const CatalogObject &obj)
{
    return add_object(catalog_id, static_cast<SkyObject::TYPE>(obj.type()), obj.ra0(),
                      obj.dec0(), obj.name(), obj.mag(), obj.longname(),
                      obj.catalogIdentifier(), obj.a(), obj.b(), obj.pa(), obj.flux());
}
 
std::pair<bool, QString>
DBManager::add_object(const int catalog_id, const SkyObject::TYPE t, const CachingDms &r,
                      const CachingDms &d, const QString &n, const float m,
                      const QString &lname, const QString &catalog_identifier,
                      const float a, const float b, const double pa, const float flux)
{
    {
        const auto &success = get_catalog(catalog_id);
        if (!success.first)
            return { false, i18n("Catalog with id=%1 not found.", catalog_id) };
 
        if (!success.second.mut)
            return { false, i18n("Catalog is immutable!") };
    }
 
    SkyPoint tmp{ r, d };
    const auto trixel = SkyMesh::Create(m_htmesh_level)->index(&tmp);
    QSqlQuery query{ m_db };
 
    const auto new_id =
        CatalogObject::getId(t, r.Degrees(), d.Degrees(), n, catalog_identifier);
    bind_catalogobject(query, catalog_id, t, r, d, n, m, lname, catalog_identifier, a, b,
                       pa, flux, trixel, new_id);
 
    if (!query.exec())
    {
        auto err = query.lastError().text();
        if (err.startsWith("UNIQUE"))
            err = i18n("The object is already in the catalog!");
 
        return { false, i18n("Could not insert object! %1", err) };
    }
 
    return { update_catalog_views() &&compile_master_catalog(),
             m_db.lastError().text() };
}
 
std::pair<bool, QString> DBManager::remove_object(const int catalog_id,
        const CatalogObject::oid &id)
{
    QSqlQuery query{ m_db };
 
    query.prepare(SqlStatements::remove_dso(catalog_id));
    query.bindValue(":oid", id);
 
    if (!query.exec())
        return { false, query.lastError().text() };
 
    return { update_catalog_views() &&compile_master_catalog(),
             m_db.lastError().text() };
}
 
std::pair<bool, QString> DBManager::dump_catalog(int catalog_id, QString file_path)
{
    const auto &found = get_catalog(catalog_id);
    if (!found.first)
        return { false, i18n("Catalog could not be found.") };
 
    QFile file{ file_path };
    if (!file.open(QIODevice::WriteOnly))
        return { false, i18n("Output file is not writable.") };
    file.resize(0);
    file.close();
 
    QSqlQuery query{ m_db };
 
    if (!query.exec(QString("ATTACH [%1] AS tmp").arg(file_path)))
        return { false,
                 i18n("Could not attach output file.<br>%1", query.lastError().text()) };
 
    m_db.transaction();
    auto _ = gsl::finally([&]()   // taken from the GSL, runs when it goes out of scope
    {
        m_db.commit();
        query.exec("DETACH tmp");
    });
 
    if (!query.exec(
                QString("CREATE TABLE tmp.cat AS SELECT * FROM cat_%1").arg(catalog_id)))
        return { false, i18n("Could not copy catalog to output file.<br>%1")
                 .arg(query.lastError().text()) };
 
    if (!query.exec(SqlStatements::create_catalog_registry("tmp.catalogs")))
        return { false, i18n("Could not create catalog registry in output file.<br>%1")
                 .arg(query.lastError().text()) };
 
    query.prepare(SqlStatements::insert_into_catalog_registry("tmp.catalogs"));
 
    auto cat    = found.second;
    cat.enabled = true;
    bind_catalog(query, cat);
 
    if (!query.exec())
    {
        return { false,
                 i18n("Could not insert catalog into registry in output file.<br>%1")
                 .arg(query.lastError().text()) };
    }
 
    if (!query.exec(QString("PRAGMA tmp.user_version = %1").arg(m_db_version)))
    {
        return { false, i18n("Could not insert set exported database version.<br>%1")
                 .arg(query.lastError().text()) };
    }
 
    if (!query.exec(QString("PRAGMA tmp.application_id = %1").arg(application_id)))
    {
        return { false,
                 i18n("Could not insert set exported database application id.<br>%1")
                 .arg(query.lastError().text()) };
    }
 
    return { true, {} };
}
 
std::pair<bool, QString> DBManager::import_catalog(const QString &file_path,
        const bool overwrite)
{
    QTemporaryDir tmp;
    const auto new_path = tmp.filePath("cat.kscat");
    QFile::copy(file_path, new_path);
 
    QFile file{ new_path };
    if (!file.open(QIODevice::ReadOnly))
        return { false, i18n("Catalog file is not readable.") };
    file.close();
 
    QSqlQuery query{ m_db };
 
    if (!query.exec(QString("ATTACH [%1] AS tmp").arg(new_path)))
    {
        m_db.commit();
        return { false,
                 i18n("Could not attach input file.<br>%1", query.lastError().text()) };
    }
 
    auto _ = gsl::finally([&]()
    {
        m_db.commit();
        query.exec("DETACH tmp");
    });
 
    if (!query.exec("PRAGMA tmp.application_id") || !query.next() ||
            query.value(0).toInt() != CatalogsDB::application_id)
        return { false, i18n("Invalid catalog file.") };
 
    if (!query.exec("PRAGMA tmp.user_version") || !query.next() ||
            query.value(0).toInt() < m_db_version)
    {
        const auto &success = migrate_db(query.value(0).toInt(), m_db, "tmp");
        if (!success.first)
            return { false, i18n("Could not migrate old catalog format.<br>%1",
                                 success.second) };
    }
 
    if (!query.exec("SELECT id FROM tmp.catalogs LIMIT 1") || !query.next())
        return { false,
                 i18n("Could read the catalog id.<br>%1", query.lastError().text()) };
 
    const auto id = query.value(0).toInt();
    query.finish();
 
    {
        const auto &found = get_catalog(id);
        if (found.first)
        {
            if (!overwrite && found.second.mut)
                return { false, i18n("Catalog already exists in the database!") };
 
            auto success = remove_catalog_force(id);
            if (!success.first)
                return success;
        }
    }
 
    m_db.transaction();
 
    if (!query.exec(
                "INSERT INTO catalogs (id, name, mut, enabled, precedence, author, source, "
                "description, version, color, license, maintainer, timestamp) SELECT id, "
                "name, mut, enabled, precedence, author, source, description, version, "
                "color, license, maintainer, timestamp FROM tmp.catalogs LIMIT 1") ||
            !query.exec(QString("CREATE TABLE cat_%1 AS SELECT * FROM tmp.cat").arg(id)))
        return { false,
                 i18n("Could not import the catalog.<br>%1", query.lastError().text()) };
 
    m_db.commit();
 
    if (!update_catalog_views() || !compile_master_catalog())
        return { false, i18n("Could not refresh the master catalog.<br>",
                             m_db.lastError().text()) };
 
    return { true, {} };
}
 
std::pair<bool, QString> DBManager::remove_catalog(const int id)
{
    if (id == SqlStatements::user_catalog_id)
        return { false, i18n("Removing the user catalog is not allowed.") };
 
    return remove_catalog_force(id);
}
 
std::pair<bool, QString> DBManager::remove_catalog_force(const int id)
{
    auto success = set_catalog_enabled(id, false);
    if (!success.first)
        return success;
 
    QSqlQuery remove_catalog{ m_db };
    remove_catalog.prepare(SqlStatements::remove_catalog);
    remove_catalog.bindValue(0, id);
 
    m_db.transaction();
 
    if (!remove_catalog.exec() || !remove_catalog.exec(SqlStatements::drop_catalog(id)))
    {
        m_db.rollback();
        return { false, i18n("Could not remove the catalog from the registry.<br>%1")
                 .arg(remove_catalog.lastError().text()) };
    }
 
    m_db.commit();
    // we don't have to refresh the master catalog because the disable
    // call already did this
 
    return { true, {} };
}
 
std::pair<bool, QString> DBManager::copy_objects(const int id_1, const int id_2)
{
    if (!(catalog_exists(id_1) && catalog_exists(id_2)))
        return { false, i18n("Both catalogs have to exist!") };
 
    if (!get_catalog(id_2).second.mut)
        return { false, i18n("Destination catalog has to be mutable!") };
 
    QSqlQuery query{ m_db };
 
    if (!query.exec(SqlStatements::move_objects(id_1, id_2)))
        return { false, query.lastError().text() };
 
    if (!query.exec(SqlStatements::set_catalog_all_objects(id_2)))
        return { false, query.lastError().text() };
 
    return { true, {} };
}
 
std::pair<bool, QString> DBManager::update_catalog_meta(const Catalog &cat)
{
    if (!catalog_exists(cat.id))
        return { false, i18n("Cannot update nonexisting catalog.") };
 
    QSqlQuery query{ m_db };
 
    query.prepare(SqlStatements::update_catalog_meta);
    query.bindValue(":name", cat.name);
    query.bindValue(":author", cat.author);
    query.bindValue(":source", cat.source);
    query.bindValue(":description", cat.description);
    query.bindValue(":id", cat.id);
    query.bindValue(":color", cat.color);
    query.bindValue(":license", cat.license);
    query.bindValue(":maintainer", cat.maintainer);
    query.bindValue(":timestamp", cat.timestamp);
 
    return { query.exec(), query.lastError().text() };
}
 
int DBManager::find_suitable_catalog_id()
{
    const auto &cats = get_catalogs(true);
 
    // find a gap in the ids to use
    const auto element = std::adjacent_find(
                             cats.cbegin(), cats.cend(), [](const auto & c1, const auto & c2)
    {
        return (c1.id >= CatalogsDB::custom_cat_min_id) &&
               (c2.id >= CatalogsDB::custom_cat_min_id) && (c2.id - c1.id) > 1;
    });
 
    return std::max(CatalogsDB::custom_cat_min_id,
                    (element == cats.cend() ? cats.back().id : element->id) + 1);
}
 
QString CatalogsDB::dso_db_path()
{
    return QDir(KSPaths::writableLocation(QStandardPaths::AppLocalDataLocation))
           .filePath(Options::dSOCatalogFilename());
}
 
std::pair<bool, Catalog> CatalogsDB::read_catalog_meta_from_file(const QString &path)
{
    QSqlDatabase db{ QSqlDatabase::addDatabase(
                         "QSQLITE", QString("tmp_%1_%2").arg(path).arg(get_connection_index())) };
    db.setDatabaseName(path);
 
    if (!db.open())
        return { false, {} };
 
    QSqlQuery query{ db };
 
    if (!query.exec("PRAGMA user_version") || !query.next() ||
            query.value(0).toInt() < SqlStatements::current_db_version)
    {
        QTemporaryDir tmp;
        const auto new_path = tmp.filePath("cat.kscat");
 
        QFile::copy(path, new_path);
        db.close();
 
        db.setDatabaseName(new_path);
        if (!db.open())
            return { false, {} };
 
        const auto &success = migrate_db(query.value(0).toInt(), db);
        if (!success.first)
            return { false, {} };
    }
 
    if (!query.exec(SqlStatements::get_first_catalog) || !query.first())
        return { false, {} };
 
    db.close();
    return { true, read_catalog(query) };
}
 
CatalogStatistics read_statistics(QSqlQuery &query)
{
    CatalogStatistics stats{};
    while (query.next())
    {
        stats.object_counts[(SkyObject::TYPE)query.value(0).toInt()] =
            query.value(1).toInt();
        stats.total_count += query.value(1).toInt();
    }
    return stats;
}
 
const std::pair<bool, CatalogStatistics> DBManager::get_master_statistics()
{
    QSqlQuery query{ m_db };
    if (!query.exec(SqlStatements::dso_count_by_type_master))
        return { false, {} };
 
    return { true, read_statistics(query) };
}
 
const std::pair<bool, CatalogStatistics>
DBManager::get_catalog_statistics(const int catalog_id)
{
    QSqlQuery query{ m_db };
    if (!query.exec(SqlStatements::dso_count_by_type(catalog_id)))
        return { false, {} };
 
    return { true, read_statistics(query) };
}
 
std::pair<bool, QString>
CatalogsDB::DBManager::add_objects(const int catalog_id,
                                   const CatalogObjectVector &objects)
{
    {
        const auto &success = get_catalog(catalog_id);
        if (!success.first)
            return { false, i18n("Catalog with id=%1 not found.", catalog_id) };
 
        if (!success.second.mut)
            return { false, i18n("Catalog is immutable!") };
    }
 
    m_db.transaction();
    QSqlQuery query{ m_db };
    for (const auto &object : objects)
    {
        SkyPoint tmp{ object.ra(), object.dec() };
        const auto trixel = SkyMesh::Create(m_htmesh_level)->index(&tmp);
 
        bind_catalogobject(query, catalog_id, object, trixel);
 
        if (!query.exec())
        {
            auto err = query.lastError().text();
            if (err.startsWith("UNIQUE"))
                err = i18n("The object is already in the catalog!");
 
            return { false, i18n("Could not insert object! %1", err) };
        }
    }
 
    return { m_db.commit() &&update_catalog_views() &&compile_master_catalog(),
             m_db.lastError().text() };
};
 
CatalogObjectList CatalogsDB::DBManager::find_objects_by_wildcard(const QString &wildcard,
        const int limit)
{
    QMutexLocker _{ &m_mutex };
 
    QSqlQuery query{ m_db };
    if (!query.prepare(SqlStatements::dso_by_wildcard()))
    {
        return {};
    }
    query.bindValue(":wildcard", wildcard);
    query.bindValue(":limit", limit);
 
    return fetch_objects(query);
};
 
std::tuple<bool, const QString, CatalogObjectList>
CatalogsDB::DBManager::general_master_query(const QString &where, const QString &order_by,
        const int limit)
{
    QMutexLocker _{ &m_mutex };
 
    QSqlQuery query{ m_db };
 
    if (!query.prepare(SqlStatements::dso_general_query(where, order_by)))
    {
        return { false, query.lastError().text(), {} };
    }
 
    query.bindValue(":limit", limit);
 
    return { false, "", fetch_objects(query) };
};
 
CatalogsDB::CatalogColorMap CatalogsDB::parse_color_string(const QString &str)
{
    CatalogsDB::CatalogColorMap colors{};
    if (str == "")
        return colors;
 
    const auto &parts = str.split(";");
    auto it           = parts.constBegin();
 
    if (it->length() > 0) // playing it save
        colors["default"] = *it;
 
    while (it != parts.constEnd())
    {
        const auto &scheme = *(++it);
        if (it != parts.constEnd())
        {
            const auto next = ++it;
            if (next == parts.constEnd())
                break;
 
            const auto &color = *next;
            colors[scheme]    = QColor(color);
        }
    }
 
    return colors;
}
 
QString get_name(const QColor &color)
{
    return color.isValid() ? color.name() : "";
}
 
QString CatalogsDB::to_color_string(CatalogColorMap colors)
{
    QStringList color_list;
 
    color_list << colors["default"].name();
    colors.erase("default");
 
    for (const auto &item : colors)
    {
        if (item.second.isValid())
        {
            color_list << item.first << item.second.name();
        }
    }
 
    return color_list.join(";");
}
 
ColorMap CatalogsDB::DBManager::get_catalog_colors()
{
    // no mutex b.c. this is read only
    QSqlQuery query{ m_db };
 
    ColorMap colors{};
 
    if (!query.exec(SqlStatements::get_colors))
        return colors;
 
    for (const auto &cat : DBManager::get_catalogs(true))
    {
        colors[cat.id] = parse_color_string(cat.color);
    }
 
    while (query.next())
    {
        const auto &catalog     = query.value("catalog").toInt();
        const auto &scheme      = query.value("scheme").toString();
        const auto &color       = query.value("color").toString();
        colors[catalog][scheme] = QColor(color);
    }
 
    return colors;
};
 
CatalogsDB::CatalogColorMap CatalogsDB::DBManager::get_catalog_colors(const int id)
{
    return get_catalog_colors()[id]; // good enough for now
};
 
std::pair<bool, QString>
CatalogsDB::DBManager::insert_catalog_colors(const int id, const CatalogColorMap &colors)
{
    QMutexLocker _{ &m_mutex };
 
    QSqlQuery query{ m_db };
 
    if (!query.prepare(SqlStatements::insert_color))
    {
        return { false, query.lastError().text() };
    }
 
    query.bindValue(":catalog", id);
    for (const auto &item : colors)
    {
        query.bindValue(":scheme", item.first);
        query.bindValue(":color", item.second);
 
        if (!query.exec())
            return { false, query.lastError().text() };
    }
 
    return { true, "" };
};