Грозы не уходят, дома почти не бываю) Начну о связке MySQL+Asterisk через ODBC коннектор, надеюсь к концу недели закончу.
Не закончил)
Добавил как работать с MySQL напрямую без ODBC.
Для изготовления нам понадобятся.
0. Предпочитаемая База данных.
1. Астериск собранный с unixODBC
2. mysql-connector-odbc
Также можно использовать коннектор к Вашей БД, будь то постгрес или фаерберд)
Если интересно то прошу
Выдержка из Вики, для того чтобы представлять что мы делаем:
В начале 1990 г. существовало несколько поставщиков баз данных, каждый из которых имел собственный интерфейс. Если приложению было необходимо общаться с несколькими источниками данных, для взаимодействия с каждой из баз данных было необходимо написать свой код. Для решения возникшей проблемы Microsoft и ряд других компаний создали стандартный интерфейс для получения и отправки источникам данных различных типов. Этот интерфейс был назван Open Database Connectivity, или открытый механизм взаимодействия с базами данных(ODBC).
Для начала создаем базу
CREATE DATABASE asterisk;
GRANT ALL ON asterisk.* TO adminnn@localhost IDENTIFIED BY 'adminnn'; FLUSH PRIVILEGES;
SET PASSWORD FOR 'adminnn'@'localhost'=PASSWORD('adminnn123');
Проверяем поставили мы unixODBC или нет
#pkg_info | grep unixODBC unixODBC-2.3.1 ODBC library suite for Unix
Ставим коннектор к мускулю
cd /usr/ports/databases/mysql-connector-odbc/
Встал, проверяем:
#pkg_info | grep mysql-connector mysql-connector-odbc-unixodbc-mysql55-5.1.9 ODBC driver for MySQL55 / unixodbc
Смотрим где что нам подключить:
# odbcinst -j unixODBC 2.3.1 DRIVERS............: /usr/local/etc/odbcinst.ini SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources USER DATA SOURCES..: /root/.odbc.ini SQLULEN Size.......: 4 SQLLEN Size........: 4 SQLSETPOSIROW Size.: 2
Правим /usr/local/etc/odbcinst.ini до такого состояния
[MySQL] Description=ODBC for MySQL Driver=/usr/local/lib/libmyodbc5.so UsageCount=20002
С версии 1.6 поддерживается трейдинг Threading = 2.
Начиная с версии 13.8 Астериска необходимо включать пулинг в самом odbc
Также можно включить и трассировку(ТОЛЬКО ДЛЯ ОТЛАДКИ!!)
[ODBC]
Trace = No
Trace File = /tmp/sql.log
Pooling = Yes
Устанавливаем драйвер:
odbcinst -i -d -f /usr/local/etc/odbcinst.ini
Небольшая ремарка по ходу пьесы по поводу odbcinst
odbcinst -i -d -f template_file
-i install
-d driver
-f name of template file
Правим /usr/local/etc/odbc.ini
Не забываем данные введенные в первой части)
[asterisk]
Driver=MySQL
SERVER=localhost
PORT=3306
DATABASE=asterisk
USER=adminnn
PASSWORD=adminnn123
CHARSET = utf8
readtimeout = 2
чтобы не было кракозябр, необходимо обязательно добавлять CHARSET = utf8,
а еще readtimeout – чтобы мы не получали lock диалплана
Можно задать еще опции трасировки,
Проверяем:
odbcinst -s -q [asterisk]
Подключено, теперь нужно проверить работу коннектора
echo "select 1" | isql -v asterisk +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select 1 +---------------------+ | 1 | +---------------------+ | 1 | +---------------------+ SQLRowCount returns 1 1 rows fetched
Прекрасно, миссия выполнена. Теперь переходим к настройке самого Asteriskа.
Для настройки связи Астериска и ODBC нужно подправить файл res_odbc.conf
[asterisk]
enabled => yes
dsn => asterisk
username => adminnn
password => adminnn123
pre-connect => yes
connect_timeout => 2
sanitysql => select 1
[asterisk] имя этой секции будем использовать в других конфигурационных файлах.
pooling и limit полезны для MSSQL и Sybase позволяет устанавливать несколько соединений до лимита
!!–НЕ устанавливать limit => 0 иначе в результате получаем нерабочий конфиг)
enable позволяет отключать всю секцию, без необходимости ее коментить
dsn (data source name) название источника данных, должно совпадать с названием в /usr/local/etc/odbc.ini
username имя пользователя, под каким мы будем заходить в БД
password пароль к базе
pre-connect заставляет подкл к базе во время загрузки модуля
connect_timeout – сколько ждать ответа от базы
sanitysql – проверяет доступность базы
Начиная с версии 13.8
Пулинг и шаринг полностью выпилины из Астера и перенесено на сторону unixODBC(необходимо включить пулинг)
В результате полностью удалены из res_odbc.conf:
“pooling”,
“shared_connection”,
“limit”,
“idlecheck”
Переходим к самим функциям (func_odbc.conf)
Здесь мы сами придумываем себе функции, а потом их используем в диалплане
Каждый раздел(контекст) описывает определенную функцию. Названия принято писать БОЛЬШИМИ буквами
Для подстановки в SQL запрос аргументов мы можем использовать:
${ARG1}, ${ARG2} … ${ARGn}
Для запросов записи, используются ${VAL1}, ${VAL2} … ${VALn} для значений, аналогично аргументам. Для получения всех значений используется ${VALUE}.
Если у вас есть данные, которые потенциально могут содержать одинарные кавычки, вы можете использовать функцию диалплана SQL_ESC().
readhandle -разделенный запятыми список DSN(до 5) к которым будет выполнен readsql. Если значение не задано используется, данные из writehandle или dsn
writehandle разделенный запятыми список DSN к которым будет выполнен writesql. “dsn” синоним “writehandle”.
readsql Выражение которое выполняется при чтении
writesql Выражение которое выполняется при записи
insertsql Выражение которое выполняется при записи, но база отвечает, что 0 строк было затронуто (0 rows were affected)
prefix по умолчанию префикс для всех функций “ODBC”, указав другой префикс можно разделить функции по определенному признаку.
escapecommas отвечает за обработку запятых в полях. если запятая “escaped”(по умолчанию), тогда поля содержащие запятые будут обработаны как единичные значения при использовании ARRAY() или HASH(). если запятые не “escaped”, тогда значение будет разделено по полям(с разделителем запятая). Выключив эту опцию, функция HASH(), не будет работать.
synopsis Описание, которое отображается при использовании ‘core show function
mode Может быть выставлена в значение ‘multirow’, и тогда ваша функция сможет возвращать, более 1 строки, нарушает нормальный режим работы func_odbc. вместо строки функция возвращает непрозачный ID, который может быть передан ODBC_FETCH() для возврата каждой строки отдельно. ODBC_FETCH_STATUS возвращает SUCCESS или FAILURE, для обозначения сохранились ли какие то данные, и вы должны вызвать ODBC_Finish для ID, чтобы прибраться после выполнения запроса. Также переменная ODBCROWS установлена изначально, которая может быть использована в итеративном режиме, чтобы вернуть количество строк результата.
rowlimit Максимум строк, которые могут храниться, в результате этого запроса. Использовать с mode=multirow, иначе func_odbc будет пытаться хранить все строки в resultset, до достижения предела памяти. В нормальном режиме, rowlimit может быть установлен, чтобы получить дополнительные строки. Эти дополнительные строки могут быть возвращены при использовании имени функции которая была вызвана для получения первой строки, как аргумент функции ODBC_FETCH().
Примеры:
1. Позволяет сделать полноценный запрос прямо из диалплана.
[SQL]
dsn=mysql1
readsql=${ARG1}
2. Блеклист(Анти бывшая баба)
[ANTIGF]
dsn=asterisk,mysql2 ; используем asterisk как первичную базу, если не отвечает используем mysql2.
readsql=SELECT COUNT(*) FROM names WHERE CID='${SQL_ESC(${ARG1})}'
syntax=<callerid>
synopsis=Проверить, содержиться ли этот CID в базе
3. Обработка присутствия
[PRESENCE]
dsn=mysql1
readsql=SELECT location FROM presence WHERE id='${SQL_ESC(${ARG1})}'
writesql=UPDATE presence SET location='${SQL_ESC(${VAL1})}' WHERE id='${SQL_ESC(${ARG1})}'
4. Работа с многострочными запросами:
С помощью префикса выставляем определенный класс(для удобства)
func_odbc.conf
[ALL_AVAIL_EXTENS]
prefix=GET
dsn=asterisk
mode=multirow
readsql=SELECT extension FROM ast_hotdesk WHERE status = '${ARG1}'
Чтобы не заморачиваться:
extensions.conf
exten => 0000,1,Goto(multirow_example,start,1)
[multirow_example]
exten => start,1,Verbose(1,Looping example)
same => n,Set(ODBC_ID=${GET_ALL_AVAIL_EXTENS(1)})
same => n, Verbose(ID=${ODBC_ID} rows=${ODBCROWS})
same => n,GotoIf($[${ODBCROWS} < 1]?no_rows,1)
same => n,Verbose(do counterr)
same => n,Set(COUNTERR=1)
sama => n,Verbose(posle counterr)
same => n,While($[${COUNTERR} <= ${ODBCROWS}])
same => n,Set(AVAIL_EXTEN_${COUNTERR}=${ODBC_FETCH(${ODBC_ID})})
same => n,Verbose(${AVAIL_EXTEN_${COUNTERR}})
same => n,Set(COUNTERR=$[${COUNTERR} + 1])
same => n,EndWhile()
same => n,ODBCFinish()
exten => no_rows,1,Verbose(1,No rows returned)
same => n,Playback(silence/1&invalid)
same => n,Hangup()
Мы получим список экстеншенов, у которых статус =1.
Рассмотрим на примере построение хотдеска, для контакт центра, где каждый работник работает по одному дню в неделю. но у нас есть два стола, задача знать на каком из столов сидит конкретный работник)
1.Добавляем необходимых пользователей в sip.conf.
2.Создаем таблицу:
CREATE TABLE ast_hotdesk ( id serial NOT NULL, extension int8, first_name text, last_name text, cid_name text, cid_number varchar(10), pin int4, context text, status bool DEFAULT false, location text, CONSTRAINT ast_hotdesk_id_pk PRIMARY KEY (id) );
3.Добавляем записи в таблицу:
INSERT INTO ast_hotdesk (extension, first_name, last_name, cid_name, cid_number, pin, context, location) VALUES (1102, 'Alexey', 'Bogomaz', 'BOS2', '7102', '555', 'test', '7102'); INSERT INTO ast_hotdesk (extension, first_name, last_name, cid_name, cid_number, pin, context, location) VALUES (1107, 'Alexey', 'Bogomaz', 'BOS7', '7107', '555', 'test', '7107');
4. Правим extensions.conf
Так мы сможем считать статус работника и его пин
exten => _*110[2-7],1,NoOp()
same => n,Set(E=${EXTEN:1}) ; strip off the leading hash (#) symbol
same => n,Verbose(1,Hot Desk Extension ${E} is changing status)
same => n,Verbose(1,Checking current status of extension ${E})
same => n,Set(${E}_STATUS=${HOTDESK_INFO(status,${E})})
same => n,Set(${E}_PIN=${HOTDESK_INFO(pin,${E})})
same => n,GotoIf($[${ODBCROWS} < 0]?invalid_user,1)
; проверяем статус абонента, если 1 то выходим.
same => n,GotoIf($[${${E}_STATUS} = 1]?logout,1:login,1)
;Логинимся с проверкой пароля
exten => login,1,NoOp() ;
same => n,Set(PIN_TRIES=1) ; номер попытки
same => n,Set(MAX_PIN_TRIES=3) ; макс к-во попыток
same => n,Playback(silence/1) ; играем тишину чтобы не сожрало первое приглашение
same => n(get_pin),NoOp()
same => n,Set(PIN_TRIES=$[${PIN_TRIES} + 1]) ; Увеличиваем номер попытки
same => n,Read(PIN_ENTERED,enter-password,${LEN(${${E}_PIN})}) ;считываем пин
same => n,GotoIf($["${PIN_ENTERED}" = "${${E}_PIN}"]?valid_login,1) ; совпало хорошо
same => n,Playback(pin-invalid) ;не совпало
same => n,GotoIf($[${PIN_TRIES} <= ${MAX_PIN_TRIES}]?get_pin:login_fail,1) ; плохо)
; определяем откуда ж нам позвонили разбираем переменную CHANNEL, используя CUT()
exten => valid_login,1,NoOp()
; Обрезаем название канала, остачу назначаем переменной LOCATION
same => n,Set(LOCATION=${CUT(CHANNEL,/,2)})
; Обрезаем уникальный идентификатор, остачу назначаем переменной LOCATION
same => n,Set(LOCATION=${CUT(LOCATION,-,1)})
Обновляем статус юзера(залогинен или нет)
same => n,Set(USERS_LOGGED_IN=${HOTDESK_CHECK_PHONE_
LOGINS(${LOCATION})})
same => n,GotoIf($[${USERS_LOGGED_IN} > 0]?logout_login,1)
same => n(set_login_status),NoOp()
;Устанавливаем статус 1, когда пользователь входит
same => n,Set(HOTDESK_STATUS(${E})=1,${LOCATION})
same => n,GotoIf($[${ODBCROWS} < 1]?error,1)
same => n,Playback(agent-loginok)
same => n,Hangup()
exten => logout_login,1,NoOp()
; разлогинваем всех на заданом устройстве
same => n,Set(LOGGED_IN_ID=${HOTDESK_LOGGED_IN_USER(${LOCATION})})
same => n(start_loop),NoOp()
same => n,Set(WHO=${ODBC_FETCH(${LOGGED_IN_ID})})
same => n,GotoIf($["${ODBC_FETCH_STATUS}" = "FAILURE"]?cleanup) ;есть ли еще что обработать
same => n,Set(HOTDESK_STATUS(${WHO})=0) ; вы
same => n,Goto(start_loop)
same => n(cleanup),ODBCFinish(${LOGGED_IN_ID})
same => n,Goto(valid_login,set_login_status) ; return to logging in
exten => logout,1,NoOp()
same => n,Set(HOTDESK_STATUS(${E})=0)
same => n,GotoIf($[${ODBCROWS} < 1]?error,1)
same => n,Playback(silence/1&agent-loggedoff)
same => n,Hangup()
exten => login_fail,1,NoOp()
same => n,Playback(silence/1&login-fail)
same => n,Hangup()
exten => error,1,NoOp()
same => n,Playback(silence/1&connection-failed)
same => n,Hangup()
exten => invalid_user,1,NoOp()
same => n,Verbose(1,Hot Desk extension ${E} does not exist)
same => n,Playback(silence/2&invalid)
same => n,Hangup()
;исходящие (больше касается штатов, с их системой нумерации итд)
include => hotdesk_outbound
[hotdesk_outbound]
exten => _X.,1,NoOp()
same => n,Set(LOCATION=${CUT(CHANNEL,/,2)})
same => n,Set(LOCATION=${CUT(LOCATION,-,1)})
same => n,Set(WHO=${HOTDESK_PHONE_STATUS(${LOCATION})})
same => n,GotoIf($[${ISNULL(${WHO})}]?no_outgoing,1)
same => n,Set(${WHO}_CID_NAME=${HOTDESK_INFO(cid_name,${WHO})})
same => n,Set(${WHO}_CID_NUMBER=${HOTDESK_INFO(cid_number,${WHO})})
same => n,Set(${WHO}_CONTEXT=${HOTDESK_INFO(context,${WHO})})
same => n,Goto(${${WHO}_CONTEXT},${EXTEN},1)
[international]
exten => _011.,1,NoOp()
same => n,Set(E=${EXTEN})
same => n,Goto(outgoing,call,1)
exten => i,1,NoOp()
same => n,Playback(silence/2&sorry-cant-let-you-do-that2)
same => n,Hangup()
include => longdistance
[longdistance]
exten => _1NXXNXXXXXX,1,NoOp()
same => n,Set(E=${EXTEN})
same => n,Goto(outgoing,call,1)
exten => _NXXNXXXXXX,1,Goto(1${EXTEN},1)
exten => i,1,NoOp()
same => n,Playback(silence/2&sorry-cant-let-you-do-that2)
same => n,Hangup()
include => local
[local]
exten => _416NXXXXXX,1,NoOp()
same => n,Set(E=${EXTEN})
same => n,Goto(outgoing,call,1)
exten => i,1,NoOp()
same => n,Playback(silence/2&sorry-cant-let-you-do-that2)
same => n,Hangup()
[outgoing]
exten => call,1,NoOp()
same => n,Set(CALLERID(name)=${${WHO}_CID_NAME})
same => n,Set(CALLERID(number)=${${WHO}_CID_NUMBER})
same => n,Dial(SIP/service_provider/${E})
same => n,Playback(silence/2&pls-try-call-later)
same => n,Hangup()
5. func_odbc.conf
[INFO]
prefix=HOTDESK
dsn=asterisk
readsql=SELECT ${ARG1} FROM ast_hotdesk WHERE extension = '${ARG2}'
; Функция проверки все лы вылогинились, или ктото забыл
[CHECK_PHONE_LOGINS]
prefix=HOTDESK
dsn=asterisk
; эта Строка должна быть ЦЕЛОЙ(без переносов)
readsql=SELECT COUNT(status) FROM ast_hotdesk WHERE status = '1' AND location = '${ARG1}'
[STATUS]
prefix=HOTDESK
dsn=asterisk
writesql=UPDATE ast_hotdesk SET status = '${VAL1}', location = '${VAL2}' WHERE extension = '${ARG1}'
;для исходящих
[PHONE_STATUS]
prefix=HOTDESK
dsn=asterisk
readsql=SELECT extension FROM ast_hotdesk WHERE status = '1' AND location = '${ARG1}'
Для чтения нескольких значений можно использовать функцию ARRAY, получить доступ можно с помощью аргумента функции!
func_odbc.conf
readsql=SELECT pin,status FROM ast_hotdesk WHERE extension = '${E}'
extensions.conf
exten => _110[2-7],n,Set(ARRAY(${E}_PIN,${E}_STATUS)=${HOTDESK_INFO(${E})})
Кроме того можно использовать доступ к базе данных в обход ODBC
Теория:
в версии 1.8 взаимодействие с mysql вернули в поставку из коробки
в версии 1.8 появилась человеческая реализация SQL запросов, без бекслешей и прочей …..
для всех манипуляций нам потребуется ОДНА команда MYSQL(),но вызывать мы ее будем несколько раз
MYSQL(Connect connid dhhost dbuser dbpass dbname) connid -рандомный id соединения который нам понадобится для дальнейшей работы с базой
dbhost – обычно localhost
dbuser – имя пользователя для подкл к БД
dbpass – пароль для покдл к БД
dbname – имя БД из которой на и вынимать данные
MYSQL(Query resultid ${connid} query-string)Выполняет стандартный запрос к базе данных MySQL, запрос содержится в параметре query-string, используется идентификатор соединения определенный в ${connid}. Результат запроса будет сохранен в переменной ${resultid}. можем использовать любую команду SELECT, INSERT,UPDATE…..
MYSQL(Fetch fetchid ${resultid} var1 var2 … varN) Делает выборку из результатов запроса, если мы выбирали данные из 2 колонок, то разложить их по переменным.
Cмысл fetchid для меня остается загадкой вот что пишут в инетрнетах
${fetchid} – Эта функция устанавливает это поле в 1, если есть данные только для этого конкретного вызова MYSQL(Fetch).
MYSQL(Clear ${resultid})Очищает память и структуры данных связанных с результатом запроса.
MYSQL(Disconnect ${connid})Прекращает соединение с базой MySQL с данным идентификатором.
MYSQL(Nextresult resultid ${connid})Если последний запрос вернул более чем один результат, это сохранит следующий результат в ${resultid}. полезно при использовании хранимых процедур.
Не понял как пользоваться:((((
ЗАМЕЧАНИЯ
Если не хотите проблем всегда выполняйте эти действия, а потом уже обрабатывайте данные скриптами итд. Если не сделать 5й пункт(или перед его вызовом вызвать скрипт который не будет коректно обрабатыватся) соединение останется не закрытым, и с каждым новым таким вызовом число незакрытых соединений будет увеличиваться. В конечном счете, в MySQL сервере кончится лимит на количество одновременно открытых соединений с базой. (В зависимости от установленного лимита в конфигурационном файле mysql).
Не рекомендуется использовать запрос типа “SELECT *”, потому что нет гарантии того, что возвращаемые поля будут в том порядке, в котором Вы их ожидаете. Вам нет необходимости использовать псевдонимы для выбираемых из базы полей типа: “SELECT (длинное выражение) as короткое_имя”, потому что имя поля не влияет на порядок полей, полученных по этому запросу.
Не тестировал:
Если работаете с полями даты и времени и хотим получить эти данные без разделителя типа- используйте ‘+0’ в имени колонки:
exten => 1,n,MYSQL(Query resultid ${connid} SELECT called+0 FROM callers WHERE callbacknum=${CALLERID(num):-11:11}
Такой вызов вернет дату и время в таком формате: yyyymmddhhmmss.
ПРИМЕР:
exten => 0204,1,NoOp(start)
same => n, MySQL(Connect connid localhost root password dbname) ;соединились
same => n, MySQL(Query res_id ${connid} SELECT count(*) from test) ; сделали запрос
same => n, Set(COUNT=${res_id}) ; сохранили к-во строк
same => n, MYSQL(Clear ${res_id}) ; очистили временную переменную
same => n, MySQL(Disconnect ${connid}) ; отключились
same => n, While($[${COUNT}>0]) ;входим в цикл и делаем пока
same => n, MySQL(Connect connid localhost root password dbname)
same => n, MYSQL(Query resultid ${connid} SELECT number, text from test where id= ${COUNT})
same => n, MYSQL(Fetch fetchid ${resultid} number text)
same => n, MYSQL(Clear ${resultid})
same => n, MYSQL(Disconnect ${connid})
same => n, JabberSend(asterisk,[email protected], Number: ${number} text: ${text})
same => n, Set(COUNT=${DEC(COUNT)})
same => n, EndWhile()
same => n, Hangup()
Думаю все понятно, конструктивная критика приветствуется!
P.S. По просьбам трудящихся добавлю коротенький пунктик о первоначальной настройке MySQL сервера 5.5
1.Добавляем в rc.conf
mysql_enable=”YES”
2.Копируем конфиг:
cp /usr/local/share/mysql/my-small.cnf /usr/local/etc/my.cnf
3.Стартуем сервер:
/usr/local/etc/rc.d/mysql-server start
4.Постинсталяционные настройки(только лок юзеры, пароль рута..)
/usr/local/bin/mysql_secure_installation
Сповіщення: Asterisk CEL | My IT-blog