みなさんこんにちは。LAPRASでデータベースエンジニアをしている@denzowillです。この記事はLAPRAS Advent Calendar 2019の22日目です。今日が12月の何日かは知りませんがこの記事は22日目です。
さて、15日目の記事としてMTGをハックする技術というマジックザギャザリングのAPIやデータについての記事を上げました。今回はそこで上げたうちの一つである、MTGJSONについて深堀していきます。
MTGJSONのファイル
MTGJSONではその名の通り、各種カードデータなどをJSONファイルとして配布していますが、それ以外にもSQLiteのファイルも配布しています。なお、SQLファイルも配布されていますがこれはSQLite用ですので、MySQLやPostgreSQLにインポートするためには書き換えが必要です。
今回はSQLiteファイルをダウンロードして遊んでみます。
https://mtgjson.com/downloads/all-files/
$ ls -lh AllPrintings.sqlite*
-rw-r--r-- 1 denzow denzow 500M 12月 24 16:05 AllPrintings.sqlite
-rw-rw-r-- 1 denzow denzow 89M 12月 25 19:51 AllPrintings.sqlite.zip
ダウンロードしたzipファイルが90M程度、展開すると500Mになりました。性質的に大きなデータを保持するカラムがあるわけではないのにこのサイズなのは歴史を感じさせますね。
このDBに存在するテーブルは8つです。(sqlite_sequence は内部テーブルなので除外)
AllPrintings.sqlite> select name from sqlite_master where type = 'table';
+------------------+
| name |
+------------------+
| sets |
| sqlite_sequence |
| cards |
| tokens |
| set_translations |
| foreign_data |
| legalities |
| rulings |
| prices |
+------------------+
9 rows in set
Time: 0.021s
ER図を起こすと以下のようになります。(cardテーブルのカラムが長くて見づらい…
sets には販売されたシリーズの情報が入っています。例えば、以下は最近発売された(される)エキスパンションや基本セットの一覧です。
AllPrintings.sqlite> select code, name, releaseDate from sets where type in ('expansion', 'core') order by releaseDate desc limit 20;
+------+------------------------+-------------+
| code | name | releaseDate |
+------+------------------------+-------------+
| THB | Theros Beyond Death | 2020-01-24 |
| ELD | Throne of Eldraine | 2019-10-04 |
| M20 | Core Set 2020 | 2019-07-12 |
| WAR | War of the Spark | 2019-05-03 |
| RNA | Ravnica Allegiance | 2019-01-25 |
| GRN | Guilds of Ravnica | 2018-10-05 |
| M19 | Core Set 2019 | 2018-07-13 |
| DOM | Dominaria | 2018-04-27 |
| RIX | Rivals of Ixalan | 2018-01-19 |
| XLN | Ixalan | 2017-09-29 |
| HOU | Hour of Devastation | 2017-07-14 |
| AKH | Amonkhet | 2017-04-28 |
| AER | Aether Revolt | 2017-01-20 |
| KLD | Kaladesh | 2016-09-30 |
| EMN | Eldritch Moon | 2016-07-22 |
| SOI | Shadows over Innistrad | 2016-04-08 |
| OGW | Oath of the Gatewatch | 2016-01-22 |
| BFZ | Battle for Zendikar | 2015-10-02 |
| ORI | Magic Origins | 2015-07-17 |
| DTK | Dragons of Tarkir | 2015-03-27 |
+------+------------------------+-------------+
1/24のTHBが待ち遠しいですね。
setsとcards間はsetCodeカラムで繋がっています。
AllPrintings.sqlite> select count(*) from cards where setCode = (select code from sets where name = 'Theros Beyond Death');
+----------+
| count(*) |
+----------+
| 51 |
+----------+
まぁ直接指定しても一緒ですので、あまり意識することはないかもしれません。
AllPrintings.sqlite> select count(*) from cards where setCode = 'THB';
+----------+
| count(*) |
+----------+
| 51 |
+----------+
いよいよcardsの中身を見ていきます。とにかく列が多い。
AllPrintings.sqlite> \d cards;
+-----+------------------------+----------+---------+------------+----+
| cid | name | type | notnull | dflt_value | pk |
+-----+------------------------+----------+---------+------------+----+
| 0 | id | INTEGER | 0 | <null> | 1 |
| 1 | artist | TEXT | 0 | <null> | 0 |
| 2 | borderColor | TEXT | 0 | <null> | 0 |
| 3 | colorIdentity | TEXT | 0 | <null> | 0 |
| 4 | colorIndicator | TEXT | 0 | <null> | 0 |
| 5 | colors | TEXT | 0 | <null> | 0 |
| 6 | convertedManaCost | FLOAT | 0 | <null> | 0 |
| 7 | duelDeck | TEXT(1) | 0 | <null> | 0 |
| 8 | edhrecRank | TEXT | 0 | <null> | 0 |
| 9 | faceConvertedManaCost | FLOAT | 0 | <null> | 0 |
| 10 | flavorText | TEXT | 0 | <null> | 0 |
| 11 | frameEffect | TEXT | 0 | <null> | 0 |
| 12 | frameEffects | TEXT | 0 | <null> | 0 |
| 13 | frameVersion | TEXT | 0 | <null> | 0 |
| 14 | hand | TEXT | 0 | <null> | 0 |
| 15 | hasFoil | INTEGER | 1 | 0 | 0 |
| 16 | hasNoDeckLimit | INTEGER | 1 | 0 | 0 |
| 17 | hasNonFoil | INTEGER | 1 | 0 | 0 |
| 18 | isAlternative | INTEGER | 1 | 0 | 0 |
| 19 | isArena | INTEGER | 1 | 0 | 0 |
| 20 | isFullArt | INTEGER | 1 | 0 | 0 |
| 21 | isMtgo | INTEGER | 1 | 0 | 0 |
| 22 | isOnlineOnly | INTEGER | 1 | 0 | 0 |
| 23 | isOversized | INTEGER | 1 | 0 | 0 |
| 24 | isPaper | INTEGER | 1 | 0 | 0 |
| 25 | isPromo | INTEGER | 1 | 0 | 0 |
| 26 | isReprint | INTEGER | 1 | 0 | 0 |
| 27 | isReserved | INTEGER | 1 | 0 | 0 |
| 28 | isStarter | INTEGER | 1 | 0 | 0 |
| 29 | isStorySpotlight | INTEGER | 1 | 0 | 0 |
| 30 | isTextless | INTEGER | 1 | 0 | 0 |
| 31 | isTimeshifted | INTEGER | 1 | 0 | 0 |
| 32 | layout | TEXT | 0 | <null> | 0 |
| 33 | leadershipSkills | TEXT | 0 | <null> | 0 |
| 34 | life | TEXT | 0 | <null> | 0 |
| 35 | loyalty | TEXT | 0 | <null> | 0 |
| 36 | manaCost | TEXT | 0 | <null> | 0 |
| 37 | mcmId | INTEGER | 0 | <null> | 0 |
| 38 | mcmMetaId | INTEGER | 0 | <null> | 0 |
| 39 | mcmName | TEXT | 0 | <null> | 0 |
| 40 | mtgArenaId | INTEGER | 0 | <null> | 0 |
| 41 | mtgoFoilId | INTEGER | 0 | <null> | 0 |
| 42 | mtgoId | INTEGER | 0 | <null> | 0 |
| 43 | mtgstocksId | INTEGER | 0 | <null> | 0 |
| 44 | multiverseId | INTEGER | 0 | <null> | 0 |
| 45 | name | TEXT | 0 | <null> | 0 |
| 46 | names | TEXT | 0 | <null> | 0 |
| 47 | number | TEXT | 0 | <null> | 0 |
| 48 | originalText | TEXT | 0 | <null> | 0 |
| 49 | originalType | TEXT | 0 | <null> | 0 |
| 50 | otherFaceIds | TEXT | 0 | <null> | 0 |
| 51 | power | TEXT | 0 | <null> | 0 |
| 52 | printings | TEXT | 0 | <null> | 0 |
| 53 | purchaseUrls | TEXT | 0 | <null> | 0 |
| 54 | rarity | TEXT | 0 | <null> | 0 |
| 55 | scryfallId | TEXT(36) | 0 | <null> | 0 |
| 56 | scryfallIllustrationId | TEXT(36) | 0 | <null> | 0 |
| 57 | scryfallOracleId | TEXT(36) | 0 | <null> | 0 |
| 58 | setCode | TEXT | 0 | <null> | 0 |
| 59 | side | TEXT | 0 | <null> | 0 |
| 60 | subtypes | TEXT | 0 | <null> | 0 |
| 61 | supertypes | TEXT | 0 | <null> | 0 |
| 62 | tcgplayerProductId | INTEGER | 0 | <null> | 0 |
| 63 | tcgplayerPurchaseUrl | TEXT | 0 | <null> | 0 |
| 64 | text | TEXT | 0 | <null> | 0 |
| 65 | toughness | TEXT | 0 | <null> | 0 |
| 66 | type | TEXT | 0 | <null> | 0 |
| 67 | types | TEXT | 0 | <null> | 0 |
| 68 | uuid | TEXT(36) | 1 | <null> | 0 |
| 69 | variations | TEXT | 0 | <null> | 0 |
| 70 | watermark | TEXT | 0 | <null> | 0 |
+-----+------------------------+----------+---------+------------+----+
isFullArtやらisTextless等、マジックザギャザリングでは同じカードでもデザイン違いもありますのでそのあたりをBooleanで表現しています。例えば稲妻/Lightning Boltを見てみます。
AllPrintings.sqlite> select sets.name, cards.name, `isFullArt` from cards join sets on cards.setCode = sets.code where cards.name = 'Lightning Bolt' ;
+-----------------------------------------+----------------+-----------+
| name | name | isFullArt |
+-----------------------------------------+----------------+-----------+
| Unlimited Edition | Lightning Bolt | 0 |
| Revised Edition | Lightning Bolt | 0 |
| Fourth Edition Foreign Black Border | Lightning Bolt | 0 |
| Fourth Edition | Lightning Bolt | 0 |
| Masters 25 | Lightning Bolt | 0 |
| Anthologies | Lightning Bolt | 0 |
| Beatdown Box Set | Lightning Bolt | 0 |
| Collectors’ Edition | Lightning Bolt | 0 |
| Intl. Collectors’ Edition | Lightning Bolt | 0 |
| Archenemy: Nicol Bolas | Lightning Bolt | 0 |
| Foreign Black Border | Lightning Bolt | 0 |
| Judge Gift Cards 1998 | Lightning Bolt | 0 |
| Limited Edition Alpha | Lightning Bolt | 0 |
| Limited Edition Beta | Lightning Bolt | 0 |
| Magic 2010 | Lightning Bolt | 0 |
| Magic 2011 | Lightning Bolt | 0 |
| Mystery Booster | Lightning Bolt | 0 |
| Masters Edition | Lightning Bolt | 0 |
| Modern Masters 2015 | Lightning Bolt | 0 |
| Magic Player Rewards 2010 | Lightning Bolt | 1 |
| Premium Deck Series: Fire and Lightning | Lightning Bolt | 0 |
| MagicFest 2019 | Lightning Bolt | 1 |
| Magic Online Promos | Lightning Bolt | 0 |
| Magic Online Promos | Lightning Bolt | 1 |
| Pro Tour Collector Set | Lightning Bolt | 0 |
| Pro Tour Collector Set | Lightning Bolt | 0 |
| Pro Tour Collector Set | Lightning Bolt | 0 |
| Summer Magic / Edgar | Lightning Bolt | 0 |
| Magic Online Theme Decks | Lightning Bolt | 0 |
| Magic Online Theme Decks | Lightning Bolt | 0 |
+-----------------------------------------+----------------+-----------+
MagicFest 2019 で配られたものなど、いくつかのセットではフルアート版が作成されていることがわかります。
また、エルドレインでパワーが5以上のクリーチャを見てみます。
AllPrintings.sqlite> select id ,name, frameEffect, power ||'/'||toughness stats from cards where setCode ='ELD' and power >= 5;
+-------+----------------------------+-------------+-------+
| id | name | frameEffect | stats |
+-------+----------------------------+-------------+-------+
| 15881 | Clackbridge Troll | extendedart | 8/8 |
| 15882 | Clackbridge Troll | <null> | 8/8 |
| 15928 | Faerie Formation | <null> | 5/4 |
| 15934 | Feasting Troll King | <null> | 7/6 |
| 15935 | Feasting Troll King | extendedart | 7/6 |
| 16024 | Kenrith, the Returned King | legendary | 5/5 |
| 16032 | Lochmere Serpent | <null> | 7/7 |
| 16033 | Lochmere Serpent | extendedart | 7/7 |
| 16042 | Lovestruck Beast | <null> | 5/5 |
| 16044 | Lovestruck Beast | showcase | 5/5 |
| 16114 | Prophet of the Peak | <null> | 5/5 |
| 16123 | Rampart Smasher | <null> | 5/5 |
| 16127 | Realm-Cloaked Giant | <null> | 7/7 |
| 16129 | Realm-Cloaked Giant | showcase | 7/7 |
| 16154 | Roving Keep | <null> | 5/7 |
| 16156 | Rowan's Stalwarts | <null> | 5/2 |
| 16170 | Shimmer Dragon | <null> | 5/6 |
| 16213 | Syr Gwyn, Hero of Ashvale | legendary | 5/5 |
| 16214 | Syr Konrad, the Grim | legendary | 5/4 |
| 16228 | Thorn Mammoth | <null> | 6/6 |
| 16241 | Tuinvale Treefolk | <null> | 6/5 |
| 16243 | Tuinvale Treefolk | showcase | 6/5 |
| 16246 | Vantress Gargoyle | extendedart | 5/4 |
| 16247 | Vantress Gargoyle | <null> | 5/4 |
+-------+----------------------------+-------------+-------+
24 rows in set
同じなめのカードがいくつか出てきていますが、これは拡張アート版を別カードとして扱っているためです。しかし、英語名はいまいちピンと気ません。英語がわからないので日本語でカード名を見たくなります。しかし、cardsには英語のデータしかありません。他言語の情報はforeign_dataに格納されています。cardsとforeign_data 間はcards.uuidとforeign_data.uuidで繋がっています。なので、先程の結果を日本語表記にする場合は以下の様になります。
AllPrintings.sqlite> select cards.id, foreign_data.name japanese, cards.name as english, frameEffect, power ||'/'||toughness stats from cards join foreign_data on cards.uuid = foreign_data.uuid where setCode ='ELD' and power >= 5 and lan
guage = 'Japanese';
+-------+----------------------------------+----------------------------+-------------+-------+
| id | japanese | english | frameEffect | stats |
+-------+----------------------------------+----------------------------+-------------+-------+
| 15882 | カタカタ橋のトロール | Clackbridge Troll | <null> | 8/8 |
| 15934 | 貪るトロールの王 | Feasting Troll King | <null> | 7/6 |
| 16024 | 帰還した王、ケンリス | Kenrith, the Returned King | legendary | 5/5 |
| 16032 | メア湖の海蛇 | Lochmere Serpent | <null> | 7/7 |
| 16042 | 恋煩いの野獣 | Lovestruck Beast | <null> | 5/5 |
| 16114 | 頂の預言者 | Prophet of the Peak | <null> | 5/5 |
| 16123 | 塁壁潰し | Rampart Smasher | <null> | 5/5 |
| 16127 | 王国まといの巨人 | Realm-Cloaked Giant | <null> | 7/7 |
| 16154 | さまよう砦 | Roving Keep | <null> | 5/7 |
| 16156 | ローアンの親衛隊 | Rowan's Stalwarts | <null> | 5/2 |
| 16214 | 厳格な者、コンラッド卿 | Syr Konrad, the Grim | legendary | 5/4 |
| 16241 | チューインベイルのツリーフォーク | Tuinvale Treefolk | <null> | 6/5 |
| 16247 | ヴァントレスのガーゴイル | Vantress Gargoyle | <null> | 5/4 |
+-------+----------------------------------+----------------------------+-------------+-------+
13 rows in set
結合し、foreign_data.languageにJapaneseを指定しました。これでカードがぱっとわかるようになります。しかし、先程の24行より結果の行数が減ってしまいました。少しSQLを書き換えてみます。
AllPrintings.sqlite> select cards.id, foreign_data.name japanese, cards.name as english, frameEffect, power ||'/'||toughness stats from cards left outer join (select * from foreign_data where language = 'Japanese') foreign_data on cards
.uuid = foreign_data.uuid where setCode ='ELD' and power >= 5;
+-------+----------------------------------+----------------------------+-------------+-------+
| id | japanese | english | frameEffect | stats |
+-------+----------------------------------+----------------------------+-------------+-------+
| 15881 | <null> | Clackbridge Troll | extendedart | 8/8 |
| 15882 | カタカタ橋のトロール | Clackbridge Troll | <null> | 8/8 |
| 15928 | <null> | Faerie Formation | <null> | 5/4 |
| 15934 | 貪るトロールの王 | Feasting Troll King | <null> | 7/6 |
| 15935 | <null> | Feasting Troll King | extendedart | 7/6 |
| 16024 | 帰還した王、ケンリス | Kenrith, the Returned King | legendary | 5/5 |
| 16032 | メア湖の海蛇 | Lochmere Serpent | <null> | 7/7 |
| 16033 | <null> | Lochmere Serpent | extendedart | 7/7 |
| 16042 | 恋煩いの野獣 | Lovestruck Beast | <null> | 5/5 |
| 16044 | <null> | Lovestruck Beast | showcase | 5/5 |
| 16114 | 頂の預言者 | Prophet of the Peak | <null> | 5/5 |
| 16123 | 塁壁潰し | Rampart Smasher | <null> | 5/5 |
| 16127 | 王国まといの巨人 | Realm-Cloaked Giant | <null> | 7/7 |
| 16129 | <null> | Realm-Cloaked Giant | showcase | 7/7 |
| 16154 | さまよう砦 | Roving Keep | <null> | 5/7 |
| 16156 | ローアンの親衛隊 | Rowan's Stalwarts | <null> | 5/2 |
| 16170 | <null> | Shimmer Dragon | <null> | 5/6 |
| 16213 | <null> | Syr Gwyn, Hero of Ashvale | legendary | 5/5 |
| 16214 | 厳格な者、コンラッド卿 | Syr Konrad, the Grim | legendary | 5/4 |
| 16228 | <null> | Thorn Mammoth | <null> | 6/6 |
| 16241 | チューインベイルのツリーフォーク | Tuinvale Treefolk | <null> | 6/5 |
| 16243 | <null> | Tuinvale Treefolk | showcase | 6/5 |
| 16246 | <null> | Vantress Gargoyle | extendedart | 5/4 |
| 16247 | ヴァントレスのガーゴイル | Vantress Gargoyle | <null> | 5/4 |
+-------+----------------------------------+----------------------------+-------------+-------+
24 rows in set
Time: 1.669s
拡張アート版のuuidに対応する翻訳データが用意されていないケース(Vantress Gargoyle|extendedart)と
Shimmer Dragon のように英語版しか存在しないケースで行が減っていました。このあたりはデータの取扱に注意が必要ですね。
もう少し遊んでみましょう。foreign_data にはちゃんとテキストやフレーバーも翻訳されたものが格納されています。そこで、現在スタンダード環境で使えるカードのうち、鹿をテキストに持つものがどの程度あるか見てみます。
AllPrintings.sqlite> select name, text from foreign_data join (select uuid from legalities where format = 'standard' and status = 'Legal') format on foreign_data.uuid = format.uuid where language = 'Japanese' and foreign_data.text like '%
鹿%' \G
***************************[ 1. row ]***************************
name | ケンリスの変身
text | エンチャント（クリーチャー）
ケンリスの変身が戦場に出たとき、カードを１枚引く。
エンチャントしているクリーチャーは能力をすべて失い、基本のパワーとタフネスが３/３の緑の大鹿・クリーチャーである。（それは他のすべてのカード・タイプとクリーチャー・タイプを失う。）
***************************[ 2. row ]***************************
name | 天上の大鹿
text | トランプル
天上の大鹿が戦場に出たとき、あなたはあなたのライブラリーやあなたの墓地から「自然の報復者、ビビアン」という名前のカード１枚を探し、公開してあなたの手札に加えてもよい。これによりあなたがあなたのライブラリーからカードを探したなら、あなたのライブラリーを切り直す。
ちゃんと結果がでました。意外と2枚しかないんですね。この間まで、めちゃめちゃ鹿がいたイメージがありましたが気の所為でした。
まとめ
つらつらとMTGJSON のスキーマ構造について書いてしまいました。誰にも刺さらない記事なきがしますが、結構遊びがいのあるデータなので、年末にでもこれを使った何かアプリやらBotやらを書いて見たいと思います。