From cbf1ba38794ab6a323441dcc3b0e5e942f7ab386 Mon Sep 17 00:00:00 2001
From: Roland Reichwein <mail@reichwein.it>
Date: Sun, 1 Jan 2023 14:53:05 +0100
Subject: Added CompiledSQL class, Test coverage

---
 storage.cpp | 130 ++++++++++++++++++++++++++++++------------------------------
 1 file changed, 65 insertions(+), 65 deletions(-)

(limited to 'storage.cpp')

diff --git a/storage.cpp b/storage.cpp
index 392f06c..ffdb2f4 100644
--- a/storage.cpp
+++ b/storage.cpp
@@ -11,23 +11,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_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 = 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())");
+ m_stmt_create.init("CREATE TABLE IF NOT EXISTS documents (id VARCHAR(16) PRIMARY KEY, value BLOB, rev INTEGER, cursorpos INTEGER, timestamp BIGINT)");
+ m_stmt_create.execute();
 }
 
 Storage::~Storage()
@@ -36,11 +36,11 @@ Storage::~Storage()
 
 uint64_t Storage::getNumberOfDocuments()
 {
- m_stmt_getNumberOfDocuments->reset();
- if (!m_stmt_getNumberOfDocuments->executeStep())
+ m_stmt_getNumberOfDocuments.init("SELECT COUNT(*) FROM documents");
+ if (!m_stmt_getNumberOfDocuments.execute())
   throw std::runtime_error("Count not possible");
  
- return static_cast<int64_t>(m_stmt_getNumberOfDocuments->getColumn(0));
+ return m_stmt_getNumberOfDocuments.getColumn<int64_t>(0);
 }
 
 void Storage::cleanup()
@@ -48,106 +48,106 @@ void Storage::cleanup()
  if (m_maxage == 0)
   return;
 
- m_stmt_cleanup->reset();
- m_stmt_cleanup->exec();
+ m_stmt_cleanup.init("DELETE FROM documents WHERE timestamp + "s + std::to_string(static_cast<int64_t>(m_maxage)) + " < unixepoch()"s);
+ m_stmt_cleanup.execute();
 }
 
 bool Storage::exists(const std::string& id)
 {
- m_stmt_exists->reset();
- m_stmt_exists->bind(1, id);
+ m_stmt_exists.init("SELECT id FROM documents WHERE id = ?");
+ m_stmt_exists.bind(1, id);
 
- return m_stmt_exists->executeStep();
+ return m_stmt_exists.execute();
 }
 
 std::string Storage::getDocument(const std::string& id)
 {
- m_stmt_getDocument->reset();
- m_stmt_getDocument->bind(1, id);
+ m_stmt_getDocument.init("SELECT value FROM documents WHERE id = ?");
+ m_stmt_getDocument.bind(1, id);
 
- if (!m_stmt_getDocument->executeStep())
+ if (!m_stmt_getDocument.execute())
   throw std::runtime_error("id "s + id + " not found"s);
 
- return m_stmt_getDocument->getColumn(0);
+ return m_stmt_getDocument.getColumn<std::string>(0);
 }
 
 int Storage::getRevision(const std::string& id)
 {
- m_stmt_getRevision->reset();
- m_stmt_getRevision->bind(1, id);
+ m_stmt_getRevision.init("SELECT rev FROM documents WHERE id = ?");
+ m_stmt_getRevision.bind(1, id);
 
- if (!m_stmt_getRevision->executeStep())
+ if (!m_stmt_getRevision.execute())
   throw std::runtime_error("id "s + id + " not found"s);
 
- return m_stmt_getRevision->getColumn(0);
+ return m_stmt_getRevision.getColumn<int>(0);
 }
 
 int Storage::getCursorPos(const std::string& id)
 {
- m_stmt_getCursorPos->reset();
- m_stmt_getCursorPos->bind(1, id);
+ m_stmt_getCursorPos.init("SELECT cursorpos FROM documents WHERE id = ?");
+ m_stmt_getCursorPos.bind(1, id);
 
- if (!m_stmt_getCursorPos->executeStep())
+ if (!m_stmt_getCursorPos.execute())
   throw std::runtime_error("id "s + id + " not found"s);
 
- return m_stmt_getCursorPos->getColumn(0);
+ return m_stmt_getCursorPos.getColumn<int>(0);
 }
 
 std::tuple<std::string, int, int> Storage::getRow(const std::string& id)
 {
- m_stmt_getRow->reset();
- m_stmt_getRow->bind(1, id);
+ m_stmt_getRow.init("SELECT value, rev, cursorpos FROM documents WHERE id = ?");
+ m_stmt_getRow.bind(1, id);
 
- if (!m_stmt_getRow->executeStep())
+ if (!m_stmt_getRow.execute())
   throw std::runtime_error("id "s + id + " not found"s);
 
- return {m_stmt_getRow->getColumn(0), m_stmt_getRow->getColumn(1), m_stmt_getRow->getColumn(2)};
+ return {m_stmt_getRow.getColumn<std::string>(0), m_stmt_getRow.getColumn<int>(1), m_stmt_getRow.getColumn<int>(2)};
 }
  
 void Storage::setDocument(const std::string& id, const std::string& document)
 {
- 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();
+ m_stmt_setDocument.init("UPDATE documents SET value = ? WHERE id = ?");
+ m_stmt_setDocument.bind(1, document);
+ m_stmt_setDocument.bind(2, id);
+
+ if (!m_stmt_setDocument.execute()) {
+  m_stmt_setDocument_new.init("INSERT INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, unixepoch())");
+  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.execute();
  }
 }
 
 void Storage::setRevision(const std::string& id, int rev)
 {
- m_stmt_setRevision->reset();
- m_stmt_setRevision->bind(1, rev);
- m_stmt_setRevision->bind(2, id);
+ m_stmt_setRevision.init("UPDATE documents SET rev = ? WHERE id = ?");
+ m_stmt_setRevision.bind(1, rev);
+ m_stmt_setRevision.bind(2, id);
 
- if (!m_stmt_setRevision->exec())
+ if (!m_stmt_setRevision.execute())
   throw std::runtime_error("Unable to insert row with id "s + id);
 }
 
 void Storage::setCursorPos(const std::string& id, int cursorPos)
 {
- m_stmt_setCursorPos->reset();
- m_stmt_setCursorPos->bind(1, cursorPos);
- m_stmt_setCursorPos->bind(2, id);
+ m_stmt_setCursorPos.init("UPDATE documents SET cursorpos = ? WHERE id = ?");
+ m_stmt_setCursorPos.bind(1, cursorPos);
+ m_stmt_setCursorPos.bind(2, id);
 
- if (!m_stmt_setCursorPos->exec())
+ if (!m_stmt_setCursorPos.execute())
   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)
 {
- 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())
+ m_stmt_setRow.init("INSERT OR REPLACE INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, unixepoch())");
+ 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.execute())
   throw std::runtime_error("Unable to insert row with id "s + id);
 }
 
-- 
cgit v1.2.3