main-db.cpp 39.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 long MainDbPrivate::insertSipAddress (const string &sipAddress) {
119
120
121
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

122
		long 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
	}

131
	void MainDbPrivate::insertContent (long long eventId, const Content &content) {
132
133
		L_Q();

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

136
		long 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
		long long messageContentId = q->getLastInsertId();
142
		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 long MainDbPrivate::insertContentType (const string &contentType) {
149
150
151
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

152
		long 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 long MainDbPrivate::insertChatRoom (long long sipAddressId, int capabilities, const tm &date) {
162
163
		soci::session *session = dbSession.getBackendSession<soci::session>();

164
		long 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 long chatRoomId, long 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 long eventId, long 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
		long long eventId,
210
211
212
213
		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::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
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
	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
247
		long long eventId,
248
249
250
251
		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
	shared_ptr<EventLog> MainDbPrivate::selectConferenceCallEvent (
264
		long long eventId,
265
266
267
268
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
269
270
271
272
		// TODO.
		return nullptr;
	}

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

283
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
284
		long long eventId,
285
286
287
288
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
289
290
291
292
		unsigned int notifyId;
		string participantAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
293
294
		*session << "SELECT notify_id, participant_address.value"
			"  FROM conference_notified_event, conference_participant_event, sip_address as participant_address"
295
			"  WHERE conference_participant_event.event_id = :eventId"
Ronan's avatar
Ronan committed
296
297
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
			"    AND participant_address.id = participant_address_id",
298
299
300
301
302
303
304
305
306
307
			soci::into(notifyId), soci::into(participantAddress), soci::use(eventId);

		// TODO: Use cache.
		return make_shared<ConferenceParticipantEvent>(
			type,
			date,
			Address(peerAddress),
			notifyId,
			Address(participantAddress)
		);
Ronan's avatar
Ronan committed
308
309
	}

310
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
311
		long long eventId,
312
313
314
315
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
316
317
318
319
320
		unsigned int notifyId;
		string participantAddress;
		string gruuAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
321
322
323
		*session << "SELECT notify_id, participant_address.value, gruu_address.value"
			"  FROM conference_notified_event, conference_participant_event, conference_participant_device_event,"
			"    sip_address AS participant_address, sip_address AS gruu_address"
324
325
			"  WHERE conference_participant_device_event.event_id = :eventId"
			"    AND conference_participant_event.event_id = conference_participant_device_event.event_id"
326
327
328
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
			"    AND participant_address.id = participant_address_id"
			"    AND gruu_address.id = gruu_address_id",
329
330
331
332
333
334
335
336
337
338
339
			soci::into(notifyId), soci::into(participantAddress), soci::into(gruuAddress), soci::use(eventId);

		// TODO: Use cache.
		return make_shared<ConferenceParticipantDeviceEvent>(
			type,
			date,
			Address(peerAddress),
			notifyId,
			Address(participantAddress),
			Address(gruuAddress)
		);
Ronan's avatar
Ronan committed
340
341
	}

342
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
343
		long long eventId,
344
345
346
347
348
349
350
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
		unsigned int notifyId;
		string subject;

351
352
353
354
355
356
		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "SELECT notify_id, subject"
			"  FROM conference_notified_event, conference_subject_event"
			"  WHERE conference_subject_event.event_id = :eventId"
			"    AND conference_notified_event.event_id = conference_subject_event.event_id",
			soci::into(notifyId), soci::into(subject), soci::use(eventId);
357
358
359
360
361
362
363
364

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

367
368
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
369
	long long MainDbPrivate::insertEvent (const shared_ptr<EventLog> &eventLog) {
370
371
372
373
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
Ronan's avatar
Ronan committed
374
		soci::use(static_cast<int>(eventLog->getType())), soci::use(Utils::getLongAsTm(eventLog->getTime()));
375
		return q->getLastInsertId();
376
377
	}

Ronan's avatar
Ronan committed
378
	long long MainDbPrivate::insertConferenceEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
379
380
		long long eventId = insertEvent(eventLog);
		long long curChatRoomId = insertSipAddress(
Ronan's avatar
Ronan committed
381
			static_pointer_cast<ConferenceEvent>(eventLog)->getConferenceAddress().asString()
382
383
384
385
386
387
388
389
390
391
392
393
		);

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

Ronan's avatar
Ronan committed
394
	long long MainDbPrivate::insertConferenceCallEvent (const shared_ptr<EventLog> &eventLog) {
395
396
397
398
		// TODO.
		return 0;
	}

Ronan's avatar
Ronan committed
399
400
	long long MainDbPrivate::insertConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
401
402
403
404
405
406
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

Ronan's avatar
Ronan committed
407
		tm eventTime = Utils::getLongAsTm(static_cast<long>(eventLog->getTime()));
408

409
410
		long long localSipAddressId = insertSipAddress(chatMessage->getLocalAddress().asString());
		long long remoteSipAddressId = insertSipAddress(chatMessage->getRemoteAddress().asString());
411
		insertChatRoom(remoteSipAddressId, chatRoom->getCapabilities(), eventTime);
412
		long long eventId = insertConferenceEvent(eventLog);
413

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

416
417
418
419
420
421
422
423
424
425
426
427
		*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);
428

429
430
431
		return eventId;
	}

Ronan's avatar
Ronan committed
432
	long long MainDbPrivate::insertConferenceNotifiedEvent (const shared_ptr<EventLog> &eventLog) {
433
434
		long long chatRoomId;
		long long eventId = insertConferenceEvent(eventLog, &chatRoomId);
Ronan's avatar
Ronan committed
435
		unsigned int lastNotifyId = static_pointer_cast<ConferenceNotifiedEvent>(eventLog)->getNotifyId();
436
437
438

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
439
440
441
			"  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);
442

443
444
445
		return eventId;
	}

Ronan's avatar
Ronan committed
446
	long long MainDbPrivate::insertConferenceParticipantEvent (const shared_ptr<EventLog> &eventLog) {
447
448
		long long eventId = insertConferenceNotifiedEvent(eventLog);
		long long participantAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
449
			static_pointer_cast<ConferenceParticipantEvent>(eventLog)->getParticipantAddress().asString()
450
451
		);

452
		soci::session *session = dbSession.getBackendSession<soci::session>();
453
		*session << "INSERT INTO conference_participant_event (event_id, participant_address_id)"
454
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
455

456
457
458
		return eventId;
	}

Ronan's avatar
Ronan committed
459
	long long MainDbPrivate::insertConferenceParticipantDeviceEvent (const shared_ptr<EventLog> &eventLog) {
460
461
		long long eventId = insertConferenceParticipantEvent(eventLog);
		long long gruuAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
462
			static_pointer_cast<ConferenceParticipantDeviceEvent>(eventLog)->getGruuAddress().asString()
463
464
465
		);

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

469
		return eventId;
470
471
	}

Ronan's avatar
Ronan committed
472
	long long MainDbPrivate::insertConferenceSubjectEvent (const shared_ptr<EventLog> &eventLog) {
473
		long long eventId = insertConferenceNotifiedEvent(eventLog);
474
475

		soci::session *session = dbSession.getBackendSession<soci::session>();
476
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
477
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(
Ronan's avatar
Ronan committed
478
				static_pointer_cast<ConferenceSubjectEvent>(eventLog)->getSubject()
479
			);
480

481
		return eventId;
482
483
	}

484
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
485

486
	void MainDb::init () {
487
		L_D();
Ronan's avatar
Ronan committed
488
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
489

Ronan's avatar
Ronan committed
490
		*session <<
491
			"CREATE TABLE IF NOT EXISTS sip_address ("
492
			"  id" + primaryKeyStr("UNSIGNED BIGINT") + ","
Ronan's avatar
Ronan committed
493
			"  value VARCHAR(255) UNIQUE NOT NULL"
494
495
			")";

496
		*session <<
497
			"CREATE TABLE IF NOT EXISTS content_type ("
498
			"  id" + primaryKeyStr("UNSIGNED SMALLINT") + ","
Ronan's avatar
Ronan committed
499
			"  value VARCHAR(255) UNIQUE NOT NULL"
500
501
			")";

Ronan's avatar
Ronan committed
502
		*session <<
503
			"CREATE TABLE IF NOT EXISTS event ("
504
			"  id" + primaryKeyStr("UNSIGNED BIGINT") + ","
505
			"  type TINYINT UNSIGNED NOT NULL,"
506
			"  date DATE NOT NULL"
507
508
			")";

Ronan's avatar
Ronan committed
509
		*session <<
510
			"CREATE TABLE IF NOT EXISTS chat_room ("
511
			// Server (for conference) or user sip address.
512
			"  peer_sip_address_id" + primaryKeyStr("UNSIGNED BIGINT") + ","
513
514

			// Dialog creation date.
515
516
517
518
			"  creation_date DATE NOT NULL,"

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

520
521
522
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

523
524
525
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
526
			"  last_notify_id INT UNSIGNED,"
527

528
			"  FOREIGN KEY (peer_sip_address_id)"
529
530
531
532
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

533
534
		*session <<
			"CREATE TABLE IF NOT EXISTS chat_room_participant ("
535
536
537
			"  chat_room_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
			"  sip_address_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","

538
539
			"  is_admin BOOLEAN NOT NULL,"

540
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
541
542
543
544
545
546
			"  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"
547
548
549
550
			")";

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_event ("
551
552
553
			"  event_id" + primaryKeyStr("UNSIGNED BIGINT") + ","

			"  chat_room_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
554
555
556
557
558
559
560
561
562

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

563
564
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_notified_event ("
565
566
			"  event_id" + primaryKeyStr("UNSIGNED BIGINT") + ","

567
568
569
570
571
572
573
			"  notify_id INT UNSIGNED NOT NULL,"

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

574
575
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
576
577
578
			"  event_id" + primaryKeyStr("UNSIGNED BIGINT") + ","

			"  participant_address_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
579

580
581
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
582
583
584
585
586
587
588
589
			"    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 ("
590
591
592
			"  event_id" + primaryKeyStr("UNSIGNED BIGINT") + ","

			"  gruu_address_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
593

594
595
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_participant_event(event_id)"
596
597
598
599
600
601
602
603
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (gruu_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_subject_event ("
604
605
			"  event_id" + primaryKeyStr("BIGINT") + ","

606
607
			"  subject VARCHAR(255),"

608
609
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
610
			"    ON DELETE CASCADE"
611
612
			")";

Ronan's avatar
Ronan committed
613
		*session <<
614
			"CREATE TABLE IF NOT EXISTS conference_chat_message_event ("
615
616
617
618
			"  event_id" + primaryKeyStr("UNSIGNED BIGINT") + ","

			"  local_sip_address_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
			"  remote_sip_address_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
619
620
621
622

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

623
624
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
625
			"  is_secured BOOLEAN NOT NULL,"
626

627
			"  FOREIGN KEY (event_id)"
628
			"    REFERENCES conference_event(id)"
629
			"    ON DELETE CASCADE,"
630
631
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
632
			"    ON DELETE CASCADE,"
633
			"  FOREIGN KEY (remote_sip_address_id)"
634
			"    REFERENCES sip_address(id)"
635
636
637
			"    ON DELETE CASCADE"
			")";

638
		*session <<
639
			"CREATE TABLE IF NOT EXISTS chat_message_participant ("
640
641
			"  event_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
			"  sip_address_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","
642
643
			"  state TINYINT UNSIGNED NOT NULL,"

Ronan's avatar
Ronan committed
644
645
			"  PRIMARY KEY (event_id, sip_address_id),"
			"  FOREIGN KEY (event_id)"
646
			"    REFERENCES conference_chat_message_event(event_id)"
647
648
649
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
650
			"    ON DELETE CASCADE"
651
652
			")";

653
		*session <<
654
			"CREATE TABLE IF NOT EXISTS chat_message_content ("
655
656
657
658
			"  id" + primaryKeyStr("UNSIGNED BIGINT") + ","

			"  event_id " + primaryKeyRefStr("UNSIGNED BIGINT") + ","
			"  content_type_id" + primaryKeyRefStr("UNSIGNED SMALLINT") + ","
659
			"  body TEXT NOT NULL,"
660

Ronan's avatar
Ronan committed
661
			"  FOREIGN KEY (event_id)"
662
			"    REFERENCES conference_chat_message_event(event_id)"
663
664
665
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
666
667
			"    ON DELETE CASCADE"
			")";
668

669
		*session <<
670
			"CREATE TABLE IF NOT EXISTS chat_message_content_app_data ("
671
672
			"  chat_message_content_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","

673
674
675
			"  key VARCHAR(255),"
			"  data BLOB,"

676
677
678
			"  PRIMARY KEY (chat_message_content_id, key),"
			"  FOREIGN KEY (chat_message_content_id)"
			"    REFERENCES chat_message_content(id)"
679
680
681
			"    ON DELETE CASCADE"
			")";

682
		*session <<
683
			"CREATE TABLE IF NOT EXISTS conference_message_crypto_data ("
684
685
			"  event_id" + primaryKeyRefStr("UNSIGNED BIGINT") + ","

686
			"  key VARCHAR(255),"
687
688
			"  data BLOB,"

Ronan's avatar
Ronan committed
689
690
			"  PRIMARY KEY (event_id, key),"
			"  FOREIGN KEY (event_id)"
691
			"    REFERENCES conference_chat_message_event(event_id)"
692
			"    ON DELETE CASCADE"
693
			")";
694
695
696
697

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
698
699
			"CREATE TRIGGER IF NOT EXISTS chat_message_participant_deleter"
			"  AFTER UPDATE OF state ON chat_message_participant FOR EACH ROW"
700
701
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
702
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
703
704
			"    SELECT state FROM chat_message_participant WHERE"
			"    NEW.event_id = chat_message_participant.event_id"
705
706
707
708
709
			"    AND state <> ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += "    LIMIT 1"
			"  )) = 0"
			"  BEGIN"
710
711
			"  DELETE FROM chat_message_participant WHERE NEW.event_id = chat_message_participant.event_id;"
			"  UPDATE conference_chat_message_event SET state = ";
712
		participantMessageDeleter += displayedId;
Ronan's avatar
Ronan committed
713
		participantMessageDeleter += " WHERE event_id = NEW.event_id;"
714
715
716
			"  END";

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
717
	}
718

Ronan's avatar
Ronan committed
719
	bool MainDb::addEvent (const shared_ptr<EventLog> &eventLog) {
720
721
		L_D();

Ronan's avatar
Ronan committed
722
723
		if (!isConnected()) {
			lWarning() << "Unable to add event. Not connected.";
724
			return false;
Ronan's avatar
Ronan committed
725
726
		}

727
728
729
730
731
732
		bool soFarSoGood = false;

		L_BEGIN_LOG_EXCEPTION

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

Ronan's avatar
Ronan committed
733
		switch (eventLog->getType()) {
734
			case EventLog::Type::None:
Ronan's avatar
Ronan committed
735
				return false;
736

737
738
739
			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
				d->insertConferenceEvent(eventLog);
740
741
				break;

742
743
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
744
				d->insertConferenceCallEvent(eventLog);
745
				break;
746

747
748
			case EventLog::Type::ConferenceChatMessage:
				d->insertConferenceChatMessageEvent(eventLog);
749
750
				break;

751
752
753
754
			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
755
756
757
				d->insertConferenceParticipantEvent(eventLog);
				break;

758
759
			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
760
761
762
				d->insertConferenceParticipantDeviceEvent(eventLog);
				break;

763
			case EventLog::Type::ConferenceSubjectChanged:
764
				d->insertConferenceSubjectEvent(eventLog);
Ronan's avatar
Ronan committed
765
766
767
				break;
		}

768
769
770
771
772
773
774
		tr.commit();

		soFarSoGood = true;

		L_END_LOG_EXCEPTION

		return soFarSoGood;
Ronan's avatar
Ronan committed
775
776
	}

Ronan's avatar
Ronan committed
777
	bool MainDb::deleteEvent (const shared_ptr<EventLog> &eventLog) {
Ronan's avatar
Ronan committed
778
779
		L_D();

Ronan's avatar
Ronan committed
780
781
782
783
784
		if (!isConnected()) {
			lWarning() << "Unable to delete event. Not connected.";
			return false;
		}

Ronan's avatar
Ronan committed
785
		long long &storageId = eventLog->getPrivate()->storageId;
Ronan's avatar
Ronan committed
786
		if (storageId < 0)
Ronan's avatar
Ronan committed
787
788
789
790
791
			return false;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
792
793
		*session << "DELETE FROM event WHERE id = :id", soci::use(storageId);
		storageId = -1;
Ronan's avatar
Ronan committed
794
795
796

		L_END_LOG_EXCEPTION

Ronan's avatar
Ronan committed
797
		return storageId == -1;
Ronan's avatar
Ronan committed
798
799
	}

800
	void MainDb::cleanEvents (FilterMask mask) {
Ronan's avatar
Ronan committed
801
802
		L_D();

Ronan's avatar
Ronan committed
803
804
805
806
807
		if (!isConnected()) {
			lWarning() << "Unable to clean events. Not connected.";
			return;
		}

Ronan's avatar
Ronan committed
808
		string query = "DELETE FROM event" +
809
			buildSqlEventFilter({ ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter }, mask);
Ronan's avatar
Ronan committed
810
811
812
813
814
815
816

		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
817
818
	}

819
	int MainDb::getEventsCount (FilterMask mask) const {
820
		L_D();
Ronan's avatar
Ronan committed
821

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

Ronan's avatar
Ronan committed
827
		string query = "SELECT COUNT(*) FROM event" +
828
			buildSqlEventFilter({ ConferenceCallFilter, ConferenceChatMessageFilter, ConferenceInfoFilter }, mask);
Ronan's avatar
Ronan committed
829
830
831
832
833
834
835
836
837
838
839
840
		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;
	}

841
	int MainDb::getMessagesCount (const string &peerAddress) const {
842
		L_D();
843

Ronan's avatar
Ronan committed
844
845
846
847
		if (!isConnected()) {
			lWarning() << "Unable to get messages count. Not connected.";
			return 0;
		}
848

849
850
851
852
853
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

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

855
		string query = "SELECT COUNT(*) FROM conference_chat_message_event";
Ronan's avatar
Ronan committed
856
857
858
		if (peerAddress.empty())
			*session << query, soci::into(count);
		else {
859
860
861
862
			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
863
864
865
866
				")";

			*session << query, soci::use(peerAddress), soci::into(count);
		}
867
868
869
870

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
871
872
	}

873
	int MainDb::getUnreadMessagesCount (const string &peerAddress) const {
874
		L_D();
875

Ronan's avatar
Ronan committed
876
877
878
879
880
		if (!isConnected()) {
			lWarning() << "Unable to get unread messages count. Not connected.";
			return 0;
		}

881
882
		int count = 0;

883
		string query = "SELECT COUNT(*) FROM conference_chat_message_event WHERE";
884
		if (!peerAddress.empty())
885
886
887
888
889
			query += " event_id IN ("
				"  SELECT event_id FROM conference_event WHERE chat_room_id = ("
				"    SELECT id FROM sip_address WHERE value = :peerAddress"
				"  )"
				") AND";
890
891

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

894
895
896
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
897
898
899
900
901

		if (peerAddress.empty())
			*session << query, soci::into(count);
		else
			*session << query, soci::use(peerAddress), soci::into(count);
902
903
904
905

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
906
907
	}

908
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &peerAddress, int nLast, FilterMask mask) const {
909
		return getHistoryRange(peerAddress, 0, nLast - 1, mask);
Ronan's avatar
Ronan committed
910
911
	}

912
	list<shared_ptr<EventLog>> MainDb::getHistoryRange (
Ronan's avatar
Ronan committed
913
		const string &peerAddress,
914
915
916
917
		int begin,
		int end,
		FilterMask mask
	) const {
Ronan's avatar
Ronan committed
918
919
920
921
		L_D();

		list<shared_ptr<EventLog>> events;

Ronan's avatar
Ronan committed
922
923
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
Ronan's avatar
Ronan committed
924
			return events;
Ronan's avatar
Ronan committed
925
926
		}

Ronan's avatar
Ronan committed
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
		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");
Ronan's avatar
Ronan committed
944
		query += "  ORDER BY date DESC";
Ronan's avatar
Ronan committed
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960

		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
961
			tm date = row.get<tm>(2);
962
			events.push_back(d->selectGenericConferenceEvent(
963
964
965
				// See: http://soci.sourceforge.net/doc/master/backends/
				// `row id` is not supported by soci on Sqlite3. It's necessary to cast id to int...
				getBackend() == Sqlite3 ? static_cast<long long>(row.get<int>(0)) : row.get<long long>(0),
966
967
968
969
				static_cast<EventLog::Type>(row.get<int>(1)),
				mktime(&date),
				peerAddress
			));
Ronan's avatar
Ronan committed
970
971
972
973
974
		}

		L_END_LOG_EXCEPTION

		return events;
Ronan's avatar
Ronan committed
975
976
	}

977
	void MainDb::cleanHistory (const string &peerAddress, FilterMask mask) {
978