main-db.cpp 36.1 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
	shared_ptr<EventLog> MainDbPrivate::selectGenericConferenceEvent (
209
210
211
212
213
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
214
215
216
217
218
219
		switch (type) {
			case EventLog::Type::None:
				return nullptr;

			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
220
				return selectConferenceEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
221
222
223

			case EventLog::Type::CallStart:
			case EventLog::Type::CallEnd:
224
				return selectConferenceCallEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
225
226

			case EventLog::Type::ConferenceChatMessage:
227
				return selectConferenceChatMessageEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
228
229
230
231
232

			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
233
				return selectConferenceParticipantEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
234
235
236

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
237
				return selectConferenceParticipantDeviceEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
238
239

			case EventLog::Type::ConferenceSubjectChanged:
240
				return selectConferenceSubjectEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
241
242
243
244
245
		}

		return nullptr;
	}

246
247
248
249
250
251
	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
252
253
254
255
256
257
258
259
260
		// Useless here.
		(void)eventId;

		// TODO: Use cache.
		return make_shared<ConferenceEvent>(
			type,
			date,
			Address(peerAddress)
		);
Ronan's avatar
Ronan committed
261
262
	}

263
264
265
266
267
268
	shared_ptr<EventLog> MainDbPrivate::selectConferenceCallEvent (
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
269
270
271
272
		// TODO.
		return nullptr;
	}

273
274
275
276
277
278
	shared_ptr<EventLog> MainDbPrivate::selectConferenceChatMessageEvent (
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
279
280
281
282
		// TODO.
		return nullptr;
	}

283
284
285
286
287
288
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
289
290
291
292
		// TODO.
		return nullptr;
	}

293
294
295
296
297
298
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
299
300
301
302
		// TODO.
		return nullptr;
	}

303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
		long eventId,
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
		soci::session *session = dbSession.getBackendSession<soci::session>();

		unsigned int notifyId;
		string subject;

		*session << "SELECT notify_id, subject FROM conference_subject_event WHERE event_id = :eventId",
			soci::use(eventId), soci::into(notifyId), soci::into(subject);

		// TODO: Use cache.
		return make_shared<ConferenceSubjectEvent>(
			date,
			Address(peerAddress),
			notifyId,
			subject
		);
Ronan's avatar
Ronan committed
324
325
	}

326
327
328
// -----------------------------------------------------------------------------

	long MainDbPrivate::insertEvent (const EventLog &eventLog) {
329
330
331
332
333
334
		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();
335
336
	}

337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
	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) {
354
355
356
357
		// TODO.
		return 0;
	}

358
359
	long MainDbPrivate::insertConferenceChatMessageEvent (const EventLog &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_cast<const ConferenceChatMessageEvent &>(eventLog).getChatMessage();
360
361
362
363
364
365
366
367
		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()));

368
369
370
371
		long localSipAddressId = insertSipAddress(chatMessage->getLocalAddress().asString());
		long remoteSipAddressId = insertSipAddress(chatMessage->getRemoteAddress().asString());
		insertChatRoom(remoteSipAddressId, chatRoom->getCapabilities(), eventTime);
		long eventId = insertConferenceEvent(eventLog);
372

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

375
376
377
378
379
380
381
382
383
384
385
386
		*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);
387

388
389
390
		return eventId;
	}

391
	long MainDbPrivate::insertConferenceNotifiedEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
392
393
394
		long chatRoomId;
		long eventId = insertConferenceEvent(eventLog, &chatRoomId);
		unsigned int lastNotifyId = static_cast<const ConferenceNotifiedEvent &>(eventLog).getNotifyId();
395
396
397

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
398
399
400
			"  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);
401

402
403
404
405
406
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantEvent (const EventLog &eventLog) {
		long eventId = insertConferenceNotifiedEvent(eventLog);
407
408
409
410
		long participantAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantEvent &>(eventLog).getParticipantAddress().asString()
		);

411
		soci::session *session = dbSession.getBackendSession<soci::session>();
412
		*session << "INSERT INTO conference_participant_event (event_id, participant_address_id)"
413
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
414

415
416
417
418
		return eventId;
	}

	long MainDbPrivate::insertConferenceParticipantDeviceEvent (const EventLog &eventLog) {
419
420
421
422
423
424
		long eventId = insertConferenceParticipantEvent(eventLog);
		long gruuAddressId = insertSipAddress(
			static_cast<const ConferenceParticipantDeviceEvent &>(eventLog).getGruuAddress().asString()
		);

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

428
		return eventId;
429
430
431
	}

	long MainDbPrivate::insertConferenceSubjectEvent (const EventLog &eventLog) {
432
		long eventId = insertConferenceNotifiedEvent(eventLog);
433
434

		soci::session *session = dbSession.getBackendSession<soci::session>();
435
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
436
437
438
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(
				static_cast<const ConferenceSubjectEvent &>(eventLog).getSubject()
			);
439

440
		return eventId;
441
442
	}

443
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
444

445
	void MainDb::init () {
446
		L_D();
Ronan's avatar
Ronan committed
447
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
448

Ronan's avatar
Ronan committed
449
		*session <<
450
451
			"CREATE TABLE IF NOT EXISTS sip_address ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
452
			"  value VARCHAR(255) UNIQUE NOT NULL"
453
454
			")";

455
		*session <<
456
457
			"CREATE TABLE IF NOT EXISTS content_type ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
458
			"  value VARCHAR(255) UNIQUE NOT NULL"
459
460
			")";

Ronan's avatar
Ronan committed
461
		*session <<
462
463
			"CREATE TABLE IF NOT EXISTS event ("
			"  id" + primaryKeyAutoIncrementStr() + ","
464
			"  type TINYINT UNSIGNED NOT NULL,"
465
			"  date DATE NOT NULL"
466
467
			")";

Ronan's avatar
Ronan committed
468
		*session <<
469
			"CREATE TABLE IF NOT EXISTS chat_room ("
470
			// Server (for conference) or user sip address.
471
			"  peer_sip_address_id INT UNSIGNED PRIMARY KEY,"
472
473

			// Dialog creation date.
474
475
476
477
			"  creation_date DATE NOT NULL,"

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

479
480
481
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

482
483
484
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
485
			"  last_notify_id INT UNSIGNED,"
486

487
			"  FOREIGN KEY (peer_sip_address_id)"
488
489
490
491
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

492
493
494
495
496
497
		*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,"

498
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
499
500
501
502
503
504
			"  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"
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
			")";

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

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

530
531
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
532
			"  event_id INT UNSIGNED PRIMARY KEY,"
533
534
			"  participant_address_id INT UNSIGNED NOT NULL,"

535
536
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
537
538
539
540
541
542
543
544
			"    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 ("
545
			"  event_id INT UNSIGNED PRIMARY KEY,"
546
547
			"  gruu_address_id INT UNSIGNED NOT NULL,"

548
549
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_participant_event(event_id)"
550
551
552
553
554
555
556
557
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (gruu_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_subject_event ("
558
			"  event_id INT UNSIGNED PRIMARY KEY,"
559
560
			"  subject VARCHAR(255),"

561
562
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
563
			"    ON DELETE CASCADE"
564
565
			")";

Ronan's avatar
Ronan committed
566
		*session <<
567
			"CREATE TABLE IF NOT EXISTS conference_chat_message_event ("
568
			"  event_id INT UNSIGNED PRIMARY KEY,"
569
570
			"  local_sip_address_id INT UNSIGNED NOT NULL,"
			"  remote_sip_address_id INT UNSIGNED NOT NULL,"
571
572
573
574

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

575
576
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
577
			"  is_secured BOOLEAN NOT NULL,"
578

579
			"  FOREIGN KEY (event_id)"
580
			"    REFERENCES conference_event(id)"
581
			"    ON DELETE CASCADE,"
582
583
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
584
			"    ON DELETE CASCADE,"
585
			"  FOREIGN KEY (remote_sip_address_id)"
586
			"    REFERENCES sip_address(id)"
587
588
589
			"    ON DELETE CASCADE"
			")";

590
		*session <<
591
			"CREATE TABLE IF NOT EXISTS chat_message_participant ("
Ronan's avatar
Ronan committed
592
			"  event_id INT UNSIGNED NOT NULL,"
593
594
595
			"  sip_address_id INT UNSIGNED NOT NULL,"
			"  state TINYINT UNSIGNED NOT NULL,"

Ronan's avatar
Ronan committed
596
597
			"  PRIMARY KEY (event_id, sip_address_id),"
			"  FOREIGN KEY (event_id)"
598
			"    REFERENCES conference_chat_message_event(event_id)"
599
600
601
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
602
			"    ON DELETE CASCADE"
603
604
			")";

605
		*session <<
606
			"CREATE TABLE IF NOT EXISTS chat_message_content ("
607
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
608
			"  event_id INT UNSIGNED NOT NULL,"
609
			"  content_type_id INT UNSIGNED NOT NULL,"
610
			"  body TEXT NOT NULL,"
611

Ronan's avatar
Ronan committed
612
			"  FOREIGN KEY (event_id)"
613
			"    REFERENCES conference_chat_message_event(event_id)"
614
615
616
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
617
618
			"    ON DELETE CASCADE"
			")";
619

620
		*session <<
621
622
			"CREATE TABLE IF NOT EXISTS chat_message_content_app_data ("
			"  chat_message_content_id INT UNSIGNED NOT NULL,"
623
624
625
			"  key VARCHAR(255),"
			"  data BLOB,"

626
627
628
			"  PRIMARY KEY (chat_message_content_id, key),"
			"  FOREIGN KEY (chat_message_content_id)"
			"    REFERENCES chat_message_content(id)"
629
630
631
			"    ON DELETE CASCADE"
			")";

632
		*session <<
633
			"CREATE TABLE IF NOT EXISTS conference_message_crypto_data ("
Ronan's avatar
Ronan committed
634
			"  event_id INT UNSIGNED NOT NULL,"
635
			"  key VARCHAR(255),"
636
637
			"  data BLOB,"

Ronan's avatar
Ronan committed
638
639
			"  PRIMARY KEY (event_id, key),"
			"  FOREIGN KEY (event_id)"
640
			"    REFERENCES conference_chat_message_event(event_id)"
641
			"    ON DELETE CASCADE"
642
			")";
643
644
645
646

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
647
648
			"CREATE TRIGGER IF NOT EXISTS chat_message_participant_deleter"
			"  AFTER UPDATE OF state ON chat_message_participant FOR EACH ROW"
649
650
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
651
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
652
653
			"    SELECT state FROM chat_message_participant WHERE"
			"    NEW.event_id = chat_message_participant.event_id"
654
655
656
657
658
			"    AND state <> ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += "    LIMIT 1"
			"  )) = 0"
			"  BEGIN"
659
660
			"  DELETE FROM chat_message_participant WHERE NEW.event_id = chat_message_participant.event_id;"
			"  UPDATE conference_chat_message_event SET state = ";
661
		participantMessageDeleter += displayedId;
Ronan's avatar
Ronan committed
662
		participantMessageDeleter += " WHERE event_id = NEW.event_id;"
663
664
665
			"  END";

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
666
	}
667

668
	bool MainDb::addEvent (const EventLog &eventLog) {
669
670
		L_D();

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

676
677
678
679
680
681
		bool soFarSoGood = false;

		L_BEGIN_LOG_EXCEPTION

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

Ronan's avatar
Ronan committed
682
		switch (eventLog.getType()) {
683
			case EventLog::Type::None:
Ronan's avatar
Ronan committed
684
				return false;
685

686
687
			case EventLog::Type::ConferenceChatMessage:
				d->insertConferenceChatMessageEvent(eventLog);
688
689
				break;

690
691
			case EventLog::Type::CallStart:
			case EventLog::Type::CallEnd:
692
				d->insertConferenceCallEvent(eventLog);
693
				break;
694

695
696
			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
697
698
699
				d->insertConferenceEvent(eventLog);
				break;

700
701
702
703
			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
704
705
706
				d->insertConferenceParticipantEvent(eventLog);
				break;

707
708
			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
709
710
711
				d->insertConferenceParticipantDeviceEvent(eventLog);
				break;

712
			case EventLog::Type::ConferenceSubjectChanged:
713
				d->insertConferenceSubjectEvent(eventLog);
Ronan's avatar
Ronan committed
714
715
716
				break;
		}

717
718
719
720
721
722
723
		tr.commit();

		soFarSoGood = true;

		L_END_LOG_EXCEPTION

		return soFarSoGood;
Ronan's avatar
Ronan committed
724
725
	}

726
	bool MainDb::deleteEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
727
728
		L_D();

Ronan's avatar
Ronan committed
729
730
731
732
733
		if (!isConnected()) {
			lWarning() << "Unable to delete event. Not connected.";
			return false;
		}

Ronan's avatar
Ronan committed
734
735
		long &storageId = const_cast<EventLog &>(eventLog).getPrivate()->storageId;
		if (storageId < 0)
Ronan's avatar
Ronan committed
736
737
738
739
740
			return false;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
741
742
		*session << "DELETE FROM event WHERE id = :id", soci::use(storageId);
		storageId = -1;
Ronan's avatar
Ronan committed
743
744
745

		L_END_LOG_EXCEPTION

Ronan's avatar
Ronan committed
746
		return storageId == -1;
Ronan's avatar
Ronan committed
747
748
	}

749
	void MainDb::cleanEvents (FilterMask mask) {
Ronan's avatar
Ronan committed
750
751
		L_D();

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

Ronan's avatar
Ronan committed
757
		string query = "DELETE FROM event" +
758
			buildSqlEventFilter({ ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter }, mask);
Ronan's avatar
Ronan committed
759
760
761
762
763
764
765

		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
766
767
	}

768
	int MainDb::getEventsCount (FilterMask mask) const {
769
		L_D();
Ronan's avatar
Ronan committed
770

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

Ronan's avatar
Ronan committed
776
		string query = "SELECT COUNT(*) FROM event" +
777
			buildSqlEventFilter({ ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter }, mask);
Ronan's avatar
Ronan committed
778
779
780
781
782
783
784
785
786
787
788
789
		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;
	}

790
	int MainDb::getMessagesCount (const string &peerAddress) const {
791
		L_D();
792

Ronan's avatar
Ronan committed
793
794
795
796
		if (!isConnected()) {
			lWarning() << "Unable to get messages count. Not connected.";
			return 0;
		}
797

798
799
800
801
802
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

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

804
		string query = "SELECT COUNT(*) FROM conference_chat_message_event";
Ronan's avatar
Ronan committed
805
806
807
		if (peerAddress.empty())
			*session << query, soci::into(count);
		else {
808
809
810
811
			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
812
813
814
815
				")";

			*session << query, soci::use(peerAddress), soci::into(count);
		}
816
817
818
819

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
820
821
	}

822
	int MainDb::getUnreadMessagesCount (const string &peerAddress) const {
823
		L_D();
824

Ronan's avatar
Ronan committed
825
826
827
828
829
		if (!isConnected()) {
			lWarning() << "Unable to get unread messages count. Not connected.";
			return 0;
		}

830
831
		int count = 0;

832
		string query = "SELECT COUNT(*) FROM conference_chat_message_event WHERE";
833
		if (!peerAddress.empty())
834
835
836
837
838
			query += " event_id IN ("
				"  SELECT event_id FROM conference_event WHERE chat_room_id = ("
				"    SELECT id FROM sip_address WHERE value = :peerAddress"
				"  )"
				") AND";
839
840

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

843
844
845
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
846
847
848
849
850

		if (peerAddress.empty())
			*session << query, soci::into(count);
		else
			*session << query, soci::use(peerAddress), soci::into(count);
851
852
853
854

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
855
856
	}

857
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &peerAddress, int nLast, FilterMask mask) const {
Ronan's avatar
Ronan committed
858
859
860
861
862
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
863
		// TODO.
Ronan's avatar
Ronan committed
864
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
865
866
	}

867
	list<shared_ptr<EventLog>> MainDb::getHistory (
Ronan's avatar
Ronan committed
868
		const string &peerAddress,
869
870
871
872
		int begin,
		int end,
		FilterMask mask
	) const {
Ronan's avatar
Ronan committed
873
874
875
876
		L_D();

		list<shared_ptr<EventLog>> events;

Ronan's avatar
Ronan committed
877
878
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
Ronan's avatar
Ronan committed
879
			return events;
Ronan's avatar
Ronan committed
880
881
		}

Ronan's avatar
Ronan committed
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
		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
916
			tm date = row.get<tm>(2);
917
			events.push_back(d->selectGenericConferenceEvent(
918
919
920
921
922
				row.get<long>(0),
				static_cast<EventLog::Type>(row.get<int>(1)),
				mktime(&date),
				peerAddress
			));
Ronan's avatar
Ronan committed
923
924
925
926
927
		}

		L_END_LOG_EXCEPTION

		return events;
Ronan's avatar
Ronan committed
928
929
	}

930
	void MainDb::cleanHistory (const string &peerAddress, FilterMask mask) {
931
932
		L_D();

Ronan's avatar
Ronan committed
933
934
935
936
937
		if (!isConnected()) {
			lWarning() << "Unable to clean history. Not connected.";
			return;
		}

Ronan's avatar
Ronan committed
938
939
		// TODO: Deal with mask.

940
		string query;
941
942
943
		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"
944
945
946
947
948
				")";

		if (query.empty())
			return;

949
950
951
		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
955
	}
Ronan's avatar
Ronan committed
956

957
958
// -----------------------------------------------------------------------------

959
960
961
962
963
964
list<shared_ptr<ChatRoom>> MainDb::getChatRooms () const {
	list<shared_ptr<ChatRoom>> chatRooms;
	// TODO.
	return chatRooms;
}

965
966
967
shared_ptr<ChatRoom> MainDb::findChatRoom (const string &peerAddress) const {
	L_D();

968
	// TODO: Use core cache.
969

Ronan's avatar
Ronan committed
970
	L_BEGIN_LOG_EXCEPTION
971

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

Ronan's avatar
Ronan committed
974
975
976
977
	tm creationDate;
	tm lastUpdateDate;
	int capabilities;
	string subject;
978

Ronan's avatar
Ronan committed
979
980
981
982
983
984
985
	*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);

986
987
	// TODO.

Ronan's avatar
Ronan committed
988
	L_END_LOG_EXCEPTION
989
990
991
992
993
994

	return shared_ptr<ChatRoom>();
}

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

995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
	#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

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

		try {
			return message.get<T>(static_cast<size_t>(index));
		} catch (const exception &) {
			isNull = true;
		}

		return T();
	}

1021
	bool MainDb::import (Backend, const string &parameters) {
1022
1023
		L_D();

1024
1025
1026
1027
1028
		if (!isConnected()) {
			lWarning() << "Unable to import data. Not connected.";
			return 0;
		}

1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
		// 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 {
1042
			soci::rowset<soci::row> messages = (inSession->prepare << "SELECT * FROM history");
1043
			try {
Ronan's avatar
Ronan committed
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
				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);
					}