/******************************************************************************************** * * * Copyright (C) 2017 Armin Felder, Dennis Beier * * This file is part of RocketChatMobileEngine . * * * * RocketChatMobileEngine 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 3 of the License, or * * (at your option) any later version. * * * * RocketChatMobileEngine 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 * * along with RocketChatMobileEngine. If not, see . * * * ********************************************************************************************/ #include #include #include #include #include #include #include #include #include #include #include #include #include #include "persistancelayer.h" PersistanceLayer *PersistanceLayer::persistanceLayer = nullptr; PersistanceLayer *PersistanceLayer::instance() { if ( persistanceLayer == nullptr ) { persistanceLayer = new PersistanceLayer; //persistanceLayer->init(); } return persistanceLayer; } void PersistanceLayer::initShema() { initQueries(); mDb.transaction(); QSqlQuery pragmaquery; pragmaquery.prepare( QStringLiteral( "PRAGMA synchronous = OFF" ) ); if ( !pragmaquery.exec() ) { qDebug() << pragmaquery.lastError(); } pragmaquery.prepare( QStringLiteral( "PRAGMA JOURNAL_MODE=MEMORY" ) ); if ( !pragmaquery.exec() ) { qDebug() << pragmaquery.lastError(); } pragmaquery.prepare( QStringLiteral( "PRAGMA TEMP_STORE=MEMORY" ) ); if ( !pragmaquery.exec() ) { qDebug() << pragmaquery.lastError(); } pragmaquery.prepare( QStringLiteral( "PRAGMA PAGE_SIZE=4096" ) ); if ( !pragmaquery.exec() ) { qDebug() << pragmaquery.lastError(); } /* pragmaquery.prepare( "PRAGMA locking_mode = EXCLUSIVE" ); if ( !pragmaquery.exec() ) { qDebug() << pragmaquery.lastError(); } */ QSqlQuery query; query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS user_data " "(id integer primary key," "username varchar(40)," "password varchar(40)," "token varchar(100)," "token_expire integer," "user_id varchar(12)," "server varchar(100))" ) ); if ( !query.exec() ) { qWarning() << query.lastError(); } query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS rooms " "(id varchar(12) primary key, " "name varchar(100)," "type varchar(10)," "read_only bool," "muted text," "archived bool," "joined bool, " "blocked bool DEFAULT 0, " "username varchar(100) DEFAULT \"\", " "chat_partner_id varchar(100) DEFAULT \"\", " "updated_at integer)" ) ); if ( !query.exec() ) { qWarning() << query.lastError(); } query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS messages " "(id varchar(12) primary key," "rid varchar(12)," "author varchar(30)," "user_id varchar(12)," "ts integer," "read BOOLEAN DEFAULT FALSE," "json text)" ) ); if ( !query.exec() ) { qWarning() << query.lastError(); } query.prepare( QStringLiteral( "CREATE INDEX msgRidIndex " "ON messages (rid)" ) ); if ( !query.exec() ) { qWarning() << query.lastError(); } query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS file_cache " "(url varchar(200) primary key," "path varchar(200)," "ts integer)" ) ); if ( !query.exec() ) { qWarning() << query.lastError(); } query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS current_room" "(id integer primary key," "rid varchar(12)," "name varchar(30))" ) ); if ( !query.exec() ) { qWarning() << query.lastError(); } query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS custom_emojis" "(id varchar(30) primary key," "file varchar(200)," "html varchar(200)," "unicode varchar(20)," "category varchar(100)," "sort_order integer DEFAULT 1)" ) ); if ( !query.exec() ) { qWarning() << query.lastError(); } query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS blocked_users" "(id varchar(30) primary key, author varchar(30) )" ) ); if ( !query.exec() ) { qWarning() << query.lastError(); } /* query.prepare( QStringLiteral( "CREATE INDEX authorIdx " "ON blocked_users (author)" ) ); if ( !query.exec() ) { qWarning() << query.lastError(); } */ mDb.commit(); query.prepare( QStringLiteral( "SELECT db_ver FROM app_info WHERE id=0" ) ); int dbVer = -1; if ( !query.exec() ) { qWarning() << query.lastError(); } else { dbVer = 0; QSqlRecord rec = query.record(); int dbVerIndex = rec.indexOf( QStringLiteral( "db_ver" ) ); while ( query.next() ) { dbVer = query.value( dbVerIndex ).toInt(); } } query.finish(); if ( !dbVer ) { upgradeSchema(); } else if ( dbVer == -1 ) { mDb.transaction(); query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS app_info" "(id varchar(30) primary key," "db_ver integer," "app_ver integer)" ) ); if ( !query.exec() ) { qWarning() << query.lastError(); } QString dbVersion = QString::number( DBVERSION ); query.prepare( QStringLiteral( "INSERT INTO app_info (db_ver) VALUES(" ) + dbVersion + ")" ); if ( !query.exec() ) { qWarning() << query.lastError(); } mDb.commit(); } mDb.transaction(); query.prepare( QStringLiteral( "CREATE TABLE IF NOT EXISTS app_settings" "(id integer primary key," "serverid integer," "property varchar(100) UNIQUE," "value varchar(100))" ) ); if ( !query.exec() ) { qWarning() << query.lastError(); } mDb.commit(); query.prepare( QStringLiteral( "SELECT id FROM custom_emojis" ) ); if ( !query.exec() ) { qWarning() << "failed SELECT custom_emojis"; qWarning() << query.lastError(); } else { int size = 0; while ( query.next() ) { size++; break; } query.finish(); if ( size <= 0 ) { QFile dump( QStringLiteral( ":/sql/emojis.sql" ) ); if ( !dump.open( QIODevice::ReadOnly | QIODevice::Text ) ) { qWarning() << "error loading dump"; qWarning() << dump.errorString(); } else { mDb.transaction(); QTextStream in( &dump ); QString dumpString; dumpString.reserve( 200 ); while ( !in.atEnd() ) { dumpString = in.readLine(); query.prepare( dumpString ); if ( !query.exec() ) { qWarning() << "failed execute import dump query"; qWarning() << query.lastError(); } } dump.close(); mDb.commit(); } } } mDb.commit(); } void PersistanceLayer::upgradeSchema() { QSqlQuery query; query.prepare( QStringLiteral( "SELECT db_ver FROM app_info" ) ); if ( !query.exec() ) { qWarning() << "failed execute app_version query"; } else { int dbVersion = 0; QSqlRecord rec = query.record(); int dbVerIndex = rec.indexOf( "db_ver" ); while ( query.next() ) { dbVersion = query.value( dbVerIndex ).toInt(); } query.finish(); if ( dbVersion < DBVERSION ) { QFile sqlFile; int currentTarget = 0; if ( dbVersion < 1 ) { currentTarget = 1; sqlFile.setFileName( QStringLiteral( ":/sql/migrations/1.sql" ) ); query.prepare( QStringLiteral( "DELETE FROM custom_emojis" ) ); if ( query.exec() ) { qWarning() << "truncation of, custom_emojis failed"; qWarning() << query.lastError(); } } else if ( dbVersion < 2 ) { currentTarget = 2; sqlFile.setFileName( QStringLiteral( ":/sql/migrations/2.sql" ) ); } else if ( dbVersion < 3 ) { currentTarget = 3; sqlFile.setFileName( QStringLiteral( ":/sql/migrations/3.sql" ) ); } else if ( dbVersion < 4 ) { currentTarget = 4; sqlFile.setFileName( QStringLiteral( ":/sql/migrations/4.sql" ) ); } else if ( dbVersion < 5 ) { currentTarget = 5; sqlFile.setFileName( QStringLiteral( ":/sql/migrations/5.sql" ) ); } else if ( dbVersion < 6 ) { currentTarget = 6; sqlFile.setFileName( QStringLiteral( ":/sql/migrations/6.sql" ) ); } if ( !sqlFile.open( QIODevice::ReadOnly | QIODevice::Text ) ) { qWarning() << "error loading dump"; qWarning() << sqlFile.errorString(); } else { mDb.transaction(); QTextStream in( &sqlFile ); QString line; while ( !in.atEnd() ) { line = in.readLine(); query.prepare( line ); if ( !query.exec() ) { qWarning() << "migration to DB ver " << currentTarget << " failed"; qWarning() << query.lastError(); qWarning() << query.lastQuery(); exit( EXIT_FAILURE ); } } mDb.commit(); } upgradeSchema(); } } } void PersistanceLayer::wipeDb() { mDb.close(); mDbReady = false; QString directory = QStandardPaths::writableLocation( QStandardPaths::AppLocalDataLocation ); QString filePath = directory + QStringLiteral( "/data.sqlite" ); QFile file( filePath ); if ( !file.remove() ) { qWarning() << file.errorString(); } else { qDebug() << "db wiped"; QDir fileDownloadsDir( QStandardPaths::writableLocation( QStandardPaths::TempLocation ) + QStringLiteral( "/rocketChat" ) ); if ( fileDownloadsDir.removeRecursively() ) { qDebug() << "Downloads wiped"; } else { qWarning() << "can't wipe downloads"; } } init(); } void PersistanceLayer::transaction( ) { commitMutex.lock(); // if ( mTransactionCounter == 0 ) { if ( !mDb.transaction() ) { qWarning() << mDb.lastError(); } // } mTransactionCounter++; commitMutex.unlock(); } void PersistanceLayer::askForcommit() { // commitMutex.lock(); // mCommitCounter++; // commitMutex.unlock(); commit( ); } void PersistanceLayer::commit( ) { commitMutex.lock(); //if ( mCommitCounter && mCommitCounter == mTransactionCounter ) { qDebug() << "commit !"; if ( !mDb.commit() ) { qWarning() << mDb.lastError(); } else { mCommitCounter = 0; mTransactionCounter = 0; } //} commitMutex.unlock(); } void PersistanceLayer::setUserName( const QString &pUsername ) { QSqlQuery querySetUsername; querySetUsername.prepare( QStringLiteral( "UPDATE user_data " "SET username=:username WHERE id=:id" ) ); querySetUsername.bindValue( QStringLiteral( ":id" ), 0 ); querySetUsername.bindValue( QStringLiteral( ":username" ), pUsername ); if ( !querySetUsername.exec() ) { qWarning() << querySetUsername.lastError(); } } void PersistanceLayer::setPassword( const QString &pPassword ) { QSqlQuery querySetPassword; querySetPassword.prepare( QStringLiteral( "UPDATE user_data " "SET password=:password WHERE id=:id" ) ); querySetPassword.bindValue( QStringLiteral( ":password" ), pPassword ); querySetPassword.bindValue( QStringLiteral( ":id" ), 0 ); if ( !querySetPassword.exec() ) { qWarning() << querySetPassword.lastError(); } } void PersistanceLayer::setToken( const QString &pToken, uint pExpire ) { QSqlQuery querySetToken; querySetToken.prepare( QStringLiteral( "UPDATE user_data " "SET token=:token, token_expire=:token_expire WHERE id=:id" ) ); querySetToken.bindValue( QStringLiteral( ":token" ), pToken ); querySetToken.bindValue( QStringLiteral( ":id" ), 0 ); querySetToken.bindValue( QStringLiteral( ":token_expire" ), pExpire ); if ( !querySetToken.exec() ) { qWarning() << querySetToken.lastError(); } } void PersistanceLayer::setUserData( const QString &pUser, const QString &pPass ) { QSqlQuery querySetUser; querySetUser.prepare( QStringLiteral( "REPLACE INTO user_data " "(id,password,username) VALUES(:id,:password,:username)" ) ); querySetUser.bindValue( ":id", 0 ); querySetUser.bindValue( ":password", pPass ); querySetUser.bindValue( ":username", pUser ); if ( !querySetUser.exec() ) { qWarning() << querySetUser.lastError(); qWarning() << querySetUser.lastQuery(); } } void PersistanceLayer::setUserId( const QString &pUserId ) { QSqlQuery querySetUserId; querySetUserId.prepare( QStringLiteral( "UPDATE user_data SET user_id=:userId WHERE id=:id" ) ); querySetUserId.bindValue( QStringLiteral( ":id" ), 0 ); querySetUserId.bindValue( QStringLiteral( ":userId" ), pUserId ); if ( !querySetUserId.exec() ) { qWarning() << querySetUserId.lastError(); } } void PersistanceLayer::setCurrentChannel( const QString &pChannelId, const QString &pChannelName ) { QSqlQuery querySetChannel; querySetChannel.prepare( QStringLiteral( "REPLACE INTO current_room" " (id,rid,name) VALUES(:id,:rid,:name)" ) ); querySetChannel.bindValue( QStringLiteral( ":id" ), 0 ); querySetChannel.bindValue( QStringLiteral( ":rid" ), pChannelId ); querySetChannel.bindValue( QStringLiteral( ":name" ), pChannelName ); if ( !querySetChannel.exec() ) { qWarning() << querySetChannel.lastError(); } } void PersistanceLayer::setSetting( const QString &pProperty, const QString &pValue ) { QSqlQuery querySetSetting; querySetSetting.prepare( QStringLiteral( "REPLACE INTO app_settings" " (property,value) VALUES(:property,:value)" ) ); querySetSetting.bindValue( QStringLiteral( ":property" ), pProperty ); querySetSetting.bindValue( QStringLiteral( ":value" ), pValue ); if ( !querySetSetting.exec() ) { qWarning() << querySetSetting.lastQuery(); qWarning() << querySetSetting.lastError(); } } void PersistanceLayer::addChannel( const QString &pId, const QString &pName, const QString &pType, qint64 pUpdatedAt, bool pJoined, bool pReadOnly, const QString &pMuted, bool pArchived, bool pBlocked, const QString &pUsername, const QString &pChatPartnerId ) { QSqlQuery queryAddChannel; queryAddChannel.prepare( QStringLiteral( "REPLACE INTO rooms " "(id,name,type,joined,read_only,muted,archived,blocked,username,chat_partner_id, updated_at) VALUES(:id,:name,:type,:joined,:read_only,:muted,:archived,:blocked,:username,:chat_partner_id,:updated_at)" ) ); queryAddChannel.bindValue( QStringLiteral( ":id" ), pId ); queryAddChannel.bindValue( QStringLiteral( ":name" ), pName ); queryAddChannel.bindValue( QStringLiteral( ":type" ), pType ); queryAddChannel.bindValue( QStringLiteral( ":joined" ), pJoined ); queryAddChannel.bindValue( QStringLiteral( ":read_only" ), pReadOnly ); queryAddChannel.bindValue( QStringLiteral( ":muted" ), pMuted ); queryAddChannel.bindValue( QStringLiteral( ":archived" ), pArchived ); queryAddChannel.bindValue( QStringLiteral( ":blocked" ), pBlocked ); queryAddChannel.bindValue( QStringLiteral( ":username" ), pUsername ); queryAddChannel.bindValue( QStringLiteral( ":chat_partner_id" ), pChatPartnerId ); queryAddChannel.bindValue( QStringLiteral( ":updated_at" ), pUpdatedAt ); if ( !queryAddChannel.exec() ) { qWarning() << queryAddChannel.lastError(); qWarning() << queryAddChannel.lastQuery(); } queryAddChannel.finish(); } void PersistanceLayer::deleteChannel( const QString &pId ) { QSqlQuery queryDeleteChannel; queryDeleteChannel.prepare( QStringLiteral( "DELETE FROM rooms WHERE id=:id" ) ); queryDeleteChannel.bindValue( QStringLiteral( ":id" ), pId ); if ( !queryDeleteChannel.exec() ) { qWarning() << queryDeleteChannel.lastError(); } else { QSqlQuery queryDeleteMessagesFromChannel; queryDeleteMessagesFromChannel.prepare( QStringLiteral( "DELETE FROM messages WHERE rid=:id" ) ); queryDeleteMessagesFromChannel.bindValue( QStringLiteral( ":id" ), pId ); if ( !queryDeleteMessagesFromChannel.exec() ) { qWarning() << queryDeleteMessagesFromChannel.lastError(); } } } void PersistanceLayer::addMessage( const QString &pId, const QString &pRid, const QString &pAuthor, qint64 pTs, const QString &pJson, const QString &pUserId ) { QSqlQuery queryAddMessage; queryAddMessage.prepare( QStringLiteral( "REPLACE INTO messages " "(id,rid,author,ts,json,read, user_id) VALUES (:id,:rid,:author,:ts,:json,0,:user_id)" ) ); queryAddMessage.bindValue( QStringLiteral( ":id" ), pId ); queryAddMessage.bindValue( QStringLiteral( ":rid" ), pRid ); queryAddMessage.bindValue( QStringLiteral( ":author" ), pAuthor ); queryAddMessage.bindValue( QStringLiteral( ":ts" ), pTs ); queryAddMessage.bindValue( QStringLiteral( ":json" ), pJson ); queryAddMessage.bindValue( QStringLiteral( ":user_id" ), pUserId ); if ( !queryAddMessage.exec() ) { qWarning() << "id: " << pId << " rid: " << pRid << " author: " << pAuthor << " ts: " << pTs << " json: " << pJson << "userId: " << pUserId; qWarning() << queryAddMessage.lastQuery(); qWarning() << queryAddMessage.executedQuery(); qWarning() << queryAddMessage.lastError(); } } void PersistanceLayer::addFileCacheEntry( const QString &pUrl, const QString &pPath ) { QSqlQuery queryAddCacheEntry; queryAddCacheEntry.prepare( QStringLiteral( "REPLACE INTO file_cache (url,path,ts)" " VALUES (:url,:path,:ts)" ) ); qDebug() << pUrl; qDebug() << pPath; QDateTime dateTime = QDateTime::currentDateTime(); queryAddCacheEntry.bindValue( QStringLiteral( ":url" ), pUrl ); queryAddCacheEntry.bindValue( QStringLiteral( ":path" ), pPath ); queryAddCacheEntry.bindValue( QStringLiteral( ":ts" ), dateTime.toTime_t() ); if ( !queryAddCacheEntry.exec() ) { qWarning() << queryAddCacheEntry.lastError(); } } void PersistanceLayer::addCustomEmoji( const QString &pTag, const QString &pPath, const QString &pHtml, const QString &pCategory ) { addCustomEmoji( pTag, pPath, pHtml, pCategory, QString( "" ) ); } void PersistanceLayer::addCustomEmoji( const QString &pTag, const QString &pPath, const QString &pHtml, const QString &pCategory, const QString &pUnicode ) { addCustomEmoji( pTag, pPath, pHtml, pCategory, pUnicode, 0 ); } void PersistanceLayer::addCustomEmoji( const QString &pTag, const QString &pPath, const QString &pHtml, const QString &pCategory, const QString &pUnicode, int pOrder ) { QSqlQuery queryAddEmoji; queryAddEmoji.prepare( QStringLiteral( "REPLACE INTO custom_emojis (id,file,html,unicode,category,sort_order)" " VALUES (:id,:file,:html,:unicode,:category,:sort_order)" ) ); queryAddEmoji.bindValue( QStringLiteral( ":id" ), pTag ); queryAddEmoji.bindValue( QStringLiteral( ":file" ), pPath ); queryAddEmoji.bindValue( QStringLiteral( ":html" ), pHtml ); queryAddEmoji.bindValue( QStringLiteral( ":unicode" ), pUnicode ); queryAddEmoji.bindValue( QStringLiteral( ":category" ), pCategory ); queryAddEmoji.bindValue( QStringLiteral( ":sort_order" ), pOrder ); if ( !queryAddEmoji.exec() ) { qWarning() << queryAddEmoji.lastError(); } } void PersistanceLayer::addUserToBlockList( const QString &pUserId, const QString &pUsername ) { QSqlQuery queryAddUser; queryAddUser.prepare( QStringLiteral( "INSERT INO blocked_users(id, author) VALUES(:id,:author)" ) ); queryAddUser.bindValue( ":id", pUserId ); queryAddUser.bindValue( ":author", pUsername ); if ( !queryAddUser.exec() ) { qWarning() << queryAddUser.lastError(); } } QHash PersistanceLayer::getMessageByid( const QString &pId ) { QSqlQuery queryGetMessageByRid; queryGetMessageByRid.prepare( QStringLiteral( "SELECT * FROM messages LEFT JOIN blocked_users ON (messages.user_id=blocked_users.user_id) WHERE blocked_users.user_id IS NULL AND id=:id" ) ); queryGetMessageByRid.bindValue( QStringLiteral( ":id" ), pId ); QHash row; if ( !queryGetMessageByRid.exec() ) { qWarning() << queryGetMessageByRid.lastError(); } else { QSqlRecord rec = queryGetMessageByRid.record(); int idCol = rec.indexOf( QStringLiteral( "id" ) ); int ridCol = rec.indexOf( QStringLiteral( "rid" ) ); int authorCol = rec.indexOf( QStringLiteral( "author" ) ); int tsCol = rec.indexOf( QStringLiteral( "ts" ) ); int messageCol = rec.indexOf( QStringLiteral( "message" ) ); int typeCol = rec.indexOf( QStringLiteral( "type" ) ); while ( queryGetMessageByRid.next() ) { row[QStringLiteral( "id" )] = queryGetMessageByRid.value( idCol ).toString(); row[QStringLiteral( "rid" )] = queryGetMessageByRid.value( ridCol ).toString(); row[QStringLiteral( "author" )] = queryGetMessageByRid.value( authorCol ).toString(); row[QStringLiteral( "message" )] = queryGetMessageByRid.value( messageCol ).toString(); row[QStringLiteral( "ts" )] = queryGetMessageByRid.value( tsCol ).toString(); row[QStringLiteral( "type" )] = queryGetMessageByRid.value( typeCol ).toString(); } } queryGetMessageByRid.finish(); return row; } QString PersistanceLayer::getUserName() { QSqlQuery queryGetName; queryGetName.prepare( QStringLiteral( "SELECT username FROM user_data WHERE id=0" ) ); QString name = ""; if ( !queryGetName.exec() ) { qWarning() << queryGetName.lastError(); } else { QSqlRecord rec = queryGetName.record(); int nameCol = rec.indexOf( QStringLiteral( "username" ) ); while ( queryGetName.next() ) { name = queryGetName.value( nameCol ).toString(); } } return name; } std::tuple PersistanceLayer::getCurrentChannel() { QString id; QString name; QSqlQuery queryGetCurrentChannel; queryGetCurrentChannel.prepare( QStringLiteral( "SELECT * FROM current_room WHERE id=0" ) ); if ( !queryGetCurrentChannel.exec() ) { qWarning() << queryGetCurrentChannel.lastError(); } else { while ( queryGetCurrentChannel.next() ) { id = queryGetCurrentChannel.value( QStringLiteral( "rid" ) ).toString(); name = queryGetCurrentChannel.value( QStringLiteral( "name" ) ).toString(); } } queryGetCurrentChannel.finish(); return std::tuple( id, name ); } QString PersistanceLayer::getPassword() { QString pass = ""; QSqlQuery queryGetPass; queryGetPass.prepare( QStringLiteral( "SELECT password FROM user_data WHERE id=0" ) ); if ( !queryGetPass.exec() ) { qWarning() << queryGetPass.lastError(); } else { QSqlRecord rec = queryGetPass.record(); int nameCol = rec.indexOf( QStringLiteral( "password" ) ); while ( queryGetPass.next() ) { pass = queryGetPass.value( nameCol ).toString(); } } queryGetPass.finish(); return pass; } QPair PersistanceLayer::getToken() { // getToken.bindValue( ":id", 0 ); QPair token; QSqlQuery queryGetToken; queryGetToken.prepare( QStringLiteral( "SELECT token, token_expire FROM user_data WHERE id=0" ) ); if ( !queryGetToken.exec() ) { qDebug() << queryGetToken.lastError(); } else { QSqlRecord rec = queryGetToken.record(); int tokenCol = rec.indexOf( QStringLiteral( "token" ) ); int expireCol = rec.indexOf( QStringLiteral( "token_expire" ) ); while ( queryGetToken.next() ) { token.first = queryGetToken.value( tokenCol ).toString(); token.second = queryGetToken.value( expireCol ).toUInt(); } } queryGetToken.finish(); return token; } QString PersistanceLayer::getUserId() { QSqlQuery queryGetUserId; queryGetUserId.prepare( "SELECT id,user_id FROM user_data WHERE id=0" ); //getUserId.addBindValue( ":id", 0 ); QString userId; if ( !queryGetUserId.exec() ) { qWarning() << "userid error" << queryGetUserId.lastError(); } else { QSqlRecord rec = queryGetUserId.record(); int userIdCol = rec.indexOf( QStringLiteral( "user_id" ) ); while ( queryGetUserId.next() ) { userId = queryGetUserId.value( userIdCol ).toString(); } } queryGetUserId.finish(); return userId; } QList PersistanceLayer::getMessagesByRid( const QString &pRid ) { QSqlQuery queryGetMessagesByRid; queryGetMessagesByRid.prepare( QStringLiteral( "SELECT * FROM messages WHERE rid=:rid ORDER BY ts DESC LIMIT 20" ) ); queryGetMessagesByRid.bindValue( QStringLiteral( ":rid" ), pRid ); QList rows; rows.reserve( 50 ); if ( !queryGetMessagesByRid.exec() ) { qWarning() << QStringLiteral( "request room with rid: " ) << pRid; qWarning() << queryGetMessagesByRid.lastError(); } else { QSqlRecord rec = queryGetMessagesByRid.record(); int jsonCol = rec.indexOf( QStringLiteral( "json" ) ); int readCol = rec.indexOf( QStringLiteral( "read" ) ); while ( queryGetMessagesByRid.next() ) { //QHash entry; QString json = queryGetMessagesByRid.value( jsonCol ).toString(); bool read = queryGetMessagesByRid.value( readCol ).toBool(); QJsonDocument doc = QJsonDocument::fromJson( json.toUtf8() ); QJsonObject object = doc.object(); object[QStringLiteral( "read" )] = read; //entry[id] = object; // rows[id] = object; rows.append( object ); //rows.append(entry); } } queryGetMessagesByRid.finish(); return rows; } QList PersistanceLayer::getMessagesByRid( const QString &pRid, qint64 pFrom, qint64 pTo ) { QSqlQuery queryGetMessageByRidAndRange; queryGetMessageByRidAndRange.prepare( QStringLiteral( "SELECT * FROM messages WHERE rid=:rid AND ts<=:from AND ts>=:to ORDER BY ts DESC LIMIT 50" ) ); queryGetMessageByRidAndRange.bindValue( QStringLiteral( ":rid" ), pRid ); queryGetMessageByRidAndRange.bindValue( QStringLiteral( ":from" ), static_cast( pFrom / 1000 ) ); queryGetMessageByRidAndRange.bindValue( QStringLiteral( ":to" ), static_cast( pTo / 1000 ) ); QList rows; rows.reserve( 50 ); if ( !queryGetMessageByRidAndRange.exec() ) { qWarning() << "request room with rid: " << pRid; qWarning() << queryGetMessageByRidAndRange.lastError(); } else { QSqlRecord rec = queryGetMessageByRidAndRange.record(); int jsonCol = rec.indexOf( QStringLiteral( "json" ) ); int readCol = rec.indexOf( QStringLiteral( "read" ) ); while ( queryGetMessageByRidAndRange.next() ) { QString json = queryGetMessageByRidAndRange.value( jsonCol ).toString(); bool read = queryGetMessageByRidAndRange.value( readCol ).toBool(); QJsonDocument doc = QJsonDocument::fromJson( json.toUtf8() ); QJsonObject object = doc.object(); object[QStringLiteral( "read" )] = read; //entry["id"] = id; // entry["json"] = object; // rows.append(entry); rows.append( object ); } } queryGetMessageByRidAndRange.finish(); return rows; } QList PersistanceLayer::getMessagesByRid( const QString &pRid, qint64 pFrom, int pLimit ) { QSqlQuery queryGetMessagesByRidLimit; queryGetMessagesByRidLimit.prepare( QStringLiteral( "SELECT * FROM messages WHERE rid=:rid AND ts<=:from ORDER BY ts DESC LIMIT :limit" ) ); queryGetMessagesByRidLimit.bindValue( QStringLiteral( ":rid" ), pRid ); queryGetMessagesByRidLimit.bindValue( QStringLiteral( ":from" ), static_cast( pFrom ) ); queryGetMessagesByRidLimit.bindValue( QStringLiteral( ":limit" ), pLimit ); QList rows; rows.reserve( 50 ); if ( !queryGetMessagesByRidLimit.exec() ) { qWarning() << "request room with rid: " << pRid; qWarning() << queryGetMessagesByRidLimit.lastError(); } else { QSqlRecord rec = queryGetMessagesByRidLimit.record(); int jsonCol = rec.indexOf( QStringLiteral( "json" ) ); int readCol = rec.indexOf( QStringLiteral( "read" ) ); while ( queryGetMessagesByRidLimit.next() ) { // QHash entry; QString json = queryGetMessagesByRidLimit.value( jsonCol ).toString(); bool read = queryGetMessagesByRidLimit.value( readCol ).toBool(); QJsonDocument doc = QJsonDocument::fromJson( json.toUtf8() ); QJsonObject object = doc.object(); if ( object.isEmpty() ) { qWarning() << "invalid message Json: " << json; } else { object[QStringLiteral( "read" )] = read; // QJsonValue idJson = id; // entry["json"] = object; rows.append( object ); } } } queryGetMessagesByRidLimit.finish(); return rows; } QList> PersistanceLayer::getListOfBlockedUsers() { QList> retList; QSqlQuery queryGetBlockedUsers; queryGetBlockedUsers.prepare( QStringLiteral( "SELECT * FROM blocked_users" ) ); if ( !queryGetBlockedUsers.exec() ) { qWarning() << "getList of blocked users "; qWarning() << queryGetBlockedUsers.lastError(); } else { QSqlRecord rec = queryGetBlockedUsers.record(); int idCol = rec.indexOf( "id" ); int nameCol = rec.indexOf( "author" ); while ( queryGetBlockedUsers.next() ) { QPair pair; pair.first = queryGetBlockedUsers.value( idCol ).String; pair.second = queryGetBlockedUsers.value( nameCol ).String; retList.append( pair ); } } queryGetBlockedUsers.finish(); return retList; } QList PersistanceLayer::getChannels( ) { QList roomsList; roomsList.reserve( 100 ); QSqlQuery queryGetChannels; queryGetChannels.prepare( QStringLiteral( "SELECT * FROM rooms ORDER BY updated_at DESC " ) ); if ( !queryGetChannels.exec() ) { qWarning() << queryGetChannels.lastError(); } else { QSqlRecord rec = queryGetChannels.record(); int idCol = rec.indexOf( QStringLiteral( "id" ) ); int nameCol = rec.indexOf( QStringLiteral( "name" ) ); int joinedCol = rec.indexOf( QStringLiteral( "joined" ) ); int typeCol = rec.indexOf( QStringLiteral( "type" ) ); int readOnlyCol = rec.indexOf( QStringLiteral( "read_only" ) ); int mutedCol = rec.indexOf( QStringLiteral( "muted" ) ); int archivedCol = rec.indexOf( QStringLiteral( "archived" ) ); int blocked = rec.indexOf( QStringLiteral( "blocked" ) ); int usernameCol = rec.indexOf( QStringLiteral( "username" ) ); int chatPartnerIdCol = rec.indexOf( QStringLiteral( "chat_partner_id" ) ); int updatedAt = rec.indexOf( QStringLiteral( "updated_at" ) ); while ( queryGetChannels.next() ) { QVariantHash roomsHash; QString muted = queryGetChannels.value( mutedCol ).toString(); roomsHash[QStringLiteral( "id" )] = queryGetChannels.value( idCol ).toString(); roomsHash[QStringLiteral( "name" )] = queryGetChannels.value( nameCol ).toString(); roomsHash[QStringLiteral( "joined" )] = queryGetChannels.value( joinedCol ).toBool(); roomsHash[QStringLiteral( "type" )] = queryGetChannels.value( typeCol ).toString(); roomsHash[QStringLiteral( "readOnly" )] = queryGetChannels.value( readOnlyCol ).toBool(); roomsHash[QStringLiteral( "muted" )] = muted; roomsHash[QStringLiteral( "list" )] = muted.split( ',' ); roomsHash[QStringLiteral( "archived" )] = queryGetChannels.value( archivedCol ).toBool(); roomsHash[QStringLiteral( "blocked" )] = queryGetChannels.value( blocked ).toBool(); roomsHash[QStringLiteral( "username" )] = queryGetChannels.value( usernameCol ).toString(); roomsHash[QStringLiteral( "chatPartnerId" )] = queryGetChannels.value( chatPartnerIdCol ).toString(); roomsHash[QStringLiteral( "updatedAt" )] = queryGetChannels.value( updatedAt ).toLongLong(); roomsList.append( roomsHash ); } } queryGetChannels.finish(); return roomsList; } QList< QHash > PersistanceLayer::getCustomEmojis() { QList > returnList; returnList.reserve( 3000 ); QSqlQuery queryGetEmojies; queryGetEmojies.prepare( QStringLiteral( "SELECT id,file,html,category,unicode,sort_order FROM custom_emojis ORDER BY sort_order ASC" ) ); if ( !queryGetEmojies.exec() ) { qWarning() << queryGetEmojies.lastError(); } else { QSqlRecord rec = queryGetEmojies.record(); int idCol = rec.indexOf( QStringLiteral( "id" ) ); int fileCol = rec.indexOf( QStringLiteral( "file" ) ); int htmlCol = rec.indexOf( QStringLiteral( "html" ) ); int catCol = rec.indexOf( QStringLiteral( "category" ) ); int unicodeCol = rec.indexOf( QStringLiteral( "unicode" ) ); int orderCol = rec.indexOf( QStringLiteral( "sort_order" ) ); while ( queryGetEmojies.next() ) { QHash entry; entry[QStringLiteral( "id" )] = queryGetEmojies.value( idCol ).toString(); entry[QStringLiteral( "file" )] = queryGetEmojies.value( fileCol ).toString(); entry[QStringLiteral( "html" )] = queryGetEmojies.value( htmlCol ).toString(); entry[QStringLiteral( "category" )] = queryGetEmojies.value( catCol ).toString(); entry[QStringLiteral( "unicode" )] = queryGetEmojies.value( unicodeCol ).toString(); entry[QStringLiteral( "sort_order" )] = queryGetEmojies.value( orderCol ).toString(); returnList.append( entry ); } } queryGetEmojies.finish(); return returnList; } QList> PersistanceLayer::getFiles() { QList > returnList; QSqlQuery getFilesQuery; getFilesQuery.prepare( "SELECT path,url FROM file_cache " ); if ( !getFilesQuery.exec() ) { qWarning() << getFilesQuery.lastError(); } else { QSqlRecord rec = getFilesQuery.record(); int pathCol = rec.indexOf( QStringLiteral( "path" ) ); int urlCol = rec.indexOf( QStringLiteral( "url" ) ); while ( getFilesQuery.next() ) { QHash entry; entry[QStringLiteral( "path" )] = getFilesQuery.value( pathCol ).toString(); entry[QStringLiteral( "url" )] = getFilesQuery.value( urlCol ).toString(); returnList.append( entry ); } } return returnList; } QString PersistanceLayer::getFileCacheEntry( const QString &pUrl ) { QSqlQuery queryGetSetting; queryGetSetting.prepare( QStringLiteral( "SELECT property,value FROM app_settings WHERE property=:property" ) ); queryGetFileCacheEntry.bindValue( QStringLiteral( ":url" ), pUrl ); QString returnString = ""; if ( !queryGetFileCacheEntry.exec() ) { qWarning() << queryGetFileCacheEntry.lastError(); } else { QSqlRecord rec = queryGetFileCacheEntry.record(); int pathCol = rec.indexOf( QStringLiteral( "path" ) ); while ( queryGetFileCacheEntry.next() ) { returnString = queryGetFileCacheEntry.value( pathCol ).toString(); } if ( returnString != "" ) { QFile cachedFile( returnString ); if ( !cachedFile.exists() ) { removeFileCacheEntry( pUrl, returnString ); returnString = ""; } } } queryGetFileCacheEntry.finish(); return returnString; } QString PersistanceLayer::getSetting( const QString &pProperty ) { QSqlQuery queryRemoveFileCacheEntry; queryRemoveFileCacheEntry.prepare( QStringLiteral( "DELETE FROM file_cache WHERE url=:url" ) ); queryGetSetting.bindValue( QStringLiteral( ":property" ), pProperty ); QString returnString = ""; if ( !queryGetSetting.exec() ) { qWarning() << queryGetSetting.lastError(); } else { QSqlRecord rec = queryGetSetting.record(); int valueClol = rec.indexOf( QStringLiteral( "value" ) ); while ( queryGetSetting.next() ) { returnString = queryGetSetting.value( valueClol ).toString(); } } queryGetSetting.finish(); return returnString; } void PersistanceLayer::removeFileCacheEntry( const QString &pUrl ) { QString fileName = getFileCacheEntry( pUrl ); removeFileCacheEntry( pUrl, fileName ); } void PersistanceLayer::removeFileCacheEntry( const QString &pUrl, const QString &pPath ) { if ( pUrl != "" && pPath != "" ) { QFile cachedFile( pPath ); if ( cachedFile.exists() ) { if ( !cachedFile.remove() ) { qWarning() << cachedFile.errorString(); } } } queryRemoveFileCacheEntry.bindValue( QStringLiteral( ":url" ), pUrl ); if ( !queryRemoveFileCacheEntry.exec() ) { qWarning() << queryRemoveFileCacheEntry.lastError(); } } QString PersistanceLayer::getNewVideoPath( ) { QString path; path = QStandardPaths::writableLocation( QStandardPaths::MoviesLocation ) + QStringLiteral( "/recordings" ); if ( path != "" ) { QDir dir( path ); if ( !dir.exists() ) { if ( !dir.mkpath( path ) ) { qWarning() << "failed to create folder: " << path; } } QDateTime dateTime = QDateTime::currentDateTime(); uint timeStamp = dateTime.toTime_t(); QFile file( path + '/' + QString( timeStamp ) + QStringLiteral( ".mp4" ) ); if ( !file.exists() ) { //file.open( QIODevice::ReadWrite ); //file.close(); } qDebug() << path + '/' + QString::number( timeStamp ) + ".mp4"; return path + '/' + QString::number( timeStamp ) + QStringLiteral( ".mp4" ); } return ""; } QString PersistanceLayer::getNewImagePath( ) { QString path; path = QStandardPaths::writableLocation( QStandardPaths::PicturesLocation ) + QStringLiteral( "/recordings" ); if ( path != "" ) { QDir dir( path ); if ( !dir.exists() ) { if ( !dir.mkpath( path ) ) { qWarning() << "failed to create folder: " << path; } } QDateTime dateTime = QDateTime::currentDateTime(); uint timeStamp = dateTime.toTime_t(); QFile file( path + '/' + timeStamp + QStringLiteral( ".jpg" ) ); if ( !file.exists() ) { //file.open( QIODevice::ReadWrite ); //file.close(); } qDebug() << path + '/' + QString::number( timeStamp ) + QStringLiteral( ".jpg" ); return path + '/' + QString::number( timeStamp ) + QStringLiteral( ".jpg" ); } return ""; } PersistanceLayer::~PersistanceLayer() { mDb.close(); } void PersistanceLayer::init() { if ( !mDbReady ) { QString directory = QStandardPaths::writableLocation( QStandardPaths::AppLocalDataLocation ); QString filePath = directory + QStringLiteral( "/data.sqlite" ); qDebug() << "SQLITE: " << filePath; mDb.setDatabaseName( filePath ); QDir dbPath( directory ); if ( !dbPath.exists() ) { dbPath.mkpath( directory ); } QFile file( filePath ); if ( !file.exists() ) { file.open( QIODevice::ReadWrite ); file.close(); } if ( !mDb.open() ) { qDebug() << mDb.lastError().text() + "file used:" + filePath; throw std::runtime_error( mDb.lastError().text().toStdString() + "file used:" + filePath.toStdString() ); } // commitTimer.setInterval( 10000 ); connect( &commitTimer, &QTimer::timeout, [&]() { commit(); commitTimer.start(); } ); // commitTimer.start(); mDbReady = true; initShema(); emit( ready() ); } else { emit( ready() ); } } void PersistanceLayer::initQueries() { } void PersistanceLayer::deleteMessage( const QString id ) { QSqlQuery deleteMessage( mDb ); deleteMessage.prepare( "DELETE FROM messages WHERE id=:id;" ); deleteMessage.bindValue( ":id", id ); if ( !deleteMessage.exec() ) { qWarning() << "id: " << id; qWarning() << deleteMessage.lastQuery(); qWarning() << deleteMessage.executedQuery(); qWarning() << deleteMessage.lastError(); } } void PersistanceLayer::close() { persistanceLayer->deleteLater(); }