Add SQL VACUUM to cleanup

1 files changed, 36 insertions, 32 deletions

diff --git a/storage.cpp b/storage.cpp
index a7d5a36..f7f15b1 100644
--- a/storage.cpp
+++ b/storage.cpp
@@ -13,21 +13,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_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(m_db, "CREATE TABLE IF NOT EXISTS documents (id VARCHAR(16) PRIMARY KEY, value BLOB, rev INTEGER, cursorpos INTEGER, timestamp BIGINT)"),
+ m_stmt_getNumberOfDocuments(m_db, "SELECT COUNT(*) FROM documents"),
+ m_stmt_cleanup(m_db, "DELETE FROM documents WHERE timestamp + ? < ?"),
+ m_stmt_vacuum(m_db, "VACUUM"),
+ m_stmt_exists(m_db, "SELECT id FROM documents WHERE id = ?"),
+ m_stmt_getDocument(m_db, "SELECT value FROM documents WHERE id = ?"),
+ m_stmt_getRevision(m_db, "SELECT rev FROM documents WHERE id = ?"),
+ m_stmt_getCursorPos(m_db, "SELECT cursorpos FROM documents WHERE id = ?"),
+ m_stmt_getRow(m_db, "SELECT value, rev, cursorpos FROM documents WHERE id = ?"),
+ m_stmt_setDocument(m_db, "UPDATE documents SET value = ?, timestamp = ?, rev = rev + 1 WHERE id = ?"),
+ m_stmt_setDocument_new(m_db, "INSERT INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, ?)"),
+ m_stmt_setRevision(m_db, "UPDATE documents SET rev = ? WHERE id = ?"),
+ m_stmt_setCursorPos(m_db, "UPDATE documents SET cursorpos = ? WHERE id = ?"),
+ m_stmt_setRow(m_db, "INSERT OR REPLACE INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, ?)")
 {
- m_stmt_create.init("CREATE TABLE IF NOT EXISTS documents (id VARCHAR(16) PRIMARY KEY, value BLOB, rev INTEGER, cursorpos INTEGER, timestamp BIGINT)");
+ CompiledSQL::Guard g{m_stmt_create};
  m_stmt_create.execute();
 }
 
@@ -37,7 +39,7 @@ Storage::~Storage()
 
 uint64_t Storage::getNumberOfDocuments()
 {
- m_stmt_getNumberOfDocuments.init("SELECT COUNT(*) FROM documents");
+ CompiledSQL::Guard g{m_stmt_getNumberOfDocuments};
  if (!m_stmt_getNumberOfDocuments.execute())
   throw std::runtime_error("Count not possible");
  
@@ -54,18 +56,20 @@ namespace {
 
 void Storage::cleanup()
 {
- if (m_maxage == 0)
-  return;
-
- m_stmt_cleanup.init("DELETE FROM documents WHERE timestamp + ? < ?");
- m_stmt_cleanup.bind(1, static_cast<int64_t>(m_maxage));
- m_stmt_cleanup.bind(2, static_cast<int64_t>(unixepoch()));
- m_stmt_cleanup.execute();
+ if (m_maxage != 0) {
+  CompiledSQL::Guard g{m_stmt_cleanup};
+  m_stmt_cleanup.bind(1, static_cast<int64_t>(m_maxage));
+  m_stmt_cleanup.bind(2, static_cast<int64_t>(unixepoch()));
+  m_stmt_cleanup.execute();
+ }
+ 
+ CompiledSQL::Guard g{m_stmt_vacuum};
+ m_stmt_vacuum.execute();
 }
 
 bool Storage::exists(const std::string& id)
 {
- m_stmt_exists.init("SELECT id FROM documents WHERE id = ?");
+ CompiledSQL::Guard g{m_stmt_exists};
  m_stmt_exists.bind(1, id);
 
  return m_stmt_exists.execute();
@@ -73,7 +77,7 @@ bool Storage::exists(const std::string& id)
 
 std::string Storage::getDocument(const std::string& id)
 {
- m_stmt_getDocument.init("SELECT value FROM documents WHERE id = ?");
+ CompiledSQL::Guard g{m_stmt_getDocument};
  m_stmt_getDocument.bind(1, id);
 
  if (!m_stmt_getDocument.execute())
@@ -84,7 +88,7 @@ std::string Storage::getDocument(const std::string& id)
 
 int Storage::getRevision(const std::string& id)
 {
- m_stmt_getRevision.init("SELECT rev FROM documents WHERE id = ?");
+ CompiledSQL::Guard g{m_stmt_getRevision};
  m_stmt_getRevision.bind(1, id);
 
  if (!m_stmt_getRevision.execute())
@@ -95,7 +99,7 @@ int Storage::getRevision(const std::string& id)
 
 int Storage::getCursorPos(const std::string& id)
 {
- m_stmt_getCursorPos.init("SELECT cursorpos FROM documents WHERE id = ?");
+ CompiledSQL::Guard g{m_stmt_getCursorPos};
  m_stmt_getCursorPos.bind(1, id);
 
  if (!m_stmt_getCursorPos.execute())
@@ -106,7 +110,7 @@ int Storage::getCursorPos(const std::string& id)
 
 std::tuple<std::string, int, int> Storage::getRow(const std::string& id)
 {
- m_stmt_getRow.init("SELECT value, rev, cursorpos FROM documents WHERE id = ?");
+ CompiledSQL::Guard g{m_stmt_getRow};
  m_stmt_getRow.bind(1, id);
 
  if (!m_stmt_getRow.execute())
@@ -117,13 +121,13 @@ std::tuple<std::string, int, int> Storage::getRow(const std::string& id)
  
 void Storage::setDocument(const std::string& id, const std::string& document)
 {
- m_stmt_setDocument.init("UPDATE documents SET value = ?, timestamp = ?, rev = rev + 1 WHERE id = ?");
+ CompiledSQL::Guard g{m_stmt_setDocument};
  m_stmt_setDocument.bind(1, document);
  m_stmt_setDocument.bind(2, static_cast<int64_t>(unixepoch()));
  m_stmt_setDocument.bind(3, id);
 
  if (!m_stmt_setDocument.execute()) {
-  m_stmt_setDocument_new.init("INSERT INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, ?)");
+  CompiledSQL::Guard g{m_stmt_setDocument_new};
   m_stmt_setDocument_new.bind(1, id);
   m_stmt_setDocument_new.bind(2, document);
   m_stmt_setDocument_new.bind(3, 0);
@@ -135,7 +139,7 @@ void Storage::setDocument(const std::string& id, const std::string& document)
 
 void Storage::setRevision(const std::string& id, int rev)
 {
- m_stmt_setRevision.init("UPDATE documents SET rev = ? WHERE id = ?");
+ CompiledSQL::Guard g{m_stmt_setRevision};
  m_stmt_setRevision.bind(1, rev);
  m_stmt_setRevision.bind(2, id);
 
@@ -145,7 +149,7 @@ void Storage::setRevision(const std::string& id, int rev)
 
 void Storage::setCursorPos(const std::string& id, int cursorPos)
 {
- m_stmt_setCursorPos.init("UPDATE documents SET cursorpos = ? WHERE id = ?");
+ CompiledSQL::Guard g{m_stmt_setCursorPos};
  m_stmt_setCursorPos.bind(1, cursorPos);
  m_stmt_setCursorPos.bind(2, id);
 
@@ -155,7 +159,7 @@ void Storage::setCursorPos(const std::string& id, int cursorPos)
 
 void Storage::setRow(const std::string& id, const std::string& document, int rev, int cursorPos)
 {
- m_stmt_setRow.init("INSERT OR REPLACE INTO documents (id, value, rev, cursorpos, timestamp) values (?, ?, ?, ?, ?)");
+ CompiledSQL::Guard g{m_stmt_setRow};
  m_stmt_setRow.bind(1, id);
  m_stmt_setRow.bind(2, document);
  m_stmt_setRow.bind(3, rev);