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

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

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

Ronan's avatar
Ronan committed
46 47 48 49
LINPHONE_BEGIN_NAMESPACE

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

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

52 53
#ifdef SOCI_ENABLED

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

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

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

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

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

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

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

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

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

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

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

116 117 118
		return sql;
	}

119 120
// -----------------------------------------------------------------------------

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

125
		long long id;
126 127
		*session << "SELECT id FROM sip_address WHERE value = :sipAddress", soci::use(sipAddress), soci::into(id);
		if (session->got_data())
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
	long long MainDbPrivate::insertContentType (const string &contentType) {
152 153 154
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

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

Ronan's avatar
Ronan committed
160
		lInfo() << "Insert new content type in database: `" << contentType << "`.";
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);
Ronan's avatar
Ronan committed
171 172
		if (!session->got_data()) {
			lInfo() << "Insert new chat room in database: `" << sipAddressId << "` (capabilities=" << capabilities << ").";
173 174 175
			*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);
Ronan's avatar
Ronan committed
176
		}
177
		else
178
			*session << "UPDATE chat_room SET last_update_date = :lastUpdateDate WHERE peer_sip_address_id = :sipAddressId",
179
				soci::use(date), soci::use(sipAddressId);
180

181 182 183
		return sipAddressId;
	}

184
	void MainDbPrivate::insertChatRoomParticipant (long long chatRoomId, long long sipAddressId, bool isAdmin) {
185
		soci::session *session = dbSession.getBackendSession<soci::session>();
186 187 188 189 190 191
		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);
Ronan's avatar
Ronan committed
192 193
		if (statement.get_affected_rows() == 0) {
			lInfo() << "Insert new chat room participant in database: `" << sipAddressId << "` (isAdmin=" << isAdmin << ").";
194 195 196
			*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));
Ronan's avatar
Ronan committed
197
		}
198 199
	}

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

Ronan's avatar
Ronan committed
214 215
// -----------------------------------------------------------------------------

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

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

230 231
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
232
				return selectConferenceCallEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
233 234

			case EventLog::Type::ConferenceChatMessage:
235
				return selectConferenceChatMessageEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
236 237 238 239 240

			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
241
				return selectConferenceParticipantEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
242 243 244

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
245
				return selectConferenceParticipantDeviceEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
246 247

			case EventLog::Type::ConferenceSubjectChanged:
248
				return selectConferenceSubjectEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
249 250 251 252 253
		}

		return nullptr;
	}

254
	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
255
		long long,
256 257 258 259
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
260 261 262 263 264
		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 287 288 289 290 291 292 293 294 295 296 297 298
		L_Q();

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

		// TODO: Avoid address creation.
		shared_ptr<ChatRoom> chatRoom = core->findChatRoom(Address(peerAddress));
		if (!chatRoom)
			return nullptr;

		string localSipAddress;
		string remoteSipAddress;
		string imdnMessageId;
		int state;
		int direction;
		int isSecured;

299
		soci::session *session = dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
300
		*session << "SELECT local_sip_address.value, remote_sip_address.value, imdn_message_id, state, direction, is_secured"
301 302
			"  FROM event, conference_chat_message_event, sip_address AS local_sip_address,"
			"  sip_address AS remote_sip_address"
303 304
			"  WHERE event_id = :eventId"
			"  AND event_id = event.id"
305
			"  AND local_sip_address_id = local_sip_address.id"
306 307
			"  AND remote_sip_address_id = remote_sip_address.id", soci::into(localSipAddress), soci::into(remoteSipAddress),
			soci::into(imdnMessageId), soci::into(state), soci::into(direction), soci::into(isSecured), soci::use(eventId);
308 309

		// TODO: Create me.
Ronan's avatar
Ronan committed
310 311 312
		// TODO: Use cache, do not fetch the same message twice.
		shared_ptr<ChatMessage> chatMessage = make_shared<ChatMessage>(chatRoom);

313
		chatMessage->getPrivate()->setState(static_cast<ChatMessage::State>(state), true);
Ronan's avatar
Ronan committed
314 315 316 317 318 319 320 321 322 323
		chatMessage->getPrivate()->setDirection(static_cast<ChatMessage::Direction>(direction));
		chatMessage->setIsSecured(static_cast<bool>(isSecured));

		if (direction == static_cast<int>(ChatMessage::Direction::Outgoing)) {
			chatMessage->setFromAddress(Address(localSipAddress));
			chatMessage->setToAddress(Address(remoteSipAddress));
		} else {
			chatMessage->setFromAddress(Address(remoteSipAddress));
			chatMessage->setToAddress(Address(localSipAddress));
		}
324 325 326 327 328 329

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

332
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
333
		long long eventId,
334 335 336 337
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
338 339 340 341
		unsigned int notifyId;
		string participantAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
342 343
		*session << "SELECT notify_id, participant_address.value"
			"  FROM conference_notified_event, conference_participant_event, sip_address as participant_address"
344
			"  WHERE conference_participant_event.event_id = :eventId"
Ronan's avatar
Ronan committed
345 346
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
			"    AND participant_address.id = participant_address_id",
347 348 349 350 351 352 353 354 355
			soci::into(notifyId), soci::into(participantAddress), soci::use(eventId);

		return make_shared<ConferenceParticipantEvent>(
			type,
			date,
			Address(peerAddress),
			notifyId,
			Address(participantAddress)
		);
Ronan's avatar
Ronan committed
356 357
	}

358
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
359
		long long eventId,
360 361 362 363
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
364 365 366 367 368
		unsigned int notifyId;
		string participantAddress;
		string gruuAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
369 370 371
		*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"
372 373
			"  WHERE conference_participant_device_event.event_id = :eventId"
			"    AND conference_participant_event.event_id = conference_participant_device_event.event_id"
374 375 376
			"    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",
377 378 379 380 381 382 383 384 385 386
			soci::into(notifyId), soci::into(participantAddress), soci::into(gruuAddress), soci::use(eventId);

		return make_shared<ConferenceParticipantDeviceEvent>(
			type,
			date,
			Address(peerAddress),
			notifyId,
			Address(participantAddress),
			Address(gruuAddress)
		);
Ronan's avatar
Ronan committed
387 388
	}

389
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
390
		long long eventId,
391 392 393 394 395 396 397
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
		unsigned int notifyId;
		string subject;

398 399 400 401 402 403
		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);
404 405 406 407 408 409 410

		return make_shared<ConferenceSubjectEvent>(
			date,
			Address(peerAddress),
			notifyId,
			subject
		);
Ronan's avatar
Ronan committed
411 412
	}

413 414
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
415
	long long MainDbPrivate::insertEvent (const shared_ptr<EventLog> &eventLog) {
416 417 418 419
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
420
		soci::use(static_cast<int>(eventLog->getType())), soci::use(Utils::getTimeTAsTm(eventLog->getTime()));
421
		return q->getLastInsertId();
422 423
	}

Ronan's avatar
Ronan committed
424
	long long MainDbPrivate::insertConferenceEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
425 426
		long long eventId = insertEvent(eventLog);
		long long curChatRoomId = insertSipAddress(
Ronan's avatar
Ronan committed
427
			static_pointer_cast<ConferenceEvent>(eventLog)->getConferenceAddress().asString()
428 429 430 431 432 433 434 435 436 437 438 439
		);

		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
440
	long long MainDbPrivate::insertConferenceCallEvent (const shared_ptr<EventLog> &eventLog) {
441 442 443 444
		// TODO.
		return 0;
	}

Ronan's avatar
Ronan committed
445 446
	long long MainDbPrivate::insertConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
447 448 449 450 451 452
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

453
		tm eventTime = Utils::getTimeTAsTm(eventLog->getTime());
454

455 456
		long long localSipAddressId = insertSipAddress(chatMessage->getLocalAddress().asString());
		long long remoteSipAddressId = insertSipAddress(chatMessage->getRemoteAddress().asString());
457
		insertChatRoom(remoteSipAddressId, chatRoom->getCapabilities(), eventTime);
458
		long long eventId = insertConferenceEvent(eventLog);
459

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

462 463 464 465 466 467 468 469 470 471
		*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);

472
		for (const Content *content : chatMessage->getContents())
473
			insertContent(eventId, *content);
474

475 476 477
		return eventId;
	}

Ronan's avatar
Ronan committed
478
	long long MainDbPrivate::insertConferenceNotifiedEvent (const shared_ptr<EventLog> &eventLog) {
479 480
		long long chatRoomId;
		long long eventId = insertConferenceEvent(eventLog, &chatRoomId);
Ronan's avatar
Ronan committed
481
		unsigned int lastNotifyId = static_pointer_cast<ConferenceNotifiedEvent>(eventLog)->getNotifyId();
482 483 484

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
485 486 487
			"  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);
488

489 490 491
		return eventId;
	}

Ronan's avatar
Ronan committed
492
	long long MainDbPrivate::insertConferenceParticipantEvent (const shared_ptr<EventLog> &eventLog) {
493 494
		long long eventId = insertConferenceNotifiedEvent(eventLog);
		long long participantAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
495
			static_pointer_cast<ConferenceParticipantEvent>(eventLog)->getParticipantAddress().asString()
496 497
		);

498
		soci::session *session = dbSession.getBackendSession<soci::session>();
499
		*session << "INSERT INTO conference_participant_event (event_id, participant_address_id)"
500
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
501

502 503 504
		return eventId;
	}

Ronan's avatar
Ronan committed
505
	long long MainDbPrivate::insertConferenceParticipantDeviceEvent (const shared_ptr<EventLog> &eventLog) {
506 507
		long long eventId = insertConferenceParticipantEvent(eventLog);
		long long gruuAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
508
			static_pointer_cast<ConferenceParticipantDeviceEvent>(eventLog)->getGruuAddress().asString()
509 510 511
		);

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

515
		return eventId;
516 517
	}

Ronan's avatar
Ronan committed
518
	long long MainDbPrivate::insertConferenceSubjectEvent (const shared_ptr<EventLog> &eventLog) {
519
		long long eventId = insertConferenceNotifiedEvent(eventLog);
520 521

		soci::session *session = dbSession.getBackendSession<soci::session>();
522
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
523
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(
Ronan's avatar
Ronan committed
524
				static_pointer_cast<ConferenceSubjectEvent>(eventLog)->getSubject()
525
			);
526

527
		return eventId;
528 529
	}

530 531 532 533 534 535 536 537 538 539 540 541 542
// -----------------------------------------------------------------------------

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

543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559
	void MainDbPrivate::invalidEventsFromQuery (const string &query, const string &peerAddress) {
		L_Q();

		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::rowset<soci::row> rows = (session->prepare << query, soci::use(peerAddress));
		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();
			}
		}
	}

560
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
561

562
	void MainDb::init () {
563
		L_D();
564 565

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

Ronan's avatar
Ronan committed
568
		*session <<
569
			"CREATE TABLE IF NOT EXISTS sip_address ("
Erwan Croze's avatar
Erwan Croze committed
570
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
571
			"  value VARCHAR(255) UNIQUE NOT NULL"
572
			") " + charset;
573

574
		*session <<
575
			"CREATE TABLE IF NOT EXISTS content_type ("
Erwan Croze's avatar
Erwan Croze committed
576
			"  id" + primaryKeyStr("SMALLINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
577
			"  value VARCHAR(255) UNIQUE NOT NULL"
578
			") " + charset;
579

Ronan's avatar
Ronan committed
580
		*session <<
581
			"CREATE TABLE IF NOT EXISTS event ("
Erwan Croze's avatar
Erwan Croze committed
582
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
583
			"  type TINYINT UNSIGNED NOT NULL,"
584
			"  date DATE NOT NULL"
585
			") " + charset;
586

Ronan's avatar
Ronan committed
587
		*session <<
588
			"CREATE TABLE IF NOT EXISTS chat_room ("
589
			// Server (for conference) or user sip address.
Erwan Croze's avatar
Erwan Croze committed
590
			"  peer_sip_address_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
591 592

			// Dialog creation date.
593 594 595 596
			"  creation_date DATE NOT NULL,"

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

598 599 600
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

601 602 603
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
604
			"  last_notify_id INT UNSIGNED,"
605

606
			"  FOREIGN KEY (peer_sip_address_id)"
607 608
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
609
			") " + charset;
610

611 612
		*session <<
			"CREATE TABLE IF NOT EXISTS chat_room_participant ("
Erwan Croze's avatar
Erwan Croze committed
613 614
			"  chat_room_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
			"  sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
615

616 617
			"  is_admin BOOLEAN NOT NULL,"

618
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
619 620 621 622 623 624
			"  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"
625
			") " + charset;
626 627 628

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_event ("
Erwan Croze's avatar
Erwan Croze committed
629
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
630

Erwan Croze's avatar
Erwan Croze committed
631
			"  chat_room_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
632 633 634 635 636 637 638

			"  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"
639
			") " + charset;
640

641 642
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_notified_event ("
Erwan Croze's avatar
Erwan Croze committed
643
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
644

645 646 647 648 649
			"  notify_id INT UNSIGNED NOT NULL,"

			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_event(event_id)"
			"    ON DELETE CASCADE"
650
			") " + charset;
651

652 653
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
Erwan Croze's avatar
Erwan Croze committed
654
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
655

Erwan Croze's avatar
Erwan Croze committed
656
			"  participant_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
657

658 659
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
660 661 662 663
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (participant_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
664
			") " + charset;
665 666 667

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_device_event ("
Erwan Croze's avatar
Erwan Croze committed
668
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
669

Erwan Croze's avatar
Erwan Croze committed
670
			"  gruu_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
671

672 673
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_participant_event(event_id)"
674 675 676 677
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (gruu_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
678
			") " + charset;
679 680 681

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

684
			"  subject VARCHAR(255) NOT NULL,"
685

686 687
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
688
			"    ON DELETE CASCADE"
689
			") " + charset;
690

Ronan's avatar
Ronan committed
691
		*session <<
692
			"CREATE TABLE IF NOT EXISTS conference_chat_message_event ("
Erwan Croze's avatar
Erwan Croze committed
693
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
694

Erwan Croze's avatar
Erwan Croze committed
695 696
			"  local_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
			"  remote_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
697 698 699 700

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

701 702
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
703
			"  is_secured BOOLEAN NOT NULL,"
704

705
			"  FOREIGN KEY (event_id)"
706
			"    REFERENCES conference_event(event_id)"
707
			"    ON DELETE CASCADE,"
708 709
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
710
			"    ON DELETE CASCADE,"
711
			"  FOREIGN KEY (remote_sip_address_id)"
712
			"    REFERENCES sip_address(id)"
713
			"    ON DELETE CASCADE"
714
			") " + charset;
715

716
		*session <<
717
			"CREATE TABLE IF NOT EXISTS chat_message_participant ("
Erwan Croze's avatar
Erwan Croze committed
718 719
			"  event_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
			"  sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
720 721
			"  state TINYINT UNSIGNED NOT NULL,"

Ronan's avatar
Ronan committed
722 723
			"  PRIMARY KEY (event_id, sip_address_id),"
			"  FOREIGN KEY (event_id)"
724
			"    REFERENCES conference_chat_message_event(event_id)"
725 726 727
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
728
			"    ON DELETE CASCADE"
729
			") " + charset;
730

731
		*session <<
732
			"CREATE TABLE IF NOT EXISTS chat_message_content ("
Erwan Croze's avatar
Erwan Croze committed
733
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
734

735
			"  event_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
Erwan Croze's avatar
Erwan Croze committed
736
			"  content_type_id" + primaryKeyRefStr("SMALLINT UNSIGNED") + ","
737
			"  body TEXT NOT NULL,"
738

Ronan's avatar
Ronan committed
739
			"  FOREIGN KEY (event_id)"
740
			"    REFERENCES conference_chat_message_event(event_id)"
741 742 743
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
744
			"    ON DELETE CASCADE"
745
			") " + charset;
746

747 748 749 750 751 752 753 754 755 756 757
		*session <<
			"CREATE TABLE IF NOT EXISTS chat_message_file_content ("
			"  chat_message_content_id" + primaryKeyStr("BIGINT UNSIGNED") + ","

			"  name VARCHAR(256) NOT NULL,"
			"  size INT UNSIGNED NOT NULL,"
			"  path VARCHAR(512) NOT NULL,"

			"  FOREIGN KEY (chat_message_content_id)"
			"    REFERENCES chat_message_content(id)"
			"    ON DELETE CASCADE"
758
			") " + charset;
759

760
		*session <<
761
			"CREATE TABLE IF NOT EXISTS chat_message_content_app_data ("
Erwan Croze's avatar
Erwan Croze committed
762
			"  chat_message_content_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
763

764
			"  name VARCHAR(255),"
765 766
			"  data BLOB,"

767
			"  PRIMARY KEY (chat_message_content_id, name),"
768 769
			"  FOREIGN KEY (chat_message_content_id)"
			"    REFERENCES chat_message_content(id)"
770
			"    ON DELETE CASCADE"
771
			") " + charset;
772

773
		*session <<
774
			"CREATE TABLE IF NOT EXISTS conference_message_crypto_data ("
Erwan Croze's avatar
Erwan Croze committed
775
			"  event_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
776

777
			"  name VARCHAR(255),"
778 779
			"  data BLOB,"

780
			"  PRIMARY KEY (event_id, name),"
Ronan's avatar
Ronan committed
781
			"  FOREIGN KEY (event_id)"
782
			"    REFERENCES conference_chat_message_event(event_id)"
783
			"    ON DELETE CASCADE"
784
			") " + charset;
785 786 787 788

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
789 790
			"CREATE TRIGGER IF NOT EXISTS chat_message_participant_deleter"
			"  AFTER UPDATE OF state ON chat_message_participant FOR EACH ROW"
791 792
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
793
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
794 795
			"    SELECT state FROM chat_message_participant WHERE"
			"    NEW.event_id = chat_message_participant.event_id"
796 797 798 799 800
			"    AND state <> ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += "    LIMIT 1"
			"  )) = 0"
			"  BEGIN"
801 802
			"  DELETE FROM chat_message_participant WHERE NEW.event_id = chat_message_participant.event_id;"
			"  UPDATE conference_chat_message_event SET state = ";