mysql functions for bamboo

bamboo.sql

DELIMITER //
CREATE FUNCTION BAMBOO_DURATION(BUILD_DATE DATETIME, STATUS VARCHAR(255), DURATION BIGINT(20))
  RETURNS INT
  BEGIN
  DECLARE RV INT DEFAULT 0;
  if status = 'Building' then
#     now - build_date
    set RV = NOW() - BUILD_DATE;
  ELSE
    set RV = DURATION/1000;
  END IF;
  RETURN RV;
END;
//

CREATE FUNCTION BAMBOO_STATUS(status VARCHAR(255))
  RETURNS INT
  BEGIN
  DECLARE RV INT DEFAULT -1;
  if status = 'Successful' then
    set RV = 0;
  ELSEIF status = 'Failed' then
    set RV = 1;
  ELSEIF status = 'Building' then
    set RV = 2;
  END IF;
  RETURN RV;
END;
//

CREATE FUNCTION BAMBOO_STATUS_TEXT(BUILD_STATE VARCHAR(255), LIFE_CYCLE_STATE VARCHAR(255))
  RETURNS VARCHAR(255)
  BEGIN
  DECLARE RV VARCHAR(255) DEFAULT '';
  IF BUILD_STATE = 'Successful' THEN
    set RV = 'Successful';
  ELSEIF BUILD_STATE = 'Failed' THEN
    set RV = 'Failed';
  ELSEIF BUILD_STATE = 'Unknown' THEN
    IF LIFE_CYCLE_STATE = 'InProgress' THEN
      set RV = 'Building';
    ELSEIF LIFE_CYCLE_STATE = 'NotBuilt' THEN
      set RV = 'Cancelled';
    ELSEIF LIFE_CYCLE_STATE = 'Pending' THEN
      set RV = 'Pending';
    ELSEIF LIFE_CYCLE_STATE = 'Queued' THEN
      set RV = 'Queued';
    END IF;
  END IF;
  RETURN RV;
END;

GRANT EXECUTE ON function bamboo.BAMBOO_DURATION TO 'devops'@'%'; flush privileges;//


SELECT B.TITLE AS PLAN_TITLE, 
t1.BUILD_KEY, 
t1.BUILD_NUMBER,
BAMBOO_STATUS_TEXT(t1.BUILD_STATE, t1.LIFE_CYCLE_STATE) as status,
t1.BUILD_DATE, 
BAMBOO_DURATION(t1.BUILD_DATE, BAMBOO_STATUS_TEXT(t1.BUILD_STATE, t1.LIFE_CYCLE_STATE), t1.DURATION)/60 as duration,
BAMBOO_STATUS(t1.BUILD_STATE) as flag
FROM BUILDRESULTSUMMARY t1
LEFT JOIN BUILD AS B ON t1.BUILD_KEY = B.FULL_KEY
LEFT JOIN BUILD_DEFINITION AS BD ON B.BUILD_ID = BD.BUILD_ID
WHERE t1.BUILDRESULTSUMMARY_ID = (SELECT t2.BUILDRESULTSUMMARY_ID
                 FROM BUILDRESULTSUMMARY t2
                 WHERE t2.BUILD_KEY = t1.BUILD_KEY
                 ORDER BY t2.BUILDRESULTSUMMARY_ID DESC
                 LIMIT 1) and t1.BUILD_KEY in ('RDB-GMOCKL1', 'RDB-LOGGERL1', 'RDB-ROAD', 'RDB-L1RDBDEV', 'RDB-COMMLIBS', 'RDB-RDBAGENT', 'RDB-L1ROAD', 'RDB-L1RDBPROBAR', 'RDB-L1RDBSLAM',
                 'RDB-L1RDBSER', 'RDB-L1RDBLOC', 'RDB-RDBALGORITUMSAM', 'RDB-LRDBSAM', 'RDB-L1RDBSTOR', 'RDB-L1RDBUNUNDISTORT', 'RDB-L1RDBTOOL', 'RDB-L1RDBSERVERJAVA', 'RDB-L1RDBDB', 'RDB-L1RDBAR')
ORDER BY PLAN_TITLE ASC