main-db.cpp 36.8 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
	}

Ronan's avatar
Ronan committed
206 207
// -----------------------------------------------------------------------------

208
	shared_ptr<EventLog> MainDbPrivate::selectGenericConferenceEvent (
209 210 211 212 213
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
214 215 216 217 218 219
		switch (type) {
			case EventLog::Type::None:
				return nullptr;

			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
220
				return selectConferenceEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
221 222 223

			case EventLog::Type::CallStart:
			case EventLog::Type::CallEnd:
224
				return selectConferenceCallEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
225 226

			case EventLog::Type::ConferenceChatMessage:
227
				return selectConferenceChatMessageEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
228 229 230 231 232

			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
233
				return selectConferenceParticipantEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
234 235 236

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
237
				return selectConferenceParticipantDeviceEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
238 239

			case EventLog::Type::ConferenceSubjectChanged:
240
				return selectConferenceSubjectEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
241 242 243 244 245
		}

		return nullptr;
	}

246 247 248 249 250 251
	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
252 253 254 255 256 257 258 259 260
		// Useless here.
		(void)eventId;

		// TODO: Use cache.
		return make_shared<ConferenceEvent>(
			type,
			date,
			Address(peerAddress)
		);
Ronan's avatar
Ronan committed
261 262
	}

263 264 265 266 267 268
	shared_ptr<EventLog> MainDbPrivate::selectConferenceCallEvent (
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
269 270 271 272
		// TODO.
		return nullptr;
	}

273 274 275 276 277 278
	shared_ptr<EventLog> MainDbPrivate::selectConferenceChatMessageEvent (
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
279 280 281 282
		// TODO.
		return nullptr;
	}

283 284 285 286 287 288
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306
		unsigned int notifyId;
		string participantAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "SELECT notify_id, participant_address_id"
			"  FROM conference_notified_event, conference_participant_event"
			"  WHERE conference_participant_event.event_id = :eventId"
			"    AND conference_notified_event.event_id = conference_participant_event.event_id",
			soci::into(notifyId), soci::into(participantAddress), soci::use(eventId);

		// TODO: Use cache.
		return make_shared<ConferenceParticipantEvent>(
			type,
			date,
			Address(peerAddress),
			notifyId,
			Address(participantAddress)
		);
Ronan's avatar
Ronan committed
307 308
	}

309 310 311 312 313 314
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
315 316 317 318
		// TODO.
		return nullptr;
	}

319 320 321 322 323 324 325 326 327
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
		unsigned int notifyId;
		string subject;

328 329 330 331 332 333
		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "SELECT notify_id, subject"
			"  FROM conference_notified_event, conference_subject_event"
			"  WHERE conference_subject_event.event_id = :eventId"
			"    AND conference_notified_event.event_id = conference_subject_event.event_id",
			soci::into(notifyId), soci::into(subject), soci::use(eventId);
334 335 336 337 338 339 340 341

		// TODO: Use cache.
		return make_shared<ConferenceSubjectEvent>(
			date,
			Address(peerAddress),
			notifyId,
			subject
		);
Ronan's avatar
Ronan committed
342 343
	}

344 345 346
// -----------------------------------------------------------------------------

	long MainDbPrivate::insertEvent (const EventLog &eventLog) {
347 348 349 350 351 352
		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();
353 354
	}

355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371
	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) {
372 373 374 375
		// TODO.
		return 0;
	}

376 377
	long MainDbPrivate::insertConferenceChatMessageEvent (const EventLog &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_cast<const ConferenceChatMessageEvent &>(eventLog).getChatMessage();
378 379 380 381 382 383 384 385
		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()));

386 387 388 389
		long localSipAddressId = insertSipAddress(chatMessage->getLocalAddress().asString());
		long remoteSipAddressId = insertSipAddress(chatMessage->getRemoteAddress().asString());
		insertChatRoom(remoteSipAddressId, chatRoom->getCapabilities(), eventTime);
		long eventId = insertConferenceEvent(eventLog);
390

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

393 394 395 396 397 398 399 400 401 402 403 404
		*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);
405

406 407 408
		return eventId;
	}

409
	long MainDbPrivate::insertConferenceNotifiedEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
410 411 412
		long chatRoomId;
		long eventId = insertConferenceEvent(eventLog, &chatRoomId);
		unsigned int lastNotifyId = static_cast<const ConferenceNotifiedEvent &>(eventLog).getNotifyId();
413 414 415

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
416 417 418
			"  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);
419

420 421 422 423 424
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantEvent (const EventLog &eventLog) {
		long eventId = insertConferenceNotifiedEvent(eventLog);
425 426 427 428
		long participantAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantEvent &>(eventLog).getParticipantAddress().asString()
		);

429
		soci::session *session = dbSession.getBackendSession<soci::session>();
430
		*session << "INSERT INTO conference_participant_event (event_id, participant_address_id)"
431
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
432

433 434 435 436
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantDeviceEvent (const EventLog &eventLog) {
437 438 439 440 441 442
		long eventId = insertConferenceParticipantEvent(eventLog);
		long gruuAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantDeviceEvent &>(eventLog).getGruuAddress().asString()
		);

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

446
		return eventId;
447 448 449
	}

	long MainDbPrivate::insertConferenceSubjectEvent (const EventLog &eventLog) {
450
		long eventId = insertConferenceNotifiedEvent(eventLog);
451 452

		soci::session *session = dbSession.getBackendSession<soci::session>();
453
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
454 455 456
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(
				static_cast<const ConferenceSubjectEvent &>(eventLog).getSubject()
			);
457

458
		return eventId;
459 460
	}

461
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
462

463
	void MainDb::init () {
464
		L_D();
Ronan's avatar
Ronan committed
465
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
466

Ronan's avatar
Ronan committed
467
		*session <<
468 469
			"CREATE TABLE IF NOT EXISTS sip_address ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
470
			"  value VARCHAR(255) UNIQUE NOT NULL"
471 472
			")";

473
		*session <<
474 475
			"CREATE TABLE IF NOT EXISTS content_type ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
476
			"  value VARCHAR(255) UNIQUE NOT NULL"
477 478
			")";

Ronan's avatar
Ronan committed
479
		*session <<
480 481
			"CREATE TABLE IF NOT EXISTS event ("
			"  id" + primaryKeyAutoIncrementStr() + ","
482
			"  type TINYINT UNSIGNED NOT NULL,"
483
			"  date DATE NOT NULL"
484 485
			")";

Ronan's avatar
Ronan committed
486
		*session <<
487
			"CREATE TABLE IF NOT EXISTS chat_room ("
488
			// Server (for conference) or user sip address.
489
			"  peer_sip_address_id INT UNSIGNED PRIMARY KEY,"
490 491

			// Dialog creation date.
492 493 494 495
			"  creation_date DATE NOT NULL,"

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

497 498 499
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

500 501 502
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
503
			"  last_notify_id INT UNSIGNED,"
504

505
			"  FOREIGN KEY (peer_sip_address_id)"
506 507 508 509
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

510 511 512 513 514 515
		*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,"

516
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
517 518 519 520 521 522
			"  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"
523 524 525 526 527 528 529 530 531 532 533 534 535 536 537
			")";

		*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"
			")";

538 539 540 541 542 543 544 545 546 547
		*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"
			")";

548 549
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
550
			"  event_id INT UNSIGNED PRIMARY KEY,"
551 552
			"  participant_address_id INT UNSIGNED NOT NULL,"

553 554
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
555 556 557 558 559 560 561 562
			"    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 ("
563
			"  event_id INT UNSIGNED PRIMARY KEY,"
564 565
			"  gruu_address_id INT UNSIGNED NOT NULL,"

566 567
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_participant_event(event_id)"
568 569 570 571 572 573 574 575
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (gruu_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_subject_event ("
576
			"  event_id INT UNSIGNED PRIMARY KEY,"
577 578
			"  subject VARCHAR(255),"

579 580
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
581
			"    ON DELETE CASCADE"
582 583
			")";

Ronan's avatar
Ronan committed
584
		*session <<
585
			"CREATE TABLE IF NOT EXISTS conference_chat_message_event ("
586
			"  event_id INT UNSIGNED PRIMARY KEY,"
587 588
			"  local_sip_address_id INT UNSIGNED NOT NULL,"
			"  remote_sip_address_id INT UNSIGNED NOT NULL,"
589 590 591 592

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

593 594
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
595
			"  is_secured BOOLEAN NOT NULL,"
596

597
			"  FOREIGN KEY (event_id)"
598
			"    REFERENCES conference_event(id)"
599
			"    ON DELETE CASCADE,"
600 601
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
602
			"    ON DELETE CASCADE,"
603
			"  FOREIGN KEY (remote_sip_address_id)"
604
			"    REFERENCES sip_address(id)"
605 606 607
			"    ON DELETE CASCADE"
			")";

608
		*session <<
609
			"CREATE TABLE IF NOT EXISTS chat_message_participant ("
Ronan's avatar
Ronan committed
610
			"  event_id INT UNSIGNED NOT NULL,"
611 612 613
			"  sip_address_id INT UNSIGNED NOT NULL,"
			"  state TINYINT UNSIGNED NOT NULL,"

Ronan's avatar
Ronan committed
614 615
			"  PRIMARY KEY (event_id, sip_address_id),"
			"  FOREIGN KEY (event_id)"
616
			"    REFERENCES conference_chat_message_event(event_id)"
617 618 619
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
620
			"    ON DELETE CASCADE"
621 622
			")";

623
		*session <<
624
			"CREATE TABLE IF NOT EXISTS chat_message_content ("
625
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
626
			"  event_id INT UNSIGNED NOT NULL,"
627
			"  content_type_id INT UNSIGNED NOT NULL,"
628
			"  body TEXT NOT NULL,"
629

Ronan's avatar
Ronan committed
630
			"  FOREIGN KEY (event_id)"
631
			"    REFERENCES conference_chat_message_event(event_id)"
632 633 634
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
635 636
			"    ON DELETE CASCADE"
			")";
637

638
		*session <<
639 640
			"CREATE TABLE IF NOT EXISTS chat_message_content_app_data ("
			"  chat_message_content_id INT UNSIGNED NOT NULL,"
641 642 643
			"  key VARCHAR(255),"
			"  data BLOB,"

644 645 646
			"  PRIMARY KEY (chat_message_content_id, key),"
			"  FOREIGN KEY (chat_message_content_id)"
			"    REFERENCES chat_message_content(id)"
647 648 649
			"    ON DELETE CASCADE"
			")";

650
		*session <<
651
			"CREATE TABLE IF NOT EXISTS conference_message_crypto_data ("
Ronan's avatar
Ronan committed
652
			"  event_id INT UNSIGNED NOT NULL,"
653
			"  key VARCHAR(255),"
654 655
			"  data BLOB,"

Ronan's avatar
Ronan committed
656 657
			"  PRIMARY KEY (event_id, key),"
			"  FOREIGN KEY (event_id)"
658
			"    REFERENCES conference_chat_message_event(event_id)"
659
			"    ON DELETE CASCADE"
660
			")";
661 662 663 664

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
665 666
			"CREATE TRIGGER IF NOT EXISTS chat_message_participant_deleter"
			"  AFTER UPDATE OF state ON chat_message_participant FOR EACH ROW"
667 668
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
669
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
670 671
			"    SELECT state FROM chat_message_participant WHERE"
			"    NEW.event_id = chat_message_participant.event_id"
672 673 674 675 676
			"    AND state <> ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += "    LIMIT 1"
			"  )) = 0"
			"  BEGIN"
677 678
			"  DELETE FROM chat_message_participant WHERE NEW.event_id = chat_message_participant.event_id;"
			"  UPDATE conference_chat_message_event SET state = ";
679
		participantMessageDeleter += displayedId;
Ronan's avatar
Ronan committed
680
		participantMessageDeleter += " WHERE event_id = NEW.event_id;"
681 682 683
			"  END";

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
684
	}
685

686
	bool MainDb::addEvent (const EventLog &eventLog) {
687 688
		L_D();

Ronan's avatar
Ronan committed
689 690
		if (!isConnected()) {
			lWarning() << "Unable to add event. Not connected.";
691
			return false;
Ronan's avatar
Ronan committed
692 693
		}

694 695 696 697 698 699
		bool soFarSoGood = false;

		L_BEGIN_LOG_EXCEPTION

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

Ronan's avatar
Ronan committed
700
		switch (eventLog.getType()) {
701
			case EventLog::Type::None:
Ronan's avatar
Ronan committed
702
				return false;
703

704 705
			case EventLog::Type::ConferenceChatMessage:
				d->insertConferenceChatMessageEvent(eventLog);
706 707
				break;

708 709
			case EventLog::Type::CallStart:
			case EventLog::Type::CallEnd:
710
				d->insertConferenceCallEvent(eventLog);
711
				break;
712

713 714
			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
715 716 717
				d->insertConferenceEvent(eventLog);
				break;

718 719 720 721
			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
722 723 724
				d->insertConferenceParticipantEvent(eventLog);
				break;

725 726
			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
727 728 729
				d->insertConferenceParticipantDeviceEvent(eventLog);
				break;

730
			case EventLog::Type::ConferenceSubjectChanged:
731
				d->insertConferenceSubjectEvent(eventLog);
Ronan's avatar
Ronan committed
732 733 734
				break;
		}

735 736 737 738 739 740 741
		tr.commit();

		soFarSoGood = true;

		L_END_LOG_EXCEPTION

		return soFarSoGood;
Ronan's avatar
Ronan committed
742 743
	}

744
	bool MainDb::deleteEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
745 746
		L_D();

Ronan's avatar
Ronan committed
747 748 749 750 751
		if (!isConnected()) {
			lWarning() << "Unable to delete event. Not connected.";
			return false;
		}

Ronan's avatar
Ronan committed
752 753
		long &storageId = const_cast<EventLog &>(eventLog).getPrivate()->storageId;
		if (storageId < 0)
Ronan's avatar
Ronan committed
754 755 756 757 758
			return false;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
759 760
		*session << "DELETE FROM event WHERE id = :id", soci::use(storageId);
		storageId = -1;
Ronan's avatar
Ronan committed
761 762 763

		L_END_LOG_EXCEPTION

Ronan's avatar
Ronan committed
764
		return storageId == -1;
Ronan's avatar
Ronan committed
765 766
	}

767
	void MainDb::cleanEvents (FilterMask mask) {
Ronan's avatar
Ronan committed
768 769
		L_D();

Ronan's avatar
Ronan committed
770 771 772 773 774
		if (!isConnected()) {
			lWarning() << "Unable to clean events. Not connected.";
			return;
		}

Ronan's avatar
Ronan committed
775
		string query = "DELETE FROM event" +
776
			buildSqlEventFilter({ ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter }, mask);
Ronan's avatar
Ronan committed
777 778 779 780 781 782 783

		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
784 785
	}

786
	int MainDb::getEventsCount (FilterMask mask) const {
787
		L_D();
Ronan's avatar
Ronan committed
788

Ronan's avatar
Ronan committed
789 790 791 792 793
		if (!isConnected()) {
			lWarning() << "Unable to get events count. Not connected.";
			return 0;
		}

Ronan's avatar
Ronan committed
794
		string query = "SELECT COUNT(*) FROM event" +
795
			buildSqlEventFilter({ ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter }, mask);
Ronan's avatar
Ronan committed
796 797 798 799 800 801 802 803 804 805 806 807
		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;
	}

808
	int MainDb::getMessagesCount (const string &peerAddress) const {
809
		L_D();
810

Ronan's avatar
Ronan committed
811 812 813 814
		if (!isConnected()) {
			lWarning() << "Unable to get messages count. Not connected.";
			return 0;
		}
815

816 817 818 819 820
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

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

822
		string query = "SELECT COUNT(*) FROM conference_chat_message_event";
Ronan's avatar
Ronan committed
823 824 825
		if (peerAddress.empty())
			*session << query, soci::into(count);
		else {
826 827 828 829
			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
830 831 832 833
				")";

			*session << query, soci::use(peerAddress), soci::into(count);
		}
834 835 836 837

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
838 839
	}

840
	int MainDb::getUnreadMessagesCount (const string &peerAddress) const {
841
		L_D();
842

Ronan's avatar
Ronan committed
843 844 845 846 847
		if (!isConnected()) {
			lWarning() << "Unable to get unread messages count. Not connected.";
			return 0;
		}

848 849
		int count = 0;

850
		string query = "SELECT COUNT(*) FROM conference_chat_message_event WHERE";
851
		if (!peerAddress.empty())
852 853 854 855 856
			query += " event_id IN ("
				"  SELECT event_id FROM conference_event WHERE chat_room_id = ("
				"    SELECT id FROM sip_address WHERE value = :peerAddress"
				"  )"
				") AND";
857 858

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

861 862 863
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
864 865 866 867 868

		if (peerAddress.empty())
			*session << query, soci::into(count);
		else
			*session << query, soci::use(peerAddress), soci::into(count);
869 870 871 872

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
873 874
	}

875
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &peerAddress, int nLast, FilterMask mask) const {
Ronan's avatar
Ronan committed
876 877 878 879 880
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
881
		// TODO.
Ronan's avatar
Ronan committed
882
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
883 884
	}

885
	list<shared_ptr<EventLog>> MainDb::getHistory (
Ronan's avatar
Ronan committed
886
		const string &peerAddress,
887 888 889 890
		int begin,
		int end,
		FilterMask mask
	) const {
Ronan's avatar
Ronan committed
891 892 893 894
		L_D();

		list<shared_ptr<EventLog>> events;

Ronan's avatar
Ronan committed
895 896
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
Ronan's avatar
Ronan committed
897
			return events;
Ronan's avatar
Ronan committed
898 899
		}

Ronan's avatar
Ronan committed
900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933
		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