配置实时数据库

今天讲解 Asterisk 如何实时将 SIP 用户写入 sqlite3 数据库。

先定义数据库表结构，我当前使用的 PJSIP 协议。

CREATE TABLE ps_endpoints (
    id VARCHAR(40) NOT NULL,
    transport VARCHAR(40),
    aors VARCHAR(200),
    auth VARCHAR(40),
    context VARCHAR(40),
    disallow VARCHAR(200),
    allow VARCHAR(200),
    direct_media varchar(5) check(direct_media in ('yes','no')),
    connected_line_method varchar(10) check(connected_line_method in ('invite','reinvite','update')),
    direct_media_method varchar(10) check(direct_media_method in ('invite','reinvite','update')),
    direct_media_glare_mitigation varchar(20) check(direct_media_glare_mitigation in ('none','outgoing','incoming')),
    disable_direct_media_on_nat varchar(5) check(disable_direct_media_on_nat in ('yes','no')),
    dtmf_mode varchar(20) check(dtmf_mode in ('rfc4733','inband','info')),
    external_media_address VARCHAR(40),
    force_rport varchar(5) check(force_rport in ('yes','no')),
    ice_support varchar(5) check(ice_support in ('yes','no')),
    identify_by varchar(10) check(identify_by in ('username')),
    mailboxes VARCHAR(40),
    moh_suggest VARCHAR(40),
    outbound_auth VARCHAR(40),
    outbound_proxy VARCHAR(40),
    rewrite_contact varchar(5) check(rewrite_contact in ('yes','no')),
    rtp_ipv6 varchar(5) check(rtp_ipv6 in ('yes','no')),
    rtp_symmetric varchar(5) check(rtp_symmetric in ('yes','no')),
    send_diversion varchar(5) check(send_diversion in ('yes','no')),
    send_pai varchar(5) check(send_pai in ('yes','no')),
    send_rpid varchar(5) check(send_rpid in ('yes','no')),
    timers_min_se INTEGER,
    timers varchar(20) check(timers in ('forced','no','required','yes')),
    timers_sess_expires INTEGER,
    callerid VARCHAR(40),
    callerid_privacy varchar(40) check(callerid_privacy in ('allowed_not_screened','allowed_passed_screened','allowed_failed_screened','allowed','prohib_not_screened','prohib_passed_screened','prohib_failed_screened','prohib','unavailable')),
    callerid_tag VARCHAR(40),
    `100rel` varchar(20) check(`100rel` in ('no','required','yes')),
    aggregate_mwi varchar(5) check(aggregate_mwi in ('yes','no')),
    trust_id_inbound varchar(5) check(trust_id_inbound in ('yes','no')),
    trust_id_outbound varchar(5) check(trust_id_outbound in ('yes','no')),
    use_ptime varchar(5) check(use_ptime in ('yes','no')),
    use_avpf varchar(5) check(use_avpf in ('yes','no')),
    media_encryption varchar(10) check(media_encryption in ('no','sdes','dtls')),
    inband_progress varchar(5) check(inband_progress in ('yes','no')),
    call_group VARCHAR(40),
    pickup_group VARCHAR(40),
    named_call_group VARCHAR(40),
    named_pickup_group VARCHAR(40),
    device_state_busy_at INTEGER,
    fax_detect varchar(5) check(fax_detect in ('yes','no')),
    t38_udptl varchar(5) check(t38_udptl in ('yes','no')),
    t38_udptl_ec varchar(20) check(t38_udptl_ec in ('none','fec','redundancy')),
    t38_udptl_maxdatagram INTEGER,
    t38_udptl_nat varchar(5) check(t38_udptl_nat in ('yes','no')),
    t38_udptl_ipv6 varchar(5) check(t38_udptl_ipv6 in ('yes','no')),
    tone_zone VARCHAR(40),
    language VARCHAR(40),
    one_touch_recording varchar(5) check(one_touch_recording in ('yes','no')),
    record_on_feature VARCHAR(40),
    record_off_feature VARCHAR(40),
    rtp_engine VARCHAR(40),
    allow_transfer varchar(5) check(allow_transfer in ('yes','no')),
    allow_subscribe varchar(5) check(allow_subscribe in ('yes','no')),
    sdp_owner VARCHAR(40),
    sdp_session VARCHAR(40),
    tos_audio INTEGER,
    tos_video INTEGER,
    cos_audio INTEGER,
    cos_video INTEGER,
    sub_min_expiry INTEGER,
    from_domain VARCHAR(40),
    from_user VARCHAR(40),
    mwi_fromuser VARCHAR(40),
    dtls_verify VARCHAR(40),
    dtls_rekey VARCHAR(40),
    dtls_cert_file VARCHAR(200),
    dtls_private_key VARCHAR(200),
    dtls_cipher VARCHAR(200),
    dtls_ca_file VARCHAR(200),
    dtls_ca_path VARCHAR(200),
    dtls_setup varchar(20) check(dtls_setup in ('active','passive','actpass')),
    srtp_tag_32 varchar(5) check(srtp_tag_32 in ('yes','no')),
    UNIQUE (id)
);

CREATE INDEX ps_endpoints_id ON ps_endpoints (id);


CREATE TABLE ps_auths (
    id VARCHAR(40) NOT NULL,
    auth_type varchar(10) check(auth_type in ('md5','userpass')),
    nonce_lifetime INTEGER,
    md5_cred VARCHAR(40),
    password VARCHAR(80),
    realm VARCHAR(40),
    username VARCHAR(40),
    UNIQUE (id)
);

CREATE INDEX ps_auths_id ON ps_auths (id);

CREATE TABLE ps_aors (
    id VARCHAR(40) NOT NULL,
    contact VARCHAR(40),
    default_expiration INTEGER,
    mailboxes VARCHAR(80),
    max_contacts INTEGER,
    minimum_expiration INTEGER,
    remove_existing varchar(5) check(remove_existing in ('yes','no')),
    qualify_frequency INTEGER,
    authenticate_qualify varchar(5) check(authenticate_qualify in ('yes','no')),
    UNIQUE (id)
);

CREATE INDEX ps_aors_id ON ps_aors (id);

PJSIP 测试数据：

insert into ps_endpoints(id,context,disallow,allow,auth,aors) values ('9000','vcode','all','ulaw,alaw,gsm','9000','9000');
insert into ps_aors(id,max_contacts) values('9000',1);
insert into ps_auths(id,auth_type,password,username) values('9000','userpass','123321','9000');

注意：数据库表中的字段和配置文件中的字段是一致的。

配置 Asterisk

在 res_config_sqlite3.conf 中添加如下内容：

[asterisk]
dbfile => /var/lib/asterisk/realtime.sqlite3

在 extconfig.conf 文件中添加如下内容：

ps_endpoints => sqlite3,asterisk
ps_auths => sqlite3,asterisk
ps_aors => sqlite3,asterisk

在 sorcery.conf 文件中添加如下内容：

[res_pjsip]
endpoint=realtime,ps_endpoints
auth=realtime,ps_auths
aor=realtime,ps_aors

使用 sqlite3 命令创建 realtime.sqlite3 数据库，并创建上面的三张 PJSIP 表，然后插入记录。

最后在软电话上测试是否可以注册成功。

虽然，我使用的 sqlite3 存储 sip 用户信息，但我推荐你使用 mysql 或者 postgresql。 它的好处是可以对接第三方应用时，实时添加操作数据库，实时添加用户。而 sqlite3 会报错，提示数据库已锁定。 虽然我通过同步数据库文件解决，但它不是一个实时的方案。它在并发操作这块还是有些欠缺。

如果您有什么问题，欢迎关注公众号【技术源泉】私信我。

以下是备忘录，在 Asterisk 新版本中已不推荐使用。

如果使用的 SIP，请使用如下方法：

1，调整配置文件

修改 extconfig.conf，添加如下内容：

sippeers=>sqlite,general,sippeers

第一个 sippeers 是固定名称，最后一个 sippeers 是表名。

2，在数据库中添加表

例如，数据文件存储在/var/lib/asterisk/realtime.sqlite3

这个是 SIP 用户

CREATE TABLE sippeers (
    id INTEGER NOT NULL primary key autoincrement,
    name VARCHAR(40) NOT NULL,
    ipaddr VARCHAR(45),
    port INTEGER,
    regseconds INTEGER,
    defaultuser VARCHAR(40),
    fullcontact VARCHAR(80),
    regserver VARCHAR(20),
    useragent VARCHAR(20),
    lastms INTEGER,
    host VARCHAR(40),
    type varchar(10) check(type in ('friend','user','peer')),
    context VARCHAR(40),
    permit VARCHAR(95),
    deny VARCHAR(95),
    secret VARCHAR(40),
    md5secret VARCHAR(40),
    remotesecret VARCHAR(40),
    transport varchar(20) check(transport in ('udp','tcp','tls','ws','wss','udp,tcp','tcp,udp')),
    dtmfmode varchar(20) check(dtmfmode in ('rfc2833','info','shortinfo','inband','auto')),
    directmedia varchar(20) check(directmedia in ('yes','no','nonat','update')),
    nat VARCHAR(29),
    callgroup VARCHAR(40),
    pickupgroup VARCHAR(40),
    language VARCHAR(40),
    disallow VARCHAR(200),
    allow VARCHAR(200),
    insecure VARCHAR(40),
    trustrpid varchar(10) check(trustrpid in ('yes','no')),
    progressinband varchar(10) check(progressinband in ('yes','no','never')),
    promiscredir varchar(5) check(promiscredir in ('yes','no')),
    useclientcode varchar(5) check(useclientcode in ('yes','no')),
    accountcode VARCHAR(40),
    setvar VARCHAR(200),
    callerid VARCHAR(40),
    amaflags VARCHAR(40),
    callcounter varchar(5) check(callcounter in ('yes','no')),
    busylevel INTEGER,
    allowoverlap varchar(5) check(allowoverlap in ('yes','no')),
    allowsubscribe varchar(5) check(allowsubscribe in ('yes','no')),
    videosupport varchar(5) check(videosupport in ('yes','no')),
    maxcallbitrate INTEGER,
    rfc2833compensate varchar(5) check(rfc2833compensate in ('yes','no')),
    mailbox VARCHAR(40),
    `session-timers` varchar(20) check(`session-timers` in ('accept','refuse','originate')),
    `session-expires` INTEGER,
    `session-minse` INTEGER,
    `session-refresher` varchar(5) check(`session-refresher` in ('uac','uas')),
    t38pt_usertpsource VARCHAR(40),
    regexten VARCHAR(40),
    fromdomain VARCHAR(40),
    fromuser VARCHAR(40),
    `qualify` VARCHAR(40),
    defaultip VARCHAR(45),
    rtptimeout INTEGER,
    rtpholdtimeout INTEGER,
    sendrpid varchar(5) check(sendrpid in ('yes','no')),
    outboundproxy VARCHAR(40),
    callbackextension VARCHAR(40),
    timert1 INTEGER,
    timerb INTEGER,
    qualifyfreq INTEGER,
    constantssrc varchar(5) check(constantssrc in ('yes','no')),
    contactpermit VARCHAR(95),
    contactdeny VARCHAR(95),
    usereqphone varchar(5) check(usereqphone in ('yes','no')),
    textsupport varchar(5) check(textsupport in ('yes','no')),
    faxdetect varchar(5) check(faxdetect in ('yes','no')),
    buggymwi varchar(5) check(buggymwi in ('yes','no')),
    auth VARCHAR(40),
    fullname VARCHAR(40),
    trunkname VARCHAR(40),
    cid_number VARCHAR(40),
    callingpres varchar(50) check(callingpres in ('allowed_not_screened','allowed_passed_screen','allowed_failed_screen','allowed','prohib_not_screened','prohib_passed_screen','prohib_failed_screen','prohib')),
    mohinterpret VARCHAR(40),
    mohsuggest VARCHAR(40),
    parkinglot VARCHAR(40),
    hasvoicemail varchar(5) check(hasvoicemail in ('yes','no')),
    subscribemwi varchar(5) check(subscribemwi in ('yes','no')),
    vmexten VARCHAR(40),
    autoframing varchar(5) check(autoframing in ('yes','no')),
    rtpkeepalive INTEGER,
    `call-limit` INTEGER,
    g726nonstandard varchar(5) check(g726nonstandard in ('yes','no')),
    ignoresdpversion varchar(5) check(ignoresdpversion in ('yes','no')),
    allowtransfer varchar(5) check(allowtransfer in ('yes','no')),
    dynamic varchar(5) check(dynamic in ('yes','no')),
    path VARCHAR(256),
    supportpath varchar(5) check(supportpath in ('yes','no')),
    UNIQUE (name)
);


CREATE INDEX sippeers_name ON sippeers (name);

CREATE INDEX sippeers_name_host ON sippeers (name, host);

CREATE INDEX sippeers_ipaddr_port ON sippeers (ipaddr, port);

CREATE INDEX sippeers_host_port ON sippeers (host, port);