fix admin setting issue (#168)

* feat: add protocol to sip-gateways

* add tls/srtp options

* fix sql

* update db script has new changes

---------

Co-authored-by: Dave Horton <daveh@beachdognet.com>

db/jambones-sql.sql

@@ -1,5 +1,4 @@
 /* SQLEditor (MySQL (2))*/
-
 SET FOREIGN_KEY_CHECKS=0;
 
 DROP TABLE IF EXISTS account_static_ips;
@@ -145,7 +144,7 @@ regex VARCHAR(32) NOT NULL COMMENT 'regex-based pattern match against dialed num
 description VARCHAR(1024),
 priority INTEGER NOT NULL COMMENT 'lower priority routes are attempted first',
 PRIMARY KEY (lcr_route_sid)
-) COMMENT='An ordered list of  digit patterns in an LCR table.  The pat';
+) COMMENT='An ordered list of  digit patterns in an LCR table.  The patterns are tested in sequence until one matches';
 
 CREATE TABLE lcr
 (
@@ -156,7 +155,7 @@ default_carrier_set_entry_sid CHAR(36) COMMENT 'default carrier/route to use whe
 service_provider_sid CHAR(36),
 account_sid CHAR(36),
 PRIMARY KEY (lcr_sid)
-) COMMENT='An LCR (least cost routing) table that is used by a service ';
+) COMMENT='An LCR (least cost routing) table that is used by a service provider or account to make decisions about routing outbound calls when multiple carriers are available.';
 
 CREATE TABLE password_settings
 (
@@ -430,6 +429,7 @@ inbound BOOLEAN NOT NULL COMMENT 'if true, whitelist this IP to allow inbound ca
 outbound BOOLEAN NOT NULL COMMENT 'if true, include in least-cost routing when placing calls to the PSTN',
 voip_carrier_sid CHAR(36) NOT NULL,
 is_active BOOLEAN NOT NULL DEFAULT 1,
+protocol ENUM('udp','tcp','tls', 'tls/srtp') DEFAULT 'udp' COMMENT 'Outbound call protocol',
 PRIMARY KEY (sip_gateway_sid)
 ) COMMENT='A whitelisted sip gateway used for origination/termination';
 

db/upgrade-jambonz-db.js

@@ -89,6 +89,7 @@ const sql = {
     'ALTER TABLE `users` ADD COLUMN `is_active` BOOLEAN NOT NULL default true',
   ],
   8003: [
+    'SET FOREIGN_KEY_CHECKS=0',
     'ALTER TABLE `voip_carriers` ADD COLUMN `register_status` VARCHAR(4096)',
     'ALTER TABLE `sbc_addresses` ADD COLUMN `last_updated` DATETIME',
     'ALTER TABLE `sbc_addresses` ADD COLUMN `tls_port` INTEGER',
@@ -99,6 +100,44 @@ const sql = {
     sip_domain_name VARCHAR(255),
     monitoring_domain_name VARCHAR(255)
     )`,
+    'DROP TABLE IF EXISTS `lcr_routes`',
+    'DROP TABLE IF EXISTS `lcr_carrier_set_entry`',
+    `CREATE TABLE lcr_routes
+    (
+    lcr_route_sid CHAR(36),
+    lcr_sid CHAR(36) NOT NULL,
+    regex VARCHAR(32) NOT NULL COMMENT 'regex-based pattern match against dialed number, used for LCR routing of PSTN calls',
+    description VARCHAR(1024),
+    priority INTEGER NOT NULL COMMENT 'lower priority routes are attempted first',
+    PRIMARY KEY (lcr_route_sid)
+    )`,
+    `CREATE TABLE lcr
+    (
+    lcr_sid CHAR(36) NOT NULL UNIQUE ,
+    name VARCHAR(64) COMMENT 'User-assigned name for this LCR table',
+    is_active BOOLEAN NOT NULL DEFAULT 1,
+    default_carrier_set_entry_sid CHAR(36) COMMENT 'default carrier/route to use when no digit match based results are found.',
+    service_provider_sid CHAR(36),
+    account_sid CHAR(36),
+    PRIMARY KEY (lcr_sid)
+    )`,
+    `CREATE TABLE lcr_carrier_set_entry
+    (
+    lcr_carrier_set_entry_sid CHAR(36),
+    workload INTEGER NOT NULL DEFAULT 1 COMMENT 'represents a proportion of traffic to send through the associated carrier; can be used for load balancing traffic across carriers with a common priority for a destination',
+    lcr_route_sid CHAR(36) NOT NULL,
+    voip_carrier_sid CHAR(36) NOT NULL,
+    priority INTEGER NOT NULL DEFAULT 0 COMMENT 'lower priority carriers are attempted first',
+    PRIMARY KEY (lcr_carrier_set_entry_sid)
+    )`,
+    'CREATE INDEX lcr_sid_idx ON lcr_routes (lcr_sid)',
+    'ALTER TABLE lcr_routes ADD FOREIGN KEY lcr_sid_idxfk (lcr_sid) REFERENCES lcr (lcr_sid)',
+    'CREATE INDEX lcr_sid_idx ON lcr (lcr_sid)',
+    'ALTER TABLE lcr ADD FOREIGN KEY default_carrier_set_entry_sid_idxfk (default_carrier_set_entry_sid) REFERENCES lcr_carrier_set_entry (lcr_carrier_set_entry_sid)',
+    'CREATE INDEX service_provider_sid_idx ON lcr (service_provider_sid)',
+    'CREATE INDEX account_sid_idx ON lcr (account_sid)',
+    'ALTER TABLE lcr_carrier_set_entry ADD FOREIGN KEY lcr_route_sid_idxfk (lcr_route_sid) REFERENCES lcr_routes (lcr_route_sid)',
+    'ALTER TABLE lcr_carrier_set_entry ADD FOREIGN KEY voip_carrier_sid_idxfk_3 (voip_carrier_sid) REFERENCES voip_carriers (voip_carrier_sid)',
   ]
 };
 

lib/models/sip-gateway.js

@@ -58,6 +58,10 @@ SipGateway.fields = [
   {
     name: 'application_sid',
     type: 'string'
+  },
+  {
+    name: 'protocol',
+    type: 'string'
   }
 ];
 

lib/routes/api/system-information.js

@@ -8,7 +8,7 @@ router.post('/', async(req, res) => {
 
 router.get('/', async(req, res) => {
   const [sysInfo] = await SystemInformation.retrieveAll();
-  res.status(200).json(sysInfo);
+  res.status(200).json(sysInfo || {});
 });
 
 module.exports = router;

package-lock.json

@@ -1,12 +1,12 @@
 {
   "name": "jambonz-api-server",
-  "version": "v0.8.2",
+  "version": "0.8.3",
   "lockfileVersion": 2,
   "requires": true,
   "packages": {
     "": {
       "name": "jambonz-api-server",
-      "version": "v0.8.2",
+      "version": "0.8.3",
       "license": "MIT",
       "dependencies": {
         "@aws-sdk/client-transcribe": "^3.290.0",

package.json

@@ -1,6 +1,6 @@
 {
   "name": "jambonz-api-server",
-  "version": "v0.8.2",
+  "version": "0.8.3",
   "description": "",
   "main": "app.js",
   "scripts": {

test/sip-gateways.js

@@ -27,7 +27,8 @@ test('sip gateway tests', async(t) => {
         ipv4: '192.168.1.1',
         netmask: 32,
         inbound: true,
-        outbound: true
+        outbound: true,
+        protocol: 'tcp'
       }
     });
     t.ok(result.statusCode === 201, 'successfully created sip gateway ');
@@ -48,6 +49,7 @@ test('sip gateway tests', async(t) => {
     });
     //console.log(`result: ${JSON.stringify(result)}`);
     t.ok(result.ipv4 === '192.168.1.1' , 'successfully retrieved voip carrier by sid');
+    t.ok(result.protocol === 'tcp' , 'successfully retrieved voip carrier by sid');
 
 
     /* update sip gateway */
@@ -58,7 +60,8 @@ test('sip gateway tests', async(t) => {
       body: {
         port: 5061,
         netmask:24,
-        outbound: false
+        outbound: false,
+        protocol: 'udp'
       }
     });
     t.ok(result.statusCode === 204, 'successfully updated voip carrier');