diff options
| -rw-r--r-- | compiledsql.cpp | 42 | ||||
| -rw-r--r-- | compiledsql.h | 17 | ||||
| -rw-r--r-- | storage.cpp | 68 | ||||
| -rw-r--r-- | storage.h | 1 | ||||
| -rw-r--r-- | tests/test-compiledsql.cpp | 44 | ||||
| -rw-r--r-- | whiteboard.cpp | 2 |
6 files changed, 102 insertions, 72 deletions
diff --git a/compiledsql.cpp b/compiledsql.cpp index 818ab35..b8d6d70 100644 --- a/compiledsql.cpp +++ b/compiledsql.cpp @@ -1,31 +1,25 @@ #include "compiledsql.h" +#include <iostream> + #include <boost/algorithm/string/predicate.hpp> -CompiledSQL::CompiledSQL(SQLite::Database& db): - m_stmt{}, +CompiledSQL::CompiledSQL(SQLite::Database& db, const std::string& stmt): m_db{db}, + m_query{stmt}, + m_stmt{}, m_isSelect{} { -} - -void CompiledSQL::init(const std::string& stmt) -{ - if (m_stmt) { - m_stmt->reset(); - } else { - if ( + if ( #if __cplusplus >= 202002 - stmt.starts_with("SELECT ") + stmt.starts_with("SELECT ") #else - boost::algorithm::starts_with(stmt, "SELECT ") + boost::algorithm::starts_with(stmt, "SELECT ") #endif - ) { - m_isSelect = true; - } else { - m_isSelect = false; - } - m_stmt = std::make_shared<SQLite::Statement>(m_db, stmt); + ) { + m_isSelect = true; + } else { + m_isSelect = false; } } @@ -38,3 +32,15 @@ bool CompiledSQL::execute() } } +CompiledSQL::Guard::Guard(CompiledSQL& cs): m_cs{cs} +{ + if (!m_cs.m_stmt) { + m_cs.m_stmt = std::make_shared<SQLite::Statement>(m_cs.m_db, m_cs.m_query); + } +} + +CompiledSQL::Guard::~Guard() +{ + m_cs.m_stmt->reset(); +} + diff --git a/compiledsql.h b/compiledsql.h index 7510408..bb1062c 100644 --- a/compiledsql.h +++ b/compiledsql.h @@ -9,10 +9,8 @@ class CompiledSQL { public: - CompiledSQL(SQLite::Database& db); + CompiledSQL(SQLite::Database& db, const std::string& stmt); - void init(const std::string& stmt); - // index 1-based as in SQLite template<typename T> void bind(int index, T value) @@ -29,8 +27,19 @@ public: return m_stmt->getColumn(index); } + class Guard + { + public: + Guard(CompiledSQL& cs); + ~Guard(); + private: + CompiledSQL& m_cs; + }; + private: - std::shared_ptr<SQLite::Statement> m_stmt; SQLite::Database& m_db; + std::string m_query; + std::shared_ptr<SQLite::Statement> m_stmt; bool m_isSelect; // In SQLite, SELECT statements will be handled w/ executeStep(), others w/ exec() }; + diff --git a/storage.cpp b/storage.cpp index a7d5a36..f7f15b1 100644 --- a/storage.cpp +++ b/storage.cpp @@ -13,21 +13,23 @@ using namespace std::string_literals; Storage::Storage(const Config& config): m_db(config.getDataPath() + "/whiteboard.db3", SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE), m_maxage(config.getMaxage()), - m_stmt_create(m_db), - m_stmt_getNumberOfDocuments(m_db), - m_stmt_cleanup(m_db), - m_stmt_exists(m_db), - m_stmt_getDocument(m_db), - m_stmt_getRevision(m_db), - m_stmt_getCursorPos(m_db), - m_stmt_getRow(m_db), - m_stmt_setDocument(m_db), - m_stmt_setDocument_new(m_db), - m_stmt_setRevision(m_db), - m_stmt_setCursorPos(m_db), - m_stmt_setRow(m_db) + + m_stmt_create(m_db, "CREATE TABLE IF NOT EXISTS documents (id VARCHAR(16) PRIMARY KEY, value BLOB, rev INTEGER, cursorpos INTEGER, timestamp BIGINT)"), + m_stmt_getNumberOfDocuments(m_db, "SELECT COUNT(*) FROM documents"), + m_stmt_cleanup(m_db, "DELETE FROM documents WHERE timestamp + ? < ?"), + m_stmt_vacuum(m_db, "VACUUM"), + m_stmt_exists(m_db, "SELECT id FROM documents WHERE id = ?"), + m_stmt_getDocument(m_db, "SELECT value FROM documents WHERE id = ?"), + m_stmt_getRevision(m_db, "SELECT rev FROM documents WHERE id = ?"), + m_stmt_getCursorPos(m_db, "SELECT cursorpos FROM documents WHERE id = ?"), + m_stmt_getRow(m_db, "SELECT value, rev, cursorpos FROM documents WHERE id = ?"), + m_stmt_setDocument(m_db, "UPDATE documents SET value = ?, timestamp = ?, rev = rev + 1 WHERE id = ?"), + m_stmt_setDocument_new(m_db, "INSERT INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, ?)"), + m_stmt_setRevision(m_db, "UPDATE documents SET rev = ? WHERE id = ?"), + m_stmt_setCursorPos(m_db, "UPDATE documents SET cursorpos = ? WHERE id = ?"), + m_stmt_setRow(m_db, "INSERT OR REPLACE INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, ?)") { - m_stmt_create.init("CREATE TABLE IF NOT EXISTS documents (id VARCHAR(16) PRIMARY KEY, value BLOB, rev INTEGER, cursorpos INTEGER, timestamp BIGINT)"); + CompiledSQL::Guard g{m_stmt_create}; m_stmt_create.execute(); } @@ -37,7 +39,7 @@ Storage::~Storage() uint64_t Storage::getNumberOfDocuments() { - m_stmt_getNumberOfDocuments.init("SELECT COUNT(*) FROM documents"); + CompiledSQL::Guard g{m_stmt_getNumberOfDocuments}; if (!m_stmt_getNumberOfDocuments.execute()) throw std::runtime_error("Count not possible"); @@ -54,18 +56,20 @@ namespace { void Storage::cleanup() { - if (m_maxage == 0) - return; - - m_stmt_cleanup.init("DELETE FROM documents WHERE timestamp + ? < ?"); - m_stmt_cleanup.bind(1, static_cast<int64_t>(m_maxage)); - m_stmt_cleanup.bind(2, static_cast<int64_t>(unixepoch())); - m_stmt_cleanup.execute(); + if (m_maxage != 0) { + CompiledSQL::Guard g{m_stmt_cleanup}; + m_stmt_cleanup.bind(1, static_cast<int64_t>(m_maxage)); + m_stmt_cleanup.bind(2, static_cast<int64_t>(unixepoch())); + m_stmt_cleanup.execute(); + } + + CompiledSQL::Guard g{m_stmt_vacuum}; + m_stmt_vacuum.execute(); } bool Storage::exists(const std::string& id) { - m_stmt_exists.init("SELECT id FROM documents WHERE id = ?"); + CompiledSQL::Guard g{m_stmt_exists}; m_stmt_exists.bind(1, id); return m_stmt_exists.execute(); @@ -73,7 +77,7 @@ bool Storage::exists(const std::string& id) std::string Storage::getDocument(const std::string& id) { - m_stmt_getDocument.init("SELECT value FROM documents WHERE id = ?"); + CompiledSQL::Guard g{m_stmt_getDocument}; m_stmt_getDocument.bind(1, id); if (!m_stmt_getDocument.execute()) @@ -84,7 +88,7 @@ std::string Storage::getDocument(const std::string& id) int Storage::getRevision(const std::string& id) { - m_stmt_getRevision.init("SELECT rev FROM documents WHERE id = ?"); + CompiledSQL::Guard g{m_stmt_getRevision}; m_stmt_getRevision.bind(1, id); if (!m_stmt_getRevision.execute()) @@ -95,7 +99,7 @@ int Storage::getRevision(const std::string& id) int Storage::getCursorPos(const std::string& id) { - m_stmt_getCursorPos.init("SELECT cursorpos FROM documents WHERE id = ?"); + CompiledSQL::Guard g{m_stmt_getCursorPos}; m_stmt_getCursorPos.bind(1, id); if (!m_stmt_getCursorPos.execute()) @@ -106,7 +110,7 @@ int Storage::getCursorPos(const std::string& id) std::tuple<std::string, int, int> Storage::getRow(const std::string& id) { - m_stmt_getRow.init("SELECT value, rev, cursorpos FROM documents WHERE id = ?"); + CompiledSQL::Guard g{m_stmt_getRow}; m_stmt_getRow.bind(1, id); if (!m_stmt_getRow.execute()) @@ -117,13 +121,13 @@ std::tuple<std::string, int, int> Storage::getRow(const std::string& id) void Storage::setDocument(const std::string& id, const std::string& document) { - m_stmt_setDocument.init("UPDATE documents SET value = ?, timestamp = ?, rev = rev + 1 WHERE id = ?"); + CompiledSQL::Guard g{m_stmt_setDocument}; m_stmt_setDocument.bind(1, document); m_stmt_setDocument.bind(2, static_cast<int64_t>(unixepoch())); m_stmt_setDocument.bind(3, id); if (!m_stmt_setDocument.execute()) { - m_stmt_setDocument_new.init("INSERT INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, ?)"); + CompiledSQL::Guard g{m_stmt_setDocument_new}; m_stmt_setDocument_new.bind(1, id); m_stmt_setDocument_new.bind(2, document); m_stmt_setDocument_new.bind(3, 0); @@ -135,7 +139,7 @@ void Storage::setDocument(const std::string& id, const std::string& document) void Storage::setRevision(const std::string& id, int rev) { - m_stmt_setRevision.init("UPDATE documents SET rev = ? WHERE id = ?"); + CompiledSQL::Guard g{m_stmt_setRevision}; m_stmt_setRevision.bind(1, rev); m_stmt_setRevision.bind(2, id); @@ -145,7 +149,7 @@ void Storage::setRevision(const std::string& id, int rev) void Storage::setCursorPos(const std::string& id, int cursorPos) { - m_stmt_setCursorPos.init("UPDATE documents SET cursorpos = ? WHERE id = ?"); + CompiledSQL::Guard g{m_stmt_setCursorPos}; m_stmt_setCursorPos.bind(1, cursorPos); m_stmt_setCursorPos.bind(2, id); @@ -155,7 +159,7 @@ void Storage::setCursorPos(const std::string& id, int cursorPos) void Storage::setRow(const std::string& id, const std::string& document, int rev, int cursorPos) { - m_stmt_setRow.init("INSERT OR REPLACE INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, ?)"); + CompiledSQL::Guard g{m_stmt_setRow}; m_stmt_setRow.bind(1, id); m_stmt_setRow.bind(2, document); m_stmt_setRow.bind(3, rev); @@ -41,6 +41,7 @@ private: CompiledSQL m_stmt_create; CompiledSQL m_stmt_getNumberOfDocuments; CompiledSQL m_stmt_cleanup; + CompiledSQL m_stmt_vacuum; CompiledSQL m_stmt_exists; CompiledSQL m_stmt_getDocument; CompiledSQL m_stmt_getRevision; diff --git a/tests/test-compiledsql.cpp b/tests/test-compiledsql.cpp index 00a8221..d14220c 100644 --- a/tests/test-compiledsql.cpp +++ b/tests/test-compiledsql.cpp @@ -44,23 +44,33 @@ TEST_F(CompiledSQLTest, create) { SQLite::Database db{testDbFilename, SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE}; - CompiledSQL stmt1{db}; - stmt1.init("CREATE TABLE documents (id VARCHAR(16) PRIMARY KEY)"); - stmt1.execute(); - - CompiledSQL stmt2{db}; - stmt2.init("INSERT INTO documents (id) values (?)"); - stmt2.bind(1, "abc"); - ASSERT_TRUE(stmt2.execute()); - - CompiledSQL stmt3{db}; - stmt3.init("SELECT id FROM documents WHERE id = ?"); - stmt3.bind(1, "abc"); - ASSERT_TRUE(stmt3.execute()); - EXPECT_EQ(stmt3.getColumn<std::string>(0), "abc"); - stmt3.init("SELECT id FROM documents WHERE id = ?"); - stmt3.bind(1, "def"); - ASSERT_FALSE(stmt3.execute()); + { + CompiledSQL stmt1{db, "CREATE TABLE documents (id VARCHAR(16) PRIMARY KEY)"}; + CompiledSQL::Guard g{stmt1}; + stmt1.execute(); + } + + { + CompiledSQL stmt2{db, "INSERT INTO documents (id) values (?)"}; + CompiledSQL::Guard g{stmt2}; + stmt2.bind(1, "abc"); + ASSERT_TRUE(stmt2.execute()); + } + + { + CompiledSQL stmt3{db, "SELECT id FROM documents WHERE id = ?"}; + CompiledSQL::Guard g{stmt3}; + stmt3.bind(1, "abc"); + ASSERT_TRUE(stmt3.execute()); + EXPECT_EQ(stmt3.getColumn<std::string>(0), "abc"); + } + + { + CompiledSQL stmt4{db, "SELECT id FROM documents WHERE id = ?"}; + CompiledSQL::Guard g{stmt4}; + stmt4.bind(1, "def"); + ASSERT_FALSE(stmt4.execute()); + } EXPECT_TRUE(fs::exists(testDbFilename)); } diff --git a/whiteboard.cpp b/whiteboard.cpp index 35ae30b..a49ef73 100644 --- a/whiteboard.cpp +++ b/whiteboard.cpp @@ -79,7 +79,7 @@ void Whiteboard::storage_cleanup() throw std::runtime_error("Storage not initialized"); m_storage->cleanup(); } - std::this_thread::sleep_for(std::chrono::minutes(10)); + std::this_thread::sleep_for(std::chrono::hours(24)); } } |