commit 3ac5a213ea481d1291168520780e2f8becd54b8e
parent f7cd2d7cb698e4aa0dbd2e90c1538d87b083d439
Author: alex wennerberg <alex@alexwennerberg.com>
Date: Fri, 7 Feb 2025 16:49:50 -0500
ruby rewrite
Diffstat:
21 files changed, 359 insertions(+), 171 deletions(-)
diff --git a/Gemfile.lock b/Gemfile.lock
@@ -0,0 +1,59 @@
+GEM
+ remote: https://rubygems.org/
+ specs:
+ base64 (0.2.0)
+ logger (1.6.4)
+ mustermann (3.0.3)
+ ruby2_keywords (~> 0.0.1)
+ nio4r (2.7.4)
+ puma (6.5.0)
+ nio4r (~> 2.0)
+ rack (3.1.8)
+ rack-protection (4.1.1)
+ base64 (>= 0.1.0)
+ logger (>= 1.6.0)
+ rack (>= 3.0.0, < 4)
+ rack-session (2.0.0)
+ rack (>= 3.0.0)
+ rackup (2.2.1)
+ rack (>= 3)
+ ruby2_keywords (0.0.5)
+ sinatra (4.1.1)
+ logger (>= 1.6.0)
+ mustermann (~> 3.0)
+ rack (>= 3.0.0, < 4)
+ rack-protection (= 4.1.1)
+ rack-session (>= 2.0.0, < 3)
+ tilt (~> 2.0)
+ sqlite3 (2.4.1-aarch64-linux-gnu)
+ sqlite3 (2.4.1-aarch64-linux-musl)
+ sqlite3 (2.4.1-arm-linux-gnu)
+ sqlite3 (2.4.1-arm-linux-musl)
+ sqlite3 (2.4.1-arm64-darwin)
+ sqlite3 (2.4.1-x86-linux-gnu)
+ sqlite3 (2.4.1-x86-linux-musl)
+ sqlite3 (2.4.1-x86_64-darwin)
+ sqlite3 (2.4.1-x86_64-linux-gnu)
+ sqlite3 (2.4.1-x86_64-linux-musl)
+ tilt (2.5.0)
+
+PLATFORMS
+ aarch64-linux-gnu
+ aarch64-linux-musl
+ arm-linux-gnu
+ arm-linux-musl
+ arm64-darwin
+ x86-linux-gnu
+ x86-linux-musl
+ x86_64-darwin
+ x86_64-linux-gnu
+ x86_64-linux-musl
+
+DEPENDENCIES
+ puma
+ rackup
+ sinatra
+ sqlite3
+
+BUNDLED WITH
+ 2.5.9
diff --git a/README.md b/README.md
@@ -5,15 +5,10 @@ Utilities for pulling data from 3 card blind metashape:
https://sites.google.com/view/3cb-metashape/home
-Dependencies:
- - python
- - sqlite
+(Working on Ruby rewrite)
Pull all cards from mtgjson https://mtgjson.com/downloads/all-files/
wget https://mtgjson.com/api/v5/AtomicCards.json
cat AtomicCards.json | jq -r '.data | keys[]' > allcards.txt
-
-Then:
- python3 getdata.py
diff --git a/buildpage.py b/buildpage.py
@@ -1,33 +0,0 @@
-import sqlite3,urllib
-from jinja2 import Environment, PackageLoader, select_autoescape
-
-con = sqlite3.connect("3cb.db")
-env = Environment(
- loader=PackageLoader("buildpage"),
- autoescape=select_autoescape()
- )
-
-def card_link(card):
- return f"https://scryfall.com/search?q={urllib.parse.quote_plus(card)}"
-
-env.filters['card_link'] = card_link
-env.filters['zip'] = zip
-
-def rows_to_dict(res):
- desc = res.description
- column_names = [col[0] for col in desc]
- return [dict(zip(column_names, row)) for row in res.fetchall()]
-
-def export_html():
- with open("sql/export.sql") as f:
- query = f.read()
- res = con.cursor().execute(query)
- decks = rows_to_dict(res)
- template = env.get_template("index.html")
- print(template.render(decks=decks))
-
-if __name__ == "__main__":
- export_html()
-
-
-
diff --git a/main.rb b/main.rb
@@ -0,0 +1,63 @@
+require 'sinatra'
+require 'sqlite3'
+require 'erb'
+include ERB::Util
+
+db = SQLite3::Database.new "3cb.db"
+db.results_as_hash = true
+set :server_settings, :timeout => 5
+
+configure :production do
+ require 'prometheus/middleware/collector'
+ require 'prometheus/middleware/exporter'
+ use Rack::Deflater
+ use Prometheus::Middleware::Collector
+ use Prometheus::Middleware::Exporter
+end
+
+
+get '/' do
+ @rounds = db.execute "select * from round order by id desc"
+ @subtitle = "rounds"
+ erb :index
+end
+
+get '/round/:id' do |id|
+ @round = db.execute("select * from round where id = ?", id)[0]
+ @matches = db.execute "select * from round_score where round = ? order by final_score desc,prelim_score desc", id
+ @subtitle = "round " + id
+ erb :matches
+end
+
+get '/card' do
+ name = params[:name]
+ @matches = db.execute "select * from round_score where
+ card1 = ?1 or
+ card2 = ?1 or
+ card3 = ?1 order by round desc", name
+ @subtitle = name
+ erb :matches
+end
+
+get '/player' do
+ name = params[:name]
+ @matches = db.execute "select * from round_score where player = ? order by round desc", name
+ @subtitle = "player " + name
+ erb :matches # rename round -> matches
+end
+
+get '/deck' do
+ cards = [params[:c1], params[:c2], params[:c3]].sort
+ # i am lazy
+ @matches = db.execute("
+ select * from round_score where
+ card1 = ?1 and card2 = ?2 and card3 = ?3 or
+ card1 = ?1 and card2 = ?3 and card3 = ?2 or
+ card1 = ?2 and card2 = ?1 and card3 = ?3 or
+ card1 = ?2 and card2 = ?3 and card3 = ?1 or
+ card1 = ?3 and card2 = ?1 and card3 = ?2 or
+ card1 = ?3 and card2 = ?2 and card3 = ?1
+ order by round desc", [cards[0], cards[1], cards[2]])
+ @subtitle = cards.join("; ")
+ erb :matches
+end
diff --git a/public/style.css b/public/style.css
@@ -0,0 +1,75 @@
+:root {
+ --text: #000;
+ --link: #00a;
+}
+
+body {
+ font-size:16px;
+ background: var(--bg);
+ color: var(--text);
+ margin: 0;
+ font-family: Courier, monospace;
+ word-wrap: break-word;
+ line-height: 1.5;
+}
+
+.flextable {
+ display: flex;
+ flex-direction: row;
+ flex-wrap: wrap;
+}
+
+img {
+ margin: 0;
+}
+
+.deck-box {
+ display: flex;
+ column-gap: 0.5rem;
+ flex-direction: row;
+ justify-content: space-between;
+ margin: 0.2rem;
+ padding: 0.2rem;
+ background: #eee;
+}
+
+@media only screen and (max-width: 767px) {
+ .deck-box {
+ flex-direction: column;
+ }
+}
+
+header {
+ font-size:1.4rem;
+ padding-left: 0.3rem;
+ border-bottom: 1px solid black;
+}
+footer {
+ border-top: 1px solid black;
+ text-align:right;
+}
+
+main {
+ padding: 0.3rem;
+}
+
+a {
+ color: var(--link);
+}
+a:hover {
+ text-decoration: none;
+}
+.hover_img a { position:relative; }
+.hover_img a span { position:absolute; display:none; z-index:99; }
+.hover_img a:hover span { display:block; }
+
+@media only screen and (max-width: 600px) {
+ .hide-mobile {
+ display:none;
+ }
+ body {
+ font-size: 16px;
+ border: 0px;
+ }
+}
+
diff --git a/requirements.txt b/requirements.txt
@@ -1,5 +0,0 @@
-requests
-titlecase
-openpyxl
-Levenshtein
-jinja2
diff --git a/runround.sh b/runround.sh
@@ -3,5 +3,5 @@ python getdata.py
sqlite3 3cb.db < sql/new.sql
sqlite3 3cb.db < sql/export.sql > _site/export.tsv
python3 buildpage.py > _site/index.html
-rsync -r _site/ root@pi:/var/www/data.3cardmagic.org
+rsync -r _site/ alpine@fishbb.org:/var/www/data.3cardmagic.org
diff --git a/schema.sql b/schema.sql
@@ -0,0 +1,54 @@
+CREATE TABLE round (
+ id integer primary key,
+ fileid text not null
+);
+CREATE TABLE deck (
+ round integer not null,
+ player varchar not null,
+ card1 text,
+ card2 text,
+ card3 text
+);
+CREATE TABLE match (
+ round integer,
+ group_name text,
+ player text,
+ opp_player text,
+ score integer
+);
+CREATE VIEW card as
+ select round,player,card1 as card from deck
+ union select round,player,card2 as card from deck
+ union select round,player,card3 as card from deck
+/* card(round,player,card) */;
+CREATE INDEX matchidx on match(round);
+CREATE INDEX deckidx on deck(round);
+CREATE INDEX matchidx2 on match(player);
+CREATE INDEX deckidx2 on deck(player);
+CREATE VIEW rank as
+select round,player,rank() over (partition by round order by
+sum(case when match.group_name = 'final' then match.score else null end) desc nulls last,
+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 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
+/* round_score(round,player,card1,card2,card3,prelim_group,prelim_score,final_score,rank,num_players) */;
+CREATE TABLE ban (name text);
+CREATE INDEX c1idx on deck(card1);
+CREATE INDEX c2idx on deck(card2);
+CREATE INDEX c3idx on deck(card3);
diff --git a/sql/delete-last.sql b/sql/delete-last.sql
@@ -1,3 +1,3 @@
-delete from round where id > 68;
-delete from deck where round > 68;
-delete from match where round > 68;
+delete from round where id > 72;
+delete from deck where round > 72;
+delete from match where round > 72;
diff --git a/sql/export.sql b/sql/export.sql
@@ -1,35 +0,0 @@
-/* best-per-round */
-/* .headers off */
-/* .mode csv */
-/* .mode list */
-/* .separator "\t" */
-
-with firsts as (
- select card,min(round) as first_round from card
- group by 1
-),
-
-summary as (select deck.round,deck.player,
-card1,
-card2,
-card3,
-f1.card is not null as card1new,
-f2.card is not null as card2new,
-f3.card is not null as card3new,
-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
-from deck join match
-on deck.round = match.round and deck.player = match.player
-left join firsts f1
-on f1.card = deck.card1
-and f1.first_round = deck.round
-left join firsts f2
-on f2.card = deck.card2
-and f2.first_round = deck.round
-left join firsts f3
-on f3.card = deck.card3
-and f3.first_round = deck.round
-group by 1,2 order by 1 desc, prelim_group,2)
-
-select * from summary order by round desc,final_score desc,prelim_score desc,prelim_group,player;
diff --git a/sql/players.sql b/sql/players.sql
@@ -1,26 +0,0 @@
-/* TODO slightly off -- jsut sums finals and original together */
-with rank as (
-select round,player, percent_rank() over (
- partition by round
- order by sum(score)
-) as rank from match
-group by 1,2 order by 1
-),
-
-new as (
-select min(card.round) as rnd, card, card.player
-from match join card on match.round = card.round and match.player = card.player
-group by 2 order by 1),
-
-newcnt as (select player,count(1) as count from new group by 1),
-/* select * from newcnt; */
-/* TODO -- median percentile, not mean */
-allpl as (
-select deck.player, count(1) as cnt, printf('%.2f', avg(rank.rank)) as avg_rank,
-group_concat(printf('%.2f', rank.rank), ',') as ranks
-from deck
-join rank on deck.round = rank.round
-and deck.player = rank.player
-group by 1 order by 3 desc)
-
-select * from allpl where cnt > 10;
diff --git a/sql/topdecks.sql b/sql/topdecks.sql
@@ -1,13 +0,0 @@
-select deck.card1, deck.card2, deck.card3, cast(avg(score) * 100 as int) / 100.0 as avgscore, count(1) from
-deck
-join match on deck.round = match.round and deck.player = match.player
-join deck d2 on deck.round = d2.round and match.opp_player = d2.player
-left join ban on deck.card1 = ban.name
-left join ban b2 on deck.card2 = b2.name
-left join ban b3 on deck.card3 = b3.name
-where d2.player is not null
-and (ban.name is null and b2.name is null and b3.name is null)
-group by 1
-having count(1) > 30
-order by avgscore desc
-limit 5;
diff --git a/templates/index.html b/templates/index.html
@@ -1,49 +0,0 @@
-<head>
- <meta charset="utf-8">
-<style>
-table { font-family: Arial, Helvetica, sans-serif; border-collapse: collapse; width: 100%; }
-td, th { border: 1px solid #ddd; padding: 8px; }
-tr:nth-child(even){background-color: #f2f2f2;}
-tr:hover {background-color: #ddd;}
-#th { padding-top: 12px; padding-bottom: 12px; text-align: left; background-color: #04AA6D; color: white; }
-.hover_img a { position:relative; }
-.hover_img a span { position:absolute; display:none; z-index:99; }
-.hover_img a:hover span { display:block; }
-</style>
-<title>3 card blind data analysis</title>
-<script src="sorttable.js"></script>
-</head>
-<body>
- <h1>3 Card Blind Data Analysis</h1>
- Full, cleaned data for <a href="//3cardmagic.org">3 card blind metashape</a>. Source code can be found <a href="https://git.sr.ht/~aw/3cb-data">here</a>. <a href="export.tsv">Download</a> an export as tsv-formatted data.<br>
- Maintained by aw on the 3cb discord. Tag him for questions/comments/feature requests.<br>
- <b>Bold</b> represents that that round is the first time this card was played.
- <p>Click on a header to sort by that value.</p>
- <table class="sortable">
- <th><td>player</td><td>card 1</td><td>card 2</td>
- <td>card 3</td><td>group</td><td>score</td><td>final</td>
- </th>
- {% for deck in decks %}
- <tr>
- <td>{{deck.round}}</td>
- <td>{{deck.player}}</td>
- {% set cards = [deck.card1, deck.card2, deck.card3] %}
- {% set new = [deck.card1new, deck.card2new, deck.card3new] %}
- {% for card, new in cards|zip(new) %}
- <td>
- <div class="hover_img">
- <a href="https://scryfall.com/search?q={{card}}">
- {% if new %}<b>{{card}}</b>{% else %}{{card}}{% endif %}
- <span>
- <img loading=lazy src="https://api.scryfall.com/cards/named?exact={{card}}&format=image&version=small" /></span></a>
- </div>
- </td>
- {% endfor %}
- <td>{{deck.prelim_group}}</td>
- <td>{{deck.prelim_score}}</td>
- <td>{{deck.final_score or ""}}</td>
- </tr>
- {% endfor %}
- </table>
-</body>
-</html>
diff --git a/views/card.erb b/views/card.erb
@@ -0,0 +1,3 @@
+<%= erb :header %>
+<%= erb :footer%>
+
diff --git a/views/footer.erb b/views/footer.erb
@@ -0,0 +1,6 @@
+<footer>
+ Made by aw for <a href="https://sites.google.com/view/3cb-metashape/home">3 Card Blind Metashape</a>. <a href="https://git.sr.ht/~aw/3cb-data">Source</a>
+</footer>
+</main>
+</body>
+</html>
diff --git a/views/header.erb b/views/header.erb
@@ -0,0 +1,14 @@
+<html>
+<html dir="ltr" lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1" />
+<meta name="description" content="Data analysis for 3 card blind metashape" />
+<link rel="stylesheet" type="text/css" href="/style.css">
+<title>3 card blind data analysis</title>
+</head>
+<body>
+ <header>
+ <b><a href="/">3cb-data</a></b> > <b><%= @subtitle %></b>
+ </header>
+<main>
diff --git a/views/index.erb b/views/index.erb
@@ -0,0 +1,9 @@
+<%= erb :header %>
+<div class="flextable">
+<% @rounds.each do |round| %>
+ <div class="flextable-item">
+ <a href="/round/<%= round["id"] %>"><%= round["id"].to_s.rjust(2, "0") %></a>
+ </div>
+ <% end %>
+</div>
+<%= erb :footer %>
diff --git a/views/matches.erb b/views/matches.erb
@@ -0,0 +1,24 @@
+<%= erb :header %>
+ <% if @round %>
+ <a href='https://docs.google.com/spreadsheets/d/<%= @round["fileid"] %>/'>Google Sheet</a><% end %>
+<div class="flextable">
+ <% @matches.each do |match| %>
+ <div class="deck-box"
+ <% if match["rank"] == 1 %> style="background:#c5ffc5" <% elsif match["final_score"] %> style="background:#ffffc5"<% end %>>
+ <div style="width:160px">
+ <b><a href='/player?name=<%= match["player"] %>'><%= match["player"] %></a></b><br>
+ <a href="/round/<%= match["round"]%>">Round <%= match["round"] %></a><br>
+ Rank: <%= match["rank"] %><br>
+ Groups<%= match["group_name"] %>: <%= match["prelim_score"] %>
+<% if match["final_score"] %><br>
+ Finals: <%= match["final_score"] %>
+<% end %><br>
+ <a href="/deck?c1=<%= match["card1"] %>&c2=<%= match["card2"] %>&c3=<%= match["card3"]%>">deck</a><br>
+</div>
+<div>
+ <% [match["card1"], match["card2"], match["card3"]].each do |card| %><a href="/card?name=<%= card %>"><img alt='<%= card %>' width=146 height=204px loading=lazy src="https://api.scryfall.com/cards/named?exact=<%= url_encode(card) %>&format=image&version=medium" /></a><% end %>
+</div>
+</div>
+<% end %>
+</div>
+<%= erb :footer %>
diff --git a/views/player.erb b/views/player.erb
@@ -0,0 +1,15 @@
+<%= erb :header %>
+Submissions:
+<table>
+<% @matches.each do |match| %>
+ <tr>
+ <td><%= match["round"] %></td>
+ <td><%= match["prelim_score"] %></td>
+ <td><%= match["final_score"] %></td>
+ <td><%= match["card1"] %></td>
+ <td><%= match["card2"] %></td>
+ <td><%= match["card3"] %></td>
+ </tr>
+<% end %>
+</table>
+<%= erb :footer%>
diff --git a/views/round.erb b/views/round.erb
@@ -0,0 +1,21 @@
+<%= erb :header %>
+ <% if @round %>
+ <a href='https://docs.google.com/spreadsheets/d/<%= @round["fileid"] %>/'>Google Sheet</a><% end %>
+<div class="flextable">
+ <% @matches.each do |match| %>
+ <div class="deck-box"
+ <% if match["final_score"] %> style="background:#ffffc5"<% end %>>
+ <div style="width:160px">
+ <b><a href='/player/<%= match["player"] %>'><%= match["player"] %></a></b><br>
+ <a href="/round/<%= match["round"]%>"><%= match["round"] %></a>
+ <a href="/deck?card=<%= match["card1"] %>&card=<%= match["card2"] %>card= <%= match["card3"]%>>deck</a><br>
+ Group <%= match["prelim_group"] %>: <%=match["prelim_score"]%><br>
+ <% if match["final_score"] %>Finals: <%= match["final_score"] %><% end %>
+</div>
+<div>
+ <% [match["card1"], match["card2"], match["card3"]].each do |card| %><img alt='<%= card %>' width=146height=204px loading=lazy src="https://api.scryfall.com/cards/named?exact=<%= card %>&format=image&version=small" /><% end %>
+</div>
+</div>
+<% end %>
+</div>
+<%= erb :footer %>
diff --git a/views/submission.erb b/views/submission.erb
@@ -0,0 +1,11 @@
+<div class="deck-match">
+ <div>
+ playername<br>
+ Group A: 43<br>
+ Finals: 10<br>
+ </div>
+ <div>
+ card123
+ deck link
+ </div>
+</div>
