int WhatsappDatabase::messagesCount(const std::string &chatId, int fromMe)
{
const char *query = "SELECT count(_id) from messages where key_remote_jid = ? and key_from_me = ?";
sqlite3_stmt *res;
if (sqlite3_prepare_v2(database.getHandle(), query, -1, &res, NULL) != SQLITE_OK)
{
throw SQLiteException("Could not load messages", database);
}
if (sqlite3_bind_text(res, 1, chatId.c_str(), -1, SQLITE_STATIC) != SQLITE_OK)
{
throw SQLiteException("Could not bind sql parameter", database);
}
if (sqlite3_bind_int(res, 2, fromMe) != SQLITE_OK)
{
throw SQLiteException("Could not bind sql parameter", database);
}
if (sqlite3_step(res) != SQLITE_ROW)
{
throw SQLiteException("No result for count query", database);
}
int count = sqlite3_column_int(res, 0);
sqlite3_finalize(res);
return count;
}
void PreparedStatement::prepare(const std::string& sql)
{
locker l(d->mutex);
if (d->prepared) {
throw SQLiteException(-1, "Statement is already prepared");
}
if (d->db == nullptr) {
throw SQLiteException(-1, "PreparedStatement::prepare : Database pointer is null");
}
int result = sqlite3_prepare_v2(d->db->getSqltite3db(), sql.c_str(), sql.size() + 1, &d->stmt, nullptr);
if (result != SQLITE_OK) {
throw SQLiteException(result, errorMsg());
}
// The statement is ready
d->prepared = true;
// Count columns
int count = sqlite3_column_count(d->stmt);
// Add column index and name to d->_columnsNames
d->columnsNames->clear();
for (int i = 0; i < count; i++) {
std::string name(sqlite3_column_name(d->stmt, i));
d->columnsNames->emplace(name, i);
}
}
void QueryMessagesThread::run()
{
const char *query = "SELECT key_remote_jid, key_from_me, status, data, timestamp, media_url, media_mime_type, media_wa_type, media_size, media_name, media_duration, latitude, longitude, thumb_image, remote_resource, raw_data " \
"FROM messages " \
"WHERE key_remote_jid = ? " \
"ORDER BY timestamp asc";
sqlite3_stmt *res;
if (sqlite3_prepare_v2(sqLiteDatabase.getHandle(), query, -1, &res, NULL) != SQLITE_OK)
{
throw SQLiteException("Could not load messages", sqLiteDatabase);
}
if (sqlite3_bind_text(res, 1, chatId.c_str(), -1, SQLITE_STATIC) != SQLITE_OK)
{
throw SQLiteException("Could not bind sql parameter", sqLiteDatabase);
}
while (sqlite3_step(res) == SQLITE_ROW)
{
if (!running)
{
break;
}
std::string chatId = sqLiteDatabase.readString(res, 0);
int fromMe = sqlite3_column_int(res, 1);
int status = sqlite3_column_int(res, 2);
std::string data = sqLiteDatabase.readString(res, 3);
long long timestamp = sqlite3_column_int64(res, 4);
std::string mediaUrl = sqLiteDatabase.readString(res, 5);
std::string mediaMimeType = sqLiteDatabase.readString(res, 6);
int mediaWhatsappType = sqlite3_column_int(res, 7);
int mediaSize = sqlite3_column_int(res, 8);
std::string mediaName = sqLiteDatabase.readString(res, 9);
int mediaDuration = sqlite3_column_int(res, 10);
double latitude = sqlite3_column_double(res, 11);
double longitude = sqlite3_column_double(res, 12);
const void *thumbImage = sqlite3_column_blob(res, 13);
int thumbImageSize = sqlite3_column_bytes(res, 13);
std::string remoteResource = sqLiteDatabase.readString(res, 14);
const void *rawData = sqlite3_column_blob(res, 15);
int rawDataSize = sqlite3_column_bytes(res, 15);
WhatsappMessage *message = new WhatsappMessage(chatId, fromMe == 1, status, data, timestamp, 0, 0, mediaUrl, mediaMimeType, mediaWhatsappType, mediaSize, mediaName, mediaDuration, latitude, longitude, thumbImage, thumbImageSize, remoteResource, rawData, rawDataSize);
messages.push_back(message);
}
sqlite3_finalize(res);
}
bool WhatsappDatabase::hasColumn(const std::string &tableName, const std::string &columnName)
{
std::stringstream query;
query << "PRAGMA table_info('" << tableName << "')";
std::string queryString = query.str();
sqlite3_stmt *res;
if (sqlite3_prepare_v2(database.getHandle(), queryString.c_str(), -1, &res, NULL) != SQLITE_OK)
{
throw SQLiteException("Could not query columns", database);
}
while (sqlite3_step(res) == SQLITE_ROW)
{
std::string columnNameDb = database.readString(res, 1);
if (columnName == columnNameDb)
{
sqlite3_finalize(res);
return true;
}
}
sqlite3_finalize(res);
return false;
}
void WhatsappDatabase::getChats(Settings &settings, std::vector<WhatsappChat*> &chats)
{
const char *query = "SELECT chat_list.key_remote_jid, chat_list.subject, chat_list.creation, max(messages.timestamp) " \
"FROM chat_list " \
"LEFT OUTER JOIN messages on messages.key_remote_jid = chat_list.key_remote_jid " \
"GROUP BY chat_list.key_remote_jid, chat_list.subject, chat_list.creation " \
"ORDER BY max(messages.timestamp) desc";
sqlite3_stmt *res;
if (sqlite3_prepare_v2(database.getHandle(), query, -1, &res, NULL) != SQLITE_OK)
{
throw SQLiteException("Could not load chat list", database);
}
while (sqlite3_step(res) == SQLITE_ROW)
{
std::string key = database.readString(res, 0);
std::string subject = database.readString(res, 1);
long long creation = sqlite3_column_int64(res, 2);
long long lastMessage = sqlite3_column_int64(res, 3);
std::string displayName = settings.findDisplayName(key);
int messagesSent = messagesCount(key, 1);
int messagesReceived = messagesCount(key, 0);
WhatsappChat *chat = new WhatsappChat(*this, displayName, key, subject, creation, lastMessage, messagesSent, messagesReceived);
chats.push_back(chat);
}
sqlite3_finalize(res);
}
/**
* Executes a SQL statement. Does not return any data.
* @param strSQL - The SQL statement to execute.
* @param bError - Set to true to trap SQL statement errors, or false to ignore.
* @param abortOnErr - true calls Err::abort on SQLite exception, else just throw the exception.
*/
void SQLiteDatabase::execute(const std::string& strSQL, bool bError, bool abortOnErr )
{
try {
if (m_pdb == NULL) {
throw SQLiteException("ERROR: SQLite Database has not been opened.");
}
char* pszMsg = NULL;
int iReturnCode = sqlite3_exec(m_pdb, strSQL.c_str(), NULL, NULL, &pszMsg);
if (bError) {
if (iReturnCode != SQLITE_OK) {
std::string str = pszMsg;
sqlite3_free(pszMsg);
error(iReturnCode, str);
}
}
else {
if (iReturnCode != SQLITE_OK) {
sqlite3_free(pszMsg);
}
}
}
catch(SQLiteException e) {
if ( abortOnErr ) {
Err::errAbort(std::string("SQLite Database exception: ") + e.getMessage());
}
else {
throw(e);
}
}
}
void importContacts(Settings &settings, const std::string &wadbFilename)
{
SQLiteDatabase wadb(wadbFilename);
const char *query = "SELECT jid, display_name, status " \
"FROM wa_contacts";
sqlite3_stmt *res;
if (sqlite3_prepare_v2(wadb.getHandle(), query, -1, &res, NULL) != SQLITE_OK)
{
throw SQLiteException("Could not load contacts list", wadb);
}
while (sqlite3_step(res) == SQLITE_ROW)
{
std::string jid = wadb.readString(res, 0);
std::string displayName = wadb.readString(res, 1);
std::string status = wadb.readString(res, 2);
if (displayName.length() > 0)
{
settings.write("Contacts/" + jid, displayName);
}
}
sqlite3_finalize(res);
}
void PreparedStatement::executeUpdate()
{
locker l(d->mutex);
int result = next();
if (result != SQLITE_OK && result != SQLITE_DONE) {
throw SQLiteException(result, errorMsg());
}
sqlite3_reset(d->stmt);
}
int PreparedStatement::columnNumber(const std::string &name) const
{
locker l(d->mutex);
auto it = d->columnsNames->find(name);
if (it == d->columnsNames->end()) {
throw SQLiteException(-1, "PreparedStatement::columnName - Invalid column number");
}
return it->second;
}
int SQLiteStatement::execute() const
{
SQLiteResults::SQLiteResult res = static_cast<SQLiteResults::SQLiteResult>(sqlite3_step(this->_stmt));
switch(res)
{
case SQLiteResults::Done:
return sqlite3_changes(this->_db);
case SQLiteResults::Row:
throw SQLiteException("SQLiteStatement::execute() does not expect results");
default:
break;
}
throw SQLiteException(this->_db);
}
bool SQLiteStatement::reset() const
{
SQLiteResults::SQLiteResult res = static_cast<SQLiteResults::SQLiteResult>(sqlite3_reset(this->_stmt));
if(res == SQLiteResults::Ok)
return true;
throw SQLiteException(this->_db);
return false;
}
bool SQLiteStatement::step() const
{
SQLiteResults::SQLiteResult res = static_cast<SQLiteResults::SQLiteResult>(sqlite3_step(this->_stmt));
if((res != SQLiteResults::Done) && (res != SQLiteResults::Row))
throw SQLiteException(this->_db);
if(res == SQLiteResults::Row)
return true;
return false;
}
Cursor PreparedStatement::execute()
{
locker l(d->mutex);
if (!d->cursorClosed) {
throw SQLiteException(-1, "Current Cursor must be closed before executing prepared statement");
}
if (d->excecuted) {
sqlite3_reset(d->stmt);
}
Cursor c(this);
return c;
}
bool WhatsappDatabase::hasTable(const std::string &tableName)
{
const char *query = "SELECT name FROM sqlite_master WHERE type='table' AND name = ?";
sqlite3_stmt *res;
if (sqlite3_prepare_v2(database.getHandle(), query, -1, &res, NULL) != SQLITE_OK)
{
throw SQLiteException("Could not query tables", database);
}
if (sqlite3_bind_text(res, 1, tableName.c_str(), -1, SQLITE_STATIC) != SQLITE_OK)
{
throw SQLiteException("Could not bind sql parameter", database);
}
bool hasTable = false;
if (sqlite3_step(res) == SQLITE_ROW)
{
hasTable = true;
}
sqlite3_finalize(res);
return hasTable;
}
void PreparedStatement::finalize()
{
locker l(d->mutex);
if (!d->prepared) {
// Don't try to finalize unprepared statement
return;
}
if ((d->db != nullptr) && (d->stmt != nullptr)) {
if (sqlite3_finalize(d->stmt) != SQLITE_OK) {
throw SQLiteException(sqlite3_errcode(d->db->getSqltite3db()), errorMsg());
}
d->prepared = false;
d->stmt == nullptr;
d->excecuted = false;
}
}
/**
* Error handling function. Throws a SQLiteException pointer.
* The error code and Message will be combined into the SQLiteException message.
* @param iReturnCode - The error code from SQLite.
* @param strMessage - The error message.
*/
void SQLiteDatabase::error(int iReturnCode, const std::string& strMessage)
{
std::stringstream ss;
std::string str;
ss << iReturnCode;
ss >> str;
std::string strMsg;
/* Note: the journal file is always created in the same directory as the database file.
* Changing the temp directory has no affect on the journal file.
*/
if ( (iReturnCode == SQLITE_CANTOPEN ) && !Fs::isWriteableDir(Fs::dirname( m_dbName) ) ) {
strMsg = "ERROR: sqlite3 update permission denied...journal file write failed because parent directory is not writeable: " + Fs::dirname(m_dbName);
}
else{
strMsg = "ERROR: SQLiteCode: " + str + ", Message: " + strMessage;
}
throw SQLiteException(strMsg);
}
void Utility::throwException(int rc, const std::string& addErrMsg)
{
switch (rc)
{
case SQLITE_OK:
break;
case SQLITE_ERROR:
throw InvalidSQLStatementException(std::string("SQL error or missing database"), addErrMsg);
case SQLITE_INTERNAL:
throw InternalDBErrorException(std::string("An internal logic error in SQLite"), addErrMsg);
case SQLITE_PERM:
throw DBAccessDeniedException(std::string("Access permission denied"), addErrMsg);
case SQLITE_ABORT:
throw ExecutionAbortedException(std::string("Callback routine requested an abort"), addErrMsg);
case SQLITE_BUSY:
throw DBLockedException(std::string("The database file is locked"), addErrMsg);
case SQLITE_LOCKED:
throw TableLockedException(std::string("A table in the database is locked"), addErrMsg);
case SQLITE_NOMEM:
throw NoMemoryException(std::string("A malloc() failed"), addErrMsg);
case SQLITE_READONLY:
throw ReadOnlyException(std::string("Attempt to write a readonly database"), addErrMsg);
case SQLITE_INTERRUPT:
throw InterruptException(std::string("Operation terminated by sqlite_interrupt()"), addErrMsg);
case SQLITE_IOERR:
throw IOErrorException(std::string("Some kind of disk I/O error occurred"), addErrMsg);
case SQLITE_CORRUPT:
throw CorruptImageException(std::string("The database disk image is malformed"), addErrMsg);
case SQLITE_NOTFOUND:
throw TableNotFoundException(std::string("Table or record not found"), addErrMsg);
case SQLITE_FULL:
throw DatabaseFullException(std::string("Insertion failed because database is full"), addErrMsg);
case SQLITE_CANTOPEN:
throw CantOpenDBFileException(std::string("Unable to open the database file"), addErrMsg);
case SQLITE_PROTOCOL:
throw LockProtocolException(std::string("Database lock protocol error"), addErrMsg);
case SQLITE_EMPTY:
throw InternalDBErrorException(std::string("(Internal Only) Database table is empty"), addErrMsg);
case SQLITE_SCHEMA:
throw SchemaDiffersException(std::string("The database schema changed"), addErrMsg);
case SQLITE_TOOBIG:
throw RowTooBigException(std::string("Too much data for one row of a table"), addErrMsg);
case SQLITE_CONSTRAINT:
throw ConstraintViolationException(std::string("Abort due to constraint violation"), addErrMsg);
case SQLITE_MISMATCH:
throw DataTypeMismatchException(std::string("Data type mismatch"), addErrMsg);
case SQLITE_MISUSE:
throw InvalidLibraryUseException(std::string("Library used incorrectly"), addErrMsg);
case SQLITE_NOLFS:
throw OSFeaturesMissingException(std::string("Uses OS features not supported on host"), addErrMsg);
case SQLITE_AUTH:
throw AuthorizationDeniedException(std::string("Authorization denied"), addErrMsg);
case SQLITE_FORMAT:
throw CorruptImageException(std::string("Auxiliary database format error"), addErrMsg);
case SQLITE_NOTADB:
throw CorruptImageException(std::string("File opened that is not a database file"), addErrMsg);
case SQLITE_RANGE:
throw InvalidSQLStatementException(std::string("Bind Parameter out of range (Access of invalid position 0? bind starts with 1!)"), addErrMsg);
case SQLITE_ROW:
break; // sqlite_step() has another row ready
case SQLITE_DONE:
break; // sqlite_step() has finished executing
default:
throw SQLiteException(std::string("Unknown error code: ") + Poco::NumberFormatter::format(rc), addErrMsg);
}
}
SQLiteStatement::SQLiteStatement(const SQLiteDatabase &db, string query): _db(db), _query(query), _stmt(nullptr)
{
if(sqlite3_prepare_v2(db, query.c_str(), query.length(), &this->_stmt, nullptr) != SQLiteResults::Ok)
throw SQLiteException(db);
}
void SQLiteStatement::bind(int idx, sqlite3_int64 value) const
{
if(sqlite3_bind_int64(this->_stmt, idx, value) != SQLiteResults::Ok)
throw SQLiteException(this->_db);
}
void SQLiteStatement::bind(int idx, const char *value, ValueManagement valmgmt) const
{
if(sqlite3_bind_text(this->_stmt, idx, value, -1, reinterpret_cast<sqlite3_destructor_type>(valmgmt)) != SQLiteResults::Ok)
throw SQLiteException(this->_db);
}
void SQLiteStatement::bind(int idx, const void* value, int len, SQLiteStatement::ValueManagement valmgmt) const
{
if(sqlite3_bind_blob(this->_stmt, idx, value, len, reinterpret_cast<sqlite3_destructor_type>(valmgmt)) != SQLiteResults::Ok)
throw SQLiteException(this->_db);
}
void Utility::throwException(int rc, const std::string& addErrMsg)
{
switch (rc)
{
case SQLITE_OK:
break;
case SQLITE_ERROR:
throw InvalidSQLStatementException(addErrMsg);
case SQLITE_INTERNAL:
throw InternalDBErrorException(addErrMsg);
case SQLITE_PERM:
throw DBAccessDeniedException(addErrMsg);
case SQLITE_ABORT:
throw ExecutionAbortedException(addErrMsg);
case SQLITE_BUSY:
throw DBLockedException(addErrMsg);
case SQLITE_LOCKED:
case SQLITE_LOCKED_SHAREDCACHE:
throw TableLockedException(addErrMsg);
case SQLITE_NOMEM:
throw NoMemoryException(addErrMsg);
case SQLITE_READONLY:
throw ReadOnlyException(addErrMsg);
case SQLITE_INTERRUPT:
throw InterruptException(addErrMsg);
case SQLITE_IOERR:
throw IOErrorException(addErrMsg);
case SQLITE_CORRUPT:
throw CorruptImageException(addErrMsg);
case SQLITE_NOTFOUND:
throw TableNotFoundException(addErrMsg);
case SQLITE_FULL:
throw DatabaseFullException(addErrMsg);
case SQLITE_CANTOPEN:
throw CantOpenDBFileException(addErrMsg);
case SQLITE_PROTOCOL:
throw LockProtocolException(addErrMsg);
case SQLITE_EMPTY:
throw InternalDBErrorException(addErrMsg);
case SQLITE_SCHEMA:
throw SchemaDiffersException(addErrMsg);
case SQLITE_TOOBIG:
throw RowTooBigException(addErrMsg);
case SQLITE_CONSTRAINT:
throw ConstraintViolationException(addErrMsg);
case SQLITE_MISMATCH:
throw DataTypeMismatchException(addErrMsg);
case SQLITE_MISUSE:
throw InvalidLibraryUseException(addErrMsg);
case SQLITE_NOLFS:
throw OSFeaturesMissingException(addErrMsg);
case SQLITE_AUTH:
throw AuthorizationDeniedException(addErrMsg);
case SQLITE_FORMAT:
throw CorruptImageException(addErrMsg);
case SQLITE_NOTADB:
throw CorruptImageException(addErrMsg);
case SQLITE_RANGE:
throw InvalidSQLStatementException(std::string("Bind parameter out of range (Access of invalid position 0? Bind starts with 1!)"), addErrMsg);
case SQLITE_ROW:
break; // sqlite_step() has another row ready
case SQLITE_DONE:
break; // sqlite_step() has finished executing
default:
throw SQLiteException(std::string("Unknown error code: ") + Poco::NumberFormatter::format(rc), addErrMsg);
}
}
void ErrorCode::check_()
{
if(err_ != SQLITE_OK)
BOOST_THROW_EXCEPTION(SQLiteException() << mstd::error_message(message_) << mstd::error_no(err_));
}
