main-db.cpp 30.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"

29
#include "chat/chat-room/chat-room.h"
30
#include "conference/participant.h"
31
#include "content/content-type.h"
Ronan's avatar
Ronan committed
32
#include "content/content.h"
33
#include "db/session/db-session-provider.h"
Ronan's avatar
Ronan committed
34 35 36 37
#include "event-log/call/call-event.h"
#include "event-log/chat/chat-message-event.h"
#include "event-log/conference/conference-participant-device-event.h"
#include "event-log/conference/conference-subject-event.h"
Ronan's avatar
Ronan committed
38
#include "event-log/event-log-p.h"
Ronan's avatar
Ronan committed
39
#include "logger/logger.h"
Ronan's avatar
Ronan committed
40
#include "main-db-p.h"
Ronan's avatar
Ronan committed
41 42 43

// =============================================================================

Ronan's avatar
Ronan committed
44 45
using namespace std;

Ronan's avatar
Ronan committed
46 47 48 49
LINPHONE_BEGIN_NAMESPACE

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

50
MainDb::MainDb () : AbstractDb(*new MainDbPrivate) {}
Ronan's avatar
Ronan committed
51

52 53
#ifdef SOCI_ENABLED

Ronan's avatar
Ronan committed
54
// -----------------------------------------------------------------------------
55
// Soci backend.
Ronan's avatar
Ronan committed
56 57
// -----------------------------------------------------------------------------

58 59 60 61 62
	template<typename T>
	struct EnumToSql {
		T first;
		const char *second;
	};
63

64 65 66 67 68 69 70
	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)
		);
	}

71 72 73 74
	static constexpr EnumToSql<MainDb::Filter> eventFilterToSql[] = {
		{ MainDb::MessageFilter, "1" },
		{ MainDb::CallFilter, "2" },
		{ MainDb::ConferenceFilter, "3" }
75
	};
76

77
	static constexpr const char *mapEventFilterToSql (MainDb::Filter filter) {
78 79 80
		return mapEnumToSql(
			eventFilterToSql, sizeof eventFilterToSql / sizeof eventFilterToSql[0], filter
		);
81 82
	}

Ronan's avatar
Ronan committed
83 84
// -----------------------------------------------------------------------------

85
	static string buildSqlEventFilter (const list<MainDb::Filter> &filters, MainDb::FilterMask mask) {
86
		L_ASSERT(
87 88
			find_if(filters.cbegin(), filters.cend(), [](const MainDb::Filter &filter) {
					return filter == MainDb::NoFilter;
89 90 91
				}) == filters.cend()
		);

92
		if (mask == MainDb::NoFilter)
93 94 95 96 97 98 99 100 101 102 103 104 105
			return "";

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

			if (isStart) {
				isStart = false;
				sql += " WHERE ";
			} else
				sql += " OR ";
106
			sql += " type = ";
107 108 109 110 111 112
			sql += mapEventFilterToSql(filter);
		}

		return sql;
	}

113 114
// -----------------------------------------------------------------------------

115
	long MainDbPrivate::insertSipAddress (const string &sipAddress) {
116 117 118
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

119
		long id;
120 121
		*session << "SELECT id FROM sip_address WHERE value = :sipAddress", soci::use(sipAddress), soci::into(id);
		if (session->got_data())
122 123
			return id;

124 125
		*session << "INSERT INTO sip_address (value) VALUES (:sipAddress)", soci::use(sipAddress);
		return q->getLastInsertId();
126 127
	}

128
	void MainDbPrivate::insertContent (long messageEventId, const Content &content) {
129 130
		L_Q();

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

133 134 135 136
		long contentTypeId = insertContentType(content.getContentType().asString());
		*session << "INSERT INTO message_content (message_event_id, content_type_id, body) VALUES"
			"  (:messageEventId, :contentTypeId, :body)", soci::use(messageEventId), soci::use(contentTypeId),
			soci::use(content.getBodyAsString());
137 138 139 140 141 142

		long messageContentId = q->getLastInsertId();
		for (const auto &appData : content.getAppDataMap())
			*session << "INSERT INTO message_content_app_data (message_content_id, key, data) VALUES"
				"  (:messageContentId, :key, :data)",
				soci::use(messageContentId), soci::use(appData.first), soci::use(appData.second);
143 144
	}

145
	long MainDbPrivate::insertContentType (const string &contentType) {
146 147 148
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

149
		long id;
150 151
		*session << "SELECT id FROM content_type WHERE value = :contentType", soci::use(contentType), soci::into(id);
		if (session->got_data())
152 153
			return id;

154 155
		*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
		return q->getLastInsertId();
156 157
	}

158
	long MainDbPrivate::insertEvent (EventLog::Type type, const tm &date) {
159 160 161 162
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
163
			soci::use(static_cast<int>(type)), soci::use(date);
164
		return q->getLastInsertId();
165 166
	}

167
	long MainDbPrivate::insertChatRoom (long sipAddressId, int capabilities, const tm &date) {
168 169
		soci::session *session = dbSession.getBackendSession<soci::session>();

170
		long id;
171
		*session << "SELECT peer_sip_address_id FROM chat_room WHERE peer_sip_address_id = :sipAddressId",
172
			soci::use(sipAddressId), soci::into(id);
173
		if (!session->got_data())
174 175 176
			*session << "INSERT INTO chat_room (peer_sip_address_id, creation_date, last_update_date, capabilities, subject) VALUES"
				"  (:sipAddressId, :creationDate, :lastUpdateDate, :capabilities, '')",
				soci::use(sipAddressId), soci::use(date), soci::use(date), soci::use(capabilities);
177
		else
178
			*session << "UPDATE chat_room SET last_update_date = :lastUpdateDate WHERE peer_sip_address_id = :sipAddressId",
179
				soci::use(date), soci::use(sipAddressId);
180

181 182 183
		return sipAddressId;
	}

184
	void MainDbPrivate::insertChatRoomParticipant (long chatRoomId, long sipAddressId, bool isAdmin) {
185
		soci::session *session = dbSession.getBackendSession<soci::session>();
186 187 188 189 190 191 192 193 194 195
		soci::statement statement = (
			session->prepare << "UPDATE chat_room_participant SET is_admin = :isAdmin"
				"  WHERE chat_room_id = :chatRoomId AND sip_address_id = :sipAddressId",
				soci::use(static_cast<int>(isAdmin)), soci::use(chatRoomId), soci::use(sipAddressId)
		);
		statement.execute(true);
		if (statement.get_affected_rows() == 0)
			*session << "INSERT INTO chat_room_participant (chat_room_id, sip_address_id, is_admin)"
				"  VALUES (:chatRoomId, :sipAddressId, :isAdmin)",
				soci::use(chatRoomId), soci::use(sipAddressId), soci::use(static_cast<int>(isAdmin));
196 197
	}

198
	long MainDbPrivate::insertMessageEvent (
199
		const MessageEventReferences &references,
200 201
		int state,
		int direction,
202 203
		const string &imdnMessageId,
		bool isSecured,
204
		const list<Content> &contents
205
	) {
206 207
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();
208

209 210
		*session << "INSERT INTO message_event ("
			"  event_id, chat_room_id, local_sip_address_id, remote_sip_address_id,"
211
			"  state, direction, imdn_message_id, is_secured"
212
			") VALUES ("
213
			"  :eventId, :chatRoomId, :localSipaddressId, :remoteSipaddressId,"
214
			"  :state, :direction, :imdnMessageId, :isSecured"
215
			")", soci::use(references.eventId), soci::use(references.chatRoomId), soci::use(references.localSipAddressId),
216 217
			soci::use(references.remoteSipAddressId), soci::use(state), soci::use(direction),
			soci::use(imdnMessageId), soci::use(isSecured ? 1 : 0);
218 219 220 221

		long messageEventId = q->getLastInsertId();

		for (const auto &content : contents)
222
			insertContent(messageEventId, content);
223 224

		return messageEventId;
225 226
	}

227
	void MainDbPrivate::insertMessageParticipant (long messageEventId, long sipAddressId, int state) {
228 229 230 231
		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::statement statement = (
			session->prepare << "UPDATE message_participant SET state = :state"
				"  WHERE message_event_id = :messageEventId AND sip_address_id = :sipAddressId",
232
				soci::use(state), soci::use(messageEventId), soci::use(sipAddressId)
233 234
		);
		statement.execute(true);
235
		if (statement.get_affected_rows() == 0 && state != static_cast<int>(ChatMessage::State::Displayed))
236 237
			*session << "INSERT INTO message_participant (message_event_id, sip_address_id, state)"
				"  VALUES (:messageEventId, :sipAddressId, :state)",
238
				soci::use(messageEventId), soci::use(sipAddressId), soci::use(state);
239 240
	}

241 242 243 244 245 246
// -----------------------------------------------------------------------------

	long MainDbPrivate::insertEvent (const EventLog &eventLog) {
		return insertEvent(eventLog.getType(), Utils::getLongAsTm(eventLog.getTime()));
	}

247 248 249 250 251
	long MainDbPrivate::insertCallEvent (const EventLog &eventLog) {
		// TODO.
		return 0;
	}

252 253 254 255 256 257 258
	long MainDbPrivate::insertMessageEvent (const EventLog &eventLog) {
		// TODO.
		return 0;
	}

	long MainDbPrivate::insertConferenceEvent (const EventLog &eventLog) {
		long eventId = insertEvent(eventLog);
259 260 261 262
		long chatRoomId = insertSipAddress(
			static_cast<const ConferenceEvent &>(eventLog).getConferenceAddress().asString()
		);

263 264
		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_event (event_id, chat_room_id)"
265
			"  VALUES (:eventId, :chatRoomId)", soci::use(eventId), soci::use(chatRoomId);
266 267 268 269 270
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantEvent (const EventLog &eventLog) {
		long eventId = insertConferenceEvent(eventLog);
271 272 273 274
		long participantAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantEvent &>(eventLog).getParticipantAddress().asString()
		);

275
		soci::session *session = dbSession.getBackendSession<soci::session>();
276 277
		*session << "INSERT INTO conference_participant_event (conference_event_id, participant_address_id)"
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
278 279 280 281
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantDeviceEvent (const EventLog &eventLog) {
282 283 284 285 286 287 288 289 290
		long eventId = insertConferenceParticipantEvent(eventLog);
		long gruuAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantDeviceEvent &>(eventLog).getGruuAddress().asString()
		);

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_participant_device_event (conference_participant_event_id, gruu_address_id)"
			"  VALUES (:eventId, :gruuAddressId)", soci::use(eventId), soci::use(gruuAddressId);
		return eventId;
291 292 293
	}

	long MainDbPrivate::insertConferenceSubjectEvent (const EventLog &eventLog) {
294 295 296 297 298 299 300 301
		long eventId = insertConferenceEvent(eventLog);

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_subject_event (conference_event_id, subject)"
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(
				static_cast<const ConferenceSubjectEvent &>(eventLog).getSubject()
			);
		return eventId;
302 303
	}

304 305
// -----------------------------------------------------------------------------

306 307 308 309 310 311 312 313 314 315 316 317 318
	#define LEGACY_MESSAGE_COL_LOCAL_ADDRESS 1
	#define LEGACY_MESSAGE_COL_REMOTE_ADDRESS 2
	#define LEGACY_MESSAGE_COL_DIRECTION 3
	#define LEGACY_MESSAGE_COL_TEXT 4
	#define LEGACY_MESSAGE_COL_STATE 7
	#define LEGACY_MESSAGE_COL_URL 8
	#define LEGACY_MESSAGE_COL_DATE 9
	#define LEGACY_MESSAGE_COL_APP_DATA 10
	#define LEGACY_MESSAGE_COL_CONTENT_ID 11
	#define LEGACY_MESSAGE_COL_IMDN_MESSAGE_ID 12
	#define LEGACY_MESSAGE_COL_CONTENT_TYPE 13
	#define LEGACY_MESSAGE_COL_IS_SECURED 14

319 320 321 322 323
	template<typename T>
	static T getValueFromLegacyMessage (const soci::row &message, int index, bool &isNull) {
		isNull = false;

		try {
324
			return message.get<T>(static_cast<size_t>(index));
325 326 327 328 329 330 331
		} catch (const exception &) {
			isNull = true;
		}

		return T();
	}

332
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
333

334
	void MainDb::init () {
335
		L_D();
Ronan's avatar
Ronan committed
336
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
337

Ronan's avatar
Ronan committed
338
		*session <<
339 340
			"CREATE TABLE IF NOT EXISTS sip_address ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
341
			"  value VARCHAR(255) UNIQUE NOT NULL"
342 343
			")";

344
		*session <<
345 346
			"CREATE TABLE IF NOT EXISTS content_type ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
347
			"  value VARCHAR(255) UNIQUE NOT NULL"
348 349
			")";

Ronan's avatar
Ronan committed
350
		*session <<
351 352
			"CREATE TABLE IF NOT EXISTS event ("
			"  id" + primaryKeyAutoIncrementStr() + ","
353
			"  type TINYINT UNSIGNED NOT NULL,"
354
			"  date DATE NOT NULL"
355 356
			")";

Ronan's avatar
Ronan committed
357
		*session <<
358
			"CREATE TABLE IF NOT EXISTS chat_room ("
359
			// Server (for conference) or user sip address.
360
			"  peer_sip_address_id INT UNSIGNED PRIMARY KEY,"
361 362

			// Dialog creation date.
363 364 365 366
			"  creation_date DATE NOT NULL,"

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

368 369 370
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

371 372 373
			// Chatroom subject.
			"  subject VARCHAR(255),"

374 375
			"  last_notify INT UNSIGNED,"

376
			"  FOREIGN KEY (peer_sip_address_id)"
377 378 379 380
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

381 382 383 384 385 386
		*session <<
			"CREATE TABLE IF NOT EXISTS chat_room_participant ("
			"  chat_room_id INT UNSIGNED NOT NULL,"
			"  sip_address_id INT UNSIGNED NOT NULL,"
			"  is_admin BOOLEAN NOT NULL,"

387
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
388 389 390 391 392 393
			"  FOREIGN KEY (chat_room_id)"
			"    REFERENCES chat_room(peer_sip_address_id)"
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442
			")";

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_event ("
			"  event_id INT UNSIGNED PRIMARY KEY,"
			"  chat_room_id INT UNSIGNED NOT NULL,"

			"  FOREIGN KEY (event_id)"
			"    REFERENCES event(id)"
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (chat_room_id)"
			"    REFERENCES chat_room(peer_sip_address_id)"
			"    ON DELETE CASCADE"
			")";

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
			"  conference_event_id INT UNSIGNED PRIMARY KEY,"
			"  participant_address_id INT UNSIGNED NOT NULL,"

			"  FOREIGN KEY (conference_event_id)"
			"    REFERENCES event(event_id)"
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (participant_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_device_event ("
			"  conference_participant_event_id INT UNSIGNED PRIMARY KEY,"
			"  gruu_address_id INT UNSIGNED NOT NULL,"

			"  FOREIGN KEY (conference_participant_event_id)"
			"    REFERENCES conference_participant_event(conference_event_id)"
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (gruu_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_subject_event ("
			"  conference_event_id INT UNSIGNED PRIMARY KEY,"
			"  subject VARCHAR(255),"

			"  FOREIGN KEY (conference_event_id)"
			"    REFERENCES event(event_id)"
			"    ON DELETE CASCADE"
443 444
			")";

Ronan's avatar
Ronan committed
445
		*session <<
446
			"CREATE TABLE IF NOT EXISTS message_event ("
447
			"  event_id INT UNSIGNED PRIMARY KEY,"
448 449 450
			"  chat_room_id INT UNSIGNED NOT NULL,"
			"  local_sip_address_id INT UNSIGNED NOT NULL,"
			"  remote_sip_address_id INT UNSIGNED NOT NULL,"
451 452 453 454

			// See: https://tools.ietf.org/html/rfc5438#section-6.3
			"  imdn_message_id VARCHAR(255) NOT NULL,"

455 456
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
457
			"  is_secured BOOLEAN NOT NULL,"
458

459 460 461
			"  FOREIGN KEY (event_id)"
			"    REFERENCES event(id)"
			"    ON DELETE CASCADE,"
462 463
			"  FOREIGN KEY (chat_room_id)"
			"    REFERENCES chat_room(peer_sip_address_id)"
464
			"    ON DELETE CASCADE,"
465 466
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
467
			"    ON DELETE CASCADE,"
468
			"  FOREIGN KEY (remote_sip_address_id)"
469
			"    REFERENCES sip_address(id)"
470 471 472
			"    ON DELETE CASCADE"
			")";

473 474 475 476 477 478 479 480
		*session <<
			"CREATE TABLE IF NOT EXISTS message_participant ("
			"  message_event_id INT UNSIGNED NOT NULL,"
			"  sip_address_id INT UNSIGNED NOT NULL,"
			"  state TINYINT UNSIGNED NOT NULL,"

			"  PRIMARY KEY (message_event_id, sip_address_id),"
			"  FOREIGN KEY (message_event_id)"
481
			"    REFERENCES message_event(event_id)"
482 483 484
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
485
			"    ON DELETE CASCADE"
486 487
			")";

488
		*session <<
489
			"CREATE TABLE IF NOT EXISTS message_content ("
490 491
			"  id" + primaryKeyAutoIncrementStr() + ","
			"  message_event_id INT UNSIGNED NOT NULL,"
492
			"  content_type_id INT UNSIGNED NOT NULL,"
493
			"  body TEXT NOT NULL,"
494

495
			"  FOREIGN KEY (message_event_id)"
496
			"    REFERENCES message_event(event_id)"
497 498 499
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
500 501
			"    ON DELETE CASCADE"
			")";
502

503 504 505 506 507 508 509 510 511 512 513 514
		*session <<
			"CREATE TABLE IF NOT EXISTS message_content_app_data ("
			"  message_content_id INT UNSIGNED NOT NULL,"
			"  key VARCHAR(255),"
			"  data BLOB,"

			"  PRIMARY KEY (message_content_id, key),"
			"  FOREIGN KEY (message_content_id)"
			"    REFERENCES message_content(id)"
			"    ON DELETE CASCADE"
			")";

515 516 517
		*session <<
			"CREATE TABLE IF NOT EXISTS message_crypto_data ("
			"  message_event_id INT UNSIGNED NOT NULL,"
518
			"  key VARCHAR(255),"
519 520
			"  data BLOB,"

521
			"  PRIMARY KEY (message_event_id, key),"
522
			"  FOREIGN KEY (message_event_id)"
523
			"    REFERENCES message_event(event_id)"
524
			"    ON DELETE CASCADE"
525
			")";
526 527 528 529

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
530
			"CREATE TRIGGER IF NOT EXISTS message_participant_deleter"
531 532 533
			"  AFTER UPDATE OF state ON message_participant FOR EACH ROW"
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
534
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
535 536 537 538 539 540 541 542
			"    SELECT state FROM message_participant WHERE"
			"    NEW.message_event_id = message_participant.message_event_id"
			"    AND state <> ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += "    LIMIT 1"
			"  )) = 0"
			"  BEGIN"
			"  DELETE FROM message_participant WHERE NEW.message_event_id = message_participant.message_event_id;"
543 544 545
			"  UPDATE message_event SET state = ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += " WHERE event_id = NEW.message_event_id;"
546 547 548
			"  END";

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
549
	}
550

551
	bool MainDb::addEvent (const EventLog &eventLog) {
552 553
		L_D();

Ronan's avatar
Ronan committed
554 555
		if (!isConnected()) {
			lWarning() << "Unable to add event. Not connected.";
556
			return false;
Ronan's avatar
Ronan committed
557 558
		}

559 560 561 562 563 564
		bool soFarSoGood = false;

		L_BEGIN_LOG_EXCEPTION

		soci::transaction tr(*d->dbSession.getBackendSession<soci::session>());

Ronan's avatar
Ronan committed
565
		switch (eventLog.getType()) {
566
			case EventLog::Type::None:
Ronan's avatar
Ronan committed
567
				return false;
568

Ronan's avatar
Ronan committed
569
			case EventLog::Type::ChatMessage:
570 571 572
				d->insertMessageEvent(eventLog);
				break;

573 574
			case EventLog::Type::CallStart:
			case EventLog::Type::CallEnd:
575 576
				d->insertCallEvent(eventLog);
				break;
577

578 579
			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
580 581 582
				d->insertConferenceEvent(eventLog);
				break;

583 584 585 586
			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
587 588 589
				d->insertConferenceParticipantEvent(eventLog);
				break;

590 591
			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
592 593 594
				d->insertConferenceParticipantDeviceEvent(eventLog);
				break;

595
			case EventLog::Type::ConferenceSubjectChanged:
596
				d->insertConferenceSubjectEvent(eventLog);
Ronan's avatar
Ronan committed
597 598 599
				break;
		}

600 601 602 603 604 605 606
		tr.commit();

		soFarSoGood = true;

		L_END_LOG_EXCEPTION

		return soFarSoGood;
Ronan's avatar
Ronan committed
607 608
	}

609
	bool MainDb::deleteEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
610 611
		L_D();

Ronan's avatar
Ronan committed
612 613 614 615 616
		if (!isConnected()) {
			lWarning() << "Unable to delete event. Not connected.";
			return false;
		}

Ronan's avatar
Ronan committed
617 618
		long &storageId = const_cast<EventLog &>(eventLog).getPrivate()->storageId;
		if (storageId < 0)
Ronan's avatar
Ronan committed
619 620 621 622 623
			return false;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
624 625
		*session << "DELETE FROM event WHERE id = :id", soci::use(storageId);
		storageId = -1;
Ronan's avatar
Ronan committed
626 627 628

		L_END_LOG_EXCEPTION

Ronan's avatar
Ronan committed
629
		return storageId == -1;
Ronan's avatar
Ronan committed
630 631
	}

632
	void MainDb::cleanEvents (FilterMask mask) {
Ronan's avatar
Ronan committed
633 634
		L_D();

Ronan's avatar
Ronan committed
635 636 637 638 639
		if (!isConnected()) {
			lWarning() << "Unable to clean events. Not connected.";
			return;
		}

Ronan's avatar
Ronan committed
640 641 642 643 644 645 646 647 648
		string query = "DELETE FROM event" +
			buildSqlEventFilter({ MessageFilter, CallFilter, ConferenceFilter }, mask);

		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
649 650
	}

651
	int MainDb::getEventsCount (FilterMask mask) const {
652
		L_D();
Ronan's avatar
Ronan committed
653

Ronan's avatar
Ronan committed
654 655 656 657 658
		if (!isConnected()) {
			lWarning() << "Unable to get events count. Not connected.";
			return 0;
		}

Ronan's avatar
Ronan committed
659 660 661 662 663 664 665 666 667 668 669 670 671 672
		string query = "SELECT COUNT(*) FROM event" +
			buildSqlEventFilter({ MessageFilter, CallFilter, ConferenceFilter }, mask);
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
		*session << query, soci::into(count);

		L_END_LOG_EXCEPTION

		return count;
	}

673
	int MainDb::getMessagesCount (const string &peerAddress) const {
674
		L_D();
675

Ronan's avatar
Ronan committed
676 677 678 679
		if (!isConnected()) {
			lWarning() << "Unable to get messages count. Not connected.";
			return 0;
		}
680

681 682 683 684 685
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
686 687 688 689 690 691

		string query = "SELECT COUNT(*) FROM message_event";
		if (peerAddress.empty())
			*session << query, soci::into(count);
		else {
			query += "  WHERE chat_room_id = ("
692
				"  SELECT id FROM sip_address WHERE value = :peerAddress"
Ronan's avatar
Ronan committed
693 694 695 696
				")";

			*session << query, soci::use(peerAddress), soci::into(count);
		}
697 698 699 700

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
701 702
	}

703
	int MainDb::getUnreadMessagesCount (const string &peerAddress) const {
704
		L_D();
705

Ronan's avatar
Ronan committed
706 707 708 709 710
		if (!isConnected()) {
			lWarning() << "Unable to get unread messages count. Not connected.";
			return 0;
		}

711 712
		int count = 0;

713 714 715 716 717 718 719
		string query = "SELECT COUNT(*) FROM message_event WHERE";
		if (!peerAddress.empty())
			query += " chat_room_id = ("
				"  SELECT id FROM sip_address WHERE value = :peerAddress"
				") AND ";

		query += " direction = " + Utils::toString(static_cast<int>(ChatMessage::Direction::Incoming)) +
720
			+ "  AND state <> " + Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
721

722 723 724
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
725 726 727 728 729

		if (peerAddress.empty())
			*session << query, soci::into(count);
		else
			*session << query, soci::use(peerAddress), soci::into(count);
730 731 732 733

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
734 735
	}

736
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &peerAddress, int nLast, FilterMask mask) const {
Ronan's avatar
Ronan committed
737 738 739 740 741
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
742
		// TODO.
Ronan's avatar
Ronan committed
743
		(void)peerAddress;
Ronan's avatar
Ronan committed
744 745
		(void)nLast;
		(void)mask;
Ronan's avatar
Ronan committed
746
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
747 748
	}

749
	list<shared_ptr<EventLog>> MainDb::getHistory (
Ronan's avatar
Ronan committed
750
		const string &peerAddress,
751 752 753 754
		int begin,
		int end,
		FilterMask mask
	) const {
Ronan's avatar
Ronan committed
755 756 757 758 759
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
760
		// TODO.
Ronan's avatar
Ronan committed
761
		(void)peerAddress;
Ronan's avatar
Ronan committed
762 763 764
		(void)begin;
		(void)end;
		(void)mask;
Ronan's avatar
Ronan committed
765
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
766 767
	}

768
	void MainDb::cleanHistory (const string &peerAddress, FilterMask mask) {
769 770
		L_D();

Ronan's avatar
Ronan committed
771 772 773 774 775
		if (!isConnected()) {
			lWarning() << "Unable to clean history. Not connected.";
			return;
		}

776
		string query;
777
		if (mask == MainDb::NoFilter || mask & MessageFilter)
778 779 780 781 782 783 784 785 786
			query += "SELECT event_id FROM message_event WHERE chat_room_id = ("
				"  SELECT peer_sip_address_id FROM chat_room WHERE peer_sip_address_id = ("
				"    SELECT id FROM sip_address WHERE value = :peerAddress"
				"  )"
				")";

		if (query.empty())
			return;

787 788 789
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
790
		*session << "DELETE FROM event WHERE id IN (" + query + ")", soci::use(peerAddress);
791 792

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
793
	}
Ronan's avatar
Ronan committed
794

795 796
// -----------------------------------------------------------------------------

797 798 799
shared_ptr<ChatRoom> MainDb::findChatRoom (const string &peerAddress) const {
	L_D();

800
	// TODO: Use core cache.
801

Ronan's avatar
Ronan committed
802
	L_BEGIN_LOG_EXCEPTION
803

Ronan's avatar
Ronan committed
804
	soci::session *session = d->dbSession.getBackendSession<soci::session>();
805

Ronan's avatar
Ronan committed
806 807 808 809
	tm creationDate;
	tm lastUpdateDate;
	int capabilities;
	string subject;
810

Ronan's avatar
Ronan committed
811 812 813 814 815 816 817 818
	*session << "SELECT creation_date, last_update_date, capabilities, subject "
		"  FROM chat_room"
		"  WHERE peer_sip_address_id = ("
		"    SELECT id from sip_address WHERE value = :peerAddress"
		"  )", soci::use(peerAddress), soci::into(creationDate), soci::into(lastUpdateDate),
		soci::use(capabilities), soci::use(subject);

	L_END_LOG_EXCEPTION
819 820 821 822 823 824 825

	return shared_ptr<ChatRoom>();
}

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

	bool MainDb::import (Backend, const string &parameters) {
826 827
		L_D();

828 829 830 831 832
		if (!isConnected()) {
			lWarning() << "Unable to import data. Not connected.";
			return 0;
		}

833 834 835 836 837 838 839 840 841 842 843 844 845
		// Backend is useless, it's sqlite3. (Only available legacy backend.)
		const string uri = "sqlite3://" + parameters;
		DbSession inDbSession = DbSessionProvider::getInstance()->getSession(uri);

		if (!inDbSession) {
			lWarning() << "Unable to connect to: `" << uri << "`.";
			return false;
		}

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

		// Import messages.
		try {
846
			soci::rowset<soci::row> messages = (inSession->prepare << "SELECT * FROM history");
847
			try {
Ronan's avatar
Ronan committed
848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919
				soci::transaction tr(*d->dbSession.getBackendSession<soci::session>());

				for (const auto &message : messages) {
					const int direction = message.get<int>(LEGACY_MESSAGE_COL_DIRECTION);
					if (direction != 0 && direction != 1) {
						lWarning() << "Unable to import legacy message with invalid direction.";
						continue;
					}

					const int state = message.get<int>(
						LEGACY_MESSAGE_COL_STATE, static_cast<int>(ChatMessage::State::Displayed)
					);
					if (state < 0 || state > static_cast<int>(ChatMessage::State::Displayed)) {
						lWarning() << "Unable to import legacy message with invalid state.";
						continue;
					}

					const tm date = Utils::getLongAsTm(message.get<int>(LEGACY_MESSAGE_COL_DATE, 0));

					bool isNull;
					const string url = getValueFromLegacyMessage<string>(message, LEGACY_MESSAGE_COL_URL, isNull);

					const int contentId = message.get<int>(LEGACY_MESSAGE_COL_CONTENT_ID, -1);
					ContentType contentType(message.get<string>(LEGACY_MESSAGE_COL_CONTENT_TYPE, ""));
					if (!contentType.isValid())
						contentType = contentId != -1
							? ContentType::FileTransfer
							: (isNull ? ContentType::PlainText : ContentType::ExternalBody);
					if (contentType == ContentType::ExternalBody) {
						lInfo() << "Import of external body content is skipped.";
						continue;
					}

					const string text = getValueFromLegacyMessage<string>(message, LEGACY_MESSAGE_COL_TEXT, isNull);

					Content content;
					content.setContentType(contentType);
					if (contentType == ContentType::PlainText) {
						if (isNull) {
							lWarning() << "Unable to import legacy message with no text.";
							continue;
						}
						content.setBody(text);
					} else {
						if (contentType != ContentType::FileTransfer) {
							lWarning() << "Unable to import unsupported legacy content.";
							continue;
						}

						const string appData = getValueFromLegacyMessage<string>(message, LEGACY_MESSAGE_COL_APP_DATA, isNull);
						if (isNull) {
							lWarning() << "Unable to import legacy file message without app data.";
							continue;
						}

						content.setAppData("legacy", appData);
					}

					struct MessageEventReferences references;
					references.eventId = d->insertEvent(EventLog::Type::ChatMessage, date);
					references.localSipAddressId = d->insertSipAddress(message.get<string>(LEGACY_MESSAGE_COL_LOCAL_ADDRESS));
					references.remoteSipAddressId = d->insertSipAddress(message.get<string>(LEGACY_MESSAGE_COL_REMOTE_ADDRESS));
					references.chatRoomId = d->insertChatRoom(
						references.remoteSipAddressId,
						static_cast<int>(ChatRoom::Capabilities::Basic),
						date
					);

					d->insertChatRoomParticipant(references.chatRoomId, references.remoteSipAddressId, false);

					long messageEventId = d->insertMessageEvent (
						references,
920 921
						state,
						direction,
Ronan's avatar
Ronan committed
922 923 924 925 926 927 928 929 930
						message.get<string>(LEGACY_MESSAGE_COL_IMDN_MESSAGE_ID, ""),
						!!message.get<int>(LEGACY_MESSAGE_COL_IS_SECURED, 0),
						{ move(content) }
					);

					if (state != static_cast<int>(ChatMessage::State::Displayed))
						d->insertMessageParticipant(
							messageEventId,
							references.remoteSipAddressId,
931
							state
Ronan's avatar
Ronan committed
932 933 934 935
						);
				}

				tr.commit();
936 937 938 939 940 941 942 943 944 945 946 947 948
			} catch (const exception &e) {
				lInfo() << "Failed to import legacy messages from: `" << uri << "`. (" << e.what() << ")";
				return false;
			}
			lInfo() << "Successful import of legacy messages from: `" << uri << "`.";
		} catch (const exception &) {
			// Table doesn't exist.
			return false;
		}

		return true;
	}

Ronan's avatar
Ronan committed
949
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
950 951
// No backend.
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
952

Ronan's avatar
Ronan committed
953 954
#else

955
	void MainDb::init () {}
Ronan's avatar
Ronan committed
956

957
	bool MainDb::addEvent (const EventLog &) {
Ronan's avatar
Ronan committed
958
		return false;
Ronan's avatar
Ronan committed
959 960
	}

961
	bool MainDb::deleteEvent (const EventLog &) {
Ronan's avatar
Ronan committed
962 963
		return false;
	}
Ronan's avatar
Ronan committed
964

965
	void MainDb::cleanEvents (FilterMask) {}
Ronan's avatar
Ronan committed
966

967
	int MainDb::getEventsCount (FilterMask) const {
Ronan's avatar
Ronan committed
968 969
		return 0;
	}
Ronan's avatar
Ronan committed
970

971
	int MainDb::getMessagesCount (const string &) const {
Ronan's avatar
Ronan committed
972 973
		return 0;
	}
Ronan's avatar
Ronan committed
974

975
	int MainDb::getUnreadMessagesCount (const string &) const {
Ronan's avatar
Ronan committed
976 977
		return 0;
	}
Ronan's avatar
Ronan committed
978