1-- 2-- Note: For multivalue entries like buttons / addons / deny|permit / disallow|allow / mailbox the separator to use is ';' 3-- 4-- Table with line-configuration 5-- 6CREATE TABLE IF NOT EXISTS `sccpdevice` ( 7 `type` varchar(15) default NULL, 8 `addon` varchar(45) default NULL, -- multiple entries should be separated using ';' 9 `description` varchar(45) default NULL, 10 `tzoffset` varchar(5) default NULL, 11 `transfer` varchar(5) default 'on', 12 `cfwdall` varchar(5) default 'on', 13 `cfwdbusy` varchar(5) default 'on', 14 `imageversion` varchar(31) default NULL, 15 `deny` varchar(100) default '0.0.0.0/0.0.0.0', 16 `permit` varchar(100) default 'internal', 17 `dndFeature` varchar(5) default 'on', 18 `directrtp` varchar(3) default 'off', 19 `earlyrtp` varchar(10) default 'progress', 20 `mwilamp` varchar(5) default 'on', 21 `mwioncall` varchar(5) default 'off', 22 `private` varchar(5) default NULL, 23 `privacy` varchar(100) default 'full', 24 `nat` varchar(7) default 'auto', 25 `softkeyset` varchar(100) default '', 26 `audio_tos` varchar(11) default NULL, 27 `audio_cos` varchar(1) default NULL, 28 `video_tos` varchar(11) default NULL, 29 `video_cos` varchar(1) default NULL, 30 `conf_allow` varchar(3) default 'on', 31 `conf_play_general_announce` varchar(3) default 'on', 32 `conf_play_part_announce` varchar(3) default 'on', 33 `conf_mute_on_entry` varchar(3) default 'off', 34 `conf_music_on_hold_class` varchar(80) default 'default', 35 `conf_show_conflist` varchar(3) default 'on', 36 `force_dtmfmode` varchar(8) default 'auto', 37 `setvar` varchar(100) default NULL, 38 `backgroundImage` varchar(255) DEFAULT NULL, 39 `backgroundThumbnail` varchar(255) DEFAULT NULL, 40 `ringtone` varchar(255) DEFAULT NULL, 41 `name` varchar(15) NOT NULL default '', 42 PRIMARY KEY (`name`) 43) ENGINE=INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 44 45-- 46-- Table with device-configuration 47-- 48CREATE TABLE IF NOT EXISTS `sccpline` ( 49 `id` MEDIUMINT NOT NULL AUTO_INCREMENT, 50 `pin` varchar(7) default NULL, 51 `label` varchar(45) default NULL, 52 `description` varchar(45) default NULL, 53 `context` varchar(45) default NULL, 54 `incominglimit` varchar(45) default NULL, 55 `transfer` varchar(45) default NULL, 56 `mailbox` varchar(45) default NULL, 57 `vmnum` varchar(45) default NULL, 58 `cid_name` varchar(45) default NULL, 59 `cid_num` varchar(45) default NULL, 60 `disallow` varchar(255) DEFAULT NULL, 61 `allow` varchar(255) DEFAULT NULL, 62 `trnsfvm` varchar(45) default NULL, 63 `secondary_dialtone_digits` varchar(45) default NULL, 64 `secondary_dialtone_tone` varchar(45) default NULL, 65 `musicclass` varchar(45) default NULL, 66 `language` varchar(45) default NULL, 67 `accountcode` varchar(45) default NULL, 68 `echocancel` varchar(45) default NULL, 69 `silencesuppression` varchar(45) default NULL, 70 `callgroup` varchar(45) default NULL, 71 `pickupgroup` varchar(45) default NULL, 72 `namedcallgroup` varchar(100) default NULL, 73 `namedpickupgroup` varchar(100) default NULL, 74 `directed_pickup` varchar(5) default 'yes', 75 `directed_pickup_context` varchar(100) default NULL, 76 `pickup_modeanswer` varchar(5) default 'yes', 77 `amaflags` varchar(45) default NULL, 78 `dnd` varchar(7) default 'reject', 79 `videomode` varchar(5) default 'auto', 80 `setvar` varchar(50) default NULL, 81 `name` varchar(40) NOT NULL default '', 82 PRIMARY KEY (`name`), 83 UNIQUE (`id`) 84) ENGINE=INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 85 86-- 87-- Table with button-configuration for device 88-- 89-- foreign constrainst: 90-- device -> sccpdevice.name 91-- type -> buttontype enum 92-- name -> if type=='line' then sccpline.name 93-- else free field 94-- unique constraints: 95-- device, instance, type 96-- 97CREATE TABLE IF NOT EXISTS `buttonconfig` ( 98 `device` varchar(15) NOT NULL default '', 99 `instance` tinyint(4) NOT NULL default '0', 100 `type` enum('line','speeddial','service','feature','empty') NOT NULL default 'line', 101 `name` varchar(36) default NULL, 102 `options` varchar(100) default NULL, 103 PRIMARY KEY (`device`,`instance`,`type`), 104 KEY `device` (`device`), 105 FOREIGN KEY (device) REFERENCES sccpdevice(name) ON DELETE CASCADE ON UPDATE CASCADE 106) ENGINE=INNODB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 107 108 109-- 110-- trigger to check buttonconfig sccpline foreign key constrainst: 111-- if type=='line' then check name against sccpline.name column 112-- else free field 113-- 114DROP TRIGGER IF EXISTS trg_buttonconfig; 115 116DELIMITER $$ 117CREATE TRIGGER trg_buttonconfig BEFORE INSERT ON buttonconfig 118FOR EACH ROW 119BEGIN 120 IF NEW.`type` = 'line' THEN 121 IF (SELECT COUNT(*) FROM `sccpline` WHERE `sccpline`.`name` = NEW.`name`) = 0 122 THEN 123 UPDATE `Foreign key contraint violated: line does not exist in sccpline` SET x=1; 124 END IF; 125 END IF; 126END$$ 127DELIMITER ; 128 129-- 130-- View for merging device and button configuration 131-- 132-- combines sccpdevice and buttonconfig on buttonconfig.device=sccpdevice.name to 133-- produce a complete chan-sccp-b device entry including multiple buttons seperated by comma's 134-- 135-- When altering sccpdevice or buttonconfig, this view needs to be dropped and recreated afterwards 136-- 137-- 138-- group_concat_max_len = 2048; in my.cnf 139-- 140 141CREATE OR REPLACE 142ALGORITHM = MERGE 143VIEW sccpdeviceconfig AS 144 SELECT GROUP_CONCAT( CONCAT_WS( ',', buttonconfig.type, buttonconfig.name, buttonconfig.options ) 145 ORDER BY instance ASC 146 SEPARATOR ';' ) AS button, sccpdevice.* 147 FROM sccpdevice 148 LEFT JOIN buttonconfig ON ( buttonconfig.device = sccpdevice.name ) 149GROUP BY sccpdevice.name; 150 151 152 153