This chapter provides a description of the database schema and instructions for provisioning the ICR databases through both the web UI and the API.
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: Administrators, Service Providers, Subscribers, Call Plans, Routes, and Numbers.
The CSV files to be imported for any administrator account should have the following format:
Column |
Value |
|---|---|
1 |
Username |
2 |
Role ID |
3 |
First Name |
4 |
Last Name |
5 |
|
6 |
Permissions |
A unique string that is the username for this user. Limit 64 characters.
The {role_id} defines the type of administrator that is being created. The following can be used as {role_id}:
platform_owner
service_provider:<service_provider_name>
subscriber:<service_provider_name>:<subscriber_name>
A string that is the first name of this user. Limit 250 characters.
A string that is the last name of this user. Limit 250 characters.
A string that is the email address for this user. Limit 250 characters.
An array of permission groups that this user is a member of. Permissions are dependent on the {role_id} of the created administrator.
add_permission_group_entries
alter_platform_owner_user
alter_service_provider_user
create_permission_group
default:service_provider
default:subscriber
icr_default:icr_platform_owner_views
icr_default:icr_service_provider_provisioning
icr_default:icr_service_provider_views
icr_provision_audio
icr_view_audio
list_permission_groups
view_subscriber
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 |
|---|---|
1 |
Service Provider Name |
2 |
Subscriber Name |
3 |
Name |
4 |
Display Name |
5 |
Rules |
6 |
Destination Type |
7 |
Destination Value |
A unique string that is the name of the Service Provider using this call plan. Must match an existing Service Provider.
A unique string that is the name of the Subscriber using this call plan. Must match an existing Subscriber.
A string that is the name for this call plan. Limit 250 characters.
A string that is the display name for this call plan, which will be used in the web UI. Limit 250 characters.
An array of the rules for a call plan. See Criteria Strings for syntax. The default value is [].
A string that is the type of far end pickup destination. The default value is “NONE”. The accepted values are:
NONE
SIP
NUMBER
TRUNK
TRUNK-OUTPULSE
A string that is the value for the far end destination. The default value is []. See Destination Value Strings for syntax.
The following table shows the format used based on which rule type is specified by the CSV file.
Rule Type |
Format of criteria |
|---|---|
always |
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 |
npa |
Name of NPA list |
limited |
Number of maximum concurrent calls |
periodic |
Number of maximum calls per number of hours |
label |
Provisioned label |
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 |
terminate |
No value required |
continue |
No value required |
rule |
No value required |
menu |
No value required |
trunk-group |
String of digits or comma-separated list of digits or identifiers |
trunk-outpulse |
No value required |
call_plan |
Name of the next Call Plan |
The CSV files to be imported for Route Tables should have the following format:
Column |
Value |
|---|---|
1 |
Number |
2 |
Call Plan Name |
3 |
Service Provider Name |
4 |
Subscriber Name |
5 |
Priority |
A unique string of digits that is the number that uses this routing entry. Can have “X” as wild card digit. Cannot have hyphens or parenthesis.
A unique string that is the call plan that uses this routing entry. Must match an existing call plan.
A unique string that is the name of the Service Provider for the subscriber who uses this routing entry. Must match an existing Service Provider.
A unique string that is the Subscriber for this call plan that uses this routing entry. Must match an existing Subscriber.
An integer that is the priority for this call plan. Lower numbers are evaluated first. The default value is 0. Limit values from 1 to 99.
The CSV files to be imported for Service Providers should have the following format:
Column |
Value |
|---|---|
1 |
Name |
2 |
Display Name |
3 |
First Name |
4 |
Last Name |
5 |
|
6 |
Address |
7 |
Phone |
8 |
Notes |
9 |
Legacy Service Provider ID |
A unique string that is the name of this Service Provider. Limit 64 characters. Can contain only uppercase or lowercase characters, numbers, or underscores.
A string used as a display name. Limit 250 characters. Can contain only uppercase or lowercase characters, numbers, or underscores.
A string that is the first name of a contact for this Service Provider. Limit 250 characters.
A string that is the last name of a contact for this Service Provider. Limit 250 characters.
A string that is the email address of this Service Provider. Limit 250 characters.
A string that is the physical address of this Service Provider. Limit 250 characters.
A string of digits that is the phone number of this Service Provider. Limit 250 characters.
A string that is a free-form notes field. Limit 2048 characters.
An integer that was the Service Provider ID for legacy API use.
The CSV files to be imported for subscribers should have the following format:
Column |
Value |
|---|---|
1 |
Name |
2 |
Display Name |
3 |
Service Provider Name |
4 |
First Name |
5 |
Last Name |
6 |
|
7 |
Address |
8 |
Phone |
9 |
Notes |
10 |
Legacy User ID |
A unique string that is the name of this Subscriber. Limit 64 characters. Can contain only uppercase or lowercase characters, numbers, or underscores.
A string used as a display name. Limit 250 characters. Can contain only uppercase or lowercase characters, numbers, or underscores.
A unique string that is the name of this Subscriber’s Service Provider. Must match an existing Service Provider.
A string that is the first name of a contact for this Subscriber. Limit 250 characters.
A string that is the last name of a contact for this Subscriber. Limit 250 characters.
A string that is the email address of this Service Provider. Limit 250 characters.
A string that is the physical address of this Service Provider. Limit 250 characters.
A string of digits that is the phone number of this Subscriber. Limit 250 characters.
A string that is a free-form notes field. Limit 2048 characters.
A big integer that was the Subscriber ID for legacy API use.
The CSV files to be imported for numbers should have the following format:
Column |
Value |
|---|---|
1 |
Number |
2 |
Timezone |
3 |
Service Provider Name |
4 |
Subscriber Name |
5 |
Parameters |
A unique string of digits that is this number. This should be a valid phone number. Limit 250 characters.
A unique string that is the time zone associated with this number. It is used for time of day, day of week, and holiday/event based routing.
A unique string that is the name of the Service Provider for the Subscriber who uses this number. Must match an existing Service Provider.
A unique string that is the name of the Subscriber who uses this number. Must match an existing Subscriber.
A JSON object that defines the set of parameters configured for this number that are passed to the routing engine.
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.
Import Using the Web UI
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.
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.
Import Using the API
To import data with the API, the user sends a POST command to the specific object.
The example below shows a Service Provider importing a Subscriber.
curl -X POST \
-H "Content-Type:icr/json" localhost/icr/api/job/service_provider:icr_service_provider?executable=restworkx-icr \
-H "Authorization:Basic <secure-access-token>" \
-d '{"job_type":"import","in_file":"/var/opt/xpressworkx/app-manager/icr/media/upload/subscriber-import.csv","object":"subscriber","service_provider_name":"icr_service_provider"}'
Which would return a message such as:
"meta": {
"executable": "restworkx-icr",
"code": 200,
"encoding": "ascii",
"scope": "success",
"records_shown": 1,
"message": "",
"authorization": "Basic <secure-access-token>"
},
"data": [{
"scope": "job",
"meta": {
"modified_by": "system",
"txnid": null,
"modified": "2022-04-07 11:39:44.724128",
"created_by": "system",
"created": "2022-04-07 11:39:44.724102"
},
"links": {
"self": {
"scope": "job",
"identifier": {
"job_id": 104,
"out_file": "/var/opt/xpressworkx/app-manager/icr/media/download/subscriber-import-2022-04-07-23770cb05d.csv"
},
"uri": "/job"
},
},
"attributes": {
"status": "processing",
"out_file": "/var/opt/xpressworkx/app-manager/icr/media/download/subscriber-import-2022-04-07-23770cb05d.csv",
"pid": null,
"object": "subscriber",
"job_type": "import",
"job_id": 104,
"elapsed": "0",
"in_file": "/var/opt/xpressworkx/app-manager/icr/media/upload/subscriber-import.csv",
"kwargs": null,
"progress": null,
"role_id": "service_provider:icr_service_provider",
"output_hash": null
},
}],
"jsonapi": {
"version": "2.2-14",
"name": "Intelligent Call Routing",
"short_name": "icr",
"author": "IMSWorkX, Inc."
}
Note
The parameter executable=restworkx-icr is required to import an object.
Information can be exported from the database, saved, and then imported to restore a database.
Export Using the Web UI
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.
Click Start Export.
The output CSV file can be found on the My Account page under Job Logs.
Export Using the API
To export data with the API, the user sends a POST command to the specific object.
The example below shows a Platform Owner exporting Service Providers.
curl -X POST \
-H "Content-Type:icr/json" localhost/icr/api/job/platform_owner?executable=restworkx-icr \
-H "Authorization:Basic <secure-access-token>" \
-d '{"job_type": "export","object": "service_provider"}'
Which would return a message such as:
"meta": {
"executable": "restworkx-icr",
"code": 200,
"encoding": "ascii",
"scope": "success",
"records_shown": 1,
"message": "",
"authorization": "Basic <secure-access-token>"
},
"data": [{
"scope": "job",
"meta": {
"modified_by": "system",
"txnid": null,
"modified": "2022-04-07 13:50:20.834266",
"created_by": "system",
"created": "2022-04-07 13:50:20.834246"
},
"links": {
"self": {
"scope": "job",
"identifier": {
"job_id": 109,
"out_file": "/var/opt/xpressworkx/app-manager/icr/media/download/service_provider-export-2022-04-07-e996a4f9dc.csv"
},
"uri": "/job"
},
},
"attributes": {
"status": "processing",
"out_file": "/var/opt/xpressworkx/app-manager/icr/media/download/service_provider-export-2022-04-07-e996a4f9dc.csv",
"pid": null,
"object": "service_provider",
"job_type": "export",
"job_id": 109,
"elapsed": "0",
"in_file": null,
"kwargs": null,
"progress": null,
"role_id": "platform_owner",
"output_hash": null
},
}],
"jsonapi": {
"version": "2.2-14",
"name": "Intelligent Call Routing",
"short_name": "icr",
"author": "IMSWorkX, Inc."
}
Note
The parameter executable=restworkx-icr is required to export an object.
The CSV files exported for Call Detail Records (CDRs) will have the following format.
Column |
Value |
|---|---|
1 |
Called Number |
2 |
Subscriber Name |
3 |
CDRs |
4 |
Start Time |
5 |
Calling Number |
6 |
End Time |
7 |
Original Called Number |
8 |
Route |
9 |
Service Provider Name |
The number to which this specific call is routed.
A unique string that is the name of the Subscriber using this service. Must match an existing Subscriber.
An array that is all of the events for this specific call.
A timestamp indicating the date and time this specific call started.
The number making this specific call.
A timestamp indicating the date and time this specific call ended.
The number that was dialed to start this call.
A JSON object that is the route event of the CDR. If blank, this CDR was ended without a route being returned.
A unique string that is the name of the Service Provider for this Subscriber using this service. Must match an existing Service Provider.
ICR does not have an explicit delete function for any of its data. Instead, ICR has the ability to archive data that is not in use.
Archived data remains in the ICR application, but this data is not used when routing calls. Archived data can then be returned to the live data by restoring or can be removed completely by purging. When viewing data through the web UI or with an API request, the user can switch between the live and archived database to see all data.
Note
By default, ICR permanently purges archived data after seven days. This can be changed by a Platform Owner in Settings.
Data that is not currently in use by the ICR application can be removed from the live database by archiving the data. Provisioned data can be archived in the web UI or through the API.
Archive a Single Object Using the Web UI
Navigate to the object that you want to archive.
Right-click on the object to be archived.
Click Archive.
The object will be moved to the archive database.
Archive Multiple Objects Using the Web UI
Navigate to the objects that you want to archive.
Select the objects by clicking on the first object then:
Hold the Shift key and click another object to select everything between and including the two selected objects.
Hold the Ctrl key and click the other objects you want.
Click Archive Selected on the bottom of the page.
The objects will be moved to the archive database.
Archive Using the API
To archive an object with the API, the user sends a DELETE command to the specific object.
The example below shows a user archiving a Subscriber object called new_subscriber.
curl -X DELETE \
-H "Authorization:Basic <secure-access-token>" \
-d 'localhost/icr/api/subscriber/service_test/new_subscriber'
Which would return a message such as:
"meta": {
"encoding": "ascii",
"records_shown": 0,
"message": "Record successfully archived or restored.",
"code": "205",
"scope": "success"
},
"data": [],
"jsonapi": {
"version": "2.2-17",
"name": "Intelligent Call Routing",
"short_name": "icr",
"author": "IMSWorkX, Inc."
}
Restoring data will move data from the archive database to the live database. Restoring data can be done through the web UI or the API.
Restore a Single Object Using the Web UI
Switch to the archive view by toggling Live / Archive Database in the task bar.
Navigate to the object that you want to restore.
Right-click on the object to be restored.
Click Restore.
The object will be moved to the live database.
Restore Multiple Objects Using the Web UI
Switch to the archive view by toggling Live / Archive Database in the task bar.
Navigate to the objects that you want to restore.
Select the objects by clicking on the first object then:
Hold the Shift key and click another object to select everything between and including the two selected objects.
Hold the Ctrl key and click the other objects you want.
Click Restore Selected on the bottom of the page.
The objects will be moved to the live database.
Restore Using the API
To restore an object with the API, the user sends a DELETE command to the specific object.
The example below shows a user restoring a Subscriber object called new_subscriber.
curl -X DELETE \
-H "Authorization:Basic <secure-access-token>" \
-d 'localhost/icr/api/subscriber/service_test/new_subscriber?archive=true&restore=true'
Which would return a message such as:
"meta": {
"encoding": "ascii",
"records_shown": 0,
"message": "Record successfully archived or restored.",
"code": "205",
"scope": "success"
},
"data": [],
"jsonapi": {
"version": "2.2-14",
"name": "Intelligent Call Routing",
"short_name": "icr",
"author": "IMSWorkX, Inc."
}
Note
The parameters archive=true&restore=true are required to restore an object.
Purging data permanently removes the data from the archived database. Purging data can be done through the API, web UI, or the Maximum Inactive Days for Records setting in the web UI.
Warning
Once data has been purged from the database, it cannot be restored.
Purge a Single Object Using the Web UI
Switch to the archive view by toggling Live / Archive Database in the task bar.
Navigate to the object that you want to purge.
Right-click on the object to be purged.
Click Purge.
The object will be permanently removed from the database.
Purge Multiple Objects Using the Web UI
Switch to the archive view by toggling Live / Archive Database in the task bar.
Navigate to the objects that you want to purge.
Select the objects by clicking on the first object then:
Hold the Shift key and click another object to select everything between and including the two selected objects.
Hold the Ctrl key and click the other objects you want.
Click Purge Selected on the bottom of the page.
The objects will be permanently removed from the database.
Purge Using the Maximum Inactive Days Setting
ICR will remove records that have been archived longer than the value specified by this setting every night at midnight, which is determined by the time on the active NIU. A value of zero will always keep archived records in the database.
This setting can be found in the Settings page when the user is logged in as a Platform Owner.
Purge Using the API
To purge an object with the API, the user sends a DELETE command to the specific object.
The example below shows a user purging a Subscriber object called new_subscriber.
curl -X DELETE \
-H "Authorization:Basic <secure-access-token>" \
-d 'localhost/icr/api/subscriber/service_test/new_subscriber?archive=true'
Which would return a message such as:
"meta": {
"encoding": "ascii",
"records_shown": 0,
"message": "Record successfully archived or restored.",
"code": "205",
"scope": "success"
},
"data": [],
"jsonapi": {
"version": "2.2-14",
"name": "Intelligent Call Routing",
"short_name": "icr",
"author": "IMSWorkX, Inc."
}
Note
The parameter archive=true is required to purge an object.