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

Ronan's avatar
Ronan committed
29
#include "abstract/abstract-db-p.h"
Ronan's avatar
Ronan committed
30
#include "chat/chat-message/chat-message.h"
31
#include "chat/chat-room/chat-room.h"
32
#include "conference/participant.h"
33
#include "content/content-type.h"
Ronan's avatar
Ronan committed
34
#include "content/content.h"
35
#include "db/session/db-session-provider.h"
Ronan's avatar
Ronan committed
36
#include "event-log/call-event.h"
Ronan's avatar
Ronan committed
37
#include "event-log/chat-message-event.h"
Ronan's avatar
Ronan committed
38
#include "event-log/event-log-p.h"
Ronan's avatar
Ronan committed
39
#include "logger/logger.h"
Ronan's avatar
Ronan committed
40

41
#include "main-db.h"
Ronan's avatar
Ronan committed
42
43
44

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

Ronan's avatar
Ronan committed
45
46
using namespace std;

Ronan's avatar
Ronan committed
47
48
LINPHONE_BEGIN_NAMESPACE

49
50
51
52
53
54
55
56
57
struct MessageEventReferences {
#ifdef SOCI_ENABLED
	long eventId;
	long localSipAddressId;
	long remoteSipAddressId;
	long chatRoomId;
#endif
};

58
class MainDbPrivate : public AbstractDbPrivate {
59
60
61
#ifdef SOCI_ENABLED
public:
	long insertSipAddress (const string &sipAddress);
62
	void insertContent (long messageEventId, const Content &content);
63
64
	long insertContentType (const string &contentType);
	long insertEvent (EventLog::Type type, const tm &date);
65
	long insertChatRoom (long sipAddressId, int capabilities, const tm &date);
66
	void insertChatRoomParticipant (long chatRoomId, long sipAddressId, bool isAdmin);
67

68
69
70
71
72
73
	long insertMessageEvent (
		const MessageEventReferences &references,
		ChatMessage::State state,
		ChatMessage::Direction direction,
		const string &imdnMessageId,
		bool isSecured,
74
		const list<Content> &contents
75
76
	);

77
78
	void insertMessageParticipant (long messageEventId, long sipAddressId, ChatMessage::State state);

79
80
81
82
	void importLegacyMessages (const soci::rowset<soci::row> &messages);
#endif

private:
83
84
85
	unordered_map<string, weak_ptr<ChatRoom>> chatRooms;

	L_DECLARE_PUBLIC(MainDb);
86
};
Ronan's avatar
Ronan committed
87
88
89

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

90
MainDb::MainDb () : AbstractDb(*new MainDbPrivate) {}
Ronan's avatar
Ronan committed
91

92
93
#ifdef SOCI_ENABLED

Ronan's avatar
Ronan committed
94
// -----------------------------------------------------------------------------
95
// Soci backend.
Ronan's avatar
Ronan committed
96
97
// -----------------------------------------------------------------------------

98
99
100
101
102
	template<typename T>
	struct EnumToSql {
		T first;
		const char *second;
	};
103

104
105
106
107
108
109
110
	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)
		);
	}

111
112
113
114
	static constexpr EnumToSql<MainDb::Filter> eventFilterToSql[] = {
		{ MainDb::MessageFilter, "1" },
		{ MainDb::CallFilter, "2" },
		{ MainDb::ConferenceFilter, "3" }
115
	};
116

117
	static constexpr const char *mapEventFilterToSql (MainDb::Filter filter) {
118
119
120
		return mapEnumToSql(
			eventFilterToSql, sizeof eventFilterToSql / sizeof eventFilterToSql[0], filter
		);
121
122
	}

Ronan's avatar
Ronan committed
123
124
// -----------------------------------------------------------------------------

125
	static string buildSqlEventFilter (const list<MainDb::Filter> &filters, MainDb::FilterMask mask) {
126
		L_ASSERT(
127
128
			find_if(filters.cbegin(), filters.cend(), [](const MainDb::Filter &filter) {
					return filter == MainDb::NoFilter;
129
130
131
				}) == filters.cend()
		);

132
		if (mask == MainDb::NoFilter)
133
134
135
136
137
138
139
140
141
142
143
144
145
			return "";

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

			if (isStart) {
				isStart = false;
				sql += " WHERE ";
			} else
				sql += " OR ";
146
			sql += " type = ";
147
148
149
150
151
152
			sql += mapEventFilterToSql(filter);
		}

		return sql;
	}

153
154
// -----------------------------------------------------------------------------

155
	long MainDbPrivate::insertSipAddress (const string &sipAddress) {
156
157
158
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

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

164
165
		*session << "INSERT INTO sip_address (value) VALUES (:sipAddress)", soci::use(sipAddress);
		return q->getLastInsertId();
166
167
	}

168
	void MainDbPrivate::insertContent (long messageEventId, const Content &content) {
169
170
		L_Q();

171
172
		soci::session *session = dbSession.getBackendSession<soci::session>();

173
174
175
176
		long contentTypeId = insertContentType(content.getContentType().asString());
		*session << "INSERT INTO message_content (message_event_id, content_type_id, body) VALUES"
			"  (:messageEventId, :contentTypeId, :body)", soci::use(messageEventId), soci::use(contentTypeId),
			soci::use(content.getBodyAsString());
177
178
179
180
181
182

		long messageContentId = q->getLastInsertId();
		for (const auto &appData : content.getAppDataMap())
			*session << "INSERT INTO message_content_app_data (message_content_id, key, data) VALUES"
				"  (:messageContentId, :key, :data)",
				soci::use(messageContentId), soci::use(appData.first), soci::use(appData.second);
183
184
	}

185
	long MainDbPrivate::insertContentType (const string &contentType) {
186
187
188
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

189
		long id;
190
191
		*session << "SELECT id FROM content_type WHERE value = :contentType", soci::use(contentType), soci::into(id);
		if (session->got_data())
192
193
			return id;

194
195
		*session << "INSERT INTO content_type (value) VALUES (:contentType)", soci::use(contentType);
		return q->getLastInsertId();
196
197
	}

198
	long MainDbPrivate::insertEvent (EventLog::Type type, const tm &date) {
199
200
201
202
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();

		*session << "INSERT INTO event (type, date) VALUES (:type, :date)",
203
			soci::use(static_cast<int>(type)), soci::use(date);
204
		return q->getLastInsertId();
205
206
	}

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

210
		long id;
211
		*session << "SELECT peer_sip_address_id FROM chat_room WHERE peer_sip_address_id = :sipAddressId",
212
			soci::use(sipAddressId), soci::into(id);
213
		if (!session->got_data())
214
215
216
			*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);
217
		else
218
			*session << "UPDATE chat_room SET last_update_date = :lastUpdateDate WHERE peer_sip_address_id = :sipAddressId",
219
				soci::use(date), soci::use(sipAddressId);
220

221
222
223
		return sipAddressId;
	}

224
	void MainDbPrivate::insertChatRoomParticipant (long chatRoomId, long sipAddressId, bool isAdmin) {
225
		soci::session *session = dbSession.getBackendSession<soci::session>();
226
227
228
229
230
231
232
233
234
235
		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));
236
237
	}

238
	long MainDbPrivate::insertMessageEvent (
239
240
241
242
243
		const MessageEventReferences &references,
		ChatMessage::State state,
		ChatMessage::Direction direction,
		const string &imdnMessageId,
		bool isSecured,
244
		const list<Content> &contents
245
	) {
246
247
		L_Q();
		soci::session *session = dbSession.getBackendSession<soci::session>();
248

249
250
		*session << "INSERT INTO message_event ("
			"  event_id, chat_room_id, local_sip_address_id, remote_sip_address_id,"
251
			"  state, direction, imdn_message_id, is_secured"
252
			") VALUES ("
253
			"  :eventId, :chatRoomId, :localSipaddressId, :remoteSipaddressId,"
254
			"  :state, :direction, :imdnMessageId, :isSecured"
255
			")", soci::use(references.eventId), soci::use(references.chatRoomId), soci::use(references.localSipAddressId),
256
			soci::use(references.remoteSipAddressId), soci::use(static_cast<int>(state)),
257
258
259
260
261
			soci::use(static_cast<int>(direction)), soci::use(imdnMessageId), soci::use(isSecured ? 1 : 0);

		long messageEventId = q->getLastInsertId();

		for (const auto &content : contents)
262
			insertContent(messageEventId, content);
263
264

		return messageEventId;
265
266
	}

267
	void MainDbPrivate::insertMessageParticipant (long messageEventId, long sipAddressId, ChatMessage::State state) {
268
269
270
271
272
273
274
		soci::session *session = dbSession.getBackendSession<soci::session>();
		soci::statement statement = (
			session->prepare << "UPDATE message_participant SET state = :state"
				"  WHERE message_event_id = :messageEventId AND sip_address_id = :sipAddressId",
				soci::use(static_cast<int>(state)), soci::use(messageEventId), soci::use(sipAddressId)
		);
		statement.execute(true);
275
		if (statement.get_affected_rows() == 0 && state != ChatMessage::State::Displayed)
276
277
278
279
280
			*session << "INSERT INTO message_participant (message_event_id, sip_address_id, state)"
				"  VALUES (:messageEventId, :sipAddressId, :state)",
				soci::use(messageEventId), soci::use(sipAddressId), soci::use(static_cast<int>(state));
	}

281
282
// -----------------------------------------------------------------------------

283
284
285
286
287
288
289
290
291
292
293
294
295
	#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

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

		try {
301
			return message.get<T>(static_cast<size_t>(index));
302
303
304
305
306
307
308
		} catch (const exception &) {
			isNull = true;
		}

		return T();
	}

309
	void MainDbPrivate::importLegacyMessages (const soci::rowset<soci::row> &messages) {
310
311
312
313
314
		soci::session *session = dbSession.getBackendSession<soci::session>();

		soci::transaction tr(*session);

		for (const auto &message : messages) {
315
316
			const int direction = message.get<int>(LEGACY_MESSAGE_COL_DIRECTION);
			if (direction != 0 && direction != 1) {
317
				lWarning() << "Unable to import legacy message with invalid direction.";
318
				continue;
319
320
			}

321
322
323
			const int state = message.get<int>(
				LEGACY_MESSAGE_COL_STATE, static_cast<int>(ChatMessage::State::Displayed)
			);
324
325
326
327
			if (state < 0 || state > static_cast<int>(ChatMessage::State::Displayed)) {
				lWarning() << "Unable to import legacy message with invalid state.";
				continue;
			}
328

329
			const tm date = Utils::getLongAsTm(message.get<int>(LEGACY_MESSAGE_COL_DATE, 0));
330

331
			bool isNull;
332
			const string url = getValueFromLegacyMessage<string>(message, LEGACY_MESSAGE_COL_URL, isNull);
333

334
335
			const int contentId = message.get<int>(LEGACY_MESSAGE_COL_CONTENT_ID, -1);
			ContentType contentType(message.get<string>(LEGACY_MESSAGE_COL_CONTENT_TYPE, ""));
336
337
338
339
340
341
342
343
			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;
			}
344

345
			const string text = getValueFromLegacyMessage<string>(message, LEGACY_MESSAGE_COL_TEXT, isNull);
346
347
348
349
350
351
352
353
354
355

			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 {
356
357
358
359
360
361
362
363
364
365
366
367
				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);
368
			}
369
370
371

			struct MessageEventReferences references;
			references.eventId = insertEvent(EventLog::Type::ChatMessage, date);
372
373
			references.localSipAddressId = insertSipAddress(message.get<string>(LEGACY_MESSAGE_COL_LOCAL_ADDRESS));
			references.remoteSipAddressId = insertSipAddress(message.get<string>(LEGACY_MESSAGE_COL_REMOTE_ADDRESS));
374
375
376
377
378
			references.chatRoomId = insertChatRoom(
				references.remoteSipAddressId,
				static_cast<int>(ChatRoom::Capabilities::Basic),
				date
			);
379

380
381
			insertChatRoomParticipant(references.chatRoomId, references.remoteSipAddressId, false);

382
			long messageEventId = insertMessageEvent (
383
384
385
				references,
				static_cast<ChatMessage::State>(state),
				static_cast<ChatMessage::Direction>(direction),
386
387
				message.get<string>(LEGACY_MESSAGE_COL_IMDN_MESSAGE_ID, ""),
				!!message.get<int>(LEGACY_MESSAGE_COL_IS_SECURED, 0),
388
				{ move(content) }
389
			);
390
391
392

			if (state != static_cast<int>(ChatMessage::State::Displayed))
				insertMessageParticipant(messageEventId, references.remoteSipAddressId, static_cast<ChatMessage::State>(state));
393
394
395
		}

		tr.commit();
396
397
	}

398
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
399

400
	void MainDb::init () {
401
		L_D();
Ronan's avatar
Ronan committed
402
		soci::session *session = d->dbSession.getBackendSession<soci::session>();
403

Ronan's avatar
Ronan committed
404
		*session <<
405
406
			"CREATE TABLE IF NOT EXISTS sip_address ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
407
			"  value VARCHAR(255) UNIQUE NOT NULL"
408
409
			")";

410
		*session <<
411
412
			"CREATE TABLE IF NOT EXISTS content_type ("
			"  id" + primaryKeyAutoIncrementStr() + ","
Ronan's avatar
Ronan committed
413
			"  value VARCHAR(255) UNIQUE NOT NULL"
414
415
			")";

Ronan's avatar
Ronan committed
416
		*session <<
417
418
			"CREATE TABLE IF NOT EXISTS event ("
			"  id" + primaryKeyAutoIncrementStr() + ","
419
			"  type TINYINT UNSIGNED NOT NULL,"
420
			"  date DATE NOT NULL"
421
422
			")";

Ronan's avatar
Ronan committed
423
		*session <<
424
			"CREATE TABLE IF NOT EXISTS chat_room ("
425
			// Server (for conference) or user sip address.
426
			"  peer_sip_address_id INT UNSIGNED PRIMARY KEY,"
427
428

			// Dialog creation date.
429
430
431
432
			"  creation_date DATE NOT NULL,"

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

434
435
436
			// ConferenceChatRoom, BasicChatRoom, RTT...
			"capabilities TINYINT UNSIGNED,"

437
438
439
440
			// Chatroom subject.
			"  subject VARCHAR(255),"

			"  FOREIGN KEY (peer_sip_address_id)"
441
442
443
444
			"    REFERENCES sip_address(id)"
			"    ON DELETE CASCADE"
			")";

445
446
447
448
449
450
		*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,"

451
			"  PRIMARY KEY (chat_room_id, sip_address_id),"
452
453
454
455
456
457
458
459
			"  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"
			")";

Ronan's avatar
Ronan committed
460
		*session <<
461
			"CREATE TABLE IF NOT EXISTS message_event ("
462
			"  event_id INT UNSIGNED PRIMARY KEY,"
463
464
465
			"  chat_room_id INT UNSIGNED NOT NULL,"
			"  local_sip_address_id INT UNSIGNED NOT NULL,"
			"  remote_sip_address_id INT UNSIGNED NOT NULL,"
466
467
468
469

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

470
471
			"  state TINYINT UNSIGNED NOT NULL,"
			"  direction TINYINT UNSIGNED NOT NULL,"
472
			"  is_secured BOOLEAN NOT NULL,"
473

474
475
476
			"  FOREIGN KEY (event_id)"
			"    REFERENCES event(id)"
			"    ON DELETE CASCADE,"
477
478
			"  FOREIGN KEY (chat_room_id)"
			"    REFERENCES chat_room(peer_sip_address_id)"
479
			"    ON DELETE CASCADE,"
480
481
			"  FOREIGN KEY (local_sip_address_id)"
			"    REFERENCES sip_address(id)"
482
			"    ON DELETE CASCADE,"
483
			"  FOREIGN KEY (remote_sip_address_id)"
484
			"    REFERENCES sip_address(id)"
485
486
487
			"    ON DELETE CASCADE"
			")";

488
489
490
491
492
493
494
495
		*session <<
			"CREATE TABLE IF NOT EXISTS message_participant ("
			"  message_event_id INT UNSIGNED NOT NULL,"
			"  sip_address_id INT UNSIGNED NOT NULL,"
			"  state TINYINT UNSIGNED NOT NULL,"

			"  PRIMARY KEY (message_event_id, sip_address_id),"
			"  FOREIGN KEY (message_event_id)"
496
			"    REFERENCES message_event(event_id)"
497
498
499
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (sip_address_id)"
			"    REFERENCES sip_address(id)"
500
			"    ON DELETE CASCADE"
501
502
			")";

503
		*session <<
504
			"CREATE TABLE IF NOT EXISTS message_content ("
505
506
			"  id" + primaryKeyAutoIncrementStr() + ","
			"  message_event_id INT UNSIGNED NOT NULL,"
507
			"  content_type_id INT UNSIGNED NOT NULL,"
508
			"  body TEXT NOT NULL,"
509

510
			"  FOREIGN KEY (message_event_id)"
511
			"    REFERENCES message_event(event_id)"
512
513
514
			"    ON DELETE CASCADE,"
			"  FOREIGN KEY (content_type_id)"
			"    REFERENCES content_type(id)"
515
516
			"    ON DELETE CASCADE"
			")";
517

518
519
520
521
522
523
524
525
526
527
528
529
		*session <<
			"CREATE TABLE IF NOT EXISTS message_content_app_data ("
			"  message_content_id INT UNSIGNED NOT NULL,"
			"  key VARCHAR(255),"
			"  data BLOB,"

			"  PRIMARY KEY (message_content_id, key),"
			"  FOREIGN KEY (message_content_id)"
			"    REFERENCES message_content(id)"
			"    ON DELETE CASCADE"
			")";

530
531
532
		*session <<
			"CREATE TABLE IF NOT EXISTS message_crypto_data ("
			"  message_event_id INT UNSIGNED NOT NULL,"
533
			"  key VARCHAR(255),"
534
535
			"  data BLOB,"

536
			"  PRIMARY KEY (message_event_id, key),"
537
			"  FOREIGN KEY (message_event_id)"
538
			"    REFERENCES message_event(event_id)"
539
			"    ON DELETE CASCADE"
540
			")";
541
542
543
544

		// Trigger to delete participant_message cache entries.
		string displayedId = Utils::toString(static_cast<int>(ChatMessage::State::Displayed));
		string participantMessageDeleter =
545
			"CREATE TRIGGER IF NOT EXISTS message_participant_deleter"
546
547
548
			"  AFTER UPDATE OF state ON message_participant FOR EACH ROW"
			"  WHEN NEW.state = ";
		participantMessageDeleter += displayedId;
549
		participantMessageDeleter += " AND (SELECT COUNT(*) FROM ("
550
551
552
553
554
555
556
557
			"    SELECT state FROM message_participant WHERE"
			"    NEW.message_event_id = message_participant.message_event_id"
			"    AND state <> ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += "    LIMIT 1"
			"  )) = 0"
			"  BEGIN"
			"  DELETE FROM message_participant WHERE NEW.message_event_id = message_participant.message_event_id;"
558
559
560
			"  UPDATE message_event SET state = ";
		participantMessageDeleter += displayedId;
		participantMessageDeleter += " WHERE event_id = NEW.message_event_id;"
561
562
563
			"  END";

		*session << participantMessageDeleter;
Ronan's avatar
Ronan committed
564
	}
565

566
	bool MainDb::addEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
567
568
		if (!isConnected()) {
			lWarning() << "Unable to add event. Not connected.";
569
			return false;
Ronan's avatar
Ronan committed
570
571
		}

Ronan's avatar
Ronan committed
572
		// TODO.
Ronan's avatar
Ronan committed
573
		switch (eventLog.getType()) {
574
			case EventLog::Type::None:
Ronan's avatar
Ronan committed
575
				return false;
Ronan's avatar
Ronan committed
576
			case EventLog::Type::ChatMessage:
577
578
579
580
581
582
583
584
			case EventLog::Type::CallStart:
			case EventLog::Type::CallEnd:
			case EventLog::Type::ConferenceCreated:
			case EventLog::Type::ConferenceDestroyed:
			case EventLog::Type::ConferenceParticipantAdded:
			case EventLog::Type::ConferenceParticipantRemoved:
			case EventLog::Type::ConferenceParticipantSetAdmin:
			case EventLog::Type::ConferenceParticipantUnsetAdmin:
Ronan's avatar
Ronan committed
585
586
587
588
589
590
				break;
		}

		return true;
	}

591
	bool MainDb::deleteEvent (const EventLog &eventLog) {
Ronan's avatar
Ronan committed
592
593
		L_D();

Ronan's avatar
Ronan committed
594
595
596
597
598
		if (!isConnected()) {
			lWarning() << "Unable to delete event. Not connected.";
			return false;
		}

Ronan's avatar
Ronan committed
599
		long &id = const_cast<EventLog &>(eventLog).getPrivate()->id;
Ronan's avatar
Ronan committed
600
601
602
603
604
605
606
607
608
609
610
611
		if (id < 0)
			return false;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
		*session << "DELETE FROM event WHERE id = :id", soci::use(id);
		id = -1;

		L_END_LOG_EXCEPTION

		return id == -1;
Ronan's avatar
Ronan committed
612
613
	}

614
	void MainDb::cleanEvents (FilterMask mask) {
Ronan's avatar
Ronan committed
615
616
		L_D();

Ronan's avatar
Ronan committed
617
618
619
620
621
		if (!isConnected()) {
			lWarning() << "Unable to clean events. Not connected.";
			return;
		}

Ronan's avatar
Ronan committed
622
623
624
625
626
627
628
629
630
		string query = "DELETE FROM event" +
			buildSqlEventFilter({ MessageFilter, CallFilter, ConferenceFilter }, mask);

		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
631
632
	}

633
	int MainDb::getEventsCount (FilterMask mask) const {
634
		L_D();
Ronan's avatar
Ronan committed
635

Ronan's avatar
Ronan committed
636
637
638
639
640
		if (!isConnected()) {
			lWarning() << "Unable to get events count. Not connected.";
			return 0;
		}

Ronan's avatar
Ronan committed
641
642
643
644
645
646
647
648
649
650
651
652
653
654
		string query = "SELECT COUNT(*) FROM event" +
			buildSqlEventFilter({ MessageFilter, CallFilter, ConferenceFilter }, mask);
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION

		return count;
	}

655
	int MainDb::getMessagesCount (const string &peerAddress) const {
656
		L_D();
657

Ronan's avatar
Ronan committed
658
659
660
661
		if (!isConnected()) {
			lWarning() << "Unable to get messages count. Not connected.";
			return 0;
		}
662

663
664
665
666
667
		int count = 0;

		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
Ronan's avatar
Ronan committed
668
669
670
671
672
673

		string query = "SELECT COUNT(*) FROM message_event";
		if (peerAddress.empty())
			*session << query, soci::into(count);
		else {
			query += "  WHERE chat_room_id = ("
674
				"  SELECT id FROM sip_address WHERE value = :peerAddress"
Ronan's avatar
Ronan committed
675
676
677
678
				")";

			*session << query, soci::use(peerAddress), soci::into(count);
		}
679
680
681
682

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
683
684
	}

685
	int MainDb::getUnreadMessagesCount (const string &peerAddress) const {
686
		L_D();
687

Ronan's avatar
Ronan committed
688
689
690
691
692
		if (!isConnected()) {
			lWarning() << "Unable to get unread messages count. Not connected.";
			return 0;
		}

693
694
		int count = 0;

695
696
697
698
699
700
701
		string query = "SELECT COUNT(*) FROM message_event WHERE";
		if (!peerAddress.empty())
			query += " chat_room_id = ("
				"  SELECT id FROM sip_address WHERE value = :peerAddress"
				") AND ";

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

704
705
706
		L_BEGIN_LOG_EXCEPTION

		soci::session *session = d->dbSession.getBackendSession<soci::session>();
707
708
709
710
711

		if (peerAddress.empty())
			*session << query, soci::into(count);
		else
			*session << query, soci::use(peerAddress), soci::into(count);
712
713
714
715

		L_END_LOG_EXCEPTION

		return count;
Ronan's avatar
Ronan committed
716
717
	}

718
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &peerAddress, int nLast, FilterMask mask) const {
Ronan's avatar
Ronan committed
719
720
721
722
723
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
724
		// TODO.
Ronan's avatar
Ronan committed
725
		(void)peerAddress;
Ronan's avatar
Ronan committed
726
727
		(void)nLast;
		(void)mask;
Ronan's avatar
Ronan committed
728
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
729
730
	}

731
	list<shared_ptr<EventLog>> MainDb::getHistory (
Ronan's avatar
Ronan committed
732
		const string &peerAddress,
733
734
735
736
		int begin,
		int end,
		FilterMask mask
	) const {
Ronan's avatar
Ronan committed
737
738
739
740
741
		if (!isConnected()) {
			lWarning() << "Unable to get history. Not connected.";
			return list<shared_ptr<EventLog>>();
		}

Ronan's avatar
Ronan committed
742
		// TODO.
Ronan's avatar
Ronan committed
743
		(void)peerAddress;
Ronan's avatar
Ronan committed
744
745
746
		(void)begin;
		(void)end;
		(void)mask;
Ronan's avatar
Ronan committed
747
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
748
749
	}

750
	void MainDb::cleanHistory (const string &peerAddress, FilterMask mask) {
751
752
		L_D();

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

758
		string query;
759
		if (mask == MainDb::NoFilter || mask & MessageFilter)
760
761
762
763
764
765
766
767
768
			query += "SELECT event_id FROM message_event WHERE chat_room_id = ("
				"  SELECT peer_sip_address_id FROM chat_room WHERE peer_sip_address_id = ("
				"    SELECT id FROM sip_address WHERE value = :peerAddress"
				"  )"
				")";

		if (query.empty())
			return;

769
770
771
		L_BEGIN_LOG_EXCEPTION

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

		L_END_LOG_EXCEPTION
Ronan's avatar
Ronan committed
775
	}
Ronan's avatar
Ronan committed
776

777
778
// -----------------------------------------------------------------------------

779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
shared_ptr<ChatRoom> MainDb::findChatRoom (const string &peerAddress) const {
	L_D();

	const auto it = d->chatRooms.find(peerAddress);
	if (it != d->chatRooms.cend()) {
		try {
			return it->second.lock();
		} catch (const exception &) {
			lError() << "Cannot lock chat room: `" + peerAddress + "`";
		}
	} else {
		L_BEGIN_LOG_EXCEPTION

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

		tm creationDate;
		tm lastUpdateDate;
		int capabilities;
		string subject;

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

		L_END_LOG_EXCEPTION
	}

	return shared_ptr<ChatRoom>();
}

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

	bool MainDb::import (Backend, const string &parameters) {
815
816
		L_D();

817
818
819
820
821
		if (!isConnected()) {
			lWarning() << "Unable to import data. Not connected.";
			return 0;
		}

822
823
824
825
826
827
828
829
830
831
832
833
834
		// 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 {
835
			soci::rowset<soci::row> messages = (inSession->prepare << "SELECT * FROM history");
836
			try {
837
				d->importLegacyMessages(messages);
838
839
840
841
842
843
844
845
846
847
848
849
850
			} catch (const exception &e) {
				lInfo() << "Failed to import legacy messages from: `" << uri << "`. (" << e.what() << ")";
				return false;
			}
			lInfo() << "Successful import of legacy messages from: `" << uri << "`.";
		} catch (const exception &) {
			// Table doesn't exist.
			return false;
		}

		return true;
	}

Ronan's avatar
Ronan committed
851
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
852
853
// No backend.
// -----------------------------------------------------------------------------
Ronan's avatar
Ronan committed
854

Ronan's avatar
Ronan committed
855
856
#else

857
	void MainDb::init () {}
Ronan's avatar
Ronan committed
858

859
	bool MainDb::addEvent (const EventLog &) {
Ronan's avatar
Ronan committed
860
		return false;
Ronan's avatar
Ronan committed
861
862
	}

863
	bool MainDb::deleteEvent (const EventLog &) {
Ronan's avatar
Ronan committed
864
865
		return false;
	}
Ronan's avatar
Ronan committed
866

867
	void MainDb::cleanEvents (FilterMask) {}
Ronan's avatar
Ronan committed
868

869
	int MainDb::getEventsCount (FilterMask) const {
Ronan's avatar
Ronan committed
870
871
		return 0;
	}
Ronan's avatar
Ronan committed
872

873
	int MainDb::getMessagesCount (const string &) const {
Ronan's avatar
Ronan committed
874
875
		return 0;
	}
Ronan's avatar
Ronan committed
876

877
	int MainDb::getUnreadMessagesCount (const string &) const {
Ronan's avatar
Ronan committed
878
879
		return 0;
	}
Ronan's avatar
Ronan committed
880

881
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &, int, FilterMask) const {
Ronan's avatar
Ronan committed
882
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
883
	}
Ronan's avatar
Ronan committed
884

885
	list<shared_ptr<EventLog>> MainDb::getHistory (const string &, int, int, FilterMask) const {
Ronan's avatar
Ronan committed
886
		return list<shared_ptr<EventLog>>();
Ronan's avatar
Ronan committed
887
	}
Ronan's avatar
Ronan committed
888

889
890
	void MainDb::cleanHistory (const string &, FilterMask) {}

891
892
893
	shared_ptr<ChatRoom> MainDb::findChatRoom (const string &) const {
		return nullptr;
	}
Ronan's avatar
Ronan committed
894

895
	bool MainDb::import (Backend, const string &) {
Ghislain MARY's avatar
Ghislain MARY committed
896
897
898
		return false;
	}

Ronan's avatar
Ronan committed
899
#endif // ifdef SOCI_ENABLED
Ronan's avatar
Ronan committed
900
901

LINPHONE_END_NAMESPACE