Author Topic: Accessing the CAM Database  (Read 18332 times)

csavage

  • Newbie
  • *
  • Offline Offline
  • Posts: 1
    • View Profile
    • Email
Accessing the CAM Database
« on: April 28, 2006, 03:35:55 PM »
These notes were put together from research I have done over the past few days, and I am posting them here in case they would be of use to anyone else using Call Accountign Mate.  The version of CAM I am using is 2.6.

ACCESSING the CAM DATABASE

The database for Call Accounting Mate (CAM) is file CALLS.DAT in the C:\Bill directory.

CAM 2.6.x uses a SQLite version 2.8 database.  SQLite version 3.x (SQLite3.exe) and the version 3.x ODBC driver for SQLite will not access the CAM database.  Use version 2.

The CALLS database can be accessed either from the command line tool sqlite.exe or by using the version 2, non-UTF ODBC driver.  A machine data source has been made named "CallAccountingMateDB" that can be used by Query Tool for ODBC (QTODBC) or other ODBC-compliant database applications.

Both the command line utility sqlite.exe and QTODBC with its GUI can be used to view the contents of the calls database and run SQL statements.  In Windows, QTODB is much easier to use than sqlite.exe for querying the data.  Sqlite.exe does provide access to the more proprietary aspects of calls.dat if one needs that.  The schema listings below, for instance, were made using sqlite.exe.


RESOURCES

        Home page for SQLite:  http://www.sqlite.org/

      ODBC Driver for SQLite:  http://www.ch-werner.de/sqliteodbc/

       Call Accounting Forum:  http://www.callaccounting.ws/forum/

Query Tool by George Poulose:  http://gpoulose.home.att.net/


TABLES of INTEREST

WhiteList

The WhiteList table holds the CAM Contacts information.

Schema:

   create table WhiteList
   (
     ID,
     OrdID,
     Name,
     PhoneList,
     Phone,
     primary key(ID, OrdID)
   );
   create index IX_WHITELIST_NAME on WhiteList(Name);
   create unique index UQ_WHITELIST_ID_ORDID on WhiteList(ID, OrdID);
   create unique index UQ_WHITELIST_PHONE on WhiteList(Phone);

When running CAM reports, the Phone field is searched for a matching phone number in order to display the name of the contact as recorded in the Name field.  Multiple phone numbers entered for one contact create that many separate records (rows) in the WhiteList table.

Here is an example of one fictitious contact ("ACME Corp") that, when viewed from CAM's web interface, has three telephone numbers listed in its single Contact record.

ID   OrdID  Name       PhoneList                Phone
--  -----  ---------  -----------------------  -------
29   0      ACME Corp  7082414,7082415,9064486  7082414
29   1      ACME Corp  7082414,7082415,9064486  7082415
29   2      ACME Corp  7082414,7082415,9064486  9064486


The Phone field has a unique index for fast lookup.  Note that this also precludes having the same telephone number appear for more than one named contact, a situation that may occur in the real world.

Departments & Users

These two tables contain the data in the Departments/Users section of Call Accounting Mate.

Their construction is straightforward.  A DeptID field in the Users table serves to relate individual users to their department.

Schema for the Departments table:

   create table Departments
   (
     ID INTEGER PRIMARY KEY,
     Name varchar(64) not null,
     Code varchar(32) not null
   );

Schema for the Users table:

   create table Users
   (
     ID INTEGER PRIMARY KEY,
     DeptID not null,
     Name not null,
     Ext not null,
     AccCode not null,
     AltExt not null);

The above are simplified schema, just listing the field names.  There are many triggers on these tables, especially Users. The triggers apparently serve to enforce data integrity and relationships with other tables in the CAM database.

=======================================

- csavage

brokentwig

  • Newbie
  • *
  • Offline Offline
  • Posts: 1
    • View Profile
Re: Accessing the CAM Database
« Reply #1 on: December 04, 2009, 02:04:37 PM »
Nice post, thanks for the help.  One of the issues I have had in the past is scheduled tasks.  Although initially the Scheduled tasks run fine and in an appropriate time period, after the database starts getting larger (10-20 GB), the tasks hang the service.  Using you info I installed the ODBC drivers and ran a "DELETE FROM tasks" on the calls.dat.  Started the service back up and walla...runs like a top.  Thanks again.