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.
515 lines
20 KiB
515 lines
20 KiB
SET AUTOCOMMIT=0;
|
|
|
|
USE billing;
|
|
|
|
alter table lnp_numbers
|
|
add column routing_number varchar(31) default null after number;
|
|
|
|
USE provisioning;
|
|
|
|
ALTER TABLE voip_rewrite_rules
|
|
CHANGE COLUMN direction direction ENUM('in', 'out', 'lnp') NOT NULL DEFAULT 'in';
|
|
|
|
ALTER TABLE voip_rewrite_rule_sets
|
|
ADD COLUMN caller_lnp_dpid INT(11) UNSIGNED DEFAULT NULL,
|
|
ADD COLUMN callee_lnp_dpid INT(11) UNSIGNED DEFAULT NULL;
|
|
|
|
set @pid = 0;
|
|
select id into @pid from voip_preference_groups where name = 'Number Manipulations';
|
|
insert into voip_preferences
|
|
(voip_preference_groups_id, attribute, label, type, max_occur,
|
|
usr_pref, prof_pref, dom_pref, peer_pref, contract_pref,
|
|
modify_timestamp, internal, expose_to_customer, data_type, read_only,
|
|
description)
|
|
values
|
|
(@pid, 'rewrite_caller_lnp_dpid', 'Internal # for lnp caller rewrite rule set', 1, 1,
|
|
1, 1, 1, 1, 0,
|
|
now(), 1, 0, 'int', 0,
|
|
NULL),
|
|
(@pid, 'rewrite_callee_lnp_dpid', 'Internal # for lnp callee rewrite rule set', 1, 1,
|
|
1, 1, 1, 1, 0,
|
|
now(), 1, 0, 'int', 0,
|
|
NULL);
|
|
|
|
DROP TRIGGER voip_rwrulesets_crepl_trig;
|
|
DELIMITER ;;
|
|
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;
|
|
IF NEW.caller_lnp_dpid IS NULL THEN
|
|
INSERT INTO voip_rwrs_sequence VALUES();
|
|
SET NEW.caller_lnp_dpid = (SELECT LAST_INSERT_ID());
|
|
END IF;
|
|
IF NEW.callee_lnp_dpid IS NULL THEN
|
|
INSERT INTO voip_rwrs_sequence VALUES();
|
|
SET NEW.callee_lnp_dpid = (SELECT LAST_INSERT_ID());
|
|
END IF;
|
|
|
|
DELETE a FROM voip_rwrs_sequence a, voip_rwrs_sequence b WHERE a.id < b.id;
|
|
END;;
|
|
DELIMITER ;
|
|
|
|
DROP TRIGGER voip_rwrulesets_urepl_trig;
|
|
DELIMITER ;;
|
|
CREATE TRIGGER voip_rwrulesets_urepl_trig AFTER UPDATE ON voip_rewrite_rule_sets
|
|
FOR EACH ROW BEGIN
|
|
|
|
IF NEW.caller_in_dpid != OLD.caller_in_dpid THEN
|
|
UPDATE kamailio.dialplan SET dpid = NEW.caller_in_dpid WHERE dpid <=> OLD.caller_in_dpid;
|
|
UPDATE voip_usr_preferences a, voip_preferences b
|
|
SET a.value = NEW.caller_in_dpid
|
|
WHERE b.attribute <=> 'rewrite_caller_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_in_dpid;
|
|
UPDATE voip_dom_preferences a, voip_preferences b
|
|
SET a.value = NEW.caller_in_dpid
|
|
WHERE b.attribute <=> 'rewrite_caller_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_in_dpid;
|
|
UPDATE voip_peer_preferences a, voip_preferences b
|
|
SET a.value = NEW.caller_in_dpid
|
|
WHERE b.attribute <=> 'rewrite_caller_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_in_dpid;
|
|
UPDATE voip_prof_preferences a, voip_preferences b
|
|
SET a.value = NEW.caller_in_dpid
|
|
WHERE b.attribute <=> 'rewrite_caller_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_in_dpid;
|
|
END IF;
|
|
|
|
IF NEW.callee_in_dpid != OLD.callee_in_dpid THEN
|
|
UPDATE kamailio.dialplan SET dpid = NEW.callee_in_dpid WHERE dpid <=> OLD.callee_in_dpid;
|
|
UPDATE voip_usr_preferences a, voip_preferences b
|
|
SET a.value = NEW.callee_in_dpid
|
|
WHERE b.attribute <=> 'rewrite_callee_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_in_dpid;
|
|
UPDATE voip_dom_preferences a, voip_preferences b
|
|
SET a.value = NEW.callee_in_dpid
|
|
WHERE b.attribute <=> 'rewrite_callee_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_in_dpid;
|
|
UPDATE voip_peer_preferences a, voip_preferences b
|
|
SET a.value = NEW.callee_in_dpid
|
|
WHERE b.attribute <=> 'rewrite_callee_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_in_dpid;
|
|
UPDATE voip_prof_preferences a, voip_preferences b
|
|
SET a.value = NEW.callee_in_dpid
|
|
WHERE b.attribute <=> 'rewrite_callee_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_in_dpid;
|
|
END IF;
|
|
|
|
IF NEW.caller_out_dpid != OLD.caller_out_dpid THEN
|
|
UPDATE kamailio.dialplan SET dpid = NEW.caller_out_dpid WHERE dpid <=> OLD.caller_out_dpid;
|
|
UPDATE voip_usr_preferences a, voip_preferences b
|
|
SET a.value = NEW.caller_out_dpid
|
|
WHERE b.attribute <=> 'rewrite_caller_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_out_dpid;
|
|
UPDATE voip_dom_preferences a, voip_preferences b
|
|
SET a.value = NEW.caller_out_dpid
|
|
WHERE b.attribute <=> 'rewrite_caller_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_out_dpid;
|
|
UPDATE voip_peer_preferences a, voip_preferences b
|
|
SET a.value = NEW.caller_out_dpid
|
|
WHERE b.attribute <=> 'rewrite_caller_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_out_dpid;
|
|
UPDATE voip_prof_preferences a, voip_preferences b
|
|
SET a.value = NEW.caller_out_dpid
|
|
WHERE b.attribute <=> 'rewrite_caller_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_out_dpid;
|
|
END IF;
|
|
|
|
IF NEW.callee_out_dpid != OLD.callee_out_dpid THEN
|
|
UPDATE kamailio.dialplan SET dpid = NEW.callee_out_dpid WHERE dpid <=> OLD.callee_out_dpid;
|
|
UPDATE voip_usr_preferences a, voip_preferences b
|
|
SET a.value = NEW.callee_out_dpid
|
|
WHERE b.attribute <=> 'rewrite_callee_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_out_dpid;
|
|
UPDATE voip_dom_preferences a, voip_preferences b
|
|
SET a.value = NEW.callee_out_dpid
|
|
WHERE b.attribute <=> 'rewrite_callee_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_out_dpid;
|
|
UPDATE voip_peer_preferences a, voip_preferences b
|
|
SET a.value = NEW.callee_out_dpid
|
|
WHERE b.attribute <=> 'rewrite_callee_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_out_dpid;
|
|
UPDATE voip_prof_preferences a, voip_preferences b
|
|
SET a.value = NEW.callee_out_dpid
|
|
WHERE b.attribute <=> 'rewrite_callee_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_out_dpid;
|
|
END IF;
|
|
|
|
IF NEW.caller_lnp_dpid != OLD.caller_lnp_dpid THEN
|
|
UPDATE kamailio.dialplan SET dpid = NEW.caller_lnp_dpid WHERE dpid <=> OLD.caller_lnp_dpid;
|
|
UPDATE voip_usr_preferences a, voip_preferences b
|
|
SET a.value = NEW.caller_lnp_dpid
|
|
WHERE b.attribute <=> 'rewrite_caller_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_lnp_dpid;
|
|
UPDATE voip_dom_preferences a, voip_preferences b
|
|
SET a.value = NEW.caller_lnp_dpid
|
|
WHERE b.attribute <=> 'rewrite_caller_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_lnp_dpid;
|
|
UPDATE voip_peer_preferences a, voip_preferences b
|
|
SET a.value = NEW.caller_lnp_dpid
|
|
WHERE b.attribute <=> 'rewrite_caller_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_lnp_dpid;
|
|
UPDATE voip_prof_preferences a, voip_preferences b
|
|
SET a.value = NEW.caller_lnp_dpid
|
|
WHERE b.attribute <=> 'rewrite_caller_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_lnp_dpid;
|
|
END IF;
|
|
|
|
IF NEW.callee_lnp_dpid != OLD.callee_lnp_dpid THEN
|
|
UPDATE kamailio.dialplan SET dpid = NEW.callee_lnp_dpid WHERE dpid <=> OLD.callee_lnp_dpid;
|
|
UPDATE voip_usr_preferences a, voip_preferences b
|
|
SET a.value = NEW.callee_lnp_dpid
|
|
WHERE b.attribute <=> 'rewrite_callee_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_lnp_dpid;
|
|
UPDATE voip_dom_preferences a, voip_preferences b
|
|
SET a.value = NEW.callee_lnp_dpid
|
|
WHERE b.attribute <=> 'rewrite_callee_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_lnp_dpid;
|
|
UPDATE voip_peer_preferences a, voip_preferences b
|
|
SET a.value = NEW.callee_lnp_dpid
|
|
WHERE b.attribute <=> 'rewrite_callee_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_lnp_dpid;
|
|
UPDATE voip_prof_preferences a, voip_preferences b
|
|
SET a.value = NEW.callee_lnp_dpid
|
|
WHERE b.attribute <=> 'rewrite_callee_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_lnp_dpid;
|
|
END IF;
|
|
END;;
|
|
DELIMITER ;
|
|
|
|
DROP TRIGGER voip_rwrulesets_drepl_trig;
|
|
DELIMITER ;;
|
|
CREATE TRIGGER voip_rwrulesets_drepl_trig BEFORE DELETE ON voip_rewrite_rule_sets
|
|
FOR EACH ROW BEGIN
|
|
|
|
DELETE a FROM voip_usr_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_caller_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_in_dpid;
|
|
DELETE a FROM voip_usr_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_callee_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_in_dpid;
|
|
DELETE a FROM voip_usr_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_caller_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_out_dpid;
|
|
DELETE a FROM voip_usr_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_callee_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_out_dpid;
|
|
DELETE a FROM voip_usr_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_caller_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_lnp_dpid;
|
|
DELETE a FROM voip_usr_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_callee_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_lnp_dpid;
|
|
|
|
DELETE a FROM voip_dom_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_caller_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_in_dpid;
|
|
DELETE a FROM voip_dom_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_callee_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_in_dpid;
|
|
DELETE a FROM voip_dom_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_caller_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_out_dpid;
|
|
DELETE a FROM voip_dom_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_callee_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_out_dpid;
|
|
DELETE a FROM voip_dom_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_caller_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_lnp_dpid;
|
|
DELETE a FROM voip_dom_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_callee_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_lnp_dpid;
|
|
|
|
DELETE a FROM voip_peer_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_caller_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_in_dpid;
|
|
DELETE a FROM voip_peer_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_callee_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_in_dpid;
|
|
DELETE a FROM voip_peer_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_caller_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_out_dpid;
|
|
DELETE a FROM voip_peer_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_callee_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_out_dpid;
|
|
DELETE a FROM voip_peer_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_caller_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_lnp_dpid;
|
|
DELETE a FROM voip_peer_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_callee_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_lnp_dpid;
|
|
|
|
DELETE a FROM voip_prof_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_caller_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_in_dpid;
|
|
DELETE a FROM voip_prof_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_callee_in_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_in_dpid;
|
|
DELETE a FROM voip_prof_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_caller_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_out_dpid;
|
|
DELETE a FROM voip_prof_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_callee_out_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_out_dpid;
|
|
DELETE a FROM voip_prof_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_caller_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.caller_lnp_dpid;
|
|
DELETE a FROM voip_prof_preferences a, voip_preferences b
|
|
WHERE b.attribute <=> 'rewrite_callee_lnp_dpid'
|
|
AND a.attribute_id <=> b.id
|
|
AND a.value <=> OLD.callee_lnp_dpid;
|
|
|
|
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;
|
|
DELETE FROM kamailio.dialplan WHERE dpid <=> OLD.caller_lnp_dpid;
|
|
DELETE FROM kamailio.dialplan WHERE dpid <=> OLD.callee_lnp_dpid;
|
|
|
|
END;;
|
|
DELIMITER ;
|
|
|
|
DROP TRIGGER voip_rwrules_crepl_trig;
|
|
DELIMITER ;;
|
|
CREATE TRIGGER voip_rwrules_crepl_trig AFTER INSERT ON voip_rewrite_rules
|
|
FOR EACH ROW BEGIN
|
|
|
|
DECLARE new_set_id int(11) unsigned;
|
|
|
|
IF NEW.enabled = 1 THEN
|
|
|
|
IF NEW.direction = 'in' THEN
|
|
SELECT IF(NEW.field = 'caller', caller_in_dpid, callee_in_dpid)
|
|
INTO new_set_id FROM voip_rewrite_rule_sets WHERE id <=> NEW.set_id;
|
|
ELSEIF NEW.direction = 'out' THEN
|
|
SELECT IF(NEW.field = 'caller', caller_out_dpid, callee_out_dpid)
|
|
INTO new_set_id FROM voip_rewrite_rule_sets WHERE id <=> NEW.set_id;
|
|
ELSEIF NEW.direction = 'lnp' THEN
|
|
SELECT IF(NEW.field = 'caller', caller_lnp_dpid, callee_lnp_dpid)
|
|
INTO new_set_id FROM voip_rewrite_rule_sets WHERE id <=> NEW.set_id;
|
|
END IF;
|
|
|
|
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 IF;
|
|
|
|
END;;
|
|
DELIMITER ;
|
|
|
|
DROP TRIGGER voip_rwrules_urepl_trig;
|
|
DELIMITER ;;
|
|
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;
|
|
|
|
IF OLD.enabled = 1 AND NEW.enabled = 1 THEN
|
|
|
|
IF OLD.direction = 'in' THEN
|
|
SELECT IF(OLD.field = 'caller', caller_in_dpid, callee_in_dpid)
|
|
INTO old_set_id FROM voip_rewrite_rule_sets WHERE id <=> OLD.set_id;
|
|
ELSEIF OLD.direction = 'out' THEN
|
|
SELECT IF(OLD.field = 'caller', caller_out_dpid, callee_out_dpid)
|
|
INTO old_set_id FROM voip_rewrite_rule_sets WHERE id <=> OLD.set_id;
|
|
ELSEIF OLD.direction = 'lnp' THEN
|
|
SELECT IF(OLD.field = 'caller', caller_lnp_dpid, callee_lnp_dpid)
|
|
INTO old_set_id FROM voip_rewrite_rule_sets WHERE id <=> OLD.set_id;
|
|
END IF;
|
|
|
|
IF NEW.direction = 'in' THEN
|
|
SELECT IF(NEW.field = 'caller', caller_in_dpid, callee_in_dpid)
|
|
INTO new_set_id FROM voip_rewrite_rule_sets WHERE id <=> NEW.set_id;
|
|
ELSEIF NEW.direction = 'out' THEN
|
|
SELECT IF(NEW.field = 'caller', caller_out_dpid, callee_out_dpid)
|
|
INTO new_set_id FROM voip_rewrite_rule_sets WHERE id <=> NEW.set_id;
|
|
ELSEIF NEW.direction = 'lnp' THEN
|
|
SELECT IF(NEW.field = 'caller', caller_lnp_dpid, callee_lnp_dpid)
|
|
INTO new_set_id FROM voip_rewrite_rule_sets WHERE id <=> NEW.set_id;
|
|
END IF;
|
|
|
|
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;
|
|
ELSEIF OLD.enabled = 0 AND NEW.enabled = 1 THEN
|
|
|
|
IF NEW.direction = 'in' THEN
|
|
SELECT IF(NEW.field = 'caller', caller_in_dpid, callee_in_dpid)
|
|
INTO new_set_id FROM voip_rewrite_rule_sets WHERE id <=> NEW.set_id;
|
|
ELSEIF NEW.direction = 'out' THEN
|
|
SELECT IF(NEW.field = 'caller', caller_out_dpid, callee_out_dpid)
|
|
INTO new_set_id FROM voip_rewrite_rule_sets WHERE id <=> NEW.set_id;
|
|
ELSEIF NEW.direction = 'lnp' THEN
|
|
SELECT IF(NEW.field = 'caller', caller_lnp_dpid, callee_lnp_dpid)
|
|
INTO new_set_id FROM voip_rewrite_rule_sets WHERE id <=> NEW.set_id;
|
|
END IF;
|
|
|
|
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,'');
|
|
ELSEIF OLD.enabled = 1 AND NEW.enabled = 0 THEN
|
|
|
|
IF OLD.direction = 'in' THEN
|
|
SELECT IF(OLD.field = 'caller', caller_in_dpid, callee_in_dpid)
|
|
INTO old_set_id FROM voip_rewrite_rule_sets WHERE id <=> OLD.set_id;
|
|
ELSEIF OLD.direction = 'out' THEN
|
|
SELECT IF(OLD.field = 'caller', caller_out_dpid, callee_out_dpid)
|
|
INTO old_set_id FROM voip_rewrite_rule_sets WHERE id <=> OLD.set_id;
|
|
ELSEIF OLD.direction = 'lnp' THEN
|
|
SELECT IF(OLD.field = 'caller', caller_lnp_dpid, callee_lnp_dpid)
|
|
INTO old_set_id FROM voip_rewrite_rule_sets WHERE id <=> OLD.set_id;
|
|
END IF;
|
|
|
|
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 IF;
|
|
|
|
END;;
|
|
DELIMITER ;
|
|
|
|
DROP TRIGGER voip_rwrules_drepl_trig;
|
|
DELIMITER ;;
|
|
CREATE TRIGGER voip_rwrules_drepl_trig BEFORE DELETE ON voip_rewrite_rules
|
|
FOR EACH ROW BEGIN
|
|
|
|
DECLARE old_set_id int(11) unsigned;
|
|
|
|
IF OLD.direction = 'in' THEN
|
|
SELECT IF(OLD.field = 'caller', caller_in_dpid, callee_in_dpid)
|
|
INTO old_set_id FROM voip_rewrite_rule_sets WHERE id <=> OLD.set_id;
|
|
ELSEIF OLD.direction = 'out' THEN
|
|
SELECT IF(OLD.field = 'caller', caller_out_dpid, callee_out_dpid)
|
|
INTO old_set_id FROM voip_rewrite_rule_sets WHERE id <=> OLD.set_id;
|
|
ELSEIF OLD.direction = 'lnp' THEN
|
|
SELECT IF(OLD.field = 'caller', caller_lnp_dpid, callee_lnp_dpid)
|
|
INTO old_set_id FROM voip_rewrite_rule_sets WHERE id <=> OLD.set_id;
|
|
END IF;
|
|
|
|
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 ;;
|
|
DELIMITER ;
|
|
|
|
delimiter ;;
|
|
create procedure tmp_update_rwr_seq() begin
|
|
declare done boolean default false;
|
|
declare _id int(11) unsigned;
|
|
declare last_seq int(11) unsigned;
|
|
declare caller_cur cursor for select id from provisioning.voip_rewrite_rule_sets where caller_lnp_dpid is null;
|
|
declare callee_cur cursor for select id from provisioning.voip_rewrite_rule_sets where callee_lnp_dpid is null;
|
|
declare continue handler for not found set done := true;
|
|
|
|
open caller_cur;
|
|
testloop: loop
|
|
fetch caller_cur into _id;
|
|
if done then
|
|
leave testloop;
|
|
end if;
|
|
insert into voip_rwrs_sequence values();
|
|
set last_seq := last_insert_id();
|
|
update provisioning.voip_rewrite_rule_sets set caller_lnp_dpid = last_seq where id = _id;
|
|
end loop testloop;
|
|
close caller_cur;
|
|
|
|
set done := false;
|
|
open callee_cur;
|
|
testloop: loop
|
|
fetch callee_cur into _id;
|
|
if done then
|
|
leave testloop;
|
|
end if;
|
|
insert into voip_rwrs_sequence values();
|
|
set last_seq := last_insert_id();
|
|
update provisioning.voip_rewrite_rule_sets set callee_lnp_dpid = last_seq where id = _id;
|
|
end loop testloop;
|
|
close callee_cur;
|
|
|
|
delete from provisioning.voip_rwrs_sequence where id < last_seq;
|
|
|
|
end;;
|
|
delimiter ;
|
|
|
|
call tmp_update_rwr_seq();
|
|
|
|
drop procedure tmp_update_rwr_seq;
|
|
|
|
COMMIT;
|