db-schema/db_scripts/diff/15253.up

SET autocommit=0; #not only DDL stmts

USE accounting;

CREATE TABLE cdr_tag (
  id int(3) unsigned NOT NULL AUTO_INCREMENT,
  type ENUM(
    'calling_party_category'
  ) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY ctc_type_idx (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO cdr_tag SET id=null, type='calling_party_category';

#1 times the cardinality of accounting.cdr:
CREATE TABLE cdr_tag_data ( 
  cdr_id int(10) unsigned NOT NULL,
  provider_id int(3) unsigned NOT NULL,  
  direction_id int(3) unsigned NOT NULL,    
  tag_id int(3) unsigned NOT NULL,
  val TEXT NOT NULL,
  cdr_start_time decimal(13,3) NOT NULL, #partitioning key, but https://bugs.mysql.com/bug.php?id=72274
  PRIMARY KEY (cdr_id, provider_id, direction_id, tag_id, cdr_start_time)
  ## Partitioned tables using the InnoDB storage engine do not support foreign keys.
  #,FOREIGN KEY (cdr_id) REFERENCES cdr(id) ON DELETE CASCADE ON UPDATE CASCADE,
  #FOREIGN KEY (provider_id) REFERENCES cdr_provider(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  #FOREIGN KEY (direction_id) REFERENCES cdr_direction(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  #FOREIGN KEY (tag_id) REFERENCES cdr_tag(id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

COMMIT;

#non-transactional:
GRANT SELECT ON cdr_tag TO 'exporter'@'localhost';
GRANT SELECT ON cdr_tag_data TO 'exporter'@'localhost';

#update triggers:

-- cdr cascade update
DROP TRIGGER IF EXISTS cdr_cascade_update_trig;
DELIMITER ;;
CREATE TRIGGER cdr_cascade_update_trig AFTER UPDATE ON cdr
  FOR EACH ROW BEGIN

  UPDATE cdr_relation_data SET cdr_id = NEW.id WHERE cdr_id = OLD.id;
  UPDATE cdr_cash_balance_data SET cdr_id = NEW.id WHERE cdr_id = OLD.id;
  UPDATE cdr_time_balance_data SET cdr_id = NEW.id WHERE cdr_id = OLD.id;
  UPDATE cdr_tag_data SET cdr_id = NEW.id WHERE cdr_id = OLD.id;

  END;;
DELIMITER ;

-- cdr cascade delete
DROP TRIGGER IF EXISTS cdr_cascade_delete_trig;
DELIMITER ;;
CREATE TRIGGER cdr_cascade_delete_trig AFTER DELETE ON cdr
  FOR EACH ROW BEGIN

  DELETE FROM cdr_relation_data WHERE cdr_id = OLD.id;
  DELETE FROM cdr_cash_balance_data WHERE cdr_id = OLD.id;
  DELETE FROM cdr_time_balance_data WHERE cdr_id = OLD.id;
  DELETE FROM cdr_tag_data WHERE cdr_id = OLD.id;

  END;;
DELIMITER ;

-- cdr_tag cascade delete restriction
DROP TRIGGER IF EXISTS cdr_tag_rest_cascade_delete_trig;
DELIMITER ;;
CREATE TRIGGER cdr_tag_rest_cascade_delete_trig BEFORE DELETE ON cdr_tag
  FOR EACH ROW BEGIN

    SELECT count(tag_id) INTO @rel_count FROM cdr_tag_data WHERE tag_id = OLD.id LIMIT 1;

    IF @rel_count > 0 THEN
        SET @err_msg = 'Error deleting data from accounting.cdr_tag, related data exists in accounting.cdr_tag_data';
        SIGNAL sqlstate '45000' SET message_text = @err_msg;
    END IF;

  END;;
DELIMITER ;

-- cdr_provider cascade delete restriction
DROP TRIGGER IF EXISTS cdr_provider_rest_cascade_delete_trig;
DELIMITER ;;
CREATE TRIGGER cdr_provider_rest_cascade_delete_trig BEFORE DELETE ON cdr_provider
  FOR EACH ROW BEGIN

    SELECT count(provider_id) INTO @rel_prov_count
        FROM cdr_relation_data WHERE provider_id = OLD.id LIMIT 1;
    SELECT count(provider_id) INTO @cash_balance_prov_count
        FROM cdr_cash_balance_data WHERE provider_id = OLD.id LIMIT 1;
    SELECT count(provider_id) INTO @time_balance_prov_count
        FROM cdr_time_balance_data WHERE provider_id = OLD.id LIMIT 1;
    SELECT count(provider_id) INTO @tag_prov_count
        FROM cdr_tag_data WHERE provider_id = OLD.id LIMIT 1;        

    IF @rel_prov_count > 0 THEN
        SET @err_msg = 'Error deleting data from accounting.cdr_provider, related data exists in accounting.cdr_relation_data';
        SIGNAL sqlstate '45000' SET message_text = @err_msg;
    END IF;

    IF @cash_balance_prov_count > 0 THEN
        SET @err_msg = 'Error deleting data from accounting.cdr_provider, related data exists in accounting.cdr_cash_balance_data';
        SIGNAL sqlstate '45000' SET message_text = @err_msg;
    END IF;

    IF @time_balance_prov_count > 0 THEN
        SET @err_msg = 'Error deleting data from accounting.cdr_provider, related data exists in accounting.cdr_time_balance_data';
        SIGNAL sqlstate '45000' SET message_text = @err_msg;
    END IF;

    IF @tag_prov_count > 0 THEN
        SET @err_msg = 'Error deleting data from accounting.cdr_provider, related data exists in accounting.cdr_tag_data';
        SIGNAL sqlstate '45000' SET message_text = @err_msg;
    END IF;

  END;;
DELIMITER ;

-- cdr_direction cascade delete restriction
DROP TRIGGER IF EXISTS cdr_direction_rest_cascade_delete_trig;
DELIMITER ;;
CREATE TRIGGER cdr_direction_rest_cascade_delete_trig BEFORE DELETE ON cdr_direction
  FOR EACH ROW BEGIN

    SELECT count(direction_id) INTO @rel_dir_count
        FROM cdr_relation_data WHERE direction_id = OLD.id LIMIT 1;
    SELECT count(direction_id) INTO @cash_balance_dir_count
        FROM cdr_cash_balance_data WHERE direction_id = OLD.id LIMIT 1;
    SELECT count(direction_id) INTO @time_balance_dir_count
        FROM cdr_time_balance_data WHERE direction_id = OLD.id LIMIT 1;
    SELECT count(direction_id) INTO @tag_dir_count
        FROM cdr_tag_data WHERE direction_id = OLD.id LIMIT 1;

    IF @rel_dir_count > 0 THEN
        SET @err_msg = 'Error deleting data from accounting.cdr_direction, related data exists in accounting.cdr_relation_data';
        SIGNAL sqlstate '45000' SET message_text = @err_msg;
    END IF;

    IF @cash_balance_dir_count > 0 THEN
        SET @err_msg = 'Error deleting data from accounting.cdr_direction, related data exists in accounting.cdr_cash_balance_data';
        SIGNAL sqlstate '45000' SET message_text = @err_msg;
    END IF;

    IF @time_balance_dir_count > 0 THEN
        SET @err_msg = 'Error deleting data from accounting.cdr_direction, related data exists in accounting.cdr_time_balance_data';
        SIGNAL sqlstate '45000' SET message_text = @err_msg;
    END IF;

    IF @tag_dir_count > 0 THEN
        SET @err_msg = 'Error deleting data from accounting.cdr_direction, related data exists in accounting.cdr_tag_data';
        SIGNAL sqlstate '45000' SET message_text = @err_msg;
    END IF;

  END;;
DELIMITER ;