11 #include <QMutexLocker>
12 #include <qsqldatabase.h>
13 #include "cachingdms.h"
14 #include "catalogsdb.h"
18 #include "final_action.h"
19 #include "sqlstatements.cpp"
21 using namespace CatalogsDB;
27 int get_connection_index()
29 static int connection_index = 0;
30 return connection_index++;
37 query.setForwardOnly(forward_only);
38 if (!
query.prepare(statement))
40 throw DatabaseError(
"Can't prepare query!", DatabaseError::ErrorType::PREPARE,
50 std::pair<bool, QString> migrate_db(
const int version,
QSqlDatabase &db,
53 if (prefix.size() > 0)
60 const auto success = add_ts.
exec(
QString(
"ALTER TABLE %1catalogs ADD COLUMN "
61 "timestamp DEFAULT NULL")
69 if (version < 3 && prefix ==
"")
72 const auto success = add_colors.
exec(SqlStatements::create_colors_table);
82 "QSQLITE",
QString(
"cat_%1_%2").arg(filename).arg(get_connection_index())) },
83 m_db_file{ *m_db_paths.insert(filename) }
92 DatabaseError::ErrorType::OPEN, m_db.lastError());
96 std::tie(m_db_version, m_htmesh_level, init) = get_db_meta();
98 if (!init && m_db_version > 0 && m_db_version < SqlStatements::current_db_version)
100 const auto &backup_path =
QString(
"%1.%2").
arg(m_db_file).
arg(
106 QString(
"Could not backup dso database before upgrading."),
107 DatabaseError::ErrorType::VERSION,
QSqlError{});
110 const auto &success = migrate_db(m_db_version, m_db);
113 m_db_version = SqlStatements::current_db_version;
115 version_query.
prepare(SqlStatements::update_version);
116 version_query.bindValue(
":version", m_db_version);
118 if (!version_query.exec())
121 DatabaseError::ErrorType::VERSION,
122 version_query.lastError());
127 QString(
"Wrong database version. Expected %1 and got %2 and "
128 "migration is not possible.")
129 .arg(SqlStatements::current_db_version)
131 DatabaseError::ErrorType::VERSION, success.second);
135 master_exists.
exec(SqlStatements::exists_master);
136 const bool master_does_exist = master_exists.next();
137 master_exists.finish();
139 if (init || !master_does_exist)
141 if (!initialize_db())
144 DatabaseError::ErrorType::INIT, m_db.lastError());
147 if (!catalog_exists(SqlStatements::user_catalog_id))
150 register_catalog(SqlStatements::user_catalog_id,
151 SqlStatements::user_catalog_name,
true,
true, 1);
155 DatabaseError::ErrorType::CREATE_CATALOG, res.second);
159 if (!update_catalog_views())
162 DatabaseError::ErrorType::CREATE_CATALOG,
166 if (!compile_master_catalog())
169 DatabaseError::ErrorType::CREATE_MASTER,
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_lim = make_query(m_db, SqlStatements::dso_by_lim,
true);
181 m_q_obj_by_maglim = make_query(m_db, SqlStatements::dso_by_maglim,
true);
182 m_q_obj_by_maglim_and_type =
183 make_query(m_db, SqlStatements::dso_by_maglim_and_type,
true);
184 m_q_obj_by_oid = make_query(m_db, SqlStatements::dso_by_oid,
true);
189 bool DBManager::initialize_db()
191 if (m_db_version < 0 || m_htmesh_level < 1)
192 throw std::runtime_error(
"DBManager not initialized properly, m_db_vesion and "
193 "m_htmesh_level have to be set.");
195 if (!m_db.
exec(SqlStatements::create_meta_table).
isActive())
198 if (!m_db.
exec(SqlStatements::create_colors_table).
isActive())
202 meta_query.prepare(SqlStatements::set_meta);
203 meta_query.bindValue(0, m_db_version);
204 meta_query.bindValue(1, m_htmesh_level);
205 meta_query.bindValue(2,
false);
207 if (!meta_query.exec())
210 return m_db.
exec(SqlStatements::create_catalog_list_table).
isActive();
213 std::tuple<int, int, bool> DBManager::get_db_meta()
215 auto query = m_db.
exec(SqlStatements::get_meta);
221 return { SqlStatements::current_db_version, SqlStatements::default_htmesh_level,
225 std::vector<int> DBManager::get_catalog_ids(
bool include_disabled)
227 auto query = m_db.
exec(include_disabled ? SqlStatements::get_all_catalog_ids :
228 SqlStatements::get_catalog_ids);
230 std::vector<int> ids;
243 const auto &ids = get_catalog_ids();
245 auto _ = gsl::finally([&]() { m_db.
commit(); });
250 query.exec(
QString(
"DROP VIEW IF EXISTS ") + SqlStatements::all_catalog_view);
261 view += SqlStatements::all_catalog_view;
265 for (
auto *field : SqlStatements::catalog_collumns)
267 prefixed <<
QString(
"c.") + field;
270 QString prefixed_joined = prefixed.join(
",");
275 catalog_queries << SqlStatements::all_catalog_view_body(
276 prefixed_joined, SqlStatements::catalog_prefix,
id);
281 catalog_queries << SqlStatements::all_catalog_view_body(
282 prefixed_joined, SqlStatements::catalog_prefix, 0) +
286 view += catalog_queries.join(
"\nUNION ALL\n");
287 result &= query.exec(view);
293 query.bindValue(
":id", cat.
id);
294 query.bindValue(
":name", cat.
name);
295 query.bindValue(
":mut", cat.
mut);
296 query.bindValue(
":enabled", cat.
enabled);
297 query.bindValue(
":precedence", cat.
precedence);
298 query.bindValue(
":author", cat.
author);
299 query.bindValue(
":source", cat.
source);
301 query.bindValue(
":version", cat.
version);
302 query.bindValue(
":color", cat.
color);
303 query.bindValue(
":license", cat.
license);
304 query.bindValue(
":maintainer", cat.
maintainer);
305 query.bindValue(
":timestamp", cat.
timestamp);
309 const int id,
const QString &name,
const bool mut,
const bool enabled,
310 const double precedence,
const QString &author,
const QString &source,
311 const QString &description,
const int version,
const QString &color,
314 return register_catalog({ id, name, precedence, author, source, description, mut,
315 enabled,
version, color, license, maintainer, timestamp });
321 return {
false,
i18n(
"Catalog with that ID already exists.") };
325 if (!
query.exec(SqlStatements::create_catalog_table(cat.
id)))
327 return {
false,
query.lastError().text() };
330 query.prepare(SqlStatements::insert_catalog);
331 bind_catalog(query, cat);
333 return {
query.exec(),
query.lastError().text() };
338 auto _ = gsl::finally([&]() { m_db.
commit(); });
342 if (!query.exec(SqlStatements::drop_master))
347 if (!query.exec(SqlStatements::create_master))
353 success &= query.exec(SqlStatements::create_master_trixel_index);
354 success &= query.exec(SqlStatements::create_master_mag_index);
355 success &= query.exec(SqlStatements::create_master_type_index);
356 success &= query.exec(SqlStatements::create_master_name_index);
362 return { query.
value(
"id").toInt(),
363 query.
value(
"name").toString(),
364 query.
value(
"precedence").toDouble(),
365 query.
value(
"author").toString(),
366 query.
value(
"source").toString(),
367 query.
value(
"description").toString(),
368 query.
value(
"mut").toBool(),
369 query.
value(
"enabled").toBool(),
370 query.
value(
"version").toInt(),
371 query.
value(
"color").toString(),
372 query.
value(
"license").toString(),
373 query.
value(
"maintainer").toString(),
374 query.
value(
"timestamp").toDateTime() };
382 if (!m_q_cat_by_id.
exec())
383 return {
false, {} };
385 if (!m_q_cat_by_id.
next())
386 return {
false, {} };
388 Catalog cat{ read_catalog(m_q_cat_by_id) };
391 return {
true, cat };
398 auto end = gsl::finally([&]() { m_q_cat_by_id.
finish(); });
400 if (!m_q_cat_by_id.
exec())
403 return m_q_cat_by_id.
next();
430 const double position_angle =
query.
value(10).toDouble();
432 const int catalog_id =
query.
value(12).toInt();
435 mag,
name, long_name, catalog_identifier,
436 catalog_id, major, minor, position_angle,
440 CatalogObjectVector DBManager::_get_objects_in_trixel_generic(
QSqlQuery& query,
const int trixel)
443 query.bindValue(0, trixel);
447 QString(
"The by-trixel query for objects in trixel=%1 failed.")
449 DatabaseError::ErrorType::UNKNOWN,
query.lastError());
451 CatalogObjectVector objects;
463 while (
query.previous())
465 objects.push_back(read_catalogobject(query));
474 CatalogObjectList DBManager::fetch_objects(
QSqlQuery &query)
const
476 CatalogObjectList objects;
477 auto _ = gsl::finally([&]() {
query.finish(); });
481 if (!
query.isActive())
484 objects.
push_back(read_catalogobject(query));
490 const bool exactMatchOnly)
496 return CatalogObjectList();
499 m_q_obj_by_name_exact.
bindValue(
":name", name);
500 CatalogObjectList objs { fetch_objects(m_q_obj_by_name_exact) };
502 if ((limit == 1 && objs.size() > 0) || exactMatchOnly)
505 Q_ASSERT(objs.size() <= 1);
507 m_q_obj_by_name.
bindValue(
":name", name);
508 m_q_obj_by_name.
bindValue(
":limit",
int(limit - objs.size()));
510 CatalogObjectList moreObjects = fetch_objects(m_q_obj_by_name);
511 moreObjects.splice(moreObjects.begin(), objs);
517 const QString &name,
const int limit)
521 query.prepare(SqlStatements::dso_by_name_and_catalog(catalog_id));
522 query.bindValue(
":name", name);
523 query.bindValue(
":limit", limit);
524 query.bindValue(
":catalog", catalog_id);
526 return fetch_objects(query);
529 std::pair<bool, CatalogObject> DBManager::read_first_object(
QSqlQuery &query)
const
531 if (!query.exec() || !query.
first())
532 return {
false, {} };
534 return {
true, read_catalogobject(query) };
542 auto f = gsl::finally([&]() {
546 return read_first_object(m_q_obj_by_oid);
550 const int catalog_id)
555 query.prepare(SqlStatements::dso_by_oid_and_catalog(catalog_id));
556 query.bindValue(0, oid);
558 return read_first_object(query);
564 m_q_obj_by_maglim.
bindValue(
":maglim", maglim);
565 m_q_obj_by_maglim.
bindValue(
":limit", limit);
567 return fetch_objects(m_q_obj_by_maglim);
575 return fetch_objects(m_q_obj_by_lim);
581 m_q_obj_by_maglim_and_type.
bindValue(
":type", type);
582 m_q_obj_by_maglim_and_type.
bindValue(
":limit", limit);
583 m_q_obj_by_maglim_and_type.
bindValue(
":maglim", maglim);
585 return fetch_objects(m_q_obj_by_maglim_and_type);
589 const int catalog_id,
float maglim,
594 query.prepare(SqlStatements::dso_in_catalog_by_maglim(catalog_id));
595 query.bindValue(
":type", type);
596 query.bindValue(
":limit", limit);
597 query.bindValue(
":maglim", maglim);
598 return fetch_objects(query);
605 return {
false,
i18n(
"Catalog could not be found.") };
607 const auto &cat = success.second;
612 query.prepare(SqlStatements::enable_disable_catalog);
613 query.bindValue(
":enabled", enabled);
614 query.bindValue(
":id",
id);
622 auto ids = get_catalog_ids(include_disabled);
623 std::vector<Catalog> catalogs;
624 catalogs.reserve(ids.size());
626 std::transform(ids.cbegin(), ids.cend(), std::back_inserter(catalogs),
628 const auto &found = get_catalog(id);
634 QString(
"Could not retrieve the catalog with id=%1").arg(id));
640 inline void bind_catalogobject(
QSqlQuery &query,
const int catalog_id,
644 const float a,
const float b,
const double pa,
645 const float flux, Trixel trixel,
648 query.prepare(SqlStatements::insert_dso(catalog_id));
650 query.bindValue(
":hash", new_id);
651 query.bindValue(
":oid", new_id);
652 query.bindValue(
":type",
static_cast<int>(t));
653 query.bindValue(
":ra", r.
Degrees());
654 query.bindValue(
":dec", d.Degrees());
655 query.bindValue(
":magnitude", (m < 99 && !std::isnan(m)) ? m :
QVariant{});
656 query.bindValue(
":name", n);
657 query.bindValue(
":long_name", lname.
length() > 0 ? lname :
QVariant{});
658 query.bindValue(
":catalog_identifier",
659 catalog_identifier.
length() > 0 ? catalog_identifier :
QVariant{});
660 query.bindValue(
":major_axis", a > 0 ? a :
QVariant{});
662 query.bindValue(
":position_angle", pa > 0 ? pa :
QVariant{});
664 query.bindValue(
":trixel", trixel);
665 query.bindValue(
":catalog", catalog_id);
668 inline void bind_catalogobject(
QSqlQuery &query,
const int catalog_id,
685 std::pair<bool, QString>
689 const float a,
const float b,
const double pa,
const float flux)
694 return {
false,
i18n(
"Catalog with id=%1 not found.", catalog_id) };
696 if (!success.second.mut)
697 return {
false,
i18n(
"Catalog is immutable!") };
706 bind_catalogobject(query, catalog_id, t, r, d, n, m, lname, catalog_identifier, a, b,
707 pa, flux, trixel, new_id);
711 auto err = query.lastError().text();
712 if (err.startsWith(
"UNIQUE"))
713 err =
i18n(
"The object is already in the catalog!");
715 return {
false,
i18n(
"Could not insert object! %1", err) };
727 query.prepare(SqlStatements::remove_dso(catalog_id));
728 query.bindValue(
":oid",
id);
731 return {
false, query.lastError().text() };
741 return {
false,
i18n(
"Catalog could not be found.") };
743 QFile file{ file_path };
745 return {
false,
i18n(
"Output file is not writable.") };
751 if (!query.exec(
QString(
"ATTACH [%1] AS tmp").arg(file_path)))
753 i18n(
"Could not attach output file.<br>%1", query.lastError().text()) };
756 auto _ = gsl::finally([&]() {
758 query.exec(
"DETACH tmp");
762 QString(
"CREATE TABLE tmp.cat AS SELECT * FROM cat_%1").arg(catalog_id)))
763 return {
false,
i18n(
"Could not copy catalog to output file.<br>%1")
764 .
arg(query.lastError().text()) };
766 if (!query.exec(SqlStatements::create_catalog_registry(
"tmp.catalogs")))
767 return {
false,
i18n(
"Could not create catalog registry in output file.<br>%1")
768 .
arg(query.lastError().text()) };
770 query.prepare(SqlStatements::insert_into_catalog_registry(
"tmp.catalogs"));
772 auto cat = found.second;
774 bind_catalog(query, cat);
779 i18n(
"Could not insert catalog into registry in output file.<br>%1")
780 .
arg(query.lastError().text()) };
783 if (!query.exec(
QString(
"PRAGMA tmp.user_version = %1").arg(m_db_version)))
785 return {
false,
i18n(
"Could not insert set exported database version.<br>%1")
786 .
arg(query.lastError().text()) };
789 if (!query.exec(
QString(
"PRAGMA tmp.application_id = %1").arg(application_id)))
792 i18n(
"Could not insert set exported database application id.<br>%1")
793 .
arg(query.lastError().text()) };
800 const bool overwrite)
803 const auto new_path = tmp.
filePath(
"cat.kscat");
806 QFile file{ new_path };
808 return {
false,
i18n(
"Catalog file is not readable.") };
813 if (!query.exec(
QString(
"ATTACH [%1] AS tmp").arg(new_path)))
817 i18n(
"Could not attach input file.<br>%1", query.lastError().text()) };
820 auto _ = gsl::finally([&]() {
822 query.exec(
"DETACH tmp");
825 if (!query.exec(
"PRAGMA tmp.application_id") || !query.next() ||
826 query.
value(0).toInt() != CatalogsDB::application_id)
827 return {
false,
i18n(
"Invalid catalog file.") };
829 if (!query.exec(
"PRAGMA tmp.user_version") || !query.next() ||
830 query.
value(0).toInt() < m_db_version)
832 const auto &success = migrate_db(query.
value(0).toInt(), m_db,
"tmp");
834 return {
false,
i18n(
"Could not migrate old catalog format.<br>%1",
838 if (!query.exec(
"SELECT id FROM tmp.catalogs LIMIT 1") || !query.next())
840 i18n(
"Could read the catalog id.<br>%1", query.lastError().text()) };
842 const auto id = query.
value(0).toInt();
849 if (!overwrite && found.second.mut)
850 return {
false,
i18n(
"Catalog already exists in the database!") };
852 auto success = remove_catalog_force(
id);
861 "INSERT INTO catalogs (id, name, mut, enabled, precedence, author, source, "
862 "description, version, color, license, maintainer, timestamp) SELECT id, "
863 "name, mut, enabled, precedence, author, source, description, version, "
864 "color, license, maintainer, timestamp FROM tmp.catalogs LIMIT 1") ||
865 !query.exec(
QString(
"CREATE TABLE cat_%1 AS SELECT * FROM tmp.cat").arg(
id)))
867 i18n(
"Could not import the catalog.<br>%1", query.lastError().text()) };
872 return {
false,
i18n(
"Could not refresh the master catalog.<br>",
880 if (
id == SqlStatements::user_catalog_id)
881 return {
false,
i18n(
"Removing the user catalog is not allowed.") };
883 return remove_catalog_force(
id);
886 std::pair<bool, QString> DBManager::remove_catalog_force(
const int id)
901 return {
false,
i18n(
"Could not remove the catalog from the registry.<br>%1")
915 return {
false,
i18n(
"Both catalogs have to exist!") };
918 return {
false,
i18n(
"Destination catalog has to be mutable!") };
922 if (!query.exec(SqlStatements::move_objects(id_1, id_2)))
923 return {
false, query.lastError().text() };
925 if (!query.exec(SqlStatements::set_catalog_all_objects(id_2)))
926 return {
false, query.lastError().text() };
934 return {
false,
i18n(
"Cannot update nonexisting catalog.") };
938 query.prepare(SqlStatements::update_catalog_meta);
939 query.bindValue(
":name", cat.
name);
940 query.bindValue(
":author", cat.
author);
941 query.bindValue(
":source", cat.
source);
943 query.bindValue(
":id", cat.
id);
944 query.bindValue(
":color", cat.
color);
945 query.bindValue(
":license", cat.
license);
946 query.bindValue(
":maintainer", cat.
maintainer);
947 query.bindValue(
":timestamp", cat.
timestamp);
949 return { query.exec(), query.lastError().text() };
957 const auto element = std::adjacent_find(
958 cats.cbegin(), cats.cend(), [](
const auto &c1,
const auto &c2) {
959 return (c1.id >= CatalogsDB::custom_cat_min_id) &&
960 (c2.id >= CatalogsDB::custom_cat_min_id) && (c2.id - c1.id) > 1;
963 return std::max(CatalogsDB::custom_cat_min_id,
964 (element == cats.cend() ? cats.back().id : element->id) + 1);
967 QString CatalogsDB::dso_db_path()
970 .
filePath(Options::dSOCatalogFilename());
973 std::pair<bool, Catalog> CatalogsDB::read_catalog_meta_from_file(
const QString &path)
976 "QSQLITE",
QString(
"tmp_%1_%2").arg(path).arg(get_connection_index())) };
980 return {
false, {} };
984 if (!
query.exec(
"PRAGMA user_version") || !
query.next() ||
985 query.
value(0).toInt() < SqlStatements::current_db_version)
988 const auto new_path = tmp.
filePath(
"cat.kscat");
995 return {
false, {} };
997 const auto &success = migrate_db(
query.
value(0).toInt(), db);
999 return {
false, {} };
1003 return {
false, {} };
1006 return {
true, read_catalog(query) };
1012 while (
query.next())
1024 if (!query.exec(SqlStatements::dso_count_by_type_master))
1025 return {
false, {} };
1027 return {
true, read_statistics(query) };
1030 const std::pair<bool, CatalogStatistics>
1034 if (!query.exec(SqlStatements::dso_count_by_type(catalog_id)))
1035 return {
false, {} };
1037 return {
true, read_statistics(query) };
1040 std::pair<bool, QString>
1042 const CatalogObjectVector &objects)
1045 const auto &success = get_catalog(catalog_id);
1047 return {
false,
i18n(
"Catalog with id=%1 not found.", catalog_id) };
1049 if (!success.second.mut)
1050 return {
false,
i18n(
"Catalog is immutable!") };
1055 for (
const auto &
object : objects)
1057 SkyPoint tmp{
object.ra(),
object.dec() };
1060 bind_catalogobject(query, catalog_id,
object, trixel);
1064 auto err = query.lastError().text();
1065 if (err.startsWith(
"UNIQUE"))
1066 err =
i18n(
"The object is already in the catalog!");
1068 return {
false,
i18n(
"Could not insert object! %1", err) };
1072 return { m_db.commit() && update_catalog_views() && compile_master_catalog(),
1073 m_db.lastError().text() };
1082 if (!query.prepare(SqlStatements::dso_by_wildcard()))
1086 query.bindValue(
":wildcard", wildcard);
1087 query.bindValue(
":limit", limit);
1089 return fetch_objects(query);
1092 std::tuple<bool, const QString, CatalogObjectList>
1100 if (!query.prepare(SqlStatements::dso_general_query(where, order_by)))
1102 return {
false, query.lastError().text(), {} };
1105 query.bindValue(
":limit", limit);
1107 return {
false,
"", fetch_objects(query) };
1110 CatalogsDB::CatalogColorMap CatalogsDB::parse_color_string(
const QString &str)
1112 CatalogsDB::CatalogColorMap colors{};
1116 const auto &parts = str.
split(
";");
1119 if (it->length() > 0)
1120 colors[
"default"] = *it;
1122 while (it != parts.constEnd())
1124 const auto &scheme = *(++it);
1125 if (it != parts.constEnd())
1127 const auto next = ++it;
1128 if (next == parts.constEnd())
1131 const auto &color = *next;
1132 colors[scheme] =
QColor(color);
1144 QString CatalogsDB::to_color_string(CatalogColorMap colors)
1148 color_list << colors[
"default"].name();
1149 colors.
erase(
"default");
1151 for (
const auto &item : colors)
1153 if (item.second.isValid())
1155 color_list << item.
first << item.second.name();
1159 return color_list.
join(
";");
1169 if (!query.exec(SqlStatements::get_colors))
1174 colors[cat.
id] = parse_color_string(cat.
color);
1177 while (query.next())
1179 const auto &catalog = query.
value(
"catalog").toInt();
1180 const auto &scheme = query.
value(
"scheme").toString();
1181 const auto &color = query.
value(
"color").toString();
1182 colors[catalog][scheme] =
QColor(color);
1190 return get_catalog_colors()[id];
1193 std::pair<bool, QString>
1200 if (!query.prepare(SqlStatements::insert_color))
1202 return {
false, query.lastError().text() };
1205 query.bindValue(
":catalog",
id);
1206 for (
const auto &item : colors)
1208 query.bindValue(
":scheme", item.first);
1209 query.bindValue(
":color", item.second);
1212 return {
false, query.lastError().text() };
1215 return {
true,
"" };