mirror of https://github.com/sipwise/db-schema.git
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
105 lines
3.8 KiB
105 lines
3.8 KiB
-- do not run systems between r4085 and r4091, but upgrade further
|
|
-- there is no clean upgrade path if data is changed between these revisions
|
|
|
|
USE billing;
|
|
|
|
CREATE TABLE `billing`.`billing_zones_history` (
|
|
`id` int(11) UNSIGNED NOT NULL auto_increment,
|
|
`bz_id` int(11) UNSIGNED,
|
|
`billing_profile_id` int(11) UNSIGNED NOT NULL,
|
|
`zone` varchar(127) NOT NULL,
|
|
`detail` varchar(127) NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `bzid_idx` (`bz_id`),
|
|
CONSTRAINT `b_z_h_bzid_ref` FOREIGN KEY (`bz_id`)
|
|
REFERENCES `billing_zones` (`id`)
|
|
ON DELETE SET NULL ON UPDATE NO ACTION
|
|
) ENGINE=InnoDB;
|
|
|
|
DELIMITER |
|
|
|
|
CREATE TRIGGER billing.bill_zones_crepl_trig AFTER INSERT ON billing_zones
|
|
FOR EACH ROW BEGIN
|
|
|
|
INSERT INTO billing_zones_history
|
|
VALUES(NULL, NEW.id, NEW.billing_profile_id, NEW.zone, NEW.detail);
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER billing.bill_zones_urepl_trig AFTER UPDATE ON billing_zones
|
|
FOR EACH ROW BEGIN
|
|
|
|
UPDATE billing_zones_history
|
|
SET bz_id = NEW.id, billing_profile_id = NEW.billing_profile_id,
|
|
zone = NEW.zone, detail = NEW.detail
|
|
WHERE bz_id = OLD.id;
|
|
|
|
END;
|
|
|
|
|
|
|
DELIMITER ;
|
|
|
|
INSERT INTO billing.billing_zones_history
|
|
SELECT billing_zones.id, billing_zones.* FROM billing.billing_zones;
|
|
|
|
GRANT SELECT ON billing.billing_zones_history TO 'exporter'@'localhost';
|
|
|
|
CREATE TABLE `billing`.`billing_fees_history` (
|
|
`id` int(11) UNSIGNED NOT NULL auto_increment,
|
|
`bf_id` int(11) UNSIGNED,
|
|
`billing_profile_id` int(11) UNSIGNED NOT NULL,
|
|
`billing_zone_id` int(11) UNSIGNED NULL,
|
|
`destination` varchar(255) NOT NULL,
|
|
`type` enum('call', 'sms') NOT NULL DEFAULT 'call',
|
|
`onpeak_init_rate` double NOT NULL DEFAULT 0,
|
|
`onpeak_init_interval` int(5) UNSIGNED NOT NULL DEFAULT 0,
|
|
`onpeak_follow_rate` double NOT NULL DEFAULT 0,
|
|
`onpeak_follow_interval` int(5) UNSIGNED NOT NULL DEFAULT 0,
|
|
`offpeak_init_rate` double NOT NULL DEFAULT 0,
|
|
`offpeak_init_interval` int(5) UNSIGNED NOT NULL DEFAULT 0,
|
|
`offpeak_follow_rate` double NOT NULL DEFAULT 0,
|
|
`offpeak_follow_interval` int(5) UNSIGNED NOT NULL DEFAULT 0,
|
|
`use_free_time` bool NOT NULL DEFAULT FALSE,
|
|
PRIMARY KEY (`id`),
|
|
KEY `bfid_idx` (`bf_id`),
|
|
CONSTRAINT `b_f_h_bfid_ref` FOREIGN KEY (`bf_id`)
|
|
REFERENCES `billing_fees` (`id`)
|
|
ON DELETE SET NULL ON UPDATE NO ACTION
|
|
) ENGINE=InnoDB;
|
|
|
|
DELIMITER |
|
|
|
|
CREATE TRIGGER billing.bill_fees_crepl_trig AFTER INSERT ON billing_fees
|
|
FOR EACH ROW BEGIN
|
|
|
|
INSERT INTO billing_fees_history
|
|
VALUES(NULL, NEW.id, NEW.billing_profile_id, NEW.billing_zone_id, NEW.destination,
|
|
NEW.type, NEW.onpeak_init_rate, NEW.onpeak_init_interval, NEW.onpeak_follow_rate,
|
|
NEW.onpeak_follow_interval, NEW.offpeak_init_rate, NEW.offpeak_init_interval,
|
|
NEW.offpeak_follow_rate, NEW.offpeak_follow_interval, NEW.use_free_time);
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER billing.bill_fees_urepl_trig AFTER UPDATE ON billing_fees
|
|
FOR EACH ROW BEGIN
|
|
|
|
UPDATE billing_fees_history
|
|
SET bf_id = NEW.id, billing_profile_id = NEW.billing_profile_id,
|
|
billing_zone_id = NEW.billing_zone_id, destination = NEW.destination, type = NEW.type,
|
|
onpeak_init_rate = NEW.onpeak_init_rate, onpeak_init_interval = NEW.onpeak_init_interval,
|
|
onpeak_follow_rate = NEW.onpeak_follow_rate, onpeak_follow_interval = NEW.onpeak_follow_interval,
|
|
offpeak_init_rate = NEW.offpeak_init_rate, offpeak_init_interval = NEW.offpeak_init_interval,
|
|
offpeak_follow_rate = NEW.offpeak_follow_rate, offpeak_follow_interval = NEW.offpeak_follow_interval,
|
|
use_free_time = NEW.use_free_time
|
|
WHERE bf_id = OLD.id;
|
|
|
|
END;
|
|
|
|
|
|
|
DELIMITER ;
|
|
|
|
INSERT INTO billing.billing_fees_history
|
|
SELECT billing_fees.id, billing_fees.* FROM billing.billing_fees;
|