SIP RealTime (Peer|User|Friend)s

MySQL

DB Schema (dumped to be compatible back to 4.0)

CREATE TABLE `sip` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(80) NOT NULL default '',
  `host` varchar(31) NOT NULL default 'dynamic',
  `nat` varchar(5) NOT NULL default 'no',
  `type` enum('user','peer','friend') NOT NULL default 'friend',
  `accountcode` varchar(20) default NULL,
  `amaflags` varchar(13) default NULL,
  `callgroup` varchar(10) default NULL,
  `callerid` varchar(80) default NULL,
  `cancallforward` char(3) default 'yes',
  `canreinvite` char(3) default 'no',
  `context` varchar(80) default NULL,
  `defaultip` varchar(15) default NULL,
  `dtmfmode` varchar(7) default 'rfc2833',
  `fromuser` varchar(80) default NULL,
  `fromdomain` varchar(80) default NULL,
  `insecure` varchar(4) default NULL,
  `language` char(2) default NULL,
  `mailbox` varchar(50) default NULL,
  `md5secret` varchar(80) default NULL,
  `deny` varchar(95) default NULL,               
  `permit` varchar(95) default NULL,
  `mask` varchar(95) default NULL,
  `musiconhold` varchar(100) default NULL,
  `pickupgroup` varchar(10) default NULL,
  `qualify` char(3) NOT NULL default 'yes',
  `regexten` varchar(80) default NULL,
  `restrictcid` char(3) default NULL,
  `rtptimeout` char(3) default NULL,
  `rtpholdtimeout` char(3) default NULL,
  `secret` varchar(80) default NULL,
  `setvar` varchar(100) default NULL,
  `disallow` varchar(100) default 'all',
  `allow` varchar(100) default 'g729;ilbc;gsm;ulaw;alaw',
  `fullcontact` varchar(80) NOT NULL default '',
  `ipaddr` varchar(15) NOT NULL default '',
  `port` smallint(5) unsigned NOT NULL default '0',
  `regseconds` int(11) NOT NULL default '0',
  `regserver` varchar(80) default NULL,
  `username` varchar(80) NOT NULL default '',
  `useragent` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `name_2` (`name`)
);

extconfig.conf (assuming your dbname is “callweaver”)

sipusers => mysql,callweaver,sip
sippeers => mysql,callweaver,sip

res_mysql.conf

[general]
dbhost = localhost
dbname = callweaver
dbuser = callweaver
dbpass = callweaver
dbport = 3306
dbsock = /var/run/mysqld/mysqld.sock

PostgreSQL

You have to compile your callweaver with “—with-res_config_pgsql=yes”

Create new Database :

$ su postgres
$ createuser callweaver
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE
$ createdb -O callweaver callweaver
CREATE DATABASE
$ psql callweaver
...
callweaver =# ALTER USER callweaver WITH PASSWORD 'callweaver';
ALTER ROLE
callweaver =# \c - callweaver
callweaver => \i res_pgsql_sip.sql

DB Schema (Tested with postgresql 8.2) :

drop table sip;
CREATE TABLE sip (
    id serial NOT NULL,
    name character varying(80) DEFAULT '' NOT NULL,
    host character varying(31) DEFAULT 'dynamic' NOT NULL,
    nat character varying(5) DEFAULT 'no' NOT NULL,
    "type" character varying DEFAULT 'friend' NOT NULL,
    accountcode character varying(20),
    amaflags character varying(7),
    callgroup character varying(10),
    callerid character varying(80),
    cancallforward character varying(3) DEFAULT 'yes',
    canreinvite character varying(3) DEFAULT 'no',
    context character varying(80) DEFAULT NULL,
    defaultip character varying(15) DEFAULT NULL,
    dtmfmode character varying(7) DEFAULT 'rfc2833',
    fromuser character varying(80) DEFAULT NULL,
    fromdomain character varying(80) DEFAULT NULL,
    insecure character varying(4) DEFAULT NULL,
    "language" character varying(2) DEFAULT NULL,
    mailbox character varying(50) DEFAULT NULL,
    md5secret character varying(80) DEFAULT NULL,
    permit character varying(95) DEFAULT NULL,
    deny character varying(95) DEFAULT NULL,
    mask character varying(95) DEFAULT NULL,
    musiconhold character varying(100) DEFAULT NULL,
    pickupgroup character varying(10) DEFAULT NULL,
    qualify character varying(3) DEFAULT 'yes',
    regexten character varying(80) DEFAULT '' NOT NULL,
    restrictcid character varying(3) DEFAULT NULL,
    rtptimeout character varying(3) DEFAULT NULL,
    rtpholdtimeout character varying(3) DEFAULT NULL,
    secret character varying(80) DEFAULT NULL,
    setvar character varying(100) DEFAULT NULL,
    disallow character varying(100) DEFAULT 'all',
    allow character varying(100) DEFAULT 'g729;ilbc;gsm;ulaw;alaw',
    fullcontact character varying(80) DEFAULT '' NOT NULL,
    ipaddr character varying(15) DEFAULT '' NOT NULL,
    port character varying(5) DEFAULT '' NOT NULL,
    regseconds bigint DEFAULT 0::bigint NOT NULL,
    username character varying(80) DEFAULT '' NOT NULL,
    useragent character varying(255) DEFAULT NULL,
    regserver character varying(80) DEFAULT NULL
);

ALTER TABLE ONLY sip ADD CONSTRAINT sip_name_key UNIQUE (name);
GRANT ALL ON TABLE sip TO callweaver;

extconfig.conf :

sipusers => pgsql,callweaver,sip
sippeers => pgsql,callweaver,sip

res_pgsql.conf

[general]
dsn = host=localhost dbname=callweaver user=callweaver password=callweaver