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 DATABASEThe 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 INTERESTWhiteListThe 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 9064486The 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 & UsersThese 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