From 9465fd744cc2117190bafc1a3e2da9f10ca29bf9 Mon Sep 17 00:00:00 2001
From: Roland Reichwein <mail@reichwein.it>
Date: Sat, 31 Dec 2022 22:00:11 +0100
Subject: Storage via SQLite, Added tests (WIP)

---
 storage.cpp | 129 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++---
 1 file changed, 124 insertions(+), 5 deletions(-)

(limited to 'storage.cpp')

diff --git a/storage.cpp b/storage.cpp
index 9dc7615..545ba04 100644
--- a/storage.cpp
+++ b/storage.cpp
@@ -2,16 +2,135 @@
 
 #include "config.h"
 
+#include <chrono>
+
 #include <SQLiteCpp/SQLiteCpp.h>
 
-Storage::Storage(const Config& config): m_config(config)
+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_db.exec("CREATE TABLE IF NOT EXISTS documents (id VARCHAR(16) PRIMARY KEY, value BLOB, rev INTEGER, cursorpos INTEGER, timestamp BIGINT)");
+}
+
+uint64_t Storage::getNumberOfDocuments()
+{
+ SQLite::Statement query(m_db, "SELECT COUNT(*) FROM documents");
+ if (!query.executeStep())
+  throw std::runtime_error("Count not possible");
+ 
+ return static_cast<int64_t>(query.getColumn(0));
+}
+
+void Storage::cleanup()
 {
- SQLite::Database db(m_config.getDataPath() + "/whiteboard.db3", SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE);
+ 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));
 
- db.exec("CREATE TABLE IF NOT EXISTS documents (id INTEGER PRIMARY KEY, value TEXT)");
+ query.exec();
 }
 
-std::string Storage::getDocument()
+bool Storage::exists(const std::string& id)
 {
- return "";
+ SQLite::Statement query(m_db, "SELECT id FROM documents WHERE id = ?");
+ query.bind(1, id);
+
+ return query.executeStep();
 }
+
+std::string Storage::getDocument(const std::string& id)
+{
+ SQLite::Statement query(m_db, "SELECT value FROM documents WHERE id = ?");
+ query.bind(1, id);
+
+ if (!query.executeStep())
+  throw std::runtime_error("id "s + id + " not found"s);
+
+ return query.getColumn(0);
+}
+
+int Storage::getRevision(const std::string& id)
+{
+ SQLite::Statement query(m_db, "SELECT rev FROM documents WHERE id = ?");
+ query.bind(1, id);
+
+ if (!query.executeStep())
+  throw std::runtime_error("id "s + id + " not found"s);
+
+ return query.getColumn(0);
+}
+
+int Storage::getCursorPos(const std::string& id)
+{
+ SQLite::Statement query(m_db, "SELECT cursorpos FROM documents WHERE id = ?");
+ query.bind(1, id);
+
+ if (!query.executeStep())
+  throw std::runtime_error("id "s + id + " not found"s);
+
+ return query.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);
+
+ if (!query.executeStep())
+  throw std::runtime_error("id "s + id + " not found"s);
+
+ return {query.getColumn(0), query.getColumn(1), query.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();
+ }
+}
+
+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);
+
+ if (!query.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);
+
+ if (!query.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())
+  throw std::runtime_error("Unable to insert row with id "s + id);
+}
+
-- 
cgit v1.2.3