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

23
#undef SOCI_ENABLED
24

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

29
30
#include "linphone/utils/utils.h"

Ronan's avatar
Ronan committed
31
#include "chat/chat-message/chat-message-p.h"
32
#include "chat/chat-room/chat-room.h"
33
#include "chat/chat-room/client-group-chat-room.h"
34
#include "conference/participant.h"
35
#include "content/content-type.h"
Ronan's avatar
Ronan committed
36
#include "content/content.h"
Ronan's avatar
Ronan committed
37
#include "core/core-p.h"
38
#include "db/session/db-session-provider.h"
Ronan's avatar
Ronan committed
39
#include "event-log/event-log-p.h"
40
#include "event-log/events.h"
Ronan's avatar
Ronan committed
41
#include "logger/logger.h"
42
#include "main-db-event-key-p.h"
Ronan's avatar
Ronan committed
43
#include "main-db-p.h"
Ronan's avatar
Ronan committed
44
45
46

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

Ronan's avatar
Ronan committed
47
48
using namespace std;

Ronan's avatar
Ronan committed
49
50
51
52
LINPHONE_BEGIN_NAMESPACE

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

53
MainDb::MainDb (const shared_ptr<Core> &core) : AbstractDb(*new MainDbPrivate), CoreAccessor(core) {}
Ronan's avatar
Ronan committed
54

55
56
#ifdef SOCI_ENABLED

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

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

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

74
	static constexpr EnumToSql<MainDb::Filter> eventFilterToSql[] = {
75
		{ MainDb::ConferenceCallFilter, "3, 4" },
Ronan's avatar
Ronan committed
76
		{ MainDb::ConferenceChatMessageFilter, "5" },
77
		{ MainDb::ConferenceInfoFilter, "1, 2, 6, 7, 8, 9, 10, 11, 12" }
78
	};
79

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

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

Ronan's avatar
Ronan committed
88
89
90
91
92
	static string buildSqlEventFilter (
		const list<MainDb::Filter> &filters,
		MainDb::FilterMask mask,
		const string &condKeyWord = "WHERE"
	) {
93
		L_ASSERT(
94
			find_if(filters.cbegin(), filters.cend(), [](const MainDb::Filter &filter) {
95
96
				return filter == MainDb::NoFilter;
			}) == filters.cend()
97
98
		);

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

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

			if (isStart) {
				isStart = false;
Ronan's avatar
Ronan committed
110
				sql += " " + condKeyWord + " type IN (";
111
			} else
Ronan's avatar
Ronan committed
112
				sql += ", ";
113
114
115
			sql += mapEventFilterToSql(filter);
		}

Ronan's avatar
Ronan committed
116
117
118
		if (!isStart)
			sql += ") ";

119
120
121
		return sql;
	}

122
123
// -----------------------------------------------------------------------------

124
	long long MainDbPrivate::insertSipAddress (const string &sipAddress) {
125
126
127
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

128
		long long id;
129
130
		*session << "SELECT id FROM sip_address WHERE value = :sipAddress", soci::use(sipAddress), soci::into(id);
		if (session->got_data())
131
132
			return id;

Ronan's avatar
Ronan committed
133
		lInfo() << "Insert new sip address in database: `" << sipAddress << "`.";
134
135
		*session << "INSERT INTO sip_address (value) VALUES (:sipAddress)", soci::use(sipAddress);
		return q->getLastInsertId();
136
137
	}

138
	void MainDbPrivate::insertContent (long long eventId, const Content &content) {
139
		L_Q();
140
141
		soci::session *session = dbSession.getBackendSession<soci::session>();

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

147
		long long messageContentId = q->getLastInsertId();
148
		for (const auto &appData : content.getAppDataMap())
Ronan's avatar
Ronan committed
149
150
			*session << "INSERT INTO chat_message_content_app_data (chat_message_content_id, name, data) VALUES"
				"  (:messageContentId, :name, :data)",
151
				soci::use(messageContentId), soci::use(appData.first), soci::use(appData.second);
152
153
	}

154
	long long MainDbPrivate::insertContentType (const string &contentType) {
155
156
157
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

158
		long long id;
159
160
		*session << "SELECT id FROM content_type WHERE value = :contentType", soci::use(contentType), soci::into(id);
		if (session->got_data())
161
162
			return id;

Ronan's avatar
Ronan committed
163
		lInfo() << "Insert new content type in database: `" << contentType << "`.";
164
165
		*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
		return q->getLastInsertId();
166
167
	}

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

171
		long long id;
172
		*session << "SELECT peer_sip_address_id FROM chat_room WHERE peer_sip_address_id = :sipAddressId",
173
			soci::use(sipAddressId), soci::into(id);
Ronan's avatar
Ronan committed
174
175
		if (!session->got_data()) {
			lInfo() << "Insert new chat room in database: `" << sipAddressId << "` (capabilities=" << capabilities << ").";
176
177
178
			*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);
Ronan's avatar
Ronan committed
179
		}
180
		else
181
			*session << "UPDATE chat_room SET last_update_date = :lastUpdateDate WHERE peer_sip_address_id = :sipAddressId",
182
				soci::use(date), soci::use(sipAddressId);
183

184
185
186
		return sipAddressId;
	}

187
	void MainDbPrivate::insertChatRoomParticipant (long long chatRoomId, long long sipAddressId, bool isAdmin) {
188
		soci::session *session = dbSession.getBackendSession<soci::session>();
189
190
191
192
193
194
		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);
Ronan's avatar
Ronan committed
195
196
		if (statement.get_affected_rows() == 0) {
			lInfo() << "Insert new chat room participant in database: `" << sipAddressId << "` (isAdmin=" << isAdmin << ").";
197
198
199
			*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));
Ronan's avatar
Ronan committed
200
		}
201
202
	}

203
	void MainDbPrivate::insertChatMessageParticipant (long long eventId, long long sipAddressId, int state) {
204
205
		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::statement statement = (
206
			session->prepare << "UPDATE chat_message_participant SET state = :state"
Ronan's avatar
Ronan committed
207
208
				"  WHERE event_id = :eventId AND sip_address_id = :sipAddressId",
				soci::use(state), soci::use(eventId), soci::use(sipAddressId)
209
210
		);
		statement.execute(true);
211
		if (statement.get_affected_rows() == 0 && state != static_cast<int>(ChatMessage::State::Displayed))
212
			*session << "INSERT INTO chat_message_participant (event_id, sip_address_id, state)"
Ronan's avatar
Ronan committed
213
214
				"  VALUES (:eventId, :sipAddressId, :state)",
				soci::use(eventId), soci::use(sipAddressId), soci::use(state);
215
216
	}

Ronan's avatar
Ronan committed
217
218
// -----------------------------------------------------------------------------

219
	shared_ptr<EventLog> MainDbPrivate::selectGenericConferenceEvent (
220
		long long eventId,
221
222
223
224
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
225
226
227
228
229
230
		switch (type) {
			case EventLog::Type::None:
				return nullptr;

			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
231
				return selectConferenceEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
232

233
234
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
235
				return selectConferenceCallEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
236
237

			case EventLog::Type::ConferenceChatMessage:
238
				return selectConferenceChatMessageEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
239
240
241
242
243

			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
244
				return selectConferenceParticipantEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
245
246
247

			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
248
				return selectConferenceParticipantDeviceEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
249
250

			case EventLog::Type::ConferenceSubjectChanged:
251
				return selectConferenceSubjectEvent(eventId, type, date, peerAddress);
Ronan's avatar
Ronan committed
252
253
254
255
256
		}

		return nullptr;
	}

257
	shared_ptr<EventLog> MainDbPrivate::selectConferenceEvent (
258
		long long,
259
260
261
262
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
263
264
265
266
267
		return make_shared<ConferenceEvent>(
			type,
			date,
			Address(peerAddress)
		);
Ronan's avatar
Ronan committed
268
269
	}

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

280
	shared_ptr<EventLog> MainDbPrivate::selectConferenceChatMessageEvent (
281
		long long eventId,
282
283
284
285
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
Ronan's avatar
Ronan committed
286
287
288
289
290
291
292
293
294
295
296
		L_Q();

		shared_ptr<Core> core = q->getCore();

		// TODO: Avoid address creation.
		shared_ptr<ChatRoom> chatRoom = core->findChatRoom(Address(peerAddress));
		if (!chatRoom)
			return nullptr;

		// TODO: Use cache, do not fetch the same message twice.

297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
		// 1 - Fetch chat message.
		shared_ptr<ChatMessage> chatMessage = make_shared<ChatMessage>(chatRoom);
		{
			string localSipAddress;
			string remoteSipAddress;
			string imdnMessageId;
			int state;
			int direction;
			int isSecured;

			soci::session *session = dbSession.getBackendSession<soci::session>();
			*session << "SELECT local_sip_address.value, remote_sip_address.value, imdn_message_id, state, direction, is_secured"
				"  FROM event, conference_chat_message_event, sip_address AS local_sip_address,"
				"  sip_address AS remote_sip_address"
				"  WHERE event_id = :eventId"
				"  AND event_id = event.id"
				"  AND local_sip_address_id = local_sip_address.id"
				"  AND remote_sip_address_id = remote_sip_address.id", soci::into(localSipAddress), soci::into(remoteSipAddress),
				soci::into(imdnMessageId), soci::into(state), soci::into(direction), soci::into(isSecured), soci::use(eventId);

			chatMessage->getPrivate()->setState(static_cast<ChatMessage::State>(state), true);
			chatMessage->getPrivate()->setDirection(static_cast<ChatMessage::Direction>(direction));
			chatMessage->setIsSecured(static_cast<bool>(isSecured));

			if (direction == static_cast<int>(ChatMessage::Direction::Outgoing)) {
				chatMessage->setFromAddress(Address(localSipAddress));
				chatMessage->setToAddress(Address(remoteSipAddress));
			} else {
				chatMessage->setFromAddress(Address(remoteSipAddress));
				chatMessage->setToAddress(Address(localSipAddress));
			}
		}
Ronan's avatar
Ronan committed
329

330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
		// 2 - Fetch contents.
		{
			soci::session *session = dbSession.getBackendSession<soci::session>();
			const string query = "SELECT content_type.value, body FROM chat_message_content, content_type"
				"  WHERE event_id = :eventId AND content_type_id = content_type.id";
			soci::rowset<soci::row> rows = (session->prepare << query, soci::use(eventId));
			for (const auto &row : rows) {
				ContentType contentType(row.get<string>(1));
				Content *content;

				if (contentType == ContentType::FileTransfer)
					content = new FileTransferContent();
				else if (contentType.isFile()) {
					long long contentId = q->getBackend() == AbstractDb::Sqlite3
						? static_cast<long long>(row.get<int>(0))
						: row.get<long long>(0);

					string name;
					int size;
					string path;

					*session << "SELECT name, size, path FROM chat_message_file_content"
Ronan's avatar
Ronan committed
352
						"  WHERE chat_message_content_id = :contentId",
Ronan's avatar
Ronan committed
353
						soci::into(name), soci::into(size), soci::into(path), soci::use(contentId);
354
355
356
357
358
359
360
361

					FileContent *fileContent = new FileContent();
					fileContent->setFileName(name);
					fileContent->setFileSize(static_cast<size_t>(size));
					fileContent->setFilePath(path);

					content = fileContent;
				} else
Ronan's avatar
Ronan committed
362
					content = new Content();
363
364
365

				content->setContentType(contentType);
				content->setBody(row.get<string>(2));
Ronan's avatar
Ronan committed
366
				chatMessage->addContent(content);
367
			}
Ronan's avatar
Ronan committed
368
		}
369
370
371
372
373
374

		// TODO: Use cache.
		return make_shared<ConferenceChatMessageEvent>(
			date,
			chatMessage
		);
Ronan's avatar
Ronan committed
375
376
	}

377
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantEvent (
378
		long long eventId,
379
380
381
382
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
383
384
385
386
		unsigned int notifyId;
		string participantAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
387
388
		*session << "SELECT notify_id, participant_address.value"
			"  FROM conference_notified_event, conference_participant_event, sip_address as participant_address"
389
			"  WHERE conference_participant_event.event_id = :eventId"
Ronan's avatar
Ronan committed
390
391
			"    AND conference_notified_event.event_id = conference_participant_event.event_id"
			"    AND participant_address.id = participant_address_id",
392
393
394
395
396
397
398
399
400
			soci::into(notifyId), soci::into(participantAddress), soci::use(eventId);

		return make_shared<ConferenceParticipantEvent>(
			type,
			date,
			Address(peerAddress),
			notifyId,
			Address(participantAddress)
		);
Ronan's avatar
Ronan committed
401
402
	}

403
	shared_ptr<EventLog> MainDbPrivate::selectConferenceParticipantDeviceEvent (
404
		long long eventId,
405
406
407
408
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
409
410
411
412
413
		unsigned int notifyId;
		string participantAddress;
		string gruuAddress;

		soci::session *session = dbSession.getBackendSession<soci::session>();
414
415
416
		*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"
417
418
			"  WHERE conference_participant_device_event.event_id = :eventId"
			"    AND conference_participant_event.event_id = conference_participant_device_event.event_id"
419
420
421
			"    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",
422
423
424
425
426
427
428
429
430
431
			soci::into(notifyId), soci::into(participantAddress), soci::into(gruuAddress), soci::use(eventId);

		return make_shared<ConferenceParticipantDeviceEvent>(
			type,
			date,
			Address(peerAddress),
			notifyId,
			Address(participantAddress),
			Address(gruuAddress)
		);
Ronan's avatar
Ronan committed
432
433
	}

434
	shared_ptr<EventLog> MainDbPrivate::selectConferenceSubjectEvent (
435
		long long eventId,
436
437
438
439
440
441
442
		EventLog::Type type,
		time_t date,
		const string &peerAddress
	) const {
		unsigned int notifyId;
		string subject;

443
444
445
446
447
448
		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);
449
450
451
452
453
454
455

		return make_shared<ConferenceSubjectEvent>(
			date,
			Address(peerAddress),
			notifyId,
			subject
		);
Ronan's avatar
Ronan committed
456
457
	}

458
459
// -----------------------------------------------------------------------------

Ronan's avatar
Ronan committed
460
	long long MainDbPrivate::insertEvent (const shared_ptr<EventLog> &eventLog) {
461
462
463
464
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
465
		soci::use(static_cast<int>(eventLog->getType())), soci::use(Utils::getTimeTAsTm(eventLog->getTime()));
466
		return q->getLastInsertId();
467
468
	}

Ronan's avatar
Ronan committed
469
	long long MainDbPrivate::insertConferenceEvent (const shared_ptr<EventLog> &eventLog, long long *chatRoomId) {
470
471
		long long eventId = insertEvent(eventLog);
		long long curChatRoomId = insertSipAddress(
Ronan's avatar
Ronan committed
472
			static_pointer_cast<ConferenceEvent>(eventLog)->getConferenceAddress().asString()
473
474
475
476
477
478
479
480
481
482
483
484
		);

		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
485
	long long MainDbPrivate::insertConferenceCallEvent (const shared_ptr<EventLog> &eventLog) {
486
487
488
489
		// TODO.
		return 0;
	}

Ronan's avatar
Ronan committed
490
491
	long long MainDbPrivate::insertConferenceChatMessageEvent (const shared_ptr<EventLog> &eventLog) {
		shared_ptr<ChatMessage> chatMessage = static_pointer_cast<ConferenceChatMessageEvent>(eventLog)->getChatMessage();
492
493
494
495
496
497
		shared_ptr<ChatRoom> chatRoom = chatMessage->getChatRoom();
		if (!chatRoom) {
			lError() << "Unable to get a valid chat room. It was removed from database.";
			return -1;
		}

498
		tm eventTime = Utils::getTimeTAsTm(eventLog->getTime());
499

500
501
		long long localSipAddressId = insertSipAddress(chatMessage->getLocalAddress().asString());
		long long remoteSipAddressId = insertSipAddress(chatMessage->getRemoteAddress().asString());
502
		insertChatRoom(remoteSipAddressId, chatRoom->getCapabilities(), eventTime);
503
		long long eventId = insertConferenceEvent(eventLog);
504

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

507
508
509
510
511
512
513
514
515
516
		*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);

517
		for (const Content *content : chatMessage->getContents())
518
			insertContent(eventId, *content);
519

520
521
522
		return eventId;
	}

Ronan's avatar
Ronan committed
523
	long long MainDbPrivate::insertConferenceNotifiedEvent (const shared_ptr<EventLog> &eventLog) {
524
525
		long long chatRoomId;
		long long eventId = insertConferenceEvent(eventLog, &chatRoomId);
Ronan's avatar
Ronan committed
526
		unsigned int lastNotifyId = static_pointer_cast<ConferenceNotifiedEvent>(eventLog)->getNotifyId();
527
528
529

		soci::session *session = dbSession.getBackendSession<soci::session>();
		*session << "INSERT INTO conference_notified_event (event_id, notify_id)"
Ronan's avatar
Ronan committed
530
531
532
			"  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);
533

534
535
536
		return eventId;
	}

Ronan's avatar
Ronan committed
537
	long long MainDbPrivate::insertConferenceParticipantEvent (const shared_ptr<EventLog> &eventLog) {
538
539
		long long eventId = insertConferenceNotifiedEvent(eventLog);
		long long participantAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
540
			static_pointer_cast<ConferenceParticipantEvent>(eventLog)->getParticipantAddress().asString()
541
542
		);

543
		soci::session *session = dbSession.getBackendSession<soci::session>();
544
		*session << "INSERT INTO conference_participant_event (event_id, participant_address_id)"
545
			"  VALUES (:eventId, :participantAddressId)", soci::use(eventId), soci::use(participantAddressId);
546

547
548
549
		return eventId;
	}

Ronan's avatar
Ronan committed
550
	long long MainDbPrivate::insertConferenceParticipantDeviceEvent (const shared_ptr<EventLog> &eventLog) {
551
552
		long long eventId = insertConferenceParticipantEvent(eventLog);
		long long gruuAddressId = insertSipAddress(
Ronan's avatar
Ronan committed
553
			static_pointer_cast<ConferenceParticipantDeviceEvent>(eventLog)->getGruuAddress().asString()
554
555
556
		);

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

560
		return eventId;
561
562
	}

Ronan's avatar
Ronan committed
563
	long long MainDbPrivate::insertConferenceSubjectEvent (const shared_ptr<EventLog> &eventLog) {
564
		long long eventId = insertConferenceNotifiedEvent(eventLog);
565
566

		soci::session *session = dbSession.getBackendSession<soci::session>();
567
		*session << "INSERT INTO conference_subject_event (event_id, subject)"
568
			"  VALUES (:eventId, :subject)", soci::use(eventId), soci::use(
Ronan's avatar
Ronan committed
569
				static_pointer_cast<ConferenceSubjectEvent>(eventLog)->getSubject()
570
			);
571

572
		return eventId;
573
574
	}

575
576
577
578
579
580
581
582
583
584
585
586
587
// -----------------------------------------------------------------------------

	shared_ptr<EventLog> MainDbPrivate::getEventFromCache (long long eventId) const {
		auto it = storageIdToEvent.find(eventId);
		if (it == storageIdToEvent.cend())
			return nullptr;

		shared_ptr<EventLog> eventLog = it->second.lock();
		// Must exist. If not, implementation bug.
		L_ASSERT(eventLog);
		return eventLog;
	}

588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
	void MainDbPrivate::invalidEventsFromQuery (const string &query, const string &peerAddress) {
		L_Q();

		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::rowset<soci::row> rows = (session->prepare << query, soci::use(peerAddress));
		for (const auto &row : rows) {
			shared_ptr<EventLog> eventLog = getEventFromCache(
				q->getBackend() == AbstractDb::Sqlite3 ? static_cast<long long>(row.get<int>(0)) : row.get<long long>(0)
			);
			if (eventLog) {
				const EventLogPrivate *dEventLog = eventLog->getPrivate();
				L_ASSERT(dEventLog->dbKey.isValid());
				dEventLog->dbKey = MainDbEventKey();
			}
		}
	}

605
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
606

607
	void MainDb::init () {
608
		L_D();
609
610

		const string charset = getBackend() == Mysql ? "DEFAULT CHARSET=utf8" : "";
Ronan's avatar
Ronan committed
611
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
612

Ronan's avatar
Ronan committed
613
		*session <<
614
			"CREATE TABLE IF NOT EXISTS sip_address ("
Erwan Croze's avatar
Erwan Croze committed
615
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
616
			"  value VARCHAR(255) UNIQUE NOT NULL"
617
			") " + charset;
618

619
		*session <<
620
			"CREATE TABLE IF NOT EXISTS content_type ("
Erwan Croze's avatar
Erwan Croze committed
621
			"  id" + primaryKeyStr("SMALLINT UNSIGNED") + ","
Ronan's avatar
Ronan committed
622
			"  value VARCHAR(255) UNIQUE NOT NULL"
623
			") " + charset;
624

Ronan's avatar
Ronan committed
625
		*session <<
626
			"CREATE TABLE IF NOT EXISTS event ("
Erwan Croze's avatar
Erwan Croze committed
627
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
628
			"  type TINYINT UNSIGNED NOT NULL,"
629
			"  date DATE NOT NULL"
630
			") " + charset;
631

Ronan's avatar
Ronan committed
632
		*session <<
633
			"CREATE TABLE IF NOT EXISTS chat_room ("
634
			// Server (for conference) or user sip address.
635
636
637
			"  peer_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","

			"  local_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
638
639

			// Dialog creation date.
640
641
642
643
			"  creation_date DATE NOT NULL,"

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

645
646
647
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

648
649
650
			// Chatroom subject.
			"  subject VARCHAR(255),"

Ronan's avatar
Ronan committed
651
			"  last_notify_id INT UNSIGNED,"
652

653
			"  PRIMARY KEY (peer_sip_address_id, local_sip_address_id),"
654
			"  FOREIGN KEY (peer_sip_address_id)"
655
656
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
657
658
659
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
660
			") " + charset;
661

662
663
		*session <<
			"CREATE TABLE IF NOT EXISTS chat_room_participant ("
Erwan Croze's avatar
Erwan Croze committed
664
665
			"  chat_room_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
			"  sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
666

667
668
			"  is_admin BOOLEAN NOT NULL,"

669
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
670
671
672
673
674
675
			"  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"
676
			") " + charset;
677
678
679

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_event ("
Erwan Croze's avatar
Erwan Croze committed
680
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
681

Erwan Croze's avatar
Erwan Croze committed
682
			"  chat_room_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
683
684
685
686
687
688
689

			"  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"
690
			") " + charset;
691

692
693
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_notified_event ("
Erwan Croze's avatar
Erwan Croze committed
694
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
695

696
697
698
699
700
			"  notify_id INT UNSIGNED NOT NULL,"

			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_event(event_id)"
			"    ON DELETE CASCADE"
701
			") " + charset;
702

703
704
		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_event ("
Erwan Croze's avatar
Erwan Croze committed
705
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
706

Erwan Croze's avatar
Erwan Croze committed
707
			"  participant_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
708

709
710
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
711
712
713
714
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (participant_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
715
			") " + charset;
716
717
718

		*session <<
			"CREATE TABLE IF NOT EXISTS conference_participant_device_event ("
Erwan Croze's avatar
Erwan Croze committed
719
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
720

Erwan Croze's avatar
Erwan Croze committed
721
			"  gruu_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
722

723
724
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_participant_event(event_id)"
725
726
727
728
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (gruu_address_id)"
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
729
			") " + charset;
730
731
732

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

735
			"  subject VARCHAR(255) NOT NULL,"
736

737
738
			"  FOREIGN KEY (event_id)"
			"    REFERENCES conference_notified_event(event_id)"
739
			"    ON DELETE CASCADE"
740
			") " + charset;
741

Ronan's avatar
Ronan committed
742
		*session <<
743
			"CREATE TABLE IF NOT EXISTS conference_chat_message_event ("
Erwan Croze's avatar
Erwan Croze committed
744
			"  event_id" + primaryKeyStr("BIGINT UNSIGNED") + ","
745

Erwan Croze's avatar
Erwan Croze committed
746
747
			"  local_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
			"  remote_sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
748
749
750
751

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

752
753
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
754
			"  is_secured BOOLEAN NOT NULL,"
755

756
			"  FOREIGN KEY (event_id)"
757
			"    REFERENCES conference_event(event_id)"
758
			"    ON DELETE CASCADE,"
759
760
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
761
			"    ON DELETE CASCADE,"
762
			"  FOREIGN KEY (remote_sip_address_id)"
763
			"    REFERENCES sip_address(id)"
764
			"    ON DELETE CASCADE"
765
			") " + charset;
766

767
		*session <<
768
			"CREATE TABLE IF NOT EXISTS chat_message_participant ("
Erwan Croze's avatar
Erwan Croze committed
769
770
			"  event_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
			"  sip_address_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
771
772
			"  state TINYINT UNSIGNED NOT NULL,"

Ronan's avatar
Ronan committed
773
774
			"  PRIMARY KEY (event_id, sip_address_id),"
			"  FOREIGN KEY (event_id)"
775
			"    REFERENCES conference_chat_message_event(event_id)"
776
777
778
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
779
			"    ON DELETE CASCADE"
780
			") " + charset;
781

782
		*session <<
783
			"CREATE TABLE IF NOT EXISTS chat_message_content ("
Erwan Croze's avatar
Erwan Croze committed
784
			"  id" + primaryKeyStr("BIGINT UNSIGNED") + ","
785

786
			"  event_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
Erwan Croze's avatar
Erwan Croze committed
787
			"  content_type_id" + primaryKeyRefStr("SMALLINT UNSIGNED") + ","
788
			"  body TEXT NOT NULL,"
789

Ronan's avatar
Ronan committed
790
			"  FOREIGN KEY (event_id)"
791
			"    REFERENCES conference_chat_message_event(event_id)"
792
793
794
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
795
			"    ON DELETE CASCADE"
796
			") " + charset;
797

798
799
800
801
802
803
804
805
806
807
808
		*session <<
			"CREATE TABLE IF NOT EXISTS chat_message_file_content ("
			"  chat_message_content_id" + primaryKeyStr("BIGINT UNSIGNED") + ","

			"  name VARCHAR(256) NOT NULL,"
			"  size INT UNSIGNED NOT NULL,"
			"  path VARCHAR(512) NOT NULL,"

			"  FOREIGN KEY (chat_message_content_id)"
			"    REFERENCES chat_message_content(id)"
			"    ON DELETE CASCADE"
809
			") " + charset;
810

811
		*session <<
812
			"CREATE TABLE IF NOT EXISTS chat_message_content_app_data ("
Erwan Croze's avatar
Erwan Croze committed
813
			"  chat_message_content_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
814

815
			"  name VARCHAR(255),"
816
817
			"  data BLOB,"

818
			"  PRIMARY KEY (chat_message_content_id, name),"
819
820
			"  FOREIGN KEY (chat_message_content_id)"
			"    REFERENCES chat_message_content(id)"
821
			"    ON DELETE CASCADE"
822
			") " + charset;
823

824
		*session <<
825
			"CREATE TABLE IF NOT EXISTS conference_message_crypto_data ("
Erwan Croze's avatar
Erwan Croze committed
826
			"  event_id" + primaryKeyRefStr("BIGINT UNSIGNED") + ","
827

828
			"  name VARCHAR(255),"
829
830
			"  data BLOB,"

831
			"  PRIMARY KEY (event_id, name),"
Ronan's avatar
Ronan committed
832
			"  FOREIGN KEY (event_id)"
833
			"    REFERENCES conference_chat_message_event(event_id)"
834
			"    ON DELETE CASCADE"
835
			") " + charset;
836
837
838
839

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
840
841
			"CREATE TRIGGER IF NOT EXISTS chat_message_participant_deleter"
			"  AFTER UPDATE OF state ON chat_message_participant FOR EACH ROW"
842
843
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
844
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
845
846
			"    SELECT state FROM chat_message_participant WHERE"
			"    NEW.event_id = chat_message_participant.event_id"
847
848
849
850
851
			"    AND state <> ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += "    LIMIT 1"
			"  )) = 0"
			"  BEGIN"
852
853
			"  DELETE FROM chat_message_participant WHERE NEW.event_id = chat_message_participant.event_id;"
			"  UPDATE conference_chat_message_event SET state = ";
854
		participantMessageDeleter += displayedId;
Ronan's avatar
Ronan committed
855
		participantMessageDeleter += " WHERE event_id = NEW.event_id;"
856
857
858
			"  END";

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
859
	}
860

Ronan's avatar
Ronan committed
861
	bool MainDb::addEvent (const shared_ptr<EventLog> &eventLog) {
862
863
		L_D();

Ronan's avatar
Ronan committed
864
865
		if (!isConnected()) {
			lWarning() << "Unable to add event. Not connected.";
866
			return false;
Ronan's avatar
Ronan committed
867
868
		}

869
870
871
872
873
874
		const EventLogPrivate *dEventLog = eventLog->getPrivate();
		if (dEventLog->dbKey.isValid()) {
			lWarning() << "Unable to add an event twice!!!";
			return false;
		}

875
		bool soFarSoGood = false;
876
		long long storageId;
877
878
879
880
881

		L_BEGIN_LOG_EXCEPTION

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

Ronan's avatar
Ronan committed
882
		switch (eventLog->getType()) {
883
			case EventLog::Type::None:
Ronan's avatar
Ronan committed
884
				return false;
885

886
887
			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
888
				storageId = d->insertConferenceEvent(eventLog);
889
890
				break;

891
892
			case EventLog::Type::ConferenceCallStart:
			case EventLog::Type::ConferenceCallEnd:
893
				storageId = d->insertConferenceCallEvent(eventLog);
894
				break;
895

896
			case EventLog::Type::ConferenceChatMessage:
897
				storageId = d->insertConferenceChatMessageEvent(eventLog);
898
899
				break;

900
901
902
903
			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
904
				storageId = d->insertConferenceParticipantEvent(eventLog);
905
906
				break;

907
908
			case EventLog::Type::ConferenceParticipantDeviceAdded:
			case EventLog::Type::ConferenceParticipantDeviceRemoved:
909
				storageId = d->insertConferenceParticipantDeviceEvent(eventLog);
910
911
				break;

912
			case EventLog::Type::ConferenceSubjectChanged: