main-db.cpp 33 KB
Newer Older
Ronan's avatar
Ronan committed
1
/*
2
 * main-db.cpp
Ghislain MARY's avatar
Ghislain MARY committed
3
 * Copyright (C) 2010-2017 Belledonne Communications SARL
Ronan's avatar
Ronan committed
4
 *
Ghislain MARY's avatar
Ghislain MARY committed
5 6 7 8
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
Ronan's avatar
Ronan committed
9 10 11 12 13 14 15
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
Ghislain MARY's avatar
Ghislain MARY committed
16 17
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
Ronan's avatar
Ronan committed
18 19
 */

20
#include <algorithm>
21
#include <ctime>
22

Ronan's avatar
Ronan committed
23 24 25 26
#ifdef SOCI_ENABLED
	#include <soci/soci.h>
#endif // ifdef SOCI_ENABLED

27 28
#include "linphone/utils/utils.h"

29
#include "chat/chat-room/chat-room.h"
30
#include "conference/participant.h"
31
#include "content/content-type.h"
Ronan's avatar
Ronan committed
32
#include "content/content.h"
33
#include "db/session/db-session-provider.h"
34
#include "event-log/events.h"
Ronan's avatar
Ronan committed
35
#include "event-log/event-log-p.h"
Ronan's avatar
Ronan committed
36
#include "logger/logger.h"
Ronan's avatar
Ronan committed
37
#include "main-db-p.h"
Ronan's avatar
Ronan committed
38 39 40

// =============================================================================

Ronan's avatar
Ronan committed
41 42
using namespace std;

Ronan's avatar
Ronan committed
43 44 45 46
LINPHONE_BEGIN_NAMESPACE

// -----------------------------------------------------------------------------

47
MainDb::MainDb () : AbstractDb(*new MainDbPrivate) {}
Ronan's avatar
Ronan committed
48

49 50
#ifdef SOCI_ENABLED

Ronan's avatar
Ronan committed
51
// -----------------------------------------------------------------------------
52
// Soci backend.
Ronan's avatar
Ronan committed
53 54
// -----------------------------------------------------------------------------

55 56 57 58 59
	template<typename T>
	struct EnumToSql {
		T first;
		const char *second;
	};
60

61 62 63 64 65 66 67
	template<typename T>
	static constexpr const char *mapEnumToSql (const EnumToSql<T> enumToSql[], size_t n, T key) {
		return n == 0 ? "" : (
			enumToSql[n - 1].first == key ? enumToSql[n - 1].second : mapEnumToSql(enumToSql, n - 1, key)
		);
	}

68
	static constexpr EnumToSql<MainDb::Filter> eventFilterToSql[] = {
Ronan's avatar
Ronan committed
69 70 71
		{ MainDb::ConferenceCallFilter, "1, 2" },
		{ MainDb::ConferenceChatMessageFilter, "5" },
		{ MainDb::ConferenceInfoFilter, "3, 4, 6, 7, 8, 9, 10, 11, 12" }
72
	};
73

74
	static constexpr const char *mapEventFilterToSql (MainDb::Filter filter) {
75 76 77
		return mapEnumToSql(
			eventFilterToSql, sizeof eventFilterToSql / sizeof eventFilterToSql[0], filter
		);
78 79
	}

Ronan's avatar
Ronan committed
80 81
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
82 83 84 85 86
	static string buildSqlEventFilter (
		const list<MainDb::Filter> &filters,
		MainDb::FilterMask mask,
		const string &condKeyWord = "WHERE"
	) {
87
		L_ASSERT(
88 89
			find_if(filters.cbegin(), filters.cend(), [](const MainDb::Filter &filter) {
					return filter == MainDb::NoFilter;
90 91 92
				}) == filters.cend()
		);

93
		if (mask == MainDb::NoFilter)
94 95 96 97 98 99 100 101 102 103
			return "";

		bool isStart = true;
		string sql;
		for (const auto &filter : filters) {
			if (!(mask & filter))
				continue;

			if (isStart) {
				isStart = false;
Ronan's avatar
Ronan committed
104
				sql += " " + condKeyWord + " type IN (";
105
			} else
Ronan's avatar
Ronan committed
106
				sql += ", ";
107 108 109
			sql += mapEventFilterToSql(filter);
		}

Ronan's avatar
Ronan committed
110 111 112
		if (!isStart)
			sql += ") ";

113 114 115
		return sql;
	}

116 117
// -----------------------------------------------------------------------------

118
	long MainDbPrivate::insertSipAddress (const string &sipAddress) {
119 120 121
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

122
		long id;
123 124
		*session << "SELECT id FROM sip_address WHERE value = :sipAddress", soci::use(sipAddress), soci::into(id);
		if (session->got_data())
125 126
			return id;

127 128
		*session << "INSERT INTO sip_address (value) VALUES (:sipAddress)", soci::use(sipAddress);
		return q->getLastInsertId();
129 130
	}

Ronan's avatar
Ronan committed
131
	void MainDbPrivate::insertContent (long eventId, const Content &content) {
132 133
		L_Q();

134 135
		soci::session *session = dbSession.getBackendSession<soci::session>();

136
		long contentTypeId = insertContentType(content.getContentType().asString());
137
		*session << "INSERT INTO chat_message_content (event_id, content_type_id, body) VALUES"
Ronan's avatar
Ronan committed
138
			"  (:eventId, :contentTypeId, :body)", soci::use(eventId), soci::use(contentTypeId),
139
			soci::use(content.getBodyAsString());
140 141 142

		long messageContentId = q->getLastInsertId();
		for (const auto &appData : content.getAppDataMap())
143
			*session << "INSERT INTO chat_message_content_app_data (chat_message_content_id, key, data) VALUES"
144 145
				"  (:messageContentId, :key, :data)",
				soci::use(messageContentId), soci::use(appData.first), soci::use(appData.second);
146 147
	}

148
	long MainDbPrivate::insertContentType (const string &contentType) {
149 150 151
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

152
		long id;
153 154
		*session << "SELECT id FROM content_type WHERE value = :contentType", soci::use(contentType), soci::into(id);
		if (session->got_data())
155 156
			return id;

157 158
		*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
		return q->getLastInsertId();
159 160
	}

161
	long MainDbPrivate::insertChatRoom (long sipAddressId, int capabilities, const tm &date) {
162 163
		soci::session *session = dbSession.getBackendSession<soci::session>();

164
		long id;
165
		*session << "SELECT peer_sip_address_id FROM chat_room WHERE peer_sip_address_id = :sipAddressId",
166
			soci::use(sipAddressId), soci::into(id);
167
		if (!session->got_data())
168 169 170
			*session << "INSERT INTO chat_room (peer_sip_address_id, creation_date, last_update_date, capabilities, subject) VALUES"
				"  (:sipAddressId, :creationDate, :lastUpdateDate, :capabilities, '')",
				soci::use(sipAddressId), soci::use(date), soci::use(date), soci::use(capabilities);
171
		else
172
			*session << "UPDATE chat_room SET last_update_date = :lastUpdateDate WHERE peer_sip_address_id = :sipAddressId",
173
				soci::use(date), soci::use(sipAddressId);
174

175 176 177
		return sipAddressId;
	}

178
	void MainDbPrivate::insertChatRoomParticipant (long chatRoomId, long sipAddressId, bool isAdmin) {
179
		soci::session *session = dbSession.getBackendSession<soci::session>();
180 181 182 183 184 185 186 187 188 189
		soci::statement statement = (
			session->prepare << "UPDATE chat_room_participant SET is_admin = :isAdmin"
				"  WHERE chat_room_id = :chatRoomId AND sip_address_id = :sipAddressId",
				soci::use(static_cast<int>(isAdmin)), soci::use(chatRoomId), soci::use(sipAddressId)
		);
		statement.execute(true);
		if (statement.get_affected_rows() == 0)
			*session << "INSERT INTO chat_room_participant (chat_room_id, sip_address_id, is_admin)"
				"  VALUES (:chatRoomId, :sipAddressId, :isAdmin)",
				soci::use(chatRoomId), soci::use(sipAddressId), soci::use(static_cast<int>(isAdmin));
190 191
	}

192
	void MainDbPrivate::insertChatMessageParticipant (long eventId, long sipAddressId, int state) {
193 194
		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::statement statement = (
195
			session->prepare << "UPDATE chat_message_participant SET state = :state"
Ronan's avatar
Ronan committed
196 197
				"  WHERE event_id = :eventId AND sip_address_id = :sipAddressId",
				soci::use(state), soci::use(eventId), soci::use(sipAddressId)
198 199
		);
		statement.execute(true);
200
		if (statement.get_affected_rows() == 0 && state != static_cast<int>(ChatMessage::State::Displayed))
201
			*session << "INSERT INTO chat_message_participant (event_id, sip_address_id, state)"
Ronan's avatar
Ronan committed
202 203
				"  VALUES (:eventId, :sipAddressId, :state)",
				soci::use(eventId), soci::use(sipAddressId), soci::use(state);
204 205
	}

206 207 208
// -----------------------------------------------------------------------------

	long MainDbPrivate::insertEvent (const EventLog &eventLog) {
209 210 211 212 213 214
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
		soci::use(static_cast<int>(eventLog.getType())), soci::use(Utils::getLongAsTm(eventLog.getTime()));
		return q->getLastInsertId();
215 216
	}

217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233
	long MainDbPrivate::insertConferenceEvent (const EventLog &eventLog, long *chatRoomId) {
		long eventId = insertEvent(eventLog);
		long curChatRoomId = insertSipAddress(
			static_cast<const ConferenceEvent &>(eventLog).getConferenceAddress().asString()
		);

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_event (event_id, chat_room_id)"
			"  VALUES (:eventId, :chatRoomId)", soci::use(eventId), soci::use(curChatRoomId);

		if (chatRoomId)
			*chatRoomId = curChatRoomId;

		return eventId;
	}

	long MainDbPrivate::insertConferenceCallEvent (const EventLog &eventLog) {
234 235 236 237
		// TODO.
		return 0;
	}

238 239
	long MainDbPrivate::insertConferenceChatMessageEvent (const EventLog &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_cast<const ConferenceChatMessageEvent &>(eventLog).getChatMessage();
240 241 242 243 244 245 246 247
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

		tm eventTime = Utils::getLongAsTm(static_cast<long>(eventLog.getTime()));

248 249 250 251
		long localSipAddressId = insertSipAddress(chatMessage->getLocalAddress().asString());
		long remoteSipAddressId = insertSipAddress(chatMessage->getRemoteAddress().asString());
		insertChatRoom(remoteSipAddressId, chatRoom->getCapabilities(), eventTime);
		long eventId = insertConferenceEvent(eventLog);
252

253
		soci::session *session = dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
254

255 256 257 258 259 260 261 262 263 264 265 266
		*session << "INSERT INTO conference_chat_message_event ("
			"  event_id, local_sip_address_id, remote_sip_address_id,"
			"  state, direction, imdn_message_id, is_secured"
			") VALUES ("
			"  :eventId, :localSipaddressId, :remoteSipaddressId,"
			"  :state, :direction, :imdnMessageId, :isSecured"
			")", soci::use(eventId), soci::use(localSipAddressId), soci::use(remoteSipAddressId),
			soci::use(static_cast<int>(chatMessage->getState())), soci::use(static_cast<int>(chatMessage->getDirection())),
			soci::use(chatMessage->getImdnMessageId()), soci::use(chatMessage->isSecured() ? 1 : 0);

		for (const auto &content : chatMessage->getContents())
			insertContent(eventId, content);
267

268 269 270
		return eventId;
	}

271
	long MainDbPrivate::insertConferenceNotifiedEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
272 273 274
		long chatRoomId;
		long eventId = insertConferenceEvent(eventLog, &chatRoomId);
		unsigned int lastNotifyId = static_cast<const ConferenceNotifiedEvent &>(eventLog).getNotifyId();
275 276 277

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
278 279 280
			"  VALUES (:eventId, :notifyId)", soci::use(eventId), soci::use(lastNotifyId);
		*session << "UPDATE chat_room SET last_notify_id = :lastNotifyId WHERE peer_sip_address_id = :chatRoomId",
			soci::use(lastNotifyId), soci::use(chatRoomId);
281

282 283 284 285 286
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantEvent (const EventLog &eventLog) {
		long eventId = insertConferenceNotifiedEvent(eventLog);
287 288 289 290
		long participantAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantEvent &>(eventLog).getParticipantAddress().asString()
		);

291
		soci::session *session = dbSession.getBackendSession<soci::session>();
292
		*session << "INSERT INTO conference_participant_event (event_id, participant_address_id)"
293
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
294

295 296 297 298
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantDeviceEvent (const EventLog &eventLog) {
299 300 301 302 303 304
		long eventId = insertConferenceParticipantEvent(eventLog);
		long gruuAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantDeviceEvent &>(eventLog).getGruuAddress().asString()
		);

		soci::session *session = dbSession.getBackendSession<soci::session>();
305
		*session << "INSERT INTO conference_participant_device_event (event_id, gruu_address_id)"
306
			"  VALUES (:eventId, :gruuAddressId)", soci::use(eventId), soci::use(gruuAddressId);
307

308
		return eventId;
309 310 311
	}

	long MainDbPrivate::insertConferenceSubjectEvent (const EventLog &eventLog) {
312
		long eventId = insertConferenceNotifiedEvent(eventLog);
313 314

		soci::session *session = dbSession.getBackendSession<soci::session>();
315
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
316 317 318
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(
				static_cast<const ConferenceSubjectEvent &>(eventLog).getSubject()
			);
319

320
		return eventId;
321 322
	}

323
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
324

325
	void MainDb::init () {
326
		L_D();
Ronan's avatar
Ronan committed
327
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
328

Ronan's avatar
Ronan committed
329
		*session <<
330 331
			"CREATE TABLE IF NOT EXISTS sip_address ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
332
			"  value VARCHAR(255) UNIQUE NOT NULL"
333 334
			")";

335
		*session <<
336 337
			"CREATE TABLE IF NOT EXISTS content_type ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
338
			"  value VARCHAR(255) UNIQUE NOT NULL"
339 340
			")";

Ronan's avatar
Ronan committed
341
		*session <<
342 343
			"CREATE TABLE IF NOT EXISTS event ("
			"  id" + primaryKeyAutoIncrementStr() + ","
344
			"  type TINYINT UNSIGNED NOT NULL,"
345
			"  date DATE NOT NULL"
346 347
			")";

Ronan's avatar
Ronan committed
348
		*session <<
349
			"CREATE TABLE IF NOT EXISTS chat_room ("
350
			// Server (for conference) or user sip address.
351
			"  peer_sip_address_id INT UNSIGNED PRIMARY KEY,"
352 353

			// Dialog creation date.
354 355 356 357
			"  creation_date DATE NOT NULL,"

			// Last event date (call, message...).
			"  last_update_date DATE NOT NULL,"
358

359 360 361
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

362 363 364
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
365
			"  last_notify_id INT UNSIGNED,"
366

367
			"  FOREIGN KEY (peer_sip_address_id)"
368 369 370 371
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

372 373 374 375 376 377
		*session <<
			"CREATE TABLE IF NOT EXISTS chat_room_participant ("
			"  chat_room_id INT UNSIGNED NOT NULL,"
			"  sip_address_id INT UNSIGNED NOT NULL,"
			"  is_admin BOOLEAN NOT NULL,"

378
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
379 380 381 382 383 384
			"  FOREIGN KEY (chat_room_id)"
			"    REFERENCES chat_room(peer_sip_address_id)"
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
385 386 387 388 389 390 391 392 393 394 395 396 397 398 399
			")";

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_event ("
			"  event_id INT UNSIGNED PRIMARY KEY,"
			"  chat_room_id INT UNSIGNED NOT NULL,"

			"  FOREIGN KEY (event_id)"
			"    REFERENCES event(id)"
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (chat_room_id)"
			"    REFERENCES chat_room(peer_sip_address_id)"
			"    ON DELETE CASCADE"
			")";

400 401 402 403 404 405 406 407 408 409
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_notified_event ("
			"  event_id INT UNSIGNED PRIMARY KEY,"
			"  notify_id INT UNSIGNED NOT NULL,"

			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_event(event_id)"
			"    ON DELETE CASCADE"
			")";

410 411
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
412
			"  event_id INT UNSIGNED PRIMARY KEY,"
413 414
			"  participant_address_id INT UNSIGNED NOT NULL,"

415 416
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
417 418 419 420 421 422 423 424
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (participant_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_device_event ("
425
			"  event_id INT UNSIGNED PRIMARY KEY,"
426 427
			"  gruu_address_id INT UNSIGNED NOT NULL,"

428 429
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_participant_event(event_id)"
430 431 432 433 434 435 436 437
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (gruu_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_subject_event ("
438
			"  event_id INT UNSIGNED PRIMARY KEY,"
439 440
			"  subject VARCHAR(255),"

441 442
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
443
			"    ON DELETE CASCADE"
444 445
			")";

Ronan's avatar
Ronan committed
446
		*session <<
447
			"CREATE TABLE IF NOT EXISTS conference_chat_message_event ("
448
			"  event_id INT UNSIGNED PRIMARY KEY,"
449 450
			"  local_sip_address_id INT UNSIGNED NOT NULL,"
			"  remote_sip_address_id INT UNSIGNED NOT NULL,"
451 452 453 454

			// See: https://tools.ietf.org/html/rfc5438#section-6.3
			"  imdn_message_id VARCHAR(255) NOT NULL,"

455 456
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
457
			"  is_secured BOOLEAN NOT NULL,"
458

459
			"  FOREIGN KEY (event_id)"
460
			"    REFERENCES conference_event(id)"
461
			"    ON DELETE CASCADE,"
462 463
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
464
			"    ON DELETE CASCADE,"
465
			"  FOREIGN KEY (remote_sip_address_id)"
466
			"    REFERENCES sip_address(id)"
467 468 469
			"    ON DELETE CASCADE"
			")";

470
		*session <<
471
			"CREATE TABLE IF NOT EXISTS chat_message_participant ("
Ronan's avatar
Ronan committed
472
			"  event_id INT UNSIGNED NOT NULL,"
473 474 475
			"  sip_address_id INT UNSIGNED NOT NULL,"
			"  state TINYINT UNSIGNED NOT NULL,"

Ronan's avatar
Ronan committed
476 477
			"  PRIMARY KEY (event_id, sip_address_id),"
			"  FOREIGN KEY (event_id)"
478
			"    REFERENCES conference_chat_message_event(event_id)"
479 480 481
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
482
			"    ON DELETE CASCADE"
483 484
			")";

485
		*session <<
486
			"CREATE TABLE IF NOT EXISTS chat_message_content ("
487
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
488
			"  event_id INT UNSIGNED NOT NULL,"
489
			"  content_type_id INT UNSIGNED NOT NULL,"
490
			"  body TEXT NOT NULL,"
491

Ronan's avatar
Ronan committed
492
			"  FOREIGN KEY (event_id)"
493
			"    REFERENCES conference_chat_message_event(event_id)"
494 495 496
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
497 498
			"    ON DELETE CASCADE"
			")";
499

500
		*session <<
501 502
			"CREATE TABLE IF NOT EXISTS chat_message_content_app_data ("
			"  chat_message_content_id INT UNSIGNED NOT NULL,"
503 504 505
			"  key VARCHAR(255),"
			"  data BLOB,"

506 507 508
			"  PRIMARY KEY (chat_message_content_id, key),"
			"  FOREIGN KEY (chat_message_content_id)"
			"    REFERENCES chat_message_content(id)"
509 510 511
			"    ON DELETE CASCADE"
			")";

512
		*session <<
513
			"CREATE TABLE IF NOT EXISTS conference_message_crypto_data ("
Ronan's avatar
Ronan committed
514
			"  event_id INT UNSIGNED NOT NULL,"
515
			"  key VARCHAR(255),"
516 517
			"  data BLOB,"

Ronan's avatar
Ronan committed
518 519
			"  PRIMARY KEY (event_id, key),"
			"  FOREIGN KEY (event_id)"
520
			"    REFERENCES conference_chat_message_event(event_id)"
521
			"    ON DELETE CASCADE"
522
			")";
523 524 525 526

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
527 528
			"CREATE TRIGGER IF NOT EXISTS chat_message_participant_deleter"
			"  AFTER UPDATE OF state ON chat_message_participant FOR EACH ROW"
529 530
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
531
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
532 533
			"    SELECT state FROM chat_message_participant WHERE"
			"    NEW.event_id = chat_message_participant.event_id"
534 535 536 537 538
			"    AND state <> ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += "    LIMIT 1"
			"  )) = 0"
			"  BEGIN"
539 540
			"  DELETE FROM chat_message_participant WHERE NEW.event_id = chat_message_participant.event_id;"
			"  UPDATE conference_chat_message_event SET state = ";
541
		participantMessageDeleter += displayedId;
Ronan's avatar
Ronan committed
542
		participantMessageDeleter += " WHERE event_id = NEW.event_id;"
543 544 545
			"  END";

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
546
	}
547

548
	bool MainDb::addEvent (const EventLog &eventLog) {
549 550
		L_D();

Ronan's avatar
Ronan committed
551 552
		if (!isConnected()) {
			lWarning() << "Unable to add event. Not connected.";
553
			return false;
Ronan's avatar
Ronan committed
554 555
		}

556 557 558 559 560 561
		bool soFarSoGood = false;

		L_BEGIN_LOG_EXCEPTION

		soci::transaction tr(*d->dbSession.getBackendSession<soci::session>());

Ronan's avatar
Ronan committed
562
		switch (eventLog.getType()) {
563
			case EventLog::Type::None:
Ronan's avatar
Ronan committed
564
				return false;
565

566 567
			case EventLog::Type::ConferenceChatMessage:
				d->insertConferenceChatMessageEvent(eventLog);
568 569
				break;

570 571
			case EventLog::Type::CallStart:
			case EventLog::Type::CallEnd:
572
				d->insertConferenceCallEvent(eventLog);
573
				break;
574

575 576
			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
577 578 579
				d->insertConferenceEvent(eventLog);
				break;

580 581 582 583
			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
584 585 586
				d->insertConferenceParticipantEvent(eventLog);
				break;

587 588
			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
589 590 591
				d->insertConferenceParticipantDeviceEvent(eventLog);
				break;

592
			case EventLog::Type::ConferenceSubjectChanged:
593
				d->insertConferenceSubjectEvent(eventLog);
Ronan's avatar
Ronan committed
594 595 596
				break;
		}

597 598 599 600 601 602 603
		tr.commit();

		soFarSoGood = true;

		L_END_LOG_EXCEPTION

		return soFarSoGood;
Ronan's avatar
Ronan committed
604 605
	}

606
	bool MainDb::deleteEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
607 608
		L_D();

Ronan's avatar
Ronan committed
609 610 611 612 613
		if (!isConnected()) {
			lWarning() << "Unable to delete event. Not connected.";
			return false;
		}

Ronan's avatar
Ronan committed
614 615
		long &storageId = const_cast<EventLog &>(eventLog).getPrivate()->storageId;
		if (storageId < 0)
Ronan's avatar
Ronan committed
616 617 618 619 620
			return false;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
621 622
		*session << "DELETE FROM event WHERE id = :id", soci::use(storageId);
		storageId = -1;
Ronan's avatar
Ronan committed
623 624 625

		L_END_LOG_EXCEPTION

Ronan's avatar
Ronan committed
626
		return storageId == -1;
Ronan's avatar
Ronan committed
627 628
	}

629
	void MainDb::cleanEvents (FilterMask mask) {
Ronan's avatar
Ronan committed
630 631
		L_D();

Ronan's avatar
Ronan committed
632 633 634 635 636
		if (!isConnected()) {
			lWarning() << "Unable to clean events. Not connected.";
			return;
		}

Ronan's avatar
Ronan committed
637
		string query = "DELETE FROM event" +
638
			buildSqlEventFilter({ ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter }, mask);
Ronan's avatar
Ronan committed
639 640 641 642 643 644 645

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
		*session << query;

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
646 647
	}

648
	int MainDb::getEventsCount (FilterMask mask) const {
649
		L_D();
Ronan's avatar
Ronan committed
650

Ronan's avatar
Ronan committed
651 652 653 654 655
		if (!isConnected()) {
			lWarning() << "Unable to get events count. Not connected.";
			return 0;
		}

Ronan's avatar
Ronan committed
656
		string query = "SELECT COUNT(*) FROM event" +
657
			buildSqlEventFilter({ ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter }, mask);
Ronan's avatar
Ronan committed
658 659 660 661 662 663 664 665 666 667 668 669
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
		*session << query, soci::into(count);

		L_END_LOG_EXCEPTION

		return count;
	}

670
	int MainDb::getMessagesCount (const string &peerAddress) const {
671
		L_D();
672

Ronan's avatar
Ronan committed
673 674 675 676
		if (!isConnected()) {
			lWarning() << "Unable to get messages count. Not connected.";
			return 0;
		}
677

678 679 680 681 682
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
683

684
		string query = "SELECT COUNT(*) FROM conference_chat_message_event";
Ronan's avatar
Ronan committed
685 686 687
		if (peerAddress.empty())
			*session << query, soci::into(count);
		else {
688 689 690 691
			query += "  WHERE event_id IN ("
				"  SELECT event_id FROM conference_event WHERE chat_room_id = ("
				"    SELECT id FROM sip_address WHERE value = :peerAddress"
				"  )"
Ronan's avatar
Ronan committed
692 693 694 695
				")";

			*session << query, soci::use(peerAddress), soci::into(count);
		}
696 697 698 699

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
700 701
	}

702
	int MainDb::getUnreadMessagesCount (const string &peerAddress) const {
703
		L_D();
704

Ronan's avatar
Ronan committed
705 706 707 708 709
		if (!isConnected()) {
			lWarning() << "Unable to get unread messages count. Not connected.";
			return 0;
		}

710 711
		int count = 0;

712
		string query = "SELECT COUNT(*) FROM conference_chat_message_event WHERE";
713
		if (!peerAddress.empty())
714 715 716 717 718
			query += " event_id IN ("
				"  SELECT event_id FROM conference_event WHERE chat_room_id = ("
				"    SELECT id FROM sip_address WHERE value = :peerAddress"
				"  )"
				") AND";
719 720

		query += " direction = " + Utils::toString(static_cast<int>(ChatMessage::Direction::Incoming)) +
721
			+ " AND state <> " + Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
722

723 724 725
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
726 727 728 729 730

		if (peerAddress.empty())
			*session << query, soci::into(count);
		else
			*session << query, soci::use(peerAddress), soci::into(count);
731 732 733 734

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
735 736
	}

737
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &peerAddress, int nLast, FilterMask mask) const {
Ronan's avatar
Ronan committed
738 739 740 741 742
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
743
		// TODO.
Ronan's avatar
Ronan committed
744
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
745 746
	}

747
	list<shared_ptr<EventLog>> MainDb::getHistory (
Ronan's avatar
Ronan committed
748
		const string &peerAddress,
749 750 751 752
		int begin,
		int end,
		FilterMask mask
	) const {
Ronan's avatar
Ronan committed
753 754 755 756
		L_D();

		list<shared_ptr<EventLog>> events;

Ronan's avatar
Ronan committed
757 758
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
Ronan's avatar
Ronan committed
759
			return events;
Ronan's avatar
Ronan committed
760 761
		}

Ronan's avatar
Ronan committed
762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802
		if (begin < 0)
			begin = 0;

		if (end > 0 && begin > end) {
			lWarning() << "Unable to get history. Invalid range.";
			return events;
		}

		string query = "SELECT id, type, date FROM event"
			"  WHERE id IN ("
			"    SELECT event_id FROM conference_event WHERE chat_room_id = ("
			"      SELECT id FROM sip_address WHERE value = :peerAddress"
			"    )"
			"  )";
		query += buildSqlEventFilter({
			ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter
		}, mask, "AND");
		query += "  ORDER BY id DESC";

		if (end >= 0)
			query += "  LIMIT " + Utils::toString(end + 1 - begin);
		else
			query += "  LIMIT -1";

		if (begin > 0)
			query += "  OFFSET " + Utils::toString(begin);

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
		soci::transaction tr(*session);

		soci::rowset<soci::row> rows = (session->prepare << query, soci::use(peerAddress));
		for (const auto &row : rows) {
			(void)row;
			events.push_back(std::make_shared<EventLog>());
		}

		L_END_LOG_EXCEPTION

		return events;
Ronan's avatar
Ronan committed
803 804
	}

805
	void MainDb::cleanHistory (const string &peerAddress, FilterMask mask) {
806 807
		L_D();

Ronan's avatar
Ronan committed
808 809 810 811 812
		if (!isConnected()) {
			lWarning() << "Unable to clean history. Not connected.";
			return;
		}

Ronan's avatar
Ronan committed
813 814
		// TODO: Deal with mask.

815
		string query;
816 817 818
		if (mask == MainDb::NoFilter || mask & ConferenceChatMessageFilter)
			query += "SELECT event_id FROM conference_event WHERE chat_room_id = ("
				"  SELECT id FROM sip_address WHERE value = :peerAddress"
819 820 821 822 823
				")";

		if (query.empty())
			return;

824 825 826
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
827
		*session << "DELETE FROM event WHERE id IN (" + query + ")", soci::use(peerAddress);
828 829

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
830
	}
Ronan's avatar
Ronan committed
831

832 833
// -----------------------------------------------------------------------------

834 835 836 837 838 839
list<shared_ptr<ChatRoom>> MainDb::getChatRooms () const {
	list<shared_ptr<ChatRoom>> chatRooms;
	// TODO.
	return chatRooms;
}

840 841 842
shared_ptr<ChatRoom> MainDb::findChatRoom (const string &peerAddress) const {
	L_D();

843
	// TODO: Use core cache.
844

Ronan's avatar
Ronan committed
845
	L_BEGIN_LOG_EXCEPTION
846

Ronan's avatar
Ronan committed
847
	soci::session *session = d->dbSession.getBackendSession<soci::session>();
848

Ronan's avatar
Ronan committed
849 850 851 852
	tm creationDate;
	tm lastUpdateDate;
	int capabilities;
	string subject;
853

Ronan's avatar
Ronan committed
854 855 856 857 858 859 860
	*session << "SELECT creation_date, last_update_date, capabilities, subject "
		"  FROM chat_room"
		"  WHERE peer_sip_address_id = ("
		"    SELECT id from sip_address WHERE value = :peerAddress"
		"  )", soci::use(peerAddress), soci::into(creationDate), soci::into(lastUpdateDate),
		soci::use(capabilities), soci::use(subject);

861 862
	// TODO.

Ronan's avatar
Ronan committed
863
	L_END_LOG_EXCEPTION
864 865 866 867 868 869

	return shared_ptr<ChatRoom>();
}

// -----------------------------------------------------------------------------

870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895
	#define LEGACY_MESSAGE_COL_LOCAL_ADDRESS 1
	#define LEGACY_MESSAGE_COL_REMOTE_ADDRESS 2
	#define LEGACY_MESSAGE_COL_DIRECTION 3
	#define LEGACY_MESSAGE_COL_TEXT 4
	#define LEGACY_MESSAGE_COL_STATE 7
	#define LEGACY_MESSAGE_COL_URL 8
	#define LEGACY_MESSAGE_COL_DATE 9
	#define LEGACY_MESSAGE_COL_APP_DATA 10
	#define LEGACY_MESSAGE_COL_CONTENT_ID 11
	#define LEGACY_MESSAGE_COL_IMDN_MESSAGE_ID 12
	#define LEGACY_MESSAGE_COL_CONTENT_TYPE 13
	#define LEGACY_MESSAGE_COL_IS_SECURED 14

	template<typename T>
	static T getValueFromLegacyMessage (const soci::row &message, int index, bool &isNull) {
		isNull = false;

		try {
			return message.get<T>(static_cast<size_t>(index));
		} catch (const exception &) {
			isNull = true;
		}

		return T();
	}

896
	bool MainDb::import (Backend, const string &parameters) {
897 898
		L_D();

899 900 901 902 903
		if (!isConnected()) {
			lWarning() << "Unable to import data. Not connected.";
			return 0;
		}

904 905 906 907 908 909 910 911 912 913 914 915 916
		// Backend is useless, it's sqlite3. (Only available legacy backend.)
		const string uri = "sqlite3://" + parameters;
		DbSession inDbSession = DbSessionProvider::getInstance()->getSession(uri);

		if (!inDbSession) {
			lWarning() << "Unable to connect to: `" << uri << "`.";
			return false;
		}

		soci::session *inSession = inDbSession.getBackendSession<soci::session>();

		// Import messages.
		try {
917
			soci::rowset<soci::row> messages = (inSession->prepare << "SELECT * FROM history");
918
			try {