main-db.cpp 62.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/chat-room-p.h"
31 32
#include "chat/chat-room/client-group-chat-room.h"
#include "conference/participant-p.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 123 124 125 126 127 128 129 130
	long long MainDbPrivate::resolveId (const soci::row &row, int col) const {
		L_Q();
		// See: http://soci.sourceforge.net/doc/master/backends/
		// `row id` is not supported by soci on Sqlite3. It's necessary to cast id to int...
		return q->getBackend() == AbstractDb::Sqlite3
			? static_cast<long long>(row.get<int>(0))
			: row.get<long long>(0);
	}

131
	long long MainDbPrivate::insertSipAddress (const string &sipAddress) {
132 133 134
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

135 136
		long long id = selectSipAddressId(sipAddress);
		if (id >= 0)
137 138
			return id;

Ronan's avatar
Ronan committed
139
		lInfo() << "Insert new sip address in database: `" << sipAddress << "`.";
140 141
		*session << "INSERT INTO sip_address (value) VALUES (:sipAddress)", soci::use(sipAddress);
		return q->getLastInsertId();
142 143
	}

144
	void MainDbPrivate::insertContent (long long eventId, const Content &content) {
145
		L_Q();
146 147
		soci::session *session = dbSession.getBackendSession<soci::session>();

148
		long long contentTypeId = insertContentType(content.getContentType().asString());
149
		*session << "INSERT INTO chat_message_content (event_id, content_type_id, body) VALUES"
Ronan's avatar
Ronan committed
150
			"  (:eventId, :contentTypeId, :body)", soci::use(eventId), soci::use(contentTypeId),
151
			soci::use(content.getBodyAsString());
152

153
		long long messageContentId = q->getLastInsertId();
Sylvain Berfini's avatar
Sylvain Berfini committed
154
		if (content.getContentType().isFile()) {
155
			const FileContent &fileContent = static_cast<const FileContent &>(content);
Sylvain Berfini's avatar
Sylvain Berfini committed
156 157
			*session << "INSERT INTO chat_message_file_content (chat_message_content_id, name, size, path) VALUES "
				" (:contentId, :name, :size, :path)",
158 159
				soci::use(messageContentId), soci::use(fileContent.getFileName()),
				soci::use(fileContent.getFileSize()), soci::use(fileContent.getFilePath());
Sylvain Berfini's avatar
Sylvain Berfini committed
160 161
		}

162
		for (const auto &appData : content.getAppDataMap())
Ronan's avatar
Ronan committed
163 164
			*session << "INSERT INTO chat_message_content_app_data (chat_message_content_id, name, data) VALUES"
				"  (:messageContentId, :name, :data)",
165
				soci::use(messageContentId), soci::use(appData.first), soci::use(appData.second);
166 167
	}

168 169 170 171 172 173 174 175 176 177 178 179
	void MainDbPrivate::updateContent (long long eventId, long long messageContentId, const Content &content) {
		soci::session *session = dbSession.getBackendSession<soci::session>();

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

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

180
	long long MainDbPrivate::insertContentType (const string &contentType) {
181 182 183
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

184
		long long id;
185 186
		*session << "SELECT id FROM content_type WHERE value = :contentType", soci::use(contentType), soci::into(id);
		if (session->got_data())
187 188
			return id;

Ronan's avatar
Ronan committed
189
		lInfo() << "Insert new content type in database: `" << contentType << "`.";
190 191
		*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
		return q->getLastInsertId();
192 193
	}

194 195 196 197
	long long MainDbPrivate::insertChatRoom (
		long long peerSipAddressId,
		long long localSipAddressId,
		int capabilities,
198
		const tm &creationTime
199 200 201
	) {
		L_Q();

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

204
		long long id = selectChatRoomId(peerSipAddressId, localSipAddressId);
205 206
		if (id >= 0)
			return id;
207

208 209 210 211 212 213 214 215 216
		lInfo() << "Insert new chat room in database: (peer=" << peerSipAddressId <<
			", local=" << localSipAddressId << ", capabilities=" << capabilities << ").";
		*session << "INSERT INTO chat_room ("
			"  peer_sip_address_id, local_sip_address_id, creation_time, last_update_time, capabilities"
			") VALUES (:peerSipAddressId, :localSipAddressId, :creationTime, :lastUpdateTime, :capabilities)",
			soci::use(peerSipAddressId), soci::use(localSipAddressId), soci::use(creationTime), soci::use(creationTime),
			soci::use(capabilities);

		return q->getLastInsertId();
217 218
	}

219 220 221 222 223 224
	long long MainDbPrivate::insertChatRoom (const std::shared_ptr<ChatRoom> &chatRoom) {
		L_Q();

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

		const ChatRoomId &chatRoomId = chatRoom->getChatRoomId();
225 226
		long long peerSipAddressId = insertSipAddress(chatRoomId.getPeerAddress().asString());
		long long localSipAddressId = insertSipAddress(chatRoomId.getLocalAddress().asString());
227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246

		long long id = selectChatRoomId(peerSipAddressId, localSipAddressId);
		if (id >= 0) {
			lWarning() << "Unable to insert chat room (it already exists): (peer=" << peerSipAddressId <<
				", local=" << localSipAddressId << ").";
			return id;
		}

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

		tm creationTime = Utils::getTimeTAsTm(chatRoom->getCreationTime());

		*session << "INSERT INTO chat_room ("
			"  peer_sip_address_id, local_sip_address_id, creation_time, last_update_time, capabilities, subject"
			") VALUES (:peerSipAddressId, :localSipAddressId, :creationTime, :lastUpdateTime, :capabilities, :subject)",
			soci::use(peerSipAddressId), soci::use(localSipAddressId), soci::use(creationTime), soci::use(creationTime),
			soci::use(static_cast<int>(chatRoom->getCapabilities())), soci::use(chatRoom->getSubject());

		id = q->getLastInsertId();
247 248 249 250

		if (!chatRoom->canHandleParticipants())
			return id;

Ronan's avatar
Ronan committed
251 252
		shared_ptr<Participant> me = chatRoom->getMe();
		insertChatRoomParticipant(id, insertSipAddress(me->getAddress().asString()), me->isAdmin());
253
		for (const auto &participant : chatRoom->getParticipants())
254
			insertChatRoomParticipant(id, insertSipAddress(participant->getAddress().asString()), participant->isAdmin());
255 256

		return id;
257 258
	}

259
	void MainDbPrivate::insertChatRoomParticipant (long long chatRoomId, long long sipAddressId, bool isAdmin) {
260
		soci::session *session = dbSession.getBackendSession<soci::session>();
261 262
		soci::statement statement = (
			session->prepare << "UPDATE chat_room_participant SET is_admin = :isAdmin"
263
				"  WHERE chat_room_id = :chatRoomId AND participant_sip_address_id = :sipAddressId",
264 265 266
				soci::use(static_cast<int>(isAdmin)), soci::use(chatRoomId), soci::use(sipAddressId)
		);
		statement.execute(true);
Ronan's avatar
Ronan committed
267 268
		if (statement.get_affected_rows() == 0) {
			lInfo() << "Insert new chat room participant in database: `" << sipAddressId << "` (isAdmin=" << isAdmin << ").";
269
			*session << "INSERT INTO chat_room_participant (chat_room_id, participant_sip_address_id, is_admin)"
270 271
				"  VALUES (:chatRoomId, :sipAddressId, :isAdmin)",
				soci::use(chatRoomId), soci::use(sipAddressId), soci::use(static_cast<int>(isAdmin));
Ronan's avatar
Ronan committed
272
		}
273 274
	}

275
	void MainDbPrivate::insertChatMessageParticipant (long long eventId, long long sipAddressId, int state) {
276 277
		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::statement statement = (
278
			session->prepare << "UPDATE chat_message_participant SET state = :state"
279
				"  WHERE event_id = :eventId AND participant_sip_address_id = :sipAddressId",
Ronan's avatar
Ronan committed
280
				soci::use(state), soci::use(eventId), soci::use(sipAddressId)
281 282
		);
		statement.execute(true);
283
		if (statement.get_affected_rows() == 0 && state != static_cast<int>(ChatMessage::State::Displayed))
284
			*session << "INSERT INTO chat_message_participant (event_id, participant_sip_address_id, state)"
Ronan's avatar
Ronan committed
285 286
				"  VALUES (:eventId, :sipAddressId, :state)",
				soci::use(eventId), soci::use(sipAddressId), soci::use(state);
287 288
	}

289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318
	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);
	}

319 320 321 322 323 324 325
	void MainDbPrivate::deleteChatRoomParticipant (long long chatRoomId, long long participantSipAddressId) {
		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "DELETE FROM chat_room_participant"
			"  WHERE chat_room_id = :chatRoomId AND participant_sip_address_id = :participantSipAddressId",
			soci::use(chatRoomId), soci::use(participantSipAddressId);
	}

Ronan's avatar
Ronan committed
326 327
// -----------------------------------------------------------------------------

328
	shared_ptr<EventLog> MainDbPrivate::selectGenericConferenceEvent (
329
		long long eventId,
330
		EventLog::Type type,
331
		time_t creationTime,
332
		const ChatRoomId &chatRoomId
333
	) const {
Ronan's avatar
Ronan committed
334 335 336 337 338 339
		switch (type) {
			case EventLog::Type::None:
				return nullptr;

			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
340
				return selectConferenceEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
341

342 343
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
344
				return selectConferenceCallEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
345 346

			case EventLog::Type::ConferenceChatMessage:
347
				return selectConferenceChatMessageEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
348 349 350 351 352

			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
353
				return selectConferenceParticipantEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
354 355 356

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
357
				return selectConferenceParticipantDeviceEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
358 359

			case EventLog::Type::ConferenceSubjectChanged:
360
				return selectConferenceSubjectEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
361 362 363 364 365
		}

		return nullptr;
	}

366
	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
367
		long long,
368
		EventLog::Type type,
369
		time_t creationTime,
370
		const ChatRoomId &chatRoomId
371
	) const {
372 373
		return make_shared<ConferenceEvent>(
			type,
374
			creationTime,
375
			chatRoomId
376
		);
Ronan's avatar
Ronan committed
377 378
	}

379
	shared_ptr<EventLog> MainDbPrivate::selectConferenceCallEvent (
380
		long long eventId,
381
		EventLog::Type type,
382
		time_t creationTime,
383
		const ChatRoomId &chatRoomId
384
	) const {
Ronan's avatar
Ronan committed
385 386 387 388
		// TODO.
		return nullptr;
	}

389
	shared_ptr<EventLog> MainDbPrivate::selectConferenceChatMessageEvent (
390
		long long eventId,
391
		EventLog::Type type,
392
		time_t creationTime,
393
		const ChatRoomId &chatRoomId
394
	) const {
Ronan's avatar
Ronan committed
395 396 397 398
		L_Q();

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

399
		shared_ptr<ChatRoom> chatRoom = core->findChatRoom(chatRoomId);
Ronan's avatar
Ronan committed
400 401 402 403 404
		if (!chatRoom)
			return nullptr;

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

405
		// 1 - Fetch chat message.
406
		shared_ptr<ChatMessage> chatMessage;
407
		{
408 409
			string fromSipAddress;
			string toSipAddress;
410 411 412

			tm messageTime;

413
			string imdnMessageId;
414

415 416 417 418 419
			int state;
			int direction;
			int isSecured;

			soci::session *session = dbSession.getBackendSession<soci::session>();
420
			*session << "SELECT from_sip_address.value, to_sip_address.value, time, imdn_message_id, state, direction, is_secured"
421 422
				"  FROM event, conference_chat_message_event, sip_address AS from_sip_address,"
				"  sip_address AS to_sip_address"
423 424
				"  WHERE event_id = :eventId"
				"  AND event_id = event.id"
425 426
				"  AND from_sip_address_id = from_sip_address.id"
				"  AND to_sip_address_id = to_sip_address.id", soci::into(fromSipAddress), soci::into(toSipAddress),
427 428
				soci::into(messageTime), soci::into(imdnMessageId), soci::into(state), soci::into(direction),
				soci::into(isSecured), soci::use(eventId);
429

430 431 432 433
			chatMessage = shared_ptr<ChatMessage>(new ChatMessage(
				chatRoom,
				static_cast<ChatMessage::Direction>(direction)
			));
434 435 436
			chatMessage->getPrivate()->setState(static_cast<ChatMessage::State>(state), true);
			chatMessage->setIsSecured(static_cast<bool>(isSecured));

437 438
			chatMessage->getPrivate()->forceFromAddress(IdentityAddress(fromSipAddress));
			chatMessage->getPrivate()->forceToAddress(IdentityAddress(toSipAddress));
439 440

			chatMessage->getPrivate()->setTime(Utils::getTmAsTimeT(messageTime));
441
		}
Ronan's avatar
Ronan committed
442

443 444 445
		// 2 - Fetch contents.
		{
			soci::session *session = dbSession.getBackendSession<soci::session>();
446
			const string query = "SELECT chat_message_content.id, content_type.id, content_type.value, body FROM chat_message_content, content_type"
447 448 449
				"  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) {
450
				ContentType contentType(row.get<string>(2));
451 452 453 454 455
				Content *content;

				if (contentType == ContentType::FileTransfer)
					content = new FileTransferContent();
				else if (contentType.isFile()) {
456
					long long contentId = resolveId(row, 0);
457 458 459 460 461 462

					string name;
					int size;
					string path;

					*session << "SELECT name, size, path FROM chat_message_file_content"
Ronan's avatar
Ronan committed
463
						"  WHERE chat_message_content_id = :contentId",
Ronan's avatar
Ronan committed
464
						soci::into(name), soci::into(size), soci::into(path), soci::use(contentId);
465 466 467 468 469 470 471 472

					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
473
					content = new Content();
474 475

				content->setContentType(contentType);
Sylvain Berfini's avatar
Sylvain Berfini committed
476
				content->setBody(row.get<string>(3));
477
				chatMessage->addContent(*content);
478
			}
Ronan's avatar
Ronan committed
479
		}
480 481 482

		// TODO: Use cache.
		return make_shared<ConferenceChatMessageEvent>(
483
			creationTime,
484 485
			chatMessage
		);
Ronan's avatar
Ronan committed
486 487
	}

488
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
489
		long long eventId,
490
		EventLog::Type type,
491
		time_t creationTime,
492
		const ChatRoomId &chatRoomId
493
	) const {
494 495 496 497
		unsigned int notifyId;
		string participantAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
498 499
		*session << "SELECT notify_id, participant_address.value"
			"  FROM conference_notified_event, conference_participant_event, sip_address as participant_address"
500
			"  WHERE conference_participant_event.event_id = :eventId"
Ronan's avatar
Ronan committed
501
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
502
			"    AND participant_address.id = participant_sip_address_id",
503 504 505 506
			soci::into(notifyId), soci::into(participantAddress), soci::use(eventId);

		return make_shared<ConferenceParticipantEvent>(
			type,
507
			creationTime,
508
			chatRoomId,
509
			notifyId,
510
			IdentityAddress(participantAddress)
511
		);
Ronan's avatar
Ronan committed
512 513
	}

514
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
515
		long long eventId,
516
		EventLog::Type type,
517
		time_t creationTime,
518
		const ChatRoomId &chatRoomId
519
	) const {
520 521
		unsigned int notifyId;
		string participantAddress;
522
		string deviceAddress;
523 524

		soci::session *session = dbSession.getBackendSession<soci::session>();
525
		*session << "SELECT notify_id, participant_address.value, device_address.value"
526
			"  FROM conference_notified_event, conference_participant_event, conference_participant_device_event,"
527
			"    sip_address AS participant_address, sip_address AS device_address"
528 529
			"  WHERE conference_participant_device_event.event_id = :eventId"
			"    AND conference_participant_event.event_id = conference_participant_device_event.event_id"
530
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
531 532
			"    AND participant_address.id = participant_sip_address_id"
			"    AND device_address.id = device_sip_address_id",
533
			soci::into(notifyId), soci::into(participantAddress), soci::into(deviceAddress), soci::use(eventId);
534 535 536

		return make_shared<ConferenceParticipantDeviceEvent>(
			type,
537
			creationTime,
538
			chatRoomId,
539
			notifyId,
540 541
			IdentityAddress(participantAddress),
			IdentityAddress(deviceAddress)
542
		);
Ronan's avatar
Ronan committed
543 544
	}

545
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
546
		long long eventId,
547
		EventLog::Type type,
548
		time_t creationTime,
549
		const ChatRoomId &chatRoomId
550 551 552 553
	) const {
		unsigned int notifyId;
		string subject;

554 555 556 557 558 559
		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);
560 561

		return make_shared<ConferenceSubjectEvent>(
562
			creationTime,
563
			chatRoomId,
564 565 566
			notifyId,
			subject
		);
Ronan's avatar
Ronan committed
567 568
	}

569 570
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
571
	long long MainDbPrivate::insertEvent (const shared_ptr<EventLog> &eventLog) {
572 573 574
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

575
		*session << "INSERT INTO event (type, creation_time) VALUES (:type, :creationTime)",
576 577
			soci::use(static_cast<int>(eventLog->getType())),
			soci::use(Utils::getTimeTAsTm(eventLog->getCreationTime()));
578
		return q->getLastInsertId();
579 580
	}

Ronan's avatar
Ronan committed
581
	long long MainDbPrivate::insertConferenceEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
582 583 584 585 586 587 588 589 590 591 592 593 594
		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);
595

596 597 598 599
			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);

600
			*session << "UPDATE chat_room SET last_update_time = :lastUpdateTime"
601 602 603
				"  WHERE id = :chatRoomId", soci::use(Utils::getTimeTAsTm(eventLog->getCreationTime())),
				soci::use(curChatRoomId);
		}
604 605 606 607 608 609 610

		if (chatRoomId)
			*chatRoomId = curChatRoomId;

		return eventId;
	}

Ronan's avatar
Ronan committed
611
	long long MainDbPrivate::insertConferenceCallEvent (const shared_ptr<EventLog> &eventLog) {
612 613 614 615
		// TODO.
		return 0;
	}

Ronan's avatar
Ronan committed
616 617
	long long MainDbPrivate::insertConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
618 619 620 621 622 623
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

624
		long long eventId = insertConferenceEvent(eventLog);
625 626
		if (eventId < 0)
			return -1;
627

628 629
		long long fromSipAddressId = insertSipAddress(chatMessage->getFromAddress().asString());
		long long toSipAddressId = insertSipAddress(chatMessage->getToAddress().asString());
Ronan's avatar
Ronan committed
630

631
		soci::session *session = dbSession.getBackendSession<soci::session>();
632
		*session << "INSERT INTO conference_chat_message_event ("
633
			"  event_id, from_sip_address_id, to_sip_address_id,"
634
			"  time, state, direction, imdn_message_id, is_secured"
635 636
			") VALUES ("
			"  :eventId, :localSipaddressId, :remoteSipaddressId,"
637
			"  :time, :state, :direction, :imdnMessageId, :isSecured"
638
			")", soci::use(eventId), soci::use(fromSipAddressId), soci::use(toSipAddressId),
639 640 641
			soci::use(Utils::getTimeTAsTm(chatMessage->getTime())), soci::use(static_cast<int>(chatMessage->getState())),
			soci::use(static_cast<int>(chatMessage->getDirection())), soci::use(chatMessage->getImdnMessageId()),
			soci::use(chatMessage->isSecured() ? 1 : 0);
642

643
		for (const Content *content : chatMessage->getContents())
644
			insertContent(eventId, *content);
645

646 647 648
		return eventId;
	}

649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669
	void MainDbPrivate::updateConferenceChatMessageEvent(const std::shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return;
		}

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

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

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

670 671 672 673 674 675
	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
676
		unsigned int lastNotifyId = static_pointer_cast<ConferenceNotifiedEvent>(eventLog)->getNotifyId();
677 678 679

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
680 681
			"  VALUES (:eventId, :notifyId)", soci::use(eventId), soci::use(lastNotifyId);
		*session << "UPDATE chat_room SET last_notify_id = :lastNotifyId WHERE peer_sip_address_id = :chatRoomId",
682 683 684 685
			soci::use(lastNotifyId), soci::use(curChatRoomId);

		if (chatRoomId)
			*chatRoomId = curChatRoomId;
686

687 688 689
		return eventId;
	}

Ronan's avatar
Ronan committed
690
	long long MainDbPrivate::insertConferenceParticipantEvent (const shared_ptr<EventLog> &eventLog) {
691 692
		long long chatRoomId;
		long long eventId = insertConferenceNotifiedEvent(eventLog, &chatRoomId);
693 694 695
		if (eventId < 0)
			return -1;

696 697 698
		shared_ptr<ConferenceParticipantEvent> participantEvent =
			static_pointer_cast<ConferenceParticipantEvent>(eventLog);

699
		long long participantAddressId = insertSipAddress(
700
			participantEvent->getParticipantAddress().asString()
701 702
		);

703
		soci::session *session = dbSession.getBackendSession<soci::session>();
704
		*session << "INSERT INTO conference_participant_event (event_id, participant_sip_address_id)"
705
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
706

707 708 709 710 711 712 713 714 715
		bool isAdmin = eventLog->getType() == EventLog::Type::ConferenceParticipantSetAdmin;
		switch (eventLog->getType()) {
			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
				insertChatRoomParticipant(chatRoomId, participantAddressId, isAdmin);
				break;

			case EventLog::Type::ConferenceParticipantRemoved:
716 717
				deleteChatRoomParticipant(chatRoomId, participantAddressId);
				break;
718 719 720 721 722

			default:
				break;
		}

723 724 725
		return eventId;
	}

Ronan's avatar
Ronan committed
726
	long long MainDbPrivate::insertConferenceParticipantDeviceEvent (const shared_ptr<EventLog> &eventLog) {
727
		long long eventId = insertConferenceParticipantEvent(eventLog);
728 729 730 731 732
		if (eventId < 0)
			return -1;

		long long deviceAddressId = insertSipAddress(
			static_pointer_cast<ConferenceParticipantDeviceEvent>(eventLog)->getDeviceAddress().asString()
733 734 735
		);

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

739
		return eventId;
740 741
	}

Ronan's avatar
Ronan committed
742
	long long MainDbPrivate::insertConferenceSubjectEvent (const shared_ptr<EventLog> &eventLog) {
743 744 745 746 747 748
		long long chatRoomId;
		long long eventId = insertConferenceNotifiedEvent(eventLog, &chatRoomId);
		if (eventId < 0)
			return -1;

		const string &subject = static_pointer_cast<ConferenceSubjectEvent>(eventLog)->getSubject();
749 750

		soci::session *session = dbSession.getBackendSession<soci::session>();
751
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
752 753 754 755
			"  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);
756

757
		return eventId;
758 759
	}

760 761 762 763 764 765 766 767 768 769 770 771 772
// -----------------------------------------------------------------------------

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

773
	void MainDbPrivate::invalidConferenceEventsFromQuery (const string &query, long long chatRoomId) {
774
		soci::session *session = dbSession.getBackendSession<soci::session>();
775
		soci::rowset<soci::row> rows = (session->prepare << query, soci::use(chatRoomId));
776
		for (const auto &row : rows) {
777
			shared_ptr<EventLog> eventLog = getEventFromCache(resolveId(row, 0));
778 779 780 781 782 783 784 785
			if (eventLog) {
				const EventLogPrivate *dEventLog = eventLog->getPrivate();
				L_ASSERT(dEventLog->dbKey.isValid());
				dEventLog->dbKey = MainDbEventKey();
			}
		}
	}

786
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
787

788
	void MainDb::init () {
789
		L_D();
790 791

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

Ronan's avatar
Ronan committed
794
		*session <<
795
			"CREATE TABLE IF NOT EXISTS sip_address ("
Erwan Croze's avatar
Erwan Croze committed
796
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
797
			"  value VARCHAR(255) UNIQUE NOT NULL"
798
			") " + charset;
799

800
		*session <<
801
			"CREATE TABLE IF NOT EXISTS content_type ("
Erwan Croze's avatar
Erwan Croze committed
802
			"  id" + primaryKeyStr("SMALLINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
803
			"  value VARCHAR(255) UNIQUE NOT NULL"
804
			") " + charset;
805

Ronan's avatar
Ronan committed
806
		*session <<
807
			"CREATE TABLE IF NOT EXISTS event ("
Erwan Croze's avatar
Erwan Croze committed
808
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
809
			"  type TINYINT UNSIGNED NOT NULL,"
810
			"  creation_time DATE NOT NULL"
811
			") " + charset;
812

Ronan's avatar
Ronan committed
813
		*session <<
814
			"CREATE TABLE IF NOT EXISTS chat_room ("
815 816
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","

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