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

Database Structure Stuff