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.
50 lines
1.9 KiB
50 lines
1.9 KiB
USE provisioning;
|
|
|
|
DROP TRIGGER enum_set_default;
|
|
|
|
DELIMITER |
|
|
|
|
-- fix trigger enum_set_default introduced with r9086
|
|
-- to correctly update voip_*_preferences with default values
|
|
-- for preferences that cover more than one in (usr|dom|peer)_preferences.
|
|
|
|
-- update preferences for existing domains / peer hosts /subscribers
|
|
-- when enum preferences are created that use a default value
|
|
-- (does not care about reseller_id)
|
|
CREATE TRIGGER enum_set_default AFTER INSERT ON voip_preferences_enum
|
|
FOR EACH ROW BEGIN
|
|
IF (NEW.dom_pref=1 AND NEW.default_val = 1 AND NEW.value IS NOT NULL) THEN
|
|
INSERT into voip_dom_preferences (domain_id, attribute_id, value)
|
|
SELECT id, NEW.preference_id, NEW.value
|
|
FROM voip_domains;
|
|
END IF;
|
|
IF (NEW.peer_pref=1 AND NEW.default_val = 1 AND NEW.value IS NOT NULL) THEN
|
|
INSERT into voip_peer_preferences (peer_host_id, attribute_id, value)
|
|
SELECT id, NEW.preference_id, NEW.value
|
|
FROM voip_peer_hosts;
|
|
END IF;
|
|
IF (NEW.usr_pref=1 AND NEW.default_val = 1 AND NEW.value IS NOT NULL) THEN
|
|
INSERT into voip_usr_preferences (subscriber_id, attribute_id, value)
|
|
SELECT id, NEW.preference_id, NEW.value
|
|
FROM voip_subscribers;
|
|
END IF;
|
|
END |
|
|
|
|
-- fix trigger enum_update introduced with r9149 to only update values of
|
|
-- enum preferences in voip_(usr|dom|peer)_preferences that where actually
|
|
-- set to that value (previously all preferences were updated).
|
|
|
|
DROP TRIGGER enum_update;
|
|
|
|
CREATE TRIGGER enum_update AFTER UPDATE ON voip_preferences_enum
|
|
FOR EACH ROW BEGIN
|
|
UPDATE voip_usr_preferences SET value=NEW.value
|
|
WHERE attribute_id=NEW.preference_id AND value=OLD.value;
|
|
UPDATE voip_dom_preferences SET value=NEW.value
|
|
WHERE attribute_id=NEW.preference_id AND value=OLD.value;
|
|
UPDATE voip_peer_preferences SET value=NEW.value
|
|
WHERE attribute_id=NEW.preference_id AND value=OLD.value;
|
|
END |
|
|
|
|
DELIMITER ;
|