| 1 |
;; With a traditional database, queries are typically formulated in a |
|---|
| 2 |
;; declarative query language like SQL. With the BKNR datastore, |
|---|
| 3 |
;; no separate is needed as all data is stored in main memory, as |
|---|
| 4 |
;; regular Lisp objects. |
|---|
| 5 |
|
|---|
| 6 |
;; This file is meant to give you an impression how one can write |
|---|
| 7 |
;; queries in Lisp, using the BOS square meter selling application as |
|---|
| 8 |
;; an example database. |
|---|
| 9 |
|
|---|
| 10 |
(in-package :bos.m2) |
|---|
| 11 |
|
|---|
| 12 |
(enable-interpol-syntax) |
|---|
| 13 |
|
|---|
| 14 |
|
|---|
| 15 |
;; Number of sponsors |
|---|
| 16 |
|
|---|
| 17 |
;; SELECT COUNT(*) FROM SPONSOR |
|---|
| 18 |
|
|---|
| 19 |
(time (length (class-instances 'sponsor))) |
|---|
| 20 |
|
|---|
| 21 |
|
|---|
| 22 |
;; Find all sponsors that have "hans" in their name |
|---|
| 23 |
|
|---|
| 24 |
;; SELECT * FROM SPONSOR WHERE NAME LIKE "%hans%" |
|---|
| 25 |
|
|---|
| 26 |
(time (remove-if-not (lambda (sponsor) |
|---|
| 27 |
(ignore-errors (cl-ppcre:scan "(?i)hans" (user-full-name sponsor)))) |
|---|
| 28 |
(class-instances 'sponsor))) |
|---|
| 29 |
|
|---|
| 30 |
|
|---|
| 31 |
;; Number of square meters |
|---|
| 32 |
|
|---|
| 33 |
;; SELECT COUNT(*) FROM M2 |
|---|
| 34 |
|
|---|
| 35 |
(time (length (class-instances 'm2))) |
|---|
| 36 |
|
|---|
| 37 |
|
|---|
| 38 |
;; Number of square meters sold |
|---|
| 39 |
|
|---|
| 40 |
;; SELECT COUNT(*) FROM M2 WHERE CONTRACT IS NOT NULL |
|---|
| 41 |
|
|---|
| 42 |
(time (length (remove-if-not #'m2-contract (class-instances 'm2)))) |
|---|
| 43 |
|
|---|
| 44 |
|
|---|
| 45 |
;; Number of contracts with sponsors from Germany |
|---|
| 46 |
|
|---|
| 47 |
;; SELECT COUNT(*) FROM CONTRACT C WHERE (SELECT COUNTRY FROM SPONSOR WHERE ID = C.ID) = 'NL' |
|---|
| 48 |
|
|---|
| 49 |
(time (length (remove "DE" (class-instances 'contract) |
|---|
| 50 |
:key (compose #'sponsor-country #'contract-sponsor) |
|---|
| 51 |
:test (complement #'equal)))) |
|---|
| 52 |
|
|---|
| 53 |
|
|---|
| 54 |
;; Number of contracts with sponsors from Holland that are larger than 10 square meters. |
|---|
| 55 |
|
|---|
| 56 |
;; SELECT COUNT(*) FROM CONTRACT C, SPONSOR S WHERE S.COUNTRY = 'NL' AND (SELECT COUNT(*) FROM M2 M WHERE M.ID = C.ID) > 10 |
|---|
| 57 |
|
|---|
| 58 |
(time (length (remove-if-not (lambda (contract) (and (equal "NL" (sponsor-country (contract-sponsor contract))) |
|---|
| 59 |
(> (length (contract-m2s contract)) 10))) |
|---|
| 60 |
(class-instances 'contract)))) |
|---|
| 61 |
|
|---|
| 62 |
|
|---|
| 63 |
;; Find a particular sponsor |
|---|
| 64 |
|
|---|
| 65 |
;; SELECT * FROM SPONSOR WHERE EMAIL = "hans.huebner@gmail.com" LIMIT TO 1 ROW |
|---|
| 66 |
|
|---|
| 67 |
(defvar *sponsor* (time (find "hans.huebner@gmail.com" (class-instances 'sponsor) :key #'user-email :test #'equal))) |
|---|
| 68 |
|
|---|
| 69 |
|
|---|
| 70 |
;; Find the contracts with this sponsor |
|---|
| 71 |
|
|---|
| 72 |
;; SELECT * FROM CONTRACT WHERE SPONSOR_ID = [sponsor-id] |
|---|
| 73 |
|
|---|
| 74 |
(time (sponsor-contracts *sponsor*)) |
|---|
| 75 |
|
|---|
| 76 |
|
|---|
| 77 |
;; Find out how many square meters that this sponsor "owns" |
|---|
| 78 |
|
|---|
| 79 |
;; SELECT COUNT(*) FROM M2 WHERE CONTRACT_ID IN (SELECT CONTRACT_ID FROM CONTRACT WHERE SPONSOR_ID = [sponsor-id]) |
|---|
| 80 |
|
|---|
| 81 |
(time (apply #'+ (mapcar (compose #'length #'contract-m2s) (sponsor-contracts *sponsor*)))) |
|---|
| 82 |
|
|---|
| 83 |
;; We can do it without consing |
|---|
| 84 |
|
|---|
| 85 |
(time (loop for contract in (sponsor-contracts *sponsor*) |
|---|
| 86 |
sum (length (contract-m2s contract)))) |
|---|
| 87 |
|
|---|
| 88 |
|
|---|
| 89 |
;; Determine the number of sponsors from each country |
|---|
| 90 |
|
|---|
| 91 |
;; SELECT COUNTRY, COUNT(*) FROM SPONSOR GROUP BY COUNTRY |
|---|
| 92 |
|
|---|
| 93 |
(time (mapcar (lambda (entry) |
|---|
| 94 |
(list (car entry) (length (cdr entry)))) |
|---|
| 95 |
(group-on (class-instances 'sponsor) |
|---|
| 96 |
:key (compose #'make-keyword-from-string #'sponsor-country)))) |
|---|
