migration-016-017.sql

-- Migration from 016 to 017

CREATE TABLE IMAGE_ZOOM_LEVELS (
  ID BIGSERIAL NOT NULL,

  IS_ORIGINAL BOOLEAN_CHAR NOT NULL,
  CONTAINER_DATASET_ID TECH_ID NOT NULL, 
  
  -- Perm id of the 'physical' dataset which contains all images with this zoom.
  -- Physical datasets are not stored in "image_data_sets" table, but we need the reference to them 
  -- when we delete or archive one zoom level. 
  PHYSICAL_DATASET_PERM_ID TEXT NOT NULL,
  
  PRIMARY KEY (ID),
  UNIQUE (PHYSICAL_DATASET_PERM_ID),
  CONSTRAINT FK_IMAGE_ZOOM_LEVELS_1 FOREIGN KEY (CONTAINER_DATASET_ID) REFERENCES IMAGE_DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX IMAGE_ZOOM_LEVELS_PHYS_DS_IDX ON IMAGE_ZOOM_LEVELS (PHYSICAL_DATASET_PERM_ID);
CREATE INDEX IMAGE_ZOOM_LEVELS_CONT_FK_IDX ON IMAGE_ZOOM_LEVELS (CONTAINER_DATASET_ID);


ALTER TABLE ACQUIRED_IMAGES ALTER COLUMN IMG_ID DROP NOT NULL;
ALTER TABLE ACQUIRED_IMAGES DROP CONSTRAINT FK_IMAGES_3;
ALTER TABLE ACQUIRED_IMAGES ADD CONSTRAINT FK_IMAGES_3 FOREIGN KEY (IMG_ID) REFERENCES IMAGES (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE ACQUIRED_IMAGES DROP CONSTRAINT FK_IMAGES_4;
ALTER TABLE ACQUIRED_IMAGES ADD CONSTRAINT FK_IMAGES_4 FOREIGN KEY (THUMBNAIL_ID) REFERENCES IMAGES (ID) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE acquired_images	ADD CONSTRAINT fk_images_img_or_thumb_arc_ck CHECK (IMG_ID IS NOT NULL OR THUMBNAIL_ID IS NOT NULL);

CREATE OR REPLACE FUNCTION DELETE_UNUSED_NULLED_IMAGES() RETURNS trigger AS $$
BEGIN
	if NEW.img_id IS NULL then
		if OLD.img_id IS NOT NULL then
		  delete from images where id = OLD.img.id;
		end if;
	end if;
	if NEW.thumbnail_id IS NULL then
		if OLD.thumbnail_id IS NOT NULL then
		  delete from images where id = OLD.thumbnail_id;
		end if;
	end if;
	RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER UNUSED_NULLED_IMAGES AFTER UPDATE ON ACQUIRED_IMAGES
    FOR EACH ROW EXECUTE PROCEDURE DELETE_UNUSED_NULLED_IMAGES();

CREATE OR REPLACE FUNCTION DELETE_EMPTY_ACQUIRED_IMAGES() RETURNS trigger AS $$
BEGIN
	delete from acquired_images where id = OLD.id;
	RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER EMPTY_ACQUIRED_IMAGES BEFORE UPDATE ON ACQUIRED_IMAGES
		FOR EACH ROW
		WHEN (NEW.img_id IS NULL AND NEW.thumbnail_id IS NULL)
		EXECUTE PROCEDURE DELETE_EMPTY_ACQUIRED_IMAGES();