main-db.cpp 40.5 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 "core/core.h"
34
#include "db/session/db-session-provider.h"
Ronan's avatar
Ronan committed
35
#include "event-log/event-log-p.h"
36
#include "event-log/events.h"
Ronan's avatar
Ronan committed
37
#include "logger/logger.h"
Ronan's avatar
Ronan committed
38
#include "main-db-p.h"
Ronan's avatar
Ronan committed
39 40 41

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

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

Ronan's avatar
Ronan committed
44 45 46 47
LINPHONE_BEGIN_NAMESPACE

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

48 49 50 51
MainDb::MainDb (Core *core) : AbstractDb(*new MainDbPrivate) {
	L_D();
	d->core = core;
}
Ronan's avatar
Ronan committed
52

53 54
#ifdef SOCI_ENABLED

Ronan's avatar
Ronan committed
55
// -----------------------------------------------------------------------------
56
// Soci backend.
Ronan's avatar
Ronan committed
57 58
// -----------------------------------------------------------------------------

59 60 61 62 63
	template<typename T>
	struct EnumToSql {
		T first;
		const char *second;
	};
64

65 66 67 68 69 70 71
	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)
		);
	}

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

78
	static constexpr const char *mapEventFilterToSql (MainDb::Filter filter) {
79 80 81
		return mapEnumToSql(
			eventFilterToSql, sizeof eventFilterToSql / sizeof eventFilterToSql[0], filter
		);
82 83
	}

Ronan's avatar
Ronan committed
84 85
// -----------------------------------------------------------------------------

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

97
		if (mask == MainDb::NoFilter)
98 99 100 101 102 103 104 105 106 107
			return "";

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

			if (isStart) {
				isStart = false;
Ronan's avatar
Ronan committed
108
				sql += " " + condKeyWord + " type IN (";
109
			} else
Ronan's avatar
Ronan committed
110
				sql += ", ";
111 112 113
			sql += mapEventFilterToSql(filter);
		}

Ronan's avatar
Ronan committed
114 115 116
		if (!isStart)
			sql += ") ";

117 118 119
		return sql;
	}

120 121
// -----------------------------------------------------------------------------

122
	long long MainDbPrivate::insertSipAddress (const string &sipAddress) {
123 124 125
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

126
		long long id;
127 128
		*session << "SELECT id FROM sip_address WHERE value = :sipAddress", soci::use(sipAddress), soci::into(id);
		if (session->got_data())
129 130
			return id;

131 132
		*session << "INSERT INTO sip_address (value) VALUES (:sipAddress)", soci::use(sipAddress);
		return q->getLastInsertId();
133 134
	}

135
	void MainDbPrivate::insertContent (long long eventId, const Content &content) {
136 137
		L_Q();

138 139
		soci::session *session = dbSession.getBackendSession<soci::session>();

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

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

152
	long long MainDbPrivate::insertContentType (const string &contentType) {
153 154 155
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

156
		long long id;
157 158
		*session << "SELECT id FROM content_type WHERE value = :contentType", soci::use(contentType), soci::into(id);
		if (session->got_data())
159 160
			return id;

161 162
		*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
		return q->getLastInsertId();
163 164
	}

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

168
		long long id;
169
		*session << "SELECT peer_sip_address_id FROM chat_room WHERE peer_sip_address_id = :sipAddressId",
170
			soci::use(sipAddressId), soci::into(id);
171
		if (!session->got_data())
172 173 174
			*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);
175
		else
176
			*session << "UPDATE chat_room SET last_update_date = :lastUpdateDate WHERE peer_sip_address_id = :sipAddressId",
177
				soci::use(date), soci::use(sipAddressId);
178

179 180 181
		return sipAddressId;
	}

182
	void MainDbPrivate::insertChatRoomParticipant (long long chatRoomId, long long sipAddressId, bool isAdmin) {
183
		soci::session *session = dbSession.getBackendSession<soci::session>();
184 185 186 187 188 189 190 191 192 193
		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));
194 195
	}

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

Ronan's avatar
Ronan committed
210 211
// -----------------------------------------------------------------------------

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

			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
224
				return selectConferenceEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
225

226 227
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
228
				return selectConferenceCallEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
229 230

			case EventLog::Type::ConferenceChatMessage:
231
				return selectConferenceChatMessageEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
232 233 234 235 236

			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
237
				return selectConferenceParticipantEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
238 239 240

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
241
				return selectConferenceParticipantDeviceEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
242 243

			case EventLog::Type::ConferenceSubjectChanged:
244
				return selectConferenceSubjectEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
245 246 247 248 249
		}

		return nullptr;
	}

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

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

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

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

287
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
288
		long long eventId,
289 290 291 292
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
293 294 295 296
		unsigned int notifyId;
		string participantAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
297 298
		*session << "SELECT notify_id, participant_address.value"
			"  FROM conference_notified_event, conference_participant_event, sip_address as participant_address"
299
			"  WHERE conference_participant_event.event_id = :eventId"
Ronan's avatar
Ronan committed
300 301
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
			"    AND participant_address.id = participant_address_id",
302 303 304 305 306 307 308 309 310 311
			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
312 313
	}

314
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
315
		long long eventId,
316 317 318 319
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
320 321 322 323 324
		unsigned int notifyId;
		string participantAddress;
		string gruuAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
325 326 327
		*session << "SELECT notify_id, participant_address.value, gruu_address.value"
			"  FROM conference_notified_event, conference_participant_event, conference_participant_device_event,"
			"    sip_address AS participant_address, sip_address AS gruu_address"
328 329
			"  WHERE conference_participant_device_event.event_id = :eventId"
			"    AND conference_participant_event.event_id = conference_participant_device_event.event_id"
330 331 332
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
			"    AND participant_address.id = participant_address_id"
			"    AND gruu_address.id = gruu_address_id",
333 334 335 336 337 338 339 340 341 342 343
			soci::into(notifyId), soci::into(participantAddress), soci::into(gruuAddress), soci::use(eventId);

		// TODO: Use cache.
		return make_shared<ConferenceParticipantDeviceEvent>(
			type,
			date,
			Address(peerAddress),
			notifyId,
			Address(participantAddress),
			Address(gruuAddress)
		);
Ronan's avatar
Ronan committed
344 345
	}

346
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
347
		long long eventId,
348 349 350 351 352 353 354
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
		unsigned int notifyId;
		string subject;

355 356 357 358 359 360
		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);
361 362 363 364 365 366 367 368

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

371 372
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
373
	long long MainDbPrivate::insertEvent (const shared_ptr<EventLog> &eventLog) {
374 375 376 377
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
Ronan's avatar
Ronan committed
378
		soci::use(static_cast<int>(eventLog->getType())), soci::use(Utils::getLongAsTm(eventLog->getTime()));
379
		return q->getLastInsertId();
380 381
	}

Ronan's avatar
Ronan committed
382
	long long MainDbPrivate::insertConferenceEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
383 384
		long long eventId = insertEvent(eventLog);
		long long curChatRoomId = insertSipAddress(
Ronan's avatar
Ronan committed
385
			static_pointer_cast<ConferenceEvent>(eventLog)->getConferenceAddress().asString()
386 387 388 389 390 391 392 393 394 395 396 397
		);

		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;
	}

Ronan's avatar
Ronan committed
398
	long long MainDbPrivate::insertConferenceCallEvent (const shared_ptr<EventLog> &eventLog) {
399 400 401 402
		// TODO.
		return 0;
	}

Ronan's avatar
Ronan committed
403 404
	long long MainDbPrivate::insertConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
405 406 407 408 409 410
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

Ronan's avatar
Ronan committed
411
		tm eventTime = Utils::getLongAsTm(static_cast<long>(eventLog->getTime()));
412

413 414
		long long localSipAddressId = insertSipAddress(chatMessage->getLocalAddress().asString());
		long long remoteSipAddressId = insertSipAddress(chatMessage->getRemoteAddress().asString());
415
		insertChatRoom(remoteSipAddressId, chatRoom->getCapabilities(), eventTime);
416
		long long eventId = insertConferenceEvent(eventLog);
417

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

420 421 422 423 424 425 426 427 428 429 430 431
		*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);
432

433 434 435
		return eventId;
	}

Ronan's avatar
Ronan committed
436
	long long MainDbPrivate::insertConferenceNotifiedEvent (const shared_ptr<EventLog> &eventLog) {
437 438
		long long chatRoomId;
		long long eventId = insertConferenceEvent(eventLog, &chatRoomId);
Ronan's avatar
Ronan committed
439
		unsigned int lastNotifyId = static_pointer_cast<ConferenceNotifiedEvent>(eventLog)->getNotifyId();
440 441 442

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
443 444 445
			"  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);
446

447 448 449
		return eventId;
	}

Ronan's avatar
Ronan committed
450
	long long MainDbPrivate::insertConferenceParticipantEvent (const shared_ptr<EventLog> &eventLog) {
451 452
		long long eventId = insertConferenceNotifiedEvent(eventLog);
		long long participantAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
453
			static_pointer_cast<ConferenceParticipantEvent>(eventLog)->getParticipantAddress().asString()
454 455
		);

456
		soci::session *session = dbSession.getBackendSession<soci::session>();
457
		*session << "INSERT INTO conference_participant_event (event_id, participant_address_id)"
458
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
459

460 461 462
		return eventId;
	}

Ronan's avatar
Ronan committed
463
	long long MainDbPrivate::insertConferenceParticipantDeviceEvent (const shared_ptr<EventLog> &eventLog) {
464 465
		long long eventId = insertConferenceParticipantEvent(eventLog);
		long long gruuAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
466
			static_pointer_cast<ConferenceParticipantDeviceEvent>(eventLog)->getGruuAddress().asString()
467 468 469
		);

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

473
		return eventId;
474 475
	}

Ronan's avatar
Ronan committed
476
	long long MainDbPrivate::insertConferenceSubjectEvent (const shared_ptr<EventLog> &eventLog) {
477
		long long eventId = insertConferenceNotifiedEvent(eventLog);
478 479

		soci::session *session = dbSession.getBackendSession<soci::session>();
480
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
481
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(
Ronan's avatar
Ronan committed
482
				static_pointer_cast<ConferenceSubjectEvent>(eventLog)->getSubject()
483
			);
484

485
		return eventId;
486 487
	}

488
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
489

490
	void MainDb::init () {
491
		L_D();
Ronan's avatar
Ronan committed
492
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
493

Ronan's avatar
Ronan committed
494
		*session <<
495
			"CREATE TABLE IF NOT EXISTS sip_address ("
496
			"  id" + primaryKeyStr("UNSIGNED BIGINT") + ","
Ronan's avatar
Ronan committed
497
			"  value VARCHAR(255) UNIQUE NOT NULL"
498 499
			")";

500
		*session <<
501
			"CREATE TABLE IF NOT EXISTS content_type ("
502
			"  id" + primaryKeyStr("UNSIGNED SMALLINT") + ","
Ronan's avatar
Ronan committed
503
			"  value VARCHAR(255) UNIQUE NOT NULL"
504 505
			")";

Ronan's avatar
Ronan committed
506
		*session <<
507
			"CREATE TABLE IF NOT EXISTS event ("
508
			"  id" + primaryKeyStr("UNSIGNED BIGINT") + ","
509
			"  type TINYINT UNSIGNED NOT NULL,"
510
			"  date DATE NOT NULL"
511 512
			")";

Ronan's avatar
Ronan committed
513
		*session <<
514
			"CREATE TABLE IF NOT EXISTS chat_room ("
515
			// Server (for conference) or user sip address.
516
			"  peer_sip_address_id" + primaryKeyStr("UNSIGNED BIGINT") + ","
517 518

			// Dialog creation date.
519 520 521 522
			"  creation_date DATE NOT NULL,"

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

524 525 526
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

527 528 529
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
530
			"  last_notify_id INT UNSIGNED,"
531

532
			"  FOREIGN KEY (peer_sip_address_id)"
533 534 535 536
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

537 538
		*session <<
			"CREATE TABLE IF NOT EXISTS chat_room_participant ("
539 540 541
			"  chat_room_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
			"  sip_address_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","

542 543
			"  is_admin BOOLEAN NOT NULL,"

544
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
545 546 547 548 549 550
			"  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"
551 552 553 554
			")";

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_event ("
555 556 557
			"  event_id" + primaryKeyStr("UNSIGNED BIGINT") + ","

			"  chat_room_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
558 559 560 561 562 563 564 565 566

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

567 568
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_notified_event ("
569 570
			"  event_id" + primaryKeyStr("UNSIGNED BIGINT") + ","

571 572 573 574 575 576 577
			"  notify_id INT UNSIGNED NOT NULL,"

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

578 579
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
580 581 582
			"  event_id" + primaryKeyStr("UNSIGNED BIGINT") + ","

			"  participant_address_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
583

584 585
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
586 587 588 589 590 591 592 593
			"    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 ("
594 595 596
			"  event_id" + primaryKeyStr("UNSIGNED BIGINT") + ","

			"  gruu_address_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
597

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

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_subject_event ("
608 609
			"  event_id" + primaryKeyStr("BIGINT") + ","

610
			"  subject VARCHAR(255) NOT NULL,"
611

612 613
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
614
			"    ON DELETE CASCADE"
615 616
			")";

Ronan's avatar
Ronan committed
617
		*session <<
618
			"CREATE TABLE IF NOT EXISTS conference_chat_message_event ("
619 620 621 622
			"  event_id" + primaryKeyStr("UNSIGNED BIGINT") + ","

			"  local_sip_address_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
			"  remote_sip_address_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
623 624 625 626

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

627 628
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
629
			"  is_secured BOOLEAN NOT NULL,"
630

631
			"  FOREIGN KEY (event_id)"
632
			"    REFERENCES conference_event(event_id)"
633
			"    ON DELETE CASCADE,"
634 635
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
636
			"    ON DELETE CASCADE,"
637
			"  FOREIGN KEY (remote_sip_address_id)"
638
			"    REFERENCES sip_address(id)"
639 640 641
			"    ON DELETE CASCADE"
			")";

642
		*session <<
643
			"CREATE TABLE IF NOT EXISTS chat_message_participant ("
644 645
			"  event_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
			"  sip_address_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
646 647
			"  state TINYINT UNSIGNED NOT NULL,"

Ronan's avatar
Ronan committed
648 649
			"  PRIMARY KEY (event_id, sip_address_id),"
			"  FOREIGN KEY (event_id)"
650
			"    REFERENCES conference_chat_message_event(event_id)"
651 652 653
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
654
			"    ON DELETE CASCADE"
655 656
			")";

657
		*session <<
658
			"CREATE TABLE IF NOT EXISTS chat_message_content ("
659 660 661 662
			"  id" + primaryKeyStr("UNSIGNED BIGINT") + ","

			"  event_id " + primaryKeyRefStr("UNSIGNED BIGINT") + ","
			"  content_type_id" + primaryKeyRefStr("UNSIGNED SMALLINT") + ","
663
			"  body TEXT NOT NULL,"
664

Ronan's avatar
Ronan committed
665
			"  FOREIGN KEY (event_id)"
666
			"    REFERENCES conference_chat_message_event(event_id)"
667 668 669
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
670 671
			"    ON DELETE CASCADE"
			")";
672

673
		*session <<
674
			"CREATE TABLE IF NOT EXISTS chat_message_content_app_data ("
675 676
			"  chat_message_content_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","

677 678 679
			"  key VARCHAR(255),"
			"  data BLOB,"

680 681 682
			"  PRIMARY KEY (chat_message_content_id, key),"
			"  FOREIGN KEY (chat_message_content_id)"
			"    REFERENCES chat_message_content(id)"
683 684 685
			"    ON DELETE CASCADE"
			")";

686
		*session <<
687
			"CREATE TABLE IF NOT EXISTS conference_message_crypto_data ("
688 689
			"  event_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","

690
			"  key VARCHAR(255),"
691 692
			"  data BLOB,"

Ronan's avatar
Ronan committed
693 694
			"  PRIMARY KEY (event_id, key),"
			"  FOREIGN KEY (event_id)"
695
			"    REFERENCES conference_chat_message_event(event_id)"
696
			"    ON DELETE CASCADE"
697
			")";
698 699 700 701

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
702 703
			"CREATE TRIGGER IF NOT EXISTS chat_message_participant_deleter"
			"  AFTER UPDATE OF state ON chat_message_participant FOR EACH ROW"
704 705
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
706
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
707 708
			"    SELECT state FROM chat_message_participant WHERE"
			"    NEW.event_id = chat_message_participant.event_id"
709 710 711 712 713
			"    AND state <> ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += "    LIMIT 1"
			"  )) = 0"
			"  BEGIN"
714 715
			"  DELETE FROM chat_message_participant WHERE NEW.event_id = chat_message_participant.event_id;"
			"  UPDATE conference_chat_message_event SET state = ";
716
		participantMessageDeleter += displayedId;
Ronan's avatar
Ronan committed
717
		participantMessageDeleter += " WHERE event_id = NEW.event_id;"
718 719 720
			"  END";

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
721
	}
722

Ronan's avatar
Ronan committed
723
	bool MainDb::addEvent (const shared_ptr<EventLog> &eventLog) {
724 725
		L_D();

Ronan's avatar
Ronan committed
726 727
		if (!isConnected()) {
			lWarning() << "Unable to add event. Not connected.";
728
			return false;
Ronan's avatar
Ronan committed
729 730
		}

731 732 733 734 735 736
		bool soFarSoGood = false;

		L_BEGIN_LOG_EXCEPTION

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

Ronan's avatar
Ronan committed
737
		switch (eventLog->getType()) {
738
			case EventLog::Type::None:
Ronan's avatar
Ronan committed
739
				return false;
740

741 742 743
			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
				d->insertConferenceEvent(eventLog);
744 745
				break;

746 747
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
748
				d->insertConferenceCallEvent(eventLog);
749
				break;
750

751 752
			case EventLog::Type::ConferenceChatMessage:
				d->insertConferenceChatMessageEvent(eventLog);
753 754
				break;

755 756 757 758
			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
759 760 761
				d->insertConferenceParticipantEvent(eventLog);
				break;

762 763
			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
764 765 766
				d->insertConferenceParticipantDeviceEvent(eventLog);
				break;

767
			case EventLog::Type::ConferenceSubjectChanged:
768
				d->insertConferenceSubjectEvent(eventLog);
Ronan's avatar
Ronan committed
769 770 771
				break;
		}

772 773 774 775 776 777 778
		tr.commit();

		soFarSoGood = true;

		L_END_LOG_EXCEPTION

		return soFarSoGood;
Ronan's avatar
Ronan committed
779 780
	}

Ronan's avatar
Ronan committed
781
	bool MainDb::deleteEvent (const shared_ptr<EventLog> &eventLog) {
Ronan's avatar
Ronan committed
782 783
		L_D();

Ronan's avatar
Ronan committed
784 785 786 787 788
		if (!isConnected()) {
			lWarning() << "Unable to delete event. Not connected.";
			return false;
		}

Ronan's avatar
Ronan committed
789
		long long &storageId = eventLog->getPrivate()->storageId;
Ronan's avatar
Ronan committed
790
		if (storageId < 0)
Ronan's avatar
Ronan committed
791 792 793 794 795
			return false;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
796 797
		*session << "DELETE FROM event WHERE id = :id", soci::use(storageId);
		storageId = -1;
Ronan's avatar
Ronan committed
798 799 800

		L_END_LOG_EXCEPTION

Ronan's avatar
Ronan committed
801
		return storageId == -1;
Ronan's avatar
Ronan committed
802 803
	}

804
	void MainDb::cleanEvents (FilterMask mask) {
Ronan's avatar
Ronan committed
805 806
		L_D();

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

Ronan's avatar
Ronan committed
812
		string query = "DELETE FROM event" +
813
			buildSqlEventFilter({ ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter }, mask);
Ronan's avatar
Ronan committed
814 815 816 817 818 819 820

		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
821 822
	}

823
	int MainDb::getEventsCount (FilterMask mask) const {
824
		L_D();
Ronan's avatar
Ronan committed
825

Ronan's avatar
Ronan committed
826 827 828 829 830
		if (!isConnected()) {
			lWarning() << "Unable to get events count. Not connected.";
			return 0;
		}

Ronan's avatar
Ronan committed
831
		string query = "SELECT COUNT(*) FROM event" +
832
			buildSqlEventFilter({ ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter }, mask);
Ronan's avatar
Ronan committed
833 834 835 836 837 838 839 840 841 842 843 844
		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;
	}

845 846 847 848 849 850 851 852
	list<shared_ptr<EventLog>> MainDb::getHistorySinceNotifyId (
		const string &peerAddress,
		unsigned int notifyId
	) {
		// TODO.
		return list<shared_ptr<EventLog>>();
	}

853
	int MainDb::getMessagesCount (const string &peerAddress) const {
854
		L_D();