Подключение к MS SQL через ODBC

Астериск может записывать информацию в базу данных. Для этого нужно установить драйвер для доступа к нужной БД и настроить unixODBC.

Установка Microsoft ODBC Driver for SQL Server

На сайте Майкрософт есть подробная инструкция по установке Microsoft ODBC драйвера для SQL. Ниже приведена инструкция по установке этого драйвера на Debian 9.

Сначала нужно установить пакеты, используемые для установки драйвера по инструкции от Майкрософт:

sudo apt-get install curl

sudo apt-get install dirmngr apt-transport-https   — возможно также понадобятся (для команды gpg)

Дальше собственно сама процедура установки драйвера: добавить публичный GnuPG-ключ репозитория Microsoft, добавить репозиторий Microsoft в список репозиториев, обновить репозиторий:

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/debian/9/prod.list > /etc/apt/sources.list.d/mssql-release.list
exit
sudo apt-get update

sudo ACCEPT_EULA=Y apt-get install msodbcsql17

Драйвер установлен.

Версия установленного драйвера: ODBC Driver 17 for SQL Server

Посмотреть информацию о настройках ODBC:

odbcinst -j

Посмотреть список подключенных драйверов:

odbcinst -q -d

Настройка ODBC: создание DSN для подключения к конкретному MSSQL-серверу

Для подключения к источнику данных, т.е. к конкретному MSSQL-серверу (или даже к конкретной базе данных на этом сервере) нужно в настройки ODBC добавить имя (и параметры) этого источника данных (т.е. DSN - data source name).

sudo nano /etc/odbc.ini

Добавить в файл odbc.ini новую секцию:

[имя источника данных]   — выбрать произвольное имя (например, asterisk-connector)

Driver   — указать драйвер для подключения к источнику данных

Server   — имя сервера

Database   — имя базы данных (если не указать, то после подключения к серверу нужно будет выбрать БД командой «USE имя_БД»)

Пример:

[asterisk-connector]
Driver = ODBC Driver 17 for SQL Server
Description = Connection to MSSQL Database for Asterisk
Server = dbserv1
Database = Somedb

Проверки:

Посмотреть список подключенных источников данных (DSN):

odbcinst -q -s

Проверка подключениея через созданный DSN:

isql -v имя_DSN логин пароль

Например:

isql -v asterisk-connector asterisk pa$$w0rd

Появится приглашение SQL>

Простой тест:

select 1

Пример результата:

SQL> select 1
+------------+
|            |
+------------+
| 1          |
+------------+
SQLRowCount returns 0
1 rows fetched
SQL>

Выбрать базу данных:

USE Somedb

Пример результата:

SQL> USE Somedb
[01000][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to 'Somedb'.
[ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO
SQLRowCount returns 0
SQL>

Сделать запрос записей из какой-нибудь таблицы:

SELECT * from [Calls List]

Пример результата:

SQL> SELECT * from [Calls List]
+-----------------+------------+-------------------------------+-------------------------------+-------------------------------+------------+------------------------+
| timestamp       | Entry No_  | External Phone                | Internal Phone                | Call Id                       | Duration   | Call DateTime          |
+-----------------+------------+-------------------------------+-------------------------------+-------------------------------+------------+------------------------+
| 0000000036710FD1| 1          | +73431000067                  | 1234                          | 78346332423                   | 12         | 2019-03-07 16:24:12.000|
+-----------------+------------+-------------------------------+-------------------------------+-------------------------------+------------+------------------------+
SQLRowCount returns 0
1 rows fetched
SQL>

Настройка соединения Астериска c базой данных через unixODBC

Соединение Астериска c различными базами данных через unixODBC настраивается в файле res_odbc.conf.

Подробнее о структуре файла можно прочитать на сайте asterisk-pbx.ru

Отредактировать файл res_odbc.conf:

sudo nano /etc/asterisk/res_odbc.conf

Включить (enabled => yes) секцию [sqlserver], параметры которой настроены для подключения к MSSQL, и отредактировать имя источника данных (DSN), логин и пароль:

[sqlserver]
enabled => yes
dsn => asterisk-connector
username => asterisk
password => pa$$w0rd

Сборка Астериска с поддержкой ODBC

Если в Астериске отсутствует поддержка ODBC, нужно добавить компоненты ODBC с помощью menuselect и пересобрать Астериск.

Для того, чтобы компоненты ODBC стали доступными для выбора, должны быть установлены все необходимые пакеты, в т.ч. unixodbc и unixodbc-dev:

sudo apt-get install unixodbc unixodbc-dev

Пересборка Астериска:

cd ~/src/asterisk-complete/asterisk/asterisk-15.*/
make distclean
./configure
make menuselect
sudo make install

Если в menuselect нужные компоненты недоступны, и вручную никак не удаётся подобрать нужные пакеты:

sudo apt-get install dpkg
cd ~/src/asterisk-complete/asterisk/asterisk-15.*/
sudo ./contrib/scripts/install_prereq install

Ещё совет из книги O'Reilly «Asterisk: The Definitive Guide» (с.38):

sudo apt-get install build-essential subversion libncurses5-dev libssl-dev libxml2-dev libsqlite3-dev uuid-dev vim-nox

Удалить лишние пакеты:

sudo apt autoremove

Проверка подключения астериска к ODBC:

/usr/sbin/asterisk -rx "odbc show"

или из консоли:

/usr/sbin/asterisk -rvvvvv
*CLI> odbc show

Пример вывода:

ODBC DSN Settings
-----------------

  Name:   sqlserver
  DSN:    asterisk-connector
    Number of active connections: 1 (out of 5)

Настройка SQL-запросов

SQL-запросы из диалплана выполняются с помощью обращений к функциям, описанным в файле func_odbc.conf. Функции содержат SQL-запросы.

Описание функций в func_odbc.conf

Открыть файл func_odbc.conf в редакторе nano и добавить в него функции с нужными нам SQL-запросами:

cd /etc/asterisk/
mv func_odbc.conf func_odbc.conf.sample
touch func_odbc.conf
nano func_odbc.conf

Пример файла func_odbc.conf:

[NEWCALL]
dsn=sqlserver
writesql=INSERT INTO [Calls List] ([External Phone],[Internal Phone],[Duration],[Call DateTime],[Call Id]) VALUES (N'${VAL1}', N'${VAL2}', 0, '${VAL3}', '${VAL4}')

[CALLANSWERED]
dsn=sqlserver
writesql=UPDATE [Calls List] SET [Internal Phone] = N'${VAL1}' WHERE [Call Id] = '${ARG1}'

[CALLFINISHED]
dsn=sqlserver
writesql=UPDATE [Calls List] SET [Duration] = ${VAL1} WHERE [Call Id] = '${ARG1}'

Для каждой функции обязательно указать параметр dsn=имя-коннектора, где имя-коннектора — это имя секции в файле /etc/asterisk/res_odbc.conf с параметрами подключения к нужной БД.

Функции могут содержать два типа запросов (один или оба): readsql (для SELECT) и writesql (для INSERT или UPDATE).

Параметры ${ARG1} (аргументы) и ${VAL1} (значения) передаются из вызываемых в диалплане функций.

В диалплане функции должны называться «ODBC_имя-функции», где имя-функции — это название секции в файле func_odbc.conf

После редактирования файла перезапустить модуль func_odbc:

module reload func_odbc.so

Вызов функций в диалплане

Вызов функции типа readsql:

Set(ODBC_имя-функции(аргумент1,аргумент2,...))

Вызов функции типа writesql:

Set(ODBC_имя-функции(аргумент1,аргумент2,...)=значение1,значение2,...)

Пример вызова функций (фрагмент диалплана):

[ivr_call2all]
exten => 73431000000,1,Goto(1009,1)
exten => 1009,1,NoOp(Call at ${STRFTIME(${EPOCH},,%d.%m.%Y %H:%M:%S)} from ${CALLERID(num)} to ${EXTEN} (3 numbers) with Music on hold)

 same =>      n,Set(__CallID=${UNIQUEID})
 same =>      n,Set(ODBC_NEWCALL()=${CALLERID(num)},NoAnswer,${STRFTIME(${EPOCH},,%Y-%m-%d %H:%M:%S.000)},${CallID})
 same =>      n,Dial(PJSIP/1001&PJSIP/1002&PJSIP/1003,,tm(native-random)U(answer-script))
 same =>      n,Playback(telephone-number&vm-isunavail)
 same =>      n,Hangup()
 exten =>   h,1,Set(ODBC_CALLFINISHED(${CallID})=${ANSWEREDTIME})

[answer-script]
exten =>    s,1,Set(ODBC_CALLANSWERED(${CallID})=${DIALEDPEERNUMBER})
 same =>      n,Return

Комментарии:

ODBC_NEWCALL()=... вызывается при поступлении вызова: в таблице создаётся новая запись.

Указываются А-номер, B-номер (т.к. ещё никто не ответил, вместо номера в таблицу записывается строка 'NoAnswer'), текущие дата-время, идентификатор вызова ${UNIQUEID} для плеча.
Значение ${UNIQUEID} предварительно сохраняется в переменную ${CallID}, т.к. когда оператор ответит на вызов, создастся новое плечо с другим ${UNIQUEID}, а нам в таблице по идентификатору нужно идентифицировать весь вызов, а не каждое плечо по отдельности.
При присвоении значения переменной CallID перед ней пишутся два подчеркивания, чтобы переменная не очистилась после завершения текущего плеча вызова (при ответе абонента): Set(__CallID=${UNIQUEID})

ODBC_CALLANSWERED(${CallID})=${DIALEDPEERNUMBER} вызывается в момент ответа абонента благодаря опции U(имя-контекста) в функции Dial():

При ответе абонента выполнение диалплана переходит в контекст [имя-контекста], в котором вызывается функция ODBC_CALLANSWERED и выполняется возврат Return.
Функция ODBC_CALLANSWERED ищет запись в таблице по идентификатору ${CallID} и обновляет поле с B-номером значением ${DIALEDPEERNUMBER}, раньше это сделать нельзя, т.к. значение ${DIALEDPEERNUMBER} становится известным только в момент ответа.

ODBC_CALLFINISHED(${CallID})=${ANSWEREDTIME} вызывается после завершения работы команды Dial(), т.к. команда Dial() возвращает параметры, среди них ${ANSWEREDTIME} (длительность разговора).

После завершения вызова (т.е. после завершения работы команды Dial()) выполнение диалплана переходит к экстеншену h, где вызывается функция ODBC_CALLFINISHED.
Функция ODBC_CALLFINISHED ищет запись в таблице по идентификатору ${CallID} и обновляет поле Duration, в которое записывает длительность разговора (значение переменной ${ANSWEREDTIME})

Отладка

Чтобы правильно написать запрос SQL в файле func_odbc.conf и правильно вызвать функцию ODBC_xxx в диалплане, можно сначала выполнить sql-запрос в isql:

isql -v asterisk-connector asterisk pa$$w0rd

При выполнении sql-запроса будут выводиться сообщения, в т.ч. об ошибках, которые помогут найти и исправить ошибку в sql-запросе.

Пример содержимого таблицы Somedb, в которой создаются записи о вызовах:

SQL> SELECT * from [Calls List]
+-----------------+-------------------------------+-------------------------------+------------+------------------------+-------------------------------+
| timestamp       | External Phone                | Internal Phone                | Duration   | Call DateTime          | Call Id                       |
+-----------------+-------------------------------+-------------------------------+------------+------------------------+-------------------------------+
| 0000000036711014| 1501                          | 1001                          | 17         | 2019-03-13 18:01:28.000| 1552482088.168                |
| 0000000036711015| 1002                          | NoAnswer                      | 0          | 2019-03-13 18:02:14.000| 1552482134.173                |
| 0000000036711018| 1002                          | 1001                          | 13         | 2019-03-13 18:02:33.000| 1552482153.178                |
| 000000003671101B| 89120000000                   | 1002                          | 21         | 2019-03-13 18:03:17.000| 1552482197.183                |
+-----------------+-------------------------------+-------------------------------+------------+------------------------+-------------------------------+
SQLRowCount returns 0
4 rows fetched
SQL>

SQL-запросы прямо в диалплане

Как это сделать, можно прочитать здесь