From d5861096a975274fcce72b45fe4ed38c96c61610 Mon Sep 17 00:00:00 2001 From: Roland Reichwein Date: Sun, 1 Jan 2023 03:13:00 +0100 Subject: (Re-)Use precompiled SQL statements --- storage.cpp | 121 ++++++++++++++++++++++++++++--------------------- storage.h | 17 +++++++ 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 +#include #include @@ -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(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(m_db, "SELECT COUNT(*) FROM documents"); + m_stmt_cleanup = std::make_shared(m_db, "DELETE FROM documents WHERE timestamp + " + std::to_string(static_cast(m_maxage)) + " < unixepoch()"); + m_stmt_exists = std::make_shared(m_db, "SELECT id FROM documents WHERE id = ?"); + m_stmt_getDocument = std::make_shared(m_db, "SELECT value FROM documents WHERE id = ?"); + m_stmt_getRevision = std::make_shared(m_db, "SELECT rev FROM documents WHERE id = ?"); + m_stmt_getCursorPos = std::make_shared(m_db, "SELECT cursorpos FROM documents WHERE id = ?"); + m_stmt_getRow = std::make_shared(m_db, "SELECT value, rev, cursorpos FROM documents WHERE id = ?"); + m_stmt_setDocument = std::make_shared(m_db, "UPDATE documents SET value = ? WHERE id = ?"); + m_stmt_setDocument_new = std::make_shared(m_db, "INSERT INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, unixepoch())"); + m_stmt_setRevision = std::make_shared(m_db, "UPDATE documents SET rev = ? WHERE id = ?"); + m_stmt_setCursorPos = std::make_shared(m_db, "UPDATE documents SET cursorpos = ? WHERE id = ?"); + m_stmt_setRow = std::make_shared(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(query.getColumn(0)); + return static_cast(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(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 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); } diff --git a/storage.h b/storage.h index dc4e216..24f2961 100644 --- a/storage.h +++ b/storage.h @@ -1,5 +1,6 @@ #pragma once +#include #include #include @@ -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 m_stmt_create; + std::shared_ptr m_stmt_getNumberOfDocuments; + std::shared_ptr m_stmt_cleanup; + std::shared_ptr m_stmt_exists; + std::shared_ptr m_stmt_getDocument; + std::shared_ptr m_stmt_getRevision; + std::shared_ptr m_stmt_getCursorPos; + std::shared_ptr m_stmt_getRow; + std::shared_ptr m_stmt_setDocument; + std::shared_ptr m_stmt_setDocument_new; + std::shared_ptr m_stmt_setRevision; + std::shared_ptr m_stmt_setCursorPos; + std::shared_ptr 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( . @@ -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); -- cgit v1.2.3