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.
209 lines
10 KiB
209 lines
10 KiB
-- NOTE -- This restores data in kamailio.dialplan which has been deleted by 5800.up!
|
|
-- NOTE -- Run 5800.up first to create the data backup.
|
|
|
|
use provisioning;
|
|
|
|
DELETE FROM voip_rewrite_rules;
|
|
DELETE FROM voip_rewrite_rule_sets;
|
|
|
|
ALTER TABLE voip_rewrite_rule_sets ADD COLUMN `caller_in_dpid` int(11) unsigned DEFAULT NULL AFTER `description`;
|
|
ALTER TABLE voip_rewrite_rule_sets ADD COLUMN `callee_in_dpid` int(11) unsigned DEFAULT NULL AFTER `caller_in_dpid`;
|
|
ALTER TABLE voip_rewrite_rule_sets ADD COLUMN `caller_out_dpid` int(11) unsigned DEFAULT NULL AFTER `callee_in_dpid`;
|
|
ALTER TABLE voip_rewrite_rule_sets ADD COLUMN `callee_out_dpid` int(11) unsigned DEFAULT NULL AFTER `caller_out_dpid`;
|
|
|
|
CREATE TABLE `voip_rwrs_sequence` (
|
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB;
|
|
|
|
DELIMITER |
|
|
|
|
DROP TRIGGER voip_rwrules_crepl_trig|
|
|
|
|
CREATE TRIGGER voip_rwrules_crepl_trig AFTER INSERT ON voip_rewrite_rules
|
|
FOR EACH ROW BEGIN
|
|
|
|
DECLARE new_set_id int(11) unsigned;
|
|
|
|
SELECT IF(NEW.direction = 'in', IF(NEW.field = 'caller', caller_in_dpid, callee_in_dpid), IF(NEW.field = 'caller', caller_out_dpid, callee_out_dpid))
|
|
INTO new_set_id FROM voip_rewrite_rule_sets WHERE id = NEW.set_id;
|
|
|
|
INSERT INTO kamailio.dialplan (dpid,pr,match_op,match_exp,match_len,subst_exp,repl_exp,attrs)
|
|
VALUES(new_set_id,NEW.priority,1,NEW.match_pattern,0,NEW.match_pattern,NEW.replace_pattern,'');
|
|
|
|
END;
|
|
|
|
|
|
|
DROP TRIGGER voip_rwrules_urepl_trig|
|
|
|
|
CREATE TRIGGER voip_rwrules_urepl_trig AFTER UPDATE ON voip_rewrite_rules
|
|
FOR EACH ROW BEGIN
|
|
|
|
DECLARE old_set_id int(11) unsigned;
|
|
DECLARE new_set_id int(11) unsigned;
|
|
|
|
SELECT IF(OLD.direction = 'in', IF(OLD.field = 'caller', caller_in_dpid, callee_in_dpid), IF(OLD.field = 'caller', caller_out_dpid, callee_out_dpid))
|
|
INTO old_set_id FROM voip_rewrite_rule_sets WHERE id = OLD.set_id;
|
|
SELECT IF(NEW.direction = 'in', IF(NEW.field = 'caller', caller_in_dpid, callee_in_dpid), IF(NEW.field = 'caller', caller_out_dpid, callee_out_dpid))
|
|
INTO new_set_id FROM voip_rewrite_rule_sets WHERE id = NEW.set_id;
|
|
|
|
UPDATE kamailio.dialplan
|
|
SET dpid = new_set_id,
|
|
pr = NEW.priority,
|
|
match_exp = NEW.match_pattern,
|
|
subst_exp = NEW.match_pattern,
|
|
repl_exp = NEW.replace_pattern
|
|
WHERE dpid = old_set_id
|
|
AND pr = OLD.priority
|
|
AND match_exp = OLD.match_pattern
|
|
AND subst_exp = OLD.match_pattern
|
|
AND repl_exp = OLD.replace_pattern;
|
|
|
|
END;
|
|
|
|
|
|
|
DROP TRIGGER voip_rwrules_drepl_trig|
|
|
|
|
CREATE TRIGGER voip_rwrules_drepl_trig BEFORE DELETE ON voip_rewrite_rules
|
|
FOR EACH ROW BEGIN
|
|
|
|
DECLARE old_set_id int(11) unsigned;
|
|
|
|
SELECT IF(OLD.direction = 'in', IF(OLD.field = 'caller', caller_in_dpid, callee_in_dpid), IF(OLD.field = 'caller', caller_out_dpid, callee_out_dpid))
|
|
INTO old_set_id FROM voip_rewrite_rule_sets WHERE id = OLD.set_id;
|
|
|
|
DELETE FROM kamailio.dialplan
|
|
WHERE dpid = old_set_id
|
|
AND pr = OLD.priority
|
|
AND match_exp = OLD.match_pattern
|
|
AND subst_exp = OLD.match_pattern
|
|
AND repl_exp = OLD.replace_pattern;
|
|
|
|
END;
|
|
|
|
|
|
|
DROP TRIGGER voip_rwrulesets_drepl_trig|
|
|
|
|
CREATE TRIGGER voip_rwrulesets_drepl_trig BEFORE DELETE ON voip_rewrite_rule_sets
|
|
FOR EACH ROW BEGIN
|
|
|
|
-- work around MySQL bug. the cascaded delete should trigger our
|
|
-- voip_dom_preferences delete action, but doesn't
|
|
DELETE FROM kamailio.dialplan WHERE dpid = OLD.caller_in_dpid;
|
|
DELETE FROM kamailio.dialplan WHERE dpid = OLD.callee_in_dpid;
|
|
DELETE FROM kamailio.dialplan WHERE dpid = OLD.caller_out_dpid;
|
|
DELETE FROM kamailio.dialplan WHERE dpid = OLD.callee_out_dpid;
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_rwrulesets_crepl_trig BEFORE INSERT ON voip_rewrite_rule_sets
|
|
FOR EACH ROW BEGIN
|
|
|
|
IF NEW.caller_in_dpid IS NULL THEN
|
|
INSERT INTO voip_rwrs_sequence VALUES();
|
|
SET NEW.caller_in_dpid = (SELECT LAST_INSERT_ID());
|
|
END IF;
|
|
IF NEW.callee_in_dpid IS NULL THEN
|
|
INSERT INTO voip_rwrs_sequence VALUES();
|
|
SET NEW.callee_in_dpid = (SELECT LAST_INSERT_ID());
|
|
END IF;
|
|
IF NEW.caller_out_dpid IS NULL THEN
|
|
INSERT INTO voip_rwrs_sequence VALUES();
|
|
SET NEW.caller_out_dpid = (SELECT LAST_INSERT_ID());
|
|
END IF;
|
|
IF NEW.callee_out_dpid IS NULL THEN
|
|
INSERT INTO voip_rwrs_sequence VALUES();
|
|
SET NEW.callee_out_dpid = (SELECT LAST_INSERT_ID());
|
|
END IF;
|
|
|
|
DELETE a FROM voip_rwrs_sequence a, voip_rwrs_sequence b WHERE a.id < b.id;
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_rwrulesets_urepl_trig AFTER UPDATE ON voip_rewrite_rule_sets
|
|
FOR EACH ROW BEGIN
|
|
|
|
UPDATE kamailio.dialplan SET dpid = NEW.caller_in_dpid WHERE dpid = OLD.caller_in_dpid;
|
|
UPDATE kamailio.dialplan SET dpid = NEW.callee_in_dpid WHERE dpid = OLD.callee_in_dpid;
|
|
UPDATE kamailio.dialplan SET dpid = NEW.caller_out_dpid WHERE dpid = OLD.caller_out_dpid;
|
|
UPDATE kamailio.dialplan SET dpid = NEW.callee_out_dpid WHERE dpid = OLD.callee_out_dpid;
|
|
|
|
END;
|
|
|
|
|
|
|
DELIMITER ;
|
|
|
|
DELETE FROM voip_preferences WHERE attribute = 'rewrite_rule_set';
|
|
INSERT INTO voip_preferences (attribute, type, usr_pref, dom_pref, peer_pref, data_type, max_occur, internal, description, modify_timestamp)
|
|
VALUES('rewrite_rule_set', 1, 1, 1, 1, 'int', 1, -1, 'Specifies the list of caller and callee rewrite rules which should be applied for incoming and outgoing calls.', '1970-01-01 00:00:00');
|
|
|
|
INSERT INTO voip_preferences (attribute, type, usr_pref, dom_pref, peer_pref, data_type, max_occur, internal, modify_timestamp)
|
|
VALUES('rewrite_caller_in_dpid', 1, 1, 1, 1, 'int', 1, 1, '1970-01-01 00:00:00');
|
|
INSERT INTO voip_preferences (attribute, type, usr_pref, dom_pref, peer_pref, data_type, max_occur, internal, modify_timestamp)
|
|
VALUES('rewrite_callee_in_dpid', 1, 1, 1, 1, 'int', 1, 1, '1970-01-01 00:00:00');
|
|
INSERT INTO voip_preferences (attribute, type, usr_pref, dom_pref, peer_pref, data_type, max_occur, internal, modify_timestamp)
|
|
VALUES('rewrite_caller_out_dpid', 1, 1, 1, 1, 'int', 1, 1, '1970-01-01 00:00:00');
|
|
INSERT INTO voip_preferences (attribute, type, usr_pref, dom_pref, peer_pref, data_type, max_occur, internal, modify_timestamp)
|
|
VALUES('rewrite_callee_out_dpid', 1, 1, 1, 1, 'int', 1, 1, '1970-01-01 00:00:00');
|
|
|
|
-- restore data from migration tables
|
|
INSERT INTO voip_rewrite_rule_sets (name,description,caller_in_dpid,callee_in_dpid,caller_out_dpid,callee_out_dpid)
|
|
SELECT CONCAT('peer___',ip), CONCAT('auto-generated ruleset for ',name),
|
|
dp_caller_in_id, dp_callee_in_id, dp_caller_out_id, dp_callee_out_id
|
|
FROM voip_peer_hosts_rwsmig;
|
|
INSERT INTO voip_rewrite_rules (set_id, match_pattern, replace_pattern, description, direction, field, priority)
|
|
SELECT a.id, b.match_pattern, b.replace_pattern, b.description, b.direction, b.field, b.priority
|
|
FROM voip_rewrite_rule_sets a, voip_peer_rewrites_rwsmig b, voip_peer_hosts_rwsmig c
|
|
WHERE a.name = CONCAT('peer___',c.ip)
|
|
AND b.peer_id = c.id;
|
|
INSERT INTO voip_peer_preferences (peer_host_id,attribute_id,value)
|
|
SELECT a.id, b.id, a.dp_caller_in_id
|
|
FROM voip_peer_hosts_rwsmig a, voip_preferences b
|
|
WHERE b.attribute = 'rewrite_caller_in_dpid';
|
|
INSERT INTO voip_peer_preferences (peer_host_id,attribute_id,value)
|
|
SELECT a.id, b.id, a.dp_callee_in_id
|
|
FROM voip_peer_hosts_rwsmig a, voip_preferences b
|
|
WHERE b.attribute = 'rewrite_callee_in_dpid';
|
|
INSERT INTO voip_peer_preferences (peer_host_id,attribute_id,value)
|
|
SELECT a.id, b.id, a.dp_caller_out_id
|
|
FROM voip_peer_hosts_rwsmig a, voip_preferences b
|
|
WHERE b.attribute = 'rewrite_caller_out_dpid';
|
|
INSERT INTO voip_peer_preferences (peer_host_id,attribute_id,value)
|
|
SELECT a.id, b.id, a.dp_callee_out_id
|
|
FROM voip_peer_hosts_rwsmig a, voip_preferences b
|
|
WHERE b.attribute = 'rewrite_callee_out_dpid';
|
|
|
|
INSERT INTO voip_rewrite_rule_sets (name,description,caller_in_dpid,callee_in_dpid,caller_out_dpid,callee_out_dpid)
|
|
SELECT CONCAT('dom___',b.domain), CONCAT('auto-generated ruleset for ',b.domain),
|
|
dp_caller_in_id, dp_callee_in_id, dp_caller_out_id, dp_callee_out_id
|
|
FROM voip_domain_rwsmig a, voip_domains b
|
|
WHERE a.domain_id = b.id;
|
|
INSERT INTO voip_rewrite_rules (set_id, match_pattern, replace_pattern, description, direction, field, priority)
|
|
SELECT a.id, b.match_pattern, b.replace_pattern, b.description, b.direction, b.field, b.priority
|
|
FROM voip_rewrite_rule_sets a, voip_domain_rewrites_rwsmig b, voip_domains c
|
|
WHERE a.name = CONCAT('dom___',c.domain)
|
|
AND b.domain_id = c.id;
|
|
INSERT INTO voip_dom_preferences (domain_id,attribute_id,value)
|
|
SELECT a.domain_id, b.id, a.dp_caller_in_id
|
|
FROM voip_domain_rwsmig a, voip_preferences b
|
|
WHERE b.attribute = 'rewrite_caller_in_dpid';
|
|
INSERT INTO voip_dom_preferences (domain_id,attribute_id,value)
|
|
SELECT a.domain_id, b.id, a.dp_callee_in_id
|
|
FROM voip_domain_rwsmig a, voip_preferences b
|
|
WHERE b.attribute = 'rewrite_callee_in_dpid';
|
|
INSERT INTO voip_dom_preferences (domain_id,attribute_id,value)
|
|
SELECT a.domain_id, b.id, a.dp_caller_out_id
|
|
FROM voip_domain_rwsmig a, voip_preferences b
|
|
WHERE b.attribute = 'rewrite_caller_out_dpid';
|
|
INSERT INTO voip_dom_preferences (domain_id,attribute_id,value)
|
|
SELECT a.domain_id, b.id, a.dp_callee_out_id
|
|
FROM voip_domain_rwsmig a, voip_preferences b
|
|
WHERE b.attribute = 'rewrite_callee_out_dpid';
|
|
|
|
DROP TABLE voip_peer_hosts_rwsmig;
|
|
DROP TABLE voip_peer_rewrites_rwsmig;
|
|
DROP TABLE voip_domain_rwsmig;
|
|
DROP TABLE voip_domain_rewrites_rwsmig;
|