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

KDE's Doxygen guidelines are available online.