ex_trainer/priv/repo/migrations/20210627114539_alter_trainings_add_period.exs
Joao P Dubas a69b771847 [extra-01] training has a period validity
We move from start/end date to a period date range to represent the
interval where a training take place.

Besides that, new constraints are added to ensure a trainings of a user
can't overlap. These constraints reflect in changeset validation.
2021-06-28 00:31:36 +00:00

47 lines
1.1 KiB
Elixir

defmodule Wabanex.Repo.Migrations.AlterTrainingsAddPeriod do
use Ecto.Migration
def up do
alter table(:trainings) do
add :period, :daterange
end
execute("""
update trainings set
period=daterange(start_date, end_date, '[)')
, updated_at=date_trunc('second', current_timestamp::timestamp)
""")
alter table(:trainings) do
remove :start_date
remove :end_date
modify :period, :daterange, null: false
end
create constraint(:trainings, :non_overlap_period,
exclude: "gist (user_id WITH =, period with &&)"
)
end
def down do
alter table(:trainings) do
add :start_date, :date
add :end_date, :date
end
execute("""
update trainings set
start_date=lower(period)
, end_date=upper(period)
, update_at=date_trunc('second', current_timestamp::timestamp)
""")
drop constraint(:trainings, :non_overlap_period)
alter table(:trainings) do
remove :period
modify :start_date, :date, null: false
end
end
end