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

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

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

Ronan's avatar
Ronan committed
43 44 45 46
LINPHONE_BEGIN_NAMESPACE

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

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

49 50
#ifdef SOCI_ENABLED

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

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

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

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

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

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

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

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

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

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

		return sql;
	}

110 111
// -----------------------------------------------------------------------------

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

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

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

Ronan's avatar
Ronan committed
125
	void MainDbPrivate::insertContent (long eventId, const Content &content) {
126 127
		L_Q();

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

130
		long contentTypeId = insertContentType(content.getContentType().asString());
Ronan's avatar
Ronan committed
131 132
		*session << "INSERT INTO message_content (event_id, content_type_id, body) VALUES"
			"  (:eventId, :contentTypeId, :body)", soci::use(eventId), soci::use(contentTypeId),
133
			soci::use(content.getBodyAsString());
134 135 136 137 138 139

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

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

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

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

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

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

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

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

178 179 180
		return sipAddressId;
	}

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

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

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

Ronan's avatar
Ronan committed
216
		long eventId = q->getLastInsertId();
217 218

		for (const auto &content : contents)
Ronan's avatar
Ronan committed
219
			insertContent(eventId, content);
220

Ronan's avatar
Ronan committed
221
		return eventId;
222 223
	}

Ronan's avatar
Ronan committed
224
	void MainDbPrivate::insertMessageParticipant (long eventId, long sipAddressId, int state) {
225 226 227
		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::statement statement = (
			session->prepare << "UPDATE message_participant SET state = :state"
Ronan's avatar
Ronan committed
228 229
				"  WHERE event_id = :eventId AND sip_address_id = :sipAddressId",
				soci::use(state), soci::use(eventId), soci::use(sipAddressId)
230 231
		);
		statement.execute(true);
232
		if (statement.get_affected_rows() == 0 && state != static_cast<int>(ChatMessage::State::Displayed))
Ronan's avatar
Ronan committed
233 234 235
			*session << "INSERT INTO message_participant (event_id, sip_address_id, state)"
				"  VALUES (:eventId, :sipAddressId, :state)",
				soci::use(eventId), soci::use(sipAddressId), soci::use(state);
236 237
	}

238 239 240 241 242 243
// -----------------------------------------------------------------------------

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

244 245 246 247 248
	long MainDbPrivate::insertCallEvent (const EventLog &eventLog) {
		// TODO.
		return 0;
	}

249 250 251 252 253 254 255
	long MainDbPrivate::insertMessageEvent (const EventLog &eventLog) {
		// TODO.
		return 0;
	}

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

260 261
		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_event (event_id, chat_room_id)"
262
			"  VALUES (:eventId, :chatRoomId)", soci::use(eventId), soci::use(chatRoomId);
263 264 265
		return eventId;
	}

266
	long MainDbPrivate::insertConferenceNotifiedEvent (const EventLog &eventLog) {
267
		long eventId = insertConferenceEvent(eventLog);
268 269 270 271 272 273 274 275 276 277 278

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
			"  VALUES (:eventId, :notifyId)", soci::use(eventId), soci::use(
				static_cast<const ConferenceNotifiedEvent &>(eventLog).getNotifyId()
			);
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantEvent (const EventLog &eventLog) {
		long eventId = insertConferenceNotifiedEvent(eventLog);
279 280 281 282
		long participantAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantEvent &>(eventLog).getParticipantAddress().asString()
		);

283
		soci::session *session = dbSession.getBackendSession<soci::session>();
284
		*session << "INSERT INTO conference_participant_event (event_id, participant_address_id)"
285
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
286 287 288 289
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantDeviceEvent (const EventLog &eventLog) {
290 291 292 293 294 295
		long eventId = insertConferenceParticipantEvent(eventLog);
		long gruuAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantDeviceEvent &>(eventLog).getGruuAddress().asString()
		);

		soci::session *session = dbSession.getBackendSession<soci::session>();
296
		*session << "INSERT INTO conference_participant_device_event (event_id, gruu_address_id)"
297 298
			"  VALUES (:eventId, :gruuAddressId)", soci::use(eventId), soci::use(gruuAddressId);
		return eventId;
299 300 301
	}

	long MainDbPrivate::insertConferenceSubjectEvent (const EventLog &eventLog) {
302
		long eventId = insertConferenceNotifiedEvent(eventLog);
303 304

		soci::session *session = dbSession.getBackendSession<soci::session>();
305
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
306 307 308 309
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(
				static_cast<const ConferenceSubjectEvent &>(eventLog).getSubject()
			);
		return eventId;
310 311
	}

312 313
// -----------------------------------------------------------------------------

314 315 316 317 318 319 320 321 322 323 324 325 326
	#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

327 328 329 330 331
	template<typename T>
	static T getValueFromLegacyMessage (const soci::row &message, int index, bool &isNull) {
		isNull = false;

		try {
332
			return message.get<T>(static_cast<size_t>(index));
333 334 335 336 337 338 339
		} catch (const exception &) {
			isNull = true;
		}

		return T();
	}

340
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
341

342
	void MainDb::init () {
343
		L_D();
Ronan's avatar
Ronan committed
344
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
345

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

352
		*session <<
353 354
			"CREATE TABLE IF NOT EXISTS content_type ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
355
			"  value VARCHAR(255) UNIQUE NOT NULL"
356 357
			")";

Ronan's avatar
Ronan committed
358
		*session <<
359 360
			"CREATE TABLE IF NOT EXISTS event ("
			"  id" + primaryKeyAutoIncrementStr() + ","
361
			"  type TINYINT UNSIGNED NOT NULL,"
362
			"  date DATE NOT NULL"
363 364
			")";

Ronan's avatar
Ronan committed
365
		*session <<
366
			"CREATE TABLE IF NOT EXISTS chat_room ("
367
			// Server (for conference) or user sip address.
368
			"  peer_sip_address_id INT UNSIGNED PRIMARY KEY,"
369 370

			// Dialog creation date.
371 372 373 374
			"  creation_date DATE NOT NULL,"

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

376 377 378
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

379 380 381
			// Chatroom subject.
			"  subject VARCHAR(255),"

382 383
			"  last_notify INT UNSIGNED,"

384
			"  FOREIGN KEY (peer_sip_address_id)"
385 386 387 388
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

389 390 391 392 393 394
		*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,"

395
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
396 397 398 399 400 401
			"  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"
402 403 404 405 406 407 408 409 410 411 412 413 414 415 416
			")";

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

417 418 419 420 421 422 423 424 425 426
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_notified_event ("
			"  event_id INT UNSIGNED PRIMARY KEY,"
			"  notify_id INT UNSIGNED NOT NULL,"

			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_event(event_id)"
			"    ON DELETE CASCADE"
			")";

427 428
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
429
			"  event_id INT UNSIGNED PRIMARY KEY,"
430 431
			"  participant_address_id INT UNSIGNED NOT NULL,"

432 433
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
434 435 436 437 438 439 440 441
			"    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 ("
442
			"  event_id INT UNSIGNED PRIMARY KEY,"
443 444
			"  gruu_address_id INT UNSIGNED NOT NULL,"

445 446
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_participant_event(event_id)"
447 448 449 450 451 452 453 454
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (gruu_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

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

458 459
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
460
			"    ON DELETE CASCADE"
461 462
			")";

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

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

473 474
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
475
			"  is_secured BOOLEAN NOT NULL,"
476

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

491 492
		*session <<
			"CREATE TABLE IF NOT EXISTS message_participant ("
Ronan's avatar
Ronan committed
493
			"  event_id INT UNSIGNED NOT NULL,"
494 495 496
			"  sip_address_id INT UNSIGNED NOT NULL,"
			"  state TINYINT UNSIGNED NOT NULL,"

Ronan's avatar
Ronan committed
497 498
			"  PRIMARY KEY (event_id, sip_address_id),"
			"  FOREIGN KEY (event_id)"
499
			"    REFERENCES message_event(event_id)"
500 501 502
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
503
			"    ON DELETE CASCADE"
504 505
			")";

506
		*session <<
507
			"CREATE TABLE IF NOT EXISTS message_content ("
508
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
509
			"  event_id INT UNSIGNED NOT NULL,"
510
			"  content_type_id INT UNSIGNED NOT NULL,"
511
			"  body TEXT NOT NULL,"
512

Ronan's avatar
Ronan committed
513
			"  FOREIGN KEY (event_id)"
514
			"    REFERENCES message_event(event_id)"
515 516 517
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
518 519
			"    ON DELETE CASCADE"
			")";
520

521 522 523 524 525 526 527 528 529 530 531 532
		*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"
			")";

533 534
		*session <<
			"CREATE TABLE IF NOT EXISTS message_crypto_data ("
Ronan's avatar
Ronan committed
535
			"  event_id INT UNSIGNED NOT NULL,"
536
			"  key VARCHAR(255),"
537 538
			"  data BLOB,"

Ronan's avatar
Ronan committed
539 540
			"  PRIMARY KEY (event_id, key),"
			"  FOREIGN KEY (event_id)"
541
			"    REFERENCES message_event(event_id)"
542
			"    ON DELETE CASCADE"
543
			")";
544 545 546 547

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
548
			"CREATE TRIGGER IF NOT EXISTS message_participant_deleter"
549 550 551
			"  AFTER UPDATE OF state ON message_participant FOR EACH ROW"
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
552
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
553
			"    SELECT state FROM message_participant WHERE"
Ronan's avatar
Ronan committed
554
			"    NEW.event_id = message_participant.event_id"
555 556 557 558 559
			"    AND state <> ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += "    LIMIT 1"
			"  )) = 0"
			"  BEGIN"
Ronan's avatar
Ronan committed
560
			"  DELETE FROM message_participant WHERE NEW.event_id = message_participant.event_id;"
561 562
			"  UPDATE message_event SET state = ";
		participantMessageDeleter += displayedId;
Ronan's avatar
Ronan committed
563
		participantMessageDeleter += " WHERE event_id = NEW.event_id;"
564 565 566
			"  END";

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
567
	}
568

569
	bool MainDb::addEvent (const EventLog &eventLog) {
570 571
		L_D();

Ronan's avatar
Ronan committed
572 573
		if (!isConnected()) {
			lWarning() << "Unable to add event. Not connected.";
574
			return false;
Ronan's avatar
Ronan committed
575 576
		}

577 578 579 580 581 582
		bool soFarSoGood = false;

		L_BEGIN_LOG_EXCEPTION

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

Ronan's avatar
Ronan committed
583
		switch (eventLog.getType()) {
584
			case EventLog::Type::None:
Ronan's avatar
Ronan committed
585
				return false;
586

Ronan's avatar
Ronan committed
587
			case EventLog::Type::ChatMessage:
588 589 590
				d->insertMessageEvent(eventLog);
				break;

591 592
			case EventLog::Type::CallStart:
			case EventLog::Type::CallEnd:
593 594
				d->insertCallEvent(eventLog);
				break;
595

596 597
			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
598 599 600
				d->insertConferenceEvent(eventLog);
				break;

601 602 603 604
			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
605 606 607
				d->insertConferenceParticipantEvent(eventLog);
				break;

608 609
			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
610 611 612
				d->insertConferenceParticipantDeviceEvent(eventLog);
				break;

613
			case EventLog::Type::ConferenceSubjectChanged:
614
				d->insertConferenceSubjectEvent(eventLog);
Ronan's avatar
Ronan committed
615 616 617
				break;
		}

618 619 620 621 622 623 624
		tr.commit();

		soFarSoGood = true;

		L_END_LOG_EXCEPTION

		return soFarSoGood;
Ronan's avatar
Ronan committed
625 626
	}

627
	bool MainDb::deleteEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
628 629
		L_D();

Ronan's avatar
Ronan committed
630 631 632 633 634
		if (!isConnected()) {
			lWarning() << "Unable to delete event. Not connected.";
			return false;
		}

Ronan's avatar
Ronan committed
635 636
		long &storageId = const_cast<EventLog &>(eventLog).getPrivate()->storageId;
		if (storageId < 0)
Ronan's avatar
Ronan committed
637 638 639 640 641
			return false;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
642 643
		*session << "DELETE FROM event WHERE id = :id", soci::use(storageId);
		storageId = -1;
Ronan's avatar
Ronan committed
644 645 646

		L_END_LOG_EXCEPTION

Ronan's avatar
Ronan committed
647
		return storageId == -1;
Ronan's avatar
Ronan committed
648 649
	}

650
	void MainDb::cleanEvents (FilterMask mask) {
Ronan's avatar
Ronan committed
651 652
		L_D();

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

Ronan's avatar
Ronan committed
658 659 660 661 662 663 664 665 666
		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
667 668
	}

669
	int MainDb::getEventsCount (FilterMask mask) const {
670
		L_D();
Ronan's avatar
Ronan committed
671

Ronan's avatar
Ronan committed
672 673 674 675 676
		if (!isConnected()) {
			lWarning() << "Unable to get events count. Not connected.";
			return 0;
		}

Ronan's avatar
Ronan committed
677 678 679 680 681 682 683 684 685 686 687 688 689 690
		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;
	}

691
	int MainDb::getMessagesCount (const string &peerAddress) const {
692
		L_D();
693

Ronan's avatar
Ronan committed
694 695 696 697
		if (!isConnected()) {
			lWarning() << "Unable to get messages count. Not connected.";
			return 0;
		}
698

699 700 701 702 703
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
704 705 706 707 708 709

		string query = "SELECT COUNT(*) FROM message_event";
		if (peerAddress.empty())
			*session << query, soci::into(count);
		else {
			query += "  WHERE chat_room_id = ("
710
				"  SELECT id FROM sip_address WHERE value = :peerAddress"
Ronan's avatar
Ronan committed
711 712 713 714
				")";

			*session << query, soci::use(peerAddress), soci::into(count);
		}
715 716 717 718

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
719 720
	}

721
	int MainDb::getUnreadMessagesCount (const string &peerAddress) const {
722
		L_D();
723

Ronan's avatar
Ronan committed
724 725 726 727 728
		if (!isConnected()) {
			lWarning() << "Unable to get unread messages count. Not connected.";
			return 0;
		}

729 730
		int count = 0;

731 732 733 734 735 736 737
		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)) +
738
			+ "  AND state <> " + Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
739

740 741 742
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
743 744 745 746 747

		if (peerAddress.empty())
			*session << query, soci::into(count);
		else
			*session << query, soci::use(peerAddress), soci::into(count);
748 749 750 751

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
752 753
	}

754
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &peerAddress, int nLast, 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
		(void)nLast;
		(void)mask;
Ronan's avatar
Ronan committed
764
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
765 766
	}

767
	list<shared_ptr<EventLog>> MainDb::getHistory (
Ronan's avatar
Ronan committed
768
		const string &peerAddress,
769 770 771 772
		int begin,
		int end,
		FilterMask mask
	) const {
Ronan's avatar
Ronan committed
773 774 775 776 777
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
778
		// TODO.
Ronan's avatar
Ronan committed
779
		(void)peerAddress;
Ronan's avatar
Ronan committed
780 781 782
		(void)begin;
		(void)end;
		(void)mask;
Ronan's avatar
Ronan committed
783
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
784 785
	}

786
	void MainDb::cleanHistory (const string &peerAddress, FilterMask mask) {
787 788
		L_D();

Ronan's avatar
Ronan committed
789 790 791 792 793
		if (!isConnected()) {
			lWarning() << "Unable to clean history. Not connected.";
			return;
		}

794
		string query;
795
		if (mask == MainDb::NoFilter || mask & MessageFilter)
796 797 798 799 800 801 802 803 804
			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;

805 806 807
		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
811
	}
Ronan's avatar
Ronan committed
812

813 814
// -----------------------------------------------------------------------------

815 816 817
shared_ptr<ChatRoom> MainDb::findChatRoom (const string &peerAddress) const {
	L_D();

818
	// TODO: Use core cache.
819

Ronan's avatar
Ronan committed
820
	L_BEGIN_LOG_EXCEPTION
821

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

Ronan's avatar
Ronan committed
824 825 826 827
	tm creationDate;
	tm lastUpdateDate;
	int capabilities;
	string subject;
828

Ronan's avatar
Ronan committed
829 830 831 832 833 834 835 836
	*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
837 838 839 840 841 842 843

	return shared_ptr<ChatRoom>();
}

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

	bool MainDb::import (Backend, const string &parameters) {
844 845
		L_D();

846 847 848 849 850
		if (!isConnected()) {
			lWarning() << "Unable to import data. Not connected.";
			return 0;
		}

851 852 853 854 855 856 857 858 859 860 861 862 863
		// 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 {
864
			soci::rowset<soci::row> messages = (inSession->prepare << "SELECT * FROM history");
865
			try {
Ronan's avatar
Ronan committed
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 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935
				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);

Ronan's avatar
Ronan committed
936
					long eventId = d->insertMessageEvent (
Ronan's avatar
Ronan committed
937
						references,
938 939
						state,
						direction,
Ronan's avatar
Ronan committed
940 941 942