diff options
| -rw-r--r-- | doc/adr/0002-why-h2.md | 21 | ||||
| -rw-r--r-- | project.clj | 2 | ||||
| -rwxr-xr-x | resources/org/sqlite/native/OpenBSD/x86_64/libsqlitejdbc.so | bin | 1185128 -> 0 bytes | |||
| -rw-r--r-- | resources/sql/h2_init.sql (renamed from resources/sql/sqlite_init.sql) | 8 | ||||
| -rw-r--r-- | src/weepinbell/core.clj | 6 | ||||
| -rw-r--r-- | src/weepinbell/migration.clj | 22 | ||||
| -rw-r--r-- | src/weepinbell/storage/h2.clj (renamed from src/weepinbell/storage/sqlite.clj) | 8 | ||||
| -rw-r--r-- | test/weepinbell/storage_test.clj | 16 |
8 files changed, 52 insertions, 31 deletions
diff --git a/doc/adr/0002-why-h2.md b/doc/adr/0002-why-h2.md new file mode 100644 index 0000000..4dcf10e --- /dev/null +++ b/doc/adr/0002-why-h2.md @@ -0,0 +1,21 @@ +# ADR: Use H2 database
+
+Date: 2026-10-23
+
+## Context
+
+SQLite is the ideal choice for small services like webmention receivers, but in the Java ecosystem, the only production-ready SQLite JDBC is [`org.xerial/sqlite-jdbc`](https://github.com/xerial/sqlite-jdbc), which has its limitations.
+
+`sqlite-jdbc` is **not truly cross-platform**. It is mostly written in C and compiled to `.so`. Then native libraries are bundled to `.jar` and called from Java. SQLite itself is super lightweight, but bundling native libraries of **every OS and arch** is extremely heavy. Plus, `sqlite-jdbc` does not support OpenBSD, NetBSD and many platforms. I had to manually compile C libraries for weepinbell to work on those platforms and it'll also make `.jar` much heavier.[^1]
+
+It's safe to say that, at least for the time being, SQLite is not suitable for Java and Clojure.
+
+## Decision
+
+The closest thing to SQLite in Java/Clojure ecosystem is [H2](https://www.h2database.com/html/main.html). It's file-based, light-weight (~ 2.5 MB) and very fast.
+
+It's also a SQL database, with some minor incompatibilties with SQLite (syntax-wise and feature-wise). Migrating from SQLite to H2 has very little cost.
+
+The SQLite version was never released and shipped to users. The H2 version will replace SQLite in the `v1.0.0` release. Apart from the git history. It'll be like we never supported SQLite.
+
+[^1]: Also see [ADR-0001](./0001-sqlite-jdbc-exclusions.md)
diff --git a/project.clj b/project.clj index c4188b3..1b45e41 100644 --- a/project.clj +++ b/project.clj @@ -22,7 +22,7 @@ [org.nibor.autolink/autolink "0.12.0"] [io.github.tonsky/fast-edn "1.2.0"] [com.github.seancorfield/next.jdbc "1.3.828"] - [org.xerial/sqlite-jdbc "3.53.4.0"] + [com.h2database/h2 "2.3.232"] [nrepl "1.8.0"]] :managed-dependencies [[org.clojure/tools.reader "1.6.0"] [org.ow2.asm/asm "9.11.1"]] diff --git a/resources/org/sqlite/native/OpenBSD/x86_64/libsqlitejdbc.so b/resources/org/sqlite/native/OpenBSD/x86_64/libsqlitejdbc.so Binary files differdeleted file mode 100755 index fc57dd4..0000000 --- a/resources/org/sqlite/native/OpenBSD/x86_64/libsqlitejdbc.so +++ /dev/null diff --git a/resources/sql/sqlite_init.sql b/resources/sql/h2_init.sql index 012e9c6..2ca12bd 100644 --- a/resources/sql/sqlite_init.sql +++ b/resources/sql/h2_init.sql @@ -1,5 +1,5 @@ create table if not exists webmention ( - id integer primary key autoincrement, + id integer auto_increment primary key, hash text not null unique, wm_source text not null, wm_target text not null, @@ -7,11 +7,11 @@ create table if not exists webmention ( wm_target_domain text not null, wm_source_path text not null, wm_target_path text not null, - lastmod integer not null + lastmod bigint not null ); create table if not exists microformats ( - id integer primary key autoincrement, + id integer auto_increment primary key, wm_id integer not null, mf_type text not null, mf_data text not null, /* schema on read, store EDN string here */ @@ -22,7 +22,7 @@ create table if not exists microformats ( ); create table if not exists proxied_photo ( - id integer primary key autoincrement, + id integer auto_increment primary key, wm_id integer not null, url text not null, diff --git a/src/weepinbell/core.clj b/src/weepinbell/core.clj index cb0033a..903d228 100644 --- a/src/weepinbell/core.clj +++ b/src/weepinbell/core.clj @@ -4,7 +4,7 @@ [weepinbell.config :refer [load-config]] [weepinbell.webhook :as webhook] [weepinbell.storage.edn-storage :as edn] - [weepinbell.storage.sqlite :as sqlite] + [weepinbell.storage.h2 :as h2] [weepinbell.storage.worker :as worker] [clojure.core.async :refer [chan >!! <!!]] [taoensso.timbre :as timbre] @@ -32,7 +32,7 @@ data-path (:data-path config) bare-store (case (:storage-type config) :edn (edn/use-store data-path) - :sqlite (sqlite/use-store data-path)) + :h2 (h2/use-store data-path)) [stop-worker async-store] (worker/start bare-store) ;; Hooks, a map of callback functions that processor calls ;; on certain events. Can be used for webhooks and more. @@ -85,6 +85,6 @@ (defn -main [& args] (case (first args) - "migrate-db" (apply m/edn->sqlite (rest args)) + "migrate-db" (apply m/edn->h2 (rest args)) (start-engine))) diff --git a/src/weepinbell/migration.clj b/src/weepinbell/migration.clj index 4883b99..6579d84 100644 --- a/src/weepinbell/migration.clj +++ b/src/weepinbell/migration.clj @@ -1,26 +1,26 @@ (ns weepinbell.migration
- (:require [weepinbell.storage.sqlite :as sqlite]
+ (:require [weepinbell.storage.h2 :as h2]
[weepinbell.storage.edn-storage :as edns])
- (:import [org.sqlite SQLiteException]))
+ #_(:import [org.h2 SQLiteException]))
-(defn edn->sqlite
+(defn edn->h2
"Given a EDN file and a SQLite DB path, migrate data from that
edn file to SQLite DB. It appends data to DB so the destination
db can be empty or it can has some data."
- [edn-file sqlite-db]
- {:pre [(some? edn-file) (some? sqlite-db)]}
- (let [ds (sqlite/make-datasource sqlite-db)]
- (sqlite/create-tables ds)
+ [edn-file h2-db]
+ {:pre [(some? edn-file) (some? h2-db)]}
+ (let [ds (h2/make-datasource h2-db)]
+ (h2/create-tables ds)
(edns/with-webmentions edn-file
(fn [wms]
(doseq [wm wms]
(let [id (get-in wm [:webmention :id])]
(println "Appending webmention" id "to database")
(try
- (sqlite/insert-webmention ds wm)
- (catch SQLiteException e
- (println "Something wrong with data or database")
- (println (.getMessage e)))
+ (h2/insert-webmention ds wm)
+;; (catch SQLiteException e
+;; (println "Something wrong with data or database")
+;; (println (.getMessage e)))
(catch Exception e
(println "Something unexpected happened.")
(println (.getMessage e))))))))
diff --git a/src/weepinbell/storage/sqlite.clj b/src/weepinbell/storage/h2.clj index 59feb37..b42b5ba 100644 --- a/src/weepinbell/storage/sqlite.clj +++ b/src/weepinbell/storage/h2.clj @@ -1,4 +1,4 @@ -(ns weepinbell.storage.sqlite +(ns weepinbell.storage.h2 (:require [clojure.string :refer [blank? split]] [next.jdbc :as jdbc] [next.jdbc.result-set :refer [as-unqualified-lower-maps]] @@ -55,7 +55,7 @@ (let [mf (:microformats data) photo (get-in data [:extra :proxied-photo]) wm_result (sql/insert! ds :webmention (wm->schema data)) - wm_id (get wm_result (keyword "last_insert_rowid()"))] + wm_id (:id wm_result)] (when mf (sql/insert! ds :microformats (mf->schema wm_id data))) (when photo @@ -113,14 +113,14 @@ (defn make-db [db-file] (io/make-parents db-file) - {:dbtype "sqlite" :dbname db-file}) + {:dbtype "h2" :dbname db-file}) (defn make-ds [db] (jdbc/get-datasource db)) (defn make-datasource [db-file] (jdbc/with-options (make-ds (make-db db-file)) {:builder-fn as-unqualified-lower-maps})) -(def init-sql-script (io/resource "sql/sqlite_init.sql")) +(def init-sql-script (io/resource "sql/h2_init.sql")) (defn create-tables [ds] (doseq [:let [statements (-> init-sql-script slurp (split #";"))] statement (remove blank? statements)] diff --git a/test/weepinbell/storage_test.clj b/test/weepinbell/storage_test.clj index b0df532..0885855 100644 --- a/test/weepinbell/storage_test.clj +++ b/test/weepinbell/storage_test.clj @@ -1,7 +1,7 @@ (ns weepinbell.storage-test
(:require [clojure.test :refer [testing is deftest]]
[weepinbell.storage.edn-storage :as edns]
- [weepinbell.storage.sqlite :as sqlite]
+ [weepinbell.storage.h2 :as h2]
[weepinbell.storage.worker :as worker]
[clojure.java.io :as io]
[clojure.spec.alpha :as s]
@@ -12,16 +12,16 @@ (def test-edn-file "tmp/testfile")
(when (.exists (io/file test-edn-file))
(io/delete-file test-edn-file))
-(def test-sqlite-db "tmp/testsqlite.db")
-(when (.exists (io/file test-sqlite-db))
- (io/delete-file test-sqlite-db))
+(def test-h2-db "tmp/testh2")
+(when (.exists (io/file test-h2-db))
+ (io/delete-file test-h2-db))
(def edn-storage (edns/use-store test-edn-file))
-(def sqlite-storage (sqlite/use-store test-sqlite-db))
+(def h2-storage (h2/use-store test-h2-db))
(def storages [edn-storage
- sqlite-storage
- (last (worker/start sqlite-storage))
- (last (worker/start edn-storage))])
+ h2-storage
+ (last (worker/start edn-storage))
+ (last (worker/start h2-storage))])
;; check if storages conform spec
|