main-db.cpp 26.9 KB
Newer Older
Ronan's avatar
Ronan committed
1
/*
2
 * main-db.cpp
Ghislain MARY's avatar
Ghislain MARY committed
3
 * Copyright (C) 2010-2017 Belledonne Communications SARL
Ronan's avatar
Ronan committed
4
 *
Ghislain MARY's avatar
Ghislain MARY committed
5 6 7 8
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
Ronan's avatar
Ronan committed
9 10 11 12 13 14 15
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
Ghislain MARY's avatar
Ghislain MARY committed
16 17
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
Ronan's avatar
Ronan committed
18 19
 */

20
#include <algorithm>
21
#include <ctime>
22

Ronan's avatar
Ronan committed
23 24 25 26
#ifdef SOCI_ENABLED
	#include <soci/soci.h>
#endif // ifdef SOCI_ENABLED

27 28
#include "linphone/utils/utils.h"

Ronan's avatar
Ronan committed
29
#include "abstract/abstract-db-p.h"
Ronan's avatar
Ronan committed
30
#include "chat/chat-message/chat-message.h"
31
#include "chat/chat-room/chat-room.h"
32
#include "conference/participant.h"
33
#include "content/content-type.h"
Ronan's avatar
Ronan committed
34
#include "content/content.h"
35
#include "db/session/db-session-provider.h"
Ronan's avatar
Ronan committed
36
#include "event-log/call-event.h"
Ronan's avatar
Ronan committed
37
#include "event-log/chat-message-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

41
#include "main-db.h"
Ronan's avatar
Ronan committed
42 43 44

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

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

Ronan's avatar
Ronan committed
47 48
LINPHONE_BEGIN_NAMESPACE

49 50 51 52 53 54 55 56 57
struct MessageEventReferences {
#ifdef SOCI_ENABLED
	long eventId;
	long localSipAddressId;
	long remoteSipAddressId;
	long chatRoomId;
#endif
};

58
class MainDbPrivate : public AbstractDbPrivate {
59 60 61
#ifdef SOCI_ENABLED
public:
	long insertSipAddress (const string &sipAddress);
62
	void insertContent (long messageEventId, const Content &content);
63 64
	long insertContentType (const string &contentType);
	long insertEvent (EventLog::Type type, const tm &date);
65
	long insertChatRoom (long sipAddressId, int capabilities, const tm &date);
66
	void insertChatRoomParticipant (long chatRoomId, long sipAddressId, bool isAdmin);
67

68 69 70 71 72 73
	long insertMessageEvent (
		const MessageEventReferences &references,
		ChatMessage::State state,
		ChatMessage::Direction direction,
		const string &imdnMessageId,
		bool isSecured,
74
		const list<Content> &contents
75 76
	);

77 78
	void insertMessageParticipant (long messageEventId, long sipAddressId, ChatMessage::State state);

79 80 81 82
	void importLegacyMessages (const soci::rowset<soci::row> &messages);
#endif

private:
83 84 85
	unordered_map<string, weak_ptr<ChatRoom>> chatRooms;

	L_DECLARE_PUBLIC(MainDb);
86
};
Ronan's avatar
Ronan committed
87 88 89

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

90
MainDb::MainDb () : AbstractDb(*new MainDbPrivate) {}
Ronan's avatar
Ronan committed
91

92 93
#ifdef SOCI_ENABLED

Ronan's avatar
Ronan committed
94
// -----------------------------------------------------------------------------
95
// Soci backend.
Ronan's avatar
Ronan committed
96 97
// -----------------------------------------------------------------------------

98 99 100 101 102
	template<typename T>
	struct EnumToSql {
		T first;
		const char *second;
	};
103

104 105 106 107 108 109 110
	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)
		);
	}

111 112 113 114
	static constexpr EnumToSql<MainDb::Filter> eventFilterToSql[] = {
		{ MainDb::MessageFilter, "1" },
		{ MainDb::CallFilter, "2" },
		{ MainDb::ConferenceFilter, "3" }
115
	};
116

117
	static constexpr const char *mapEventFilterToSql (MainDb::Filter filter) {
118 119 120
		return mapEnumToSql(
			eventFilterToSql, sizeof eventFilterToSql / sizeof eventFilterToSql[0], filter
		);
121 122
	}

Ronan's avatar
Ronan committed
123 124
// -----------------------------------------------------------------------------

125
	static string buildSqlEventFilter (const list<MainDb::Filter> &filters, MainDb::FilterMask mask) {
126
		L_ASSERT(
127 128
			find_if(filters.cbegin(), filters.cend(), [](const MainDb::Filter &filter) {
					return filter == MainDb::NoFilter;
129 130 131
				}) == filters.cend()
		);

132
		if (mask == MainDb::NoFilter)
133 134 135 136 137 138 139 140 141 142 143 144 145
			return "";

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

			if (isStart) {
				isStart = false;
				sql += " WHERE ";
			} else
				sql += " OR ";
146
			sql += " type = ";
147 148 149 150 151 152
			sql += mapEventFilterToSql(filter);
		}

		return sql;
	}

153 154
// -----------------------------------------------------------------------------

155
	long MainDbPrivate::insertSipAddress (const string &sipAddress) {
156 157 158
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

159
		long id;
160 161
		*session << "SELECT id FROM sip_address WHERE value = :sipAddress", soci::use(sipAddress), soci::into(id);
		if (session->got_data())
162 163
			return id;

164 165
		*session << "INSERT INTO sip_address (value) VALUES (:sipAddress)", soci::use(sipAddress);
		return q->getLastInsertId();
166 167
	}

168
	void MainDbPrivate::insertContent (long messageEventId, const Content &content) {
169 170
		L_Q();

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

173 174 175 176
		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());
177 178 179 180 181 182

		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);
183 184
	}

185
	long MainDbPrivate::insertContentType (const string &contentType) {
186 187 188
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

189
		long id;
190 191
		*session << "SELECT id FROM content_type WHERE value = :contentType", soci::use(contentType), soci::into(id);
		if (session->got_data())
192 193
			return id;

194 195
		*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
		return q->getLastInsertId();
196 197
	}

198
	long MainDbPrivate::insertEvent (EventLog::Type type, const tm &date) {
199 200 201 202
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
203
			soci::use(static_cast<int>(type)), soci::use(date);
204
		return q->getLastInsertId();
205 206
	}

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

210
		long id;
211
		*session << "SELECT peer_sip_address_id FROM chat_room WHERE peer_sip_address_id = :sipAddressId",
212
			soci::use(sipAddressId), soci::into(id);
213
		if (!session->got_data())
214 215 216
			*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);
217
		else
218
			*session << "UPDATE chat_room SET last_update_date = :lastUpdateDate WHERE peer_sip_address_id = :sipAddressId",
219
				soci::use(date), soci::use(sipAddressId);
220

221 222 223
		return sipAddressId;
	}

224
	void MainDbPrivate::insertChatRoomParticipant (long chatRoomId, long sipAddressId, bool isAdmin) {
225
		soci::session *session = dbSession.getBackendSession<soci::session>();
226 227 228 229 230 231 232 233 234 235
		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));
236 237
	}

238
	long MainDbPrivate::insertMessageEvent (
239 240 241 242 243
		const MessageEventReferences &references,
		ChatMessage::State state,
		ChatMessage::Direction direction,
		const string &imdnMessageId,
		bool isSecured,
244
		const list<Content> &contents
245
	) {
246 247
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();
248

249 250
		*session << "INSERT INTO message_event ("
			"  event_id, chat_room_id, local_sip_address_id, remote_sip_address_id,"
251
			"  state, direction, imdn_message_id, is_secured"
252
			") VALUES ("
253
			"  :eventId, :chatRoomId, :localSipaddressId, :remoteSipaddressId,"
254
			"  :state, :direction, :imdnMessageId, :isSecured"
255
			")", soci::use(references.eventId), soci::use(references.chatRoomId), soci::use(references.localSipAddressId),
256
			soci::use(references.remoteSipAddressId), soci::use(static_cast<int>(state)),
257 258 259 260 261
			soci::use(static_cast<int>(direction)), soci::use(imdnMessageId), soci::use(isSecured ? 1 : 0);

		long messageEventId = q->getLastInsertId();

		for (const auto &content : contents)
262
			insertContent(messageEventId, content);
263 264

		return messageEventId;
265 266
	}

267
	void MainDbPrivate::insertMessageParticipant (long messageEventId, long sipAddressId, ChatMessage::State state) {
268 269 270 271 272 273 274
		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);
275
		if (statement.get_affected_rows() == 0 && state != ChatMessage::State::Displayed)
276 277 278 279 280
			*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));
	}

281 282
// -----------------------------------------------------------------------------

283 284 285 286 287 288 289 290 291 292 293 294 295
	#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

296 297 298 299 300
	template<typename T>
	static T getValueFromLegacyMessage (const soci::row &message, int index, bool &isNull) {
		isNull = false;

		try {
301
			return message.get<T>(static_cast<size_t>(index));
302 303 304 305 306 307 308
		} catch (const exception &) {
			isNull = true;
		}

		return T();
	}

309
	void MainDbPrivate::importLegacyMessages (const soci::rowset<soci::row> &messages) {
310 311 312 313 314
		soci::session *session = dbSession.getBackendSession<soci::session>();

		soci::transaction tr(*session);

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

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

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

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

334 335
			const int contentId = message.get<int>(LEGACY_MESSAGE_COL_CONTENT_ID, -1);
			ContentType contentType(message.get<string>(LEGACY_MESSAGE_COL_CONTENT_TYPE, ""));
336 337 338 339 340 341 342 343
			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;
			}
344

345
			const string text = getValueFromLegacyMessage<string>(message, LEGACY_MESSAGE_COL_TEXT, isNull);
346 347 348 349 350 351 352 353 354 355

			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 {
356 357 358 359 360 361 362 363 364 365 366 367
				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);
368
			}
369 370 371

			struct MessageEventReferences references;
			references.eventId = insertEvent(EventLog::Type::ChatMessage, date);
372 373
			references.localSipAddressId = insertSipAddress(message.get<string>(LEGACY_MESSAGE_COL_LOCAL_ADDRESS));
			references.remoteSipAddressId = insertSipAddress(message.get<string>(LEGACY_MESSAGE_COL_REMOTE_ADDRESS));
374 375 376 377 378
			references.chatRoomId = insertChatRoom(
				references.remoteSipAddressId,
				static_cast<int>(ChatRoom::Capabilities::Basic),
				date
			);
379

380 381
			insertChatRoomParticipant(references.chatRoomId, references.remoteSipAddressId, false);

382
			long messageEventId = insertMessageEvent (
383 384 385
				references,
				static_cast<ChatMessage::State>(state),
				static_cast<ChatMessage::Direction>(direction),
386 387
				message.get<string>(LEGACY_MESSAGE_COL_IMDN_MESSAGE_ID, ""),
				!!message.get<int>(LEGACY_MESSAGE_COL_IS_SECURED, 0),
388
				{ move(content) }
389
			);
390 391 392

			if (state != static_cast<int>(ChatMessage::State::Displayed))
				insertMessageParticipant(messageEventId, references.remoteSipAddressId, static_cast<ChatMessage::State>(state));
393 394 395
		}

		tr.commit();
396 397
	}

398
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
399

400
	void MainDb::init () {
401
		L_D();
Ronan's avatar
Ronan committed
402
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
403

Ronan's avatar
Ronan committed
404
		*session <<
405 406
			"CREATE TABLE IF NOT EXISTS sip_address ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
407
			"  value VARCHAR(255) UNIQUE NOT NULL"
408 409
			")";

410
		*session <<
411 412
			"CREATE TABLE IF NOT EXISTS content_type ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
413
			"  value VARCHAR(255) UNIQUE NOT NULL"
414 415
			")";

Ronan's avatar
Ronan committed
416
		*session <<
417 418
			"CREATE TABLE IF NOT EXISTS event ("
			"  id" + primaryKeyAutoIncrementStr() + ","
419
			"  type TINYINT UNSIGNED NOT NULL,"
420
			"  date DATE NOT NULL"
421 422
			")";

Ronan's avatar
Ronan committed
423
		*session <<
424
			"CREATE TABLE IF NOT EXISTS chat_room ("
425
			// Server (for conference) or user sip address.
426
			"  peer_sip_address_id INT UNSIGNED PRIMARY KEY,"
427 428

			// Dialog creation date.
429 430 431 432
			"  creation_date DATE NOT NULL,"

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

434 435 436
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

437 438 439 440
			// Chatroom subject.
			"  subject VARCHAR(255),"

			"  FOREIGN KEY (peer_sip_address_id)"
441 442 443 444
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

445 446 447 448 449 450
		*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,"

451
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
452 453 454 455 456 457 458 459
			"  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"
			")";

Ronan's avatar
Ronan committed
460
		*session <<
461
			"CREATE TABLE IF NOT EXISTS message_event ("
462
			"  event_id INT UNSIGNED PRIMARY KEY,"
463 464 465
			"  chat_room_id INT UNSIGNED NOT NULL,"
			"  local_sip_address_id INT UNSIGNED NOT NULL,"
			"  remote_sip_address_id INT UNSIGNED NOT NULL,"
466 467 468 469

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

470 471
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
472
			"  is_secured BOOLEAN NOT NULL,"
473

474 475 476
			"  FOREIGN KEY (event_id)"
			"    REFERENCES event(id)"
			"    ON DELETE CASCADE,"
477 478
			"  FOREIGN KEY (chat_room_id)"
			"    REFERENCES chat_room(peer_sip_address_id)"
479
			"    ON DELETE CASCADE,"
480 481
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
482
			"    ON DELETE CASCADE,"
483
			"  FOREIGN KEY (remote_sip_address_id)"
484
			"    REFERENCES sip_address(id)"
485 486 487
			"    ON DELETE CASCADE"
			")";

488 489 490 491 492 493 494 495
		*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)"
496
			"    REFERENCES message_event(event_id)"
497 498 499
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
500
			"    ON DELETE CASCADE"
501 502
			")";

503
		*session <<
504
			"CREATE TABLE IF NOT EXISTS message_content ("
505 506
			"  id" + primaryKeyAutoIncrementStr() + ","
			"  message_event_id INT UNSIGNED NOT NULL,"
507
			"  content_type_id INT UNSIGNED NOT NULL,"
508
			"  body TEXT NOT NULL,"
509

510
			"  FOREIGN KEY (message_event_id)"
511
			"    REFERENCES message_event(event_id)"
512 513 514
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
515 516
			"    ON DELETE CASCADE"
			")";
517

518 519 520 521 522 523 524 525 526 527 528 529
		*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"
			")";

530 531 532
		*session <<
			"CREATE TABLE IF NOT EXISTS message_crypto_data ("
			"  message_event_id INT UNSIGNED NOT NULL,"
533
			"  key VARCHAR(255),"
534 535
			"  data BLOB,"

536
			"  PRIMARY KEY (message_event_id, key),"
537
			"  FOREIGN KEY (message_event_id)"
538
			"    REFERENCES message_event(event_id)"
539
			"    ON DELETE CASCADE"
540
			")";
541 542 543 544

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
545
			"CREATE TRIGGER IF NOT EXISTS message_participant_deleter"
546 547 548
			"  AFTER UPDATE OF state ON message_participant FOR EACH ROW"
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
549
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
550 551 552 553 554 555 556 557
			"    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;"
558 559 560
			"  UPDATE message_event SET state = ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += " WHERE event_id = NEW.message_event_id;"
561 562 563
			"  END";

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
564
	}
565

566
	bool MainDb::addEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
567 568
		if (!isConnected()) {
			lWarning() << "Unable to add event. Not connected.";
569
			return false;
Ronan's avatar
Ronan committed
570 571
		}

Ronan's avatar
Ronan committed
572
		// TODO.
Ronan's avatar
Ronan committed
573
		switch (eventLog.getType()) {
574
			case EventLog::Type::None:
Ronan's avatar
Ronan committed
575
				return false;
Ronan's avatar
Ronan committed
576
			case EventLog::Type::ChatMessage:
577 578 579 580 581 582 583 584
			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:
Ronan's avatar
Ronan committed
585 586 587 588 589 590
				break;
		}

		return true;
	}

591
	bool MainDb::deleteEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
592 593
		L_D();

Ronan's avatar
Ronan committed
594 595 596 597 598
		if (!isConnected()) {
			lWarning() << "Unable to delete event. Not connected.";
			return false;
		}

Ronan's avatar
Ronan committed
599
		long &id = const_cast<EventLog &>(eventLog).getPrivate()->id;
Ronan's avatar
Ronan committed
600 601 602 603 604 605 606 607 608 609 610 611
		if (id < 0)
			return false;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
		*session << "DELETE FROM event WHERE id = :id", soci::use(id);
		id = -1;

		L_END_LOG_EXCEPTION

		return id == -1;
Ronan's avatar
Ronan committed
612 613
	}

614
	void MainDb::cleanEvents (FilterMask mask) {
Ronan's avatar
Ronan committed
615 616
		L_D();

Ronan's avatar
Ronan committed
617 618 619 620 621
		if (!isConnected()) {
			lWarning() << "Unable to clean events. Not connected.";
			return;
		}

Ronan's avatar
Ronan committed
622 623 624 625 626 627 628 629 630
		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
631 632
	}

633
	int MainDb::getEventsCount (FilterMask mask) const {
634
		L_D();
Ronan's avatar
Ronan committed
635

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

Ronan's avatar
Ronan committed
641 642 643 644 645 646 647 648 649 650 651 652 653 654
		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;
	}

655
	int MainDb::getMessagesCount (const string &peerAddress) const {
656
		L_D();
657

Ronan's avatar
Ronan committed
658 659 660 661
		if (!isConnected()) {
			lWarning() << "Unable to get messages count. Not connected.";
			return 0;
		}
662

663 664 665 666 667
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
668 669 670 671 672 673

		string query = "SELECT COUNT(*) FROM message_event";
		if (peerAddress.empty())
			*session << query, soci::into(count);
		else {
			query += "  WHERE chat_room_id = ("
674
				"  SELECT id FROM sip_address WHERE value = :peerAddress"
Ronan's avatar
Ronan committed
675 676 677 678
				")";

			*session << query, soci::use(peerAddress), soci::into(count);
		}
679 680 681 682

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
683 684
	}

685
	int MainDb::getUnreadMessagesCount (const string &peerAddress) const {
686
		L_D();
687

Ronan's avatar
Ronan committed
688 689 690 691 692
		if (!isConnected()) {
			lWarning() << "Unable to get unread messages count. Not connected.";
			return 0;
		}

693 694
		int count = 0;

695 696 697 698 699 700 701
		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)) +
702
			+ "  AND state <> " + Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
703

704 705 706
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
707 708 709 710 711

		if (peerAddress.empty())
			*session << query, soci::into(count);
		else
			*session << query, soci::use(peerAddress), soci::into(count);
712 713 714 715

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
716 717
	}

718
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &peerAddress, int nLast, FilterMask mask) const {
Ronan's avatar
Ronan committed
719 720 721 722 723
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
724
		// TODO.
Ronan's avatar
Ronan committed
725
		(void)peerAddress;
Ronan's avatar
Ronan committed
726 727
		(void)nLast;
		(void)mask;
Ronan's avatar
Ronan committed
728
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
729 730
	}

731
	list<shared_ptr<EventLog>> MainDb::getHistory (
Ronan's avatar
Ronan committed
732
		const string &peerAddress,
733 734 735 736
		int begin,
		int end,
		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 746
		(void)begin;
		(void)end;
		(void)mask;
Ronan's avatar
Ronan committed
747
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
748 749
	}

750
	void MainDb::cleanHistory (const string &peerAddress, FilterMask mask) {
751 752
		L_D();

Ronan's avatar
Ronan committed
753 754 755 756 757
		if (!isConnected()) {
			lWarning() << "Unable to clean history. Not connected.";
			return;
		}

758
		string query;
759
		if (mask == MainDb::NoFilter || mask & MessageFilter)
760 761 762 763 764 765 766 767 768
			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;

769 770 771
		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
775
	}
Ronan's avatar
Ronan committed
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
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 + "`";
		}
	} else {
		L_BEGIN_LOG_EXCEPTION

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

		tm creationDate;
		tm lastUpdateDate;
		int capabilities;
		string subject;

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

	return shared_ptr<ChatRoom>();
}

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

	bool MainDb::import (Backend, const string &parameters) {
815 816
		L_D();

817 818 819 820 821
		if (!isConnected()) {
			lWarning() << "Unable to import data. Not connected.";
			return 0;
		}

822 823 824 825 826 827 828 829 830 831 832 833 834
		// 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 {
835
			soci::rowset<soci::row> messages = (inSession->prepare << "SELECT * FROM history");
836
			try {
837
				d->importLegacyMessages(messages);
838 839 840 841 842 843 844 845 846 847 848 849 850
			} 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
851
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
852 853
// No backend.
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
854

Ronan's avatar
Ronan committed
855 856
#else

857
	void MainDb::init () {}
Ronan's avatar
Ronan committed
858

859
	bool MainDb::addEvent (const EventLog &) {
Ronan's avatar
Ronan committed
860
		return false;
Ronan's avatar
Ronan committed
861 862
	}

863
	bool MainDb::deleteEvent (const EventLog &) {
Ronan's avatar
Ronan committed
864 865
		return false;
	}
Ronan's avatar
Ronan committed
866

867
	void MainDb::cleanEvents (FilterMask) {}
Ronan's avatar
Ronan committed
868

869
	int MainDb::getEventsCount (FilterMask) const {
Ronan's avatar
Ronan committed
870 871
		return 0;
	}
Ronan's avatar
Ronan committed
872

873
	int MainDb::getMessagesCount (const string &) const {
Ronan's avatar
Ronan committed
874 875
		return 0;
	}
Ronan's avatar
Ronan committed
876

877
	int MainDb::getUnreadMessagesCount (const string &) const {
Ronan's avatar
Ronan committed
878 879
		return 0;
	}
Ronan's avatar
Ronan committed
880

881
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &, int, FilterMask) const {
Ronan's avatar
Ronan committed
882
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
883
	}
Ronan's avatar
Ronan committed
884

885
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &, int, int, FilterMask) const {
Ronan's avatar
Ronan committed
886
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
887
	}
Ronan's avatar
Ronan committed
888

889 890
	void MainDb::cleanHistory (const string &, FilterMask) {}

891 892 893
	shared_ptr<ChatRoom> MainDb::findChatRoom (const string &) const {
		return nullptr;
	}
Ronan's avatar
Ronan committed
894

895
	bool MainDb::import (Backend, const string &) {
Ghislain MARY's avatar
Ghislain MARY committed
896 897 898
		return false;
	}

Ronan's avatar
Ronan committed
899
#endif // ifdef SOCI_ENABLED
Ronan's avatar
Ronan committed
900 901

LINPHONE_END_NAMESPACE