The postgreSQL database is a relational database management system. A database in this management system stores entities such as carriers, rules, contacts, routes, and the relationships among the entities. The management system can manage many different databases simultaneously. The CSR creates a database in this system and performs call routing based on the data in the database.
To activate the postgreSQL database, perform the following steps:
Step 1 Log in to the Solaris system by entering postgres as the user.
A "postgres" password prompt may appear.
Step 2 Enter the following command:
/etc/init.d/postgres_init start
This command calls postgreSQL command postmaster -S -i -B 40 -N 20 -D /usr/local/pgsql/data. The parameters are described as follows:
-S—Tells the postmaster to run in silent mode. This is not recorded to a log file.
-i—Tells postmaster to allow network connections. If this is not set, the postmaster accepts connections only from the local host.
Note This is needed for the GUI to connect to the database even when it is running on the same
machine.
-B and -N—Set up shared memory segments.
-D—Tells the postmaster the location of the database files.
Note For servers with console login, disable Xwindow in the file .profile, so the database can be restarted
from a reboot.
Exiting the Database
To exit the postgreSQL database, perform the following steps:
Step 1 Log in to the Solaris system by entering postgres as the user.
A "postgres" password prompt may appear.
Step 2 Enter the following command:
/etc/init.d/postgres_init stop
This command calls the postgreSQL command pg_ctl stop.
Reactivating the Database
To reactivate the postgreSQL database, perform the following steps.
Step 1 Log in into the Solaris system by entering postgres as the user.
A "postgres" password prompt may appear.
Step 2 Enter the following command:
/etc/init.d/postgres_init restart
This command calls the postgreSQL command pg_ctl restart. The parameters for this command are described in "Activating the Database" section.
Exporting a Database
To export a database to a text file or to back up the entire database system, perform the following steps:
Exporting to a Text File
Step 1 Export a database into a text file by entering the following command:
pg_dump -x databasename > outfile
Note "-x" is optional. When it is used, the "dumped" database loses the ownership information.
Step 2 Reload the dataset by entering the following command:
psql -e database < outfile
Note "-e" is optional. When it is used, every log message "echoes" on the screen.
Backing Up the Database System
You can back up the database system file directory /usr/local/pgsql/data/base into a tar file.
Reintializing a Database
To reinitialize a database, the existing database must be destroyed, and a new one must be created.
To reinitialize a database, performing the following steps:
Step 1 Destroy the database by entering the following command with "csrdb" as the name of the database:
dropdb csrdb
Note The database to be destroyed must not be connected to the CSR application, CSR GUI, and
pgsql.
Step 2 Create a new database by entering the following command:
createcsrdb csrdb
After the database is re-created, the CSR and CSR GUI can be restarted and connected to the new database. If "csrdb" is not used as the name of the database, note the following:
To have CSR started upon reboot of the Solaris system, modify the following files:
etc/init.d/csr_init
/etc/rc2.d/S99csr_init
To start CSR from the command prompt, specify the database name in command csr newdbname &
This section describes database tables, constraints placed on each table, and relationships between tables.
Note Advanced knowledge of SQL is required to access the database through SQL commands. Use
the psql interface to access an SQL prompt. For more detail on psql interface, see the
postgreSQL online manual.
Configuration Tables
See "Data Schema," for the database script to create database tables, constraints and relationships.
Note The configuration data must contain one record that is created when the user executes the
createcsrdb command.
Table 2-1 Configuration Data Table
Name
Type
Allow Null
Default
Constraints
Comments
ActiveDataset
text
NO
`_1'
Valid values:
_1
_2
There are two datasets in the CSR (active and inactive). There are two of each table for entities that are created (for example, Carriers_1, Carriers_2). The active dataset marks which of the above is active.
LoggingLevel
text
NO
`Error'
Valid values:
error
info
debug
When the application, is active, logs are generated. This marks the level of logging that is being done. During realtime, this runs only at error level to avoid performance deterioration.
CSR_ID
varchar(64)
NO
ID used to register with the gatekeeper which application is connecting. (Also known as the application ID).
CSR_Version
text
Version of CSR. This is entered when the GUI requests it from the CSR.
GKTMP_Version
integer
Version of GKTMP. This is entered when the GUI requests it from the CSR.
Priority
integer
NO
1
>=1
Priority to register this application to the gatekeeper.
ListenPort
integer
>0 and < 32767
TCP port to listen for incoming connection. A gatekeeper or simulator may connect to the CSR, and this is the port to connect to.
ThreadCount
integer
NO
10
Number of threads in the working pool that handle routes in the rules engine. This number must be adjusted for different systems.
MaxSizeOfQueue
integer
NO
5000
Maximum size of the queue to handle calls. Typically, the queue is either empty or has an entry. During busy hours, the queue may become larger and cause long turn-around times for the calls to be routed.
TestRoutesAccountingEnabled
boolean
NO
FALSE
When issuing test commands through the GUI, the call units are updated if this is set to True.
RejectSrcCarrierInactive
boolean
NO
FALSE
If this rejection rule applies, the CSR returns a rejection instead of a null body.
RejectNullSrcCarrier
boolean
NO
FALSE
If this rejection rule applies, the CSR returns a rejection instead of a null body.
RejectSrcCarrierUnknown
boolean
NO
FALSE
If this rejection rule applies, the CSR returns a rejection instead of a null body.
RejectDestinationUnknown
boolean
NO
FALSE
If this rejection rule applies, the CSR returns a rejection instead of a null body.
RejectSrcPercentage
boolean
NO
FALSE
If this rejection rule applies, the CSR returns a rejection instead of a null body.
RejectSrcUnits
boolean
NO
FALSE
If this rejection rule applies, the CSR returns a rejection instead of a null body.
RejectNoDstCarrier
boolean
NO
FALSE
If this rejection rule applies, the CSR returns a rejection instead of a null body.
Table 2-2 Gatekeeper Table
Name
Type
Allow Null
Default
Constraints
Comments
GKKey
integer
NO
Primary key. Key of the gatekeeper.
GKID
varchar(64)
NO
ID of the gatekeeper. Must be unique.
GK_IP
inet
NO
IP address of the gatekeeper.
GK_Port
integer
NO
>0 and < 32767
Port that the gatekeeper is listening on.
ListenARQ
boolean
NO
FALSE
Set to TRUE if registering for this type of message.
ListenLRQ
boolean
NO
FALSE
Set to TRUE if registering for this type of message.
ListenIRR
boolean
NO
FALSE
Set to TRUE if registering for this type of message.
ListenDRQ
boolean
NO
FALSE
Set to TRUE if registering for this type of message.
ListenRRQ
boolean
NO
FALSE
Set to TRUE if registering for this type of message.
ListenURQ
boolean
NO
FALSE
Set to TRUE if registering for this type of message.
ListenLCF
boolean
NO
FALSE
Set to TRUE if registering for this type of message.
ListenLRJ
boolean
NO
FALSE
Set to TRUE if registering for this type of message.
ListenRAI
boolean
NO
FALSE
Set to TRUE if registering for this type of message.
ListenBRQ
boolean
NO
FALSE
Set to TRUE if registering for this type of message.
ConnectionState
text
NO
Valid values:
connect
disconnect
If set to connect, the CSR attempts to connect to this gatekeeper.
Table 2-3 TestRoutes Table
Name
Type
Allow Null
Default
Constraints
Comments
TestKey
integer
NO
Primary key. Key of this test route.
TestTime
time
Time of the test.
TestDate
date
Date of the test.
DatasetID
text
`_1'
Valid values:
_1
_2
This marks which dataset to issue the test on.
ANI
varchar(64)
ANI message.
DNIS
varchar(64)
DNIS message.
SrcCarrierID
varchar(64)
ID of source carrier.
DstCarrierID
varchar(64)
ID of destination carrier.
RunMe
Boolean
NO
FALSE
Set to TRUE when this test is to be run by the CSR.
HomeCarrierID
varchar(64)
ID of home carrier.
ResultCode
text
Result code for this test.
Table 2-4 TestOutputs Table
Name
Type
Allow Null
Default
Constraints
Comments
TestKey
integer
NO
Foreign key. Key of this test (see TestRoutes table).
SelectOrder
integer
NO
For a given TestKey, this shows the order in which the carriers will be returned. The database does not guarantee the original ordering.
DstCarrierID
varchar(64)
NO
ID of Destination Carrier.
ListKey
integer
NO
Specifies key for the contact list associated with the particular carrier.
IsSelected
boolean
NO
FALSE
Specifies whether this is a selected carrier.
Table 2-5 LogEntries Table
Name
Type
Allow Null
Default
Constraints
Comments
TestKey
integer
NO
Foreign key. Test key that this log file was generated for.
EntryOrder
integer
NO
For a given Test key, this field is used to show the order in which the log events were generated. The database does not guarantee the original ordering.
LogString
varchar(1024)
NO
The log entries associated with the test run.
Caution The following table should not be modified or deleted. It consists of error messages that are logged. The CSR application does not work without this table.
Table 2-6 StringResources Table
Name
Type
Allow Null
Default
Constraints
Comments
ResourceKey
integer
NO
Primary key. Key to the resource.
ResourceString
text
NO
String (error value).
Table 2-7 Filters Table
Name
Type
Allow Null
Default
Constraints
Comments
FilterKey
integer
NO
Primary key. Key of the filter.
GKID
varchar(64)
NO
Foreign key. ID of the gatekeeper associated with this filter.
MsgType
text
NO
Valid values:
ARQ
LRQ
RRQ
URQ
LCF
LRJ
DRQ
BRQ
IRR
RAI
The message type for this filter.
FilterType
text
NO
Valid values:
endpoint
supportedprefix
destinationinformation
remoteextesionaddress'
redirectreason
answercall
notificationonly
Type of filter.
Value
text
NO
Value of this message. There are certain values for each filter type enforced by the GUI.
Dataset Tables
The dataset tables use functionality that is specific to postgreSQL, that is, table inheritance. This allows a table to be created and other tables to be inherited from it. For example, you can create a Carriers Table and have tables Carriers_1 and Carriers_2 inherit from it. This allows two datasets to be created.
Table 2-8 Carriers Table
Name
Type
Allow Null
Default
Constraints
Comments
CarrierKey
integer
NO
<>0
Primary key. Key of the carrier.
CarrierName
varchar(64)
NO
Name of carrier.
CarrierType
text
NO
Valid values:
home
itsp
tdm
Type of carrier.
CarrierID
varchar(64)
NO
ID of carrier. Must be unique.
MaxIngressUnits
integer
>=0
Maximum ingress units.
MaxEgressUnits
integer
>=0
Maximum egress units.
RuleKey
integer
Foreign key. Rule associated with this specific carrier.
ListKey
integer
NO
Foreign key. Key of the list.
isActive
boolean
NO
TRUE
Indicates whether carrier is active.
Note Tables Carriers_1 and Carriers_2 can inherit from the Carriers table.
Table 2-9 MatchPatterns Table
Name
Type
Allow Null
Default
Constraints
Comments
PatternKey
integer
NO
<> 0
Primary key. Key of the pattern.
Pattern
varchar(64)
NO
Unique. Valid values are 0 to 9, a to f, A to F, *, and "."
RouteKey
integer
Foreign key. Route that this pattern is associated with.
Note Tables MatchPatterns_1 and MatchPatterns_2 can inherit from this table.
Table 2-10 Routes Table
Name
Type
Allow Null
Default
Constraints
Comments
RouteKey
integer
NO
<> 0
Primary key. Key of the route.
RouteName
text
Name of the route.
RuleKey
integer
Foreign key. Rule associated with this route.
Note Tables Routes_1 and Routes_2 can inherit from the Routes table.
Table 2-11 Rules Table
Name
Type
Allow Null
Default
Constraints
Comments
RuleKey
integer
NO
<> 0
Primary key. Key of the rule.
RuleName
text
Optional name to describe this rule.
IsDefaultRule
boolean
NO
FALSE
Indicates if this is the default rule. One default rule must exist.
MinQoS
integer
>= 1 and <=9
Minimum quality of service. This is used with RejectInsufficientQoS rule.
PreferredCarrierKey
integer
Foreign key. Key to a carrier. Used only when SelectPreferredCarrier rule is used.
RejectMaxOriginationPercentage
boolean
NO
FALSE
Indicates whether to reject this rule.
RejectMaxIngressUnits
boolean
NO
FALSE
Indicates whether to reject this rule.
RejectMaxIngressUnitsPerRoute
boolean
NO
FALSE
Indicates whether to reject this rule.
RejectEgressCostMoreThanIngress
boolean
NO
FALSE
Indicates whether to reject this rule.
RejectInsufficientQoS
boolean
NO
FALSE
Indicates whether to reject this rule.
RejectMaxEgressUnits
boolean
NO
FALSE
Indicates whether to reject this rule.
RejectMaxEgressUnitsPerRoute
boolean
NO
FALSE
Indicates whether to reject this rule.
PreventITSPtoITSP
boolean
NO
FALSE
Indicates whether to reject this rule.
SelectMinCostPerEgressRoute
integer
NO
0
>= 0 and <= 5
Specifies the order of selection rule from which the CSR runs.
SelectBestQoS
integer
NO
0
>= 0 and <= 5
Qos level.
SelectSameCarrier
integer
NO
0
>= 0 and <= 5
Specifies carrier.
SelectPreferredCarrier
integer
NO
0
>= 0 and <= 5
Specifies preferred carrier.
SelectPercentageEgress
integer
NO
0
>= 0 and <= 5
Specifies egress percentage.
Note Tables Rules_1 and Rules_2 can inherit from the Rules table.
Table 2-12 Contacts Table
Name
Type
Allow Null
Default
Constraints
Comments
ContactKey
integer
NO
<> 0
Primary key. Key of the contact.
RASAddress
varchar(64)
NO
RAS address.
AddressType
text
NO
Valid values:
ipv4
gkid
dnsname
Type of address (IP address, gatekeeper ID, or DNS name). The CSR converts the DNS name to IP address. When you use the DNS name, a DNS server must be available. Otherwise, this value is not used.
Cost
integer
>= 0
Specifies desired cost.
Priority
integer
>= 0
Specifies desired priority.
Note Tables Contacts_1 and Contacts_2 can inherit from the Contacts table.
Lists Table
Table 2-13 Lists Table
Name
Type
Allow Null
Default
Constraints
Comments
ListKey
integer
NO
<> 0
Primary key. Key of the list.
ListName
text
Name of the list.
Note Tables Lists_1 and Lists_2 can inherit from the Lists table.
Table 2-14 ContactLists Table
Name
Type
Allow Null
Default
Constraints
Comments
ListKey
NO
Foreign key. Key of the list.
ContactKey
NO
Foreign key. Key of the contact.
Note Tables ContactLists_1 and ContactLists_2 can inherit from this table.
Table 2-15 RouteAttributes Table
Name
Type
Allow Null
Default
Constraints
Comments
AttributeKey
integer
NO
<>0
Primary key. Key of the route attribute.
AttributeName
text
Name of attribute associated with the route.
CarrierKey
integer
NO
Foreign key. Key of the carrier associated with this route attribute.
DaypartType
text
NO
Valid values:
absolute
weekly
daily
always
The CSR searches for the correct value in the following order:
absolute = specific date/time range. Start date, end date, start time, and end time must be entered.
weekly=a specific day of the week during a specific time. Start time, end time, and weekday must be entered.
daily=each day for a specific time. Start time and end time must be entered.
always=all the time.
WeekDay
integer
>= 0 and <= 6. As 0=Sunday, 6=Saturday.
This is for the weekly setting.
StartTime
time
Valid format: hour(0-23):min (0-60):sec (0-60)
This is for the absolute, weekly, and daily settings.
EndTime
time
Valid format: hour(0-23):min (0-60):sec (0-60)
This is for absolute, weekly, and daily settings.
Cost
integer
>= 1
Cost associated with this attribute.
MaxUnits
integer
>= 0
Maximum units.
MaxPercentage
integer
>= 1 and <=100
Maximum percentage.
Note Tables IngressRouteAttributes_1 and IngressRouteAttributes _2 can inherit from the
RouteAttributes table.
Table 2-16 EgressRouteAttributes Table
Name
Type
Allow Null
Default
Constraints
Comments
QoS
integer
>= 0 and <= 9
Quality of service.
ListKey
integer
Foreign key. Key of list associated with this egress route attribute.
Note EgressRouteAttributes_1 and EgressRouteAttributes_2 can inherit from the
EgressRouteAttributes and RouteAttriubtes tables.
Table 2-17 QoS Table
Name
Type
Allow Null
Default
Constraints
Comments
IngressCarrierKey
integer
NO
Foreign key. Key of the ingress carrier.
EgressCarrierKey
integer
NO
Foreign key. Key of the egress carrier
RouteKey
integer
NO
Foreign key. Key of the route associated with the QoS.
RelativeQoS
integer
NO
>= 1 and <= 9
Relative quality of service.
Note Tables QoS_1 and QoS_2 can inherit from this QoS table.
Table 2-18 QoS Table
Name
Type
Allow Null
Default
Constraints
Comments
RouteKey
integer
NO
Foreign key. Key of the route associated with this QoS.
AttributeKey
integer
NO
Foreign key. Key of the attribute associated with this QoS.
Note Tables IngressRoutes_1, IngressRoutes_2, EgressRoutes_1, and EgressRoutes_2 can inherit
from this QoS table.
PostgreSQL Database Command Summary
Following is a summary of frequently used postgreSQL commands. Refer to the online manual pages and help section for more information.
createdb—Creates a new database in postgreSQL. A database name is required when using this command.
createuser—Creates a postgreSQL user. A user name is required when using this command.
dropdb—Removes a database from postgreSQL. A database name is required when using this command.
dropuser—Removes a postgreSQL user. A user name is required when using this command.
initdb—Creates a new postgreSQL database installation. This command is used only once when the database is installed. It creates the base directory. This command cannot be used again.
psql—SQL interface to the postgreSQL databases. A database name is required when using this command.
vacuumdb—Cleans and analyzes a postgreSQL database.
