MYSQL SLUG FIX (Unique fix)

README

MYSQL SLUG FIX

The table 'content' contains pages with a slug. This slug should be unique but it isn't.

unique_fix.sql fixes this problem and makes the slug-column unique.

schema.sql

CREATE TABLE  content (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  title VARCHAR( 255 ) NOT NULL ,
  slug VARCHAR( 64 ) NOT NULL ,
  content TEXT NOT NULL
);

unique_fix.sql

USE database_name;

DROP FUNCTION IF EXISTS UPDATE_SLUG;
DELIMITER |
CREATE FUNCTION UPDATE_SLUG(old_slug TEXT)
  RETURNS TEXT
    BEGIN
      DECLARE slug_maxlength INT DEFAULT 64;
      DECLARE cut_length INT DEFAULT 4;
      DECLARE new_slug TEXT;
      DECLARE count_duplicate INT;
      DECLARE slugcount INT;
      DECLARE slug_part_maxlength INT;
      DECLARE slug_part TEXT;
      DECLARE count_duplicate_2 INT;

      SELECT COUNT(*) INTO count_duplicate FROM content WHERE slug=old_slug;
      IF (count_duplicate > 1) THEN
        SET slugcount = count_duplicate - 1;
        SET slug_part_maxlength = slug_maxlength - cut_length;
        SET slug_part = SUBSTRING(old_slug, 1, slug_part_maxlength);
        SET new_slug = CONCAT(slug_part,'-', slugcount);
        SELECT COUNT(*) INTO count_duplicate_2 FROM content WHERE slug=new_slug LIMIT 1;
        WHILE count_duplicate_2 > 0 DO
          SET slugcount = slugcount + 1;
          SET new_slug = CONCAT(slug_part,'-', slugcount);
          SELECT COUNT(*) INTO count_duplicate_2 FROM content WHERE slug=new_slug LIMIT 1;
        END WHILE;

      ELSE
        SET new_slug = old_slug;
      END IF;
      RETURN new_slug;
  END |
DELIMITER ;

UPDATE content SET slug = UPDATE_SLUG(slug) ORDER BY id DESC;