diff options
| -rw-r--r-- | resources/config.edn | 2 | ||||
| -rw-r--r-- | resources/sql/h2_init.sql | 8 | ||||
| -rw-r--r-- | src/weepinbell/storage/h2.clj | 94 |
3 files changed, 63 insertions, 41 deletions
diff --git a/resources/config.edn b/resources/config.edn index 180e6c1..04b9b60 100644 --- a/resources/config.edn +++ b/resources/config.edn @@ -10,7 +10,7 @@ :allowed-target-domains ["example.com"] ;; available storage types: ;; - :edn (:data-path should point to an edn text file) - ;; - :sqlite (:data-path should point to a SQLite db file) + ;; - :h2 (:data-path should point to a H2 db, WITHOUT extension like ".db") ;; :data-path can be non-existent. weepinbell will create them. :storage-type :edn :data-path "data/webmention.edn" diff --git a/resources/sql/h2_init.sql b/resources/sql/h2_init.sql index 2ca12bd..475987f 100644 --- a/resources/sql/h2_init.sql +++ b/resources/sql/h2_init.sql @@ -30,3 +30,11 @@ create table if not exists proxied_photo ( references webmention(id) on delete cascade ); + +create index idx_webmention_hash on webmention (hash); + +create index idx_webmention_pair on webmention (wm_source, wm_target); + +create index idx_webmention_domain on webmention (wm_target_domain); + +create index idx_webmention_uri on webmention (wm_target_domain, wm_target_path); diff --git a/src/weepinbell/storage/h2.clj b/src/weepinbell/storage/h2.clj index dc81ce7..8d6c2af 100644 --- a/src/weepinbell/storage/h2.clj +++ b/src/weepinbell/storage/h2.clj @@ -11,10 +11,11 @@ ;; data trasnformation utilities (defn schema->map - [{:keys [wm_source wm_target hash lastmod]} mf-row photo-row] + [{:keys [wm_source wm_target hash lastmod + mf_data photo_url]}] (let [parse #(if (string? %) (edn/read-string %) %) - mf (parse (:mf_data mf-row)) - photo (:url photo-row)] + mf (parse mf_data) + photo photo_url] (cond-> {:webmention {:source wm_source :target wm_target :id hash @@ -42,59 +43,68 @@ ;; conver EDN to raw string :mf_data (pr-str mf)})) -(defn- row->wrapped [ds wm-row] - (when wm-row - (let [wm_id (:id wm-row)] - (schema->map wm-row - (first (sql/find-by-keys ds :microformats {:wm_id wm_id})) - (first (sql/find-by-keys ds :proxied_photo {:wm_id wm_id})))))) - ;; sql (defn insert-webmention [ds data] - (let [mf (:microformats data) - photo (get-in data [:extra :proxied-photo]) - wm_result (sql/insert! ds :webmention (wm->schema data)) - wm_id (:id wm_result)] - (when mf - (sql/insert! ds :microformats (mf->schema wm_id data))) - (when photo - (sql/insert! ds :proxied_photo - {:wm_id wm_id - :url photo})))) + (jdbc/with-transaction [tx ds] + (let [tx (jdbc/with-options tx {:builder-fn as-unqualified-lower-maps}) + mf (:microformats data) + photo (get-in data [:extra :proxied-photo]) + wm_result (sql/insert! tx :webmention (wm->schema data)) + wm_id (:id wm_result)] + (when mf + (sql/insert! tx :microformats (mf->schema wm_id data))) + (when photo + (sql/insert! tx :proxied_photo + {:wm_id wm_id + :url photo}))))) (defn delete-webmention [ds hashcode] ;; microformats and photo will be deleted by ;; cascade rule by SQL, no need to handle it (sql/delete! ds :webmention {:hash hashcode})) +(def base-sql "SELECT w.*, m.mf_data, m.mf_type, p.url AS photo_url + FROM webmention w + LEFT JOIN microformats m + ON w.id = m.wm_id + LEFT JOIN proxied_photo p + ON w.id = p.wm_id") + (defn get-webmention-by-hash [ds hashcode] - (first (sql/find-by-keys ds :webmention - {:hash hashcode} - {:order-by [:lastmod :id]}))) + (first (jdbc/execute! ds [(str base-sql " + WHERE hash = ?") hashcode]))) (defn get-webmention-by-pair [ds source target] - (first (sql/find-by-keys ds :webmention - {:wm_source source - :wm_target target} - {:order-by [:lastmod :id]}))) + (first (jdbc/execute! ds [(str base-sql " + WHERE wm_source = ? + AND wm_target = ? + ORDER BY w.lastmod, w.id") + source target]))) + (defn get-webmention ([ds hashcode] (some->> (get-webmention-by-hash ds hashcode) - (row->wrapped ds))) + schema->map)) ([ds source target] (some->> (get-webmention-by-pair ds source target) - (row->wrapped ds)))) + schema->map))) (defn get-webmentions-by-domain ([ds domain] (get-webmentions-by-domain ds domain nil)) ([ds domain path] - (let [keys (cond-> {:wm_target_domain domain} - (some? path) (assoc :wm_target_path path))] - (->> (sql/find-by-keys ds :webmention keys - {:order-by [:lastmod :id]}) - (map (partial row->wrapped ds)))))) + (let [r (if (some? path) + (jdbc/execute! ds [(str base-sql " + WHERE wm_target_domain = ? + AND wm_target_path = ? + ORDER BY w.lastmod, w.id") + domain path]) + (jdbc/execute! ds [(str base-sql " + WHERE wm_target_domain = ? + ORDER BY w.lastmod, w.id") + domain]))] + (map schema->map r)))) (defn- update-related! [ds wm_id data] (let [mf (:microformats data) @@ -108,13 +118,17 @@ (defn update-webmention ([ds hashcode new] - (when-let [wm (get-webmention-by-hash ds hashcode)] - (sql/update! ds :webmention (wm->schema new) {:id (:id wm)}) - (update-related! ds (:id wm) new))) + (jdbc/with-transaction [tx ds] + (let [tx (jdbc/with-options tx {:builder-fn as-unqualified-lower-maps})] + (when-let [wm (get-webmention-by-hash tx hashcode)] + (sql/update! tx :webmention (wm->schema new) {:id (:id wm)}) + (update-related! tx (:id wm) new))))) ([ds source target new] - (when-let [wm (get-webmention-by-pair ds source target)] - (sql/update! ds :webmention (wm->schema new) {:id (:id wm)}) - (update-related! ds (:id wm) new)))) + (jdbc/with-transaction [tx ds] + (let [tx (jdbc/with-options tx {:builder-fn as-unqualified-lower-maps})] + (when-let [wm (get-webmention-by-pair tx source target)] + (sql/update! tx :webmention (wm->schema new) {:id (:id wm)}) + (update-related! tx (:id wm) new)))))) (defn make-db [db-file] (io/make-parents db-file) |