| 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)))) |
|---|
