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

23
#undef SOCI_ENABLED
24

Ronan's avatar
Ronan committed
25 26 27 28
#ifdef SOCI_ENABLED
	#include <soci/soci.h>
#endif // ifdef SOCI_ENABLED

29 30
#include "linphone/utils/utils.h"

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

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

Ronan's avatar
Ronan committed
47 48
using namespace std;

Ronan's avatar
Ronan committed
49 50 51 52
LINPHONE_BEGIN_NAMESPACE

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

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

55 56
#ifdef SOCI_ENABLED

Ronan's avatar
Ronan committed
57
// -----------------------------------------------------------------------------
58
// Soci backend.
Ronan's avatar
Ronan committed
59 60
// -----------------------------------------------------------------------------

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

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

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

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

Ronan's avatar
Ronan committed
86 87
// -----------------------------------------------------------------------------

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

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

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

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

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

119 120 121
		return sql;
	}

122 123
// -----------------------------------------------------------------------------

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

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

Ronan's avatar
Ronan committed
133
		lInfo() << "Insert new sip address in database: `" << sipAddress << "`.";
134 135
		*session << "INSERT INTO sip_address (value) VALUES (:sipAddress)", soci::use(sipAddress);
		return q->getLastInsertId();
136 137
	}

138
	void MainDbPrivate::insertContent (long long eventId, const Content &content) {
139
		L_Q();
140 141
		soci::session *session = dbSession.getBackendSession<soci::session>();

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

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

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

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

Ronan's avatar
Ronan committed
163
		lInfo() << "Insert new content type in database: `" << contentType << "`.";
164 165
		*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
		return q->getLastInsertId();
166 167
	}

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

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

184 185 186
		return sipAddressId;
	}

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

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

Ronan's avatar
Ronan committed
217 218
// -----------------------------------------------------------------------------

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

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

233 234
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
235
				return selectConferenceCallEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
236 237

			case EventLog::Type::ConferenceChatMessage:
238
				return selectConferenceChatMessageEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
239 240 241 242 243

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

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
248
				return selectConferenceParticipantDeviceEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
249 250

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

		return nullptr;
	}

257
	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
258
		long long,
259 260 261 262
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
263 264 265 266 267
		return make_shared<ConferenceEvent>(
			type,
			date,
			Address(peerAddress)
		);
Ronan's avatar
Ronan committed
268 269
	}

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

280
	shared_ptr<EventLog> MainDbPrivate::selectConferenceChatMessageEvent (
281
		long long eventId,
282 283 284 285
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
286 287 288 289 290 291 292 293 294 295 296
		L_Q();

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

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

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

297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328
		// 1 - Fetch chat message.
		shared_ptr<ChatMessage> chatMessage = make_shared<ChatMessage>(chatRoom);
		{
			string localSipAddress;
			string remoteSipAddress;
			string imdnMessageId;
			int state;
			int direction;
			int isSecured;

			soci::session *session = dbSession.getBackendSession<soci::session>();
			*session << "SELECT local_sip_address.value, remote_sip_address.value, imdn_message_id, state, direction, is_secured"
				"  FROM event, conference_chat_message_event, sip_address AS local_sip_address,"
				"  sip_address AS remote_sip_address"
				"  WHERE event_id = :eventId"
				"  AND event_id = event.id"
				"  AND local_sip_address_id = local_sip_address.id"
				"  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);

			chatMessage->getPrivate()->setState(static_cast<ChatMessage::State>(state), true);
			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));
			}
		}
Ronan's avatar
Ronan committed
329

330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351
		// 2 - Fetch contents.
		{
			soci::session *session = dbSession.getBackendSession<soci::session>();
			const string query = "SELECT content_type.value, body FROM chat_message_content, content_type"
				"  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
352
						"  WHERE chat_message_content_id = :contentId",
Ronan's avatar
Ronan committed
353
						soci::into(name), soci::into(size), soci::into(path), soci::use(contentId);
354 355 356 357 358 359 360 361

					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
362
					content = new Content();
363 364 365

				content->setContentType(contentType);
				content->setBody(row.get<string>(2));
Ronan's avatar
Ronan committed
366
				chatMessage->addContent(content);
367
			}
Ronan's avatar
Ronan committed
368
		}
369 370 371 372 373 374

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

377
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
378
		long long eventId,
379 380 381 382
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
383 384 385 386
		unsigned int notifyId;
		string participantAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
387 388
		*session << "SELECT notify_id, participant_address.value"
			"  FROM conference_notified_event, conference_participant_event, sip_address as participant_address"
389
			"  WHERE conference_participant_event.event_id = :eventId"
Ronan's avatar
Ronan committed
390 391
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
			"    AND participant_address.id = participant_address_id",
392 393 394 395 396 397 398 399 400
			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
401 402
	}

403
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
404
		long long eventId,
405 406 407 408
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
409 410 411 412 413
		unsigned int notifyId;
		string participantAddress;
		string gruuAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
414 415 416
		*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"
417 418
			"  WHERE conference_participant_device_event.event_id = :eventId"
			"    AND conference_participant_event.event_id = conference_participant_device_event.event_id"
419 420 421
			"    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",
422 423 424 425 426 427 428 429 430 431
			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
432 433
	}

434
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
435
		long long eventId,
436 437 438 439 440 441 442
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
		unsigned int notifyId;
		string subject;

443 444 445 446 447 448
		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);
449 450 451 452 453 454 455

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

458 459
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
460
	long long MainDbPrivate::insertEvent (const shared_ptr<EventLog> &eventLog) {
461 462 463 464
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
465
		soci::use(static_cast<int>(eventLog->getType())), soci::use(Utils::getTimeTAsTm(eventLog->getTime()));
466
		return q->getLastInsertId();
467 468
	}

Ronan's avatar
Ronan committed
469
	long long MainDbPrivate::insertConferenceEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
470 471
		long long eventId = insertEvent(eventLog);
		long long curChatRoomId = insertSipAddress(
Ronan's avatar
Ronan committed
472
			static_pointer_cast<ConferenceEvent>(eventLog)->getConferenceAddress().asString()
473 474 475 476 477 478 479 480 481 482 483 484
		);

		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
485
	long long MainDbPrivate::insertConferenceCallEvent (const shared_ptr<EventLog> &eventLog) {
486 487 488 489
		// TODO.
		return 0;
	}

Ronan's avatar
Ronan committed
490 491
	long long MainDbPrivate::insertConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
492 493 494 495 496 497
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

498
		tm eventTime = Utils::getTimeTAsTm(eventLog->getTime());
499

500 501
		long long localSipAddressId = insertSipAddress(chatMessage->getLocalAddress().asString());
		long long remoteSipAddressId = insertSipAddress(chatMessage->getRemoteAddress().asString());
502
		insertChatRoom(remoteSipAddressId, chatRoom->getCapabilities(), eventTime);
503
		long long eventId = insertConferenceEvent(eventLog);
504

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

507 508 509 510 511 512 513 514 515 516
		*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);

517
		for (const Content *content : chatMessage->getContents())
518
			insertContent(eventId, *content);
519

520 521 522
		return eventId;
	}

Ronan's avatar
Ronan committed
523
	long long MainDbPrivate::insertConferenceNotifiedEvent (const shared_ptr<EventLog> &eventLog) {
524 525
		long long chatRoomId;
		long long eventId = insertConferenceEvent(eventLog, &chatRoomId);
Ronan's avatar
Ronan committed
526
		unsigned int lastNotifyId = static_pointer_cast<ConferenceNotifiedEvent>(eventLog)->getNotifyId();
527 528 529

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
530 531 532
			"  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);
533

534 535 536
		return eventId;
	}

Ronan's avatar
Ronan committed
537
	long long MainDbPrivate::insertConferenceParticipantEvent (const shared_ptr<EventLog> &eventLog) {
538 539
		long long eventId = insertConferenceNotifiedEvent(eventLog);
		long long participantAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
540
			static_pointer_cast<ConferenceParticipantEvent>(eventLog)->getParticipantAddress().asString()
541 542
		);

543
		soci::session *session = dbSession.getBackendSession<soci::session>();
544
		*session << "INSERT INTO conference_participant_event (event_id, participant_address_id)"
545
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
546

547 548 549
		return eventId;
	}

Ronan's avatar
Ronan committed
550
	long long MainDbPrivate::insertConferenceParticipantDeviceEvent (const shared_ptr<EventLog> &eventLog) {
551 552
		long long eventId = insertConferenceParticipantEvent(eventLog);
		long long gruuAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
553
			static_pointer_cast<ConferenceParticipantDeviceEvent>(eventLog)->getGruuAddress().asString()
554 555 556
		);

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

560
		return eventId;
561 562
	}

Ronan's avatar
Ronan committed
563
	long long MainDbPrivate::insertConferenceSubjectEvent (const shared_ptr<EventLog> &eventLog) {
564
		long long eventId = insertConferenceNotifiedEvent(eventLog);
565 566

		soci::session *session = dbSession.getBackendSession<soci::session>();
567
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
568
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(
Ronan's avatar
Ronan committed
569
				static_pointer_cast<ConferenceSubjectEvent>(eventLog)->getSubject()
570
			);
571

572
		return eventId;
573 574
	}

575 576 577 578 579 580 581 582 583 584 585 586 587
// -----------------------------------------------------------------------------

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

588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604
	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();
			}
		}
	}

605
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
606

607
	void MainDb::init () {
608
		L_D();
609 610

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

Ronan's avatar
Ronan committed
613
		*session <<
614
			"CREATE TABLE IF NOT EXISTS sip_address ("
Erwan Croze's avatar
Erwan Croze committed
615
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
616
			"  value VARCHAR(255) UNIQUE NOT NULL"
617
			") " + charset;
618

619
		*session <<
620
			"CREATE TABLE IF NOT EXISTS content_type ("
Erwan Croze's avatar
Erwan Croze committed
621
			"  id" + primaryKeyStr("SMALLINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
622
			"  value VARCHAR(255) UNIQUE NOT NULL"
623
			") " + charset;
624

Ronan's avatar
Ronan committed
625
		*session <<
626
			"CREATE TABLE IF NOT EXISTS event ("
Erwan Croze's avatar
Erwan Croze committed
627
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
628
			"  type TINYINT UNSIGNED NOT NULL,"
629
			"  date DATE NOT NULL"
630
			") " + charset;
631

Ronan's avatar
Ronan committed
632
		*session <<
633
			"CREATE TABLE IF NOT EXISTS chat_room ("
634
			// Server (for conference) or user sip address.
635 636 637
			"  peer_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","

			"  local_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
638 639

			// Dialog creation date.
640 641 642 643
			"  creation_date DATE NOT NULL,"

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

645 646 647
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

648 649 650
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
651
			"  last_notify_id INT UNSIGNED,"
652

653
			"  PRIMARY KEY (peer_sip_address_id, local_sip_address_id),"
654
			"  FOREIGN KEY (peer_sip_address_id)"
655 656
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
657 658 659
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
660
			") " + charset;
661

662 663
		*session <<
			"CREATE TABLE IF NOT EXISTS chat_room_participant ("
Erwan Croze's avatar
Erwan Croze committed
664 665
			"  chat_room_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
			"  sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
666

667 668
			"  is_admin BOOLEAN NOT NULL,"

669
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
670 671 672 673 674 675
			"  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"
676
			") " + charset;
677 678 679

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

Erwan Croze's avatar
Erwan Croze committed
682
			"  chat_room_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
683 684 685 686 687 688 689

			"  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"
690
			") " + charset;
691

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

696 697 698 699 700
			"  notify_id INT UNSIGNED NOT NULL,"

			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_event(event_id)"
			"    ON DELETE CASCADE"
701
			") " + charset;
702

703 704
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
Erwan Croze's avatar
Erwan Croze committed
705
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
706

Erwan Croze's avatar
Erwan Croze committed
707
			"  participant_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
708

709 710
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
711 712 713 714
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (participant_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
715
			") " + charset;
716 717 718

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

Erwan Croze's avatar
Erwan Croze committed
721
			"  gruu_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
722

723 724
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_participant_event(event_id)"
725 726 727 728
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (gruu_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
729
			") " + charset;
730 731 732

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

735
			"  subject VARCHAR(255) NOT NULL,"
736

737 738
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
739
			"    ON DELETE CASCADE"
740
			") " + charset;
741

Ronan's avatar
Ronan committed
742
		*session <<
743
			"CREATE TABLE IF NOT EXISTS conference_chat_message_event ("
Erwan Croze's avatar
Erwan Croze committed
744
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
745

Erwan Croze's avatar
Erwan Croze committed
746 747
			"  local_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
			"  remote_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
748 749 750 751

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

752 753
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
754
			"  is_secured BOOLEAN NOT NULL,"
755

756
			"  FOREIGN KEY (event_id)"
757
			"    REFERENCES conference_event(event_id)"
758
			"    ON DELETE CASCADE,"
759 760
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
761
			"    ON DELETE CASCADE,"
762
			"  FOREIGN KEY (remote_sip_address_id)"
763
			"    REFERENCES sip_address(id)"
764
			"    ON DELETE CASCADE"
765
			") " + charset;
766

767
		*session <<
768
			"CREATE TABLE IF NOT EXISTS chat_message_participant ("
Erwan Croze's avatar
Erwan Croze committed
769 770
			"  event_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
			"  sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
771 772
			"  state TINYINT UNSIGNED NOT NULL,"

Ronan's avatar
Ronan committed
773 774
			"  PRIMARY KEY (event_id, sip_address_id),"
			"  FOREIGN KEY (event_id)"
775
			"    REFERENCES conference_chat_message_event(event_id)"
776 777 778
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
779
			"    ON DELETE CASCADE"
780
			") " + charset;
781

782
		*session <<
783
			"CREATE TABLE IF NOT EXISTS chat_message_content ("
Erwan Croze's avatar
Erwan Croze committed
784
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
785

786
			"  event_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
Erwan Croze's avatar
Erwan Croze committed
787
			"  content_type_id" + primaryKeyRefStr("SMALLINT UNSIGNED") + ","
788
			"  body TEXT NOT NULL,"
789

Ronan's avatar
Ronan committed
790
			"  FOREIGN KEY (event_id)"
791
			"    REFERENCES conference_chat_message_event(event_id)"
792 793 794
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
795
			"    ON DELETE CASCADE"
796
			") " + charset;
797

798 799 800 801 802 803 804 805 806 807 808
		*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"
809
			") " + charset;
810

811
		*session <<
812
			"CREATE TABLE IF NOT EXISTS chat_message_content_app_data ("
Erwan Croze's avatar
Erwan Croze committed
813
			"  chat_message_content_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
814

815
			"  name VARCHAR(255),"
816 817
			"  data BLOB,"

818
			"  PRIMARY KEY (chat_message_content_id, name),"
819 820
			"  FOREIGN KEY (chat_message_content_id)"
			"    REFERENCES chat_message_content(id)"
821
			"    ON DELETE CASCADE"
822
			") " + charset;
823

824
		*session <<
825
			"CREATE TABLE IF NOT EXISTS conference_message_crypto_data ("
Erwan Croze's avatar
Erwan Croze committed
826
			"  event_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","