main-db.cpp 57.6 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/client-group-chat-room.h"
31
#include "chat/chat-room/chat-room-p.h"
32
#include "conference/participant.h"
33
#include "content/content-type.h"
Ronan's avatar
Ronan committed
34
#include "content/content.h"
Ronan's avatar
Ronan committed
35
#include "core/core-p.h"
36
#include "db/session/db-session-provider.h"
Ronan's avatar
Ronan committed
37
#include "event-log/event-log-p.h"
38
#include "event-log/events.h"
Ronan's avatar
Ronan committed
39
#include "logger/logger.h"
40
#include "main-db-event-key-p.h"
Ronan's avatar
Ronan committed
41
#include "main-db-p.h"
Ronan's avatar
Ronan committed
42 43 44

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

Ronan's avatar
Ronan committed
45 46
using namespace std;

Ronan's avatar
Ronan committed
47 48 49 50
LINPHONE_BEGIN_NAMESPACE

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

51
MainDb::MainDb (const shared_ptr<Core> &core) : AbstractDb(*new MainDbPrivate), CoreAccessor(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[] = {
73
		{ MainDb::ConferenceCallFilter, "3, 4" },
Ronan's avatar
Ronan committed
74
		{ MainDb::ConferenceChatMessageFilter, "5" },
75
		{ MainDb::ConferenceInfoFilter, "1, 2, 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
			find_if(filters.cbegin(), filters.cend(), [](const MainDb::Filter &filter) {
93 94
				return filter == MainDb::NoFilter;
			}) == filters.cend()
95 96
		);

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 127
		long long id = selectSipAddressId(sipAddress);
		if (id >= 0)
128 129
			return id;

Ronan's avatar
Ronan committed
130
		lInfo() << "Insert new sip address in database: `" << sipAddress << "`.";
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
		L_Q();
137 138
		soci::session *session = dbSession.getBackendSession<soci::session>();

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

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

151 152 153 154 155 156 157 158 159 160 161 162
	void MainDbPrivate::updateContent (long long eventId, long long messageContentId, const Content &content) {
		soci::session *session = dbSession.getBackendSession<soci::session>();

		long long contentTypeId = insertContentType(content.getContentType().asString());
		*session << "UPDATE chat_message_content SET content_type_id=:contentTypeId, body=:body WHERE event_id=:eventId",
			soci::use(contentTypeId), soci::use(content.getBodyAsString()), soci::use(eventId);

		for (const auto &appData : content.getAppDataMap())
			*session << "UPDATE chat_message_content_app_data SET name=:name, data=:data WHERE chat_message_content_id=:messageContentId",
				soci::use(appData.first), soci::use(appData.second), soci::use(messageContentId);
	}

163
	long long MainDbPrivate::insertContentType (const string &contentType) {
164 165 166
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

167
		long long id;
168 169
		*session << "SELECT id FROM content_type WHERE value = :contentType", soci::use(contentType), soci::into(id);
		if (session->got_data())
170 171
			return id;

Ronan's avatar
Ronan committed
172
		lInfo() << "Insert new content type in database: `" << contentType << "`.";
173 174
		*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
		return q->getLastInsertId();
175 176
	}

177 178 179 180
	long long MainDbPrivate::insertChatRoom (
		long long peerSipAddressId,
		long long localSipAddressId,
		int capabilities,
181 182
		const tm &date,
		const string &subject
183 184 185
	) {
		L_Q();

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

188 189 190 191 192 193
		long long id = selectChatRoomId(peerSipAddressId, localSipAddressId);
		if (id < 0) {
			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_date, last_update_date, capabilities, subject"
Benjamin REIS's avatar
Benjamin REIS committed
194
				") VALUES (:peerSipAddressId, :localSipAddressId, :creationDate, :lastUpdateDate, :capabilities, :subject)",
195
				soci::use(peerSipAddressId), soci::use(localSipAddressId), soci::use(date), soci::use(date),
196
				soci::use(capabilities), soci::use(subject);
197 198

			return q->getLastInsertId();
Ronan's avatar
Ronan committed
199
		}
200

201 202 203
		return id;
	}

204 205 206 207 208 209
	long long MainDbPrivate::insertChatRoom (
		const ChatRoomId &chatRoomId,
		int capabilities,
		const tm &date,
		const string &subject
	) {
210 211 212 213
		return insertChatRoom (
			insertSipAddress(chatRoomId.getPeerAddress().asString()),
			insertSipAddress(chatRoomId.getLocalAddress().asString()),
			capabilities,
214 215
			date,
			subject
216
		);
217 218
	}

219
	void MainDbPrivate::insertChatRoomParticipant (long long chatRoomId, long long sipAddressId, bool isAdmin) {
220
		soci::session *session = dbSession.getBackendSession<soci::session>();
221 222
		soci::statement statement = (
			session->prepare << "UPDATE chat_room_participant SET is_admin = :isAdmin"
223
				"  WHERE chat_room_id = :chatRoomId AND participant_sip_address_id = :sipAddressId",
224 225 226
				soci::use(static_cast<int>(isAdmin)), soci::use(chatRoomId), soci::use(sipAddressId)
		);
		statement.execute(true);
Ronan's avatar
Ronan committed
227 228
		if (statement.get_affected_rows() == 0) {
			lInfo() << "Insert new chat room participant in database: `" << sipAddressId << "` (isAdmin=" << isAdmin << ").";
229
			*session << "INSERT INTO chat_room_participant (chat_room_id, participant_sip_address_id, is_admin)"
230 231
				"  VALUES (:chatRoomId, :sipAddressId, :isAdmin)",
				soci::use(chatRoomId), soci::use(sipAddressId), soci::use(static_cast<int>(isAdmin));
Ronan's avatar
Ronan committed
232
		}
233 234
	}

235
	void MainDbPrivate::insertChatMessageParticipant (long long eventId, long long sipAddressId, int state) {
236 237
		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::statement statement = (
238
			session->prepare << "UPDATE chat_message_participant SET state = :state"
239
				"  WHERE event_id = :eventId AND participant_sip_address_id = :sipAddressId",
Ronan's avatar
Ronan committed
240
				soci::use(state), soci::use(eventId), soci::use(sipAddressId)
241 242
		);
		statement.execute(true);
243
		if (statement.get_affected_rows() == 0 && state != static_cast<int>(ChatMessage::State::Displayed))
244
			*session << "INSERT INTO chat_message_participant (event_id, participant_sip_address_id, state)"
Ronan's avatar
Ronan committed
245 246
				"  VALUES (:eventId, :sipAddressId, :state)",
				soci::use(eventId), soci::use(sipAddressId), soci::use(state);
247 248
	}

249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278
	long long MainDbPrivate::selectSipAddressId (const std::string &sipAddress) const {
		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);
	}

Ronan's avatar
Ronan committed
279 280
// -----------------------------------------------------------------------------

281
	shared_ptr<EventLog> MainDbPrivate::selectGenericConferenceEvent (
282
		long long eventId,
283 284
		EventLog::Type type,
		time_t date,
285
		const ChatRoomId &chatRoomId
286
	) const {
Ronan's avatar
Ronan committed
287 288 289 290 291 292
		switch (type) {
			case EventLog::Type::None:
				return nullptr;

			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
293
				return selectConferenceEvent(eventId, type, date, chatRoomId);
Ronan's avatar
Ronan committed
294

295 296
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
297
				return selectConferenceCallEvent(eventId, type, date, chatRoomId);
Ronan's avatar
Ronan committed
298 299

			case EventLog::Type::ConferenceChatMessage:
300
				return selectConferenceChatMessageEvent(eventId, type, date, chatRoomId);
Ronan's avatar
Ronan committed
301 302 303 304 305

			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
306
				return selectConferenceParticipantEvent(eventId, type, date, chatRoomId);
Ronan's avatar
Ronan committed
307 308 309

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
310
				return selectConferenceParticipantDeviceEvent(eventId, type, date, chatRoomId);
Ronan's avatar
Ronan committed
311 312

			case EventLog::Type::ConferenceSubjectChanged:
313
				return selectConferenceSubjectEvent(eventId, type, date, chatRoomId);
Ronan's avatar
Ronan committed
314 315 316 317 318
		}

		return nullptr;
	}

319
	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
320
		long long,
321 322
		EventLog::Type type,
		time_t date,
323
		const ChatRoomId &chatRoomId
324
	) const {
325 326 327
		return make_shared<ConferenceEvent>(
			type,
			date,
328
			chatRoomId
329
		);
Ronan's avatar
Ronan committed
330 331
	}

332
	shared_ptr<EventLog> MainDbPrivate::selectConferenceCallEvent (
333
		long long eventId,
334 335
		EventLog::Type type,
		time_t date,
336
		const ChatRoomId &chatRoomId
337
	) const {
Ronan's avatar
Ronan committed
338 339 340 341
		// TODO.
		return nullptr;
	}

342
	shared_ptr<EventLog> MainDbPrivate::selectConferenceChatMessageEvent (
343
		long long eventId,
344 345
		EventLog::Type type,
		time_t date,
346
		const ChatRoomId &chatRoomId
347
	) const {
Ronan's avatar
Ronan committed
348 349 350 351
		L_Q();

		shared_ptr<Core> core = q->getCore();

352
		shared_ptr<ChatRoom> chatRoom = core->findChatRoom(chatRoomId);
Ronan's avatar
Ronan committed
353 354 355 356 357
		if (!chatRoom)
			return nullptr;

		// TODO: Use cache, do not fetch the same message twice.

358
		// 1 - Fetch chat message.
359
		shared_ptr<ChatMessage> chatMessage;
360
		{
361 362
			string fromSipAddress;
			string toSipAddress;
363 364 365 366 367 368
			string imdnMessageId;
			int state;
			int direction;
			int isSecured;

			soci::session *session = dbSession.getBackendSession<soci::session>();
369 370 371
			*session << "SELECT from_sip_address.value, to_sip_address.value, imdn_message_id, state, direction, is_secured"
				"  FROM event, conference_chat_message_event, sip_address AS from_sip_address,"
				"  sip_address AS to_sip_address"
372 373
				"  WHERE event_id = :eventId"
				"  AND event_id = event.id"
374 375
				"  AND from_sip_address_id = from_sip_address.id"
				"  AND to_sip_address_id = to_sip_address.id", soci::into(fromSipAddress), soci::into(toSipAddress),
376 377
				soci::into(imdnMessageId), soci::into(state), soci::into(direction), soci::into(isSecured), soci::use(eventId);

378 379 380 381
			chatMessage = shared_ptr<ChatMessage>(new ChatMessage(
				chatRoom,
				static_cast<ChatMessage::Direction>(direction)
			));
382 383 384
			chatMessage->getPrivate()->setState(static_cast<ChatMessage::State>(state), true);
			chatMessage->setIsSecured(static_cast<bool>(isSecured));

385 386
			chatMessage->getPrivate()->forceFromAddress(IdentityAddress(fromSipAddress));
			chatMessage->getPrivate()->forceToAddress(IdentityAddress(toSipAddress));
387
		}
Ronan's avatar
Ronan committed
388

389 390 391
		// 2 - Fetch contents.
		{
			soci::session *session = dbSession.getBackendSession<soci::session>();
392
			const string query = "SELECT content_type.id, content_type.value, body FROM chat_message_content, content_type"
393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410
				"  WHERE event_id = :eventId AND content_type_id = content_type.id";
			soci::rowset<soci::row> rows = (session->prepare << query, soci::use(eventId));
			for (const auto &row : rows) {
				ContentType contentType(row.get<string>(1));
				Content *content;

				if (contentType == ContentType::FileTransfer)
					content = new FileTransferContent();
				else if (contentType.isFile()) {
					long long contentId = q->getBackend() == AbstractDb::Sqlite3
						? static_cast<long long>(row.get<int>(0))
						: row.get<long long>(0);

					string name;
					int size;
					string path;

					*session << "SELECT name, size, path FROM chat_message_file_content"
Ronan's avatar
Ronan committed
411
						"  WHERE chat_message_content_id = :contentId",
Ronan's avatar
Ronan committed
412
						soci::into(name), soci::into(size), soci::into(path), soci::use(contentId);
413 414 415 416 417 418 419 420

					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
421
					content = new Content();
422 423 424

				content->setContentType(contentType);
				content->setBody(row.get<string>(2));
425
				chatMessage->addContent(*content);
426
			}
Ronan's avatar
Ronan committed
427
		}
428 429 430 431 432 433

		// TODO: Use cache.
		return make_shared<ConferenceChatMessageEvent>(
			date,
			chatMessage
		);
Ronan's avatar
Ronan committed
434 435
	}

436
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
437
		long long eventId,
438 439
		EventLog::Type type,
		time_t date,
440
		const ChatRoomId &chatRoomId
441
	) const {
442 443 444 445
		unsigned int notifyId;
		string participantAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
446 447
		*session << "SELECT notify_id, participant_address.value"
			"  FROM conference_notified_event, conference_participant_event, sip_address as participant_address"
448
			"  WHERE conference_participant_event.event_id = :eventId"
Ronan's avatar
Ronan committed
449
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
450
			"    AND participant_address.id = participant_sip_address_id",
451 452 453 454 455
			soci::into(notifyId), soci::into(participantAddress), soci::use(eventId);

		return make_shared<ConferenceParticipantEvent>(
			type,
			date,
456
			chatRoomId,
457
			notifyId,
458
			IdentityAddress(participantAddress)
459
		);
Ronan's avatar
Ronan committed
460 461
	}

462
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
463
		long long eventId,
464 465
		EventLog::Type type,
		time_t date,
466
		const ChatRoomId &chatRoomId
467
	) const {
468 469
		unsigned int notifyId;
		string participantAddress;
470
		string deviceAddress;
471 472

		soci::session *session = dbSession.getBackendSession<soci::session>();
473
		*session << "SELECT notify_id, participant_address.value, device_address.value"
474
			"  FROM conference_notified_event, conference_participant_event, conference_participant_device_event,"
475
			"    sip_address AS participant_address, sip_address AS device_address"
476 477
			"  WHERE conference_participant_device_event.event_id = :eventId"
			"    AND conference_participant_event.event_id = conference_participant_device_event.event_id"
478
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
479 480
			"    AND participant_address.id = participant_sip_address_id"
			"    AND device_address.id = device_sip_address_id",
481
			soci::into(notifyId), soci::into(participantAddress), soci::into(deviceAddress), soci::use(eventId);
482 483 484 485

		return make_shared<ConferenceParticipantDeviceEvent>(
			type,
			date,
486
			chatRoomId,
487
			notifyId,
488 489
			IdentityAddress(participantAddress),
			IdentityAddress(deviceAddress)
490
		);
Ronan's avatar
Ronan committed
491 492
	}

493
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
494
		long long eventId,
495 496
		EventLog::Type type,
		time_t date,
497
		const ChatRoomId &chatRoomId
498 499 500 501
	) const {
		unsigned int notifyId;
		string subject;

502 503 504 505 506 507
		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);
508 509 510

		return make_shared<ConferenceSubjectEvent>(
			date,
511
			chatRoomId,
512 513 514
			notifyId,
			subject
		);
Ronan's avatar
Ronan committed
515 516
	}

517 518
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
519
	long long MainDbPrivate::insertEvent (const shared_ptr<EventLog> &eventLog) {
520 521 522 523
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
524 525
			soci::use(static_cast<int>(eventLog->getType())),
			soci::use(Utils::getTimeTAsTm(eventLog->getCreationTime()));
526
		return q->getLastInsertId();
527 528
	}

Ronan's avatar
Ronan committed
529
	long long MainDbPrivate::insertConferenceEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
530 531 532 533 534 535 536 537 538 539 540 541 542
		shared_ptr<ConferenceEvent> conferenceEvent = static_pointer_cast<ConferenceEvent>(eventLog);

		long long eventId = -1;
		long long curChatRoomId = selectChatRoomId(conferenceEvent->getChatRoomId());
		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);
543

544 545 546 547 548 549 550 551
			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);

			*session << "UPDATE chat_room SET last_update_date = :lastUpdateDate"
				"  WHERE id = :chatRoomId", soci::use(Utils::getTimeTAsTm(eventLog->getCreationTime())),
				soci::use(curChatRoomId);
		}
552 553 554 555 556 557 558

		if (chatRoomId)
			*chatRoomId = curChatRoomId;

		return eventId;
	}

Ronan's avatar
Ronan committed
559
	long long MainDbPrivate::insertConferenceCallEvent (const shared_ptr<EventLog> &eventLog) {
560 561 562 563
		// TODO.
		return 0;
	}

Ronan's avatar
Ronan committed
564 565
	long long MainDbPrivate::insertConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
566 567 568 569 570 571
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

572
		long long eventId = insertConferenceEvent(eventLog);
573 574
		if (eventId < 0)
			return -1;
575

576 577
		long long fromSipAddressId = insertSipAddress(chatMessage->getFromAddress().asString());
		long long toSipAddressId = insertSipAddress(chatMessage->getToAddress().asString());
Ronan's avatar
Ronan committed
578

579
		soci::session *session = dbSession.getBackendSession<soci::session>();
580
		*session << "INSERT INTO conference_chat_message_event ("
581
			"  event_id, from_sip_address_id, to_sip_address_id,"
582 583 584 585
			"  state, direction, imdn_message_id, is_secured"
			") VALUES ("
			"  :eventId, :localSipaddressId, :remoteSipaddressId,"
			"  :state, :direction, :imdnMessageId, :isSecured"
586
			")", soci::use(eventId), soci::use(fromSipAddressId), soci::use(toSipAddressId),
587 588 589
			soci::use(static_cast<int>(chatMessage->getState())), soci::use(static_cast<int>(chatMessage->getDirection())),
			soci::use(chatMessage->getImdnMessageId()), soci::use(chatMessage->isSecured() ? 1 : 0);

590
		for (const Content *content : chatMessage->getContents())
591
			insertContent(eventId, *content);
592

593 594 595
		return eventId;
	}

596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616
	void MainDbPrivate::updateConferenceChatMessageEvent(const std::shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return;
		}

		const EventLogPrivate *dEventLog = eventLog->getPrivate();
		MainDbEventKeyPrivate *dEventKey = dEventLog->dbKey.getPrivate();
		long long eventId = dEventKey->storageId;

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "UPDATE conference_chat_message_event SET state=:state WHERE event_id=:eventId"
			, soci::use(static_cast<int>(chatMessage->getState())), soci::use(eventId);

		/*for (const Content *content : chatMessage->getContents())
			updateContent(eventId, *content);*/
		//TODO check if content needs to be inserted, updated or removed
	}

617 618 619 620 621 622
	long long MainDbPrivate::insertConferenceNotifiedEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
		long long curChatRoomId;
		long long eventId = insertConferenceEvent(eventLog, &curChatRoomId);
		if (eventId < 0)
			return -1;

Ronan's avatar
Ronan committed
623
		unsigned int lastNotifyId = static_pointer_cast<ConferenceNotifiedEvent>(eventLog)->getNotifyId();
624 625 626

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
627 628
			"  VALUES (:eventId, :notifyId)", soci::use(eventId), soci::use(lastNotifyId);
		*session << "UPDATE chat_room SET last_notify_id = :lastNotifyId WHERE peer_sip_address_id = :chatRoomId",
629 630 631 632
			soci::use(lastNotifyId), soci::use(curChatRoomId);

		if (chatRoomId)
			*chatRoomId = curChatRoomId;
633

634 635 636
		return eventId;
	}

Ronan's avatar
Ronan committed
637
	long long MainDbPrivate::insertConferenceParticipantEvent (const shared_ptr<EventLog> &eventLog) {
638
		long long eventId = insertConferenceNotifiedEvent(eventLog);
639 640 641
		if (eventId < 0)
			return -1;

642
		long long participantAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
643
			static_pointer_cast<ConferenceParticipantEvent>(eventLog)->getParticipantAddress().asString()
644 645
		);

646
		soci::session *session = dbSession.getBackendSession<soci::session>();
647
		*session << "INSERT INTO conference_participant_event (event_id, participant_sip_address_id)"
648
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
649

650 651 652
		return eventId;
	}

Ronan's avatar
Ronan committed
653
	long long MainDbPrivate::insertConferenceParticipantDeviceEvent (const shared_ptr<EventLog> &eventLog) {
654
		long long eventId = insertConferenceParticipantEvent(eventLog);
655 656 657 658 659
		if (eventId < 0)
			return -1;

		long long deviceAddressId = insertSipAddress(
			static_pointer_cast<ConferenceParticipantDeviceEvent>(eventLog)->getDeviceAddress().asString()
660 661 662
		);

		soci::session *session = dbSession.getBackendSession<soci::session>();
663
		*session << "INSERT INTO conference_participant_device_event (event_id, device_sip_address_id)"
664
			"  VALUES (:eventId, :deviceAddressId)", soci::use(eventId), soci::use(deviceAddressId);
665

666
		return eventId;
667 668
	}

Ronan's avatar
Ronan committed
669
	long long MainDbPrivate::insertConferenceSubjectEvent (const shared_ptr<EventLog> &eventLog) {
670 671 672 673 674 675
		long long chatRoomId;
		long long eventId = insertConferenceNotifiedEvent(eventLog, &chatRoomId);
		if (eventId < 0)
			return -1;

		const string &subject = static_pointer_cast<ConferenceSubjectEvent>(eventLog)->getSubject();
676 677

		soci::session *session = dbSession.getBackendSession<soci::session>();
678
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
679 680 681 682
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(subject);

		*session << "UPDATE chat_room SET subject = :subject"
			"  WHERE id = :chatRoomId", soci::use(subject), soci::use(chatRoomId);
683

684
		return eventId;
685 686
	}

687 688 689 690 691 692 693 694 695 696 697 698 699
// -----------------------------------------------------------------------------

	shared_ptr<EventLog> MainDbPrivate::getEventFromCache (long long eventId) const {
		auto it = storageIdToEvent.find(eventId);
		if (it == storageIdToEvent.cend())
			return nullptr;

		shared_ptr<EventLog> eventLog = it->second.lock();
		// Must exist. If not, implementation bug.
		L_ASSERT(eventLog);
		return eventLog;
	}

700
	void MainDbPrivate::invalidConferenceEventsFromQuery (const string &query, long long chatRoomId) {
701 702 703
		L_Q();

		soci::session *session = dbSession.getBackendSession<soci::session>();
704
		soci::rowset<soci::row> rows = (session->prepare << query, soci::use(chatRoomId));
705 706 707 708 709 710 711 712 713 714 715 716
		for (const auto &row : rows) {
			shared_ptr<EventLog> eventLog = getEventFromCache(
				q->getBackend() == AbstractDb::Sqlite3 ? static_cast<long long>(row.get<int>(0)) : row.get<long long>(0)
			);
			if (eventLog) {
				const EventLogPrivate *dEventLog = eventLog->getPrivate();
				L_ASSERT(dEventLog->dbKey.isValid());
				dEventLog->dbKey = MainDbEventKey();
			}
		}
	}

717
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
718

719
	void MainDb::init () {
720
		L_D();
721 722

		const string charset = getBackend() == Mysql ? "DEFAULT CHARSET=utf8" : "";
Ronan's avatar
Ronan committed
723
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
724

Ronan's avatar
Ronan committed
725
		*session <<
726
			"CREATE TABLE IF NOT EXISTS sip_address ("
Erwan Croze's avatar
Erwan Croze committed
727
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
728
			"  value VARCHAR(255) UNIQUE NOT NULL"
729
			") " + charset;
730

731
		*session <<
732
			"CREATE TABLE IF NOT EXISTS content_type ("
Erwan Croze's avatar
Erwan Croze committed
733
			"  id" + primaryKeyStr("SMALLINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
734
			"  value VARCHAR(255) UNIQUE NOT NULL"
735
			") " + charset;
736

Ronan's avatar
Ronan committed
737
		*session <<
738
			"CREATE TABLE IF NOT EXISTS event ("
Erwan Croze's avatar
Erwan Croze committed
739
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
740
			"  type TINYINT UNSIGNED NOT NULL,"
741
			"  date DATE NOT NULL"
742
			") " + charset;
743

Ronan's avatar
Ronan committed
744
		*session <<
745
			"CREATE TABLE IF NOT EXISTS chat_room ("
746 747
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","

748
			// Server (for conference) or user sip address.
749
			"  peer_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + " NOT NULL,"
750

751
			"  local_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + " NOT NULL,"
752 753

			// Dialog creation date.
754 755 756 757
			"  creation_date DATE NOT NULL,"

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

759
			// ConferenceChatRoom, BasicChatRoom, RTT...
760
			"  capabilities TINYINT UNSIGNED NOT NULL,"
761

762 763 764
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
765
			"  last_notify_id INT UNSIGNED,"
766

767 768
			"  UNIQUE (peer_sip_address_id, local_sip_address_id),"

769
			"  FOREIGN KEY (peer_sip_address_id)"
770
			"    REFERENCES sip_address(id)"
771
			"    ON DELETE CASCADE,"
772 773 774
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
775
			") " + charset;
776

777 778
		*session <<
			"CREATE TABLE IF NOT EXISTS chat_room_participant ("
Erwan Croze's avatar
Erwan Croze committed
779
			"  chat_room_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
780
			"  participant_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
781

782 783
			"  is_admin BOOLEAN NOT NULL,"

784
			"  PRIMARY KEY (chat_room_id, participant_sip_address_id),"
785

786
			"  FOREIGN KEY (chat_room_id)"
787
			"    REFERENCES chat_room(id)"
788
			"    ON DELETE CASCADE,"
789
			"  FOREIGN KEY (participant_sip_address_id)"
790 791
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
792
			") " + charset;
793 794 795

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_event ("
796
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
797

798
			"  chat_room_id" + primaryKeyRefStr("BIGINT UNSIGNED") + " NOT NULL,"
799

800 801 802 803
			"  FOREIGN KEY (event_id)"
			"    REFERENCES event(id)"
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (chat_room_id)"
804
			"    REFERENCES chat_room(id)"
805
			"    ON DELETE CASCADE"
806
			") " + charset;
807

808 809
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_notified_event ("
Erwan Croze's avatar
Erwan Croze committed
810
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
811

812 813 814 815 816
			"  notify_id INT UNSIGNED NOT NULL,"

			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_event(event_id)"
			"    ON DELETE CASCADE"
817
			") " + charset;
818

819 820
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
Erwan Croze's avatar
Erwan Croze committed