app.py (7766B)
1 from flask import Flask, render_template, request, g 2 import sqlite3 3 from urllib.parse import quote as url_quote 4 import time 5 import logging 6 7 app = Flask(__name__) 8 9 logging.basicConfig( 10 level=logging.DEBUG, 11 format='%(asctime)s - %(name)s - %(levelname)s - %(message)s' 12 ) 13 logger = logging.getLogger(__name__) 14 15 @app.before_request 16 def before_request(): 17 g.start_time = time.time() 18 19 @app.after_request 20 def after_request(response): 21 if hasattr(g, 'start_time'): 22 duration_ms = (time.time() - g.start_time) * 1000 23 logger.info(f"{request.method} {request.path} - {response.status_code} - {duration_ms:.2f}ms") 24 return response 25 26 def get_db(): 27 db = sqlite3.connect('3cb.db') 28 db.row_factory = sqlite3.Row 29 db.set_trace_callback(lambda stmt: logger.debug(f"SQLite: {stmt}")) 30 return db 31 32 def execute_query(db, query, params=()): 33 """Execute a query with logging of query text and execution time.""" 34 start_time = time.time() 35 result = db.execute(query, params) 36 duration_ms = (time.time() - start_time) * 1000 37 38 # Format query for logging (remove extra whitespace/newlines) 39 formatted_query = ' '.join(query.split()) 40 logger.info(f"SQL Query ({duration_ms:.2f}ms): {formatted_query} | Params: {params}") 41 42 return result 43 44 @app.route('/') 45 def index(): 46 db = get_db() 47 rounds = db.execute("select r.id, r.fileid, r.date, count(distinct d.player) as player_count from round r left join deck d on r.id = d.round group by r.id, r.fileid, r.date order by r.id desc").fetchall() 48 db.close() 49 subtitle = "rounds" 50 return render_template('index.html', rounds=rounds, subtitle=subtitle) 51 52 @app.route('/round/<int:id>') 53 def round_detail(id): 54 db = get_db() 55 round_data = db.execute("select * from round where id = ?", (id,)).fetchone() 56 matches = db.execute(""" 57 select * 58 from round_score 59 where round = ? 60 order by final_score desc, prelim_score desc 61 """, (id,)).fetchall() 62 banned_cards = [row["name"] for row in db.execute("select name from ban").fetchall()] 63 db.close() 64 subtitle = "round " + str(id) 65 return render_template('matches.html', round=round_data, matches=matches, banned_cards=banned_cards, subtitle=subtitle) 66 67 @app.route('/card') 68 def card(): 69 name = request.args.get('name') 70 db = get_db() 71 matches = db.execute(""" 72 select * 73 from round_score 74 where card1 = ?1 or card2 = ?1 or card3 = ?1 75 order by round desc 76 """, (name,)).fetchall() 77 banned_cards = [row["name"] for row in db.execute("select name from ban").fetchall()] 78 db.close() 79 subtitle = name 80 return render_template('matches.html', matches=matches, banned_cards=banned_cards, subtitle=subtitle) 81 82 @app.route('/player') 83 def player(): 84 name = request.args.get('name') 85 db = get_db() 86 matches = db.execute(""" 87 select * 88 from round_score 89 where player = ? 90 order by round desc 91 """, (name,)).fetchall() 92 banned_cards = [row["name"] for row in db.execute("select name from ban").fetchall()] 93 db.close() 94 subtitle = "player " + name 95 return render_template('matches.html', matches=matches, banned_cards=banned_cards, subtitle=subtitle) 96 97 @app.route('/deck') 98 def deck(): 99 cards = sorted([request.args.get('c1'), request.args.get('c2'), request.args.get('c3')]) 100 db = get_db() 101 matches = db.execute(""" 102 select * 103 from round_score 104 where 105 card1 = ?1 and card2 = ?2 and card3 = ?3 or 106 card1 = ?1 and card2 = ?3 and card3 = ?2 or 107 card1 = ?2 and card2 = ?1 and card3 = ?3 or 108 card1 = ?2 and card2 = ?3 and card3 = ?1 or 109 card1 = ?3 and card2 = ?1 and card3 = ?2 or 110 card1 = ?3 and card2 = ?2 and card3 = ?1 111 order by round desc 112 """, (cards[0], cards[1], cards[2])).fetchall() 113 banned_cards = [row["name"] for row in db.execute("select name from ban").fetchall()] 114 db.close() 115 subtitle = "; ".join(cards) 116 return render_template('matches.html', matches=matches, banned_cards=banned_cards, subtitle=subtitle) 117 118 @app.route('/players') 119 def players(): 120 db = get_db() 121 players = db.execute(""" 122 select 123 d.player, 124 count(distinct d.round) as rounds_played, 125 sum(case when r.rank = 1 then 1 else 0 end) as rounds_won, 126 min(ro.date) as first_played 127 from deck d 128 left join rank r on d.round = r.round and d.player = r.player 129 left join round ro on d.round = ro.id 130 group by d.player 131 order by rounds_played desc 132 """).fetchall() 133 db.close() 134 subtitle = "players" 135 return render_template('players.html', players=players, subtitle=subtitle) 136 137 @app.route('/banned') 138 def banned(): 139 db = get_db() 140 banned_cards = db.execute("select name from ban order by name").fetchall() 141 db.close() 142 subtitle = "banned cards" 143 return render_template('banned.html', banned_cards=banned_cards, subtitle=subtitle) 144 145 @app.route('/cards') 146 def cards(): 147 db = get_db() 148 cards = db.execute(""" 149 SELECT card, COUNT(*) as play_count 150 FROM card 151 WHERE card IS NOT NULL 152 GROUP BY card 153 ORDER BY play_count DESC, card ASC 154 """).fetchall() 155 banned_cards = [row["name"] for row in db.execute("select name from ban").fetchall()] 156 db.close() 157 subtitle = "all cards" 158 return render_template('cards.html', cards=cards, banned_cards=banned_cards, subtitle=subtitle) 159 160 # Template filter for url encoding 161 @app.template_filter('url_encode') 162 def url_encode_filter(s): 163 return url_quote(str(s)) 164 165 # Template filter for mana symbols using Mana font 166 @app.template_filter('mana_symbols') 167 def mana_symbols_filter(s): 168 if not s or s == 'N/A': 169 return s 170 171 import re 172 173 # Parse mana symbols like {W}, {U}, {1}, {2/U}, etc. 174 def replace_symbol(match): 175 symbol = match.group(1).lower() 176 177 # Handle hybrid mana (e.g., {W/U}, {2/W}) 178 if '/' in symbol: 179 parts = symbol.split('/') 180 # Check if first part is a number (e.g., 2/W for hybrid) 181 if parts[0].isdigit(): 182 return f'<i class="ms ms-{parts[1]} ms-cost ms-shadow"></i>' 183 else: 184 return f'<i class="ms ms-{parts[0]}{parts[1]} ms-cost ms-shadow"></i>' 185 # Handle Phyrexian mana (e.g., {W/P}) 186 elif 'p' in symbol: 187 color = symbol.replace('/p', '').replace('p', '') 188 return f'<i class="ms ms-{color} ms-p ms-cost ms-shadow"></i>' 189 # Regular mana 190 else: 191 return f'<i class="ms ms-{symbol} ms-cost ms-shadow"></i>' 192 193 # Replace all mana symbols 194 result = re.sub(r'\{([^}]+)\}', replace_symbol, s) 195 196 return result 197 198 create_mview = """CREATE table round_score as 199 select deck.round, deck.player, 200 card1, 201 card2, 202 card3, 203 m1.cost as card1_cost, 204 m1.card_type as card1_type, 205 m2.cost as card2_cost, 206 m2.card_type as card2_type, 207 m3.cost as card3_cost, 208 m3.card_type as card3_type, 209 min(match.group_name) as prelim_group, 210 sum(case when match.group_name != 'final' then match.score else 0 end) as prelim_score, 211 sum(case when match.group_name = 'final' then match.score else null end) as final_score, 212 rank.rank, 213 count(*) as num_players 214 from deck join match 215 on deck.round = match.round and deck.player = match.player 216 join rank 217 on deck.round = rank.round and deck.player = rank.player 218 left join mtg m1 on deck.card1 = m1.name 219 left join mtg m2 on deck.card2 = m2.name 220 left join mtg m3 on deck.card3 = m3.name 221 group by 1,2 222 """ 223 224 def init_db(): 225 """Initialize the materialized view""" 226 db = get_db() 227 db.execute("drop table if exists round_score") 228 db.execute(create_mview) 229 db.close() 230 231 if __name__ == '__main__': 232 # setup "materialized view" 233 init_db() 234 app.run(debug=False)