A new trigger is created which calculates 'zone_id' based on 'country_id' at insertion.
It also keeps the possibility to insert directly at a given 'zone_id' if entry is empty.
The auto_increment on table doesnt need to be removed.
Tested ok for me on 1.2.8
Permits to each language translator to manage it's country zones in the reserved interval without needing to shift all the following zones each time an addendum is done. It keeps zones sequentially grouped per country.
Code: Select all
ALTER TABLE `zone` ADD COLUMN `new_zid` int(11) NOT NULL;
ALTER TABLE `zone` ADD COLUMN `old_zid` int(11) NOT NULL;
UPDATE `zone` SET old_zid = zone_id;
SET @row_num := 0;
SET @prev_value := 0;
UPDATE `zone` t, (SELECT `zone_id`, `country_id` * 1000 + @row_num := IF (@prev_value = `country_id`, @row_num + 1, 0) AS RowNumber, @prev_value := `country_id`
FROM `zone`
ORDER BY `country_id`,`zone_id` ASC) r
SET t.new_zid = r.RowNumber
WHERE t.`zone_id` = r.`zone_id`;
DELIMITER $$
DROP PROCEDURE IF EXISTS modify_zid $$
CREATE PROCEDURE modify_zid()
BEGIN
DECLARE found INT DEFAULT TRUE;
DECLARE cur_table_name CHAR(255);
DECLARE cur_column_name CHAR(255);
DECLARE curs_zid CURSOR FOR
SELECT DISTINCT table_name, column_name FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name != 'zone' AND column_name LIKE '%zone_id%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET found = FALSE;
OPEN curs_zid;
FETCH curs_zid INTO cur_table_name, cur_column_name;
WHILE (found) DO
SET @sql = CONCAT('UPDATE `', cur_table_name, '` r SET r.', cur_column_name, ' = - (SELECT `new_zid` FROM `zone` t WHERE t.old_zid = r.', cur_column_name,') WHERE r.', cur_column_name, ' IS NOT NULL AND r.', cur_column_name, ' != 0');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SET @sql = CONCAT('UPDATE `', cur_table_name, '` r SET r.', cur_column_name, ' = - r.', cur_column_name, ' WHERE r.', cur_column_name, ' IS NOT NULL AND r.', cur_column_name, ' < 0');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
FETCH curs_zid INTO cur_table_name, cur_column_name;
END WHILE;
CLOSE curs_zid;
END $$
CREATE TRIGGER trg_bi_zones BEFORE INSERT ON `zone`
FOR EACH ROW
BEGIN
IF (NEW.zone_id = 0 OR NEW.zone_id IS NULL) THEN
SET NEW.zone_id = (SELECT IFNULL(MAX(zone_id) + 1, NEW.country_id * 1000) FROM `zone` WHERE zone_id >= (NEW.country_id * 1000) AND zone_id < ((NEW.country_id + 1) * 1000));
END IF;
END $$
DELIMITER ;
CALL modify_zid();
DROP PROCEDURE IF EXISTS modify_zid;
UPDATE `zone` SET zone_id = - new_zid;
UPDATE `zone` SET zone_id = - zone_id;
ALTER TABLE `zone` DROP COLUMN `new_zid`;
ALTER TABLE `zone` DROP COLUMN `old_zid`;