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.
204 lines
6.8 KiB
204 lines
6.8 KiB
USE kamailio;
|
|
|
|
CREATE TABLE `kamailio`.`lcr_gw` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`lcr_id` smallint(5) unsigned NOT NULL,
|
|
`gw_name` varchar(128) NOT NULL,
|
|
`ip_addr` varchar(15) NOT NULL,
|
|
`hostname` varchar(64) DEFAULT NULL,
|
|
`port` smallint(5) unsigned DEFAULT NULL,
|
|
`params` varchar(64) DEFAULT NULL,
|
|
`uri_scheme` tinyint(3) unsigned DEFAULT NULL,
|
|
`transport` tinyint(3) unsigned DEFAULT NULL,
|
|
`strip` tinyint(3) unsigned DEFAULT NULL,
|
|
`tag` varchar(16) DEFAULT NULL,
|
|
`flags` int(10) unsigned NOT NULL DEFAULT '0',
|
|
`defunct` int(10) unsigned DEFAULT NULL,
|
|
`group_id` int(11) unsigned NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `lcr_id_gw_name_idx` (`lcr_id`,`gw_name`),
|
|
UNIQUE KEY `lcr_id_ip_addr_idx` (`lcr_id`,`ip_addr`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
CREATE TABLE `kamailio`.`lcr_rule` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`lcr_id` smallint(5) unsigned NOT NULL,
|
|
`prefix` varchar(16) DEFAULT NULL,
|
|
`from_uri` varchar(64) DEFAULT NULL,
|
|
`stopper` int(10) unsigned NOT NULL DEFAULT '0',
|
|
`enabled` int(10) unsigned NOT NULL DEFAULT '1',
|
|
`group_id` int(11) unsigned NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `lcr_id_idx` (`lcr_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
CREATE TABLE `kamailio`.`lcr_rule_target` (
|
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`lcr_id` smallint(5) unsigned NOT NULL,
|
|
`rule_id` int(10) unsigned NOT NULL,
|
|
`gw_id` int(10) unsigned NOT NULL,
|
|
`priority` tinyint(3) unsigned NOT NULL,
|
|
`weight` int(10) unsigned NOT NULL DEFAULT '1',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `rule_id_gw_id_idx` (`rule_id`,`gw_id`),
|
|
KEY `lcr_id_idx` (`lcr_id`),
|
|
KEY `gw_id_idx` (`gw_id`),
|
|
CONSTRAINT `l_r_t_ruleid_ref` FOREIGN KEY (`rule_id`)
|
|
REFERENCES `lcr_rule` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT `l_r_t_gwid_ref` FOREIGN KEY (`gw_id`)
|
|
REFERENCES `lcr_gw` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
|
|
USE provisioning;
|
|
|
|
DROP trigger voip_prul_crepl_trig;
|
|
DROP trigger voip_prul_urepl_trig;
|
|
DROP trigger voip_prul_drepl_trig;
|
|
DROP trigger voip_pgrp_urepl_trig;
|
|
DROP trigger voip_pgrp_drepl_trig;
|
|
DROP trigger voip_phost_crepl_trig;
|
|
DROP trigger voip_phost_urepl_trig;
|
|
DROP trigger voip_phost_drepl_trig;
|
|
|
|
DELIMITER |
|
|
|
|
CREATE TRIGGER voip_prul_crepl_trig AFTER INSERT ON voip_peer_rules
|
|
FOR EACH ROW BEGIN
|
|
|
|
INSERT INTO kamailio.lcr_rule (lcr_id, prefix, from_uri, stopper, enabled, group_id)
|
|
VALUES(1, NEW.callee_prefix,
|
|
IF(LENGTH(NEW.caller_prefix), CONCAT('^', NEW.caller_prefix), ''),
|
|
0, 1, NEW.group_id);
|
|
|
|
INSERT INTO kamailio.lcr_rule_target (lcr_id, rule_id, gw_id, priority, weight)
|
|
SELECT gw.lcr_id, LAST_INSERT_ID(), gw.id, vpg.priority, vph.weight
|
|
FROM kamailio.lcr_gw gw
|
|
INNER JOIN provisioning.voip_peer_hosts vph ON vph.ip = gw.ip_addr
|
|
AND gw.lcr_id = 1
|
|
AND vph.group_id = gw.group_id
|
|
INNER JOIN provisioning.voip_peer_groups vpg ON vpg.id = vph.group_id
|
|
WHERE vph.group_id = NEW.group_id;
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_prul_urepl_trig AFTER UPDATE ON voip_peer_rules
|
|
FOR EACH ROW BEGIN
|
|
|
|
UPDATE kamailio.lcr_rule
|
|
SET prefix = NEW.callee_prefix,
|
|
from_uri = IF(LENGTH(NEW.caller_prefix), CONCAT('^', NEW.caller_prefix), '')
|
|
WHERE prefix = OLD.callee_prefix
|
|
AND from_uri = IF(LENGTH(OLD.caller_prefix), CONCAT('^', OLD.caller_prefix), '')
|
|
AND group_id = OLD.group_id;
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_prul_drepl_trig AFTER DELETE ON voip_peer_rules
|
|
FOR EACH ROW BEGIN
|
|
|
|
DELETE FROM kamailio.lcr_rule
|
|
WHERE prefix = OLD.callee_prefix
|
|
AND from_uri = IF(LENGTH(OLD.caller_prefix), CONCAT('^', OLD.caller_prefix), '')
|
|
AND group_id = OLD.group_id;
|
|
|
|
-- foreign key will delete from kamailio.lcr_rule_target
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_pgrp_urepl_trig AFTER UPDATE ON voip_peer_groups
|
|
FOR EACH ROW BEGIN
|
|
|
|
UPDATE kamailio.lcr_rule_target rt, kamailio.lcr_gw gw
|
|
SET rt.priority = NEW.priority
|
|
WHERE gw.id = rt.gw_id
|
|
AND gw.lcr_id = 1
|
|
AND gw.group_id = NEW.id;
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_pgrp_drepl_trig AFTER DELETE ON voip_peer_groups
|
|
FOR EACH ROW BEGIN
|
|
|
|
DELETE FROM kamailio.lcr_rule WHERE group_id = OLD.id;
|
|
DELETE FROM kamailio.lcr_gw WHERE group_id = OLD.id;
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_phost_crepl_trig AFTER INSERT ON voip_peer_hosts
|
|
FOR EACH ROW BEGIN
|
|
|
|
INSERT INTO kamailio.lcr_gw (lcr_id, gw_name, ip_addr, port, uri_scheme, transport, strip, flags, group_id)
|
|
VALUES(1, NEW.name, NEW.ip, NEW.port, 1, 1, 0, NEW.id, NEW.group_id);
|
|
|
|
INSERT INTO kamailio.lcr_rule_target (lcr_id, rule_id, gw_id, priority, weight)
|
|
SELECT rule.lcr_id, rule.id, LAST_INSERT_ID(), vpg.priority, NEW.weight
|
|
FROM kamailio.lcr_rule rule
|
|
INNER JOIN provisioning.voip_peer_groups vpg ON vpg.id = rule.group_id
|
|
WHERE vpg.id = NEW.group_id;
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_phost_urepl_trig AFTER UPDATE ON voip_peer_hosts
|
|
FOR EACH ROW BEGIN
|
|
|
|
UPDATE kamailio.lcr_gw
|
|
SET gw_name = NEW.name, ip_addr = NEW.ip, port = NEW.port, flags = NEW.id
|
|
WHERE lcr_id = 1 AND ip_addr = OLD.ip;
|
|
|
|
UPDATE kamailio.lcr_rule_target rt, kamailio.lcr_gw gw
|
|
SET rt.weight = NEW.weight
|
|
WHERE gw.id = rt.gw_id
|
|
AND gw.lcr_id = 1
|
|
AND gw.group_id = NEW.group_id
|
|
AND gw.ip_addr = NEW.ip;
|
|
|
|
END;
|
|
|
|
|
|
|
CREATE TRIGGER voip_phost_drepl_trig AFTER DELETE ON voip_peer_hosts
|
|
FOR EACH ROW BEGIN
|
|
|
|
DELETE FROM kamailio.lcr_gw
|
|
WHERE lcr_id = 1
|
|
AND group_id = OLD.group_id
|
|
AND ip_addr = OLD.ip;
|
|
|
|
-- foreign key will delete from kamailio.lcr_rule_target
|
|
|
|
END;
|
|
|
|
|
|
|
DELIMITER ;
|
|
|
|
USE kamailio;
|
|
|
|
INSERT INTO lcr_rule (lcr_id, prefix, from_uri, stopper, enabled, group_id)
|
|
SELECT 1, callee_prefix,
|
|
IF(LENGTH(caller_prefix), CONCAT('^', caller_prefix), ''),
|
|
0, 1, group_id
|
|
FROM provisioning.voip_peer_rules;
|
|
|
|
INSERT INTO lcr_gw (lcr_id, gw_name, ip_addr, port, uri_scheme, transport, strip, flags, group_id)
|
|
SELECT 1, name, ip, port, 1, 1, 0, id, group_id
|
|
FROM provisioning.voip_peer_hosts;
|
|
|
|
INSERT INTO lcr_rule_target (lcr_id, rule_id, gw_id, priority, weight)
|
|
SELECT rule.lcr_id, rule.id, gw.id, vpg.priority, vph.weight
|
|
FROM kamailio.lcr_rule rule
|
|
INNER JOIN provisioning.voip_peer_groups vpg ON vpg.id = rule.group_id
|
|
INNER JOIN provisioning.voip_peer_hosts vph ON vpg.id = vph.group_id
|
|
INNER JOIN lcr_gw gw ON gw.ip_addr = vph.ip AND gw.lcr_id = 1;
|
|
|
|
DROP table lcr;
|
|
DROP table gw;
|
|
|