main-db.cpp 32 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
LINPHONE_BEGIN_NAMESPACE

45 46 47 48 49 50 51
struct MessageEventReferences {
	long eventId;
	long localSipAddressId;
	long remoteSipAddressId;
	long chatRoomId;
};

Ronan's avatar
Ronan committed
52 53
// -----------------------------------------------------------------------------

54
MainDb::MainDb () : AbstractDb(*new MainDbPrivate) {}
Ronan's avatar
Ronan committed
55

56 57
#ifdef SOCI_ENABLED

Ronan's avatar
Ronan committed
58
// -----------------------------------------------------------------------------
59
// Soci backend.
Ronan's avatar
Ronan committed
60 61
// -----------------------------------------------------------------------------

62 63 64 65 66
	template<typename T>
	struct EnumToSql {
		T first;
		const char *second;
	};
67

68 69 70 71 72 73 74
	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)
		);
	}

75 76 77 78
	static constexpr EnumToSql<MainDb::Filter> eventFilterToSql[] = {
		{ MainDb::MessageFilter, "1" },
		{ MainDb::CallFilter, "2" },
		{ MainDb::ConferenceFilter, "3" }
79
	};
80

81
	static constexpr const char *mapEventFilterToSql (MainDb::Filter filter) {
82 83 84
		return mapEnumToSql(
			eventFilterToSql, sizeof eventFilterToSql / sizeof eventFilterToSql[0], filter
		);
85 86
	}

Ronan's avatar
Ronan committed
87 88
// -----------------------------------------------------------------------------

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

96
		if (mask == MainDb::NoFilter)
97 98 99 100 101 102 103 104 105 106 107 108 109
			return "";

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

			if (isStart) {
				isStart = false;
				sql += " WHERE ";
			} else
				sql += " OR ";
110
			sql += " type = ";
111 112 113 114 115 116
			sql += mapEventFilterToSql(filter);
		}

		return sql;
	}

117 118
// -----------------------------------------------------------------------------

119
	long MainDbPrivate::insertSipAddress (const string &sipAddress) {
120 121 122
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

123
		long id;
124 125
		*session << "SELECT id FROM sip_address WHERE value = :sipAddress", soci::use(sipAddress), soci::into(id);
		if (session->got_data())
126 127
			return id;

128 129
		*session << "INSERT INTO sip_address (value) VALUES (:sipAddress)", soci::use(sipAddress);
		return q->getLastInsertId();
130 131
	}

Ronan's avatar
Ronan committed
132
	void MainDbPrivate::insertContent (long eventId, const Content &content) {
133 134
		L_Q();

135 136
		soci::session *session = dbSession.getBackendSession<soci::session>();

137
		long contentTypeId = insertContentType(content.getContentType().asString());
Ronan's avatar
Ronan committed
138 139
		*session << "INSERT INTO message_content (event_id, content_type_id, body) VALUES"
			"  (:eventId, :contentTypeId, :body)", soci::use(eventId), soci::use(contentTypeId),
140
			soci::use(content.getBodyAsString());
141 142 143 144 145 146

		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);
147 148
	}

149
	long MainDbPrivate::insertContentType (const string &contentType) {
150 151 152
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

153
		long id;
154 155
		*session << "SELECT id FROM content_type WHERE value = :contentType", soci::use(contentType), soci::into(id);
		if (session->got_data())
156 157
			return id;

158 159
		*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
		return q->getLastInsertId();
160 161
	}

162
	long MainDbPrivate::insertEvent (EventLog::Type type, const tm &date) {
163 164 165 166
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
167
			soci::use(static_cast<int>(type)), soci::use(date);
168
		return q->getLastInsertId();
169 170
	}

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

174
		long id;
175
		*session << "SELECT peer_sip_address_id FROM chat_room WHERE peer_sip_address_id = :sipAddressId",
176
			soci::use(sipAddressId), soci::into(id);
177
		if (!session->got_data())
178 179 180
			*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);
181
		else
182
			*session << "UPDATE chat_room SET last_update_date = :lastUpdateDate WHERE peer_sip_address_id = :sipAddressId",
183
				soci::use(date), soci::use(sipAddressId);
184

185 186 187
		return sipAddressId;
	}

188
	void MainDbPrivate::insertChatRoomParticipant (long chatRoomId, long sipAddressId, bool isAdmin) {
189
		soci::session *session = dbSession.getBackendSession<soci::session>();
190 191 192 193 194 195 196 197 198 199
		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));
200 201
	}

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

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

Ronan's avatar
Ronan committed
223
		long eventId = q->getLastInsertId();
224 225

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

Ronan's avatar
Ronan committed
228
		return eventId;
229 230
	}

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

245 246 247 248 249 250
// -----------------------------------------------------------------------------

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

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

256
	long MainDbPrivate::insertMessageEvent (const EventLog &eventLog) {
257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275
		shared_ptr<ChatMessage> chatMessage = static_cast<const ChatMessageEvent &>(eventLog).getChatMessage();
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

		tm eventTime = Utils::getLongAsTm(static_cast<long>(eventLog.getTime()));

		struct MessageEventReferences references;
		references.eventId = insertEvent(EventLog::Type::ChatMessage, eventTime);
		references.localSipAddressId = insertSipAddress(chatMessage->getLocalAddress().asString());
		references.remoteSipAddressId = insertSipAddress(chatMessage->getRemoteAddress().asString());
		references.chatRoomId = insertChatRoom(
			references.remoteSipAddressId,
			chatRoom->getCapabilities(),
			eventTime
		);

Ronan's avatar
Ronan committed
276
		return insertMessageEvent (
277 278 279 280 281 282 283
			references,
			static_cast<int>(chatMessage->getState()),
			static_cast<int>(chatMessage->getDirection()),
			chatMessage->getImdnMessageId(),
			chatMessage->isSecured(),
			chatMessage->getContents()
		);
284 285
	}

Ronan's avatar
Ronan committed
286
	long MainDbPrivate::insertConferenceEvent (const EventLog &eventLog, long *chatRoomId) {
287
		long eventId = insertEvent(eventLog);
Ronan's avatar
Ronan committed
288
		long curChatRoomId = insertSipAddress(
289 290 291
			static_cast<const ConferenceEvent &>(eventLog).getConferenceAddress().asString()
		);

292 293
		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_event (event_id, chat_room_id)"
Ronan's avatar
Ronan committed
294 295 296 297
			"  VALUES (:eventId, :chatRoomId)", soci::use(eventId), soci::use(curChatRoomId);

		if (chatRoomId)
			*chatRoomId = curChatRoomId;
298

299 300 301
		return eventId;
	}

302
	long MainDbPrivate::insertConferenceNotifiedEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
303 304 305
		long chatRoomId;
		long eventId = insertConferenceEvent(eventLog, &chatRoomId);
		unsigned int lastNotifyId = static_cast<const ConferenceNotifiedEvent &>(eventLog).getNotifyId();
306 307 308

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
309 310 311
			"  VALUES (:eventId, :notifyId)", soci::use(eventId), soci::use(lastNotifyId);
		*session << "UPDATE chat_room SET last_notify_id = :lastNotifyId WHERE peer_sip_address_id = :chatRoomId",
			soci::use(lastNotifyId), soci::use(chatRoomId);
312

313 314 315 316 317
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantEvent (const EventLog &eventLog) {
		long eventId = insertConferenceNotifiedEvent(eventLog);
318 319 320 321
		long participantAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantEvent &>(eventLog).getParticipantAddress().asString()
		);

322
		soci::session *session = dbSession.getBackendSession<soci::session>();
323
		*session << "INSERT INTO conference_participant_event (event_id, participant_address_id)"
324
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
325

326 327 328 329
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantDeviceEvent (const EventLog &eventLog) {
330 331 332 333 334 335
		long eventId = insertConferenceParticipantEvent(eventLog);
		long gruuAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantDeviceEvent &>(eventLog).getGruuAddress().asString()
		);

		soci::session *session = dbSession.getBackendSession<soci::session>();
336
		*session << "INSERT INTO conference_participant_device_event (event_id, gruu_address_id)"
337
			"  VALUES (:eventId, :gruuAddressId)", soci::use(eventId), soci::use(gruuAddressId);
338

339
		return eventId;
340 341 342
	}

	long MainDbPrivate::insertConferenceSubjectEvent (const EventLog &eventLog) {
343
		long eventId = insertConferenceNotifiedEvent(eventLog);
344 345

		soci::session *session = dbSession.getBackendSession<soci::session>();
346
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
347 348 349
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(
				static_cast<const ConferenceSubjectEvent &>(eventLog).getSubject()
			);
350

351
		return eventId;
352 353
	}

354 355
// -----------------------------------------------------------------------------

356 357 358 359 360 361 362 363 364 365 366 367 368
	#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

369 370 371 372 373
	template<typename T>
	static T getValueFromLegacyMessage (const soci::row &message, int index, bool &isNull) {
		isNull = false;

		try {
374
			return message.get<T>(static_cast<size_t>(index));
375 376 377 378 379 380 381
		} catch (const exception &) {
			isNull = true;
		}

		return T();
	}

382
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
383

384
	void MainDb::init () {
385
		L_D();
Ronan's avatar
Ronan committed
386
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
387

Ronan's avatar
Ronan committed
388
		*session <<
389 390
			"CREATE TABLE IF NOT EXISTS sip_address ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
391
			"  value VARCHAR(255) UNIQUE NOT NULL"
392 393
			")";

394
		*session <<
395 396
			"CREATE TABLE IF NOT EXISTS content_type ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
397
			"  value VARCHAR(255) UNIQUE NOT NULL"
398 399
			")";

Ronan's avatar
Ronan committed
400
		*session <<
401 402
			"CREATE TABLE IF NOT EXISTS event ("
			"  id" + primaryKeyAutoIncrementStr() + ","
403
			"  type TINYINT UNSIGNED NOT NULL,"
404
			"  date DATE NOT NULL"
405 406
			")";

Ronan's avatar
Ronan committed
407
		*session <<
408
			"CREATE TABLE IF NOT EXISTS chat_room ("
409
			// Server (for conference) or user sip address.
410
			"  peer_sip_address_id INT UNSIGNED PRIMARY KEY,"
411 412

			// Dialog creation date.
413 414 415 416
			"  creation_date DATE NOT NULL,"

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

418 419 420
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

421 422 423
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
424
			"  last_notify_id INT UNSIGNED,"
425

426
			"  FOREIGN KEY (peer_sip_address_id)"
427 428 429 430
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

431 432 433 434 435 436
		*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,"

437
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
438 439 440 441 442 443
			"  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"
444 445 446 447 448 449 450 451 452 453 454 455 456 457 458
			")";

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

459 460 461 462 463 464 465 466 467 468
		*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"
			")";

469 470
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
471
			"  event_id INT UNSIGNED PRIMARY KEY,"
472 473
			"  participant_address_id INT UNSIGNED NOT NULL,"

474 475
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
476 477 478 479 480 481 482 483
			"    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 ("
484
			"  event_id INT UNSIGNED PRIMARY KEY,"
485 486
			"  gruu_address_id INT UNSIGNED NOT NULL,"

487 488
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_participant_event(event_id)"
489 490 491 492 493 494 495 496
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (gruu_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

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

500 501
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
502
			"    ON DELETE CASCADE"
503 504
			")";

Ronan's avatar
Ronan committed
505
		*session <<
506
			"CREATE TABLE IF NOT EXISTS message_event ("
507
			"  event_id INT UNSIGNED PRIMARY KEY,"
508 509 510
			"  chat_room_id INT UNSIGNED NOT NULL,"
			"  local_sip_address_id INT UNSIGNED NOT NULL,"
			"  remote_sip_address_id INT UNSIGNED NOT NULL,"
511 512 513 514

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

515 516
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
517
			"  is_secured BOOLEAN NOT NULL,"
518

519 520 521
			"  FOREIGN KEY (event_id)"
			"    REFERENCES event(id)"
			"    ON DELETE CASCADE,"
522 523
			"  FOREIGN KEY (chat_room_id)"
			"    REFERENCES chat_room(peer_sip_address_id)"
524
			"    ON DELETE CASCADE,"
525 526
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
527
			"    ON DELETE CASCADE,"
528
			"  FOREIGN KEY (remote_sip_address_id)"
529
			"    REFERENCES sip_address(id)"
530 531 532
			"    ON DELETE CASCADE"
			")";

533 534
		*session <<
			"CREATE TABLE IF NOT EXISTS message_participant ("
Ronan's avatar
Ronan committed
535
			"  event_id INT UNSIGNED NOT NULL,"
536 537 538
			"  sip_address_id INT UNSIGNED NOT NULL,"
			"  state TINYINT UNSIGNED NOT NULL,"

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

548
		*session <<
549
			"CREATE TABLE IF NOT EXISTS message_content ("
550
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
551
			"  event_id INT UNSIGNED NOT NULL,"
552
			"  content_type_id INT UNSIGNED NOT NULL,"
553
			"  body TEXT NOT NULL,"
554

Ronan's avatar
Ronan committed
555
			"  FOREIGN KEY (event_id)"
556
			"    REFERENCES message_event(event_id)"
557 558 559
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
560 561
			"    ON DELETE CASCADE"
			")";
562

563 564 565 566 567 568 569 570 571 572 573 574
		*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"
			")";

575 576
		*session <<
			"CREATE TABLE IF NOT EXISTS message_crypto_data ("
Ronan's avatar
Ronan committed
577
			"  event_id INT UNSIGNED NOT NULL,"
578
			"  key VARCHAR(255),"
579 580
			"  data BLOB,"

Ronan's avatar
Ronan committed
581 582
			"  PRIMARY KEY (event_id, key),"
			"  FOREIGN KEY (event_id)"
583
			"    REFERENCES message_event(event_id)"
584
			"    ON DELETE CASCADE"
585
			")";
586 587 588 589

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
590
			"CREATE TRIGGER IF NOT EXISTS message_participant_deleter"
591 592 593
			"  AFTER UPDATE OF state ON message_participant FOR EACH ROW"
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
594
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
595
			"    SELECT state FROM message_participant WHERE"
Ronan's avatar
Ronan committed
596
			"    NEW.event_id = message_participant.event_id"
597 598 599 600 601
			"    AND state <> ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += "    LIMIT 1"
			"  )) = 0"
			"  BEGIN"
Ronan's avatar
Ronan committed
602
			"  DELETE FROM message_participant WHERE NEW.event_id = message_participant.event_id;"
603 604
			"  UPDATE message_event SET state = ";
		participantMessageDeleter += displayedId;
Ronan's avatar
Ronan committed
605
		participantMessageDeleter += " WHERE event_id = NEW.event_id;"
606 607 608
			"  END";

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
609
	}
610

611
	bool MainDb::addEvent (const EventLog &eventLog) {
612 613
		L_D();

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

619 620 621 622 623 624
		bool soFarSoGood = false;

		L_BEGIN_LOG_EXCEPTION

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

Ronan's avatar
Ronan committed
625
		switch (eventLog.getType()) {
626
			case EventLog::Type::None:
Ronan's avatar
Ronan committed
627
				return false;
628

Ronan's avatar
Ronan committed
629
			case EventLog::Type::ChatMessage:
630 631 632
				d->insertMessageEvent(eventLog);
				break;

633 634
			case EventLog::Type::CallStart:
			case EventLog::Type::CallEnd:
635 636
				d->insertCallEvent(eventLog);
				break;
637

638 639
			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
640 641 642
				d->insertConferenceEvent(eventLog);
				break;

643 644 645 646
			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
647 648 649
				d->insertConferenceParticipantEvent(eventLog);
				break;

650 651
			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
652 653 654
				d->insertConferenceParticipantDeviceEvent(eventLog);
				break;

655
			case EventLog::Type::ConferenceSubjectChanged:
656
				d->insertConferenceSubjectEvent(eventLog);
Ronan's avatar
Ronan committed
657 658 659
				break;
		}

660 661 662 663 664 665 666
		tr.commit();

		soFarSoGood = true;

		L_END_LOG_EXCEPTION

		return soFarSoGood;
Ronan's avatar
Ronan committed
667 668
	}

669
	bool MainDb::deleteEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
670 671
		L_D();

Ronan's avatar
Ronan committed
672 673 674 675 676
		if (!isConnected()) {
			lWarning() << "Unable to delete event. Not connected.";
			return false;
		}

Ronan's avatar
Ronan committed
677 678
		long &storageId = const_cast<EventLog &>(eventLog).getPrivate()->storageId;
		if (storageId < 0)
Ronan's avatar
Ronan committed
679 680 681 682 683
			return false;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
684 685
		*session << "DELETE FROM event WHERE id = :id", soci::use(storageId);
		storageId = -1;
Ronan's avatar
Ronan committed
686 687 688

		L_END_LOG_EXCEPTION

Ronan's avatar
Ronan committed
689
		return storageId == -1;
Ronan's avatar
Ronan committed
690 691
	}

692
	void MainDb::cleanEvents (FilterMask mask) {
Ronan's avatar
Ronan committed
693 694
		L_D();

Ronan's avatar
Ronan committed
695 696 697 698 699
		if (!isConnected()) {
			lWarning() << "Unable to clean events. Not connected.";
			return;
		}

Ronan's avatar
Ronan committed
700 701 702 703 704 705 706 707 708
		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
709 710
	}

711
	int MainDb::getEventsCount (FilterMask mask) const {
712
		L_D();
Ronan's avatar
Ronan committed
713

Ronan's avatar
Ronan committed
714 715 716 717 718
		if (!isConnected()) {
			lWarning() << "Unable to get events count. Not connected.";
			return 0;
		}

Ronan's avatar
Ronan committed
719 720 721 722 723 724 725 726 727 728 729 730 731 732
		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;
	}

733
	int MainDb::getMessagesCount (const string &peerAddress) const {
734
		L_D();
735

Ronan's avatar
Ronan committed
736 737 738 739
		if (!isConnected()) {
			lWarning() << "Unable to get messages count. Not connected.";
			return 0;
		}
740

741 742 743 744 745
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
746 747 748 749 750 751

		string query = "SELECT COUNT(*) FROM message_event";
		if (peerAddress.empty())
			*session << query, soci::into(count);
		else {
			query += "  WHERE chat_room_id = ("
752
				"  SELECT id FROM sip_address WHERE value = :peerAddress"
Ronan's avatar
Ronan committed
753 754 755 756
				")";

			*session << query, soci::use(peerAddress), soci::into(count);
		}
757 758 759 760

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
761 762
	}

763
	int MainDb::getUnreadMessagesCount (const string &peerAddress) const {
764
		L_D();
765

Ronan's avatar
Ronan committed
766 767 768 769 770
		if (!isConnected()) {
			lWarning() << "Unable to get unread messages count. Not connected.";
			return 0;
		}

771 772
		int count = 0;

773 774 775 776 777 778 779
		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)) +
780
			+ "  AND state <> " + Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
781

782 783 784
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
785 786 787 788 789

		if (peerAddress.empty())
			*session << query, soci::into(count);
		else
			*session << query, soci::use(peerAddress), soci::into(count);
790 791 792 793

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
794 795
	}

796
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &peerAddress, int nLast, FilterMask mask) const {
Ronan's avatar
Ronan committed
797 798 799 800 801
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
802
		// TODO.
Ronan's avatar
Ronan committed
803
		(void)peerAddress;
Ronan's avatar
Ronan committed
804 805
		(void)nLast;
		(void)mask;
Ronan's avatar
Ronan committed
806
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
807 808
	}

809
	list<shared_ptr<EventLog>> MainDb::getHistory (
Ronan's avatar
Ronan committed
810
		const string &peerAddress,
811 812 813 814
		int begin,
		int end,
		FilterMask mask
	) const {
Ronan's avatar
Ronan committed
815 816 817 818 819
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
820
		// TODO.
Ronan's avatar
Ronan committed
821
		(void)peerAddress;
Ronan's avatar
Ronan committed
822 823 824
		(void)begin;
		(void)end;
		(void)mask;
Ronan's avatar
Ronan committed
825
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
826 827
	}

828
	void MainDb::cleanHistory (const string &peerAddress, FilterMask mask) {
829 830
		L_D();

Ronan's avatar
Ronan committed
831 832 833 834 835
		if (!isConnected()) {
			lWarning() << "Unable to clean history. Not connected.";
			return;
		}

836
		string query;
837
		if (mask == MainDb::NoFilter || mask & MessageFilter)
838 839 840 841 842 843 844 845 846
			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;

847 848 849
		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
853
	}
Ronan's avatar
Ronan committed
854

855 856
// -----------------------------------------------------------------------------

857 858 859 860 861 862
list<shared_ptr<ChatRoom>> MainDb::getChatRooms () const {
	list<shared_ptr<ChatRoom>> chatRooms;
	// TODO.
	return chatRooms;
}

863 864 865
shared_ptr<ChatRoom> MainDb::findChatRoom (const string &peerAddress) const {
	L_D();

866
	// TODO: Use core cache.
867

Ronan's avatar
Ronan committed
868
	L_BEGIN_LOG_EXCEPTION
869

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

Ronan's avatar
Ronan committed
872 873 874 875
	tm creationDate;
	tm lastUpdateDate;
	int capabilities;
	string subject;
876

Ronan's avatar
Ronan committed
877 878 879 880 881 882 883
	*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);

884 885
	// TODO.

Ronan's avatar
Ronan committed
886
	L_END_LOG_EXCEPTION
887 888 889 890 891 892 893

	return shared_ptr<ChatRoom>();
}

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

	bool MainDb::import (Backend, const string &parameters) {
894 895
		L_D();

896 897 898 899 900
		if (!isConnected()) {
			lWarning() << "Unable to import data. Not connected.";
			return 0;
		}

901