--
-- Copyright 2006-2017 University of Dundee. All rights reserved.
-- Use is subject to license terms supplied in LICENSE.txt
--

--
-- This file was generated by dsl/resources/ome/dsl/psql-footer.vm
--

--
-- Triggers
--
  CREATE OR REPLACE FUNCTION channel_pixels_index_move() RETURNS "trigger" AS '
    DECLARE
      duplicate INT8;
    BEGIN

      -- Avoids a query if the new and old values of x are the same.
      IF new.pixels = old.pixels AND new.pixels_index = old.pixels_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM channel
       WHERE pixels = new.pixels AND pixels_index = new.pixels_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping channel %% via (-1 - oldvalue )'', duplicate;
          UPDATE channel SET pixels_index = -1 - pixels_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER channel_pixels_index_trigger
        BEFORE UPDATE ON channel
        FOR EACH ROW EXECUTE PROCEDURE channel_pixels_index_move ();

  CREATE OR REPLACE FUNCTION channelbinding_renderingDef_index_move() RETURNS "trigger" AS '
    DECLARE
      duplicate INT8;
    BEGIN

      -- Avoids a query if the new and old values of x are the same.
      IF new.renderingDef = old.renderingDef AND new.renderingDef_index = old.renderingDef_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM channelbinding
       WHERE renderingDef = new.renderingDef AND renderingDef_index = new.renderingDef_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping channelbinding %% via (-1 - oldvalue )'', duplicate;
          UPDATE channelbinding SET renderingDef_index = -1 - renderingDef_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER channelbinding_renderingDef_index_trigger
        BEFORE UPDATE ON channelbinding
        FOR EACH ROW EXECUTE PROCEDURE channelbinding_renderingDef_index_move ();

  CREATE OR REPLACE FUNCTION codomainmapcontext_channelBinding_index_move() RETURNS "trigger" AS '
    DECLARE
      duplicate INT8;
    BEGIN

      -- Avoids a query if the new and old values of x are the same.
      IF new.channelBinding = old.channelBinding AND new.channelBinding_index = old.channelBinding_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM codomainmapcontext
       WHERE channelBinding = new.channelBinding AND channelBinding_index = new.channelBinding_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping codomainmapcontext %% via (-1 - oldvalue )'', duplicate;
          UPDATE codomainmapcontext SET channelBinding_index = -1 - channelBinding_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER codomainmapcontext_channelBinding_index_trigger
        BEFORE UPDATE ON codomainmapcontext
        FOR EACH ROW EXECUTE PROCEDURE codomainmapcontext_channelBinding_index_move ();

  CREATE OR REPLACE FUNCTION filesetentry_fileset_index_move() RETURNS "trigger" AS '
    DECLARE
      duplicate INT8;
    BEGIN

      -- Avoids a query if the new and old values of x are the same.
      IF new.fileset = old.fileset AND new.fileset_index = old.fileset_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM filesetentry
       WHERE fileset = new.fileset AND fileset_index = new.fileset_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping filesetentry %% via (-1 - oldvalue )'', duplicate;
          UPDATE filesetentry SET fileset_index = -1 - fileset_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER filesetentry_fileset_index_trigger
        BEFORE UPDATE ON filesetentry
        FOR EACH ROW EXECUTE PROCEDURE filesetentry_fileset_index_move ();

  CREATE OR REPLACE FUNCTION filesetjoblink_parent_index_move() RETURNS "trigger" AS '
    DECLARE
      duplicate INT8;
    BEGIN

      -- Avoids a query if the new and old values of x are the same.
      IF new.parent = old.parent AND new.parent_index = old.parent_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM filesetjoblink
       WHERE parent = new.parent AND parent_index = new.parent_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping filesetjoblink %% via (-1 - oldvalue )'', duplicate;
          UPDATE filesetjoblink SET parent_index = -1 - parent_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER filesetjoblink_parent_index_trigger
        BEFORE UPDATE ON filesetjoblink
        FOR EACH ROW EXECUTE PROCEDURE filesetjoblink_parent_index_move ();

  CREATE OR REPLACE FUNCTION groupexperimentermap_child_index_move() RETURNS "trigger" AS '
    DECLARE
      duplicate INT8;
    BEGIN

      -- Avoids a query if the new and old values of x are the same.
      IF new.child = old.child AND new.child_index = old.child_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM groupexperimentermap
       WHERE child = new.child AND child_index = new.child_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping groupexperimentermap %% via (-1 - oldvalue )'', duplicate;
          UPDATE groupexperimentermap SET child_index = -1 - child_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER groupexperimentermap_child_index_trigger
        BEFORE UPDATE ON groupexperimentermap
        FOR EACH ROW EXECUTE PROCEDURE groupexperimentermap_child_index_move ();

  CREATE OR REPLACE FUNCTION lightpathexcitationfilterlink_parent_index_move() RETURNS "trigger" AS '
    DECLARE
      duplicate INT8;
    BEGIN

      -- Avoids a query if the new and old values of x are the same.
      IF new.parent = old.parent AND new.parent_index = old.parent_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM lightpathexcitationfilterlink
       WHERE parent = new.parent AND parent_index = new.parent_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping lightpathexcitationfilterlink %% via (-1 - oldvalue )'', duplicate;
          UPDATE lightpathexcitationfilterlink SET parent_index = -1 - parent_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER lightpathexcitationfilterlink_parent_index_trigger
        BEFORE UPDATE ON lightpathexcitationfilterlink
        FOR EACH ROW EXECUTE PROCEDURE lightpathexcitationfilterlink_parent_index_move ();

  CREATE OR REPLACE FUNCTION pixels_image_index_move() RETURNS "trigger" AS '
    DECLARE
      duplicate INT8;
    BEGIN

      -- Avoids a query if the new and old values of x are the same.
      IF new.image = old.image AND new.image_index = old.image_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM pixels
       WHERE image = new.image AND image_index = new.image_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping pixels %% via (-1 - oldvalue )'', duplicate;
          UPDATE pixels SET image_index = -1 - image_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER pixels_image_index_trigger
        BEFORE UPDATE ON pixels
        FOR EACH ROW EXECUTE PROCEDURE pixels_image_index_move ();

  CREATE OR REPLACE FUNCTION projectiondef_renderingDef_index_move() RETURNS "trigger" AS '
    DECLARE
      duplicate INT8;
    BEGIN

      -- Avoids a query if the new and old values of x are the same.
      IF new.renderingDef = old.renderingDef AND new.renderingDef_index = old.renderingDef_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM projectiondef
       WHERE renderingDef = new.renderingDef AND renderingDef_index = new.renderingDef_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping projectiondef %% via (-1 - oldvalue )'', duplicate;
          UPDATE projectiondef SET renderingDef_index = -1 - renderingDef_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER projectiondef_renderingDef_index_trigger
        BEFORE UPDATE ON projectiondef
        FOR EACH ROW EXECUTE PROCEDURE projectiondef_renderingDef_index_move ();

  CREATE OR REPLACE FUNCTION shape_roi_index_move() RETURNS "trigger" AS '
    DECLARE
      duplicate INT8;
    BEGIN

      -- Avoids a query if the new and old values of x are the same.
      IF new.roi = old.roi AND new.roi_index = old.roi_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM shape
       WHERE roi = new.roi AND roi_index = new.roi_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping shape %% via (-1 - oldvalue )'', duplicate;
          UPDATE shape SET roi_index = -1 - roi_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER shape_roi_index_trigger
        BEFORE UPDATE ON shape
        FOR EACH ROW EXECUTE PROCEDURE shape_roi_index_move ();

  CREATE OR REPLACE FUNCTION wellsample_well_index_move() RETURNS "trigger" AS '
    DECLARE
      duplicate INT8;
    BEGIN

      -- Avoids a query if the new and old values of x are the same.
      IF new.well = old.well AND new.well_index = old.well_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM wellsample
       WHERE well = new.well AND well_index = new.well_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping wellsample %% via (-1 - oldvalue )'', duplicate;
          UPDATE wellsample SET well_index = -1 - well_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER wellsample_well_index_trigger
        BEFORE UPDATE ON wellsample
        FOR EACH ROW EXECUTE PROCEDURE wellsample_well_index_move ();


--
-- Indexes
--
  CREATE INDEX i_affinetransform_owner ON affinetransform(owner_id);
  CREATE INDEX i_affinetransform_group ON affinetransform(group_id);
  CREATE INDEX i_annotation_owner ON annotation(owner_id);
  CREATE INDEX i_annotation_group ON annotation(group_id);
  CREATE INDEX i_annotationannotationlink_owner ON annotationannotationlink(owner_id);
  CREATE INDEX i_annotationannotationlink_group ON annotationannotationlink(group_id);
  CREATE INDEX i_AnnotationAnnotationLink_parent ON annotationannotationlink(parent);
  CREATE INDEX i_AnnotationAnnotationLink_child ON annotationannotationlink(child);
  CREATE INDEX i_Arc_type ON arc(type);
  CREATE INDEX i_channel_owner ON channel(owner_id);
  CREATE INDEX i_channel_group ON channel(group_id);
  CREATE INDEX i_Channel_statsInfo ON channel(statsInfo);
  CREATE INDEX i_Channel_logicalChannel ON channel(logicalChannel);
  CREATE INDEX i_Channel_pixels ON channel(pixels);
  CREATE INDEX i_channelannotationlink_owner ON channelannotationlink(owner_id);
  CREATE INDEX i_channelannotationlink_group ON channelannotationlink(group_id);
  CREATE INDEX i_ChannelAnnotationLink_parent ON channelannotationlink(parent);
  CREATE INDEX i_ChannelAnnotationLink_child ON channelannotationlink(child);
  CREATE INDEX i_channelbinding_owner ON channelbinding(owner_id);
  CREATE INDEX i_channelbinding_group ON channelbinding(group_id);
  CREATE INDEX i_ChannelBinding_renderingDef ON channelbinding(renderingDef);
  CREATE INDEX i_ChannelBinding_family ON channelbinding(family);
  CREATE INDEX i_codomainmapcontext_owner ON codomainmapcontext(owner_id);
  CREATE INDEX i_codomainmapcontext_group ON codomainmapcontext(group_id);
  CREATE INDEX i_CodomainMapContext_channelBinding ON codomainmapcontext(channelBinding);
  CREATE INDEX i_dataset_owner ON dataset(owner_id);
  CREATE INDEX i_dataset_group ON dataset(group_id);
  CREATE INDEX i_datasetannotationlink_owner ON datasetannotationlink(owner_id);
  CREATE INDEX i_datasetannotationlink_group ON datasetannotationlink(group_id);
  CREATE INDEX i_DatasetAnnotationLink_parent ON datasetannotationlink(parent);
  CREATE INDEX i_DatasetAnnotationLink_child ON datasetannotationlink(child);
  CREATE INDEX i_datasetimagelink_owner ON datasetimagelink(owner_id);
  CREATE INDEX i_datasetimagelink_group ON datasetimagelink(group_id);
  CREATE INDEX i_DatasetImageLink_parent ON datasetimagelink(parent);
  CREATE INDEX i_DatasetImageLink_child ON datasetimagelink(child);
  CREATE INDEX i_detector_owner ON detector(owner_id);
  CREATE INDEX i_detector_group ON detector(group_id);
  CREATE INDEX i_Detector_voltage ON detector(voltage);
  CREATE INDEX i_Detector_type ON detector(type);
  CREATE INDEX i_Detector_instrument ON detector(instrument);
  CREATE INDEX i_detectorannotationlink_owner ON detectorannotationlink(owner_id);
  CREATE INDEX i_detectorannotationlink_group ON detectorannotationlink(group_id);
  CREATE INDEX i_DetectorAnnotationLink_parent ON detectorannotationlink(parent);
  CREATE INDEX i_DetectorAnnotationLink_child ON detectorannotationlink(child);
  CREATE INDEX i_detectorsettings_owner ON detectorsettings(owner_id);
  CREATE INDEX i_detectorsettings_group ON detectorsettings(group_id);
  CREATE INDEX i_DetectorSettings_voltage ON detectorsettings(voltage);
  CREATE INDEX i_DetectorSettings_readOutRate ON detectorsettings(readOutRate);
  CREATE INDEX i_DetectorSettings_binning ON detectorsettings(binning);
  CREATE INDEX i_DetectorSettings_detector ON detectorsettings(detector);
  CREATE INDEX i_dichroic_owner ON dichroic(owner_id);
  CREATE INDEX i_dichroic_group ON dichroic(group_id);
  CREATE INDEX i_Dichroic_instrument ON dichroic(instrument);
  CREATE INDEX i_dichroicannotationlink_owner ON dichroicannotationlink(owner_id);
  CREATE INDEX i_dichroicannotationlink_group ON dichroicannotationlink(group_id);
  CREATE INDEX i_DichroicAnnotationLink_parent ON dichroicannotationlink(parent);
  CREATE INDEX i_DichroicAnnotationLink_child ON dichroicannotationlink(child);
  CREATE INDEX i_Event_experimenter ON event(experimenter);
  CREATE INDEX i_Event_experimenterGroup ON event(experimenterGroup);
  CREATE INDEX i_Event_type ON event(type);
  CREATE INDEX i_Event_containingEvent ON event(containingEvent);
  CREATE INDEX i_Event_session ON event("session");
  CREATE INDEX i_EventLog_event ON eventlog(event);
  CREATE INDEX i_experiment_owner ON experiment(owner_id);
  CREATE INDEX i_experiment_group ON experiment(group_id);
  CREATE INDEX i_Experiment_type ON experiment(type);
  CREATE INDEX i_experimenterannotationlink_owner ON experimenterannotationlink(owner_id);
  CREATE INDEX i_experimenterannotationlink_group ON experimenterannotationlink(group_id);
  CREATE INDEX i_ExperimenterAnnotationLink_parent ON experimenterannotationlink(parent);
  CREATE INDEX i_ExperimenterAnnotationLink_child ON experimenterannotationlink(child);
  CREATE INDEX i_experimentergroupannotationlink_owner ON experimentergroupannotationlink(owner_id);
  CREATE INDEX i_experimentergroupannotationlink_group ON experimentergroupannotationlink(group_id);
  CREATE INDEX i_ExperimenterGroupAnnotationLink_parent ON experimentergroupannotationlink(parent);
  CREATE INDEX i_ExperimenterGroupAnnotationLink_child ON experimentergroupannotationlink(child);
  CREATE INDEX i_externalinfo_owner ON externalinfo(owner_id);
  CREATE INDEX i_externalinfo_group ON externalinfo(group_id);
  CREATE INDEX i_Filament_type ON filament(type);
  CREATE INDEX i_FileAnnotation_file ON annotation("file");
  CREATE INDEX i_fileset_owner ON fileset(owner_id);
  CREATE INDEX i_fileset_group ON fileset(group_id);
  CREATE INDEX i_filesetannotationlink_owner ON filesetannotationlink(owner_id);
  CREATE INDEX i_filesetannotationlink_group ON filesetannotationlink(group_id);
  CREATE INDEX i_FilesetAnnotationLink_parent ON filesetannotationlink(parent);
  CREATE INDEX i_FilesetAnnotationLink_child ON filesetannotationlink(child);
  CREATE INDEX i_filesetentry_owner ON filesetentry(owner_id);
  CREATE INDEX i_filesetentry_group ON filesetentry(group_id);
  CREATE INDEX i_FilesetEntry_fileset ON filesetentry(fileset);
  CREATE INDEX i_FilesetEntry_originalFile ON filesetentry(originalFile);
  CREATE INDEX i_filesetjoblink_owner ON filesetjoblink(owner_id);
  CREATE INDEX i_filesetjoblink_group ON filesetjoblink(group_id);
  CREATE INDEX i_FilesetJobLink_parent ON filesetjoblink(parent);
  CREATE INDEX i_FilesetJobLink_child ON filesetjoblink(child);
  CREATE INDEX i_filter_owner ON filter(owner_id);
  CREATE INDEX i_filter_group ON filter(group_id);
  CREATE INDEX i_Filter_type ON filter(type);
  CREATE INDEX i_Filter_transmittanceRange ON filter(transmittanceRange);
  CREATE INDEX i_Filter_instrument ON filter(instrument);
  CREATE INDEX i_filterannotationlink_owner ON filterannotationlink(owner_id);
  CREATE INDEX i_filterannotationlink_group ON filterannotationlink(group_id);
  CREATE INDEX i_FilterAnnotationLink_parent ON filterannotationlink(parent);
  CREATE INDEX i_FilterAnnotationLink_child ON filterannotationlink(child);
  CREATE INDEX i_filterset_owner ON filterset(owner_id);
  CREATE INDEX i_filterset_group ON filterset(group_id);
  CREATE INDEX i_FilterSet_instrument ON filterset(instrument);
  CREATE INDEX i_FilterSet_dichroic ON filterset(dichroic);
  CREATE INDEX i_filtersetemissionfilterlink_owner ON filtersetemissionfilterlink(owner_id);
  CREATE INDEX i_filtersetemissionfilterlink_group ON filtersetemissionfilterlink(group_id);
  CREATE INDEX i_FilterSetEmissionFilterLink_parent ON filtersetemissionfilterlink(parent);
  CREATE INDEX i_FilterSetEmissionFilterLink_child ON filtersetemissionfilterlink(child);
  CREATE INDEX i_filtersetexcitationfilterlink_owner ON filtersetexcitationfilterlink(owner_id);
  CREATE INDEX i_filtersetexcitationfilterlink_group ON filtersetexcitationfilterlink(group_id);
  CREATE INDEX i_FilterSetExcitationFilterLink_parent ON filtersetexcitationfilterlink(parent);
  CREATE INDEX i_FilterSetExcitationFilterLink_child ON filtersetexcitationfilterlink(child);
  CREATE INDEX i_folder_owner ON folder(owner_id);
  CREATE INDEX i_folder_group ON folder(group_id);
  CREATE INDEX i_Folder_parentFolder ON folder(parentFolder);
  CREATE INDEX i_folderannotationlink_owner ON folderannotationlink(owner_id);
  CREATE INDEX i_folderannotationlink_group ON folderannotationlink(group_id);
  CREATE INDEX i_FolderAnnotationLink_parent ON folderannotationlink(parent);
  CREATE INDEX i_FolderAnnotationLink_child ON folderannotationlink(child);
  CREATE INDEX i_folderimagelink_owner ON folderimagelink(owner_id);
  CREATE INDEX i_folderimagelink_group ON folderimagelink(group_id);
  CREATE INDEX i_FolderImageLink_parent ON folderimagelink(parent);
  CREATE INDEX i_FolderImageLink_child ON folderimagelink(child);
  CREATE INDEX i_folderroilink_owner ON folderroilink(owner_id);
  CREATE INDEX i_folderroilink_group ON folderroilink(group_id);
  CREATE INDEX i_FolderRoiLink_parent ON folderroilink(parent);
  CREATE INDEX i_FolderRoiLink_child ON folderroilink(child);
  CREATE INDEX i_GroupExperimenterMap_parent ON groupexperimentermap(parent);
  CREATE INDEX i_GroupExperimenterMap_child ON groupexperimentermap(child);
  CREATE INDEX i_image_owner ON image(owner_id);
  CREATE INDEX i_image_group ON image(group_id);
  CREATE INDEX i_Image_format ON image(format);
  CREATE INDEX i_Image_imagingEnvironment ON image(imagingEnvironment);
  CREATE INDEX i_Image_objectiveSettings ON image(objectiveSettings);
  CREATE INDEX i_Image_instrument ON image(instrument);
  CREATE INDEX i_Image_stageLabel ON image(stageLabel);
  CREATE INDEX i_Image_experiment ON image(experiment);
  CREATE INDEX i_Image_fileset ON image(fileset);
  CREATE INDEX i_imageannotationlink_owner ON imageannotationlink(owner_id);
  CREATE INDEX i_imageannotationlink_group ON imageannotationlink(group_id);
  CREATE INDEX i_ImageAnnotationLink_parent ON imageannotationlink(parent);
  CREATE INDEX i_ImageAnnotationLink_child ON imageannotationlink(child);
  CREATE INDEX i_imagingenvironment_owner ON imagingenvironment(owner_id);
  CREATE INDEX i_imagingenvironment_group ON imagingenvironment(group_id);
  CREATE INDEX i_ImagingEnvironment_temperature ON imagingenvironment(temperature);
  CREATE INDEX i_ImagingEnvironment_airPressure ON imagingenvironment(airPressure);
  CREATE INDEX i_instrument_owner ON instrument(owner_id);
  CREATE INDEX i_instrument_group ON instrument(group_id);
  CREATE INDEX i_Instrument_microscope ON instrument(microscope);
  CREATE INDEX i_instrumentannotationlink_owner ON instrumentannotationlink(owner_id);
  CREATE INDEX i_instrumentannotationlink_group ON instrumentannotationlink(group_id);
  CREATE INDEX i_InstrumentAnnotationLink_parent ON instrumentannotationlink(parent);
  CREATE INDEX i_InstrumentAnnotationLink_child ON instrumentannotationlink(child);
  CREATE INDEX i_job_owner ON job(owner_id);
  CREATE INDEX i_job_group ON job(group_id);
  CREATE INDEX i_Job_status ON job(status);
  CREATE INDEX i_joboriginalfilelink_owner ON joboriginalfilelink(owner_id);
  CREATE INDEX i_joboriginalfilelink_group ON joboriginalfilelink(group_id);
  CREATE INDEX i_JobOriginalFileLink_parent ON joboriginalfilelink(parent);
  CREATE INDEX i_JobOriginalFileLink_child ON joboriginalfilelink(child);
  CREATE INDEX i_Laser_type ON laser(type);
  CREATE INDEX i_Laser_laserMedium ON laser(laserMedium);
  CREATE INDEX i_Laser_pulse ON laser(pulse);
  CREATE INDEX i_Laser_wavelength ON laser(wavelength);
  CREATE INDEX i_Laser_pump ON laser(pump);
  CREATE INDEX i_Laser_repetitionRate ON laser(repetitionRate);
  CREATE INDEX i_lightpath_owner ON lightpath(owner_id);
  CREATE INDEX i_lightpath_group ON lightpath(group_id);
  CREATE INDEX i_LightPath_dichroic ON lightpath(dichroic);
  CREATE INDEX i_lightpathannotationlink_owner ON lightpathannotationlink(owner_id);
  CREATE INDEX i_lightpathannotationlink_group ON lightpathannotationlink(group_id);
  CREATE INDEX i_LightPathAnnotationLink_parent ON lightpathannotationlink(parent);
  CREATE INDEX i_LightPathAnnotationLink_child ON lightpathannotationlink(child);
  CREATE INDEX i_lightpathemissionfilterlink_owner ON lightpathemissionfilterlink(owner_id);
  CREATE INDEX i_lightpathemissionfilterlink_group ON lightpathemissionfilterlink(group_id);
  CREATE INDEX i_LightPathEmissionFilterLink_parent ON lightpathemissionfilterlink(parent);
  CREATE INDEX i_LightPathEmissionFilterLink_child ON lightpathemissionfilterlink(child);
  CREATE INDEX i_lightpathexcitationfilterlink_owner ON lightpathexcitationfilterlink(owner_id);
  CREATE INDEX i_lightpathexcitationfilterlink_group ON lightpathexcitationfilterlink(group_id);
  CREATE INDEX i_LightPathExcitationFilterLink_parent ON lightpathexcitationfilterlink(parent);
  CREATE INDEX i_LightPathExcitationFilterLink_child ON lightpathexcitationfilterlink(child);
  CREATE INDEX i_lightsettings_owner ON lightsettings(owner_id);
  CREATE INDEX i_lightsettings_group ON lightsettings(group_id);
  CREATE INDEX i_LightSettings_wavelength ON lightsettings(wavelength);
  CREATE INDEX i_LightSettings_lightSource ON lightsettings(lightSource);
  CREATE INDEX i_LightSettings_microbeamManipulation ON lightsettings(microbeamManipulation);
  CREATE INDEX i_lightsource_owner ON lightsource(owner_id);
  CREATE INDEX i_lightsource_group ON lightsource(group_id);
  CREATE INDEX i_LightSource_power ON lightsource("power");
  CREATE INDEX i_LightSource_instrument ON lightsource(instrument);
  CREATE INDEX i_lightsourceannotationlink_owner ON lightsourceannotationlink(owner_id);
  CREATE INDEX i_lightsourceannotationlink_group ON lightsourceannotationlink(group_id);
  CREATE INDEX i_LightSourceAnnotationLink_parent ON lightsourceannotationlink(parent);
  CREATE INDEX i_LightSourceAnnotationLink_child ON lightsourceannotationlink(child);
  CREATE INDEX i_link_owner ON link(owner_id);
  CREATE INDEX i_link_group ON link(group_id);
  CREATE INDEX i_logicalchannel_owner ON logicalchannel(owner_id);
  CREATE INDEX i_logicalchannel_group ON logicalchannel(group_id);
  CREATE INDEX i_LogicalChannel_pinHoleSize ON logicalchannel(pinHoleSize);
  CREATE INDEX i_LogicalChannel_illumination ON logicalchannel(illumination);
  CREATE INDEX i_LogicalChannel_contrastMethod ON logicalchannel(contrastMethod);
  CREATE INDEX i_LogicalChannel_excitationWave ON logicalchannel(excitationWave);
  CREATE INDEX i_LogicalChannel_emissionWave ON logicalchannel(emissionWave);
  CREATE INDEX i_LogicalChannel_otf ON logicalchannel(otf);
  CREATE INDEX i_LogicalChannel_detectorSettings ON logicalchannel(detectorSettings);
  CREATE INDEX i_LogicalChannel_lightSourceSettings ON logicalchannel(lightSourceSettings);
  CREATE INDEX i_LogicalChannel_filterSet ON logicalchannel(filterSet);
  CREATE INDEX i_LogicalChannel_photometricInterpretation ON logicalchannel(photometricInterpretation);
  CREATE INDEX i_LogicalChannel_mode ON logicalchannel("mode");
  CREATE INDEX i_LogicalChannel_lightPath ON logicalchannel(lightPath);
  CREATE INDEX i_Mask_pixels ON shape(pixels);
  CREATE INDEX i_microbeammanipulation_owner ON microbeammanipulation(owner_id);
  CREATE INDEX i_microbeammanipulation_group ON microbeammanipulation(group_id);
  CREATE INDEX i_MicrobeamManipulation_type ON microbeammanipulation(type);
  CREATE INDEX i_MicrobeamManipulation_experiment ON microbeammanipulation(experiment);
  CREATE INDEX i_microscope_owner ON microscope(owner_id);
  CREATE INDEX i_microscope_group ON microscope(group_id);
  CREATE INDEX i_Microscope_type ON microscope(type);
  CREATE INDEX i_namespaceannotationlink_owner ON namespaceannotationlink(owner_id);
  CREATE INDEX i_namespaceannotationlink_group ON namespaceannotationlink(group_id);
  CREATE INDEX i_NamespaceAnnotationLink_parent ON namespaceannotationlink(parent);
  CREATE INDEX i_NamespaceAnnotationLink_child ON namespaceannotationlink(child);
  CREATE INDEX i_nodeannotationlink_owner ON nodeannotationlink(owner_id);
  CREATE INDEX i_nodeannotationlink_group ON nodeannotationlink(group_id);
  CREATE INDEX i_NodeAnnotationLink_parent ON nodeannotationlink(parent);
  CREATE INDEX i_NodeAnnotationLink_child ON nodeannotationlink(child);
  CREATE INDEX i_otf_owner ON otf(owner_id);
  CREATE INDEX i_otf_group ON otf(group_id);
  CREATE INDEX i_OTF_pixelsType ON otf(pixelsType);
  CREATE INDEX i_OTF_filterSet ON otf(filterSet);
  CREATE INDEX i_OTF_objective ON otf(objective);
  CREATE INDEX i_OTF_instrument ON otf(instrument);
  CREATE INDEX i_objective_owner ON objective(owner_id);
  CREATE INDEX i_objective_group ON objective(group_id);
  CREATE INDEX i_Objective_immersion ON objective(immersion);
  CREATE INDEX i_Objective_correction ON objective(correction);
  CREATE INDEX i_Objective_workingDistance ON objective(workingDistance);
  CREATE INDEX i_Objective_instrument ON objective(instrument);
  CREATE INDEX i_objectiveannotationlink_owner ON objectiveannotationlink(owner_id);
  CREATE INDEX i_objectiveannotationlink_group ON objectiveannotationlink(group_id);
  CREATE INDEX i_ObjectiveAnnotationLink_parent ON objectiveannotationlink(parent);
  CREATE INDEX i_ObjectiveAnnotationLink_child ON objectiveannotationlink(child);
  CREATE INDEX i_objectivesettings_owner ON objectivesettings(owner_id);
  CREATE INDEX i_objectivesettings_group ON objectivesettings(group_id);
  CREATE INDEX i_ObjectiveSettings_medium ON objectivesettings(medium);
  CREATE INDEX i_ObjectiveSettings_objective ON objectivesettings(objective);
  CREATE INDEX i_originalfile_owner ON originalfile(owner_id);
  CREATE INDEX i_originalfile_group ON originalfile(group_id);
  CREATE INDEX i_OriginalFile_hasher ON originalfile(hasher);
  CREATE INDEX i_originalfileannotationlink_owner ON originalfileannotationlink(owner_id);
  CREATE INDEX i_originalfileannotationlink_group ON originalfileannotationlink(group_id);
  CREATE INDEX i_OriginalFileAnnotationLink_parent ON originalfileannotationlink(parent);
  CREATE INDEX i_OriginalFileAnnotationLink_child ON originalfileannotationlink(child);
  CREATE INDEX i_pixels_owner ON pixels(owner_id);
  CREATE INDEX i_pixels_group ON pixels(group_id);
  CREATE INDEX i_Pixels_image ON pixels(image);
  CREATE INDEX i_Pixels_relatedTo ON pixels(relatedTo);
  CREATE INDEX i_Pixels_pixelsType ON pixels(pixelsType);
  CREATE INDEX i_Pixels_dimensionOrder ON pixels(dimensionOrder);
  CREATE INDEX i_Pixels_physicalSizeX ON pixels(physicalSizeX);
  CREATE INDEX i_Pixels_physicalSizeY ON pixels(physicalSizeY);
  CREATE INDEX i_Pixels_physicalSizeZ ON pixels(physicalSizeZ);
  CREATE INDEX i_Pixels_timeIncrement ON pixels(timeIncrement);
  CREATE INDEX i_pixelsoriginalfilemap_owner ON pixelsoriginalfilemap(owner_id);
  CREATE INDEX i_pixelsoriginalfilemap_group ON pixelsoriginalfilemap(group_id);
  CREATE INDEX i_PixelsOriginalFileMap_parent ON pixelsoriginalfilemap(parent);
  CREATE INDEX i_PixelsOriginalFileMap_child ON pixelsoriginalfilemap(child);
  CREATE INDEX i_planeinfo_owner ON planeinfo(owner_id);
  CREATE INDEX i_planeinfo_group ON planeinfo(group_id);
  CREATE INDEX i_PlaneInfo_pixels ON planeinfo(pixels);
  CREATE INDEX i_PlaneInfo_deltaT ON planeinfo(deltaT);
  CREATE INDEX i_PlaneInfo_positionX ON planeinfo(positionX);
  CREATE INDEX i_PlaneInfo_positionY ON planeinfo(positionY);
  CREATE INDEX i_PlaneInfo_positionZ ON planeinfo(positionZ);
  CREATE INDEX i_PlaneInfo_exposureTime ON planeinfo(exposureTime);
  CREATE INDEX i_planeinfoannotationlink_owner ON planeinfoannotationlink(owner_id);
  CREATE INDEX i_planeinfoannotationlink_group ON planeinfoannotationlink(group_id);
  CREATE INDEX i_PlaneInfoAnnotationLink_parent ON planeinfoannotationlink(parent);
  CREATE INDEX i_PlaneInfoAnnotationLink_child ON planeinfoannotationlink(child);
  CREATE INDEX i_plate_owner ON plate(owner_id);
  CREATE INDEX i_plate_group ON plate(group_id);
  CREATE INDEX i_Plate_wellOriginX ON plate(wellOriginX);
  CREATE INDEX i_Plate_wellOriginY ON plate(wellOriginY);
  CREATE INDEX i_plateacquisition_owner ON plateacquisition(owner_id);
  CREATE INDEX i_plateacquisition_group ON plateacquisition(group_id);
  CREATE INDEX i_PlateAcquisition_plate ON plateacquisition(plate);
  CREATE INDEX i_plateacquisitionannotationlink_owner ON plateacquisitionannotationlink(owner_id);
  CREATE INDEX i_plateacquisitionannotationlink_group ON plateacquisitionannotationlink(group_id);
  CREATE INDEX i_PlateAcquisitionAnnotationLink_parent ON plateacquisitionannotationlink(parent);
  CREATE INDEX i_PlateAcquisitionAnnotationLink_child ON plateacquisitionannotationlink(child);
  CREATE INDEX i_plateannotationlink_owner ON plateannotationlink(owner_id);
  CREATE INDEX i_plateannotationlink_group ON plateannotationlink(group_id);
  CREATE INDEX i_PlateAnnotationLink_parent ON plateannotationlink(parent);
  CREATE INDEX i_PlateAnnotationLink_child ON plateannotationlink(child);
  CREATE INDEX i_project_owner ON project(owner_id);
  CREATE INDEX i_project_group ON project(group_id);
  CREATE INDEX i_projectannotationlink_owner ON projectannotationlink(owner_id);
  CREATE INDEX i_projectannotationlink_group ON projectannotationlink(group_id);
  CREATE INDEX i_ProjectAnnotationLink_parent ON projectannotationlink(parent);
  CREATE INDEX i_ProjectAnnotationLink_child ON projectannotationlink(child);
  CREATE INDEX i_projectdatasetlink_owner ON projectdatasetlink(owner_id);
  CREATE INDEX i_projectdatasetlink_group ON projectdatasetlink(group_id);
  CREATE INDEX i_ProjectDatasetLink_parent ON projectdatasetlink(parent);
  CREATE INDEX i_ProjectDatasetLink_child ON projectdatasetlink(child);
  CREATE INDEX i_projectiondef_owner ON projectiondef(owner_id);
  CREATE INDEX i_projectiondef_group ON projectiondef(group_id);
  CREATE INDEX i_ProjectionDef_renderingDef ON projectiondef(renderingDef);
  CREATE INDEX i_ProjectionDef_axis ON projectiondef(axis);
  CREATE INDEX i_ProjectionDef_type ON projectiondef(type);
  CREATE INDEX i_quantumdef_owner ON quantumdef(owner_id);
  CREATE INDEX i_quantumdef_group ON quantumdef(group_id);
  CREATE INDEX i_reagent_owner ON reagent(owner_id);
  CREATE INDEX i_reagent_group ON reagent(group_id);
  CREATE INDEX i_Reagent_screen ON reagent(screen);
  CREATE INDEX i_reagentannotationlink_owner ON reagentannotationlink(owner_id);
  CREATE INDEX i_reagentannotationlink_group ON reagentannotationlink(group_id);
  CREATE INDEX i_ReagentAnnotationLink_parent ON reagentannotationlink(parent);
  CREATE INDEX i_ReagentAnnotationLink_child ON reagentannotationlink(child);
  CREATE INDEX i_renderingdef_owner ON renderingdef(owner_id);
  CREATE INDEX i_renderingdef_group ON renderingdef(group_id);
  CREATE INDEX i_RenderingDef_pixels ON renderingdef(pixels);
  CREATE INDEX i_RenderingDef_model ON renderingdef(model);
  CREATE INDEX i_RenderingDef_quantization ON renderingdef(quantization);
  CREATE INDEX i_roi_owner ON roi(owner_id);
  CREATE INDEX i_roi_group ON roi(group_id);
  CREATE INDEX i_Roi_image ON roi(image);
  CREATE INDEX i_Roi_source ON roi(source);
  CREATE INDEX i_roiannotationlink_owner ON roiannotationlink(owner_id);
  CREATE INDEX i_roiannotationlink_group ON roiannotationlink(group_id);
  CREATE INDEX i_RoiAnnotationLink_parent ON roiannotationlink(parent);
  CREATE INDEX i_RoiAnnotationLink_child ON roiannotationlink(child);
  CREATE INDEX i_screen_owner ON screen(owner_id);
  CREATE INDEX i_screen_group ON screen(group_id);
  CREATE INDEX i_screenannotationlink_owner ON screenannotationlink(owner_id);
  CREATE INDEX i_screenannotationlink_group ON screenannotationlink(group_id);
  CREATE INDEX i_ScreenAnnotationLink_parent ON screenannotationlink(parent);
  CREATE INDEX i_ScreenAnnotationLink_child ON screenannotationlink(child);
  CREATE INDEX i_screenplatelink_owner ON screenplatelink(owner_id);
  CREATE INDEX i_screenplatelink_group ON screenplatelink(group_id);
  CREATE INDEX i_ScreenPlateLink_parent ON screenplatelink(parent);
  CREATE INDEX i_ScreenPlateLink_child ON screenplatelink(child);
  CREATE INDEX i_Session_node ON session(node);
  CREATE INDEX i_Session_owner ON session(owner);
  CREATE INDEX i_Session_sudoer ON session(sudoer);
  CREATE INDEX i_sessionannotationlink_owner ON sessionannotationlink(owner_id);
  CREATE INDEX i_sessionannotationlink_group ON sessionannotationlink(group_id);
  CREATE INDEX i_SessionAnnotationLink_parent ON sessionannotationlink(parent);
  CREATE INDEX i_SessionAnnotationLink_child ON sessionannotationlink(child);
  CREATE INDEX i_shape_owner ON shape(owner_id);
  CREATE INDEX i_shape_group ON shape(group_id);
  CREATE INDEX i_Shape_roi ON shape(roi);
  CREATE INDEX i_Shape_transform ON shape(transform);
  CREATE INDEX i_Shape_strokeWidth ON shape(strokeWidth);
  CREATE INDEX i_Shape_fontSize ON shape(fontSize);
  CREATE INDEX i_shapeannotationlink_owner ON shapeannotationlink(owner_id);
  CREATE INDEX i_shapeannotationlink_group ON shapeannotationlink(group_id);
  CREATE INDEX i_ShapeAnnotationLink_parent ON shapeannotationlink(parent);
  CREATE INDEX i_ShapeAnnotationLink_child ON shapeannotationlink(child);
  CREATE INDEX i_Share_group ON share("group");
  CREATE INDEX i_ShareMember_parent ON sharemember(parent);
  CREATE INDEX i_ShareMember_child ON sharemember(child);
  CREATE INDEX i_stagelabel_owner ON stagelabel(owner_id);
  CREATE INDEX i_stagelabel_group ON stagelabel(group_id);
  CREATE INDEX i_StageLabel_positionX ON stagelabel(positionX);
  CREATE INDEX i_StageLabel_positionY ON stagelabel(positionY);
  CREATE INDEX i_StageLabel_positionZ ON stagelabel(positionZ);
  CREATE INDEX i_statsinfo_owner ON statsinfo(owner_id);
  CREATE INDEX i_statsinfo_group ON statsinfo(group_id);
  CREATE INDEX i_thumbnail_owner ON thumbnail(owner_id);
  CREATE INDEX i_thumbnail_group ON thumbnail(group_id);
  CREATE INDEX i_Thumbnail_pixels ON thumbnail(pixels);
  CREATE INDEX i_transmittancerange_owner ON transmittancerange(owner_id);
  CREATE INDEX i_transmittancerange_group ON transmittancerange(group_id);
  CREATE INDEX i_TransmittanceRange_cutIn ON transmittancerange(cutIn);
  CREATE INDEX i_TransmittanceRange_cutOut ON transmittancerange(cutOut);
  CREATE INDEX i_TransmittanceRange_cutInTolerance ON transmittancerange(cutInTolerance);
  CREATE INDEX i_TransmittanceRange_cutOutTolerance ON transmittancerange(cutOutTolerance);
  CREATE INDEX i_well_owner ON well(owner_id);
  CREATE INDEX i_well_group ON well(group_id);
  CREATE INDEX i_Well_plate ON well(plate);
  CREATE INDEX i_wellannotationlink_owner ON wellannotationlink(owner_id);
  CREATE INDEX i_wellannotationlink_group ON wellannotationlink(group_id);
  CREATE INDEX i_WellAnnotationLink_parent ON wellannotationlink(parent);
  CREATE INDEX i_WellAnnotationLink_child ON wellannotationlink(child);
  CREATE INDEX i_wellreagentlink_owner ON wellreagentlink(owner_id);
  CREATE INDEX i_wellreagentlink_group ON wellreagentlink(group_id);
  CREATE INDEX i_WellReagentLink_parent ON wellreagentlink(parent);
  CREATE INDEX i_WellReagentLink_child ON wellreagentlink(child);
  CREATE INDEX i_wellsample_owner ON wellsample(owner_id);
  CREATE INDEX i_wellsample_group ON wellsample(group_id);
  CREATE INDEX i_WellSample_posX ON wellsample(posX);
  CREATE INDEX i_WellSample_posY ON wellsample(posY);
  CREATE INDEX i_WellSample_plateAcquisition ON wellsample(plateAcquisition);
  CREATE INDEX i_WellSample_well ON wellsample(well);
  CREATE INDEX i_WellSample_image ON wellsample(image);

CREATE INDEX annotation_name ON annotation(name);
CREATE INDEX namespace_displayname ON namespace(displayname);
CREATE INDEX roi_name ON roi(name);

--
-- Finally, a function for showing our permissions
-- select id, ome_perms(permissions) FROM sometable...
--
CREATE OR REPLACE FUNCTION ome_perms(p INT8) RETURNS VARCHAR AS '
DECLARE
    ur CHAR DEFAULT ''-'';
    uw CHAR DEFAULT ''-'';
    gr CHAR DEFAULT ''-'';
    gw CHAR DEFAULT ''-'';
    wr CHAR DEFAULT ''-'';
    ww CHAR DEFAULT ''-'';
BEGIN
    -- annotate flags may be overwritten by write flags

    -- shift 8 (-RWA--------)
    SELECT INTO ur CASE WHEN (p & 1024) = 1024 THEN ''r'' ELSE ''-'' END;
    SELECT INTO uw CASE WHEN (p &  512) =  512 THEN ''w''
                        WHEN (p &  256) =  256 THEN ''a'' ELSE ''-'' END;

    -- shift 4 (-----RWA----)
    SELECT INTO gr CASE WHEN (p &   64) =   64 THEN ''r'' ELSE ''-'' END;
    SELECT INTO gw CASE WHEN (p &   32) =   32 THEN ''w''
                        WHEN (p &   16) =   16 THEN ''a'' ELSE ''-'' END;

    -- shift 0 (---------RWA)
    SELECT INTO wr CASE WHEN (p &    4) =    4 THEN ''r'' ELSE ''-'' END;
    SELECT INTO ww CASE WHEN (p &    2) =    2 THEN ''w''
                        WHEN (p &    1) =    1 THEN ''a'' ELSE ''-'' END;

    RETURN ur || uw || gr || gw || wr || ww;
END;' LANGUAGE plpgsql;


set constraints all deferred;

--
-- #1176 : create our own nextval() functionality for more consistent
-- sequence operation in hibernate. This functionality was updated for
-- OMERO 4.2 (#2508) in order to prevent logging during triggers.
--

CREATE OR REPLACE FUNCTION ome_nextval(seq VARCHAR) RETURNS INT8 AS '
BEGIN
      RETURN ome_nextval(seq, 1);
END;' LANGUAGE plpgsql;

-- These renamings allow us to reuse the Hibernate-generated tables
-- for sequence generation. Eventually, a method might be found to
-- make Hibernate generate them for us.
CREATE SEQUENCE _lock_seq;
ALTER TABLE seq_table RENAME TO _lock_ids;
ALTER TABLE _lock_ids RENAME COLUMN sequence_name TO name;
ALTER TABLE _lock_ids DROP CONSTRAINT seq_table_pkey;
ALTER TABLE _lock_ids DROP COLUMN next_val;
ALTER TABLE _lock_ids ADD COLUMN id int PRIMARY KEY DEFAULT nextval('_lock_seq');
CREATE UNIQUE INDEX _lock_ids_name ON _lock_ids (name);

CREATE OR REPLACE FUNCTION ome_nextval(seq VARCHAR, increment int4) RETURNS INT8 AS '
DECLARE
      Lid  int4;
      nv   int8;
BEGIN
      SELECT id INTO Lid FROM _lock_ids WHERE name = seq;
      IF Lid IS NULL THEN
          SELECT INTO Lid nextval(''_lock_seq'');
          INSERT INTO _lock_ids (id, name) VALUES (Lid, seq);
      END IF;

      PERFORM pg_advisory_lock(1, Lid);

      BEGIN
          PERFORM nextval(seq) FROM generate_series(1, increment);
          SELECT currval(seq) INTO nv;
      EXCEPTION
          WHEN OTHERS THEN
              PERFORM pg_advisory_unlock(1, Lid);
          RAISE;
      END;

      PERFORM pg_advisory_unlock(1, Lid);

      RETURN nv;

END;' LANGUAGE plpgsql;

CREATE SEQUENCE seq_acquisitionmode; INSERT INTO _lock_ids (name, id) SELECT 'seq_acquisitionmode', nextval('_lock_seq');
CREATE SEQUENCE seq_adminprivilege; INSERT INTO _lock_ids (name, id) SELECT 'seq_adminprivilege', nextval('_lock_seq');
CREATE SEQUENCE seq_affinetransform; INSERT INTO _lock_ids (name, id) SELECT 'seq_affinetransform', nextval('_lock_seq');
CREATE SEQUENCE seq_annotation; INSERT INTO _lock_ids (name, id) SELECT 'seq_annotation', nextval('_lock_seq');
CREATE SEQUENCE seq_annotationannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_annotationannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_arctype; INSERT INTO _lock_ids (name, id) SELECT 'seq_arctype', nextval('_lock_seq');
CREATE SEQUENCE seq_binning; INSERT INTO _lock_ids (name, id) SELECT 'seq_binning', nextval('_lock_seq');
CREATE SEQUENCE seq_channel; INSERT INTO _lock_ids (name, id) SELECT 'seq_channel', nextval('_lock_seq');
CREATE SEQUENCE seq_channelannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_channelannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_channelbinding; INSERT INTO _lock_ids (name, id) SELECT 'seq_channelbinding', nextval('_lock_seq');
CREATE SEQUENCE seq_checksumalgorithm; INSERT INTO _lock_ids (name, id) SELECT 'seq_checksumalgorithm', nextval('_lock_seq');
CREATE SEQUENCE seq_codomainmapcontext; INSERT INTO _lock_ids (name, id) SELECT 'seq_codomainmapcontext', nextval('_lock_seq');
CREATE SEQUENCE seq_contrastmethod; INSERT INTO _lock_ids (name, id) SELECT 'seq_contrastmethod', nextval('_lock_seq');
CREATE SEQUENCE seq_correction; INSERT INTO _lock_ids (name, id) SELECT 'seq_correction', nextval('_lock_seq');
CREATE SEQUENCE seq_dbpatch; INSERT INTO _lock_ids (name, id) SELECT 'seq_dbpatch', nextval('_lock_seq');
CREATE SEQUENCE seq_dataset; INSERT INTO _lock_ids (name, id) SELECT 'seq_dataset', nextval('_lock_seq');
CREATE SEQUENCE seq_datasetannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_datasetannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_datasetimagelink; INSERT INTO _lock_ids (name, id) SELECT 'seq_datasetimagelink', nextval('_lock_seq');
CREATE SEQUENCE seq_detector; INSERT INTO _lock_ids (name, id) SELECT 'seq_detector', nextval('_lock_seq');
CREATE SEQUENCE seq_detectorannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_detectorannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_detectorsettings; INSERT INTO _lock_ids (name, id) SELECT 'seq_detectorsettings', nextval('_lock_seq');
CREATE SEQUENCE seq_detectortype; INSERT INTO _lock_ids (name, id) SELECT 'seq_detectortype', nextval('_lock_seq');
CREATE SEQUENCE seq_dichroic; INSERT INTO _lock_ids (name, id) SELECT 'seq_dichroic', nextval('_lock_seq');
CREATE SEQUENCE seq_dichroicannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_dichroicannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_dimensionorder; INSERT INTO _lock_ids (name, id) SELECT 'seq_dimensionorder', nextval('_lock_seq');
CREATE SEQUENCE seq_event; INSERT INTO _lock_ids (name, id) SELECT 'seq_event', nextval('_lock_seq');
CREATE SEQUENCE seq_eventlog; INSERT INTO _lock_ids (name, id) SELECT 'seq_eventlog', nextval('_lock_seq');
CREATE SEQUENCE seq_eventtype; INSERT INTO _lock_ids (name, id) SELECT 'seq_eventtype', nextval('_lock_seq');
CREATE SEQUENCE seq_experiment; INSERT INTO _lock_ids (name, id) SELECT 'seq_experiment', nextval('_lock_seq');
CREATE SEQUENCE seq_experimenttype; INSERT INTO _lock_ids (name, id) SELECT 'seq_experimenttype', nextval('_lock_seq');
CREATE SEQUENCE seq_experimenter; INSERT INTO _lock_ids (name, id) SELECT 'seq_experimenter', nextval('_lock_seq');
CREATE SEQUENCE seq_experimenterannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_experimenterannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_experimentergroup; INSERT INTO _lock_ids (name, id) SELECT 'seq_experimentergroup', nextval('_lock_seq');
CREATE SEQUENCE seq_experimentergroupannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_experimentergroupannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_externalinfo; INSERT INTO _lock_ids (name, id) SELECT 'seq_externalinfo', nextval('_lock_seq');
CREATE SEQUENCE seq_family; INSERT INTO _lock_ids (name, id) SELECT 'seq_family', nextval('_lock_seq');
CREATE SEQUENCE seq_filamenttype; INSERT INTO _lock_ids (name, id) SELECT 'seq_filamenttype', nextval('_lock_seq');
CREATE SEQUENCE seq_fileset; INSERT INTO _lock_ids (name, id) SELECT 'seq_fileset', nextval('_lock_seq');
CREATE SEQUENCE seq_filesetannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_filesetannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_filesetentry; INSERT INTO _lock_ids (name, id) SELECT 'seq_filesetentry', nextval('_lock_seq');
CREATE SEQUENCE seq_filesetjoblink; INSERT INTO _lock_ids (name, id) SELECT 'seq_filesetjoblink', nextval('_lock_seq');
CREATE SEQUENCE seq_filter; INSERT INTO _lock_ids (name, id) SELECT 'seq_filter', nextval('_lock_seq');
CREATE SEQUENCE seq_filterannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_filterannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_filterset; INSERT INTO _lock_ids (name, id) SELECT 'seq_filterset', nextval('_lock_seq');
CREATE SEQUENCE seq_filtersetemissionfilterlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_filtersetemissionfilterlink', nextval('_lock_seq');
CREATE SEQUENCE seq_filtersetexcitationfilterlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_filtersetexcitationfilterlink', nextval('_lock_seq');
CREATE SEQUENCE seq_filtertype; INSERT INTO _lock_ids (name, id) SELECT 'seq_filtertype', nextval('_lock_seq');
CREATE SEQUENCE seq_folder; INSERT INTO _lock_ids (name, id) SELECT 'seq_folder', nextval('_lock_seq');
CREATE SEQUENCE seq_folderannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_folderannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_folderimagelink; INSERT INTO _lock_ids (name, id) SELECT 'seq_folderimagelink', nextval('_lock_seq');
CREATE SEQUENCE seq_folderroilink; INSERT INTO _lock_ids (name, id) SELECT 'seq_folderroilink', nextval('_lock_seq');
CREATE SEQUENCE seq_format; INSERT INTO _lock_ids (name, id) SELECT 'seq_format', nextval('_lock_seq');
CREATE SEQUENCE seq_groupexperimentermap; INSERT INTO _lock_ids (name, id) SELECT 'seq_groupexperimentermap', nextval('_lock_seq');
CREATE SEQUENCE seq_illumination; INSERT INTO _lock_ids (name, id) SELECT 'seq_illumination', nextval('_lock_seq');
CREATE SEQUENCE seq_image; INSERT INTO _lock_ids (name, id) SELECT 'seq_image', nextval('_lock_seq');
CREATE SEQUENCE seq_imageannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_imageannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_imagingenvironment; INSERT INTO _lock_ids (name, id) SELECT 'seq_imagingenvironment', nextval('_lock_seq');
CREATE SEQUENCE seq_immersion; INSERT INTO _lock_ids (name, id) SELECT 'seq_immersion', nextval('_lock_seq');
CREATE SEQUENCE seq_instrument; INSERT INTO _lock_ids (name, id) SELECT 'seq_instrument', nextval('_lock_seq');
CREATE SEQUENCE seq_instrumentannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_instrumentannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_job; INSERT INTO _lock_ids (name, id) SELECT 'seq_job', nextval('_lock_seq');
CREATE SEQUENCE seq_joboriginalfilelink; INSERT INTO _lock_ids (name, id) SELECT 'seq_joboriginalfilelink', nextval('_lock_seq');
CREATE SEQUENCE seq_jobstatus; INSERT INTO _lock_ids (name, id) SELECT 'seq_jobstatus', nextval('_lock_seq');
CREATE SEQUENCE seq_lasermedium; INSERT INTO _lock_ids (name, id) SELECT 'seq_lasermedium', nextval('_lock_seq');
CREATE SEQUENCE seq_lasertype; INSERT INTO _lock_ids (name, id) SELECT 'seq_lasertype', nextval('_lock_seq');
CREATE SEQUENCE seq_lightpath; INSERT INTO _lock_ids (name, id) SELECT 'seq_lightpath', nextval('_lock_seq');
CREATE SEQUENCE seq_lightpathannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_lightpathannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_lightpathemissionfilterlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_lightpathemissionfilterlink', nextval('_lock_seq');
CREATE SEQUENCE seq_lightpathexcitationfilterlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_lightpathexcitationfilterlink', nextval('_lock_seq');
CREATE SEQUENCE seq_lightsettings; INSERT INTO _lock_ids (name, id) SELECT 'seq_lightsettings', nextval('_lock_seq');
CREATE SEQUENCE seq_lightsource; INSERT INTO _lock_ids (name, id) SELECT 'seq_lightsource', nextval('_lock_seq');
CREATE SEQUENCE seq_lightsourceannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_lightsourceannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_link; INSERT INTO _lock_ids (name, id) SELECT 'seq_link', nextval('_lock_seq');
CREATE SEQUENCE seq_logicalchannel; INSERT INTO _lock_ids (name, id) SELECT 'seq_logicalchannel', nextval('_lock_seq');
CREATE SEQUENCE seq_medium; INSERT INTO _lock_ids (name, id) SELECT 'seq_medium', nextval('_lock_seq');
CREATE SEQUENCE seq_microbeammanipulation; INSERT INTO _lock_ids (name, id) SELECT 'seq_microbeammanipulation', nextval('_lock_seq');
CREATE SEQUENCE seq_microbeammanipulationtype; INSERT INTO _lock_ids (name, id) SELECT 'seq_microbeammanipulationtype', nextval('_lock_seq');
CREATE SEQUENCE seq_microscope; INSERT INTO _lock_ids (name, id) SELECT 'seq_microscope', nextval('_lock_seq');
CREATE SEQUENCE seq_microscopetype; INSERT INTO _lock_ids (name, id) SELECT 'seq_microscopetype', nextval('_lock_seq');
CREATE SEQUENCE seq_namespace; INSERT INTO _lock_ids (name, id) SELECT 'seq_namespace', nextval('_lock_seq');
CREATE SEQUENCE seq_namespaceannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_namespaceannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_node; INSERT INTO _lock_ids (name, id) SELECT 'seq_node', nextval('_lock_seq');
CREATE SEQUENCE seq_nodeannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_nodeannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_otf; INSERT INTO _lock_ids (name, id) SELECT 'seq_otf', nextval('_lock_seq');
CREATE SEQUENCE seq_objective; INSERT INTO _lock_ids (name, id) SELECT 'seq_objective', nextval('_lock_seq');
CREATE SEQUENCE seq_objectiveannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_objectiveannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_objectivesettings; INSERT INTO _lock_ids (name, id) SELECT 'seq_objectivesettings', nextval('_lock_seq');
CREATE SEQUENCE seq_originalfile; INSERT INTO _lock_ids (name, id) SELECT 'seq_originalfile', nextval('_lock_seq');
CREATE SEQUENCE seq_originalfileannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_originalfileannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_photometricinterpretation; INSERT INTO _lock_ids (name, id) SELECT 'seq_photometricinterpretation', nextval('_lock_seq');
CREATE SEQUENCE seq_pixels; INSERT INTO _lock_ids (name, id) SELECT 'seq_pixels', nextval('_lock_seq');
CREATE SEQUENCE seq_pixelsoriginalfilemap; INSERT INTO _lock_ids (name, id) SELECT 'seq_pixelsoriginalfilemap', nextval('_lock_seq');
CREATE SEQUENCE seq_pixelstype; INSERT INTO _lock_ids (name, id) SELECT 'seq_pixelstype', nextval('_lock_seq');
CREATE SEQUENCE seq_planeinfo; INSERT INTO _lock_ids (name, id) SELECT 'seq_planeinfo', nextval('_lock_seq');
CREATE SEQUENCE seq_planeinfoannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_planeinfoannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_plate; INSERT INTO _lock_ids (name, id) SELECT 'seq_plate', nextval('_lock_seq');
CREATE SEQUENCE seq_plateacquisition; INSERT INTO _lock_ids (name, id) SELECT 'seq_plateacquisition', nextval('_lock_seq');
CREATE SEQUENCE seq_plateacquisitionannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_plateacquisitionannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_plateannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_plateannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_project; INSERT INTO _lock_ids (name, id) SELECT 'seq_project', nextval('_lock_seq');
CREATE SEQUENCE seq_projectannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_projectannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_projectdatasetlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_projectdatasetlink', nextval('_lock_seq');
CREATE SEQUENCE seq_projectionaxis; INSERT INTO _lock_ids (name, id) SELECT 'seq_projectionaxis', nextval('_lock_seq');
CREATE SEQUENCE seq_projectiondef; INSERT INTO _lock_ids (name, id) SELECT 'seq_projectiondef', nextval('_lock_seq');
CREATE SEQUENCE seq_projectiontype; INSERT INTO _lock_ids (name, id) SELECT 'seq_projectiontype', nextval('_lock_seq');
CREATE SEQUENCE seq_pulse; INSERT INTO _lock_ids (name, id) SELECT 'seq_pulse', nextval('_lock_seq');
CREATE SEQUENCE seq_quantumdef; INSERT INTO _lock_ids (name, id) SELECT 'seq_quantumdef', nextval('_lock_seq');
CREATE SEQUENCE seq_reagent; INSERT INTO _lock_ids (name, id) SELECT 'seq_reagent', nextval('_lock_seq');
CREATE SEQUENCE seq_reagentannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_reagentannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_renderingdef; INSERT INTO _lock_ids (name, id) SELECT 'seq_renderingdef', nextval('_lock_seq');
CREATE SEQUENCE seq_renderingmodel; INSERT INTO _lock_ids (name, id) SELECT 'seq_renderingmodel', nextval('_lock_seq');
CREATE SEQUENCE seq_roi; INSERT INTO _lock_ids (name, id) SELECT 'seq_roi', nextval('_lock_seq');
CREATE SEQUENCE seq_roiannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_roiannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_screen; INSERT INTO _lock_ids (name, id) SELECT 'seq_screen', nextval('_lock_seq');
CREATE SEQUENCE seq_screenannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_screenannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_screenplatelink; INSERT INTO _lock_ids (name, id) SELECT 'seq_screenplatelink', nextval('_lock_seq');
CREATE SEQUENCE seq_session; INSERT INTO _lock_ids (name, id) SELECT 'seq_session', nextval('_lock_seq');
CREATE SEQUENCE seq_sessionannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_sessionannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_shape; INSERT INTO _lock_ids (name, id) SELECT 'seq_shape', nextval('_lock_seq');
CREATE SEQUENCE seq_shapeannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_shapeannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_sharemember; INSERT INTO _lock_ids (name, id) SELECT 'seq_sharemember', nextval('_lock_seq');
CREATE SEQUENCE seq_stagelabel; INSERT INTO _lock_ids (name, id) SELECT 'seq_stagelabel', nextval('_lock_seq');
CREATE SEQUENCE seq_statsinfo; INSERT INTO _lock_ids (name, id) SELECT 'seq_statsinfo', nextval('_lock_seq');
CREATE SEQUENCE seq_thumbnail; INSERT INTO _lock_ids (name, id) SELECT 'seq_thumbnail', nextval('_lock_seq');
CREATE SEQUENCE seq_transmittancerange; INSERT INTO _lock_ids (name, id) SELECT 'seq_transmittancerange', nextval('_lock_seq');
CREATE SEQUENCE seq_well; INSERT INTO _lock_ids (name, id) SELECT 'seq_well', nextval('_lock_seq');
CREATE SEQUENCE seq_wellannotationlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_wellannotationlink', nextval('_lock_seq');
CREATE SEQUENCE seq_wellreagentlink; INSERT INTO _lock_ids (name, id) SELECT 'seq_wellreagentlink', nextval('_lock_seq');
CREATE SEQUENCE seq_wellsample; INSERT INTO _lock_ids (name, id) SELECT 'seq_wellsample', nextval('_lock_seq');


--
-- END #1176/#2508
--


--
-- #1390 : Triggering the addition of an "REINDEX" event on annotation and other events.
--

CREATE OR REPLACE FUNCTION _prepare_session(_event_id int8, _user_id int8, _group_id int8) RETURNS void
    AS '
    BEGIN

        IF NOT EXISTS(SELECT table_name FROM information_schema.tables where table_name = ''_current_session'') THEN
            CREATE TEMP TABLE _current_session ("event_id" int8, "user_id" int8, "group_id" int8) ON COMMIT DELETE ROWS;
            INSERT INTO _current_session VALUES (_event_id, _user_id, _group_id);
        ELSE
            DELETE FROM _current_session;
            INSERT INTO _current_session VALUES (_event_id, _user_id, _group_id);
        END IF;
    END;'
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION _current_event() RETURNS int8
    AS '
    DECLARE
        eid int8;
    BEGIN
        IF NOT EXISTS(SELECT table_name FROM information_schema.tables where table_name = ''_current_session'') THEN
            RETURN 0;
        END IF;
        SELECT INTO eid event_id FROM _current_session;
        RETURN eid;

    END;'
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION _current_or_new_event() RETURNS int8
    AS '
    DECLARE
        eid int8;
    BEGIN
        SELECT INTO eid _current_event();
        IF eid = 0 OR eid IS NULL THEN
            SELECT INTO eid ome_nextval(''seq_event'');
            INSERT INTO event (id, permissions, status, time, experimenter, experimentergroup, session, type)
                SELECT eid, -52, ''TRIGGERED'', clock_timestamp(), 0, 0, 0, 0;
        END IF;
        RETURN eid;
    END;'
LANGUAGE plpgsql;


CREATE TABLE _reindexing_required (
    event_id BIGINT NOT NULL,
    entity_type TEXT NOT NULL,
    entity_id BIGINT NOT NULL,
    CONSTRAINT FK_reindexing_required_event_id
        FOREIGN KEY (event_id)
        REFERENCES event);

CREATE INDEX _reindexing_required_event_index
    ON _reindexing_required (event_id);

CREATE INDEX _reindexing_required_row_index
    ON _reindexing_required (event_id, entity_type, entity_id);

CREATE FUNCTION annotation_update_event_trigger() RETURNS TRIGGER AS $$

    DECLARE
        pid BIGINT;
        eid BIGINT;

    BEGIN
        SELECT INTO eid _current_or_new_event();
 
        FOR pid IN SELECT DISTINCT parent FROM annotationannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.annotations.Annotation', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.annotations.Annotation' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM channelannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.core.Channel', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.core.Channel' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM datasetannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.containers.Dataset', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.containers.Dataset' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM detectorannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.acquisition.Detector', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.acquisition.Detector' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM dichroicannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.acquisition.Dichroic', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.acquisition.Dichroic' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM experimenterannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.meta.Experimenter', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.meta.Experimenter' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM experimentergroupannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.meta.ExperimenterGroup', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.meta.ExperimenterGroup' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM filesetannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.fs.Fileset', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.fs.Fileset' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM filterannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.acquisition.Filter', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.acquisition.Filter' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM folderannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.containers.Folder', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.containers.Folder' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM imageannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.core.Image', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.core.Image' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM instrumentannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.acquisition.Instrument', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.acquisition.Instrument' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM lightpathannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.acquisition.LightPath', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.acquisition.LightPath' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM lightsourceannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.acquisition.LightSource', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.acquisition.LightSource' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM namespaceannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.meta.Namespace', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.meta.Namespace' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM nodeannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.meta.Node', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.meta.Node' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM objectiveannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.acquisition.Objective', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.acquisition.Objective' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM originalfileannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.core.OriginalFile', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.core.OriginalFile' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM planeinfoannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.core.PlaneInfo', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.core.PlaneInfo' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM plateannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.screen.Plate', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.screen.Plate' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM plateacquisitionannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.screen.PlateAcquisition', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.screen.PlateAcquisition' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM projectannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.containers.Project', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.containers.Project' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM reagentannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.screen.Reagent', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.screen.Reagent' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM roiannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.roi.Roi', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.roi.Roi' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM screenannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.screen.Screen', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.screen.Screen' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM sessionannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.meta.Session', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.meta.Session' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM shapeannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.roi.Shape', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.roi.Shape' AND ua.entity_id = pid);
        END LOOP;

        FOR pid IN SELECT DISTINCT parent FROM wellannotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.screen.Well', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = 'ome.model.screen.Well' AND ua.entity_id = pid);
        END LOOP;

        RETURN new;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER annotation_trigger
        AFTER UPDATE ON annotation
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_update_event_trigger();

-- Note: not adding an annotation insert trigger since that would be handled by any links on insert

CREATE OR REPLACE FUNCTION annotation_link_update_trigger() RETURNS "trigger"
    AS '
    DECLARE
        eid int8;

    BEGIN
        SELECT INTO eid _current_or_new_event();

        INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
            SELECT eid, TG_ARGV[0], old.parent
            WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                WHERE ua.event_id = eid AND ua.entity_type = TG_ARGV[0] AND ua.entity_id = old.parent);

        INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
            SELECT eid, TG_ARGV[0], new.parent
            WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                WHERE ua.event_id = eid AND ua.entity_type = TG_ARGV[0] AND ua.entity_id = new.parent);

        RETURN new;

    END;'
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION annotation_link_insert_trigger() RETURNS "trigger"
    AS '
    DECLARE
        eid int8;

    BEGIN
        SELECT INTO eid _current_or_new_event();

        INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
            SELECT eid, TG_ARGV[0], new.parent
            WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                WHERE ua.event_id = eid AND ua.entity_type = TG_ARGV[0] AND ua.entity_id = new.parent);

        RETURN new;

    END;'
LANGUAGE plpgsql;

CREATE TRIGGER annotation_annotation_link_update_trigger
        AFTER UPDATE ON annotationannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.annotations.Annotation');
CREATE TRIGGER annotation_annotation_link_insert_trigger
        AFTER INSERT ON annotationannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.annotations.Annotation');
CREATE TRIGGER channel_annotation_link_update_trigger
        AFTER UPDATE ON channelannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.core.Channel');
CREATE TRIGGER channel_annotation_link_insert_trigger
        AFTER INSERT ON channelannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.core.Channel');
CREATE TRIGGER dataset_annotation_link_update_trigger
        AFTER UPDATE ON datasetannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.containers.Dataset');
CREATE TRIGGER dataset_annotation_link_insert_trigger
        AFTER INSERT ON datasetannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.containers.Dataset');
CREATE TRIGGER detector_annotation_link_update_trigger
        AFTER UPDATE ON detectorannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.acquisition.Detector');
CREATE TRIGGER detector_annotation_link_insert_trigger
        AFTER INSERT ON detectorannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.acquisition.Detector');
CREATE TRIGGER dichroic_annotation_link_update_trigger
        AFTER UPDATE ON dichroicannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.acquisition.Dichroic');
CREATE TRIGGER dichroic_annotation_link_insert_trigger
        AFTER INSERT ON dichroicannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.acquisition.Dichroic');
CREATE TRIGGER experimenter_annotation_link_update_trigger
        AFTER UPDATE ON experimenterannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.meta.Experimenter');
CREATE TRIGGER experimenter_annotation_link_insert_trigger
        AFTER INSERT ON experimenterannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.meta.Experimenter');
CREATE TRIGGER experimentergroup_annotation_link_update_trigger
        AFTER UPDATE ON experimentergroupannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.meta.ExperimenterGroup');
CREATE TRIGGER experimentergroup_annotation_link_insert_trigger
        AFTER INSERT ON experimentergroupannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.meta.ExperimenterGroup');
CREATE TRIGGER fileset_annotation_link_update_trigger
        AFTER UPDATE ON filesetannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.fs.Fileset');
CREATE TRIGGER fileset_annotation_link_insert_trigger
        AFTER INSERT ON filesetannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.fs.Fileset');
CREATE TRIGGER filter_annotation_link_update_trigger
        AFTER UPDATE ON filterannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.acquisition.Filter');
CREATE TRIGGER filter_annotation_link_insert_trigger
        AFTER INSERT ON filterannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.acquisition.Filter');
CREATE TRIGGER folder_annotation_link_update_trigger
        AFTER UPDATE ON folderannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.containers.Folder');
CREATE TRIGGER folder_annotation_link_insert_trigger
        AFTER INSERT ON folderannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.containers.Folder');
CREATE TRIGGER image_annotation_link_update_trigger
        AFTER UPDATE ON imageannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.core.Image');
CREATE TRIGGER image_annotation_link_insert_trigger
        AFTER INSERT ON imageannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.core.Image');
CREATE TRIGGER instrument_annotation_link_update_trigger
        AFTER UPDATE ON instrumentannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.acquisition.Instrument');
CREATE TRIGGER instrument_annotation_link_insert_trigger
        AFTER INSERT ON instrumentannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.acquisition.Instrument');
CREATE TRIGGER lightpath_annotation_link_update_trigger
        AFTER UPDATE ON lightpathannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.acquisition.LightPath');
CREATE TRIGGER lightpath_annotation_link_insert_trigger
        AFTER INSERT ON lightpathannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.acquisition.LightPath');
CREATE TRIGGER lightsource_annotation_link_update_trigger
        AFTER UPDATE ON lightsourceannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.acquisition.LightSource');
CREATE TRIGGER lightsource_annotation_link_insert_trigger
        AFTER INSERT ON lightsourceannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.acquisition.LightSource');
CREATE TRIGGER namespace_annotation_link_update_trigger
        AFTER UPDATE ON namespaceannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.meta.Namespace');
CREATE TRIGGER namespace_annotation_link_insert_trigger
        AFTER INSERT ON namespaceannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.meta.Namespace');
CREATE TRIGGER node_annotation_link_update_trigger
        AFTER UPDATE ON nodeannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.meta.Node');
CREATE TRIGGER node_annotation_link_insert_trigger
        AFTER INSERT ON nodeannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.meta.Node');
CREATE TRIGGER objective_annotation_link_update_trigger
        AFTER UPDATE ON objectiveannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.acquisition.Objective');
CREATE TRIGGER objective_annotation_link_insert_trigger
        AFTER INSERT ON objectiveannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.acquisition.Objective');
CREATE TRIGGER originalfile_annotation_link_update_trigger
        AFTER UPDATE ON originalfileannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.core.OriginalFile');
CREATE TRIGGER originalfile_annotation_link_insert_trigger
        AFTER INSERT ON originalfileannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.core.OriginalFile');
CREATE TRIGGER planeinfo_annotation_link_update_trigger
        AFTER UPDATE ON planeinfoannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.core.PlaneInfo');
CREATE TRIGGER planeinfo_annotation_link_insert_trigger
        AFTER INSERT ON planeinfoannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.core.PlaneInfo');
CREATE TRIGGER plate_annotation_link_update_trigger
        AFTER UPDATE ON plateannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.screen.Plate');
CREATE TRIGGER plate_annotation_link_insert_trigger
        AFTER INSERT ON plateannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.screen.Plate');
CREATE TRIGGER plateacquisition_annotation_link_update_trigger
        AFTER UPDATE ON plateacquisitionannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.screen.PlateAcquisition');
CREATE TRIGGER plateacquisition_annotation_link_insert_trigger
        AFTER INSERT ON plateacquisitionannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.screen.PlateAcquisition');
CREATE TRIGGER project_annotation_link_update_trigger
        AFTER UPDATE ON projectannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.containers.Project');
CREATE TRIGGER project_annotation_link_insert_trigger
        AFTER INSERT ON projectannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.containers.Project');
CREATE TRIGGER reagent_annotation_link_update_trigger
        AFTER UPDATE ON reagentannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.screen.Reagent');
CREATE TRIGGER reagent_annotation_link_insert_trigger
        AFTER INSERT ON reagentannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.screen.Reagent');
CREATE TRIGGER roi_annotation_link_update_trigger
        AFTER UPDATE ON roiannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.roi.Roi');
CREATE TRIGGER roi_annotation_link_insert_trigger
        AFTER INSERT ON roiannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.roi.Roi');
CREATE TRIGGER screen_annotation_link_update_trigger
        AFTER UPDATE ON screenannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.screen.Screen');
CREATE TRIGGER screen_annotation_link_insert_trigger
        AFTER INSERT ON screenannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.screen.Screen');
CREATE TRIGGER session_annotation_link_update_trigger
        AFTER UPDATE ON sessionannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.meta.Session');
CREATE TRIGGER session_annotation_link_insert_trigger
        AFTER INSERT ON sessionannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.meta.Session');
CREATE TRIGGER shape_annotation_link_update_trigger
        AFTER UPDATE ON shapeannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.roi.Shape');
CREATE TRIGGER shape_annotation_link_insert_trigger
        AFTER INSERT ON shapeannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.roi.Shape');
CREATE TRIGGER well_annotation_link_update_trigger
        AFTER UPDATE ON wellannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('ome.model.screen.Well');
CREATE TRIGGER well_annotation_link_insert_trigger
        AFTER INSERT ON wellannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('ome.model.screen.Well');

-- Delete triggers to go with update triggers (See #9337)
CREATE OR REPLACE FUNCTION annotation_link_delete_trigger() RETURNS "trigger"
    AS '
    DECLARE
        eid int8;

    BEGIN
        SELECT INTO eid _current_or_new_event();

        INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
            SELECT eid, TG_ARGV[0], old.parent
            WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                WHERE ua.event_id = eid AND ua.entity_type = TG_ARGV[0] AND ua.entity_id = old.parent);

        RETURN old;

    END;'
LANGUAGE plpgsql;

CREATE TRIGGER annotation_annotation_link_delete_trigger
        BEFORE DELETE ON annotationannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.annotations.Annotation');
CREATE TRIGGER channel_annotation_link_delete_trigger
        BEFORE DELETE ON channelannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.core.Channel');
CREATE TRIGGER dataset_annotation_link_delete_trigger
        BEFORE DELETE ON datasetannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.containers.Dataset');
CREATE TRIGGER detector_annotation_link_delete_trigger
        BEFORE DELETE ON detectorannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.acquisition.Detector');
CREATE TRIGGER dichroic_annotation_link_delete_trigger
        BEFORE DELETE ON dichroicannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.acquisition.Dichroic');
CREATE TRIGGER experimenter_annotation_link_delete_trigger
        BEFORE DELETE ON experimenterannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.meta.Experimenter');
CREATE TRIGGER experimentergroup_annotation_link_delete_trigger
        BEFORE DELETE ON experimentergroupannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.meta.ExperimenterGroup');
CREATE TRIGGER fileset_annotation_link_delete_trigger
        BEFORE DELETE ON filesetannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.fs.Fileset');
CREATE TRIGGER filter_annotation_link_delete_trigger
        BEFORE DELETE ON filterannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.acquisition.Filter');
CREATE TRIGGER folder_annotation_link_delete_trigger
        BEFORE DELETE ON folderannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.containers.Folder');
CREATE TRIGGER image_annotation_link_delete_trigger
        BEFORE DELETE ON imageannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.core.Image');
CREATE TRIGGER instrument_annotation_link_delete_trigger
        BEFORE DELETE ON instrumentannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.acquisition.Instrument');
CREATE TRIGGER lightpath_annotation_link_delete_trigger
        BEFORE DELETE ON lightpathannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.acquisition.LightPath');
CREATE TRIGGER lightsource_annotation_link_delete_trigger
        BEFORE DELETE ON lightsourceannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.acquisition.LightSource');
CREATE TRIGGER namespace_annotation_link_delete_trigger
        BEFORE DELETE ON namespaceannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.meta.Namespace');
CREATE TRIGGER node_annotation_link_delete_trigger
        BEFORE DELETE ON nodeannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.meta.Node');
CREATE TRIGGER objective_annotation_link_delete_trigger
        BEFORE DELETE ON objectiveannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.acquisition.Objective');
CREATE TRIGGER originalfile_annotation_link_delete_trigger
        BEFORE DELETE ON originalfileannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.core.OriginalFile');
CREATE TRIGGER planeinfo_annotation_link_delete_trigger
        BEFORE DELETE ON planeinfoannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.core.PlaneInfo');
CREATE TRIGGER plate_annotation_link_delete_trigger
        BEFORE DELETE ON plateannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.screen.Plate');
CREATE TRIGGER plateacquisition_annotation_link_delete_trigger
        BEFORE DELETE ON plateacquisitionannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.screen.PlateAcquisition');
CREATE TRIGGER project_annotation_link_delete_trigger
        BEFORE DELETE ON projectannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.containers.Project');
CREATE TRIGGER reagent_annotation_link_delete_trigger
        BEFORE DELETE ON reagentannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.screen.Reagent');
CREATE TRIGGER roi_annotation_link_delete_trigger
        BEFORE DELETE ON roiannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.roi.Roi');
CREATE TRIGGER screen_annotation_link_delete_trigger
        BEFORE DELETE ON screenannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.screen.Screen');
CREATE TRIGGER session_annotation_link_delete_trigger
        BEFORE DELETE ON sessionannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.meta.Session');
CREATE TRIGGER shape_annotation_link_delete_trigger
        BEFORE DELETE ON shapeannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.roi.Shape');
CREATE TRIGGER well_annotation_link_delete_trigger
        BEFORE DELETE ON wellannotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('ome.model.screen.Well');


-- move update notes from updated_annotations to eventlog REINDEX entries

CREATE OR REPLACE FUNCTION updated_entities_note_reindex() RETURNS void AS $$

    DECLARE
        curs CURSOR FOR SELECT * FROM _reindexing_required ORDER BY event_id LIMIT 100000 FOR UPDATE;
        row _reindexing_required%%rowtype;

    BEGIN
        FOR row IN curs
        LOOP
            DELETE FROM _reindexing_required WHERE CURRENT OF curs;

            INSERT INTO eventlog (id, action, permissions, entityid, entitytype, event)
                SELECT ome_nextval('seq_eventlog'), 'REINDEX', -52, row.entity_id, row.entity_type, row.event_id
                WHERE NOT EXISTS (SELECT 1 FROM eventlog AS el
                    WHERE el.entityid = row.entity_id AND el.entitytype = row.entity_type AND el.event = row.event_id);

        END LOOP;
    END;
$$ LANGUAGE plpgsql;

--
-- END #1390
--

--
-- add reindexing triggers for ROI folders; see FullTextBridge.set_folders
--

CREATE FUNCTION folder_update_trigger() RETURNS TRIGGER AS $$

    DECLARE
        eid BIGINT;
        iid BIGINT;

    BEGIN
        FOR iid IN SELECT DISTINCT r.image FROM roi AS r, folderroilink AS l
            WHERE l.parent = NEW.id AND l.child = r.id AND r.image IS NOT NULL LOOP

            IF eid IS NULL THEN
                eid := _current_or_new_event();
            END IF;

            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.core.Image', iid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS rr
                    WHERE rr.event_id = eid AND rr.entity_type = 'ome.model.core.Image' AND rr.entity_id = iid);
        END LOOP;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION folder_roi_link_insert_trigger() RETURNS TRIGGER AS $$

    DECLARE
        eid BIGINT;
        iid BIGINT;

    BEGIN
        FOR iid IN SELECT DISTINCT r.image FROM roi AS r
            WHERE r.id = NEW.child AND r.image IS NOT NULL LOOP

            IF eid IS NULL THEN
                eid := _current_or_new_event();
            END IF;

            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.core.Image', iid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS rr
                    WHERE rr.event_id = eid AND rr.entity_type = 'ome.model.core.Image' AND rr.entity_id = iid);
        END LOOP;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION folder_roi_link_update_trigger() RETURNS TRIGGER AS $$

    DECLARE
        eid BIGINT;
        iid BIGINT;

    BEGIN
        FOR iid IN SELECT DISTINCT r.image FROM roi AS r
            WHERE r.id IN (OLD.child, NEW.child) AND r.image IS NOT NULL LOOP

            IF eid IS NULL THEN
                eid := _current_or_new_event();
            END IF;

            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.core.Image', iid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS rr
                    WHERE rr.event_id = eid AND rr.entity_type = 'ome.model.core.Image' AND rr.entity_id = iid);
        END LOOP;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION folder_roi_link_delete_trigger() RETURNS TRIGGER AS $$

    DECLARE
        eid BIGINT;
        iid BIGINT;

    BEGIN
        FOR iid IN SELECT DISTINCT r.image FROM roi AS r
            WHERE r.id = OLD.child AND r.image IS NOT NULL LOOP

            IF eid IS NULL THEN
                eid := _current_or_new_event();
            END IF;

            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.core.Image', iid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS rr
                    WHERE rr.event_id = eid AND rr.entity_type = 'ome.model.core.Image' AND rr.entity_id = iid);
        END LOOP;

        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION roi_insert_trigger() RETURNS TRIGGER AS $$

    DECLARE
        eid BIGINT;
        iid BIGINT;

    BEGIN
        iid := NEW.image;

        IF iid IS NOT NULL THEN
            eid := _current_or_new_event();

            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.core.Image', iid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS rr
                    WHERE rr.event_id = eid AND rr.entity_type = 'ome.model.core.Image' AND rr.entity_id = iid);
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION roi_update_trigger() RETURNS TRIGGER AS $$

    DECLARE
        eid BIGINT;
        iid BIGINT;

    BEGIN
        IF OLD.image = NEW.image THEN
            RETURN NEW;
        END IF;

        iid := OLD.image;

        IF iid IS NOT NULL THEN
            eid := _current_or_new_event();

            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.core.Image', iid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS rr
                    WHERE rr.event_id = eid AND rr.entity_type = 'ome.model.core.Image' AND rr.entity_id = iid);
        END IF;

        iid := NEW.image;

        IF iid IS NOT NULL THEN
            IF eid IS NULL THEN
                eid := _current_or_new_event();
            END IF;

            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.core.Image', iid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS rr
                    WHERE rr.event_id = eid AND rr.entity_type = 'ome.model.core.Image' AND rr.entity_id = iid);
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION roi_delete_trigger() RETURNS TRIGGER AS $$

    DECLARE
        eid BIGINT;
        iid BIGINT;

    BEGIN
        iid := OLD.image;

        IF iid IS NOT NULL THEN
            eid := _current_or_new_event();

            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, 'ome.model.core.Image', iid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS rr
                    WHERE rr.event_id = eid AND rr.entity_type = 'ome.model.core.Image' AND rr.entity_id = iid);
        END IF;

        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER folder_update_trigger
    AFTER UPDATE ON folder
    FOR EACH ROW
    EXECUTE PROCEDURE folder_update_trigger();

CREATE TRIGGER folder_roi_link_insert_trigger
    AFTER INSERT ON folderroilink
    FOR EACH ROW
    EXECUTE PROCEDURE folder_roi_link_insert_trigger();

CREATE TRIGGER folder_roi_link_update_trigger
    AFTER UPDATE ON folderroilink
    FOR EACH ROW
    EXECUTE PROCEDURE folder_roi_link_update_trigger();

CREATE TRIGGER folder_roi_link_delete_trigger
    AFTER DELETE ON folderroilink
    FOR EACH ROW
    EXECUTE PROCEDURE folder_roi_link_delete_trigger();

CREATE TRIGGER roi_insert_trigger
    AFTER INSERT ON roi
    FOR EACH ROW
    EXECUTE PROCEDURE roi_insert_trigger();

CREATE TRIGGER roi_update_trigger
    AFTER UPDATE ON roi
    FOR EACH ROW
    EXECUTE PROCEDURE roi_update_trigger();

CREATE TRIGGER roi_delete_trigger
    AFTER DELETE ON roi
    FOR EACH ROW
    EXECUTE PROCEDURE roi_delete_trigger();

--
-- #12317 -- delete map property values along with their holders
--

CREATE FUNCTION experimenter_config_map_entry_delete_trigger_function() RETURNS "trigger" AS '
BEGIN
    DELETE FROM experimenter_config
        WHERE experimenter_id = OLD.id;
    RETURN OLD;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER experimenter_config_map_entry_delete_trigger
    BEFORE DELETE ON experimenter
    FOR EACH ROW
    EXECUTE PROCEDURE experimenter_config_map_entry_delete_trigger_function();

CREATE FUNCTION experimentergroup_config_map_entry_delete_trigger_function() RETURNS "trigger" AS '
BEGIN
    DELETE FROM experimentergroup_config
        WHERE experimentergroup_id = OLD.id;
    RETURN OLD;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER experimentergroup_config_map_entry_delete_trigger
    BEFORE DELETE ON experimentergroup
    FOR EACH ROW
    EXECUTE PROCEDURE experimentergroup_config_map_entry_delete_trigger_function();

CREATE FUNCTION genericexcitationsource_map_map_entry_delete_trigger_function() RETURNS "trigger" AS '
BEGIN
    DELETE FROM genericexcitationsource_map
        WHERE genericexcitationsource_id = OLD.lightsource_id;
    RETURN OLD;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER genericexcitationsource_map_map_entry_delete_trigger
    BEFORE DELETE ON genericexcitationsource
    FOR EACH ROW
    EXECUTE PROCEDURE genericexcitationsource_map_map_entry_delete_trigger_function();

CREATE FUNCTION imagingenvironment_map_map_entry_delete_trigger_function() RETURNS "trigger" AS '
BEGIN
    DELETE FROM imagingenvironment_map
        WHERE imagingenvironment_id = OLD.id;
    RETURN OLD;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER imagingenvironment_map_map_entry_delete_trigger
    BEFORE DELETE ON imagingenvironment
    FOR EACH ROW
    EXECUTE PROCEDURE imagingenvironment_map_map_entry_delete_trigger_function();

CREATE FUNCTION annotation_mapValue_map_entry_delete_trigger_function() RETURNS "trigger" AS '
BEGIN
    DELETE FROM annotation_mapValue
        WHERE annotation_id = OLD.id;
    RETURN OLD;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER annotation_mapValue_map_entry_delete_trigger
    BEFORE DELETE ON annotation
    FOR EACH ROW
    EXECUTE PROCEDURE annotation_mapValue_map_entry_delete_trigger_function();

CREATE FUNCTION metadataimportjob_versionInfo_map_entry_delete_trigger_function() RETURNS "trigger" AS '
BEGIN
    DELETE FROM metadataimportjob_versionInfo
        WHERE metadataimportjob_id = OLD.job_id;
    RETURN OLD;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER metadataimportjob_versionInfo_map_entry_delete_trigger
    BEFORE DELETE ON metadataimportjob
    FOR EACH ROW
    EXECUTE PROCEDURE metadataimportjob_versionInfo_map_entry_delete_trigger_function();

CREATE FUNCTION uploadjob_versionInfo_map_entry_delete_trigger_function() RETURNS "trigger" AS '
BEGIN
    DELETE FROM uploadjob_versionInfo
        WHERE uploadjob_id = OLD.job_id;
    RETURN OLD;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER uploadjob_versionInfo_map_entry_delete_trigger
    BEFORE DELETE ON uploadjob
    FOR EACH ROW
    EXECUTE PROCEDURE uploadjob_versionInfo_map_entry_delete_trigger_function();

--
-- done #12317
--

-- First, we install a trigger to ensure users may go
-- from versionA/patchA to versionB/patchB once only.
--
CREATE FUNCTION dbpatch_versions_trigger_function() RETURNS TRIGGER AS $$
BEGIN
    IF (NEW.currentversion <> NEW.previousversion OR NEW.currentpatch <> NEW.previouspatch) AND
       (SELECT COUNT(*) FROM dbpatch WHERE id <> NEW.id AND
        (currentversion <> previousversion OR currentpatch <> previouspatch) AND
        ((currentversion = NEW.currentversion AND currentpatch = NEW.currentpatch) OR
         (previousversion = NEW.previousversion AND previouspatch = NEW.previouspatch))) > 0 THEN
        RAISE 'upgrades cannot be repeated';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER dbpatch_versions_trigger
    BEFORE INSERT OR UPDATE ON dbpatch
    FOR EACH ROW
    EXECUTE PROCEDURE dbpatch_versions_trigger_function();


--
-- Since this is a table that we will be using in DB-specific ways, we're also going
-- to make working with it a bit simpler.
--
alter table dbpatch alter id set default ome_nextval('seq_dbpatch');
alter table dbpatch alter permissions set default -52;
alter table dbpatch alter message set default 'Updating';

--
-- Then, we insert into the patch table the patch (initialization) which we are currently
-- running so that if anything goes wrong, we'll have some record.
--
insert into dbpatch (currentVersion, currentPatch, previousVersion, previousPatch, message)
             values ('OMERO5.4',  0,    'OMERO5.4',   0,             'Initializing');

--
-- Temporarily make event columns nullable; restored below.
--
alter table event alter column "type" drop not null;
alter table event alter column experimentergroup drop not null;

--
-- Here we will create the root account and the necessary groups
--
alter table experimenter alter ldap set default false;
alter table experimentergroup alter ldap set default false;
insert into experimenter (id,permissions,version,omename,firstname,lastname)
        values (0,0,0,'root','root','root');
insert into experimenter (id,permissions,version,omename,firstname,lastname)
        values (ome_nextval('seq_experimenter'),0,0,'guest','Guest','Account');
insert into node
        (id,permissions,uuid,conn,up,down)
        select 0,-52,'000000000000000000000000000000000000','unkclock_timestampn',clock_timestamp(),clock_timestamp();
insert into session
        (id,permissions,timetoidle,timetolive,started,closed,defaulteventtype,uuid,owner,node)
        select 0,-52,0,0,clock_timestamp(),clock_timestamp(),'BOOTSTRAP',0000, 0,0;
insert into session
        (id,permissions,timetoidle,timetolive,started,closed,defaulteventtype,uuid,owner,node)
        select ome_nextval('seq_session'),-52, 0,0,clock_timestamp(),clock_timestamp(),'PREVIOUSITEMS','1111',0,0;
insert into event (id,permissions,time,status,experimenter,session) values (0,0,clock_timestamp(),'BOOTSTRAP',0,0);

--
-- Default group permissions (ticket:1783)
-- * "system" is private, so that administrators do not share by accident
-- * "user" is public by default since its purpose is to share objects (ticket:1794)
-- * "guest" is private by default so that it doesn't show up on any lists,
--    though nothing should be created there.
--
insert into experimentergroup (id,permissions,version,name)
        values (0,-120,0,'system');
insert into experimentergroup (id,permissions,version,name)
        values (ome_nextval('seq_experimentergroup'),-52,0,'user');
insert into experimentergroup (id,permissions,version,name)
        values (ome_nextval('seq_experimentergroup'),-120,0,'guest');

insert into eventtype (id,permissions,value) values
        (0,-52,'Bootstrap');
insert into groupexperimentermap
        (id,permissions,version, parent, child, child_index,owner)
        values
        (0,-52,0,0,0,0,true);
insert into groupexperimentermap
        (id,permissions,version, parent, child, child_index,owner)
        select ome_nextval('seq_groupexperimentermap'),-52,0,1,0,1,false;
insert into groupexperimentermap
        (id,permissions,version, parent, child, child_index,owner)
        select ome_nextval('seq_groupexperimentermap'),-52,0,2,1,0,false;

update event set type = 0;
update event set experimentergroup = 0;

alter table event alter column type set not null;
alter table event alter column experimentergroup set not null;


-- temporarily disable the not null constraints
alter table pixelstype alter column bitsize drop not null;

insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'WideField';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'LaserScanningConfocalMicroscopy';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'SpinningDiskConfocal';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'SlitScanConfocal';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'MultiPhotonMicroscopy';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'StructuredIllumination';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'SingleMoleculeImaging';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'TotalInternalReflection';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'FluorescenceLifetime';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'SpectralImaging';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'FluorescenceCorrelationSpectroscopy';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'NearFieldScanningOpticalMicroscopy';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'SecondHarmonicGenerationImaging';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'PALM';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'STORM';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'STED';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'TIRF';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'FSM';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'LCM';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'Other';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'Unknown';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'BrightField';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'SweptFieldConfocal';
insert into acquisitionmode (id,permissions,value)
    select ome_nextval('seq_acquisitionmode'),-52,'SPIM';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'Chgrp';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'Chown';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'DeleteFile';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'DeleteManagedRepo';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'DeleteOwned';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'DeleteScriptRepo';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'ModifyGroup';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'ModifyGroupMembership';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'ModifyUser';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'ReadSession';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'Sudo';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'WriteFile';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'WriteManagedRepo';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'WriteOwned';
insert into adminprivilege (id,permissions,value)
    select ome_nextval('seq_adminprivilege'),-52,'WriteScriptRepo';
insert into arctype (id,permissions,value)
    select ome_nextval('seq_arctype'),-52,'Hg';
insert into arctype (id,permissions,value)
    select ome_nextval('seq_arctype'),-52,'Xe';
insert into arctype (id,permissions,value)
    select ome_nextval('seq_arctype'),-52,'HgXe';
insert into arctype (id,permissions,value)
    select ome_nextval('seq_arctype'),-52,'Other';
insert into arctype (id,permissions,value)
    select ome_nextval('seq_arctype'),-52,'Unknown';
insert into binning (id,permissions,value)
    select ome_nextval('seq_binning'),-52,'1x1';
insert into binning (id,permissions,value)
    select ome_nextval('seq_binning'),-52,'2x2';
insert into binning (id,permissions,value)
    select ome_nextval('seq_binning'),-52,'4x4';
insert into binning (id,permissions,value)
    select ome_nextval('seq_binning'),-52,'8x8';
insert into checksumalgorithm (id,permissions,value)
    select ome_nextval('seq_checksumalgorithm'),-52,'Adler-32';
insert into checksumalgorithm (id,permissions,value)
    select ome_nextval('seq_checksumalgorithm'),-52,'CRC-32';
insert into checksumalgorithm (id,permissions,value)
    select ome_nextval('seq_checksumalgorithm'),-52,'MD5-128';
insert into checksumalgorithm (id,permissions,value)
    select ome_nextval('seq_checksumalgorithm'),-52,'Murmur3-32';
insert into checksumalgorithm (id,permissions,value)
    select ome_nextval('seq_checksumalgorithm'),-52,'Murmur3-128';
insert into checksumalgorithm (id,permissions,value)
    select ome_nextval('seq_checksumalgorithm'),-52,'SHA1-160';
insert into checksumalgorithm (id,permissions,value)
    select ome_nextval('seq_checksumalgorithm'),-52,'File-Size-64';
insert into contrastmethod (id,permissions,value)
    select ome_nextval('seq_contrastmethod'),-52,'Brightfield';
insert into contrastmethod (id,permissions,value)
    select ome_nextval('seq_contrastmethod'),-52,'Phase';
insert into contrastmethod (id,permissions,value)
    select ome_nextval('seq_contrastmethod'),-52,'DIC';
insert into contrastmethod (id,permissions,value)
    select ome_nextval('seq_contrastmethod'),-52,'HoffmanModulation';
insert into contrastmethod (id,permissions,value)
    select ome_nextval('seq_contrastmethod'),-52,'ObliqueIllumination';
insert into contrastmethod (id,permissions,value)
    select ome_nextval('seq_contrastmethod'),-52,'PolarizedLight';
insert into contrastmethod (id,permissions,value)
    select ome_nextval('seq_contrastmethod'),-52,'Darkfield';
insert into contrastmethod (id,permissions,value)
    select ome_nextval('seq_contrastmethod'),-52,'Fluorescence';
insert into contrastmethod (id,permissions,value)
    select ome_nextval('seq_contrastmethod'),-52,'Other';
insert into contrastmethod (id,permissions,value)
    select ome_nextval('seq_contrastmethod'),-52,'Unknown';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'UV';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'PlanApo';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'PlanFluor';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'SuperFluor';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'VioletCorrected';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'Achro';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'Achromat';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'Fluor';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'Fl';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'Fluar';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'Neofluar';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'Fluotar';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'Apo';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'Other';
insert into correction (id,permissions,value)
    select ome_nextval('seq_correction'),-52,'Unknown';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'CCD';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'IntensifiedCCD';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'AnalogVideo';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'PMT';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'Photodiode';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'Spectroscopy';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'LifetimeImaging';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'CorrelationSpectroscopy';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'FTIR';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'EM-CCD';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'APD';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'CMOS';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'EBCCD';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'Other';
insert into detectortype (id,permissions,value)
    select ome_nextval('seq_detectortype'),-52,'Unknown';
insert into dimensionorder (id,permissions,value)
    select ome_nextval('seq_dimensionorder'),-52,'XYZCT';
insert into dimensionorder (id,permissions,value)
    select ome_nextval('seq_dimensionorder'),-52,'XYZTC';
insert into dimensionorder (id,permissions,value)
    select ome_nextval('seq_dimensionorder'),-52,'XYCTZ';
insert into dimensionorder (id,permissions,value)
    select ome_nextval('seq_dimensionorder'),-52,'XYCZT';
insert into dimensionorder (id,permissions,value)
    select ome_nextval('seq_dimensionorder'),-52,'XYTCZ';
insert into dimensionorder (id,permissions,value)
    select ome_nextval('seq_dimensionorder'),-52,'XYTZC';
insert into eventtype (id,permissions,value)
    select ome_nextval('seq_eventtype'),-52,'Import';
insert into eventtype (id,permissions,value)
    select ome_nextval('seq_eventtype'),-52,'Internal';
insert into eventtype (id,permissions,value)
    select ome_nextval('seq_eventtype'),-52,'Shoola';
insert into eventtype (id,permissions,value)
    select ome_nextval('seq_eventtype'),-52,'User';
insert into eventtype (id,permissions,value)
    select ome_nextval('seq_eventtype'),-52,'Task';
insert into eventtype (id,permissions,value)
    select ome_nextval('seq_eventtype'),-52,'Test';
insert into eventtype (id,permissions,value)
    select ome_nextval('seq_eventtype'),-52,'Processing';
insert into eventtype (id,permissions,value)
    select ome_nextval('seq_eventtype'),-52,'FullText';
insert into eventtype (id,permissions,value)
    select ome_nextval('seq_eventtype'),-52,'Sessions';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'FP';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'FRET';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'TimeLapse';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'FourDPlus';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'Screen';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'Immunocytochemistry';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'Immunofluorescence';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'FISH';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'Electrophysiology';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'IonImaging';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'Colocalization';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'PGIDocumentation';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'FluorescenceLifetime';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'SpectralImaging';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'Photobleaching';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'Other';
insert into experimenttype (id,permissions,value)
    select ome_nextval('seq_experimenttype'),-52,'Unknown';
insert into family (id,permissions,value)
    select ome_nextval('seq_family'),-52,'linear';
insert into family (id,permissions,value)
    select ome_nextval('seq_family'),-52,'polynomial';
insert into family (id,permissions,value)
    select ome_nextval('seq_family'),-52,'exponential';
insert into family (id,permissions,value)
    select ome_nextval('seq_family'),-52,'logarithmic';
insert into filamenttype (id,permissions,value)
    select ome_nextval('seq_filamenttype'),-52,'Incandescent';
insert into filamenttype (id,permissions,value)
    select ome_nextval('seq_filamenttype'),-52,'Halogen';
insert into filamenttype (id,permissions,value)
    select ome_nextval('seq_filamenttype'),-52,'Other';
insert into filamenttype (id,permissions,value)
    select ome_nextval('seq_filamenttype'),-52,'Unknown';
insert into filtertype (id,permissions,value)
    select ome_nextval('seq_filtertype'),-52,'LongPass';
insert into filtertype (id,permissions,value)
    select ome_nextval('seq_filtertype'),-52,'ShortPass';
insert into filtertype (id,permissions,value)
    select ome_nextval('seq_filtertype'),-52,'BandPass';
insert into filtertype (id,permissions,value)
    select ome_nextval('seq_filtertype'),-52,'MultiPass';
insert into filtertype (id,permissions,value)
    select ome_nextval('seq_filtertype'),-52,'Dichroic';
insert into filtertype (id,permissions,value)
    select ome_nextval('seq_filtertype'),-52,'NeutralDensity';
insert into filtertype (id,permissions,value)
    select ome_nextval('seq_filtertype'),-52,'Tuneable';
insert into filtertype (id,permissions,value)
    select ome_nextval('seq_filtertype'),-52,'Other';
insert into filtertype (id,permissions,value)
    select ome_nextval('seq_filtertype'),-52,'Unknown';
insert into illumination (id,permissions,value)
    select ome_nextval('seq_illumination'),-52,'Transmitted';
insert into illumination (id,permissions,value)
    select ome_nextval('seq_illumination'),-52,'Epifluorescence';
insert into illumination (id,permissions,value)
    select ome_nextval('seq_illumination'),-52,'Oblique';
insert into illumination (id,permissions,value)
    select ome_nextval('seq_illumination'),-52,'NonLinear';
insert into illumination (id,permissions,value)
    select ome_nextval('seq_illumination'),-52,'Other';
insert into illumination (id,permissions,value)
    select ome_nextval('seq_illumination'),-52,'Unknown';
insert into immersion (id,permissions,value)
    select ome_nextval('seq_immersion'),-52,'Oil';
insert into immersion (id,permissions,value)
    select ome_nextval('seq_immersion'),-52,'Water';
insert into immersion (id,permissions,value)
    select ome_nextval('seq_immersion'),-52,'WaterDipping';
insert into immersion (id,permissions,value)
    select ome_nextval('seq_immersion'),-52,'Air';
insert into immersion (id,permissions,value)
    select ome_nextval('seq_immersion'),-52,'Multi';
insert into immersion (id,permissions,value)
    select ome_nextval('seq_immersion'),-52,'Glycerol';
insert into immersion (id,permissions,value)
    select ome_nextval('seq_immersion'),-52,'Other';
insert into immersion (id,permissions,value)
    select ome_nextval('seq_immersion'),-52,'Unknown';
insert into jobstatus (id,permissions,value)
    select ome_nextval('seq_jobstatus'),-52,'Submitted';
insert into jobstatus (id,permissions,value)
    select ome_nextval('seq_jobstatus'),-52,'Resubmitted';
insert into jobstatus (id,permissions,value)
    select ome_nextval('seq_jobstatus'),-52,'Queued';
insert into jobstatus (id,permissions,value)
    select ome_nextval('seq_jobstatus'),-52,'Requeued';
insert into jobstatus (id,permissions,value)
    select ome_nextval('seq_jobstatus'),-52,'Running';
insert into jobstatus (id,permissions,value)
    select ome_nextval('seq_jobstatus'),-52,'Error';
insert into jobstatus (id,permissions,value)
    select ome_nextval('seq_jobstatus'),-52,'Waiting';
insert into jobstatus (id,permissions,value)
    select ome_nextval('seq_jobstatus'),-52,'Finished';
insert into jobstatus (id,permissions,value)
    select ome_nextval('seq_jobstatus'),-52,'Cancelled';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'Rhodamine6G';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'CoumarinC30';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'ArFl';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'ArCl';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'KrFl';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'KrCl';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'XeFl';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'XeCl';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'XeBr';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'GaAs';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'GaAlAs';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'EMinus';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'Cu';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'Ag';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'N';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'Ar';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'Kr';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'Xe';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'HeNe';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'HeCd';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'CO';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'CO2';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'H2O';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'HFl';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'NdGlass';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'NdYAG';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'ErGlass';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'ErYAG';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'HoYLF';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'HoYAG';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'Ruby';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'TiSapphire';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'Alexandrite';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'Other';
insert into lasermedium (id,permissions,value)
    select ome_nextval('seq_lasermedium'),-52,'Unknown';
insert into lasertype (id,permissions,value)
    select ome_nextval('seq_lasertype'),-52,'Excimer';
insert into lasertype (id,permissions,value)
    select ome_nextval('seq_lasertype'),-52,'Gas';
insert into lasertype (id,permissions,value)
    select ome_nextval('seq_lasertype'),-52,'MetalVapor';
insert into lasertype (id,permissions,value)
    select ome_nextval('seq_lasertype'),-52,'SolidState';
insert into lasertype (id,permissions,value)
    select ome_nextval('seq_lasertype'),-52,'Dye';
insert into lasertype (id,permissions,value)
    select ome_nextval('seq_lasertype'),-52,'Semiconductor';
insert into lasertype (id,permissions,value)
    select ome_nextval('seq_lasertype'),-52,'FreeElectron';
insert into lasertype (id,permissions,value)
    select ome_nextval('seq_lasertype'),-52,'Other';
insert into lasertype (id,permissions,value)
    select ome_nextval('seq_lasertype'),-52,'Unknown';
insert into medium (id,permissions,value)
    select ome_nextval('seq_medium'),-52,'Air';
insert into medium (id,permissions,value)
    select ome_nextval('seq_medium'),-52,'Oil';
insert into medium (id,permissions,value)
    select ome_nextval('seq_medium'),-52,'Water';
insert into medium (id,permissions,value)
    select ome_nextval('seq_medium'),-52,'Glycerol';
insert into medium (id,permissions,value)
    select ome_nextval('seq_medium'),-52,'Other';
insert into medium (id,permissions,value)
    select ome_nextval('seq_medium'),-52,'Unknown';
insert into microbeammanipulationtype (id,permissions,value)
    select ome_nextval('seq_microbeammanipulationtype'),-52,'FRAP';
insert into microbeammanipulationtype (id,permissions,value)
    select ome_nextval('seq_microbeammanipulationtype'),-52,'Photoablation';
insert into microbeammanipulationtype (id,permissions,value)
    select ome_nextval('seq_microbeammanipulationtype'),-52,'Photoactivation';
insert into microbeammanipulationtype (id,permissions,value)
    select ome_nextval('seq_microbeammanipulationtype'),-52,'Uncaging';
insert into microbeammanipulationtype (id,permissions,value)
    select ome_nextval('seq_microbeammanipulationtype'),-52,'OpticalTrapping';
insert into microbeammanipulationtype (id,permissions,value)
    select ome_nextval('seq_microbeammanipulationtype'),-52,'FLIP';
insert into microbeammanipulationtype (id,permissions,value)
    select ome_nextval('seq_microbeammanipulationtype'),-52,'InverseFRAP';
insert into microbeammanipulationtype (id,permissions,value)
    select ome_nextval('seq_microbeammanipulationtype'),-52,'Other';
insert into microbeammanipulationtype (id,permissions,value)
    select ome_nextval('seq_microbeammanipulationtype'),-52,'Unknown';
insert into microscopetype (id,permissions,value)
    select ome_nextval('seq_microscopetype'),-52,'Upright';
insert into microscopetype (id,permissions,value)
    select ome_nextval('seq_microscopetype'),-52,'Inverted';
insert into microscopetype (id,permissions,value)
    select ome_nextval('seq_microscopetype'),-52,'Dissection';
insert into microscopetype (id,permissions,value)
    select ome_nextval('seq_microscopetype'),-52,'Electrophysiology';
insert into microscopetype (id,permissions,value)
    select ome_nextval('seq_microscopetype'),-52,'Other';
insert into microscopetype (id,permissions,value)
    select ome_nextval('seq_microscopetype'),-52,'Unknown';
insert into photometricinterpretation (id,permissions,value)
    select ome_nextval('seq_photometricinterpretation'),-52,'RGB';
insert into photometricinterpretation (id,permissions,value)
    select ome_nextval('seq_photometricinterpretation'),-52,'ARGB';
insert into photometricinterpretation (id,permissions,value)
    select ome_nextval('seq_photometricinterpretation'),-52,'CMYK';
insert into photometricinterpretation (id,permissions,value)
    select ome_nextval('seq_photometricinterpretation'),-52,'HSV';
insert into photometricinterpretation (id,permissions,value)
    select ome_nextval('seq_photometricinterpretation'),-52,'Monochrome';
insert into photometricinterpretation (id,permissions,value)
    select ome_nextval('seq_photometricinterpretation'),-52,'ColorMap';
insert into pixelstype (id,permissions,value)
    select ome_nextval('seq_pixelstype'),-52,'bit';
insert into pixelstype (id,permissions,value)
    select ome_nextval('seq_pixelstype'),-52,'int8';
insert into pixelstype (id,permissions,value)
    select ome_nextval('seq_pixelstype'),-52,'int16';
insert into pixelstype (id,permissions,value)
    select ome_nextval('seq_pixelstype'),-52,'int32';
insert into pixelstype (id,permissions,value)
    select ome_nextval('seq_pixelstype'),-52,'uint8';
insert into pixelstype (id,permissions,value)
    select ome_nextval('seq_pixelstype'),-52,'uint16';
insert into pixelstype (id,permissions,value)
    select ome_nextval('seq_pixelstype'),-52,'uint32';
insert into pixelstype (id,permissions,value)
    select ome_nextval('seq_pixelstype'),-52,'float';
insert into pixelstype (id,permissions,value)
    select ome_nextval('seq_pixelstype'),-52,'double';
insert into pixelstype (id,permissions,value)
    select ome_nextval('seq_pixelstype'),-52,'complex';
insert into pixelstype (id,permissions,value)
    select ome_nextval('seq_pixelstype'),-52,'double-complex';
insert into projectionaxis (id,permissions,value)
    select ome_nextval('seq_projectionaxis'),-52,'T';
insert into projectionaxis (id,permissions,value)
    select ome_nextval('seq_projectionaxis'),-52,'ModuloT';
insert into projectionaxis (id,permissions,value)
    select ome_nextval('seq_projectionaxis'),-52,'Z';
insert into projectionaxis (id,permissions,value)
    select ome_nextval('seq_projectionaxis'),-52,'ModuloZ';
insert into projectiontype (id,permissions,value)
    select ome_nextval('seq_projectiontype'),-52,'maximum';
insert into projectiontype (id,permissions,value)
    select ome_nextval('seq_projectiontype'),-52,'mean';
insert into projectiontype (id,permissions,value)
    select ome_nextval('seq_projectiontype'),-52,'sum';
insert into pulse (id,permissions,value)
    select ome_nextval('seq_pulse'),-52,'CW';
insert into pulse (id,permissions,value)
    select ome_nextval('seq_pulse'),-52,'Single';
insert into pulse (id,permissions,value)
    select ome_nextval('seq_pulse'),-52,'QSwitched';
insert into pulse (id,permissions,value)
    select ome_nextval('seq_pulse'),-52,'Repetitive';
insert into pulse (id,permissions,value)
    select ome_nextval('seq_pulse'),-52,'ModeLocked';
insert into pulse (id,permissions,value)
    select ome_nextval('seq_pulse'),-52,'Other';
insert into pulse (id,permissions,value)
    select ome_nextval('seq_pulse'),-52,'Unknown';
insert into renderingmodel (id,permissions,value)
    select ome_nextval('seq_renderingmodel'),-52,'rgb';
insert into renderingmodel (id,permissions,value)
    select ome_nextval('seq_renderingmodel'),-52,'greyscale';

-- Adding bit depth to pixelstype (#2724)
update pixelstype set bitsize = 1 where value = 'bit';
update pixelstype set bitsize = 8 where value = 'int8';
update pixelstype set bitsize = 8 where value = 'uint8';
update pixelstype set bitsize = 16 where value = 'int16';
update pixelstype set bitsize = 16 where value = 'uint16';
update pixelstype set bitsize = 32 where value = 'int32';
update pixelstype set bitsize = 32 where value = 'uint32';
update pixelstype set bitsize = 32 where value = 'float';
update pixelstype set bitsize = 64 where value = 'double';
update pixelstype set bitsize = 64 where value = 'complex';
update pixelstype set bitsize = 128 where value = 'double-complex';

-- reactivate not null constraints
alter table pixelstype alter column bitsize set not null;

--
-- Cryptographic functions for specifying UUID
--

create or replace function uuid() returns character(36)
as '
    select substring(x.my_rand from 1 for 8)||''-''||
           substring(x.my_rand from 9 for 4)||''-4''||
           substring(x.my_rand from 13 for 3)||''-''||x.clock_1||
           substring(x.my_rand from 16 for 3)||''-''||
           substring(x.my_rand from 19 for 12)
from
(select md5(clock_timestamp()::text||random()) as my_rand, to_hex(8+(3*random())::int) as clock_1) as x;'
language sql;

--
-- Configuration table including a UUID uniquely identifying this database.
--
create table configuration ( name varchar(255) primary key, value text );
insert into configuration values ('omero.db.uuid',uuid());

--
-- ticket:2201 - creating repository data structures
--
alter  table pixels add column path text;
alter  table pixels add column name text;
alter  table pixels add column repo varchar(36);
create index pixels_repo_index on pixels (repo);
-- No unique index on (path, repo, name) since it depends on params

alter  table originalfile alter column mimetype set default 'application/octet-stream';
alter  table originalfile alter column repo type varchar(36);
create index originalfile_mime_index on originalfile (mimetype);
create index originalfile_path_index on originalfile (path);
create index originalfile_repo_index on originalfile (repo);
create index originalfile_hash_index on originalfile (hash);

-- protect against 2017-SV5

CREATE UNIQUE INDEX originalfile_repo_path_index ON originalfile
    (repo, regexp_split_to_array('/' || path || name || '/', '/+'))
    WHERE repo IS NOT NULL;

--
-- end ticket:2201
--


-- ticket:11591 Shrink the userIP column down to maximum (IPv4-mapped IPv6)
alter table session alter column userIP TYPE varchar(45);


-- Indices. See #1640, #2573, etc.
create unique index namespace_name on namespace (name);
create unique index well_col_row on well (plate, "column", "row");
create index eventlog_entitytype on eventlog(entitytype);
create index eventlog_entityid on eventlog(entityid);
create index eventlog_action on eventlog(action);
create index annotation_discriminator on annotation(discriminator);
create index annotation_ns on annotation(ns);

CREATE INDEX experimenter_config_name ON experimenter_config(name);
CREATE INDEX experimenter_config_value ON experimenter_config(value);
CREATE INDEX experimentergroup_config_name ON experimentergroup_config(name);
CREATE INDEX experimentergroup_config_value ON experimentergroup_config(value);
CREATE INDEX genericexcitationsource_map_name ON genericexcitationsource_map(name);
CREATE INDEX genericexcitationsource_map_value ON genericexcitationsource_map(value);
CREATE INDEX imagingenvironment_map_name ON imagingenvironment_map(name);
CREATE INDEX imagingenvironment_map_value ON imagingenvironment_map(value);
CREATE INDEX annotation_mapValue_name ON annotation_mapValue(name);
CREATE INDEX annotation_mapValue_value ON annotation_mapValue(value);
CREATE INDEX metadataimportjob_versionInfo_name ON metadataimportjob_versionInfo(name);
CREATE INDEX metadataimportjob_versionInfo_value ON metadataimportjob_versionInfo(value);
CREATE INDEX uploadjob_versionInfo_name ON uploadjob_versionInfo(name);
CREATE INDEX uploadjob_versionInfo_value ON uploadjob_versionInfo(value);

create table password ( experimenter_id bigint primary key REFERENCES experimenter (id), hash VARCHAR(255),
    changed TIMESTAMP WITHOUT TIME ZONE);
insert into password values (0,'@ROOTPASS@');
insert into password values (1,'');
-- root can now login with omero.rootpass property value
-- and guest can login with any value

-- use a table to note the role IDs explicitly in the database

CREATE TABLE _roles (
    root_user_id BIGINT NOT NULL,
    guest_user_id BIGINT NOT NULL,
    system_group_id BIGINT NOT NULL,
    user_group_id BIGINT NOT NULL,
    guest_group_id BIGINT NOT NULL
);

INSERT INTO _roles (root_user_id, guest_user_id, system_group_id, user_group_id, guest_group_id)
    VALUES (0, 1, 0, 1, 2);

--
-- FS Resources
--

-- Prevent the deletion of mimetype = "Directory" objects
create or replace function _fs_dir_delete() returns trigger AS $_fs_dir_delete$
    begin
        --
        -- If any children are found, prevent deletion
        --
        if OLD.mimetype = 'Directory' and exists(
            select id from originalfile
            where repo = OLD.repo and path = OLD.path || OLD.name || '/'
            limit 1) then

                -- CANCEL DELETE
                RAISE EXCEPTION '%%', 'Directory('||OLD.id||')='||OLD.path||OLD.name||'/ is not empty!';

        end if;
        return OLD; -- proceed
    end;
$_fs_dir_delete$ LANGUAGE plpgsql;

create trigger _fs_dir_delete
before delete on originalfile
    for each row execute procedure _fs_dir_delete();

-- Prevent Directory and Repository entries in the originalfile table from having their mimetype changed.
CREATE OR REPLACE FUNCTION _fs_protected_mimetype() RETURNS "trigger" AS $$
    BEGIN
        IF OLD.mimetype IN ('Directory', 'Repository') AND (NEW.mimetype IS NULL OR NEW.mimetype != OLD.mimetype) THEN
            RAISE EXCEPTION 'cannot change media type %% of file id=%%', OLD.mimetype, OLD.id;
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER _fs_protected_mimetype
    BEFORE UPDATE ON originalfile
    FOR EACH ROW EXECUTE PROCEDURE _fs_protected_mimetype();

-- Prevent SQL DELETE from removing the root experimenter from the system or user group.
CREATE FUNCTION prevent_root_deactivate_delete() RETURNS "trigger" AS $$

    DECLARE
        roles _roles%%ROWTYPE;

    BEGIN
        SELECT * INTO STRICT roles FROM _roles;

        IF OLD.child = roles.root_user_id THEN
            IF OLD.parent = roles.system_group_id THEN
                RAISE EXCEPTION 'cannot remove system group membership for root';
            ELSIF OLD.parent = roles.user_group_id THEN
                RAISE EXCEPTION 'cannot remove user group membership for root';
            END IF;
        END IF;
        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_root_deactivate_delete
    BEFORE DELETE ON groupexperimentermap
    FOR EACH ROW EXECUTE PROCEDURE prevent_root_deactivate_delete();

-- Prevent SQL UPDATE from removing the root experimenter from the system or user group.
CREATE FUNCTION prevent_root_deactivate_update() RETURNS "trigger" AS $$

    DECLARE
        roles _roles%%ROWTYPE;

    BEGIN
        SELECT * INTO STRICT roles FROM _roles;

        IF OLD.child != NEW.child OR OLD.parent != NEW.parent THEN
            IF OLD.child = roles.root_user_id THEN
                IF OLD.parent = roles.system_group_id THEN
                    RAISE EXCEPTION 'cannot remove system group membership for root';
                ELSIF OLD.parent = roles.user_group_id THEN
                    RAISE EXCEPTION 'cannot remove user group membership for root';
                END IF;
            END IF;
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_root_deactivate_update
    BEFORE UPDATE ON groupexperimentermap
    FOR EACH ROW EXECUTE PROCEDURE prevent_root_deactivate_update();

-- Prevent the root and guest experimenters from being renamed.
CREATE FUNCTION prevent_experimenter_rename() RETURNS "trigger" AS $$

    DECLARE
        roles _roles%%ROWTYPE;

    BEGIN
        SELECT * INTO STRICT roles FROM _roles;

        IF OLD.omename != NEW.omename THEN
            IF OLD.id = roles.root_user_id THEN
                RAISE EXCEPTION 'cannot rename root experimenter';
            ELSIF OLD.id = roles.guest_user_id THEN
                RAISE EXCEPTION 'cannot rename guest experimenter';
            END IF;
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_experimenter_rename
    BEFORE UPDATE ON experimenter
    FOR EACH ROW EXECUTE PROCEDURE prevent_experimenter_rename();

-- Prevent the system, user and guest groups from being renamed.
CREATE FUNCTION prevent_experimenter_group_rename() RETURNS "trigger" AS $$

    DECLARE
        roles _roles%%ROWTYPE;

    BEGIN
        SELECT * INTO STRICT roles FROM _roles;

        IF OLD.name != NEW.name THEN
            IF OLD.id = roles.system_group_id THEN
                RAISE EXCEPTION 'cannot rename system experimenter group';
            ELSIF OLD.id = roles.user_group_id THEN
                RAISE EXCEPTION 'cannot rename user experimenter group';
            ELSIF OLD.id = roles.guest_group_id THEN
                RAISE EXCEPTION 'cannot rename guest experimenter group';
            END IF;
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_experimenter_group_rename
    BEFORE UPDATE ON experimentergroup
    FOR EACH ROW EXECUTE PROCEDURE prevent_experimenter_group_rename();

-- Prevent light administrator privileges from restricting the root experimenter.
CREATE FUNCTION prevent_root_privilege_restriction() RETURNS "trigger" AS $$

    DECLARE
        roles _roles%%ROWTYPE;

    BEGIN
        SELECT * INTO STRICT roles FROM _roles;

        IF NEW.experimenter_id = roles.root_user_id AND NEW.name LIKE 'AdminPrivilege:%%' AND NEW.value NOT ILIKE 'true' THEN
            RAISE EXCEPTION 'cannot restrict admin privileges of root experimenter';
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_root_privilege_restriction
    BEFORE INSERT OR UPDATE ON experimenter_config
    FOR EACH ROW EXECUTE PROCEDURE prevent_root_privilege_restriction();

create table _fs_deletelog (
    event_id bigint not null,
    file_id bigint not null,
    owner_id bigint not null,
    group_id bigint not null,
    path text not null,
    name varchar(255) not null,
    repo varchar(36) not null);

create index _fs_deletelog_event on _fs_deletelog(event_id);
create index _fs_deletelog_file on _fs_deletelog(file_id);
create index _fs_deletelog_owner on _fs_deletelog(owner_id);
create index _fs_deletelog_group on _fs_deletelog(group_id);
create index _fs_deletelog_path on _fs_deletelog(path);
create index _fs_deletelog_name on _fs_deletelog(name);
create index _fs_deletelog_repo on _fs_deletelog(repo);

create or replace function _fs_log_delete() returns trigger AS $_fs_log_delete$
    begin
        if OLD.repo is not null then
            INSERT INTO _fs_deletelog (event_id, file_id, owner_id, group_id, "path", "name", repo)
                SELECT _current_or_new_event(), OLD.id, OLD.owner_id, OLD.group_id, OLD."path", OLD."name", OLD.repo;
        end if;
        return OLD;
    END;
$_fs_log_delete$ LANGUAGE plpgsql;

create trigger _fs_log_delete
after delete on originalfile
    for each row execute procedure _fs_log_delete();

-- Compare IllegalArgumentException cases in object.vm
-- and unit testing in PropertyConstraintTest methods.

ALTER TABLE laser
    ALTER COLUMN wavelength TYPE positive_float;

ALTER TABLE lightsettings
    ALTER COLUMN wavelength TYPE positive_float;

ALTER TABLE logicalchannel ALTER COLUMN emissionwave TYPE positive_float;
ALTER TABLE logicalchannel ALTER COLUMN excitationwave TYPE positive_float;

ALTER TABLE pixels ALTER COLUMN physicalsizex TYPE positive_float;
ALTER TABLE pixels ALTER COLUMN physicalsizey TYPE positive_float;
ALTER TABLE pixels ALTER COLUMN physicalsizez TYPE positive_float;

ALTER TABLE transmittancerange ALTER COLUMN cutin TYPE positive_float;
ALTER TABLE transmittancerange ALTER COLUMN cutintolerance TYPE nonnegative_float;
ALTER TABLE transmittancerange ALTER COLUMN cutout TYPE positive_float;
ALTER TABLE transmittancerange ALTER COLUMN cutouttolerance TYPE nonnegative_float;

-- image.series should never be null

ALTER TABLE image ALTER COLUMN series SET DEFAULT 0;
ALTER TABLE image ALTER COLUMN series SET NOT NULL;

CREATE FUNCTION image_series_default_zero() RETURNS "trigger" AS $$
    BEGIN
        IF NEW.series IS NULL THEN
            NEW.series := 0;
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER image_series_default_zero
    BEFORE INSERT OR UPDATE ON image
    FOR EACH ROW EXECUTE PROCEDURE image_series_default_zero();

-- ensure that all annotation namespaces are noted in namespace table

CREATE FUNCTION add_to_namespace() RETURNS "trigger" AS $$
    BEGIN
        IF NOT (NEW.ns IS NULL OR EXISTS (SELECT 1 FROM namespace WHERE name = NEW.ns LIMIT 1)) THEN
            INSERT INTO namespace (id, name, permissions)
                SELECT ome_nextval('seq_namespace'), NEW.ns, -52;
        END IF;

        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION update_namespace() RETURNS "trigger" AS $$
    BEGIN
        IF OLD.name <> NEW.name AND EXISTS (SELECT 1 FROM annotation WHERE ns = OLD.name LIMIT 1) THEN
            RAISE EXCEPTION 'cannot rename namespace that is still used by annotation';
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION delete_from_namespace() RETURNS "trigger" AS $$
    BEGIN
        IF EXISTS (SELECT 1 FROM annotation WHERE ns = OLD.name LIMIT 1) THEN
            RAISE EXCEPTION 'cannot delete namespace that is still used by annotation';
        END IF;

        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER add_to_namespace
    AFTER INSERT OR UPDATE ON annotation
    FOR EACH ROW EXECUTE PROCEDURE add_to_namespace();

CREATE TRIGGER update_namespace
    BEFORE UPDATE ON namespace
    FOR EACH ROW EXECUTE PROCEDURE update_namespace();

CREATE TRIGGER delete_from_namespace
    BEFORE DELETE ON namespace
    FOR EACH ROW EXECUTE PROCEDURE delete_from_namespace();

-- Replace globals' annotation count tables with views.

DROP TABLE count_experimenter_annotationlinks_by_owner;
DROP TABLE count_experimentergroup_annotationlinks_by_owner;
DROP TABLE count_namespace_annotationlinks_by_owner;
DROP TABLE count_node_annotationlinks_by_owner;
DROP TABLE count_session_annotationlinks_by_owner;

CREATE VIEW count_experimenter_annotationlinks_by_owner (experimenter_id, owner_id, count) AS
    SELECT parent, owner_id, count(*)
        FROM experimenterannotationlink
        GROUP BY parent, owner_id
        ORDER BY parent;

CREATE VIEW count_experimentergroup_annotationlinks_by_owner (experimentergroup_id, owner_id, count) AS
    SELECT parent, owner_id, count(*)
        FROM experimentergroupannotationlink
        GROUP BY parent, owner_id
        ORDER BY parent;

CREATE VIEW count_namespace_annotationlinks_by_owner (namespace_id, owner_id, count) AS
    SELECT parent, owner_id, count(*)
        FROM namespaceannotationlink
        GROUP BY parent, owner_id
        ORDER BY parent;

CREATE VIEW count_node_annotationlinks_by_owner (node_id, owner_id, count) AS
    SELECT parent, owner_id, count(*)
        FROM nodeannotationlink
        GROUP BY parent, owner_id
        ORDER BY parent;

CREATE VIEW count_session_annotationlinks_by_owner (session_id, owner_id, count) AS
    SELECT parent, owner_id, count(*)
        FROM sessionannotationlink
        GROUP BY parent, owner_id
        ORDER BY parent;

-- A property value is null if and only if the corresponding unit is null.

ALTER TABLE detector ADD CONSTRAINT voltage_unitpair
    CHECK (voltage IS NULL AND voltageUnit IS NULL
        OR voltage IS NOT NULL AND voltageUnit IS NOT NULL);

ALTER TABLE detectorsettings ADD CONSTRAINT voltage_unitpair
    CHECK (voltage IS NULL AND voltageUnit IS NULL
        OR voltage IS NOT NULL AND voltageUnit IS NOT NULL);

ALTER TABLE detectorsettings ADD CONSTRAINT readOutRate_unitpair
    CHECK (readOutRate IS NULL AND readOutRateUnit IS NULL
        OR readOutRate IS NOT NULL AND readOutRateUnit IS NOT NULL);

ALTER TABLE imagingenvironment ADD CONSTRAINT temperature_unitpair
    CHECK (temperature IS NULL AND temperatureUnit IS NULL
        OR temperature IS NOT NULL AND temperatureUnit IS NOT NULL);

ALTER TABLE imagingenvironment ADD CONSTRAINT airPressure_unitpair
    CHECK (airPressure IS NULL AND airPressureUnit IS NULL
        OR airPressure IS NOT NULL AND airPressureUnit IS NOT NULL);

ALTER TABLE laser ADD CONSTRAINT wavelength_unitpair
    CHECK (wavelength IS NULL AND wavelengthUnit IS NULL
        OR wavelength IS NOT NULL AND wavelengthUnit IS NOT NULL);

ALTER TABLE laser ADD CONSTRAINT repetitionRate_unitpair
    CHECK (repetitionRate IS NULL AND repetitionRateUnit IS NULL
        OR repetitionRate IS NOT NULL AND repetitionRateUnit IS NOT NULL);

ALTER TABLE lightsettings ADD CONSTRAINT wavelength_unitpair
    CHECK (wavelength IS NULL AND wavelengthUnit IS NULL
        OR wavelength IS NOT NULL AND wavelengthUnit IS NOT NULL);

ALTER TABLE lightsource ADD CONSTRAINT power_unitpair
    CHECK (power IS NULL AND powerUnit IS NULL
        OR power IS NOT NULL AND powerUnit IS NOT NULL);

ALTER TABLE logicalchannel ADD CONSTRAINT pinHoleSize_unitpair
    CHECK (pinHoleSize IS NULL AND pinHoleSizeUnit IS NULL
        OR pinHoleSize IS NOT NULL AND pinHoleSizeUnit IS NOT NULL);

ALTER TABLE logicalchannel ADD CONSTRAINT excitationWave_unitpair
    CHECK (excitationWave IS NULL AND excitationWaveUnit IS NULL
        OR excitationWave IS NOT NULL AND excitationWaveUnit IS NOT NULL);

ALTER TABLE logicalchannel ADD CONSTRAINT emissionWave_unitpair
    CHECK (emissionWave IS NULL AND emissionWaveUnit IS NULL
        OR emissionWave IS NOT NULL AND emissionWaveUnit IS NOT NULL);

ALTER TABLE objective ADD CONSTRAINT workingDistance_unitpair
    CHECK (workingDistance IS NULL AND workingDistanceUnit IS NULL
        OR workingDistance IS NOT NULL AND workingDistanceUnit IS NOT NULL);

ALTER TABLE pixels ADD CONSTRAINT physicalSizeX_unitpair
    CHECK (physicalSizeX IS NULL AND physicalSizeXUnit IS NULL
        OR physicalSizeX IS NOT NULL AND physicalSizeXUnit IS NOT NULL);

ALTER TABLE pixels ADD CONSTRAINT physicalSizeY_unitpair
    CHECK (physicalSizeY IS NULL AND physicalSizeYUnit IS NULL
        OR physicalSizeY IS NOT NULL AND physicalSizeYUnit IS NOT NULL);

ALTER TABLE pixels ADD CONSTRAINT physicalSizeZ_unitpair
    CHECK (physicalSizeZ IS NULL AND physicalSizeZUnit IS NULL
        OR physicalSizeZ IS NOT NULL AND physicalSizeZUnit IS NOT NULL);

ALTER TABLE pixels ADD CONSTRAINT timeIncrement_unitpair
    CHECK (timeIncrement IS NULL AND timeIncrementUnit IS NULL
        OR timeIncrement IS NOT NULL AND timeIncrementUnit IS NOT NULL);

ALTER TABLE planeinfo ADD CONSTRAINT deltaT_unitpair
    CHECK (deltaT IS NULL AND deltaTUnit IS NULL
        OR deltaT IS NOT NULL AND deltaTUnit IS NOT NULL);

ALTER TABLE planeinfo ADD CONSTRAINT positionX_unitpair
    CHECK (positionX IS NULL AND positionXUnit IS NULL
        OR positionX IS NOT NULL AND positionXUnit IS NOT NULL);

ALTER TABLE planeinfo ADD CONSTRAINT positionY_unitpair
    CHECK (positionY IS NULL AND positionYUnit IS NULL
        OR positionY IS NOT NULL AND positionYUnit IS NOT NULL);

ALTER TABLE planeinfo ADD CONSTRAINT positionZ_unitpair
    CHECK (positionZ IS NULL AND positionZUnit IS NULL
        OR positionZ IS NOT NULL AND positionZUnit IS NOT NULL);

ALTER TABLE planeinfo ADD CONSTRAINT exposureTime_unitpair
    CHECK (exposureTime IS NULL AND exposureTimeUnit IS NULL
        OR exposureTime IS NOT NULL AND exposureTimeUnit IS NOT NULL);

ALTER TABLE plate ADD CONSTRAINT wellOriginX_unitpair
    CHECK (wellOriginX IS NULL AND wellOriginXUnit IS NULL
        OR wellOriginX IS NOT NULL AND wellOriginXUnit IS NOT NULL);

ALTER TABLE plate ADD CONSTRAINT wellOriginY_unitpair
    CHECK (wellOriginY IS NULL AND wellOriginYUnit IS NULL
        OR wellOriginY IS NOT NULL AND wellOriginYUnit IS NOT NULL);

ALTER TABLE shape ADD CONSTRAINT strokeWidth_unitpair
    CHECK (strokeWidth IS NULL AND strokeWidthUnit IS NULL
        OR strokeWidth IS NOT NULL AND strokeWidthUnit IS NOT NULL);

ALTER TABLE shape ADD CONSTRAINT fontSize_unitpair
    CHECK (fontSize IS NULL AND fontSizeUnit IS NULL
        OR fontSize IS NOT NULL AND fontSizeUnit IS NOT NULL);

ALTER TABLE stagelabel ADD CONSTRAINT positionX_unitpair
    CHECK (positionX IS NULL AND positionXUnit IS NULL
        OR positionX IS NOT NULL AND positionXUnit IS NOT NULL);

ALTER TABLE stagelabel ADD CONSTRAINT positionY_unitpair
    CHECK (positionY IS NULL AND positionYUnit IS NULL
        OR positionY IS NOT NULL AND positionYUnit IS NOT NULL);

ALTER TABLE stagelabel ADD CONSTRAINT positionZ_unitpair
    CHECK (positionZ IS NULL AND positionZUnit IS NULL
        OR positionZ IS NOT NULL AND positionZUnit IS NOT NULL);

ALTER TABLE transmittancerange ADD CONSTRAINT cutIn_unitpair
    CHECK (cutIn IS NULL AND cutInUnit IS NULL
        OR cutIn IS NOT NULL AND cutInUnit IS NOT NULL);

ALTER TABLE transmittancerange ADD CONSTRAINT cutOut_unitpair
    CHECK (cutOut IS NULL AND cutOutUnit IS NULL
        OR cutOut IS NOT NULL AND cutOutUnit IS NOT NULL);

ALTER TABLE transmittancerange ADD CONSTRAINT cutInTolerance_unitpair
    CHECK (cutInTolerance IS NULL AND cutInToleranceUnit IS NULL
        OR cutInTolerance IS NOT NULL AND cutInToleranceUnit IS NOT NULL);

ALTER TABLE transmittancerange ADD CONSTRAINT cutOutTolerance_unitpair
    CHECK (cutOutTolerance IS NULL AND cutOutToleranceUnit IS NULL
        OR cutOutTolerance IS NOT NULL AND cutOutToleranceUnit IS NOT NULL);

ALTER TABLE wellsample ADD CONSTRAINT posX_unitpair
    CHECK (posX IS NULL AND posXUnit IS NULL
        OR posX IS NOT NULL AND posXUnit IS NOT NULL);

ALTER TABLE wellsample ADD CONSTRAINT posY_unitpair
    CHECK (posY IS NULL AND posYUnit IS NULL
        OR posY IS NOT NULL AND posYUnit IS NOT NULL);


-- Temporary workaround for the width of map types

ALTER TABLE experimenter_config ALTER COLUMN name TYPE TEXT;
ALTER TABLE experimenter_config ALTER COLUMN value TYPE TEXT;
ALTER TABLE experimentergroup_config ALTER COLUMN name TYPE TEXT;
ALTER TABLE experimentergroup_config ALTER COLUMN value TYPE TEXT;
ALTER TABLE genericexcitationsource_map ALTER COLUMN name TYPE TEXT;
ALTER TABLE genericexcitationsource_map ALTER COLUMN value TYPE TEXT;
ALTER TABLE imagingenvironment_map ALTER COLUMN name TYPE TEXT;
ALTER TABLE imagingenvironment_map ALTER COLUMN value TYPE TEXT;
ALTER TABLE annotation_mapValue ALTER COLUMN name TYPE TEXT;
ALTER TABLE annotation_mapValue ALTER COLUMN value TYPE TEXT;
ALTER TABLE metadataimportjob_versionInfo ALTER COLUMN name TYPE TEXT;
ALTER TABLE metadataimportjob_versionInfo ALTER COLUMN value TYPE TEXT;
ALTER TABLE uploadjob_versionInfo ALTER COLUMN name TYPE TEXT;
ALTER TABLE uploadjob_versionInfo ALTER COLUMN value TYPE TEXT;

-- The folder hierarchy should be acyclic.

CREATE FUNCTION preserve_folder_tree() RETURNS "trigger" AS $$

    DECLARE
        parent_id BIGINT;

    BEGIN
        parent_id := NEW.parentfolder;
        WHILE parent_id IS NOT NULL
        LOOP
            IF parent_id = NEW.id THEN
                RAISE EXCEPTION 'folder %% would cause a cycle in the hierarchy', NEW.id;
            ELSE
                SELECT parentfolder INTO STRICT parent_id FROM folder WHERE id = parent_id;
            END IF;
        END LOOP;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER preserve_folder_tree
    AFTER INSERT OR UPDATE ON folder
    FOR EACH ROW EXECUTE PROCEDURE preserve_folder_tree();

-- Set up the current administrative privileges table and use it to prevent privilege elevation.

CREATE UNLOGGED TABLE _current_admin_privileges (
    transaction BIGINT,
    privilege VARCHAR(255)
);

CREATE INDEX i_current_admin_privileges_transactions ON _current_admin_privileges(transaction);

CREATE OR REPLACE FUNCTION group_link_insert_check() RETURNS "trigger" AS $$

    DECLARE
        roles _roles%%ROWTYPE;

    BEGIN
        SELECT * INTO STRICT roles FROM _roles;
        IF NEW.parent = roles.system_group_id AND EXISTS (SELECT 1 FROM adminprivilege p WHERE NOT
                (EXISTS (SELECT 1 FROM experimenter_config WHERE experimenter_id = NEW.child AND name = 'AdminPrivilege:' || p.value AND value NOT ILIKE 'true') OR
                 EXISTS (SELECT 1 FROM _current_admin_privileges WHERE transaction = txid_current() AND privilege = p.value))) THEN
            RAISE EXCEPTION 'cannot give administrator privileges that current user does not have';
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION group_link_update_check() RETURNS "trigger" AS $$

    DECLARE
        roles _roles%%ROWTYPE;

    BEGIN
        SELECT * INTO STRICT roles FROM _roles;

        IF (OLD.parent <> NEW.parent OR OLD.child <> NEW.child) AND NEW.parent = roles.system_group_id AND
        EXISTS (SELECT 1 FROM adminprivilege p WHERE NOT
                (EXISTS (SELECT 1 FROM experimenter_config WHERE experimenter_id = NEW.child AND name = 'AdminPrivilege:' || p.value AND value NOT ILIKE 'true') OR
                 EXISTS (SELECT 1 FROM _current_admin_privileges WHERE transaction = txid_current() AND privilege = p.value))) THEN
            RAISE EXCEPTION 'cannot give administrator privileges that current user does not have';
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION user_config_delete_check() RETURNS "trigger" AS $$

    DECLARE
        roles _roles%%ROWTYPE;

    BEGIN
        SELECT * INTO STRICT roles FROM _roles;

        IF OLD.name LIKE 'AdminPrivilege:%%' AND OLD.value NOT ILIKE 'true' AND
       EXISTS (SELECT 1 FROM groupexperimentermap WHERE parent = roles.system_group_id AND child = OLD.experimenter_id) AND
       NOT EXISTS (SELECT 1 FROM _current_admin_privileges p WHERE p.transaction = txid_current() AND 'AdminPrivilege:' || p.privilege = OLD.name) THEN
            RAISE EXCEPTION 'cannot give administrator privileges that current user does not have';
        END IF;

        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION user_config_update_check() RETURNS "trigger" AS $$

    DECLARE
        roles _roles%%ROWTYPE;

    BEGIN
        SELECT * INTO STRICT roles FROM _roles;

        IF (OLD.experimenter_id <> NEW.experimenter_id OR OLD.name <> NEW.name OR OLD.value <> NEW.value) AND
       OLD.name LIKE 'AdminPrivilege:%%' AND OLD.value NOT ILIKE 'true' AND
       EXISTS (SELECT 1 FROM groupexperimentermap WHERE parent = roles.system_group_id AND child = OLD.experimenter_id) AND
       NOT EXISTS (SELECT 1 FROM _current_admin_privileges p WHERE p.transaction = txid_current() AND 'AdminPrivilege:' || p.privilege = OLD.name) THEN
            RAISE EXCEPTION 'cannot give administrator privileges that current user does not have';
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER group_link_insert_trigger
    AFTER INSERT ON groupexperimentermap DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW EXECUTE PROCEDURE group_link_insert_check();

CREATE CONSTRAINT TRIGGER group_link_update_trigger
    AFTER UPDATE ON groupexperimentermap DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW EXECUTE PROCEDURE group_link_update_check();

CREATE CONSTRAINT TRIGGER user_config_delete_trigger
    AFTER DELETE ON experimenter_config DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW EXECUTE PROCEDURE user_config_delete_check();

CREATE CONSTRAINT TRIGGER user_config_update_trigger
    AFTER UPDATE ON experimenter_config DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW EXECUTE PROCEDURE user_config_update_check();

-- Use secret key in setting originalfile.repo.

CREATE FUNCTION _protect_originalfile_repo_insert() RETURNS "trigger" AS $$

    DECLARE
        secret_key VARCHAR;
        secret_key_length INTEGER;
        is_good_change BOOLEAN := TRUE;

    BEGIN
        FOR secret_key IN SELECT uuid FROM node WHERE down IS NULL LOOP
            secret_key_length := LENGTH(secret_key);

            IF NEW.repo IS NULL THEN
                IF LEFT(NEW.name, secret_key_length) = secret_key THEN
                    NEW.name := RIGHT(NEW.name, -secret_key_length);
                END IF;
            ELSE
                IF LEFT(NEW.name, secret_key_length) = secret_key THEN
                    is_good_change := TRUE;
                    NEW.name := RIGHT(NEW.name, -secret_key_length);
                    EXIT;
                ELSE
                    is_good_change := FALSE;
                END IF;
            END IF;
        END LOOP;

        IF NOT is_good_change THEN
            RAISE EXCEPTION 'cannot set original repo property without secret key';
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION _protect_originalfile_repo_update() RETURNS "trigger" AS $$

    DECLARE
        secret_key VARCHAR;
        secret_key_length INTEGER;
        is_good_change BOOLEAN := TRUE;

    BEGIN
        FOR secret_key IN SELECT uuid FROM node WHERE down IS NULL LOOP
            secret_key_length := LENGTH(secret_key);

            IF NEW.repo IS NULL OR OLD.repo = NEW.repo THEN
                IF LEFT(NEW.name, secret_key_length) = secret_key THEN
                    NEW.name := RIGHT(NEW.name, -secret_key_length);
                END IF;
            ELSE
                IF LEFT(NEW.name, secret_key_length) = secret_key THEN
                    is_good_change := TRUE;
                    NEW.name := RIGHT(NEW.name, -secret_key_length);
                    EXIT;
                ELSE
                    is_good_change := FALSE;
                END IF;
            END IF;
        END LOOP;

        IF NOT is_good_change THEN
            RAISE EXCEPTION 'cannot set original file repo property without secret key';
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER _protect_originalfile_repo_insert
    BEFORE INSERT ON originalfile
    FOR EACH ROW EXECUTE PROCEDURE _protect_originalfile_repo_insert();

CREATE TRIGGER _protect_originalfile_repo_update
    BEFORE UPDATE ON originalfile
    FOR EACH ROW EXECUTE PROCEDURE _protect_originalfile_repo_update();

CREATE INDEX node_down ON node(down);

-- Here we have finished initializing this database.

update dbpatch set message = 'Database ready.', finished = clock_timestamp()
  where currentVersion = 'OMERO5.4' and
        currentPatch = 0 and
        previousVersion = 'OMERO5.4' and
        previousPatch = 0;

COMMIT;
