3cb-data

commit 5dc33b7871de7a7273b7daebab02435c605472f3
parent ff630ec7a1242d8de71981fd6818defb8f873f22
Author: Alex Wennerberg <alex@Alexs-MacBook-Air.local>
Date:   Wed, 17 Dec 2025 15:41:22 -0800

fake "materialized view"

Diffstat:
M
app.py
 | 
22
++++++++++++++++++++++
M
schema.sql
 | 
1
-

2 files changed, 22 insertions(+), 1 deletion(-)
diff --git a/app.py b/app.py
@@ -219,5 +219,27 @@ def mana_symbols_filter(s):
 
     return result
 
+create_mview = """CREATE table round_score as
+select deck.round, deck.player,
+card1,
+card2,
+card3,
+min(match.group_name) as prelim_group,
+sum(case when match.group_name != 'final' then match.score else 0 end) as prelim_score,
+sum(case when match.group_name = 'final' then match.score else null end) as final_score,
+rank.rank,
+count(*) as num_players
+from deck join match
+on deck.round = match.round and deck.player = match.player
+join rank 
+on deck.round = rank.round and deck.player = rank.player
+group by 1,2
+"""
+
 if __name__ == '__main__':
+    # setup "materialized view"
+    db = get_db()
+    db.execute("drop table if exists round_score")
+    db.execute(create_mview)
+    db.close()
     app.run(debug=False)
diff --git a/schema.sql b/schema.sql
@@ -54,7 +54,6 @@ on deck.round = match.round and deck.player = match.player
 join rank 
 on deck.round = rank.round and deck.player = rank.player
 group by 1,2
-/* round_score(round,player,card1,card2,card3,prelim_group,prelim_score,final_score,rank,num_players) */;
 CREATE TABLE if not exists ban (name text);
 CREATE INDEX if not exists c1idx on deck(card1);
 CREATE INDEX if not exists c2idx on deck(card2);