net/asterisk-chan-sccp/files/sccp_sqlite3.sql

   1 /*
   2  * this is for users how like to sepatet device and button configuration
   3  * You have to change the table names to:
   4  *
   5  * sccpdevices -> sccpdeviceconfig
   6  * sccplines -> sccpline
   7 */
   8
   9 PRAGMA auto_vacuum=2;
  10 --
  11 -- Table with line-configuration
  12 --
  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 '',
  52   PRIMARY KEY  (name)
  53 );
  54
  55 --
  56 -- Table with device-configuration
  57 --
  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 '',
  87   PRIMARY KEY  (name)
  88 );
  89
  90 CREATE TABLE buttontype (
  91   type                          varchar(9)      DEFAULT NULL,
  92   PRIMARY KEY (type)
  93 );
  94
  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');
 100 --
 101 -- Table with button-configuration for device
 102 --
 103 CREATE TABLE buttonconfig (
 104   device                        varchar(15)     NOT NULL DEFAULT '',
 105   instance                      tinyint(4)      NOT NULL DEFAULT '0',
 106   type                          varchar(9),
 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)
 112 );
 113
 114 --
 115 -- View for merging device and button configuration
 116 --
 117 CREATE VIEW sccpdeviceconfig AS
 118         SELECT  sccpdevice.*,
 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;