diff options
| -rw-r--r-- | categories/sm64-any-16-stars.conf | 18 | ||||
| -rw-r--r-- | database/run.lisp | 41 |
2 files changed, 52 insertions, 7 deletions
diff --git a/categories/sm64-any-16-stars.conf b/categories/sm64-any-16-stars.conf new file mode 100644 index 0000000..7a87b7f --- /dev/null +++ b/categories/sm64-any-16-stars.conf @@ -0,0 +1,18 @@ +[category] +:name Super Mario 64 +:percentage 16 Stars Any% + +[splits] +:name Bobomb Battlefield +:name Whomp's Fortress +:name Cool Cool Mountain +:name Bowser in the Dark World +:name Shifting Sand Land +:name Lethal Lava Land +:name Hazy Maze Cave +:name MIPS Clip +:name Dire, dire docks +:name Bowser in the Fire Sea +:name BLJ's +:name Bowser in the Sky + 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))))) |