Kstars

catalogsdb/sqlstatements.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#pragma once
8#include <array>
9#include <QString>
10#include <QStringList>
11
12namespace CatalogsDB
13{
14/**
15 * Holds a collection of hardcoded sql statements.
16 */
17namespace SqlStatements
18{
19/* Hack to support older sqlite versions. */
20#if (QT_VERSION <= QT_VERSION_CHECK(5, 12, 0))
21const QString mag_asc = "magnitude IS NOT NULL, magnitude ASC";
22const QString mag_desc = "magnitude IS NULL, magnitude DESC";
23#else
24const QString mag_asc = "magnitude ASC NULLS FIRST";
25const QString mag_desc = "magnitude DESC NULLS LAST";
26#endif
27
28/* constants */
29const QString catalog_prefix = "cat_";
30constexpr int current_db_version = 3;
31constexpr int default_htmesh_level = 3;
32constexpr int user_catalog_id = 0;
33const QString user_catalog_name = "user";
34const QString master_catalog = "master";
35const QString all_catalog_view = "all_catalogs";
36const QString colors_table = "catalog_colors";
37
38/* metadata */
39const QString create_meta_table =
40 "CREATE TABLE IF NOT EXISTS meta (version INTEGER NOT "
41 "NULL, htmesh_level INTEGER NOT NULL, init INTEGER NOT NULL)";
42
43const QString update_version = "UPDATE meta SET version = :version";
44const QString get_meta = "SELECT version, htmesh_level, init FROM meta LIMIT 1";
45const QString set_meta = "INSERT INTO meta (version, htmesh_level, init) VALUES "
46 "(:version, :htmesh_level, :init)";
47
48/* Colors */
49const QString create_colors_table =
50 QString("CREATE TABLE IF NOT EXISTS %1 (catalog INTEGER NOT "
51 "NULL, scheme TEXT NOT NULL, color TEXT NOT NULL, UNIQUE(catalog, scheme, "
52 "color))")
53 .arg(colors_table);
54
55const QString get_colors =
56 QString("SELECT catalog, scheme, color FROM %1").arg(colors_table);
57
58const QString insert_color =
59 QString("INSERT INTO %1 (catalog, scheme, color) VALUES (:catalog, :scheme, :color) "
60 "ON CONFLICT(catalog, scheme, color) DO UPDATE SET color = :color")
61 .arg(colors_table);
62
63/* catalog queries */
64template <typename input_iterator>
66{
68 std::for_each(begin, end, [&](const auto & str)
69 {
70 field_strings << str;
71 });
72 return field_strings;
73}
74
75template <typename input_iterator>
76QString create_field_list(input_iterator begin, input_iterator end)
77{
78 QStringList field_strings{ from_it(begin, end) };
79
80 return field_strings.join(", ");
81}
82
83template <typename input_iterator>
84QString create_field_list(input_iterator begin, input_iterator end, const QString &prefix)
85{
86 QStringList field_strings{ from_it(begin, end) };
88 std::transform(field_strings.cbegin(), field_strings.cend(),
89 std::back_inserter(prefixed_field_strings),
90 [&](const auto & str)
91 {
92 return prefix + str;
93 });
94
95 return prefixed_field_strings.join(", ");
96}
97
98constexpr std::array<const char *, 15> catalog_collumns =
99{
100 "hash", "oid", "type",
101 "ra", "dec", "magnitude",
102 "name", "long_name", "catalog_identifier",
103 "major_axis", "minor_axis", "position_angle",
104 "flux", "trixel", "catalog"
105};
106
107const auto catalog_fields =
108 create_field_list(catalog_collumns.begin(), catalog_collumns.end());
109
110constexpr std::array<const char *, 14> master_catalog_collumns = { "oid",
111 "type",
112 "ra",
113 "dec",
114 "magnitude",
115 "name",
116 "long_name",
117 "catalog_identifier",
118 "major_axis",
119 "minor_axis",
120 "position_angle",
121 "flux",
122 "trixel",
123 "catalog"
124};
125
126const auto master_catalog_fields =
127 create_field_list(master_catalog_collumns.begin(), master_catalog_collumns.end());
128
129/**
130 * The standard fields to query when loading objects from the db into
131 * kstars.
132 */
133constexpr std::array<const char *, 13> dso_query_fields = { "oid",
134 "type",
135 "ra",
136 "dec",
137 "magnitude",
138 "name",
139 "long_name",
140 "catalog_identifier",
141 "major_axis",
142 "minor_axis",
143 "position_angle",
144 "flux",
145 "catalog"
146};
147
148const auto object_fields =
149 create_field_list(dso_query_fields.begin(), dso_query_fields.end());
150
151// WARN: the ordering by ID is assumed in code!
152const QString get_catalog_ids =
153 "SELECT id FROM catalogs WHERE enabled = 1 ORDER BY id ASC";
154const QString get_all_catalog_ids = "SELECT id FROM catalogs ORDER BY id ASC";
155const QString enable_disable_catalog =
156 "UPDATE catalogs SET enabled = :enabled WHERE id = :id";
157
158inline const QString move_objects(const int id_1, const int id_2)
159{
160 return QString("INSERT INTO cat_%1 SELECT * FROM cat_%2").arg(id_2).arg(id_1);
161}
162
163inline const QString set_catalog_all_objects(const int id)
164{
165 return QString("UPDATE cat_%1 SET catalog = %1 WHERE TRUE").arg(id);
166}
167
168/* views */
169const QString _all_catalog_view_body =
170 "SELECT %1, cl.precedence FROM %2%3 c INNER JOIN catalogs cl ON cl.id = "
171 "c.catalog";
172
173inline QString all_catalog_view_body(const QString &fields, const QString &cat_prefix,
174 int id)
175{
176 return QString(_all_catalog_view_body).arg(fields).arg(cat_prefix).arg(id);
177}
178
179const QString empty_view = "SELECT NULL WHERE FALSE";
180
181/* catalog management */
182const QString _create_catalog_list_table = "CREATE TABLE IF NOT EXISTS %1 ("
183 "id INTEGER PRIMARY KEY,"
184 "name TEXT NOT NULL,"
185 "precedence REAL NOT NULL,"
186 "author TEXT DEFAULT NULL,"
187 "source TEXT DEFAULT NULL,"
188 "description TEXT DEFAULT NULL,"
189 "mut INTEGER DEFAULT 0,"
190 "version INTEGER DEFAULT -1,"
191 "enabled INTEGER DEFAULT 1,"
192 "color TEXT DEFAULT NULL,"
193 "license TEXT DEFAULT NULL,"
194 "maintainer TEXT DEFAULT NULL,"
195 "timestamp DATETIME DEFAULT NULL)";
196
197inline const QString create_catalog_registry(const QString &name)
198{
199 return QString(_create_catalog_list_table).arg(name);
200};
201
202const QString create_catalog_list_table = create_catalog_registry("catalogs");
203
204const QString _insert_catalog =
205 "INSERT OR IGNORE INTO %1 (id, name, mut, enabled, precedence, author, source, "
206 "description, version, color, license, maintainer, timestamp) "
207 "VALUES (:id, :name, :mut, :enabled, :precedence, :author, :source, :description, "
208 ":version, :color, :license, :maintainer, :timestamp)";
209
210inline const QString insert_into_catalog_registry(const QString &name)
211{
212 return QString(_insert_catalog).arg(name);
213}
214
215template <typename input_iterator>
216inline QString create_update_list(input_iterator begin, input_iterator end)
217{
218 QStringList field_strings{ from_it(begin, end) };
220 std::transform(field_strings.cbegin(), field_strings.cend(),
221 std::back_inserter(prefixed_field_strings),
222 [&](const auto & str)
223 {
224 return QString("%1 = :%1").arg(str);
225 });
226
227 return prefixed_field_strings.join(", ");
228}
229
230constexpr std::array<const char *, 8> _catalog_meta_fields =
231{
232 "name", "author", "source", "description",
233 "color", "license", "maintainer", "timestamp"
234};
235
236const QString update_catalog_meta =
237 QString("UPDATE catalogs SET %1 WHERE id = :id")
238 .arg(create_update_list(_catalog_meta_fields.cbegin(),
239 _catalog_meta_fields.cend()));
240
241const QString insert_catalog = insert_into_catalog_registry("catalogs");
242const QString remove_catalog = "DELETE FROM catalogs WHERE id = :id";
243const QString _drop_catalog = "DROP TABLE cat_%1";
244inline const QString drop_catalog(int id)
245{
246 return QString(_drop_catalog).arg(id);
247}
248
249const QString _create_catalog_table = "CREATE TABLE IF NOT EXISTS %1 ("
250 "hash BLOB PRIMARY KEY,"
251 "oid BLOB NOT NULL,"
252 "type INTEGER NOT NULL,"
253 "ra REAL NOT NULL,"
254 "dec REAL NOT NULL,"
255 "magnitude REAL DEFAULT NULL,"
256 "name TEXT NOT NULL,"
257 "long_name TEXT DEFAULT NULL,"
258 "catalog_identifier TEXT DEFAULT NULL,"
259 "major_axis REAL DEFAULT NULL,"
260 "minor_axis REAL DEFAULT NULL,"
261 "position_angle REAL DEFAULT NULL,"
262 "flux REAL DEFAULT NULL,"
263 "trixel INTEGER DEFAULT -1,"
264 "res_1 BLOB DEFAULT NULL,"
265 "res_2 BLOB DEFAULT NULL,"
266 "res_3 BLOB DEFAULT NULL,"
267 "res_4 BLOB DEFAULT NULL,"
268 "catalog INTEGER NOT NULL,"
269 "FOREIGN KEY (catalog) REFERENCES catalogs (id) "
270 "ON DELETE CASCADE "
271 "ON UPDATE CASCADE)";
272
273inline QString create_catalog_table(int id)
274{
275 return QString(_create_catalog_table)
276 .arg(QString(catalog_prefix) + QString::number(id));
277}
278
279const QString drop_master = "DROP TABLE IF EXISTS master";
280
281const QString _create_master = "CREATE TABLE master AS "
282 "SELECT %1 FROM "
283 "all_catalogs "
284 "GROUP BY oid "
285 "ORDER BY MAX(precedence)";
286
287const QString create_master = QString(_create_master).arg(master_catalog_fields);
288
289const QString create_master_trixel_index =
290 "CREATE INDEX master_trixel_mag ON master(trixel ASC, magnitude DESC, major_axis "
291 "ASC)";
292
293const QString create_master_mag_index =
294 "CREATE INDEX master_mag ON master(magnitude ASC)";
295const QString create_master_type_index =
296 "CREATE INDEX master_mag_type ON master(type, magnitude ASC)";
297const QString create_master_name_index =
298 "CREATE INDEX master_name ON master(name "
299 "COLLATE NOCASE ASC, long_name COLLATE NOCASE ASC, "
300 "magnitude ASC)";
301
302const QString get_first_catalog = "SELECT id, name, precedence, author, source, "
303 "description, mut, enabled, version, color, license, "
304 "maintainer, timestamp FROM catalogs LIMIT 1";
305
306const QString get_catalog_by_id = "SELECT id, name, precedence, author, source, "
307 "description, mut, enabled, version, color, license, "
308 "maintainer, timestamp FROM catalogs WHERE id = :id";
309
310const QString exists_catalog_by_id = "SELECT 1 FROM catalogs WHERE id = :id";
311
312const QString exists_master =
313 "SELECT name FROM sqlite_master WHERE type='table' AND name='master';";
314
315/* DSO queries */
316const QString _dso_by_catalog = QString("SELECT %1 FROM cat_%2").arg(catalog_fields);
317inline const QString dso_by_catalog(int catalog_id)
318{
319 return _dso_by_catalog.arg(catalog_id);
320}
321
322// Nulls last because we load the objects in reverse :P
323
324const QString _dso_by_trixel = "SELECT %1 FROM master WHERE trixel = "
325 ":trixel ORDER BY %2";
326
327const QString dso_by_trixel = QString(_dso_by_trixel).arg(object_fields).arg(mag_desc);
328
329const QString _dso_by_trixel_null_mag = "SELECT %1 FROM master WHERE trixel = "
330 ":trixel AND magnitude IS NULL";
331const QString dso_by_trixel_null_mag = QString(_dso_by_trixel_null_mag).arg(object_fields);
332
333const QString _dso_by_trixel_no_nulls = "SELECT %1 FROM master WHERE trixel = "
334 ":trixel AND magnitude IS NOT NULL ORDER"
335 " BY magnitude DESC";
336const QString dso_by_trixel_no_nulls = QString(_dso_by_trixel_no_nulls).arg(object_fields);
337
338const QString _dso_by_oid = "SELECT %1 FROM master WHERE oid = :id LIMIT 1";
339
340const QString dso_by_oid = QString(_dso_by_oid).arg(object_fields);
341
342inline const QString dso_by_oid_and_catalog(const int id)
343{
344 return QString("SELECT %1 FROM cat_%2 WHERE oid = :id LIMIT 1")
345 .arg(object_fields)
346 .arg(id);
347};
348
349const QString _dso_by_name =
350 "SELECT %1, name like \"%\" || :name || \"%\" AS in_name, long_name like "
351 "\"%\" || :name || \"%\" AS in_lname FROM master WHERE in_name "
352 "OR in_lname "
353 "ORDER BY name, long_name, "
354 "%2 LIMIT :limit";
355
356const QString _dso_by_name_exact = "SELECT %1 FROM master WHERE name = :name LIMIT 1";
357
358const QString dso_by_name = QString(_dso_by_name).arg(object_fields).arg(mag_asc);
359const QString dso_by_name_exact = QString(_dso_by_name_exact).arg(object_fields);
360
361inline const QString dso_by_name_and_catalog(const int id)
362{
363 return QString("SELECT %1 FROM cat_%2 WHERE name like \"%\" || :name || \"%\" "
364 "OR long_name like \"%\" || :name || \"%\" OR catalog_identifier like \"%\" || :name || \"%\""
365 "ORDER BY %3 LIMIT :limit")
366 .arg(object_fields)
367 .arg(id)
368 .arg(mag_asc);
369}
370
371const QString _dso_by_wildcard = "SELECT %1 FROM master WHERE name LIKE :wildcard LIMIT "
372 ":limit ORDER BY CAST(name AS INTEGER)";
373
374inline const QString dso_by_wildcard()
375{
376 return QString(_dso_by_wildcard).arg(object_fields);
377}
378
379inline const QString dso_general_query(const QString &where, const QString &order_by = "")
380{
381 auto query = QString("SELECT %1 FROM master WHERE %2").arg(object_fields).arg(where);
382
383 if (order_by.size() > 0)
384 query += " ORDER BY " + order_by;
385
386 query += " LIMIT :limit";
387
388 return query;
389}
390
391const QString _dso_by_maglim = "SELECT %1 FROM master WHERE magnitude < :maglim "
392 "ORDER BY %2 LIMIT :limit";
393
394const QString dso_by_maglim = QString(_dso_by_maglim).arg(object_fields).arg(mag_asc);
395
396const QString _dso_by_lim = "SELECT %1 FROM master "
397 "ORDER BY %2 LIMIT :limit";
398
399const QString dso_by_lim = QString(_dso_by_lim).arg(object_fields).arg(mag_asc);
400
401inline const QString dso_in_catalog_by_maglim(const int id)
402{
403 return QString("SELECT %1 FROM cat_%2 WHERE magnitude < :maglim "
404 "AND type = :type ORDER BY %3 LIMIT :limit")
405 .arg(object_fields)
406 .arg(id)
407 .arg(mag_asc);
408}
409
410const QString _dso_by_maglim_and_type =
411 "SELECT %1 FROM master WHERE type = :type AND magnitude < :maglim "
412 "ORDER BY %2 LIMIT :limit";
413
414const QString dso_by_maglim_and_type =
415 QString(_dso_by_maglim_and_type).arg(object_fields).arg(mag_asc);
416
417const QString _dso_count_by_type = "SELECT type, COUNT(*) FROM %1 GROUP BY type";
418const QString dso_count_by_type_master = _dso_count_by_type.arg("master");
419
420inline const QString dso_count_by_type(int catalog_id)
421{
422 return _dso_count_by_type.arg("cat_" + QString::number(catalog_id));
423}
424
425const QString _insert_dso_template = "INSERT OR REPLACE INTO cat_%3 (%1) VALUES (%2)";
426const QString _insert_dso =
427 QString(_insert_dso_template)
428 .arg(catalog_fields)
429 .arg(create_field_list(catalog_collumns.begin(), catalog_collumns.end(), ":"));
430
431inline const QString insert_dso(int catalog_id)
432{
433 return _insert_dso.arg(catalog_id);
434}
435
436const QString _remove_dso{ "DELETE FROM cat_%1 WHERE oid = :oid" };
437inline const QString remove_dso(const int id)
438{
439 return _remove_dso.arg(id);
440}
441
442} // namespace SqlStatements
443} // namespace CatalogsDB
constexpr std::array< const char *, 13 > dso_query_fields
The standard fields to query when loading objects from the db into kstars.
KSERVICE_EXPORT KService::List query(FilterFunc filterFunc)
QString arg(Args &&... args) const const
QString number(double n, char format, int precision)
This file is part of the KDE documentation.
Documentation copyright © 1996-2024 The KDE developers.
Generated on Tue Mar 26 2024 11:19:03 by doxygen 1.10.0 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.