--
-- Copyright 2008 Glencoe Software, Inc. All rights reserved.
-- Use is subject to license terms supplied in LICENSE.txt
--

--
-- Merged OMERO3A__5/views.sql and update script from OMERO3A__5
-- to OMERO3A__11.

-- The only change here is to the dbpatch version number
-- and the BEGIN/COMMITs
--
-- EXCEPT
--
--  * A fix for duplicate values in new indexes which deletes
--    all duplicates execpt for that with the highest id. Used
--    at the beginning of 8->9 and 9->10
--

--------------------------------------------------------
-- OMERO3A__5/view.sql
-- ===================

-- The following was generated by dsl/resources/ome/dsl/views.vm
-- and can be used to overwrite the generated Map<Long, Long> tables
-- with functional views.

  DROP TABLE count_Plate_screenLinks_by_owner;

  CREATE OR REPLACE VIEW count_Plate_screenLinks_by_owner (Plate_id, owner_id, count) AS select child, owner_id, count(*)
    FROM ScreenPlateLink GROUP BY child, owner_id ORDER BY child;

  DROP TABLE count_Plate_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_Plate_annotationLinks_by_owner (Plate_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM PlateAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Pixels_pixelsFileMaps_by_owner;

  CREATE OR REPLACE VIEW count_Pixels_pixelsFileMaps_by_owner (Pixels_id, owner_id, count) AS select child, owner_id, count(*)
    FROM PixelsOriginalFileMap GROUP BY child, owner_id ORDER BY child;

  DROP TABLE count_Pixels_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_Pixels_annotationLinks_by_owner (Pixels_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM PixelsAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_ExperimenterGroup_groupExperimenterMap_by_owner;

  CREATE OR REPLACE VIEW count_ExperimenterGroup_groupExperimenterMap_by_owner (ExperimenterGroup_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM GroupExperimenterMap GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Screen_plateLinks_by_owner;

  CREATE OR REPLACE VIEW count_Screen_plateLinks_by_owner (Screen_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ScreenPlateLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Screen_reagentLinks_by_owner;

  CREATE OR REPLACE VIEW count_Screen_reagentLinks_by_owner (Screen_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ScreenReagentLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Screen_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_Screen_annotationLinks_by_owner (Screen_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ScreenAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_OriginalFile_pixelsFileMaps_by_owner;

  CREATE OR REPLACE VIEW count_OriginalFile_pixelsFileMaps_by_owner (OriginalFile_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM PixelsOriginalFileMap GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_OriginalFile_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_OriginalFile_annotationLinks_by_owner (OriginalFile_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM OriginalFileAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Annotation_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_Annotation_annotationLinks_by_owner (Annotation_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM AnnotationAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Project_datasetLinks_by_owner;

  CREATE OR REPLACE VIEW count_Project_datasetLinks_by_owner (Project_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ProjectDatasetLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Project_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_Project_annotationLinks_by_owner (Project_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ProjectAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_RoiLink_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_RoiLink_annotationLinks_by_owner (RoiLink_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM RoiLinkAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_ScreenAcquisition_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_ScreenAcquisition_annotationLinks_by_owner (ScreenAcquisition_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ScreenAcquisitionAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_CategoryGroup_categoryLinks_by_owner;

  CREATE OR REPLACE VIEW count_CategoryGroup_categoryLinks_by_owner (CategoryGroup_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM CategoryGroupCategoryLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_WellSample_imageLinks_by_owner;

  CREATE OR REPLACE VIEW count_WellSample_imageLinks_by_owner (WellSample_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM WellSampleImageLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_WellSample_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_WellSample_annotationLinks_by_owner (WellSample_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM WellSampleAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Job_originalFileLinks_by_owner;

  CREATE OR REPLACE VIEW count_Job_originalFileLinks_by_owner (Job_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM JobOriginalFileLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Well_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_Well_annotationLinks_by_owner (Well_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM WellAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Category_categoryGroupLinks_by_owner;

  CREATE OR REPLACE VIEW count_Category_categoryGroupLinks_by_owner (Category_id, owner_id, count) AS select child, owner_id, count(*)
    FROM CategoryGroupCategoryLink GROUP BY child, owner_id ORDER BY child;

  DROP TABLE count_Category_imageLinks_by_owner;

  CREATE OR REPLACE VIEW count_Category_imageLinks_by_owner (Category_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM CategoryImageLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Experimenter_groupExperimenterMap_by_owner;

  CREATE OR REPLACE VIEW count_Experimenter_groupExperimenterMap_by_owner (Experimenter_id, owner_id, count) AS select child, owner_id, count(*)
    FROM GroupExperimenterMap GROUP BY child, owner_id ORDER BY child;

  DROP TABLE count_Experimenter_annotationLinks_by_owner;

  CREATE OR REPLACE 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;

  DROP TABLE count_Image_categoryLinks_by_owner;

  CREATE OR REPLACE VIEW count_Image_categoryLinks_by_owner (Image_id, owner_id, count) AS select child, owner_id, count(*)
    FROM CategoryImageLink GROUP BY child, owner_id ORDER BY child;

  DROP TABLE count_Image_datasetLinks_by_owner;

  CREATE OR REPLACE VIEW count_Image_datasetLinks_by_owner (Image_id, owner_id, count) AS select child, owner_id, count(*)
    FROM DatasetImageLink GROUP BY child, owner_id ORDER BY child;

  DROP TABLE count_Image_sampleLinks_by_owner;

  CREATE OR REPLACE VIEW count_Image_sampleLinks_by_owner (Image_id, owner_id, count) AS select child, owner_id, count(*)
    FROM WellSampleImageLink GROUP BY child, owner_id ORDER BY child;

  DROP TABLE count_Image_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_Image_annotationLinks_by_owner (Image_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ImageAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Channel_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_Channel_annotationLinks_by_owner (Channel_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ChannelAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_PlaneInfo_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_PlaneInfo_annotationLinks_by_owner (PlaneInfo_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM PlaneInfoAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Reagent_screenLinks_by_owner;

  CREATE OR REPLACE VIEW count_Reagent_screenLinks_by_owner (Reagent_id, owner_id, count) AS select child, owner_id, count(*)
    FROM ScreenReagentLink GROUP BY child, owner_id ORDER BY child;

  DROP TABLE count_Reagent_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_Reagent_annotationLinks_by_owner (Reagent_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ReagentAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Dataset_projectLinks_by_owner;

  CREATE OR REPLACE VIEW count_Dataset_projectLinks_by_owner (Dataset_id, owner_id, count) AS select child, owner_id, count(*)
    FROM ProjectDatasetLink GROUP BY child, owner_id ORDER BY child;

  DROP TABLE count_Dataset_imageLinks_by_owner;

  CREATE OR REPLACE VIEW count_Dataset_imageLinks_by_owner (Dataset_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM DatasetImageLink GROUP BY parent, owner_id ORDER BY parent;

  DROP TABLE count_Dataset_annotationLinks_by_owner;

  CREATE OR REPLACE VIEW count_Dataset_annotationLinks_by_owner (Dataset_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM DatasetAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

--------------------------------------------------------
-- PATCH 5-->6
-- ===========
-- Drop all ScreenPlateWell related tables and recreate.
--

BEGIN;

    insert into dbpatch (currentVersion, currentPatch,   previousVersion,     previousPatch)
                 values ('OMERO3A',      11,             'OMERO3A',           5);

    drop sequence seq_plate;
    drop sequence seq_plateannotationlink;
    drop sequence seq_reagent;
    drop sequence seq_reagentannotationlink;
    drop sequence seq_screen;
    drop sequence seq_screenacquisition;
    drop sequence seq_screenacquisitionannotationlink;
    drop sequence seq_screenannotationlink;
    drop sequence seq_screenplatelink;
    drop sequence seq_screenreagentlink;
    drop sequence seq_well;
    drop sequence seq_wellannotationlink;
    drop sequence seq_wellsample;
    drop sequence seq_wellsampleannotationlink;
    drop sequence seq_wellsampleimagelink;

    create sequence seq_plate;
    create sequence seq_plateannotationlink;
    create sequence seq_reagent;
    create sequence seq_reagentannotationlink;
    create sequence seq_screen;
    create sequence seq_screenacquisition;
    create sequence seq_screenacquisitionannotationlink;
    create sequence seq_screenacquisitionwellsamplelink;
    create sequence seq_screenannotationlink;
    create sequence seq_screenplatelink;
    create sequence seq_well;
    create sequence seq_wellannotationlink;
    create sequence seq_wellreagentlink;
    create sequence seq_wellsample;
    create sequence seq_wellsampleannotationlink;

    drop view count_ScreenAcquisition_annotationLinks_by_owner;
    drop view count_Screen_annotationLinks_by_owner;
    drop view count_Screen_plateLinks_by_owner;
    drop view count_Screen_reagentLinks_by_owner;
    drop view count_Plate_annotationLinks_by_owner;
    drop view count_Plate_screenLinks_by_owner;
    drop view count_WellSample_annotationLinks_by_owner;
    drop view count_WellSample_imageLinks_by_owner;
    drop view count_Well_annotationLinks_by_owner;
    drop view count_Reagent_annotationLinks_by_owner;
    drop view count_Reagent_screenLinks_by_owner;
    drop view count_image_samplelinks_by_owner;

    drop table screenacquisitionannotationlink;
    drop table screenannotationlink;
    drop table screenplatelink;
    drop table screenreagentlink;
    drop table plateannotationlink;
    drop table wellannotationlink;
    drop table wellsampleannotationlink;
    drop table wellsampleimagelink;
    drop table reagentannotationlink;

    drop table wellsample;
    drop table screenacquisition;
    drop table screen;
    drop table well;
    drop table plate;
    drop table reagent;

    create table plate (
        id int8 not null,
        description text,
        permissions int8 not null,
        externalIdentifier varchar(255),
        name varchar(255) not null,
        status varchar(255),
        version int4,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        primary key (id)
    );

    create table plateannotationlink (
        id int8 not null,
        permissions int8 not null,
        version int4,
        child int8 not null,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        parent int8 not null,
        primary key (id)
    );

    create table reagent (
        id int8 not null,
        description text,
        permissions int8 not null,
        name varchar(255) not null,
        reagentIdentifier varchar(255),
        version int4,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        screen int8 not null,
        primary key (id)
    );

    create table reagentannotationlink (
        id int8 not null,
        permissions int8 not null,
        version int4,
        child int8 not null,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        parent int8 not null,
        primary key (id)
    );

    create table screen (
        id int8 not null,
        description text,
        permissions int8 not null,
        name varchar(255) not null,
        protocolDescription varchar(255),
        protocolIdentifier varchar(255),
        reagentSetDescription varchar(255),
        reagentSetIdentifier varchar(255),
        type varchar(255),
        version int4,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        primary key (id)
    );

    create table screenacquisition (
        id int8 not null,
        permissions int8 not null,
        endTime timestamp,
        startTime timestamp,
        version int4,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        screen int8 not null,
        primary key (id)
    );

    create table screenacquisitionannotationlink (
        id int8 not null,
        permissions int8 not null,
        version int4,
        child int8 not null,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        parent int8 not null,
        primary key (id)
    );

    create table screenacquisitionwellsamplelink (
        id int8 not null,
        permissions int8 not null,
        version int4,
        child int8 not null,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        parent int8 not null,
        primary key (id)
    );

    create table screenannotationlink (
        id int8 not null,
        permissions int8 not null,
        version int4,
        child int8 not null,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        parent int8 not null,
        primary key (id)
    );

    create table screenplatelink (
        id int8 not null,
        permissions int8 not null,
        version int4,
        child int8 not null,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        parent int8 not null,
        primary key (id)
    );

    create table well (
        id int8 not null,
        "column" int4,
        permissions int8 not null,
        externalDescription varchar(255),
        externalIdentifier varchar(255),
        row int4,
        type varchar(255),
        version int4,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        plate int8 not null,
        primary key (id)
    );

    create table wellannotationlink (
        id int8 not null,
        permissions int8 not null,
        version int4,
        child int8 not null,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        parent int8 not null,
        primary key (id)
    );

    create table wellreagentlink (
        id int8 not null,
        permissions int8 not null,
        version int4,
        child int8 not null,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        parent int8 not null,
        primary key (id)
    );

    create table wellsample (
        id int8 not null,
        permissions int8 not null,
        posX double precision,
        posY double precision,
        timepoint int4,
        version int4,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        image int8 not null,
        well int8 not null,
        primary key (id)
    );

    create table wellsampleannotationlink (
        id int8 not null,
        permissions int8 not null,
        version int4,
        child int8 not null,
        creation_id int8 not null,
        external_id int8 unique,
        group_id int8 not null,
        owner_id int8 not null,
        update_id int8 not null,
        parent int8 not null,
        primary key (id)
    );

    alter table plate 
        add constraint FKplate_update_id_event 
        foreign key (update_id) 
        references event;

    alter table plate 
        add constraint FKplate_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table plate 
        add constraint FKplate_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table plate 
        add constraint FKplate_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table plate 
        add constraint FKplate_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table plateannotationlink 
        add constraint FKplateannotationlink_child_annotation 
        foreign key (child) 
        references annotation;

    alter table plateannotationlink 
        add constraint FKplateannotationlink_update_id_event 
        foreign key (update_id) 
        references event;

    alter table plateannotationlink 
        add constraint FKplateannotationlink_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table plateannotationlink 
        add constraint FKplateannotationlink_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table plateannotationlink 
        add constraint FKplateannotationlink_parent_plate 
        foreign key (parent) 
        references plate;

    alter table plateannotationlink 
        add constraint FKplateannotationlink_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table plateannotationlink 
        add constraint FKplateannotationlink_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table reagent 
        add constraint FKreagent_update_id_event 
        foreign key (update_id) 
        references event;

    alter table reagent 
        add constraint FKreagent_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table reagent 
        add constraint FKreagent_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table reagent 
        add constraint FKreagent_screen_screen 
        foreign key (screen) 
        references screen;

    alter table reagent 
        add constraint FKreagent_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table reagent 
        add constraint FKreagent_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table reagentannotationlink 
        add constraint FKreagentannotationlink_child_annotation 
        foreign key (child) 
        references annotation;

    alter table reagentannotationlink 
        add constraint FKreagentannotationlink_update_id_event 
        foreign key (update_id) 
        references event;

    alter table reagentannotationlink 
        add constraint FKreagentannotationlink_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table reagentannotationlink 
        add constraint FKreagentannotationlink_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table reagentannotationlink 
        add constraint FKreagentannotationlink_parent_reagent 
        foreign key (parent) 
        references reagent;

    alter table reagentannotationlink 
        add constraint FKreagentannotationlink_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table reagentannotationlink 
        add constraint FKreagentannotationlink_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table screen 
        add constraint FKscreen_update_id_event 
        foreign key (update_id) 
        references event;

    alter table screen 
        add constraint FKscreen_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table screen 
        add constraint FKscreen_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table screen 
        add constraint FKscreen_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table screen 
        add constraint FKscreen_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table screenacquisition 
        add constraint FKscreenacquisition_update_id_event 
        foreign key (update_id) 
        references event;

    alter table screenacquisition 
        add constraint FKscreenacquisition_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table screenacquisition 
        add constraint FKscreenacquisition_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table screenacquisition 
        add constraint FKscreenacquisition_screen_screen 
        foreign key (screen) 
        references screen;

    alter table screenacquisition 
        add constraint FKscreenacquisition_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table screenacquisition 
        add constraint FKscreenacquisition_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table screenacquisitionannotationlink 
        add constraint FKscreenacquisitionannotationlink_child_annotation 
        foreign key (child) 
        references annotation;

    alter table screenacquisitionannotationlink 
        add constraint FKscreenacquisitionannotationlink_update_id_event 
        foreign key (update_id) 
        references event;

    alter table screenacquisitionannotationlink 
        add constraint FKscreenacquisitionannotationlink_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table screenacquisitionannotationlink 
        add constraint FKscreenacquisitionannotationlink_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table screenacquisitionannotationlink 
        add constraint FKscreenacquisitionannotationlink_parent_screenacquisition 
        foreign key (parent) 
        references screenacquisition;

    alter table screenacquisitionannotationlink 
        add constraint FKscreenacquisitionannotationlink_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table screenacquisitionannotationlink 
        add constraint FKscreenacquisitionannotationlink_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table screenacquisitionwellsamplelink 
        add constraint FKscreenacquisitionwellsamplelink_child_wellsample 
        foreign key (child) 
        references wellsample;

    alter table screenacquisitionwellsamplelink 
        add constraint FKscreenacquisitionwellsamplelink_update_id_event 
        foreign key (update_id) 
        references event;

    alter table screenacquisitionwellsamplelink 
        add constraint FKscreenacquisitionwellsamplelink_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table screenacquisitionwellsamplelink 
        add constraint FKscreenacquisitionwellsamplelink_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table screenacquisitionwellsamplelink 
        add constraint FKscreenacquisitionwellsamplelink_parent_screenacquisition 
        foreign key (parent) 
        references screenacquisition;

    alter table screenacquisitionwellsamplelink 
        add constraint FKscreenacquisitionwellsamplelink_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table screenacquisitionwellsamplelink 
        add constraint FKscreenacquisitionwellsamplelink_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table screenannotationlink 
        add constraint FKscreenannotationlink_child_annotation 
        foreign key (child) 
        references annotation;

    alter table screenannotationlink 
        add constraint FKscreenannotationlink_update_id_event 
        foreign key (update_id) 
        references event;

    alter table screenannotationlink 
        add constraint FKscreenannotationlink_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table screenannotationlink 
        add constraint FKscreenannotationlink_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table screenannotationlink 
        add constraint FKscreenannotationlink_parent_screen 
        foreign key (parent) 
        references screen;

    alter table screenannotationlink 
        add constraint FKscreenannotationlink_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table screenannotationlink 
        add constraint FKscreenannotationlink_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table screenplatelink 
        add constraint FKscreenplatelink_child_plate 
        foreign key (child) 
        references plate;

    alter table screenplatelink 
        add constraint FKscreenplatelink_update_id_event 
        foreign key (update_id) 
        references event;

    alter table screenplatelink 
        add constraint FKscreenplatelink_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table screenplatelink 
        add constraint FKscreenplatelink_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table screenplatelink 
        add constraint FKscreenplatelink_parent_screen 
        foreign key (parent) 
        references screen;

    alter table screenplatelink 
        add constraint FKscreenplatelink_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table screenplatelink 
        add constraint FKscreenplatelink_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table well 
        add constraint FKwell_update_id_event 
        foreign key (update_id) 
        references event;

    alter table well 
        add constraint FKwell_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table well 
        add constraint FKwell_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table well 
        add constraint FKwell_plate_plate 
        foreign key (plate) 
        references plate;

    alter table well 
        add constraint FKwell_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table well 
        add constraint FKwell_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table wellannotationlink 
        add constraint FKwellannotationlink_child_annotation 
        foreign key (child) 
        references annotation;

    alter table wellannotationlink 
        add constraint FKwellannotationlink_update_id_event 
        foreign key (update_id) 
        references event;

    alter table wellannotationlink 
        add constraint FKwellannotationlink_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table wellannotationlink 
        add constraint FKwellannotationlink_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table wellannotationlink 
        add constraint FKwellannotationlink_parent_well 
        foreign key (parent) 
        references well;

    alter table wellannotationlink 
        add constraint FKwellannotationlink_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table wellannotationlink 
        add constraint FKwellannotationlink_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table wellreagentlink 
        add constraint FKwellreagentlink_child_reagent 
        foreign key (child) 
        references reagent;

    alter table wellreagentlink 
        add constraint FKwellreagentlink_update_id_event 
        foreign key (update_id) 
        references event;

    alter table wellreagentlink 
        add constraint FKwellreagentlink_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table wellreagentlink 
        add constraint FKwellreagentlink_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table wellreagentlink 
        add constraint FKwellreagentlink_parent_well 
        foreign key (parent) 
        references well;

    alter table wellreagentlink 
        add constraint FKwellreagentlink_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table wellreagentlink 
        add constraint FKwellreagentlink_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table wellsample 
        add constraint FKwellsample_update_id_event 
        foreign key (update_id) 
        references event;

    alter table wellsample 
        add constraint FKwellsample_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table wellsample 
        add constraint FKwellsample_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table wellsample 
        add constraint FKwellsample_well_well 
        foreign key (well) 
        references well;

    alter table wellsample 
        add constraint FKwellsample_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table wellsample 
        add constraint FKwellsample_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

    alter table wellsample 
        add constraint FKwellsample_image_image 
        foreign key (image) 
        references image;

    alter table wellsampleannotationlink 
        add constraint FKwellsampleannotationlink_child_annotation 
        foreign key (child) 
        references annotation;

    alter table wellsampleannotationlink 
        add constraint FKwellsampleannotationlink_update_id_event 
        foreign key (update_id) 
        references event;

    alter table wellsampleannotationlink 
        add constraint FKwellsampleannotationlink_owner_id_experimenter 
        foreign key (owner_id) 
        references experimenter;

    alter table wellsampleannotationlink 
        add constraint FKwellsampleannotationlink_creation_id_event 
        foreign key (creation_id) 
        references event;

    alter table wellsampleannotationlink 
        add constraint FKwellsampleannotationlink_parent_wellsample 
        foreign key (parent) 
        references wellsample;

    alter table wellsampleannotationlink 
        add constraint FKwellsampleannotationlink_group_id_experimentergroup 
        foreign key (group_id) 
        references experimentergroup;

    alter table wellsampleannotationlink 
        add constraint FKwellsampleannotationlink_external_id_externalinfo 
        foreign key (external_id) 
        references externalinfo;

  CREATE OR REPLACE VIEW count_Plate_screenLinks_by_owner (Plate_id, owner_id, count) AS select child, owner_id, count(*)
    FROM ScreenPlateLink GROUP BY child, owner_id ORDER BY child;

  CREATE OR REPLACE VIEW count_Plate_annotationLinks_by_owner (Plate_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM PlateAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  CREATE OR REPLACE VIEW count_Screen_plateLinks_by_owner (Screen_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ScreenPlateLink GROUP BY parent, owner_id ORDER BY parent;

  CREATE OR REPLACE VIEW count_Screen_annotationLinks_by_owner (Screen_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ScreenAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  CREATE OR REPLACE VIEW count_ScreenAcquisition_annotationLinks_by_owner (ScreenAcquisition_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ScreenAcquisitionAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  CREATE OR REPLACE VIEW count_ScreenAcquisition_wellSampleLinks_by_owner (ScreenAcquisition_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ScreenAcquisitionWellSampleLink GROUP BY parent, owner_id ORDER BY parent;

  CREATE OR REPLACE VIEW count_WellSample_screenAcquisitionLinks_by_owner (WellSample_id, owner_id, count) AS select child, owner_id, count(*)
    FROM ScreenAcquisitionWellSampleLink GROUP BY child, owner_id ORDER BY child;

  CREATE OR REPLACE VIEW count_WellSample_annotationLinks_by_owner (WellSample_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM WellSampleAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  CREATE OR REPLACE VIEW count_Well_reagentLinks_by_owner (Well_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM WellReagentLink GROUP BY parent, owner_id ORDER BY parent;

  CREATE OR REPLACE VIEW count_Well_annotationLinks_by_owner (Well_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM WellAnnotationLink GROUP BY parent, owner_id ORDER BY parent;

  CREATE OR REPLACE VIEW count_Reagent_wellLinks_by_owner (Reagent_id, owner_id, count) AS select child, owner_id, count(*)
    FROM WellReagentLink GROUP BY child, owner_id ORDER BY child;

  CREATE OR REPLACE VIEW count_Reagent_annotationLinks_by_owner (Reagent_id, owner_id, count) AS select parent, owner_id, count(*)
    FROM ReagentAnnotationLink GROUP BY parent, owner_id ORDER BY parent;


--------------------------------------------------------
-- PATCH 6->7
-- ===========
-- Drop all ScreenPlateWell related tables and recreate.
--

     create table count_ExperimenterGroup_annotationLinks_by_owner (
         experimentergroup_id int8 not null,
         count int8 not null,
         owner_id int8,
         primary key (experimentergroup_id, owner_id)
     );

     create table count_Session_annotationLinks_by_owner (
         session_id int8 not null,
         count int8 not null,
         owner_id int8,
         primary key (session_id, owner_id)
     );

     create table experimentergroupannotationlink (
         id int8 not null,
         permissions int8 not null,
         "version" int4,
         child int8 not null,
         creation_id int8 not null,
         external_id int8 unique,
         group_id int8 not null,
         owner_id int8 not null,
         update_id int8 not null,
         parent int8 not null,
         primary key (id)
     );

     alter table session add column version int4;

     create table sessionannotationlink (
         id int8 not null,
         permissions int8 not null,
         version int4,
         child int8 not null,
         creation_id int8 not null,
         external_id int8 unique,
         group_id int8 not null,
         owner_id int8 not null,
         update_id int8 not null,
         parent int8 not null,
         primary key (id)
     );

     create table share (
         active bool not null,
         data bytea not null,
         itemCount int8 not null,
         session_id int8 not null,
         primary key (session_id)
     );

     alter table count_ExperimenterGroup_annotationLinks_by_owner 
         add constraint FK_count_to_ExperimenterGroup_annotationLinks 
         foreign key (experimentergroup_id) 
         references experimentergroup;

     alter table count_Session_annotationLinks_by_owner 
         add constraint FK_count_to_Session_annotationLinks 
         foreign key (session_id) 
         references session;

     alter table experimentergroupannotationlink 
         add constraint FKexperimentergroupannotationlink_child_annotation 
         foreign key (child) 
         references annotation;

     alter table experimentergroupannotationlink 
         add constraint FKexperimentergroupannotationlink_update_id_event 
         foreign key (update_id) 
         references event;

     alter table experimentergroupannotationlink 
         add constraint FKexperimentergroupannotationlink_owner_id_experimenter 
         foreign key (owner_id) 
         references experimenter;

     alter table experimentergroupannotationlink 
         add constraint FKexperimentergroupannotationlink_creation_id_event 
         foreign key (creation_id) 
         references event;

     alter table experimentergroupannotationlink 
         add constraint FKexperimentergroupannotationlink_parent_experimentergroup 
         foreign key (parent) 
         references experimentergroup;

     alter table experimentergroupannotationlink 
         add constraint FKexperimentergroupannotationlink_group_id_experimentergroup 
         foreign key (group_id) 
         references experimentergroup;

     alter table experimentergroupannotationlink 
         add constraint FKexperimentergroupannotationlink_external_id_externalinfo 
         foreign key (external_id) 
         references externalinfo;

     alter table sessionannotationlink 
         add constraint FKsessionannotationlink_child_annotation 
         foreign key (child) 
         references annotation;

     alter table sessionannotationlink 
         add constraint FKsessionannotationlink_update_id_event 
         foreign key (update_id) 
         references event;

     alter table sessionannotationlink 
         add constraint FKsessionannotationlink_owner_id_experimenter 
         foreign key (owner_id) 
         references experimenter;

     alter table sessionannotationlink 
         add constraint FKsessionannotationlink_creation_id_event 
         foreign key (creation_id) 
         references event;

     alter table sessionannotationlink 
         add constraint FKsessionannotationlink_parent_session 
         foreign key (parent) 
         references session;

     alter table sessionannotationlink 
         add constraint FKsessionannotationlink_group_id_experimentergroup 
         foreign key (group_id) 
         references experimentergroup;

     alter table sessionannotationlink 
         add constraint FKsessionannotationlink_external_id_externalinfo 
         foreign key (external_id) 
         references externalinfo;

     alter table share 
         add constraint FKshare_session_id_session 
         foreign key (session_id) 
         references session;

     create sequence seq_experimentergroupannotationlink;

     create sequence seq_sessionannotationlink;

     -- VIEWS

   DROP TABLE count_ExperimenterGroup_annotationLinks_by_owner;

   CREATE OR REPLACE 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;

   DROP TABLE count_Session_annotationLinks_by_owner;

   CREATE OR REPLACE 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;

--------------------------------------------------------
-- PATCH 8-->9
-- ===========
--
-- ticket:1047 Fixing a typo in UNIQUE index creation
-- which *substantially* hurt performance on indexed
-- collections
--

    CREATE OR REPLACE FUNCTION OMERO3A_11_fix_index(tblraw VARCHAR, col1raw VARCHAR, col2raw VARCHAR) RETURNS void AS '
    DECLARE
        keep INT8;    -- IObject.id of duplicate to keep
        sql  VARCHAR; -- Reusable container for SQL statements
        tbl  VARCHAR; -- Table name
        col1 VARCHAR; -- Name of first column
        col2 VARCHAR; -- Name of second column
        rec  RECORD;  -- Record value from dynamic sql
    BEGIN

        tbl := quote_ident(tblraw);
        col1:= quote_ident(col1raw);
        col2:= quote_ident(col2raw);
        sql :=
      ''   SELECT lhs.''||col1||'' as dup1, lhs.''||col2||''  as dup2 ''||
      ''     FROM ''||tbl||'' lhs, ''||tbl||''  rhs '' ||
      ''    WHERE lhs.id < rhs.id                   '' ||
      ''      AND lhs.''||col1||'' = rhs.''||col1||
      ''      AND lhs.''||col2||'' = rhs.''||col2||
      '' GROUP BY lhs.''||col1||'',  lhs.''||col2||'';'';

        FOR rec IN EXECUTE sql LOOP
        -----------------------------
        sql:=
      ''   SELECT id ''||
      ''     FROM ''||tbl||
      ''    WHERE ''||col1||'' = ''||rec.dup1||
      ''      AND ''||col2||'' = ''||rec.dup2||
      '' ORDER BY id DESC '' ||
      ''    LIMIT 1;'';

        EXECUTE sql INTO keep;

        sql:=
      ''   DELETE ''||
      ''     FROM ''||tbl||
      ''    WHERE ''||col1||'' = ''||rec.dup1||
      ''      AND ''||col2||'' = ''||rec.dup2||
      ''      AND ''||keep||'' != id;'';

        EXECUTE sql;

        END LOOP;
        RETURN;
     END;' LANGUAGE plpgsql;

  SELECT OMERO3A_11_fix_index('pixels','image','image_index');
  SELECT OMERO3A_11_fix_index('channel','pixels','pixels_index');
  SELECT OMERO3A_11_fix_index('channelbinding','renderingdef','renderingdef_index');
  SELECT OMERO3A_11_fix_index('codomainmapcontext','renderingdef','renderingdef_index');
  SELECT OMERO3A_11_fix_index('groupexperimentermap','child','child_index');

  ALTER TABLE pixels DROP CONSTRAINT pixels_id_key;
  ALTER TABLE pixels  ADD CONSTRAINT pixels_image_key UNIQUE (image, image_index);

  ALTER TABLE channel DROP CONSTRAINT channel_id_key;
  ALTER TABLE channel  ADD CONSTRAINT channel_pixels_key UNIQUE (pixels, pixels_index);

  ALTER TABLE channelbinding DROP CONSTRAINT channelbinding_id_key;
  ALTER TABLE channelbinding  ADD CONSTRAINT channelbinding_renderingDef_key UNIQUE (renderingdef, renderingdef_index);

  ALTER TABLE codomainmapcontext DROP CONSTRAINT codomainmapcontext_id_key;
  ALTER TABLE codomainmapcontext  ADD CONSTRAINT codomainmapcontext_renderingDef_key UNIQUE (renderingdef, renderingdef_index);

  ALTER TABLE groupexperimentermap DROP CONSTRAINT groupexperimentermap_id_key;
  ALTER TABLE groupexperimentermap  ADD CONSTRAINT groupexperimentermap_child_key UNIQUE (child, child_index);

--------------------------------------------------------
-- PATCH 9->10
-- ===========
--
-- ticket:1047 Adding UNIQUE (parent, child) constraints
-- on all link types
--

    select OMERO3A_11_fix_index('annotationannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('categorygroupcategorylink', 'parent', 'child');
    select OMERO3A_11_fix_index('categoryimagelink', 'parent', 'child');
    select OMERO3A_11_fix_index('channelannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('datasetannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('datasetimagelink', 'parent', 'child');
    select OMERO3A_11_fix_index('experimenterannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('experimentergroupannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('groupexperimentermap', 'parent', 'child');
    select OMERO3A_11_fix_index('imageannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('joboriginalfilelink', 'parent', 'child');
    select OMERO3A_11_fix_index('originalfileannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('pixelsannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('pixelsoriginalfilemap', 'parent', 'child');
    select OMERO3A_11_fix_index('planeinfoannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('plateannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('projectannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('projectdatasetlink', 'parent', 'child');
    select OMERO3A_11_fix_index('reagentannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('roilink', 'parent', 'child');
    select OMERO3A_11_fix_index('roilinkannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('screenacquisitionannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('screenacquisitionwellsamplelink', 'parent', 'child');
    select OMERO3A_11_fix_index('screenannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('screenplatelink', 'parent', 'child');
    select OMERO3A_11_fix_index('sessionannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('wellannotationlink', 'parent', 'child');
    select OMERO3A_11_fix_index('wellreagentlink', 'parent', 'child');
    select OMERO3A_11_fix_index('wellsampleannotationlink', 'parent', 'child');

    alter table annotationannotationlink add constraint annotationannotationlink_parent_key UNIQUE (parent, child);
    alter table categorygroupcategorylink add constraint categorygroupcategorylink_parent_key UNIQUE (parent, child);
    alter table categoryimagelink add constraint categoryimagelink_parent_key UNIQUE (parent, child);
    alter table channelannotationlink add constraint channelannotationlink_parent_key UNIQUE (parent, child);
    alter table datasetannotationlink add constraint datasetannotationlink_parent_key UNIQUE (parent, child);
    alter table datasetimagelink add constraint datasetimagelink_parent_key UNIQUE (parent, child);
    alter table experimenterannotationlink add constraint experimenterannotationlink_parent_key UNIQUE (parent, child);
    alter table experimentergroupannotationlink add constraint experimentergroupannotationlink_parent_key UNIQUE (parent, child);
    alter table groupexperimentermap add constraint groupexperimentermap_parent_key UNIQUE (parent, child);
    alter table imageannotationlink add constraint imageannotationlink_parent_key UNIQUE (parent, child);
    alter table joboriginalfilelink add constraint joboriginalfilelink_parent_key UNIQUE (parent, child);
    alter table originalfileannotationlink add constraint originalfileannotationlink_parent_key UNIQUE (parent, child);
    alter table pixelsannotationlink add constraint pixelsannotationlink_parent_key UNIQUE (parent, child);
    alter table pixelsoriginalfilemap add constraint pixelsoriginalfilemap_parent_key UNIQUE (parent, child);
    alter table planeinfoannotationlink add constraint planeinfoannotationlink_parent_key UNIQUE (parent, child);
    alter table plateannotationlink add constraint plateannotationlink_parent_key UNIQUE (parent, child);
    alter table projectannotationlink add constraint projectannotationlink_parent_key UNIQUE (parent, child);
    alter table projectdatasetlink add constraint projectdatasetlink_parent_key UNIQUE (parent, child);
    alter table reagentannotationlink add constraint reagentannotationlink_parent_key UNIQUE (parent, child);
    alter table roilink add constraint roilink_parent_key UNIQUE (parent, child);
    alter table roilinkannotationlink add constraint roilinkannotationlink_parent_key UNIQUE (parent, child);
    alter table screenacquisitionannotationlink add constraint screenacquisitionannotationlink_parent_key UNIQUE (parent, child);
    alter table screenacquisitionwellsamplelink add constraint screenacquisitionwellsamplelink_parent_key UNIQUE (parent, child);
    alter table screenannotationlink add constraint screenannotationlink_parent_key UNIQUE (parent, child);
    alter table screenplatelink add constraint screenplatelink_parent_key UNIQUE (parent, child);
    alter table sessionannotationlink add constraint sessionannotationlink_parent_key UNIQUE (parent, child);
    alter table wellannotationlink add constraint wellannotationlink_parent_key UNIQUE (parent, child);
    alter table wellreagentlink add constraint wellreagentlink_parent_key UNIQUE (parent, child);
    alter table wellsampleannotationlink add constraint wellsampleannotationlink_parent_key UNIQUE (parent, child);

--------------------------------------------------------
-- PATCH 10->11
-- ============
--
-- ticket:1056 fixing unique(ordered, ordered_index)
--

   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_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 codomainmapcontext
        WHERE renderingDef = new.renderingDef AND renderingDef_index = new.renderingDef_index
       OFFSET 0
        LIMIT 1;

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

       RETURN new;
     END;' LANGUAGE plpgsql;

   CREATE TRIGGER codomainmapcontext_renderingDef_index_trigger
         BEFORE UPDATE ON codomainmapcontext
         FOR EACH ROW EXECUTE PROCEDURE codomainmapcontext_renderingDef_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 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 ();

   -- Removing the fix_index function for cleanliness.
   DROP FUNCTION OMERO3A_11_fix_index(varchar, varchar, varchar);

   UPDATE dbpatch set message = 'Database updated.', finished = now()
    where currentVersion  = 'OMERO3A' and
          currentPatch    = 11         and
          previousVersion = 'OMERO3A' and
          previousPatch   = 5;

COMMIT;
