Free Mind Systems

This post is intended to be an introduction and a real example of how triggers can be a powerfull tool for programers and integrators, with a real solution for a real problem (manage tags without changing the source code of an app).

If you has worked with Oracle likely to have used triggers and pl/sql, but in Mysql this feature available from version 5 (1) isn’t widely use, why?

When you are programing or integrating Mysql backed applications, you always have to keep it in mind; Triggers are your friends. Many times you have external applications to manage that source code can’t be touched (not own, hard to maintain if patched, different technologies, closed source, …) or simply you need integrated data between apps and don’t have api’s, webservices or when performance is a big deal.

Triggers are not sustitute to large developments, to much sql code can be a hell in order to have a well mantained/structured code and can difficult your testing strategy.

Problem:
We need manage tags in an application that we can’t touch the source code.

Solution:
We will use an existing text field in a form and transparently convert it in a tag field.

How:
Without change the code (at database level), we will split the comma separated text field, insert it in a tags table and link the tag to the record.

Background
Current example table, we are going to extend the field `other`as tag field, we name it products:

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `price` int(20) NOT NULL DEFAULT '0',
  `other` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `products_name_idx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Tags Management tables: freetags and freetagged_objects:

CREATE TABLE `freetags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `freetagged_objects` (
  `tag_id` int(20) NOT NULL DEFAULT '0',
  `object_id` int(20) NOT NULL DEFAULT '0',
  `tagged_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `module` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`tag_id`, `object_id`),
  KEY `freetagged_objects_tag_id_object_id_idx` (`tag_id`, `object_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now we will create two triggers (2), one for insert and other for updates, they will split the coma separated words in the `other` field on product table and manage the tags tables (insert the tag on the freetags table if not already, and link the product id to the tag on the freetagged_objects). You can use `module` field to have tags for differents tables/models.

INSERT_PRODUCTS_TAGS

DELIMITER ||
DROP TRIGGER IF EXISTS insert_products_tags;
||
DELIMITER @@
CREATE TRIGGER insert_products_tags AFTER INSERT ON products
  FOR EACH ROW
  BEGIN
    DECLARE current_id integer;
    DECLARE tag_id integer;
    DECLARE next integer;
    DECLARE tag_field varchar(255);
    DECLARE next_sep integer;
    DECLARE current_tag varchar(255);
    DECLARE right_tag varchar(255);

    -- We use the field other as comma-separated tag_field
    SET tag_field = NEW.other;

    -- Check for empty tags
    IF (CHAR_LENGTH(tag_field) <> 0) THEN
        -- Loop until no more ocurrencies
       set next = 1;
       WHILE next = 1 DO
         -- Find possition of the next ","
         SELECT INSTR(tag_field, ',') INTO next_sep;
         IF (next_sep > 0) THEN
            SELECT SUBSTR(tag_field, 1, next_sep - 1) INTO current_tag;
            SELECT SUBSTR(tag_field, next_sep + 1, CHAR_LENGTH(tag_field)) INTO right_tag;
            set tag_field = right_tag;
         ELSE
           set next = 0;
           set current_tag = tag_field;
         END IF;

         -- Drop spaces between comas
         SELECT TRIM(current_tag) INTO current_tag;

         -- Insert the tag if not already present
         IF (NOT EXISTS (SELECT tag FROM freetags WHERE tag = current_tag)) THEN
           -- Insert the tag
           INSERT INTO freetags (tag) values (current_tag);
           SELECT LAST_INSERT_ID() INTO tag_id;
         ELSE
           -- Or get the id
           SELECT id FROM freetags WHERE tag = current_tag INTO tag_id;
         END IF;

         -- Link the object tagged with the tag
         INSERT INTO freetagged_objects
           (tag_id, object_id, module)
            values
           (tag_id, NEW.id, 'products');
       END WHILE;
    END IF;
  END;
@@

Now If you execute an insert on products table:

INSERT INTO PRODUCTS
    (name, price, other)
    values
    ("product1", 2, "tag1, tag2,tag3 , tag 4");

You will see the tags inserted and linked on the generated product id

Now we need to ensure that if you update the “product1″ record and you modified the field `other` the tags gets updated. When programing I was thinking if review the new tags one by one, or delete all the links and parse again the tag field. Finally for eficiency and simplicity decided delete and regenerate.

UPDATE_PRODUCTS_TAGS

DELIMITER ||
DROP TRIGGER IF EXISTS update_products_tags;
||
DELIMITER @@
CREATE TRIGGER update_products_tags BEFORE UPDATE ON products
  FOR EACH ROW
  BEGIN
    DECLARE current_id integer;
    DECLARE tag_id integer;
    DECLARE next integer;
    DECLARE tag_field varchar(255);
    DECLARE next_sep integer;
    DECLARE current_tag varchar(255);
    DECLARE right_tag varchar(255);

    -- We use the field other as comma-separated tag_field
    SET tag_field = NEW.other;

    -- Only act if the field changes
    IF (tag_field <> OLD.other) THEN
      -- At the moment we regenerate the tags (not compare), needs some thinking because compare can be
      -- more performance killer
      DELETE FROM freetagged_objects WHERE object_id = OLD.id AND module = 'products';
      -- Insert again if not empty
      IF (CHAR_LENGTH(tag_field) <> 0) THEN
          -- Loop until no more ocurrencies
         set next = 1;
         WHILE next = 1 DO
           -- Find possition of the next ","
           SELECT INSTR(tag_field, ',') INTO next_sep;
           IF (next_sep > 0) THEN
              SELECT SUBSTR(tag_field, 1, next_sep - 1) INTO current_tag;
              SELECT SUBSTR(tag_field, next_sep + 1, CHAR_LENGTH(tag_field)) INTO right_tag;
              set tag_field = right_tag;
           ELSE
             set next = 0;
             set current_tag = tag_field;
           END IF;

           -- Drop spaces between comas
           SELECT TRIM(current_tag) INTO current_tag;

           -- Insert the tag if not already present
           IF (NOT EXISTS (SELECT tag FROM freetags WHERE tag = current_tag)) THEN
             -- Insert the tag
             INSERT INTO freetags (tag) values (current_tag);
             SELECT LAST_INSERT_ID() INTO tag_id;
           ELSE
             -- Or get the id
             SELECT id FROM freetags WHERE tag = current_tag INTO tag_id;
           END IF;

           -- Link the object tagged with the tag
           INSERT INTO freetagged_objects
             (tag_id, object_id, module)
              values
             (tag_id, NEW.id, 'products');
         END WHILE;
      END IF;
    END IF;
  END;
@@

This example has many common uses and shows a fairly wide variety of SQL syntax. I recommended Mysql 5.1 for triggers and stored procedures development, more info on Mysql Website.

1) Support for triggers is included beginning with MySQL 5.0.2.

2) For trigger development is really helpfull some database IDE development like (TOAD or SQL/Developer for Oracle). For Mysql you can use two exceptional tools: Tora (Open Source TOAD like clone for Oracle an Mysql) and Mysql Query Browser (Open Source by Mysql).