summaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
-rw-r--r--compiledsql.cpp42
-rw-r--r--compiledsql.h17
-rw-r--r--storage.cpp68
-rw-r--r--storage.h1
-rw-r--r--tests/test-compiledsql.cpp44
-rw-r--r--whiteboard.cpp2
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);
diff --git a/storage.h b/storage.h
index 34144ee..fe8d060 100644
--- a/storage.h
+++ b/storage.h
@@ -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));
}
}