Gertrudius
commented
1 year ago Here's the current draft of this trigger. As it stands, the discourse deletes will actually best be handled by an after-delete trigger, because that ensures that the epigraphic unit is already deleted before it tries to delete the discourse unit that references it. The logic I've added should cause it to delete any discourse unit whose last linked epigraphic unit has been deleted. That should cover the line situation as well, as when each epigraphic unit on the line is deleted it will do the check to see if it is the last sign in the discourse unit. Local tests look good, so I've propagated it to production.
DROP TRIGGER after_text_epigraphy_delete;
DELIMITER //
CREATE TRIGGER after_text_epigraphy_delete
AFTER DELETE
ON text_epigraphy FOR EACH ROW
BEGIN
INSERT INTO logging(type, time, reference_table, uuid, object_values) VALUES ("DELETE",SYSDATE(),"text_epigraphy",old.uuid,concat("type¦",COALESCE(old.type,'NULL'),"¦text_uuid¦",COALESCE(old.text_uuid,'NULL'),"¦tree_uuid¦",COALESCE(old.tree_uuid,'NULL'),"¦parent_uuid¦",COALESCE(old.parent_uuid,'NULL'),"¦side¦",COALESCE(old.side,'NULL'),"¦column¦",COALESCE(old.column,'NULL'),"¦line¦",COALESCE(old.line,'NULL'),"¦char_on_line¦",COALESCE(old.char_on_line,'NULL'),"¦char_on_tablet¦",COALESCE(old.char_on_tablet,'NULL'),"¦sign_uuid¦",COALESCE(old.sign_uuid,'NULL'),"¦sign¦",COALESCE(old.sign,'NULL'),"¦reading_uuid¦",COALESCE(old.reading_uuid,'NULL'),"¦reading¦",COALESCE(old.reading,'NULL'),"¦discourse_uuid¦",COALESCE(old.discourse_uuid,'NULL')));
DELETE FROM uuid WHERE uuid.uuid = old.uuid;
IF (old.type IN ("number","sign","undeterminedSigns") AND (SELECT COUNT(discourse_uuid) FROM text_epigraphy WHERE discourse_uuid = old.discourse_uuid) <= 1) THEN
CALL text_discourse_iterate_on_delete(old.discourse_uuid);
DELETE FROM text_discourse WHERE uuid = old.discourse_uuid;
END IF;
END
//
edstratford
The user will have the ability to delete one or more signs a word, or an entire line in text_epigraphy. This will trigger the deletion of words, sometimes multiple words. This means that the trigger before_text_epigraphy_delete needs to be (if possible) refactored to delete whole words in text_discourse if necessary. At this point, it will not delete discourse rows that are not of the type number or word (i.e. phrase, clause, etc.), even if all it's constituents are deleted. If we decide to do that we'll do it in an upcoming issue.