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 #pragma once 8 9 #include <QSqlDatabase> 10 #include <QSqlError> 11 #include <exception> 12 #include <list> 13 #include <QString> 14 #include <QList> 15 #include <catalogsdb_debug.h> 16 #include <QSqlQuery> 17 #include <QMutex> 18 19 #include "polyfills/qstring_hash.h" 20 #include <unordered_map> 21 22 #include <unordered_set> 23 #include <utility> 24 #include "catalogobject.h" 25 #include "nan.h" 26 #include "typedef.h" 27 28 namespace CatalogsDB 29 { 30 /** 31 * A simple struct to hold information about catalogs. 32 */ 33 struct Catalog 34 { 35 /** 36 * The catalog id. 37 */ 38 int id = -1; 39 40 /** 41 * The catalog mame. 42 */ 43 QString name = "Unknown"; 44 45 /** 46 * The precedence level of a catalog. 47 * 48 * If doublicate objects exist in the database, the one from the 49 * catalog with the highest precedence winns. 50 */ 51 double precedence = 0; 52 53 /** 54 * The author of the catalog. 55 */ 56 QString author = ""; 57 58 /** 59 * The catalog source. 60 */ 61 QString source = ""; 62 63 /** 64 * A (short) description for the catalog. 65 * QT html is allowed. 66 */ 67 QString description = ""; 68 69 /** 70 * Wether the catalog is mutable. 71 */ 72 bool mut = false; 73 74 /** 75 * Wether the catalog is enabled. 76 */ 77 bool enabled = false; 78 79 /** 80 * The catalog version. 81 */ 82 int version = -1; 83 84 /** 85 * The catalog color in the form `[default color];[scheme file 86 * name];[color]...`. 87 */ 88 QString color = ""; 89 90 /** 91 * The catalog license. 92 */ 93 QString license = ""; 94 95 /** 96 * The catalog maintainer. 97 */ 98 QString maintainer = ""; 99 100 /** 101 * Build time of the catalog. Usually only catalogs with the same 102 * timestamp can be considered dedublicated. 103 * 104 * A `null` timestamp indicates that the catalog has not been 105 * built by the catalog repository. 106 */ 107 QDateTime timestamp{}; 108 }; 109 110 const Catalog cat_defaults{}; 111 112 /** 113 * Holds statistical information about the objects in a catalog. 114 */ 115 struct CatalogStatistics 116 { 117 std::map<SkyObject::TYPE, int> object_counts; 118 int total_count = 0; 119 }; 120 121 const QString db_file_extension = "kscat"; 122 constexpr int application_id = 0x4d515158; 123 constexpr int custom_cat_min_id = 1000; 124 constexpr int user_catalog_id = 0; 125 constexpr float default_maglim = 99; 126 const QString flux_unit = "mag"; 127 const QString flux_frequency = "400 nm"; 128 using CatalogColorMap = std::map<QString, QColor>; 129 using ColorMap = std::map<int, CatalogColorMap>; 130 using CatalogObjectList = std::list<CatalogObject>; 131 using CatalogObjectVector = std::vector<CatalogObject>; 132 133 /** 134 * \returns A hash table of the form `color scheme: color` by 135 * parsing a string of the form `[default color];[scheme file 136 * name];[color]...`. 137 */ 138 CatalogColorMap parse_color_string(const QString &str); 139 140 /** 141 * \returns A color string of the form`[default color];[scheme file 142 * name];[color]...`. 143 * 144 * The inverse of `CatalogsDB::parse_color_string`. 145 */ 146 QString to_color_string(CatalogColorMap colors); 147 148 /** 149 * Manages the catalog database and provides an interface to provide an 150 * interface to query and modify the database. For more information on 151 * how the catalog database system works see the KStars Handbook. 152 * 153 * The class manages a database connection which is assumed to be 154 * working (invariant). If the database can't be accessed a 155 * DatabaseError is thrown upon construction. The manager is designed 156 * to hold as little state as possible because the database should be 157 * the single source of truth. Prepared statements are made class 158 * members, only if they are performance critical. 159 * 160 * Most methods in this class are thread safe. 161 * 162 * The intention is that you access a/the catalogs database directly 163 * locally in the code where objects from the database are required and 164 * not through layers of references and pointers. 165 * 166 * The main DSO database can be accessed as follows: 167 * ```cpp 168 * CatalogsDB::DBManager manager{ CatalogsDB::dso_db_path() }; 169 * for(auto& o : manager.get_objects(10)) { 170 * // do something 171 * } 172 * ``` 173 * 174 * To query the database, first check if the required query is already 175 * hardcoded into the `DBManager`. If this is not the case you can either 176 * add it (if it is performance critical and executed frequently) or use 177 * `DBManager::general_master_query` to construct a custom `SQL` query. 178 */ 179 class DBManager 180 { 181 public: 182 /** 183 * Constructs a database manager from the \p filename which is 184 * resolved to a path in the kstars data directory. 185 * 186 * The constructor resolves the path to the database, opens it 187 * (throws if that does not work), checks the database version 188 * (throws if that does not match), initializes the database, 189 * registers the user catalog and updates the all_catalog_view. 190 */ 191 DBManager(const QString &filename); 192 DBManager(const DBManager &other); 193 194 DBManager &operator=(DBManager other) 195 { 196 using std::swap; 197 DBManager tmp{ other }; 198 199 m_db_file = other.m_db_file; 200 swap(m_db, other.m_db); 201 swap(m_q_cat_by_id, other.m_q_cat_by_id); 202 swap(m_q_obj_by_trixel, other.m_q_obj_by_trixel); 203 swap(m_q_obj_by_name, other.m_q_obj_by_name); 204 swap(m_q_obj_by_name_exact, other.m_q_obj_by_name_exact); 205 swap(m_q_obj_by_maglim, other.m_q_obj_by_maglim); 206 swap(m_q_obj_by_maglim_and_type, other.m_q_obj_by_maglim_and_type); 207 swap(m_q_obj_by_oid, other.m_q_obj_by_oid); 208 209 return *this; 210 }; 211 ~DBManager()212 ~DBManager() 213 { 214 m_db.commit(); 215 m_db.close(); 216 } 217 218 /** 219 * @return the filename of the database 220 */ db_file_name()221 const QString &db_file_name() const { return m_db_file; }; 222 223 /** 224 * @return wether the catalog with the \p id has been found and 225 * the catalog. 226 * 227 * @todo use std::optional when transitioning to c++17 228 */ 229 const std::pair<bool, Catalog> get_catalog(const int id); 230 231 /** 232 * \return a vector with all catalogs from the database. If \p 233 * include_disabled is `true`, disabled catalogs will be included. 234 */ 235 const std::vector<Catalog> get_catalogs(bool include_disabled = false); 236 237 /** 238 * @return true if the catalog with \p id exists 239 * 240 * @todo use std::optional when transitioning to c++17 241 */ 242 bool catalog_exists(const int id); 243 244 /** 245 * @return return a vector of objects in the trixel with \p id. 246 */ 247 CatalogObjectVector get_objects_in_trixel(const int trixel); 248 249 /** 250 * \brief Find an objects by name. 251 * 252 * This will search the `name`, `long_name` and `catalog_identifier` 253 * fields in all enabled catalogs for \p `name` and then return a new 254 * instance of `CatalogObject` sourced from the master catalog. 255 * 256 * \param limit Upper limit to the quanitity of results. `-1` means "no 257 * limit" 258 * \param exactMatchOnly If true, the supplied name must match exactly 259 * 260 * \return a list of matching objects 261 */ 262 CatalogObjectList find_objects_by_name(const QString &name, const int limit = -1, 263 const bool exactMatchOnly = false); 264 265 /** 266 * \brief Find an objects by name in the catalog with \p `catalog_id`. 267 * 268 * \return a list of matching objects 269 */ 270 CatalogObjectList find_objects_by_name(const int catalog_id, const QString &name, 271 const int limit = -1); 272 273 /** 274 * \brief Find an objects by searching the name four wildcard. See 275 * the LIKE sqlite statement. 276 * 277 * \return a list of matching objects 278 */ 279 CatalogObjectList find_objects_by_wildcard(const QString &wildcard, 280 const int limit = -1); 281 /** 282 * \brief Find an objects by searching the master catlog with a 283 * query like `SELECT ... FROM master WHERE \p where ORDER BY \p 284 * order_by ...`. 285 * 286 * To be used if performance does not matter (much). 287 * \p order_by can be ommitted. 288 * 289 * \return wether the query was successful, an error message if 290 * any and a list of matching objects 291 */ 292 std::tuple<bool, const QString, CatalogObjectList> 293 general_master_query(const QString &where, const QString &order_by = "", 294 const int limit = -1); 295 296 /** 297 * \brief Get an object by \p `oid`. Optinally a \p `catalog_id` can be speicfied. 298 * 299 * \returns if the object was found and the object itself 300 */ 301 std::pair<bool, CatalogObject> get_object(const CatalogObject::oid &oid); 302 std::pair<bool, CatalogObject> get_object(const CatalogObject::oid &oid, 303 const int catalog_id); 304 305 /** 306 * Get \p limit objects with magnitude smaller than \p maglim (smaller = 307 * brighter) from the database. 308 */ 309 CatalogObjectList get_objects(float maglim = default_maglim, int limit = -1); 310 311 /** 312 * Get \p limit objects of \p type with magnitude smaller than \p 313 * maglim (smaller = brighter) from the database. Optionally one 314 * can filter by \p `catalog_id`. 315 */ 316 CatalogObjectList get_objects(SkyObject::TYPE type, float maglim = default_maglim, 317 int limit = -1); 318 /** 319 * Get \p limit objects from the catalog with \p `catalog_id` of 320 * \p type with magnitude smaller than \p maglim (smaller = 321 * brighter) from the database. Optionally one can filter by \p 322 * `catalog_id`. 323 */ 324 CatalogObjectList get_objects_in_catalog(SkyObject::TYPE type, const int catalog_id, 325 float maglim = default_maglim, 326 int limit = -1); 327 328 /** 329 * @return return the htmesh level used by the catalog db 330 */ htmesh_level()331 int htmesh_level() const { return m_htmesh_level; }; 332 333 /** 334 * \brief Enable or disable a catalog. 335 * \return `true` in case of succes, `false` and an error message in case 336 * of an error 337 * 338 * This will recreate the master table. 339 */ 340 std::pair<bool, QString> set_catalog_enabled(const int id, const bool enabled); 341 342 /** 343 * \brief remove a catalog 344 * \return `true` in case of succes, `false` and an error message 345 * in case of an error 346 * 347 * This will recreate the master table. 348 */ 349 std::pair<bool, QString> remove_catalog(const int id); 350 351 /** 352 * Add a `CatalogObject` to a table with \p `catalog_id`. For the rest of 353 * the arguments see `CatalogObject::CatalogObject`. 354 * 355 * \returns wether the operation was successful and if not, an error 356 * message 357 */ 358 std::pair<bool, QString> add_object(const int catalog_id, const SkyObject::TYPE t, 359 const CachingDms &r, const CachingDms &d, 360 const QString &n, const float m = NaN::f, 361 const QString &lname = QString(), 362 const QString &catalog_identifier = QString(), 363 const float a = 0.0, const float b = 0.0, 364 const double pa = 0.0, const float flux = 0); 365 366 /** 367 * Add the \p `object` to a table with \p `catalog_id`. For the 368 * rest of the arguments see `CatalogObject::CatalogObject`. 369 * 370 * \returns wether the operation was successful and if not, an 371 * error message 372 */ 373 std::pair<bool, QString> add_object(const int catalog_id, const CatalogObject &obj); 374 375 /** 376 * Add the \p `objects` to a table with \p `catalog_id`. For the 377 * rest of the arguments see `CatalogObject::CatalogObject`. 378 * 379 * \returns wether the operation was successful and if not, an 380 * error message 381 */ 382 std::pair<bool, QString> add_objects(const int catalog_id, 383 const CatalogObjectVector &objects); 384 385 /** 386 * Remove the catalog object with the \p `oid` from the catalog with the 387 * \p `catalog_id`. 388 * 389 * Refreshes the master catalog. 390 * 391 * \returns wether the operation was successful and if not, an 392 * error message 393 */ 394 std::pair<bool, QString> remove_object(const int catalog_id, 395 const CatalogObject::oid &id); 396 397 /** 398 * Dumps the catalog with \p `id` into the file under the path \p 399 * `file_path`. This file can then be imported with 400 * `import_catalog`. If the file already exists, it will be 401 * overwritten. 402 * 403 * The `user_version` and `application_id` pragmas are set to special 404 * values, but otherwise the dump format is equal to the internal 405 * database format. 406 * 407 * \returns wether the operation was successful and if not, an error 408 * message 409 */ 410 std::pair<bool, QString> dump_catalog(int catalog_id, QString file_path); 411 412 /** 413 * Loads a dumped catalog from path \p `file_path`. Will overwrite 414 * an existing catalog if \p `overwrite` is set to true. Immutable 415 * catalogs are overwritten by default. 416 * 417 * Checks if the pragma `application_id` matches 418 * `CatalogsDB::application_id` and the pragma `user_version` to match 419 * the database format version. 420 * 421 * \returns wether the operation was successful and if not, an error 422 * message 423 */ 424 std::pair<bool, QString> import_catalog(const QString &file_path, 425 const bool overwrite = false); 426 /** 427 * Registers a new catalog in the database. 428 * 429 * For the parameters \sa Catalog. The catalog gets inserted into 430 * `m_catalogs`. The `all_catalog_view` is updated. 431 * 432 * \return true in case of success, false in case of an error 433 * (along with the error) 434 */ 435 std::pair<bool, QString> 436 register_catalog(const int id, const QString &name, const bool mut, 437 const bool enabled, const double precedence, 438 const QString &author = cat_defaults.author, 439 const QString &source = cat_defaults.source, 440 const QString &description = cat_defaults.description, 441 const int version = cat_defaults.version, 442 const QString &color = cat_defaults.color, 443 const QString &license = cat_defaults.license, 444 const QString &maintainer = cat_defaults.maintainer, 445 const QDateTime ×tamp = cat_defaults.timestamp); 446 447 std::pair<bool, QString> register_catalog(const Catalog &cat); 448 449 /** 450 * Update the metatadata \p `catalog`. 451 * 452 * The updated fields are: title, author, source, description. 453 * 454 * \return true in case of success, false in case of an error 455 * (along with the error). 456 */ 457 std::pair<bool, QString> update_catalog_meta(const Catalog &cat); 458 459 /** 460 * Clone objects from the catalog with \p `id_1` to another with `id_2`. Useful to create a 461 * custom catalog from an immutable one. 462 */ 463 std::pair<bool, QString> copy_objects(const int id_1, const int id_2); 464 465 /** 466 * Finds the smallest free id for a catalog. 467 */ 468 int find_suitable_catalog_id(); 469 470 /** 471 * \returns statistics about the master catalog. 472 */ 473 const std::pair<bool, CatalogStatistics> get_master_statistics(); 474 475 /** 476 * \returns statistics about the catalog with \p `catalog_id`. 477 */ 478 const std::pair<bool, CatalogStatistics> get_catalog_statistics(const int catalog_id); 479 480 /** 481 * Compiles the master catalog by merging the individual catalogs based 482 * on `oid` and precedence and creates an index by (trixel, magnitude) on 483 * the master table. **Caution** you may want to call 484 * `update_catalog_views` beforhand. 485 * 486 * @return true in case of success, false in case of an error 487 */ 488 bool compile_master_catalog(); 489 490 /** 491 * Updates the all_catalog_view so that it includes all known 492 * catalogs. 493 * 494 * @return true in case of success, false in case of an error 495 */ 496 bool update_catalog_views(); 497 498 /** \returns the catalog colors as a hash table of the form `catalog id: 499 * scheme: color`. 500 * 501 * The colors are loaded from the `Catalog::color` field and the 502 * `SqlStatements::color_table` in that order. 503 */ 504 ColorMap get_catalog_colors(); 505 506 /** \returns the catalog colors as a hash table of for the catalog 507 * with \p id in the form `scheme: color`. 508 * 509 * The colors are loaded from the `Catalog::color` field and the 510 * `SqlStatements::color_table` in that order. 511 */ 512 CatalogColorMap get_catalog_colors(const int id); 513 514 /** Saves the configures colors of the catalog with id \p id in \p 515 * colors into the database. \returns wether the insertion was 516 * possible and an error message if not. 517 */ 518 std::pair<bool, QString> insert_catalog_colors(const int id, 519 const CatalogColorMap &colors); 520 521 private: 522 /** 523 * The backing catalog database. 524 */ 525 QSqlDatabase m_db; 526 527 /** 528 * The filename of the database. 529 * 530 * Will be a reference to a member of `m_db_paths`. 531 */ 532 std::reference_wrapper<const QString> m_db_file; 533 534 //@{ 535 /** 536 * Some performance criticall sql queries are prepared stored as memebers. 537 * When using those queries `m_mutex` should be locked! 538 * 539 * \sa prepare_queries 540 */ 541 542 QSqlQuery m_q_cat_by_id; 543 QSqlQuery m_q_obj_by_trixel; 544 QSqlQuery m_q_obj_by_name; 545 QSqlQuery m_q_obj_by_name_exact; 546 QSqlQuery m_q_obj_by_maglim; 547 QSqlQuery m_q_obj_by_maglim_and_type; 548 QSqlQuery m_q_obj_by_oid; 549 //@} 550 551 /** 552 * The level of the htmesh used to index the catalog entries. 553 * 554 * If the htmesh level of a catalog is different, the catalog will 555 * be reindexed upon importing it. 556 * 557 * A value of -1 means that the htmesh-level has not been 558 * deterined yet. 559 */ 560 int m_htmesh_level = -1; 561 562 /** 563 * The version of the database. 564 * 565 * A value of -1 means that the htmesh-level has not been 566 * deterined yet. 567 */ 568 int m_db_version = -1; 569 570 /** 571 * A simple mutex to be locked when using prepared statements, 572 * that are stored in the class. 573 */ 574 QMutex m_mutex; 575 576 //@{ 577 /** 578 * Helpers 579 */ 580 581 /** 582 * Initializes the database with the minimum viable tables. 583 * 584 * The catalog registry is created and the database version is set 585 * to SqlStatements::current_db_version and the htmesh-level is 586 * set to SqlStatements::default_htmesh_level if they don't exist. 587 * 588 * @return true in case of success, false in case of an error 589 */ 590 bool initialize_db(); 591 592 /** 593 * Reads the database version and the htmesh level from the 594 * database. If the meta table does not exist, the default vaulues 595 * SqlStatements::current_db_version and 596 * SqlStatements::default_htmesh_level. 597 * 598 * @return [version, htmesh-level, is-init?] 599 */ 600 std::tuple<int, int, bool> get_db_meta(); 601 602 /** 603 * Gets a vector of catalog ids of catalogs. If \p include_disabled is 604 * `true`, disabled catalogs will be included. 605 */ 606 std::vector<int> get_catalog_ids(bool include_enabled = false); 607 608 /** 609 * Prepares performance critical sql queries. 610 * 611 * @return [success, error] 612 */ 613 std::pair<bool, QSqlError> prepare_queries(); 614 615 /** 616 * Read a `CatalogObject` from the tip of the \p query. 617 */ 618 CatalogObject read_catalogobject(const QSqlQuery &query) const; 619 620 /** 621 * Read the first `CatalogObject` from the tip of the \p `query` 622 * that hasn't been exec'd yet. 623 */ 624 std::pair<bool, CatalogObject> read_first_object(QSqlQuery &query) const; 625 626 /** 627 * Read all `CatalogObject`s from the \p query. 628 */ 629 CatalogObjectList fetch_objects(QSqlQuery &query) const; 630 631 /** 632 * Internal implementation to forcably remove a catalog (even the 633 * user catalog, use with caution!) 634 */ 635 std::pair<bool, QString> remove_catalog_force(const int id); 636 637 /** 638 * A list of database paths. The index gets stored in the 639 * `CatalogObject` and can be used to retrieve the path to the 640 * database. 641 */ 642 static QSet<QString> m_db_paths; 643 //@} 644 }; 645 646 /** 647 * Database related error, thrown when database access fails or an 648 * action does not succeed. 649 * 650 * QSqlError is not used here to encapsulate the database further. 651 */ 652 class DatabaseError : std::exception 653 { 654 public: 655 enum class ErrorType 656 { 657 OPEN, 658 VERSION, 659 INIT, 660 CREATE_CATALOG, 661 CREATE_MASTER, 662 NOT_FOUND, 663 PREPARE, 664 UNKNOWN 665 }; 666 667 DatabaseError(QString message, ErrorType type = ErrorType::UNKNOWN, 668 const QSqlError &error = QSqlError()) 669 : m_message{ std::move(message) }, m_type{ type }, m_error{ error }, m_report{ 670 m_message.toStdString() + 671 (error.text().length() > 0 ? "\nSQL ERROR: " + error.text().toStdString() : 672 std::string("")) 673 } {}; 674 what()675 const char *what() const noexcept override { return m_report.c_str(); } message()676 const QString &message() const noexcept { return m_message; } type()677 ErrorType type() const noexcept { return m_type; } 678 679 private: 680 const QString m_message; 681 const ErrorType m_type; 682 const QSqlError m_error; 683 const std::string m_report; 684 }; 685 686 /** \returns the path to the dso database */ 687 QString dso_db_path(); 688 689 /** \returns true and a catalog if the catalog metadata (name, author, 690 ...) can be read */ 691 std::pair<bool, Catalog> read_catalog_meta_from_file(const QString &path); 692 } // namespace CatalogsDB 693