Carrier-Sensitive Routing User Guide
PostgreSQL Database Operations and Functionalities

Table of Contents

PostgreSQL Database Operations and Functionalities
Database Operations
Database Functionalities

PostgreSQL Database Operations and Functionalities


This chapter describes the operations and functionalities of the postgreSQL database and contains the following sections:

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.

Database Operations

This section describes the following tasks:

Activating 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 &

Database Functionalities

This section describes the following:

Database Tables

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.

RejectCarrierInactive

boolean

NO

FALSE

 

If this rejection rule applies, the CSR returns a rejection instead of a null body.

RejectNullCarrier

boolean

NO

FALSE

 

If this rejection rule applies, the CSR returns a rejection instead of a null body.

RejectCarrierUnknown

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.

RejectPercentage

boolean

NO

FALSE

 

If this rejection rule applies, the CSR returns a rejection instead of a null body.

RejectUnits

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.

CarrierID

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.