Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
(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")))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment