From 179afb9ed871c4cc7aee1913a3ea65bcebb84f35 Mon Sep 17 00:00:00 2001
From: Olaf Wintermann <olaf.wintermann@gmail.com>
Date: Mon, 11 Aug 2025 21:25:53 +0200
Subject: [PATCH] add db schema

---
 rss-application/pom.xml                       |  6 ++
 .../kotlin/de/unixwork/rssreader/Database.kt  | 67 +++++++++++++++++++
 .../de/unixwork/rssreader/FeedSourceList.kt   |  2 +
 3 files changed, 75 insertions(+)
 create mode 100644 rss-application/src/main/kotlin/de/unixwork/rssreader/Database.kt

diff --git a/rss-application/pom.xml b/rss-application/pom.xml
index 2599412..5ef160a 100644
--- a/rss-application/pom.xml
+++ b/rss-application/pom.xml
@@ -95,6 +95,12 @@
             <artifactId>ui-kotlin</artifactId>
             <version>1.0-SNAPSHOT</version>
         </dependency>
+
+        <dependency>
+            <groupId>com.h2database</groupId>
+            <artifactId>h2</artifactId>
+            <version>2.3.232</version>
+        </dependency>
     </dependencies>
 
 </project>
diff --git a/rss-application/src/main/kotlin/de/unixwork/rssreader/Database.kt b/rss-application/src/main/kotlin/de/unixwork/rssreader/Database.kt
new file mode 100644
index 0000000..97d9935
--- /dev/null
+++ b/rss-application/src/main/kotlin/de/unixwork/rssreader/Database.kt
@@ -0,0 +1,67 @@
+package de.unixwork.rssreader
+
+import java.sql.Connection
+import java.sql.DriverManager
+
+object Database {
+    val connection: Connection
+
+    init {
+        connection = DriverManager.getConnection("jdbc:h2:~/.rssreader/feeds")
+        ensureSchema(connection)
+    }
+
+    private fun ensureSchema(conn: Connection) {
+        val stmt = conn.createStatement()
+        val rs = stmt.executeQuery(
+            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FEEDCOLLECTIONS'"
+        )
+
+        var tableExists = false
+        if (rs.next()) {
+            tableExists = rs.getInt(1) > 0
+        }
+        rs.close()
+        stmt.close()
+
+        if (!tableExists) {
+            println("Database empty — creating tables...")
+            conn.createStatement().use { createStmt ->
+                createStmt.addBatch("""
+                    CREATE TABLE feedcollections (
+                        feedcollection_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+                        name VARCHAR,
+                        update_interval INT
+                    )
+                """.trimIndent())
+
+                createStmt.addBatch("""
+                    CREATE TABLE feeds(
+                        feed_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+                        feedcollection_id INT NOT NULL REFERENCES feedcollections(feedcollection_id) ON DELETE CASCADE,
+                        url VARCHAR NOT NULL,
+                        last_update TIMESTAMP
+                    )
+                """.trimIndent())
+
+                createStmt.addBatch("""
+                    CREATE TABLE items (
+                        item_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+                        feed_id INT NOT NULL REFERENCES feeds(feed_id),
+                        title VARCHAR NOT NULL,
+                        link VARCHAR NOT NULL,
+                        description CLOB,
+                        author VARCHAR,
+                        pub_date TIMESTAMP,
+                        guid VARCHAR UNIQUE,
+                        content CLOB
+                    )
+                """.trimIndent())
+
+                createStmt.executeBatch()
+            }
+        } else {
+            println("Database schema already exists.")
+        }
+    }
+}
diff --git a/rss-application/src/main/kotlin/de/unixwork/rssreader/FeedSourceList.kt b/rss-application/src/main/kotlin/de/unixwork/rssreader/FeedSourceList.kt
index 2aca7ae..fb5c2da 100644
--- a/rss-application/src/main/kotlin/de/unixwork/rssreader/FeedSourceList.kt
+++ b/rss-application/src/main/kotlin/de/unixwork/rssreader/FeedSourceList.kt
@@ -7,6 +7,8 @@ class FeedSourceList : Document() {
     val feeds = this.sourcelist("feeds")
 
     init {
+        val db = Database
+
         // testcode
         val sublist1 = SubList<FeedCollection>()
         sublist1.header = "Test 1"
-- 
2.47.3