· 5 years ago · Oct 29, 2020, 08:38 AM
1#include "dal_sqlite.h"
2#include "third_party/sqlite/sqlite3.h"
3#include <boost/format.hpp>
4#include <cassert>
5#include <iostream>
6#include <memory>
7#include <boost/thread/future.hpp>
8
9using boost::unique_future;
10using boost::thread;
11using boost::asio::io_context;
12using boost::promise;
13using std::make_unique;
14using std::make_shared;
15using boost::optional;
16using std::move;
17using boost::format;
18using boost::get;
19using boost::any_cast;
20
21using std::vector;
22using std::string;
23
24
25using std::cout;
26using std::endl;
27
28// #define DEBUG_DAL
29
30
31void log_error(const char* msg) {
32 assert(msg);
33 ::printf("%s\n", msg);
34}
35
36void log_error(const std::string& msg) {
37 log_error(msg.c_str());
38}
39
40#define LOG_ERROR log_error
41
42
43#define STRINGIZE_HELPER( __something ) #__something
44#define STRINGIZE( __something ) STRINGIZE_HELPER( __something )
45
46#define VERIFY( __expression ) \
47if ( !(__expression) ) \
48{ \
49 throw std::runtime_error( \
50 "Failed verify (" __FILE__ "@" STRINGIZE(__LINE__) "):" \
51 STRINGIZE( __expression ) \
52 ); \
53}
54
55#define VERIFY_TRACE( __expression, __message ) \
56if ( !(__expression) ) \
57{ \
58 LOG_ERROR( __message ); \
59 throw std::runtime_error( \
60 "Failed verify (" __FILE__ "@" STRINGIZE(__LINE__) "): " \
61 STRINGIZE( __expression ) \
62 ); \
63}
64
65
66
67
68static std::string sql_escape(const char* str) {
69 VERIFY(str);
70 auto escaped = sqlite3_mprintf("%q", str);
71 VERIFY(escaped);
72 string result(escaped);
73 sqlite3_free(escaped);
74 return result;
75}
76
77static std::string sql_escape(const std::string& str) {
78 return sql_escape(str.c_str());
79}
80
81template <typename T>
82const T& sql_escape(const T& val) {
83 return val;
84}
85
86struct DataSet {
87 std::vector<std::vector<std::string>> rows;
88 std::vector<std::string> columns;
89};
90
91
92class SQLiteWrapper {
93public:
94 explicit SQLiteWrapper(const std::string& db_path) {
95 const int err = sqlite3_open(db_path.c_str(), &m_db);
96 VERIFY(err == SQLITE_OK);
97 VERIFY(m_db != nullptr);
98
99 prepare_db();
100 // cout << "SQLiteWrapper is constructed" << endl;
101 }
102
103 ~SQLiteWrapper() {
104 const int err = sqlite3_close(m_db);
105 if (err != SQLITE_OK) {
106 //TODO: add logs
107 }
108 }
109
110 boost::optional<DataSet> exec(const std::string& query) {
111 VERIFY(m_db);
112
113 boost::optional<DataSet> result;
114
115#ifdef DEBUG_DAL
116 cout << "Execute query: [" << query << "]" << endl;
117#endif
118 char* err_msg = nullptr;
119
120 DataSet dataset;
121
122 const int err = sqlite3_exec(m_db, query.c_str(), row_callback,
123 &dataset, &err_msg);
124
125 if (err == SQLITE_OK) {
126 if (!dataset.rows.empty()) {
127 result = move(dataset);
128 }
129 } else {
130#ifdef DEBUG_DAL
131 cout << "sqlite exec error: " << err_msg << endl;
132#endif
133 sqlite3_free(err_msg);
134 }
135
136 return result;
137 }
138
139 int num_affected_rows() const {
140 VERIFY(m_db);
141
142 return sqlite3_changes(m_db);
143 }
144private:
145 static int row_callback(void* out_dataset, int argc, char** argv,
146 char** columnName)
147 {
148 VERIFY(out_dataset);
149
150 // cout << "row callback is called. argc=" << argc << endl;
151
152 DataSet* dataset = reinterpret_cast<DataSet*>(out_dataset);
153
154 if (dataset->columns.empty()) {
155 for (int i = 0; i < argc; ++i) {
156 dataset->columns.emplace_back(columnName[i]);
157 }
158 }
159
160 vector<string> row;
161 for (int i = 0; i < argc; ++i) {
162 row.emplace_back(argv[i] ? argv[i] : string());
163 }
164
165 dataset->rows.emplace_back(move(row));
166
167 return 0;
168 }
169
170 void prepare_db() {
171 // Regards to https://sqlite.org/foreignkeys.html
172 exec("PRAGMA foreign_keys = ON;");
173
174 exec(
175 "CREATE TABLE IF NOT EXISTS profile ( "
176 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
177 "nickname VARCHAR(32),"
178 "occupation VARCHAR(128),"
179 "email VARCHAR(128),"
180 "age INTEGER,"
181 "sex INTEGER,"
182 "userpic VARCHAR(128),"
183 "about text"
184 ")"
185 );
186
187 exec(
188 "CREATE TABLE IF NOT EXISTS user ("
189 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
190 "name VARCHAR(32),"
191 "password VARCHAR(128),"
192 "presence BIGINT,"
193 "profile_id INTEGER,"
194 "FOREIGN KEY(profile_id) REFERENCES profile(id)"
195 ")"
196 );
197
198 exec(
199 "CREATE TABLE IF NOT EXISTS message ("
200 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
201 "parent INTEGER,"
202 "issue_time BIGINT,"
203 "user INTEGER,"
204 "channel_id INTEGER,"
205 "content TEXT"
206 ")"
207 );
208
209 exec(
210 "CREATE TABLE IF NOT EXISTS channel ("
211 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
212 "type INTEGER,"
213 "creator INTEGER,"
214 "title VARCHAR(128),"
215 "creation_time BIGINT"
216 ")"
217 );
218
219 exec(
220 "CREATE TABLE IF NOT EXISTS subscription ("
221 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
222 "user_id INTEGER,"
223 "channel_id INTEGER,"
224 "presence BIGINT"
225 ")"
226 );
227
228 }
229
230 bool m_transaction = false;
231 sqlite3* m_db = nullptr;
232};
233
234
235struct ExceptionGuard {
236 ExceptionGuard(
237 std::function<void()>& global_exception_handler,
238 std::function<void()> scope_exception_handler
239 )
240 : m_global_exception_handler(global_exception_handler)
241 , m_saved_exception_handler(global_exception_handler)
242 {
243 m_global_exception_handler = scope_exception_handler;
244 }
245 ~ExceptionGuard() {
246 if (!std::uncaught_exception()) {
247 m_global_exception_handler = m_saved_exception_handler;
248 }
249 }
250private:
251 std::function<void()>& m_global_exception_handler;
252 std::function<void()> m_saved_exception_handler;
253};
254
255
256struct TransactionGuard {
257 explicit TransactionGuard(SQLiteWrapper& db)
258 : m_db(db)
259 {
260 m_db.exec("BEGIN TRANSACTION;");
261 m_transaction_is_active = true;
262 }
263
264 ~TransactionGuard() {
265 if (m_transaction_is_active) {
266 m_db.exec("ROLLBACK;");
267 }
268 }
269
270 void commit() {
271 VERIFY(m_transaction_is_active);
272 m_db.exec("COMMIT;");
273 m_transaction_is_active = false;
274 }
275
276private:
277 SQLiteWrapper& m_db;
278 bool m_transaction_is_active = false;
279};
280
281
282
283DALSQLite::DALSQLite(const std::string& db_path)
284 : m_work(make_unique<io_context::work>(m_ioc))
285 , m_work_thread(make_unique<thread>([this]{
286 while (m_work) {
287 try { m_ioc.run(); }
288 catch(const std::exception& e) {
289#ifdef DEBUG_DAL
290 cout << "DALSQLite exception: " << e.what() << endl;
291#endif
292 if (m_exception_handler) {
293 m_exception_handler();
294 m_exception_handler = std::function<void()>();
295 }
296 }
297 }
298 }))
299 , m_db(make_unique<SQLiteWrapper>(db_path))
300{}
301
302DALSQLite::~DALSQLite()
303{
304 m_ioc.post([this] {
305 m_work.reset();
306 m_ioc.stop();
307 });
308
309 m_work_thread->join();
310}
311
312
313unique_future<int> DALSQLite::create_user(const DBUserInfo& info) {
314 auto result(make_shared<promise<int>>());
315 auto fut = result->get_future();
316
317 m_ioc.post([this, info=info, result]() {
318 ExceptionGuard exception_guard(m_exception_handler, [result] {
319 result->set_exception(std::current_exception());
320 });
321
322 TransactionGuard transaction(*m_db);
323
324 const auto insert_profile_query = str(
325 format("INSERT INTO profile "
326 " (nickname, occupation, email, age, sex, userpic, about) "
327 "VALUES ('%1%', '%2%', '%3%', %4%, %5%, '%6%', '%7%'); "
328 "SELECT last_insert_rowid();")
329 % sql_escape(info.profile.nickname)
330 % sql_escape(info.profile.occupation)
331 % sql_escape(info.profile.email)
332 % sql_escape(info.profile.age)
333 % sql_escape(info.profile.sex)
334 % sql_escape(info.profile.userpic)
335 % sql_escape(info.profile.about)
336 );
337
338 const auto profile_dataset = m_db->exec(insert_profile_query);
339
340 VERIFY(profile_dataset);
341
342 const int profile_id = stoi(profile_dataset->rows.at(0).at(0));
343
344 const auto insert_user_query = str(
345 format("INSERT INTO user (name, password, profile_id, presence) "
346 "VALUES ('%1%', '%2%', %3%, %4%); "
347 "SELECT last_insert_rowid();")
348 % sql_escape(info.name)
349 % sql_escape(info.password)
350 % profile_id
351 % info.presence
352 );
353
354 const auto user_dataset = m_db->exec(insert_user_query);
355
356 VERIFY(user_dataset);
357
358 const int user_id = stoi(user_dataset->rows.at(0).at(0));
359 transaction.commit();
360 result->set_value(user_id);
361 });
362
363 return fut;
364}
365
366
367unique_future<bool> DALSQLite::has_user(int user_id) {
368 auto result(make_shared<promise<bool>>());
369 auto fut = result->get_future();
370
371 m_ioc.post([this, user_id, result]() {
372 ExceptionGuard exception_guard(m_exception_handler, [result] {
373 result->set_exception(std::current_exception());
374 });
375
376 const auto user_count_query = str(
377 format("SELECT COUNT(*) "
378 "FROM user "
379 "WHERE id='%1%';") % user_id);
380
381 const auto user_count_data = m_db->exec(user_count_query);
382
383 VERIFY(user_count_data);
384 VERIFY(user_count_data->rows.size() == 1);
385
386 const bool has_user = stoi(user_count_data->rows.at(0).at(0)) == 1;
387 result->set_value(has_user);
388 });
389
390 return fut;
391}
392
393
394unique_future<DBUserInfo> DALSQLite::read_user(int user_id) {
395 auto result(make_shared<promise<DBUserInfo>>());
396 auto fut = result->get_future();
397
398 m_ioc.post([this, result, user_id]() {
399 ExceptionGuard exception_guard(m_exception_handler, [result] {
400 result->set_exception(std::current_exception());
401 });
402
403 const auto user_query = str(
404 format("SELECT name, password, profile_id, presence "
405 "FROM user "
406 "WHERE id='%1%';") % sql_escape(user_id));
407
408 const auto user_data = m_db->exec(user_query);
409
410 VERIFY(user_data);
411
412 VERIFY(user_data->rows.size() == 1);
413
414 const int profile_id = stoi(user_data->rows.at(0).at(2));
415 const auto profile_query = str(
416 format("SELECT nickname, occupation, email, age, sex, userpic, about "
417 "FROM profile "
418 "WHERE id='%1%';") % sql_escape(profile_id));
419
420 const auto profile_data = m_db->exec(profile_query);
421
422 VERIFY(profile_data);
423
424 VERIFY(profile_data->rows.size() == 1);
425
426 DBUserInfo user_info;
427
428 user_info.dbid = user_id;
429
430 const auto& user_fields = user_data->rows.at(0);
431 user_info.name = user_fields.at(0);
432 user_info.password = user_fields.at(1);
433 user_info.presence = stol(user_fields.at(3));
434
435
436 const auto& profile_fields = profile_data->rows.at(0);
437 user_info.profile.nickname = profile_fields.at(0);
438 user_info.profile.occupation = profile_fields.at(1);
439 user_info.profile.email = profile_fields.at(2);
440 user_info.profile.age = stoi(profile_fields.at(3));
441 user_info.profile.sex = stoi(profile_fields.at(4));
442 user_info.profile.userpic = profile_fields.at(5);
443 user_info.profile.about = profile_fields.at(6);
444
445 result->set_value(move(user_info));
446 });
447
448 return fut;
449}
450
451unique_future<bool> DALSQLite::update_user(DBUserInfo info) {
452 auto result(make_shared<promise<bool>>());
453 auto fut = result->get_future();
454
455 m_ioc.post([this, result, info=move(info)]() {
456 ExceptionGuard exception_guard(m_exception_handler, [result] {
457 result->set_exception(std::current_exception());
458 });
459
460 TransactionGuard transaction(*m_db);
461
462 const auto profile_id_query = str(
463 format("SELECT profile_id "
464 "FROM user "
465 "WHERE id='%1%';") % info.dbid);
466
467 const auto profileid_data = m_db->exec(profile_id_query);
468
469 VERIFY(profileid_data);
470
471 VERIFY(profileid_data->rows.size() == 1);
472
473 const int profile_id = stoi(profileid_data->rows.at(0).at(0));
474
475 const auto update_profile_query = str(
476 format("UPDATE profile "
477 "SET nickname='%1%', occupation='%2%', email='%3%', age='%4%',"
478 " sex='%5%', userpic='%6%', about='%7%' "
479 "WHERE id='%8%'")
480 % sql_escape(info.profile.nickname)
481 % sql_escape(info.profile.occupation)
482 % sql_escape(info.profile.email)
483 % sql_escape(info.profile.age)
484 % sql_escape(info.profile.sex)
485 % sql_escape(info.profile.userpic)
486 % sql_escape(info.profile.about)
487 % sql_escape(profile_id)
488 );
489
490 const auto update_profile_data = m_db->exec(update_profile_query);
491
492 VERIFY(m_db->num_affected_rows() == 1);
493
494 const auto update_user_query = str(
495 format("UPDATE user "
496 "SET name='%1%', password='%2%', presence='%3%' "
497 "WHERE id='%4%';")
498 % sql_escape(info.name)
499 % sql_escape(info.password)
500 % sql_escape(info.presence)
501 % sql_escape(info.dbid)
502 );
503
504 const auto update_user_data = m_db->exec(update_user_query);
505
506 VERIFY(m_db->num_affected_rows() == 1);
507
508 transaction.commit();
509 result->set_value(true);
510 });
511
512 return fut;
513}
514
515
516unique_future<bool> DALSQLite::update_user_field(
517 int user_id, eDBUserInfoFields field,
518 boost::any value
519) {
520 auto result(make_shared<promise<bool>>());
521 auto fut = result->get_future();
522
523 m_ioc.post([this, result, user_id, field, value]() {
524 ExceptionGuard exception_guard(m_exception_handler, [result] {
525 result->set_exception(std::current_exception());
526 });
527
528 TransactionGuard transaction(*m_db);
529
530 const auto profile_field_flags =
531 eDBUserInfoFields_ProfileNickname |
532 eDBUserInfoFields_ProfileOccupation |
533 eDBUserInfoFields_ProfileEmail |
534 eDBUserInfoFields_ProfileAge |
535 eDBUserInfoFields_ProfileSex |
536 eDBUserInfoFields_ProfileUserpic |
537 eDBUserInfoFields_ProfileAbout
538 ;
539
540 const auto user_field_flags =
541 eDBUserInfoFields_Name |
542 eDBUserInfoFields_Password |
543 eDBUserInfoFields_Presence
544 ;
545
546 if (field & profile_field_flags) {
547 const auto profile_id_query = str(
548 format("SELECT profile_id "
549 "FROM user "
550 "WHERE id='%1%';") % user_id);
551
552 const auto profileid_data = m_db->exec(profile_id_query);
553
554 VERIFY(profileid_data);
555
556 VERIFY(profileid_data->rows.size() == 1);
557
558 const int profile_id = stoi(profileid_data->rows.at(0).at(0));
559
560 auto query =
561 format("UPDATE profile SET %2%='%3%' WHERE id='%1%';") % profile_id;
562
563 const auto update_profile_field_query = str(
564 field == eDBUserInfoFields_ProfileNickname ?
565 (query % "nickname" % sql_escape(any_cast<string>(value)))
566 : field == eDBUserInfoFields_ProfileOccupation ?
567 (query % "occupation" % sql_escape(any_cast<string>(value)))
568 : field == eDBUserInfoFields_ProfileEmail ?
569 (query % "email" % sql_escape(any_cast<string>(value)))
570 : field == eDBUserInfoFields_ProfileAge ?
571 (query % "age" % sql_escape(any_cast<int>(value)))
572 : field == eDBUserInfoFields_ProfileSex ?
573 (query % "sex" % sql_escape(any_cast<int>(value)))
574 : field == eDBUserInfoFields_ProfileUserpic ?
575 (query % "userpic" % sql_escape(any_cast<string>(value)))
576 : field == eDBUserInfoFields_ProfileAbout ?
577 (query % "about" % sql_escape(any_cast<string>(value)))
578 : format("")
579 );
580
581 VERIFY(!update_profile_field_query.empty());
582
583 m_db->exec(update_profile_field_query);
584
585 VERIFY(m_db->num_affected_rows() == 1);
586 } else {
587 VERIFY(field & user_field_flags);
588
589 auto query =
590 format("UPDATE user SET %2%='%3%' WHERE id='%1%';") % user_id;
591
592 const auto update_user_field_query = str(
593 field == eDBUserInfoFields_Name ?
594 (query % "name" % sql_escape(any_cast<string>(value)))
595 : field == eDBUserInfoFields_Password ?
596 (query % "password" % sql_escape(any_cast<string>(value)))
597 : field == eDBUserInfoFields_Presence ?
598 (query % "presence" % sql_escape(any_cast<time_t>(value)))
599 : format("")
600 );
601
602 VERIFY(!update_user_field_query.empty());
603
604 m_db->exec(update_user_field_query);
605
606 VERIFY(m_db->num_affected_rows() == 1);
607 }
608
609 transaction.commit();
610 result->set_value(true);
611 });
612
613 return fut;
614}
615
616
617unique_future<boost::any>
618DALSQLite::read_user_field(int user_id, eDBUserInfoFields field) {
619 auto result(make_shared<promise<boost::any>>());
620 auto fut = result->get_future();
621
622 m_ioc.post([this, result, user_id, field]() {
623 ExceptionGuard exception_guard(m_exception_handler, [result] {
624 result->set_exception(std::current_exception());
625 });
626
627 const auto profile_field_flags =
628 eDBUserInfoFields_ProfileNickname |
629 eDBUserInfoFields_ProfileOccupation |
630 eDBUserInfoFields_ProfileEmail |
631 eDBUserInfoFields_ProfileAge |
632 eDBUserInfoFields_ProfileSex |
633 eDBUserInfoFields_ProfileUserpic |
634 eDBUserInfoFields_ProfileAbout
635 ;
636
637 const auto user_field_flags =
638 eDBUserInfoFields_Name |
639 eDBUserInfoFields_Password |
640 eDBUserInfoFields_Presence
641 ;
642
643 if (field & profile_field_flags) {
644 const auto profile_id_query = str(
645 format("SELECT profile_id "
646 "FROM user "
647 "WHERE id='%1%';") % user_id);
648
649 const auto profileid_data = m_db->exec(profile_id_query);
650
651 VERIFY(profileid_data);
652
653 VERIFY(profileid_data->rows.size() == 1);
654
655 const int profile_id = stoi(profileid_data->rows.at(0).at(0));
656
657 auto query = format("SELECT %1% FROM profile WHERE id='%2%';");
658
659 const auto profile_field_query = str(
660 field == eDBUserInfoFields_ProfileNickname ?
661 (query % "nickname" % profile_id)
662 : field == eDBUserInfoFields_ProfileOccupation ?
663 (query % "occupation" % profile_id)
664 : field == eDBUserInfoFields_ProfileEmail ?
665 (query % "email" % profile_id)
666 : field == eDBUserInfoFields_ProfileAge ?
667 (query % "age" % profile_id)
668 : field == eDBUserInfoFields_ProfileSex ?
669 (query % "sex" % profile_id)
670 : field == eDBUserInfoFields_ProfileUserpic ?
671 (query % "userpic" % profile_id)
672 : field == eDBUserInfoFields_ProfileAbout ?
673 (query % "about" % profile_id)
674 : format("")
675 );
676
677 VERIFY(!profile_field_query.empty());
678
679 const auto profile_field_data = m_db->exec(profile_field_query);
680
681 VERIFY(profile_field_data);
682
683 VERIFY(profile_field_data->rows.size() == 1);
684
685 const auto& field_strval = profile_field_data->rows.at(0).at(0);
686
687 auto field_value =
688 field == eDBUserInfoFields_ProfileNickname ?
689 boost::any(field_strval)
690 : field == eDBUserInfoFields_ProfileOccupation ?
691 boost::any(field_strval)
692 : field == eDBUserInfoFields_ProfileEmail ?
693 boost::any(field_strval)
694 : field == eDBUserInfoFields_ProfileAge ?
695 boost::any(stoi(field_strval))
696 : field == eDBUserInfoFields_ProfileSex ?
697 boost::any(stoi(field_strval))
698 : field == eDBUserInfoFields_ProfileUserpic ?
699 boost::any(field_strval)
700 : field == eDBUserInfoFields_ProfileAbout ?
701 boost::any(field_strval)
702 : boost::any()
703 ;
704
705 VERIFY(!field_value.empty());
706
707 result->set_value(std::move(field_value));
708 } else {
709 VERIFY(field & user_field_flags);
710
711 auto query = format("SELECT %1% FROM user WHERE id='%2%';");
712
713 const auto user_field_query = str(
714 field == eDBUserInfoFields_Name ?
715 (query % "name" % user_id)
716 : field == eDBUserInfoFields_Password ?
717 (query % "password" % user_id)
718 : field == eDBUserInfoFields_Presence ?
719 (query % "presence" % user_id)
720 : format("")
721 );
722
723 VERIFY(!user_field_query.empty())
724
725 const auto user_field_data = m_db->exec(user_field_query);
726
727 VERIFY(user_field_data);
728
729 const auto& field_strval = user_field_data->rows.at(0).at(0);
730
731 auto field_value =
732 field == eDBUserInfoFields_Name ?
733 boost::any(field_strval)
734 : field == eDBUserInfoFields_Password ?
735 boost::any(field_strval)
736 : field == eDBUserInfoFields_Presence ?
737 boost::any(static_cast<time_t>(stol(field_strval)))
738 : boost::any()
739 ;
740
741 VERIFY(!field_value.empty());
742
743 result->set_value(std::move(field_value));
744 }
745 });
746
747 return fut;
748}
749
750
751unique_future<bool> DALSQLite::delete_user(int user_id) {
752 auto result(make_shared<promise<bool>>());
753 auto fut = result->get_future();
754
755 m_ioc.post([this, user_id, result]() {
756 ExceptionGuard exception_guard(m_exception_handler, [result] {
757 result->set_exception(std::current_exception());
758 });
759
760 TransactionGuard transaction(*m_db);
761
762 const auto profile_id_query = str(
763 format("SELECT profile_id "
764 "FROM user "
765 "WHERE id='%1%';") % user_id);
766
767 const auto profileid_data = m_db->exec(profile_id_query);
768
769 VERIFY(profileid_data);
770 VERIFY(profileid_data->rows.size() == 1);
771
772 const int profile_id = stoi(profileid_data->rows.at(0).at(0));
773
774 const auto delete_user_query = str(
775 format("DELETE FROM user WHERE id='%1%';") % sql_escape(user_id));
776
777 m_db->exec(delete_user_query);
778
779 VERIFY(m_db->num_affected_rows() == 1);
780
781 const auto delete_profile_query = str(
782 format("DELETE FROM profile WHERE id='%1%';") % sql_escape(profile_id));
783
784 m_db->exec(delete_profile_query);
785
786 VERIFY(m_db->num_affected_rows() == 1);
787
788 transaction.commit();
789 result->set_value(true);
790 });
791
792 return fut;
793}
794
795
796
797
798boost::unique_future<int> DALSQLite::create_message(const DBMessage& message) {
799 auto result(make_shared<promise<int>>());
800 auto fut = result->get_future();
801
802 m_ioc.post([this, message=message, result]() {
803 ExceptionGuard exception_guard(m_exception_handler, [result] {
804 result->set_exception(std::current_exception());
805 });
806
807 TransactionGuard transaction(*m_db);
808
809 const auto insert_message_query = str(
810 format("INSERT INTO message "
811 " (parent, issue_time, user, "
812 " channel_id, content) "
813 "VALUES ('%1%', '%2%', '%3%', '%4%', '%5%'); "
814 "SELECT last_insert_rowid();")
815 % sql_escape(message.parent_id)
816 % sql_escape(message.issue_time)
817 % sql_escape(message.user_id)
818 % sql_escape(message.channel_id)
819 % sql_escape(message.content)
820 );
821
822 const auto message_dataset = m_db->exec(insert_message_query);
823
824 VERIFY(message_dataset);
825
826 const int message_id = stoi(message_dataset->rows.at(0).at(0));
827
828 transaction.commit();
829
830 result->set_value(message_id);
831 });
832
833 return fut;
834}
835
836boost::unique_future<bool> DALSQLite::has_message(int message_id) {
837 auto result(make_shared<promise<bool>>());
838 auto fut = result->get_future();
839
840 m_ioc.post([this, message_id, result]() {
841 ExceptionGuard exception_guard(m_exception_handler, [result] {
842 result->set_exception(std::current_exception());
843 });
844
845 const auto message_count_query = str(
846 format("SELECT COUNT(*) "
847 "FROM message "
848 "WHERE id='%1%';") % message_id);
849
850 const auto message_count_data = m_db->exec(message_count_query);
851
852 VERIFY(message_count_data);
853 VERIFY(message_count_data->rows.size() == 1);
854
855 const bool has_message = stoi(message_count_data->rows.at(0).at(0)) == 1;
856 result->set_value(has_message);
857 });
858
859 return fut;
860}
861
862boost::unique_future<DBMessage> DALSQLite::read_message(int message_id) {
863 auto result(make_shared<promise<DBMessage>>());
864 auto fut = result->get_future();
865
866 m_ioc.post([this, result, message_id]() {
867 ExceptionGuard exception_guard(m_exception_handler, [result] {
868 result->set_exception(std::current_exception());
869 });
870
871 const auto message_query = str(
872 format("SELECT parent, issue_time, user, "
873 " channel_id, content "
874 "FROM message "
875 "WHERE id='%1%';") % sql_escape(message_id));
876
877 const auto message_data = m_db->exec(message_query);
878
879 VERIFY(message_data);
880
881 VERIFY(message_data->rows.size() == 1);
882
883 const auto& row = message_data->rows.at(0);
884
885 DBMessage message;
886 message.dbid = message_id;
887 message.parent_id = stoi(row.at(0));
888 message.issue_time = stol(row.at(1));
889 message.user_id = stoi(row.at(2));
890 message.channel_id = stoi(row.at(3));
891 message.content = row.at(4);
892
893 result->set_value(move(message));
894 });
895
896 return fut;
897}
898
899boost::unique_future<bool> DALSQLite::update_message(DBMessage message) {
900 auto result(make_shared<promise<bool>>());
901 auto fut = result->get_future();
902
903 m_ioc.post([this, result, msg=move(message)]() {
904 ExceptionGuard exception_guard(m_exception_handler, [result] {
905 result->set_exception(std::current_exception());
906 });
907
908 TransactionGuard transaction(*m_db);
909
910 const auto update_message_query = str(
911 format("UPDATE message "
912 "SET parent='%1%', issue_time='%2%', user='%3%',"
913 " channel_id='%4%', content='%5%' "
914 "WHERE id='%6%';")
915 % sql_escape(msg.parent_id)
916 % sql_escape(msg.issue_time)
917 % sql_escape(msg.user_id)
918 % sql_escape(msg.channel_id)
919 % sql_escape(msg.content)
920 % sql_escape(msg.dbid)
921 );
922
923 m_db->exec(update_message_query);
924
925 VERIFY(m_db->num_affected_rows() == 1);
926
927 transaction.commit();
928
929 result->set_value(true);
930 });
931
932 return fut;
933}
934
935boost::unique_future<bool> DALSQLite::update_message_field(
936 int message_id, eDBMessageFields field, boost::any value)
937{
938 auto result(make_shared<promise<bool>>());
939 auto fut = result->get_future();
940
941 m_ioc.post([this, result, message_id, field, value]() {
942 ExceptionGuard exception_guard(m_exception_handler, [result] {
943 result->set_exception(std::current_exception());
944 });
945
946 TransactionGuard transaction(*m_db);
947
948 auto query =
949 format("UPDATE message SET %2%='%3%' WHERE id='%1%';") % message_id;
950
951#ifdef DEBUG_DAL
952 cout << __PRETTY_FUNCTION__ << " Field: " << field << endl;
953#endif
954 const auto update_message_field_query = str(
955 field == eDBMessageFields_Parent ?
956 (query % "parent" % sql_escape(any_cast<int>(value)))
957 : field == eDBMessageFields_IssueTime ?
958 (query % "issue_time" % sql_escape(any_cast<time_t>(value)))
959 : field == eDBMessageFields_User ?
960 (query % "user" % sql_escape(any_cast<int>(value)))
961 : field == eDBMessageFields_ChannelId ?
962 (query % "channel_id" % sql_escape(any_cast<int>(value)))
963 : field == eDBMessageFields_Content ?
964 (query % "content" % sql_escape(any_cast<string>(value)))
965 : format("")
966 );
967
968 VERIFY(!update_message_field_query.empty());
969
970 m_db->exec(update_message_field_query);
971
972 VERIFY(m_db->num_affected_rows() == 1);
973
974 transaction.commit();
975 result->set_value(true);
976 });
977
978 return fut;
979}
980
981boost::unique_future<boost::any> DALSQLite::read_message_field(
982 int message_id, eDBMessageFields field)
983{
984 auto result(make_shared<promise<boost::any>>());
985 auto fut = result->get_future();
986
987 m_ioc.post([this, result, message_id, field]() {
988 ExceptionGuard exception_guard(m_exception_handler, [result] {
989 result->set_exception(std::current_exception());
990 });
991
992 auto query =
993 format("SELECT %2% FROM message WHERE id='%1%';") % message_id;
994
995#ifdef DEBUG_DAL
996 cout << __PRETTY_FUNCTION__ << " Field: " << field << endl;
997#endif
998
999 const auto message_field_query = str(
1000 field == eDBMessageFields_Parent ? (query % "parent")
1001 : field == eDBMessageFields_IssueTime ? (query % "issue_time")
1002 : field == eDBMessageFields_User ? (query % "user")
1003 : field == eDBMessageFields_ChannelId ? (query % "channel_id")
1004 : field == eDBMessageFields_Content ? (query % "content")
1005 : format("")
1006 );
1007
1008 VERIFY(!message_field_query.empty());
1009
1010 const auto message_field_data = m_db->exec(message_field_query);
1011
1012 VERIFY(message_field_data);
1013
1014 VERIFY(message_field_data->rows.size() == 1);
1015
1016 const auto& field_strval = message_field_data->rows.at(0).at(0);
1017
1018 auto field_value =
1019 field == eDBMessageFields_Parent ?
1020 boost::any(stoi(field_strval))
1021 : field == eDBMessageFields_IssueTime ?
1022 boost::any(static_cast<time_t>(stol(field_strval)))
1023 : field == eDBMessageFields_User ?
1024 boost::any(stoi(field_strval))
1025 : field == eDBMessageFields_ChannelId ?
1026 boost::any(stoi(field_strval))
1027 : field == eDBMessageFields_Content ?
1028 boost::any(field_strval)
1029 : boost::any()
1030 ;
1031
1032 VERIFY(!field_value.empty());
1033
1034 result->set_value(field_value);
1035 });
1036
1037 return fut;
1038}
1039
1040boost::unique_future<bool> DALSQLite::delete_message(int message_id) {
1041 auto result(make_shared<promise<bool>>());
1042 auto fut = result->get_future();
1043
1044 m_ioc.post([this, message_id, result]() {
1045 ExceptionGuard exception_guard(m_exception_handler, [result] {
1046 result->set_exception(std::current_exception());
1047 });
1048
1049 TransactionGuard transaction(*m_db);
1050
1051 const auto delete_message_query = str(
1052 format("DELETE FROM message WHERE id='%1%';") % sql_escape(message_id));
1053
1054 m_db->exec(delete_message_query);
1055
1056 VERIFY(m_db->num_affected_rows() == 1);
1057
1058 transaction.commit();
1059 result->set_value(true);
1060 });
1061
1062 return fut;
1063}
1064
1065
1066
1067
1068boost::unique_future<int> DALSQLite::create_channel(const DBChannel& channel) {
1069 auto result(make_shared<promise<int>>());
1070 auto fut = result->get_future();
1071
1072 m_ioc.post([this, channel=channel, result]() {
1073 ExceptionGuard exception_guard(m_exception_handler, [result] {
1074 result->set_exception(std::current_exception());
1075 });
1076
1077 TransactionGuard transaction(*m_db);
1078
1079 const auto insert_channel_query = str(
1080 format("INSERT INTO channel "
1081 " (creator, type, title, creation_time) "
1082 "VALUES ('%1%', '%2%', '%3%', '%4%'); "
1083 "SELECT last_insert_rowid();")
1084 % sql_escape(channel.creator)
1085 % sql_escape(channel.type)
1086 % sql_escape(channel.title)
1087 % sql_escape(channel.creation_time)
1088 );
1089
1090 const auto channel_dataset = m_db->exec(insert_channel_query);
1091
1092 VERIFY(channel_dataset);
1093
1094 const int channel_id = stoi(channel_dataset->rows.at(0).at(0));
1095
1096 transaction.commit();
1097
1098 result->set_value(channel_id);
1099 });
1100
1101 return fut;
1102}
1103
1104boost::unique_future<bool> DALSQLite::has_channel(int channel_id) {
1105 auto result(make_shared<promise<bool>>());
1106 auto fut = result->get_future();
1107
1108 m_ioc.post([this, channel_id, result]() {
1109 ExceptionGuard exception_guard(m_exception_handler, [result] {
1110 result->set_exception(std::current_exception());
1111 });
1112
1113 const auto channel_count_query = str(
1114 format("SELECT COUNT(*) "
1115 "FROM channel "
1116 "WHERE id='%1%';") % channel_id);
1117
1118 const auto channel_count_data = m_db->exec(channel_count_query);
1119
1120 VERIFY(channel_count_data);
1121 VERIFY(channel_count_data->rows.size() == 1);
1122
1123 const bool has_channel = stoi(channel_count_data->rows.at(0).at(0)) == 1;
1124 result->set_value(has_channel);
1125 });
1126
1127 return fut;
1128}
1129
1130boost::unique_future<DBChannel> DALSQLite::read_channel(int channel_id) {
1131 auto result(make_shared<promise<DBChannel>>());
1132 auto fut = result->get_future();
1133
1134 m_ioc.post([this, result, channel_id]() {
1135 ExceptionGuard exception_guard(m_exception_handler, [result] {
1136 result->set_exception(std::current_exception());
1137 });
1138
1139 const auto channel_query = str(
1140 format("SELECT creator, type, title, creation_time "
1141 "FROM channel "
1142 "WHERE id='%1%';") % sql_escape(channel_id));
1143
1144 const auto channel_data = m_db->exec(channel_query);
1145
1146 VERIFY(channel_data);
1147
1148 VERIFY(channel_data->rows.size() == 1);
1149
1150 const auto& row = channel_data->rows.at(0);
1151
1152 DBChannel channel;
1153 channel.dbid = channel_id;
1154 channel.creator = stoi(row.at(0));
1155 channel.type = stoi(row.at(1));
1156 channel.title = row.at(2);
1157 channel.creation_time = stol(row.at(3));
1158
1159 result->set_value(move(channel));
1160 });
1161
1162 return fut;
1163}
1164
1165boost::unique_future<bool> DALSQLite::update_channel(DBChannel channel) {
1166 auto result(make_shared<promise<bool>>());
1167 auto fut = result->get_future();
1168
1169 m_ioc.post([this, result, channel=move(channel)]() {
1170 ExceptionGuard exception_guard(m_exception_handler, [result] {
1171 result->set_exception(std::current_exception());
1172 });
1173
1174 TransactionGuard transaction(*m_db);
1175
1176 const auto update_channel_query = str(
1177 format("UPDATE channel "
1178 "SET creator='%1%', type='%2%', title='%3%', creation_time='%4%' "
1179 "WHERE id='%5%';")
1180 % sql_escape(channel.creator)
1181 % sql_escape(channel.type)
1182 % sql_escape(channel.title)
1183 % sql_escape(channel.creation_time)
1184 % sql_escape(channel.dbid)
1185 );
1186
1187 m_db->exec(update_channel_query);
1188
1189 VERIFY(m_db->num_affected_rows() == 1);
1190
1191 transaction.commit();
1192
1193 result->set_value(true);
1194 });
1195
1196 return fut;
1197}
1198
1199boost::unique_future<bool> DALSQLite::update_channel_field(
1200 int channel_id, eDBChannelFields field, boost::any value)
1201{
1202 auto result(make_shared<promise<bool>>());
1203 auto fut = result->get_future();
1204
1205 m_ioc.post([this, result, channel_id, field, value]() {
1206 ExceptionGuard exception_guard(m_exception_handler, [result] {
1207 result->set_exception(std::current_exception());
1208 });
1209
1210 TransactionGuard transaction(*m_db);
1211
1212 auto query =
1213 format("UPDATE channel SET %2%='%3%' WHERE id='%1%';") % channel_id;
1214
1215#ifdef DEBUG_DAL
1216 cout << __PRETTY_FUNCTION__ << " Field: " << field << endl;
1217#endif
1218 const auto update_channel_field_query = str(
1219 field == eDBChannelFields_Type ?
1220 (query % "type" % sql_escape(any_cast<int>(value)))
1221 : field == eDBChannelFields_Creator ?
1222 (query % "creator" % sql_escape(any_cast<int>(value)))
1223 : field == eDBChannelFields_Title ?
1224 (query % "title" % sql_escape(any_cast<string>(value)))
1225 : field == eDBChannelFields_CreationTime ?
1226 (query % "creation_time" % sql_escape(any_cast<time_t>(value)))
1227 : format("")
1228 );
1229
1230 VERIFY(!update_channel_field_query.empty());
1231
1232 m_db->exec(update_channel_field_query);
1233
1234 VERIFY(m_db->num_affected_rows() == 1);
1235
1236 transaction.commit();
1237
1238 result->set_value(true);
1239 });
1240
1241 return fut;
1242}
1243
1244boost::unique_future<boost::any> DALSQLite::read_channel_field(
1245 int channel_id, eDBChannelFields field)
1246{
1247 auto result(make_shared<promise<boost::any>>());
1248 auto fut = result->get_future();
1249
1250 m_ioc.post([this, result, channel_id, field]() {
1251 ExceptionGuard exception_guard(m_exception_handler, [result] {
1252 result->set_exception(std::current_exception());
1253 });
1254
1255 auto query =
1256 format("SELECT %2% FROM channel WHERE id='%1%';") % channel_id;
1257
1258#ifdef DEBUG_DAL
1259 cout << __PRETTY_FUNCTION__ << " Field: " << field << endl;
1260#endif
1261
1262 const auto channel_field_query = str(
1263 field == eDBChannelFields_Type ? (query % "type")
1264 : field == eDBChannelFields_Creator ? (query % "creator")
1265 : field == eDBChannelFields_Title ? (query % "title")
1266 : field == eDBChannelFields_CreationTime ? (query % "creation_time")
1267 : format("")
1268 );
1269
1270 VERIFY(!channel_field_query.empty());
1271
1272 const auto channel_field_data = m_db->exec(channel_field_query);
1273
1274 VERIFY(channel_field_data);
1275
1276 VERIFY(channel_field_data->rows.size() == 1);
1277
1278 const auto& field_strval = channel_field_data->rows.at(0).at(0);
1279
1280 auto field_value =
1281 field == eDBChannelFields_Type ?
1282 boost::any(stoi(field_strval))
1283 : field == eDBChannelFields_Creator ?
1284 boost::any(stoi(field_strval))
1285 : field == eDBChannelFields_Title ?
1286 boost::any(field_strval)
1287 : field == eDBChannelFields_CreationTime ?
1288 boost::any(static_cast<time_t>(stol(field_strval)))
1289 : boost::any()
1290 ;
1291
1292 VERIFY(!field_value.empty());
1293
1294 result->set_value(field_value);
1295 });
1296
1297 return fut;
1298}
1299
1300boost::unique_future<bool> DALSQLite::delete_channel(int channel_id) {
1301 auto result(make_shared<promise<bool>>());
1302 auto fut = result->get_future();
1303
1304 m_ioc.post([this, channel_id, result]() {
1305 ExceptionGuard exception_guard(m_exception_handler, [result] {
1306 result->set_exception(std::current_exception());
1307 });
1308
1309 TransactionGuard transaction(*m_db);
1310
1311 const auto delete_channel_query = str(
1312 format("DELETE FROM channel WHERE id='%1%';") % sql_escape(channel_id));
1313
1314 m_db->exec(delete_channel_query);
1315
1316 VERIFY(m_db->num_affected_rows() == 1);
1317
1318 transaction.commit();
1319
1320 result->set_value(true);
1321 });
1322
1323 return fut;
1324}
1325
1326
1327
1328boost::unique_future<int> DALSQLite::create_subscription(
1329 const DBSubscription& subscription) {
1330
1331 auto result(make_shared<promise<int>>());
1332 auto fut = result->get_future();
1333
1334 m_ioc.post([this, subscription=subscription, result]() {
1335 ExceptionGuard exception_guard(m_exception_handler, [result] {
1336 result->set_exception(std::current_exception());
1337 });
1338
1339 TransactionGuard transaction(*m_db);
1340
1341 const auto insert_subscription_query = str(
1342 format("INSERT INTO subscription (user_id, channel_id, presence) "
1343 "VALUES ('%1%', '%2%', '%3%'); "
1344 "SELECT last_insert_rowid();")
1345 % sql_escape(subscription.user)
1346 % sql_escape(subscription.channel)
1347 % sql_escape(subscription.presence)
1348 );
1349
1350 const auto subscription_dataset = m_db->exec(insert_subscription_query);
1351
1352 VERIFY(subscription_dataset);
1353
1354 const int subscription_id = stoi(subscription_dataset->rows.at(0).at(0));
1355
1356 transaction.commit();
1357
1358 result->set_value(subscription_id);
1359 });
1360
1361 return fut;
1362}
1363
1364boost::unique_future<bool> DALSQLite::has_subscription(int subscription_id) {
1365 auto result(make_shared<promise<bool>>());
1366 auto fut = result->get_future();
1367
1368 m_ioc.post([this, subscription_id, result]() {
1369 ExceptionGuard exception_guard(m_exception_handler, [result] {
1370 result->set_exception(std::current_exception());
1371 });
1372
1373 const auto subscription_count_query = str(
1374 format("SELECT COUNT(*) "
1375 "FROM subscription "
1376 "WHERE id='%1%';") % subscription_id);
1377
1378 const auto subscription_count_data = m_db->exec(subscription_count_query);
1379
1380 VERIFY(subscription_count_data);
1381 VERIFY(subscription_count_data->rows.size() == 1);
1382
1383 const bool has_subscription =
1384 stoi(subscription_count_data->rows.at(0).at(0)) == 1;
1385
1386 result->set_value(has_subscription);
1387 });
1388
1389 return fut;
1390}
1391
1392boost::unique_future<DBSubscription> DALSQLite::read_subscription(
1393 int subscription_id) {
1394
1395 auto result(make_shared<promise<DBSubscription>>());
1396 auto fut = result->get_future();
1397
1398 m_ioc.post([this, result, subscription_id]() {
1399 ExceptionGuard exception_guard(m_exception_handler, [result] {
1400 result->set_exception(std::current_exception());
1401 });
1402
1403 const auto subscription_query = str(
1404 format("SELECT user_id, channel_id, presence "
1405 "FROM subscription "
1406 "WHERE id='%1%';") % sql_escape(subscription_id));
1407
1408 const auto subscription_data = m_db->exec(subscription_query);
1409
1410 VERIFY(subscription_data);
1411
1412 VERIFY(subscription_data->rows.size() == 1);
1413
1414 const auto& row = subscription_data->rows.at(0);
1415
1416 DBSubscription subscription;
1417 subscription.dbid = subscription_id;
1418 subscription.user = stoi(row.at(0));
1419 subscription.channel = stoi(row.at(1));
1420 subscription.presence = static_cast<time_t>(stol(row.at(2)));
1421
1422 result->set_value(move(subscription));
1423 });
1424
1425 return fut;
1426}
1427
1428boost::unique_future<bool> DALSQLite::update_subscription(
1429 DBSubscription subscription) {
1430
1431 auto result(make_shared<promise<bool>>());
1432 auto fut = result->get_future();
1433
1434 m_ioc.post([this, result, subscription=move(subscription)]() {
1435 ExceptionGuard exception_guard(m_exception_handler, [result] {
1436 result->set_exception(std::current_exception());
1437 });
1438
1439 TransactionGuard transaction(*m_db);
1440
1441 const auto update_subscription_query = str(
1442 format("UPDATE subscription "
1443 "SET user_id='%1%', channel_id='%2%', presence='%3%' "
1444 "WHERE id='%4%';")
1445 % sql_escape(subscription.user)
1446 % sql_escape(subscription.channel)
1447 % sql_escape(subscription.presence)
1448 % sql_escape(subscription.dbid)
1449 );
1450
1451 m_db->exec(update_subscription_query);
1452
1453 VERIFY(m_db->num_affected_rows() == 1);
1454
1455 transaction.commit();
1456
1457 result->set_value(true);
1458 });
1459
1460 return fut;
1461}
1462
1463boost::unique_future<bool> DALSQLite::update_subscription_field(
1464 int subscription_id, eDBSubscriptionFields field, boost::any value)
1465{
1466 auto result(make_shared<promise<bool>>());
1467 auto fut = result->get_future();
1468
1469 m_ioc.post([this, result, subscription_id, field, value]() {
1470 ExceptionGuard exception_guard(m_exception_handler, [result] {
1471 result->set_exception(std::current_exception());
1472 });
1473
1474 TransactionGuard transaction(*m_db);
1475
1476 auto query =
1477 format("UPDATE subscription SET %2%='%3%' WHERE id='%1%';")
1478 % subscription_id;
1479
1480#ifdef DEBUG_DAL
1481 cout << __PRETTY_FUNCTION__ << " Field: " << field << endl;
1482#endif
1483 const auto update_subscription_field_query = str(
1484 field == eDBSubscriptionFields_User ?
1485 (query % "user_id" % sql_escape(any_cast<int>(value)))
1486 : field == eDBSubscriptionFields_Channel ?
1487 (query % "channel_id" % sql_escape(any_cast<int>(value)))
1488 : field == eDBSubscriptionFields_Presence ?
1489 (query % "presence" % sql_escape(any_cast<time_t>(value)))
1490 : format("")
1491 );
1492
1493 VERIFY(!update_subscription_field_query.empty());
1494
1495 m_db->exec(update_subscription_field_query);
1496
1497 VERIFY(m_db->num_affected_rows() == 1);
1498
1499 transaction.commit();
1500
1501 result->set_value(true);
1502 });
1503
1504 return fut;
1505}
1506
1507boost::unique_future<boost::any> DALSQLite::read_subscription_field(
1508 int subscription_id, eDBSubscriptionFields field)
1509{
1510 auto result(make_shared<promise<boost::any>>());
1511 auto fut = result->get_future();
1512
1513 m_ioc.post([this, result, subscription_id, field]() {
1514 ExceptionGuard exception_guard(m_exception_handler, [result] {
1515 result->set_exception(std::current_exception());
1516 });
1517
1518 auto query =
1519 format("SELECT %2% FROM subscription WHERE id='%1%';") % subscription_id;
1520
1521#ifdef DEBUG_DAL
1522 cout << __PRETTY_FUNCTION__ << " Field: " << field << endl;
1523#endif
1524
1525 const auto subscription_field_query = str(
1526 field == eDBSubscriptionFields_User ? (query % "user_id")
1527 : field == eDBSubscriptionFields_Channel ? (query % "channel_id")
1528 : field == eDBSubscriptionFields_Presence ? (query % "presence")
1529 : format("")
1530 );
1531
1532 VERIFY(!subscription_field_query.empty());
1533
1534 const auto subscription_field_data = m_db->exec(subscription_field_query);
1535
1536 VERIFY(subscription_field_data);
1537
1538 VERIFY(subscription_field_data->rows.size() == 1);
1539
1540 const auto& field_strval = subscription_field_data->rows.at(0).at(0);
1541
1542 auto field_value =
1543 field == eDBSubscriptionFields_User ?
1544 boost::any(stoi(field_strval))
1545 : field == eDBSubscriptionFields_Channel ?
1546 boost::any(stoi(field_strval))
1547 : field == eDBSubscriptionFields_Presence ?
1548 boost::any(static_cast<time_t>(stol(field_strval)))
1549 : boost::any()
1550 ;
1551
1552 VERIFY(!field_value.empty());
1553
1554 result->set_value(field_value);
1555 });
1556
1557 return fut;
1558}
1559
1560boost::unique_future<bool> DALSQLite::delete_subscription(
1561 int subscrdelete_subscription_id) {
1562
1563 auto result(make_shared<promise<bool>>());
1564 auto fut = result->get_future();
1565
1566 m_ioc.post([this, subscrdelete_subscription_id, result]() {
1567 ExceptionGuard exception_guard(m_exception_handler, [result] {
1568 result->set_exception(std::current_exception());
1569 });
1570
1571 TransactionGuard transaction(*m_db);
1572
1573 const auto delete_subscription_query = str(
1574 format("DELETE FROM subscription WHERE id='%1%';")
1575 % sql_escape(subscrdelete_subscription_id));
1576
1577 m_db->exec(delete_subscription_query);
1578
1579 VERIFY(m_db->num_affected_rows() == 1);
1580
1581 transaction.commit();
1582
1583 result->set_value(true);
1584 });
1585
1586 return fut;
1587}
1588
1589
1590