summaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
authorRoland Reichwein <mail@reichwein.it>2023-01-01 03:13:00 +0100
committerRoland Reichwein <mail@reichwein.it>2023-01-01 03:13:00 +0100
commitd5861096a975274fcce72b45fe4ed38c96c61610 (patch)
treeee97aa2a5e4ba9f25df32569ed90d8fc9338be16
parent9465fd744cc2117190bafc1a3e2da9f10ca29bf9 (diff)
(Re-)Use precompiled SQL statements
-rw-r--r--storage.cpp121
-rw-r--r--storage.h17
-rw-r--r--tests/test-storage.cpp10
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);
}
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 <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);