Table of Contents
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
