1-- 2-- sccpdevice 3-- 4-- Add/Remove columns if needed, check sccp.conf.annotated for valid column entries 5-- 6-- Note: For multivalue entries like buttons / addons / deny|permit / disallow|allow / mailbox the separator to use is ';' 7-- 8DROP TABLE IF EXISTS sccpdevice; 9CREATE TABLE sccpdevice ( 10 type varchar(15) default NULL, 11 addon varchar(45) default NULL, -- multiple entries should be separated using ';' 12 description varchar(45) default NULL, 13 tzoffset varchar(5) default NULL, 14 transfer varchar(5) default NULL, 15 cfwdall varchar(5) default 'on', 16 cfwdbusy varchar(5) default 'on', 17 imageversion varchar(31) default NULL, 18 deny varchar(100) default '0.0.0.0/0.0.0.0', 19 permit varchar(100) default 'internal', 20 dndFeature varchar(5) default 'on', 21 directrtp varchar(3) default 'off', 22 earlyrtp varchar(10) default 'progress', 23 mwilamp varchar(5) default 'on', 24 mwioncall varchar(5) default 'on', 25 private varchar(5) default NULL, 26 privacy varchar(100) default 'full', 27 nat varchar(7) NULL default 'auto', 28 softkeyset varchar(100) NULL default NULL, 29 audio_tos varchar(11) default NULL, 30 audio_cos varchar(1) default NULL, 31 video_tos varchar(11) default NULL, 32 video_cos varchar(1) default NULL, 33 conf_allow varchar(3) NULL default 'on', 34 conf_play_general_announce varchar(3) NULL default 'on', 35 conf_play_part_announce varchar(3) NULL default 'on', 36 conf_mute_on_entry varchar(3) NULL default 'off', 37 conf_music_on_hold_class varchar(80) NULL default 'default', 38 conf_show_conflist varchar(3) NULL default 'on', 39 force_dtmfmode varchar(8) NULL default 'auto', 40 backgroundImage varchar(255) DEFAULT NULL, 41 backgroundThumbnail varchar(255) DEFAULT NULL, 42 ringtone varchar(255) DEFAULT NULL, 43 setvar varchar(100) default NULL, 44 name varchar(15) NOT NULL default '', 45 PRIMARY KEY (name) 46); 47 48-- 49-- sccpline 50-- 51-- Add/Remove columns if needed, check sccp.conf.annotated for valid column entries 52-- 53CREATE SEQUENCE sccpline_id_seq; 54SELECT setval('sccpline_id_seq', 1); 55 56DROP TABLE IF EXISTS sccpline; 57CREATE TABLE sccpline ( 58 id smallint DEFAULT NEXTVAL('sccpline_id_seq') UNIQUE, 59 pin varchar(7) default NULL, 60 label varchar(45) default NULL, 61 description varchar(45) default NULL, 62 context varchar(45) default NULL, 63 incominglimit varchar(45) default NULL, 64 transfer varchar(45) default NULL, 65 mailbox varchar(45) default NULL, 66 vmnum varchar(45) default NULL, 67 cid_name varchar(45) default NULL, 68 cid_num varchar(45) default NULL, 69 disallow varchar(45) default NULL, 70 allow varchar(45) default NULL, 71 trnsfvm varchar(45) default NULL, 72 secondary_dialtone_digits varchar(45) default NULL, 73 secondary_dialtone_tone varchar(45) default NULL, 74 musicclass varchar(45) default NULL, 75 language varchar(45) default NULL, 76 accountcode varchar(45) default NULL, 77 echocancel varchar(45) default NULL, 78 silencesuppression varchar(45) default NULL, 79 callgroup varchar(45) default NULL, 80 pickupgroup varchar(45) default NULL, 81 namedcallgroup varchar(100) default NULL, 82 namedpickupgroup varchar(100) default NULL, 83 directed_pickup varchar(5) default 'yes', 84 directed_pickup_context varchar(100) default NULL, 85 pickup_modeanswer varchar(5) default 'yes', 86 dnd varchar(7) default 'reject', 87 videomode varchar(5) default 'auto', 88 amaflags varchar(45) default NULL, 89 regexten character varying(20) DEFAULT NULL, 90 setvar varchar(50) default NULL, 91 name varchar(40) NOT NULL, 92 PRIMARY KEY (name) 93); 94 95-- 96-- Enum buttontype 97-- 98DROP TYPE IF EXISTS buttontype; 99CREATE TYPE buttontype AS ENUM ('line','speeddial','service','feature','empty'); 100 101-- 102-- buttonconfig table 103-- foreign constrainst: 104-- device -> sccpdevice.name 105-- type -> buttontype enum 106-- name -> if type=='line' then sccpline.name 107-- else free field 108-- unique constraints: 109-- device, instance, type 110-- 111DROP TABLE IF EXISTS buttonconfig; 112CREATE TABLE buttonconfig( 113 device character varying(15) NOT NULL, 114 instance integer NOT NULL DEFAULT 0, 115 "type" buttontype NOT NULL DEFAULT 'line'::buttontype, 116 "name" character varying(36) DEFAULT NULL::character varying, 117 options character varying(100) DEFAULT NULL::character varying, 118 CONSTRAINT buttonconfig_pkey PRIMARY KEY (device, instance), 119 CONSTRAINT devicename FOREIGN KEY (device) 120 REFERENCES sccpdevice ("name") MATCH SIMPLE 121 ON UPDATE NO ACTION ON DELETE NO ACTION 122 UNIQUE (device, instance) 123); 124 125-- 126-- concatenation/aggregation function 127-- used by sccpdeviceconfig view 128-- 129DROP AGGREGATE IF EXISTS textcat_column("text"); 130CREATE AGGREGATE textcat_column("text") ( 131 SFUNC=textcat, 132 STYPE=text 133); 134 135-- 136-- sccpdeviceconfig view 137-- 138-- combines sccpdevice and buttonconfig on buttonconfig.device=sccpdevice.name to 139-- produce a complete chan-sccp-b device entry including multiple buttons seperated by comma's 140-- 141-- When altering sccpdevice or buttonconfig, this view needs to be dropped and recreated afterwards 142-- 143CREATE OR REPLACE VIEW sccpdeviceconfig AS 144 SELECT 145 (SELECT textcat_column(bc.type || ',' || bc.name || COALESCE(',' || bc.options, '') || ';') FROM (SELECT * FROM buttonconfig WHERE device=sccpdevice.name ORDER BY instance) bc ) as button, 146 sccpdevice.* 147 FROM sccpdevice 148; 149