Select Git revision
op_cache.cpp
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
database.cpp 22.96 KiB
/****************************************************************************
* Copyright (C) 2017 Savoir-faire Linux *
* Author: Nicolas Jäger <nicolas.jager@savoirfairelinux.com> *
* Author: Sébastien Blin <sebastien.blin@savoirfairelinux.com> *
* Author: Guillaume Roguez <guillaume.roguez@savoirfairelinux.com> *
* *
* This library is free software; you can redistribute it and/or *
* modify it under the terms of the GNU Lesser General Public *
* License as published by the Free Software Foundation; either *
* version 2.1 of the License, or (at your option) any later version. *
* *
* This library 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 *
* Lesser General Public License for more details. *
* *
* You should have received a copy of the GNU General Public License *
* along with this program. If not, see <http://www.gnu.org/licenses/>. *
***************************************************************************/
#include "database.h"
// Qt
#include <QObject>
#include <QtCore/QDir>
#include <QtCore/QDebug>
#include <QtCore/QFile>
#include <QtCore/QJsonArray>
#include <QtCore/QJsonDocument>
#include <QtCore/QJsonObject>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>
#include <QtCore/QStandardPaths>
#include <QtCore/QVariant>
#include <QDir>
// Std
#include <sstream>
// Data
#include "api/interaction.h"
// Lrc for migrations
#include "person.h"
#include "account.h"
#include "accountmodel.h"
#include "private/vcardutils.h"
namespace lrc
{
using namespace api;
static constexpr auto VERSION = "1";
static constexpr auto NAME = "ring.db";
Database::Database()
: QObject()
{
if (not QSqlDatabase::drivers().contains("QSQLITE")) {
throw std::runtime_error("QSQLITE not supported");
}
{
// create data directory if not created yet
QDir dataDir;
dataDir.mkpath(QStandardPaths::writableLocation(QStandardPaths::DataLocation));
}
// initalize the database. db_ = QSqlDatabase::addDatabase("QSQLITE");
#ifdef ENABLE_TEST
db_.setDatabaseName(QString("/tmp/") + QString(NAME));
#else
db_.setDatabaseName(QStandardPaths::writableLocation(QStandardPaths::DataLocation) + "/" + NAME);
#endif
// open the database.
if (not db_.open()) {
throw std::runtime_error("cannot open database");
}
// if db is empty we create them.
if (db_.tables().empty()) {
try {
QSqlDatabase::database().transaction();
createTables();
QSqlDatabase::database().commit();
} catch (QueryError& e) {
QSqlDatabase::database().rollback();
throw std::runtime_error("Could not correctly create the database");
}
// NOTE: the migration can take some time.
migrateOldFiles();
}
}
Database::~Database()
{
}
void
Database::createTables()
{
QSqlQuery query;
auto tableProfiles = "CREATE TABLE profiles (id INTEGER PRIMARY KEY,\
uri TEXT NOT NULL, \
alias TEXT, \
photo TEXT, \
type TEXT, \
status TEXT)";
auto tableConversations = "CREATE TABLE conversations (id INTEGER,\
participant_id INTEGER, \
FOREIGN KEY(participant_id) REFERENCES profiles(id))";
auto tableInteractions = "CREATE TABLE interactions (id INTEGER PRIMARY KEY,\
account_id INTEGER, \
author_id INTEGER, \
conversation_id INTEGER, \
timestamp INTEGER, \
body TEXT, \
type TEXT, \
status TEXT, \
daemon_id TEXT, \
FOREIGN KEY(account_id) REFERENCES profiles(id), \
FOREIGN KEY(author_id) REFERENCES profiles(id), \
FOREIGN KEY(conversation_id) REFERENCES conversations(id))";
// add profiles table
if (not db_.tables().contains("profiles", Qt::CaseInsensitive)
and not query.exec(tableProfiles)) {
throw QueryError(query);
}
// add conversations table
if (not db_.tables().contains("conversations", Qt::CaseInsensitive)
and not query.exec(tableConversations)) {
throw QueryError(query); }
// add interactions table
if (not db_.tables().contains("interactions", Qt::CaseInsensitive)
and not query.exec(tableInteractions)) {
throw QueryError(query);
}
storeVersion(VERSION);
}
void
Database::storeVersion(const std::string& version)
{
QSqlQuery query;
auto storeVersionQuery = std::string("PRAGMA user_version = ") + version;
if (not query.exec(storeVersionQuery.c_str()))
throw QueryError(query);
}
int
Database::insertInto(const std::string& table, // "tests"
const std::map<std::string, std::string>& bindCol, // {{":id", "id"}, {":forename", "colforname"}, {":name", "colname"}}
const std::map<std::string, std::string>& bindsSet) // {{":id", "7"}, {":forename", "alice"}, {":name", "cooper"}}
{
QSqlQuery query;
std::string columns;
std::string binds;
for (const auto& entry : bindCol) {
columns += entry.second + ",";
binds += entry.first + ",";
}
// remove the last ','
columns.pop_back();
binds.pop_back();
auto prepareStr = std::string("INSERT INTO " + table + " (" + columns + ") VALUES (" + binds + ")");
query.prepare(prepareStr.c_str());
for (const auto& entry : bindsSet)
query.bindValue(entry.first.c_str(), entry.second.c_str());
if (not query.exec())
throw QueryInsertError(query, table, bindCol, bindsSet);
if (not query.exec("SELECT last_insert_rowid()"))
throw QueryInsertError(query, table, bindCol, bindsSet);
if (!query.next())
return -1;
return query.value(0).toInt();
}
void
Database::update(const std::string& table, // "tests"
const std::string& set, // "location=:place, phone:=nmbr"
const std::map<std::string, std::string>& bindsSet, // {{":place", "montreal"}, {":nmbr", "514"}}
const std::string& where, // "contact=:name AND id=:id
const std::map<std::string, std::string>& bindsWhere) // {{":name", "toto"}, {":id", "65"}}
{
QSqlQuery query;
auto prepareStr = std::string("UPDATE " + table + " SET " + set + " WHERE " + where);
query.prepare(prepareStr.c_str());
for (const auto& entry : bindsSet)
query.bindValue(entry.first.c_str(), entry.second.c_str());
for (const auto& entry : bindsWhere)
query.bindValue(entry.first.c_str(), entry.second.c_str());
if (not query.exec())
throw QueryUpdateError(query, table, set, bindsSet, where, bindsWhere);
}
Database::Result
Database::select(const std::string& select, // "id", "body", ...
const std::string& table, // "tests"
const std::string& where, // "contact=:name AND id=:id
const std::map<std::string, std::string>& bindsWhere) // {{":name", "toto"}, {":id", "65"}}
{
QSqlQuery query;
std::string columnsSelect;
auto prepareStr = std::string("SELECT " + select + " FROM " + table + " WHERE " + where);
query.prepare(prepareStr.c_str());
for (const auto& entry : bindsWhere)
query.bindValue(entry.first.c_str(), entry.second.c_str());
if (not query.exec())
throw QuerySelectError(query, select, table, where, bindsWhere);
QSqlRecord rec = query.record();
const auto col_num = rec.count();
Database::Result result = {col_num, std::vector<std::string>()};
// for each row
while (query.next()) {
for (int i = 0 ; i < col_num ; i++)
result.payloads.emplace_back(query.value(i).toString().toStdString());
}
return std::move(result);
}
void
Database::deleteFrom(const std::string& table, // "tests"
const std::string& where, // "contact=:name AND id=:id
const std::map<std::string, std::string>& bindsWhere) // {{":name", "toto"}, {":id", "65"}}
{
QSqlQuery query;
auto prepareStr = std::string("DELETE FROM " + table + " WHERE " + where);
query.prepare(prepareStr.c_str());
for (const auto& entry : bindsWhere)
query.bindValue(entry.first.c_str(), entry.second.c_str());
if(not query.exec())
throw QueryDeleteError(query, table, where, bindsWhere);
}
Database::QueryError::QueryError(const QSqlQuery& query)
: std::runtime_error(query.lastError().text().toStdString())
, query(query)
{}
Database::QueryInsertError::QueryInsertError(const QSqlQuery& query,
const std::string& table,
const std::map<std::string, std::string>& bindCol,
const std::map<std::string, std::string>& bindsSet)
: QueryError(query)
, table(table), bindCol(bindCol), bindsSet(bindsSet)
{}
std::string
Database::QueryInsertError::details()
{
std::ostringstream oss;
oss << "paramaters sent :";
oss << "table = " << table.c_str();
for (auto& b : bindCol)
oss << " {" << b.first.c_str() << "}, {" << b.second.c_str() <<"}";
for (auto& b : bindsSet)
oss << " {" << b.first.c_str() << "}, {" << b.second.c_str() <<"}";
return oss.str();
}
Database::QueryUpdateError::QueryUpdateError(const QSqlQuery& query,
const std::string& table,
const std::string& set,
const std::map<std::string, std::string>& bindsSet,
const std::string& where,
const std::map<std::string, std::string>& bindsWhere)
: QueryError(query)
, table(table), set(set), bindsSet(bindsSet), where(where), bindsWhere(bindsWhere)
{}
std::string
Database::QueryUpdateError::details()
{
std::ostringstream oss;
oss << "paramaters sent :";
oss << "table = " << table.c_str();
oss << "set = " << set.c_str();
oss << "bindsSet :";
for (auto& b : bindsSet)
oss << " {" << b.first.c_str() << "}, {" << b.second.c_str() <<"}";
oss << "where = " << where.c_str();
oss << "bindsWhere :";
for (auto& b : bindsWhere)
oss << " {" << b.first.c_str() << "}, {" << b.second.c_str() <<"}";
return oss.str();
}
Database::QuerySelectError::QuerySelectError(const QSqlQuery& query,
const std::string& select,
const std::string& table,
const std::string& where,
const std::map<std::string, std::string>& bindsWhere)
: QueryError(query)
, select(select), table(table), where(where), bindsWhere(bindsWhere)
{}
std::string
Database::QuerySelectError::details()
{
std::ostringstream oss;
oss << "paramaters sent :";
oss << "select = " << select.c_str();
oss << "table = " << table.c_str();
oss << "where = " << where.c_str();
oss << "bindsWhere :";
for (auto& b : bindsWhere)
oss << " {" << b.first.c_str() << "}, {" << b.second.c_str() <<"}";
return oss.str();
}
Database::QueryDeleteError::QueryDeleteError(const QSqlQuery& query,
const std::string& table,
const std::string& where,
const std::map<std::string, std::string>& bindsWhere)
: QueryError(query)
, table(table), where(where), bindsWhere(bindsWhere){}
std::string
Database::QueryDeleteError::details()
{
std::ostringstream oss;
oss << "paramaters sent :";
oss << "table = " << table.c_str();
oss << "where = " << where.c_str();
oss << "bindsWhere :";
for (auto& b : bindsWhere)
oss << " {" << b.first.c_str() << "}, {" << b.second.c_str() <<"}";
return oss.str();
}
void
Database::migrateOldFiles()
{
migrateLocalProfiles();
migratePeerProfiles();
migrateTextHistory();
// NOTE we don't remove old files for now.
}
void
Database::migrateLocalProfiles()
{
const QDir profilesDir = (QStandardPaths::writableLocation(QStandardPaths::DataLocation)) + "/profiles/";
const QStringList entries = profilesDir.entryList({QStringLiteral("*.vcf")}, QDir::Files);
foreach (const QString& item , entries) {
auto filePath = profilesDir.path() + '/' + item;
QString content;
QFile file(filePath);
if (file.open(QIODevice::ReadOnly | QIODevice::Text)) {
content = QString::fromUtf8(file.readAll());
} else {
qWarning() << "Could not open .vcf file";
continue;
}
auto personProfile = new Person(nullptr);
QList<Account*> accs;
VCardUtils::mapToPerson(personProfile, content.toUtf8(), &accs);
const auto vCard = VCardUtils::toHashMap(content.toUtf8());
// all accounts have the same profile picture for now.
const auto alias = vCard["FN"];
const auto avatar = vCard["PHOTO;ENCODING=BASE64;TYPE=PNG"];
for (const auto& account: accs) {
if (!account) continue;
auto type = account->protocol() == Account::Protocol::RING ? "RING" : "SIP";
auto uri = account->username();
// Remove the ring: from the username because account uri is stored without "ring:" in the database
if (uri.startsWith("ring:")) {
uri = uri.mid(std::string("ring:").size());
}
if (select("id", "profiles","uri=:uri", {{":uri", uri.toStdString()}}).payloads.empty()) {
insertInto("profiles",
{{":uri", "uri"}, {":alias", "alias"},
{":photo", "photo"}, {":type", "type"},
{":status", "status"}},
{{":uri", uri.toStdString()}, {":alias", alias.toStdString()},
{":photo", avatar.toStdString()}, {":type", type},
{":status", "TRUSTED"}});
}
}
}
}
voidDatabase::migratePeerProfiles()
{
const QDir profilesDir = (QStandardPaths::writableLocation(QStandardPaths::DataLocation)) + "/peer_profiles/";
const QStringList entries = profilesDir.entryList({QStringLiteral("*.vcf")}, QDir::Files);
foreach (const QString& item , entries) {
auto filePath = profilesDir.path() + '/' + item;
QString content;
QFile file(filePath);
if (file.open(QIODevice::ReadOnly | QIODevice::Text)) {
content = QString::fromUtf8(file.readAll());
} else {
qWarning() << "Could not open vcf file";
continue;
}
const auto vCard = VCardUtils::toHashMap(content.toUtf8());
auto uri = vCard["TEL;other"];
const auto alias = vCard["FN"];
const auto avatar = vCard["PHOTO;ENCODING=BASE64;TYPE=PNG"];
const std::string type = uri.startsWith("ring:") ? "RING" : "SIP";
if (uri.startsWith("ring:")) {
uri = uri.mid(std::string("ring:").size());
}
if (select("id", "profiles","uri=:uri", {{":uri", uri.toStdString()}}).payloads.empty()) {
insertInto("profiles",
{{":uri", "uri"}, {":alias", "alias"}, {":photo", "photo"}, {":type", "type"},
{":status", "status"}},
{{":uri", uri.toStdString()}, {":alias", alias.toStdString()},
{":photo", avatar.toStdString()}, {":type", type},
{":status", "TRUSTED"}});
}
}
}
void
Database::migrateTextHistory()
{
// load all text recordings so we can recover CMs that are not in the call history
QDir dir(QStandardPaths::writableLocation(QStandardPaths::DataLocation) + "/text/");
if (dir.exists()) {
// get .json files, sorted by time, latest first
QStringList filters;
filters << "*.json";
auto list = dir.entryInfoList(filters, QDir::Files | QDir::NoSymLinks | QDir::Readable, QDir::Time);
for (int i = 0; i < list.size(); ++i) {
QFileInfo fileInfo = list.at(i);
QString content;
QFile file(fileInfo.absoluteFilePath());
if (file.open(QIODevice::ReadOnly | QIODevice::Text)) {
content = QString::fromUtf8(file.readAll());
} else {
qWarning() << "Could not open text recording json file";
continue;
}
if (!content.isEmpty()) {
QJsonParseError err;
auto loadDoc = QJsonDocument::fromJson(content.toUtf8(), &err).object();
if (loadDoc.find("peers") == loadDoc.end()) continue;
if (loadDoc.find("groups") == loadDoc.end()) continue;
// Load account
auto peersObject = loadDoc["peers"].toArray()[0].toObject();
auto account = AccountModel::instance().getById(peersObject["accountId"].toString().toUtf8()); if (!account) continue;
auto accountUri = account->username();
if (accountUri.startsWith("ring:")) {
accountUri = accountUri.mid(QString("ring:").length());
}
auto accountIds = select("id", "profiles","uri=:uri", {{":uri", accountUri.toStdString()}}).payloads;
auto contactIds = select("id", "profiles","uri=:uri", {{":uri", peersObject["uri"].toString().toStdString()}}).payloads;
if (contactIds.empty()) {
insertInto("profiles",
{{":uri", "uri"}, {":alias", "alias"}, {":photo", "photo"}, {":type", "type"},
{":status", "status"}},
{{":uri", peersObject["uri"].toString().toStdString()}, {":alias", ""},
{":photo", ""}, {":type", "RING"},
{":status", "NONTRUSTED"}});
// NOTE: this profile is in a case where it's not a contact for the daemon. So we choose to remove this contact from
// conversations.
contactIds = select("id", "profiles","uri=:uri", {{":uri", peersObject["uri"].toString().toStdString()}}).payloads;
}
if (accountIds.empty()) {
qDebug() << "Can't find profile for URI: " << peersObject["accountId"].toString() << ". Ignore this file.";
} else if (contactIds.empty()) {
qDebug() << "Can't find profile for URI: " << peersObject["uri"].toString() << ". Ignore this file.";
} else {
auto contactId = contactIds[0];
auto accountId = accountIds[0];
auto newConversationsId = select("IFNULL(MAX(id), 0) + 1",
"conversations",
"1=1",
{}).payloads[0];
try
{
QSqlDatabase::database().transaction();
insertInto("conversations",
{{":id", "id"}, {":participant_id", "participant_id"}},
{{":id", newConversationsId}, {":participant_id", accountId}});
insertInto("conversations",
{{":id", "id"}, {":participant_id", "participant_id"}},
{{":id", newConversationsId}, {":participant_id", contactId}});
QSqlDatabase::database().commit();
} catch (QueryInsertError& e) {
qDebug() << e.details().c_str();
QSqlDatabase::database().rollback();
}
// Load interactions
auto groupsArray = loadDoc["groups"].toArray();
for (const auto& groupObject: groupsArray) {
auto messagesArray = groupObject.toObject()["messages"].toArray();
for (const auto& messageRef: messagesArray) {
auto messageObject = messageRef.toObject();
auto direction = messageObject["direction"].toInt();
auto body = messageObject["payloads"].toArray()[0].toObject()["payload"].toString();
insertInto("interactions",
{{":account_id", "account_id"}, {":author_id", "author_id"},
{":conversation_id", "conversation_id"}, {":timestamp", "timestamp"},
{":body", "body"}, {":type", "type"},
{":status", "status"}},
{{":account_id", accountId}, {":author_id", direction ? accountId : contactId},
{":conversation_id", newConversationsId},
{":timestamp", std::to_string(messageObject["timestamp"].toInt())},
{":body", body.toStdString()}, {":type", "TEXT"},
{":status", direction ? "SUCCEED" : "READ"}});
}
}
}
} else {
qWarning() << "Text recording file is empty";
}
}
}}
} // namespace lrc