This chapter provides a description of the database schema and instructions for provisioning the ICR databases.
Central to the database is the call plan table which is structured so that the records map serialized routing objects to serialized destination objects.
There are also route and destination tables that store the fully qualified names of the implementations of any routing or destination objects provided by the engine. Thus, the call plan table stores physical route and destination objects in the database using two columns. One column is an ID reference to either the route or destination table which indicates the implementing class of the object. The second column is a serialized string of the physical object’s value. The tables in the provisioning section illustrate this concept.
Call plans are also tied to users and groups. This relationship allows for the quick development of small/simple schemas that do little more than add meta information to the routing engine core on a per user or per group basis. Thus, the core ICR engine remains very generic and customization can be done as needed.
Information that is stored in the database can be divided into the following tables: Service Providers, Subscribers, Call Plans, Routing Rules, and Routing.
Information stored in the Service Providers Table includes:
Names
IDs
Default web accounts
Other account information such as addresses
Information stored in the Subscribers Table includes:
ID and/or number
Service Provider
Number Provisioning is controlled by the Service Provider and includes:
Number to be used
Subscriber that is linked to the number
The Call Plan is owned by the subscriber and includes:
Subscriber ID
Name or ID of the call plan
List of routing rules
Information stored in the Routing Rules Table includes:
Type of routing rule
Criteria
Destination type
Destination value
Maps a number to call plan.
A CSV file can be used to import large amounts of information into a database. Importing data appends the new information to the existing database, and any conflicting records are not overwritten. Each table has at least one unique column that is used to determine if there are conflicting records. Any database entries that are not in the CSV file remain unchanged in the database after the file has been imported.
Note
All columns must be included. If the field is blank, make sure that the column is still included in the CSV file.
Note
When importing CSV files into ICR, do not log off until the import is complete. Logging off before the import is complete will cause the entire import of data to fail.
Whether the database operation to import records from a file is available depends on which page is currently displayed.
Click Import on the task bar. The Import window will appear.
Click Choose File to browse for a CSV file to import.
Click Start Import.
The file is uploaded and the import begins and runs in the background.
Note
Click Cancel at any time to close the window and stop the import.
The status of an import can be found on the My Account page under Job Logs.
The CSV files to be imported for Service Providers should have the following format:
Column |
Value Note, entries in bold should appear exactly as entered in this table. |
Accepted Data Type |
|---|---|---|
1 |
Name |
String |
2 |
1 if active or 0 if not active |
1 or 0 |
3 |
Address |
String |
4 |
First Name |
String |
5 |
Last Name |
String |
6 |
Email string @ included |
|
7 |
Phone Number |
String of digits, no hyphens or parenthesis |
The CSV files to be imported for subscribers should have the following format:
Column |
Value Note, entries in bold should appear exactly as entered in this table. |
Accepted Data Type |
|---|---|---|
1 |
Name (Can be used if a subscriber is a company or center as opposed to an individual) |
String |
2 |
User ID (The ID of the subscriber or user that has permission to view and edit call plans and services associated with this number.) |
Integer |
3 |
1 if active or 0 if not active |
1 or 0 |
4 |
First Name |
String |
5 |
Last Name |
String |
6 |
Email string @ included |
|
7 |
Phone Number |
String of digits, no hyphens or parenthesis |
The CSV files to be imported for call plans into either user database should have the format described in the following table.
The CSV files to be imported for call plans should have the following format:
Note
Some fields include a JSON object. For more information about JSON, see http://json.org/.
Column |
Value Note, entries in bold should appear exactly as entered in this table. |
Accepted Data Type |
|---|---|---|
1 |
Rule Type (forward-call, time-of-day, day-of-week, holiday, percent-allocation, match, geo) |
String matching one of the keywords |
2 |
Criteria (String format that pairs with the rule type provided) |
String |
3 |
Destination Type (number, sip-uri, trunk-group, hang-up, Rule, pdest, tg-with-outpulse) |
String matching one of the keywords |
4 |
Destination Value |
String that pairs with the destination type provided |
5 |
Priority (a number 1 to 99 that indicates the order in which to process this rule. 1 is processed first, 99 is processed last) |
Integer 1 to 99 |
6 |
1 if active or 0 if not active |
1 or 0 |
7 |
1 if parent rule or 0 if not parent rule |
1 or 0 |
8 |
Child Reference (the id that this rules children must reference) |
Integer |
9 |
1 if child plan or 0 if it is not a child plan |
1 or 0 |
10 |
Parent Reference (the id that refers to this rules parent) |
Integer |
The following table shows the format used based on which rule type is specified by the CSV file.
Rule Type |
Format of criteria |
|---|---|
forward-call |
Empty |
time-of-day |
HH:MM-HH:MM in 24 hour/military time |
day-of-week |
Number 1-7 where 1 is Sunday and 7 is Saturday |
holiday |
YYYY/MM/DD.HH:MM-YYYY/MM/DD.HH:MM |
percent-allocation |
Number 1 to 99 |
match |
Posix regular expression |
geo |
name of NPA list |
The following table shows the format used based on which destination is specified by the CSV file.
Destination |
Format of destination value |
|---|---|
number |
String of digits, no hyphens or parenthesis |
sip-uri |
String of digits with @ symbol |
hang-up |
No value required |
rule |
No value required |
trunk-group |
String of digits or comma separated list of digits or identifiers |
tg-with-outpulse |
No value required |
pdest |
Name of the preconfigured destination |
The CSV files to be imported for Routing Rules Tables should have the following format:
Column |
Value Note, entries in bold should appear exactly as entered in this table. |
Accepted Data Type |
|---|---|---|
1 |
800 number or number to route (Can have X as wild card ex: 800545XXXX) |
String of digits, no hyphens or parenthesis |
2 |
Call Plan Name (The name of the call plan behind it) |
String |
3 |
Priority (a number 1 to 99 that indicates the order in which to process this rule. 1 is processed first, 99 is processed last) |
Integer 1 to 99 |
4 |
1 if active or 0 if not active |
1 or 0 |
Tip
Inactive Routing Rules will not be processed.
The CSV files to be imported for numbers should have the following format:
Column |
Value Note, entries in bold should appear exactly as entered in this table. |
Accepted Data Type |
|---|---|---|
1 |
Phone Number |
String of Digits |
2 |
User ID (The ID of the subscriber or user that has permission to view and edit call plans and services associated with this number.) |
Integer |
3 |
IVR Pin |
String of Digits |
4 |
Timezone ID(The Id of the timezone associated with this number which is used for time of day, day of week, holiday/event based routing.) |
Integer |
The CSV files to be imported for subscriber users should have the following format:
Column |
Value Note, entries in bold should appear exactly as entered in this table. |
Accepted Data Type |
|---|---|---|
1 |
Type ( platform_owner, service_provider, or subscriber) |
String matching one of the keywords |
2 |
Username |
String |
3 |
String |
|
4 |
First Name |
String |
5 |
Last Name |
String |
6 |
Timezone ID(The Id of the timezone associated with this number which is used for time of day, day of week, holiday/event based routing.) |
Integer |
7 |
1 if active or 0 if not active |
1 or 0 |
8 |
1 if locked or 0 if not locked |
1 or 0 |
9 |
Temporary Password |
String |
The CSV files exported for Call Detail Records (CDRs) will have the following format.
Column |
Value |
|---|---|
1 |
called_number |
2 |
via |
3 |
protocol |
4 |
trxn_id |
5 |
call_time |
6 |
id |
7 |
trunk_group |
8 |
destination_value |
9 |
call_id |
10 |
alternate_trunk_group |
11 |
xtml_session_id |
12 |
event_trigger |
13 |
switch_point_code |
14 |
second_alternate_trunk_group |
15 |
second_alternate_outpulse |
16 |
call_plan |
17 |
original_called_number |
18 |
alternate_outpulse |
19 |
subscriber_name |
20 |
error_message |
21 |
response_message |
22 |
calling_number |
23 |
error_code |
24 |
service_provider_name |
25 |
outpulse |
Information can be exported from the database, saved, and then imported to restore a database.
Whether the database operation to export records to a file is available depends on which page is currently displayed.
Click Export on the task bar. The Export window will appear.
Click Start Export.
The output CSV file can be found on the My Account page under Job Logs.