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