canidatalogtocte.clj

(ns canidatalogtocte
  (:require [clojure.java.jdbc :as jdbc]
            [clojure.string :as str]
            [clojure.spec.alpha :as s]
            [datascript.core :as d]
            [clojure.test :refer [is]]))

(s/check-asserts true)

(def sqlite-db
  {:classname   "org.sqlite.JDBC"
   :subprotocol "sqlite"
   :subname     "sqlite.db"})

(defn query [sql-params]
  (set (rest (jdbc/query sqlite-db sql-params {:as-arrays? true}))))

(defn load-table
  [{:keys [table pk cols rows idxs]}]
  (s/assert string? table)
  (s/assert (s/nilable (s/coll-of string?)) pk)
  (s/assert (s/coll-of string?) cols)
  (s/assert (s/coll-of (s/map-of string? any?)) rows)
  (println (str table " x" (count rows)))
  (jdbc/execute! sqlite-db (str "drop table if exists " table))
  (jdbc/execute! sqlite-db (str "create table " table " (" (str/join ", " cols)
                                (when pk (str ", primary key (" (str/join ", " pk) ")"))
                                ")"))
  (doseq [cols idxs]
    (jdbc/execute! sqlite-db (str "create index " (name (gensym (str table "_idx"))) " on " table "(" (str/join ", " cols) ")")))
  (jdbc/insert-multi! sqlite-db table rows))

(s/def ::eav (s/tuple ::e ::a ::v))
(s/def ::e nat-int?)
(s/def ::a keyword?)
(s/def ::v (s/or :n number? :s string?))

(defn eavs [db]
  (->> (for [[e a v] (d/datoms db :eavt)]
         [e a v])
       (s/assert (s/coll-of ::eav))))

(defn fqn [k] (.sym k))

(defn load-eav-table
  [{:keys [table db]}]
  (let [eavs (eavs db)]
    (load-table {:table table
                 :cols  ["E" "A" "V"]
                 :rows  (for [[e a v] eavs] {"E" e "A" (fqn a) "V" v})
                 :idxs  [["E" "A" "V"]
                         ["A" "E" "V"]
                         ["A" "V" "E"]]})))

(def schema1 {})
(def db1 (-> (d/empty-db schema1)
             (d/db-with [{:name "A1" :age 42 :gender "M"}
                         {:name "A2" :age 38 :gender "F"}
                         {:name "B1" :age 40 :gender "F"}
                         {:name "a1" :age 4 :gender "F" :mum "A2"}
                         {:name "a2" :age 2 :gender "M" :mum "A2"}
                         {:name "b1" :age 4 :gender "F" :mum "B1"}])))

(load-eav-table {:table "db1" :db db1})

(is (= (d/q '[:find ?name :where [?e :name ?name]] db1)
       (query ["SELECT V as name FROM db1 where A='name'"])))

(is (= (d/q '[:find ?name :where [?e :name ?name] [?e :gender "M"]] db1)
       (query "WITH   DB     as (SELECT * FROM db1),
                      FACTS1 as (SELECT E as e, V as name
                                  FROM DB
                                 WHERE A='name'),
                      FACTS2 as (SELECT FACTS1.name as name, DB.E as e
                                  FROM DB
                                  JOIN FACTS1 ON DB.E=FACTS1.e
                                 WHERE DB.A='gender' AND DB.V='M')
               SELECT name from FACTS2")))

(is (= (d/q '[:find ?name :where [?e :name ?name] [?e :mum ?mum] [?m :name ?mum] [?m :age 40]] db1)

       (query "WITH   DB     as (SELECT * FROM db1),
                      FACTS1 as (SELECT E as e, V as name
                                  FROM DB
                                 WHERE A='name'),
                      FACTS2 as (SELECT FACTS1.name as name, DB.E as e, DB.V as mum
                                  FROM DB
                                  JOIN FACTS1 ON DB.E=FACTS1.e
                                 WHERE DB.A='mum'),
                      FACTS3 as (SELECT FACTS2.name as name, FACTS2.e as e, DB.E as m, DB.V as mum
                                  FROM DB
                                  JOIN FACTS2 ON DB.V=FACTS2.mum
                                 WHERE DB.A='name'),
                      FACTS4 as (SELECT FACTS3.name as name, FACTS3.e as e, DB.E as m, FACTS3.mum as mum
                                  FROM DB
                                  JOIN FACTS3 ON DB.E=FACTS3.m
                                 WHERE DB.A='age' AND DB.V=40)
               SELECT name from FACTS4")))