====== OpenSER 1.0.x Database Table Structure ======
Authors of initial tutorial:
Norman Brandinger
===== 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.1.x/acc.html
** Accounting (//acc//) Table **
Column Name Column Attribute Column Description
caller_UUID varchar(64) Caller Unique User ID (not used by OpenSER ?)
callee_UUID varchar(64) Callee Unique User ID (not used by OpenSER ?)
sip_from varchar(128) From header field indicates the initiator of the request
sip_to varchar(128) To header field specifies the logical recipient of the request
sip_status varchar(128) Response codes are consistent with, and extend, HTTP/1.1
response codes. Not all HTTP/1.1 response codes are appropriate.
SIP defines a new class, 6xx
sip_method varchar(16) A method is the primary function that a request is meant to
invoke on a server
i_uri varchar(128) Inbound Request-URI
o_uri varchar(128) Outbound Request-URI
from_uri varchar(128) From URI
to_uri varchar(128) To URI
sip_callid varchar(128) Call-ID header field uniquely identifies a particular invitation
or all registrations of a particular client.
username varchar(64) Username / Phone Number
domain varchar(128) Domain part of Inbound Request-URI
fromtag varchar(128) 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
totag varchar(128) 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
time datetime Date / Time this record was written
timestamp timestamp(14) Timestamp header field describes when the UAC sent the request
to the UAS
caller_deleted char(1) Caller Deleted (not used by OpenSER ?)
callee_deleted char(1) Callee Deleted (not used by OpenSER ?)
src_leg varchar(128) Source Call Leg (Source-Destination pairs defines a call-leg).
A call leg is another name for a dialog.
dst_leg varchar(128) Destination Call Leg (Source-Destination pairs defines a
call-leg). A call leg is another name for a dialog.
** Accounting (//acc//) Table Indexes **
Keyname Field(s)
INDEX: acc_user (username, domain)
KEY: sip_callid (sip_callid)
===== Active Sessions =====
This table is used by SERWeb. It is not used by OpenSER.
** Active Sessions (//active_sessions// ) Table **
Column Name Column Attribute Column Description
sid varchar(32)
name varchar(32)
val text
changed varchar(14)
** Active Sessions (//active_sessions//) Indexes **
PRIMARY KEY (name, sid)
KEY changed (changed)
===== Administrative Privileges =====
Multi-domain SERWeb Access Control List (ACL). It is not used by OpenSER.
** Administrative Privileges (//admin_privileges//) Table **
Column Name Column Attribute Column Description
username varchar(64) Username / Phone Number
domain varchar(128)
priv_name varchar(64)
priv_value varchar(64)
** Administrative Privileges (//admin_privileges//) Indexes **
PRIMARY KEY (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.1.x/registrar.html
** Aliases (//aliases//) Table **
Column Name Column Attribute Column 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) Received IP:PORT in the format SIP:IP:PORT
path varchar(255) Path Header(s) per RFC 3327
expires datetime Date/Time that this entry expires
q float(10,2) Value used for preferential routing
callid varchar(255) Call-ID header field uniquely identifies a particular invitation or
all registrations of a particular client.
cseq int(11) CSeq header field contains a single decimal sequence number and the
request method
last_modified timestamp(14) 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) Socket used to connect to OpenSER. For example: UDP:IP:PORT
methods int(11) Flags that indicate the SIP Methods this contact will accept.
** Aliases (//aliases//) Indexes **
PRIMARY KEY (username, domain, contact)
INDEX aliases_contact (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.1.x/alias_db.html
** Database Aliases (//dbaliases//) Table **
Column Name Column Attribute Column 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**
UNIQUE KEY alias_key (alias_username, alias_domain)
INDEX alias_user (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.1.x/domain.html
** Domains (//domain//) Table **
Column Name Column Attribute Column Description
domain varchar(128) Domain Name
last_modified datetime Date/Time this record was last modified
** Domains (//domain//) Indexes**
PRIMARY KEY (domain)
===== 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.1.x/group.html
** Groups (//grp//) Table **
Column Name Column Attribute Column Description
username varchar(64) Username / Phone Number
domain varchar(128) Domain Name
grp varchar(50) Group Name
last_modified datetime Date/Time this record was last modified
** Groups (//grp//) Indexes **
PRIMARY KEY (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.1.x/lcr.html
** Gateways (//gw//) Table **
Column Name Column Attribute Column Description
gw_name varchar(128) Gateway Name
grp_id int Gateway ID
ip_addr int IP Address of the gateway
port smallint Port of the gateway
uri_scheme tinyint URI scheme of the gateway
transport tinyint Transport type to be used for the gateway
strip tinyint The number of digits to strip from the RURI before applying the prefix
prefix varchar(16) The RURI(destination) prefix
** Gateways (//gw//) Indexes **
PRIMARY KEY (gw_name)
KEY (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.1.x/lcr.html
** Gateway Groups (//gw_grp//) Table **
Column Name Column Attribute Column Description
grp_id int unsigned Group ID
grp_name varchar(64) Group Name
===== 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.1.x/lcr.html
** Least Cost Routing (//lcr//) Table **
Column Name Column Attribute Column Description
prefix varchar(16) The Request-URI (destination) prefix
from_uri varchar(128) The FROM (source) URI
grp_id int unsigned Group ID
priority tinyint unsigned Priority
** Least Cost Routing (//lcr//) Indexes **
KEY (prefix)
KEY (from_uri)
KEY (grp_id)
===== User Locations =====
Persistent user location information
More information can be found at: http://www.openser-project.org/docs/modules/1.1.x/usrloc.html
** User Locations (//location//) Table **
Column Name Column Attribute Column 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) Received IP:PORT in the format SIP:IP:PORT
path varchar(255) Path Header(s) per RFC 3327
expires datetime Expires header field gives the relative time after which the message
(or content) expires
q float(10,2) Value used for preferential routing
callid varchar(255) Call-ID header field uniquely identifies a particular invitation or all
registrations of a particular client.
cseq int(11) CSeq header field contains a single decimal sequence number and the request method
last_modified timestamp(14) Date/Time this record was last modified
flags int(11) Internal Flags
user_agent varchar(255) User-Agent header field contains information about the UAC originating the request
socket varchar(128) Socket used to connect to OpenSER. For example: UDP:IP:PORT
methods int(11) Methods accepted
** User Locations (//location//) Indexes **
PRIMARY KEY (username, domain, contact)
===== Missed Calls =====
acc like table for keeping track of missed calls
**Missed Calls (//missed_calls//) Table**
Column Name Column Attribute Column Description
sip_from varchar(128) From header field indicates the initiator of the request
sip_to varchar(128) To header field specifies the logical recipient of the request
sip_status varchar(128) Response codes are consistent with, and extend, HTTP/1.1 response codes.
Not all HTTP/1.1 response codes are appropriate. SIP defines a new class, 6xx
sip_method varchar(16) A method is the primary function that a request is meant to invoke on a server
i_uri varchar(128) Inbound Request-URI
o_uri varchar(128) Outbound Request-URI
from_uri varchar(128) From URI
to_uri varchar(128) To URI
sip_callid varchar(128) Call-ID header field uniquely identifies a particular invitation or all
registrations of a particular client.
username varchar(64) Username / Phone Number
domain varchar(128) Domain Name
fromtag varchar(128) 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
totag varchar(128) 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
time datetime Date / Time this record was written
timestamp timestamp(14) Timestamp header field describes when the UAC sent the request to the UAS
src_leg varchar(128) Source Call Leg (Source-Destination pairs defines a call-leg).
A call leg is another name for a dialog.
dst_leg varchar(128) Destination Call Leg (Source-Destination pairs defines a call-leg).
A call leg is another name for a dialog.
** Missed Calls (//missed_calls//) Indexes **
INDEX mc_user (username, domain)
===== Pending =====
SerWEB - Not used by OpenSER.
Unconfirmed subscription requests
** Pending (//pending//) Table **
Column Name Column Attribute Column Description
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 Date / Time this record was created
datetime_modified datetime Date / Time this record was last modified
confirmation varchar(64)
flag char(1)
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)
timezone varchar(128)
rpid varchar(128) 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.
domn int(10)
uuid varchar(64) Unique User ID
** Pending (//pending//) Indexes **
PRIMARY KEY (username, domain)
KEY user_2 (username)
UNIQUE KEY phplib_id (phplib_id)
===== Phone Book =====
SERWeb - Not used by OpenSER. User's Phonebook
** Phone Book (//phonebook//) Table **
Column Name Column Attribute Column Description
id int(10) 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 **
PRIMARY KEY (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.1.x/group.html
** Regular Expression Group (//re_grp//) Table **
Column Name Column Attribute Column Description
reg_exp varchar(128) Regular Expression
group_id int(11) Group ID
** Regular Expression Group (//re_grp//) Indexes **
UNIQUE KEY reg_exp (reg_exp)
===== Server Monitoring =====
SERWeb - Not used by OpenSER
** Server Monitoring (//server_monitoring//) Table **
Column Name Column Attribute Column Description
time datetime
id int(10)
param varchar(32)
value int(10)
increment int(10)
** Server Monitoring (//server_monitoring//) Indexes **
PRIMARY KEY (id, param)
===== Server Monitoring =====
SERWeb - Not used by OpenSER
** Server Monitoring (//server_monitoring_agg//) Table**
Column Name Column Attribute Column Description
param varchar(32)
s_value int(10)
s_increment int(10)
last_aggregated_increment int(10)
av float
mv int(10)
ad float
lv int(10)
min_val int(10)
max_val int(10)
min_inc int(10)
max_inc int(10)
lastupdate datetime Date/Time this record was last modified
** Server Monitoring (//server_monitoring_agg//) Indexes **
PRIMARY KEY (param)
===== 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.1.x/msilo.html
** Offline Message Storage (//silo//) Table **
Column Name Column Attribute Column Description
mid integer Unique ID per message
src_addr varchar(255) Source address - From URI
dst_addr varchar(255) Destination address - To URI
r_uri varchar(255) Request-URI == username@domain (for compatibility with old version)
username varchar(64) Username / Phone Number
domain varchar(128) Domain
inc_time integer Incoming time
exp_time integer Expiration time
snd_time integer Reminder send time
ctype varchar(32) Content type
body blob Body of the message
===== 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.1.x/speeddial.html
** Speed Dial (//speed_dial//) Table**
Column Name Column Attribute Column 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 **
PRIMARY KEY (username, domain, sd_domain, sd_username)
===== Subscriber =====
This table is used to provide authentication information
More information about the speeddial module can be found at: http://www.openser-project.org/docs/modules/1.1.x/auth_db.html
** Subscriber (//subscriber//) Table **
Column Name Column Attribute Column Description
phplib_id varchar(32) Unique ID (used by SERWeb)
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 Date / Time this record was created
datetime_modified datetime Date / Time this record was last modified
confirmation varchar(64)
flag char(1)
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)
timezone varchar(128)
rpid varchar(128) 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.
domn int(10)
uuid varchar(64) Unique User ID
** Subscriber (//subscriber//) Indexes **
UNIQUE KEY phplib_id (phplib_id)
PRIMARY KEY (username, domain)
KEY user_2 (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.1.x/permissions.html
** Trusted (//trusted//) Table **
Column Name Column Attribute Column Description
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) regular expression matches From URI of request
** Trusted (//trusted//) Indexes **
PRIMARY KEY (src_ip, proto, from_pattern)
===== 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.1.x/uri_db.html
** URI (//uri//) Table **
Column Name Column Attribute Column Description
username varchar(64) Username / Phone Number
domain varchar(128) Domain Name
uri_user varchar(50) Username / Phone Number
last_modified datetime Date/Time this record was last modified
** URI (//uri//) Indexes **
PRIMARY KEY (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.1.x/avpops.html
** User Preferences (//usr_preferences//) Table **
Column Name Column Attribute Column Description
uuid varchar(64) Unique User ID
username varchar(100) Username / Phone Number
domain varchar(128) Domain Name
attribute varchar(32) AVP Attribute
type int(11) AVP Type
value varchar(128) AVP Value
modified timestamp(14) Date/Time this record was last modified
** User Preferences (//usr_preferences//) Indexes **
PRIMARY KEY (uuid, username, domain, attribute, type,v alue)
INDEX ua_idx (uuid, attribute)
INDEX uda_idx (username, domain, attribute)
===== User Preference Types =====
** User Preference Types (//usr_preferences_types//) Table **
Column Name Column Attribute Column Description
att_name varchar(32)
att_rich_type varchar(32)
att_raw_type int
att_type_spec text
default_value varchar(100)
** User Preference Types (//usr_preferences_types//) Indexes **
PRIMARY KEY (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 **
Column Name Column Attribute Column Description
table_name varchar(64) Table Name
table_version smallint(5) Table Version
===== Database Structure Stuff =====
{{indexmenu>database|js}}