NAPISD
PAHdb website C++ backend
Loading...
Searching...
No Matches
PAHdb.cpp
1#include "PAHdb.h"
2
3void PAHdb::connect(std::string_view database = "", std::string_view host = "",
4 std::string_view username = "",
5 std::string_view password = "", int port = 0,
6 bool compress = false, int timeout = 0,
7 std::string_view socket = "") {
8
9 try {
10
11 _connection.connect(database.data(), socket.data(), username.data(),
12 password.data(), port);
13 } catch (const mysqlpp::ConnectionFailed &e) {
14
15 throw(Exception(e.what()));
16 }
17}
18
19std::vector<std::vector<std::pair<double, double>>>
20PAHdb::getTransitionsFromUIDsAndVersion(const std::vector<int> &uids,
21 int version) {
22
23 mysqlpp::Query query = _connection.query();
24
25 mysqlpp::StoreQueryResult result;
26
27 mysqlpp::Row row;
28
29 std::vector<std::vector<std::pair<double, double>>> vector;
30
31 std::pair<double, double> transition;
32
33 try {
34
35 for (const auto &uid : uids) {
36
37 query.reset();
38
39 query << "SELECT frequency, intensity FROM "
40 << pre[static_cast<int>(_table)]
41 << "transitions WHERE specie_id=(SELECT id FROM "
42 << pre[static_cast<int>(_table)]
43 << "species AS s1 WHERE status & 2 = 0 AND update_number=(SELECT "
44 "MAX(update_number) FROM "
45 << pre[static_cast<int>(_table)]
46 << "species AS s2 WHERE update_number<=" << version
47 << " AND s1.uid=s2.uid) AND uid=" << uid << ")";
48
49 result = query.store();
50
51 std::vector<std::pair<double, double>> transitions;
52
53 try {
54
55 for (const auto &row : result) {
56
57 transition.first = row["frequency"];
58
59 transition.second = row["intensity"];
60
61 transitions.push_back(transition);
62 }
63 } catch (mysqlpp::BadFieldName &e) {
64
65 throw(Exception(e.what()));
66 }
67
68 vector.push_back(transitions);
69 }
70 } catch (const mysqlpp::BadQuery &e) {
71
72 throw(Exception(e.what()));
73 }
74
75 return (vector);
76}
77
78std::vector<std::vector<std::pair<double, double>>>
79PAHdb::getTransitionsFromVersion(int version, std::vector<int> &uids) {
80
81 uids.clear();
82
83 mysqlpp::Query query = _connection.query();
84
85 mysqlpp::StoreQueryResult result;
86
87 mysqlpp::Row row;
88
89 uids.clear();
90
91 try {
92
93 query.reset();
94
95 query << "SELECT uid FROM " << pre[static_cast<int>(_table)]
96 << "species AS s1 WHERE status & 2 = 0 AND update_number=(SELECT "
97 "MAX(update_number) FROM "
98 << pre[static_cast<int>(_table)]
99 << "species AS s2 WHERE update_number <=" << version
100 << " AND s1.uid=s2.uid) ORDER BY uid";
101
102 result = query.store();
103
104 try {
105
106 for (const auto &row : result) {
107
108 uids.push_back(row["uid"]);
109 }
110 } catch (mysqlpp::BadFieldName &e) {
111
112 throw(Exception(e.what()));
113 }
114 } catch (const mysqlpp::BadQuery &e) {
115
116 throw(Exception(e.what()));
117 }
118
119 return (PAHdb::getTransitionsFromUIDsAndVersion(uids, version));
120}
121
122std::vector<std::vector<std::pair<double, double>>>
123PAHdb::getTransitionsFromIds(const std::vector<int> &ids) {
124
125 mysqlpp::Query query = _connection.query();
126
127 mysqlpp::StoreQueryResult result;
128
129 mysqlpp::Row row;
130
131 std::vector<std::vector<std::pair<double, double>>> vector;
132
133 std::pair<double, double> transition;
134
135 try {
136
137 for (const auto &id : ids) {
138
139 query.reset();
140
141 query << "SELECT frequency, intensity FROM "
142 << pre[static_cast<int>(_table)]
143 << "transitions WHERE specie_id=" << id;
144
145 result = query.store();
146
147 std::vector<std::pair<double, double>> transitions;
148
149 try {
150
151 for (const auto &row : result) {
152
153 transition.first = row["frequency"];
154
155 transition.second = row["intensity"];
156
157 transitions.push_back(transition);
158 }
159 } catch (mysqlpp::BadFieldName &e) {
160
161 throw(Exception(e.what()));
162 }
163
164 vector.push_back(transitions);
165 }
166 } catch (const mysqlpp::BadQuery &e) {
167
168 throw(Exception(e.what()));
169 }
170
171 return (vector);
172}
173
174std::vector<std::vector<std::pair<double, double>>>
175PAHdb::getExperimentalAndTheoreticalTransitionsFromId(int id) {
176
177 mysqlpp::Query query = _connection.query();
178
179 mysqlpp::StoreQueryResult result;
180
181 mysqlpp::Row row;
182
183 std::vector<std::vector<std::pair<double, double>>> vector;
184
185 std::vector<std::pair<double, double>> transitions;
186
187 std::pair<double, double> transition;
188
189 try {
190
191 query.reset();
192
193 switch (_table) {
194
195 case PAHdb::Database::Experiment:
196
197 query << "SELECT frequency, intensity FROM "
198 << "exp_transitions WHERE specie_id=" << id;
199
200 break;
201
202 case PAHdb::Database::Anharmonic:
203 case PAHdb::Database::Theory:
204 default:
205
206 query
207 << "SELECT frequency, intensity FROM "
208 << "exp_transitions JOIN exp_species ON "
209 << "exp_transitions.specie_id=exp_species.id "
210 << " WHERE uid=(SELECT uid FROM " << pre[static_cast<int>(_table)]
211 << "species WHERE id=" << id
212 << ") AND update_number=(SELECT MAX(update_number) FROM exp_species "
213 << "WHERE status & 2 = 0 AND uid=(SELECT uid FROM "
214 << pre[static_cast<int>(_table)] << "species WHERE id=" << id << "))";
215 };
216
217 result = query.store();
218
219 try {
220
221 for (const auto &row : result) {
222
223 transition.first = row["frequency"];
224
225 transition.second = row["intensity"];
226
227 transitions.push_back(transition);
228 }
229 } catch (mysqlpp::BadFieldName &e) {
230
231 throw(Exception(e.what()));
232 }
233
234 vector.push_back(transitions);
235
236 query.reset();
237
238 switch (_table) {
239 case PAHdb::Database::Theory:
240
241 query << "SELECT frequency, intensity FROM "
242 << "transitions WHERE specie_id=" << id;
243
244 break;
245
246 case PAHdb::Database::Anharmonic:
247 case PAHdb::Database::Experiment:
248 default:
249
250 query << "SELECT frequency, intensity FROM "
251 << "transitions JOIN species ON "
252 << "transitions.specie_id=species.id "
253 << " WHERE uid=(SELECT uid FROM " << pre[static_cast<int>(_table)]
254 << "species WHERE id=" << id
255 << ") AND update_number=(SELECT MAX(update_number) FROM species "
256 << "WHERE status & 2 = 0 AND uid=(SELECT uid FROM "
257 << pre[static_cast<int>(_table)] << "species WHERE id=" << id
258 << "))";
259 };
260
261 result = query.store();
262
263 transitions.clear();
264
265 try {
266
267 for (const auto &row : result) {
268
269 transition.first = row["frequency"];
270
271 transition.second = row["intensity"];
272
273 transitions.push_back(transition);
274 }
275
276 } catch (mysqlpp::BadFieldName &e) {
277
278 throw(Exception(e.what()));
279 }
280
281 vector.push_back(transitions);
282
283 query.reset();
284
285 switch (_table) {
286
287 case PAHdb::Database::Anharmonic:
288
289 query << "SELECT frequency, intensity FROM "
290 << "anharmonic_transitions WHERE specie_id=" << id;
291
292 break;
293
294 case PAHdb::Database::Theory:
295 case PAHdb::Database::Experiment:
296 default:
297
298 query << "SELECT frequency, intensity FROM "
299 << "anharmonic_transitions JOIN anharmonic_species ON "
300 << "anharmonic_transitions.specie_id=anharmonic_species.id "
301 << " WHERE uid=(SELECT uid FROM " << pre[static_cast<int>(_table)]
302 << "species WHERE id=" << id
303 << ") AND update_number=(SELECT MAX(update_number) FROM "
304 "anharmonic_species "
305 << "WHERE status & 2 = 0 AND uid=(SELECT uid FROM "
306 << pre[static_cast<int>(_table)] << "species WHERE id=" << id
307 << "))";
308 };
309
310 result = query.store();
311
312 transitions.clear();
313
314 try {
315
316 for (const auto &row : result) {
317
318 transition.first = row["frequency"];
319
320 transition.second = row["intensity"];
321
322 transitions.push_back(transition);
323 }
324 } catch (mysqlpp::BadFieldName &e) {
325
326 throw(Exception(e.what()));
327 }
328
329 vector.push_back(transitions);
330
331 } catch (const mysqlpp::BadQuery &e) {
332
333 throw(Exception(e.what()));
334 }
335
336 return (vector);
337}
338
339std::vector<PAHGeometry>
340PAHdb::getGeometriesFromIds(const std::vector<int> &ids) {
341
342 mysqlpp::Query query = _connection.query();
343
344 mysqlpp::StoreQueryResult result;
345
346 mysqlpp::Row row;
347
348 std::vector<PAHGeometry> vector;
349
350 try {
351
352 for (const auto &id : ids) {
353
354 query.reset();
355
356 switch (_table) {
357
358 case PAHdb::Database::Experiment:
359
360 query << "SELECT x, y, z, type FROM "
361 << pre[static_cast<int>(PAHdb::Database::Theory)]
362 << "geometries JOIN species ON "
363 << "geometries.specie_id=species.id JOIN exp_species ON "
364 << "species.id=exp_species.theoretical_id WHERE exp_species.id="
365 << id;
366
367 break;
368
369 case PAHdb::Database::Anharmonic:
370 case PAHdb::Database::Theory:
371 default:
372 query << "SELECT * FROM " << pre[static_cast<int>(_table)]
373 << "geometries WHERE specie_id=" << id << " ORDER BY position";
374
375 break;
376 };
377
378 result = query.store();
379
380 std::vector<Atom> atoms;
381
382 try {
383
384 for (const auto &row : result) {
385
386 atoms.push_back(Atom(row["x"], row["y"], row["z"], row["type"]));
387 }
388 } catch (mysqlpp::BadFieldName &e) {
389
390 throw(Exception(e.what()));
391 }
392
393 vector.push_back(PAHGeometry(atoms));
394 }
395 } catch (const mysqlpp::BadQuery &e) {
396
397 throw(Exception(e.what()));
398 }
399
400 return (vector);
401}
402
403std::vector<std::string>
404PAHdb::getFormulaeFromIds(const std::vector<int> &ids) {
405
406 mysqlpp::Query query = _connection.query();
407
408 mysqlpp::StoreQueryResult result;
409
410 mysqlpp::Row row;
411
412 std::vector<std::string> vector;
413
414 try {
415
416 for (const auto &id : ids) {
417
418 query.reset();
419
420 switch (_table) {
421
422 case PAHdb::Database::Experiment:
423
424 query << "SELECT formula, species.update_number FROM species JOIN "
425 << "exp_species ON species.uid=exp_species.uid WHERE "
426 << "exp_species.id=" << id
427 << " ORDER BY species.update_number DESC LIMIT 1";
428
429 break;
430
431 case PAHdb::Database::Anharmonic:
432 case PAHdb::Database::Theory:
433 default:
434
435 query << "SELECT formula FROM " << pre[static_cast<int>(_table)]
436 << "species WHERE id=" << id;
437 break;
438 };
439
440 result = query.store();
441
442 row = *result.begin();
443
444 try {
445
446 vector.push_back(static_cast<std::string>(row["formula"]));
447
448 } catch (mysqlpp::BadFieldName &e) {
449
450 throw(Exception(e.what()));
451 }
452 }
453 } catch (const mysqlpp::BadQuery &e) {
454
455 throw(Exception(e.what()));
456 }
457
458 return (vector);
459}
460
461std::vector<sql_properties>
462PAHdb::getPropertiesByUIDsAndVersion(const std::vector<int> &uids,
463 int version) {
464
465 std::vector<sql_properties> properties;
466
467 mysqlpp::Query query = _connection.query();
468
469 try {
470
471 query.reset();
472
473 query << "SELECT uid, n_h, n_c, n_n, n_o, n_mg, n_si, n_fe, charge, "
474 "n_solo, n_duo, n_trio, n_quartet, n_quintet, n_ch2, n_chx FROM "
475 << pre[static_cast<int>(_table)]
476 << "species AS s1 WHERE status & 2 = 0 AND update_number=(SELECT "
477 "MAX(update_number) FROM "
478 << pre[static_cast<int>(_table)]
479 << "species AS s2 WHERE update_number<=" << version
480 << " AND s1.uid=s2.uid) AND uid IN (" << uids.front();
481
482 std::for_each(std::next(uids.cbegin(), 1), uids.cend(),
483 [&](auto &uid) { query << "," << uid; });
484
485 query << ") ORDER BY FIELD(uid";
486
487 for (const auto &uid : uids) {
488
489 query << "," << uid;
490 }
491
492 query << ")";
493
494 query.storein(properties);
495
496 } catch (const mysqlpp::BadQuery &e) {
497
498 throw(Exception(e.what()));
499 }
500
501 return (properties);
502}
503
504std::vector<sql_properties>
505PAHdb::getPropertiesByIDs(const std::vector<int> &ids) {
506
507 std::vector<sql_properties> properties;
508
509 mysqlpp::Query query = _connection.query();
510
511 try {
512
513 query.reset();
514
515 switch (_table) {
516
517 case PAHdb::Database::Experiment:
518
519 query << "SELECT t1.uid, t2.n_h, t2.n_c, t2.n_n, t2.n_o, t2.n_mg, "
520 << "t2.n_si, t2.n_fe, t2.charge, t2.n_solo, t2.n_duo, t2.n_trio, "
521 << "t2.n_quartet, t2.n_quintet, t2.n_ch2, t2.n_chx FROM (SELECT * "
522 << "FROM " << pre[static_cast<int>(_table)]
523 << "species AS s WHERE status & 2 = 0 AND id IN (" << ids.front();
524
525 break;
526
527 case PAHdb::Database::Theory:
528 case PAHdb::Database::Anharmonic:
529 default:
530
531 query << "SELECT uid, n_h, n_c, n_n, n_o, n_mg, n_si, n_fe, charge, "
532 << "n_solo, n_duo, n_trio, n_quartet, n_quintet, n_ch2, n_chx FROM "
533 << pre[static_cast<int>(_table)] << "species WHERE id IN ("
534 << ids.front();
535 }
536
537 std::for_each(std::next(ids.cbegin(), 1), ids.cend(),
538 [&](auto &id) { query << "," << id; });
539
540 query << ')';
541
542 if (_table == PAHdb::Database::Experiment) {
543
544 query << ") AS t1, (SELECT * FROM species AS s1 WHERE status & 2 = 0 "
545 "AND update_number = (SELECT MAX(update_number) FROM species AS "
546 "s3 WHERE status & 2 = 0 AND s1.uid = s3.uid)) AS t2 WHERE "
547 "t1.uid = t2.uid";
548 }
549
550 query << " ORDER BY FIELD("
551 << (_table == PAHdb::Database::Experiment ? "t1." : "") << "id";
552
553 for (const auto &id : ids) {
554
555 query << "," << id;
556 }
557
558 query << ")";
559
560 query.storein(properties);
561
562 } catch (const mysqlpp::BadQuery &e) {
563
564 throw(Exception(e.what()));
565 }
566
567 return (properties);
568}
569
570void PAHdb::setProgress(double progress) {
571
572 if (progress > 100 || progress < 0) {
573
574 throw(Exception("progress can only be between 0 - 100"));
575 }
576
577 mysqlpp::Query query = _connection.query();
578
579 mysqlpp::StoreQueryResult result;
580
581 try {
582
583 query << "UPDATE tasks SET ret_val = " << 100.0 - progress
584 << " WHERE pid = " << getpid();
585
586 result = query.store();
587 } catch (const mysqlpp::BadQuery &e) {
588
589 throw(Exception(e.what()));
590 }
591}
592
593void PAHdb::setError(const char *error) {
594
595 mysqlpp::Query query = _connection.query();
596
597 mysqlpp::StoreQueryResult result;
598
599 try {
600
601 query << "UPDATE tasks SET error = '" << error
602 << "' WHERE pid = " << getpid();
603
604 result = query.store();
605 } catch (const mysqlpp::BadQuery &e) {
606
607 throw(Exception(e.what()));
608 }
609}
Atom
Definition Atom.h:7
Since FY2019 the NASA Ames PAH IR Spectroscopic Database is being supported through a directed Work Package at NASA Ames titled: "Laboratory Astrophysics - The NASA Ames PAH IR Spectroscopic Database".
Since FY2023 the NASA Ames PAH IR Spectroscopic Database is being supported through the Laboratory Astrophysics Rd 2 directed Work Package at NASA Ames.
© Copyright 2021-2025, Christiaan Boersma