Script reserving 1000 sequential entries per country in zone

Community contributions and modifications for AlegroCart
Post Reply
gob33
Posts: 107
Joined: Wed Apr 07, 2010 3:10 pm

Script reserving 1000 sequential entries per country in zone

Post by gob33 » Sat Dec 06, 2014 7:49 am

This describes an update script for transforming table "zones" by re-numbering 'zone_id' in intervals of 1000 entries per countries. ID changes are reported in all other tables.
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`;

Post Reply