Kstars

catalogsdb.cpp
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 <QTemporaryDir>
13#include <qsqldatabase.h>
14#include "cachingdms.h"
15#include "catalogsdb.h"
16#include "kspaths.h"
17#include "skymesh.h"
18#include "Options.h"
19#include "final_action.h"
20#include "sqlstatements.cpp"
21
22using namespace CatalogsDB;
23
24/**
25 * Get an increasing index for new connections.
26 */
27int get_connection_index()
28{
29 static int connection_index = 0;
30 return connection_index++;
31}
32
33QSqlQuery 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 */
50std::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 {
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
80DBManager::DBManager(const QString &filename) : m_db_file(filename)
81{
82 m_db = QSqlDatabase::addDatabase("QSQLITE", QUuid::createUuid().toString());
83 m_db.setDatabaseName(m_db_file);
84
85 // we are throwing here, because errors at this stage should be fatal
86 if (!m_db.open())
87 {
88 throw DatabaseError(QString("Cannot open CatalogDatabase '%1'!").arg(m_db_file),
89 DatabaseError::ErrorType::OPEN, m_db.lastError());
90 }
91
92 bool init = false;
93 std::tie(m_db_version, m_htmesh_level, init) = get_db_meta();
94
95 if (!init && m_db_version > 0 && m_db_version < SqlStatements::current_db_version)
96 {
97 const auto &backup_path = QString("%1.%2").arg(m_db_file).arg(
98 QDateTime::currentDateTime().toString("dd_MMMM_yy_hh_mm_sss_zzz"));
99
100 if (!QFile::copy(m_db_file, backup_path))
101 {
102 throw DatabaseError(
103 QString("Could not backup dso database before upgrading."),
104 DatabaseError::ErrorType::VERSION, QSqlError{});
105 }
106
107 const auto &success = migrate_db(m_db_version, m_db);
108 if (success.first)
109 {
110 m_db_version = SqlStatements::current_db_version;
111 QSqlQuery version_query{ m_db };
112 version_query.prepare(SqlStatements::update_version);
113 version_query.bindValue(":version", m_db_version);
114
115 if (!version_query.exec())
116 {
117 throw DatabaseError(QString("Could not update the database version."),
118 DatabaseError::ErrorType::VERSION,
119 version_query.lastError());
120 }
121 }
122 else
123 throw DatabaseError(
124 QString("Wrong database version. Expected %1 and got %2 and "
125 "migration is not possible.")
126 .arg(SqlStatements::current_db_version)
127 .arg(m_db_version),
128 DatabaseError::ErrorType::VERSION, success.second);
129 }
130
131 QSqlQuery master_exists{ m_db };
132 master_exists.exec(SqlStatements::exists_master);
133 const bool master_does_exist = master_exists.next();
134 master_exists.finish();
135
136 if (init || !master_does_exist)
137 {
138 if (!initialize_db())
139 {
140 throw DatabaseError(QString("Could not initialize database."),
141 DatabaseError::ErrorType::INIT, m_db.lastError());
142 }
143
144 if (!catalog_exists(SqlStatements::user_catalog_id))
145 {
146 const auto &res =
147 register_catalog(SqlStatements::user_catalog_id,
148 SqlStatements::user_catalog_name, true, true, 1);
149 if (!res.first)
150 {
151 throw DatabaseError(QString("Could not create user database."),
152 DatabaseError::ErrorType::CREATE_CATALOG, res.second);
153 }
154 }
155
157 {
158 throw DatabaseError(QString("Unable to create combined catalog view!"),
159 DatabaseError::ErrorType::CREATE_CATALOG,
160 m_db.lastError());
161 }
162
164 {
165 throw DatabaseError(QString("Unable to create master catalog!"),
166 DatabaseError::ErrorType::CREATE_MASTER,
167 m_db.lastError());
168 }
169 }
170
171 m_q_cat_by_id = make_query(m_db, SqlStatements::get_catalog_by_id, true);
172 m_q_obj_by_trixel = make_query(m_db, SqlStatements::dso_by_trixel, false);
173 m_q_obj_by_trixel_no_nulls = make_query(m_db, SqlStatements::dso_by_trixel_no_nulls, false);
174 m_q_obj_by_trixel_null_mag = make_query(m_db, SqlStatements::dso_by_trixel_null_mag, false);
175 m_q_obj_by_name = make_query(m_db, SqlStatements::dso_by_name, true);
176 m_q_obj_by_name_exact = make_query(m_db, SqlStatements::dso_by_name_exact, true);
177 m_q_obj_by_lim = make_query(m_db, SqlStatements::dso_by_lim, 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
185
186bool 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
210std::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
222std::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
239{
240 const auto &ids = get_catalog_ids();
241 bool result = true;
242 auto _ = gsl::finally([&]()
243 {
244 m_db.commit();
245 });
246
247 m_db.transaction();
248 QSqlQuery query{ m_db };
249 result &=
250 query.exec(QString("DROP VIEW IF EXISTS ") + SqlStatements::all_catalog_view);
251
252 if (!result)
253 {
254 return result;
255 }
256
257 QString view
258 {
259 "CREATE VIEW "
260 }; // small enough to be included here and not in sqlstatements
261
262 view += SqlStatements::all_catalog_view;
263 view += " AS\n";
264
266 for (auto *field : SqlStatements::catalog_collumns)
267 {
268 prefixed << QString("c.") + field;
269 }
270
271 QString prefixed_joined = prefixed.join(",");
272
274 for (auto id : ids)
275 {
276 catalog_queries << SqlStatements::all_catalog_view_body(
277 prefixed_joined, SqlStatements::catalog_prefix, id);
278 }
279
280 if (ids.size() == 0)
281 {
282 catalog_queries << SqlStatements::all_catalog_view_body(
283 prefixed_joined, SqlStatements::catalog_prefix, 0) +
284 " WHERE FALSE"; // we blackhole the query
285 }
286
287 view += catalog_queries.join("\nUNION ALL\n");
288 result &= query.exec(view);
289 return result;
290}
291
292void bind_catalog(QSqlQuery &query, const Catalog &cat)
293{
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);
301 query.bindValue(":description", cat.description);
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);
307}
308
309std::pair<bool, QString> DBManager::register_catalog(
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,
313 const QString &license, const QString &maintainer, const QDateTime &timestamp)
314{
315 return register_catalog({ id, name, precedence, author, source, description, mut,
316 enabled, version, color, license, maintainer, timestamp });
317}
318
319std::pair<bool, QString> DBManager::register_catalog(const Catalog &cat)
320{
321 if (catalog_exists(cat.id))
322 return { false, i18n("Catalog with that ID already exists.") };
323
324 QSqlQuery query{ m_db };
325
326 if (!query.exec(SqlStatements::create_catalog_table(cat.id)))
327 {
328 return { false, query.lastError().text() };
329 }
330
331 query.prepare(SqlStatements::insert_catalog);
332 bind_catalog(query, cat);
333
334 return { query.exec(), query.lastError().text() };
335};
336
338{
339 auto _ = gsl::finally([&]()
340 {
341 m_db.commit();
342 });
343 QSqlQuery query{ m_db };
344 m_db.transaction();
345
346 if (!query.exec(SqlStatements::drop_master))
347 {
348 return false;
349 }
350
351 if (!query.exec(SqlStatements::create_master))
352 {
353 return false;
354 }
355
356 bool success = true;
357 success &= query.exec(SqlStatements::create_master_trixel_index);
358 success &= query.exec(SqlStatements::create_master_mag_index);
359 success &= query.exec(SqlStatements::create_master_type_index);
360 success &= query.exec(SqlStatements::create_master_name_index);
361 return success;
362};
363
364const Catalog read_catalog(const QSqlQuery &query)
365{
366 return { query.value("id").toInt(),
367 query.value("name").toString(),
368 query.value("precedence").toDouble(),
369 query.value("author").toString(),
370 query.value("source").toString(),
371 query.value("description").toString(),
372 query.value("mut").toBool(),
373 query.value("enabled").toBool(),
374 query.value("version").toInt(),
375 query.value("color").toString(),
376 query.value("license").toString(),
377 query.value("maintainer").toString(),
378 query.value("timestamp").toDateTime() };
379}
380
381const std::pair<bool, Catalog> DBManager::get_catalog(const int id)
382{
383 QMutexLocker _{ &m_mutex };
384 m_q_cat_by_id.bindValue(0, id);
385
386 if (!m_q_cat_by_id.exec())
387 return { false, {} };
388
389 if (!m_q_cat_by_id.next())
390 return { false, {} };
391
392 Catalog cat{ read_catalog(m_q_cat_by_id) };
393
394 m_q_cat_by_id.finish();
395 return { true, cat };
396}
397
399{
400 QMutexLocker _{ &m_mutex };
401 m_q_cat_by_id.bindValue(0, id);
402 auto end = gsl::finally([&]()
403 {
404 m_q_cat_by_id.finish();
405 });
406
407 if (!m_q_cat_by_id.exec())
408 return false;
409
410 return m_q_cat_by_id.next();
411}
412
413size_t count_rows(QSqlQuery &query)
414{
415 size_t count{ 0 };
416 while (query.next())
417 {
418 count++;
419 }
420
421 return count;
422}
423
424CatalogObject DBManager::read_catalogobject(const QSqlQuery &query) const
425{
426 const CatalogObject::oid id = query.value(0).toByteArray();
427 const SkyObject::TYPE type = static_cast<SkyObject::TYPE>(query.value(1).toInt());
428
429 const double ra = query.value(2).toDouble();
430 const double dec = query.value(3).toDouble();
431 const float mag = query.isNull(4) ? NaN::f : query.value(4).toFloat();
432 const QString name = query.value(5).toString();
433 const QString long_name = query.value(6).toString();
434 const QString catalog_identifier = query.value(7).toString();
435 const float major = query.value(8).toFloat();
436 const float minor = query.value(9).toFloat();
437 const double position_angle = query.value(10).toDouble();
438 const float flux = query.value(11).toFloat();
439 const int catalog_id = query.value(12).toInt();
440
441 return { id, type, dms(ra), dms(dec),
443 catalog_id, major, minor, position_angle,
444 flux, m_db_file };
445}
446
447CatalogObjectVector DBManager::_get_objects_in_trixel_generic(QSqlQuery &query, const int trixel)
448{
449 QMutexLocker _{ &m_mutex }; // this costs ~ .1ms which is ok
450 query.bindValue(0, trixel);
451
452 if (!query.exec()) // we throw because this is not recoverable
453 throw DatabaseError(
454 QString("The by-trixel query for objects in trixel=%1 failed.")
455 .arg(trixel),
456 DatabaseError::ErrorType::UNKNOWN, query.lastError());
457
458 CatalogObjectVector objects;
459 size_t count =
460 count_rows(query); // this also moves the query head to the end
461
462 if (count == 0)
463 {
464 query.finish();
465 return objects;
466 }
467
468 objects.reserve(count);
469
470 while (query.previous())
471 {
472 objects.push_back(read_catalogobject(query));
473 }
474
475 query.finish();
476
477 // move semantics baby!
478 return objects;
479}
480
481CatalogObjectList DBManager::fetch_objects(QSqlQuery &query) const
482{
483 CatalogObjectList objects;
484 auto _ = gsl::finally([&]()
485 {
486 query.finish();
487 });
488
489 query.exec();
490
491 if (!query.isActive())
492 return {};
493 while (query.next())
494 objects.push_back(read_catalogobject(query));
495
496 return objects;
497}
498
499CatalogObjectList DBManager::find_objects_by_name(const QString &name, const int limit,
500 const bool exactMatchOnly)
501{
502 QMutexLocker _{ &m_mutex };
503
504 // limit < 0 is a sentinel value for unlimited
505 if (limit == 0)
506 return CatalogObjectList();
507
508 // search for an exact match first
509 m_q_obj_by_name_exact.bindValue(":name", name);
510 CatalogObjectList objs { fetch_objects(m_q_obj_by_name_exact) };
511
512 if ((limit == 1 && objs.size() > 0) || exactMatchOnly)
513 return objs;
514
515 Q_ASSERT(objs.size() <= 1);
516
517 m_q_obj_by_name.bindValue(":name", name);
518 m_q_obj_by_name.bindValue(":limit", int(limit - objs.size()));
519
520 CatalogObjectList moreObjects = fetch_objects(m_q_obj_by_name);
521 moreObjects.splice(moreObjects.begin(), objs);
522 return moreObjects;
523
524}
525
526CatalogObjectList DBManager::find_objects_by_name(const int catalog_id,
527 const QString &name, const int limit)
528{
529 QSqlQuery query{ m_db };
530
531 query.prepare(SqlStatements::dso_by_name_and_catalog(catalog_id));
532 query.bindValue(":name", name);
533 query.bindValue(":limit", limit);
534 query.bindValue(":catalog", catalog_id);
535
536 return fetch_objects(query);
537}
538
539std::pair<bool, CatalogObject> DBManager::read_first_object(QSqlQuery &query) const
540{
541 if (!query.exec() || !query.first())
542 return { false, {} };
543
544 return { true, read_catalogobject(query) };
545}
546
547std::pair<bool, CatalogObject> DBManager::get_object(const CatalogObject::oid &oid)
548{
549 QMutexLocker _{ &m_mutex };
550 m_q_obj_by_oid.bindValue(0, oid);
551
552 auto f = gsl::finally([&]() // taken from the GSL, runs when it goes out of scope
553 {
554 m_q_obj_by_oid.finish();
555 });
556
557 return read_first_object(m_q_obj_by_oid);
558};
559
560std::pair<bool, CatalogObject> DBManager::get_object(const CatalogObject::oid &oid,
561 const int catalog_id)
562{
563 QMutexLocker _{ &m_mutex };
564 QSqlQuery query{ m_db };
565
566 query.prepare(SqlStatements::dso_by_oid_and_catalog(catalog_id));
567 query.bindValue(0, oid);
568
569 return read_first_object(query);
570};
571
572CatalogObjectList DBManager::get_objects(float maglim, int limit)
573{
574 QMutexLocker _{ &m_mutex };
575 m_q_obj_by_maglim.bindValue(":maglim", maglim);
576 m_q_obj_by_maglim.bindValue(":limit", limit);
577
578 return fetch_objects(m_q_obj_by_maglim);
579}
580
581CatalogObjectList DBManager::get_objects_all()
582{
583 QMutexLocker _{ &m_mutex };
584 m_q_obj_by_lim.bindValue(":limit", -1);
585
586 return fetch_objects(m_q_obj_by_lim);
587}
588
589CatalogObjectList DBManager::get_objects(SkyObject::TYPE type, float maglim, int limit)
590{
591 QMutexLocker _{ &m_mutex };
592 m_q_obj_by_maglim_and_type.bindValue(":type", type);
593 m_q_obj_by_maglim_and_type.bindValue(":limit", limit);
594 m_q_obj_by_maglim_and_type.bindValue(":maglim", maglim);
595
596 return fetch_objects(m_q_obj_by_maglim_and_type);
597}
598
600 const int catalog_id, float maglim,
601 int limit)
602{
603 QSqlQuery query{ m_db };
604
605 query.prepare(SqlStatements::dso_in_catalog_by_maglim(catalog_id));
606 query.bindValue(":type", type);
607 query.bindValue(":limit", limit);
608 query.bindValue(":maglim", maglim);
609 return fetch_objects(query);
610}
611
612std::pair<bool, QString> DBManager::set_catalog_enabled(const int id, const bool enabled)
613{
614 const auto &success = get_catalog(id);
615 if (!success.first)
616 return { false, i18n("Catalog could not be found.") };
617
618 const auto &cat = success.second;
619 if (cat.enabled == enabled)
620 return { true, "" };
621
622 QSqlQuery query{ m_db };
623 query.prepare(SqlStatements::enable_disable_catalog);
624 query.bindValue(":enabled", enabled);
625 query.bindValue(":id", id);
626
627 return { query.exec() &&update_catalog_views() &&compile_master_catalog(),
628 query.lastError().text() + m_db.lastError().text() };
629}
630
631const std::vector<Catalog> DBManager::get_catalogs(bool include_disabled)
632{
633 auto ids = get_catalog_ids(include_disabled);
634 std::vector<Catalog> catalogs;
635 catalogs.reserve(ids.size());
636
637 std::transform(ids.cbegin(), ids.cend(), std::back_inserter(catalogs),
638 [&](const int id)
639 {
640 const auto &found = get_catalog(id);
641 if (found.first)
642 return found.second;
643
644 // This really should **not** happen
645 throw DatabaseError(
646 QString("Could not retrieve the catalog with id=%1").arg(id));
647 });
648
649 return catalogs;
650}
651
652inline void bind_catalogobject(QSqlQuery &query, const int catalog_id,
653 const SkyObject::TYPE t, const CachingDms &r,
654 const CachingDms &d, const QString &n, const float m,
656 const float a, const float b, const double pa,
657 const float flux, Trixel trixel,
659{
660 query.prepare(SqlStatements::insert_dso(catalog_id));
661
662 query.bindValue(":hash", new_id); // no dedupe, maybe in the future
663 query.bindValue(":oid", new_id);
664 query.bindValue(":type", static_cast<int>(t));
665 query.bindValue(":ra", r.Degrees());
666 query.bindValue(":dec", d.Degrees());
667 query.bindValue(":magnitude", (m < 99 && !std::isnan(m)) ? m : QVariant{});
668 query.bindValue(":name", n);
669 query.bindValue(":long_name", lname.length() > 0 ? lname : QVariant{});
670 query.bindValue(":catalog_identifier",
672 query.bindValue(":major_axis", a > 0 ? a : QVariant{});
673 query.bindValue(":minor_axis", b > 0 ? b : QVariant{});
674 query.bindValue(":position_angle", pa > 0 ? pa : QVariant{});
675 query.bindValue(":flux", flux > 0 ? flux : QVariant{});
676 query.bindValue(":trixel", trixel);
677 query.bindValue(":catalog", catalog_id);
678}
679
680inline void bind_catalogobject(QSqlQuery &query, const int catalog_id,
681 const CatalogObject &obj, Trixel trixel)
682{
683 bind_catalogobject(query, catalog_id, static_cast<SkyObject::TYPE>(obj.type()),
684 obj.ra0(), obj.dec0(), obj.name(), obj.mag(), obj.longname(),
685 obj.catalogIdentifier(), obj.a(), obj.b(), obj.pa(), obj.flux(),
686 trixel, obj.getObjectId());
687};
688
689std::pair<bool, QString> DBManager::add_object(const int catalog_id,
690 const CatalogObject &obj)
691{
692 return add_object(catalog_id, static_cast<SkyObject::TYPE>(obj.type()), obj.ra0(),
693 obj.dec0(), obj.name(), obj.mag(), obj.longname(),
694 obj.catalogIdentifier(), obj.a(), obj.b(), obj.pa(), obj.flux());
695}
696
697std::pair<bool, QString>
699 const CachingDms &d, const QString &n, const float m,
701 const float a, const float b, const double pa, const float flux)
702{
703 {
704 const auto &success = get_catalog(catalog_id);
705 if (!success.first)
706 return { false, i18n("Catalog with id=%1 not found.", catalog_id) };
707
708 if (!success.second.mut)
709 return { false, i18n("Catalog is immutable!") };
710 }
711
712 SkyPoint tmp{ r, d };
713 const auto trixel = SkyMesh::Create(m_htmesh_level)->index(&tmp);
714 QSqlQuery query{ m_db };
715
716 const auto new_id =
717 CatalogObject::getId(t, r.Degrees(), d.Degrees(), n, catalog_identifier);
718 bind_catalogobject(query, catalog_id, t, r, d, n, m, lname, catalog_identifier, a, b,
719 pa, flux, trixel, new_id);
720
721 if (!query.exec())
722 {
723 auto err = query.lastError().text();
724 if (err.startsWith("UNIQUE"))
725 err = i18n("The object is already in the catalog!");
726
727 return { false, i18n("Could not insert object! %1", err) };
728 }
729
731 m_db.lastError().text() };
732}
733
734std::pair<bool, QString> DBManager::remove_object(const int catalog_id,
735 const CatalogObject::oid &id)
736{
737 QSqlQuery query{ m_db };
738
739 query.prepare(SqlStatements::remove_dso(catalog_id));
740 query.bindValue(":oid", id);
741
742 if (!query.exec())
743 return { false, query.lastError().text() };
744
746 m_db.lastError().text() };
747}
748
750{
751 const auto &found = get_catalog(catalog_id);
752 if (!found.first)
753 return { false, i18n("Catalog could not be found.") };
754
755 QFile file{ file_path };
756 if (!file.open(QIODevice::WriteOnly))
757 return { false, i18n("Output file is not writable.") };
758 file.resize(0);
759 file.close();
760
761 QSqlQuery query{ m_db };
762
763 if (!query.exec(QString("ATTACH [%1] AS tmp").arg(file_path)))
764 return { false,
765 i18n("Could not attach output file.<br>%1", query.lastError().text()) };
766
767 m_db.transaction();
768 auto _ = gsl::finally([&]() // taken from the GSL, runs when it goes out of scope
769 {
770 m_db.commit();
771 query.exec("DETACH tmp");
772 });
773
774 if (!query.exec(
775 QString("CREATE TABLE tmp.cat AS SELECT * FROM cat_%1").arg(catalog_id)))
776 return { false, i18n("Could not copy catalog to output file.<br>%1")
777 .arg(query.lastError().text()) };
778
779 if (!query.exec(SqlStatements::create_catalog_registry("tmp.catalogs")))
780 return { false, i18n("Could not create catalog registry in output file.<br>%1")
781 .arg(query.lastError().text()) };
782
783 query.prepare(SqlStatements::insert_into_catalog_registry("tmp.catalogs"));
784
785 auto cat = found.second;
786 cat.enabled = true;
787 bind_catalog(query, cat);
788
789 if (!query.exec())
790 {
791 return { false,
792 i18n("Could not insert catalog into registry in output file.<br>%1")
793 .arg(query.lastError().text()) };
794 }
795
796 if (!query.exec(QString("PRAGMA tmp.user_version = %1").arg(m_db_version)))
797 {
798 return { false, i18n("Could not insert set exported database version.<br>%1")
799 .arg(query.lastError().text()) };
800 }
801
802 if (!query.exec(QString("PRAGMA tmp.application_id = %1").arg(application_id)))
803 {
804 return { false,
805 i18n("Could not insert set exported database application id.<br>%1")
806 .arg(query.lastError().text()) };
807 }
808
809 return { true, {} };
810}
811
812std::pair<bool, QString> DBManager::import_catalog(const QString &file_path,
813 const bool overwrite)
814{
816 const auto new_path = tmp.filePath("cat.kscat");
818
819 QFile file{ new_path };
820 if (!file.open(QIODevice::ReadOnly))
821 return { false, i18n("Catalog file is not readable.") };
822 file.close();
823
824 QSqlQuery query{ m_db };
825
826 if (!query.exec(QString("ATTACH [%1] AS tmp").arg(new_path)))
827 {
828 m_db.commit();
829 return { false,
830 i18n("Could not attach input file.<br>%1", query.lastError().text()) };
831 }
832
833 auto _ = gsl::finally([&]()
834 {
835 m_db.commit();
836 query.exec("DETACH tmp");
837 });
838
839 if (!query.exec("PRAGMA tmp.application_id") || !query.next() ||
840 query.value(0).toInt() != CatalogsDB::application_id)
841 return { false, i18n("Invalid catalog file.") };
842
843 if (!query.exec("PRAGMA tmp.user_version") || !query.next() ||
844 query.value(0).toInt() < m_db_version)
845 {
846 const auto &success = migrate_db(query.value(0).toInt(), m_db, "tmp");
847 if (!success.first)
848 return { false, i18n("Could not migrate old catalog format.<br>%1",
849 success.second) };
850 }
851
852 if (!query.exec("SELECT id FROM tmp.catalogs LIMIT 1") || !query.next())
853 return { false,
854 i18n("Could read the catalog id.<br>%1", query.lastError().text()) };
855
856 const auto id = query.value(0).toInt();
857 query.finish();
858
859 {
860 const auto &found = get_catalog(id);
861 if (found.first)
862 {
863 if (!overwrite && found.second.mut)
864 return { false, i18n("Catalog already exists in the database!") };
865
866 auto success = remove_catalog_force(id);
867 if (!success.first)
868 return success;
869 }
870 }
871
872 m_db.transaction();
873
874 if (!query.exec(
875 "INSERT INTO catalogs (id, name, mut, enabled, precedence, author, source, "
876 "description, version, color, license, maintainer, timestamp) SELECT id, "
877 "name, mut, enabled, precedence, author, source, description, version, "
878 "color, license, maintainer, timestamp FROM tmp.catalogs LIMIT 1") ||
879 !query.exec(QString("CREATE TABLE cat_%1 AS SELECT * FROM tmp.cat").arg(id)))
880 return { false,
881 i18n("Could not import the catalog.<br>%1", query.lastError().text()) };
882
883 m_db.commit();
884
886 return { false, i18n("Could not refresh the master catalog.<br>",
887 m_db.lastError().text()) };
888
889 return { true, {} };
890}
891
892std::pair<bool, QString> DBManager::remove_catalog(const int id)
893{
894 if (id == SqlStatements::user_catalog_id)
895 return { false, i18n("Removing the user catalog is not allowed.") };
896
897 return remove_catalog_force(id);
898}
899
900std::pair<bool, QString> DBManager::remove_catalog_force(const int id)
901{
902 auto success = set_catalog_enabled(id, false);
903 if (!success.first)
904 return success;
905
907 remove_catalog.prepare(SqlStatements::remove_catalog);
908 remove_catalog.bindValue(0, id);
909
910 m_db.transaction();
911
912 if (!remove_catalog.exec() || !remove_catalog.exec(SqlStatements::drop_catalog(id)))
913 {
914 m_db.rollback();
915 return { false, i18n("Could not remove the catalog from the registry.<br>%1")
916 .arg(remove_catalog.lastError().text()) };
917 }
918
919 m_db.commit();
920 // we don't have to refresh the master catalog because the disable
921 // call already did this
922
923 return { true, {} };
924}
925
926std::pair<bool, QString> DBManager::copy_objects(const int id_1, const int id_2)
927{
929 return { false, i18n("Both catalogs have to exist!") };
930
931 if (!get_catalog(id_2).second.mut)
932 return { false, i18n("Destination catalog has to be mutable!") };
933
934 QSqlQuery query{ m_db };
935
936 if (!query.exec(SqlStatements::move_objects(id_1, id_2)))
937 return { false, query.lastError().text() };
938
939 if (!query.exec(SqlStatements::set_catalog_all_objects(id_2)))
940 return { false, query.lastError().text() };
941
942 return { true, {} };
943}
944
945std::pair<bool, QString> DBManager::update_catalog_meta(const Catalog &cat)
946{
947 if (!catalog_exists(cat.id))
948 return { false, i18n("Cannot update nonexisting catalog.") };
949
950 QSqlQuery query{ m_db };
951
952 query.prepare(SqlStatements::update_catalog_meta);
953 query.bindValue(":name", cat.name);
954 query.bindValue(":author", cat.author);
955 query.bindValue(":source", cat.source);
956 query.bindValue(":description", cat.description);
957 query.bindValue(":id", cat.id);
958 query.bindValue(":color", cat.color);
959 query.bindValue(":license", cat.license);
960 query.bindValue(":maintainer", cat.maintainer);
961 query.bindValue(":timestamp", cat.timestamp);
962
963 return { query.exec(), query.lastError().text() };
964}
965
967{
968 const auto &cats = get_catalogs(true);
969
970 // find a gap in the ids to use
971 const auto element = std::adjacent_find(
972 cats.cbegin(), cats.cend(), [](const auto & c1, const auto & c2)
973 {
974 return (c1.id >= CatalogsDB::custom_cat_min_id) &&
975 (c2.id >= CatalogsDB::custom_cat_min_id) && (c2.id - c1.id) > 1;
976 });
977
978 return std::max(CatalogsDB::custom_cat_min_id,
979 (element == cats.cend() ? cats.back().id : element->id) + 1);
980}
981
982QString CatalogsDB::dso_db_path()
983{
984 return QDir(KSPaths::writableLocation(QStandardPaths::AppLocalDataLocation))
985 .filePath(Options::dSOCatalogFilename());
986}
987
988std::pair<bool, Catalog> CatalogsDB::read_catalog_meta_from_file(const QString &path)
989{
991 "QSQLITE", QString("tmp_%1_%2").arg(path).arg(get_connection_index())) };
992 db.setDatabaseName(path);
993
994 if (!db.open())
995 return { false, {} };
996
997 QSqlQuery query{ db };
998
999 if (!query.exec("PRAGMA user_version") || !query.next() ||
1000 query.value(0).toInt() < SqlStatements::current_db_version)
1001 {
1003 const auto new_path = tmp.filePath("cat.kscat");
1004
1005 QFile::copy(path, new_path);
1006 db.close();
1007
1009 if (!db.open())
1010 return { false, {} };
1011
1012 const auto &success = migrate_db(query.value(0).toInt(), db);
1013 if (!success.first)
1014 return { false, {} };
1015 }
1016
1017 if (!query.exec(SqlStatements::get_first_catalog) || !query.first())
1018 return { false, {} };
1019
1020 db.close();
1021 return { true, read_catalog(query) };
1022}
1023
1024CatalogStatistics read_statistics(QSqlQuery &query)
1025{
1026 CatalogStatistics stats{};
1027 while (query.next())
1028 {
1029 stats.object_counts[(SkyObject::TYPE)query.value(0).toInt()] =
1030 query.value(1).toInt();
1031 stats.total_count += query.value(1).toInt();
1032 }
1033 return stats;
1034}
1035
1036const std::pair<bool, CatalogStatistics> DBManager::get_master_statistics()
1037{
1038 QSqlQuery query{ m_db };
1039 if (!query.exec(SqlStatements::dso_count_by_type_master))
1040 return { false, {} };
1041
1042 return { true, read_statistics(query) };
1043}
1044
1045const std::pair<bool, CatalogStatistics>
1047{
1048 QSqlQuery query{ m_db };
1049 if (!query.exec(SqlStatements::dso_count_by_type(catalog_id)))
1050 return { false, {} };
1051
1052 return { true, read_statistics(query) };
1053}
1054
1055std::pair<bool, QString>
1057 const CatalogObjectVector &objects)
1058{
1059 {
1060 const auto &success = get_catalog(catalog_id);
1061 if (!success.first)
1062 return { false, i18n("Catalog with id=%1 not found.", catalog_id) };
1063
1064 if (!success.second.mut)
1065 return { false, i18n("Catalog is immutable!") };
1066 }
1067
1068 m_db.transaction();
1069 QSqlQuery query{ m_db };
1070 for (const auto &object : objects)
1071 {
1072 SkyPoint tmp{ object.ra(), object.dec() };
1073 const auto trixel = SkyMesh::Create(m_htmesh_level)->index(&tmp);
1074
1075 bind_catalogobject(query, catalog_id, object, trixel);
1076
1077 if (!query.exec())
1078 {
1079 auto err = query.lastError().text();
1080 if (err.startsWith("UNIQUE"))
1081 err = i18n("The object is already in the catalog!");
1082
1083 return { false, i18n("Could not insert object! %1", err) };
1084 }
1085 }
1086
1087 return { m_db.commit() &&update_catalog_views() &&compile_master_catalog(),
1088 m_db.lastError().text() };
1089};
1090
1092 const int limit)
1093{
1094 QMutexLocker _{ &m_mutex };
1095
1096 QSqlQuery query{ m_db };
1097 if (!query.prepare(SqlStatements::dso_by_wildcard()))
1098 {
1099 return {};
1100 }
1101 query.bindValue(":wildcard", wildcard);
1102 query.bindValue(":limit", limit);
1103
1104 return fetch_objects(query);
1105};
1106
1107std::tuple<bool, const QString, CatalogObjectList>
1109 const int limit)
1110{
1111 QMutexLocker _{ &m_mutex };
1112
1113 QSqlQuery query{ m_db };
1114
1115 if (!query.prepare(SqlStatements::dso_general_query(where, order_by)))
1116 {
1117 return { false, query.lastError().text(), {} };
1118 }
1119
1120 query.bindValue(":limit", limit);
1121
1122 return { false, "", fetch_objects(query) };
1123};
1124
1125CatalogsDB::CatalogColorMap CatalogsDB::parse_color_string(const QString &str)
1126{
1127 CatalogsDB::CatalogColorMap colors{};
1128 if (str == "")
1129 return colors;
1130
1131 const auto &parts = str.split(";");
1132 auto it = parts.constBegin();
1133
1134 if (it->length() > 0) // playing it save
1135 colors["default"] = *it;
1136
1137 while (it != parts.constEnd())
1138 {
1139 const auto &scheme = *(++it);
1140 if (it != parts.constEnd())
1141 {
1142 const auto next = ++it;
1143 if (next == parts.constEnd())
1144 break;
1145
1146 const auto &color = *next;
1147 colors[scheme] = QColor(color);
1148 }
1149 }
1150
1151 return colors;
1152}
1153
1154QString get_name(const QColor &color)
1155{
1156 return color.isValid() ? color.name() : "";
1157}
1158
1159QString CatalogsDB::to_color_string(CatalogColorMap colors)
1160{
1162
1163 color_list << colors["default"].name();
1164 colors.erase("default");
1165
1166 for (const auto &item : colors)
1167 {
1168 if (item.second.isValid())
1169 {
1170 color_list << item.first << item.second.name();
1171 }
1172 }
1173
1174 return color_list.join(";");
1175}
1176
1178{
1179 // no mutex b.c. this is read only
1180 QSqlQuery query{ m_db };
1181
1182 ColorMap colors{};
1183
1184 if (!query.exec(SqlStatements::get_colors))
1185 return colors;
1186
1187 for (const auto &cat : DBManager::get_catalogs(true))
1188 {
1189 colors[cat.id] = parse_color_string(cat.color);
1190 }
1191
1192 while (query.next())
1193 {
1194 const auto &catalog = query.value("catalog").toInt();
1195 const auto &scheme = query.value("scheme").toString();
1196 const auto &color = query.value("color").toString();
1197 colors[catalog][scheme] = QColor(color);
1198 }
1199
1200 return colors;
1201};
1202
1203CatalogsDB::CatalogColorMap CatalogsDB::DBManager::get_catalog_colors(const int id)
1204{
1205 return get_catalog_colors()[id]; // good enough for now
1206};
1207
1208std::pair<bool, QString>
1209CatalogsDB::DBManager::insert_catalog_colors(const int id, const CatalogColorMap &colors)
1210{
1211 QMutexLocker _{ &m_mutex };
1212
1213 QSqlQuery query{ m_db };
1214
1215 if (!query.prepare(SqlStatements::insert_color))
1216 {
1217 return { false, query.lastError().text() };
1218 }
1219
1220 query.bindValue(":catalog", id);
1221 for (const auto &item : colors)
1222 {
1223 query.bindValue(":scheme", item.first);
1224 query.bindValue(":color", item.second);
1225
1226 if (!query.exec())
1227 return { false, query.lastError().text() };
1228 }
1229
1230 return { true, "" };
1231};
a dms subclass that caches its sine and cosine values every time the angle is changed.
Definition cachingdms.h:19
A simple container object to hold the minimum information for a Deep Sky Object to be drawn on the sk...
float flux() const
const oid getId() const
float a() const
const oid getObjectId() const
const QString & catalogIdentifier() const
double pa() const override
float b() const
Manages the catalog database and provides an interface to provide an interface to query and modify th...
Definition catalogsdb.h:183
CatalogObjectList get_objects_all()
Get all objects from the database.
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=NaN::f, const QString &lname=QString(), const QString &catalog_identifier=QString(), const float a=0.0, const float b=0.0, const double pa=0.0, const float flux=0)
Add a CatalogObject to a table with `catalog_id`.
int find_suitable_catalog_id()
Finds the smallest free id for a catalog.
bool compile_master_catalog()
Compiles the master catalog by merging the individual catalogs based on oid and precedence and create...
bool update_catalog_views()
Updates the all_catalog_view so that it includes all known catalogs.
CatalogObjectList find_objects_by_name(const QString &name, const int limit=-1, const bool exactMatchOnly=false)
Find an objects by name.
std::pair< bool, QString > set_catalog_enabled(const int id, const bool enabled)
Enable or disable a catalog.
ColorMap get_catalog_colors()
const std::pair< bool, CatalogStatistics > get_catalog_statistics(const int catalog_id)
CatalogObjectList find_objects_by_wildcard(const QString &wildcard, const int limit=-1)
Find an objects by searching the name four wildcard.
std::tuple< bool, const QString, CatalogObjectList > general_master_query(const QString &where, const QString &order_by="", const int limit=-1)
Find an objects by searching the master catlog with a query like SELECT ... FROM master WHERE \p wher...
std::pair< bool, QString > update_catalog_meta(const Catalog &cat)
Update the metatadata `catalog`.
std::pair< bool, QString > remove_catalog(const int id)
remove a catalog
const std::pair< bool, Catalog > get_catalog(const int id)
std::pair< bool, QString > remove_object(const int catalog_id, const CatalogObject::oid &id)
Remove the catalog object with the `oid` from the catalog with the `catalog_id`.
CatalogObjectList get_objects_in_catalog(SkyObject::TYPE type, const int catalog_id, float maglim=default_maglim, int limit=-1)
Get limit objects from the catalog with `catalog_id` of type with magnitude smaller than maglim (smal...
DBManager(const QString &filename)
Constructs a database manager from the filename which is resolved to a path in the kstars data direct...
std::pair< bool, QString > register_catalog(const int id, const QString &name, const bool mut, const bool enabled, const double precedence, const QString &author=cat_defaults.author, const QString &source=cat_defaults.source, const QString &description=cat_defaults.description, const int version=cat_defaults.version, const QString &color=cat_defaults.color, const QString &license=cat_defaults.license, const QString &maintainer=cat_defaults.maintainer, const QDateTime &timestamp=cat_defaults.timestamp)
Registers a new catalog in the database.
std::pair< bool, QString > import_catalog(const QString &file_path, const bool overwrite=false)
Loads a dumped catalog from path `file_path`.
std::pair< bool, QString > add_objects(const int catalog_id, const CatalogObjectVector &objects)
Add the `objects` to a table with `catalog_id`.
const std::vector< Catalog > get_catalogs(bool include_disabled=false)
CatalogObjectList get_objects(float maglim=default_maglim, int limit=-1)
Get limit objects with magnitude smaller than maglim (smaller = brighter) from the database.
bool catalog_exists(const int id)
std::pair< bool, QString > insert_catalog_colors(const int id, const CatalogColorMap &colors)
Saves the configures colors of the catalog with id id in colors into the database.
const std::pair< bool, CatalogStatistics > get_master_statistics()
std::pair< bool, QString > dump_catalog(int catalog_id, QString file_path)
Dumps the catalog with `id` into the file under the path file_path.
std::pair< bool, CatalogObject > get_object(const CatalogObject::oid &oid)
Get an object by `oid`.
std::pair< bool, QString > copy_objects(const int id_1, const int id_2)
Clone objects from the catalog with `id_1` to another with id_2.
Database related error, thrown when database access fails or an action does not succeed.
Definition catalogsdb.h:682
static SkyMesh * Create(int level)
creates the single instance of SkyMesh.
Definition skymesh.cpp:25
virtual QString name(void) const
Definition skyobject.h:145
virtual QString longname(void) const
Definition skyobject.h:164
int type(void) const
Definition skyobject.h:188
float mag() const
Definition skyobject.h:206
TYPE
The type classification of the SkyObject.
Definition skyobject.h:112
The sky coordinates of a point in the sky.
Definition skypoint.h:45
const CachingDms & ra0() const
Definition skypoint.h:251
const CachingDms & dec0() const
Definition skypoint.h:257
An angle, stored as degrees, but expressible in many ways.
Definition dms.h:38
const double & Degrees() const
Definition dms.h:141
QString i18n(const char *text, const TYPE &arg...)
Type type(const QSqlDatabase &db)
std::optional< QSqlQuery > query(const QString &queryStatement)
QString name(StandardAction id)
bool isValid() const const
QString name(NameFormat format) const const
QDateTime currentDateTime()
QString filePath(const QString &fileName) const const
bool copy(const QString &fileName, const QString &newName)
QSqlQuery exec(const QString &query) const const
QSqlDatabase addDatabase(QSqlDriver *driver, const QString &connectionName)
QSqlError lastError() const const
bool rollback()
void setDatabaseName(const QString &name)
bool transaction()
QString text() const const
void bindValue(const QString &placeholder, const QVariant &val, QSql::ParamType paramType)
bool exec()
void finish()
bool isActive() const const
bool next()
QString arg(Args &&... args) const const
void resize(qsizetype newSize, QChar fillChar)
QStringList split(QChar sep, Qt::SplitBehavior behavior, Qt::CaseSensitivity cs) const const
QTextStream & dec(QTextStream &stream)
QUuid createUuid()
Holds statistical information about the objects in a catalog.
Definition catalogsdb.h:119
A simple struct to hold information about catalogs.
Definition catalogsdb.h:37
This file is part of the KDE documentation.
Documentation copyright © 1996-2024 The KDE developers.
Generated on Fri May 17 2024 11:48:25 by doxygen 1.10.0 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.