0001-agendas-PG-constraint-to-prevent-event-overlaping-44.patch
| chrono/agendas/migrations/0051_event_date_range_constraint.py | ||
|---|---|---|
| 1 |
# -*- coding: utf-8 -*- |
|
| 2 |
from __future__ import unicode_literals |
|
| 3 | ||
| 4 |
from django.db import migrations |
|
| 5 | ||
| 6 | ||
| 7 |
class Migration(migrations.Migration): |
|
| 8 | ||
| 9 |
dependencies = [ |
|
| 10 |
('agendas', '0050_event_slug'),
|
|
| 11 |
] |
|
| 12 | ||
| 13 |
operations = [ |
|
| 14 |
migrations.RunSQL( |
|
| 15 |
sql=""" |
|
| 16 |
-- Add technical columns |
|
| 17 |
ALTER TABLE agendas_event ADD COLUMN end_datetime TIMESTAMP WITH TIME ZONE; |
|
| 18 |
ALTER TABLE agendas_event ADD COLUMN ignore_reason CHARACTER VARYING(20); |
|
| 19 | ||
| 20 |
-- trigger and procedure to maintain ignore_reason from bookings |
|
| 21 |
CREATE OR REPLACE FUNCTION set_ignore_reason() RETURNS TRIGGER AS $$ |
|
| 22 |
BEGIN |
|
| 23 |
IF (TG_OP = 'INSERT') THEN |
|
| 24 |
IF NEW.cancellation_datetime IS NOT NULL THEN |
|
| 25 |
UPDATE agendas_event SET ignore_reason = 'cancel' WHERE id = NEW.event_id; |
|
| 26 |
END IF; |
|
| 27 |
RETURN NEW; |
|
| 28 |
ELSE |
|
| 29 |
PERFORM 1 FROM agendas_booking b WHERE b.event_id = OLD.event_id AND b.cancellation_datetime IS NOT NULL; |
|
| 30 |
IF FOUND THEN |
|
| 31 |
UPDATE agendas_event SET ignore_reason = 'cancel' WHERE id = OLD.event_id; |
|
| 32 |
ELSE |
|
| 33 |
UPDATE agendas_event SET ignore_reason = NULL WHERE id = OLD.event_id; |
|
| 34 |
END IF; |
|
| 35 |
IF (TG_OP = 'UPDATE') THEN |
|
| 36 |
RETURN NEW; |
|
| 37 |
ELSE |
|
| 38 |
RETURN OLD; |
|
| 39 |
END IF; |
|
| 40 |
END IF; |
|
| 41 |
END; |
|
| 42 |
$$ LANGUAGE plpgsql; |
|
| 43 | ||
| 44 |
DROP TRIGGER IF EXISTS set_ignore_reason_trg ON agendas_booking; |
|
| 45 |
CREATE TRIGGER set_ignore_reason_trg |
|
| 46 |
AFTER INSERT OR UPDATE OR DELETE ON agendas_booking |
|
| 47 |
FOR EACH ROW |
|
| 48 |
EXECUTE PROCEDURE set_ignore_reason(); |
|
| 49 | ||
| 50 |
-- triggers to maintain end_datetime |
|
| 51 |
CREATE OR REPLACE FUNCTION update_duration() RETURNS TRIGGER AS $$ |
|
| 52 |
BEGIN |
|
| 53 |
UPDATE agendas_event SET end_datetime = start_datetime + (NEW.duration ||' minutes')::interval WHERE meeting_type_id = NEW.id; |
|
| 54 |
RETURN NEW; |
|
| 55 |
END; |
|
| 56 |
$$ LANGUAGE plpgsql; |
|
| 57 | ||
| 58 |
DROP TRIGGER IF EXISTS update_duration_trg ON agendas_meetingtype; |
|
| 59 |
CREATE TRIGGER update_duration_trg |
|
| 60 |
AFTER UPDATE ON agendas_meetingtype |
|
| 61 |
FOR EACH ROW |
|
| 62 |
WHEN (OLD.duration != NEW.duration) |
|
| 63 |
EXECUTE PROCEDURE update_duration(); |
|
| 64 | ||
| 65 |
CREATE OR REPLACE FUNCTION set_end_datetime() RETURNS TRIGGER AS $$ |
|
| 66 |
BEGIN |
|
| 67 |
IF NEW.meeting_type_id IS NULL THEN |
|
| 68 |
NEW.end_datetime = NULL; |
|
| 69 |
ELSE |
|
| 70 |
NEW.end_datetime = NEW.start_datetime + ((SELECT mt.duration FROM agendas_meetingtype mt WHERE mt.id = NEW.meeting_type_id) ||' minutes')::interval; |
|
| 71 |
END IF; |
|
| 72 |
RETURN NEW; |
|
| 73 |
END; |
|
| 74 |
$$ LANGUAGE plpgsql; |
|
| 75 | ||
| 76 |
DROP TRIGGER IF EXISTS set_end_datetime_trg ON agendas_event; |
|
| 77 |
CREATE TRIGGER set_end_datetime_trg |
|
| 78 |
BEFORE INSERT OR UPDATE ON agendas_event |
|
| 79 |
FOR EACH ROW |
|
| 80 |
EXECUTE PROCEDURE set_end_datetime(); |
|
| 81 | ||
| 82 |
-- Init legacy |
|
| 83 |
UPDATE agendas_event SET ignore_reason = 'history'; |
|
| 84 |
UPDATE |
|
| 85 |
agendas_event |
|
| 86 |
SET |
|
| 87 |
end_datetime = agendas_event.start_datetime + (agendas_meetingtype.duration ||' minutes')::interval |
|
| 88 |
FROM |
|
| 89 |
agendas_meetingtype |
|
| 90 |
WHERE |
|
| 91 |
agendas_meetingtype.id = agendas_event.meeting_type_id; |
|
| 92 |
""", |
|
| 93 |
reverse_sql=""" |
|
| 94 |
DROP TRIGGER IF EXISTS set_ignore_reason_trg ON agendas_booking; |
|
| 95 |
DROP FUNCTION IF EXISTS set_ignore_reason; |
|
| 96 |
DROP TRIGGER IF EXISTS update_duration_trg ON agendas_meetingtype; |
|
| 97 |
DROP FUNCTION IF EXISTS update_duration; |
|
| 98 |
DROP TRIGGER IF EXISTS set_end_datetime_trg ON agendas_event; |
|
| 99 |
DROP FUNCTION IF EXISTS set_end_datetime; |
|
| 100 |
ALTER TABLE agendas_event DROP COLUMN end_datetime; |
|
| 101 |
ALTER TABLE agendas_event DROP COLUMN ignore_reason; |
|
| 102 |
""", |
|
| 103 |
) |
|
| 104 |
] |
|
| chrono/agendas/migrations/0052_event_date_range_constraint.py | ||
|---|---|---|
| 1 |
# -*- coding: utf-8 -*- |
|
| 2 |
from __future__ import unicode_literals |
|
| 3 | ||
| 4 |
from django.db import migrations |
|
| 5 | ||
| 6 | ||
| 7 |
class Migration(migrations.Migration): |
|
| 8 | ||
| 9 |
dependencies = [ |
|
| 10 |
('agendas', '0051_event_date_range_constraint'),
|
|
| 11 |
] |
|
| 12 | ||
| 13 |
operations = [ |
|
| 14 |
migrations.RunSQL( |
|
| 15 |
sql=""" |
|
| 16 |
CREATE EXTENSION IF NOT EXISTS btree_Gist; |
|
| 17 |
ALTER TABLE agendas_event |
|
| 18 |
ADD CONSTRAINT tstzrange_constraint |
|
| 19 |
EXCLUDE USING GIST(desk_id WITH =, tstzrange(start_datetime, end_datetime) WITH &&) |
|
| 20 |
WHERE (ignore_reason IS NULL AND end_datetime IS NOT NULL and desk_id IS NOT NULL); |
|
| 21 |
""", |
|
| 22 |
reverse_sql=""" |
|
| 23 |
ALTER TABLE agendas_event DROP CONSTRAINT tstzrange_constraint; |
|
| 24 |
""", |
|
| 25 |
) |
|
| 26 |
] |
|
| tests/test_api.py | ||
|---|---|---|
| 3074 | 3074 |
assert len([x for x in resp.json['data'] if x['disabled']]) == 2 |
| 3075 | 3075 | |
| 3076 | 3076 | |
| 3077 |
def test_datetimes_api_concurrent_bookings(app, user, meetings_agenda): |
|
| 3078 |
meeting_type = MeetingType.objects.get(agenda=meetings_agenda) |
|
| 3079 |
api_url = '/api/agenda/%s/meetings/%s/datetimes/' % (meeting_type.agenda.slug, meeting_type.slug) |
|
| 3080 |
assert Booking.objects.count() == 0 |
|
| 3081 |
resp = app.get(api_url) |
|
| 3082 |
assert resp.json['data'][0]['datetime'] == '2017-05-22 10:00:00' |
|
| 3083 | ||
| 3084 |
# make a booking |
|
| 3085 |
fillslot_url = resp.json['data'][0]['api']['fillslot_url'] |
|
| 3086 |
app.authorization = ('Basic', ('john.doe', 'password'))
|
|
| 3087 |
resp = app.post(fillslot_url) |
|
| 3088 |
assert Booking.objects.count() == 1 |
|
| 3089 | ||
| 3090 |
# make a second one artificially on an overlapping slot |
|
| 3091 |
meeting_type = MeetingType.objects.create(agenda=meetings_agenda, label='Boo', duration=8) |
|
| 3092 |
booking = Booking.objects.first() |
|
| 3093 |
event = booking.event |
|
| 3094 |
new_event = Event.objects.create( |
|
| 3095 |
meeting_type=meeting_type, |
|
| 3096 |
start_datetime=event.start_datetime, |
|
| 3097 |
desk=event.desk, |
|
| 3098 |
places=event.places, |
|
| 3099 |
agenda=meetings_agenda, |
|
| 3100 |
) |
|
| 3101 |
Booking.objects.create(event=new_event) |
|
| 3102 | ||
| 3103 |
# should not crash and slot not showing up |
|
| 3104 |
resp = app.get(api_url) |
|
| 3105 |
assert resp.json['data'][0]['datetime'] == '2017-05-22 10:00:00' |
|
| 3106 |
assert resp.json['data'][0]['disabled'] is True |
|
| 3107 | ||
| 3108 | ||
| 3109 | 3077 |
def test_agenda_detail_routing(app, meetings_agenda): |
| 3110 | 3078 |
api_url = '/api/agenda/%s/' % meetings_agenda.slug |
| 3111 | 3079 |
resp = app.get(api_url) |
| tests/test_manager.py | ||
|---|---|---|
| 503 | 503 |
desk=desk, |
| 504 | 504 |
meeting_type=meetingtype, |
| 505 | 505 |
start_datetime=localtime(now()).replace( |
| 506 |
day=middle_day.day - middle_day.weekday() + 2, hour=9, minute=0
|
|
| 506 |
day=middle_day.day - middle_day.weekday() + 2, hour=hour, minute=minute
|
|
| 507 | 507 |
), |
| 508 | 508 |
) |
| 509 | 509 |
event.resources.add(resource) |
| ... | ... | |
| 2715 | 2715 |
desk=desk, |
| 2716 | 2716 |
meeting_type=meetingtype, |
| 2717 | 2717 |
start_datetime=localtime(now()).replace( |
| 2718 |
day=middle_day.day - middle_day.weekday() + 2, hour=9, minute=0
|
|
| 2718 |
day=middle_day.day - middle_day.weekday() + 2, hour=hour, minute=minute
|
|
| 2719 | 2719 |
), |
| 2720 | 2720 |
) |
| 2721 | 2721 |
Booking.objects.create(event=event) |
| tests/test_misc.py | ||
|---|---|---|
| 1 |
import datetime |
|
| 2 | ||
| 3 |
from django.db import IntegrityError |
|
| 4 |
from django.db import connection |
|
| 5 |
from django.db import transaction |
|
| 6 |
from django.utils.timezone import now |
|
| 7 | ||
| 8 | ||
| 9 |
import pytest |
|
| 10 | ||
| 1 | 11 |
from chrono.manager.widgets import DateTimeWidget, TimeWidget |
| 12 |
from chrono.agendas.models import Agenda |
|
| 13 |
from chrono.agendas.models import Booking |
|
| 14 |
from chrono.agendas.models import Desk |
|
| 15 |
from chrono.agendas.models import Event |
|
| 16 |
from chrono.agendas.models import MeetingType |
|
| 17 | ||
| 18 |
pytestmark = pytest.mark.django_db |
|
| 2 | 19 | |
| 3 | 20 | |
| 4 | 21 |
def test_widgets_init(): |
| 5 | 22 |
DateTimeWidget() |
| 6 | 23 |
TimeWidget() |
| 24 | ||
| 25 | ||
| 26 |
def check_ignore_reason(event, value): |
|
| 27 |
with connection.cursor() as cursor: |
|
| 28 |
cursor.execute("SELECT ignore_reason FROM agendas_event WHERE id = %s", [event.pk])
|
|
| 29 |
row = cursor.fetchone() |
|
| 30 |
assert row[0] == value |
|
| 31 | ||
| 32 | ||
| 33 |
def set_ignore_reason(event, value): |
|
| 34 |
with connection.cursor() as cursor: |
|
| 35 |
cursor.execute("UPDATE agendas_event SET ignore_reason = %s WHERE id = %s", [value, event.pk])
|
|
| 36 | ||
| 37 | ||
| 38 |
def check_end_datetime(event, value): |
|
| 39 |
with connection.cursor() as cursor: |
|
| 40 |
cursor.execute("SELECT end_datetime FROM agendas_event WHERE id = %s", [event.pk])
|
|
| 41 |
row = cursor.fetchone() |
|
| 42 |
assert row[0] == value |
|
| 43 | ||
| 44 | ||
| 45 |
def test_event_ignore_reason(): |
|
| 46 |
agenda = Agenda.objects.create(label='Meetings', kind='meetings') |
|
| 47 |
meeting_type = MeetingType.objects.create(agenda=agenda, label='Foo', duration=60) |
|
| 48 |
desk = Desk.objects.create(agenda=agenda, label='Desk') |
|
| 49 | ||
| 50 |
event = Event.objects.create( |
|
| 51 |
start_datetime=now(), meeting_type=meeting_type, places=10, agenda=agenda, desk=desk |
|
| 52 |
) |
|
| 53 |
check_ignore_reason(event, None) |
|
| 54 |
Booking.objects.create(event=event) |
|
| 55 |
check_ignore_reason(event, None) |
|
| 56 |
Booking.objects.create(event=event, cancellation_datetime=now()) |
|
| 57 |
check_ignore_reason(event, 'cancel') |
|
| 58 |
Booking.objects.update(cancellation_datetime=None) |
|
| 59 |
check_ignore_reason(event, None) |
|
| 60 |
Booking.objects.update(cancellation_datetime=now()) |
|
| 61 |
check_ignore_reason(event, 'cancel') |
|
| 62 |
Booking.objects.first().delete() |
|
| 63 |
check_ignore_reason(event, 'cancel') |
|
| 64 |
Booking.objects.all().delete() |
|
| 65 |
check_ignore_reason(event, None) |
|
| 66 | ||
| 67 | ||
| 68 |
def test_event_end_datetime(): |
|
| 69 |
agenda = Agenda.objects.create(label='Meetings', kind='meetings') |
|
| 70 |
meeting_type1 = MeetingType.objects.create(agenda=agenda, label='Foo', duration=60) |
|
| 71 |
meeting_type2 = MeetingType.objects.create(agenda=agenda, label='Foo', duration=45) |
|
| 72 | ||
| 73 |
event = Event.objects.create(start_datetime=now(), places=10, agenda=agenda) |
|
| 74 |
check_end_datetime(event, None) |
|
| 75 |
event.meeting_type = meeting_type1 |
|
| 76 |
event.save() |
|
| 77 |
check_end_datetime(event, event.start_datetime + datetime.timedelta(minutes=60)) |
|
| 78 |
event.meeting_type = meeting_type2 |
|
| 79 |
event.save() |
|
| 80 |
check_end_datetime(event, event.start_datetime + datetime.timedelta(minutes=45)) |
|
| 81 |
event.start_datetime = now() |
|
| 82 |
event.save() |
|
| 83 |
check_end_datetime(event, event.start_datetime + datetime.timedelta(minutes=45)) |
|
| 84 |
event.meeting_type = None |
|
| 85 |
event.save() |
|
| 86 |
check_end_datetime(event, None) |
|
| 87 |
event.meeting_type = meeting_type2 |
|
| 88 |
event.save() |
|
| 89 |
check_end_datetime(event, event.start_datetime + datetime.timedelta(minutes=45)) |
|
| 90 | ||
| 91 |
event2 = Event.objects.create(start_datetime=now(), meeting_type=meeting_type1, places=10, agenda=agenda) |
|
| 92 |
check_end_datetime(event2, event2.start_datetime + datetime.timedelta(minutes=60)) |
|
| 93 |
meeting_type1.duration = 42 |
|
| 94 |
meeting_type1.save() |
|
| 95 |
check_end_datetime(event2, event2.start_datetime + datetime.timedelta(minutes=42)) |
|
| 96 |
check_end_datetime(event, event.start_datetime + datetime.timedelta(minutes=45)) |
|
| 97 | ||
| 98 | ||
| 99 |
def test_meeting_event_exclusion_constraint(): |
|
| 100 |
agenda = Agenda.objects.create(label='Meetings', kind='meetings') |
|
| 101 |
meeting_type1 = MeetingType.objects.create(agenda=agenda, label='Foo 1', duration=60) |
|
| 102 |
meeting_type2 = MeetingType.objects.create(agenda=agenda, label='Foo 2', duration=30) |
|
| 103 |
desk1 = Desk.objects.create(agenda=agenda, label='Desk 1') |
|
| 104 |
desk2 = Desk.objects.create(agenda=agenda, label='Desk 2') |
|
| 105 | ||
| 106 |
# create an event |
|
| 107 |
event1 = Event.objects.create( |
|
| 108 |
start_datetime=now(), meeting_type=meeting_type1, places=10, agenda=agenda, desk=desk1 |
|
| 109 |
) |
|
| 110 | ||
| 111 |
# create a event with the same date range on other desk => no error |
|
| 112 |
Event.objects.create( |
|
| 113 |
start_datetime=event1.start_datetime, meeting_type=meeting_type1, places=10, agenda=agenda, desk=desk2 |
|
| 114 |
) |
|
| 115 |
# no check if no meeting_type set |
|
| 116 |
Event.objects.create(start_datetime=event1.start_datetime, places=10, agenda=agenda, desk=desk1) |
|
| 117 |
# no check if no desk set |
|
| 118 |
Event.objects.create( |
|
| 119 |
start_datetime=event1.start_datetime, meeting_type=meeting_type1, places=10, agenda=agenda |
|
| 120 |
) |
|
| 121 | ||
| 122 |
# create an event just after the first one => ok |
|
| 123 |
Event.objects.create( |
|
| 124 |
start_datetime=event1.start_datetime + datetime.timedelta(minutes=60), |
|
| 125 |
meeting_type=meeting_type1, |
|
| 126 |
places=10, |
|
| 127 |
agenda=agenda, |
|
| 128 |
desk=desk1, |
|
| 129 |
) |
|
| 130 | ||
| 131 |
# try to create an event with the same date range and the same desk => error |
|
| 132 |
with pytest.raises(IntegrityError): |
|
| 133 |
with transaction.atomic(): |
|
| 134 |
Event.objects.create( |
|
| 135 |
start_datetime=event1.start_datetime, |
|
| 136 |
meeting_type=meeting_type1, |
|
| 137 |
places=10, |
|
| 138 |
agenda=agenda, |
|
| 139 |
desk=desk1, |
|
| 140 |
) |
|
| 141 |
# try to create an event with the same start date, other duration and the same desk => error |
|
| 142 |
with pytest.raises(IntegrityError): |
|
| 143 |
with transaction.atomic(): |
|
| 144 |
Event.objects.create( |
|
| 145 |
start_datetime=event1.start_datetime, |
|
| 146 |
meeting_type=meeting_type2, |
|
| 147 |
places=10, |
|
| 148 |
agenda=agenda, |
|
| 149 |
desk=desk1, |
|
| 150 |
) |
|
| 151 |
# try to create an event overlaping the first one on the same desk => error |
|
| 152 |
with pytest.raises(IntegrityError): |
|
| 153 |
with transaction.atomic(): |
|
| 154 |
Event.objects.create( |
|
| 155 |
start_datetime=event1.start_datetime + datetime.timedelta(minutes=10), |
|
| 156 |
meeting_type=meeting_type1, |
|
| 157 |
places=10, |
|
| 158 |
agenda=agenda, |
|
| 159 |
desk=desk1, |
|
| 160 |
) |
|
| 161 |
with pytest.raises(IntegrityError): |
|
| 162 |
with transaction.atomic(): |
|
| 163 |
Event.objects.create( |
|
| 164 |
start_datetime=event1.start_datetime + datetime.timedelta(minutes=10), |
|
| 165 |
meeting_type=meeting_type2, |
|
| 166 |
places=10, |
|
| 167 |
agenda=agenda, |
|
| 168 |
desk=desk1, |
|
| 169 |
) |
|
| 170 |
with pytest.raises(IntegrityError): |
|
| 171 |
with transaction.atomic(): |
|
| 172 |
Event.objects.create( |
|
| 173 |
start_datetime=event1.start_datetime - datetime.timedelta(minutes=10), |
|
| 174 |
meeting_type=meeting_type1, |
|
| 175 |
places=10, |
|
| 176 |
agenda=agenda, |
|
| 177 |
desk=desk1, |
|
| 178 |
) |
|
| 179 |
with pytest.raises(IntegrityError): |
|
| 180 |
with transaction.atomic(): |
|
| 181 |
Event.objects.create( |
|
| 182 |
start_datetime=event1.start_datetime - datetime.timedelta(minutes=10), |
|
| 183 |
meeting_type=meeting_type2, |
|
| 184 |
places=10, |
|
| 185 |
agenda=agenda, |
|
| 186 |
desk=desk1, |
|
| 187 |
) |
|
| 188 |
# but if event1 is cancelled it's ok |
|
| 189 |
Booking.objects.create(event=event1, cancellation_datetime=now()) |
|
| 190 |
Event.objects.create( |
|
| 191 |
start_datetime=event1.start_datetime, meeting_type=meeting_type1, places=10, agenda=agenda, desk=desk1 |
|
| 192 |
) |
|
| 7 |
- |
|