AsteriskのPJSIPの設定をPostgreSQLに格納する手順のメモ


[Setting up PJSIP Realtime](https://wiki.asterisk.org/wiki/display/AST/Setting+up+PJSIP+Realtime)を参考にPostgreSQLにpjsipの設定を格納する方法のメモ

## はじめに

Asterisk13(11ぐらいから？)では、MySQLにPJSIPの設定を格納するのは、ODBC経由になったらしい。
PostgreSQLの場合も同様のようだ。(本当？、PostgreSQLはODBC経由でなくていいのかな？)

[Setting up PJSIP Realtime](https://wiki.asterisk.org/wiki/display/AST/Setting+up+PJSIP+Realtime)
というドキュメントを見つけたので同じことをPostgreSQLで試した。

## 環境

* OS: CentOS 6
* Asterisk: 13.9.1
* PostgreSQL: 9.5

## 設定手順

一部説明を端折っている。後日追記するかもしれない。

### PostgreSQLの設定

localhost経由でログインできるようにしておく。

```
rpm -ivh https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-i386/pgdg-centos95-9.5-2.noarch.rpm
yum install postgresql95-server
su postgres -c '/usr/pgsql-9.5/bin/initdb --no-locale -D /var/lib/pgsql/9.5/data -E UTF-8 '
/sbin/service postgresql-9.5 start
/usr/pgsql-9.5/bin/createuser asterisk -P
```

次のデータベースを作っておく

データベース: asteriskdb
ユーザ: asterisk

### alembicの導入

テーブルの作成にalembicというコマンドを使う。CentOS6でもpython-alembicというものがあるがバージョンが古すぎる。pip経由でインストールしたalembicを使う

```
sudo yum install python-pip python-psycopg2
sudo pip install alembic
  Downloading alembic-0.8.6.tar.gz (961kB)
```


### テーブルの作成

Asteriskのソースの中に、コンフィグようの設定ファイルを作るスクリプトが用意されている。

```
cd ${asterisk_source}/contrib/ast-db-manage
cp config.ini.sample config.ini
vi config.init
```

config.iniを次のように書き換える。

```
sqlalchemy.url = postgresql://asterisk:pass@127.0.0.1/asteriskdb
```

```
sqlalchemy.url = postgresql://ユーザ名:パスワード@ホスト/データベース
```


テーブルを作る

```
alembic -c config.ini upgrade head
```

こんな感じで実行結果が出力される。

```
# alembic -c config.ini upgrade head
alembic -c config.ini upgrade head
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [alembic.migration] Running upgrade None -> 4da0c5f79a9c
INFO  [alembic.migration] Running upgrade 4da0c5f79a9c -> 43956d550a44
INFO  [alembic.migration] Running upgrade 43956d550a44 -> 581a4264e537
INFO  [alembic.migration] Running upgrade 581a4264e537 -> 2fc7930b41b3
```

次のようなテーブルができる。

```
asteriskdb=> \dt
                    List of relations
 Schema |            Name             | Type  |  Owner   
--------+-----------------------------+-------+----------
 public | alembic_version             | table | asterisk
 public | extensions                  | table | asterisk
 public | iaxfriends                  | table | asterisk
 public | meetme                      | table | asterisk
 public | musiconhold                 | table | asterisk
 public | ps_aors                     | table | asterisk
 public | ps_auths                    | table | asterisk
 public | ps_contacts                 | table | asterisk
 public | ps_domain_aliases           | table | asterisk
 public | ps_endpoint_id_ips          | table | asterisk
 public | ps_endpoints                | table | asterisk
 public | ps_globals                  | table | asterisk
 public | ps_registrations            | table | asterisk
 public | ps_subscription_persistence | table | asterisk
 public | ps_systems                  | table | asterisk
 public | ps_transports               | table | asterisk
 public | queue_members               | table | asterisk
 public | queue_rules                 | table | asterisk
 public | queues                      | table | asterisk
 public | sippeers                    | table | asterisk
 public | voicemail                   | table | asterisk
(21 rows)
```


### ODBCの設定

PostgreSQL用のODCドライバを導入

```
sudo yum install postgresql95-odbc.i686
```

odbc.iniを作成する

```
[asterisk]
Driver = /usr/pgsql-9.5/lib/psqlodbc.so
Servername = 127.0.0.1
Database = asteriskdb
FileUsage       = 1
```

FileUsage = 1の意味はよくわかっていない


接続確認をする

```
isql -v asterisk asterisk password
```

```
isql -v DSN名 ユーザ名 パスワード
```

```
SQL> select * from alembic_version;
+---------------------------------+
| version_num                     |
+---------------------------------+
| 1c688d9a003c                    |
+---------------------------------+
SQLRowCount returns 1
1 rows fetched
```

Asteriskで接続確認

```
asterisk -vvvc

*CLI> odbc show

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

  Name:   asterisk
  DSN:    asterisk
    Last connection attempt: 1970-01-01 09:00:00

```

元のドキュメントだと次のようになっていて、PooledやConnectedの出力があるが、Asterisk13.9.1ではこれらのメッセージは表示されない。


```
# asterisk -vvvvc
*CLI> odbc show
 
ODBC DSN Settings
-----------------
 Name: asterisk
 DSN:    asterisk
 Last connection attempt: 1969-12-31 18:00:00
 Pooled: No
 Connected: Yes
*CLI> 
```

最新のソースでは、``Number of active connections``というエントリが表示されるようだ。

[参考](https://github.com/asterisk/asterisk/blob/master/res/res_odbc.c#L663-L680)



### Asteriskの設定

sorcery.conf の下記の部分のコメントを外す。

```
[res_pjsip]
endpoint=realtime,ps_endpoints
endpoint=config,pjsip.conf,criteria=type=endpoint
auth=realtime,ps_auths
aor=realtime,ps_aors
domain_alias=realtime,ps_domain_aliases
identify=realtime,ps_endpoint_id_ips
```

```
endpoint=config,pjsip.conf,criteria=type=endpoint
```

という設定は、pjsip.confも見る場合に追記するらしい。


res_odbc.confの設定を記述する。

```
[asterisk]
enabled => yes
dsn => asterisk
username => asterisk
password => password
pre-connect => yes
sanitysql => select count(*) from alembic_version
```

sanitysqlはなくても良いと思う。

modules.confを次のようにする。``noload => chan_sip``は必要な場合のみ追記

```
preload => res_odbc.so
preload => res_config_odbc.so
noload => chan_sip.so 
```

### サンプル設定の追加

```
insert into ps_aors (id, max_contacts) values (101, 1);
insert into ps_aors (id, max_contacts) values (102, 1);
insert into ps_auths (id, auth_type, password, username) values (101, 'userpass', 101, 101);
insert into ps_auths (id, auth_type, password, username) values (102, 'userpass', 102, 102);
insert into ps_endpoints (id, transport, aors, auth, context, disallow, allow, direct_media) values (101, 'transport-udp', '101', '101', 'testing', 'all', 'g722', 'no');
insert into ps_endpoints (id, transport, aors, auth, context, disallow, allow, direct_media) values (102, 'transport-udp', '102', '102', 'testing', 'all', 'g722', 'no');
```

### 確認

asterisk -vvvvcで確認をする。

```
*CLI> pjsip show endpoints

 Endpoint:  <Endpoint/CID.....................................>  <State.....>  <Channels.>
    I/OAuth:  <AuthId/UserName...........................................................>
        Aor:  <Aor............................................>  <MaxContact>
      Contact:  <Aor/ContactUri..........................> <Hash....> <Status> <RTT(ms)..>
  Transport:  <TransportId........>  <Type>  <cos>  <tos>  <BindAddress..................>
    Channel:  <ChannelId......................................>  <State.....>  <Time.....>
        Exten: <DialedExten...........>  CLCID: <ConnectedLineCID.......>
==========================================================================================

 Endpoint:  101                                                  Unavailable   0 of inf
     InAuth:  101/101
        Aor:  101                                                1

 Endpoint:  102                                                  Unavailable   0 of inf
     InAuth:  102/102
        Aor:  102                                                1

```

データベースで定義したエンドポイントが確認できた。


## (参考)古いalembicを利用した場合

```
alembic -c config.ini upgrade head
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [alembic.migration] Running upgrade None -> 4da0c5f79a9c
INFO  [alembic.migration] Running upgrade 4da0c5f79a9c -> 43956d550a44
INFO  [alembic.migration] Running upgrade 43956d550a44 -> 581a4264e537
INFO  [alembic.migration] Running upgrade 581a4264e537 -> 2fc7930b41b3
Traceback (most recent call last):
  File "/usr/bin/alembic", line 9, in <module>
    load_entry_point('alembic==0.4.2', 'console_scripts', 'alembic')()
  File "/usr/lib/python2.6/site-packages/alembic/config.py", line 255, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/lib/python2.6/site-packages/alembic/config.py", line 250, in main
    self.run_cmd(cfg, options)
  File "/usr/lib/python2.6/site-packages/alembic/config.py", line 241, in run_cmd
    **dict((k, getattr(options, k)) for k in kwarg)
  File "/usr/lib/python2.6/site-packages/alembic/command.py", line 124, in upgrade
    script.run_env()
  File "/usr/lib/python2.6/site-packages/alembic/script.py", line 191, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/lib/python2.6/site-packages/alembic/util.py", line 186, in load_python_file
    module = imp.load_source(module_id, path, open(path, 'rb'))
  File "config/env.py", line 73, in <module>
    run_migrations_online()
  File "config/env.py", line 66, in run_migrations_online
    context.run_migrations()
  File "<string>", line 7, in run_migrations
  File "/usr/lib/python2.6/site-packages/alembic/environment.py", line 494, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/lib/python2.6/contextlib.py", line 34, in __exit__
    self.gen.throw(type, value, traceback)
  File "/usr/lib/python2.6/site-packages/alembic/operations.py", line 50, in context
    yield op
  File "/usr/lib/python2.6/site-packages/alembic/environment.py", line 494, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/lib/python2.6/site-packages/alembic/migration.py", line 211, in run_migrations
    change(**kw)
  File "config/versions/2fc7930b41b3_add_pjsip_endpoint_options_for_12_1.py", line 131, in upgrade
    new_column_name='mwi_from_user', existing_type=sa.String(40))
TypeError: alter_column() got an unexpected keyword argument 'new_column_name'
```

バージョン0.4.2と古いことが原因の模様

```
rpm -qi python-alembic 
Name        : python-alembic               Relocations: (not relocatable)
Version     : 0.4.2                             Vendor: Fedora Project
Release     : 3.el6                         Build Date: Tue 30 Jul 2013 01:28:23 AM JST
Install Date: Tue 07 Jun 2016 10:03:13 PM JST      Build Host: buildvm-03.phx2.fedoraproject.org
Group       : Development/Libraries         Source RPM: python-alembic-0.4.2-3.el6.src.rpm
Size        : 1250852                          License: MIT
Signature   : RSA/8, Fri 02 Aug 2013 03:11:04 AM JST, Key ID 3b49df2a0608b895
Packager    : Fedora Project
URL         : http://pypi.python.org/pypi/alembic
Summary     : Database migration tool for SQLAlchemy
Description :
Alembic is a new database migrations tool, written by the author of
`SQLAlchemy <http://www.sqlalchemy.org>`_.  A migrations tool offers the
following functionality:

* Can emit ALTER statements to a database in order to change the structure
of tables and other constructs.
* Provides a system whereby "migration scripts" may be constructed; each script
indicates a particular series of steps that can "upgrade" a target database to
a new version, and optionally a series of steps that can "downgrade"
similarly, doing the same steps in reverse.
* Allows the scripts to execute in some sequential manner.

Documentation and status of Alembic is at http://readthedocs.org/docs/alembic/
```