示例#1
0
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()
}
示例#2
0
文件: main.go 项目: jackc/lunchkin-db
// afterConnect creates the prepared statements that this application uses
func afterConnect(conn *pgx.Connection) (err error) {
	err = conn.Prepare("getPlayers", `
    select coalesce(array_to_json(array_agg(row_to_json(t))), '[]'::json)
    from (
      select player_id, name
      from player
      order by name
    ) t
  `)
	if err != nil {
		return
	}

	err = conn.Prepare("createPlayer", `
    insert into player(name) values($1) returning player_id
  `)
	if err != nil {
		return
	}

	err = conn.Prepare("deletePlayer", `
    delete from player where player_id=$1
  `)
	if err != nil {
		return
	}

	err = conn.Prepare("getGames", `
    select coalesce(array_to_json(array_agg(row_to_json(g))), '[]'::json)
    from (
      select game_id, date, length,
        (
          select coalesce(array_to_json(array_agg(row_to_json(t))), '[]'::json)
          from (
            select player_id, name, level, effective_level, winner
            from game_player
              join player using(player_id)
            where game.game_id=game_player.game_id
          ) t
        ) players
      from game
    ) g
  `)
	if err != nil {
		return
	}

	err = conn.Prepare("deleteGame", `
    delete from game where game_id=$1
  `)
	if err != nil {
		return
	}

	err = conn.Prepare("getStandings", `
    select coalesce(array_to_json(array_agg(row_to_json(t))), '[]'::json)
    from (
      select *
      from player_summary
      order by num_points desc, name asc
    ) t
  `)
	if err != nil {
		return
	}

	return
}