VoiceGuide Database

VoiceGuide uses a database to store details of queued outgoing calls, CDRs, reportable statistics, etc.

VoiceGuide will by default use an SQLite database, which is sufficient for most applications.
The SQLite database file is called vgDb.db and is located in VoiceGuide's \data\ subdirectory.

Sometimes it is desirable that VoiceGuide uses a different database engine instead.

To use another database engine:

1. Create the main Database object to be used by VoiceGuide.
2. Specify the connection string in VoiceGuide's Config.xml, in section <Dialer>

VoiceGuide will create all the necessary Tables/Indexes/etc the first time it uses the user specified database.

The <Dialer> section should be placed inside the Config.xml's <VoiceGuideConfig> section.

MS SQL Server

Example Config.xml <Dialer> section for MS SQL Server:

<Dialer>
<OutDialQue_ADODB_Provider>System.Data.SqlClient</OutDialQue_ADODB_Provider>
<OutDialQue_Database>vgDb</OutDialQue_Database>
<OutDialQue_ConnectString>Data Source=10.1.1.78,1433;Database=$DATABASE;User ID=someuser;Password=somepassword;</OutDialQue_ConnectString>
<OutDialQue_PortToUse_LinkField></OutDialQue_PortToUse_LinkField>
<OutDialQue_SqlPrefix></OutDialQue_SqlPrefix>
<OutDialQue_SqlSuffix></OutDialQue_SqlSuffix>
</Dialer>

After setting the Config.xml to appropriate values you will need to create the database (schema) object named vgDb (or whatever the name specified in the <OutDialQue_Database> section is). Use the Microsoft SQL Server Management Studio to create the database object.

Configuring MS SQL Server

If the SQL Server is on a system separate to VoiceGuide then it needs to be configured to allow external connections. Also the system's firewall needs to be set to pass though the external connection requests to the database.

MySQL

When using MySQL as the VoiceGuide backend DB you should first install the MySQL ADO.NET Data Provider.

Here is an example Config.xml <Dialer> section for MySQL:

<Dialer>
<OutDialQue_ADODB_Provider>MySql.Data.MySqlClient</OutDialQue_ADODB_Provider>
<OutDialQue_Database>vgDb</OutDialQue_Database>
<OutDialQue_ConnectString>Database="$DATABASE";Data Source="10.1.1.9";User Id="someuser";Password="somepassword";</OutDialQue_ConnectString>
<OutDialQue_PortToUse_LinkField></OutDialQue_PortToUse_LinkField>
<OutDialQue_SqlPrefix>SELECT</OutDialQue_SqlPrefix>
<OutDialQue_SqlSuffix>LIMIT 1</OutDialQue_SqlSuffix>
</Dialer>

After setting the Config.xml to appropriate values you will need to create the database (schema) object named vgDb (or whatever the name specified in the <OutDialQue_Database> section is).  You can use the MySQL Workbench to create the database schema object. You must ensure that the user specified in the Connection string has the rights to fully work with and manage the vgDb schema.

Best way to configure and test the ADO.NET connection is to just start the VoiceGuide "Outbound Call Loader" application. On startup the Outbound Call Loader will connect to the database object and run the Db_Create_MySql.Data.MySqlClient.sql script creating the Tables and Indexes. You can then use the Outbound Call Loader to load new calls into the system, or try loading the calls into the database yourself directly.

The<OutDialQue_SqlPrefix> and <OutDialQue_SqlSuffix> values are used when VoiceGuide constructs the SQL query to find the suitable call in the PortToUse table.

To enable priority sorting of calls on MySQL the OutDialQue_SqlSuffix setting should be set to:

<OutDialQue_SqlSuffix>ORDER BY Priority ASC LIMIT 1</OutDialQue_SqlSuffix>

Please note that priority sorting of calls can increase the time taken to retrieve the next call details if large number of calls is queued.

Oracle

When using Oracle as the VoiceGuide backend DB you should install Oracle's ADO.NET Data Provider (Oracle.DataAccess.Client).

Here is an example Config.xml <Dialer> section for Oracle:

<Dialer>
<OutDialQue_ADODB_Provider>Oracle.DataAccess.Client</OutDialQue_ADODB_Provider>
<OutDialQue_Database>vgDb</OutDialQue_Database>
<OutDialQue_ConnectString>Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.26)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=$DATABASE)));User Id=HR;Password=hr;</OutDialQue_ConnectString>
<OutDialQue_PortToUse_LinkField>Disable</OutDialQue_PortToUse_LinkField>
<OutDialQue_SqlPrefix>SELECT</OutDialQue_SqlPrefix>
<OutDialQue_SqlSuffix></OutDialQue_SqlSuffix>
</Dialer>

At this stage we would recommend disabling the PortToUse table if Oracle is used.

Postgres 9.2.x

Here is an example Config.xml <Dialer> section for Postgres 9.2.x (and later):

<Dialer>
<OutDialQue_ADODB_Provider>Npgsql</OutDialQue_ADODB_Provider>
<OutDialQue_Database>vgDb</OutDialQue_Database>
<OutDialQue_ConnectString>Server=10.1.1.36;User Id=postgres;Password=postgres;Database=vgDb;</OutDialQue_ConnectString>
<OutDialQue_PortToUse_LinkField></OutDialQue_PortToUse_LinkField>
<OutDialQue_SqlPrefix>SELECT</OutDialQue_SqlPrefix>
<OutDialQue_SqlSuffix>LIMIT 1</OutDialQue_SqlSuffix>
</Dialer>

For a Postgress installation on the same machine the IP address 127.0.0.1 should be used:

<Dialer>
<OutDialQue_ADODB_Provider>Npgsql</OutDialQue_ADODB_Provider>
<OutDialQue_Database>vgDb</OutDialQue_Database>
<OutDialQue_ConnectString>Server=127.0.0.1;Port=5432;User Id=postgres;Password=postgres;Database=vgDb;</OutDialQue_ConnectString>
<OutDialQue_PortToUse_LinkField></OutDialQue_PortToUse_LinkField>
<OutDialQue_SqlPrefix>SELECT</OutDialQue_SqlPrefix>
<OutDialQue_SqlSuffix>LIMIT 1</OutDialQue_SqlSuffix>
</Dialer>

If Postgres is installed on another server you will need to configure Postgres to allow remote access. This is done by editing Postgres pg_hba.conf configuration file.

Best way to configure and test the ADO.NET connection is to just start the VoiceGuide "Outbound Call Loader" application. On startup the Outbound Call Loader will connect to the database object and run the Db_Create_Npgsql.sql script creating the Tables and Indexes. You can then use the Outbound Call Loader to load new calls into the system, or try loading the calls into the database yourself directly.

SQLite  

For completeness, here is an example Config.xml <Dialer> section which is used by default to connect to an SQLite database:

<Dialer>
<OutDialQue_ADODB_Provider>System.Data.SQLite</OutDialQue_ADODB_Provider>
<OutDialQue_Database>C:\Program Files (x86)\VoiceGuide\data\vgDb.db</OutDialQue_Database>
<OutDialQue_ConnectString>Data Source=$DATABASE</OutDialQue_ConnectString>
<OutDialQue_PortToUse_LinkField></OutDialQue_PortToUse_LinkField>
<OutDialQue_SqlPrefix>SELECT</OutDialQue_SqlPrefix>
<OutDialQue_SqlSuffix>LIMIT 1</OutDialQue_SqlSuffix>
</Dialer>

Call Prioritization

Priority ordering is specified by using the ORDER BY clause when selecting calls from the database. This clause is specified in the OutDialQue_SqlSuffix setting in the <Dialer> section of the Config.xml file:

<OutDialQue_SqlSuffix>ORDER BY Priority ASC</OutDialQue_SqlSuffix>

If you have a large number of calls loaded then ordering the retrieved calls by Priority can degrade call data retrieval speed. If you are seeing excessive call retrieval times from the database then removing this clause would speed up call retrieval.

If priority is being enabled and a large number of calls are being loaded then we'd recommend using a sever class database like SQL Server or MySql etc.

Loading Calls Directly

This section outlines how calls need to be loaded into the Dialer Database if you would like your own programs to load the calls instead of using the VoiceGuide Telephone Number Loader or the VoiceGuide WCF/COM function or XML file to load the calls.

The outbound calls database uses two tables: CallQue and PortToUse

Addition of new calls into the system involves placing new entries in both tables, with possible multiple entries in the PortToUse table, depending on what port selections need to be specified.

The PortToUse table is used to indicate on which ports the particular call can be made. If a call is allowed to be made on any of the systems ports then a single entry in the PortToUse table needs to be made, with the PortToUse. PortNumber field assigned a value of -1. Otherwise if call can only be made on some of the ports then a new row needs to placed in PortToUse for each port on which the outgoing call is allowed to be made. The telephony ports on the VoiceGuide system are numbered from 1.

By default the PortToUse table is used when establishing which call is to be made next. Scheduling information is still included in the CallQue table as there is an option of turning off the PortToUse table use altogether if there is no need to limit the ports on which the calls can be made.

It's recommended to use the Outbound Call Loader application to load some calls into the database and then examine the database tables to how the information is placed in the tables.

The SQL statements used can be seen in the vgDialListLoad trace files (see VG's \log\ subdirectory), and it's recommend that these traces be looked at by anybody wanting to see how the Dial List Loader is actually performing the inserts.

Here are the two main SQL statements used (with parameter placeholders):

INSERT INTO callque (GUID, PhoneNumber, PhoneNumberPrefix, ActivateTime, Priority, TimeStart_Mon, TimeStart_Tue, TimeStart_Wed, TimeStart_Thu, TimeStart_Fri, TimeStart_Sat, TimeStart_Sun, TimeStop_Mon, TimeStop_Tue, TimeStop_Wed, TimeStop_Thu, TimeStop_Fri, TimeStop_Sat, TimeStop_Sun, CampaignName, OnAnswerLive, OnAnswerMachine, OnAnswerFax, OnNotAnswered, OnRetriesExhausted, AnswerTimeout, RetriesLeft, RetriesDelay, RV, CallOptions, EscalationCalls)
OUTPUT Inserted.ID VALUES (@guid, @strNbrToDial, @strPhoneNumberPrefix, @dateActivateTime, @iPriority, @iTimeStart_Mon, @iTimeStart_Tue, @iTimeStart_Wed, @iTimeStart_Thu, @iTimeStart_Fri, @iTimeStart_Sat, @iTimeStart_Sun, @iTimeStop_Mon, @iTimeStop_Tue, @iTimeStop_Wed, @iTimeStop_Thu, @iTimeStop_Fri, @iTimeStop_Sat, @iTimeStop_Sun, @strCampaignName, @strOnAnswerLive, @strOnAnswerMachine, @strOnAnswerFax, @strOnNotAnswered, @strOnRetriesExhausted, @iAnswerTimeout, @iRetriesLeft, @iRetriesDelay, @strRV, @strOptions, @strEscalationCalls);

INSERT INTO porttouse (CallID, CallGUID, PortNumber, ActivateTime, Priority, TimeStart_Mon, TimeStart_Tue, TimeStart_Wed, TimeStart_Thu, TimeStart_Fri, TimeStart_Sat, TimeStart_Sun, TimeStop_Mon, TimeStop_Tue, TimeStop_Wed, TimeStop_Thu, TimeStop_Fri, TimeStop_Sat, TimeStop_Sun)
VALUES (@iCallID, @guid, @iPortNumber, @dateActivateTime, @iPriority, @iTimeStart_Mon, @iTimeStart_Tue, @iTimeStart_Wed, @iTimeStart_Thu, @iTimeStart_Fri, @iTimeStart_Sat, @iTimeStart_Sun, @iTimeStop_Mon, @iTimeStop_Tue, @iTimeStop_Wed, @iTimeStop_Thu, @iTimeStop_Fri, @iTimeStop_Sat, @iTimeStop_Sun);

All the call details are first inserted into the CallQue table, and then a subset of call information is inserted into the PortToUse table. After inserting a row into the CallQue table the value of the autogenerated ID field needs to be retrieved and the value of that ID field is used in the CallID field when inserting related rows into the PortToUse table. This can be done differently depending on what database is used.
In the INSERT INTO CallQue example above the ID retrieval is done using the SQL command OUTPUT Inserted.ID

The ID retrieval can also be be done on some databases using this SQL command:<

SELECT DISTINCT @@identity FROM callque

The above command can be issued at the same time as the Insert command, like this:

INSERT INTO callque (...) VALUES (...); SELECT DISTINCT @@identity FROM callque

An alternative approach instead of placing the CallQue.ID in the PortToUse.CallID column would be to use a GUID to link the PortToUse entries to the CallQue entry. Just generate a GUID and write the same GUID into the PortToUse.CallGUID column and into the CallQue.GUID column. A value of -1 can then be placed in the PortToUse.CallID column.

Loading Calls Directly - CallTrack table

CallTrack table is used to store a record of the outbound calls' progress, and information from that table is used to generate outbound call reports.

When loading calls directly an entry in CallTrack table should also be created if you would like to keep track of call progress information.

After inserting a row into the CallQue table the value of the autogenerated ID field needs to be retrieved and the value of that ID field should be used in the CallTrack.QueID field when inserting related rows into the CallTrack table.

An alternative approach: instead of placing the CallQue.ID in the CallTrack.QueID column would be to use a GUID to link the CallQue entries to the CallTrack entry. Just generate a GUID and write the same GUID into the CallTrack.QueGUID column and into the CallQue.GUID column.

Other Notes

<OutDialQue_PortToUse_LinkField> options are:

The <OutDialQue_SqlPrefix> and <OutDialQue_SqlSuffix> values are used when VoiceGuide constructs the SQL query to find the suitable call in the PortToUse table.

The default value used if <OutDialQue_SqlPrefix> is blank is: SELECT TOP 1

The actual SQL scripts used by VoiceGuide to automatically create the database tables used by it can be found in VoiceGuide's \system\setup\ subdirectory. The default scripts used is named Db_Create.sql

ADO.NET Provider Specific versions of this script can be created. To make a Provider-specific script file the Provider name needs to be specified as part of the filename:

Db_Create_ProviderName.sql

eg:

Db_Create_System.Data.SqlClient.sql