Table of Contents

OpenSER 1.2.x Database Table Structure

Authors of initial tutorial:
  Norman Brandinger

last updated: 2009/02/27 20:56

For initial database structure you can also use the script openser_mysql.sh create which comes with the package. You can edit the script and modify e.g. database, user, password to your needs. Usually it's located in /usr/local/sbin after installation of openser (src distribution).

Accounting

This table is used by the ACC module to report on transactions - accounted calls.

More information is available at: http://www.openser-project.org/docs/modules/1.2.x/acc.html

Accounting (acc) Table

Field Type Attributes Description
id int(10) UNSIGNED auto_increment Unique ID per record
method varchar(16) A method is the primary function that a request is meant to invoke on a server
from_tag varchar(64) The tag parameter serves as a general mechanism to identify a dialog, which is the combination of the Call-ID along with two tags, one from participant in the dialog
to_tag varchar(64) The tag parameter serves as a general mechanism to identify a dialog, which is the combination of the Call-ID along with two tags, one from participant in the dialog
callid varchar(128) Call-ID header field uniquely identifies a particular invitation or all registrations of a particular client.
s ip_code char(3) SIP reply code
sip_reason varchar(32) SIP reply reason
time datetime Date / Time this record was written.

Accounting (acc) Table Indexes

Keyname Type Field
PRIMARY Primary id
acc_callid Index callid

Active Sessions

This table is used by SERWeb. It is not used by OpenSER. Active Sessions (active_sessions ) Table

Field Type Default Description
sid varchar(32) SERWeb session id
name varchar(32)
val text NULL Serialized value of web session
changed varchar(14)

Active Sessions (active_sessions) Indexes

Keyname Type Field
PRIMARY Primary name, sid
changed Index changed

Address

This table is used by permissions module.

Address (address) Table Indexes

Field Type Attributes Default Description
id bigint(20) auto_increment
grp smallint(5) UNSIGNED 0
ip_addr varchar(15)
mask tinyint(4) 32
port smallint(5) UNSIGNED 0

Address (address) Table Indexes

Keyname Type Field
PRIMARY Primary id

Administrative Privileges

Multi-domain SERWeb Access Control List (ACL). It is not used by OpenSER.

Administrative Privileges (admin_privileges) Table

It is used for multidomain serweb ACL control

Field Type Description
username varchar(64) Username / Phone Number
domain varchar(128) Domain part of user's SIP URI
priv_name varchar(64) Privilege name
priv_value varchar(64) Privilege value

Administrative Privileges (admin_privileges) Indexes

Keyname Type Field
PRIMARY Primary username, priv_name, priv_value, domain

Aliases

This table is similar to the “location” table.

More information is available at: http://www.openser-project.org/docs/modules/1.2.x/registrar.html

Aliases (aliases) Table

Field Type Default Description
username varchar(64) Alias Username / Phone Number
domain varchar(128) Domain Name
contact varchar(255) Contact header field value provides a URI whoses meaning depends on the type of request or response it is in.
received varchar(255) NULL Received IP:PORT in the format SIP:IP:PORT
path varchar(255) NULL Path Header(s) per RFC 3327
expires datetime 2020-05-28 21:32:15 Date/Time that this entry expires.
q float(10,2) 1.00 Value used for preferential routing.
callid varchar(255) Default-Call-ID Call-ID header field uniquely identifies a particular invitation or all registrations of a particular client.
cseq int(11) 13 CSeq header field contains a single decimal sequence number and the request method.
last_modified datetime 1900-01-01 00:00:00 Date/Time this entry was last changed.
flags int(11) Flags
user_agent varchar(255) User-Agent header field contains information about the UAC originating the request.
socket varchar(128) NULL Socket used to connect to OpenSER. For example: UDP:IP:PORT
methods int(11) NULL Flags that indicate the SIP Methods this contact will accept.

Aliases (aliases) Indexes

Keyname Type Field
PRIMARY Primary username, domain, contact
aliases_contact Index contact

Database Aliases

This table us used by the alias_db module as an alternative for user aliases via userloc.

More information about the alias_db module can be found at: http://www.openser-project.org/docs/modules/1.2.x/alias_db.html

Database Aliases (dbaliases) Table

Field Type Description
alias_username varchar(64) Alias Username / Phone Number
alias_domain varchar(128) Alias Domain Name
username varchar(64) Username / Phone Number
domain varchar(128) Domain Name

Database Aliases (dbaliases) Indexes

Keyname Type Field
alias_key Unique alias_username, alias_domain
alias_user Index username, domain

Domains

This table is used by the domain module to determine if a host part of a URI is “local” or not.

More information about the domain module can be found at: http://www.openser-project.org/docs/modules/1.2.x/domain.html

Domains (domain) Table

Field Type Default Description
domain varchar(128) Domain Name
last_modified datetime 0000-00-00 00:00:00 Date/Time this record was last modified

Domains (domain) Indexes

Keyname Type Field
PRIMARY Primary domain

Domain Policy

More information about the domainpolicy module can be found at: http://www.openser-project.org/docs/modules/1.2.x/domainpolicy.html

Domain Policy (domainpolicy) Table

Field Type Attributes Default Description
id int(11) auto_increment Unique ID per record
rule varchar(255) Domain policy rule name which is equal to the URI as published in the domain policy NAPTRs.
type varchar(255) Domain policy rule type. In the case of federation names, this is “fed”. For standard referrals according to draft-lendl-speermint-technical-policy-00, this is “std”. For direct domain lookups, this is “dom”. Default value is “type”.
att varchar(255) NULL It contains the AVP's name. If the rule stored in this row triggers, than dp_can_connect() will add an AVP with that name.
val varchar(255) NULL It contains the values for AVPs created by dp_can_connect(). Default value is “val”.
comment varchar(255) NULL Comments about the rule

Domain Policy Indexes

Keyname Type Field
PRIMARY Primary id, rule
rule Unique rule, att, val
rule_idx Index rule

Groups

This table us used by the group module as a means of group membership checking. Used primarily for Access Control Lists (ACL's)

More information about the group module can be found at: http://www.openser-project.org/docs/modules/1.2.x/group.html

Groups (grp) Table

Field Type Default Description
username varchar(64) Username / Phone Number
domain varchar(128) Domain Name
grp varchar(50) Group Name
last_modified datetime 0000-00-00 00:00:00 Date/Time this record was last modified

Groups (grp) Indexes

Keyname Type Field
PRIMARY Primary username, domain, grp

Gateways

This table contains Least Cost Routing Gateway definitions

More information can be found at: http://www.openser-project.org/docs/modules/1.2.x/lcr.html

Gateways (gw) Table

Field Type Attributes Default Description
gw_name varchar(128) Gateway Name
grp_id int(10) UNSIGNED Gateway ID
ip_addr int(10) UNSIGNED IP Address of the gateway
port smallint(5) UNSIGNED NULL Port of the gateway
uri_scheme tinyint(3) UNSIGNED NULL URI scheme of the gateway
transport tinyint(3) UNSIGNED NULL Transport type to be used for the gateway
strip tinyint(3) UNSIGNED NULL The number of digits to strip from the RURI before applying the prefix.
prefix varchar(16) NULL The R-URI(destination) prefix

Gateways (gw) Indexes

Keyname Type Field
PRIMARY Primary gw_name
grp_id Index grp_id

Gateway Groups

This table is used for administrative purposes only to associate names with gateway group ids

More information can be found at: http://www.openser-project.org/docs/modules/1.2.x/lcr.html

Gateway Groups (gw_grp) Table

Field Type Attributes Description
grp_id int(10) UNSIGNED auto_increment Group ID
grp_name varchar(64) Group Name

Gateway Groups (gw_grp) Indexes

Keyname Type Field
PRIMARY Primary grp_id

Least Cost Routing

This table is used by the lcr (Least Cost Routing) rules

More information about the lcr module can be found at: http://www.openser-project.org/docs/modules/1.2.x/lcr.html

Least Cost Routing (lcr) Table

Field Type Attributes Default Description
prefix varchar(16) The Request-URI (destination) prefix
from_uri varchar(128) NULL The FROM (source) URI
grp_id int(10 UNSIGNED Group ID
priority tinyint(3) UNSIGNED Priority

Least Cost Routing (lcr) Indexes

Keyname Type Field
prefix Index prefix
from_uri Index from_uri
grp_id Index grp_id

User Locations

Persistent user location information

More information can be found at: http://www.openser-project.org/docs/modules/1.2.x/usrloc.html

User Locations (location) Table

Field Type Default Description
username varchar(64) Username / Phone Number
domain varchar(128) Domain Name
contact varchar(255) Contact header field value provides a URI whose meaning depends on the type of request or response it is in
received varchar(255) NULL Received IP:PORT in the format SIP:IP:PORT
path varchar(255) NULL Path Header(s) per RFC 3327
expires datetime 2020-05-28 21:32:15 Expires header field gives the relative time after which the message (or content) expires
q float(10,2) 1.00 Value used for preferential routing
callid varchar(255) Default-Call-ID Call-ID header field uniquely identifies a particular invitation or all registrations of a particular client.
cseq int(11) 13 CSeq header field contains a single decimal sequence number and the request method.
last_modified datetime 1900-01-01 00:00:00 Date/Time this record was last modified
flags int(11) 0 Internal Flags
user_agent varchar(255) User-Agent header field contains information about the UAC originating the request.
socket varchar(128) NULL Socket used to connect to OpenSER. For example: UDP:IP:PORT.
methods int(11) NULL Methods accepted.

User Locations (location) Indexes

Keyname Type Field
PRIMARY Primary username, domain, contact

Missed Calls

acc like table for keeping track of missed calls

Missed Calls (missed_calls) Table

Field Type Attributes Description
id int(10) UNSIGNED auto_increment Unique ID per record
method varchar(16) A method is the primary function that a request is meant to invoke on a server.
from_tag varchar(64) The tag parameter serves as a general mechanism to identify a dialog, which is the combination of the Call-ID along with two tags, one from participant in the dialog.
to_tag varchar(64) The tag parameter serves as a general mechanism to identify a dialog, which is the combination of the Call-ID along with two tags, one from participant in the dialog.
callid varchar(128) Call-ID header field uniquely identifies a particular invitation or all registrations of a particular client.
sip_code char(3) Code of the SIP reply
sip_reason varchar(32) Reason phrase of the SIP reply
time datetime Date / Time this record was written.

Missed Calls (missed_calls) Indexes

Keyname Type Field
PRIMARY Primary id
acc_callid Index callid

Prefix-Domain Translation

Prefix-Domain Translation means to change the host and port in R-URI, based on the prefix found in R-URI and source domain (that is domain in From-URI).

More information can be found at: http://www.openser-project.org/docs/modules/1.2.x/pdt.html

Prefix-Domain Translation (pdt) Table

Field Type Description
sdomain varchar(255) Source Domain.
prefix varchar(32) Prefix found in the username part of R-URI.
domain varchar(255) Domain corresponding to (sdomain, prefix) pair where the message must be sent.

Prefix-Domain Translation (pdt) Table Indexes

Keyname Type Field
PRIMARY Primary sdomain, prefix

Pending

SerWEB - Not used by OpenSER – should have same structure as table subscriber.

Unconfirmed subscription requests

Pending (pending) Table

Field Type Attributes Default Description
id int(10) UNSIGNED auto_increment Unique ID per record
phplib_id varchar(32) Unique ID
username varchar(64) Username / Phone Number
domain varchar(128) Domain Name
password varchar(25) Password
first_name varchar(25) First Name
last_name varchar(45) Last Name
phone varchar(15) Phone Number
email_address varchar(50) Email Address
datetime_created datetime 0000-00-00 00:00:00 Date / Time this record was created
datetime_modified datetime 0000-00-00 00:00:00 Date / Time this record was last modified
confirmation varchar(64)
flag char(1) o
sendnotification varchar(50)
greeting varchar(50)
ha1 varchar(128) md5(username:realm:password)
ha1b varchar(128) md5(username@domain:realm:password)
allow_find char(1) 0
timezone varchar(128) NULL
rpid varchar(128) NULL The SIP Remote-Party-ID header identifies the calling party and includes user, party, screen and privacy headers that specify how a call is presented and screened.

Pending (pending) Indexes

Keyname Type Field
PRIMARY Primary id
user_id Unique username, domain
phplib_id Unique phplib_id
username_id Index username

Phone Book

SERWeb - Not used by OpenSER. User's Phonebook

Phone Book (phonebook) Table

Field Type Attributes Description
id int(10) UNSIGNED auto_increment ID of this record
username varchar(64) Username / Phone Number
domain varchar(128) Domain Name
fname varchar(32) First Name
lname varchar(32) Last Name
sip_uri varchar(128) SIP URI associated with this record

Phone Book (phonebook) Indexes

Keyname Type Field
PRIMARY Primary id

Regular Expression Group

This table is used by the group module to check membership based on regular expressions

More information about the group module can be found at: http://www.openser-project.org/docs/modules/1.2.x/group.html

Regular Expression Group (re_grp) Table

Field Type Default Description
reg_exp varchar(128) 0 Regular Expression
group_id int(11) Group ID

Regular Expression Group (re_grp) Indexes

Keyname Type Field
reg_exp UNIQUE reg_exp

Server Monitoring

SERWeb - Not used by OpenSER

Server Monitoring (server_monitoring) Table

Field Type Attributes Default Description
time datetime 0000-00-00 00:00:00
id int(10) UNSIGNED
param varchar(32)
value int(10) 0
increment int(10) 0

Server Monitoring (server_monitoring) Indexes

Keyname Type Field
PRIMARY Primary id, param

Server Monitoring

SERWeb - Not used by OpenSER

Server Monitoring (server_monitoring_agg) Table

Field Type Default Description
param varchar(32)
s_value int(10) 0
s_increment int(10) 0
last_aggregated_increment int(10) 0
av float 0
mv int(10) 0
ad float 0
lv int(10) 0
min_val int(10) 0
max_val int(10) 0
min_inc int(10) 0
max_inc int(10) 0
lastupdate datetime 0000-00-00 00:00:00 Date/Time this record was last modified.

Server Monitoring (server_monitoring_agg) Indexes

Keyname Type Field
PRIMARY Primary param

SIP Trace

This table is used to store incoming/outgoing SIP messages in database. How this can be done you find out reading http://www.openser-project.org/docs/modules/1.2.x/siptrace.html.

SIP Trace (sip_trace) Table

Field Type Attributes Default Description
id bigint(20) auto_increment unique auto increment ID per message
date datetime 0000-00-00 00:00:00 recording date
callid varchar(254) call id from SIP message
traced_user varchar(128) SIP URI of the user being traced
msg text full SIP message
method varchar(50) SIP method name
status varchar(254) SIP reply status
fromip varchar(50) source IP address
toip varchar(50) destination IP address
fromtag varchar(64) From tag
direction varchar(4) direction of the SIP message (in, out)

SIP Trace (sip_trace) Indexes

Keyname Type Field
PRIMARY Primary id
user_idx Index traced_user
date_id Index date
ip_idx Index fromip
call_id Index callid

Offline Message Storage

This table us used by the msilo module to provide offline message storage

More information about the msilo module can be found at: http://www.openser-project.org/docs/modules/1.2.x/msilo.html

Offline Message Storage (silo) Table

Field Type Attributes Default Description
mid int(11) auto_increment Unique ID per message
src_addr varchar(255) Source address - From URI
dst_addr varchar(255) Destination address - To URI
username varchar(64) Username / Phone Number of target user
domain varchar(128) SIP domain of target user
inc_time int(11) 0 Incoming time
exp_time int(11) 0 Expiration time
snd_time int(11) 0 Reminder send time
ctype varchar(32) text/plain Content type
body blob BINARY Body of the message

Offline Message Storage (silo) Indexes

Keyname Type Field
PRIMARY Primary mid
username Index username, domain

Speed Dial

This table is used by the speeddial module to provide on-server speed dial facilities

More information about the speeddial module can be found at: http://www.openser-project.org/docs/modules/1.2.x/speeddial.html

Speed Dial (speed_dial) Table

Field Type Description
uuid varchar(64) Unique User ID
username varchar(64) Username / Phone Number
domain varchar(128) Domain Name
sd_username varchar(64) Speed Dial Username
sd_domain varchar(128) Speed Dial Domain
new_uri varchar(192) New URI
fname varchar(128) First Name
lname varchar(128) Last Name
description varchar(64) Description

Speed Dial (speed_dial) Indexes

Keyname Type Field
PRIMARY Primary username, domain, sd_domain, sd_username

Subscriber

This table is used to provide authentication information

More information about the auth_db module can be found at: http://www.openser-project.org/docs/modules/1.2.x/auth_db.html

Subscriber (subscriber) Table

Field Type Attributes Default Description
id int(10) UNSIGNED auto_increment Unique ID per record
username varchar(64) Username / Phone Number
domain varchar(128) Domain Name
password varchar(25) Password
first_name varchar(25) First Name
last_name varchar(45) Last Name
email_address varchar(50) Email Address
datetime_created datetime 0000-00-00 00:00:00 Date / Time this record was created
ha1 varchar(128) md5(username:realm:password)
ha1b varchar(128) md5(username@domain:realm:password)
timezone varchar(128) NULL user's time zone
rpid varchar(128) NULL The SIP Remote-Party-ID header identifies the calling party and includes user, party, screen and privacy headers that specify how a call is presented and screened.
— serweb specific columns —
phplib_id varchar(32) Unique ID (used by SERWeb)
phone varchar(15) Phone Number
datetime_modified datetime 0000-00-00 00:00:00 Date / Time this record was last modified
confirmation varchar(64)
flag char(1) o Flags per user
sendnotification varchar(50)
greeting varchar(50)
allow_find char(1) 0 Flag to allow others to find the SIP address when searching using the name

Subscriber (subscriber) Indexes

Keyname Type Field
PRIMARY Primary id
user_id Unique username, domain
phplib_id Unique phplib_id
username_id Index username

Trusted

This table is used by the permissions module to determine if a call has the appropriate permission to be established

More information about the permissions module can be found at: http://www.openser-project.org/docs/modules/1.2.x/permissions.html

Trusted (trusted) Table

Field Type Attributes Default Description
id bigint(20) auto_increment Unique ID per record
src_ip varchar(39) Source address is equal to source address of request
proto varchar(4) Transport protocol is either “any” or equal to transport protocol of request. Possible values that can be stored are “any”, “udp”, “tcp”, “tls”, and “sctp”.
from_pattern varchar(64) NULL Regular expression matches From URI of request.
tag varchar(32) NULL

Trusted (trusted) Indexes

Keyname Type Field
PRIMARY Primary id
Key1 Index src_ip

URI

This table is used by uri_db module to implement various SIP URI checks.

A configuration parm: modparam(“uri_db”, “use_uri_table”, 1) means that the (uri) table should be checked instead of the (subscriber) table.

More information about the uri_db module can be found at: http://www.openser-project.org/docs/modules/1.2.x/uri_db.html

URI (uri) Table

Field Type Default Description
username varchar(64) Username / Phone Number
domain varchar(128) Domain Name
uri_user varchar(50) Username / Phone Number
last_modified datetime 0000-00-00 00:00:00 Date/Time this record was last modified

URI (uri) Indexes

Keyname Type Field
PRIMARY Primary username, domain, uri_user

User Preferences

This table us used by the avpops module to implement Attribute Value Pairs (AVP's)

More information about the avpops module can be found at: http://www.openser-project.org/docs/modules/1.2.x/avpops.html

User Preferences (usr_preferences) Table

Field Type Attributes Default Description
id bigint(20) auto_increment Unique ID per record
uuid varchar(64) Unique User ID
username varchar(100) 0 Username / Phone Number
domain varchar(128) Domain Name
attribute varchar(32) AVP Attribute
type int(11) 0 AVP Type
value varchar(128) AVP Value
last_modified timestamp(14) ON UPDATE CURRENT_TIMESTAMP CURRENT_TIMESTAMP Date/Time this record was last modified

User Preferences (usr_preferences) Indexes

Keyname Type Field
PRIMARY Primary id
ua_idx Index uuid, attribute
ida_idx Index username, domain, attribute

User Preference Types

User Preference Types (usr_preferences_types) Table

Field Type Default Description
att_name varchar(32)
att_rich_type varchar(32) string
att_raw_type int(11) 2
att_type_spec text NULL
default_value varchar(100)

User Preference Types (usr_preferences_types) Indexes

Keyname Type Field
PRIMARY Primary att_name

Table Versions

This table contains OpenSER table names and version numbers. It is used by various OpenSER routines to ensure that the correct version of a particular table is being used.

Table Versions (version) Table

Field Type Default Description
table_name varchar(64) Table Name
table_version smallint(5) 0 Table Version

Table Versions (version) Indeses

Keyname Type Field
PRIMARY Primary table_name

Database Structure Stuff