3cb-data

commit 491792f4141c5ccc41b2af31ae2dc20cb6064117
parent 5dc33b7871de7a7273b7daebab02435c605472f3
Author: Alex Wennerberg <alex@Alexs-MacBook-Air.local>
Date:   Wed, 17 Dec 2025 16:14:37 -0800

Optimize those queries!

Diffstat:
M
app.py
 | 
51
++++++++++++++++++---------------------------------
M
schema.sql
 | 
15
---------------

2 files changed, 18 insertions(+), 48 deletions(-)
diff --git a/app.py b/app.py
@@ -54,14 +54,8 @@ def round_detail(id):
     db = get_db()
     round_data = db.execute("select * from round where id = ?", (id,)).fetchone()
     matches = db.execute("""
-        select rs.*,
-               m1.cost as card1_cost, m1.card_type as card1_type,
-               m2.cost as card2_cost, m2.card_type as card2_type,
-               m3.cost as card3_cost, m3.card_type as card3_type
-        from round_score rs
-        left join mtg m1 on rs.card1 = m1.name
-        left join mtg m2 on rs.card2 = m2.name
-        left join mtg m3 on rs.card3 = m3.name
+        select *
+        from round_score
         where round = ?
         order by final_score desc, prelim_score desc
     """, (id,)).fetchall()
@@ -75,14 +69,8 @@ def card():
     name = request.args.get('name')
     db = get_db()
     matches = db.execute("""
-        select rs.*,
-               m1.cost as card1_cost, m1.card_type as card1_type,
-               m2.cost as card2_cost, m2.card_type as card2_type,
-               m3.cost as card3_cost, m3.card_type as card3_type
-        from round_score rs
-        left join mtg m1 on rs.card1 = m1.name
-        left join mtg m2 on rs.card2 = m2.name
-        left join mtg m3 on rs.card3 = m3.name
+        select *
+        from round_score
         where card1 = ?1 or card2 = ?1 or card3 = ?1
         order by round desc
     """, (name,)).fetchall()
@@ -96,14 +84,8 @@ def player():
     name = request.args.get('name')
     db = get_db()
     matches = db.execute("""
-        select rs.*,
-               m1.cost as card1_cost, m1.card_type as card1_type,
-               m2.cost as card2_cost, m2.card_type as card2_type,
-               m3.cost as card3_cost, m3.card_type as card3_type
-        from round_score rs
-        left join mtg m1 on rs.card1 = m1.name
-        left join mtg m2 on rs.card2 = m2.name
-        left join mtg m3 on rs.card3 = m3.name
+        select *
+        from round_score
         where player = ?
         order by round desc
     """, (name,)).fetchall()
@@ -117,14 +99,8 @@ def deck():
     cards = sorted([request.args.get('c1'), request.args.get('c2'), request.args.get('c3')])
     db = get_db()
     matches = db.execute("""
-        select rs.*,
-               m1.cost as card1_cost, m1.card_type as card1_type,
-               m2.cost as card2_cost, m2.card_type as card2_type,
-               m3.cost as card3_cost, m3.card_type as card3_type
-        from round_score rs
-        left join mtg m1 on rs.card1 = m1.name
-        left join mtg m2 on rs.card2 = m2.name
-        left join mtg m3 on rs.card3 = m3.name
+        select *
+        from round_score
         where
         card1 = ?1 and card2 = ?2 and card3 = ?3 or
         card1 = ?1 and card2 = ?3 and card3 = ?2 or
@@ -224,6 +200,12 @@ select deck.round, deck.player,
 card1,
 card2,
 card3,
+m1.cost as card1_cost,
+m1.card_type as card1_type,
+m2.cost as card2_cost,
+m2.card_type as card2_type,
+m3.cost as card3_cost,
+m3.card_type as card3_type,
 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,
@@ -231,8 +213,11 @@ rank.rank,
 count(*) as num_players
 from deck join match
 on deck.round = match.round and deck.player = match.player
-join rank 
+join rank
 on deck.round = rank.round and deck.player = rank.player
+left join mtg m1 on deck.card1 = m1.name
+left join mtg m2 on deck.card2 = m2.name
+left join mtg m3 on deck.card3 = m3.name
 group by 1,2
 """
 
diff --git a/schema.sql b/schema.sql
@@ -39,21 +39,6 @@ sum(case when match.group_name != 'final' then match.score else null end) desc
 ) as rank 
 from match group by 1,2 order by 3 desc
 /* rank(round,player,rank) */;
-CREATE VIEW if not exists 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
 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);