generate_location_title.sql

DELIMITER //

CREATE FUNCTION generateLocationTitle(_id INT)
  RETURNS TEXT
  READS SQL DATA
  BEGIN
    DECLARE _title VARCHAR(255) DEFAULT '';
    SELECT
      REPLACE(
        CONCAT(
          CONCAT_WS(', ',
            loc.building,
            loc.floor,
            loc.room,
            loc.area,
            loc.barcode,
            loc.classification
          ),
          IF(loc.coordinate_1_label IS NULL, '',
            CONCAT(
              ' [',
              CONCAT_WS(', ',
                CONCAT_WS(': ', loc.coordinate_1_label, loc.coordinate_1_indicator),
                CONCAT_WS(': ', loc.coordinate_2_label, loc.coordinate_2_indicator),
                CONCAT_WS(': ', loc.coordinate_3_label, loc.coordinate_3_indicator)
              ),
              ']'
            )
          )
        ),
        ', ]',
        ']'
      )
    INTO _title FROM location loc WHERE id = _id; 
    RETURN _title;
  END; //

DELIMITER ;

-- mysql> select title from location WHERE id = 1;
-- +----------------------------------------------------------------+
-- | title                                                          |
-- +----------------------------------------------------------------+
-- | Building, Floor, Room, Stacks [Range: A, Section: 1, Shelf: 1] |
-- +----------------------------------------------------------------+
-- 1 row in set (0.00 sec)

-- mysql> SELECT generateLocationTitle(1);
-- +----------------------------------------------------------------+
-- | generateLocationTitle(1)                                       |
-- +----------------------------------------------------------------+
-- | Building, Floor, Room, Stacks [Range: A, Section: 1, Shelf: 1] |
-- +----------------------------------------------------------------+
-- 1 row in set (0.00 sec)

-- mysql> SELECT title FROM location WHERE id = 599;
-- +---------------------------------------+
-- | title                                 |
-- +---------------------------------------+
-- | Rubenstein, 1st [Bay: 0015, Shelf: i] |
-- +---------------------------------------+
-- 1 row in set (0.00 sec)

-- mysql> SELECT generateLocationTitle(599);
-- +---------------------------------------+
-- | generateLocationTitle(599)            |
-- +---------------------------------------+
-- | Rubenstein, 1st [Bay: 0015, Shelf: i] |
-- +---------------------------------------+
-- 1 row in set (0.00 sec)


-- UPDATE location SET title = generateLocationTitle(id), system_mtime = NOW() WHERE ...;