int PinnedMediaDBManager::getSchemaVersion(u64& version)
{
int rv = 0;
int rc;
int sql_type;
static const char* SQL_GET_SCHEMA_VERSION_FROM_DB_ATTR =
"SELECT id, value "
"FROM db_attr "
"WHERE id="XSTR(KEY_SCHEMA_VERSION);
sqlite3_stmt *stmt = NULL;
CHECK_DB_HANDLE(rv, m_db, end);
rc = sqlite3_prepare_v2(m_db, SQL_GET_SCHEMA_VERSION_FROM_DB_ATTR, -1, &stmt, NULL);
CHECK_PREPARE(rv, rc, SQL_GET_SCHEMA_VERSION_FROM_DB_ATTR, m_db, end);
rc = sqlite3_step(stmt);
CHECK_STEP(rv, rc, m_db, end);
CHECK_ROW_EXIST(rv, rc, m_db, end);
sql_type = sqlite3_column_type(stmt, 1);
if (sql_type == SQLITE_INTEGER) {
version = sqlite3_column_int64(stmt, 1);
} else {
LOG_WARN("Bad column type index: %d", 1);
rv = -1;
goto end;
}
end:
FINALIZE_STMT(rv, rc, m_db, stmt);
return rv;
}
int PinnedMediaDBManager::insertPinnedMediaItem(const std::string& path, const std::string& object_id, u64 device_id)
{
int rv = 0;
int rc;
static const char* SQL_UPDATE_PINNED_MEDIA_ITEM =
"INSERT OR REPLACE INTO pin_item (path,"
"object_id,"
"device_id) "
"VALUES (?,?,?)";
sqlite3_stmt *stmt = NULL;
CHECK_DB_HANDLE(rv, m_db, end);
rc = sqlite3_prepare_v2(m_db, SQL_UPDATE_PINNED_MEDIA_ITEM, -1, &stmt, NULL);
CHECK_PREPARE(rv, rc, SQL_UPDATE_PINNED_MEDIA_ITEM, m_db, end);
rc = sqlite3_bind_text(stmt, 1, path.c_str(), -1, SQLITE_STATIC);
CHECK_BIND(rv, rc, m_db, end);
rc = sqlite3_bind_text(stmt, 2, object_id.c_str(), -1, SQLITE_STATIC);
CHECK_BIND(rv, rc, m_db, end);
rc = sqlite3_bind_int64(stmt, 3, device_id);
CHECK_BIND(rv, rc, m_db, end);
rc = sqlite3_step(stmt);
CHECK_STEP(rv, rc, m_db, end);
end:
FINALIZE_STMT(rv, rc, m_db, stmt);
return rv;
}
bool loadBuddies(SkypePlugin *np, const std::string &db_path, std::string &user, std::map<std::string, std::string> &group_map) {
bool ret = false;
sqlite3 *db;
LOG4CXX_INFO(logger, "Opening database " << db_path);
if (sqlite3_open(db_path.c_str(), &db)) {
sqlite3_close(db);
LOG4CXX_ERROR(logger, "Can't open database");
}
else {
sqlite3_stmt *stmt;
// aliases, fullname,
PREP_STMT(stmt, "select skypename, displayname, mood_text from Contacts;");
if (stmt) {
BEGIN(stmt);
int ret2;
while((ret2 = sqlite3_step(stmt)) == SQLITE_ROW) {
const char *d;
d = (const char *) sqlite3_column_text(stmt, 0);
if (!d) {
continue;
}
ret = true;
std::string buddy = d;
d = (const char *) sqlite3_column_text(stmt, 1);
std::string alias = d ? d : buddy;
d = (const char *) sqlite3_column_text(stmt, 2);
std::string mood_text = d ? d : "";
std::vector<std::string> groups;
if (group_map.find(buddy) != group_map.end()) {
groups.push_back(group_map[buddy]);
}
np->handleBuddyChanged(user, buddy, alias, groups, pbnetwork::STATUS_NONE, mood_text);
}
if (ret2 != SQLITE_DONE) {
FINALIZE_STMT(stmt);
ret = false;
}
}
else {
LOG4CXX_ERROR(logger, "Can't create prepared statement");
LOG4CXX_ERROR(logger, (sqlite3_errmsg(db) == NULL ? "" : sqlite3_errmsg(db)));
}
sqlite3_close(db);
}
return ret;
}
SQLite3Backend::~SQLite3Backend(){
if (m_db) {
// Would be nice to use this:
//
// sqlite3_stmt *pStmt;
// while((pStmt = sqlite3_next_stmt(db, 0)) != 0 ) {
// sqlite3_finalize(pStmt);
// }
//
// But requires SQLite3 >= 3.6.0 beta
FINALIZE_STMT(m_setUser);
sqlite3_close(m_db);
}
}
void DB::loadNetworks()
{
int res;
sqlite3_stmt * stmt;
PREPARE_STMT(stmt, "network loading",
"SELECT * FROM \"network\" ORDER BY \"id\" ASC");
while ((res = sqlite3_step(stmt)) == SQLITE_ROW) {
Network net;
net.id = sqlite3_column_int(stmt, 0);
net.name = (const char *) sqlite3_column_text(stmt, 1);
networks.push_back(std::move(net));
}
FINALIZE_STMT(stmt, "network loading");
}
void DB::loadBuffers()
{
int res;
sqlite3_stmt * stmt;
PREPARE_STMT(stmt, "buffer loading",
"SELECT * FROM buffer ORDER BY id ASC");
while ((res = sqlite3_step(stmt)) == SQLITE_ROW) {
Buffer buf;
buf.id = sqlite3_column_int(stmt, 0);
buf.networkid = sqlite3_column_int(stmt, 1);
buf.name = (const char *) sqlite3_column_text(stmt, 2);
buffers.push_back(std::move(buf));
}
FINALIZE_STMT(stmt, "buffer loading");
}
void DB::loadSenders()
{
int res;
sqlite3_stmt * stmt;
PREPARE_STMT(stmt, "sender loading",
"SELECT * FROM \"sender\" ORDER BY \"id\" ASC");
while ((res = sqlite3_step(stmt)) == SQLITE_ROW) {
Sender snd;
snd.id = sqlite3_column_int(stmt, 0);
snd.nick = (const char *) sqlite3_column_text(stmt, 1);
snd.user = (const char *) sqlite3_column_text(stmt, 2);
snd.host = (const char *) sqlite3_column_text(stmt, 3);
senders.push_back(std::move(snd));
}
FINALIZE_STMT(stmt, "sender loading");
}
int PinnedMediaDBManager::commitTransaction()
{
int rv = 0;
int rc;
static const char* SQL_COMMIT = "COMMIT";
sqlite3_stmt *stmt = NULL;
CHECK_DB_HANDLE(rv, m_db, end);
rc = sqlite3_prepare_v2(m_db, SQL_COMMIT, -1, &stmt, NULL);
CHECK_PREPARE(rv, rc, SQL_COMMIT, m_db, end);
rc = sqlite3_step(stmt);
CHECK_STEP(rv, rc, m_db, end);
end:
FINALIZE_STMT(rv, rc, m_db, stmt);
return rv;
}
int PinnedMediaDBManager::beginTransaction()
{
int rv = 0;
int rc;
static const char* SQL_BEGIN = "BEGIN IMMEDIATE";
sqlite3_stmt *stmt = NULL;
CHECK_DB_HANDLE(rv, m_db, end);
rc = sqlite3_prepare_v2(m_db, SQL_BEGIN, -1, &stmt, NULL);
CHECK_PREPARE(rv, rc, SQL_BEGIN, m_db, end);
rc = sqlite3_step(stmt);
CHECK_STEP(rv, rc, m_db, end);
end:
FINALIZE_STMT(rv, rc, m_db, stmt);
return rv;
}
int PinnedMediaDBManager::deleteAllPinnedMediaItems()
{
int rv = 0;
int rc;
static const char* SQL_DELETE_ALL_PINNED_MEDIA_ITEMS =
"DELETE FROM pin_item";
sqlite3_stmt *stmt = NULL;
CHECK_DB_HANDLE(rv, m_db, end);
rc = sqlite3_prepare_v2(m_db, SQL_DELETE_ALL_PINNED_MEDIA_ITEMS, -1, &stmt, NULL);
CHECK_PREPARE(rv, rc, SQL_DELETE_ALL_PINNED_MEDIA_ITEMS, m_db, end);
rc = sqlite3_step(stmt);
CHECK_STEP(rv, rc, m_db, end);
end:
FINALIZE_STMT(rv, rc, m_db, stmt);
return rv;
}
bool getAvatar(const std::string &db_path, const std::string &name, std::string &photo) {
bool ret = false;
sqlite3 *db;
LOG4CXX_INFO(logger, "Opening database " << db_path);
if (sqlite3_open(db_path.c_str(), &db)) {
sqlite3_close(db);
LOG4CXX_ERROR(logger, "Can't open database");
}
else {
sqlite3_stmt *stmt;
PREP_STMT(stmt, "SELECT avatar_image FROM Contacts WHERE skypename=?");
if (stmt) {
BEGIN(stmt);
BIND_STR(stmt, name);
if(sqlite3_step(stmt) == SQLITE_ROW) {
int size = sqlite3_column_bytes(stmt, 0);
if (size > 0) {
const void *data = sqlite3_column_blob(stmt, 0);
photo = std::string((const char *)data + 1, size - 1);
ret = true;
} else {
ret = false;
}
}
else {
LOG4CXX_ERROR(logger, (sqlite3_errmsg(db) == NULL ? "" : sqlite3_errmsg(db)));
}
int ret;
while((ret = sqlite3_step(stmt)) == SQLITE_ROW) {
}
FINALIZE_STMT(stmt);
}
else {
LOG4CXX_ERROR(logger, "Can't create prepared statement");
LOG4CXX_ERROR(logger, (sqlite3_errmsg(db) == NULL ? "" : sqlite3_errmsg(db)));
}
sqlite3_close(db);
}
return ret;
}
DB::~DB()
{
int res;
try {
FINALIZE_STMT(stmt_add_message, "message insertion");
FINALIZE_STMT(stmt_add_buffer, "buffer insertion");
FINALIZE_STMT(stmt_add_network, "network insertion");
FINALIZE_STMT(stmt_add_sender, "sender insertion");
FINALIZE_STMT(stmt_begin, "begin");
FINALIZE_STMT(stmt_commit, "commit");
SQLOK(close(dbh), "closing database");
} catch (std::exception &e) {
std::cerr << "Failed to close database: " << e.what() << std::endl;
}
}
int PinnedMediaDBManager::deleteAllRelatedPinnedMediaItems(u64 device_id)
{
int rv = 0;
int rc;
static const char* SQL_DELETE_PINNED_MEDIA_ITEM_BY_DEVICE_ID =
"DELETE FROM pin_item WHERE device_id=?";
sqlite3_stmt *stmt = NULL;
CHECK_DB_HANDLE(rv, m_db, end);
rc = sqlite3_prepare_v2(m_db, SQL_DELETE_PINNED_MEDIA_ITEM_BY_DEVICE_ID, -1, &stmt, NULL);
CHECK_PREPARE(rv, rc, SQL_DELETE_PINNED_MEDIA_ITEM_BY_DEVICE_ID, m_db, end);
rc = sqlite3_bind_int64(stmt, 1, device_id);
CHECK_BIND(rv, rc, m_db, end);
rc = sqlite3_step(stmt);
CHECK_STEP(rv, rc, m_db, end);
end:
FINALIZE_STMT(rv, rc, m_db, stmt);
return rv;
}
int PinnedMediaDBManager::deletePinnedMediaItem(const std::string& path)
{
int rv = 0;
int rc;
static const char* SQL_DELETE_PINNED_MEDIA_ITEM_BY_PATH =
"DELETE FROM pin_item WHERE path=?";
sqlite3_stmt *stmt = NULL;
CHECK_DB_HANDLE(rv, m_db, end);
rc = sqlite3_prepare_v2(m_db, SQL_DELETE_PINNED_MEDIA_ITEM_BY_PATH, -1, &stmt, NULL);
CHECK_PREPARE(rv, rc, SQL_DELETE_PINNED_MEDIA_ITEM_BY_PATH, m_db, end);
rc = sqlite3_bind_text(stmt, 1, path.c_str(), -1, SQLITE_STATIC);
CHECK_BIND(rv, rc, m_db, end);
rc = sqlite3_step(stmt);
CHECK_STEP(rv, rc, m_db, end);
end:
FINALIZE_STMT(rv, rc, m_db, stmt);
return rv;
}
int PinnedMediaDBManager::selectPinnedMediaItem(const std::string& object_id, u64 device_id, PinnedMediaItem& output_pinned_media_item)
{
int rv = 0;
int rc;
static const char* SQL_SELECT_PINNED_MEDIA_ITEM_BY_OBJECT_ID_AND_DEVICE_ID =
"SELECT path, object_id, device_id "
"FROM pin_item "
"WHERE object_id=? AND device_id=?";
sqlite3_stmt *stmt = NULL;
CHECK_DB_HANDLE(rv, m_db, end);
rc = sqlite3_prepare_v2(m_db, SQL_SELECT_PINNED_MEDIA_ITEM_BY_OBJECT_ID_AND_DEVICE_ID, -1, &stmt, NULL);
CHECK_PREPARE(rv, rc, SQL_SELECT_PINNED_MEDIA_ITEM_BY_OBJECT_ID_AND_DEVICE_ID, m_db, end);
rc = sqlite3_bind_text(stmt, 1, object_id.c_str(), -1, SQLITE_STATIC);
CHECK_BIND(rv, rc, m_db, end);
rc = sqlite3_bind_int64(stmt, 2, device_id);
CHECK_BIND(rv, rc, m_db, end);
{
std::string path;
std::string object_id;
u64 device_id;
int sql_type;
rc = sqlite3_step(stmt);
CHECK_STEP(rv, rc, m_db, end);
CHECK_ROW_EXIST(rv, rc, m_db, end);
sql_type = sqlite3_column_type(stmt, 0);
if (sql_type == SQLITE_TEXT) {
path = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
} else {
LOG_WARN("Bad column type index: %d", 0);
rv = -1;
goto end;
}
sql_type = sqlite3_column_type(stmt, 1);
if (sql_type == SQLITE_TEXT) {
object_id = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
} else {
LOG_WARN("Bad column type index: %d", 1);
rv = -1;
goto end;
}
sql_type = sqlite3_column_type(stmt, 2);
if (sql_type == SQLITE_INTEGER) {
device_id = sqlite3_column_int64(stmt, 2);
} else {
LOG_WARN("Bad column type index: %d", 2);
rv = -1;
goto end;
}
output_pinned_media_item.path = path;
output_pinned_media_item.object_id = object_id;
output_pinned_media_item.device_id = device_id;
}
end:
FINALIZE_STMT(rv, rc, m_db, stmt);
return rv;
}
SQLite3Backend::~SQLite3Backend(){
if (m_db) {
// Would be nice to use this:
//
// sqlite3_stmt *pStmt;
// while((pStmt = sqlite3_next_stmt(db, 0)) != 0 ) {
// sqlite3_finalize(pStmt);
// }
//
// But requires SQLite3 >= 3.6.0 beta
FINALIZE_STMT(m_setUser);
FINALIZE_STMT(m_getUser);
FINALIZE_STMT(m_removeUser);
FINALIZE_STMT(m_removeUserBuddies);
FINALIZE_STMT(m_removeUserSettings);
FINALIZE_STMT(m_removeUserBuddiesSettings);
FINALIZE_STMT(m_removeBuddy);
FINALIZE_STMT(m_removeBuddySettings);
FINALIZE_STMT(m_addBuddy);
FINALIZE_STMT(m_updateBuddy);
FINALIZE_STMT(m_getBuddies);
FINALIZE_STMT(m_getBuddiesSettings);
FINALIZE_STMT(m_getUserSetting);
FINALIZE_STMT(m_setUserSetting);
FINALIZE_STMT(m_updateUserSetting);
FINALIZE_STMT(m_updateBuddySetting);
FINALIZE_STMT(m_getBuddySetting);
FINALIZE_STMT(m_setUserOnline);
FINALIZE_STMT(m_getOnlineUsers);
FINALIZE_STMT(m_getAllUsers);
sqlite3_close(m_db);
}
}