2 * this is for users how like to sepatet device and button configuration
3 * You have to change the table names to:
5 * sccpdevices -> sccpdeviceconfig
6 * sccplines -> sccpline
11 -- Table with line-configuration
13 CREATE TABLE sccpdevice (
14 type varchar(45) DEFAULT NULL,
15 addon
varchar(45) DEFAULT NULL,
16 description
varchar(45) DEFAULT NULL,
17 tzoffset
varchar(5) DEFAULT NULL,
18 transfer
varchar(5) DEFAULT 'on',
19 cfwdall
varchar(5) DEFAULT 'on',
20 cfwdbusy
varchar(5) DEFAULT 'on',
21 imageversion
varchar(45) DEFAULT NULL,
22 deny
varchar(45) DEFAULT NULL,
23 permit
varchar(45) DEFAULT NULL,
24 dndFeature
varchar(5) DEFAULT 'on',
25 directrtp
varchar(3) DEFAULT 'off',
26 earlyrtp
varchar(8) DEFAULT 'off',
27 mwilamp
varchar(5) DEFAULT 'on',
28 mwioncall
varchar(5) DEFAULT 'off',
29 pickupexten
varchar(5) DEFAULT 'on',
30 pickupcontext
varchar(100) DEFAULT '',
31 pickupmodeanswer
varchar(5) DEFAULT 'on',
32 private
varchar(5) DEFAULT 'off',
33 privacy
varchar(100) DEFAULT 'full',
34 nat
varchar(4) DEFAULT 'auto',
35 softkeyset
varchar(100) DEFAULT '',
36 audio_tos
varchar(11) DEFAULT NULL,
37 audio_cos
varchar(1) DEFAULT NULL,
38 video_tos
varchar(11) DEFAULT NULL,
39 video_cos
varchar(1) DEFAULT NULL,
40 conf_allow
varchar(3) DEFAULT 'on',
41 conf_play_general_announce
varchar(3) DEFAULT 'on',
42 conf_play_part_announce
varchar(3) DEFAULT 'on',
43 conf_mute_on_entry
varchar(3) DEFAULT 'off',
44 conf_music_on_hold_class
varchar(80) DEFAULT 'default',
45 conf_show_conflist
varchar(3) DEFAULT 'on',
46 backgroundImage
varchar(255) DEFAULT '',
47 ringtone
varchar(255) DEFAULT '',
48 setvar
varchar(100) DEFAULT NULL,
49 disallow
varchar(255) DEFAULT NULL,
50 allow
varchar(255) DEFAULT NULL,
51 name varchar(15) NOT NULL DEFAULT '',
56 -- Table with device-configuration
58 CREATE TABLE sccpline (
59 id varchar(4) DEFAULT NULL,
60 pin
varchar(45) DEFAULT NULL,
61 label
varchar(45) DEFAULT NULL,
62 description
varchar(45) DEFAULT NULL,
63 context
varchar(45) DEFAULT NULL,
64 incominglimit
varchar(45) DEFAULT NULL,
65 transfer
varchar(45) DEFAULT NULL,
66 mailbox
varchar(45) DEFAULT NULL,
67 vmnum
varchar(45) DEFAULT NULL,
68 cid_name
varchar(45) DEFAULT NULL,
69 cid_num
varchar(45) DEFAULT NULL,
70 trnsfvm
varchar(45) DEFAULT NULL,
71 secondary_dialtone_digits
varchar(45) DEFAULT NULL,
72 secondary_dialtone_tone
varchar(45) DEFAULT NULL,
73 musicclass
varchar(45) DEFAULT NULL,
74 language varchar(45) DEFAULT NULL,
75 accountcode
varchar(45) DEFAULT NULL,
76 echocancel
varchar(45) DEFAULT NULL,
77 silencesuppression
varchar(45) DEFAULT NULL,
78 callgroup
varchar(45) DEFAULT NULL,
79 pickupgroup
varchar(45) DEFAULT NULL,
80 namedcallgroup
varchar(45) DEFAULT NULL,
81 namedpickupgroup
varchar(45) DEFAULT NULL,
82 dnd
varchar(7) DEFAULT 'reject',
83 amaflags
varchar(45) DEFAULT NULL,
84 defaultSubscriptionId_number
varchar(5) DEFAULT NULL,
85 setvar
varchar(50) DEFAULT NULL,
86 name varchar(45) NOT NULL DEFAULT '',
90 CREATE TABLE buttontype (
91 type varchar(9) DEFAULT NULL,
95 INSERT INTO buttontype (type) VALUES ('line');
96 INSERT INTO buttontype (type) VALUES ('speeddial');
97 INSERT INTO buttontype (type) VALUES ('service');
98 INSERT INTO buttontype (type) VALUES ('feature');
99 INSERT INTO buttontype (type) VALUES ('empty');
101 -- Table with button-configuration for device
103 CREATE TABLE buttonconfig (
104 device
varchar(15) NOT NULL DEFAULT '',
105 instance tinyint(4) NOT NULL DEFAULT '0',
107 name varchar(36) DEFAULT NULL,
108 options varchar(100) DEFAULT NULL,
109 PRIMARY KEY (device
,instance),
110 FOREIGN KEY (device
) REFERENCES sccpdevice (device
),
111 FOREIGN KEY (type) REFERENCES buttontype (type)
115 -- View for merging device and button configuration
117 CREATE VIEW sccpdeviceconfig
AS
119 group_concat(buttonconfig.
type||
","||buttonconfig.
name||
","||buttonconfig.
options,";") as button
120 FROM buttonconfig
, sccpdevice
121 WHERE buttonconfig.device
=sccpdevice.
name
122 GROUP BY sccpdevice.
name
123 ORDER BY sccpdevice.
name, buttonconfig.
instance;