1 /*
2 SPDX-FileCopyrightText: 2021 Valentin Boettcher <hiro at protagon.space; @hiro98:tchncs.de>
3
4 SPDX-License-Identifier: GPL-2.0-or-later
5 */
6
7 #include <limits>
8 #include <cmath>
9 #include <QSqlDriver>
10 #include <QSqlRecord>
11 #include <QMutexLocker>
12 #include <qsqldatabase.h>
13 #include "cachingdms.h"
14 #include "catalogsdb.h"
15 #include "kspaths.h"
16 #include "skymesh.h"
17 #include "Options.h"
18 #include "final_action.h"
19 #include "sqlstatements.cpp"
20
21 using namespace CatalogsDB;
22 QSet<QString> DBManager::m_db_paths{};
23
24 /**
25 * Get an increasing index for new connections.
26 */
get_connection_index()27 int get_connection_index()
28 {
29 static int connection_index = 0;
30 return connection_index++;
31 }
32
make_query(QSqlDatabase & db,const QString & statement,const bool forward_only)33 QSqlQuery make_query(QSqlDatabase &db, const QString &statement, const bool forward_only)
34 {
35 QSqlQuery query{ db };
36
37 query.setForwardOnly(forward_only);
38 if (!query.prepare(statement))
39 {
40 throw DatabaseError("Can't prepare query!", DatabaseError::ErrorType::PREPARE,
41 query.lastError());
42 };
43
44 return query;
45 }
46
47 /**
48 * Migrate the database from \p version to the current version.
49 */
migrate_db(const int version,QSqlDatabase & db,QString prefix="")50 std::pair<bool, QString> migrate_db(const int version, QSqlDatabase &db,
51 QString prefix = "")
52 {
53 if (prefix.size() > 0)
54 prefix += ".";
55
56 // we have to add the timestamp collumn to the catalogs
57 if (version < 2)
58 {
59 QSqlQuery add_ts{ db };
60 const auto success = add_ts.exec(QString("ALTER TABLE %1catalogs ADD COLUMN "
61 "timestamp DEFAULT NULL")
62 .arg(prefix));
63 if (!success)
64 return { false, add_ts.lastError().text() };
65 }
66
67 // adding the color selector table; this only applies for the
68 // master database
69 if (version < 3 && prefix == "")
70 {
71 QSqlQuery add_colors{ db };
72 const auto success = add_colors.exec(SqlStatements::create_colors_table);
73 if (!success)
74 return { false, add_colors.lastError().text() };
75 }
76
77 return { true, "" };
78 }
79
DBManager(const QString & filename)80 DBManager::DBManager(const QString &filename)
81 : m_db{ QSqlDatabase::addDatabase(
82 "QSQLITE", QString("cat_%1_%2").arg(filename).arg(get_connection_index())) },
83 m_db_file{ *m_db_paths.insert(filename) }
84
85 {
86 m_db.setDatabaseName(m_db_file);
87
88 // we are throwing here, because errors at this stage should be fatal
89 if (!m_db.open())
90 {
91 throw DatabaseError(QString("Cannot open CatalogDatabase '%1'!").arg(m_db_file),
92 DatabaseError::ErrorType::OPEN, m_db.lastError());
93 }
94
95 bool init = false;
96 std::tie(m_db_version, m_htmesh_level, init) = get_db_meta();
97
98 if (!init && m_db_version > 0 && m_db_version < SqlStatements::current_db_version)
99 {
100 const auto &backup_path = QString("%1.%2").arg(m_db_file).arg(
101 QDateTime::currentDateTime().toString("dd_MMMM_yy_hh_mm_sss_zzz"));
102
103 if (!QFile::copy(m_db_file, backup_path))
104 {
105 throw DatabaseError(
106 QString("Could not backup dso database before upgrading."),
107 DatabaseError::ErrorType::VERSION, QSqlError{});
108 }
109
110 const auto &success = migrate_db(m_db_version, m_db);
111 if (success.first)
112 {
113 m_db_version = SqlStatements::current_db_version;
114 QSqlQuery version_query{ m_db };
115 version_query.prepare(SqlStatements::update_version);
116 version_query.bindValue(":version", m_db_version);
117
118 if (!version_query.exec())
119 {
120 throw DatabaseError(QString("Could not update the database version."),
121 DatabaseError::ErrorType::VERSION,
122 version_query.lastError());
123 }
124 }
125 else
126 throw DatabaseError(
127 QString("Wrong database version. Expected %1 and got %2 and "
128 "migration is not possible.")
129 .arg(SqlStatements::current_db_version)
130 .arg(m_db_version),
131 DatabaseError::ErrorType::VERSION, success.second);
132 }
133
134 QSqlQuery master_exists{ m_db };
135 master_exists.exec(SqlStatements::exists_master);
136 const bool master_does_exist = master_exists.next();
137 master_exists.finish();
138
139 if (init || !master_does_exist)
140 {
141 if (!initialize_db())
142 {
143 throw DatabaseError(QString("Could not initialize database."),
144 DatabaseError::ErrorType::INIT, m_db.lastError());
145 }
146
147 if (!catalog_exists(SqlStatements::user_catalog_id))
148 {
149 const auto &res =
150 register_catalog(SqlStatements::user_catalog_id,
151 SqlStatements::user_catalog_name, true, true, 1);
152 if (!res.first)
153 {
154 throw DatabaseError(QString("Could not create user database."),
155 DatabaseError::ErrorType::CREATE_CATALOG, res.second);
156 }
157 }
158
159 if (!update_catalog_views())
160 {
161 throw DatabaseError(QString("Unable to create combined catalog view!"),
162 DatabaseError::ErrorType::CREATE_CATALOG,
163 m_db.lastError());
164 }
165
166 if (!compile_master_catalog())
167 {
168 throw DatabaseError(QString("Unable to create master catalog!"),
169 DatabaseError::ErrorType::CREATE_MASTER,
170 m_db.lastError());
171 }
172 }
173
174 m_q_cat_by_id = make_query(m_db, SqlStatements::get_catalog_by_id, true);
175 m_q_obj_by_trixel = make_query(m_db, SqlStatements::dso_by_trixel, false);
176 m_q_obj_by_name = make_query(m_db, SqlStatements::dso_by_name, true);
177 m_q_obj_by_name_exact = make_query(m_db, SqlStatements::dso_by_name_exact, true);
178 m_q_obj_by_maglim = make_query(m_db, SqlStatements::dso_by_maglim, true);
179 m_q_obj_by_maglim_and_type =
180 make_query(m_db, SqlStatements::dso_by_maglim_and_type, true);
181 m_q_obj_by_oid = make_query(m_db, SqlStatements::dso_by_oid, true);
182 };
183
DBManager(const DBManager & other)184 DBManager::DBManager(const DBManager &other) : DBManager::DBManager{ other.m_db_file } {};
185
initialize_db()186 bool DBManager::initialize_db()
187 {
188 if (m_db_version < 0 || m_htmesh_level < 1)
189 throw std::runtime_error("DBManager not initialized properly, m_db_vesion and "
190 "m_htmesh_level have to be set.");
191
192 if (!m_db.exec(SqlStatements::create_meta_table).isActive())
193 return false;
194
195 if (!m_db.exec(SqlStatements::create_colors_table).isActive())
196 return false;
197
198 QSqlQuery meta_query{ m_db };
199 meta_query.prepare(SqlStatements::set_meta);
200 meta_query.bindValue(0, m_db_version);
201 meta_query.bindValue(1, m_htmesh_level);
202 meta_query.bindValue(2, false);
203
204 if (!meta_query.exec())
205 return false;
206
207 return m_db.exec(SqlStatements::create_catalog_list_table).isActive();
208 }
209
get_db_meta()210 std::tuple<int, int, bool> DBManager::get_db_meta()
211 {
212 auto query = m_db.exec(SqlStatements::get_meta);
213
214 if (query.first())
215 return { query.value(0).toInt(), query.value(1).toInt(),
216 query.value(2).toBool() };
217 else
218 return { SqlStatements::current_db_version, SqlStatements::default_htmesh_level,
219 true };
220 }
221
get_catalog_ids(bool include_disabled)222 std::vector<int> DBManager::get_catalog_ids(bool include_disabled)
223 {
224 auto query = m_db.exec(include_disabled ? SqlStatements::get_all_catalog_ids :
225 SqlStatements::get_catalog_ids);
226
227 std::vector<int> ids;
228
229 while (query.next())
230 {
231 int id = query.value(0).toInt();
232 ids.push_back(id);
233 }
234
235 return ids;
236 }
237
update_catalog_views()238 bool DBManager::update_catalog_views()
239 {
240 const auto &ids = get_catalog_ids();
241 bool result = true;
242 auto _ = gsl::finally([&]() { m_db.commit(); });
243
244 m_db.transaction();
245 QSqlQuery query{ m_db };
246 result &=
247 query.exec(QString("DROP VIEW IF EXISTS ") + SqlStatements::all_catalog_view);
248
249 if (!result)
250 {
251 return result;
252 }
253
254 QString view{
255 "CREATE VIEW "
256 }; // small enough to be included here and not in sqlstatements
257
258 view += SqlStatements::all_catalog_view;
259 view += " AS\n";
260
261 QStringList prefixed{};
262 for (auto *field : SqlStatements::catalog_collumns)
263 {
264 prefixed << QString("c.") + field;
265 }
266
267 QString prefixed_joined = prefixed.join(",");
268
269 QStringList catalog_queries{};
270 for (auto id : ids)
271 {
272 catalog_queries << SqlStatements::all_catalog_view_body(
273 prefixed_joined, SqlStatements::catalog_prefix, id);
274 }
275
276 if (ids.size() == 0)
277 {
278 catalog_queries << SqlStatements::all_catalog_view_body(
279 prefixed_joined, SqlStatements::catalog_prefix, 0) +
280 " WHERE FALSE"; // we blackhole the query
281 }
282
283 view += catalog_queries.join("\nUNION ALL\n");
284 result &= query.exec(view);
285 return result;
286 }
287
bind_catalog(QSqlQuery & query,const Catalog & cat)288 void bind_catalog(QSqlQuery &query, const Catalog &cat)
289 {
290 query.bindValue(":id", cat.id);
291 query.bindValue(":name", cat.name);
292 query.bindValue(":mut", cat.mut);
293 query.bindValue(":enabled", cat.enabled);
294 query.bindValue(":precedence", cat.precedence);
295 query.bindValue(":author", cat.author);
296 query.bindValue(":source", cat.source);
297 query.bindValue(":description", cat.description);
298 query.bindValue(":version", cat.version);
299 query.bindValue(":color", cat.color);
300 query.bindValue(":license", cat.license);
301 query.bindValue(":maintainer", cat.maintainer);
302 query.bindValue(":timestamp", cat.timestamp);
303 }
304
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)305 std::pair<bool, QString> DBManager::register_catalog(
306 const int id, const QString &name, const bool mut, const bool enabled,
307 const double precedence, const QString &author, const QString &source,
308 const QString &description, const int version, const QString &color,
309 const QString &license, const QString &maintainer, const QDateTime ×tamp)
310 {
311 return register_catalog({ id, name, precedence, author, source, description, mut,
312 enabled, version, color, license, maintainer, timestamp });
313 }
314
register_catalog(const Catalog & cat)315 std::pair<bool, QString> DBManager::register_catalog(const Catalog &cat)
316 {
317 if (catalog_exists(cat.id))
318 return { false, i18n("Catalog with that ID already exists.") };
319
320 QSqlQuery query{ m_db };
321
322 if (!query.exec(SqlStatements::create_catalog_table(cat.id)))
323 {
324 return { false, query.lastError().text() };
325 }
326
327 query.prepare(SqlStatements::insert_catalog);
328 bind_catalog(query, cat);
329
330 return { query.exec(), query.lastError().text() };
331 };
332
compile_master_catalog()333 bool DBManager::compile_master_catalog()
334 {
335 auto _ = gsl::finally([&]() { m_db.commit(); });
336 QSqlQuery query{ m_db };
337 m_db.transaction();
338
339 if (!query.exec(SqlStatements::drop_master))
340 {
341 return false;
342 }
343
344 if (!query.exec(SqlStatements::create_master))
345 {
346 return false;
347 }
348
349 bool success = true;
350 success &= query.exec(SqlStatements::create_master_trixel_index);
351 success &= query.exec(SqlStatements::create_master_mag_index);
352 success &= query.exec(SqlStatements::create_master_type_index);
353 success &= query.exec(SqlStatements::create_master_name_index);
354 return success;
355 };
356
read_catalog(const QSqlQuery & query)357 const Catalog read_catalog(const QSqlQuery &query)
358 {
359 return { query.value("id").toInt(),
360 query.value("name").toString(),
361 query.value("precedence").toDouble(),
362 query.value("author").toString(),
363 query.value("source").toString(),
364 query.value("description").toString(),
365 query.value("mut").toBool(),
366 query.value("enabled").toBool(),
367 query.value("version").toInt(),
368 query.value("color").toString(),
369 query.value("license").toString(),
370 query.value("maintainer").toString(),
371 query.value("timestamp").toDateTime() };
372 }
373
get_catalog(const int id)374 const std::pair<bool, Catalog> DBManager::get_catalog(const int id)
375 {
376 QMutexLocker _{ &m_mutex };
377 m_q_cat_by_id.bindValue(0, id);
378
379 if (!m_q_cat_by_id.exec())
380 return { false, {} };
381
382 if (!m_q_cat_by_id.next())
383 return { false, {} };
384
385 Catalog cat{ read_catalog(m_q_cat_by_id) };
386
387 m_q_cat_by_id.finish();
388 return { true, cat };
389 }
390
catalog_exists(const int id)391 bool DBManager::catalog_exists(const int id)
392 {
393 QMutexLocker _{ &m_mutex };
394 m_q_cat_by_id.bindValue(0, id);
395 auto end = gsl::finally([&]() { m_q_cat_by_id.finish(); });
396
397 if (!m_q_cat_by_id.exec())
398 return false;
399
400 return m_q_cat_by_id.next();
401 }
402
count_rows(QSqlQuery & query)403 size_t count_rows(QSqlQuery &query)
404 {
405 size_t count{ 0 };
406 while (query.next())
407 {
408 count++;
409 }
410
411 return count;
412 }
413
read_catalogobject(const QSqlQuery & query) const414 CatalogObject DBManager::read_catalogobject(const QSqlQuery &query) const
415 {
416 const CatalogObject::oid id = query.value(0).toByteArray();
417 const SkyObject::TYPE type = static_cast<SkyObject::TYPE>(query.value(1).toInt());
418
419 const double ra = query.value(2).toDouble();
420 const double dec = query.value(3).toDouble();
421 const float mag = query.isNull(4) ? NaN::f : query.value(4).toFloat();
422 const QString name = query.value(5).toString();
423 const QString long_name = query.value(6).toString();
424 const QString catalog_identifier = query.value(7).toString();
425 const float major = query.value(8).toFloat();
426 const float minor = query.value(9).toFloat();
427 const double position_angle = query.value(10).toDouble();
428 const float flux = query.value(11).toFloat();
429 const int catalog_id = query.value(12).toInt();
430
431 return { id, type, dms(ra), dms(dec),
432 mag, name, long_name, catalog_identifier,
433 catalog_id, major, minor, position_angle,
434 flux, m_db_file };
435 }
436
get_objects_in_trixel(const int trixel)437 CatalogObjectVector DBManager::get_objects_in_trixel(const int trixel)
438 {
439 QMutexLocker _{ &m_mutex }; // this costs ~ .1ms which is ok
440 m_q_obj_by_trixel.bindValue(0, trixel);
441
442 if (!m_q_obj_by_trixel.exec()) // we throw because this is not recoverable
443 throw DatabaseError(
444 QString("The query m_by_trixel_query for objects in trixel=%1 failed.")
445 .arg(trixel),
446 DatabaseError::ErrorType::UNKNOWN, m_q_obj_by_trixel.lastError());
447
448 CatalogObjectVector objects;
449 size_t count =
450 count_rows(m_q_obj_by_trixel); // this also moves the query head to the end
451
452 if (count == 0)
453 {
454 m_q_obj_by_trixel.finish();
455 return objects;
456 }
457
458 objects.reserve(count);
459
460 while (m_q_obj_by_trixel.previous())
461 {
462 objects.push_back(read_catalogobject(m_q_obj_by_trixel));
463 }
464
465 m_q_obj_by_trixel.finish();
466
467 // move semantics baby!
468 return objects;
469 }
470
fetch_objects(QSqlQuery & query) const471 CatalogObjectList DBManager::fetch_objects(QSqlQuery &query) const
472 {
473 CatalogObjectList objects;
474 auto _ = gsl::finally([&]() { query.finish(); });
475
476 query.exec();
477
478 if (!query.isActive())
479 return {};
480 while (query.next())
481 objects.push_back(read_catalogobject(query));
482
483 return objects;
484 }
485
find_objects_by_name(const QString & name,const int limit,const bool exactMatchOnly)486 CatalogObjectList DBManager::find_objects_by_name(const QString &name, const int limit,
487 const bool exactMatchOnly)
488 {
489 QMutexLocker _{ &m_mutex };
490
491 // search for an exact match first
492 if (limit == 1)
493 {
494 m_q_obj_by_name_exact.bindValue(":name", name);
495 const auto &objs = fetch_objects(m_q_obj_by_name_exact);
496
497 if (objs.size() > 0)
498 {
499 return objs;
500 }
501 if (exactMatchOnly)
502 {
503 return CatalogObjectList();
504 }
505 }
506
507 m_q_obj_by_name.bindValue(":name", name);
508 m_q_obj_by_name.bindValue(":limit", limit);
509
510 return fetch_objects(m_q_obj_by_name);
511 }
512
find_objects_by_name(const int catalog_id,const QString & name,const int limit)513 CatalogObjectList DBManager::find_objects_by_name(const int catalog_id,
514 const QString &name, const int limit)
515 {
516 QSqlQuery query{ m_db };
517
518 query.prepare(SqlStatements::dso_by_name_and_catalog(catalog_id));
519 query.bindValue(":name", name);
520 query.bindValue(":limit", limit);
521 query.bindValue(":catalog", catalog_id);
522
523 return fetch_objects(query);
524 }
525
read_first_object(QSqlQuery & query) const526 std::pair<bool, CatalogObject> DBManager::read_first_object(QSqlQuery &query) const
527 {
528 if (!query.exec() || !query.first())
529 return { false, {} };
530
531 return { true, read_catalogobject(query) };
532 }
533
get_object(const CatalogObject::oid & oid)534 std::pair<bool, CatalogObject> DBManager::get_object(const CatalogObject::oid &oid)
535 {
536 QMutexLocker _{ &m_mutex };
537 m_q_obj_by_oid.bindValue(0, oid);
538
539 auto f = gsl::finally([&]() { // taken from the GSL, runs when it goes out of scope
540 m_q_obj_by_oid.finish();
541 });
542
543 return read_first_object(m_q_obj_by_oid);
544 };
545
get_object(const CatalogObject::oid & oid,const int catalog_id)546 std::pair<bool, CatalogObject> DBManager::get_object(const CatalogObject::oid &oid,
547 const int catalog_id)
548 {
549 QMutexLocker _{ &m_mutex };
550 QSqlQuery query{ m_db };
551
552 query.prepare(SqlStatements::dso_by_oid_and_catalog(catalog_id));
553 query.bindValue(0, oid);
554
555 return read_first_object(query);
556 };
557
get_objects(float maglim,int limit)558 CatalogObjectList DBManager::get_objects(float maglim, int limit)
559 {
560 QMutexLocker _{ &m_mutex };
561 m_q_obj_by_maglim.bindValue(":maglim", maglim);
562 m_q_obj_by_maglim.bindValue(":limit", limit);
563
564 return fetch_objects(m_q_obj_by_maglim);
565 }
566
get_objects(SkyObject::TYPE type,float maglim,int limit)567 CatalogObjectList DBManager::get_objects(SkyObject::TYPE type, float maglim, int limit)
568 {
569 QMutexLocker _{ &m_mutex };
570 m_q_obj_by_maglim_and_type.bindValue(":type", type);
571 m_q_obj_by_maglim_and_type.bindValue(":limit", limit);
572 m_q_obj_by_maglim_and_type.bindValue(":maglim", maglim);
573
574 return fetch_objects(m_q_obj_by_maglim_and_type);
575 }
576
get_objects_in_catalog(SkyObject::TYPE type,const int catalog_id,float maglim,int limit)577 CatalogObjectList DBManager::get_objects_in_catalog(SkyObject::TYPE type,
578 const int catalog_id, float maglim,
579 int limit)
580 {
581 QSqlQuery query{ m_db };
582
583 query.prepare(SqlStatements::dso_in_catalog_by_maglim(catalog_id));
584 query.bindValue(":type", type);
585 query.bindValue(":limit", limit);
586 query.bindValue(":maglim", maglim);
587 return fetch_objects(query);
588 }
589
set_catalog_enabled(const int id,const bool enabled)590 std::pair<bool, QString> DBManager::set_catalog_enabled(const int id, const bool enabled)
591 {
592 const auto &success = get_catalog(id);
593 if (!success.first)
594 return { false, i18n("Catalog could not be found.") };
595
596 const auto &cat = success.second;
597 if (cat.enabled == enabled)
598 return { true, "" };
599
600 QSqlQuery query{ m_db };
601 query.prepare(SqlStatements::enable_disable_catalog);
602 query.bindValue(":enabled", enabled);
603 query.bindValue(":id", id);
604
605 return { query.exec() && update_catalog_views() && compile_master_catalog(),
606 query.lastError().text() + m_db.lastError().text() };
607 }
608
get_catalogs(bool include_disabled)609 const std::vector<Catalog> DBManager::get_catalogs(bool include_disabled)
610 {
611 auto ids = get_catalog_ids(include_disabled);
612 std::vector<Catalog> catalogs;
613 catalogs.reserve(ids.size());
614
615 std::transform(ids.cbegin(), ids.cend(), std::back_inserter(catalogs),
616 [&](const int id) {
617 const auto &found = get_catalog(id);
618 if (found.first)
619 return found.second;
620
621 // This really should **not** happen
622 throw DatabaseError(
623 QString("Could not retrieve the catalog with id=%1").arg(id));
624 });
625
626 return catalogs;
627 }
628
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)629 inline void bind_catalogobject(QSqlQuery &query, const int catalog_id,
630 const SkyObject::TYPE t, const CachingDms &r,
631 const CachingDms &d, const QString &n, const float m,
632 const QString &lname, const QString &catalog_identifier,
633 const float a, const float b, const double pa,
634 const float flux, Trixel trixel,
635 const CatalogObject::oid &new_id)
636 {
637 query.prepare(SqlStatements::insert_dso(catalog_id));
638
639 query.bindValue(":hash", new_id); // no dedupe, maybe in the future
640 query.bindValue(":oid", new_id);
641 query.bindValue(":type", static_cast<int>(t));
642 query.bindValue(":ra", r.Degrees());
643 query.bindValue(":dec", d.Degrees());
644 query.bindValue(":magnitude", (m < 99 && !std::isnan(m)) ? m : QVariant{});
645 query.bindValue(":name", n);
646 query.bindValue(":long_name", lname.length() > 0 ? lname : QVariant{});
647 query.bindValue(":catalog_identifier",
648 catalog_identifier.length() > 0 ? catalog_identifier : QVariant{});
649 query.bindValue(":major_axis", a > 0 ? a : QVariant{});
650 query.bindValue(":minor_axis", b > 0 ? b : QVariant{});
651 query.bindValue(":position_angle", pa > 0 ? pa : QVariant{});
652 query.bindValue(":flux", flux > 0 ? flux : QVariant{});
653 query.bindValue(":trixel", trixel);
654 query.bindValue(":catalog", catalog_id);
655 }
656
bind_catalogobject(QSqlQuery & query,const int catalog_id,const CatalogObject & obj,Trixel trixel)657 inline void bind_catalogobject(QSqlQuery &query, const int catalog_id,
658 const CatalogObject &obj, Trixel trixel)
659 {
660 bind_catalogobject(query, catalog_id, static_cast<SkyObject::TYPE>(obj.type()),
661 obj.ra0(), obj.dec0(), obj.name(), obj.mag(), obj.longname(),
662 obj.catalogIdentifier(), obj.a(), obj.b(), obj.pa(), obj.flux(),
663 trixel, obj.getObjectId());
664 };
665
add_object(const int catalog_id,const CatalogObject & obj)666 std::pair<bool, QString> DBManager::add_object(const int catalog_id,
667 const CatalogObject &obj)
668 {
669 return add_object(catalog_id, static_cast<SkyObject::TYPE>(obj.type()), obj.ra0(),
670 obj.dec0(), obj.name(), obj.mag(), obj.longname(),
671 obj.catalogIdentifier(), obj.a(), obj.b(), obj.pa(), obj.flux());
672 }
673
674 std::pair<bool, QString>
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)675 DBManager::add_object(const int catalog_id, const SkyObject::TYPE t, const CachingDms &r,
676 const CachingDms &d, const QString &n, const float m,
677 const QString &lname, const QString &catalog_identifier,
678 const float a, const float b, const double pa, const float flux)
679 {
680 {
681 const auto &success = get_catalog(catalog_id);
682 if (!success.first)
683 return { false, i18n("Catalog with id=%1 not found.", catalog_id) };
684
685 if (!success.second.mut)
686 return { false, i18n("Catalog is immutable!") };
687 }
688
689 SkyPoint tmp{ r, d };
690 const auto trixel = SkyMesh::Create(m_htmesh_level)->index(&tmp);
691 QSqlQuery query{ m_db };
692
693 const auto new_id =
694 CatalogObject::getId(t, r.Degrees(), d.Degrees(), n, catalog_identifier);
695 bind_catalogobject(query, catalog_id, t, r, d, n, m, lname, catalog_identifier, a, b,
696 pa, flux, trixel, new_id);
697
698 if (!query.exec())
699 {
700 auto err = query.lastError().text();
701 if (err.startsWith("UNIQUE"))
702 err = i18n("The object is already in the catalog!");
703
704 return { false, i18n("Could not insert object! %1", err) };
705 }
706
707 return { update_catalog_views() && compile_master_catalog(),
708 m_db.lastError().text() };
709 }
710
remove_object(const int catalog_id,const CatalogObject::oid & id)711 std::pair<bool, QString> DBManager::remove_object(const int catalog_id,
712 const CatalogObject::oid &id)
713 {
714 QSqlQuery query{ m_db };
715
716 query.prepare(SqlStatements::remove_dso(catalog_id));
717 query.bindValue(":oid", id);
718
719 if (!query.exec())
720 return { false, query.lastError().text() };
721
722 return { update_catalog_views() && compile_master_catalog(),
723 m_db.lastError().text() };
724 }
725
dump_catalog(int catalog_id,QString file_path)726 std::pair<bool, QString> DBManager::dump_catalog(int catalog_id, QString file_path)
727 {
728 const auto &found = get_catalog(catalog_id);
729 if (!found.first)
730 return { false, i18n("Catalog could not be found.") };
731
732 QFile file{ file_path };
733 if (!file.open(QIODevice::WriteOnly))
734 return { false, i18n("Output file is not writable.") };
735 file.resize(0);
736 file.close();
737
738 QSqlQuery query{ m_db };
739
740 if (!query.exec(QString("ATTACH [%1] AS tmp").arg(file_path)))
741 return { false,
742 i18n("Could not attach output file.<br>%1", query.lastError().text()) };
743
744 m_db.transaction();
745 auto _ = gsl::finally([&]() { // taken from the GSL, runs when it goes out of scope
746 m_db.commit();
747 query.exec("DETACH tmp");
748 });
749
750 if (!query.exec(
751 QString("CREATE TABLE tmp.cat AS SELECT * FROM cat_%1").arg(catalog_id)))
752 return { false, i18n("Could not copy catalog to output file.<br>%1")
753 .arg(query.lastError().text()) };
754
755 if (!query.exec(SqlStatements::create_catalog_registry("tmp.catalogs")))
756 return { false, i18n("Could not create catalog registry in output file.<br>%1")
757 .arg(query.lastError().text()) };
758
759 query.prepare(SqlStatements::insert_into_catalog_registry("tmp.catalogs"));
760
761 auto cat = found.second;
762 cat.enabled = true;
763 bind_catalog(query, cat);
764
765 if (!query.exec())
766 {
767 return { false,
768 i18n("Could not insert catalog into registry in output file.<br>%1")
769 .arg(query.lastError().text()) };
770 }
771
772 if (!query.exec(QString("PRAGMA tmp.user_version = %1").arg(m_db_version)))
773 {
774 return { false, i18n("Could not insert set exported database version.<br>%1")
775 .arg(query.lastError().text()) };
776 }
777
778 if (!query.exec(QString("PRAGMA tmp.application_id = %1").arg(application_id)))
779 {
780 return { false,
781 i18n("Could not insert set exported database application id.<br>%1")
782 .arg(query.lastError().text()) };
783 }
784
785 return { true, {} };
786 }
787
import_catalog(const QString & file_path,const bool overwrite)788 std::pair<bool, QString> DBManager::import_catalog(const QString &file_path,
789 const bool overwrite)
790 {
791 QTemporaryDir tmp;
792 const auto new_path = tmp.filePath("cat.kscat");
793 QFile::copy(file_path, new_path);
794
795 QFile file{ new_path };
796 if (!file.open(QIODevice::ReadOnly))
797 return { false, i18n("Catalog file is not readable.") };
798 file.close();
799
800 QSqlQuery query{ m_db };
801
802 if (!query.exec(QString("ATTACH [%1] AS tmp").arg(new_path)))
803 {
804 m_db.commit();
805 return { false,
806 i18n("Could not attach input file.<br>%1", query.lastError().text()) };
807 }
808
809 auto _ = gsl::finally([&]() {
810 m_db.commit();
811 query.exec("DETACH tmp");
812 });
813
814 if (!query.exec("PRAGMA tmp.application_id") || !query.next() ||
815 query.value(0).toInt() != CatalogsDB::application_id)
816 return { false, i18n("Invalid catalog file.") };
817
818 if (!query.exec("PRAGMA tmp.user_version") || !query.next() ||
819 query.value(0).toInt() < m_db_version)
820 {
821 const auto &success = migrate_db(query.value(0).toInt(), m_db, "tmp");
822 if (!success.first)
823 return { false, i18n("Could not migrate old catalog format.<br>%1",
824 success.second) };
825 }
826
827 if (!query.exec("SELECT id FROM tmp.catalogs LIMIT 1") || !query.next())
828 return { false,
829 i18n("Could read the catalog id.<br>%1", query.lastError().text()) };
830
831 const auto id = query.value(0).toInt();
832 query.finish();
833
834 {
835 const auto &found = get_catalog(id);
836 if (found.first)
837 {
838 if (!overwrite && found.second.mut)
839 return { false, i18n("Catalog already exists in the database!") };
840
841 auto success = remove_catalog_force(id);
842 if (!success.first)
843 return success;
844 }
845 }
846
847 m_db.transaction();
848
849 if (!query.exec(
850 "INSERT INTO catalogs (id, name, mut, enabled, precedence, author, source, "
851 "description, version, color, license, maintainer, timestamp) SELECT id, "
852 "name, mut, enabled, precedence, author, source, description, version, "
853 "color, license, maintainer, timestamp FROM tmp.catalogs LIMIT 1") ||
854 !query.exec(QString("CREATE TABLE cat_%1 AS SELECT * FROM tmp.cat").arg(id)))
855 return { false,
856 i18n("Could not import the catalog.<br>%1", query.lastError().text()) };
857
858 m_db.commit();
859
860 if (!update_catalog_views() || !compile_master_catalog())
861 return { false, i18n("Could not refresh the master catalog.<br>",
862 m_db.lastError().text()) };
863
864 return { true, {} };
865 }
866
remove_catalog(const int id)867 std::pair<bool, QString> DBManager::remove_catalog(const int id)
868 {
869 if (id == SqlStatements::user_catalog_id)
870 return { false, i18n("Removing the user catalog is not allowed.") };
871
872 return remove_catalog_force(id);
873 }
874
remove_catalog_force(const int id)875 std::pair<bool, QString> DBManager::remove_catalog_force(const int id)
876 {
877 auto success = set_catalog_enabled(id, false);
878 if (!success.first)
879 return success;
880
881 QSqlQuery remove_catalog{ m_db };
882 remove_catalog.prepare(SqlStatements::remove_catalog);
883 remove_catalog.bindValue(0, id);
884
885 m_db.transaction();
886
887 if (!remove_catalog.exec() || !remove_catalog.exec(SqlStatements::drop_catalog(id)))
888 {
889 m_db.rollback();
890 return { false, i18n("Could not remove the catalog from the registry.<br>%1")
891 .arg(remove_catalog.lastError().text()) };
892 }
893
894 m_db.commit();
895 // we don't have to refresh the master catalog because the disable
896 // call already did this
897
898 return { true, {} };
899 }
900
copy_objects(const int id_1,const int id_2)901 std::pair<bool, QString> DBManager::copy_objects(const int id_1, const int id_2)
902 {
903 if (!(catalog_exists(id_1) && catalog_exists(id_2)))
904 return { false, i18n("Both catalogs have to exist!") };
905
906 if (!get_catalog(id_2).second.mut)
907 return { false, i18n("Destination catalog has to be mutable!") };
908
909 QSqlQuery query{ m_db };
910
911 if (!query.exec(SqlStatements::move_objects(id_1, id_2)))
912 return { false, query.lastError().text() };
913
914 if (!query.exec(SqlStatements::set_catalog_all_objects(id_2)))
915 return { false, query.lastError().text() };
916
917 return { true, {} };
918 }
919
update_catalog_meta(const Catalog & cat)920 std::pair<bool, QString> DBManager::update_catalog_meta(const Catalog &cat)
921 {
922 if (!catalog_exists(cat.id))
923 return { false, i18n("Cannot update nonexisting catalog.") };
924
925 QSqlQuery query{ m_db };
926
927 query.prepare(SqlStatements::update_catalog_meta);
928 query.bindValue(":name", cat.name);
929 query.bindValue(":author", cat.author);
930 query.bindValue(":source", cat.source);
931 query.bindValue(":description", cat.description);
932 query.bindValue(":id", cat.id);
933 query.bindValue(":color", cat.color);
934 query.bindValue(":license", cat.license);
935 query.bindValue(":maintainer", cat.maintainer);
936 query.bindValue(":timestamp", cat.timestamp);
937
938 return { query.exec(), query.lastError().text() };
939 }
940
find_suitable_catalog_id()941 int DBManager::find_suitable_catalog_id()
942 {
943 const auto &cats = get_catalogs(true);
944
945 // find a gap in the ids to use
946 const auto element = std::adjacent_find(
947 cats.cbegin(), cats.cend(), [](const auto &c1, const auto &c2) {
948 return (c1.id >= CatalogsDB::custom_cat_min_id) &&
949 (c2.id >= CatalogsDB::custom_cat_min_id) && (c2.id - c1.id) > 1;
950 });
951
952 return std::max(CatalogsDB::custom_cat_min_id,
953 (element == cats.cend() ? cats.back().id : element->id) + 1);
954 }
955
dso_db_path()956 QString CatalogsDB::dso_db_path()
957 {
958 return QDir(KSPaths::writableLocation(QStandardPaths::AppDataLocation))
959 .filePath(Options::dSOCatalogFilename());
960 }
961
read_catalog_meta_from_file(const QString & path)962 std::pair<bool, Catalog> CatalogsDB::read_catalog_meta_from_file(const QString &path)
963 {
964 QSqlDatabase db{ QSqlDatabase::addDatabase(
965 "QSQLITE", QString("tmp_%1_%2").arg(path).arg(get_connection_index())) };
966 db.setDatabaseName(path);
967
968 if (!db.open())
969 return { false, {} };
970
971 QSqlQuery query{ db };
972
973 if (!query.exec("PRAGMA user_version") || !query.next() ||
974 query.value(0).toInt() < SqlStatements::current_db_version)
975 {
976 QTemporaryDir tmp;
977 const auto new_path = tmp.filePath("cat.kscat");
978
979 QFile::copy(path, new_path);
980 db.close();
981
982 db.setDatabaseName(new_path);
983 if (!db.open())
984 return { false, {} };
985
986 const auto &success = migrate_db(query.value(0).toInt(), db);
987 if (!success.first)
988 return { false, {} };
989 }
990
991 if (!query.exec(SqlStatements::get_first_catalog) || !query.first())
992 return { false, {} };
993
994 db.close();
995 return { true, read_catalog(query) };
996 }
997
read_statistics(QSqlQuery & query)998 CatalogStatistics read_statistics(QSqlQuery &query)
999 {
1000 CatalogStatistics stats{};
1001 while (query.next())
1002 {
1003 stats.object_counts[(SkyObject::TYPE)query.value(0).toInt()] =
1004 query.value(1).toInt();
1005 stats.total_count += query.value(1).toInt();
1006 }
1007 return stats;
1008 }
1009
get_master_statistics()1010 const std::pair<bool, CatalogStatistics> DBManager::get_master_statistics()
1011 {
1012 QSqlQuery query{ m_db };
1013 if (!query.exec(SqlStatements::dso_count_by_type_master))
1014 return { false, {} };
1015
1016 return { true, read_statistics(query) };
1017 }
1018
1019 const std::pair<bool, CatalogStatistics>
get_catalog_statistics(const int catalog_id)1020 DBManager::get_catalog_statistics(const int catalog_id)
1021 {
1022 QSqlQuery query{ m_db };
1023 if (!query.exec(SqlStatements::dso_count_by_type(catalog_id)))
1024 return { false, {} };
1025
1026 return { true, read_statistics(query) };
1027 }
1028
1029 std::pair<bool, QString>
add_objects(const int catalog_id,const CatalogObjectVector & objects)1030 CatalogsDB::DBManager::add_objects(const int catalog_id,
1031 const CatalogObjectVector &objects)
1032 {
1033 {
1034 const auto &success = get_catalog(catalog_id);
1035 if (!success.first)
1036 return { false, i18n("Catalog with id=%1 not found.", catalog_id) };
1037
1038 if (!success.second.mut)
1039 return { false, i18n("Catalog is immutable!") };
1040 }
1041
1042 m_db.transaction();
1043 QSqlQuery query{ m_db };
1044 for (const auto &object : objects)
1045 {
1046 SkyPoint tmp{ object.ra(), object.dec() };
1047 const auto trixel = SkyMesh::Create(m_htmesh_level)->index(&tmp);
1048
1049 bind_catalogobject(query, catalog_id, object, trixel);
1050
1051 if (!query.exec())
1052 {
1053 auto err = query.lastError().text();
1054 if (err.startsWith("UNIQUE"))
1055 err = i18n("The object is already in the catalog!");
1056
1057 return { false, i18n("Could not insert object! %1", err) };
1058 }
1059 }
1060
1061 return { m_db.commit() && update_catalog_views() && compile_master_catalog(),
1062 m_db.lastError().text() };
1063 };
1064
find_objects_by_wildcard(const QString & wildcard,const int limit)1065 CatalogObjectList CatalogsDB::DBManager::find_objects_by_wildcard(const QString &wildcard,
1066 const int limit)
1067 {
1068 QMutexLocker _{ &m_mutex };
1069
1070 QSqlQuery query{ m_db };
1071 if (!query.prepare(SqlStatements::dso_by_wildcard()))
1072 {
1073 return {};
1074 }
1075 query.bindValue(":wildcard", wildcard);
1076 query.bindValue(":limit", limit);
1077
1078 return fetch_objects(query);
1079 };
1080
1081 std::tuple<bool, const QString, CatalogObjectList>
general_master_query(const QString & where,const QString & order_by,const int limit)1082 CatalogsDB::DBManager::general_master_query(const QString &where, const QString &order_by,
1083 const int limit)
1084 {
1085 QMutexLocker _{ &m_mutex };
1086
1087 QSqlQuery query{ m_db };
1088
1089 if (!query.prepare(SqlStatements::dso_general_query(where, order_by)))
1090 {
1091 return { false, query.lastError().text(), {} };
1092 }
1093
1094 query.bindValue(":limit", limit);
1095
1096 return { false, "", fetch_objects(query) };
1097 };
1098
parse_color_string(const QString & str)1099 CatalogsDB::CatalogColorMap CatalogsDB::parse_color_string(const QString &str)
1100 {
1101 CatalogsDB::CatalogColorMap colors{};
1102 if (str == "")
1103 return colors;
1104
1105 const auto &parts = str.split(";");
1106 auto it = parts.constBegin();
1107
1108 if (it->length() > 0) // playing it save
1109 colors["default"] = *it;
1110
1111 while (it != parts.constEnd())
1112 {
1113 const auto &scheme = *(++it);
1114 if (it != parts.constEnd())
1115 {
1116 const auto next = ++it;
1117 if (next == parts.constEnd())
1118 break;
1119
1120 const auto &color = *next;
1121 colors[scheme] = QColor(color);
1122 }
1123 }
1124
1125 return colors;
1126 }
1127
get_name(const QColor & color)1128 QString get_name(const QColor &color)
1129 {
1130 return color.isValid() ? color.name() : "";
1131 }
1132
to_color_string(CatalogColorMap colors)1133 QString CatalogsDB::to_color_string(CatalogColorMap colors)
1134 {
1135 QStringList color_list;
1136
1137 color_list << colors["default"].name();
1138 colors.erase("default");
1139
1140 for (const auto &item : colors)
1141 {
1142 if (item.second.isValid())
1143 {
1144 color_list << item.first << item.second.name();
1145 }
1146 }
1147
1148 return color_list.join(";");
1149 }
1150
get_catalog_colors()1151 ColorMap CatalogsDB::DBManager::get_catalog_colors()
1152 {
1153 // no mutex b.c. this is read only
1154 QSqlQuery query{ m_db };
1155
1156 ColorMap colors{};
1157
1158 if (!query.exec(SqlStatements::get_colors))
1159 return colors;
1160
1161 for (const auto &cat : DBManager::get_catalogs(true))
1162 {
1163 colors[cat.id] = parse_color_string(cat.color);
1164 }
1165
1166 while (query.next())
1167 {
1168 const auto &catalog = query.value("catalog").toInt();
1169 const auto &scheme = query.value("scheme").toString();
1170 const auto &color = query.value("color").toString();
1171 colors[catalog][scheme] = QColor(color);
1172 }
1173
1174 return colors;
1175 };
1176
get_catalog_colors(const int id)1177 CatalogsDB::CatalogColorMap CatalogsDB::DBManager::get_catalog_colors(const int id)
1178 {
1179 return get_catalog_colors()[id]; // good enough for now
1180 };
1181
1182 std::pair<bool, QString>
insert_catalog_colors(const int id,const CatalogColorMap & colors)1183 CatalogsDB::DBManager::insert_catalog_colors(const int id, const CatalogColorMap &colors)
1184 {
1185 QMutexLocker _{ &m_mutex };
1186
1187 QSqlQuery query{ m_db };
1188
1189 if (!query.prepare(SqlStatements::insert_color))
1190 {
1191 return { false, query.lastError().text() };
1192 }
1193
1194 query.bindValue(":catalog", id);
1195 for (const auto &item : colors)
1196 {
1197 query.bindValue(":scheme", item.first);
1198 query.bindValue(":color", item.second);
1199
1200 if (!query.exec())
1201 return { false, query.lastError().text() };
1202 }
1203
1204 return { true, "" };
1205 };
1206