cedh-analytics/schema.sql at main · cococov/cedh-analytics · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
CREATE TABLE cards (
  card_name                       varchar(128) UNIQUE,
  card_faces                      jsonb,
  color_identity                  varchar(6),
  colors                          varchar(6),
  cmc                             int,
  prices                          jsonb,
  reserved                        bool,
  multiple_printings              bool,
  last_print                      varchar(128),
  multiverse_ids                  jsonb,
  type                            varchar(128),
  type_line                       varchar(128),
  power                           int,
  toughness                       int,
  PRIMARY KEY (card_name)
);

CREATE TABLE metagame_cards (
  occurrences                     int,
  card_name                       varchar(128) UNIQUE,
  decklists                       jsonb,
  is_commander                    bool,
  is_in_99                        bool,
  is_legal                        bool,
  percentage_of_use               decimal(5,2),
  percentage_of_use_by_identity   decimal(5,2),
  avg_win_rate                    decimal(6,4),
  avg_draw_rate                   decimal(6,4),
  PRIMARY KEY (card_name),
  CONSTRAINT fk_card FOREIGN KEY(card_name) REFERENCES cards(card_name)
);

CREATE TABLE db_cards (
  occurrences                     int,
  card_name                       varchar(128) UNIQUE,
  decklists                       jsonb,
  is_commander                    bool,
  is_in_99                        bool,
  is_legal                        bool,
  percentage_of_use               decimal(5,2),
  percentage_of_use_by_identity   decimal(5,2),
  PRIMARY KEY (card_name),
  CONSTRAINT fk_card FOREIGN KEY(card_name) REFERENCES cards(card_name)
);

CREATE TABLE tags_by_card (
  card_name                       varchar(128) UNIQUE,
  tags                            varchar(850),
  PRIMARY KEY (card_name)
);


CREATE TABLE commander_cards (
  commander                       varchar(128),
  occurrences                     int,
  card_name                       varchar(128),
  decklists                       jsonb,
  is_commander                    bool,
  is_in_99                        bool,
  is_legal                        bool,
  percentage_of_use               decimal(5,2),
  percentage_of_use_by_identity   decimal(5,2),
  avg_win_rate                    decimal(6,4),
  avg_draw_rate                   decimal(6,4),
  PRIMARY KEY (card_name, commander),
  UNIQUE (card_name, commander),
  CONSTRAINT fk_card FOREIGN KEY(card_name) REFERENCES cards(card_name)
);

CREATE TABLE tournament_cards (
  tournament                      varchar(128),
  occurrences                     int,
  card_name                       varchar(128),
  decklists                       jsonb,
  is_commander                    bool,
  is_in_99                        bool,
  is_legal                        bool,
  percentage_of_use               decimal(5,2),
  percentage_of_use_by_identity   decimal(5,2),
  avg_win_rate                    decimal(6,4),
  avg_draw_rate                   decimal(6,4),
  PRIMARY KEY (card_name, tournament),
  UNIQUE (card_name, tournament),
  CONSTRAINT fk_card FOREIGN KEY(card_name) REFERENCES cards(card_name)
);

CREATE TABLE ban_list (
  card_name                       varchar(128) UNIQUE,
  PRIMARY KEY (card_name)
);