main-db.cpp 59.2 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
		const string &subject
191 192 193
	) {
		L_Q();

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

196 197 198 199 200
		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 ("
201 202 203
				"  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),
204
				soci::use(capabilities), soci::use(subject);
205 206

			return q->getLastInsertId();
Ronan's avatar
Ronan committed
207
		}
208

209 210 211
		return id;
	}

212 213 214
	long long MainDbPrivate::insertChatRoom (
		const ChatRoomId &chatRoomId,
		int capabilities,
215
		const tm &creationTime,
216 217
		const string &subject
	) {
218 219 220 221
		return insertChatRoom (
			insertSipAddress(chatRoomId.getPeerAddress().asString()),
			insertSipAddress(chatRoomId.getLocalAddress().asString()),
			capabilities,
222
			creationTime,
223
			subject
224
		);
225 226
	}

227
	void MainDbPrivate::insertChatRoomParticipant (long long chatRoomId, long long sipAddressId, bool isAdmin) {
228
		soci::session *session = dbSession.getBackendSession<soci::session>();
229 230
		soci::statement statement = (
			session->prepare << "UPDATE chat_room_participant SET is_admin = :isAdmin"
231
				"  WHERE chat_room_id = :chatRoomId AND participant_sip_address_id = :sipAddressId",
232 233 234
				soci::use(static_cast<int>(isAdmin)), soci::use(chatRoomId), soci::use(sipAddressId)
		);
		statement.execute(true);
Ronan's avatar
Ronan committed
235 236
		if (statement.get_affected_rows() == 0) {
			lInfo() << "Insert new chat room participant in database: `" << sipAddressId << "` (isAdmin=" << isAdmin << ").";
237
			*session << "INSERT INTO chat_room_participant (chat_room_id, participant_sip_address_id, is_admin)"
238 239
				"  VALUES (:chatRoomId, :sipAddressId, :isAdmin)",
				soci::use(chatRoomId), soci::use(sipAddressId), soci::use(static_cast<int>(isAdmin));
Ronan's avatar
Ronan committed
240
		}
241 242
	}

243
	void MainDbPrivate::insertChatMessageParticipant (long long eventId, long long sipAddressId, int state) {
244 245
		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::statement statement = (
246
			session->prepare << "UPDATE chat_message_participant SET state = :state"
247
				"  WHERE event_id = :eventId AND participant_sip_address_id = :sipAddressId",
Ronan's avatar
Ronan committed
248
				soci::use(state), soci::use(eventId), soci::use(sipAddressId)
249 250
		);
		statement.execute(true);
251
		if (statement.get_affected_rows() == 0 && state != static_cast<int>(ChatMessage::State::Displayed))
252
			*session << "INSERT INTO chat_message_participant (event_id, participant_sip_address_id, state)"
Ronan's avatar
Ronan committed
253 254
				"  VALUES (:eventId, :sipAddressId, :state)",
				soci::use(eventId), soci::use(sipAddressId), soci::use(state);
255 256
	}

257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286
	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
287 288
// -----------------------------------------------------------------------------

289
	shared_ptr<EventLog> MainDbPrivate::selectGenericConferenceEvent (
290
		long long eventId,
291
		EventLog::Type type,
292
		time_t creationTime,
293
		const ChatRoomId &chatRoomId
294
	) const {
Ronan's avatar
Ronan committed
295 296 297 298 299 300
		switch (type) {
			case EventLog::Type::None:
				return nullptr;

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

303 304
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
305
				return selectConferenceCallEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
306 307

			case EventLog::Type::ConferenceChatMessage:
308
				return selectConferenceChatMessageEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
309 310 311 312 313

			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
314
				return selectConferenceParticipantEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
315 316 317

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
318
				return selectConferenceParticipantDeviceEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
319 320

			case EventLog::Type::ConferenceSubjectChanged:
321
				return selectConferenceSubjectEvent(eventId, type, creationTime, chatRoomId);
Ronan's avatar
Ronan committed
322 323 324 325 326
		}

		return nullptr;
	}

327
	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
328
		long long,
329
		EventLog::Type type,
330
		time_t creationTime,
331
		const ChatRoomId &chatRoomId
332
	) const {
333 334
		return make_shared<ConferenceEvent>(
			type,
335
			creationTime,
336
			chatRoomId
337
		);
Ronan's avatar
Ronan committed
338 339
	}

340
	shared_ptr<EventLog> MainDbPrivate::selectConferenceCallEvent (
341
		long long eventId,
342
		EventLog::Type type,
343
		time_t creationTime,
344
		const ChatRoomId &chatRoomId
345
	) const {
Ronan's avatar
Ronan committed
346 347 348 349
		// TODO.
		return nullptr;
	}

350
	shared_ptr<EventLog> MainDbPrivate::selectConferenceChatMessageEvent (
351
		long long eventId,
352
		EventLog::Type type,
353
		time_t creationTime,
354
		const ChatRoomId &chatRoomId
355
	) const {
Ronan's avatar
Ronan committed
356 357 358 359
		L_Q();

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

360
		shared_ptr<ChatRoom> chatRoom = core->findChatRoom(chatRoomId);
Ronan's avatar
Ronan committed
361 362 363 364 365
		if (!chatRoom)
			return nullptr;

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

366
		// 1 - Fetch chat message.
367
		shared_ptr<ChatMessage> chatMessage;
368
		{
369 370
			string fromSipAddress;
			string toSipAddress;
371 372 373

			tm messageTime;

374
			string imdnMessageId;
375

376 377 378 379 380
			int state;
			int direction;
			int isSecured;

			soci::session *session = dbSession.getBackendSession<soci::session>();
381
			*session << "SELECT from_sip_address.value, to_sip_address.value, time, imdn_message_id, state, direction, is_secured"
382 383
				"  FROM event, conference_chat_message_event, sip_address AS from_sip_address,"
				"  sip_address AS to_sip_address"
384 385
				"  WHERE event_id = :eventId"
				"  AND event_id = event.id"
386 387
				"  AND from_sip_address_id = from_sip_address.id"
				"  AND to_sip_address_id = to_sip_address.id", soci::into(fromSipAddress), soci::into(toSipAddress),
388 389
				soci::into(messageTime), soci::into(imdnMessageId), soci::into(state), soci::into(direction),
				soci::into(isSecured), soci::use(eventId);
390

391 392 393 394
			chatMessage = shared_ptr<ChatMessage>(new ChatMessage(
				chatRoom,
				static_cast<ChatMessage::Direction>(direction)
			));
395 396 397
			chatMessage->getPrivate()->setState(static_cast<ChatMessage::State>(state), true);
			chatMessage->setIsSecured(static_cast<bool>(isSecured));

398 399
			chatMessage->getPrivate()->forceFromAddress(IdentityAddress(fromSipAddress));
			chatMessage->getPrivate()->forceToAddress(IdentityAddress(toSipAddress));
400 401

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

404 405 406
		// 2 - Fetch contents.
		{
			soci::session *session = dbSession.getBackendSession<soci::session>();
407
			const string query = "SELECT chat_message_content.id, content_type.id, content_type.value, body FROM chat_message_content, content_type"
408 409 410
				"  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) {
411
				ContentType contentType(row.get<string>(2));
412 413 414 415 416 417 418 419 420 421 422 423 424 425
				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
426
						"  WHERE chat_message_content_id = :contentId",
Ronan's avatar
Ronan committed
427
						soci::into(name), soci::into(size), soci::into(path), soci::use(contentId);
428 429 430 431 432 433 434 435

					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
436
					content = new Content();
437 438 439

				content->setContentType(contentType);
				content->setBody(row.get<string>(2));
440
				chatMessage->addContent(*content);
441
			}
Ronan's avatar
Ronan committed
442
		}
443 444 445

		// TODO: Use cache.
		return make_shared<ConferenceChatMessageEvent>(
446
			creationTime,
447 448
			chatMessage
		);
Ronan's avatar
Ronan committed
449 450
	}

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

		soci::session *session = dbSession.getBackendSession<soci::session>();
461 462
		*session << "SELECT notify_id, participant_address.value"
			"  FROM conference_notified_event, conference_participant_event, sip_address as participant_address"
463
			"  WHERE conference_participant_event.event_id = :eventId"
Ronan's avatar
Ronan committed
464
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
465
			"    AND participant_address.id = participant_sip_address_id",
466 467 468 469
			soci::into(notifyId), soci::into(participantAddress), soci::use(eventId);

		return make_shared<ConferenceParticipantEvent>(
			type,
470
			creationTime,
471
			chatRoomId,
472
			notifyId,
473
			IdentityAddress(participantAddress)
474
		);
Ronan's avatar
Ronan committed
475 476
	}

477
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
478
		long long eventId,
479
		EventLog::Type type,
480
		time_t creationTime,
481
		const ChatRoomId &chatRoomId
482
	) const {
483 484
		unsigned int notifyId;
		string participantAddress;
485
		string deviceAddress;
486 487

		soci::session *session = dbSession.getBackendSession<soci::session>();
488
		*session << "SELECT notify_id, participant_address.value, device_address.value"
489
			"  FROM conference_notified_event, conference_participant_event, conference_participant_device_event,"
490
			"    sip_address AS participant_address, sip_address AS device_address"
491 492
			"  WHERE conference_participant_device_event.event_id = :eventId"
			"    AND conference_participant_event.event_id = conference_participant_device_event.event_id"
493
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
494 495
			"    AND participant_address.id = participant_sip_address_id"
			"    AND device_address.id = device_sip_address_id",
496
			soci::into(notifyId), soci::into(participantAddress), soci::into(deviceAddress), soci::use(eventId);
497 498 499

		return make_shared<ConferenceParticipantDeviceEvent>(
			type,
500
			creationTime,
501
			chatRoomId,
502
			notifyId,
503 504
			IdentityAddress(participantAddress),
			IdentityAddress(deviceAddress)
505
		);
Ronan's avatar
Ronan committed
506 507
	}

508
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
509
		long long eventId,
510
		EventLog::Type type,
511
		time_t creationTime,
512
		const ChatRoomId &chatRoomId
513 514 515 516
	) const {
		unsigned int notifyId;
		string subject;

517 518 519 520 521 522
		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);
523 524

		return make_shared<ConferenceSubjectEvent>(
525
			creationTime,
526
			chatRoomId,
527 528 529
			notifyId,
			subject
		);
Ronan's avatar
Ronan committed
530 531
	}

532 533
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
534
	long long MainDbPrivate::insertEvent (const shared_ptr<EventLog> &eventLog) {
535 536 537
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

538
		*session << "INSERT INTO event (type, creation_time) VALUES (:type, :creationTime)",
539 540
			soci::use(static_cast<int>(eventLog->getType())),
			soci::use(Utils::getTimeTAsTm(eventLog->getCreationTime()));
541
		return q->getLastInsertId();
542 543
	}

Ronan's avatar
Ronan committed
544
	long long MainDbPrivate::insertConferenceEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
545 546 547 548 549 550 551 552 553 554 555 556 557
		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);
558

559 560 561 562
			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);

563
			*session << "UPDATE chat_room SET last_update_time = :lastUpdateTime"
564 565 566
				"  WHERE id = :chatRoomId", soci::use(Utils::getTimeTAsTm(eventLog->getCreationTime())),
				soci::use(curChatRoomId);
		}
567 568 569 570 571 572 573

		if (chatRoomId)
			*chatRoomId = curChatRoomId;

		return eventId;
	}

Ronan's avatar
Ronan committed
574
	long long MainDbPrivate::insertConferenceCallEvent (const shared_ptr<EventLog> &eventLog) {
575 576 577 578
		// TODO.
		return 0;
	}

Ronan's avatar
Ronan committed
579 580
	long long MainDbPrivate::insertConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
581 582 583 584 585 586
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

587
		long long eventId = insertConferenceEvent(eventLog);
588 589
		if (eventId < 0)
			return -1;
590

591 592
		long long fromSipAddressId = insertSipAddress(chatMessage->getFromAddress().asString());
		long long toSipAddressId = insertSipAddress(chatMessage->getToAddress().asString());
Ronan's avatar
Ronan committed
593

594
		soci::session *session = dbSession.getBackendSession<soci::session>();
595
		*session << "INSERT INTO conference_chat_message_event ("
596
			"  event_id, from_sip_address_id, to_sip_address_id,"
597
			"  time, state, direction, imdn_message_id, is_secured"
598 599
			") VALUES ("
			"  :eventId, :localSipaddressId, :remoteSipaddressId,"
600
			"  :time, :state, :direction, :imdnMessageId, :isSecured"
601
			")", soci::use(eventId), soci::use(fromSipAddressId), soci::use(toSipAddressId),
602 603 604
			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);
605

606
		for (const Content *content : chatMessage->getContents())
607
			insertContent(eventId, *content);
608

609 610 611
		return eventId;
	}

612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632
	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
	}

633 634 635 636 637 638
	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
639
		unsigned int lastNotifyId = static_pointer_cast<ConferenceNotifiedEvent>(eventLog)->getNotifyId();
640 641 642

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
643 644
			"  VALUES (:eventId, :notifyId)", soci::use(eventId), soci::use(lastNotifyId);
		*session << "UPDATE chat_room SET last_notify_id = :lastNotifyId WHERE peer_sip_address_id = :chatRoomId",
645 646 647 648
			soci::use(lastNotifyId), soci::use(curChatRoomId);

		if (chatRoomId)
			*chatRoomId = curChatRoomId;
649

650 651 652
		return eventId;
	}

Ronan's avatar
Ronan committed
653
	long long MainDbPrivate::insertConferenceParticipantEvent (const shared_ptr<EventLog> &eventLog) {
654
		long long eventId = insertConferenceNotifiedEvent(eventLog);
655 656 657
		if (eventId < 0)
			return -1;

658
		long long participantAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
659
			static_pointer_cast<ConferenceParticipantEvent>(eventLog)->getParticipantAddress().asString()
660 661
		);

662
		soci::session *session = dbSession.getBackendSession<soci::session>();
663
		*session << "INSERT INTO conference_participant_event (event_id, participant_sip_address_id)"
664
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
665

666 667 668
		return eventId;
	}

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

		long long deviceAddressId = insertSipAddress(
			static_pointer_cast<ConferenceParticipantDeviceEvent>(eventLog)->getDeviceAddress().asString()
676 677 678
		);

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

682
		return eventId;
683 684
	}

Ronan's avatar
Ronan committed
685
	long long MainDbPrivate::insertConferenceSubjectEvent (const shared_ptr<EventLog> &eventLog) {
686 687 688 689 690 691
		long long chatRoomId;
		long long eventId = insertConferenceNotifiedEvent(eventLog, &chatRoomId);
		if (eventId < 0)
			return -1;

		const string &subject = static_pointer_cast<ConferenceSubjectEvent>(eventLog)->getSubject();
692 693

		soci::session *session = dbSession.getBackendSession<soci::session>();
694
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
695 696 697 698
			"  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);
699

700
		return eventId;
701 702
	}

703 704 705 706 707 708 709 710 711 712 713 714 715
// -----------------------------------------------------------------------------

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

716
	void MainDbPrivate::invalidConferenceEventsFromQuery (const string &query, long long chatRoomId) {
717 718 719
		L_Q();

		soci::session *session = dbSession.getBackendSession<soci::session>();
720
		soci::rowset<soci::row> rows = (session->prepare << query, soci::use(chatRoomId));
721 722 723 724 725 726 727 728 729 730 731 732
		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();
			}
		}
	}

733
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
734

735
	void MainDb::init () {
736
		L_D();
737 738

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

Ronan's avatar
Ronan committed
741
		*session <<
742
			"CREATE TABLE IF NOT EXISTS sip_address ("
Erwan Croze's avatar
Erwan Croze committed
743
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
744
			"  value VARCHAR(255) UNIQUE NOT NULL"
745
			") " + charset;
746

747
		*session <<
748
			"CREATE TABLE IF NOT EXISTS content_type ("
Erwan Croze's avatar
Erwan Croze committed
749
			"  id" + primaryKeyStr("SMALLINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
750
			"  value VARCHAR(255) UNIQUE NOT NULL"
751
			") " + charset;
752

Ronan's avatar
Ronan committed
753
		*session <<
754
			"CREATE TABLE IF NOT EXISTS event ("
Erwan Croze's avatar
Erwan Croze committed
755
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
756
			"  type TINYINT UNSIGNED NOT NULL,"
757
			"  creation_time DATE NOT NULL"
758
			") " + charset;
759

Ronan's avatar
Ronan committed
760
		*session <<
761
			"CREATE TABLE IF NOT EXISTS chat_room ("
762 763
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","

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

767
			"  local_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + " NOT NULL,"
768

769 770
			// Dialog creation time.
			"  creation_time DATE NOT NULL,"
771

772 773
			// Last event time (call, message...).
			"  last_update_time DATE NOT NULL,"
774

775
			// ConferenceChatRoom, BasicChatRoom, RTT...
776
			"  capabilities TINYINT UNSIGNED NOT NULL,"
777

778 779 780
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
781
			"  last_notify_id INT UNSIGNED,"
782

783 784
			"  UNIQUE (peer_sip_address_id, local_sip_address_id),"

785
			"  FOREIGN KEY (peer_sip_address_id)"
786
			"    REFERENCES sip_address(id)"
787
			"    ON DELETE CASCADE,"
788 789 790
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
791
			") " + charset;
792