-- Migration from 052 to 053\012\012-- Change code uniqueness check for samples of specific type.\012-- If sample_types.is_unique_code flag is set to 'true', codes of samples of the type will need to\012-- be unique no matter if they are contained in a container or not. Otherwise standard uniqueness \012-- check is performed (taking container connection into consideration).\012\012ALTER TABLE sample_types ADD COLUMN is_unique_code boolean_char NOT NULL DEFAULT false;\012\012CREATE OR REPLACE FUNCTION SAMPLE_CODE_UNIQUENESS_CHECK() RETURNS trigger AS $$\012DECLARE\012   counter  INTEGER;\012   unique_type_code  BOOLEAN_CHAR;\012BEGIN\012  LOCK TABLE samples IN EXCLUSIVE MODE;\012  \012  SELECT is_unique_code into unique_type_code FROM sample_types WHERE id = NEW.saty_id;\012  \012  IF (unique_type_code) THEN\012    IF (NEW.dbin_id is not NULL) THEN\012\011\011\011SELECT count(*) into counter FROM samples \012\011\011\011\011where id != NEW.id and code = NEW.code and saty_id = NEW.saty_id and dbin_id = NEW.dbin_id;\012\011\011\011IF (counter > 0) THEN\012\011\011\011\011RAISE EXCEPTION 'Insert/Update of Sample (Code: %) failed because database instance sample of the same type with the same code already exists.', NEW.code;\012\011\011\011END IF;\012\011\011ELSIF (NEW.grou_id is not NULL) THEN\012\011\011\011SELECT count(*) into counter FROM samples \012\011\011\011\011where id != NEW.id and code = NEW.code and saty_id = NEW.saty_id and grou_id = NEW.grou_id;\012\011\011\011IF (counter > 0) THEN\012\011\011\011\011RAISE EXCEPTION 'Insert/Update of Sample (Code: %) failed because space sample of the same type with the same code already exists.', NEW.code;\012\011\011\011END IF;\012\011\011END IF;\012  ELSE \011\012\011  IF (NEW.samp_id_part_of is NULL) THEN\012\011\011  IF (NEW.dbin_id is not NULL) THEN\012\011\011\011  SELECT count(*) into counter FROM samples \012\011\011      where id != NEW.id and code = NEW.code and samp_id_part_of is NULL and dbin_id = NEW.dbin_id;\012        IF (counter > 0) THEN\012\011\011\011\011  RAISE EXCEPTION 'Insert/Update of Sample (Code: %) failed because database instance sample with the same code already exists.', NEW.code;\012        END IF;\012\011\011  ELSIF (NEW.grou_id is not NULL) THEN\012\011\011\011  SELECT count(*) into counter FROM samples \012\011\011\011\011  where id != NEW.id and code = NEW.code and samp_id_part_of is NULL and grou_id = NEW.grou_id;\012\011\011\011  IF (counter > 0) THEN\012\011\011\011\011  RAISE EXCEPTION 'Insert/Update of Sample (Code: %) failed because space sample with the same code already exists.', NEW.code;\012\011\011\011  END IF;\012      END IF;\012    ELSE\012\011\011  IF (NEW.dbin_id is not NULL) THEN\012\011\011\011  SELECT count(*) into counter FROM samples \012\011\011\011\011  where id != NEW.id and code = NEW.code and samp_id_part_of = NEW.samp_id_part_of and dbin_id = NEW.dbin_id;\012\011\011\011  IF (counter > 0) THEN\012\011\011\011\011  RAISE EXCEPTION 'Insert/Update of Sample (Code: %) failed because database instance sample with the same code and being the part of the same container already exists.', NEW.code;\012\011\011\011  END IF;\012\011\011  ELSIF (NEW.grou_id is not NULL) THEN\012\011\011\011  SELECT count(*) into counter FROM samples \012\011\011\011\011  where id != NEW.id and code = NEW.code and samp_id_part_of = NEW.samp_id_part_of and grou_id = NEW.grou_id;\012\011\011\011  IF (counter > 0) THEN\012\011\011\011\011  RAISE EXCEPTION 'Insert/Update of Sample (Code: %) failed because space sample with the same code and being the part of the same container already exists.', NEW.code;\012\011\011\011  END IF;\012\011\011  END IF;\012     END IF;   \012  END IF;\012  \012  RETURN NEW;\012END;\012$$ LANGUAGE 'plpgsql';\012