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;
}
