File indexing completed on 2024-05-05 11:58:23
0001 /* 0002 SPDX-FileCopyrightText: 2021 Valentin Boettcher <hiro at protagon.space; @hiro98:tchncs.de> 0003 0004 SPDX-License-Identifier: GPL-2.0-or-later 0005 */ 0006 0007 #pragma once 0008 #include <array> 0009 #include <QString> 0010 #include <QStringList> 0011 0012 namespace CatalogsDB 0013 { 0014 /** 0015 * Holds a collection of hardcoded sql statements. 0016 */ 0017 namespace SqlStatements 0018 { 0019 /* Hack to support older sqlite versions. */ 0020 #if (QT_VERSION <= QT_VERSION_CHECK(5, 12, 0)) 0021 const QString mag_asc = "magnitude IS NOT NULL, magnitude ASC"; 0022 const QString mag_desc = "magnitude IS NULL, magnitude DESC"; 0023 #else 0024 const QString mag_asc = "magnitude ASC NULLS FIRST"; 0025 const QString mag_desc = "magnitude DESC NULLS LAST"; 0026 #endif 0027 0028 /* constants */ 0029 const QString catalog_prefix = "cat_"; 0030 constexpr int current_db_version = 3; 0031 constexpr int default_htmesh_level = 3; 0032 constexpr int user_catalog_id = 0; 0033 const QString user_catalog_name = "user"; 0034 const QString master_catalog = "master"; 0035 const QString all_catalog_view = "all_catalogs"; 0036 const QString colors_table = "catalog_colors"; 0037 0038 /* metadata */ 0039 const QString create_meta_table = 0040 "CREATE TABLE IF NOT EXISTS meta (version INTEGER NOT " 0041 "NULL, htmesh_level INTEGER NOT NULL, init INTEGER NOT NULL)"; 0042 0043 const QString update_version = "UPDATE meta SET version = :version"; 0044 const QString get_meta = "SELECT version, htmesh_level, init FROM meta LIMIT 1"; 0045 const QString set_meta = "INSERT INTO meta (version, htmesh_level, init) VALUES " 0046 "(:version, :htmesh_level, :init)"; 0047 0048 /* Colors */ 0049 const QString create_colors_table = 0050 QString("CREATE TABLE IF NOT EXISTS %1 (catalog INTEGER NOT " 0051 "NULL, scheme TEXT NOT NULL, color TEXT NOT NULL, UNIQUE(catalog, scheme, " 0052 "color))") 0053 .arg(colors_table); 0054 0055 const QString get_colors = 0056 QString("SELECT catalog, scheme, color FROM %1").arg(colors_table); 0057 0058 const QString insert_color = 0059 QString("INSERT INTO %1 (catalog, scheme, color) VALUES (:catalog, :scheme, :color) " 0060 "ON CONFLICT(catalog, scheme, color) DO UPDATE SET color = :color") 0061 .arg(colors_table); 0062 0063 /* catalog queries */ 0064 template <typename input_iterator> 0065 QStringList from_it(input_iterator begin, input_iterator end) 0066 { 0067 QStringList field_strings{}; 0068 std::for_each(begin, end, [&](const auto & str) 0069 { 0070 field_strings << str; 0071 }); 0072 return field_strings; 0073 } 0074 0075 template <typename input_iterator> 0076 QString create_field_list(input_iterator begin, input_iterator end) 0077 { 0078 QStringList field_strings{ from_it(begin, end) }; 0079 0080 return field_strings.join(", "); 0081 } 0082 0083 template <typename input_iterator> 0084 QString create_field_list(input_iterator begin, input_iterator end, const QString &prefix) 0085 { 0086 QStringList field_strings{ from_it(begin, end) }; 0087 QStringList prefixed_field_strings; 0088 std::transform(field_strings.cbegin(), field_strings.cend(), 0089 std::back_inserter(prefixed_field_strings), 0090 [&](const auto & str) 0091 { 0092 return prefix + str; 0093 }); 0094 0095 return prefixed_field_strings.join(", "); 0096 } 0097 0098 constexpr std::array<const char *, 15> catalog_collumns = 0099 { 0100 "hash", "oid", "type", 0101 "ra", "dec", "magnitude", 0102 "name", "long_name", "catalog_identifier", 0103 "major_axis", "minor_axis", "position_angle", 0104 "flux", "trixel", "catalog" 0105 }; 0106 0107 const auto catalog_fields = 0108 create_field_list(catalog_collumns.begin(), catalog_collumns.end()); 0109 0110 constexpr std::array<const char *, 14> master_catalog_collumns = { "oid", 0111 "type", 0112 "ra", 0113 "dec", 0114 "magnitude", 0115 "name", 0116 "long_name", 0117 "catalog_identifier", 0118 "major_axis", 0119 "minor_axis", 0120 "position_angle", 0121 "flux", 0122 "trixel", 0123 "catalog" 0124 }; 0125 0126 const auto master_catalog_fields = 0127 create_field_list(master_catalog_collumns.begin(), master_catalog_collumns.end()); 0128 0129 /** 0130 * The standard fields to query when loading objects from the db into 0131 * kstars. 0132 */ 0133 constexpr std::array<const char *, 13> dso_query_fields = { "oid", 0134 "type", 0135 "ra", 0136 "dec", 0137 "magnitude", 0138 "name", 0139 "long_name", 0140 "catalog_identifier", 0141 "major_axis", 0142 "minor_axis", 0143 "position_angle", 0144 "flux", 0145 "catalog" 0146 }; 0147 0148 const auto object_fields = 0149 create_field_list(dso_query_fields.begin(), dso_query_fields.end()); 0150 0151 // WARN: the ordering by ID is assumed in code! 0152 const QString get_catalog_ids = 0153 "SELECT id FROM catalogs WHERE enabled = 1 ORDER BY id ASC"; 0154 const QString get_all_catalog_ids = "SELECT id FROM catalogs ORDER BY id ASC"; 0155 const QString enable_disable_catalog = 0156 "UPDATE catalogs SET enabled = :enabled WHERE id = :id"; 0157 0158 inline const QString move_objects(const int id_1, const int id_2) 0159 { 0160 return QString("INSERT INTO cat_%1 SELECT * FROM cat_%2").arg(id_2).arg(id_1); 0161 } 0162 0163 inline const QString set_catalog_all_objects(const int id) 0164 { 0165 return QString("UPDATE cat_%1 SET catalog = %1 WHERE TRUE").arg(id); 0166 } 0167 0168 /* views */ 0169 const QString _all_catalog_view_body = 0170 "SELECT %1, cl.precedence FROM %2%3 c INNER JOIN catalogs cl ON cl.id = " 0171 "c.catalog"; 0172 0173 inline QString all_catalog_view_body(const QString &fields, const QString &cat_prefix, 0174 int id) 0175 { 0176 return QString(_all_catalog_view_body).arg(fields).arg(cat_prefix).arg(id); 0177 } 0178 0179 const QString empty_view = "SELECT NULL WHERE FALSE"; 0180 0181 /* catalog management */ 0182 const QString _create_catalog_list_table = "CREATE TABLE IF NOT EXISTS %1 (" 0183 "id INTEGER PRIMARY KEY," 0184 "name TEXT NOT NULL," 0185 "precedence REAL NOT NULL," 0186 "author TEXT DEFAULT NULL," 0187 "source TEXT DEFAULT NULL," 0188 "description TEXT DEFAULT NULL," 0189 "mut INTEGER DEFAULT 0," 0190 "version INTEGER DEFAULT -1," 0191 "enabled INTEGER DEFAULT 1," 0192 "color TEXT DEFAULT NULL," 0193 "license TEXT DEFAULT NULL," 0194 "maintainer TEXT DEFAULT NULL," 0195 "timestamp DATETIME DEFAULT NULL)"; 0196 0197 inline const QString create_catalog_registry(const QString &name) 0198 { 0199 return QString(_create_catalog_list_table).arg(name); 0200 }; 0201 0202 const QString create_catalog_list_table = create_catalog_registry("catalogs"); 0203 0204 const QString _insert_catalog = 0205 "INSERT OR IGNORE INTO %1 (id, name, mut, enabled, precedence, author, source, " 0206 "description, version, color, license, maintainer, timestamp) " 0207 "VALUES (:id, :name, :mut, :enabled, :precedence, :author, :source, :description, " 0208 ":version, :color, :license, :maintainer, :timestamp)"; 0209 0210 inline const QString insert_into_catalog_registry(const QString &name) 0211 { 0212 return QString(_insert_catalog).arg(name); 0213 } 0214 0215 template <typename input_iterator> 0216 inline QString create_update_list(input_iterator begin, input_iterator end) 0217 { 0218 QStringList field_strings{ from_it(begin, end) }; 0219 QStringList prefixed_field_strings; 0220 std::transform(field_strings.cbegin(), field_strings.cend(), 0221 std::back_inserter(prefixed_field_strings), 0222 [&](const auto & str) 0223 { 0224 return QString("%1 = :%1").arg(str); 0225 }); 0226 0227 return prefixed_field_strings.join(", "); 0228 } 0229 0230 constexpr std::array<const char *, 8> _catalog_meta_fields = 0231 { 0232 "name", "author", "source", "description", 0233 "color", "license", "maintainer", "timestamp" 0234 }; 0235 0236 const QString update_catalog_meta = 0237 QString("UPDATE catalogs SET %1 WHERE id = :id") 0238 .arg(create_update_list(_catalog_meta_fields.cbegin(), 0239 _catalog_meta_fields.cend())); 0240 0241 const QString insert_catalog = insert_into_catalog_registry("catalogs"); 0242 const QString remove_catalog = "DELETE FROM catalogs WHERE id = :id"; 0243 const QString _drop_catalog = "DROP TABLE cat_%1"; 0244 inline const QString drop_catalog(int id) 0245 { 0246 return QString(_drop_catalog).arg(id); 0247 } 0248 0249 const QString _create_catalog_table = "CREATE TABLE IF NOT EXISTS %1 (" 0250 "hash BLOB PRIMARY KEY," 0251 "oid BLOB NOT NULL," 0252 "type INTEGER NOT NULL," 0253 "ra REAL NOT NULL," 0254 "dec REAL NOT NULL," 0255 "magnitude REAL DEFAULT NULL," 0256 "name TEXT NOT NULL," 0257 "long_name TEXT DEFAULT NULL," 0258 "catalog_identifier TEXT DEFAULT NULL," 0259 "major_axis REAL DEFAULT NULL," 0260 "minor_axis REAL DEFAULT NULL," 0261 "position_angle REAL DEFAULT NULL," 0262 "flux REAL DEFAULT NULL," 0263 "trixel INTEGER DEFAULT -1," 0264 "res_1 BLOB DEFAULT NULL," 0265 "res_2 BLOB DEFAULT NULL," 0266 "res_3 BLOB DEFAULT NULL," 0267 "res_4 BLOB DEFAULT NULL," 0268 "catalog INTEGER NOT NULL," 0269 "FOREIGN KEY (catalog) REFERENCES catalogs (id) " 0270 "ON DELETE CASCADE " 0271 "ON UPDATE CASCADE)"; 0272 0273 inline QString create_catalog_table(int id) 0274 { 0275 return QString(_create_catalog_table) 0276 .arg(QString(catalog_prefix) + QString::number(id)); 0277 } 0278 0279 const QString drop_master = "DROP TABLE IF EXISTS master"; 0280 0281 const QString _create_master = "CREATE TABLE master AS " 0282 "SELECT %1 FROM " 0283 "all_catalogs " 0284 "GROUP BY oid " 0285 "ORDER BY MAX(precedence)"; 0286 0287 const QString create_master = QString(_create_master).arg(master_catalog_fields); 0288 0289 const QString create_master_trixel_index = 0290 "CREATE INDEX master_trixel_mag ON master(trixel ASC, magnitude DESC, major_axis " 0291 "ASC)"; 0292 0293 const QString create_master_mag_index = 0294 "CREATE INDEX master_mag ON master(magnitude ASC)"; 0295 const QString create_master_type_index = 0296 "CREATE INDEX master_mag_type ON master(type, magnitude ASC)"; 0297 const QString create_master_name_index = 0298 "CREATE INDEX master_name ON master(name " 0299 "COLLATE NOCASE ASC, long_name COLLATE NOCASE ASC, " 0300 "magnitude ASC)"; 0301 0302 const QString get_first_catalog = "SELECT id, name, precedence, author, source, " 0303 "description, mut, enabled, version, color, license, " 0304 "maintainer, timestamp FROM catalogs LIMIT 1"; 0305 0306 const QString get_catalog_by_id = "SELECT id, name, precedence, author, source, " 0307 "description, mut, enabled, version, color, license, " 0308 "maintainer, timestamp FROM catalogs WHERE id = :id"; 0309 0310 const QString exists_catalog_by_id = "SELECT 1 FROM catalogs WHERE id = :id"; 0311 0312 const QString exists_master = 0313 "SELECT name FROM sqlite_master WHERE type='table' AND name='master';"; 0314 0315 /* DSO queries */ 0316 const QString _dso_by_catalog = QString("SELECT %1 FROM cat_%2").arg(catalog_fields); 0317 inline const QString dso_by_catalog(int catalog_id) 0318 { 0319 return _dso_by_catalog.arg(catalog_id); 0320 } 0321 0322 // Nulls last because we load the objects in reverse :P 0323 0324 const QString _dso_by_trixel = "SELECT %1 FROM master WHERE trixel = " 0325 ":trixel ORDER BY %2"; 0326 0327 const QString dso_by_trixel = QString(_dso_by_trixel).arg(object_fields).arg(mag_desc); 0328 0329 const QString _dso_by_trixel_null_mag = "SELECT %1 FROM master WHERE trixel = " 0330 ":trixel AND magnitude IS NULL"; 0331 const QString dso_by_trixel_null_mag = QString(_dso_by_trixel_null_mag).arg(object_fields); 0332 0333 const QString _dso_by_trixel_no_nulls = "SELECT %1 FROM master WHERE trixel = " 0334 ":trixel AND magnitude IS NOT NULL ORDER" 0335 " BY magnitude DESC"; 0336 const QString dso_by_trixel_no_nulls = QString(_dso_by_trixel_no_nulls).arg(object_fields); 0337 0338 const QString _dso_by_oid = "SELECT %1 FROM master WHERE oid = :id LIMIT 1"; 0339 0340 const QString dso_by_oid = QString(_dso_by_oid).arg(object_fields); 0341 0342 inline const QString dso_by_oid_and_catalog(const int id) 0343 { 0344 return QString("SELECT %1 FROM cat_%2 WHERE oid = :id LIMIT 1") 0345 .arg(object_fields) 0346 .arg(id); 0347 }; 0348 0349 const QString _dso_by_name = 0350 "SELECT %1, name like \"%\" || :name || \"%\" AS in_name, long_name like " 0351 "\"%\" || :name || \"%\" AS in_lname FROM master WHERE in_name " 0352 "OR in_lname " 0353 "ORDER BY name, long_name, " 0354 "%2 LIMIT :limit"; 0355 0356 const QString _dso_by_name_exact = "SELECT %1 FROM master WHERE name = :name LIMIT 1"; 0357 0358 const QString dso_by_name = QString(_dso_by_name).arg(object_fields).arg(mag_asc); 0359 const QString dso_by_name_exact = QString(_dso_by_name_exact).arg(object_fields); 0360 0361 inline const QString dso_by_name_and_catalog(const int id) 0362 { 0363 return QString("SELECT %1 FROM cat_%2 WHERE name like \"%\" || :name || \"%\" " 0364 "OR long_name like \"%\" || :name || \"%\" OR catalog_identifier like \"%\" || :name || \"%\"" 0365 "ORDER BY %3 LIMIT :limit") 0366 .arg(object_fields) 0367 .arg(id) 0368 .arg(mag_asc); 0369 } 0370 0371 const QString _dso_by_wildcard = "SELECT %1 FROM master WHERE name LIKE :wildcard LIMIT " 0372 ":limit ORDER BY CAST(name AS INTEGER)"; 0373 0374 inline const QString dso_by_wildcard() 0375 { 0376 return QString(_dso_by_wildcard).arg(object_fields); 0377 } 0378 0379 inline const QString dso_general_query(const QString &where, const QString &order_by = "") 0380 { 0381 auto query = QString("SELECT %1 FROM master WHERE %2").arg(object_fields).arg(where); 0382 0383 if (order_by.size() > 0) 0384 query += " ORDER BY " + order_by; 0385 0386 query += " LIMIT :limit"; 0387 0388 return query; 0389 } 0390 0391 const QString _dso_by_maglim = "SELECT %1 FROM master WHERE magnitude < :maglim " 0392 "ORDER BY %2 LIMIT :limit"; 0393 0394 const QString dso_by_maglim = QString(_dso_by_maglim).arg(object_fields).arg(mag_asc); 0395 0396 const QString _dso_by_lim = "SELECT %1 FROM master " 0397 "ORDER BY %2 LIMIT :limit"; 0398 0399 const QString dso_by_lim = QString(_dso_by_lim).arg(object_fields).arg(mag_asc); 0400 0401 inline const QString dso_in_catalog_by_maglim(const int id) 0402 { 0403 return QString("SELECT %1 FROM cat_%2 WHERE magnitude < :maglim " 0404 "AND type = :type ORDER BY %3 LIMIT :limit") 0405 .arg(object_fields) 0406 .arg(id) 0407 .arg(mag_asc); 0408 } 0409 0410 const QString _dso_by_maglim_and_type = 0411 "SELECT %1 FROM master WHERE type = :type AND magnitude < :maglim " 0412 "ORDER BY %2 LIMIT :limit"; 0413 0414 const QString dso_by_maglim_and_type = 0415 QString(_dso_by_maglim_and_type).arg(object_fields).arg(mag_asc); 0416 0417 const QString _dso_count_by_type = "SELECT type, COUNT(*) FROM %1 GROUP BY type"; 0418 const QString dso_count_by_type_master = _dso_count_by_type.arg("master"); 0419 0420 inline const QString dso_count_by_type(int catalog_id) 0421 { 0422 return _dso_count_by_type.arg("cat_" + QString::number(catalog_id)); 0423 } 0424 0425 const QString _insert_dso_template = "INSERT OR REPLACE INTO cat_%3 (%1) VALUES (%2)"; 0426 const QString _insert_dso = 0427 QString(_insert_dso_template) 0428 .arg(catalog_fields) 0429 .arg(create_field_list(catalog_collumns.begin(), catalog_collumns.end(), ":")); 0430 0431 inline const QString insert_dso(int catalog_id) 0432 { 0433 return _insert_dso.arg(catalog_id); 0434 } 0435 0436 const QString _remove_dso{ "DELETE FROM cat_%1 WHERE oid = :oid" }; 0437 inline const QString remove_dso(const int id) 0438 { 0439 return _remove_dso.arg(id); 0440 } 0441 0442 } // namespace SqlStatements 0443 } // namespace CatalogsDB