main-db.cpp 60.7 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();
Sylvain Berfini's avatar
Sylvain Berfini committed
145
		if (content.getContentType().isFile()) {
146
			const FileContent &fileContent = static_cast<const FileContent &>(content);
Sylvain Berfini's avatar
Sylvain Berfini committed
147 148
			*session << "INSERT INTO chat_message_file_content (chat_message_content_id, name, size, path) VALUES "
				" (:contentId, :name, :size, :path)",
149 150
				soci::use(messageContentId), soci::use(fileContent.getFileName()),
				soci::use(fileContent.getFileSize()), soci::use(fileContent.getFilePath());
Sylvain Berfini's avatar
Sylvain Berfini committed
151 152
		}

153
		for (const auto &appData : content.getAppDataMap())
Ronan's avatar
Ronan committed
154 155
			*session << "INSERT INTO chat_message_content_app_data (chat_message_content_id, name, data) VALUES"
				"  (:messageContentId, :name, :data)",
156
				soci::use(messageContentId), soci::use(appData.first), soci::use(appData.second);
157 158
	}

159 160 161 162 163 164 165 166 167 168 169 170
	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);
	}

171
	long long MainDbPrivate::insertContentType (const string &contentType) {
172 173 174
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

175
		long long id;
176 177
		*session << "SELECT id FROM content_type WHERE value = :contentType", soci::use(contentType), soci::into(id);
		if (session->got_data())
178 179
			return id;

Ronan's avatar
Ronan committed
180
		lInfo() << "Insert new content type in database: `" << contentType << "`.";
181 182
		*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
		return q->getLastInsertId();
183 184
	}

185 186 187 188
	long long MainDbPrivate::insertChatRoom (
		long long peerSipAddressId,
		long long localSipAddressId,
		int capabilities,
189
		const tm &creationTime
190 191 192
	) {
		L_Q();

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

195
		long long id = selectChatRoomId(peerSipAddressId, localSipAddressId);
196 197
		if (id >= 0)
			return id;
198

199 200 201 202 203 204 205 206 207
		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();
208 209
	}

210 211 212 213 214 215
	long long MainDbPrivate::insertChatRoom (const std::shared_ptr<ChatRoom> &chatRoom) {
		L_Q();

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

		const ChatRoomId &chatRoomId = chatRoom->getChatRoomId();
216 217
		long long peerSipAddressId = insertSipAddress(chatRoomId.getPeerAddress().asString());
		long long localSipAddressId = insertSipAddress(chatRoomId.getLocalAddress().asString());
218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238

		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();
		for (const auto &participant : chatRoom->getParticipants())
239
			insertChatRoomParticipant(id, insertSipAddress(participant->getAddress().asString()), participant->isAdmin());
240 241

		return id;
242 243
	}

244
	void MainDbPrivate::insertChatRoomParticipant (long long chatRoomId, long long sipAddressId, bool isAdmin) {
245
		soci::session *session = dbSession.getBackendSession<soci::session>();
246 247
		soci::statement statement = (
			session->prepare << "UPDATE chat_room_participant SET is_admin = :isAdmin"
248
				"  WHERE chat_room_id = :chatRoomId AND participant_sip_address_id = :sipAddressId",
249 250 251
				soci::use(static_cast<int>(isAdmin)), soci::use(chatRoomId), soci::use(sipAddressId)
		);
		statement.execute(true);
Ronan's avatar
Ronan committed
252 253
		if (statement.get_affected_rows() == 0) {
			lInfo() << "Insert new chat room participant in database: `" << sipAddressId << "` (isAdmin=" << isAdmin << ").";
254
			*session << "INSERT INTO chat_room_participant (chat_room_id, participant_sip_address_id, is_admin)"
255 256
				"  VALUES (:chatRoomId, :sipAddressId, :isAdmin)",
				soci::use(chatRoomId), soci::use(sipAddressId), soci::use(static_cast<int>(isAdmin));
Ronan's avatar
Ronan committed
257
		}
258 259
	}

260
	void MainDbPrivate::insertChatMessageParticipant (long long eventId, long long sipAddressId, int state) {
261 262
		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::statement statement = (
263
			session->prepare << "UPDATE chat_message_participant SET state = :state"
264
				"  WHERE event_id = :eventId AND participant_sip_address_id = :sipAddressId",
Ronan's avatar
Ronan committed
265
				soci::use(state), soci::use(eventId), soci::use(sipAddressId)
266 267
		);
		statement.execute(true);
268
		if (statement.get_affected_rows() == 0 && state != static_cast<int>(ChatMessage::State::Displayed))
269
			*session << "INSERT INTO chat_message_participant (event_id, participant_sip_address_id, state)"
Ronan's avatar
Ronan committed
270 271
				"  VALUES (:eventId, :sipAddressId, :state)",
				soci::use(eventId), soci::use(sipAddressId), soci::use(state);
272 273
	}

274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303
	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
304 305
// -----------------------------------------------------------------------------

306
	shared_ptr<EventLog> MainDbPrivate::selectGenericConferenceEvent (
307
		long long eventId,
308
		EventLog::Type type,
309
		time_t creationTime,
310
		const ChatRoomId &chatRoomId
311
	) const {
Ronan's avatar
Ronan committed
312 313 314 315 316 317
		switch (type) {
			case EventLog::Type::None:
				return nullptr;

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

320 321
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
322
				return selectConferenceCallEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
323 324

			case EventLog::Type::ConferenceChatMessage:
325
				return selectConferenceChatMessageEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
326 327 328 329 330

			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
331
				return selectConferenceParticipantEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
332 333 334

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
335
				return selectConferenceParticipantDeviceEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
336 337

			case EventLog::Type::ConferenceSubjectChanged:
338
				return selectConferenceSubjectEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
339 340 341 342 343
		}

		return nullptr;
	}

344
	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
345
		long long,
346
		EventLog::Type type,
347
		time_t creationTime,
348
		const ChatRoomId &chatRoomId
349
	) const {
350 351
		return make_shared<ConferenceEvent>(
			type,
352
			creationTime,
353
			chatRoomId
354
		);
Ronan's avatar
Ronan committed
355 356
	}

357
	shared_ptr<EventLog> MainDbPrivate::selectConferenceCallEvent (
358
		long long eventId,
359
		EventLog::Type type,
360
		time_t creationTime,
361
		const ChatRoomId &chatRoomId
362
	) const {
Ronan's avatar
Ronan committed
363 364 365 366
		// TODO.
		return nullptr;
	}

367
	shared_ptr<EventLog> MainDbPrivate::selectConferenceChatMessageEvent (
368
		long long eventId,
369
		EventLog::Type type,
370
		time_t creationTime,
371
		const ChatRoomId &chatRoomId
372
	) const {
Ronan's avatar
Ronan committed
373 374 375 376
		L_Q();

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

377
		shared_ptr<ChatRoom> chatRoom = core->findChatRoom(chatRoomId);
Ronan's avatar
Ronan committed
378 379 380 381 382
		if (!chatRoom)
			return nullptr;

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

383
		// 1 - Fetch chat message.
384
		shared_ptr<ChatMessage> chatMessage;
385
		{
386 387
			string fromSipAddress;
			string toSipAddress;
388 389 390

			tm messageTime;

391
			string imdnMessageId;
392

393 394 395 396 397
			int state;
			int direction;
			int isSecured;

			soci::session *session = dbSession.getBackendSession<soci::session>();
398
			*session << "SELECT from_sip_address.value, to_sip_address.value, time, imdn_message_id, state, direction, is_secured"
399 400
				"  FROM event, conference_chat_message_event, sip_address AS from_sip_address,"
				"  sip_address AS to_sip_address"
401 402
				"  WHERE event_id = :eventId"
				"  AND event_id = event.id"
403 404
				"  AND from_sip_address_id = from_sip_address.id"
				"  AND to_sip_address_id = to_sip_address.id", soci::into(fromSipAddress), soci::into(toSipAddress),
405 406
				soci::into(messageTime), soci::into(imdnMessageId), soci::into(state), soci::into(direction),
				soci::into(isSecured), soci::use(eventId);
407

408 409 410 411
			chatMessage = shared_ptr<ChatMessage>(new ChatMessage(
				chatRoom,
				static_cast<ChatMessage::Direction>(direction)
			));
412 413 414
			chatMessage->getPrivate()->setState(static_cast<ChatMessage::State>(state), true);
			chatMessage->setIsSecured(static_cast<bool>(isSecured));

415 416
			chatMessage->getPrivate()->forceFromAddress(IdentityAddress(fromSipAddress));
			chatMessage->getPrivate()->forceToAddress(IdentityAddress(toSipAddress));
417 418

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

421 422 423
		// 2 - Fetch contents.
		{
			soci::session *session = dbSession.getBackendSession<soci::session>();
424
			const string query = "SELECT chat_message_content.id, content_type.id, content_type.value, body FROM chat_message_content, content_type"
425 426 427
				"  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) {
428
				ContentType contentType(row.get<string>(2));
429 430 431 432 433 434 435 436 437 438 439 440 441 442
				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
443
						"  WHERE chat_message_content_id = :contentId",
Ronan's avatar
Ronan committed
444
						soci::into(name), soci::into(size), soci::into(path), soci::use(contentId);
445 446 447 448 449 450 451 452

					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
453
					content = new Content();
454 455

				content->setContentType(contentType);
Sylvain Berfini's avatar
Sylvain Berfini committed
456
				content->setBody(row.get<string>(3));
457
				chatMessage->addContent(*content);
458
			}
Ronan's avatar
Ronan committed
459
		}
460 461 462

		// TODO: Use cache.
		return make_shared<ConferenceChatMessageEvent>(
463
			creationTime,
464 465
			chatMessage
		);
Ronan's avatar
Ronan committed
466 467
	}

468
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
469
		long long eventId,
470
		EventLog::Type type,
471
		time_t creationTime,
472
		const ChatRoomId &chatRoomId
473
	) const {
474 475 476 477
		unsigned int notifyId;
		string participantAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
478 479
		*session << "SELECT notify_id, participant_address.value"
			"  FROM conference_notified_event, conference_participant_event, sip_address as participant_address"
480
			"  WHERE conference_participant_event.event_id = :eventId"
Ronan's avatar
Ronan committed
481
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
482
			"    AND participant_address.id = participant_sip_address_id",
483 484 485 486
			soci::into(notifyId), soci::into(participantAddress), soci::use(eventId);

		return make_shared<ConferenceParticipantEvent>(
			type,
487
			creationTime,
488
			chatRoomId,
489
			notifyId,
490
			IdentityAddress(participantAddress)
491
		);
Ronan's avatar
Ronan committed
492 493
	}

494
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
495
		long long eventId,
496
		EventLog::Type type,
497
		time_t creationTime,
498
		const ChatRoomId &chatRoomId
499
	) const {
500 501
		unsigned int notifyId;
		string participantAddress;
502
		string deviceAddress;
503 504

		soci::session *session = dbSession.getBackendSession<soci::session>();
505
		*session << "SELECT notify_id, participant_address.value, device_address.value"
506
			"  FROM conference_notified_event, conference_participant_event, conference_participant_device_event,"
507
			"    sip_address AS participant_address, sip_address AS device_address"
508 509
			"  WHERE conference_participant_device_event.event_id = :eventId"
			"    AND conference_participant_event.event_id = conference_participant_device_event.event_id"
510
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
511 512
			"    AND participant_address.id = participant_sip_address_id"
			"    AND device_address.id = device_sip_address_id",
513
			soci::into(notifyId), soci::into(participantAddress), soci::into(deviceAddress), soci::use(eventId);
514 515 516

		return make_shared<ConferenceParticipantDeviceEvent>(
			type,
517
			creationTime,
518
			chatRoomId,
519
			notifyId,
520 521
			IdentityAddress(participantAddress),
			IdentityAddress(deviceAddress)
522
		);
Ronan's avatar
Ronan committed
523 524
	}

525
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
526
		long long eventId,
527
		EventLog::Type type,
528
		time_t creationTime,
529
		const ChatRoomId &chatRoomId
530 531 532 533
	) const {
		unsigned int notifyId;
		string subject;

534 535 536 537 538 539
		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);
540 541

		return make_shared<ConferenceSubjectEvent>(
542
			creationTime,
543
			chatRoomId,
544 545 546
			notifyId,
			subject
		);
Ronan's avatar
Ronan committed
547 548
	}

549 550
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
551
	long long MainDbPrivate::insertEvent (const shared_ptr<EventLog> &eventLog) {
552 553 554
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

555
		*session << "INSERT INTO event (type, creation_time) VALUES (:type, :creationTime)",
556 557
			soci::use(static_cast<int>(eventLog->getType())),
			soci::use(Utils::getTimeTAsTm(eventLog->getCreationTime()));
558
		return q->getLastInsertId();
559 560
	}

Ronan's avatar
Ronan committed
561
	long long MainDbPrivate::insertConferenceEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
562 563 564 565 566 567 568 569 570 571 572 573 574
		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);
575

576 577 578 579
			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);

580
			*session << "UPDATE chat_room SET last_update_time = :lastUpdateTime"
581 582 583
				"  WHERE id = :chatRoomId", soci::use(Utils::getTimeTAsTm(eventLog->getCreationTime())),
				soci::use(curChatRoomId);
		}
584 585 586 587 588 589 590

		if (chatRoomId)
			*chatRoomId = curChatRoomId;

		return eventId;
	}

Ronan's avatar
Ronan committed
591
	long long MainDbPrivate::insertConferenceCallEvent (const shared_ptr<EventLog> &eventLog) {
592 593 594 595
		// TODO.
		return 0;
	}

Ronan's avatar
Ronan committed
596 597
	long long MainDbPrivate::insertConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
598 599 600 601 602 603
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

604
		long long eventId = insertConferenceEvent(eventLog);
605 606
		if (eventId < 0)
			return -1;
607

608 609
		long long fromSipAddressId = insertSipAddress(chatMessage->getFromAddress().asString());
		long long toSipAddressId = insertSipAddress(chatMessage->getToAddress().asString());
Ronan's avatar
Ronan committed
610

611
		soci::session *session = dbSession.getBackendSession<soci::session>();
612
		*session << "INSERT INTO conference_chat_message_event ("
613
			"  event_id, from_sip_address_id, to_sip_address_id,"
614
			"  time, state, direction, imdn_message_id, is_secured"
615 616
			") VALUES ("
			"  :eventId, :localSipaddressId, :remoteSipaddressId,"
617
			"  :time, :state, :direction, :imdnMessageId, :isSecured"
618
			")", soci::use(eventId), soci::use(fromSipAddressId), soci::use(toSipAddressId),
619 620 621
			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);
622

623
		for (const Content *content : chatMessage->getContents())
624
			insertContent(eventId, *content);
625

626 627 628
		return eventId;
	}

629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649
	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
	}

650 651 652 653 654 655
	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
656
		unsigned int lastNotifyId = static_pointer_cast<ConferenceNotifiedEvent>(eventLog)->getNotifyId();
657 658 659

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
660 661
			"  VALUES (:eventId, :notifyId)", soci::use(eventId), soci::use(lastNotifyId);
		*session << "UPDATE chat_room SET last_notify_id = :lastNotifyId WHERE peer_sip_address_id = :chatRoomId",
662 663 664 665
			soci::use(lastNotifyId), soci::use(curChatRoomId);

		if (chatRoomId)
			*chatRoomId = curChatRoomId;
666

667 668 669
		return eventId;
	}

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

676 677 678
		shared_ptr<ConferenceParticipantEvent> participantEvent =
			static_pointer_cast<ConferenceParticipantEvent>(eventLog);

679
		long long participantAddressId = insertSipAddress(
680
			participantEvent->getParticipantAddress().asString()
681 682
		);

683
		soci::session *session = dbSession.getBackendSession<soci::session>();
684
		*session << "INSERT INTO conference_participant_event (event_id, participant_sip_address_id)"
685
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
686

687 688 689 690 691 692 693 694 695 696 697 698 699 700 701
		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:
				// TODO: Deal with remove.

			default:
				break;
		}

702 703 704
		return eventId;
	}

Ronan's avatar
Ronan committed
705
	long long MainDbPrivate::insertConferenceParticipantDeviceEvent (const shared_ptr<EventLog> &eventLog) {
706
		long long eventId = insertConferenceParticipantEvent(eventLog);
707 708 709 710 711
		if (eventId < 0)
			return -1;

		long long deviceAddressId = insertSipAddress(
			static_pointer_cast<ConferenceParticipantDeviceEvent>(eventLog)->getDeviceAddress().asString()
712 713 714
		);

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

718
		return eventId;
719 720
	}

Ronan's avatar
Ronan committed
721
	long long MainDbPrivate::insertConferenceSubjectEvent (const shared_ptr<EventLog> &eventLog) {
722 723 724 725 726 727
		long long chatRoomId;
		long long eventId = insertConferenceNotifiedEvent(eventLog, &chatRoomId);
		if (eventId < 0)
			return -1;

		const string &subject = static_pointer_cast<ConferenceSubjectEvent>(eventLog)->getSubject();
728 729

		soci::session *session = dbSession.getBackendSession<soci::session>();
730
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
731 732 733 734
			"  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);
735

736
		return eventId;
737 738
	}

739 740 741 742 743 744 745 746 747 748 749 750 751
// -----------------------------------------------------------------------------

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

752
	void MainDbPrivate::invalidConferenceEventsFromQuery (const string &query, long long chatRoomId) {
753 754 755
		L_Q();

		soci::session *session = dbSession.getBackendSession<soci::session>();
756
		soci::rowset<soci::row> rows = (session->prepare << query, soci::use(chatRoomId));
757 758 759 760 761 762 763 764 765 766 767 768
		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();
			}
		}
	}

769
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
770

771
	void MainDb::init () {
772
		L_D();
773 774

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

Ronan's avatar
Ronan committed
777
		*session <<
778
			"CREATE TABLE IF NOT EXISTS sip_address ("
Erwan Croze's avatar
Erwan Croze committed
779
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
780
			"  value VARCHAR(255) UNIQUE NOT NULL"
781
			") " + charset;
782

783
		*session <<
784
			"CREATE TABLE IF NOT EXISTS content_type ("
Erwan Croze's avatar
Erwan Croze committed
785
			"  id" + primaryKeyStr("SMALLINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
786
			"  value VARCHAR(255) UNIQUE NOT NULL"
787
			") " + charset;
788

Ronan's avatar
Ronan committed
789
		*session <<
790
			"CREATE TABLE IF NOT EXISTS event ("
Erwan Croze's avatar
Erwan Croze committed
791
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
792
			"  type TINYINT UNSIGNED NOT NULL,"
793
			"  creation_time DATE NOT NULL"
794
			") " + charset;
795

Ronan's avatar
Ronan committed
796
		*session <<
797
			"CREATE TABLE IF NOT EXISTS chat_room ("
798 799
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","

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

803
			"  local_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + " NOT NULL,"
804

805 806
			// Dialog creation time.
			"  creation_time DATE NOT NULL,"
807

808 809
			// Last event time (call, message...).
			"  last_update_time DATE NOT NULL,"
810

811
			// ConferenceChatRoom, BasicChatRoom, RTT...
812
			"  capabilities TINYINT UNSIGNED NOT NULL,"
813

814 815 816
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
817
			"  last_notify_id INT UNSIGNED,"