提交: 6df03b7fc8079ff67780c9c2c8b1c900b0701550 - flitter-nix (git)

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

Commit MetaInfo

• 修订版: 6df03b7fc8079ff67780c9c2c8b1c900b0701550 (tree)
• 时间: 2023-05-24 14:53:04
• 作者: Corbin <cds@corb...>
• Commiter: Corbin

Log Message

Start factoring SQLite access.

Also, reuse the existing Welford implementation. The SQLite user-defined
function bindings worked perfectly on the first try, and this was much
easier than requiring a special SQLite with extra extension functions.

更改概述

• modified: bin/migrate_main.ml
• modified: bin/summary_main.ml
• add: src/storage.ml

差异

--- a/bin/migrate_main.ml
+++ b/bin/migrate_main.ml
@@ -121,16 +121,12 @@ let insert_runs db sexp =
 let () =
   match Array.to_list (Sys.get_argv ()) with
   | _ :: db_path :: scms ->
-      let db = db_open db_path in
-      print_string (sqlite_version_info () ^ "\n");
-      Rc.check (exec db "pragma foreign_keys = on;");
-      List.iter
-        ~f:(fun path ->
-          let sexp = Sexp.load_sexp_conv_exn path old_sexp_of_sexp in
-          insert_route db sexp;
-          print_string "Successfully created route!\n";
-          insert_runs db sexp)
-        scms;
-      let _ = db_close db in
-      ()
+      Storage.with_db db_path (fun db ->
+          List.iter
+            ~f:(fun path ->
+              let sexp = Sexp.load_sexp_conv_exn path old_sexp_of_sexp in
+              insert_route db sexp;
+              print_string "Successfully created route!\n";
+              insert_runs db sexp)
+            scms)
   | _ -> print_string usage

--- a/bin/summary_main.ml
+++ b/bin/summary_main.ml
@@ -12,36 +12,48 @@ let check_select stmt vals f =
 let () =
   match Sys.get_argv () with
   | [| _; db_path |] ->
-      let db = db_open db_path in
-      Rc.check (exec db "pragma foreign_keys = on;");
-      let stmt = prepare db "select count(*) from games;" in
-      check_select stmt [] (fun row ->
-          print_string
-            (sprintf "Number of games: %d\n" (Data.to_int_exn row.(0))));
-      let stmt = prepare db "select * from games;" in
-      check_select stmt [] (fun row ->
-          print_string (sprintf "* %s\n" (Data.to_string_exn row.(0))));
-      let _ = db_close db in
-      ()
-  | [| _; db_path; game |] ->
-      let db = db_open db_path in
-      Rc.check (exec db "pragma foreign_keys = on;");
-      let vals = [ Data.TEXT game ] in
-      let stmt = prepare db "select count(*) from routes where game = ?;" in
-      check_select stmt vals (fun row ->
-          print_string
-            (sprintf "Number of routes: %d\n" (Data.to_int_exn row.(0))));
-      let stmt =
-        prepare db
-          "select category, penultimate, finish_line from routes where game = \
-           ?;"
-      in
-      check_select stmt vals (fun row ->
-          print_string
-            (sprintf "* %s (%s, %s)\n"
-               (Data.to_string_exn row.(0))
-               (Data.to_string_exn row.(1))
-               (Data.to_string_exn row.(2))));
-      let _ = db_close db in
-      ()
+      Storage.with_db db_path (fun db ->
+          Aggregate.create_fun1 db "stdev" ~init:Welford.empty
+            ~step:(fun w d -> Welford.observe w (Data.to_int_exn d))
+            ~final:(fun w -> Data.FLOAT (Welford.stddev w));
+          let stmt =
+            prepare db
+              "with attempts as (select game, category, attempt, sum(duration) \
+               as total from segments group by game, category, attempt) select \
+               game, category, attempt, min(total), avg(total), stdev(total) \
+               from attempts group by game, category;"
+          in
+          check_select stmt [] (fun row ->
+              print_string
+                (sprintf "%s (%s): min %s (attempt %d), avg %s ± %s\n"
+                   (Data.to_string_exn row.(0))
+                   (Data.to_string_exn row.(1))
+                   (Duration.to_string (Data.to_int_exn row.(3)) 3)
+                   (Data.to_int_exn row.(2))
+                   (Duration.to_string
+                      (int_of_float (Data.to_float_exn row.(4)))
+                      3)
+                   (Duration.to_string
+                      (int_of_float (Data.to_float_exn row.(5)))
+                      3))))
+  | [| _; db_path; game; category; attempt |] ->
+      Storage.with_db db_path (fun db ->
+          let vals =
+            [
+              Data.TEXT game;
+              Data.TEXT category;
+              Data.opt_int (int_of_string_opt attempt);
+            ]
+          in
+          let stmt =
+            prepare db
+              "select starting_at, ending_at, duration from segments where \
+               game = ? and category = ? and attempt = ?;"
+          in
+          check_select stmt vals (fun row ->
+              print_string
+                (sprintf "Segment: %s, %s, %s\n"
+                   (Option.value (Data.to_string row.(0)) ~default:"(Start)")
+                   (Data.to_string_exn row.(1))
+                   (Duration.to_string (Data.to_int_exn row.(2)) 3))))
   | _ -> print_string usage

--- /dev/null
+++ b/src/storage.ml
@@ -0,0 +1,8 @@
+open Sqlite3
+
+let with_db db_path f =
+  let db = db_open db_path in
+  Rc.check (exec db "pragma foreign_keys = on;");
+  f db;
+  let _ = db_close db in
+  ()