agendas: move sql migration code in dedicated file (#66374)
This commit is contained in:
Lauréline Guérin
parent
ad152f40ee
commit
b64dc05083
|
|
@ -1,73 +1,25 @@
|
|||
import os
|
||||
|
||||
from django.db import migrations, transaction
|
||||
from django.db.utils import InternalError, OperationalError, ProgrammingError
|
||||
|
||||
sql_forwards = """
|
||||
with open(
|
||||
os.path.join(
|
||||
os.path.dirname(os.path.realpath(__file__)),
|
||||
'..',
|
||||
'sql',
|
||||
'event_triggers_for_tstzrange_constraint.sql',
|
||||
)
|
||||
) as sql_file:
|
||||
sql_triggers = sql_file.read()
|
||||
|
||||
sql_forwards = (
|
||||
"""
|
||||
-- Add technical columns
|
||||
ALTER TABLE agendas_event ADD COLUMN _end_datetime TIMESTAMP WITH TIME ZONE;
|
||||
ALTER TABLE agendas_event ADD COLUMN _ignore_reason CHARACTER VARYING(20);
|
||||
|
||||
-- trigger and procedure to maintain _ignore_reason from bookings
|
||||
CREATE OR REPLACE FUNCTION set_ignore_reason() RETURNS TRIGGER AS $$
|
||||
BEGIN
|
||||
IF (TG_OP = 'INSERT') THEN
|
||||
IF NEW.cancellation_datetime IS NOT NULL THEN
|
||||
UPDATE agendas_event SET _ignore_reason = 'cancel' WHERE id = NEW.event_id;
|
||||
END IF;
|
||||
RETURN NEW;
|
||||
ELSE
|
||||
PERFORM 1 FROM agendas_booking b WHERE b.event_id = OLD.event_id AND b.cancellation_datetime IS NOT NULL;
|
||||
IF FOUND THEN
|
||||
UPDATE agendas_event SET _ignore_reason = 'cancel' WHERE id = OLD.event_id;
|
||||
ELSE
|
||||
UPDATE agendas_event SET _ignore_reason = NULL WHERE id = OLD.event_id;
|
||||
END IF;
|
||||
IF (TG_OP = 'UPDATE') THEN
|
||||
RETURN NEW;
|
||||
ELSE
|
||||
RETURN OLD;
|
||||
END IF;
|
||||
END IF;
|
||||
END;
|
||||
$$ LANGUAGE plpgsql;
|
||||
|
||||
DROP TRIGGER IF EXISTS set_ignore_reason_trg ON agendas_booking;
|
||||
CREATE TRIGGER set_ignore_reason_trg
|
||||
AFTER INSERT OR UPDATE OR DELETE ON agendas_booking
|
||||
FOR EACH ROW
|
||||
EXECUTE PROCEDURE set_ignore_reason();
|
||||
|
||||
-- triggers to maintain _end_datetime
|
||||
CREATE OR REPLACE FUNCTION update_duration() RETURNS TRIGGER AS $$
|
||||
BEGIN
|
||||
UPDATE agendas_event SET _end_datetime = start_datetime + (NEW.duration ||' minutes')::interval WHERE meeting_type_id = NEW.id;
|
||||
RETURN NEW;
|
||||
END;
|
||||
$$ LANGUAGE plpgsql;
|
||||
|
||||
DROP TRIGGER IF EXISTS update_duration_trg ON agendas_meetingtype;
|
||||
CREATE TRIGGER update_duration_trg
|
||||
AFTER UPDATE ON agendas_meetingtype
|
||||
FOR EACH ROW
|
||||
WHEN (OLD.duration != NEW.duration)
|
||||
EXECUTE PROCEDURE update_duration();
|
||||
|
||||
CREATE OR REPLACE FUNCTION set_end_datetime() RETURNS TRIGGER AS $$
|
||||
BEGIN
|
||||
IF NEW.meeting_type_id IS NULL THEN
|
||||
NEW._end_datetime = NULL;
|
||||
ELSE
|
||||
NEW._end_datetime = NEW.start_datetime + ((
|
||||
SELECT mt.duration FROM agendas_meetingtype mt WHERE mt.id = NEW.meeting_type_id) ||' minutes')::interval;
|
||||
END IF;
|
||||
RETURN NEW;
|
||||
END;
|
||||
$$ LANGUAGE plpgsql;
|
||||
|
||||
DROP TRIGGER IF EXISTS set_end_datetime_trg ON agendas_event;
|
||||
CREATE TRIGGER set_end_datetime_trg
|
||||
BEFORE INSERT OR UPDATE ON agendas_event
|
||||
FOR EACH ROW
|
||||
EXECUTE PROCEDURE set_end_datetime();
|
||||
%s
|
||||
|
||||
-- Init legacy
|
||||
UPDATE agendas_event SET _ignore_reason = 'history';
|
||||
|
|
@ -80,6 +32,8 @@ FROM
|
|||
WHERE
|
||||
agendas_meetingtype.id = agendas_event.meeting_type_id;
|
||||
"""
|
||||
% sql_triggers
|
||||
)
|
||||
|
||||
sql_backwards = """
|
||||
DROP TRIGGER IF EXISTS set_ignore_reason_trg ON agendas_booking;
|
||||
|
|
|
|||
|
|
@ -0,0 +1,62 @@
|
|||
-- trigger and procedure to maintain _ignore_reason from bookings
|
||||
CREATE OR REPLACE FUNCTION set_ignore_reason() RETURNS TRIGGER AS $$
|
||||
BEGIN
|
||||
IF (TG_OP = 'INSERT') THEN
|
||||
IF NEW.cancellation_datetime IS NOT NULL THEN
|
||||
UPDATE agendas_event SET _ignore_reason = 'cancel' WHERE id = NEW.event_id;
|
||||
END IF;
|
||||
RETURN NEW;
|
||||
ELSE
|
||||
PERFORM 1 FROM agendas_booking b WHERE b.event_id = OLD.event_id AND b.cancellation_datetime IS NOT NULL;
|
||||
IF FOUND THEN
|
||||
UPDATE agendas_event SET _ignore_reason = 'cancel' WHERE id = OLD.event_id;
|
||||
ELSE
|
||||
UPDATE agendas_event SET _ignore_reason = NULL WHERE id = OLD.event_id;
|
||||
END IF;
|
||||
IF (TG_OP = 'UPDATE') THEN
|
||||
RETURN NEW;
|
||||
ELSE
|
||||
RETURN OLD;
|
||||
END IF;
|
||||
END IF;
|
||||
END;
|
||||
$$ LANGUAGE plpgsql;
|
||||
|
||||
DROP TRIGGER IF EXISTS set_ignore_reason_trg ON agendas_booking;
|
||||
CREATE TRIGGER set_ignore_reason_trg
|
||||
AFTER INSERT OR UPDATE OR DELETE ON agendas_booking
|
||||
FOR EACH ROW
|
||||
EXECUTE PROCEDURE set_ignore_reason();
|
||||
|
||||
-- triggers to maintain _end_datetime
|
||||
CREATE OR REPLACE FUNCTION update_duration() RETURNS TRIGGER AS $$
|
||||
BEGIN
|
||||
UPDATE agendas_event SET _end_datetime = start_datetime + (NEW.duration ||' minutes')::interval WHERE meeting_type_id = NEW.id;
|
||||
RETURN NEW;
|
||||
END;
|
||||
$$ LANGUAGE plpgsql;
|
||||
|
||||
DROP TRIGGER IF EXISTS update_duration_trg ON agendas_meetingtype;
|
||||
CREATE TRIGGER update_duration_trg
|
||||
AFTER UPDATE ON agendas_meetingtype
|
||||
FOR EACH ROW
|
||||
WHEN (OLD.duration != NEW.duration)
|
||||
EXECUTE PROCEDURE update_duration();
|
||||
|
||||
CREATE OR REPLACE FUNCTION set_end_datetime() RETURNS TRIGGER AS $$
|
||||
BEGIN
|
||||
IF NEW.meeting_type_id IS NULL THEN
|
||||
NEW._end_datetime = NULL;
|
||||
ELSE
|
||||
NEW._end_datetime = NEW.start_datetime + ((
|
||||
SELECT mt.duration FROM agendas_meetingtype mt WHERE mt.id = NEW.meeting_type_id) ||' minutes')::interval;
|
||||
END IF;
|
||||
RETURN NEW;
|
||||
END;
|
||||
$$ LANGUAGE plpgsql;
|
||||
|
||||
DROP TRIGGER IF EXISTS set_end_datetime_trg ON agendas_event;
|
||||
CREATE TRIGGER set_end_datetime_trg
|
||||
BEFORE INSERT OR UPDATE ON agendas_event
|
||||
FOR EACH ROW
|
||||
EXECUTE PROCEDURE set_end_datetime();
|
||||
Loading…
Reference in New Issue