add sqlite storage for pages

7 files changed, 208 insertions, 0 deletions

diff --git a/clams-server/src/main/java/no/eliashaugsbakk/clams/server/Main.java b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/Main.java
index 200d23d..93a42cb 100644
--- a/clams-server/src/main/java/no/eliashaugsbakk/clams/server/Main.java
+++ b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/Main.java
@@ -3,9 +3,14 @@ package no.eliashaugsbakk.clams.server;
 import io.javalin.Javalin;
 import io.javalin.rendering.template.JavalinPebble;
 import no.eliashaugsbakk.clams.server.controller.BlogController;
+import no.eliashaugsbakk.clams.server.repository.SqliteManager;
 
 public class Main {
   void main() {
+    // TODO: Change db storage location to .../share/clams/... and implement custom storage locations
+    SqliteManager dbManager = new SqliteManager("testDb.db");
+    dbManager.init();
+
     BlogController blogController = new BlogController();
 
     var app = Javalin.create(config -> {
@@ -20,6 +25,8 @@ public class Main {
 
       config.routes.get("/blog/{slug}", blogController::handleGetPost);
       config.routes.get("/blog", blogController::handleGetOverview);
+
+      config.events.serverStopped(dbManager::close);
     }).start(7070);
   }
 }
diff --git a/clams-server/src/main/java/no/eliashaugsbakk/clams/server/model/Post.java b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/model/Post.java
new file mode 100644
index 0000000..e4136d0
--- /dev/null
+++ b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/model/Post.java
@@ -0,0 +1,6 @@
+package no.eliashaugsbakk.clams.server.model;
+
+import java.time.Instant;
+
+public record Post(String title, String slug, Instant timePublished, String content) {
+}
diff --git a/clams-server/src/main/java/no/eliashaugsbakk/clams/server/model/PostMetaData.java b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/model/PostMetaData.java
new file mode 100644
index 0000000..98a1c49
--- /dev/null
+++ b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/model/PostMetaData.java
@@ -0,0 +1,6 @@
+package no.eliashaugsbakk.clams.server.model;
+
+import java.time.Instant;
+
+public record PostMetaData(String title, String slug, Instant published, Instant lastEdit) {
+}
diff --git a/clams-server/src/main/java/no/eliashaugsbakk/clams/server/repository/BlogPostRepo.java b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/repository/BlogPostRepo.java
new file mode 100644
index 0000000..d63e095
--- /dev/null
+++ b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/repository/BlogPostRepo.java
@@ -0,0 +1,11 @@
+package no.eliashaugsbakk.clams.server.repository;
+
+import java.util.List;
+import java.util.Optional;
+import no.eliashaugsbakk.clams.server.model.Post;
+import no.eliashaugsbakk.clams.server.model.PostMetaData;
+
+public interface BlogPostRepo {
+  List<PostMetaData> listPostsByPublishedDesc();
+  Optional<Post> getPost(String slug);
+}
diff --git a/clams-server/src/main/java/no/eliashaugsbakk/clams/server/repository/BlogPostRepoSqlite.java b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/repository/BlogPostRepoSqlite.java
new file mode 100644
index 0000000..50daf39
--- /dev/null
+++ b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/repository/BlogPostRepoSqlite.java
@@ -0,0 +1,85 @@
+package no.eliashaugsbakk.clams.server.repository;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.time.Instant;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Optional;
+import no.eliashaugsbakk.clams.server.model.Post;
+import no.eliashaugsbakk.clams.server.model.PostMetaData;
+
+public class BlogPostRepoSqlite implements BlogPostRepo {
+  private final SqliteManager manager;
+
+  public BlogPostRepoSqlite(SqliteManager manager) {
+    this.manager = manager;
+  }
+
+  @Override
+  public List<PostMetaData> listPostsByPublishedDesc() {
+    String sql = """
+        SELECT title, slug, published, last_edited
+        FROM posts
+        ORDER BY published DESC
+        """;
+
+    List<PostMetaData> posts = new ArrayList<>();
+
+    try (Connection conn = manager.getConnection();
+        PreparedStatement stmt = conn.prepareStatement(sql);
+        ResultSet resultSet = stmt.executeQuery()) {
+
+      while (resultSet.next()) {
+        String title = resultSet.getString("title");
+        String slug = resultSet.getString("slug");
+
+        Instant published = Instant.parse(resultSet.getString("published"));
+
+        String lastEditRaw = resultSet.getString("last_edited");
+        Instant lastEdit = (lastEditRaw != null) ? Instant.parse(lastEditRaw) : null;
+
+        posts.add(new PostMetaData(title, slug, published, lastEdit));
+      }
+
+      return posts;
+
+    } catch (SQLException e) {
+      throw new RepoException("Error fetching all post metadata sorted by time", e);
+    }
+  }
+
+  @Override
+  public Optional<Post> getPost(String slug) {
+    String sql = """
+        SELECT title, slug, content, published
+        FROM posts
+        WHERE slug = ?
+        """;
+
+    try (Connection conn = manager.getConnection();
+        PreparedStatement stmt = conn.prepareStatement(sql)) {
+
+      stmt.setString(1, slug);
+
+      try (ResultSet resultSet = stmt.executeQuery()) {
+        if (resultSet.next()) {
+          String title = resultSet.getString("title");
+          String postSlug = resultSet.getString("slug");
+          String content = resultSet.getString("content");
+
+          String publishedRaw = resultSet.getString("published");
+          Instant timePublished = Instant.parse(publishedRaw);
+
+          return Optional.of(new Post(title, postSlug, timePublished, content));
+        }
+        return Optional.empty();
+      }
+
+    } catch (SQLException e) {
+      throw new RepoException("Error fetching full post by slug: " + slug, e);
+    }
+  }
+}
diff --git a/clams-server/src/main/java/no/eliashaugsbakk/clams/server/repository/RepoException.java b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/repository/RepoException.java
new file mode 100644
index 0000000..7fa6323
--- /dev/null
+++ b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/repository/RepoException.java
@@ -0,0 +1,10 @@
+package no.eliashaugsbakk.clams.server.repository;
+
+public class RepoException extends RuntimeException {
+  public RepoException(String message) {
+    super(message);
+  }
+  public RepoException(String message, Throwable cause) {
+    super(message, cause);
+  }
+}
diff --git a/clams-server/src/main/java/no/eliashaugsbakk/clams/server/repository/SqliteManager.java b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/repository/SqliteManager.java
new file mode 100644
index 0000000..d83cfda
--- /dev/null
+++ b/clams-server/src/main/java/no/eliashaugsbakk/clams/server/repository/SqliteManager.java
@@ -0,0 +1,83 @@
+package no.eliashaugsbakk.clams.server.repository;
+
+import com.zaxxer.hikari.HikariConfig;
+import com.zaxxer.hikari.HikariDataSource;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+public class SqliteManager implements AutoCloseable {
+  private final HikariDataSource dataSource;
+
+  public SqliteManager(String dbPath) {
+    // HikariCP pools reusable connections across web threads, avoiding the overhead of opening/closing disk files on every request.
+    HikariConfig config = new HikariConfig();
+
+    config.setJdbcUrl("jdbc:sqlite:" + dbPath);
+    config.setMaximumPoolSize(10);
+
+    config.addDataSourceProperty("journal_mode", "WAL");
+    config.addDataSourceProperty("busy_timeout", "5000");
+    config.addDataSourceProperty("synchronous", "NORMAL");
+    config.addDataSourceProperty("foreign_keys", "on");
+
+    this.dataSource = new HikariDataSource(config);
+  }
+
+  public void init() {
+    String posts = """
+        CREATE TABLE IF NOT EXISTS posts (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            slug TEXT NOT NULL UNIQUE,
+            title TEXT NOT NULL,
+            content TEXT,
+            summary TEXT,
+            published TEXT NOT NULL,
+            last_edited TEXT
+        );
+        """;
+
+    String idxPublished = """
+        CREATE INDEX IF NOT EXISTS idx_posts_published ON posts(published DESC);
+        """;
+
+    String tags = """
+        CREATE TABLE IF NOT EXISTS tags (
+          id INTEGER PRIMARY KEY AUTOINCREMENT,
+          name TEXT NOT NULL UNIQUE
+        );
+        """;
+
+    String post_tags = """
+        CREATE TABLE IF NOT EXISTS post_tags (
+            post_id INTEGER,
+            tag_id INTEGER,
+            PRIMARY KEY (post_id, tag_id),
+            FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
+            FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
+        );
+        """;
+
+    try (Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement()) {
+
+      stmt.execute(posts);
+      stmt.execute(idxPublished);
+      stmt.execute(tags);
+      stmt.execute(post_tags);
+
+    } catch (SQLException e) {
+      throw new RepoException("Error while initializing database", e);
+    }
+  }
+
+  public Connection getConnection() throws SQLException {
+    return dataSource.getConnection();
+  }
+
+  @Override
+  public void close() {
+    if (dataSource != null && !dataSource.isClosed()) {
+      dataSource.close();
+    }
+  }
+}