/
migrations.go
110 lines (97 loc) · 2.6 KB
/
migrations.go
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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
package main
import (
"fmt"
"github.com/JackC/pgx"
mig "github.com/JackC/pgx/migrate"
)
func migrate(connectionParameters pgx.ConnectionParameters) (err error) {
var conn *pgx.Connection
conn, err = pgx.Connect(connectionParameters)
if err != nil {
return err
}
defer func() {
closeErr := conn.Close()
if err == nil {
err = closeErr
}
}()
var m *mig.Migrator
m, err = mig.NewMigrator(conn, "schema_version")
if err != nil {
return
}
m.OnStart = func(migration *mig.Migration) {
fmt.Printf("Migrating %d: %s\n", migration.Sequence, migration.Name)
}
m.AppendMigration("Create player", `
create table player(
player_id serial primary key,
name varchar not null unique
);
`)
m.AppendMigration("Create game", `
create table game(
game_id serial primary key,
date date not null
);
`)
m.AppendMigration("Create game_player", `
create table game_player(
game_id int references game on delete cascade,
player_id int references player,
level smallint,
effective_level smallint,
winner boolean not null default false,
primary key (game_id, player_id)
);
create index on game_player (player_id);
`)
m.AppendMigration("Create player_summary", `
create view player_summary as
select player_id, name,
count(*) as num_games,
sum(winner::int) as num_wins,
sum(case when winner then ceil(num_players::float8/num_winners::float8) else 0 end)::bigint as num_points
from (
select *,
count(*) over (partition by game_id) as num_players,
sum(winner::int) over (partition by game_id) as num_winners
from player
join game_player using(player_id)
join game using(game_id)
) t
group by player_id, name;
`)
m.AppendMigration("Add rating to player_summary", `
drop view player_summary;
create view player_summary as
select player_id,
name,
num_games,
num_wins,
num_points::bigint,
round((num_points / num_games)::numeric, 3) as rating
from (
select player_id,
name,
count(*) as num_games,
sum(winner::int) as num_wins,
sum(case when winner then ceil(num_players::float8/num_winners::float8) else 0 end) as num_points
from (
select *,
count(*) over (partition by game_id) as num_players,
sum(winner::int) over (partition by game_id) as num_winners
from player
join game_player using(player_id)
join game using(game_id)
) t
group by player_id, name
) t;
`)
m.AppendMigration("Add length to game", `
alter table game add column length smallint;
comment on column game.length is 'Number of rounds played';
`)
return m.Migrate()
}