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.
254 lines
11 KiB
254 lines
11 KiB
-- NOTE! This does not migrate data from new to old tables, but will delete everything from kamailio.dialplan!
|
|
|
|
use provisioning;
|
|
|
|
CREATE TABLE `voip_domain_rewrites` (
|
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
|
`domain_id` int(11) unsigned NOT NULL,
|
|
`match_pattern` varchar(64) NOT NULL DEFAULT '',
|
|
`replace_pattern` varchar(64) NOT NULL,
|
|
`description` varchar(127) NOT NULL DEFAULT '',
|
|
`direction` enum('in','out') NOT NULL,
|
|
`field` enum('caller','callee') NOT NULL,
|
|
`priority` int(11) unsigned NOT NULL DEFAULT 50,
|
|
PRIMARY KEY (`id`),
|
|
KEY `domainidx` (`domain_id`),
|
|
CONSTRAINT `v_drw_domainid_ref` FOREIGN KEY (`domain_id`) REFERENCES `voip_domains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE `voip_peer_rewrites` (
|
|
`id` int(11) unsigned NOT NULL auto_increment,
|
|
`peer_id` int(11) unsigned NOT NULL,
|
|
`match_pattern` varchar(64) NOT NULL default '',
|
|
`replace_pattern` varchar(64) NOT NULL,
|
|
`description` varchar(127) NOT NULL default '',
|
|
`direction` enum('in','out') NOT NULL default 'in',
|
|
`field` enum('caller','callee') NOT NULL default 'caller',
|
|
`priority` int(11) unsigned NOT NULL DEFAULT 50,
|
|
PRIMARY KEY (`id`),
|
|
KEY `peeridx` (`peer_id`),
|
|
KEY `dirfieldidx` (`direction`,`field`),
|
|
CONSTRAINT `v_prw_peerid_ref` FOREIGN KEY (`peer_id`) REFERENCES `voip_peer_hosts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
DROP TRIGGER voip_rwrules_crepl_trig;
|
|
DROP TRIGGER voip_rwrules_urepl_trig;
|
|
DROP TRIGGER voip_rwrules_drepl_trig;
|
|
DROP TRIGGER voip_rwrulesets_drepl_trig;
|
|
|
|
DELIMITER |
|
|
|
|
CREATE TRIGGER voip_domrw_crepl_trig AFTER INSERT ON voip_domain_rewrites
|
|
FOR EACH ROW BEGIN
|
|
|
|
DECLARE caller_in_id int(11) unsigned;
|
|
DECLARE callee_in_id int(11) unsigned;
|
|
DECLARE caller_out_id int(11) unsigned;
|
|
DECLARE callee_out_id int(11) unsigned;
|
|
DECLARE dp_id int(11) unsigned default 0;
|
|
|
|
SELECT vdp.value INTO caller_in_id FROM voip_dom_preferences vdp, voip_preferences vp,
|
|
voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_caller_in'
|
|
AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
|
|
SELECT vdp.value INTO callee_in_id FROM voip_dom_preferences vdp, voip_preferences vp,
|
|
voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_callee_in'
|
|
AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
|
|
SELECT vdp.value INTO caller_out_id FROM voip_dom_preferences vdp, voip_preferences vp,
|
|
voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_caller_out'
|
|
AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
|
|
SELECT vdp.value INTO callee_out_id FROM voip_dom_preferences vdp, voip_preferences vp,
|
|
voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_callee_out'
|
|
AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
|
|
|
|
IF NEW.direction = 'in' AND NEW.field = 'caller' THEN
|
|
SET dp_id = caller_in_id;
|
|
ELSEIF NEW.direction = 'in' AND NEW.field = 'callee' THEN
|
|
SET dp_id = callee_in_id;
|
|
ELSEIF NEW.direction = 'out' AND NEW.field = 'caller' THEN
|
|
SET dp_id = caller_out_id;
|
|
ELSEIF NEW.direction = 'out' AND NEW.field = 'callee' THEN
|
|
SET dp_id = callee_out_id;
|
|
END IF;
|
|
|
|
INSERT INTO kamailio.dialplan (dpid, pr, match_op, match_exp,
|
|
match_len, subst_exp, repl_exp, attrs)
|
|
VALUES(dp_id, NEW.priority, 1, NEW.match_pattern, 0, NEW.match_pattern, NEW.replace_pattern, '');
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_domrw_urepl_trig AFTER UPDATE ON voip_domain_rewrites
|
|
FOR EACH ROW BEGIN
|
|
|
|
DECLARE caller_in_id int(11) unsigned;
|
|
DECLARE callee_in_id int(11) unsigned;
|
|
DECLARE caller_out_id int(11) unsigned;
|
|
DECLARE callee_out_id int(11) unsigned;
|
|
DECLARE dp_id int(11) unsigned default 0;
|
|
|
|
SELECT vdp.value INTO caller_in_id FROM voip_dom_preferences vdp, voip_preferences vp,
|
|
voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_caller_in'
|
|
AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
|
|
SELECT vdp.value INTO callee_in_id FROM voip_dom_preferences vdp, voip_preferences vp,
|
|
voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_callee_in'
|
|
AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
|
|
SELECT vdp.value INTO caller_out_id FROM voip_dom_preferences vdp, voip_preferences vp,
|
|
voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_caller_out'
|
|
AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
|
|
SELECT vdp.value INTO callee_out_id FROM voip_dom_preferences vdp, voip_preferences vp,
|
|
voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_callee_out'
|
|
AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
|
|
|
|
IF NEW.direction = 'in' AND NEW.field = 'caller' THEN
|
|
SET dp_id = caller_in_id;
|
|
ELSEIF NEW.direction = 'in' AND NEW.field = 'callee' THEN
|
|
SET dp_id = callee_in_id;
|
|
ELSEIF NEW.direction = 'out' AND NEW.field = 'caller' THEN
|
|
SET dp_id = caller_out_id;
|
|
ELSEIF NEW.direction = 'out' AND NEW.field = 'callee' THEN
|
|
SET dp_id = callee_out_id;
|
|
END IF;
|
|
|
|
UPDATE kamailio.dialplan SET match_exp = NEW.match_pattern,
|
|
subst_exp = NEW.match_pattern, repl_exp = NEW.replace_pattern, pr = NEW.priority
|
|
WHERE dpid = dp_id AND match_exp = OLD.match_pattern AND pr = OLD.priority
|
|
AND subst_exp = OLD.match_pattern AND repl_exp = OLD.replace_pattern;
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_domrw_drepl_trig AFTER DELETE ON voip_domain_rewrites
|
|
FOR EACH ROW BEGIN
|
|
|
|
DECLARE caller_in_id int(11) unsigned;
|
|
DECLARE callee_in_id int(11) unsigned;
|
|
DECLARE caller_out_id int(11) unsigned;
|
|
DECLARE callee_out_id int(11) unsigned;
|
|
DECLARE dp_id int(11) unsigned default 0;
|
|
|
|
SELECT vdp.value INTO caller_in_id FROM voip_dom_preferences vdp, voip_preferences vp,
|
|
voip_domains vd WHERE vd.id = OLD.domain_id AND vp.attribute = 'dp_dom_caller_in'
|
|
AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
|
|
SELECT vdp.value INTO callee_in_id FROM voip_dom_preferences vdp, voip_preferences vp,
|
|
voip_domains vd WHERE vd.id = OLD.domain_id AND vp.attribute = 'dp_dom_callee_in'
|
|
AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
|
|
SELECT vdp.value INTO caller_out_id FROM voip_dom_preferences vdp, voip_preferences vp,
|
|
voip_domains vd WHERE vd.id = OLD.domain_id AND vp.attribute = 'dp_dom_caller_out'
|
|
AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
|
|
SELECT vdp.value INTO callee_out_id FROM voip_dom_preferences vdp, voip_preferences vp,
|
|
voip_domains vd WHERE vd.id = OLD.domain_id AND vp.attribute = 'dp_dom_callee_out'
|
|
AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
|
|
|
|
IF OLD.direction = 'in' AND OLD.field = 'caller' THEN
|
|
SET dp_id = caller_in_id;
|
|
ELSEIF OLD.direction = 'in' AND OLD.field = 'callee' THEN
|
|
SET dp_id = callee_in_id;
|
|
ELSEIF OLD.direction = 'out' AND OLD.field = 'caller' THEN
|
|
SET dp_id = caller_out_id;
|
|
ELSEIF OLD.direction = 'out' AND OLD.field = 'callee' THEN
|
|
SET dp_id = callee_out_id;
|
|
END IF;
|
|
|
|
DELETE FROM kamailio.dialplan
|
|
WHERE dpid = dp_id AND match_exp = OLD.match_pattern
|
|
AND subst_exp = OLD.match_pattern AND repl_exp = OLD.replace_pattern;
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_peerrw_crepl_trig AFTER INSERT ON voip_peer_rewrites
|
|
FOR EACH ROW BEGIN
|
|
DECLARE caller_in_id int(11) unsigned;
|
|
DECLARE callee_in_id int(11) unsigned;
|
|
DECLARE caller_out_id int(11) unsigned;
|
|
DECLARE callee_out_id int(11) unsigned;
|
|
DECLARE dp_id int(11) unsigned default 0;
|
|
|
|
SELECT dp_caller_in_id, dp_callee_in_id, dp_caller_out_id, dp_callee_out_id
|
|
INTO caller_in_id, callee_in_id, caller_out_id, callee_out_id
|
|
FROM voip_peer_hosts WHERE id = NEW.peer_id;
|
|
|
|
IF NEW.direction = 'in' AND NEW.field = 'caller' THEN
|
|
SET dp_id = caller_in_id;
|
|
ELSEIF NEW.direction = 'in' AND NEW.field = 'callee' THEN
|
|
SET dp_id = callee_in_id;
|
|
ELSEIF NEW.direction = 'out' AND NEW.field = 'caller' THEN
|
|
SET dp_id = caller_out_id;
|
|
ELSEIF NEW.direction = 'out' AND NEW.field = 'callee' THEN
|
|
SET dp_id = callee_out_id;
|
|
END IF;
|
|
|
|
INSERT INTO kamailio.dialplan (dpid, pr, match_op, match_exp,
|
|
match_len, subst_exp, repl_exp, attrs)
|
|
VALUES(dp_id, NEW.priority, 1, NEW.match_pattern, 0, NEW.match_pattern, NEW.replace_pattern, '');
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_peerrw_urepl_trig AFTER UPDATE ON voip_peer_rewrites
|
|
FOR EACH ROW BEGIN
|
|
DECLARE caller_in_id int(11) unsigned;
|
|
DECLARE callee_in_id int(11) unsigned;
|
|
DECLARE caller_out_id int(11) unsigned;
|
|
DECLARE callee_out_id int(11) unsigned;
|
|
DECLARE dp_id int(11) unsigned default 0;
|
|
|
|
SELECT dp_caller_in_id, dp_callee_in_id, dp_caller_out_id, dp_callee_out_id
|
|
INTO caller_in_id, callee_in_id, caller_out_id, callee_out_id
|
|
FROM voip_peer_hosts WHERE id = NEW.peer_id;
|
|
|
|
IF NEW.direction = 'in' AND NEW.field = 'caller' THEN
|
|
SET dp_id = caller_in_id;
|
|
ELSEIF NEW.direction = 'in' AND NEW.field = 'callee' THEN
|
|
SET dp_id = callee_in_id;
|
|
ELSEIF NEW.direction = 'out' AND NEW.field = 'caller' THEN
|
|
SET dp_id = caller_out_id;
|
|
ELSEIF NEW.direction = 'out' AND NEW.field = 'callee' THEN
|
|
SET dp_id = callee_out_id;
|
|
END IF;
|
|
|
|
UPDATE kamailio.dialplan SET match_exp = NEW.match_pattern,
|
|
subst_exp = NEW.match_pattern, repl_exp = NEW.replace_pattern,
|
|
pr = NEW.priority
|
|
WHERE dpid = dp_id AND match_exp = OLD.match_pattern AND pr = OLD.priority
|
|
AND subst_exp = OLD.match_pattern AND repl_exp = OLD.replace_pattern;
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_peerrw_drepl_trig AFTER DELETE ON voip_peer_rewrites
|
|
FOR EACH ROW BEGIN
|
|
DECLARE caller_in_id int(11) unsigned;
|
|
DECLARE callee_in_id int(11) unsigned;
|
|
DECLARE caller_out_id int(11) unsigned;
|
|
DECLARE callee_out_id int(11) unsigned;
|
|
DECLARE dp_id int(11) unsigned default 0;
|
|
|
|
SELECT dp_caller_in_id, dp_callee_in_id, dp_caller_out_id, dp_callee_out_id
|
|
INTO caller_in_id, callee_in_id, caller_out_id, callee_out_id
|
|
FROM voip_peer_hosts WHERE id = OLD.peer_id;
|
|
|
|
IF OLD.direction = 'in' AND OLD.field = 'caller' THEN
|
|
SET dp_id = caller_in_id;
|
|
ELSEIF OLD.direction = 'in' AND OLD.field = 'callee' THEN
|
|
SET dp_id = callee_in_id;
|
|
ELSEIF OLD.direction = 'out' AND OLD.field = 'caller' THEN
|
|
SET dp_id = caller_out_id;
|
|
ELSEIF OLD.direction = 'out' AND OLD.field = 'callee' THEN
|
|
SET dp_id = callee_out_id;
|
|
END IF;
|
|
|
|
DELETE FROM kamailio.dialplan
|
|
WHERE dpid = dp_id AND match_exp = OLD.match_pattern
|
|
AND subst_exp = OLD.match_pattern AND repl_exp = OLD.replace_pattern;
|
|
|
|
END;
|
|
|
|
|
|
|
DELIMITER ;
|
|
|
|
DELETE FROM voip_preferences WHERE attribute = 'rewrite_rule_set';
|
|
|
|
DROP TABLE `voip_rewrite_rules`;
|
|
DROP TABLE `voip_rewrite_rule_sets`;
|