11 #include <QMutexLocker>
12 #include <QTemporaryDir>
13 #include <qsqldatabase.h>
14 #include "cachingdms.h"
15 #include "catalogsdb.h"
19 #include "final_action.h"
20 #include "sqlstatements.cpp"
22 using namespace CatalogsDB;
28 int get_connection_index()
30 static int connection_index = 0;
31 return connection_index++;
38 query.setForwardOnly(forward_only);
39 if (!
query.prepare(statement))
41 throw DatabaseError(
"Can't prepare query!", DatabaseError::ErrorType::PREPARE,
51 std::pair<bool, QString> migrate_db(
const int version,
QSqlDatabase &db,
54 if (prefix.size() > 0)
61 const auto success = add_ts.
exec(
QString(
"ALTER TABLE %1catalogs ADD COLUMN "
62 "timestamp DEFAULT NULL")
70 if (version < 3 && prefix ==
"")
73 const auto success = add_colors.
exec(SqlStatements::create_colors_table);
83 "QSQLITE",
QString(
"cat_%1_%2").arg(filename).arg(get_connection_index())) },
84 m_db_file{ *m_db_paths.insert(filename) }
93 DatabaseError::ErrorType::OPEN, m_db.lastError());
97 std::tie(m_db_version, m_htmesh_level, init) = get_db_meta();
99 if (!init && m_db_version > 0 && m_db_version < SqlStatements::current_db_version)
101 const auto &backup_path =
QString(
"%1.%2").
arg(m_db_file).
arg(
107 QString(
"Could not backup dso database before upgrading."),
108 DatabaseError::ErrorType::VERSION,
QSqlError{});
111 const auto &success = migrate_db(m_db_version, m_db);
114 m_db_version = SqlStatements::current_db_version;
116 version_query.
prepare(SqlStatements::update_version);
117 version_query.bindValue(
":version", m_db_version);
119 if (!version_query.exec())
122 DatabaseError::ErrorType::VERSION,
123 version_query.lastError());
128 QString(
"Wrong database version. Expected %1 and got %2 and "
129 "migration is not possible.")
130 .arg(SqlStatements::current_db_version)
132 DatabaseError::ErrorType::VERSION, success.second);
136 master_exists.
exec(SqlStatements::exists_master);
137 const bool master_does_exist = master_exists.next();
138 master_exists.finish();
140 if (init || !master_does_exist)
142 if (!initialize_db())
145 DatabaseError::ErrorType::INIT, m_db.lastError());
148 if (!catalog_exists(SqlStatements::user_catalog_id))
151 register_catalog(SqlStatements::user_catalog_id,
152 SqlStatements::user_catalog_name,
true,
true, 1);
156 DatabaseError::ErrorType::CREATE_CATALOG, res.second);
160 if (!update_catalog_views())
163 DatabaseError::ErrorType::CREATE_CATALOG,
167 if (!compile_master_catalog())
170 DatabaseError::ErrorType::CREATE_MASTER,
175 m_q_cat_by_id = make_query(m_db, SqlStatements::get_catalog_by_id,
true);
176 m_q_obj_by_trixel = make_query(m_db, SqlStatements::dso_by_trixel,
false);
177 m_q_obj_by_trixel_no_nulls = make_query(m_db, SqlStatements::dso_by_trixel_no_nulls,
false);
178 m_q_obj_by_trixel_null_mag = make_query(m_db, SqlStatements::dso_by_trixel_null_mag,
false);
179 m_q_obj_by_name = make_query(m_db, SqlStatements::dso_by_name,
true);
180 m_q_obj_by_name_exact = make_query(m_db, SqlStatements::dso_by_name_exact,
true);
181 m_q_obj_by_lim = make_query(m_db, SqlStatements::dso_by_lim,
true);
182 m_q_obj_by_maglim = make_query(m_db, SqlStatements::dso_by_maglim,
true);
183 m_q_obj_by_maglim_and_type =
184 make_query(m_db, SqlStatements::dso_by_maglim_and_type,
true);
185 m_q_obj_by_oid = make_query(m_db, SqlStatements::dso_by_oid,
true);
190 bool DBManager::initialize_db()
192 if (m_db_version < 0 || m_htmesh_level < 1)
193 throw std::runtime_error(
"DBManager not initialized properly, m_db_vesion and "
194 "m_htmesh_level have to be set.");
196 if (!m_db.
exec(SqlStatements::create_meta_table).
isActive())
199 if (!m_db.
exec(SqlStatements::create_colors_table).
isActive())
203 meta_query.prepare(SqlStatements::set_meta);
204 meta_query.bindValue(0, m_db_version);
205 meta_query.bindValue(1, m_htmesh_level);
206 meta_query.bindValue(2,
false);
208 if (!meta_query.exec())
211 return m_db.
exec(SqlStatements::create_catalog_list_table).
isActive();
214 std::tuple<int, int, bool> DBManager::get_db_meta()
216 auto query = m_db.
exec(SqlStatements::get_meta);
219 return {
query.value(0).toInt(),
query.value(1).toInt(),
220 query.value(2).toBool() };
222 return { SqlStatements::current_db_version, SqlStatements::default_htmesh_level,
226 std::vector<int> DBManager::get_catalog_ids(
bool include_disabled)
228 auto query = m_db.
exec(include_disabled ? SqlStatements::get_all_catalog_ids :
229 SqlStatements::get_catalog_ids);
231 std::vector<int> ids;
235 int id =
query.value(0).toInt();
244 const auto &ids = get_catalog_ids();
246 auto _ = gsl::finally([&]() { m_db.
commit(); });
251 query.exec(
QString(
"DROP VIEW IF EXISTS ") + SqlStatements::all_catalog_view);
262 view += SqlStatements::all_catalog_view;
266 for (
auto *field : SqlStatements::catalog_collumns)
268 prefixed <<
QString(
"c.") + field;
271 QString prefixed_joined = prefixed.join(
",");
276 catalog_queries << SqlStatements::all_catalog_view_body(
277 prefixed_joined, SqlStatements::catalog_prefix,
id);
282 catalog_queries << SqlStatements::all_catalog_view_body(
283 prefixed_joined, SqlStatements::catalog_prefix, 0) +
287 view += catalog_queries.join(
"\nUNION ALL\n");
288 result &= query.exec(view);
294 query.bindValue(
":id", cat.
id);
295 query.bindValue(
":name", cat.
name);
296 query.bindValue(
":mut", cat.
mut);
297 query.bindValue(
":enabled", cat.
enabled);
298 query.bindValue(
":precedence", cat.
precedence);
299 query.bindValue(
":author", cat.
author);
300 query.bindValue(
":source", cat.
source);
302 query.bindValue(
":version", cat.
version);
303 query.bindValue(
":color", cat.
color);
304 query.bindValue(
":license", cat.
license);
305 query.bindValue(
":maintainer", cat.
maintainer);
306 query.bindValue(
":timestamp", cat.
timestamp);
310 const int id,
const QString &name,
const bool mut,
const bool enabled,
311 const double precedence,
const QString &author,
const QString &source,
312 const QString &description,
const int version,
const QString &color,
315 return register_catalog({ id, name, precedence, author, source, description, mut,
316 enabled, version, color, license, maintainer, timestamp });
322 return {
false,
i18n(
"Catalog with that ID already exists.") };
326 if (!
query.exec(SqlStatements::create_catalog_table(cat.
id)))
328 return {
false,
query.lastError().text() };
331 query.prepare(SqlStatements::insert_catalog);
332 bind_catalog(query, cat);
334 return {
query.exec(),
query.lastError().text() };
339 auto _ = gsl::finally([&]() { m_db.
commit(); });
343 if (!query.exec(SqlStatements::drop_master))
348 if (!query.exec(SqlStatements::create_master))
354 success &= query.exec(SqlStatements::create_master_trixel_index);
355 success &= query.exec(SqlStatements::create_master_mag_index);
356 success &= query.exec(SqlStatements::create_master_type_index);
357 success &= query.exec(SqlStatements::create_master_name_index);
363 return { query.value(
"id").toInt(),
364 query.value(
"name").toString(),
365 query.value(
"precedence").toDouble(),
366 query.value(
"author").toString(),
367 query.value(
"source").toString(),
368 query.value(
"description").toString(),
369 query.value(
"mut").toBool(),
370 query.value(
"enabled").toBool(),
371 query.value(
"version").toInt(),
372 query.value(
"color").toString(),
373 query.value(
"license").toString(),
374 query.value(
"maintainer").toString(),
375 query.value(
"timestamp").toDateTime() };
383 if (!m_q_cat_by_id.
exec())
384 return {
false, {} };
386 if (!m_q_cat_by_id.
next())
387 return {
false, {} };
389 Catalog cat{ read_catalog(m_q_cat_by_id) };
392 return {
true, cat };
399 auto end = gsl::finally([&]() { m_q_cat_by_id.
finish(); });
401 if (!m_q_cat_by_id.
exec())
404 return m_q_cat_by_id.
next();
423 const double ra =
query.value(2).toDouble();
424 const double dec =
query.value(3).toDouble();
425 const float mag =
query.isNull(4) ? NaN::f :
query.value(4).toFloat();
428 const QString catalog_identifier =
query.value(7).toString();
429 const float major =
query.value(8).toFloat();
430 const float minor =
query.value(9).toFloat();
431 const double position_angle =
query.value(10).toDouble();
432 const float flux =
query.value(11).toFloat();
433 const int catalog_id =
query.value(12).toInt();
436 mag,
name, long_name, catalog_identifier,
437 catalog_id, major, minor, position_angle,
441 CatalogObjectVector DBManager::_get_objects_in_trixel_generic(
QSqlQuery& query,
const int trixel)
444 query.bindValue(0, trixel);
448 QString(
"The by-trixel query for objects in trixel=%1 failed.")
450 DatabaseError::ErrorType::UNKNOWN,
query.lastError());
452 CatalogObjectVector objects;
462 objects.reserve(count);
464 while (
query.previous())
466 objects.push_back(read_catalogobject(query));
475 CatalogObjectList DBManager::fetch_objects(
QSqlQuery &query)
const
477 CatalogObjectList objects;
478 auto _ = gsl::finally([&]() {
query.finish(); });
482 if (!
query.isActive())
485 objects.push_back(read_catalogobject(query));
491 const bool exactMatchOnly)
497 return CatalogObjectList();
500 m_q_obj_by_name_exact.
bindValue(
":name", name);
501 CatalogObjectList objs { fetch_objects(m_q_obj_by_name_exact) };
503 if ((limit == 1 && objs.size() > 0) || exactMatchOnly)
506 Q_ASSERT(objs.size() <= 1);
508 m_q_obj_by_name.
bindValue(
":name", name);
509 m_q_obj_by_name.
bindValue(
":limit",
int(limit - objs.size()));
511 CatalogObjectList moreObjects = fetch_objects(m_q_obj_by_name);
512 moreObjects.splice(moreObjects.begin(), objs);
518 const QString &name,
const int limit)
522 query.prepare(SqlStatements::dso_by_name_and_catalog(catalog_id));
523 query.bindValue(
":name", name);
524 query.bindValue(
":limit", limit);
525 query.bindValue(
":catalog", catalog_id);
527 return fetch_objects(query);
530 std::pair<bool, CatalogObject> DBManager::read_first_object(
QSqlQuery &query)
const
532 if (!query.exec() || !query.first())
533 return {
false, {} };
535 return {
true, read_catalogobject(query) };
543 auto f = gsl::finally([&]() {
547 return read_first_object(m_q_obj_by_oid);
551 const int catalog_id)
556 query.prepare(SqlStatements::dso_by_oid_and_catalog(catalog_id));
557 query.bindValue(0, oid);
559 return read_first_object(query);
565 m_q_obj_by_maglim.
bindValue(
":maglim", maglim);
566 m_q_obj_by_maglim.
bindValue(
":limit", limit);
568 return fetch_objects(m_q_obj_by_maglim);
576 return fetch_objects(m_q_obj_by_lim);
582 m_q_obj_by_maglim_and_type.
bindValue(
":type", type);
583 m_q_obj_by_maglim_and_type.
bindValue(
":limit", limit);
584 m_q_obj_by_maglim_and_type.
bindValue(
":maglim", maglim);
586 return fetch_objects(m_q_obj_by_maglim_and_type);
590 const int catalog_id,
float maglim,
595 query.prepare(SqlStatements::dso_in_catalog_by_maglim(catalog_id));
596 query.bindValue(
":type", type);
597 query.bindValue(
":limit", limit);
598 query.bindValue(
":maglim", maglim);
599 return fetch_objects(query);
606 return {
false,
i18n(
"Catalog could not be found.") };
608 const auto &cat = success.second;
613 query.prepare(SqlStatements::enable_disable_catalog);
614 query.bindValue(
":enabled", enabled);
615 query.bindValue(
":id",
id);
623 auto ids = get_catalog_ids(include_disabled);
624 std::vector<Catalog> catalogs;
625 catalogs.reserve(ids.size());
627 std::transform(ids.cbegin(), ids.cend(), std::back_inserter(catalogs),
629 const auto &found = get_catalog(id);
635 QString(
"Could not retrieve the catalog with id=%1").arg(id));
641 inline void bind_catalogobject(
QSqlQuery &query,
const int catalog_id,
645 const float a,
const float b,
const double pa,
646 const float flux, Trixel trixel,
649 query.prepare(SqlStatements::insert_dso(catalog_id));
651 query.bindValue(
":hash", new_id);
652 query.bindValue(
":oid", new_id);
653 query.bindValue(
":type",
static_cast<int>(t));
654 query.bindValue(
":ra", r.
Degrees());
655 query.bindValue(
":dec", d.Degrees());
656 query.bindValue(
":magnitude", (m < 99 && !std::isnan(m)) ? m :
QVariant{});
657 query.bindValue(
":name", n);
658 query.bindValue(
":long_name", lname.
length() > 0 ? lname :
QVariant{});
659 query.bindValue(
":catalog_identifier",
660 catalog_identifier.
length() > 0 ? catalog_identifier :
QVariant{});
661 query.bindValue(
":major_axis", a > 0 ? a :
QVariant{});
663 query.bindValue(
":position_angle", pa > 0 ? pa :
QVariant{});
665 query.bindValue(
":trixel", trixel);
666 query.bindValue(
":catalog", catalog_id);
669 inline void bind_catalogobject(
QSqlQuery &query,
const int catalog_id,
686 std::pair<bool, QString>
690 const float a,
const float b,
const double pa,
const float flux)
695 return {
false,
i18n(
"Catalog with id=%1 not found.", catalog_id) };
697 if (!success.second.mut)
698 return {
false,
i18n(
"Catalog is immutable!") };
707 bind_catalogobject(query, catalog_id, t, r, d, n, m, lname, catalog_identifier, a, b,
708 pa, flux, trixel, new_id);
712 auto err = query.lastError().text();
713 if (err.startsWith(
"UNIQUE"))
714 err =
i18n(
"The object is already in the catalog!");
716 return {
false,
i18n(
"Could not insert object! %1", err) };
728 query.prepare(SqlStatements::remove_dso(catalog_id));
729 query.bindValue(
":oid",
id);
732 return {
false, query.lastError().text() };
742 return {
false,
i18n(
"Catalog could not be found.") };
744 QFile file{ file_path };
746 return {
false,
i18n(
"Output file is not writable.") };
752 if (!query.exec(
QString(
"ATTACH [%1] AS tmp").arg(file_path)))
754 i18n(
"Could not attach output file.<br>%1", query.lastError().text()) };
757 auto _ = gsl::finally([&]() {
759 query.exec(
"DETACH tmp");
763 QString(
"CREATE TABLE tmp.cat AS SELECT * FROM cat_%1").arg(catalog_id)))
764 return {
false,
i18n(
"Could not copy catalog to output file.<br>%1")
765 .
arg(query.lastError().text()) };
767 if (!query.exec(SqlStatements::create_catalog_registry(
"tmp.catalogs")))
768 return {
false,
i18n(
"Could not create catalog registry in output file.<br>%1")
769 .
arg(query.lastError().text()) };
771 query.prepare(SqlStatements::insert_into_catalog_registry(
"tmp.catalogs"));
773 auto cat = found.second;
775 bind_catalog(query, cat);
780 i18n(
"Could not insert catalog into registry in output file.<br>%1")
781 .
arg(query.lastError().text()) };
784 if (!query.exec(
QString(
"PRAGMA tmp.user_version = %1").arg(m_db_version)))
786 return {
false,
i18n(
"Could not insert set exported database version.<br>%1")
787 .
arg(query.lastError().text()) };
790 if (!query.exec(
QString(
"PRAGMA tmp.application_id = %1").arg(application_id)))
793 i18n(
"Could not insert set exported database application id.<br>%1")
794 .
arg(query.lastError().text()) };
801 const bool overwrite)
804 const auto new_path = tmp.
filePath(
"cat.kscat");
807 QFile file{ new_path };
809 return {
false,
i18n(
"Catalog file is not readable.") };
814 if (!query.exec(
QString(
"ATTACH [%1] AS tmp").arg(new_path)))
818 i18n(
"Could not attach input file.<br>%1", query.lastError().text()) };
821 auto _ = gsl::finally([&]() {
823 query.exec(
"DETACH tmp");
826 if (!query.exec(
"PRAGMA tmp.application_id") || !query.next() ||
827 query.value(0).toInt() != CatalogsDB::application_id)
828 return {
false,
i18n(
"Invalid catalog file.") };
830 if (!query.exec(
"PRAGMA tmp.user_version") || !query.next() ||
831 query.value(0).toInt() < m_db_version)
833 const auto &success = migrate_db(query.value(0).toInt(), m_db,
"tmp");
835 return {
false,
i18n(
"Could not migrate old catalog format.<br>%1",
839 if (!query.exec(
"SELECT id FROM tmp.catalogs LIMIT 1") || !query.next())
841 i18n(
"Could read the catalog id.<br>%1", query.lastError().text()) };
843 const auto id = query.value(0).toInt();
850 if (!overwrite && found.second.mut)
851 return {
false,
i18n(
"Catalog already exists in the database!") };
853 auto success = remove_catalog_force(
id);
862 "INSERT INTO catalogs (id, name, mut, enabled, precedence, author, source, "
863 "description, version, color, license, maintainer, timestamp) SELECT id, "
864 "name, mut, enabled, precedence, author, source, description, version, "
865 "color, license, maintainer, timestamp FROM tmp.catalogs LIMIT 1") ||
866 !query.exec(
QString(
"CREATE TABLE cat_%1 AS SELECT * FROM tmp.cat").arg(
id)))
868 i18n(
"Could not import the catalog.<br>%1", query.lastError().text()) };
873 return {
false,
i18n(
"Could not refresh the master catalog.<br>",
881 if (
id == SqlStatements::user_catalog_id)
882 return {
false,
i18n(
"Removing the user catalog is not allowed.") };
884 return remove_catalog_force(
id);
887 std::pair<bool, QString> DBManager::remove_catalog_force(
const int id)
902 return {
false,
i18n(
"Could not remove the catalog from the registry.<br>%1")
916 return {
false,
i18n(
"Both catalogs have to exist!") };
919 return {
false,
i18n(
"Destination catalog has to be mutable!") };
923 if (!query.exec(SqlStatements::move_objects(id_1, id_2)))
924 return {
false, query.lastError().text() };
926 if (!query.exec(SqlStatements::set_catalog_all_objects(id_2)))
927 return {
false, query.lastError().text() };
935 return {
false,
i18n(
"Cannot update nonexisting catalog.") };
939 query.prepare(SqlStatements::update_catalog_meta);
940 query.bindValue(
":name", cat.
name);
941 query.bindValue(
":author", cat.
author);
942 query.bindValue(
":source", cat.
source);
944 query.bindValue(
":id", cat.
id);
945 query.bindValue(
":color", cat.
color);
946 query.bindValue(
":license", cat.
license);
947 query.bindValue(
":maintainer", cat.
maintainer);
948 query.bindValue(
":timestamp", cat.
timestamp);
950 return { query.exec(), query.lastError().text() };
958 const auto element = std::adjacent_find(
959 cats.cbegin(), cats.cend(), [](
const auto &c1,
const auto &c2) {
960 return (c1.id >= CatalogsDB::custom_cat_min_id) &&
961 (c2.id >= CatalogsDB::custom_cat_min_id) && (c2.id - c1.id) > 1;
964 return std::max(CatalogsDB::custom_cat_min_id,
965 (element == cats.cend() ? cats.back().id : element->id) + 1);
968 QString CatalogsDB::dso_db_path()
971 .
filePath(Options::dSOCatalogFilename());
974 std::pair<bool, Catalog> CatalogsDB::read_catalog_meta_from_file(
const QString &path)
977 "QSQLITE",
QString(
"tmp_%1_%2").arg(path).arg(get_connection_index())) };
981 return {
false, {} };
985 if (!
query.exec(
"PRAGMA user_version") || !
query.next() ||
986 query.value(0).toInt() < SqlStatements::current_db_version)
989 const auto new_path = tmp.
filePath(
"cat.kscat");
996 return {
false, {} };
998 const auto &success = migrate_db(
query.value(0).toInt(), db);
1000 return {
false, {} };
1003 if (!
query.exec(SqlStatements::get_first_catalog) || !
query.first())
1004 return {
false, {} };
1007 return {
true, read_catalog(query) };
1013 while (
query.next())
1016 query.value(1).toInt();
1017 stats.total_count +=
query.value(1).toInt();
1025 if (!query.exec(SqlStatements::dso_count_by_type_master))
1026 return {
false, {} };
1028 return {
true, read_statistics(query) };
1031 const std::pair<bool, CatalogStatistics>
1035 if (!query.exec(SqlStatements::dso_count_by_type(catalog_id)))
1036 return {
false, {} };
1038 return {
true, read_statistics(query) };
1041 std::pair<bool, QString>
1043 const CatalogObjectVector &objects)
1046 const auto &success = get_catalog(catalog_id);
1048 return {
false,
i18n(
"Catalog with id=%1 not found.", catalog_id) };
1050 if (!success.second.mut)
1051 return {
false,
i18n(
"Catalog is immutable!") };
1056 for (
const auto &
object : objects)
1058 SkyPoint tmp{
object.ra(),
object.dec() };
1061 bind_catalogobject(query, catalog_id,
object, trixel);
1065 auto err = query.lastError().text();
1066 if (err.startsWith(
"UNIQUE"))
1067 err =
i18n(
"The object is already in the catalog!");
1069 return {
false,
i18n(
"Could not insert object! %1", err) };
1073 return { m_db.commit() && update_catalog_views() && compile_master_catalog(),
1074 m_db.lastError().text() };
1083 if (!query.prepare(SqlStatements::dso_by_wildcard()))
1087 query.bindValue(
":wildcard", wildcard);
1088 query.bindValue(
":limit", limit);
1090 return fetch_objects(query);
1093 std::tuple<bool, const QString, CatalogObjectList>
1101 if (!query.prepare(SqlStatements::dso_general_query(where, order_by)))
1103 return {
false, query.lastError().text(), {} };
1106 query.bindValue(
":limit", limit);
1108 return {
false,
"", fetch_objects(query) };
1111 CatalogsDB::CatalogColorMap CatalogsDB::parse_color_string(
const QString &str)
1113 CatalogsDB::CatalogColorMap colors{};
1117 const auto &parts = str.
split(
";");
1120 if (it->length() > 0)
1121 colors[
"default"] = *it;
1123 while (it != parts.constEnd())
1125 const auto &scheme = *(++it);
1126 if (it != parts.constEnd())
1128 const auto next = ++it;
1129 if (next == parts.constEnd())
1132 const auto &color = *next;
1133 colors[scheme] =
QColor(color);
1145 QString CatalogsDB::to_color_string(CatalogColorMap colors)
1149 color_list << colors[
"default"].name();
1150 colors.
erase(
"default");
1152 for (
const auto &item : colors)
1154 if (item.second.isValid())
1156 color_list << item.
first << item.second.name();
1160 return color_list.
join(
";");
1170 if (!query.exec(SqlStatements::get_colors))
1175 colors[cat.
id] = parse_color_string(cat.
color);
1178 while (query.next())
1180 const auto &catalog = query.value(
"catalog").toInt();
1181 const auto &scheme = query.value(
"scheme").toString();
1182 const auto &color = query.value(
"color").toString();
1183 colors[catalog][scheme] =
QColor(color);
1191 return get_catalog_colors()[id];
1194 std::pair<bool, QString>
1201 if (!query.prepare(SqlStatements::insert_color))
1203 return {
false, query.lastError().text() };
1206 query.bindValue(
":catalog",
id);
1207 for (
const auto &item : colors)
1209 query.bindValue(
":scheme", item.first);
1210 query.bindValue(
":color", item.second);
1213 return {
false, query.lastError().text() };
1216 return {
true,
"" };