– Kamailio SIP Server –

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
database:1.0.x [2006/12/21 19:52] – 86.121.146.165database:1.0.x [2012/03/22 12:30] (current) – removed spam 80.250.1.245
Line 1: Line 1:
 +====== OpenSER 1.0.x Database Table Structure ======
 +
 +
 +<code>
 +Authors of initial tutorial:
 +Norman Brandinger
 +</code>
 +
 +===== 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 **
 +<code>
 +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.
 +</code>
 +
 +
 +** Accounting (//acc//) Table Indexes **
 +<code>
 +  Keyname            Field(s)
 +
 +INDEX: acc_user       (username, domain)
 +
 +KEY: sip_callid       (sip_callid)
 +</code>
 +
 +
 +===== Active Sessions =====
 +
 +This table is used by SERWeb. It is not used by OpenSER.
 +** Active Sessions (//active_sessions// ) Table **
 +<code>
 +Column Name Column Attribute Column Description
 +sid          varchar(32)  
 +name          varchar(32)  
 +val      text  
 +changed          varchar(14)  
 +</code>
 +
 +** Active Sessions (//active_sessions//) Indexes **
 +<code>
 +PRIMARY KEY (name, sid)
 +KEY         changed (changed)
 +</code>
 +
 +===== Administrative Privileges  =====
 +
 +Multi-domain SERWeb Access Control List (ACL). It is not used by OpenSER.
 +
 +** Administrative Privileges (//admin_privileges//) Table **
 +<code>
 +Column Name Column Attribute Column Description
 +
 +username varchar(64)        Username / Phone Number
 +domain         varchar(128)  
 +priv_name varchar(64)  
 +priv_value varchar(64)  
 +</code>
 +
 +** Administrative Privileges (//admin_privileges//) Indexes **
 +<code>
 +PRIMARY KEY (username, priv_name, priv_value, domain)
 +</code>
 +
 +===== 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 **
 +<code>
 +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.
 +</code>
 +
 +** Aliases (//aliases//) Indexes **
 +<code>
 +PRIMARY KEY (username, domain, contact)
 +INDEX         aliases_contact (contact)
 +</code>
 +
 +===== 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 **
 +<code>
 +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
 +</code>
 +
 +** Database Aliases (//dbaliases//) Indexes**
 +<code>
 +UNIQUE KEY alias_key (alias_username, alias_domain)
 +INDEX    alias_user (username, domain)
 +</code>
 +
 +
 +===== 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 **
 +<code>
 +Column Name Column Attribute Column Description
 +
 +domain          varchar(128)      Domain Name
 +last_modified datetime      Date/Time this record was last modified
 +</code>
 +
 +** Domains (//domain//) Indexes**
 +<code>
 +PRIMARY KEY (domain)
 +</code>
 +
 +
 +===== 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 **
 +<code>
 +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
 +</code>
 +
 +** Groups (//grp//) Indexes **
 +<code>
 +PRIMARY KEY (username, domain, grp)
 +</code>
 +
 +
 +===== 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 **
 +<code>
 +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
 +</code>
 +
 +** Gateways (//gw//) Indexes **
 +<code>
 +PRIMARY KEY (gw_name)
 +KEY         (grp_id)
 +</code>
 +
 +===== 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 **
 +<code>
 +Column Name Column Attribute Column Description
 +
 +grp_id           int unsigned  Group ID
 +grp_name   varchar(64)    Group Name
 +</code>
 +
 +===== 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 **
 +<code>
 +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
 +</code>
 +
 +** Least Cost Routing (//lcr//) Indexes **
 +<code>
 +KEY (prefix)
 +KEY (from_uri)
 +KEY (grp_id)
 +</code>
 +
 +
 +===== 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 **
 +<code>
 +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
 +</code>
 +
 +** User Locations (//location//) Indexes **
 +<code>
 +PRIMARY KEY (username, domain, contact)
 +</code>
 +
 +
 +===== Missed Calls =====
 +acc like table for keeping track of missed calls
 +
 +**Missed Calls (//missed_calls//) Table**
 +<code>
 +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.
 +</code>
 +
 +** Missed Calls (//missed_calls//) Indexes **
 +<code>
 +INDEX mc_user (username, domain)
 +</code>
 +
 +===== Pending =====
 +SerWEB - Not used by OpenSER.
 +
 +Unconfirmed subscription requests
 +
 +** Pending (//pending//) Table **
 +<code>
 +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
 +</code>
 +
 +** Pending (//pending//) Indexes **
 +<code>
 +PRIMARY KEY (username, domain)
 +KEY         user_2 (username)
 +UNIQUE KEY phplib_id (phplib_id)
 +</code>
 +
 +===== Phone Book =====
 +SERWeb - Not used by OpenSER. User's Phonebook
 +
 +** Phone Book (//phonebook//) Table **
 +<code>
 +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
 +</code>
 +
 +** Phone Book (//phonebook//) Indexes **
 +<code>
 +PRIMARY KEY (id)
 +</code>
 +
 +===== 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 **
 +<code>
 +Column Name Column Attribute Column Description
 +
 +reg_exp         varchar(128)        Regular Expression
 +group_id int(11)                Group ID
 +</code>
 +
 +** Regular Expression Group (//re_grp//) Indexes **
 +<code>
 +UNIQUE KEY reg_exp (reg_exp)
 +</code>
 +
 +===== Server Monitoring =====
 +SERWeb - Not used by OpenSER
 +
 +** Server Monitoring (//server_monitoring//) Table **
 +<code>
 +Column Name Column Attribute Column Description
 +
 +time          datetime  
 +id          int(10)  
 +param          varchar(32)  
 +value          int(10)  
 +increment int(10)  
 +</code>
 +
 +** Server Monitoring (//server_monitoring//) Indexes **
 +<code>
 +PRIMARY KEY (id, param)
 +</code>
 +
 +===== Server Monitoring =====
 +SERWeb - Not used by OpenSER
 +
 +** Server Monitoring (//server_monitoring_agg//) Table**
 +<code>
 +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
 +</code>
 +
 +** Server Monitoring (//server_monitoring_agg//) Indexes **
 +<code>
 +PRIMARY KEY (param)
 +</code>
 +
 +===== 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 **
 +<code>
 +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
 +</code>
 +
 +
 +===== 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**
 +<code>
 +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
 +</code>
 +
 +** Speed Dial (//speed_dial//) Indexes **
 +<code>
 +PRIMARY KEY (username, domain, sd_domain, sd_username)
 +</code>
 +
 +===== 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 **
 +<code>
 +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
 +</code>
 +
 +** Subscriber (//subscriber//) Indexes **
 +<code>
 +UNIQUE KEY phplib_id (phplib_id)
 +PRIMARY KEY (username, domain)
 +KEY         user_2 (username)
 +</code>
 +
 +
 +===== 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 **
 +<code>
 +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
 +</code>
 +
 +** Trusted (//trusted//) Indexes **
 +<code>
 +PRIMARY KEY (src_ip, proto, from_pattern)
 +</code>
 +
 +
 +===== 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 **
 +<code>
 +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
 +</code>
 +
 +
 +** URI (//uri//) Indexes **
 +<code>
 +PRIMARY KEY (username, domain, uri_user)
 +</code>
 +
 +
 +===== 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 **
 +<code>
 +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
 +</code>
 +
 +** User Preferences (//usr_preferences//) Indexes **
 +<code>
 +PRIMARY KEY (uuid, username, domain, attribute, type,v alue)
 +INDEX         ua_idx (uuid, attribute)
 +INDEX         uda_idx (username, domain, attribute)
 +</code>
 +
 +
 +===== User Preference Types =====
 +** User Preference Types (//usr_preferences_types//) Table **
 +<code>
 +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)  
 +</code>
 +
 +** User Preference Types (//usr_preferences_types//) Indexes **
 +<code>
 +PRIMARY KEY (att_name)
 +</code>
 +
 +
 +
 +===== 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 **
 +<code>
 +Column Name   Column Attribute   Column Description
 +
 +table_name varchar(64)   Table Name
 +table_version smallint(5)   Table Version
 +</code>
 +
 +
 +===== Database Structure Stuff =====
 +
 +{{indexmenu>database|js}}
  

Trace: • db-schema • 3.1.x