psql-footer.vm

--
-- Copyright 2006-2016 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
--
#foreach($type in $types)
#foreach($prop in $type.classProperties)
#if( $prop.foreignKey && $prop.ordered && ! $prop.one2Many )
#set( $tbl_name = $prop.actualType.table )
#set( $col_name = $prop.name )
  CREATE OR REPLACE FUNCTION ${tbl_name}_${col_name}_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.${col_name} = old.${col_name} AND new.${col_name}_index = old.${col_name}_index THEN
          RETURN new;
      END IF;

      -- At most, there should be one duplicate
      SELECT id INTO duplicate
        FROM ${tbl_name}
       WHERE ${col_name} = new.${col_name} AND ${col_name}_index = new.${col_name}_index
      OFFSET 0
       LIMIT 1;

      IF duplicate IS NOT NULL THEN
          RAISE NOTICE ''Remapping ${tbl_name} %% via (-1 - oldvalue )'', duplicate;
          UPDATE ${tbl_name} SET ${col_name}_index = -1 - ${col_name}_index WHERE id = duplicate;
      END IF;

      RETURN new;
    END;' LANGUAGE plpgsql;

  CREATE TRIGGER ${tbl_name}_${col_name}_index_trigger
        BEFORE UPDATE ON ${tbl_name}
        FOR EACH ROW EXECUTE PROCEDURE ${tbl_name}_${col_name}_index_move ();

#end
#end
#end

--
-- Indexes
--
#foreach($type in $types)
#if(!$type.superclass && !$type.global)
#set( $tbl_name = $type.tableName() )
  CREATE INDEX i_${tbl_name}_owner ON $tbl_name(owner_id);
  CREATE INDEX i_${tbl_name}_group ON $tbl_name(group_id);
#end
#foreach($prop in $type.classProperties)
#if( $prop.foreignKey && ! $prop.name.equals("details")  && ! $prop.one2Many )
#set( $tbl_name = $prop.actualType.tableName() )
#set( $col_name = $prop.actualType.columnName($prop, '"') )
  CREATE INDEX $type.indexName($prop) ON $tbl_name($col_name);
#end
#end
#end

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;

      BEGIN
        PERFORM pg_advisory_lock(1, Lid);
      EXCEPTION
        WHEN undefined_function THEN
          RAISE DEBUG ''No function pg_advisory_lock'';
      END;
      PERFORM nextval(seq) FROM generate_series(1, increment);
      SELECT currval(seq) INTO nv;
      BEGIN
        PERFORM pg_advisory_unlock(1, Lid);
      EXCEPTION
        WHEN undefined_function THEN
          RAISE DEBUG ''No function pg_advisory_unlock'';
      END;

      RETURN nv;

END;' LANGUAGE plpgsql;

#foreach($type in $types)
#if(!$type.superclass)
#set($table = ${type.table})
CREATE SEQUENCE seq_${table}; INSERT INTO _lock_ids (name, id) SELECT 'seq_${table}', nextval('_lock_seq');
#end
#end


--
-- 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();
 
#foreach($type in $types)
#if( ${type.annotated} && !${type.superclass})
        FOR pid IN SELECT DISTINCT parent FROM ${type.tableName()}annotationlink WHERE child = new.id
        LOOP
            INSERT INTO _reindexing_required (event_id, entity_type, entity_id)
                SELECT eid, '${type.id}', pid
                WHERE NOT EXISTS (SELECT 1 FROM _reindexing_required AS ua
                    WHERE ua.event_id = eid AND ua.entity_type = '${type.id}' AND ua.entity_id = pid);
        END LOOP;

#end
#end
        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;

#foreach($type in $types)
#if( ${type.annotated} && !${type.superclass})
CREATE TRIGGER ${type.tableName()}_annotation_link_update_trigger
        AFTER UPDATE ON ${type.tableName()}annotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_update_trigger('${type.id}');
CREATE TRIGGER ${type.tableName()}_annotation_link_insert_trigger
        AFTER INSERT ON ${type.tableName()}annotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_insert_trigger('${type.id}');
#end
#end

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

#foreach($type in $types)
#if( ${type.annotated} && !${type.superclass})
CREATE TRIGGER ${type.tableName()}_annotation_link_delete_trigger
        BEFORE DELETE ON ${type.tableName()}annotationlink
        FOR EACH ROW
        EXECUTE PROCEDURE annotation_link_delete_trigger('${type.id}');
#end
#end


-- 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
--

#foreach($type in $types)
#foreach($prop in $type.classProperties)
#if( $prop.class.name == "ome.dsl.MapField" )
CREATE FUNCTION ${type.tableName()}_${prop.name}_map_entry_delete_trigger_function() RETURNS "trigger" AS '
BEGIN
    DELETE FROM ${type.tableName()}_${prop.name}
#if( $type.superclass && !$type.discriminator )
        WHERE ${type.tableName()}_id = OLD.${type.typeToColumn($type.superclass)}_id;
#else
        WHERE ${type.tableName()}_id = OLD.id;
#end
    RETURN OLD;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER ${type.tableName()}_${prop.name}_map_entry_delete_trigger
    BEFORE DELETE ON ${type.tableName()}
    FOR EACH ROW
    EXECUTE PROCEDURE ${type.tableName()}_${prop.name}_map_entry_delete_trigger_function();

#end
#end
#end
--
-- 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 ('@DBVERSION@',  @DBPATCH@,    '@DBVERSION@',   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;

##insert into event (id,version,name,e) select next, 0 as version, 'Bootstrap'
##  as name, next from (select ome_nextval('seq_event') as next) as nv;

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

#foreach($enum in $types)
#foreach($prop in $enum.properties)
#set($table = ${enum.typeToColumn($enum.id)} )
#if($prop.class.name == "ome.dsl.EntryField")
insert into $table (id,permissions,value)
    select ome_nextval('seq_$table'),-52,'$prop.name';
#end
#end
#end

-- 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 add column repo 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);
create unique index originalfile_repo_path_index on originalfile (repo, 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);

#foreach($type in $types)
#foreach($prop in $type.classProperties)
#if( $prop.class.name == "ome.dsl.MapField" )
CREATE INDEX ${type.tableName()}_${prop.name}_name ON ${type.tableName()}_${prop.name}(name);
CREATE INDEX ${type.tableName()}_${prop.name}_value ON ${type.tableName()}_${prop.name}(value);
#end
#end
#end

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

--
-- 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 $$
    BEGIN
        IF OLD.child = 0 THEN
            IF OLD.parent = 0 THEN
                RAISE EXCEPTION 'cannot remove system group membership for root';
            ELSIF OLD.parent = 1 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 $$
    BEGIN
        IF OLD.child != NEW.child OR OLD.parent != NEW.parent THEN
            IF OLD.child = 0 THEN
                IF OLD.parent = 0 THEN
                    RAISE EXCEPTION 'cannot remove system group membership for root';
                ELSIF OLD.parent = 1 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 $$
    BEGIN
        IF OLD.omename != NEW.omename THEN
            IF OLD.id = 0 THEN
                RAISE EXCEPTION 'cannot rename root experimenter';
            ELSIF OLD.id = 1 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 $$
    BEGIN
        IF OLD.name != NEW.name THEN
            IF OLD.id = 0 THEN
                RAISE EXCEPTION 'cannot rename system experimenter group';
            ELSIF OLD.id = 1 THEN
                RAISE EXCEPTION 'cannot rename user experimenter group';
            ELSIF OLD.id = 2 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();

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

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 COLUMN excitationwave TYPE positive_float;

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

ALTER TABLE transmittancerange
    ALTER COLUMN cutin TYPE positive_float,
    ALTER COLUMN cutintolerance TYPE nonnegative_float,
    ALTER COLUMN cutout TYPE positive_float,
    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.

#foreach($type in $types)
#foreach($prop in $type.classProperties)
#if($prop.foreignKey && $prop.type.startsWith("ome.model.units."))
ALTER TABLE ${prop.actualType.table} ADD CONSTRAINT ${prop.name}_unitpair
    CHECK (${prop.name} IS NULL AND ${prop.name}Unit IS NULL
        OR ${prop.name} IS NOT NULL AND ${prop.name}Unit IS NOT NULL);

#end
#end
#end

-- Temporary workaround for the width of map types

#foreach($type in $types)
#foreach($prop in $type.classProperties)
#if( $prop.class.name == "ome.dsl.MapField" )
ALTER TABLE ${type.tableName()}_${prop.name} ALTER COLUMN name TYPE TEXT;
ALTER TABLE ${type.tableName()}_${prop.name} ALTER COLUMN value TYPE TEXT;
#end
#end
#end

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

-- Here we have finished initializing this database.
update dbpatch set message = 'Database ready.', finished = clock_timestamp()
  where currentVersion = '@DBVERSION@' and
        currentPatch = @DBPATCH@ and
        previousVersion = '@DBVERSION@' and
        previousPatch = 0;

COMMIT;