schema.sql (1450B)
1 -- card name db 2 create table if not exists mtg ( 3 name text, 4 cost text, 5 card_type text 6 ); 7 CREATE TABLE if not exists round ( 8 id integer primary key, 9 fileid text not null, 10 date text 11 ); 12 CREATE TABLE if not exists deck ( 13 round integer not null, 14 player varchar not null, 15 card1 text, 16 card2 text, 17 card3 text 18 ); 19 CREATE TABLE if not exists match ( 20 round integer, 21 group_name text, 22 player text, 23 opp_player text, 24 score integer 25 ); 26 CREATE VIEW if not exists card as 27 select round,player,card1 as card from deck 28 union select round,player,card2 as card from deck 29 union select round,player,card3 as card from deck 30 /* card(round,player,card) */; 31 CREATE INDEX if not exists matchidx on match(round); 32 CREATE INDEX if not exists deckidx on deck(round); 33 CREATE INDEX if not exists matchidx2 on match(player); 34 CREATE INDEX if not exists deckidx2 on deck(player); 35 CREATE VIEW if not exists rank as 36 select round,player,rank() over (partition by round order by 37 sum(case when match.group_name = 'final' then match.score else null end) desc nulls last, 38 sum(case when match.group_name != 'final' then match.score else null end) desc 39 ) as rank 40 from match group by 1,2 order by 3 desc 41 /* rank(round,player,rank) */; 42 CREATE TABLE if not exists ban (name text); 43 CREATE INDEX if not exists c1idx on deck(card1); 44 CREATE INDEX if not exists c2idx on deck(card2); 45 CREATE INDEX if not exists c3idx on deck(card3);