main-db.cpp 95.8 KB
Newer Older
Ronan's avatar
Ronan committed
1
/*
2
 * main-db.cpp
3
 * Copyright (C) 2010-2018 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 <ctime>
21

22
#include "linphone/utils/algorithm.h"
Ronan's avatar
Ronan committed
23
#include "linphone/utils/static-string.h"
24

Ronan's avatar
Ronan committed
25
#include "chat/chat-message/chat-message-p.h"
26
#include "chat/chat-room/chat-room-p.h"
27
#include "chat/chat-room/client-group-chat-room.h"
28
#include "chat/chat-room/server-group-chat-room.h"
29
#include "conference/participant-device.h"
30
#include "conference/participant-p.h"
31
#include "core/core-p.h"
32
#include "event-log/event-log-p.h"
33
#include "event-log/events.h"
34
#include "main-db-key-p.h"
Ronan's avatar
Ronan committed
35

36 37
#include "internal/db-transaction.h"
#include "internal/statements.h"
38

Ronan's avatar
Ronan committed
39 40
// =============================================================================

41 42 43 44 45
// See: http://soci.sourceforge.net/doc/3.2/exchange.html
// Part: Object lifetime and immutability

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

46 47
using namespace std;

Ronan's avatar
Ronan committed
48 49
LINPHONE_BEGIN_NAMESPACE

50
namespace {
51
	constexpr unsigned int ModuleVersionEvents = makeVersion(1, 0, 4);
52 53 54
	constexpr unsigned int ModuleVersionFriends = makeVersion(1, 0, 0);
	constexpr unsigned int ModuleVersionLegacyFriendsImport = makeVersion(1, 0, 0);
	constexpr unsigned int ModuleVersionLegacyHistoryImport = makeVersion(1, 0, 0);
55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82

	constexpr int LegacyFriendListColId = 0;
	constexpr int LegacyFriendListColName = 1;
	constexpr int LegacyFriendListColRlsUri = 2;
	constexpr int LegacyFriendListColSyncUri = 3;
	constexpr int LegacyFriendListColRevision = 4;

	constexpr int LegacyFriendColFriendListId = 1;
	constexpr int LegacyFriendColSipAddress = 2;
	constexpr int LegacyFriendColSubscribePolicy = 3;
	constexpr int LegacyFriendColSendSubscribe = 4;
	constexpr int LegacyFriendColRefKey = 5;
	constexpr int LegacyFriendColVCard = 6;
	constexpr int LegacyFriendColVCardEtag = 7;
	constexpr int LegacyFriendColVCardSyncUri = 8;
	constexpr int LegacyFriendColPresenceReceived = 9;

	constexpr int LegacyMessageColLocalAddress = 1;
	constexpr int LegacyMessageColRemoteAddress = 2;
	constexpr int LegacyMessageColDirection = 3;
	constexpr int LegacyMessageColText = 4;
	constexpr int LegacyMessageColState = 7;
	constexpr int LegacyMessageColUrl = 8;
	constexpr int LegacyMessageColDate = 9;
	constexpr int LegacyMessageColAppData = 10;
	constexpr int LegacyMessageColContentId = 11;
	constexpr int LegacyMessageColContentType = 13;
	constexpr int LegacyMessageColIsSecured = 14;
83 84
}

85 86
// -----------------------------------------------------------------------------
// soci helpers.
Ronan's avatar
Ronan committed
87 88
// -----------------------------------------------------------------------------

89 90 91 92 93
static inline vector<char> blobToVector (soci::blob &in) {
	size_t len = in.get_len();
	if (!len)
		return vector<char>();
	vector<char> out(len);
94
	in.read(0, &out[0], len);
95 96 97 98 99 100 101 102 103 104 105 106 107 108 109
	return out;
}

static inline string blobToString (soci::blob &in) {
	vector<char> out = blobToVector(in);
	return string(out.begin(), out.end());
}

static constexpr string &blobToString (string &in) {
	return in;
}

template<typename T>
static T getValueFromRow (const soci::row &row, int index, bool &isNull) {
	isNull = false;
Ronan's avatar
Ronan committed
110

111 112 113 114 115 116
	if (row.get_indicator(size_t(index)) == soci::i_null) {
		isNull = true;
		return T();
	}
	return row.get<T>(size_t(index));
}
117

118
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
119
// Event filter tools.
120 121
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
122
// Some tools to build filters at compile time.
123 124 125 126 127 128 129 130 131 132 133 134
template<typename T>
struct EnumToSql {
	T first;
	const char *second;
};

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

Ronan's avatar
Ronan committed
136 137 138 139 140
template<EventLog::Type ...Type>
struct SqlEventFilterBuilder {};

template<EventLog::Type Type, EventLog::Type... List>
struct SqlEventFilterBuilder<Type, List...> {
141 142 143
	static constexpr auto get () L_AUTO_RETURN(
		StaticIntString<int(Type)>() + "," + SqlEventFilterBuilder<List...>::get()
	);
Ronan's avatar
Ronan committed
144 145 146 147
};

template<EventLog::Type Type>
struct SqlEventFilterBuilder<Type> {
148
	static constexpr auto get () L_AUTO_RETURN(StaticIntString<int(Type)>());
149
};
150

Ronan's avatar
Ronan committed
151 152 153 154 155
// -----------------------------------------------------------------------------
// Event filters.
// -----------------------------------------------------------------------------

namespace {
156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184
	#ifdef _WIN32
		// TODO: Find a workaround to deal with StaticString concatenation!!!
		constexpr char ConferenceCallFilter[] = "3,4";
		constexpr char ConferenceChatMessageFilter[] = "5";
		constexpr char ConferenceInfoNoDeviceFilter[] = "1,2,6,7,8,9,12";
		constexpr char ConferenceInfoFilter[] = "1,2,6,7,8,9,10,11,12";
	#else
		constexpr auto ConferenceCallFilter = SqlEventFilterBuilder<
			EventLog::Type::ConferenceCallStart,
			EventLog::Type::ConferenceCallEnd
		>::get();

		constexpr auto ConferenceChatMessageFilter = SqlEventFilterBuilder<EventLog::Type::ConferenceChatMessage>::get();

		constexpr auto ConferenceInfoNoDeviceFilter = SqlEventFilterBuilder<
			EventLog::Type::ConferenceCreated,
			EventLog::Type::ConferenceTerminated,
			EventLog::Type::ConferenceParticipantAdded,
			EventLog::Type::ConferenceParticipantRemoved,
			EventLog::Type::ConferenceParticipantSetAdmin,
			EventLog::Type::ConferenceParticipantUnsetAdmin,
			EventLog::Type::ConferenceSubjectChanged
		>::get();

		constexpr auto ConferenceInfoFilter = ConferenceInfoNoDeviceFilter + "," + SqlEventFilterBuilder<
			EventLog::Type::ConferenceParticipantDeviceAdded,
			EventLog::Type::ConferenceParticipantDeviceRemoved
		>::get();
	#endif // ifdef _WIN32
Ronan's avatar
Ronan committed
185 186 187 188 189 190 191 192 193 194

	constexpr EnumToSql<MainDb::Filter> EventFilterToSql[] = {
		{ MainDb::ConferenceCallFilter, ConferenceCallFilter },
		{ MainDb::ConferenceChatMessageFilter, ConferenceChatMessageFilter },
		{ MainDb::ConferenceInfoNoDeviceFilter, ConferenceInfoNoDeviceFilter },
		{ MainDb::ConferenceInfoFilter, ConferenceInfoFilter }
	};
}

static const char *mapEventFilterToSql (MainDb::Filter filter) {
195
	return mapEnumToSql(
Ronan's avatar
Ronan committed
196
		EventFilterToSql, sizeof EventFilterToSql / sizeof EventFilterToSql[0], filter
197 198
	);
}
199

200 201
// -----------------------------------------------------------------------------

202 203 204 205 206
static string buildSqlEventFilter (
	const list<MainDb::Filter> &filters,
	MainDb::FilterMask mask,
	const string &condKeyWord = "WHERE"
) {
207
	L_ASSERT(findIf(filters, [](const MainDb::Filter &filter) { return filter == MainDb::NoFilter; }) == filters.cend());
208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230

	if (mask == MainDb::NoFilter)
		return "";

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

		if (isStart) {
			isStart = false;
			sql += " " + condKeyWord + " type IN (";
		} else
			sql += ", ";
		sql += mapEventFilterToSql(filter);
	}

	if (!isStart)
		sql += ") ";

	return sql;
}
231

232 233
// -----------------------------------------------------------------------------
// Misc helpers.
234 235
// -----------------------------------------------------------------------------

236 237 238 239 240 241
time_t MainDbPrivate::getTmAsTimeT (const tm &t) {
	tm t2 = t;
	t2.tm_isdst = 0;
	return Utils::getTmAsTimeT(t2);
}

242 243 244 245 246 247
shared_ptr<AbstractChatRoom> MainDbPrivate::findChatRoom (const ChatRoomId &chatRoomId) const {
	L_Q();
	shared_ptr<AbstractChatRoom> chatRoom = q->getCore()->findChatRoom(chatRoomId);
	if (!chatRoom)
		lError() << "Unable to find chat room: " << chatRoomId << ".";
	return chatRoom;
248 249
}

250 251 252
// -----------------------------------------------------------------------------
// Low level API.
// -----------------------------------------------------------------------------
253

254 255 256 257
long long MainDbPrivate::insertSipAddress (const string &sipAddress) {
	long long id = selectSipAddressId(sipAddress);
	if (id >= 0)
		return id;
258

259
	lInfo() << "Insert new sip address in database: `" << sipAddress << "`.";
260
	*dbSession.getBackendSession() << "INSERT INTO sip_address (value) VALUES (:sipAddress)", soci::use(sipAddress);
261
	return dbSession.getLastInsertId();
262
}
263

264
void MainDbPrivate::insertContent (long long chatMessageId, const Content &content) {
265
	soci::session *session = dbSession.getBackendSession();
266 267 268 269

	const long long &contentTypeId = insertContentType(content.getContentType().asString());
	const string &body = content.getBodyAsString();
	*session << "INSERT INTO chat_message_content (event_id, content_type_id, body) VALUES"
270
		" (:chatMessageId, :contentTypeId, :body)", soci::use(chatMessageId), soci::use(contentTypeId),
271 272
		soci::use(body);

273
	const long long &chatMessageContentId = dbSession.getLastInsertId();
274
	if (content.isFile()) {
275 276 277 278
		const FileContent &fileContent = static_cast<const FileContent &>(content);
		const string &name = fileContent.getFileName();
		const size_t &size = fileContent.getFileSize();
		const string &path = fileContent.getFilePath();
279
		*session << "INSERT INTO chat_message_file_content (chat_message_content_id, name, size, path) VALUES"
280 281 282 283 284 285 286 287 288
			" (:chatMessageContentId, :name, :size, :path)",
			soci::use(chatMessageContentId), soci::use(name), soci::use(size), soci::use(path);
	}

	for (const auto &appData : content.getAppDataMap())
		*session << "INSERT INTO chat_message_content_app_data (chat_message_content_id, name, data) VALUES"
			" (:chatMessageContentId, :name, :data)",
			soci::use(chatMessageContentId), soci::use(appData.first), soci::use(appData.second);
}
289

290
long long MainDbPrivate::insertContentType (const string &contentType) {
291
	soci::session *session = dbSession.getBackendSession();
292

293 294 295 296
	long long id;
	*session << "SELECT id FROM content_type WHERE value = :contentType", soci::use(contentType), soci::into(id);
	if (session->got_data())
		return id;
297

298 299
	lInfo() << "Insert new content type in database: `" << contentType << "`.";
	*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
300
	return dbSession.getLastInsertId();
301 302 303 304 305 306 307
}

long long MainDbPrivate::insertOrUpdateImportedBasicChatRoom (
	long long peerSipAddressId,
	long long localSipAddressId,
	const tm &creationTime
) {
308
	soci::session *session = dbSession.getBackendSession();
309

310 311 312 313 314
	long long id = selectChatRoomId(peerSipAddressId, localSipAddressId);
	if (id >= 0) {
		*session << "UPDATE chat_room SET last_update_time = :lastUpdateTime WHERE id = :id",
			soci::use(creationTime), soci::use(id);
		return id;
315 316
	}

Benjamin REIS's avatar
Benjamin REIS committed
317
	const int capabilities = ChatRoom::CapabilitiesMask(
318 319 320 321 322 323 324 325 326
		{ ChatRoom::Capabilities::Basic, ChatRoom::Capabilities::Migratable }
	);
	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);
327

328
	return dbSession.getLastInsertId();
329
}
330

331
long long MainDbPrivate::insertChatRoom (const shared_ptr<AbstractChatRoom> &chatRoom, unsigned int notifyId) {
332 333 334
	const ChatRoomId &chatRoomId = chatRoom->getChatRoomId();
	const long long &peerSipAddressId = insertSipAddress(chatRoomId.getPeerAddress().asString());
	const long long &localSipAddressId = insertSipAddress(chatRoomId.getLocalAddress().asString());
335

336
	long long id = selectChatRoomId(peerSipAddressId, localSipAddressId);
337 338 339 340
	if (id >= 0) {
		// The chat room is already stored in DB, but still update the notify id that might have changed
		*dbSession.getBackendSession() << "UPDATE chat_room SET last_notify_id = :lastNotifyId WHERE id = :chatRoomId",
			soci::use(notifyId), soci::use(id);
341
		return id;
342
	}
343

344
	lInfo() << "Insert new chat room in database: " << chatRoomId << ".";
345

346
	const tm &creationTime = Utils::getTimeTAsTm(chatRoom->getCreationTime());
347
	const tm &lastUpdateTime = Utils::getTimeTAsTm(chatRoom->getLastUpdateTime());
348

349
	// Remove capabilities like `Proxy`.
350
	const int &capabilities = chatRoom->getCapabilities() & ~ChatRoom::CapabilitiesMask(ChatRoom::Capabilities::Proxy);
351

352 353
	const string &subject = chatRoom->getSubject();
	const int &flags = chatRoom->hasBeenLeft();
354
	*dbSession.getBackendSession() << "INSERT INTO chat_room ("
355 356 357 358 359 360 361 362
		"  peer_sip_address_id, local_sip_address_id, creation_time,"
		"  last_update_time, capabilities, subject, flags, last_notify_id"
		") VALUES ("
		"  :peerSipAddressId, :localSipAddressId, :creationTime,"
		"  :lastUpdateTime, :capabilities, :subject, :flags, :lastNotifyId"
		")",
		soci::use(peerSipAddressId), soci::use(localSipAddressId), soci::use(creationTime),
		soci::use(lastUpdateTime), soci::use(capabilities), soci::use(subject), soci::use(flags), soci::use(notifyId);
363

364
	id = dbSession.getLastInsertId();
365

366 367 368 369 370 371 372 373 374 375 376
	// Do not add 'me' when creating a server-group-chat-room.
	if (chatRoomId.getLocalAddress() != chatRoomId.getPeerAddress()) {
		shared_ptr<Participant> me = chatRoom->getMe();
		long long meId = insertChatRoomParticipant(
			id,
			insertSipAddress(me->getAddress().asString()),
			me->isAdmin()
		);
		for (const auto &device : me->getPrivate()->getDevices())
			insertChatRoomParticipantDevice(meId, insertSipAddress(device->getAddress().asString()));
	}
377

378 379 380 381 382 383 384 385
	for (const auto &participant : chatRoom->getParticipants()) {
		long long participantId = insertChatRoomParticipant(
			id,
			insertSipAddress(participant->getAddress().asString()),
			participant->isAdmin()
		);
		for (const auto &device : participant->getPrivate()->getDevices())
			insertChatRoomParticipantDevice(participantId, insertSipAddress(device->getAddress().asString()));
386 387
	}

388 389
	return id;
}
390

391 392 393 394 395
long long MainDbPrivate::insertChatRoomParticipant (
	long long chatRoomId,
	long long participantSipAddressId,
	bool isAdmin
) {
396
	soci::session *session = dbSession.getBackendSession();
397 398 399 400 401 402 403
	long long id = selectChatRoomParticipantId(chatRoomId, participantSipAddressId);
	if (id >= 0) {
		// See: https://stackoverflow.com/a/15299655 (cast to reference)
		*session << "UPDATE chat_room_participant SET is_admin = :isAdmin WHERE id = :id",
			soci::use(static_cast<const int &>(isAdmin)), soci::use(id);
		return id;
	}
404

405 406 407
	*session << "INSERT INTO chat_room_participant (chat_room_id, participant_sip_address_id, is_admin)"
		" VALUES (:chatRoomId, :participantSipAddressId, :isAdmin)",
		soci::use(chatRoomId), soci::use(participantSipAddressId), soci::use(static_cast<const int &>(isAdmin));
408

409
	return dbSession.getLastInsertId();
410
}
411

412 413 414 415
void MainDbPrivate::insertChatRoomParticipantDevice (
	long long participantId,
	long long participantDeviceSipAddressId
) {
416
	soci::session *session = dbSession.getBackendSession();
417 418 419 420 421 422 423 424 425 426 427 428
	long long count;
	*session << "SELECT COUNT(*) FROM chat_room_participant_device"
		" WHERE chat_room_participant_id = :participantId"
		" AND participant_device_sip_address_id = :participantDeviceSipAddressId",
		soci::into(count), soci::use(participantId), soci::use(participantDeviceSipAddressId);
	if (count)
		return;

	*session << "INSERT INTO chat_room_participant_device (chat_room_participant_id, participant_device_sip_address_id)"
		" VALUES (:participantId, :participantDeviceSipAddressId)",
		soci::use(participantId), soci::use(participantDeviceSipAddressId);
}
429

430 431 432 433 434 435
void MainDbPrivate::insertChatMessageParticipant (long long chatMessageId, long long sipAddressId, int state, time_t stateChangeTime) {
	const tm &stateChangeTm = Utils::getTimeTAsTm(stateChangeTime);
	*dbSession.getBackendSession() <<
		"INSERT INTO chat_message_participant (event_id, participant_sip_address_id, state, state_change_time)"
		" VALUES (:chatMessageId, :sipAddressId, :state, :stateChangeTm)",
		soci::use(chatMessageId), soci::use(sipAddressId), soci::use(state), soci::use(stateChangeTm);
436
}
437

438 439
// -----------------------------------------------------------------------------

440 441
long long MainDbPrivate::selectSipAddressId (const string &sipAddress) const {
	long long id;
442

443
	soci::session *session = dbSession.getBackendSession();
444
	*session << Statements::get(Statements::SelectSipAddressId),
445
		soci::use(sipAddress), soci::into(id);
446

447
	return session->got_data() ? id : -1;
448
}
449

450 451
long long MainDbPrivate::selectChatRoomId (long long peerSipAddressId, long long localSipAddressId) const {
	long long id;
452

453
	soci::session *session = dbSession.getBackendSession();
454
	*session << Statements::get(Statements::SelectChatRoomId),
455
		soci::use(peerSipAddressId), soci::use(localSipAddressId), soci::into(id);
456

457
	return session->got_data() ? id : -1;
458
}
459

460 461 462 463
long long MainDbPrivate::selectChatRoomId (const ChatRoomId &chatRoomId) const {
	long long peerSipAddressId = selectSipAddressId(chatRoomId.getPeerAddress().asString());
	if (peerSipAddressId < 0)
		return -1;
464

465 466 467
	long long localSipAddressId = selectSipAddressId(chatRoomId.getLocalAddress().asString());
	if (localSipAddressId < 0)
		return -1;
468

469 470
	return selectChatRoomId(peerSipAddressId, localSipAddressId);
}
471

472 473
long long MainDbPrivate::selectChatRoomParticipantId (long long chatRoomId, long long participantSipAddressId) const {
	long long id;
474

475
	soci::session *session = dbSession.getBackendSession();
476
	*session << Statements::get(Statements::SelectChatRoomParticipantId),
477
		soci::use(chatRoomId), soci::use(participantSipAddressId), soci::into(id);
478

479
	return session->got_data() ? id : -1;
480
}
481

482 483
long long MainDbPrivate::selectOneToOneChatRoomId (long long sipAddressIdA, long long sipAddressIdB) const {
	long long id;
484

485
	soci::session *session = dbSession.getBackendSession();
486
	*session << Statements::get(Statements::SelectOneToOneChatRoomId),
487 488
		soci::use(sipAddressIdA), soci::use(sipAddressIdB), soci::use(sipAddressIdA), soci::use(sipAddressIdB),
		soci::into(id);
489

490
	return session->got_data() ? id : -1;
491 492
}

493 494
// -----------------------------------------------------------------------------

495 496 497
void MainDbPrivate::deleteContents (long long chatMessageId) {
	*dbSession.getBackendSession() << "DELETE FROM chat_message_content WHERE event_id = :chatMessageId",
		soci::use(chatMessageId);
498
}
499

500
void MainDbPrivate::deleteChatRoomParticipant (long long chatRoomId, long long participantSipAddressId) {
501 502
	*dbSession.getBackendSession() << "DELETE FROM chat_room_participant"
		" WHERE chat_room_id = :chatRoomId AND participant_sip_address_id = :participantSipAddressId",
503 504
		soci::use(chatRoomId), soci::use(participantSipAddressId);
}
505

506 507 508 509
void MainDbPrivate::deleteChatRoomParticipantDevice (
	long long participantId,
	long long participantDeviceSipAddressId
) {
510 511 512
	*dbSession.getBackendSession() << "DELETE FROM chat_room_participant_device"
		" WHERE chat_room_participant_id = :participantId"
		" AND participant_device_sip_address_id = :participantDeviceSipAddressId",
513 514
		soci::use(participantId), soci::use(participantDeviceSipAddressId);
}
515

516 517
// -----------------------------------------------------------------------------
// Events API.
Ronan's avatar
Ronan committed
518 519
// -----------------------------------------------------------------------------

520
shared_ptr<EventLog> MainDbPrivate::selectGenericConferenceEvent (
521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541
	const shared_ptr<AbstractChatRoom> &chatRoom,
	const soci::row &row
) const {
	EventLog::Type type = EventLog::Type(row.get<int>(1));
	if (type == EventLog::Type::ConferenceChatMessage) {
		long long eventId = getConferenceEventIdFromRow(row);
		shared_ptr<EventLog> eventLog = getEventFromCache(eventId);
		if (!eventLog) {
			eventLog = selectConferenceChatMessageEvent(chatRoom, type, row);
			if (eventLog)
				cache(eventLog, eventId);
		}
		return eventLog;
	}

	return selectGenericConferenceNotifiedEvent(chatRoom->getChatRoomId(), row);
}

shared_ptr<EventLog> MainDbPrivate::selectGenericConferenceNotifiedEvent (
	const ChatRoomId &chatRoomId,
	const soci::row &row
542
) const {
543 544 545 546
	long long eventId = getConferenceEventIdFromRow(row);
	shared_ptr<EventLog> eventLog = getEventFromCache(eventId);
	if (eventLog)
		return eventLog;
547

548
	EventLog::Type type = EventLog::Type(row.get<int>(1));
549 550
	switch (type) {
		case EventLog::Type::None:
551
		case EventLog::Type::ConferenceChatMessage:
552
			return nullptr;
553

554 555
		case EventLog::Type::ConferenceCreated:
		case EventLog::Type::ConferenceTerminated:
556
			eventLog = selectConferenceEvent(chatRoomId, type, row);
557
			break;
Ronan's avatar
Ronan committed
558

559 560
		case EventLog::Type::ConferenceCallStart:
		case EventLog::Type::ConferenceCallEnd:
561
			eventLog = selectConferenceCallEvent(chatRoomId, type, row);
562
			break;
Ronan's avatar
Ronan committed
563

564 565 566 567
		case EventLog::Type::ConferenceParticipantAdded:
		case EventLog::Type::ConferenceParticipantRemoved:
		case EventLog::Type::ConferenceParticipantSetAdmin:
		case EventLog::Type::ConferenceParticipantUnsetAdmin:
568
			eventLog = selectConferenceParticipantEvent(chatRoomId, type, row);
569
			break;
Ronan's avatar
Ronan committed
570

571 572
		case EventLog::Type::ConferenceParticipantDeviceAdded:
		case EventLog::Type::ConferenceParticipantDeviceRemoved:
573
			eventLog = selectConferenceParticipantDeviceEvent(chatRoomId, type, row);
574
			break;
575

576
		case EventLog::Type::ConferenceSubjectChanged:
577
			eventLog = selectConferenceSubjectEvent(chatRoomId, type, row);
578
			break;
579 580
	}

581 582
	if (eventLog)
		cache(eventLog, eventId);
Ronan's avatar
Ronan committed
583

584 585
	return eventLog;
}
Ronan's avatar
Ronan committed
586

587
shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
588
	const ChatRoomId &chatRoomId,
589
	EventLog::Type type,
590
	const soci::row &row
591 592 593
) const {
	return make_shared<ConferenceEvent>(
		type,
594
		getConferenceEventCreationTimeFromRow(row),
595 596 597
		chatRoomId
	);
}
598

599
shared_ptr<EventLog> MainDbPrivate::selectConferenceCallEvent (
600
	const ChatRoomId &chatRoomId,
601
	EventLog::Type type,
602
	const soci::row &row
603 604 605 606
) const {
	// TODO.
	return nullptr;
}
Ronan's avatar
Ronan committed
607

608
shared_ptr<EventLog> MainDbPrivate::selectConferenceChatMessageEvent (
609
	const shared_ptr<AbstractChatRoom> &chatRoom,
610
	EventLog::Type type,
611
	const soci::row &row
612
) const {
613
	long long eventId = getConferenceEventIdFromRow(row);
614 615
	shared_ptr<ChatMessage> chatMessage = getChatMessageFromCache(eventId);
	if (!chatMessage) {
616 617
		chatMessage = shared_ptr<ChatMessage>(new ChatMessage(
			chatRoom,
618
			ChatMessage::Direction(row.get<int>(8))
619
		));
620
		chatMessage->setIsSecured(!!row.get<int>(9));
Ronan's avatar
Ronan committed
621

622
		ChatMessagePrivate *dChatMessage = chatMessage->getPrivate();
623
		ChatMessage::State messageState = ChatMessage::State(row.get<int>(7));
624 625 626 627
		// This is necessary if linphone has crashed while sending a message. It will set the correct state so the user can resend it.
		if (messageState == ChatMessage::State::Idle || messageState == ChatMessage::State::InProgress)
			messageState = ChatMessage::State::NotDelivered;
		dChatMessage->setState(messageState, true);
628

629 630
		dChatMessage->forceFromAddress(IdentityAddress(row.get<string>(3)));
		dChatMessage->forceToAddress(IdentityAddress(row.get<string>(4)));
Ronan's avatar
Ronan committed
631

632
		dChatMessage->setTime(MainDbPrivate::getTmAsTimeT(row.get<tm>(5)));
633
		dChatMessage->setImdnMessageId(row.get<string>(6));
634 635
		dChatMessage->setPositiveDeliveryNotificationRequired(!!row.get<int>(14));
		dChatMessage->setDisplayNotificationRequired(!!row.get<int>(15));
636

637 638
		dChatMessage->markContentsAsNotLoaded();
		dChatMessage->setIsReadOnly(true);
639

640
		cache(chatMessage, eventId);
Ronan's avatar
Ronan committed
641 642
	}

643
	return make_shared<ConferenceChatMessageEvent>(
644
		getConferenceEventCreationTimeFromRow(row),
645 646 647
		chatMessage
	);
}
648

649
shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
650
	const ChatRoomId &chatRoomId,
651
	EventLog::Type type,
652
	const soci::row &row
653 654 655
) const {
	return make_shared<ConferenceParticipantEvent>(
		type,
656
		getConferenceEventCreationTimeFromRow(row),
657
		chatRoomId,
658 659
		getConferenceEventNotifyIdFromRow(row),
		IdentityAddress(row.get<string>(12))
660 661
	);
}
662

663
shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
664
	const ChatRoomId &chatRoomId,
665
	EventLog::Type type,
666
	const soci::row &row
667 668 669
) const {
	return make_shared<ConferenceParticipantDeviceEvent>(
		type,
670
		getConferenceEventCreationTimeFromRow(row),
671
		chatRoomId,
672 673 674
		getConferenceEventNotifyIdFromRow(row),
		IdentityAddress(row.get<string>(12)),
		IdentityAddress(row.get<string>(11))
675 676
	);
}
677

678
shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
679
	const ChatRoomId &chatRoomId,
680
	EventLog::Type type,
681
	const soci::row &row
682 683
) const {
	return make_shared<ConferenceSubjectEvent>(
684
		getConferenceEventCreationTimeFromRow(row),
685
		chatRoomId,
686 687
		getConferenceEventNotifyIdFromRow(row),
		row.get<string>(13)
688 689
	);
}
690

691
// -----------------------------------------------------------------------------
692

693
long long MainDbPrivate::insertEvent (const shared_ptr<EventLog> &eventLog) {
694
	const int &type = int(eventLog->getType());
695
	const tm &creationTime = Utils::getTimeTAsTm(eventLog->getCreationTime());
696 697
	*dbSession.getBackendSession() << "INSERT INTO event (type, creation_time) VALUES (:type, :creationTime)",
		soci::use(type), soci::use(creationTime);
698

699
	return dbSession.getLastInsertId();
700
}
701

702 703 704 705 706 707 708 709 710
long long MainDbPrivate::insertConferenceEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
	shared_ptr<ConferenceEvent> conferenceEvent = static_pointer_cast<ConferenceEvent>(eventLog);

	long long eventId = -1;
	const 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();
711
		lError() << "Unable to find chat room storage id of: " << chatRoomId << ".";
712 713
	} else {
		eventId = insertEvent(eventLog);
714

715
		soci::session *session = dbSession.getBackendSession();
716 717 718 719 720 721 722
		*session << "INSERT INTO conference_event (event_id, chat_room_id)"
			" VALUES (:eventId, :chatRoomId)", soci::use(eventId), soci::use(curChatRoomId);

		const tm &lastUpdateTime = Utils::getTimeTAsTm(eventLog->getCreationTime());
		*session << "UPDATE chat_room SET last_update_time = :lastUpdateTime"
			" WHERE id = :chatRoomId", soci::use(lastUpdateTime),
			soci::use(curChatRoomId);
723

724
		if (eventLog->getType() == EventLog::Type::ConferenceTerminated)
725
			*session << "UPDATE chat_room SET flags = 1, last_notify_id = 0 WHERE id = :chatRoomId", soci::use(curChatRoomId);
726 727
		else if (eventLog->getType() == EventLog::Type::ConferenceCreated)
			*session << "UPDATE chat_room SET flags = 0 WHERE id = :chatRoomId", soci::use(curChatRoomId);
728 729
	}

730 731 732 733 734
	if (chatRoomId)
		*chatRoomId = curChatRoomId;

	return eventId;
}
735

736 737 738 739
long long MainDbPrivate::insertConferenceCallEvent (const shared_ptr<EventLog> &eventLog) {
	// TODO.
	return 0;
}
740

741 742 743 744 745
long long MainDbPrivate::insertConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
	const long long &eventId = insertConferenceEvent(eventLog);
	if (eventId < 0)
		return -1;

746
	shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
747 748 749
	const long long &fromSipAddressId = insertSipAddress(chatMessage->getFromAddress().asString());
	const long long &toSipAddressId = insertSipAddress(chatMessage->getToAddress().asString());
	const tm &messageTime = Utils::getTimeTAsTm(chatMessage->getTime());
750 751
	const int &state = int(chatMessage->getState());
	const int &direction = int(chatMessage->getDirection());
752 753
	const string &imdnMessageId = chatMessage->getImdnMessageId();
	const int &isSecured = chatMessage->isSecured() ? 1 : 0;
754 755
	const int &deliveryNotificationRequired = chatMessage->getPrivate()->getPositiveDeliveryNotificationRequired();
	const int &displayNotificationRequired = chatMessage->getPrivate()->getDisplayNotificationRequired();
756

757
	*dbSession.getBackendSession() << "INSERT INTO conference_chat_message_event ("
758
		"  event_id, from_sip_address_id, to_sip_address_id,"
759 760
		"  time, state, direction, imdn_message_id, is_secured,"
		"  delivery_notification_required, display_notification_required"
761 762
		") VALUES ("
		"  :eventId, :localSipaddressId, :remoteSipaddressId,"
763 764
		"  :time, :state, :direction, :imdnMessageId, :isSecured,"
		"  :deliveryNotificationRequired, :displayNotificationRequired"
765 766
		")", soci::use(eventId), soci::use(fromSipAddressId), soci::use(toSipAddressId),
		soci::use(messageTime), soci::use(state), soci::use(direction),
767 768
		soci::use(imdnMessageId), soci::use(isSecured),
		soci::use(deliveryNotificationRequired), soci::use(displayNotificationRequired);
769 770 771 772

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

773
	for (const auto &participant : chatMessage->getChatRoom()->getParticipants()) {
774
		const long long &participantSipAddressId = selectSipAddressId(participant->getAddress().asString());
775
		insertChatMessageParticipant(eventId, participantSipAddressId, state, chatMessage->getTime());
776 777
	}

778 779 780 781 782
	return eventId;
}

void MainDbPrivate::updateConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
	shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
783

784 785 786
	const EventLogPrivate *dEventLog = eventLog->getPrivate();
	MainDbKeyPrivate *dEventKey = static_cast<MainDbKey &>(dEventLog->dbKey).getPrivate();
	const long long &eventId = dEventKey->storageId;
787

788
	const int &state = int(chatMessage->getState());
789
	const string &imdnMessageId = chatMessage->getImdnMessageId();
790
	*dbSession.getBackendSession() << "UPDATE conference_chat_message_event SET state = :state, imdn_message_id = :imdnMessageId"
791 792
		" WHERE event_id = :eventId",
		soci::use(state), soci::use(imdnMessageId), soci::use(eventId);
793

794 795 796
	deleteContents(eventId);
	for (const auto &content : chatMessage->getContents())
		insertContent(eventId, *content);
797 798 799 800 801 802 803

	if ((chatMessage->getDirection() == ChatMessage::Direction::Outgoing)
		&& ((chatMessage->getState() == ChatMessage::State::Delivered) || (chatMessage->getState() == ChatMessage::State::NotDelivered))
	) {
		for (const auto &participant : chatMessage->getChatRoom()->getParticipants())
			setChatMessageParticipantState(eventLog, participant->getAddress(), chatMessage->getState(), std::time(nullptr));
	}
804
}
805

806 807 808 809 810
long long MainDbPrivate::insertConferenceNotifiedEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
	long long curChatRoomId;
	const long long &eventId = insertConferenceEvent(eventLog, &curChatRoomId);
	if (eventId < 0)
		return -1;
811

812
	const unsigned int &lastNotifyId = static_pointer_cast<ConferenceNotifiedEvent>(eventLog)->getNotifyId();
813

814
	soci::session *session = dbSession.getBackendSession();
815 816 817 818
	*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
		" VALUES (:eventId, :notifyId)", soci::use(eventId), soci::use(lastNotifyId);
	*session << "UPDATE chat_room SET last_notify_id = :lastNotifyId WHERE id = :chatRoomId",
		soci::use(lastNotifyId), soci::use(curChatRoomId);
819

820 821
	if (chatRoomId)
		*chatRoomId = curChatRoomId;
822

823 824
	return eventId;
}
825

826 827 828 829 830 831 832 833
long long MainDbPrivate::insertConferenceParticipantEvent (
	const shared_ptr<EventLog> &eventLog,
	long long *chatRoomId
) {
	long long curChatRoomId;
	const long long &eventId = insertConferenceNotifiedEvent(eventLog, &curChatRoomId);
	if (eventId < 0)
		return -1;
834

835 836
	shared_ptr<ConferenceParticipantEvent> participantEvent =
		static_pointer_cast<ConferenceParticipantEvent>(eventLog);
837

838 839 840 841
	const long long &participantAddressId = insertSipAddress(
		participantEvent->getParticipantAddress().asString()
	);

842 843
	*dbSession.getBackendSession() << "INSERT INTO conference_participant_event (event_id, participant_sip_address_id)"
		" VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
844 845 846 847 848 849 850 851 852 853 854 855 856 857 858

	bool isAdmin = eventLog->getType() == EventLog::Type::ConferenceParticipantSetAdmin;
	switch (eventLog->getType()) {
		case EventLog::Type::ConferenceParticipantAdded:
		case EventLog::Type::ConferenceParticipantSetAdmin:
		case EventLog::Type::ConferenceParticipantUnsetAdmin:
			insertChatRoomParticipant(curChatRoomId, participantAddressId, isAdmin);
			break;

		case EventLog::Type::ConferenceParticipantRemoved:
			deleteChatRoomParticipant(curChatRoomId, participantAddressId);
			break;

		default:
			break;
859 860
	}

861 862
	if (chatRoomId)
		*chatRoomId = curChatRoomId;
863

864 865
	return eventId;
}
866

867 868 869 870 871
long long MainDbPrivate::insertConferenceParticipantDeviceEvent (const shared_ptr<EventLog> &eventLog) {
	long long chatRoomId;
	const long long &eventId = insertConferenceParticipantEvent(eventLog, &chatRoomId);
	if (eventId < 0)
		return -1;
872

873 874
	shared_ptr<ConferenceParticipantDeviceEvent> participantDeviceEvent =
		static_pointer_cast<ConferenceParticipantDeviceEvent>(eventLog);
875

876 877 878 879 880 881 882 883 884
	const string participantAddress = participantDeviceEvent->getParticipantAddress().asString();
	const long long &participantAddressId = selectSipAddressId(participantAddress);
	if (participantAddressId < 0) {
		lError() << "Unable to find sip address id of: `" << participantAddress << "`.";
		return -1;
	}
	const long long &participantId = selectChatRoomParticipantId(chatRoomId, participantAddressId);
	if (participantId < 0) {
		lError() << "Unable to find valid participant id in database with chat room id = " << chatRoomId <<
885
		" and participant address id = " << participantAddressId;
886 887 888 889 890
		return -1;
	}
	const long long &deviceAddressId = insertSipAddress(
		participantDeviceEvent->getDeviceAddress().asString()
	);
891

892
	*dbSession.getBackendSession() << "INSERT INTO conference_participant_device_event (event_id, device_sip_address_id)"
893
		" VALUES (:eventId, :deviceAddressId)", soci::use(eventId), soci::use(deviceAddressId);
894

895 896 897 898
	switch (eventLog->getType()) {
		case EventLog::Type::ConferenceParticipantDeviceAdded:
			insertChatRoomParticipantDevice(participantId, deviceAddressId);
			break;
899

900 901 902 903 904 905
		case EventLog::Type::ConferenceParticipantDeviceRemoved:
			deleteChatRoomParticipantDevice(participantId, deviceAddressId);
			break;

		default:
			break;
906 907
	}

908 909
	return eventId;
}
910

911 912 913 914 915
long long MainDbPrivate::insertConferenceSubjectEvent (const shared_ptr<EventLog> &eventLog) {
	long long chatRoomId;
	const long long &eventId = insertConferenceNotifiedEvent(eventLog, &chatRoomId);
	if (eventId < 0)
		return -1;
916

917
	const string &subject = static_pointer_cast<ConferenceSubjectEvent>(eventLog)->getSubject();
918

919
	soci::session *session = dbSession.getBackendSession();
920 921
	*session << "INSERT INTO conference_subject_event (event_id, subject)"
		" VALUES (:eventId, :subject)", soci::use(eventId), soci::use(subject);
922

923 924 925 926 927
	*session << "UPDATE chat_room SET subject = :subject"
		" WHERE id = :chatRoomId", soci::use(subject), soci::use(chatRoomId);

	return eventId;
}
928

929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948
void MainDbPrivate::setChatMessageParticipantState (
	const shared_ptr<EventLog> &eventLog,
	const IdentityAddress &participantAddress,
	ChatMessage::State state,
	time_t stateChangeTime
) {
	const EventLogPrivate *dEventLog = eventLog->getPrivate();
	MainDbKeyPrivate *dEventKey = static_cast<MainDbKey &>(dEventLog->dbKey).getPrivate();
	const long long &eventId = dEventKey->storageId;
	const long long &participantSipAddressId = selectSipAddressId(participantAddress.asString());
	int stateInt = static_cast<int>(state);
	const tm &stateChangeTm = Utils::getTimeTAsTm(stateChangeTime);

	*dbSession.getBackendSession() << "UPDATE chat_message_participant SET state = :state,"
		" state_change_time = :stateChangeTm"
		" WHERE event_id = :eventId AND participant_sip_address_id = :participantSipAddressId",
		soci::use(stateInt), soci::use(stateChangeTm), soci::use(eventId), soci::use(participantSipAddressId);
}


949 950
// -----------------------------------------------------------------------------
// Cache API.
951 952
// -----------------------------------------------------------------------------

953 954 955 956
shared_ptr<EventLog> MainDbPrivate::getEventFromCache (long long storageId) const {
	auto it = storageIdToEvent.find(storageId);
	if (it == storageIdToEvent.cend())
		return nullptr;
957

958 959 960 961
	shared_ptr<EventLog> eventLog = it->second.lock();
	L_ASSERT(eventLog);
	return eventLog;
}
962

963 964 965 966
shared_ptr<ChatMessage> MainDbPrivate::getChatMessageFromCache (long long storageId) const {
	auto it = storageIdToChatMessage.find(storageId);
	if (it == storageIdToChatMessage.cend())
		return nullptr;
967

968 969 970 971
	shared_ptr<ChatMessage> chatMessage = it->second.lock();
	L_ASSERT(chatMessage);
	return chatMessage;
}
972

973 974
void MainDbPrivate::cache (const shared_ptr<EventLog> &eventLog, long long storageId) const {
	L_Q();
975

976 977 978 979 980 981
	EventLogPrivate *dEventLog = eventLog->getPrivate();
	L_ASSERT(!dEventLog->dbKey.isValid());
	dEventLog->dbKey = MainDbEventKey(q->getCore(), storageId);
	storageIdToEvent[storageId] = eventLog;
	L_ASSERT(dEventLog->dbKey.isValid());
}
982

983 984
void MainDbPrivate::cache (const shared_ptr<ChatMessage> &chatMessage, long long storageId) const {
	L_Q();
985

986 987 988 989 990 991
	ChatMessagePrivate *dChatMessage = chatMessage->getPrivate();
	L_ASSERT(!dChatMessage->dbKey.isValid());
	dChatMessage->dbKey = MainDbChatMessageKey(q->getCore(), storageId);
	storageIdToChatMessage[storageId] = chatMessage;
	L_ASSERT(dChatMessage->dbKey.isValid());
}
992

993
void MainDbPrivate::invalidConferenceEventsFromQuery (const string &query, long long chatRoomId) {
994
	soci::rowset<soci::row> rows = (dbSession.getBackendSession()->prepare << query, soci::use(chatRoomId));
995
	for (const auto &row : rows) {
996
		long long eventId = dbSession.resolveId(row, 0);
997 998 999 1000 1001 1002 1003 1004 1005 1006 1007
		shared_ptr<EventLog> eventLog = getEventFromCache(eventId);
		if (eventLog) {
			const EventLogPrivate *dEventLog = eventLog->getPrivate();
			L_ASSERT(dEventLog->dbKey.isValid());
			dEventLog->dbKey = MainDbEventKey();
		}
		shared_ptr<ChatMessage> chatMessage = getChatMessageFromCache(eventId);
		if (chatMessage) {
			const ChatMessagePrivate *dChatMessage = chatMessage->getPrivate();
			L_ASSERT(dChatMessage->dbKey.isValid());
			dChatMessage->dbKey = MainDbChatMessageKey();
1008 1009
		}
	}
1010
}
1011

1012 1013
// -----------------------------------------------------------------------------
// Versions.
1014 1015
// -----------------------------------------------------------------------------

1016
unsigned int MainDbPrivate::getModuleVersion (const string &name) {
1017
	soci::session *session = dbSession.getBackendSession();
1018

1019 1020 1021 1022
	unsigned int version;
	*session << "SELECT version FROM db_module_version WHERE name = :name", soci::into(version), soci::use(name);
	return session->got_data() ? version : 0;
}
1023

1024 1025
void MainDbPrivate::updateModuleVersion (const string &name, unsigned int version) {
	unsigned int oldVersion = getModuleVersion(name);
1026
	if (version <= oldVersion)
1027
		return;
1028

1029
	soci::session *session = dbSession.getBackendSession();
1030 1031 1032
	*session << "REPLACE INTO db_module_version (name, version) VALUES (:name, :version)",
		soci::use(name), soci::use(version);
}
1033

1034
void MainDbPrivate::updateSchema () {
1035 1036
	L_Q();

1037 1038 1039
	soci::session *session = dbSession.getBackendSession();
	unsigned int version = getModuleVersion("events");

1040
	if (version < makeVersion(1, 0, 1))
1041
		*session << "ALTER TABLE chat_room_participant_device ADD COLUMN state TINYINT UNSIGNED DEFAULT 0";
1042 1043 1044 1045 1046
	if (version < makeVersion(1, 0, 2)) {
		*session << "DROP TRIGGER IF EXISTS chat_message_participant_deleter";
		*session << "ALTER TABLE chat_message_participant ADD COLUMN state_change_time"
			+ dbSession.timestampType() + " NOT NULL DEFAULT " + dbSession.currentTimestamp();
	}
1047 1048 1049 1050 1051
	if (version < makeVersion(1, 0, 3)) {
		// Remove client group one-to-one chat rooms for the moment as there are still some issues
		// with them and we prefer to keep using basic chat rooms instead
		const int &capabilities = ChatRoom::CapabilitiesMask(ChatRoom::Capabilities::Conference)
			| ChatRoom::CapabilitiesMask(ChatRoom::Capabilities::OneToOne);
1052
		*session << "DELETE FROM chat_room WHERE (capabilities & :capabilities1) = :capabilities2",
1053 1054 1055
			soci::use(capabilities), soci::use(capabilities);
		linphone_config_set_bool(linphone_core_get_config(q->getCore()->getCCore()), "misc", "prefer_basic_chat_room", TRUE);
	}
1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076
	if (version < makeVersion(1, 0, 4)) {
		*session << "ALTER TABLE conference_chat_message_event ADD COLUMN delivery_notification_required BOOLEAN NOT NULL DEFAULT 0";
		*session << "ALTER TABLE conference_chat_message_event ADD COLUMN display_notification_required BOOLEAN NOT NULL DEFAULT 0";

		*session << "DROP VIEW IF EXISTS conference_event_view";

		string query;
		if (q->getBackend() == AbstractDb::Backend::Mysql)
			query = "CREATE OR REPLACE VIEW conference_event_view AS";
		else
			query = "CREATE VIEW IF NOT EXISTS conference_event_view AS";
		*session << query +
			"  SELECT id, type, creation_time, chat_room_id, from_sip_address_id, to_sip_address_id, time, imdn_message_id, state, direction, is_secured, notify_id, device_sip_address_id, participant_sip_address_id, subject, delivery_notification_required, display_notification_required"
			"  FROM event"
			"  LEFT JOIN conference_event ON conference_event.event_id = event.id"
			"  LEFT JOIN conference_chat_message_event ON conference_chat_message_event.event_id = event.id"
			"  LEFT JOIN conference_notified_event ON conference_notified_event.event_id = event.id"
			"  LEFT JOIN conference_participant_device_event ON conference_participant_device_event.event_id = event.id"
			"  LEFT JOIN conference_participant_event ON conference_participant_event.event_id = event.id"
			"  LEFT JOIN conference_subject_event ON conference_subject_event.event_id = event.id";
	}
1077 1078
}

1079 1080
// -----------------------------------------------------------------------------
// Import.
1081
// -----------------------------------------------------------------------------
1082

1083 1084
static inline bool checkLegacyTableExists (soci::session &session, const string &name) {
	session << "SELECT name FROM sqlite_master WHERE type='table' AND name = :name", soci::use(name);
1085
	return session.got_data();
1086
}
1087 1088

static inline bool checkLegacyFriendsTableExists (soci::session &session) {
1089
	return checkLegacyTableExists(session, "friends");
1090 1091 1092
}

static inline bool checkLegacyHistoryTableExists (soci::session &session) {
1093
	return checkLegacyTableExists(session, "history");
1094 1095 1096
}

void MainDbPrivate::importLegacyFriends (DbSession &inDbSession) {
1097
	L_Q();
1098
	L_DB_TRANSACTION_C(q) {
1099 1100 1101
		if (getModuleVersion("legacy-friends-import") >= makeVersion(1, 0, 0))
			return;
		updateModuleVersion("legacy-friends-import", ModuleVersionLegacyFriendsImport);
1102

1103 1104 1105
		soci::session *inSession = inDbSession.getBackendSession();
		if (!checkLegacyFriendsTableExists(*inSession))
			return;
1106

1107 1108
		unordered_map<int, long long> resolvedListsIds;
		soci::session *session = dbSession.getBackendSession();
1109

1110
		soci::rowset<soci::row> friendsLists = (inSession->prepare << "SELECT * FROM friends_lists");
1111 1112 1113

		set<string> names;
		for (const auto &friendList : friendsLists) {
1114 1115 1116 1117
			const string &name = friendList.get<string>(LegacyFriendListColName, "");
			const string &rlsUri = friendList.get<string>(LegacyFriendListColRlsUri, "");
			const string &syncUri = friendList.get<string>(LegacyFriendListColSyncUri, "");
			const int &revision = friendList.get<int>(LegacyFriendListColRevision, 0);
1118 1119 1120 1121 1122 1123 1124 1125

			string uniqueName = name;
			for (int id = 0; names.find(uniqueName) != names.end(); uniqueName = name + "-" + Utils::toString(id++));
			names.insert(uniqueName);

			*session << "INSERT INTO friends_list (name, rls_uri, sync_uri, revision) VALUES ("
				"  :name, :rlsUri, :syncUri, :revision"
				")", soci::use(uniqueName), soci::use(rlsUri), soci::use(syncUri), soci::use(revision);
1126
			resolvedListsIds[friendList.get<int>(LegacyFriendListColId)] = dbSession.getLastInsertId();
1127 1128
		}

1129
		soci::rowset<soci::row> friends = (inSession->prepare << "SELECT * FROM friends");
1130 1131 1132
		for (const auto &friendInfo : friends) {
			long long friendsListId;
			{
1133
				auto it = resolvedListsIds.find(friendInfo.get<int>(LegacyFriendColFriendListId, -1));
1134 1135 1136 1137 1138
				if (it == resolvedListsIds.end())
					continue;
				friendsListId = it->second;
			}

1139 1140 1141 1142 1143 1144 1145
			const long long &sipAddressId = insertSipAddress(friendInfo.get<string>(LegacyFriendColSipAddress, ""));
			const int &subscribePolicy = friendInfo.get<int>(LegacyFriendColSubscribePolicy, LinphoneSPAccept);
			const int &sendSubscribe = friendInfo.get<int>(LegacyFriendColSendSubscribe, 1);
			const string &vCard = friendInfo.get<string>(LegacyFriendColVCard, "");
			const string &vCardEtag = friendInfo.get<string>(LegacyFriendColVCardEtag, "");
			const string &vCardSyncUri = friendInfo.get<string>(LegacyFriendColVCardSyncUri, "");
			const int &presenceReveived = friendInfo.get<int>(LegacyFriendColPresenceReceived, 0);
1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156

			*session << "INSERT INTO friend ("
				"  sip_address_id, friends_list_id, subscribe_policy, send_subscribe,"
				"  presence_received, v_card, v_card_etag, v_card_sync_uri"
				") VALUES ("
				"  :sipAddressId, :friendsListId, :subscribePolicy, :sendSubscribe,"
				"  :presenceReceived, :vCard, :vCardEtag, :vCardSyncUri"
				")", soci::use(sipAddressId), soci::use(friendsListId), soci::use(subscribePolicy), soci::use(sendSubscribe),
				soci::use(presenceReveived), soci::use(vCard), soci::use(vCardEtag), soci::use(vCardSyncUri);

			bool isNull;
1157
			const string &data = getValueFromRow<string>(friendInfo, LegacyFriendColRefKey, isNull);
1158 1159