 /*!50003 SET character_set_results = utf8 */ ;
 /*!50003 SET collation_connection  = utf8_general_ci */ ;
 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
-/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES' */ ;
+/*!50003 SET sql_mode              = '' */ ;
 DELIMITER ;;
 CREATE FUNCTION `root_replace`(p_str varchar(255), p_oldroot varchar(255), p_newroot varchar(255)) RETURNS varchar(255) CHARSET latin1
+    COMMENT 'Returns a varchar where the old root p_oldroot (the leftmost part) of p_str has been replaced with a new root p_newroot'
 BEGIN
  DECLARE path_len smallint unsigned DEFAULT LENGTH(p_oldroot);
  RETURN CASE WHEN LEFT(p_str, path_len) = BINARY p_oldroot THEN CONCAT(p_newroot, SUBSTRING(p_str, path_len + 1)) ELSE p_str END; 

        WHERE 
          c.sessionId = row_session_id;


        UPDATE BLSampleImageAnalysis blsia
          INNER JOIN BLSampleImage blsi on blsia.blSampleImageId = blsi.blSampleImageId
          INNER JOIN BLSample bls on blsi.blsampleId = bls.blsampleId

     p_fileType varchar(45)
	)
    MODIFIES SQL DATA
    COMMENT 'Inserts or updates info about a file attachmet for a data collection. Returns: The PK value in p_id.'
BEGIN
	IF p_id IS NOT NULL OR p_dataCollectionId IS NOT NULL THEN

      INSERT INTO DataCollectionFileAttachment (dataCollectionFileAttachmentId, dataCollectionId, fileFullPath, fileType) 
        VALUES (p_id, p_dataCollectionId, p_fileFullPath, p_fileType)
	    ON DUPLICATE KEY UPDATE
		  dataCollectionId = IFNULL(p_dataCollectionId, dataCollectionId),
          fileFullPath = IFNULL(p_fileFullPath, fileFullPath),
          fileType = IFNULL(p_fileType, fileType);

	  IF p_id IS NULL THEN 
		  SET p_id = LAST_INSERT_ID();
      END IF;
    ELSE
      SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=1644, MESSAGE_TEXT='Mandatory argument is NULL: p_id OR p_dataCollectionId must be non-NULL.';
    END IF;      
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
