main-db.cpp 54.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
	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 166 167 168
	long long MainDbPrivate::insertChatRoom (
		long long peerSipAddressId,
		long long localSipAddressId,
		int capabilities,
169 170
		const tm &date,
		const string &subject
171 172 173
	) {
		L_Q();

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

176 177 178 179 180 181
		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
182
				") VALUES (:peerSipAddressId, :localSipAddressId, :creationDate, :lastUpdateDate, :capabilities, :subject)",
183
				soci::use(peerSipAddressId), soci::use(localSipAddressId), soci::use(date), soci::use(date),
184
				soci::use(capabilities), soci::use(subject);
185 186

			return q->getLastInsertId();
Ronan's avatar
Ronan committed
187
		}
188

189 190 191
		return id;
	}

192 193 194 195 196 197
	long long MainDbPrivate::insertChatRoom (
		const ChatRoomId &chatRoomId,
		int capabilities,
		const tm &date,
		const string &subject
	) {
198 199 200 201
		return insertChatRoom (
			insertSipAddress(chatRoomId.getPeerAddress().asString()),
			insertSipAddress(chatRoomId.getLocalAddress().asString()),
			capabilities,
202 203
			date,
			subject
204
		);
205 206
	}

207
	void MainDbPrivate::insertChatRoomParticipant (long long chatRoomId, long long sipAddressId, bool isAdmin) {
208
		soci::session *session = dbSession.getBackendSession<soci::session>();
209 210
		soci::statement statement = (
			session->prepare << "UPDATE chat_room_participant SET is_admin = :isAdmin"
211
				"  WHERE chat_room_id = :chatRoomId AND participant_sip_address_id = :sipAddressId",
212 213 214
				soci::use(static_cast<int>(isAdmin)), soci::use(chatRoomId), soci::use(sipAddressId)
		);
		statement.execute(true);
Ronan's avatar
Ronan committed
215 216
		if (statement.get_affected_rows() == 0) {
			lInfo() << "Insert new chat room participant in database: `" << sipAddressId << "` (isAdmin=" << isAdmin << ").";
217
			*session << "INSERT INTO chat_room_participant (chat_room_id, participant_sip_address_id, is_admin)"
218 219
				"  VALUES (:chatRoomId, :sipAddressId, :isAdmin)",
				soci::use(chatRoomId), soci::use(sipAddressId), soci::use(static_cast<int>(isAdmin));
Ronan's avatar
Ronan committed
220
		}
221 222
	}

223
	void MainDbPrivate::insertChatMessageParticipant (long long eventId, long long sipAddressId, int state) {
224 225
		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::statement statement = (
226
			session->prepare << "UPDATE chat_message_participant SET state = :state"
227
				"  WHERE event_id = :eventId AND participant_sip_address_id = :sipAddressId",
Ronan's avatar
Ronan committed
228
				soci::use(state), soci::use(eventId), soci::use(sipAddressId)
229 230
		);
		statement.execute(true);
231
		if (statement.get_affected_rows() == 0 && state != static_cast<int>(ChatMessage::State::Displayed))
232
			*session << "INSERT INTO chat_message_participant (event_id, participant_sip_address_id, state)"
Ronan's avatar
Ronan committed
233 234
				"  VALUES (:eventId, :sipAddressId, :state)",
				soci::use(eventId), soci::use(sipAddressId), soci::use(state);
235 236
	}

237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266
	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
267 268
// -----------------------------------------------------------------------------

269
	shared_ptr<EventLog> MainDbPrivate::selectGenericConferenceEvent (
270
		long long eventId,
271 272
		EventLog::Type type,
		time_t date,
273
		const ChatRoomId &chatRoomId
274
	) const {
Ronan's avatar
Ronan committed
275 276 277 278 279 280
		switch (type) {
			case EventLog::Type::None:
				return nullptr;

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

283 284
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
285
				return selectConferenceCallEvent(eventId, type, date, chatRoomId);
Ronan's avatar
Ronan committed
286 287

			case EventLog::Type::ConferenceChatMessage:
288
				return selectConferenceChatMessageEvent(eventId, type, date, chatRoomId);
Ronan's avatar
Ronan committed
289 290 291 292 293

			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
294
				return selectConferenceParticipantEvent(eventId, type, date, chatRoomId);
Ronan's avatar
Ronan committed
295 296 297

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
298
				return selectConferenceParticipantDeviceEvent(eventId, type, date, chatRoomId);
Ronan's avatar
Ronan committed
299 300

			case EventLog::Type::ConferenceSubjectChanged:
301
				return selectConferenceSubjectEvent(eventId, type, date, chatRoomId);
Ronan's avatar
Ronan committed
302 303 304 305 306
		}

		return nullptr;
	}

307
	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
308
		long long,
309 310
		EventLog::Type type,
		time_t date,
311
		const ChatRoomId &chatRoomId
312
	) const {
313 314 315
		return make_shared<ConferenceEvent>(
			type,
			date,
316
			chatRoomId
317
		);
Ronan's avatar
Ronan committed
318 319
	}

320
	shared_ptr<EventLog> MainDbPrivate::selectConferenceCallEvent (
321
		long long eventId,
322 323
		EventLog::Type type,
		time_t date,
324
		const ChatRoomId &chatRoomId
325
	) const {
Ronan's avatar
Ronan committed
326 327 328 329
		// TODO.
		return nullptr;
	}

330
	shared_ptr<EventLog> MainDbPrivate::selectConferenceChatMessageEvent (
331
		long long eventId,
332 333
		EventLog::Type type,
		time_t date,
334
		const ChatRoomId &chatRoomId
335
	) const {
Ronan's avatar
Ronan committed
336 337 338 339
		L_Q();

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

340
		shared_ptr<ChatRoom> chatRoom = core->findChatRoom(chatRoomId);
Ronan's avatar
Ronan committed
341 342 343 344 345
		if (!chatRoom)
			return nullptr;

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

346
		// 1 - Fetch chat message.
347
		shared_ptr<ChatMessage> chatMessage;
348
		{
349 350
			string fromSipAddress;
			string toSipAddress;
351 352 353 354 355 356
			string imdnMessageId;
			int state;
			int direction;
			int isSecured;

			soci::session *session = dbSession.getBackendSession<soci::session>();
357 358 359
			*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"
360 361
				"  WHERE event_id = :eventId"
				"  AND event_id = event.id"
362 363
				"  AND from_sip_address_id = from_sip_address.id"
				"  AND to_sip_address_id = to_sip_address.id", soci::into(fromSipAddress), soci::into(toSipAddress),
364 365
				soci::into(imdnMessageId), soci::into(state), soci::into(direction), soci::into(isSecured), soci::use(eventId);

366 367 368 369
			chatMessage = shared_ptr<ChatMessage>(new ChatMessage(
				chatRoom,
				static_cast<ChatMessage::Direction>(direction)
			));
370 371 372
			chatMessage->getPrivate()->setState(static_cast<ChatMessage::State>(state), true);
			chatMessage->setIsSecured(static_cast<bool>(isSecured));

373 374
			chatMessage->getPrivate()->forceFromAddress(IdentityAddress(fromSipAddress));
			chatMessage->getPrivate()->forceToAddress(IdentityAddress(toSipAddress));
375
		}
Ronan's avatar
Ronan committed
376

377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398
		// 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
399
						"  WHERE chat_message_content_id = :contentId",
Ronan's avatar
Ronan committed
400
						soci::into(name), soci::into(size), soci::into(path), soci::use(contentId);
401 402 403 404 405 406 407 408

					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
409
					content = new Content();
410 411 412

				content->setContentType(contentType);
				content->setBody(row.get<string>(2));
413
				chatMessage->addContent(*content);
414
			}
Ronan's avatar
Ronan committed
415
		}
416 417 418 419 420 421

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

424
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
425
		long long eventId,
426 427
		EventLog::Type type,
		time_t date,
428
		const ChatRoomId &chatRoomId
429
	) const {
430 431 432 433
		unsigned int notifyId;
		string participantAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
434 435
		*session << "SELECT notify_id, participant_address.value"
			"  FROM conference_notified_event, conference_participant_event, sip_address as participant_address"
436
			"  WHERE conference_participant_event.event_id = :eventId"
Ronan's avatar
Ronan committed
437
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
438
			"    AND participant_address.id = participant_sip_address_id",
439 440 441 442 443
			soci::into(notifyId), soci::into(participantAddress), soci::use(eventId);

		return make_shared<ConferenceParticipantEvent>(
			type,
			date,
444
			chatRoomId,
445
			notifyId,
446
			IdentityAddress(participantAddress)
447
		);
Ronan's avatar
Ronan committed
448 449
	}

450
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
451
		long long eventId,
452 453
		EventLog::Type type,
		time_t date,
454
		const ChatRoomId &chatRoomId
455
	) const {
456 457
		unsigned int notifyId;
		string participantAddress;
458
		string deviceAddress;
459 460

		soci::session *session = dbSession.getBackendSession<soci::session>();
461
		*session << "SELECT notify_id, participant_address.value, device_address.value"
462
			"  FROM conference_notified_event, conference_participant_event, conference_participant_device_event,"
463
			"    sip_address AS participant_address, sip_address AS device_address"
464 465
			"  WHERE conference_participant_device_event.event_id = :eventId"
			"    AND conference_participant_event.event_id = conference_participant_device_event.event_id"
466
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
467 468
			"    AND participant_address.id = participant_sip_address_id"
			"    AND device_address.id = device_sip_address_id",
469
			soci::into(notifyId), soci::into(participantAddress), soci::into(deviceAddress), soci::use(eventId);
470 471 472 473

		return make_shared<ConferenceParticipantDeviceEvent>(
			type,
			date,
474
			chatRoomId,
475
			notifyId,
476 477
			IdentityAddress(participantAddress),
			IdentityAddress(deviceAddress)
478
		);
Ronan's avatar
Ronan committed
479 480
	}

481
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
482
		long long eventId,
483 484
		EventLog::Type type,
		time_t date,
485
		const ChatRoomId &chatRoomId
486 487 488 489
	) const {
		unsigned int notifyId;
		string subject;

490 491 492 493 494 495
		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);
496 497 498

		return make_shared<ConferenceSubjectEvent>(
			date,
499
			chatRoomId,
500 501 502
			notifyId,
			subject
		);
Ronan's avatar
Ronan committed
503 504
	}

505 506
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
507
	long long MainDbPrivate::insertEvent (const shared_ptr<EventLog> &eventLog) {
508 509 510 511
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
512 513
			soci::use(static_cast<int>(eventLog->getType())),
			soci::use(Utils::getTimeTAsTm(eventLog->getCreationTime()));
514
		return q->getLastInsertId();
515 516
	}

Ronan's avatar
Ronan committed
517
	long long MainDbPrivate::insertConferenceEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
518 519 520 521 522 523 524 525 526 527 528 529 530
		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);
531

532 533 534 535 536 537 538 539
			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);
		}
540 541 542 543 544 545 546

		if (chatRoomId)
			*chatRoomId = curChatRoomId;

		return eventId;
	}

Ronan's avatar
Ronan committed
547
	long long MainDbPrivate::insertConferenceCallEvent (const shared_ptr<EventLog> &eventLog) {
548 549 550 551
		// TODO.
		return 0;
	}

Ronan's avatar
Ronan committed
552 553
	long long MainDbPrivate::insertConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
554 555 556 557 558 559
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

560
		long long eventId = insertConferenceEvent(eventLog);
561 562
		if (eventId < 0)
			return -1;
563

564 565
		long long fromSipAddressId = insertSipAddress(chatMessage->getFromAddress().asString());
		long long toSipAddressId = insertSipAddress(chatMessage->getToAddress().asString());
Ronan's avatar
Ronan committed
566

567
		soci::session *session = dbSession.getBackendSession<soci::session>();
568
		*session << "INSERT INTO conference_chat_message_event ("
569
			"  event_id, from_sip_address_id, to_sip_address_id,"
570 571 572 573
			"  state, direction, imdn_message_id, is_secured"
			") VALUES ("
			"  :eventId, :localSipaddressId, :remoteSipaddressId,"
			"  :state, :direction, :imdnMessageId, :isSecured"
574
			")", soci::use(eventId), soci::use(fromSipAddressId), soci::use(toSipAddressId),
575 576 577
			soci::use(static_cast<int>(chatMessage->getState())), soci::use(static_cast<int>(chatMessage->getDirection())),
			soci::use(chatMessage->getImdnMessageId()), soci::use(chatMessage->isSecured() ? 1 : 0);

578
		for (const Content *content : chatMessage->getContents())
579
			insertContent(eventId, *content);
580

581 582 583
		return eventId;
	}

584 585 586 587 588 589
	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
590
		unsigned int lastNotifyId = static_pointer_cast<ConferenceNotifiedEvent>(eventLog)->getNotifyId();
591 592 593

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
594 595
			"  VALUES (:eventId, :notifyId)", soci::use(eventId), soci::use(lastNotifyId);
		*session << "UPDATE chat_room SET last_notify_id = :lastNotifyId WHERE peer_sip_address_id = :chatRoomId",
596 597 598 599
			soci::use(lastNotifyId), soci::use(curChatRoomId);

		if (chatRoomId)
			*chatRoomId = curChatRoomId;
600

601 602 603
		return eventId;
	}

Ronan's avatar
Ronan committed
604
	long long MainDbPrivate::insertConferenceParticipantEvent (const shared_ptr<EventLog> &eventLog) {
605
		long long eventId = insertConferenceNotifiedEvent(eventLog);
606 607 608
		if (eventId < 0)
			return -1;

609
		long long participantAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
610
			static_pointer_cast<ConferenceParticipantEvent>(eventLog)->getParticipantAddress().asString()
611 612
		);

613
		soci::session *session = dbSession.getBackendSession<soci::session>();
614
		*session << "INSERT INTO conference_participant_event (event_id, participant_sip_address_id)"
615
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
616

617 618 619
		return eventId;
	}

Ronan's avatar
Ronan committed
620
	long long MainDbPrivate::insertConferenceParticipantDeviceEvent (const shared_ptr<EventLog> &eventLog) {
621
		long long eventId = insertConferenceParticipantEvent(eventLog);
622 623 624 625 626
		if (eventId < 0)
			return -1;

		long long deviceAddressId = insertSipAddress(
			static_pointer_cast<ConferenceParticipantDeviceEvent>(eventLog)->getDeviceAddress().asString()
627 628 629
		);

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

633
		return eventId;
634 635
	}

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

		const string &subject = static_pointer_cast<ConferenceSubjectEvent>(eventLog)->getSubject();
643 644

		soci::session *session = dbSession.getBackendSession<soci::session>();
645
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
646 647 648 649
			"  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);
650

651
		return eventId;
652 653
	}

654 655 656 657 658 659 660 661 662 663 664 665 666
// -----------------------------------------------------------------------------

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

667
	void MainDbPrivate::invalidConferenceEventsFromQuery (const string &query, long long chatRoomId) {
668 669 670
		L_Q();

		soci::session *session = dbSession.getBackendSession<soci::session>();
671
		soci::rowset<soci::row> rows = (session->prepare << query, soci::use(chatRoomId));
672 673 674 675 676 677 678 679 680 681 682 683
		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();
			}
		}
	}

684
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
685

686
	void MainDb::init () {
687
		L_D();
688 689

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

Ronan's avatar
Ronan committed
692
		*session <<
693
			"CREATE TABLE IF NOT EXISTS sip_address ("
Erwan Croze's avatar
Erwan Croze committed
694
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
695
			"  value VARCHAR(255) UNIQUE NOT NULL"
696
			") " + charset;
697

698
		*session <<
699
			"CREATE TABLE IF NOT EXISTS content_type ("
Erwan Croze's avatar
Erwan Croze committed
700
			"  id" + primaryKeyStr("SMALLINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
701
			"  value VARCHAR(255) UNIQUE NOT NULL"
702
			") " + charset;
703

Ronan's avatar
Ronan committed
704
		*session <<
705
			"CREATE TABLE IF NOT EXISTS event ("
Erwan Croze's avatar
Erwan Croze committed
706
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
707
			"  type TINYINT UNSIGNED NOT NULL,"
708
			"  date DATE NOT NULL"
709
			") " + charset;
710

Ronan's avatar
Ronan committed
711
		*session <<
712
			"CREATE TABLE IF NOT EXISTS chat_room ("
713 714
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","

715
			// Server (for conference) or user sip address.
716
			"  peer_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + " NOT NULL,"
717

718
			"  local_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + " NOT NULL,"
719 720

			// Dialog creation date.
721 722 723 724
			"  creation_date DATE NOT NULL,"

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

726
			// ConferenceChatRoom, BasicChatRoom, RTT...
727
			"  capabilities TINYINT UNSIGNED NOT NULL,"
728

729 730 731
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
732
			"  last_notify_id INT UNSIGNED,"
733

734 735
			"  UNIQUE (peer_sip_address_id, local_sip_address_id),"

736
			"  FOREIGN KEY (peer_sip_address_id)"
737
			"    REFERENCES sip_address(id)"
738
			"    ON DELETE CASCADE,"
739 740 741
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
742
			") " + charset;
743

744 745
		*session <<
			"CREATE TABLE IF NOT EXISTS chat_room_participant ("
Erwan Croze's avatar
Erwan Croze committed
746
			"  chat_room_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
747
			"  participant_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
748

749 750
			"  is_admin BOOLEAN NOT NULL,"

751
			"  PRIMARY KEY (chat_room_id, participant_sip_address_id),"
752

753
			"  FOREIGN KEY (chat_room_id)"
754
			"    REFERENCES chat_room(id)"
755
			"    ON DELETE CASCADE,"
756
			"  FOREIGN KEY (participant_sip_address_id)"
757 758
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
759
			") " + charset;
760 761 762

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

765
			"  chat_room_id" + primaryKeyRefStr("BIGINT UNSIGNED") + " NOT NULL,"
766

767 768 769 770
			"  FOREIGN KEY (event_id)"
			"    REFERENCES event(id)"
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (chat_room_id)"
771
			"    REFERENCES chat_room(id)"
772
			"    ON DELETE CASCADE"
773
			") " + charset;
774

775 776
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_notified_event ("
Erwan Croze's avatar
Erwan Croze committed
777
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
778

779 780 781 782 783
			"  notify_id INT UNSIGNED NOT NULL,"

			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_event(event_id)"
			"    ON DELETE CASCADE"
784
			") " + charset;
785

786 787
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
Erwan Croze's avatar
Erwan Croze committed
788
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
789

790
			"  participant_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + " NOT NULL,"
791

792 793
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
794
			"    ON DELETE CASCADE,"
795
			"  FOREIGN KEY (participant_sip_address_id)"
796 797
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
798
			") " + charset;
799 800 801

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