main-db.cpp 80.4 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"

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

44
#define DB_MODULE_VERSION_EVENTS L_VERSION(1, 0, 0)
45
#define DB_MODULE_VERSION_LEGACY_HISTORY_IMPORT L_VERSION(1, 0, 0)
46

Ronan's avatar
Ronan committed
47 48
// =============================================================================

49 50 51 52 53
// See: http://soci.sourceforge.net/doc/3.2/exchange.html
// Part: Object lifetime and immutability

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

Ronan's avatar
Ronan committed
54 55
using namespace std;

Ronan's avatar
Ronan committed
56 57 58 59
LINPHONE_BEGIN_NAMESPACE

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

60
MainDb::MainDb (const shared_ptr<Core> &core) : AbstractDb(*new MainDbPrivate), CoreAccessor(core) {}
Ronan's avatar
Ronan committed
61

62 63
#ifdef SOCI_ENABLED

Ronan's avatar
Ronan committed
64
// -----------------------------------------------------------------------------
65
// Soci backend.
Ronan's avatar
Ronan committed
66 67
// -----------------------------------------------------------------------------

68 69 70 71 72
	template<typename T>
	struct EnumToSql {
		T first;
		const char *second;
	};
73

74 75 76 77 78 79 80
	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)
		);
	}

81
	static constexpr EnumToSql<MainDb::Filter> eventFilterToSql[] = {
82
		{ MainDb::ConferenceCallFilter, "3, 4" },
Ronan's avatar
Ronan committed
83
		{ MainDb::ConferenceChatMessageFilter, "5" },
84
		{ MainDb::ConferenceInfoFilter, "1, 2, 6, 7, 8, 9, 10, 11, 12" }
85
	};
86

87
	static constexpr const char *mapEventFilterToSql (MainDb::Filter filter) {
88 89 90
		return mapEnumToSql(
			eventFilterToSql, sizeof eventFilterToSql / sizeof eventFilterToSql[0], filter
		);
91 92
	}

Ronan's avatar
Ronan committed
93 94
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
95 96 97 98 99
	static string buildSqlEventFilter (
		const list<MainDb::Filter> &filters,
		MainDb::FilterMask mask,
		const string &condKeyWord = "WHERE"
	) {
100
		L_ASSERT(
101
			find_if(filters.cbegin(), filters.cend(), [](const MainDb::Filter &filter) {
102 103
				return filter == MainDb::NoFilter;
			}) == filters.cend()
104 105
		);

106
		if (mask == MainDb::NoFilter)
107 108 109 110 111 112 113 114 115 116
			return "";

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

			if (isStart) {
				isStart = false;
Ronan's avatar
Ronan committed
117
				sql += " " + condKeyWord + " type IN (";
118
			} else
Ronan's avatar
Ronan committed
119
				sql += ", ";
120 121 122
			sql += mapEventFilterToSql(filter);
		}

Ronan's avatar
Ronan committed
123 124 125
		if (!isStart)
			sql += ") ";

126 127 128
		return sql;
	}

129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144
// -----------------------------------------------------------------------------

static inline vector<char> blobToVector (soci::blob &in) {
	size_t len = in.get_len();
	if (!len)
		return vector<char>();
	vector<char> out(len);
	in.read(0, &out[0], len);
	return out;
}

static inline string blobToString (soci::blob &in) {
	vector<char> out = blobToVector(in);
	return string(out.begin(), out.end());
}

145 146 147 148
static constexpr string &blobToString (string &in) {
	return in;
}

149 150
// -----------------------------------------------------------------------------

151 152 153 154 155 156
	long long MainDbPrivate::resolveId (const soci::row &row, int col) const {
		L_Q();
		// See: http://soci.sourceforge.net/doc/master/backends/
		// `row id` is not supported by soci on Sqlite3. It's necessary to cast id to int...
		return q->getBackend() == AbstractDb::Sqlite3
			? static_cast<long long>(row.get<int>(0))
Benjamin REIS's avatar
Benjamin REIS committed
157
			: static_cast<long long>(row.get<unsigned long long>(0));
158 159
	}

160 161
// -----------------------------------------------------------------------------

162
	long long MainDbPrivate::insertSipAddress (const string &sipAddress) {
163 164 165
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

166 167
		long long id = selectSipAddressId(sipAddress);
		if (id >= 0)
168 169
			return id;

Ronan's avatar
Ronan committed
170
		lInfo() << "Insert new sip address in database: `" << sipAddress << "`.";
171 172
		*session << "INSERT INTO sip_address (value) VALUES (:sipAddress)", soci::use(sipAddress);
		return q->getLastInsertId();
173 174
	}

175
	void MainDbPrivate::insertContent (long long eventId, const Content &content) {
176
		L_Q();
177 178
		soci::session *session = dbSession.getBackendSession<soci::session>();

179 180
		const long long &contentTypeId = insertContentType(content.getContentType().asString());
		const string &body = content.getBodyAsString();
181
		*session << "INSERT INTO chat_message_content (event_id, content_type_id, body) VALUES"
182
			" (:eventId, :contentTypeId, :body)", soci::use(eventId), soci::use(contentTypeId),
183
			soci::use(body);
184

185
		const long long &chatMessageContentId = q->getLastInsertId();
Sylvain Berfini's avatar
Sylvain Berfini committed
186
		if (content.getContentType().isFile()) {
187
			const FileContent &fileContent = static_cast<const FileContent &>(content);
188 189 190
			const string &name = fileContent.getFileName();
			const size_t &size = fileContent.getFileSize();
			const string &path = fileContent.getFilePath();
Sylvain Berfini's avatar
Sylvain Berfini committed
191
			*session << "INSERT INTO chat_message_file_content (chat_message_content_id, name, size, path) VALUES "
192 193
				" (:chatMessageContentId, :name, :size, :path)",
				soci::use(chatMessageContentId), soci::use(name), soci::use(size), soci::use(path);
Sylvain Berfini's avatar
Sylvain Berfini committed
194 195
		}

196
		for (const auto &appData : content.getAppDataMap())
Ronan's avatar
Ronan committed
197
			*session << "INSERT INTO chat_message_content_app_data (chat_message_content_id, name, data) VALUES"
198 199
				" (:chatMessageContentId, :name, :data)",
				soci::use(chatMessageContentId), soci::use(appData.first), soci::use(appData.second);
200 201
	}

202
	long long MainDbPrivate::insertContentType (const string &contentType) {
203 204 205
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

206
		long long id;
207 208
		*session << "SELECT id FROM content_type WHERE value = :contentType", soci::use(contentType), soci::into(id);
		if (session->got_data())
209 210
			return id;

Ronan's avatar
Ronan committed
211
		lInfo() << "Insert new content type in database: `" << contentType << "`.";
212 213
		*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
		return q->getLastInsertId();
214 215
	}

216
	long long MainDbPrivate::insertOrUpdateBasicChatRoom (
217 218
		long long peerSipAddressId,
		long long localSipAddressId,
219
		const tm &creationTime
220 221 222
	) {
		L_Q();

223 224
		soci::session *session = dbSession.getBackendSession<soci::session>();

225
		long long id = selectChatRoomId(peerSipAddressId, localSipAddressId);
226 227 228
		if (id >= 0) {
			*session << "UPDATE chat_room SET last_update_time = :lastUpdateTime WHERE id = :id",
				soci::use(creationTime), soci::use(id);
229
			return id;
230
		}
231

232
		static const int capabilities = ChatRoom::CapabilitiesMask(ChatRoom::Capabilities::Basic);
233 234 235 236 237 238 239 240 241
		lInfo() << "Insert new chat room in database: (peer=" << peerSipAddressId <<
			", local=" << localSipAddressId << ", capabilities=" << capabilities << ").";
		*session << "INSERT INTO chat_room ("
			"  peer_sip_address_id, local_sip_address_id, creation_time, last_update_time, capabilities"
			") VALUES (:peerSipAddressId, :localSipAddressId, :creationTime, :lastUpdateTime, :capabilities)",
			soci::use(peerSipAddressId), soci::use(localSipAddressId), soci::use(creationTime), soci::use(creationTime),
			soci::use(capabilities);

		return q->getLastInsertId();
242 243
	}

244
	long long MainDbPrivate::insertChatRoom (const shared_ptr<AbstractChatRoom> &chatRoom) {
245 246 247 248 249
		L_Q();

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

		const ChatRoomId &chatRoomId = chatRoom->getChatRoomId();
250 251
		const long long &peerSipAddressId = insertSipAddress(chatRoomId.getPeerAddress().asString());
		const long long &localSipAddressId = insertSipAddress(chatRoomId.getLocalAddress().asString());
252 253 254

		long long id = selectChatRoomId(peerSipAddressId, localSipAddressId);
		if (id >= 0) {
255
			lError() << "Unable to insert chat room (it already exists): (peer=" << peerSipAddressId <<
256 257 258 259 260 261 262
				", local=" << localSipAddressId << ").";
			return id;
		}

		lInfo() << "Insert new chat room in database: (peer=" << peerSipAddressId <<
			", local=" << localSipAddressId << ").";

263
		const tm &creationTime = Utils::getTimeTAsTm(chatRoom->getCreationTime());
264
		const int &capabilities = chatRoom->getCapabilities();
265
		const string &subject = chatRoom->getSubject();
266
		const int &flags = chatRoom->hasBeenLeft();
267
		*session << "INSERT INTO chat_room ("
268
			"  peer_sip_address_id, local_sip_address_id, creation_time, last_update_time, capabilities, subject, flags"
269
			") VALUES (:peerSipAddressId, :localSipAddressId, :creationTime, :lastUpdateTime, :capabilities, :subject, :flags)",
270
			soci::use(peerSipAddressId), soci::use(localSipAddressId), soci::use(creationTime), soci::use(creationTime),
271
			soci::use(capabilities), soci::use(subject), soci::use(flags);
272 273

		id = q->getLastInsertId();
274 275 276
		if (!chatRoom->canHandleParticipants())
			return id;

277 278 279
		// Do not add 'me' when creating a server-group-chat-room.
		if (chatRoomId.getLocalAddress() != chatRoomId.getPeerAddress()) {
			shared_ptr<Participant> me = chatRoom->getMe();
280 281 282 283 284 285 286
			long long meId = insertChatRoomParticipant(
				id,
				insertSipAddress(me->getAddress().asString()),
				me->isAdmin()
			);
			for (const auto &device : me->getPrivate()->getDevices())
				insertChatRoomParticipantDevice(meId, insertSipAddress(device->getAddress().asString()));
287 288
		}

289 290 291 292 293 294 295 296 297
		for (const auto &participant : chatRoom->getParticipants()) {
			long long participantId = insertChatRoomParticipant(
				id,
				insertSipAddress(participant->getAddress().asString()),
				participant->isAdmin()
			);
			for (const auto &device : participant->getPrivate()->getDevices())
				insertChatRoomParticipantDevice(participantId, insertSipAddress(device->getAddress().asString()));
		}
298 299

		return id;
300 301
	}

302 303 304 305 306
	long long MainDbPrivate::insertChatRoomParticipant (
		long long chatRoomId,
		long long participantSipAddressId,
		bool isAdmin
	) {
307
		L_Q();
308

309
		soci::session *session = dbSession.getBackendSession<soci::session>();
310
		long long id = selectChatRoomParticipantId(chatRoomId, participantSipAddressId);
311 312
		if (id >= 0) {
			// See: https://stackoverflow.com/a/15299655 (cast to reference)
313
			*session << "UPDATE chat_room_participant SET is_admin = :isAdmin WHERE id = :id",
314 315
				soci::use(static_cast<const int &>(isAdmin)), soci::use(id);
			return id;
Ronan's avatar
Ronan committed
316
		}
317

318 319
		lInfo() << "Insert new chat room participant in database: `" << participantSipAddressId <<
			"` (isAdmin=" << isAdmin << ").";
320
		*session << "INSERT INTO chat_room_participant (chat_room_id, participant_sip_address_id, is_admin)"
321 322
			" VALUES (:chatRoomId, :participantSipAddressId, :isAdmin)",
			soci::use(chatRoomId), soci::use(participantSipAddressId), soci::use(static_cast<const int &>(isAdmin));
323 324 325 326

		return q->getLastInsertId();
	}

327 328 329 330
	void MainDbPrivate::insertChatRoomParticipantDevice (
		long long participantId,
		long long participantDeviceSipAddressId
	) {
331 332 333
		soci::session *session = dbSession.getBackendSession<soci::session>();
		long long count;
		*session << "SELECT COUNT(*) FROM chat_room_participant_device"
334 335 336
			" WHERE chat_room_participant_id = :participantId"
			" AND participant_device_sip_address_id = :participantDeviceSipAddressId",
			soci::into(count), soci::use(participantId), soci::use(participantDeviceSipAddressId);
337 338 339
		if (count)
			return;

340
		lInfo() << "Insert new chat room participant device in database: `" << participantDeviceSipAddressId << "`.";
341
		*session << "INSERT INTO chat_room_participant_device (chat_room_participant_id, participant_device_sip_address_id)"
342 343
			" VALUES (:participantId, :participantDeviceSipAddressId)",
			soci::use(participantId), soci::use(participantDeviceSipAddressId);
344 345
	}

346
	void MainDbPrivate::insertChatMessageParticipant (long long eventId, long long sipAddressId, int state) {
347 348
		// TODO: Deal with read messages.
		// Remove if displayed? Think a good alorithm for mark as read.
349 350
		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::statement statement = (
351
			session->prepare << "UPDATE chat_message_participant SET state = :state"
352
				" WHERE event_id = :eventId AND participant_sip_address_id = :sipAddressId",
Ronan's avatar
Ronan committed
353
				soci::use(state), soci::use(eventId), soci::use(sipAddressId)
354
		);
355
		statement.execute();
356
		if (statement.get_affected_rows() == 0 && state != static_cast<int>(ChatMessage::State::Displayed))
357
			*session << "INSERT INTO chat_message_participant (event_id, participant_sip_address_id, state)"
358
				" VALUES (:eventId, :sipAddressId, :state)",
Ronan's avatar
Ronan committed
359
				soci::use(eventId), soci::use(sipAddressId), soci::use(state);
360 361
	}

362 363
// -----------------------------------------------------------------------------

364
	long long MainDbPrivate::selectSipAddressId (const string &sipAddress) const {
365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393
		soci::session *session = dbSession.getBackendSession<soci::session>();

		long long id;
		*session << "SELECT id FROM sip_address WHERE value = :sipAddress", soci::use(sipAddress), soci::into(id);
		return session->got_data() ? id : -1;
	}

	long long MainDbPrivate::selectChatRoomId (long long peerSipAddressId, long long localSipAddressId) const {
		soci::session *session = dbSession.getBackendSession<soci::session>();

		long long id;
		*session << "SELECT id FROM chat_room"
			"  WHERE peer_sip_address_id = :peerSipAddressId AND local_sip_address_id = :localSipAddressId",
			soci::use(peerSipAddressId), soci::use(localSipAddressId), soci::into(id);
		return session->got_data() ? id : -1;
	}

	long long MainDbPrivate::selectChatRoomId (const ChatRoomId &chatRoomId) const {
		long long peerSipAddressId = selectSipAddressId(chatRoomId.getPeerAddress().asString());
		if (peerSipAddressId < 0)
			return -1;

		long long localSipAddressId = selectSipAddressId(chatRoomId.getLocalAddress().asString());
		if (localSipAddressId < 0)
			return -1;

		return selectChatRoomId(peerSipAddressId, localSipAddressId);
	}

394
	long long MainDbPrivate::selectChatRoomParticipantId (long long chatRoomId, long long participantSipAddressId) const {
395 396 397
		long long id;
		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "SELECT id from chat_room_participant"
398 399
			" WHERE chat_room_id = :chatRoomId AND participant_sip_address_id = :participantSipAddressId",
			soci::into(id), soci::use(chatRoomId), soci::use(participantSipAddressId);
400 401 402
		return session->got_data() ? id : -1;
	}

403 404
// -----------------------------------------------------------------------------

405 406 407 408 409
	void MainDbPrivate::deleteContents (long long messageEventId) {
		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "DELETE FROM chat_message_content WHERE event_id = :messageEventId", soci::use(messageEventId);
	}

410 411 412 413 414 415 416
	void MainDbPrivate::deleteChatRoomParticipant (long long chatRoomId, long long participantSipAddressId) {
		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "DELETE FROM chat_room_participant"
			"  WHERE chat_room_id = :chatRoomId AND participant_sip_address_id = :participantSipAddressId",
			soci::use(chatRoomId), soci::use(participantSipAddressId);
	}

417 418 419 420 421 422 423 424 425 426 427
	void MainDbPrivate::deleteChatRoomParticipantDevice (
		long long participantId,
		long long participantDeviceSipAddressId
	) {
		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "DELETE FROM chat_room_participant_device"
			"  WHERE chat_room_participant_id = :participantId"
			"  AND participant_device_sip_address_id = :participantDeviceSipAddressId",
			soci::use(participantId), soci::use(participantDeviceSipAddressId);
	}

Ronan's avatar
Ronan committed
428 429
// -----------------------------------------------------------------------------

430
	shared_ptr<EventLog> MainDbPrivate::selectGenericConferenceEvent (
431
		long long eventId,
432
		EventLog::Type type,
433
		time_t creationTime,
434
		const ChatRoomId &chatRoomId
435
	) const {
Ronan's avatar
Ronan committed
436 437
		shared_ptr<EventLog> eventLog;

Ronan's avatar
Ronan committed
438 439 440 441 442
		switch (type) {
			case EventLog::Type::None:
				return nullptr;

			case EventLog::Type::ConferenceCreated:
443
			case EventLog::Type::ConferenceTerminated:
Ronan's avatar
Ronan committed
444 445
				eventLog = selectConferenceEvent(eventId, type, creationTime, chatRoomId);
				break;
Ronan's avatar
Ronan committed
446

447 448
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
Ronan's avatar
Ronan committed
449 450
				eventLog = selectConferenceCallEvent(eventId, type, creationTime, chatRoomId);
				break;
Ronan's avatar
Ronan committed
451 452

			case EventLog::Type::ConferenceChatMessage:
Ronan's avatar
Ronan committed
453 454
				eventLog = selectConferenceChatMessageEvent(eventId, type, creationTime, chatRoomId);
				break;
Ronan's avatar
Ronan committed
455 456 457 458 459

			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
Ronan's avatar
Ronan committed
460 461
				eventLog = selectConferenceParticipantEvent(eventId, type, creationTime, chatRoomId);
				break;
Ronan's avatar
Ronan committed
462 463 464

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
Ronan's avatar
Ronan committed
465 466
				eventLog = selectConferenceParticipantDeviceEvent(eventId, type, creationTime, chatRoomId);
				break;
Ronan's avatar
Ronan committed
467 468

			case EventLog::Type::ConferenceSubjectChanged:
Ronan's avatar
Ronan committed
469 470 471 472
				eventLog = selectConferenceSubjectEvent(eventId, type, creationTime, chatRoomId);
				break;
		}

473 474
		if (eventLog)
			cache(eventLog, eventId);
Ronan's avatar
Ronan committed
475

476
		return eventLog;
Ronan's avatar
Ronan committed
477 478
	}

479
	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
480
		long long,
481
		EventLog::Type type,
482
		time_t creationTime,
483
		const ChatRoomId &chatRoomId
484
	) const {
485 486
		return make_shared<ConferenceEvent>(
			type,
487
			creationTime,
488
			chatRoomId
489
		);
Ronan's avatar
Ronan committed
490 491
	}

492
	shared_ptr<EventLog> MainDbPrivate::selectConferenceCallEvent (
493
		long long eventId,
494
		EventLog::Type type,
495
		time_t creationTime,
496
		const ChatRoomId &chatRoomId
497
	) const {
Ronan's avatar
Ronan committed
498 499 500 501
		// TODO.
		return nullptr;
	}

502 503 504 505 506 507 508 509 510 511 512 513
	template<typename T>
	static void fetchContentAppData (soci::session *session, Content &content, long long contentId, T &data) {
		static const string query = "SELECT name, data FROM chat_message_content_app_data"
			" WHERE chat_message_content_id = :contentId";

		string name;
		soci::statement statement = (session->prepare << query, soci::use(contentId), soci::into(name), soci::into(data));
		statement.execute();
		while (statement.fetch())
			content.setAppData(name, blobToString(data));
	}

514
	shared_ptr<EventLog> MainDbPrivate::selectConferenceChatMessageEvent (
515
		long long eventId,
516
		EventLog::Type type,
517
		time_t creationTime,
518
		const ChatRoomId &chatRoomId
519
	) const {
Ronan's avatar
Ronan committed
520 521 522
		L_Q();

		shared_ptr<Core> core = q->getCore();
523
		shared_ptr<AbstractChatRoom> chatRoom = core->findChatRoom(chatRoomId);
524 525 526 527
		if (!chatRoom) {
			lError() << "Unable to find chat room storage id of (peer=" +
				chatRoomId.getPeerAddress().asString() +
				", local=" + chatRoomId.getLocalAddress().asString() + "`).";
Ronan's avatar
Ronan committed
528
			return nullptr;
529
		}
Ronan's avatar
Ronan committed
530

531 532
		bool hasFileTransferContent = false;

533
		// 1 - Fetch chat message.
534 535 536
		shared_ptr<ChatMessage> chatMessage = getChatMessageFromCache(eventId);
		if (chatMessage)
			goto end;
537
		{
538 539
			string fromSipAddress;
			string toSipAddress;
540 541 542

			tm messageTime;

543
			string imdnMessageId;
544

545 546 547 548 549
			int state;
			int direction;
			int isSecured;

			soci::session *session = dbSession.getBackendSession<soci::session>();
550
			*session << "SELECT from_sip_address.value, to_sip_address.value, time, imdn_message_id, state, direction, is_secured"
551 552 553 554 555
				" FROM event, conference_chat_message_event, sip_address AS from_sip_address, sip_address AS to_sip_address"
				" WHERE event_id = :eventId"
				" AND event_id = event.id"
				" AND from_sip_address_id = from_sip_address.id"
				" AND to_sip_address_id = to_sip_address.id", soci::into(fromSipAddress), soci::into(toSipAddress),
556 557
				soci::into(messageTime), soci::into(imdnMessageId), soci::into(state), soci::into(direction),
				soci::into(isSecured), soci::use(eventId);
558

559 560 561 562
			chatMessage = shared_ptr<ChatMessage>(new ChatMessage(
				chatRoom,
				static_cast<ChatMessage::Direction>(direction)
			));
563
			chatMessage->setIsSecured(static_cast<bool>(isSecured));
564

565 566
			ChatMessagePrivate *dChatMessage = chatMessage->getPrivate();
			dChatMessage->setState(static_cast<ChatMessage::State>(state), true);
567

568 569 570 571
			dChatMessage->forceFromAddress(IdentityAddress(fromSipAddress));
			dChatMessage->forceToAddress(IdentityAddress(toSipAddress));

			dChatMessage->setTime(Utils::getTmAsTimeT(messageTime));
572
			dChatMessage->setImdnMessageId(imdnMessageId);
573
		}
Ronan's avatar
Ronan committed
574

575 576 577
		// 2 - Fetch contents.
		{
			soci::session *session = dbSession.getBackendSession<soci::session>();
578
			static const string query = "SELECT chat_message_content.id, content_type.id, content_type.value, body"
579 580
				" FROM chat_message_content, content_type"
				" WHERE event_id = :eventId AND content_type_id = content_type.id";
581 582
			soci::rowset<soci::row> rows = (session->prepare << query, soci::use(eventId));
			for (const auto &row : rows) {
583
				ContentType contentType(row.get<string>(2));
584
				const long long &contentId = resolveId(row, 0);
585 586
				Content *content;

587 588
				if (contentType == ContentType::FileTransfer) {
					hasFileTransferContent = true;
589
					content = new FileTransferContent();
590
				}
591
				else if (contentType.isFile()) {
592
					// 2.1 - Fetch contents' file informations.
593 594 595 596 597
					string name;
					int size;
					string path;

					*session << "SELECT name, size, path FROM chat_message_file_content"
598
						" WHERE chat_message_content_id = :contentId",
Ronan's avatar
Ronan committed
599
						soci::into(name), soci::into(size), soci::into(path), soci::use(contentId);
600 601 602 603 604 605 606 607

					FileContent *fileContent = new FileContent();
					fileContent->setFileName(name);
					fileContent->setFileSize(static_cast<size_t>(size));
					fileContent->setFilePath(path);

					content = fileContent;
				} else
Ronan's avatar
Ronan committed
608
					content = new Content();
609

610 611 612
				content->setContentType(contentType);
				content->setBody(row.get<string>(3));

613
				// 2.2 - Fetch contents' app data.
614 615 616 617 618 619 620
				if (q->getBackend() == MainDb::Backend::Sqlite3) {
					soci::blob data(*session);
					fetchContentAppData(session, *content, contentId, data);
				} else {
					string data;
					fetchContentAppData(session, *content, contentId, data);
				}
621
				chatMessage->addContent(*content);
622
			}
Ronan's avatar
Ronan committed
623
		}
624

625 626 627
		// 3 - Load external body url from body into FileTransferContent if needed.
		if (hasFileTransferContent)
			chatMessage->getPrivate()->loadFileTransferUrlFromBodyToContent();
628

629 630
		cache(chatMessage, eventId);

631
	end:
632
		return make_shared<ConferenceChatMessageEvent>(
633
			creationTime,
634 635
			chatMessage
		);
Ronan's avatar
Ronan committed
636 637
	}

638
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
639
		long long eventId,
640
		EventLog::Type type,
641
		time_t creationTime,
642
		const ChatRoomId &chatRoomId
643
	) const {
644 645 646 647
		unsigned int notifyId;
		string participantAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
648 649
		*session << "SELECT notify_id, participant_address.value"
			"  FROM conference_notified_event, conference_participant_event, sip_address as participant_address"
650
			"  WHERE conference_participant_event.event_id = :eventId"
Ronan's avatar
Ronan committed
651
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
652
			"    AND participant_address.id = participant_sip_address_id",
653 654 655 656
			soci::into(notifyId), soci::into(participantAddress), soci::use(eventId);

		return make_shared<ConferenceParticipantEvent>(
			type,
657
			creationTime,
658
			chatRoomId,
659
			notifyId,
660
			IdentityAddress(participantAddress)
661
		);
Ronan's avatar
Ronan committed
662 663
	}

664
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
665
		long long eventId,
666
		EventLog::Type type,
667
		time_t creationTime,
668
		const ChatRoomId &chatRoomId
669
	) const {
670 671
		unsigned int notifyId;
		string participantAddress;
672
		string deviceAddress;
673 674

		soci::session *session = dbSession.getBackendSession<soci::session>();
675
		*session << "SELECT notify_id, participant_address.value, device_address.value"
676
			"  FROM conference_notified_event, conference_participant_event, conference_participant_device_event,"
677
			"    sip_address AS participant_address, sip_address AS device_address"
678 679
			"  WHERE conference_participant_device_event.event_id = :eventId"
			"    AND conference_participant_event.event_id = conference_participant_device_event.event_id"
680
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
681 682
			"    AND participant_address.id = participant_sip_address_id"
			"    AND device_address.id = device_sip_address_id",
683
			soci::into(notifyId), soci::into(participantAddress), soci::into(deviceAddress), soci::use(eventId);
684 685 686

		return make_shared<ConferenceParticipantDeviceEvent>(
			type,
687
			creationTime,
688
			chatRoomId,
689
			notifyId,
690 691
			IdentityAddress(participantAddress),
			IdentityAddress(deviceAddress)
692
		);
Ronan's avatar
Ronan committed
693 694
	}

695
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
696
		long long eventId,
697
		EventLog::Type type,
698
		time_t creationTime,
699
		const ChatRoomId &chatRoomId
700 701 702 703
	) const {
		unsigned int notifyId;
		string subject;

704 705
		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "SELECT notify_id, subject"
706 707 708
			" 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",
709
			soci::into(notifyId), soci::into(subject), soci::use(eventId);
710 711

		return make_shared<ConferenceSubjectEvent>(
712
			creationTime,
713
			chatRoomId,
714 715 716
			notifyId,
			subject
		);
Ronan's avatar
Ronan committed
717 718
	}

719 720
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
721
	long long MainDbPrivate::insertEvent (const shared_ptr<EventLog> &eventLog) {
722 723 724
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

725 726
		const int &type = static_cast<int>(eventLog->getType());
		const tm &creationTime = Utils::getTimeTAsTm(eventLog->getCreationTime());
727
		*session << "INSERT INTO event (type, creation_time) VALUES (:type, :creationTime)",
728 729 730
			soci::use(type),
			soci::use(creationTime);

731
		return q->getLastInsertId();
732 733
	}

Ronan's avatar
Ronan committed
734
	long long MainDbPrivate::insertConferenceEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
735 736 737
		shared_ptr<ConferenceEvent> conferenceEvent = static_pointer_cast<ConferenceEvent>(eventLog);

		long long eventId = -1;
738
		const long long &curChatRoomId = selectChatRoomId(conferenceEvent->getChatRoomId());
739 740 741 742 743 744 745 746 747
		if (curChatRoomId < 0) {
			// A conference event can be inserted in database only if chat room exists.
			// Otherwise it's an error.
			const ChatRoomId &chatRoomId = conferenceEvent->getChatRoomId();
			lError() << "Unable to find chat room storage id of (peer=" +
				chatRoomId.getPeerAddress().asString() +
				", local=" + chatRoomId.getLocalAddress().asString() + "`).";
		} else {
			eventId = insertEvent(eventLog);
748

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

753
			const tm &lastUpdateTime = Utils::getTimeTAsTm(eventLog->getCreationTime());
754
			*session << "UPDATE chat_room SET last_update_time = :lastUpdateTime"
755
				" WHERE id = :chatRoomId", soci::use(lastUpdateTime),
756
				soci::use(curChatRoomId);
757 758

			if (eventLog->getType() == EventLog::Type::ConferenceTerminated)
759
				*session << "UPDATE chat_room SET flags = 1 WHERE id = :chatRoomId", soci::use(curChatRoomId);
760
		}
761 762 763 764 765 766 767

		if (chatRoomId)
			*chatRoomId = curChatRoomId;

		return eventId;
	}

Ronan's avatar
Ronan committed
768
	long long MainDbPrivate::insertConferenceCallEvent (const shared_ptr<EventLog> &eventLog) {
769 770 771 772
		// TODO.
		return 0;
	}

Ronan's avatar
Ronan committed
773 774
	long long MainDbPrivate::insertConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
775
		shared_ptr<AbstractChatRoom> chatRoom = chatMessage->getChatRoom();
776 777 778 779 780
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

781
		const long long &eventId = insertConferenceEvent(eventLog);
782 783
		if (eventId < 0)
			return -1;
784

785
		soci::session *session = dbSession.getBackendSession<soci::session>();
786 787 788 789 790 791 792 793 794

		const long long &fromSipAddressId = insertSipAddress(chatMessage->getFromAddress().asString());
		const long long &toSipAddressId = insertSipAddress(chatMessage->getToAddress().asString());
		const tm &messageTime = Utils::getTimeTAsTm(chatMessage->getTime());
		const int &state = static_cast<int>(chatMessage->getState());
		const int &direction = static_cast<int>(chatMessage->getDirection());
		const string &imdnMessageId = chatMessage->getImdnMessageId();
		const int &isSecured = chatMessage->isSecured() ? 1 : 0;

795
		*session << "INSERT INTO conference_chat_message_event ("
796
			"  event_id, from_sip_address_id, to_sip_address_id,"
797
			"  time, state, direction, imdn_message_id, is_secured"
798 799
			") VALUES ("
			"  :eventId, :localSipaddressId, :remoteSipaddressId,"
800
			"  :time, :state, :direction, :imdnMessageId, :isSecured"
801
			")", soci::use(eventId), soci::use(fromSipAddressId), soci::use(toSipAddressId),