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 NULL;
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);