accouting.core.clj

(ns accounting.database
  (:require [clojureql.core :as cql]
	    [relational-model.core :as rel]))

(def db {:classname "com.mysql.jdbc.Driver"
	 :subprotocol "mysql"
	 :subname     "//localhost:3306/accounting"
	 :user        "root"
	 :password    ""
	 :auto-commit true
	 :fetch-size  500})

;; Get a macro to write this for you
(def transactions             (cql/table db :transactions))
(def entities                 (cql/table db :entities))
(def accounts                 (cql/table db :accounts))
(def journal_entries          (cql/table db :journal_entries))
(def products                 (cql/table db :products))

;; Could magic this up too
(def relations
     (-> {}
	 (rel/new-relational  :entities)
	 (rel/new-relational  :accounts)
	 (rel/new-relational  :products)
	 (rel/belongs-to      :accounts :entities :entity_id)
	 (rel/belongs-to      :accounts :products :product_id)))

;;  Useful functions
(defn find-by-id [table id]
  (cql/select table (cql/where (= :id id)))) 

;; Bind from below
(def parent   (partial rel/parent   relations))
(def children (partial rel/children relations))

;;  Setup a toy database
(comment

  (cql/conj! entities {:id 1 :name "Payment Gateway" :email ""})
  (cql/conj! entities {:id 2 :name "Supplier"        :email ""})
  (cql/conj! entities {:id 3 :name "Customer"        :email ""})

  (cql/conj! products {:id 1})
  (cql/conj! products {:id 2})
  (cql/conj! products {:id 3})

  (cql/conj! accounts {:id 1 :entity_id 1 :product_id 1})
  (cql/conj! accounts {:id 2 :entity_id 2 :product_id 1})
  (cql/conj! accounts {:id 3 :entity_id 3 :product_id 1})
  
  (cql/conj! accounts {:id 4 :entity_id 1 :product_id 2})
  (cql/conj! accounts {:id 5 :entity_id 2 :product_id 2})
  (cql/conj! accounts {:id 6 :entity_id 3 :product_id 2})

  ;; Gimme the accounts for an RTable coming out of a given table
  @(children (find-by-id products 1) accounts :products :accounts)

  ;; Gimme the product for an RTable coming out of a given table
  @(parent products (find-by-id accounts 1) :products :accounts)

  )

relational-model.core.clj

(ns relational-model.core
  (:require [clojureql.core :as cql]))

;; This is a small namespace to allow us to close over relations

(defprotocol Related
  (belongs-to [this that key]))

(defrecord Relation [belonging-to]
  Related
  (belongs-to [this that key]
	      (Relation. (assoc belonging-to that key))))

;;----------------------------------

(defn new-relational [relation-t name]
  (assoc relation-t name (Relation. {})))

(defn belongs-to [relation-t this that key]
  (assoc relation-t this
	 (.belongs-to (relation-t this) that key)))

;;----------------------------------

(defn col [t c]
  (keyword (str (name t) "." (name c))))

(defn belonging-to-fk [relations parent child]
  (-> relations child :belonging-to parent))

(defn belonging-to [relations parent-key child-key]
  (cql/where (= (col parent-key :id)
		(col child-key (belonging-to-fk relations parent-key child-key)))))

(defn relation-join [relations t1 t2 parent-key child-key projection]
  (-> t1
      (cql/join t2
		(belonging-to relations parent-key child-key))
      (cql/project projection)))

(defn children
  "Find the children of table t1 in table t2"
  [relations parent-table child-table parent-key child-key]
  (relation-join relations
		 parent-table child-table
		 parent-key child-key
		 [(col child-key :*)]))

(defn parent
  "Find the parent"
  [relations parent-table child-table parent-key child-key]
  (relation-join relations
		 child-table parent-table
		 parent-key child-key
		 [(col parent-key :*)]))