17namespace SqlStatements
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";
24const QString mag_asc =
"magnitude ASC NULLS FIRST";
25const QString mag_desc =
"magnitude DESC NULLS LAST";
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";
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)";
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)";
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, "
56 QString(
"SELECT catalog, scheme, color FROM %1").
arg(colors_table);
59 QString(
"INSERT INTO %1 (catalog, scheme, color) VALUES (:catalog, :scheme, :color) "
60 "ON CONFLICT(catalog, scheme, color) DO UPDATE SET color = :color")
64template <
typename input_iterator>
65QStringList from_it(input_iterator begin, input_iterator end)
68 std::for_each(begin, end, [&](
const auto & str)
75template <
typename input_iterator>
76QString create_field_list(input_iterator begin, input_iterator end)
80 return field_strings.
join(
", ");
83template <
typename input_iterator>
84QString create_field_list(input_iterator begin, input_iterator end,
const QString &prefix)
88 std::transform(field_strings.cbegin(), field_strings.cend(),
89 std::back_inserter(prefixed_field_strings),
95 return prefixed_field_strings.
join(
", ");
98constexpr std::array<const char *, 15> catalog_collumns =
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"
107const auto catalog_fields =
108 create_field_list(catalog_collumns.begin(), catalog_collumns.end());
110constexpr std::array<const char *, 14> master_catalog_collumns = {
"oid",
117 "catalog_identifier",
126const auto master_catalog_fields =
127 create_field_list(master_catalog_collumns.begin(), master_catalog_collumns.end());
140 "catalog_identifier",
148const auto object_fields =
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";
158inline const QString move_objects(
const int id_1,
const int id_2)
160 return QString(
"INSERT INTO cat_%1 SELECT * FROM cat_%2").
arg(id_2).
arg(id_1);
163inline const QString set_catalog_all_objects(
const int id)
165 return QString(
"UPDATE cat_%1 SET catalog = %1 WHERE TRUE").
arg(
id);
169const QString _all_catalog_view_body =
170 "SELECT %1, cl.precedence FROM %2%3 c INNER JOIN catalogs cl ON cl.id = "
179const QString empty_view =
"SELECT NULL WHERE FALSE";
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)";
199 return QString(_create_catalog_list_table).
arg(name);
202const QString create_catalog_list_table = create_catalog_registry(
"catalogs");
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)";
210inline const QString insert_into_catalog_registry(
const QString &name)
215template <
typename input_iterator>
216inline QString create_update_list(input_iterator begin, input_iterator end)
220 std::transform(field_strings.cbegin(), field_strings.cend(),
221 std::back_inserter(prefixed_field_strings),
222 [&](
const auto & str)
224 return QString(
"%1 = :%1").arg(str);
227 return prefixed_field_strings.
join(
", ");
230constexpr std::array<const char *, 8> _catalog_meta_fields =
232 "name",
"author",
"source",
"description",
233 "color",
"license",
"maintainer",
"timestamp"
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()));
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)
249const QString _create_catalog_table =
"CREATE TABLE IF NOT EXISTS %1 ("
250 "hash BLOB PRIMARY KEY,"
252 "type INTEGER 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) "
271 "ON UPDATE CASCADE)";
273inline QString create_catalog_table(
int id)
275 return QString(_create_catalog_table)
279const QString drop_master =
"DROP TABLE IF EXISTS master";
281const QString _create_master =
"CREATE TABLE master AS "
285 "ORDER BY MAX(precedence)";
289const QString create_master_trixel_index =
290 "CREATE INDEX master_trixel_mag ON master(trixel ASC, magnitude DESC, major_axis "
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, "
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";
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";
310const QString exists_catalog_by_id =
"SELECT 1 FROM catalogs WHERE id = :id";
313 "SELECT name FROM sqlite_master WHERE type='table' AND name='master';";
316const QString _dso_by_catalog =
QString(
"SELECT %1 FROM cat_%2").
arg(catalog_fields);
317inline const QString dso_by_catalog(
int catalog_id)
319 return _dso_by_catalog.
arg(catalog_id);
324const QString _dso_by_trixel =
"SELECT %1 FROM master WHERE trixel = "
325 ":trixel ORDER BY %2";
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);
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);
338const QString _dso_by_oid =
"SELECT %1 FROM master WHERE oid = :id LIMIT 1";
342inline const QString dso_by_oid_and_catalog(
const int id)
344 return QString(
"SELECT %1 FROM cat_%2 WHERE oid = :id LIMIT 1")
350 "SELECT %1, name like \"%\" || :name || \"%\" AS in_name, long_name like "
351 "\"%\" || :name || \"%\" AS in_lname FROM master WHERE in_name "
353 "ORDER BY name, long_name, "
356const QString _dso_by_name_exact =
"SELECT %1 FROM master WHERE name = :name LIMIT 1";
361inline const QString dso_by_name_and_catalog(
const int id)
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")
371const QString _dso_by_wildcard =
"SELECT %1 FROM master WHERE name LIKE :wildcard LIMIT "
372 ":limit ORDER BY CAST(name AS INTEGER)";
374inline const QString dso_by_wildcard()
376 return QString(_dso_by_wildcard).
arg(object_fields);
383 if (order_by.size() > 0)
384 query +=
" ORDER BY " + order_by;
386 query +=
" LIMIT :limit";
391const QString _dso_by_maglim =
"SELECT %1 FROM master WHERE magnitude < :maglim "
392 "ORDER BY %2 LIMIT :limit";
396const QString _dso_by_lim =
"SELECT %1 FROM master "
397 "ORDER BY %2 LIMIT :limit";
401inline const QString dso_in_catalog_by_maglim(
const int id)
403 return QString(
"SELECT %1 FROM cat_%2 WHERE magnitude < :maglim "
404 "AND type = :type ORDER BY %3 LIMIT :limit")
410const QString _dso_by_maglim_and_type =
411 "SELECT %1 FROM master WHERE type = :type AND magnitude < :maglim "
412 "ORDER BY %2 LIMIT :limit";
414const QString dso_by_maglim_and_type =
415 QString(_dso_by_maglim_and_type).
arg(object_fields).
arg(mag_asc);
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");
420inline const QString dso_count_by_type(
int catalog_id)
425const QString _insert_dso_template =
"INSERT OR REPLACE INTO cat_%3 (%1) VALUES (%2)";
429 .
arg(create_field_list(catalog_collumns.begin(), catalog_collumns.end(),
":"));
431inline const QString insert_dso(
int catalog_id)
433 return _insert_dso.
arg(catalog_id);
436const QString _remove_dso{
"DELETE FROM cat_%1 WHERE oid = :oid" };
437inline const QString remove_dso(
const int id)
439 return _remove_dso.
arg(
id);
std::optional< QSqlQuery > query(const QString &queryStatement)
constexpr std::array< const char *, 13 > dso_query_fields
The standard fields to query when loading objects from the db into kstars.
QString arg(Args &&... args) const const
QString number(double n, char format, int precision)
QString join(QChar separator) const const