diff options
author | Roland Reichwein <mail@reichwein.it> | 2023-01-01 03:13:00 +0100 |
---|---|---|
committer | Roland Reichwein <mail@reichwein.it> | 2023-01-01 03:13:00 +0100 |
commit | d5861096a975274fcce72b45fe4ed38c96c61610 (patch) | |
tree | ee97aa2a5e4ba9f25df32569ed90d8fc9338be16 | |
parent | 9465fd744cc2117190bafc1a3e2da9f10ca29bf9 (diff) |
(Re-)Use precompiled SQL statements
-rw-r--r-- | storage.cpp | 121 | ||||
-rw-r--r-- | storage.h | 17 | ||||
-rw-r--r-- | tests/test-storage.cpp | 10 |
3 files changed, 95 insertions, 53 deletions
diff --git a/storage.cpp b/storage.cpp index 545ba04..392f06c 100644 --- a/storage.cpp +++ b/storage.cpp @@ -3,6 +3,7 @@ #include "config.h" #include <chrono> +#include <iostream> #include <SQLiteCpp/SQLiteCpp.h> @@ -12,16 +13,34 @@ Storage::Storage(const Config& config): m_db(config.getDataPath() + "/whiteboard.db3", SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE), m_maxage(config.getMaxage()) { - m_db.exec("CREATE TABLE IF NOT EXISTS documents (id VARCHAR(16) PRIMARY KEY, value BLOB, rev INTEGER, cursorpos INTEGER, timestamp BIGINT)"); + m_stmt_create = std::make_shared<SQLite::Statement>(m_db, "CREATE TABLE IF NOT EXISTS documents (id VARCHAR(16) PRIMARY KEY, value BLOB, rev INTEGER, cursorpos INTEGER, timestamp BIGINT)"); + m_stmt_create->exec(); + + m_stmt_getNumberOfDocuments = std::make_shared<SQLite::Statement>(m_db, "SELECT COUNT(*) FROM documents"); + m_stmt_cleanup = std::make_shared<SQLite::Statement>(m_db, "DELETE FROM documents WHERE timestamp + " + std::to_string(static_cast<int64_t>(m_maxage)) + " < unixepoch()"); + m_stmt_exists = std::make_shared<SQLite::Statement>(m_db, "SELECT id FROM documents WHERE id = ?"); + m_stmt_getDocument = std::make_shared<SQLite::Statement>(m_db, "SELECT value FROM documents WHERE id = ?"); + m_stmt_getRevision = std::make_shared<SQLite::Statement>(m_db, "SELECT rev FROM documents WHERE id = ?"); + m_stmt_getCursorPos = std::make_shared<SQLite::Statement>(m_db, "SELECT cursorpos FROM documents WHERE id = ?"); + m_stmt_getRow = std::make_shared<SQLite::Statement>(m_db, "SELECT value, rev, cursorpos FROM documents WHERE id = ?"); + m_stmt_setDocument = std::make_shared<SQLite::Statement>(m_db, "UPDATE documents SET value = ? WHERE id = ?"); + m_stmt_setDocument_new = std::make_shared<SQLite::Statement>(m_db, "INSERT INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, unixepoch())"); + m_stmt_setRevision = std::make_shared<SQLite::Statement>(m_db, "UPDATE documents SET rev = ? WHERE id = ?"); + m_stmt_setCursorPos = std::make_shared<SQLite::Statement>(m_db, "UPDATE documents SET cursorpos = ? WHERE id = ?"); + m_stmt_setRow = std::make_shared<SQLite::Statement>(m_db, "INSERT OR REPLACE INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, unixepoch())"); +} + +Storage::~Storage() +{ } uint64_t Storage::getNumberOfDocuments() { - SQLite::Statement query(m_db, "SELECT COUNT(*) FROM documents"); - if (!query.executeStep()) + m_stmt_getNumberOfDocuments->reset(); + if (!m_stmt_getNumberOfDocuments->executeStep()) throw std::runtime_error("Count not possible"); - return static_cast<int64_t>(query.getColumn(0)); + return static_cast<int64_t>(m_stmt_getNumberOfDocuments->getColumn(0)); } void Storage::cleanup() @@ -29,108 +48,106 @@ void Storage::cleanup() if (m_maxage == 0) return; - SQLite::Statement query(m_db, "DELETE FROM documents WHERE timestamp + ? < unixepoch()"); - query.bind(1, static_cast<int64_t>(m_maxage)); - - query.exec(); + m_stmt_cleanup->reset(); + m_stmt_cleanup->exec(); } bool Storage::exists(const std::string& id) { - SQLite::Statement query(m_db, "SELECT id FROM documents WHERE id = ?"); - query.bind(1, id); + m_stmt_exists->reset(); + m_stmt_exists->bind(1, id); - return query.executeStep(); + return m_stmt_exists->executeStep(); } std::string Storage::getDocument(const std::string& id) { - SQLite::Statement query(m_db, "SELECT value FROM documents WHERE id = ?"); - query.bind(1, id); + m_stmt_getDocument->reset(); + m_stmt_getDocument->bind(1, id); - if (!query.executeStep()) + if (!m_stmt_getDocument->executeStep()) throw std::runtime_error("id "s + id + " not found"s); - return query.getColumn(0); + return m_stmt_getDocument->getColumn(0); } int Storage::getRevision(const std::string& id) { - SQLite::Statement query(m_db, "SELECT rev FROM documents WHERE id = ?"); - query.bind(1, id); + m_stmt_getRevision->reset(); + m_stmt_getRevision->bind(1, id); - if (!query.executeStep()) + if (!m_stmt_getRevision->executeStep()) throw std::runtime_error("id "s + id + " not found"s); - return query.getColumn(0); + return m_stmt_getRevision->getColumn(0); } int Storage::getCursorPos(const std::string& id) { - SQLite::Statement query(m_db, "SELECT cursorpos FROM documents WHERE id = ?"); - query.bind(1, id); + m_stmt_getCursorPos->reset(); + m_stmt_getCursorPos->bind(1, id); - if (!query.executeStep()) + if (!m_stmt_getCursorPos->executeStep()) throw std::runtime_error("id "s + id + " not found"s); - return query.getColumn(0); + return m_stmt_getCursorPos->getColumn(0); } std::tuple<std::string, int, int> Storage::getRow(const std::string& id) { - SQLite::Statement query(m_db, "SELECT value, rev, cursorpos FROM documents WHERE id = ?"); - query.bind(1, id); + m_stmt_getRow->reset(); + m_stmt_getRow->bind(1, id); - if (!query.executeStep()) + if (!m_stmt_getRow->executeStep()) throw std::runtime_error("id "s + id + " not found"s); - return {query.getColumn(0), query.getColumn(1), query.getColumn(2)}; + return {m_stmt_getRow->getColumn(0), m_stmt_getRow->getColumn(1), m_stmt_getRow->getColumn(2)}; } void Storage::setDocument(const std::string& id, const std::string& document) { - SQLite::Statement query(m_db, "UPDATE documents SET value = ? WHERE id = ?"); - query.bind(1, document); - query.bind(2, id); - - if (!query.exec()) { - SQLite::Statement query(m_db, "INSERT INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, unixepoch())"); - query.bind(1, id); - query.bind(2, document); - query.bind(3, 0); - query.bind(4, 0); - query.exec(); + m_stmt_setDocument->reset(); + m_stmt_setDocument->bind(1, document); + m_stmt_setDocument->bind(2, id); + + if (!m_stmt_setDocument->exec()) { + m_stmt_setDocument_new->reset(); + m_stmt_setDocument_new->bind(1, id); + m_stmt_setDocument_new->bind(2, document); + m_stmt_setDocument_new->bind(3, 0); + m_stmt_setDocument_new->bind(4, 0); + m_stmt_setDocument_new->exec(); } } void Storage::setRevision(const std::string& id, int rev) { - SQLite::Statement query(m_db, "UPDATE documents SET rev = ? WHERE id = ?"); - query.bind(1, rev); - query.bind(2, id); + m_stmt_setRevision->reset(); + m_stmt_setRevision->bind(1, rev); + m_stmt_setRevision->bind(2, id); - if (!query.exec()) + if (!m_stmt_setRevision->exec()) throw std::runtime_error("Unable to insert row with id "s + id); } void Storage::setCursorPos(const std::string& id, int cursorPos) { - SQLite::Statement query(m_db, "UPDATE documents SET cursorpos = ? WHERE id = ?"); - query.bind(1, cursorPos); - query.bind(2, id); + m_stmt_setCursorPos->reset(); + m_stmt_setCursorPos->bind(1, cursorPos); + m_stmt_setCursorPos->bind(2, id); - if (!query.exec()) + if (!m_stmt_setCursorPos->exec()) throw std::runtime_error("Unable to insert row with id "s + id); } void Storage::setRow(const std::string& id, const std::string& document, int rev, int cursorPos) { - SQLite::Statement query(m_db, "INSERT OR REPLACE INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, unixepoch())"); - query.bind(1, id); - query.bind(2, document); - query.bind(3, rev); - query.bind(4, cursorPos); - if (!query.exec()) + m_stmt_setRow->reset(); + m_stmt_setRow->bind(1, id); + m_stmt_setRow->bind(2, document); + m_stmt_setRow->bind(3, rev); + m_stmt_setRow->bind(4, cursorPos); + if (!m_stmt_setRow->exec()) throw std::runtime_error("Unable to insert row with id "s + id); } @@ -1,5 +1,6 @@ #pragma once +#include <memory> #include <string> #include <tuple> @@ -11,6 +12,7 @@ class Storage { public: Storage(const Config& config); + ~Storage(); uint64_t getNumberOfDocuments(); bool exists(const std::string& id); @@ -30,5 +32,20 @@ public: private: SQLite::Database m_db; uint64_t m_maxage; + + // shared_ptr to work around initialization in constructor + std::shared_ptr<SQLite::Statement> m_stmt_create; + std::shared_ptr<SQLite::Statement> m_stmt_getNumberOfDocuments; + std::shared_ptr<SQLite::Statement> m_stmt_cleanup; + std::shared_ptr<SQLite::Statement> m_stmt_exists; + std::shared_ptr<SQLite::Statement> m_stmt_getDocument; + std::shared_ptr<SQLite::Statement> m_stmt_getRevision; + std::shared_ptr<SQLite::Statement> m_stmt_getCursorPos; + std::shared_ptr<SQLite::Statement> m_stmt_getRow; + std::shared_ptr<SQLite::Statement> m_stmt_setDocument; + std::shared_ptr<SQLite::Statement> m_stmt_setDocument_new; + std::shared_ptr<SQLite::Statement> m_stmt_setRevision; + std::shared_ptr<SQLite::Statement> m_stmt_setCursorPos; + std::shared_ptr<SQLite::Statement> m_stmt_setRow; }; diff --git a/tests/test-storage.cpp b/tests/test-storage.cpp index 67d7236..11d8a20 100644 --- a/tests/test-storage.cpp +++ b/tests/test-storage.cpp @@ -19,6 +19,13 @@ class StorageTest: public ::testing::Test { protected: StorageTest(){ + } + + ~StorageTest() override{ + } + + void SetUp() override + { File::setFile(testConfigFilename, R"CONFIG( <config> <datapath>.</datapath> @@ -31,7 +38,8 @@ protected: m_config = Config{testConfigFilename}; } - ~StorageTest(){ + void TearDown() override + { std::error_code ec; fs::remove(testDbFilename, ec); fs::remove(testConfigFilename, ec); |