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 
12 namespace CatalogsDB
13 {
14 /**
15  * Holds a collection of hardcoded sql statements.
16  */
17 namespace SqlStatements
18 {
19 /* Hack to support older sqlite versions. */
20 #if (QT_VERSION <= QT_VERSION_CHECK(5, 12, 0))
21 const QString mag_asc = "magnitude IS NOT NULL, magnitude ASC";
22 const QString mag_desc = "magnitude IS NULL, magnitude DESC";
23 #else
24 const QString mag_asc = "magnitude ASC NULLS FIRST";
25 const QString mag_desc = "magnitude DESC NULLS LAST";
26 #endif
27 
28 /* constants */
29 const QString catalog_prefix = "cat_";
30 constexpr int current_db_version = 3;
31 constexpr int default_htmesh_level = 3;
32 constexpr int user_catalog_id = 0;
33 const QString user_catalog_name = "user";
34 const QString master_catalog = "master";
35 const QString all_catalog_view = "all_catalogs";
36 const QString colors_table = "catalog_colors";
37 
38 /* metadata */
39 const 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 
43 const QString update_version = "UPDATE meta SET version = :version";
44 const QString get_meta = "SELECT version, htmesh_level, init FROM meta LIMIT 1";
45 const QString set_meta = "INSERT INTO meta (version, htmesh_level, init) VALUES "
46  "(:version, :htmesh_level, :init)";
47 
48 /* Colors */
49 const 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 
55 const QString get_colors =
56  QString("SELECT catalog, scheme, color FROM %1").arg(colors_table);
57 
58 const 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 */
64 template <typename input_iterator>
65 QStringList from_it(input_iterator begin, input_iterator end)
66 {
67  QStringList field_strings{};
68  std::for_each(begin, end, [&](const auto & str)
69  {
70  field_strings << str;
71  });
72  return field_strings;
73 }
74 
75 template <typename input_iterator>
76 QString 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 
83 template <typename input_iterator>
84 QString create_field_list(input_iterator begin, input_iterator end, const QString &prefix)
85 {
86  QStringList field_strings{ from_it(begin, end) };
87  QStringList prefixed_field_strings;
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 
98 constexpr 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 
107 const auto catalog_fields =
108  create_field_list(catalog_collumns.begin(), catalog_collumns.end());
109 
110 constexpr 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 
126 const 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  */
133 constexpr 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 
148 const 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!
152 const QString get_catalog_ids =
153  "SELECT id FROM catalogs WHERE enabled = 1 ORDER BY id ASC";
154 const QString get_all_catalog_ids = "SELECT id FROM catalogs ORDER BY id ASC";
155 const QString enable_disable_catalog =
156  "UPDATE catalogs SET enabled = :enabled WHERE id = :id";
157 
158 inline 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 
163 inline 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 */
169 const 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 
173 inline 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 
179 const QString empty_view = "SELECT NULL WHERE FALSE";
180 
181 /* catalog management */
182 const 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 
197 inline const QString create_catalog_registry(const QString &name)
198 {
199  return QString(_create_catalog_list_table).arg(name);
200 };
201 
202 const QString create_catalog_list_table = create_catalog_registry("catalogs");
203 
204 const 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 
210 inline const QString insert_into_catalog_registry(const QString &name)
211 {
212  return QString(_insert_catalog).arg(name);
213 }
214 
215 template <typename input_iterator>
216 inline QString create_update_list(input_iterator begin, input_iterator end)
217 {
218  QStringList field_strings{ from_it(begin, end) };
219  QStringList prefixed_field_strings;
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 
230 constexpr std::array<const char *, 8> _catalog_meta_fields =
231 {
232  "name", "author", "source", "description",
233  "color", "license", "maintainer", "timestamp"
234 };
235 
236 const 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 
241 const QString insert_catalog = insert_into_catalog_registry("catalogs");
242 const QString remove_catalog = "DELETE FROM catalogs WHERE id = :id";
243 const QString _drop_catalog = "DROP TABLE cat_%1";
244 inline const QString drop_catalog(int id)
245 {
246  return QString(_drop_catalog).arg(id);
247 }
248 
249 const 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 
273 inline QString create_catalog_table(int id)
274 {
275  return QString(_create_catalog_table)
276  .arg(QString(catalog_prefix) + QString::number(id));
277 }
278 
279 const QString drop_master = "DROP TABLE IF EXISTS master";
280 
281 const QString _create_master = "CREATE TABLE master AS "
282  "SELECT %1 FROM "
283  "all_catalogs "
284  "GROUP BY oid "
285  "ORDER BY MAX(precedence)";
286 
287 const QString create_master = QString(_create_master).arg(master_catalog_fields);
288 
289 const QString create_master_trixel_index =
290  "CREATE INDEX master_trixel_mag ON master(trixel ASC, magnitude DESC, major_axis "
291  "ASC)";
292 
293 const QString create_master_mag_index =
294  "CREATE INDEX master_mag ON master(magnitude ASC)";
295 const QString create_master_type_index =
296  "CREATE INDEX master_mag_type ON master(type, magnitude ASC)";
297 const 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 
302 const 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 
306 const 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 
310 const QString exists_catalog_by_id = "SELECT 1 FROM catalogs WHERE id = :id";
311 
312 const QString exists_master =
313  "SELECT name FROM sqlite_master WHERE type='table' AND name='master';";
314 
315 /* DSO queries */
316 const QString _dso_by_catalog = QString("SELECT %1 FROM cat_%2").arg(catalog_fields);
317 inline 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 
324 const QString _dso_by_trixel = "SELECT %1 FROM master WHERE trixel = "
325  ":trixel ORDER BY %2";
326 
327 const QString dso_by_trixel = QString(_dso_by_trixel).arg(object_fields).arg(mag_desc);
328 
329 const QString _dso_by_trixel_null_mag = "SELECT %1 FROM master WHERE trixel = "
330  ":trixel AND magnitude IS NULL";
331 const QString dso_by_trixel_null_mag = QString(_dso_by_trixel_null_mag).arg(object_fields);
332 
333 const QString _dso_by_trixel_no_nulls = "SELECT %1 FROM master WHERE trixel = "
334  ":trixel AND magnitude IS NOT NULL ORDER"
335  " BY magnitude DESC";
336 const QString dso_by_trixel_no_nulls = QString(_dso_by_trixel_no_nulls).arg(object_fields);
337 
338 const QString _dso_by_oid = "SELECT %1 FROM master WHERE oid = :id LIMIT 1";
339 
340 const QString dso_by_oid = QString(_dso_by_oid).arg(object_fields);
341 
342 inline 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 
349 const 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 
356 const QString _dso_by_name_exact = "SELECT %1 FROM master WHERE name = :name LIMIT 1";
357 
358 const QString dso_by_name = QString(_dso_by_name).arg(object_fields).arg(mag_asc);
359 const QString dso_by_name_exact = QString(_dso_by_name_exact).arg(object_fields);
360 
361 inline 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 
371 const QString _dso_by_wildcard = "SELECT %1 FROM master WHERE name LIKE :wildcard LIMIT "
372  ":limit ORDER BY CAST(name AS INTEGER)";
373 
374 inline const QString dso_by_wildcard()
375 {
376  return QString(_dso_by_wildcard).arg(object_fields);
377 }
378 
379 inline 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 
391 const QString _dso_by_maglim = "SELECT %1 FROM master WHERE magnitude < :maglim "
392  "ORDER BY %2 LIMIT :limit";
393 
394 const QString dso_by_maglim = QString(_dso_by_maglim).arg(object_fields).arg(mag_asc);
395 
396 inline const QString dso_in_catalog_by_maglim(const int id)
397 {
398  return QString("SELECT %1 FROM cat_%2 WHERE magnitude < :maglim "
399  "AND type = :type ORDER BY %3 LIMIT :limit")
400  .arg(object_fields)
401  .arg(id)
402  .arg(mag_asc);
403 }
404 
405 const QString _dso_by_maglim_and_type =
406  "SELECT %1 FROM master WHERE type = :type AND magnitude < :maglim "
407  "ORDER BY %2 LIMIT :limit";
408 
409 const QString dso_by_maglim_and_type =
410  QString(_dso_by_maglim_and_type).arg(object_fields).arg(mag_asc);
411 
412 const QString _dso_count_by_type = "SELECT type, COUNT(*) FROM %1 GROUP BY type";
413 const QString dso_count_by_type_master = _dso_count_by_type.arg("master");
414 
415 inline const QString dso_count_by_type(int catalog_id)
416 {
417  return _dso_count_by_type.arg("cat_" + QString::number(catalog_id));
418 }
419 
420 const QString _insert_dso_template = "INSERT OR REPLACE INTO cat_%3 (%1) VALUES (%2)";
421 const QString _insert_dso =
422  QString(_insert_dso_template)
423  .arg(catalog_fields)
424  .arg(create_field_list(catalog_collumns.begin(), catalog_collumns.end(), ":"));
425 
426 inline const QString insert_dso(int catalog_id)
427 {
428  return _insert_dso.arg(catalog_id);
429 }
430 
431 const QString _remove_dso{ "DELETE FROM cat_%1 WHERE oid = :oid" };
432 inline const QString remove_dso(const int id)
433 {
434  return _remove_dso.arg(id);
435 }
436 
437 } // namespace SqlStatements
438 } // namespace CatalogsDB
std::optional< QSqlQuery > query(const QString &queryStatement)
QString number(int n, int base)
constexpr std::array< const char *, 13 > dso_query_fields
The standard fields to query when loading objects from the db into kstars.
QString join(const QString &separator) const const
QString arg(qlonglong a, int fieldWidth, int base, QChar fillChar) const const
This file is part of the KDE documentation.
Documentation copyright © 1996-2022 The KDE developers.
Generated on Fri Aug 12 2022 04:00:58 by doxygen 1.8.17 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.