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