main-db.cpp 27.8 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
#include "event-log/call-event.h"
Ronan's avatar
Ronan committed
35
#include "event-log/chat-message-event.h"
Ronan's avatar
Ronan committed
36
#include "event-log/event-log-p.h"
Ronan's avatar
Ronan committed
37
#include "logger/logger.h"
Ronan's avatar
Ronan committed
38
#include "main-db-p.h"
Ronan's avatar
Ronan committed
39 40 41

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

Ronan's avatar
Ronan committed
42 43
using namespace std;

Ronan's avatar
Ronan committed
44 45 46 47
LINPHONE_BEGIN_NAMESPACE

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

48
MainDb::MainDb () : AbstractDb(*new MainDbPrivate) {}
Ronan's avatar
Ronan committed
49

50 51
#ifdef SOCI_ENABLED

Ronan's avatar
Ronan committed
52
// -----------------------------------------------------------------------------
53
// Soci backend.
Ronan's avatar
Ronan committed
54 55
// -----------------------------------------------------------------------------

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

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

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

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

Ronan's avatar
Ronan committed
81 82
// -----------------------------------------------------------------------------

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

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

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

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

		return sql;
	}

111 112
// -----------------------------------------------------------------------------

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

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

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

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

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

131 132 133 134
		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());
135 136 137 138 139 140

		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);
141 142
	}

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

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

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

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

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

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

168
		long id;
169
		*session << "SELECT peer_sip_address_id FROM chat_room WHERE peer_sip_address_id = :sipAddressId",
170
			soci::use(sipAddressId), soci::into(id);
171
		if (!session->got_data())
172 173 174
			*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);
175
		else
176
			*session << "UPDATE chat_room SET last_update_date = :lastUpdateDate WHERE peer_sip_address_id = :sipAddressId",
177
				soci::use(date), soci::use(sipAddressId);
178

179 180 181
		return sipAddressId;
	}

182
	void MainDbPrivate::insertChatRoomParticipant (long chatRoomId, long sipAddressId, bool isAdmin) {
183
		soci::session *session = dbSession.getBackendSession<soci::session>();
184 185 186 187 188 189 190 191 192 193
		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));
194 195
	}

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

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

		long messageEventId = q->getLastInsertId();

		for (const auto &content : contents)
220
			insertContent(messageEventId, content);
221 222

		return messageEventId;
223 224
	}

225
	void MainDbPrivate::insertMessageParticipant (long messageEventId, long sipAddressId, ChatMessage::State state) {
226 227 228 229 230 231 232
		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",
				soci::use(static_cast<int>(state)), soci::use(messageEventId), soci::use(sipAddressId)
		);
		statement.execute(true);
233
		if (statement.get_affected_rows() == 0 && state != ChatMessage::State::Displayed)
234 235 236 237 238
			*session << "INSERT INTO message_participant (message_event_id, sip_address_id, state)"
				"  VALUES (:messageEventId, :sipAddressId, :state)",
				soci::use(messageEventId), soci::use(sipAddressId), soci::use(static_cast<int>(state));
	}

239 240 241 242 243 244
	void MainDbPrivate::insertConferenceEvent (long eventId, long chatRoomId) {
		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(chatRoomId);
	}

245 246
// -----------------------------------------------------------------------------

247 248 249 250 251 252 253 254 255 256 257 258 259
	#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

260 261 262 263 264
	template<typename T>
	static T getValueFromLegacyMessage (const soci::row &message, int index, bool &isNull) {
		isNull = false;

		try {
265
			return message.get<T>(static_cast<size_t>(index));
266 267 268 269 270 271 272
		} catch (const exception &) {
			isNull = true;
		}

		return T();
	}

273
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
274

275
	void MainDb::init () {
276
		L_D();
Ronan's avatar
Ronan committed
277
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
278

Ronan's avatar
Ronan committed
279
		*session <<
280 281
			"CREATE TABLE IF NOT EXISTS sip_address ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
282
			"  value VARCHAR(255) UNIQUE NOT NULL"
283 284
			")";

285
		*session <<
286 287
			"CREATE TABLE IF NOT EXISTS content_type ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
288
			"  value VARCHAR(255) UNIQUE NOT NULL"
289 290
			")";

Ronan's avatar
Ronan committed
291
		*session <<
292 293
			"CREATE TABLE IF NOT EXISTS event ("
			"  id" + primaryKeyAutoIncrementStr() + ","
294
			"  type TINYINT UNSIGNED NOT NULL,"
295
			"  date DATE NOT NULL"
296 297
			")";

Ronan's avatar
Ronan committed
298
		*session <<
299
			"CREATE TABLE IF NOT EXISTS chat_room ("
300
			// Server (for conference) or user sip address.
301
			"  peer_sip_address_id INT UNSIGNED PRIMARY KEY,"
302 303

			// Dialog creation date.
304 305 306 307
			"  creation_date DATE NOT NULL,"

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

309 310 311
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

312 313 314
			// Chatroom subject.
			"  subject VARCHAR(255),"

315 316
			"  last_notify INT UNSIGNED,"

317
			"  FOREIGN KEY (peer_sip_address_id)"
318 319 320 321
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

322 323 324 325 326 327
		*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,"

328
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
329 330 331 332 333 334
			"  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"
335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383
			")";

		*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"
384 385
			")";

Ronan's avatar
Ronan committed
386
		*session <<
387
			"CREATE TABLE IF NOT EXISTS message_event ("
388
			"  event_id INT UNSIGNED PRIMARY KEY,"
389 390 391
			"  chat_room_id INT UNSIGNED NOT NULL,"
			"  local_sip_address_id INT UNSIGNED NOT NULL,"
			"  remote_sip_address_id INT UNSIGNED NOT NULL,"
392 393 394 395

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

396 397
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
398
			"  is_secured BOOLEAN NOT NULL,"
399

400 401 402
			"  FOREIGN KEY (event_id)"
			"    REFERENCES event(id)"
			"    ON DELETE CASCADE,"
403 404
			"  FOREIGN KEY (chat_room_id)"
			"    REFERENCES chat_room(peer_sip_address_id)"
405
			"    ON DELETE CASCADE,"
406 407
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
408
			"    ON DELETE CASCADE,"
409
			"  FOREIGN KEY (remote_sip_address_id)"
410
			"    REFERENCES sip_address(id)"
411 412 413
			"    ON DELETE CASCADE"
			")";

414 415 416 417 418 419 420 421
		*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)"
422
			"    REFERENCES message_event(event_id)"
423 424 425
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
426
			"    ON DELETE CASCADE"
427 428
			")";

429
		*session <<
430
			"CREATE TABLE IF NOT EXISTS message_content ("
431 432
			"  id" + primaryKeyAutoIncrementStr() + ","
			"  message_event_id INT UNSIGNED NOT NULL,"
433
			"  content_type_id INT UNSIGNED NOT NULL,"
434
			"  body TEXT NOT NULL,"
435

436
			"  FOREIGN KEY (message_event_id)"
437
			"    REFERENCES message_event(event_id)"
438 439 440
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
441 442
			"    ON DELETE CASCADE"
			")";
443

444 445 446 447 448 449 450 451 452 453 454 455
		*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"
			")";

456 457 458
		*session <<
			"CREATE TABLE IF NOT EXISTS message_crypto_data ("
			"  message_event_id INT UNSIGNED NOT NULL,"
459
			"  key VARCHAR(255),"
460 461
			"  data BLOB,"

462
			"  PRIMARY KEY (message_event_id, key),"
463
			"  FOREIGN KEY (message_event_id)"
464
			"    REFERENCES message_event(event_id)"
465
			"    ON DELETE CASCADE"
466
			")";
467 468 469 470

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
471
			"CREATE TRIGGER IF NOT EXISTS message_participant_deleter"
472 473 474
			"  AFTER UPDATE OF state ON message_participant FOR EACH ROW"
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
475
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
476 477 478 479 480 481 482 483
			"    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;"
484 485 486
			"  UPDATE message_event SET state = ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += " WHERE event_id = NEW.message_event_id;"
487 488 489
			"  END";

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
490
	}
491

492
	bool MainDb::addEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
493 494
		if (!isConnected()) {
			lWarning() << "Unable to add event. Not connected.";
495
			return false;
Ronan's avatar
Ronan committed
496 497
		}

Ronan's avatar
Ronan committed
498
		// TODO.
Ronan's avatar
Ronan committed
499
		switch (eventLog.getType()) {
500
			case EventLog::Type::None:
Ronan's avatar
Ronan committed
501
				return false;
Ronan's avatar
Ronan committed
502
			case EventLog::Type::ChatMessage:
503 504 505 506 507 508 509 510
			case EventLog::Type::CallStart:
			case EventLog::Type::CallEnd:
			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
511 512
			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
513
			case EventLog::Type::ConferenceSubjectChanged:
Ronan's avatar
Ronan committed
514 515 516 517 518 519
				break;
		}

		return true;
	}

520
	bool MainDb::deleteEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
521 522
		L_D();

Ronan's avatar
Ronan committed
523 524 525 526 527
		if (!isConnected()) {
			lWarning() << "Unable to delete event. Not connected.";
			return false;
		}

Ronan's avatar
Ronan committed
528 529
		long &storageId = const_cast<EventLog &>(eventLog).getPrivate()->storageId;
		if (storageId < 0)
Ronan's avatar
Ronan committed
530 531 532 533 534
			return false;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
535 536
		*session << "DELETE FROM event WHERE id = :id", soci::use(storageId);
		storageId = -1;
Ronan's avatar
Ronan committed
537 538 539

		L_END_LOG_EXCEPTION

Ronan's avatar
Ronan committed
540
		return storageId == -1;
Ronan's avatar
Ronan committed
541 542
	}

543
	void MainDb::cleanEvents (FilterMask mask) {
Ronan's avatar
Ronan committed
544 545
		L_D();

Ronan's avatar
Ronan committed
546 547 548 549 550
		if (!isConnected()) {
			lWarning() << "Unable to clean events. Not connected.";
			return;
		}

Ronan's avatar
Ronan committed
551 552 553 554 555 556 557 558 559
		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
560 561
	}

562
	int MainDb::getEventsCount (FilterMask mask) const {
563
		L_D();
Ronan's avatar
Ronan committed
564

Ronan's avatar
Ronan committed
565 566 567 568 569
		if (!isConnected()) {
			lWarning() << "Unable to get events count. Not connected.";
			return 0;
		}

Ronan's avatar
Ronan committed
570 571 572 573 574 575 576 577 578 579 580 581 582 583
		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;
	}

584
	int MainDb::getMessagesCount (const string &peerAddress) const {
585
		L_D();
586

Ronan's avatar
Ronan committed
587 588 589 590
		if (!isConnected()) {
			lWarning() << "Unable to get messages count. Not connected.";
			return 0;
		}
591

592 593 594 595 596
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
597 598 599 600 601 602

		string query = "SELECT COUNT(*) FROM message_event";
		if (peerAddress.empty())
			*session << query, soci::into(count);
		else {
			query += "  WHERE chat_room_id = ("
603
				"  SELECT id FROM sip_address WHERE value = :peerAddress"
Ronan's avatar
Ronan committed
604 605 606 607
				")";

			*session << query, soci::use(peerAddress), soci::into(count);
		}
608 609 610 611

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
612 613
	}

614
	int MainDb::getUnreadMessagesCount (const string &peerAddress) const {
615
		L_D();
616

Ronan's avatar
Ronan committed
617 618 619 620 621
		if (!isConnected()) {
			lWarning() << "Unable to get unread messages count. Not connected.";
			return 0;
		}

622 623
		int count = 0;

624 625 626 627 628 629 630
		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)) +
631
			+ "  AND state <> " + Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
632

633 634 635
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
636 637 638 639 640

		if (peerAddress.empty())
			*session << query, soci::into(count);
		else
			*session << query, soci::use(peerAddress), soci::into(count);
641 642 643 644

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
645 646
	}

647
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &peerAddress, int nLast, FilterMask mask) const {
Ronan's avatar
Ronan committed
648 649 650 651 652
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
653
		// TODO.
Ronan's avatar
Ronan committed
654
		(void)peerAddress;
Ronan's avatar
Ronan committed
655 656
		(void)nLast;
		(void)mask;
Ronan's avatar
Ronan committed
657
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
658 659
	}

660
	list<shared_ptr<EventLog>> MainDb::getHistory (
Ronan's avatar
Ronan committed
661
		const string &peerAddress,
662 663 664 665
		int begin,
		int end,
		FilterMask mask
	) const {
Ronan's avatar
Ronan committed
666 667 668 669 670
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
671
		// TODO.
Ronan's avatar
Ronan committed
672
		(void)peerAddress;
Ronan's avatar
Ronan committed
673 674 675
		(void)begin;
		(void)end;
		(void)mask;
Ronan's avatar
Ronan committed
676
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
677 678
	}

679
	void MainDb::cleanHistory (const string &peerAddress, FilterMask mask) {
680 681
		L_D();

Ronan's avatar
Ronan committed
682 683 684 685 686
		if (!isConnected()) {
			lWarning() << "Unable to clean history. Not connected.";
			return;
		}

687
		string query;
688
		if (mask == MainDb::NoFilter || mask & MessageFilter)
689 690 691 692 693 694 695 696 697
			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;

698 699 700
		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
704
	}
Ronan's avatar
Ronan committed
705

706 707
// -----------------------------------------------------------------------------

708 709 710 711 712 713 714 715 716 717
shared_ptr<ChatRoom> MainDb::findChatRoom (const string &peerAddress) const {
	L_D();

	const auto it = d->chatRooms.find(peerAddress);
	if (it != d->chatRooms.cend()) {
		try {
			return it->second.lock();
		} catch (const exception &) {
			lError() << "Cannot lock chat room: `" + peerAddress + "`";
		}
Ronan's avatar
Ronan committed
718 719
		return shared_ptr<ChatRoom>();
	}
720

Ronan's avatar
Ronan committed
721
	L_BEGIN_LOG_EXCEPTION
722

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

Ronan's avatar
Ronan committed
725 726 727 728
	tm creationDate;
	tm lastUpdateDate;
	int capabilities;
	string subject;
729

Ronan's avatar
Ronan committed
730 731 732 733 734 735 736 737
	*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
738 739 740 741 742 743 744

	return shared_ptr<ChatRoom>();
}

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

	bool MainDb::import (Backend, const string &parameters) {
745 746
		L_D();

747 748 749 750 751
		if (!isConnected()) {
			lWarning() << "Unable to import data. Not connected.";
			return 0;
		}

752 753 754 755 756 757 758 759 760 761 762 763 764
		// 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 {
765
			soci::rowset<soci::row> messages = (inSession->prepare << "SELECT * FROM history");
766
			try {
Ronan's avatar
Ronan committed
767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854
				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,
						static_cast<ChatMessage::State>(state),
						static_cast<ChatMessage::Direction>(direction),
						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,
							static_cast<ChatMessage::State>(state)
						);
				}

				tr.commit();
855 856 857 858 859 860 861 862 863 864 865 866 867
			} 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
868
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
869 870
// No backend.
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
871

Ronan's avatar
Ronan committed
872 873
#else

874
	void MainDb::init () {}
Ronan's avatar
Ronan committed
875

876
	bool MainDb::addEvent (const EventLog &) {
Ronan's avatar
Ronan committed
877
		return false;
Ronan's avatar
Ronan committed
878 879
	}

880
	bool MainDb::deleteEvent (const EventLog &) {
Ronan's avatar
Ronan committed
881 882
		return false;
	}
Ronan's avatar
Ronan committed
883

884
	void MainDb::cleanEvents (FilterMask) {}
Ronan's avatar
Ronan committed
885

886
	int MainDb::getEventsCount (FilterMask) const {
Ronan's avatar
Ronan committed
887 888
		return 0;
	}
Ronan's avatar
Ronan committed
889

890
	int MainDb::getMessagesCount (const string &) const {
Ronan's avatar
Ronan committed
891 892
		return 0;
	}
Ronan's avatar
Ronan committed
893

894
	int MainDb::getUnreadMessagesCount (const string &) const {
Ronan's avatar
Ronan committed
895 896
		return 0;
	}
Ronan's avatar
Ronan committed
897

898
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &, int, FilterMask) const {
Ronan's avatar
Ronan committed
899
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
900
	}
Ronan's avatar
Ronan committed
901

902
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &, int, int, FilterMask) const {
Ronan's avatar
Ronan committed
903
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
904
	}
Ronan's avatar
Ronan committed
905

906 907
	void MainDb::cleanHistory (const string &, FilterMask) {}

908 909 910
	shared_ptr<ChatRoom> MainDb::findChatRoom (const string &) const {
		return nullptr;
	}
Ronan's avatar
Ronan committed
911

912
	bool MainDb::import (Backend, const string &) {
Ghislain MARY's avatar
Ghislain MARY committed
913 914 915
		return false;
	}

Ronan's avatar
Ronan committed
916
#endif // ifdef SOCI_ENABLED
Ronan's avatar
Ronan committed
917 918

LINPHONE_END_NAMESPACE