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

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

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

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

29
#include "chat/chat-room/chat-room.h"
30
#include "conference/participant.h"
31
#include "content/content-type.h"
Ronan's avatar
Ronan committed
32
#include "content/content.h"
33
#include "db/session/db-session-provider.h"
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
	static constexpr EnumToSql<MainDb::Filter> eventFilterToSql[] = {
Ronan's avatar
Ronan committed
69 70 71
		{ MainDb::ConferenceCallFilter, "1, 2" },
		{ MainDb::ConferenceChatMessageFilter, "5" },
		{ MainDb::ConferenceInfoFilter, "3, 4, 6, 7, 8, 9, 10, 11, 12" }
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
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
82 83 84 85 86
	static string buildSqlEventFilter (
		const list<MainDb::Filter> &filters,
		MainDb::FilterMask mask,
		const string &condKeyWord = "WHERE"
	) {
87
		L_ASSERT(
88 89
			find_if(filters.cbegin(), filters.cend(), [](const MainDb::Filter &filter) {
					return filter == MainDb::NoFilter;
90 91 92
				}) == filters.cend()
		);

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

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

			if (isStart) {
				isStart = false;
Ronan's avatar
Ronan committed
104
				sql += " " + condKeyWord + " type IN (";
105
			} else
Ronan's avatar
Ronan committed
106
				sql += ", ";
107 108 109
			sql += mapEventFilterToSql(filter);
		}

Ronan's avatar
Ronan committed
110 111 112
		if (!isStart)
			sql += ") ";

113 114 115
		return sql;
	}

116 117
// -----------------------------------------------------------------------------

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

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

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

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

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

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

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

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

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

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

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

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

175 176 177
		return sipAddressId;
	}

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

192
	void MainDbPrivate::insertChatMessageParticipant (long eventId, long sipAddressId, int state) {
193 194
		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::statement statement = (
195
			session->prepare << "UPDATE chat_message_participant SET state = :state"
Ronan's avatar
Ronan committed
196 197
				"  WHERE event_id = :eventId AND sip_address_id = :sipAddressId",
				soci::use(state), soci::use(eventId), soci::use(sipAddressId)
198 199
		);
		statement.execute(true);
200
		if (statement.get_affected_rows() == 0 && state != static_cast<int>(ChatMessage::State::Displayed))
201
			*session << "INSERT INTO chat_message_participant (event_id, sip_address_id, state)"
Ronan's avatar
Ronan committed
202 203
				"  VALUES (:eventId, :sipAddressId, :state)",
				soci::use(eventId), soci::use(sipAddressId), soci::use(state);
204 205
	}

Ronan's avatar
Ronan committed
206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270
// -----------------------------------------------------------------------------

	shared_ptr<EventLog> MainDbPrivate::selectEvent (long eventId, EventLog::Type type, time_t date) const {
		switch (type) {
			case EventLog::Type::None:
				return nullptr;

			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
				return selectConferenceEvent(eventId, type, date);

			case EventLog::Type::CallStart:
			case EventLog::Type::CallEnd:
				return selectConferenceCallEvent(eventId, type, date);

			case EventLog::Type::ConferenceChatMessage:
				return selectConferenceChatMessageEvent(eventId, type, date);

			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
				return selectConferenceParticipantEvent(eventId, type, date);

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
				return selectConferenceParticipantDeviceEvent(eventId, type, date);

			case EventLog::Type::ConferenceSubjectChanged:
				return selectConferenceSubjectEvent(eventId, type, date);
		}

		return nullptr;
	}

	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (long eventId, EventLog::Type type, time_t date) const {
		// TODO.
		return nullptr;
	}

	shared_ptr<EventLog> MainDbPrivate::selectConferenceCallEvent (long eventId, EventLog::Type type, time_t date) const {
		// TODO.
		return nullptr;
	}

	shared_ptr<EventLog> MainDbPrivate::selectConferenceChatMessageEvent (long eventId, EventLog::Type type, time_t date) const {
		// TODO.
		return nullptr;
	}

	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (long eventId, EventLog::Type type, time_t date) const {
		// TODO.
		return nullptr;
	}

	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (long eventId, EventLog::Type type, time_t date) const {
		// TODO.
		return nullptr;
	}

	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (long eventId, EventLog::Type type, time_t date) const {
		// TODO.
		return nullptr;
	}

271 272 273
// -----------------------------------------------------------------------------

	long MainDbPrivate::insertEvent (const EventLog &eventLog) {
274 275 276 277 278 279
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
		soci::use(static_cast<int>(eventLog.getType())), soci::use(Utils::getLongAsTm(eventLog.getTime()));
		return q->getLastInsertId();
280 281
	}

282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298
	long MainDbPrivate::insertConferenceEvent (const EventLog &eventLog, long *chatRoomId) {
		long eventId = insertEvent(eventLog);
		long curChatRoomId = insertSipAddress(
			static_cast<const ConferenceEvent &>(eventLog).getConferenceAddress().asString()
		);

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_event (event_id, chat_room_id)"
			"  VALUES (:eventId, :chatRoomId)", soci::use(eventId), soci::use(curChatRoomId);

		if (chatRoomId)
			*chatRoomId = curChatRoomId;

		return eventId;
	}

	long MainDbPrivate::insertConferenceCallEvent (const EventLog &eventLog) {
299 300 301 302
		// TODO.
		return 0;
	}

303 304
	long MainDbPrivate::insertConferenceChatMessageEvent (const EventLog &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_cast<const ConferenceChatMessageEvent &>(eventLog).getChatMessage();
305 306 307 308 309 310 311 312
		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()));

313 314 315 316
		long localSipAddressId = insertSipAddress(chatMessage->getLocalAddress().asString());
		long remoteSipAddressId = insertSipAddress(chatMessage->getRemoteAddress().asString());
		insertChatRoom(remoteSipAddressId, chatRoom->getCapabilities(), eventTime);
		long eventId = insertConferenceEvent(eventLog);
317

318
		soci::session *session = dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
319

320 321 322 323 324 325 326 327 328 329 330 331
		*session << "INSERT INTO conference_chat_message_event ("
			"  event_id, local_sip_address_id, remote_sip_address_id,"
			"  state, direction, imdn_message_id, is_secured"
			") VALUES ("
			"  :eventId, :localSipaddressId, :remoteSipaddressId,"
			"  :state, :direction, :imdnMessageId, :isSecured"
			")", soci::use(eventId), soci::use(localSipAddressId), soci::use(remoteSipAddressId),
			soci::use(static_cast<int>(chatMessage->getState())), soci::use(static_cast<int>(chatMessage->getDirection())),
			soci::use(chatMessage->getImdnMessageId()), soci::use(chatMessage->isSecured() ? 1 : 0);

		for (const auto &content : chatMessage->getContents())
			insertContent(eventId, content);
332

333 334 335
		return eventId;
	}

336
	long MainDbPrivate::insertConferenceNotifiedEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
337 338 339
		long chatRoomId;
		long eventId = insertConferenceEvent(eventLog, &chatRoomId);
		unsigned int lastNotifyId = static_cast<const ConferenceNotifiedEvent &>(eventLog).getNotifyId();
340 341 342

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
343 344 345
			"  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);
346

347 348 349 350 351
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantEvent (const EventLog &eventLog) {
		long eventId = insertConferenceNotifiedEvent(eventLog);
352 353 354 355
		long participantAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantEvent &>(eventLog).getParticipantAddress().asString()
		);

356
		soci::session *session = dbSession.getBackendSession<soci::session>();
357
		*session << "INSERT INTO conference_participant_event (event_id, participant_address_id)"
358
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
359

360 361 362 363
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantDeviceEvent (const EventLog &eventLog) {
364 365 366 367 368 369
		long eventId = insertConferenceParticipantEvent(eventLog);
		long gruuAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantDeviceEvent &>(eventLog).getGruuAddress().asString()
		);

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

373
		return eventId;
374 375 376
	}

	long MainDbPrivate::insertConferenceSubjectEvent (const EventLog &eventLog) {
377
		long eventId = insertConferenceNotifiedEvent(eventLog);
378 379

		soci::session *session = dbSession.getBackendSession<soci::session>();
380
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
381 382 383
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(
				static_cast<const ConferenceSubjectEvent &>(eventLog).getSubject()
			);
384

385
		return eventId;
386 387
	}

388
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
389

390
	void MainDb::init () {
391
		L_D();
Ronan's avatar
Ronan committed
392
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
393

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

400
		*session <<
401 402
			"CREATE TABLE IF NOT EXISTS content_type ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
403
			"  value VARCHAR(255) UNIQUE NOT NULL"
404 405
			")";

Ronan's avatar
Ronan committed
406
		*session <<
407 408
			"CREATE TABLE IF NOT EXISTS event ("
			"  id" + primaryKeyAutoIncrementStr() + ","
409
			"  type TINYINT UNSIGNED NOT NULL,"
410
			"  date DATE NOT NULL"
411 412
			")";

Ronan's avatar
Ronan committed
413
		*session <<
414
			"CREATE TABLE IF NOT EXISTS chat_room ("
415
			// Server (for conference) or user sip address.
416
			"  peer_sip_address_id INT UNSIGNED PRIMARY KEY,"
417 418

			// Dialog creation date.
419 420 421 422
			"  creation_date DATE NOT NULL,"

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

424 425 426
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

427 428 429
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
430
			"  last_notify_id INT UNSIGNED,"
431

432
			"  FOREIGN KEY (peer_sip_address_id)"
433 434 435 436
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

437 438 439 440 441 442
		*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,"

443
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
444 445 446 447 448 449
			"  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"
450 451 452 453 454 455 456 457 458 459 460 461 462 463 464
			")";

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

465 466 467 468 469 470 471 472 473 474
		*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"
			")";

475 476
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
477
			"  event_id INT UNSIGNED PRIMARY KEY,"
478 479
			"  participant_address_id INT UNSIGNED NOT NULL,"

480 481
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
482 483 484 485 486 487 488 489
			"    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 ("
490
			"  event_id INT UNSIGNED PRIMARY KEY,"
491 492
			"  gruu_address_id INT UNSIGNED NOT NULL,"

493 494
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_participant_event(event_id)"
495 496 497 498 499 500 501 502
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (gruu_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

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

506 507
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
508
			"    ON DELETE CASCADE"
509 510
			")";

Ronan's avatar
Ronan committed
511
		*session <<
512
			"CREATE TABLE IF NOT EXISTS conference_chat_message_event ("
513
			"  event_id INT UNSIGNED PRIMARY KEY,"
514 515
			"  local_sip_address_id INT UNSIGNED NOT NULL,"
			"  remote_sip_address_id INT UNSIGNED NOT NULL,"
516 517 518 519

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

520 521
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
522
			"  is_secured BOOLEAN NOT NULL,"
523

524
			"  FOREIGN KEY (event_id)"
525
			"    REFERENCES conference_event(id)"
526
			"    ON DELETE CASCADE,"
527 528
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
529
			"    ON DELETE CASCADE,"
530
			"  FOREIGN KEY (remote_sip_address_id)"
531
			"    REFERENCES sip_address(id)"
532 533 534
			"    ON DELETE CASCADE"
			")";

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

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

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

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

565
		*session <<
566 567
			"CREATE TABLE IF NOT EXISTS chat_message_content_app_data ("
			"  chat_message_content_id INT UNSIGNED NOT NULL,"
568 569 570
			"  key VARCHAR(255),"
			"  data BLOB,"

571 572 573
			"  PRIMARY KEY (chat_message_content_id, key),"
			"  FOREIGN KEY (chat_message_content_id)"
			"    REFERENCES chat_message_content(id)"
574 575 576
			"    ON DELETE CASCADE"
			")";

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

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

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

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
611
	}
612

613
	bool MainDb::addEvent (const EventLog &eventLog) {
614 615
		L_D();

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

621 622 623 624 625 626
		bool soFarSoGood = false;

		L_BEGIN_LOG_EXCEPTION

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

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

631 632
			case EventLog::Type::ConferenceChatMessage:
				d->insertConferenceChatMessageEvent(eventLog);
633 634
				break;

635 636
			case EventLog::Type::CallStart:
			case EventLog::Type::CallEnd:
637
				d->insertConferenceCallEvent(eventLog);
638
				break;
639

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

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

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

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

662 663 664 665 666 667 668
		tr.commit();

		soFarSoGood = true;

		L_END_LOG_EXCEPTION

		return soFarSoGood;
Ronan's avatar
Ronan committed
669 670
	}

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

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

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

		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION

Ronan's avatar
Ronan committed
691
		return storageId == -1;
Ronan's avatar
Ronan committed
692 693
	}

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

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

Ronan's avatar
Ronan committed
702
		string query = "DELETE FROM event" +
703
			buildSqlEventFilter({ ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter }, mask);
Ronan's avatar
Ronan committed
704 705 706 707 708 709 710

		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
711 712
	}

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

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

Ronan's avatar
Ronan committed
721
		string query = "SELECT COUNT(*) FROM event" +
722
			buildSqlEventFilter({ ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter }, mask);
Ronan's avatar
Ronan committed
723 724 725 726 727 728 729 730 731 732 733 734
		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;
	}

735
	int MainDb::getMessagesCount (const string &peerAddress) const {
736
		L_D();
737

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

743 744 745 746 747
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

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

749
		string query = "SELECT COUNT(*) FROM conference_chat_message_event";
Ronan's avatar
Ronan committed
750 751 752
		if (peerAddress.empty())
			*session << query, soci::into(count);
		else {
753 754 755 756
			query += "  WHERE event_id IN ("
				"  SELECT event_id FROM conference_event WHERE chat_room_id = ("
				"    SELECT id FROM sip_address WHERE value = :peerAddress"
				"  )"
Ronan's avatar
Ronan committed
757 758 759 760
				")";

			*session << query, soci::use(peerAddress), soci::into(count);
		}
761 762 763 764

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
765 766
	}

767
	int MainDb::getUnreadMessagesCount (const string &peerAddress) const {
768
		L_D();
769

Ronan's avatar
Ronan committed
770 771 772 773 774
		if (!isConnected()) {
			lWarning() << "Unable to get unread messages count. Not connected.";
			return 0;
		}

775 776
		int count = 0;

777
		string query = "SELECT COUNT(*) FROM conference_chat_message_event WHERE";
778
		if (!peerAddress.empty())
779 780 781 782 783
			query += " event_id IN ("
				"  SELECT event_id FROM conference_event WHERE chat_room_id = ("
				"    SELECT id FROM sip_address WHERE value = :peerAddress"
				"  )"
				") AND";
784 785

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

788 789 790
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
791 792 793 794 795

		if (peerAddress.empty())
			*session << query, soci::into(count);
		else
			*session << query, soci::use(peerAddress), soci::into(count);
796 797 798 799

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
800 801
	}

802
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &peerAddress, int nLast, FilterMask mask) const {
Ronan's avatar
Ronan committed
803 804 805 806 807
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
808
		// TODO.
Ronan's avatar
Ronan committed
809
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
810 811
	}

812
	list<shared_ptr<EventLog>> MainDb::getHistory (
Ronan's avatar
Ronan committed
813
		const string &peerAddress,
814 815 816 817
		int begin,
		int end,
		FilterMask mask
	) const {
Ronan's avatar
Ronan committed
818 819 820 821
		L_D();

		list<shared_ptr<EventLog>> events;

Ronan's avatar
Ronan committed
822 823
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
Ronan's avatar
Ronan committed
824
			return events;
Ronan's avatar
Ronan committed
825 826
		}

Ronan's avatar
Ronan committed
827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860
		if (begin < 0)
			begin = 0;

		if (end > 0 && begin > end) {
			lWarning() << "Unable to get history. Invalid range.";
			return events;
		}

		string query = "SELECT id, type, date FROM event"
			"  WHERE id IN ("
			"    SELECT event_id FROM conference_event WHERE chat_room_id = ("
			"      SELECT id FROM sip_address WHERE value = :peerAddress"
			"    )"
			"  )";
		query += buildSqlEventFilter({
			ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter
		}, mask, "AND");
		query += "  ORDER BY id DESC";

		if (end >= 0)
			query += "  LIMIT " + Utils::toString(end + 1 - begin);
		else
			query += "  LIMIT -1";

		if (begin > 0)
			query += "  OFFSET " + Utils::toString(begin);

		L_BEGIN_LOG_EXCEPTION

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

		soci::rowset<soci::row> rows = (session->prepare << query, soci::use(peerAddress));
		for (const auto &row : rows) {
Ronan's avatar
Ronan committed
861 862
			tm date = row.get<tm>(2);
			events.push_back(d->selectEvent(row.get<long>(0), static_cast<EventLog::Type>(row.get<int>(1)), mktime(&date)));
Ronan's avatar
Ronan committed
863 864 865 866 867
		}

		L_END_LOG_EXCEPTION

		return events;
Ronan's avatar
Ronan committed
868 869
	}

870
	void MainDb::cleanHistory (const string &peerAddress, FilterMask mask) {
871 872
		L_D();

Ronan's avatar
Ronan committed
873 874 875 876 877
		if (!isConnected()) {
			lWarning() << "Unable to clean history. Not connected.";
			return;
		}

Ronan's avatar
Ronan committed
878 879
		// TODO: Deal with mask.

880
		string query;
881 882 883
		if (mask == MainDb::NoFilter || mask & ConferenceChatMessageFilter)
			query += "SELECT event_id FROM conference_event WHERE chat_room_id = ("
				"  SELECT id FROM sip_address WHERE value = :peerAddress"
884 885 886 887 888
				")";

		if (query.empty())
			return;

889 890 891
		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
895
	}
Ronan's avatar
Ronan committed
896

897 898
// -----------------------------------------------------------------------------

899 900 901 902 903 904
list<shared_ptr<ChatRoom>> MainDb::getChatRooms () const {
	list<shared_ptr<ChatRoom>> chatRooms;
	// TODO.
	return chatRooms;
}

905 906 907
shared_ptr<ChatRoom> MainDb::findChatRoom (const string &peerAddress) const {
	L_D();

908
	// TODO: Use core cache.
909

Ronan's avatar
Ronan committed
910
	L_BEGIN_LOG_EXCEPTION
911

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

Ronan's avatar
Ronan committed
914 915 916 917
	tm creationDate;
	tm lastUpdateDate;
	int capabilities;
	string subject;
918

Ronan's avatar
Ronan committed
919 920 921 922 923 924 925
	*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);

926 927
	// TODO.

Ronan's avatar
Ronan committed
928