From 95409b07130952c0677438068b2bc7a90699b9a2 Mon Sep 17 00:00:00 2001 From: Logan Hunt Date: Wed, 1 Jun 2022 23:46:09 -0700 Subject: Add super mario 64 16 star speedrun; add personal best and speedrun listing helper functions --- database/run.lisp | 41 ++++++++++++++++++++++++++++++++++------- 1 file changed, 34 insertions(+), 7 deletions(-) (limited to 'database/run.lisp') diff --git a/database/run.lisp b/database/run.lisp index adee23a..eba8b81 100644 --- a/database/run.lisp +++ b/database/run.lisp @@ -11,6 +11,8 @@ (:record-timestamps nil) (:conc-name run-split-)) + + (defun run-splits (run) (mito:select-dao 'run-split (sxql:order-by :category_split_id) @@ -30,12 +32,37 @@ (format-time (make-time-alist elapsed)) "-"))) -;;(defun best-split (category-split) -;; (mito:select-dao 'run-split -;; (sxql:order-by (:- V -;; Returns stuff like PB, best of each split, etc. -(defun run-statistics (category) - `((asdf . 1))) -;; select *, sum(julianday(end_time)-julianday(start_time))*24*60*60 as total_time from run_split group by run_id order by total_time; +(defmacro query-with-runs-elapsed (&rest body) + `(mito:retrieve-by-sql + (sxql:select (:* (:as (:raw "sum(julianday(end_time) - julianday(start_time))*24*60*60") :elapsed)) + (sxql:from :run_split) + (sxql:group-by :run_id) + ,@body))) + +(defun best-category-run (category) + (query-with-runs-elapsed + (sxql:inner-join :run :on (:= :run_id :run.id)) + (sxql:order-by :elapsed) + (sxql:limit 1) + (sxql:where (:= :category_id (mito:object-id category))))) + +(defun best-category-split (category-split) + (query-with-runs-elapsed + (sxql:inner-join :category_split :on (:= :category_split_id :category_split.id)) + (sxql:order-by :elapsed) + (sxql:limit 1) + (sxql:where (:= :category_split_id (mito:object-id category-split))))) + +(defun list-runs (&key (order-element :end-time) (direction :asc)) + (query-with-runs-elapsed + (sxql:inner-join :run :on (:= :run_id :run.id)) + (sxql:inner-join :category :on (:= :category_id :category.id)) + (sxql:order-by (list direction order-element)))) + +(defun list-category-runs (category &key (order-element :elapsed) (direction :asc)) + (query-with-runs-elapsed + (sxql:inner-join :run :on (:= :run_id :run.id)) + (sxql:order-by (list direction order-element)) + (sxql:where (:= :category_id (mito:object-id category))))) -- cgit v1.2.3-70-g09d2