コミット: afe9c57f707a0b7e271d3083466e8c77742bde9e - flitter-nix (git)

A Nix-friendly SQLite-enhanced fork of Flitter, a speedrunning split timer for Unix-style terminals

コミットメタ情報

• リビジョン: afe9c57f707a0b7e271d3083466e8c77742bde9e (tree)
• 日時: 2023-06-06 02:09:56
• 作者: Corbin <cds@corb...>
• コミッター: Corbin

ログメッセージ

Fix a SQL query and add cumulative times per attempt.

変更サマリ

• modified: README.md
• modified: bin/summary_main.ml
• modified: src/run.ml

差分

--- a/README.md
+++ b/README.md
@@ -59,6 +59,32 @@ Keybindings are all global hotkeys; they will work even when the terminal is not
 | `Delete`    | Pause / delete run (don't save anything)            |
 | `Q`         | Quit (if not currently timing)                      |
 
+### Summaries
+
+The `flitter-summary` tool can be used to quickly extract the minimum (best),
+average, and standard deviation for an entire database, a single category, or a
+single run. For example:
+
+```bash
+$ flitter-summary splits.db
+...
+Horizon: Forbidden West (Abs%): min 33:09.354 (attempt 4), avg 38:17.799 ± 4:02.849
+...
+
+$ flitter-summary splits.db 'Horizon: Forbidden West' 'Abs%'
+Segment: New Game -> Interlude: min 15:23.983, avg 15:36.073 ± 11.095
+Segment: New Game -> Shuttle: min 13:53.595, avg 18:31.488 ± 6:05.001
+Segment: Shuttle -> Spire: min 2:13.510, avg 2:35.034 ± 17.954
+Segment: Spire -> Embassy: min 14:47.481, avg 18:00.206 ± 1:42.837
+Segment: Embassy -> Abs: min 2:14.768, avg 3:09.039 ± 1:06.530
+
+$ flitter-summary splits.db 'Horizon: Forbidden West' 'Abs%' 4
+Segment: New Game -> Shuttle: 13:53.595 (cum 13:53.595)
+Segment: Shuttle -> Spire: 2:13.510 (cum 16:07.105)
+Segment: Spire -> Embassy: 14:47.481 (cum 30:54.586)
+Segment: Embassy -> Abs: 2:14.768 (cum 33:09.354)
+```
+
 ## Contributing
 
 Feel free to make an issue or a pull request! Also feel free to copy any

--- a/bin/summary_main.ml
+++ b/bin/summary_main.ml
@@ -51,22 +51,25 @@ let () =
             ]
           in
           let stmt =
-            (* XXX SELECT DISTINCT required because path sometimes repeats i *)
+            (* SELECT DISTINCT required because path sometimes repeats i *)
             Storage.prep db
               "with path(start, end, i) as (select segments.starting_at, \
-               segments.ending_at, 0 from segments, routes where routes.game = \
-               ?1 and routes.category = ?2 and segments.starting_at = \
-               routes.starting_at union select starting_at, ending_at, path.i \
-               + 1 from segments, path where game = ?1 and category = ?2 and \
-               attempt = ?3 and starting_at = path.end) select distinct \
-               starting_at, ending_at, duration from path, segments where game \
-               = ?1 and category = ?2 and attempt = ?3 and starting_at = \
-               path.start and ending_at = path.end order by path.i;"
+               segments.ending_at, 0 from segments, routes using (starting_at) \
+               where routes.game=?1 and routes.category=?2 union select \
+               starting_at, ending_at, path.i+1 from segments, path where \
+               game=?1 and category=?2 and attempt=?3 and \
+               starting_at=path.end), segs(starting_at, ending_at, duration) \
+               as (select distinct starting_at, ending_at, duration from path, \
+               segments where game=?1 and category=?2 and attempt=?3 and \
+               starting_at=path.start and ending_at=path.end order by path.i) \
+               select starting_at, ending_at, duration, sum(duration) over \
+               (rows between unbounded preceding and current row) from segs;"
           in
           Storage.check_select stmt vals (fun row ->
               print_string
-                (sprintf "Segment: %s -> %s: %s\n"
+                (sprintf "Segment: %s -> %s: %s (cum %s)\n"
                    (Data.to_string_exn row.(0))
                    (Data.to_string_exn row.(1))
-                   (Duration.to_string (Data.to_int_exn row.(2)) 3))))
+                   (Duration.to_string (Data.to_int_exn row.(2)) 3)
+                   (Duration.to_string (Data.to_int_exn row.(3)) 3))))
   | _ -> print_string usage

--- a/src/run.ml
+++ b/src/run.ml
@@ -10,8 +10,8 @@ let sql =
    segments.ending_at, 0 from segments, routes using (starting_at) where \
    routes.game=?1 and routes.category=?2 union select starting_at, ending_at, \
    path.i+1 from segments, path where game=?1 and category=?2 and \
-   starting_at=path.end) select starting_at, ending_at, min(duration), \
-   avg(duration), stdev(duration) from path, segments where game=?1 and \
+   starting_at=path.end) select starting_at, ending_at, avg(duration), \
+   stdev(duration), min(duration) from path, segments where game=?1 and \
    starting_at=path.start and ending_at=path.end group by starting_at, \
    ending_at order by path.i;"
 
@@ -23,9 +23,9 @@ let from_db db game category =
         ( Sqlite3.Data.to_string_exn row.(0),
           Sqlite3.Data.to_string_exn row.(1),
           Gold.of_stats
-            ( Some (Sqlite3.Data.to_int_exn row.(2)),
+            ( Some (int_of_float (Sqlite3.Data.to_float_exn row.(2))),
               Some (int_of_float (Sqlite3.Data.to_float_exn row.(3))),
-              Some (int_of_float (Sqlite3.Data.to_float_exn row.(4))) ) )) )
+              Some (Sqlite3.Data.to_int_exn row.(4))))))
 
 let map f (i, l) = (i, List.map ~f:(fun (s, t, x) -> (s, t, f x)) l)
 let iter f (_, l) = List.iter ~f:(fun (s, t, x) -> f x) l