std::vector<Note> Sqlite3Database::loadNotesForTag(const bigint_t tag_id) { clearStatement(); stmt_cache_ << "SELECT notes.id, notes.title, notes.content, " "notes.notebook, notes.last_change, " << "notes.reminder FROM notes join tags_nm ON " "(notes.id=tags_nm.note_id)" << " WHERE (tag_id = " << std::to_string(tag_id) << ")"; auto result = prepareStatement(stmt_cache_.str()); int state = SQLITE_OK; std::vector<Note> result_vec; do { state = executeStep(result); if (isError(state)) throw DatabaseException("listing notes failed, invalid result"); if (state == SQLITE_DONE) break; result_vec.emplace_back(getInt(result, 0), getString(result, 1), getString(result, 2), getInt(result, 3), getTimestamp(result, 4), getTimestamp(result, 5)); } while (state != SQLITE_DONE); return result_vec; }
std::vector<Note> Sqlite3Database::searchNotes(const std::string &term) { clearStatement(); stmt_cache_ << "SELECT notes.id, notes.title, notes.content, notes.notebook," "notes.last_change, notes.reminder FROM notes left join tags_nm ON " "(notes.id=tags_nm.note_id)" " left join tags ON (tags_nm.tag_id=tags.id) WHERE (" " notes.title like '%" << term << "%' or notes.content like '%" << term << "%' or tags.title like '%" << term << "%')"; auto result = prepareStatement(stmt_cache_.str()); int state = SQLITE_OK; std::vector<Note> result_vec; do { state = executeStep(result); if (isError(state)) throw DatabaseException("Searching for notes failed with ec=" + std::to_string(state)); if (state == SQLITE_DONE) break; result_vec.emplace_back(getInt(result, 0), getString(result, 1), getString(result, 2), getInt(result, 3), getTimestamp(result, 4), getTimestamp(result, 5)); } while (state != SQLITE_DONE); return result_vec; }
void LocalStorageDatabase::updateDatabaseWithChangedItems(const HashMap<String, String>& changedItems) { if (!m_database.isOpen()) openDatabase(CreateIfNonExistent); if (!m_database.isOpen()) return; if (m_shouldClearItems) { m_shouldClearItems = false; SQLiteStatement clearStatement(m_database, "DELETE FROM ItemTable"); if (clearStatement.prepare() != SQLITE_OK) { LOG_ERROR("Failed to prepare clear statement - cannot write to local storage database"); return; } int result = clearStatement.step(); if (result != SQLITE_DONE) { LOG_ERROR("Failed to clear all items in the local storage database - %i", result); return; } } SQLiteStatement insertStatement(m_database, "INSERT INTO ItemTable VALUES (?, ?)"); if (insertStatement.prepare() != SQLITE_OK) { LOG_ERROR("Failed to prepare insert statement - cannot write to local storage database"); return; } SQLiteStatement deleteStatement(m_database, "DELETE FROM ItemTable WHERE key=?"); if (deleteStatement.prepare() != SQLITE_OK) { LOG_ERROR("Failed to prepare delete statement - cannot write to local storage database"); return; } SQLiteTransaction transaction(m_database); transaction.begin(); for (auto it = changedItems.begin(), end = changedItems.end(); it != end; ++it) { // A null value means that the key/value pair should be deleted. SQLiteStatement& statement = it->value.isNull() ? deleteStatement : insertStatement; statement.bindText(1, it->key); // If we're inserting a key/value pair, bind the value as well. if (!it->value.isNull()) statement.bindBlob(2, it->value); int result = statement.step(); if (result != SQLITE_DONE) { LOG_ERROR("Failed to update item in the local storage database - %i", result); break; } statement.reset(); } transaction.commit(); }
void Sqlite3Database::deleteTag(const bigint_t tag_id) { clearStatement(); stmt_cache_ << "DELETE FROM tags WHERE id=" << std::to_string(tag_id); auto result = prepareStatement(stmt_cache_.str()); if (isError(executeStep(result))) throw DatabaseException("deleting tag " + std::to_string(tag_id) + " failed"); }
bigint_t Sqlite3Database::newTag(const std::string &title) { clearStatement(); stmt_cache_ << "INSERT INTO tags(title) VALUES('" << title << "')"; auto result = prepareStatement(stmt_cache_.str()); if (isError(executeStep(result))) throw DatabaseException("inserting tag " + title + " failed"); return getLastInsertId(); }
void Sqlite3Database::addTag(const bigint_t note_id, const bigint_t tag_id) { clearStatement(); stmt_cache_ << "INSERT INTO tags_nm VALUES(" << std::to_string(tag_id) << ", " << std::to_string(note_id) << ")"; auto result = prepareStatement(stmt_cache_.str()); if (isError(executeStep(result))) throw DatabaseException("adding tag " + std::to_string(tag_id) + " to " + std::to_string(note_id) + " failed"); }
void Sqlite3Database::deleteNotebook(const bigint_t notebook_id) { clearStatement(); stmt_cache_ << "DELETE FROM notebooks WHERE id=" << std::to_string(notebook_id); auto result = prepareStatement(stmt_cache_.str()); if (isError(executeStep(result))) throw DatabaseException("deleting notebook failed: " + std::to_string(notebook_id)); }
void Sqlite3Database::renameNotebook(const bigint_t notebook_id, const std::string &new_title) { clearStatement(); stmt_cache_ << "UPDATE notebooks SET title='" << new_title << "' WHERE id=" << notebook_id; auto result = prepareStatement(stmt_cache_.str()); if (isError(executeStep(result))) throw DatabaseException("updating notebook title for notebook " + new_title); }
Notebook Sqlite3Database::loadNotebook(const bigint_t notebook_id) { clearStatement(); stmt_cache_ << "SELECT * FROM notebooks WHERE id=" << std::to_string(notebook_id); auto result = prepareStatement(stmt_cache_.str()); if (isError(executeStep(result))) throw DatabaseException("loading notebook id " + std::to_string(notebook_id) + " failed, invalid result"); Notebook nb(getInt(result, 0), getString(result, 1)); return nb; }
Note Sqlite3Database::loadNote(const bigint_t note_id) { clearStatement(); stmt_cache_ << "SELECT title,content,notebook,last_change,reminder" " FROM notes WHERE (id=" << std::to_string(note_id) << ")"; auto result = prepareStatement(stmt_cache_.str()); if (isError(executeStep(result))) { throw DatabaseException("loading note id " + std::to_string(note_id) + " failed, invalid result"); } return Note(note_id, getString(result, 0), // title getString(result, 1), // content getInt(result, 2), // notebook getTimestamp(result, 3), // last change getTimestamp(result, 4) // reminder ); }
std::vector<Tag> db::Sqlite3Database::listTags() { clearStatement(); stmt_cache_ << "SELECT * FROM tags"; auto result = prepareStatement(stmt_cache_.str()); int state = SQLITE_OK; std::vector<Tag> result_vec; do { state = executeStep(result); if (isError(state)) throw DatabaseException("listing tags failed, invalid result"); if (state == SQLITE_DONE) break; result_vec.emplace_back(getInt(result, 0), getString(result, 1)); } while (!isError(state) && state != SQLITE_DONE); return result_vec; }
void Sqlite3Database::updateNote(const Note ¬e) { auto date_str = pt::to_iso_string(note.reminder()); clearStatement(); stmt_cache_ << "UPDATE notes SET title=?1,content=?2," << "notebook=?3,last_change=datetime('now','localtime')," << "reminder=?4 where (id=?5)"; auto result = prepareStatement(stmt_cache_.str()); bindString(result, 1, note.title()); bindString(result, 2, note.content()); bindInt(result, 3, note.notebook()); bindString(result, 4, date_str); bindInt(result, 5, note.id()); if (isError(executeStep(result))) throw DatabaseException("updating note " + note.title() + " failed"); }
void Sqlite3Database::removeTag(const bigint_t note_id, const bigint_t tag_id) { clearStatement(); if (note_id < 0) { stmt_cache_ << "DELETE FROM tags_nm WHERE (tag_id=" << std::to_string(tag_id) << ")"; } else if (tag_id < 0) { stmt_cache_ << "DELETE FROM tags_nm WHERE (note_id=" << std::to_string(note_id) << ")"; } else { stmt_cache_ << "DELETE FROM tags_nm WHERE (note_id=" << std::to_string(note_id) << " and tag_id=" << std::to_string(tag_id) << ")"; } auto result = prepareStatement(stmt_cache_.str()); if (isError(executeStep(result))) throw DatabaseException("removing tag " + std::to_string(tag_id) + " failed"); }
// DEMO : sqlite3 example with prepared statement parameters void Sqlite3Database::newNote(Note ¬e) { auto date_str = pt::to_iso_string(note.reminder()); clearStatement(); stmt_cache_ << "INSERT INTO notes(title,content,notebook,reminder) VALUES(" << "?1, ?2, ?3, ?4" << ")"; auto result = prepareStatement(stmt_cache_.str()); bindString(result, 1, note.title()); bindString(result, 2, note.content()); bindInt(result, 3, note.notebook()); bindString(result, 4, date_str); if (isError(executeStep(result))) throw DatabaseException("inserting note " + note.title() + " failed"); // get the generated ID note.id(getLastInsertId()); }
std::vector<Note> Sqlite3Database::loadNotesFromNotebook(const bigint_t notebook_id) { clearStatement(); stmt_cache_ << "SELECT * FROM notes where (notebook=" << std::to_string(notebook_id) << ")"; auto result = prepareStatement(stmt_cache_.str()); int state = SQLITE_OK; std::vector<Note> result_vec; do { state = executeStep(result); if (isError(state)) throw DatabaseException("listing notes failed, invalid result"); if (state == SQLITE_DONE) break; result_vec.emplace_back(getInt(result, 0), getString(result, 1), getString(result, 2), getInt(result, 3), getTimestamp(result, 4), getTimestamp(result, 5)); } while (!isError(state) && state != SQLITE_DONE); return result_vec; }